---

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:

ALTER TABLE users ADD COLUMN bio text; -- nullable, no lock

Adding NOT NULL column:

-- 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