Schema Migration Skill

Create safe, zero-downtime schema migrations with rollback procedures.

Trigger Conditions

Migration file created or modified
ORM model changes detected
User invokes with "create migration" or "schema change"

Input Contract

Required: Current schema state
Required: Desired schema change
Optional: Data volume estimate, downtime tolerance

Output Contract

Migration SQL (up and down)
Backward-compatibility assessment
Lock time estimate for large tables
Data backfill strategy (if applicable)

Tool Permissions

Read: Schema files, existing migrations, ORM models
Write: Migration files
Execute: Schema comparison tools, EXPLAIN ANALYZE

Execution Steps

Analyze desired change against current schema
Determine if change is backward-compatible with N-1 app version
Apply expand-and-contract pattern if needed
Write UP migration with safety guards (IF NOT EXISTS, CONCURRENTLY)
Write DOWN migration (rollback)
Estimate lock time for table size
Design backfill strategy for data changes
Test both UP and DOWN against staging data

Success Criteria

Migration is backward-compatible with current app version
Both UP and DOWN migrations tested
Lock time estimated and acceptable
Migration is idempotent (safe to re-run)

Escalation Rules

Escalate if lock time estimate >30 seconds
Escalate if migration requires data backfill on >10M rows
Escalate if migration affects multiple services

Example Invocations

Input: "Add a non-null 'organization_id' column to the users table (5M rows)"

Output: 3-phase migration: Phase 1 (ADD COLUMN organization_id nullable, backfill from org_memberships, ~2min), Phase 2 (SET NOT NULL after backfill verified), Phase 3 (ADD INDEX CONCURRENTLY on organization_id, ~45s). Rollback: DROP COLUMN in each phase. Total estimated time: 4 minutes with zero downtime.