Migration Safety

A migration runs once, against the production database, usually mid-deploy, while old and new application code overlap. This skill reviews it for the three ways that goes wrong: locks (DDL that blocks traffic), ordering (schema and code versions that can't coexist), and irreversibility (data destroyed with no path back). The core discipline: every migration is judged against the deploy timeline, not against an empty dev database where everything is instant and nothing is watching.

When to use this skill

• The user says "review this migration", "is this migration safe", "will this lock the table", "zero-downtime", "expand and contract", "check the schema change", "/migration-safety".
• A migration file (EF Core, Prisma, alembic, Rails, Flyway, raw DDL) sits in the diff — including ones generated by plan-and-build, which writes migrations but never runs them.
• ship-it flags a migration and it needs the dedicated pass.

Do not auto-trigger for stored-procedure or query changes (sql-review) or for greenfield schemas with no production data — on an empty database most of this catalog is N/A, and the report should say that in one line instead of performing the checklist. This skill never executes migrations, DDL, or any SQL against a database — it reads files and reports.

Workflow

1. Establish the context the file doesn't show. Which engine (Postgres/MySQL/SQL Server/SQLite — lock behavior differs by engine and version, and a lock claim that doesn't name the engine is a guess)? Read the engine from the repo, not the migration tool: the EF provider package in the .csproj (Npgsql.EntityFrameworkCore.PostgreSQL vs Microsoft.EntityFrameworkCore.SqlServer), the Prisma datasource block, alembic's sqlalchemy.url, Rails database.yml. If none of these resolve it, ASK the user before step 3 — every lock finding hangs on the engine; do not pick one by tool association (EF ≠ SQL Server, alembic ≠ Postgres). Is there production data, and are the touched tables large or hot? Get evidence where possible (the user, a row-count comment, table names like events/audit_log that are large by nature) — otherwise label the assumption out loud: "assuming orders is large and hot; if it's small, findings 2–3 downgrade to noise." How do migrations deploy relative to code (before app deploy? in the same release?) — this determines step 4's ordering checks.
2. Walk the destructive-operation gate. DROP COLUMN/DROP TABLE, column/table renames (a rename IS a drop+add to running code), type narrowing (varchar(500)→(50), bigint→int, nullable→NOT NULL), truncates, and DELETE/UPDATE data mutations. Each is a blocker unless an expand-contract plan is stated: expand (add the new thing, dual-write or backfill), migrate readers, contract (drop the old thing in a later release, after old code is provably gone). Drop-in-the-same-release-as-the-code-change fails the rollback test by construction.
   • ❌ "Renamed users.name to users.full_name in one migration — the ORM was updated too, so it's fine."
   • ✅ "Rename is drop+add to the old code still running during rollout: add full_name, backfill, deploy code reading full_name (writing both), then drop name in release N+2. Or, if the deploy has real downtime, say so and the one-step rename is fine — name the assumption."
