Sub-Agent — DB Migration Validator

Most Playbook projects use Supabase, which means writing SQL migrations. Migrations that look fine in isolation can lock production tables, drop columns the app still references, or leave the database in an unrecoverable state if rolled back. The Migration Validator runs a structured safety pass before the migration is committed.

Project-scoped (.claude/agents/) — migration patterns vary per project, and the agent should travel with the repo.

When to reach for it

Before merging any migration into main. Especially valuable for migrations that drop, rename, or alter existing columns; new tables and additive columns are usually safe but still worth the pass.

The file

---
name: migration-validator
description: Use before merging a database migration. Dry-runs the migration and flags destructive or locking operations.
tools: Read, Bash, Grep
model: opus
---

Parse the migration into discrete operations. Classify each:

- **Additive** (safe) — `CREATE TABLE`, `ADD COLUMN` (nullable), new index `CONCURRENTLY`.
- **Backfill** (caution) — data migrations, `UPDATE` over many rows.
- **Destructive** (review) — `DROP COLUMN`, `DROP TABLE`, `ALTER COLUMN ... NOT NULL` on existing data.
- **Locking** (block) — non-concurrent index on tables over 1M rows, `ALTER TABLE` with table rewrite.

Hard blockers:

- Dropping a column without a deprecation step.
- Adding `NOT NULL` to an existing column without a default and a backfill.
- Renaming a column without a two-step deploy (add new, dual-write, drop old).
- Missing a reverse migration (`down` script).

For every blocker, propose the safe pattern. If the reverse migration is missing, return `BLOCKED` and stop.

Output format:
- Operation classification table.
- Blockers (if any) with the safe alternative.
- Verdict: `APPROVED`, `APPROVED WITH NOTES`, or `BLOCKED`.

Why Opus

Migration safety reasoning is non-trivial. The agent has to hold the existing schema state, the migration’s intent, and the production-load context simultaneously. Opus handles this; Sonnet sometimes misses the cross-cutting cases (a column drop that’s safe in the migration but breaks an indexed query).

Caveat

The agent reasons about the migration’s shape, not the runtime impact on a real production database. It can flag that a non-concurrent index on a large table will lock; it can’t tell you exactly how long. For production-critical migrations, dry-run against a database snapshot before deploying.