How to Write a Safe Database Migration Prompt That Won't Break Prod

A DROP COLUMN that looked harmless in review took down checkout for nine minutes. The column was still read by a worker nobody remembered. That's the failure a safe database migration prompt exists to catch: not bad SQL syntax, but the change that's valid, reviewed, and still incompatible with the version of the app running in production right now.

Most teams review migrations by eye. A reviewer skims the diff, nods at the ALTER TABLE, and approves. The problem isn't competence. It's that the dangerous part of a migration is usually what the SQL doesn't say: which app version reads the old shape, how long the lock holds on a 40-million-row table, whether there's a way back.

A reusable prompt fixes the inconsistency. You write the review checklist once, lock it to an output contract, and every migration gets the same scrutiny instead of whatever the reviewer happened to remember that morning.

Why eyeballing a migration diff misses the real risk

The SQL tells you the operation. It doesn't tell you the blast radius. A migration review prompt has to reconstruct the context the diff hides: the deployed app version, the row count, the read paths.

Here's the opinionated part. Most "AI reviews your SQL" demos are theater because they grade syntax and call it safety. Syntax is the easy 5%. The real review is operational: will this lock a hot table during peak traffic, and is there a rollback if it does? A prompt that only validates that the SQL parses is worse than useless, because it gives you false confidence.

So the prompt's job isn't to write the migration. It's to interrogate one.

What a safe database migration prompt actually does

A safe database migration prompt is a structured reviewer that takes a migration script plus deployment context and returns a risk verdict with a rollback plan. It reads the change, classifies the risk, and refuses to bless anything irreversible.

Here's what you can run it for:

• Flag destructive operations: DROP, TRUNCATE, type narrowing, adding NOT NULL to a populated column
• Catch missing foreign-key indexes that turn a constraint into a full-table scan
• Check backwards compatibility against the currently deployed app version
• Estimate lock duration and downtime risk for the target engine
• Produce an expand/contract rollout sequence with a go/no-go gate between phases
• Output a concrete rollback path, or refuse the migration if none exists

That last line is the boundary. No rollback, no pass.

Anatomy of the prompt

The structure that survives long pasted migrations puts the context first, the rules in the middle, and the output contract last. Models weight the most recent tokens, so the contract belongs at the end where it won't get buried under a 200-line schema dump.

Variables
  {{migration_sql}}      → the migration script under review
  {{db_engine}}          → postgres | mysql | etc. (lock behavior differs)
  {{deployed_app_version}} → what's running in prod right now
  {{table_row_counts}}   → row counts for affected tables

Prompt
  Role: you are a database migration safety reviewer.
  Task: review {{migration_sql}} for the risks below. Do not rewrite it.
  Rules:
    - Classify every operation: safe | risky | destructive.
    - Refuse to approve a destructive op with no rollback path.
    - Account for {{db_engine}} lock semantics on large tables.

Output contract (last line of the prompt)
  Return: RISK verdict + per-operation table + expand/contract plan + rollback.

1. Gather inputs

Paste the migration, name the engine, and supply the deployed app version. The version is the input people skip, and it's the one that catches incompatibility bugs.

2. Fill variables

Drop the script into {{migration_sql}} and set {{db_engine}}. If you don't have exact {{table_row_counts}}, an order-of-magnitude estimate still changes the lock-risk verdict.

3. Run and read the verdict

The output leads with a single verdict so a reviewer can quote it in the PR. Below it, the per-operation table is the part you actually act on.

4. Stage the rollout

For anything tagged destructive, the prompt returns the expand/contract phases. Ship phase one, deploy code that handles both shapes, then contract.

Prompt-craft patterns that make the review trustworthy

Name the engine, every time. Postgres can add a nullable column without a rewrite; older MySQL versions copy the whole table. A migration prompt that doesn't know {{db_engine}} gives you generic advice that's wrong half the time.

Put the output contract last. When you paste a long migration, the schema dump pushes earlier instructions out of the model's attention. The contract on the final line stays in view. This is the single most reliable fix for the model ignoring its format on long inputs.

Force a per-operation verdict, not a paragraph. A prose summary hides the one dangerous line in a wall of reassurance. A table with one row per operation makes the destructive tag impossible to miss.

Claude vs ChatGPT for migration review

The two models behave differently on the part that matters: the refusal boundary.

Neither is "better." Claude errs toward caution, which is what you want for a destructive-op gate. ChatGPT is more permissive, so the refusal rule has to be louder. Pin the model version either way. A prompt tuned on one snapshot can drift after an update, and a migration reviewer that quietly got more lenient is exactly the kind of regression you won't notice until prod.

Variables you'll set

Getting started

1. Copy the structure above into your model of choice.
2. Set {{db_engine}} and paste your migration into {{migration_sql}}.
3. Add the deployed app version so the compat check has something to compare against.
4. Run it and read the verdict line first, then the per-operation table.
5. For destructive ops, follow the expand/contract phases the prompt returns.
6. Pin the model version once the review reads correctly, and recheck after any model update.

If you'd rather not hand-tune the refusal boundary and the engine-specific lock logic, the Database Migration Safety Harness ships it ready to run.

A safe migration prompt is one tool in a larger refactoring toolkit. If you're also bumping packages, the Dependency Upgrade Harness Agent Pack applies the same risk-tiering to version upgrades. For choosing between building your own and buying a tuned pack, see how to choose a reusable AI prompt pack, and for staged upgrades across a codebase, read the framework migration prompt walkthrough.