DB Migration Generator

---
name: db-migration-gen
description: |
  Trigger when the user asks to write a database migration, change a schema,
  add a column, or plan a schema change. Phrases: "add column", "drop table",
  "migration", "alter schema", "add index", "rename field".
allowed-tools:
  - Read
  - Write
  - Edit
  - Grep
---

# DB Migration Generator

Produce safe, reversible schema migrations for Postgres via Drizzle, Prisma,
or raw SQL. Emphasis on zero-downtime patterns for production tables.

## Prerequisites

- Migrations directory identified (`drizzle/`, `prisma/migrations/`, `migrations/`)
- Current schema file available
- Target DB is Postgres (patterns differ for MySQL/MSSQL)

## Steps

1. **Understand the change.** Ask the user:
   - What is changing and why
   - Is this a large production table or a small one
   - Is brief write lock acceptable or must it be zero-downtime

2. **Generate the migration.** For small tables under 1M rows a single
   migration is fine. For large tables, split into multiple safe steps.

3. **Zero-downtime pattern for adding a NOT NULL column on a large table:**

   ```sql
   -- Step 1: add nullable column with default
   ALTER TABLE users ADD COLUMN signup_source text;

   -- Step 2: backfill in batches (separate migration or job)
   UPDATE users SET signup_source = 'unknown' WHERE signup_source IS NULL;

   -- Step 3: add NOT NULL constraint
   ALTER TABLE users ALTER COLUMN signup_source SET NOT NULL;
   ALTER TABLE users ALTER COLUMN signup_source SET DEFAULT 'unknown';
   ```

4. **Index creation on large tables:** Always `CREATE INDEX CONCURRENTLY`.
   Never plain `CREATE INDEX` on a production table over 1M rows. It will
   hold a write lock for minutes.

5. **Always write the down migration.** If it cannot be undone, document why.

6. **Run the migration locally** against a clone of production data if
   possible. Time it. If it takes more than ~30s, it's too slow for
   production without a zero-downtime split.

## Operations to always split into multiple migrations

- Adding NOT NULL with no default to a populated table
- Removing a column that code still references (deploy code first, then drop)
- Renaming a column (add new, backfill, update code, drop old)
- Changing a column type when data could overflow the new type

## Output

- Migration file(s) with up and down SQL or schema diff
- Estimated runtime if large table
- Comment at top explaining the strategy if multi-step