Define tables, fields, constraints, and migration plans — get a ready-to-use database spec. Copy or download Markdown.
The generator output is meant to be pasted into a ticket, pull request, or design review. It should name the owner, decision, evidence, and follow-up work before implementation starts.
Migration: add invoice_retry_state Risk: - table size: 18M rows - lock: avoid full table rewrite Rollout: - add nullable column - backfill in batches - enforce constraint after verification
Treat the generated database migration spec as a starting artifact. Replace example values, attach evidence, assign reviewers, and remove sections your team will not maintain.
A database spec is a structured document that defines tables, fields, data types, constraints, indexes, and migration plans — all before writing any SQL or ORM code. It serves as the single source of truth for how your application stores and retrieves data.
Writing a DB spec before coding prevents schema drift, reduces migration headaches, and ensures every team member understands the data model. With a clear spec in place, backend engineers can implement migrations while application developers build queries — and both sides know exactly what tables and relationships exist.
Learn more: What is Spec-First Development? or browse our DB Spec Template.
Describe the product concept the schema supports before listing columns. That context helps reviewers catch missing relationships, lifecycle states, retention rules, and reporting needs that a table definition alone will hide.
A unique index, foreign key, not-null rule, or check constraint should map to behavior the product depends on. If a constraint protects money, access, compliance, or data integrity, explain the reason in the spec.
Every risky step should have a mitigation: expand-contract rollout, background backfill, dry-run query, feature flag, compatibility window, or a rollback plan that does not lose newly written data.
Indexes improve reads but can slow writes, lock tables, or increase storage. Call out cardinality, expected query patterns, online DDL requirements, and whether large-table backfills need batching.
Can the migration run twice safely? What happens to existing rows, null values, duplicate data, and writes that arrive during the transition?
Will old and new application versions both work during deploy? Specify defaults, nullable phases, read fallbacks, and cleanup timing.
What dry-run query, migration timing estimate, lock check, or monitoring signal proves the change is safe enough for production?
Add status to subscriptions. Backfill existing records.
This does not explain allowed values, default behavior, deployment order, how long backfill takes, or what rollback means after new writes begin.
Add nullable status, deploy readers with fallback to billing state, backfill in batches of 5,000, then enforce NOT NULL after metrics show zero null reads for 24 hours.
This describes a safe transition and gives reviewers observable checkpoints.
Link the generated spec from the migration pull request so reviewers can compare intended schema behavior with actual SQL, ORM changes, data backfills, and application compatibility work.
Large-table backfills, lock-sensitive index creation, and irreversible data cleanup should become explicit runbook steps with owners, timing windows, monitoring queries, and a stop condition.
Database work often spans expand, migrate, verify, and contract phases. Keep the spec updated until fallback code, old columns, temporary indexes, and backfill jobs are fully removed.
For high-risk migrations, attach the final spec to the deployment checklist so the release owner can verify each migration phase before proceeding.
Reviewers should see the exact order: compatible app deploy, schema expansion, backfill, verification query, constraint tightening, old-code removal, and final cleanup. If a step cannot be observed, it needs a better checkpoint.
A rollback note should explain what can be reverted, what data may already be written in the new shape, and who decides whether to pause, retry, or continue forward with mitigation.
If a migration cannot name its last safe rollback point, it should not be treated as low risk.
Before approval, add row-count estimates, query plans, batch size, lock expectations, and the last safe rollback point. The generated spec gives the structure; the evidence proves the migration can run safely in your production context.
Ask reviewers to inspect deploy order, backfill timing, compatibility between old and new application versions, and cleanup timing. Most database incidents come from unsafe sequencing rather than an obviously wrong column definition.
Include SQL only when it clarifies the decision. The spec should first explain schema intent, constraints, migration order, compatibility, and rollback; implementation SQL can live in the migration itself.
Rollback. Teams often list the forward migration but not how to recover if the new app version writes data the old version cannot understand. Treat rollback as part of the design, not an afterthought.
The fields emphasize the database decisions that cause expensive incidents: constraints, indexes, backfill order, compatibility windows, rollback boundaries, and verification queries. Schema shape is only one part of the review.
Generated output is checked for whether a release owner can stage the migration, observe progress, pause safely, and explain the last safe rollback point. A DB spec should make production behavior reviewable.
Author and editor: Daniel Marsh. Review process: Editorial Policy. Corrections and tool issues: Contact. Last reviewed: April 28, 2026.