---
name: db-migrate
description: Guide safe database migrations with rollback plans. Use when adding columns, changing types, creating indexes, or any schema change.
---

# Database Migration Workflow

## Before writing the migration

1. **Check current schema** — understand what exists
2. **Plan the change** — what SQL will run
3. **Identify risks** — will this lock tables? How long on production data?
4. **Write rollback** — every migration needs a down migration

## Migration checklist

- [ ] Migration is reversible (has down/rollback)
- [ ] New columns have defaults or are nullable
- [ ] No destructive changes without data migration
- [ ] Index creation uses CONCURRENTLY (Postgres)
- [ ] Large table changes use batched updates
- [ ] Foreign keys have indexes on the referencing column

## Safe patterns

**Adding a column:**
```sql
ALTER TABLE users ADD COLUMN bio text; -- nullable, no lock
```

**Adding NOT NULL column:**
```sql
-- Step 1: Add nullable
ALTER TABLE users ADD COLUMN role text;
-- Step 2: Backfill
UPDATE users SET role = 'member' WHERE role IS NULL;
-- Step 3: Add constraint
ALTER TABLE users ALTER COLUMN role SET NOT NULL;
```

**Never do:**
- DROP COLUMN without confirming no code references it
- ALTER TYPE on large tables without planning downtime
- Add indexes without CONCURRENTLY on production