3. Flag lock-taking DDL with the specific mechanism. Name the lock and what drives its duration — "might be slow" is not a finding. The high-yield catalog: index creation without CONCURRENTLY (Postgres: blocks writes for the whole build) or ONLINE = ON (SQL Server, edition-permitting); NOT NULL added without engine-appropriate staging (Postgres: ADD CONSTRAINT ... NOT VALID then VALIDATE; adding a column NOT NULL with a constant default is metadata-only on modern PG/SQL Server — don't flag what's actually free, that's severity inflation); full-table-rewrite type changes; adding an FK without NOT VALID+VALIDATE; MySQL DDL without an online strategy on big tables. For each: the operation, the lock, the duration driver (table size, write rate), and the non-blocking alternative.
4. Check the deploy-order contract both ways. During rollout, old code runs against the new schema — every added NOT NULL-without-default, dropped column old code still selects, or renamed anything breaks it. During rollback, new-ish data meets old schema expectations — rows written by new code must not violate what old code assumes. Trace both directions against the actual code in the diff (grep for the column names old code uses). Grep for the column name AND the ORM-mapped name (EF property, Prisma field, ActiveRecord attribute) — zero hits on the raw column name proves nothing when the ORM renames; the report quotes the exact patterns searched. If the application code is not in the diff and the repo is not available, the trace did not happen: write "deploy-order contract not traced — application code not provided" and tag both directions assumed. "Unreferenced" may only appear next to the quoted search patterns that returned nothing. Also: does anything in the release require the migration to have run (new code reading a column that doesn't exist yet) — if so the migrate-then-deploy ordering is load-bearing; say it.
5. Separate backfills from DDL. Data backfills inside the schema migration (same transaction as DDL, or a single UPDATE table SET ... over millions of rows) are findings: long transactions hold locks, bloat undo/WAL, and can't checkpoint. Recommendation shape: DDL migration adds the nullable column; a separate batched job (with progress + resumability) backfills; a later migration adds the constraint. Small-table backfills are fine — small means a bounded lookup/config table (roughly ≤10k rows, or seeded in this same diff); if you cannot bound the row count, treat it as large and tag the assumption — say why either way (the size evidence or assumption from step 1).
6. Demand the rollback path, per migration. Three honest answers: (a) reversible — the down-migration exists and actually restores (auto-generated downs that drop a column do NOT restore its data — a down that loses data is not a rollback, label it); (b) roll-forward-only — irreversible by nature, so the plan is a fixing migration, and the deploy gate should know; (c) backup-gated — destructive step requires a named backup/snapshot beforehand. "It has a down file" without reading it is not an answer.
7. Report. Findings ranked blocker (data loss without a stated plan, traffic-blocking lock on a hot table, rollout/rollback contradiction) / should-fix / note, each with file:line, the failure scenario in one sentence (deploy does X → traffic sees Y), and the safe alternative. State the assumptions from step 1 that findings hang on. A green report on an additive, defaulted, indexed-concurrently migration is a valid outcome — two lines, not a performed checklist. Never edit or execute anything unprompted; ask per-finding whether to draft the fix. Before reporting: every lock finding names the engine or is tagged assumed; both deploy directions were traced or are marked not-traced; every rollback verdict came from reading the down, not from its existence; anything not opened (runner config, down file, app code) is labeled hypothesis — a down-migration you did not read is "not read", never "reversible".

Output format

# Migration safety review — <migration file(s)>

**Engine:** <engine, and where it was read from> · **Deploy order:** <migrate-then-deploy / same release / unknown>

**Assumptions** — tag each premise: verified = observed and quoted, inferred = follows
from a verified fact, assumed = unverified (if wrong, name which findings change).
(`verified` = observed this session; `inferred` = deduced from something observed; `assumed` = neither.)
- <premise> — <tag> — <which findings change if wrong>

## Blockers (<N>)
### B1. <short title> — `path/to/migration:<line>`
**Evidence:** <quoted DDL>
**Failure:** <deploy does X → traffic sees Y>
**Safe alternative:** <one line>

## Should-fix (<N>)
### S1. <same shape as B1>

## Notes (<N>)
- `path:line` — <one-liner>

## Rollback (per migration)
- `<migration>` — reversible / roll-forward-only / backup-gated — <evidence from reading the down>

The honest green is this format collapsed, not skipped:

# Migration safety review — 20260707_add_preferences — no findings
Nullable add is metadata-only; index is CONCURRENTLY (runner runs it outside a transaction — verified in <runner config>); old code unreferenced (searched: `preferences`, `Preferences` — zero hits); rollback: reversible, down drops an empty column.

Examples

Example 1: the innocent-looking rename

User: "Review this EF migration before I merge — just renames a column and adds an index."

Claude: Flags the rename as drop+add against live old code (blocker, expand-contract plan sketched across 3 releases), checks the index: CreateIndex on Orders — SQL Server without ONLINE = ON; asks/flags edition support, cites the line. Checks the down: RenameColumn back — actually reversible, noted. One assumption stated: Orders presumed hot.

Example 2: honest green

User: "Is this migration safe? Adds a nullable preferences jsonb column and a partial index CONCURRENTLY."

Claude: Nullable add = metadata-only; CONCURRENTLY = non-blocking (notes it must run outside a transaction — verifies the tool's migration wrapper handles that, cites how); old code unaffected (column unreferenced by it), rollback trivial. Report: no findings, one note about the transaction wrapper. Done in four lines.

Anti-patterns

• ❌ Reviewing the migration against an empty dev database mentally — every finding is judged against size, traffic, and the deploy overlap, or labeled with the assumption.
• ❌ Lock claims without the engine — ADD COLUMN NOT NULL DEFAULT is free on modern Postgres and was a rewrite on old versions; flagging the free case is severity inflation that erodes trust in the real findings.
• ❌ Accepting a rename or drop because "the code was updated in the same PR" — the whole problem is the minutes-to-hours when old code and new schema coexist.
• ❌ Trusting an auto-generated down-migration as a rollback without reading whether it restores data or just shape.
• ❌ Letting a million-row backfill ride inside the DDL transaction because the migration tool put it there.
• ❌ Executing the migration, dotnet ef database update, or ANY SQL "to check" — this skill reads and reports, full stop.
• ❌ Performing the full checklist on a pre-production empty schema — one line of N/A beats a page of theater.
• ✅ Engine-named lock findings, both-direction deploy-order trace, data-preserving rollback verdicts, assumptions stated, blockers with one-sentence failure scenarios.

Notes

• Tool wrappers matter: some runners wrap every migration in a transaction (breaking CREATE INDEX CONCURRENTLY), some apply timeouts, some (SQL Server) do transactional DDL. Find the runner's config in the repo before asserting behavior — a claim about the wrapper you haven't opened is a hypothesis. This skill's never-execute stance means every lock and rollback claim is at best inferred from file reads — tag them so, and when blockers are found, recommend the real gate (a run against a schema copy, plans and row counts inspected) as the author's job before merge.
• Recommend lock_timeout/statement_timeout guards (Postgres) on DDL touching hot tables where the repo's runner supports it — a migration that fails fast beats one that queues behind a long transaction and blocks everything behind it.
• Apply think-like-fable: the risk lives in the destructive ops and the deploy overlap, so they get the effort; every lock/rollback claim is re-derived from the engine + the actual file; the report leads with the one migration that must not run as-is.