--- name: migration-safety-review description: Review a pending database migration for irreversible data loss and for locks that take the site down during deploy — dropped columns, narrowed types, NOT NULL without a default, and indexes built non-concurrently. Use before running any migration against a real database, or when reviewing a schema change. --- # Migration Safety Review ## Install Save this file as `~/.claude/skills/migration-safety-review/SKILL.md`, or `.claude/skills/migration-safety-review/SKILL.md` to scope it to one repo. Claude Code auto-discovers it. Invoke with `/migration-safety-review` or by asking "is this migration safe to run?". ## Why this exists A migration is the one routine deploy step that can be unrecoverable. Application bugs get rolled back; a dropped column is gone unless someone happens to have a backup from before, and knows it, and can restore it under pressure. Two independent risks, and reviews usually catch only the first: 1. **Data loss.** Destructive and irreversible. 2. **Locks.** Perfectly reversible schema changes that hold an exclusive lock long enough to stall every query, taking the site down for the length of the migration on a table large enough to matter. The second is what turns a two-line change into an outage, and it never shows up in staging because staging has a thousand rows. ## Step 1 — Read the actual SQL, not the intent ``` # Prisma npx prisma migrate diff --from-schema-datasource prisma/schema.prisma --to-schema-datamodel prisma/schema.prisma --script git diff -- '*migration*' '*schema*' ``` Never review a migration from the ORM model diff alone. The generated SQL is what runs, and generators make destructive choices — a renamed field routinely becomes DROP plus ADD, which is a rename that deletes every value. ## Step 2 — Classify every statement **IRREVERSIBLE (data is destroyed)** - `DROP TABLE`, `DROP COLUMN` - `ALTER COLUMN ... TYPE` narrowing: text→varchar(n), bigint→int, timestamp→date - `DROP CONSTRAINT` on a unique key that was deduplicating - Any `DELETE`/`UPDATE` embedded in the migration **BLOCKING (site stalls while it runs)** - `ADD COLUMN ... NOT NULL` without a default, on a non-trivial table - `CREATE INDEX` without `CONCURRENTLY` (Postgres) - `ALTER COLUMN ... TYPE` requiring a full table rewrite - Adding a foreign key without `NOT VALID` then a separate `VALIDATE` **SAFE** - `ADD COLUMN` nullable, or with a constant default on modern Postgres - `CREATE INDEX CONCURRENTLY` - New tables - Widening a type ## Step 3 — Size the tables involved Severity is entirely a function of row count, so get it rather than assume: ```sql SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 20; ``` A blocking operation on 500 rows is a non-event. The same operation on 5 million is an outage. **Never report a lock risk without the row count next to it**, or you will either cause panic or be ignored. ## Step 4 — Check reversibility honestly For each migration ask: if this is wrong, what is the recovery? - **Reversible in code** — a down migration genuinely restores prior state. - **Reversible from backup only** — then confirm a backup exists, is recent, and that someone has actually restored one before. An untested backup is a belief. - **Not reversible** — the data is gone. This must be stated in the report in those words, not softened. A `down` that recreates a dropped column restores the SCHEMA, never the VALUES. Reviews routinely mark these reversible. They are not. ## Step 5 — Check it is compatible with the running code During a deploy, old and new application code run simultaneously. A migration that is safe in isolation breaks that window: - Dropping a column the currently-deployed code still reads → errors until the deploy finishes. - Adding `NOT NULL` before the new code writes it → inserts from old code fail. The fix is the expand/contract pattern, deliberately across two deploys: 1. Add the new nullable column, write to both, read from the old. 2. Backfill. 3. Read from the new. 4. Only in a LATER deploy, drop the old. If a migration drops or tightens anything the current release still uses, say it must be split. That is the single most valuable output of this review. ## Step 6 — Rewrite the dangerous ones Offer the safe form concretely: ```sql -- Unsafe: full table lock while the index builds CREATE INDEX idx_orders_buyer ON orders(buyer_id); -- Safe: no write lock (cannot run inside a transaction) CREATE INDEX CONCURRENTLY idx_orders_buyer ON orders(buyer_id); -- Unsafe: rewrites and fails on existing rows ALTER TABLE users ADD COLUMN plan text NOT NULL; -- Safe: three steps, no long lock ALTER TABLE users ADD COLUMN plan text; UPDATE users SET plan = 'free' WHERE plan IS NULL; -- batch on a big table ALTER TABLE users ALTER COLUMN plan SET NOT NULL; ``` ## Step 7 — Report ``` Migration: Target: Largest table touched: rows IRREVERSIBLE -> destroys , recovery: BLOCKING -> locks ( rows) for approximately safe form: INCOMPATIBLE WITH RUNNING CODE -> still reads this; split into expand/contract SAFE statements VERDICT: safe to run | run in a maintenance window | must be split first ``` End with one verdict line. Someone is about to type a command and needs an answer, not a discussion. ## Rules - Review the generated SQL, never the model diff. - A down migration restores schema, not data. Never call a drop reversible. - No lock warning without a row count; severity is row count. - Check compatibility with the CURRENTLY DEPLOYED code, not just the new code. - When a migration is genuinely safe, say so in one line. Hedging every review trains people to skip them.