Database Migration

---
name: database-migration
description: Safely run database schema migrations. Use when asked to update database schema, add columns, create tables, run alembic, or apply Django migrations.
license: MIT
compatibility: Requires python 3.10+ and one of alembic, django, or prisma
---

## Clarification Gates

Before executing any migration, you must have clear answers to all of the following. If any is missing or ambiguous, STOP and ask. Do not infer from filenames, branch names, or previous conversation context.

- **Environment:** local / staging / production?
- **Backup confirmed:** Has a database backup been taken in the last 24 hours?
- **Migration scope:** Is this a new column/table, or does it modify/drop existing data?

An agent that guesses the environment and gets it wrong has just migrated the wrong database. Ask first.

## Process

1. **Identify the migration tool.** Check the project for:
   - `alembic.ini` → Alembic (SQLAlchemy)
   - `manage.py` → Django migrations
   - `prisma/schema.prisma` → Prisma
   If none are found, ask the user which tool to use. Do not guess.

2. **Generate the migration.**
   - Alembic: `alembic revision --autogenerate -m "description"`
   - Django: `python manage.py makemigrations`
   - Prisma: `npx prisma migrate dev --name description`

3. **Review the generated migration file.** Read the entire file. Check for:
   - Columns being dropped (STOP and ask the user)
   - Table renames (STOP and ask the user)
   - Non-nullable columns added without defaults (this will fail on non-empty tables)

4. **Dry run.** If the tool supports it:
   - Alembic: `alembic upgrade head --sql` (generates SQL without executing)
   - Django: `python manage.py sqlmigrate <app> <migration_number>`

5. **Apply the migration** only after reviewing the dry run output.

6. **Verify.** Query the database schema directly to confirm the change took effect:
   ```sql
   -- PostgreSQL
   SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'target_table';
   -- SQLite
   PRAGMA table_info(target_table);
   ```

## Rationalizations

| Excuse | Rebuttal |
|--------|----------|
| "It's just an ADD COLUMN, I'll skip the review" | Schema locks can occur even on simple additions. Always review. |
| "The autogenerated migration looks correct" | Autogenerate guesses. It sometimes generates DROP + CREATE instead of ALTER. Read the file. |
| "I'll just run it — we can roll back" | Not all migrations are reversible. Dropping a column destroys data permanently. |

## Verification

- [ ] Migration file was read in full before applying
- [ ] No columns were dropped without explicit user approval
- [ ] Database schema was queried after migration to confirm the change
- [ ] "The command succeeded" is NOT accepted as sufficient evidence