---
name: migrate-plan
description: Audit a Postgres schema change BEFORE writing the migration. Identifies lock risks (NOT NULL on big tables, type changes, blocking index builds), proposes safe staged patterns, drafts the migration, and lists pre-flight checks. Pairs with /ship-check (which audits AFTER write). Built for Prisma (EAA) and Drizzle (Fathom Fury) on Railway Postgres.
trigger: /migrate-plan
---

# /migrate-plan

Migrations on a live Postgres database carry asymmetric risk: a careful one ships in 30 seconds, a careless one locks a table for 10 minutes during peak traffic and you're explaining downtime to users. /migrate-plan audits the change BEFORE you write it. /ship-check audits AFTER. Both matter; this is the upstream half.

The skill works for Prisma (EAA) and Drizzle (Fathom Fury). Both produce SQL under the hood; the risks are at the SQL layer, not the ORM layer.

## Usage

`/migrate-plan <description of the schema change>`

Examples:
- `/migrate-plan Add tier column to InstagramAccount, default 'standard'`
- `/migrate-plan Drop legacy lastScraped field on Post`
- `/migrate-plan Add unique index on QueuedPost.externalId`

## What it's for

You ship migrations on EAA and Fathom Fury regularly. Most are safe. The dangerous ones share a small set of patterns that lock tables, lose data, or create constraints that fail mid-migration. /migrate-plan catches those before you write the migration, when changing course is cheap.

It does NOT replace running the migration in a staging/dev DB. Staging tests the actual SQL; this skill plans the safe shape.

## What You Must Do When Invoked

### Step 1 — Capture the target table state

Before planning, know the current state. Read the schema, or ask:
- **Row count** — small (<10k), medium (10k–1M), large (1M+). Affects lock tolerance.
- **Indexes** — what's already indexed
- **Constraints** — FKs in/out, NOT NULLs, uniqueness
- **Hot path?** — Is this table written to constantly (queue, sessions) or mostly cold (config, lookup)?

For EAA, sensitive tables: `InstagramAccount`, `Post`, `QueuedPost`, `Blog`. For Fathom Fury, anything CIGARLINK-related (compliance data, no data loss tolerance).

If the table is unfamiliar, ask Jake one question to characterize size and write-volume.

### Step 2 — Identify the change type

Categorize the change. Risk profile differs sharply:

| Change type | Risk | Notes |
|---|---|---|
| Add nullable column | 🟢 Safe | No table rewrite |
| Add NOT NULL column WITH default | 🟡 | Postgres 11+ is fast metadata-only; older versions rewrite |
| Add NOT NULL column WITHOUT default | 🔴 | Must rewrite every row to validate; locks table |
| Drop column | 🟡 | Drops fast but data is gone |
| Rename column | 🟡 | Atomic but breaks running app reading old name |
| Change column type | 🔴 | Often rewrites the table; depends on type |
| Add index (CONCURRENTLY) | 🟢 | Non-blocking |
| Add index (regular) | 🔴 | Locks writes during build |
| Drop index | 🟢 | Fast |
| Add unique constraint | 🔴 | Scans table; fails if duplicates exist |
| Add FK | 🟡 | Validates references; fast on small, slow on large |
| Backfill data | 🟡 | Use batched updates, never one big UPDATE |

### Step 3 — Audit risks

For the specific change, name:
- **Lock duration** — how long writes are blocked (rough estimate)
- **Read impact** — does the migration block reads?
- **Failure modes** — what could fail mid-migration? (constraint violations on existing data, OOM on rewrite, replication lag)
- **Rollback complexity** — if it fails halfway, can you undo cleanly?

### Step 4 — Propose the safe pattern

If the naive migration is risky, propose a multi-step alternative:

**Example: NOT NULL on a 1M-row table**
- Naive: `ALTER TABLE x ADD COLUMN tier text NOT NULL` ← 🔴 locks for minutes
- Safe staged:
  1. Add nullable: `ALTER TABLE x ADD COLUMN tier text`
  2. Backfill in batches: `UPDATE x SET tier = 'standard' WHERE tier IS NULL` (loop with LIMIT 10000)
  3. Add default for new rows: `ALTER TABLE x ALTER COLUMN tier SET DEFAULT 'standard'`
  4. Set NOT NULL (separate deploy after backfill verified): `ALTER TABLE x ALTER COLUMN tier SET NOT NULL`

