A migration that passes review and runs cleanly in staging can still take production down, because staging almost never reproduces the three conditions that actually cause incidents: a live table under write load, a rollback that gets exercised for real instead of just existing in a file, and a rolling deploy where two versions of the app talk to the same schema at once. None of these show up by reading the SQL alone — they show up by asking three specific questions first.
The question isn't "does this migration work," it's "what does it hold a lock on, and for how long, while it runs." ALTER TABLE ... ADD COLUMN with a default value used to rewrite the whole table under an exclusive lock on older Postgres/MySQL versions; adding an index without CONCURRENTLY (Postgres) or ALGORITHM=INPLACE (MySQL) blocks writes for the duration. Signature: the migration is instant on a 500-row dev database and takes the app down for four minutes on the 40M-row production table.
Check: for any ALTER TABLE, CREATE INDEX, or ADD CONSTRAINT, look up whether that specific operation is lock-free on your database version — don't assume from the database engine alone, since the safe/unsafe line moved across major versions (Postgres 11 made column defaults instant; MySQL's online-DDL support varies by algorithm and storage engine). If it's not lock-free, either use the non-blocking variant or schedule it for a low-traffic window with an explicit lock timeout so it fails fast instead of queuing every other write behind it.
A rollback script that exists is not the same as a rollback that works. The common gap: the "down" migration drops a column or table, which is only safe if no code path — including the version you're rolling back to — still reads it, and it's only reversible at all if you haven't already destroyed data the down migration can't reconstruct (a dropped column with no backup, a merged pair of tables).
Check: actually run the down migration against a copy of production-shaped data, not just confirm the file exists. If the down migration drops or truncates anything, ask: is there a version of the previous app release that can run correctly against the post-rollback schema? If the answer is no, the migration isn't reversible in practice regardless of what the down script says.
During a rolling deploy, old code and new code both query the live database for a window that can last minutes, and both need to work against the schema as it exists at every point in that window — not just before and after. Signature: a migration that renames a column, deploys fine in a single-instance environment, then errors intermittently in production because half the pods are still running old code expecting the old name.
Check: split any breaking schema change into the classic three-step sequence — (1) add the new column/table alongside the old one and dual-write, (2) deploy the code that reads only from the new location once backfill is complete, (3) drop the old column/table in a later, separate migration. A rename is never one migration; it's at least two deploys apart.
Check lock behavior first because it's the one that causes an outage the moment the migration runs, regardless of code correctness. Rollback symmetry matters next because it determines your actual recovery time if something else goes wrong — a migration you can't safely reverse turns any unrelated incident into a worse one. Rolling-deploy compatibility is last because it's the easiest to miss in a single-instance staging environment and the hardest to reproduce outside of a real multi-instance rollout.
None of this requires an LLM to catch — it's three questions and a test against production-shaped data. Where a model helps is reading an unfamiliar migration file and a diff of the surrounding application code together, flagging exactly which of the three risks applies and which lines of app code would break under each rollout stage — which is what the db-migration-guard skill in the Claude Code Power Pack does. If this release also bumps a dependency, see the companion guide on dependency upgrade risk; if the migration already caused an incident, see the one on blameless postmortems.