Every “the migration locked the table” outage started with a reviewer who said “looks fine”. A good migration review prompt names the table size class, the locking model, the rollback, and forbids combining schema + data + behaviour in one migration.
Who this is for
Anyone reviewing migrations: DBAs, backend leads, founders pre-launch, and on-call engineers verifying a forward-only release.
When not to use these prompts
Don’t use these for greenfield schemas — that’s a design problem, not a migration review. Don’t use them when you don’t know the table size.
Prompt anatomy / structure formula
Every migration review prompt should carry six elements:
- Role: who AI plays (SRE / release captain / staff engineer / QA lead).
- Context: stack / branch / failing logs / diff / dashboard URL.
- Goal: one concrete deliverable — root cause, checklist, plan, ticket list, runbook.
- Constraints: what AI MUST NOT do (don’t auto-fix, don’t hallucinate file paths).
- Output format: numbered findings, markdown table, JSON, unified diff, runnable code.
- Examples / signal: 1-2 “good” output examples, or counter-examples.
Best for
- Pre-merge migration safety review
- Backfill plan for a billion-row update
- Detecting concurrent-deploy hazards
- Forward-only migration verification
- Rollback plan generation
12 copy-ready prompt templates
1. Migration safety triage
Review this migration: {migration}. Output: (1) Lock duration estimate based on table size class (S < 100k, M < 10M, L > 10M), (2) Concurrent-deploy compatibility — does old code break against new schema? (3) Required backfill, (4) Rollback recipe, (5) GREEN / YELLOW / RED verdict.Variables to swap: migration
2. NOT NULL on a big table
Plan to add NOT NULL to a table with {rowCount} rows. Steps: (1) Add nullable column, (2) Backfill in batches of N with idle waits, (3) Verify zero NULLs, (4) Add CHECK NOT VALID, (5) VALIDATE CONSTRAINT (Postgres) / convert to NOT NULL. Specify batch size + downtime estimate.Variables to swap: rowCount
3. DROP / RENAME hazard check
This migration DROPs / RENAMEs `{columnOrTable}`. List which application code reads / writes it (file:line). Compute the deploy-order risk: if old code reads after new schema applies, you have downtime. Output: deploy-order plan or "block — old code still active".Variables to swap: columnOrTable
4. Index creation on a big table
Plan to create an index on a {rowCount}-row table. Decide: (1) CREATE INDEX CONCURRENTLY (Postgres) or pt-online-schema-change (MySQL)? (2) Estimated time + lock impact, (3) Disk space needed, (4) How to verify no duplicates blocking, (5) Plan B if cancelled mid-way.Variables to swap: rowCount
5. Backfill batching plan
Backfill {nRows} rows of column `{col}`. Plan: (1) Batch size + sleep, (2) Idempotency (resume on failure), (3) Progress tracking, (4) Lag monitoring on replicas, (5) Abort criteria. Output as a runnable script outline.Variables to swap: nRows, col
6. Forward-only verification
This migration is forward-only (no rollback DOWN). Verify the migration is recoverable forward: (1) If applied partially, can the next deploy re-run safely? (2) Is the new schema observable by old code (NULLABLE / DEFAULT)? (3) Are we feature-flag-protected? Output GO/NO-GO.7. Rollback recipe
Write a rollback recipe for this migration: (1) Revert SQL (or compensating writes), (2) Data restore strategy if rows were transformed, (3) Order vs application revert, (4) Deadline for safe rollback (after which data drift makes it unsafe).8. Lock-aware DML rewrite
This UPDATE will lock {tableName} for hours: {sql}. Rewrite as batched UPDATEs with a lock-friendly WHERE clause. Use cursor-based iteration over PK. Show the rewritten SQL + a runner skeleton.Variables to swap: tableName, sql
9. Concurrent migration audit
Two migrations are landing this week: {migA} and {migB}. Check: (1) Do they touch the same table? (2) Will they serialize on the same lock? (3) Is the deploy order specified? (4) Are they both idempotent if one fails? Output a coordination plan.Variables to swap: migA, migB
10. Migration test plan
Generate a test plan for this migration: (1) Run on a copy of prod-shaped data, (2) Time the migration with realistic concurrency, (3) Assert post-state matches expected (row counts, constraints, indexes), (4) Run app smoke tests against the migrated DB. Output a runnable checklist.11. RLS / policy migration review
This migration adds / changes row-level security policies on `{table}`. Verify: (1) Existing queries still authorise correctly (no accidental lockout), (2) Service-role queries unaffected, (3) Policy ordering — first-match vs combined, (4) Tests cover both authed and anon paths.Variables to swap: table
12. Migration post-mortem
A migration caused incident: {incidentSummary}. Write a brief post-mortem: (1) What lock / write pattern caused the outage, (2) Why review didn't catch it, (3) One process change (e.g., require row-count in PR description), (4) One automated check we should add. 200 words max.Variables to swap: incidentSummary
Common mistakes
- Adding NOT NULL on a big table in one statement.
- Renaming a column while old code still reads it.
- CREATE INDEX without CONCURRENTLY on a hot table.
- No rollback recipe — first failure becomes a long outage.
- Combining schema change + data backfill + behaviour change in one migration.
- Skipping the row-count question — every safety check depends on it.
- No test on prod-shaped data — staging hides scale-only bugs.
How to push results further
- Always state the table size class in the PR description.
- Expand → migrate → contract: never do all three in one PR.
- Backfills need batches, sleep, and resume-on-failure.
- CONCURRENTLY for Postgres index creation; pt-online-schema-change for MySQL.
- Test migrations on a copy of prod data, not seed fixtures.
- Every migration PR should answer: “If this lands at 3pm Friday, what breaks?”
- Keep rollback in the same PR — if you can’t write it, the migration isn’t safe.
Practical depth notes
Use these prompts as starting points, not final answers. For Database Migration Review Prompts for Safe Schema Changes, the useful extra work is to replace every generic placeholder with a real constraint: audience, channel, length, brand voice, examples to imitate, and examples to avoid. Run at least two versions with different constraints, then compare the outputs side by side instead of accepting the first polished response.
A good result should pass three checks: it is specific enough that another person could reuse it, it avoids vague praise or filler, and it gives you an editable artifact rather than a broad suggestion. If the output feels generic, add one concrete reference, one forbidden pattern, and one measurable success criterion before rerunning the prompt.
FAQ
- When is a migration “small” enough to skip review?: Never. Even adding a default can lock a table on MySQL.
- Should AI auto-approve simple migrations?: No. Auto-suggest improvements; human signs off.
- How do I size a migration safely?: Get row count + average row size + concurrent QPS. Plan from there.
- Can I run migrations during business hours?: For small additive ones, yes. For schema-altering or big backfills, off-hours.
- Do I need feature flags for new schema?: Yes, when behaviour depends on the new column being populated.
- What if I can’t write a rollback?: Split the migration. Anything that can’t be rolled back is a hidden risk.
Related
- Database schema review prompts
- Code review prompts
- Deployment check prompts
- Release checklist prompts
- Coding & Developer Prompts hub
Tags: #Prompt #Coding #Database #Migration #Schema