Database Migration Planner

---
name: database-migration-planner
description: >
  Plan safe database migrations with backwards compatibility, rollback
  strategies, and zero-downtime deployment. Break risky changes into
  incremental steps. Validate data integrity at every stage.
---

# Database Migration Planner

You plan database migrations that are safe, reversible, and zero-downtime.

## The Golden Rules

1. **Every migration must be backwards-compatible** — The current application version must work before AND after the migration runs
2. **Every migration must be reversible** — Include a rollback script or strategy
3. **Never lock tables for long** — Avoid exclusive locks on large tables
4. **Validate data integrity** — Check data before and after migration
5. **Test on a copy of production data** — Never run untested migrations in production

## The Expand-Contract Pattern

For any change that would break existing code, use expand-and-contract:

### Phase 1: Expand (Add the new thing)

- Add new column, table, or index alongside the old one
- Deploy application code that writes to BOTH old and new
- Old code continues to read from the old location

### Phase 2: Migrate Data

- Backfill the new column/table with existing data
- Run in batches to avoid locking: process 1000-5000 rows at a time
- Verify data completeness: `SELECT COUNT(*) WHERE new_column IS NULL`

### Phase 3: Switch

- Deploy application code that reads from the new location
- Continue writing to both locations (safety net)
- Monitor for issues

### Phase 4: Contract (Remove the old thing)

- Stop writing to the old column/table
- Drop the old column/table in a separate migration
- This step is its own deployment — never combine with Phase 3

## Common Migration Patterns

### Adding a Column

Safe — just use a default or allow NULL:

```sql
-- SAFE: Nullable column, no table lock on modern databases
ALTER TABLE users ADD COLUMN avatar_url TEXT;

-- SAFE: With default (Postgres 11+ doesn't rewrite the table)
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;
```

### Renaming a Column

NEVER rename directly. Use expand-and-contract:

```
Step 1: Add new column -> ALTER TABLE users ADD COLUMN display_name TEXT;
Step 2: Backfill       -> UPDATE users SET display_name = username WHERE display_name IS NULL;
Step 3: App writes both, reads new
Step 4: Drop old       -> ALTER TABLE users DROP COLUMN username;
```

### Changing a Column Type

NEVER alter type directly on large tables. Expand-and-contract:

```
Step 1: Add new column -> ALTER TABLE orders ADD COLUMN total_v2 BIGINT;
Step 2: Backfill       -> UPDATE orders SET total_v2 = total::BIGINT;  (in batches)
Step 3: App switches to new column
Step 4: Drop old column
```

### Removing a Column

NEVER drop a column while any running instance reads it:

```
Step 1: Stop reading from column in application code (deploy)
Step 2: Stop writing to column in application code (deploy)
Step 3: Drop column in migration (deploy)
```

### Adding an Index

Use CONCURRENTLY to avoid locking:

```sql
-- SAFE: Non-blocking on Postgres
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- DANGEROUS: Locks the entire table until complete
CREATE INDEX idx_users_email ON users (email);
```

## Batched Data Backfill

For large tables, never run unbounded updates:

```sql
-- Process in batches of 5000
DO $$
DECLARE
  batch_size INT := 5000;
  affected INT;
BEGIN
  LOOP
    UPDATE users
    SET display_name = username
    WHERE display_name IS NULL
      AND id IN (
        SELECT id FROM users
        WHERE display_name IS NULL
        LIMIT batch_size
        FOR UPDATE SKIP LOCKED
      );
    GET DIAGNOSTICS affected = ROW_COUNT;
    EXIT WHEN affected = 0;
    COMMIT;
    PERFORM pg_sleep(0.1);  -- Brief pause to reduce load
  END LOOP;
END $$;
```

## Migration File Template

```typescript
export async function up(db: Database): Promise<void> {
  // Pre-migration validation
  const count = await db.query("SELECT COUNT(*) FROM users")
  console.log(`Migrating ${count} rows`)

  // Migration
  await db.query("ALTER TABLE users ADD COLUMN avatar_url TEXT")

  // Post-migration validation
  const schema = await db.query("SELECT column_name FROM information_schema.columns WHERE table_name = 'users'")
  assert(schema.includes("avatar_url"), "Column was not added")
}

export async function down(db: Database): Promise<void> {
  await db.query("ALTER TABLE users DROP COLUMN IF EXISTS avatar_url")
}
```

## Rollback Checklist

Before running any migration, document:

1. **What the rollback SQL/script is**
2. **Whether rollback causes data loss** (dropping a column loses data)
3. **How long rollback takes** on production-sized data
4. **Who is authorised to trigger rollback**
5. **What monitoring alerts to watch** during and after migration

## Anti-Patterns to Reject

1. **Direct column rename** — Breaks running instances
2. **DROP COLUMN without removing reads first** — Causes runtime errors
3. **Unbounded UPDATE on millions of rows** — Locks table, may timeout
4. **Multiple risky changes in one migration** — Impossible to partially rollback
5. **No rollback script** — You will need it at the worst possible time