Migration Safety Review

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:

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:

-- 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: <name>          Target: <db>          Largest table touched: <n> rows

IRREVERSIBLE
  <statement>  -> destroys <what>, recovery: <backup only | none>

BLOCKING
  <statement>  -> locks <table> (<n> rows) for approximately <estimate>
                  safe form: <rewrite>

INCOMPATIBLE WITH RUNNING CODE
  <statement>  -> <file:line> still reads this; split into expand/contract

SAFE
  <n> 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.

From Toolbay. Free to use, modify, and share. Keep this line and others can find it too.