---
name: migration-review
description: Reviews database schema migrations for production safety, including table locks and rewrites, transaction rules for concurrent index builds, deploy-breaking renames and drops, unbatched backfills, and missing or lossy rollbacks. Use when the user adds or changes a migration, asks if a migration is safe to deploy, or asks for a migration review.
---

# Migration review

Scope: `$ARGUMENTS`. If empty, review migration files added or changed on this branch: `git diff --name-only $(git merge-base HEAD origin/main)` filtered to migration folders (`db/migrate`, `migrations`, `database/migrations`, `prisma/migrations`, `alembic/versions`, `*/migrations/*.py`). Fall back to `main` if `origin/main` does not exist.

## 1. Context

- Identify the engine and version from config, docker-compose, CI services, or `CLAUDE.md`. If unknown, ask, and state assumptions in the report.
- For framework migrations, determine the SQL they produce. Read the framework DSL carefully; for Prisma read `migration.sql`; for Django and Alembic read the operations.
- Ask for approximate row counts of affected tables if the verdict depends on size.

## 2. Checks (Postgres)

| Operation | Risk | Safer approach |
|---|---|---|
| `CREATE INDEX` on existing table | Blocks writes for the build | `CREATE INDEX CONCURRENTLY`, outside a transaction |
| `ADD COLUMN ... DEFAULT <volatile expr>` (e.g. `now()`, `gen_random_uuid()`) | Rewrites table | Add nullable column, backfill in batches, then set default |
| `ADD COLUMN ... DEFAULT <constant>` | No rewrite on Postgres 11+ | OK on 11+; flag on older versions |
| `ALTER COLUMN ... SET NOT NULL` | Full scan under exclusive lock | Add `CHECK (col IS NOT NULL) NOT VALID`, `VALIDATE CONSTRAINT`, then `SET NOT NULL` (Postgres 12+ uses the validated check), then drop the check |
| `ADD FOREIGN KEY` / `ADD CHECK` | Validates whole table under lock | Add with `NOT VALID`, then `VALIDATE CONSTRAINT` in a separate step |
| `ALTER COLUMN ... TYPE` | Usually rewrites table and indexes | New column, dual write, backfill, switch, drop |
| `RENAME COLUMN` / `RENAME TABLE` | Breaks running app code | Expand and contract across releases |
| `DROP COLUMN` / `DROP TABLE` | Breaks running code; data loss | Stop reading/writing in code first, drop in a later release |
| Large `UPDATE`/`DELETE` in migration | Long transaction, lock contention, bloat | Batch by primary key range in a separate job or script |

Also recommend setting `lock_timeout` for DDL on busy tables so a blocked migration fails fast instead of queueing every other query behind it.

## 3. Checks (MySQL / MariaDB)

- Prefer online DDL; state whether the change can use `ALGORITHM=INSTANT` or `INPLACE` for the given version, and say when you are not sure.
- Flag column type changes, primary key changes and charset conversions on large tables as needing an online schema change tool or a maintenance window.
- Renames and drops follow the same expand and contract rule.

## 4. Framework rules

- Rails: concurrent index needs `disable_ddl_transaction!` and `algorithm: :concurrently`.
- Django: concurrent operations need `atomic = False` on the migration (`AddIndexConcurrently` from `django.contrib.postgres.operations`).
- Autogenerated migrations (Prisma, Alembic `--autogenerate`, Django `makemigrations`): check that renames were not generated as drop plus add.
- Every migration has a working `down`/reverse, or explicitly documents that it is irreversible and why.
- Schema and data changes are in separate migrations.

## 5. Report

For each migration file:

```
### <file>
Verdict: Safe / Safe with changes / Unsafe as written
- <statement or line>: <risk at scale> -> <safer sequence>
Rollback: <reversible / lossy / missing>
Deploy order: <e.g. "deploy code that stops reading `legacy_status` before this migration">
Assumptions: <engine, version, table size>
```

Do not run migrations. Do not connect to any database.