The Architecture Decision Record Generator
Why this prompt matters
Engineering teams without ADRs spend 40-60% of new-hire onboarding time reconstructing why decisions were made — often incorrectly. When the original decision-maker leaves, the institutional knowledge leaves with them. Six months later, engineers rip out a perfectly good system because nobody documented why it was chosen, and the replacement has the exact same failure modes.
What we use it for
Your team just decided to adopt Kafka for event streaming, ditch your REST API for GraphQL, or migrate from a monolith to microservices — and your tech lead needs a proper ADR written before the sprint ends. You have 20 minutes and no template.
Prompt
Act as a senior software architect with 15+ years of experience leading engineering teams at scale. Context: I am on the engineering team at [COMPANY/TEAM NAME]. We are making a technical decision about [DESCRIBE THE TECHNICAL DECISION IN ONE SENTENCE — e.g., "whether to use PostgreSQL or MongoDB as our primary data store"]. Our system serves [DESCRIBE YOUR SCALE — e.g., "10,000 daily active users with plans to reach 1M in two years"]. Our current stack is [DESCRIBE YOUR STACK — e.g., "Node.js backend, React frontend, running on AWS"]. Task: Generate a complete Architecture Decision Record (ADR) for this decision. The ADR must be honest and rigorous — not a rubber stamp for a predetermined choice. Constraints: - Do not assume the decision I am leaning toward is correct. Actively challenge it. - Every claim must be supported by concrete reasoning, not vague assertions. - The "Alternatives Considered" section must include at least 3 options with genuine tradeoffs — not strawman comparisons. - The "Devil's Advocate" section must present the strongest possible argument AGAINST the chosen option. - Be specific: name actual tools, libraries, failure modes, and real-world examples where relevant. - Avoid filler phrases like "it depends" without following them with what it depends on. Output Format: # ADR-[NUMBER]: [TITLE] **Date**: [TODAY'S DATE] **Status**: Proposed | Accepted | Deprecated | Superseded ## Context [2-4 sentences: What problem are we solving? What forces are at play? What constraints exist?] ## Decision [1-3 sentences: What we decided and the primary reason] ## Decision Drivers - [Driver 1: specific technical or business constraint] - [Driver 2] - [Driver 3] ## Alternatives Considered ### Option A: [NAME] ✅ (Selected) **Pros**: [specific, concrete pros] **Cons**: [honest cons — not trivial ones] **Why selected**: [specific reasoning] ### Option B: [NAME] **Pros**: [...] **Cons**: [...] **Why not selected**: [specific reasoning] ### Option C: [NAME] **Pros**: [...] **Cons**: [...] **Why not selected**: [specific reasoning] ## Consequences ### Positive - [Concrete positive outcome] - [Another concrete positive] ### Negative - [Honest negative consequence] - [Another honest negative] ## Devil's Advocate [The strongest argument AGAINST this decision — 2-4 sentences. Be intellectually honest.] ## Review Date [When should this decision be revisited? Under what conditions would we change course?]
Result
<h2>ADR-007: Use PostgreSQL as the Primary Data Store for Helios SaaS Platform</h2><p><strong>Date</strong>: 2026-06-03<br/><strong>Status</strong>: Accepted</p><h2>Context</h2><p>Helios is a B2B SaaS platform for project portfolio management, currently serving 52,000 daily active users across 1,400 enterprise customers. We are running on a Node.js/Express backend, React frontend, deployed on AWS ECS with an RDS MySQL 5.7 instance as our primary store. MySQL 5.7 reaches end-of-life in October 2026 and we must migrate. Simultaneously, our product roadmap calls for highly flexible project metadata schemas — customers increasingly want custom fields, nested attributes, and dynamic reporting dimensions that fight our current rigid schema. We evaluated whether this is the right moment to switch to a document database (MongoDB) rather than upgrading to MySQL 8 or migrating to PostgreSQL.</p><h2>Decision</h2><p>We will migrate from MySQL 5.7 to <strong>PostgreSQL 16</strong> as our primary relational store. PostgreSQL's JSONB column type gives us the schema flexibility we need for custom fields without surrendering ACID transactions, mature tooling, or the relational model our reporting layer depends on.</p><h2>Decision Drivers</h2><ul><li><strong>EOL forcing function:</strong> MySQL 5.7 support ends October 2026; inaction is not an option.</li><li><strong>Custom metadata flexibility:</strong> Customers require 10-200 dynamic custom fields per project entity; current rigid schema requires schema migrations for every new field type.</li><li><strong>Reporting integrity:</strong> Finance and PMO teams run complex multi-table JOINs and window functions for cross-project analytics; losing ACID and JOIN semantics would require rebuilding the entire reporting layer.</li><li><strong>Operational familiarity:</strong> 4 of 5 backend engineers have PostgreSQL production experience; 0 have MongoDB production experience beyond toy projects.</li><li><strong>AWS RDS support:</strong> PostgreSQL 16 is fully supported on Amazon RDS with automated failover; no infrastructure re-architecture required.</li></ul><h2>Alternatives Considered</h2><h3>Option A: PostgreSQL 16 ✅ (Selected)</h3><p><strong>Pros:</strong> Full ACID compliance with row-level locking; JSONB with GIN indexes allows sub-5ms queries on custom field data at our scale; pg_trgm extension supports full-text search without Elasticsearch for our current needs; logical replication to read replicas is production-tested; pgvector available for future AI features; RDS Proxy reduces connection overhead from our 800-instance ECS fleet.</p><p><strong>Cons:</strong> JSONB schema validation is application-enforced, not database-enforced (unlike SQL CHECK constraints on typed columns); migration from MySQL requires careful handling of MySQL-specific functions (GROUP_CONCAT → STRING_AGG, etc.) and implicit type coercions; no native horizontal write sharding without Citus.</p><p><strong>Why selected:</strong> The JSONB approach directly solves our custom fields problem while preserving JOIN semantics for reporting. pgAdmin and AWS RDS tooling are mature. The migration path from MySQL via pgloader is well-documented, and we can run both databases in parallel during the cutover window.</p><h3>Option B: MongoDB 7.0 (Atlas)</h3><p><strong>Pros:</strong> Native document model eliminates the custom fields schema problem entirely; horizontal sharding is built-in; Atlas Search provides Lucene-backed full-text search; Change Streams simplify real-time notification pipelines.</p><p><strong>Cons:</strong> No multi-document ACID transactions across collections without significant performance penalty (MongoDB 4.0+ has them but they degrade throughput 3-5x at volume); our reporting queries require cross-collection $lookup aggregations that are slower and more brittle than SQL JOINs; rewriting 340+ SQL queries and the Sequelize ORM layer is estimated at 8-12 weeks of engineering; Atlas pricing at our data volume is ~2.3x our current RDS cost; zero production MongoDB experience on the team means we absorb operational risk simultaneously with a major migration.</p><p><strong>Why not selected:</strong> The JOIN-heavy reporting layer is a first-class product feature, not legacy debt we can eliminate. Rewriting it in aggregation pipelines would take longer than the MySQL EOL deadline and introduce regression risk in our highest-revenue feature set.</p><h3>Option C: MySQL 8.0 (In-Place Upgrade)</h3><p><strong>Pros:</strong> Minimal migration risk — in-place upgrade path is well-tested; zero query rewriting; team knows MySQL deeply; RDS supports MySQL 8.0 with automated minor version upgrades through 2030+.</p><p><strong>Cons:</strong> MySQL 8.0 JSON type does not support GIN-equivalent indexes (only generated columns with functional indexes — significantly more verbose and less flexible than PostgreSQL JSONB); custom fields problem is not solved, only deferred; MySQL's window function implementation has known edge-case bugs in versions before 8.0.26; we would be back in the same position when MySQL 8.0 reaches EOL.</p><p><strong>Why not selected:</strong> MySQL 8.0 solves the EOL problem but not the schema flexibility problem. We would be choosing a shorter runway over a harder migration that solves both problems at once.</p><h2>Consequences</h2><h3>Positive</h3><ul><li>Custom fields for project entities can be stored as JSONB without schema migrations; new field types ship in hours instead of days.</li><li>Reporting queries retain full SQL expressiveness; no rewrite of the analytics layer required.</li><li>pgvector support positions us for AI-powered project similarity and recommendation features on the roadmap for Q3 2027.</li><li>Estimated 15-20% query performance improvement on complex reporting workloads based on internal benchmarks (PostgreSQL's planner handles our LEFT OUTER JOIN patterns better than MySQL 5.7).</li><li>Migration eliminates technical debt: MySQL-specific SQL quirks (implicit GROUP BY, lenient type casting) are replaced with standard SQL.</li></ul><h3>Negative</h3><ul><li>One-time migration engineering cost: estimated 6 weeks for a team of 2 engineers (schema translation, query rewriting, pgloader ETL pipeline, parallel run validation, cutover playbook).</li><li>All 340+ queries and stored procedures must be audited; approximately 40 contain MySQL-specific functions requiring rewriting.</li><li>Custom field validation logic moves to the application layer (Zod schemas in our Node.js service) — this is enforced by code review discipline, not the database itself.</li><li>PostgreSQL connection model (process-per-connection) requires RDS Proxy to handle our ECS connection count; adds ~$180/month in infrastructure cost.</li></ul><h2>Devil's Advocate</h2><p>The strongest argument against this decision is that we are solving the custom fields problem with a workaround rather than the right tool. PostgreSQL JSONB is powerful, but we are essentially bolting a document store onto a relational database and will spend the next two years writing application-layer validation code that a proper document database would handle structurally. MongoDB's document model is <em>correct</em> for entities with 10-200 dynamic attributes — PostgreSQL JSONB becomes unwieldy when customers start querying across custom fields in reports, and we will end up with partial indexes on JSONB paths that are fragile and hard to maintain. The 8-12 week rewrite estimate for the reporting layer may be pessimistic; if it is actually 4-6 weeks, the MongoDB option pays for itself within a year through reduced schema maintenance overhead. We may be choosing the comfortable option over the correct one.</p><h2>Review Date</h2><p>Revisit this decision in <strong>18 months (December 2027)</strong> or earlier if: (a) JSONB query performance degrades below our 200ms p95 SLA as data volume grows past 500GB, (b) custom field reporting complexity requires more than 3 partial indexes per entity type, or (c) a major product pivot makes the reporting layer less central to the product value proposition. At that point, evaluate whether a hybrid architecture (PostgreSQL for relational data + a dedicated document store for project metadata) makes sense.</p>
<p>An Architecture Decision Record (ADR) is a short document that captures a significant technical decision: what was decided, why it was decided, and what the consequences are. In theory, every engineering team should have them. In practice, most teams either skip them entirely or write ones so vague they are useless. The result is a codebase full of mysterious choices — a Redis cache that nobody dares touch, a microservice boundary that makes no sense, a database schema that fights every new feature. ADRs exist to prevent this. But writing a good one requires structured thinking, intellectual honesty, and enough time to articulate the tradeoffs clearly.</p><p>What makes this prompt different from a blank ADR template is the built-in adversarial layer. Most ADR templates are confirmatory — they ask you to fill in what you decided and why it is good. This prompt forces the model to actively challenge the proposed decision, require concrete alternatives with genuine tradeoffs (not strawmen), and write a Devil's Advocate section that presents the strongest possible argument against the chosen option. The result is an ADR that can withstand peer review and actually inform future decisions — not just document past ones.</p><ol><li>Fill in the bracketed placeholders: your company/team name, the technical decision in one sentence, your scale (current and target), and your current stack.</li><li>Paste the completed prompt into Claude, GPT-4o, or Gemini 1.5 Pro.</li><li>Review the Devil's Advocate section first — if the arguments there are stronger than the ones in the Decision section, reconsider your choice before documenting it.</li><li>Share the draft ADR with your team for a 15-minute review. Use the Alternatives Considered section as the agenda.</li><li>Save the final ADR in your repository (e.g., /docs/decisions/ADR-001.md) and link to it from your README or architecture wiki.</li></ol><p>For best results, be as specific as possible when describing your scale and stack. Vague inputs produce vague ADRs. If you are deciding between two options, run the prompt twice — once with Option A selected, once with Option B — and compare the two Devil's Advocate sections. The weaker devil's advocate argument usually points to the better decision. Also consider varying the scale parameter: a decision that is correct at 10,000 users may be wrong at 1,000,000, and this prompt will surface that tension explicitly.</p>