Skill: schema-migration-governor

Purpose

Safely and autonomously manage the entire lifecycle of database schema migrations. This skill prevents data loss and ensures that all migrations are reversible.

When to Use

• •When a user story requires changes to the database schema (adding/removing columns, tables, indexes).
• •Before deploying any migration to a shared environment.

How It Works

This skill delegates migration planning to a MigrationGovernor Sub-Agent.

Step 1: Detect Schema Changes

When the agent modifies a model or entity file, it should trigger the migration governor:

# Detect changes to model files
git diff --name-only HEAD | grep -E 'models/|entities/|schema/'

Step 2: Invoke the Sub-Agent for Migration Planning

claude --print "You are a MigrationGovernor sub-agent. Your task is to plan a safe database migration.

**Changed Model Files:**
$(git diff HEAD -- src/models/)

**Current Schema:**
$(cat .ralph/schema_snapshot.sql)

**Your Task:**
1. Analyze the changes and determine the required migration steps.
2. Classify each step as:
   - SAFE: Adding a column, adding a table, adding an index.
   - DESTRUCTIVE: Dropping a column, dropping a table, renaming a column.
3. Generate the 'up' migration SQL.
4. Generate the 'down' (rollback) migration SQL.

**Rules:**
- If ANY step is DESTRUCTIVE, output 'REQUIRES_CONFIRMATION' and list the destructive operations.
- All migrations MUST be reversible.
"

Step 3: Handle Destructive Operations

If the sub-agent outputs REQUIRES_CONFIRMATION:

1. •

   Check the prd.json for explicit confirmation:

   json

   {
     "id": "US-005",
     "title": "Remove legacy user fields",
     "allowDestructiveMigration": true,
     "destructiveOperations": ["DROP COLUMN users.legacy_id"]
   }
   

2. •

   If confirmation is present, proceed with the migration.

3. •

   If confirmation is not present, mark the story as blocked-migration:

   json

   {
     "id": "US-005",
     "passes": "blocked-migration",
     "notes": "Destructive migration requires explicit confirmation. Add 'allowDestructiveMigration: true' to the user story."
   }
   

Step 4: Test Migration on Shadow Database

Before applying the migration to the development database:

1. •Spin up a temporary "shadow" database (e.g., using Docker).
2. •Apply the current schema snapshot.
3. •Apply the new migration.
4. •Run a subset of tests to verify data integrity.
5. •If tests pass, apply the migration to the development database.
6. •If tests fail, enter the self-correction loop.

# Example using Docker
docker run -d --name shadow_db -e POSTGRES_PASSWORD=test postgres:15
# ... apply migration and run tests ...
docker rm -f shadow_db

Step 5: Update Schema Snapshot

After a successful migration, update the schema snapshot:

pg_dump --schema-only mydb > .ralph/schema_snapshot.sql
git add .ralph/schema_snapshot.sql
git commit -m "chore: update schema snapshot after migration"

Configuration

Add the following to your CLAUDE.md:

## Schema Migration Governor

- **ORM**: Prisma / TypeORM / SQLAlchemy / Drizzle
- **Migration Command**: `npm run db:migrate`
- **Schema Snapshot**: `.ralph/schema_snapshot.sql`
- **Shadow Database**: Enabled (Docker)
- **Require Confirmation for Destructive**: true