**Example: unique index on existing data**
- Naive: `CREATE UNIQUE INDEX ON x (externalId)` ← 🔴 fails if duplicates exist
- Safe:
  1. Find duplicates: `SELECT externalId, COUNT(*) FROM x GROUP BY externalId HAVING COUNT(*) > 1`
  2. Resolve duplicates (decide which row to keep)
  3. Build concurrently: `CREATE UNIQUE INDEX CONCURRENTLY ON x (externalId)`

### Step 5 — Output the plan

```markdown
# /migrate-plan — <description>

**Table:** <name>
**Estimated rows:** <small | medium | large | unknown>
**Hot path:** <yes | no>

## Change type
<one of the categories above, with risk color>

## Risks
- Lock duration: <estimate>
- Read impact: <yes/no, why>
- Failure modes: <what could go wrong mid-migration>
- Rollback: <simple / complex / irreversible>

## Recommended pattern
<Naive (one step) or staged (multi-step). If staged, list every step with the SQL.>

## Migration draft

\`\`\`prisma
// schema.prisma change (or drizzle equivalent)
\`\`\`

\`\`\`sql
-- Generated SQL preview
\`\`\`

## Deploy notes
- When to deploy: <low-traffic window, immediately, etc.>
- App code coordination: <ship before, ship after, or ship together>
- Rollback plan: <one-line>

## Pre-flight checklist
- [ ] Run on staging/dev DB first
- [ ] Confirm row count assumption (vs prod parity)
- [ ] Confirm no in-flight writes that would conflict
- [ ] Have rollback SQL ready
- [ ] Run /ship-check before pushing the deploy
```

### Step 6 — Stop

Don't write the migration to disk yet. Output the plan. Jake confirms, then writes. After it's written, run /ship-check before deploy.

## Calibration for Jake's stack

- **EAA: Prisma + Postgres on Railway.** Prisma writes SQL under the hood. Always read the generated SQL before applying — sometimes it differs from intent (especially renames, which Prisma may interpret as drop+add).
- **Fathom Fury: Drizzle + Postgres on Railway.** Drizzle is more SQL-transparent — easier to audit but you're writing the migration directly.
- **Railway specifics:** Railway Postgres has connection limits and limited maintenance windows. Long-locking migrations can cascade into connection exhaustion. Bias toward staged migrations even when naive would technically work.
- **Sensitive tables:** InstagramAccount, Post, QueuedPost on EAA carry production data Jake manually curated. CIGARLINK on Fathom Fury is compliance data. Default to staged patterns; never one-shot a NOT NULL or type change on these.
- **Prisma rename gotcha:** Renaming a column in `schema.prisma` and running `prisma migrate dev` will generate `DROP COLUMN; ADD COLUMN` — not a rename. You lose the data. Use `@map` to keep the SQL name stable, or write the rename SQL by hand.

## What to avoid

- **No "should be fine" without a row count.** Risk is row-count dependent. Get the number or characterize it.
- **No combining unrelated changes in one migration.** Add column + drop column + rename = three rollback risks. Split into separate migrations.
- **No skipping the SQL preview** with Prisma. Generated SQL sometimes differs from intent.
- **No `SET NOT NULL` on large tables without staged backfill.** This is the #1 production-locker.
- **No writing the migration inside this skill.** Output the plan. Author after confirmation.
- **No assuming a rollback works** — confirm the down-path SQL is safe, not just present.

## Example output (abbreviated)

```markdown
# /migrate-plan — Add tier column to InstagramAccount, default 'standard'

**Table:** InstagramAccount
**Estimated rows:** small (~50, admin-curated)
**Hot path:** medium — read often, written rarely

## Change type
Add NOT NULL column WITH default → 🟡 normally, but small-table size makes it 🟢 here.

## Risks
- Lock duration: <1 second on 50 rows. Negligible.
- Read impact: brief lock during DDL.
- Failure modes: none expected. Default value is constant.
- Rollback: simple `DROP COLUMN`.

## Recommended pattern
Naive is fine. One-step migration:

\`\`\`prisma
model InstagramAccount {
  // existing fields
  tier String @default("standard")
}
\`\`\`

## Deploy notes
- Run during normal deploy, no special timing.
- App code coordination: code reading `tier` ships in same deploy or after.
- Rollback: `ALTER TABLE "InstagramAccount" DROP COLUMN "tier"`.

## Pre-flight checklist
- [ ] `npx prisma migrate dev` locally first
- [ ] Confirm 50-row count (staging vs prod parity)
- [ ] App code uses `tier` with fallback while deploy is in flight
- [ ] /ship-check before pushing
```

That's the shape. Audit before write, stage when needed, hand off to /ship-check.