Database Migration Helper

Authors safe, backward-compatible database schema migrations. Enforces the "Expand and Contract" pattern, non-blocking column additions, and safe index creation.

1. Purpose

Prevent database outages and lockups during schema updates.

2. When to Use

Use this skill when:

• Adding, renaming, or dropping database columns.
• Adding indexes in production PostgreSQL/MySQL without locking writes.
• Writing migration files (Prisma, TypeORM, Alembic, Django, Flyway, Goose).

3. When Not to Use

• Do not use for CSV file validation; use csv-validator.

4. Required Inputs

• table_name: Target database table.
• schema_change: Proposed modification (e.g. "Add email_verified boolean column").

5. Missing-Input Behavior

1. If migration framework is unknown: inspect repository for prisma/, alembic/, migrations/.
2. Never author a migration that drops a column in the same release old code is running.

6. Procedure

Follow these numbered actions:

1. Action 1: Identify Migration Pattern
   • Adding column: Must be nullable or have a default value.
   • Renaming column: Add new column, dual-write, backfill, drop old in subsequent release.
   • Adding index: Use CREATE INDEX CONCURRENTLY in Postgres.
2. Action 2: Author Up Migration
   • Write non-blocking SQL/ORM migration script.
3. Action 3: Author Down (Rollback) Migration
   • Write reversible rollback statement.
4. Action 4: Emit Migration Plan
   • Format per Section 8.

7. Decision Rules

• In PostgreSQL: never run CREATE INDEX without CONCURRENTLY on high-traffic tables.
• Never set NOT NULL without a default on an existing populated table.

8. Output Format

Emit the report using this template:

### Database Migration Plan: [Table Name]

- **Framework**: [Prisma | Alembic | Raw SQL | Goose]
- **Migration Name**: `[e.g. 20260910_add_verified_to_users]`
- **Lock Risk**: [LOW (Zero-downtime) | HIGH (Table lock)]

#### Migration UP
```sql
[Safe SQL Migration]

Migration DOWN (Rollback)

[Rollback SQL]

## 9. Verification Checklist
- [ ] New column is nullable or has default value.
- [ ] Indexes created concurrently.
- [ ] Rollback statement verified.

## 10. Common Failures and Recovery
| Failure Symptom | Cause | Recovery Action |
| :--- | :--- | :--- |
| Table locked for 30 minutes | `CREATE INDEX` without `CONCURRENTLY` | Add `CONCURRENTLY` keyword and run outside transaction. |

## 11. Stop or Escalation Conditions
Stop and ask the user when:
- Migration requires table-rewriting locks on tables with millions of rows.

## 12. Examples

### Example 1: Successful Execution
**Input:**
```text
Table: users
Change: Add last_login_at timestamp
DB: PostgreSQL

Output:

### Database Migration Plan: users

- **Framework**: PostgreSQL SQL
- **Migration Name**: `20260910_add_last_login_at_to_users`
- **Lock Risk**: LOW (Zero-downtime)

#### Migration UP
```sql
ALTER TABLE users ADD COLUMN last_login_at TIMESTAMPTZ NULL;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_last_login_at ON users (last_login_at);

Migration DOWN (Rollback)

DROP INDEX CONCURRENTLY IF EXISTS idx_users_last_login_at;
ALTER TABLE users DROP COLUMN IF EXISTS last_login_at;

### Example 2: Ambiguous or Missing Input
**Input:**
```text
Change database.

Output:

### Database Migration Plan: Halted - Missing Details

- **Status**: Halted

Please provide the target table name and proposed column changes.

Example 3: Expected Final Output

Output:

### Database Migration Plan: orders

- **Framework**: PostgreSQL SQL
- **Migration Name**: `20260910_add_status_index`
- **Lock Risk**: LOW