/migration-planner

name: memstack-development-migration-planner
description: "Use this skill when the user says 'migration', 'schema change', 'database migration', 'alter table', 'add column', 'change type', 'rollback plan', or needs safe database schema evolution with zero-downtime strategies. Do NOT use for initial database design (use database-architect) or code refactoring."
version: 1.0.0
license: "Proprietary, MemStack™ Pro by CW Affiliate Investments LLC. See LICENSE.txt"

Migration Planner: Planning database migration...

*Plans safe database schema migrations with zero-downtime strategies, rollback procedures, data validation checkpoints, and version tracking for PostgreSQL, MySQL, and SQLite.*

Activation

When this skill activates, output:

`Migration Planner: Planning database migration...`

Then execute the protocol below.

Context Guard

| Context | Status | |---------|--------| | User says "migration", "schema change", "database migration" | ACTIVE | | User says "alter table", "add column", "change type", "rollback plan" | ACTIVE | | User wants to evolve an existing database schema safely | ACTIVE | | User wants to design a new database from scratch | DORMANT: use Database Architect | | User wants to refactor application code | DORMANT: use Refactor Planner |

Common Mistakes

| Mistake | Why It's Wrong | |---------|---------------| | "Just ALTER TABLE in production" | Unplanned schema changes cause outages. Lock durations, data backfills, and app compatibility all need planning. | | "No rollback plan" | Migrations fail. Without a tested rollback, a failed deploy means manual emergency recovery at 3 AM. | | "Rename columns directly" | Renaming breaks all queries referencing the old name instantly. Use add → migrate → drop (3-phase). | | "Big bang data migration" | Migrating millions of rows in one transaction locks the table for minutes/hours. Batch in chunks. | | "Skip validation" | Assuming data migrated correctly without checking is gambling. Validate row counts, checksums, and spot checks. |

Protocol

Step 1: Gather Migration Requirements

If the user hasn't provided details, ask:

> 1. **Database**: what engine? (PostgreSQL, MySQL, SQLite, MongoDB) > 2. **Current schema**: what does the table/schema look like now? > 3. **Target schema**: what should it look like after migration? > 4. **Data volume**: how many rows in affected tables? > 5. **Downtime tolerance**: zero-downtime required, or maintenance window OK? > 6. **Migration tool**: what do you use? (Prisma, Knex, Flyway, Alembic, raw SQL, other)

Step 2: Classify the Migration

**Migration type classification:**

| Change Type | Risk | Lock Behavior (PostgreSQL) | Strategy | |------------|------|---------------------------|----------| | **Add column (nullable)** | Low | Brief `ACCESS EXCLUSIVE` lock | Direct ALTER | | **Add column (with default)** | Low (PG 11+) | Brief lock (PG 11+ doesn't rewrite) | Direct ALTER | | **Add column (NOT NULL, no default)** | High | Fails on existing rows | Add nullable → backfill → add constraint | | **Drop column** | Medium | Brief lock, but app must not reference it | Remove app refs first → then DROP | | **Rename column** | High | Breaks all queries instantly | 3-phase: add new → migrate → drop old | | **Change column type** | High | Full table rewrite + lock | New column → backfill → swap → drop | | **Add index** | Medium | Blocks writes (without CONCURRENTLY) | `CREATE INDEX CONCURRENTLY` | | **Add foreign key** | Medium | Validates all rows (lock) | `NOT VALID` → `VALIDATE CONSTRAINT` separately | | **Drop table** | High | Irreversible data loss | Backup → verify no refs → drop | | **Add table** | Low | No lock on existing tables | Direct CREATE |

**Migration classification output:**

## Migration Classification

| # | Change | Type | Risk | Lock Duration | Strategy |
|---|--------|------|------|--------------|----------|
| 1 | [Change description] | [Type] | Low/Med/High | [Est. duration] | [Strategy] |
| 2 | [Change description] | [Type] | Low/Med/High | [Est. duration] | [Strategy] |

Step 3: Design the Migration

**Safe migration patterns:**

**Pattern A: Add nullable column (simple, low risk)**

-- Migration UP
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Migration DOWN
ALTER TABLE users DROP COLUMN phone;

**Pattern B: Add NOT NULL column (3-step)**

-- Step 1: Add nullable column
ALTER TABLE users ADD COLUMN status VARCHAR(20);

-- Step 2: Backfill existing rows (in batches)
UPDATE users SET status = 'active' WHERE status IS NULL AND id BETWEEN 1 AND 10000;
UPDATE users SET status = 'active' WHERE status IS NULL AND id BETWEEN 10001 AND 20000;
-- ... continue in batches

-- Step 3: Add NOT NULL constraint
ALTER TABLE users ALTER COLUMN status SET NOT NULL;
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

**Pattern C: Rename column (3-phase, zero-downtime)**

-- Phase 1: Add new column + trigger
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(10,2);
-- Create trigger to sync old → new
CREATE OR REPLACE FUNCTION sync_amount() RETURNS TRIGGER AS $$
BEGIN
  NEW.total_amount := NEW.amount;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER sync_amount_trigger BEFORE INSERT OR UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION sync_amount();

-- Phase 2: Backfill existing rows
UPDATE orders SET total_amount = amount WHERE total_amount IS NULL AND id BETWEEN 1 AND 10000;

-- Phase 3: (After app code updated to use total_amount)
DROP TRIGGER sync_amount_trigger ON orders;
DROP FUNCTION sync_amount();
ALTER TABLE orders DROP COLUMN amount;

**Pattern D: Change column type (safe swap)**

-- Step 1: Add new column with target type
ALTER TABLE products ADD COLUMN price_v2 DECIMAL(12,4);

-- Step 2: Backfill with type conversion
UPDATE products SET price_v2 = price::DECIMAL(12,4) WHERE id BETWEEN 1 AND 10000;

-- Step 3: Swap columns (after app code updated)
ALTER TABLE products DROP COL