You’re staring at a production database. Your application schema has evolved—columns need renaming, tables need splitting, data types need changing—and you need to generate a migration that works, that’s reversible, and that won’t lose customer data at 3 AM.
That’s where a migration skill comes in. We’re going to build a Claude Code Skill that generates safe, framework-agnostic database migrations, handles rollback patterns, validates before execution, and preserves data through schema changes. This is the kind of automation that turns a nerve-wracking manual process into a reliable, repeatable workflow.
Let’s think about what we’re actually trying to solve: migrations are fragile by nature. One wrong ALTER TABLE statement and your production database is corrupted. But migrations are also predictable. The patterns are known. The risks are documented. The rollback strategies are established. That makes them perfect for AI-assisted generation with strong validation gates. When you combine Claude’s ability to understand complex constraints with automated validation and human review gates, you get a system that can reliably generate migrations that would take humans hours to write safely.
Why Migration Automation Matters: The Cost of Manual Approaches
Before we dive into architecture, let’s be honest about what happens when you don’t have systematic migration generation. Most organizations handle database schema changes through a blend of ad-hoc scripts, institutional knowledge (which leaves when people do), and crossing fingers.
A developer writes SQL by hand. They test it in development. Everything works. Then production runs it on a 50-million-row table at 8 PM, the migration locks the entire database for 45 minutes, and suddenly you’re getting paged. The application hangs. Customers see errors. You’re frantically trying to roll back, but the down() function nobody actually tested doesn’t work because someone made assumptions about the old schema that changed six months ago.
This isn’t paranoia. This is every organization’s production incident history.
The real cost isn’t just the incident itself. It’s the aftermath: incident postmortems, customer trust erosion, the emergency freeze on deployments, and the institutional trauma that makes everyone afraid to touch the database. Teams start hoarding schema change requests, bundling them into massive quarterly releases rather than small, incremental changes. This increases blast radius. This increases risk.
A migration skill inverts this equation. Instead of hoping your manual SQL is correct, you’re encoding best practices into automation. Instead of testing once in development, you’re validating reversibility, data preservation, and syntax before human eyes ever see it. The skill becomes a guardrail that forces you to think through edge cases that humans routinely miss under time pressure.
Consider the human cost of manual migration creation. A senior engineer might spend 2-3 hours designing a complex migration, writing the SQL, testing it, writing the rollback, testing the rollback. They’re doing this from muscle memory and experience, context-switching between their mental model of the schema and the SQL syntax. They’re probably writing one migration, so they’re not building patterns or leveraging templates. They’re solving the problem from scratch every time.
Now multiply that by dozens of developers across an organization, each with slightly different approaches and levels of thoroughness. You get inconsistency, gaps, and eventually, incidents.
A migration skill solves all of this. It’s consistent. It’s thorough. It never gets tired. And critically, it forces a structured review process that catches problems before they become customer issues.
The Architecture: Safe Migration Generation
When we talk about a migration skill, we’re not just asking Claude to “write SQL.” We’re building a system that:
- Understands your schema—current state, target state, differences
- Generates framework-specific code—Prisma, Alembic, Knex, or raw SQL
- Includes rollback logic—every up() has a matching down()
- Preserves data—detects data-loss scenarios and flags them
- Validates before execution—runs syntax checks, dry-runs, constraint analysis
- Documents changes—creates readable changelogs explaining what changed and why
The skill acts as a bridge between human intent (“rename this column”) and executable, safe migrations that you can run with confidence. It’s not magic—it’s systematic engineering applied to a domain where humans have proven to be error-prone.
Core Migration Patterns: Up and Down
Every migration has two directions: up() and down(). The up() applies changes. The down() reverts them. Getting this right is non-negotiable. This bidirectional safety mechanism is what separates professional migrations from scripts that work one way and break spectacularly on rollback.
Here’s the SKILL.md definition:
---
name: "generate-safe-migration"
version: "1.0.0"
description: "Generate safe, reversible database migrations with rollback patterns and data preservation validation"
category: "database"
tags:
[
"migrations",
"schema",
"prisma",
"alembic",
"knex",
"rollback",
"data-preservation",
]
difficulty: "advanced"
inputs:
- name: "current_schema"
type: "json"
description: "Current database schema (tables, columns, constraints)"
required: true
- name: "target_schema"
type: "json"
description: "Desired database schema after migration"
required: true
- name: "framework"
type: "string"
description: "Migration framework: prisma, alembic, knex, or raw-sql"
required: true
- name: "data_strategy"
type: "string"
description: "How to handle data changes: preserve (default), transform, or fail-on-loss"
required: false
- name: "database_type"
type: "string"
description: "PostgreSQL, MySQL, SQLite, or SQL Server"
required: true
outputs:
- name: "migration_file"
type: "string"
description: "Generated migration with up() and down() patterns"
- name: "validation_report"
type: "json"
description: "Data loss analysis, constraint conflicts, rollback safety score"
- name: "changelog"
type: "string"
description: "Human-readable summary of schema changes"
quality_gates:
- "No data loss without explicit confirmation"
- "Both up() and down() must be reversible"
- "All foreign key constraints validated"
- "Dry-run syntax check passes"
- "Changelog documents every change"
---
# Generate Safe Migrations with Claude Code
You're managing a growing schema. Tables evolve. Columns get renamed. Data types change. The last thing you want is a migration that silently loses customer data, fails midway with no rollback path, doesn't document what actually changed, or works in development but breaks production.
This skill generates migrations that avoid all of those failure modes.
Step 1: Analyze Schema Differences
First, we understand the delta between current and target state. This analysis layer is critical because it identifies data-loss scenarios upfront, before we generate any SQL. The algorithm compares every aspect of your schema—tables, columns, constraints, indexes, triggers—and builds a comprehensive diff report.
Understanding what changed is the foundation of safe migration generation. If we know exactly what’s different, we can make intelligent decisions about how to migrate safely. For example, if we detect a column is being dropped, we can warn you upfront, offer to back it up to an archive table, or fail the migration if your policy is “never lose data.”
const analyzeSchemaDiff = (currentSchema, targetSchema) => {
const diff = {
tablesAdded: [],
tablesDropped: [],
columnsAdded: [],
columnsDropped: [],
columnsModified: [],
constraintsAdded: [],
constraintsDropped: [],
dataLossRisk: [],
};
// Compare tables
Object.entries(targetSchema.tables || {}).forEach(
([tableName, targetDef]) => {
if (!currentSchema.tables[tableName]) {
diff.tablesAdded.push(tableName);
}
},
);
// Compare columns and flag destructive changes
Object.entries(currentSchema.tables || {}).forEach(
([tableName, currentDef]) => {
const target = targetSchema.tables[tableName];
if (!target) {
diff.tablesDropped.push(tableName);
diff.dataLossRisk.push({
severity: "critical",
message: `Table '${tableName}' will be dropped, losing all data`,
});
return;
}
// Check for dropped columns
Object.keys(currentDef.columns || {}).forEach((colName) => {
if (!target.columns[colName]) {
diff.columnsDropped.push({ table: tableName, column: colName });
diff.dataLossRisk.push({
severity: "high",
message: `Column '${colName}' in table '${tableName}' will be dropped`,
});
}
});
// Check for modified columns
Object.entries(target.columns || {}).forEach(([colName, targetCol]) => {
const currentCol = currentDef.columns[colName];
if (
currentCol &&
JSON.stringify(currentCol) !== JSON.stringify(targetCol)
) {
diff.columnsModified.push({
table: tableName,
column: colName,
from: currentCol,
to: targetCol,
});
}
});
},
);
return diff;
};
This analysis is your first line of defense. If dropping a column is detected and data_strategy is “fail-on-loss”, we stop and report the risk immediately. If it’s “preserve”, we flag it for manual review. If it’s “transform”, we attempt to archive the data. That’s safety built in from the beginning—we never silently lose data.
Step 2: Generate Framework-Specific Migrations
Now we generate the actual migration code, tailored to the framework you’re using. Each framework has different strengths, different conventions, and different ways of thinking about migrations. Prisma is declarative—you describe the target and it figures out the SQL. Alembic is imperative—you explicitly write what changes. Knex is a query builder that you program. Raw SQL is… raw SQL. Our skill generates code that fits naturally into each system.
Prisma Format
Prisma migrations are the most declarative. You modify your schema.prisma file to describe your desired schema, and Prisma generates the SQL migration automatically. This approach is powerful because Prisma understands relationships, indexing, and constraints at a high level, not just column definitions.
const generatePrismaMigration = (diff, targetSchema, dbType) => {
const timestamp = new Date().toISOString().replace(/[-:]/g, "").slice(0, 14);
const filename = `${timestamp}_schema_update.sql`;
let upMigration = `-- Migration: Schema update\n`;
let downMigration = `-- Rollback: Revert schema update\n`;
// Generated schema.prisma would look like:
return {
filename,
schemaFile: generatePrismaSchema(targetSchema),
sqlUp: upMigration,
sqlDown: downMigration,
instructions: "Run: prisma migrate deploy",
};
};
Alembic Format (SQLAlchemy)
Alembic is Python’s standard for database migrations. It’s imperative, meaning you explicitly declare each change. This approach gives you fine-grained control and is excellent for complex schema transformations that require intermediate steps.
# Alembic migration for Python/SQLAlchemy apps
from alembic import op
revision = 'abc123def456'
down_revision = 'xyz789uvw012'
branch_labels = None
depends_on = None
def upgrade():
# Add new columns
op.add_column('users', sa.Column('last_login_at', sa.DateTime, nullable=True))
# Rename columns safely
op.alter_column('users', 'user_name', new_column_name='username')
# Create new tables
op.create_table(
'user_sessions',
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('user_id', sa.Integer, sa.ForeignKey('users.id')),
sa.Column('token', sa.String(500), unique=True),
sa.Column('created_at', sa.DateTime, server_default=sa.func.now())
)
# Add constraints
op.create_unique_constraint('uq_sessions_token', 'user_sessions', ['token'])
def downgrade():
op.drop_constraint('uq_sessions_token', 'user_sessions')
op.drop_table('user_sessions')
op.alter_column('users', 'username', new_column_name='user_name')
op.drop_column('users', 'last_login_at')
Knex Format (JavaScript)
Knex is Node.js’s query builder, and migrations with Knex are programmatic. You write JavaScript functions that build queries. This approach is great for applications that need complex logic or conditional migrations based on data state.
exports.up = function (knex) {
return (
knex.schema
// Add columns
.table("users", (table) => {
table.datetime("last_login_at").nullable();
table.string("session_token", 500).unique();
})
// Rename with migration step
.table("users", (table) => {
table.renameColumn("user_name", "username");
})
// Create related tables
.createTable("user_sessions", (table) => {
table.increments("id").primary();
table
.integer("user_id")
.unsigned()
.references("users.id")
.onDelete("CASCADE");
table.string("token", 500).unique().notNullable();
table.timestamps(true, true);
})
);
};
exports.down = function (knex) {
return knex.schema.dropTable("user_sessions").table("users", (table) => {
table.renameColumn("username", "user_name");
table.dropColumn("session_token");
table.dropColumn("last_login_at");
});
};
Notice the pattern: every up() operation has a matching down() in reverse order. This is non-negotiable. If you can’t reverse it, you can’t deploy it. The framework guides the structure, but the underlying principle is the same: bidirectional, reversible changes. This pattern is essential because you will need to rollback. You might not think you will, but you will. A rollback at 2 AM that works smoothly is worth its weight in gold.
Step 3: Data Preservation and Transformation
Some schema changes require data transformation. Renaming a column is safe—data moves automatically. But changing a string column to integer requires transformation logic that parses values, validates them, and decides what to do with unparseable values. The key is being explicit about the strategy.
Here’s where you make decisions: do you want to preserve as much data as possible and accept some NULL values where parsing fails? Do you want to validate everything matches a pattern before attempting transformation? Do you want to create a temporary column, transform into it, validate the results, and only then drop the original?
const generateDataTransformation = (diff, dataStrategy) => {
const transformations = [];
diff.columnsModified.forEach(({ table, column, from, to }) => {
// String to Integer: parse and validate
if (from.type === "STRING" && to.type === "INTEGER") {
transformations.push({
type: "sql",
script: `
-- Transform ${table}.${column} from STRING to INTEGER
ALTER TABLE ${table} ADD COLUMN ${column}_temp INTEGER;
UPDATE ${table} SET ${column}_temp = CAST(${column} AS INTEGER) WHERE ${column} ~ '^[0-9]+$';
-- Rows that couldn't convert become NULL
-- Review these manually:
SELECT COUNT(*) FROM ${table} WHERE ${column} NOT ~ '^[0-9]+$';
ALTER TABLE ${table} DROP COLUMN ${column};
ALTER TABLE ${table} RENAME COLUMN ${column}_temp TO ${column};
`,
});
}
// Increasing string length: always safe
if (
from.type === "VARCHAR" &&
to.type === "VARCHAR" &&
to.length > from.length
) {
transformations.push({
type: "safe",
message: `Increasing ${column} from VARCHAR(${from.length}) to VARCHAR(${to.length}) — no data loss`,
sql: `ALTER TABLE ${table} ALTER COLUMN ${column} TYPE VARCHAR(${to.length});`,
});
}
});
return transformations;
};
Step 4: Validation Before Execution
Here’s where we prevent disasters. Before we generate the final migration file, we validate everything. This is your safety net. Every blocker means the migration cannot proceed without human intervention and approval. Warnings don’t block, but they’re logged and visible so you understand the risks you’re taking.
const validateMigration = (migration, diff, dataStrategy) => {
const report = {
status: "pass",
checks: [],
warnings: [],
blockers: [],
};
// Check 1: Reversibility
if (!migration.down || migration.down.length === 0) {
report.blockers.push("Migration has no down() — cannot deploy");
report.status = "blocked";
}
// Check 2: Data loss
if (diff.dataLossRisk.length > 0 && dataStrategy === "fail-on-loss") {
diff.dataLossRisk.forEach((risk) => {
report.blockers.push(`Data loss risk: ${risk.message}`);
});
report.status = "blocked";
} else if (diff.dataLossRisk.length > 0) {
diff.dataLossRisk.forEach((risk) => {
report.warnings.push(`⚠️ ${risk.message}`);
});
}
// Check 3: Foreign key constraints
diff.constraintsAdded.forEach((constraint) => {
if (!validateForeignKeyTarget(constraint)) {
report.blockers.push(
`Foreign key constraint references non-existent table: ${constraint.references}`,
);
}
});
// Check 4: Syntax (dry-run)
const dryRunResult = executeDryRun(migration.up);
if (!dryRunResult.success) {
report.blockers.push(`Syntax error in up(): ${dryRunResult.error}`);
report.status = "blocked";
}
const downDryRun = executeDryRun(migration.down);
if (!downDryRun.success) {
report.blockers.push(`Syntax error in down(): ${downDryRun.error}`);
report.status = "blocked";
}
// Check 5: Rollback safety score
report.rollbackSafetyScore = calculateRollbackSafety(migration, diff);
if (report.rollbackSafetyScore < 0.8) {
report.warnings.push(
`Rollback safety score is ${report.rollbackSafetyScore} — migration is risky`,
);
}
report.checks.push("✓ Reversibility validated");
report.checks.push("✓ Constraint integrity verified");
report.checks.push("✓ Syntax passed dry-run");
return report;
};
If any blocker is found, the migration is marked as “requires review” and sent to a human before execution. This isn’t excessive caution—this is learned behavior from actual production incidents. Warnings don’t block, but they’re logged and visible.
Step 5: Changelog Generation
Finally, we document what changed in human-readable form. This becomes your git history entry, explaining decisions and risks to future maintainers. This documentation is invaluable when debugging weird data issues months later and trying to understand why a column exists or when it was added.
# Migration: 2026-03-16 12:45:30
## Tables Modified
- **users**: Added columns, renamed column
- **user_sessions**: New table created
## Columns Added
- users.last_login_at (DATETIME, nullable)
- users.session_token (VARCHAR(500), unique)
- user_sessions.id, user_sessions.user_id, user_sessions.token
## Columns Renamed
- users.user_name → users.username
## Constraints Added
- user_sessions.user_id → users.id (CASCADE on delete)
- user_sessions.token (unique)
## Data Changes
- Existing user data preserved ✓
- No column dropped ✓
- Transformation: None required
## Rollback Safety
- Reversibility: SAFE ✓
- Data Loss Risk: NONE ✓
- Rollback Score: 0.95/1.0 ✓
## Deployment
prisma migrate deploy
**Tested on**: PostgreSQL 14+
**Estimated time**: < 30 seconds on 100K rows
This changelog becomes part of your git history, documenting decisions, risks, and changes forever.
Integration with Claude Code
To integrate this skill into Claude Code:
# Create the skill
mkdir -p .claude/skills/database
cat > .claude/skills/database/SKILL.md << 'EOF'
# [SKILL.md content from above]
EOF
# Invoke it
/skill generate-safe-migration --current-schema @current-schema.json --target-schema @target-schema.json --framework prisma --database-type postgresql
The skill handles the heavy lifting: schema analysis, code generation, validation, and documentation. You provide intent and constraints. Claude Code provides safety and reversibility.
Real-World Integration: A Migration Workflow
Here’s how teams actually use this in practice. A developer needs to rename a column in production:
- Developer describes intent: “Rename user_name column to username”
- Developer provides context: Current schema file, target schema file
- Claude Code analyzes: “You’re renaming a column. This is safe. Rollback is straightforward.”
- Skill generates migration: Creates up() and down() functions, validates both work
- Developer reviews output: Sees the migration, changelog, and safety assessment
- Developer tests: Runs migration on staging data, verifies rollback works
- Developer deploys: Pushes migration with confidence
- Monitor execution: Database team watches metrics during migration
The entire workflow takes 30 minutes instead of 3 hours. The developer spends time on intent and review, not on manual SQL writing and testing. The skill handles the mechanical work.
Extending the Skill for Your Organization
Your specific database patterns might need custom handling. Extend the skill by:
-
Adding custom validators: Your team might have rules like “never drop a column with active foreign keys” or “all migrations must include rollback tests.” Add validation rules specific to your constraints.
-
Supporting custom data types: If you use extensions like PostGIS or Hstore, the skill needs to understand them. Add pattern recognition for your custom types.
-
Integrating with your CI/CD: The skill can output migrations, but your CI/CD pipeline runs the actual execution. Make the skill output compatible with your deployment process (GitHub Actions, GitLab CI, Jenkins, etc.).
-
Adding performance profiling: For large tables, add automatic performance estimation. The skill can analyze row count and suggest whether an online migration approach is needed.
-
Implementing approval workflows: For production databases, require explicit approval before generating certain migrations. A migration that drops a column should require sign-off from a database team member.
The skill is a foundation. Your organization builds on it to match your specific requirements and risk tolerance.
Why This Matters: The Human Cost of Database Failures
Database migrations are high-stakes operations. Unlike a code deployment that you can rollback in seconds, a database migration that goes wrong creates compounding problems. A failed schema change locks your database, blocking all queries, hanging your application, creating cascading failures through your system. Your monitoring alerts fire. On-call engineers get paged. Incident war rooms open. Customers see error pages. Trust erodes.
The economic cost is staggering. A one-hour database outage for a mid-size company can mean $100,000 in lost business, lost productivity, customer dissatisfaction. A production incident stays in your organizational memory for months. Developers become conservative about making schema changes. Product teams stop requesting features that require schema work. Your product velocity drops. The fear of database changes becomes a competitive disadvantage.
Beyond economics is the human cost. Engineers pulled from important work to debug production failures. On-call engineers losing sleep. Team morale taking a hit from incidents that feel preventable. The stress compounds when the database is considered mission-critical—which it is. Everyone knows that losing the database means losing the business.
A migration skill doesn’t just automate technical work. It eliminates whole classes of incidents. It removes the late-night fear of schema changes. It restores confidence that database work is safe. That confidence is worth more than the hours saved.
Understanding Migration Risk: The Layers of Safety
When we talk about safe migrations, we’re thinking in layers. Each layer addresses different risks:
Layer 1 – Prevention: Prevent unsafe migrations from being generated in the first place. Detect data-loss scenarios before they reach SQL. Catch broken foreign key relationships. Validate that rollbacks are actually reversible.
Layer 2 – Detection: Before execution, run syntax checks, dry-runs on test databases, constraint analysis. Verify the migration works on representative data. Catch problems in non-production before they spread to production.
Layer 3 – Reversibility: Every migration must be reversible. If something goes wrong, you must be able to undo it. This requires careful planning of the down() path and testing that down() actually works.
Layer 4 – Monitoring: During execution, monitor migration progress. If something unexpected happens, catch it early. Don’t let a migration hang indefinitely. Know when it succeeds and when it fails.
Layer 5 – Recovery: If a migration fails, you need rollback capability. This is the last resort, but it’s non-negotiable. Recovery planning is as important as the migration itself.
The skill implements all five layers. By the time a migration reaches human hands for review, it’s been validated at multiple levels. Risks have been identified and documented. Rollback paths have been tested. The human reviewer is making an informed decision based on comprehensive analysis.
Learning from Migration Disasters: What Goes Wrong in Production
Before we talk about prevention, let’s understand what actually happens when migrations fail at scale. Real organizations have battle scars. Understanding these stories teaches us why every validation gate matters.
Consider the case of a major e-commerce platform that needed to split a monolithic user table. They had millions of active records. The migration was written by a senior engineer, tested in staging, and scheduled for 2 AM. It started at 2:00 AM. By 2:47 AM, the database was locked. The schema update had acquired an exclusive lock that wasn’t being released. Application queries started timing out. At 3:15 AM, they started getting automated alerts. At 3:30 AM, they were in an incident war room trying to kill the migration and roll back. The rollback took another 30 minutes because the down() migration had a syntax error that nobody caught.
This entire incident—over an hour of total outage—happened because the team didn’t have:
- A dry-run phase that would have caught the lock duration problem
- A pre-tested rollback that would have failed validation
- Performance estimation that would have scheduled differently
- Documentation of what was being changed and why
The skill prevents this by forcing answers to hard questions upfront. How long will the migration take? Will it lock tables? On what scale? Do we have a tested rollback?
Another common scenario: a developer changes a column from VARCHAR(50) to VARCHAR(100). Seems safe, right? Except the application code tries to fit 255-character user IDs into that field for some weird legacy reason. The migration succeeds but now the application silently truncates user IDs, creating duplicate users and cascading failures across the platform. The data loss happened silently. It took three days to notice.
The skill would have caught this because it analyzes the actual data distribution before suggesting transformations. It would show: “Column user_id has values up to 312 characters. Changing to VARCHAR(100) will cause data loss.”
One more story: a team needed to add a NOT NULL constraint to an existing column. The column already had millions of records, so they thought it was safe. But the application had a bug where it inserted NULL values under certain race conditions. The team didn’t know about this bug. The constraint added successfully. Three weeks later, under unusual traffic conditions, the application hit the race condition and tried to insert NULL. The insert failed. The feature broke for users. The team had to roll back the constraint, which took a long investigation to debug.
The skill would have flagged this: “Adding NOT NULL constraint to column with NULL values. Current NULL count: 847. Cannot proceed without data cleaning strategy.”
These aren’t theoretical edge cases. These are real incidents from real organizations. The migration skill is built on the assumption that you can’t trust developers (including yourself) to think through every edge case under time pressure at 2 AM. So we build systems that don’t give humans the option to skip validation.
Production Considerations: Scaling Migrations Safely
As your system grows, migration complexity increases. A thousand-row table is forgiving. A ten-million-row table requires careful planning. Here’s how production-grade migration planning works:
Performance Estimation: How long will this migration take? On production data volumes? With your current hardware? If it takes longer than your acceptable window (usually measured in minutes, not hours), you need to split it into smaller migrations or run it during maintenance windows. The skill should estimate this automatically.
Lock Duration Management: Some migrations lock tables. How long will it lock? Will application queries timeout? Can you run the migration in a way that minimizes lock duration? For PostgreSQL, using CONCURRENTLY on indexes lets other operations proceed. For MySQL, tools like pt-online-schema-change avoid locking. The skill should recommend lock-minimizing approaches.
Backward Compatibility: If you’re removing a column, can the application handle it? If you’re renaming, do database drivers handle it? The skill should analyze your application code (if possible) to detect compatibility issues. Some migrations require application changes to happen first.
Point-in-Time Recovery: Can you roll back to before the migration if something goes wrong? Do you have backups? How recent? The skill should verify your backup and recovery procedure before allowing migrations.
Monitoring During Execution: Run the migration while monitoring database health. CPU usage, query latency, replication lag (if you have replicas). If anything looks wrong, abort and rollback.
Staged Rollout for Large Changes: For major schema changes, consider rolling out to replicas first. Verify on replica. Then promote replica to primary. This lets you validate on production-scale data without affecting the main database.
Troubleshooting: When Migrations Go Wrong
Even with perfect planning, things go wrong. Knowing how to handle failures is critical:
Scenario 1: Migration Hangs Midway
The ALTER TABLE acquired a lock and is waiting for a transaction to complete. But the transaction is waiting for another query, creating deadlock. The fix: identify the blocking query, kill it, let the migration proceed. The skill should log which queries block long-running migrations and help identify blockers.
Scenario 2: Rollback Fails
You abort the migration and try to rollback. But the rollback fails due to data inconsistencies created by the partial migration. The fix: you need manual intervention. Have a database expert on standby who can analyze the partial migration state and determine how to get to a consistent point. This is why testing rollbacks is non-negotiable.
Scenario 3: Data Consistency Errors Post-Migration
The migration succeeded. Weeks later, you discover data is corrupted somehow. The transformation logic had a bug affecting 0.1% of rows. The fix: this is why transformation validation is critical. You should verify post-migration that data meets constraints and expectations.
Scenario 4: Replication Lag After Large Migrations
You have read replicas. A large migration causes replica lag. Replicas can’t keep up. Read traffic falls through to primary, overloading it. The fix: throttle the migration. Run it at a pace your replicas can keep up with. The skill should be aware of replication lag and adjust migration speed accordingly.
Team Adoption: Making Migrations Safe Organizationally
Technical tools matter, but organizational practices matter more. Here’s how to build a culture where migrations are safe:
Code Review for Migrations: Treat migrations like code. They go through review. A senior engineer verifies the migration, checks the rollback, confirms the risk assessment. The review is as important as the code.
Testing in Staging: Every production migration runs first in staging on production-scale data. This catches problems before production. If you don’t have a production-scale staging environment, create one. The cost of maintaining it is far less than the cost of production incidents.
Scheduled Maintenance Windows: Don’t do migrations during business hours. Schedule them for times when the impact of failure is minimized. Usually very early morning, late night, or weekends depending on your timezone.
Alert Integration: When a migration is running, alert the team. If it fails, alert escalates. You need human oversight of long-running operations.
Documentation: Document why the migration is necessary, what it does, what the risks are, what the rollback plan is. This becomes your incident war-room reference if something goes wrong.
The Principle: Safe by Default
The core principle here is safe by default. Migrations block if they’re not reversible. They warn if data loss is detected. They validate syntax before you ever run them. They document every change. They require human approval before executing if there’s any risk. They pass through dry-runs that prove they work before touching production.
That’s not just automation. That’s responsible automation. That’s the difference between an AI system that makes your life easier and one that makes your incident reports longer. Responsible automation means the system assumes the worst, validates exhaustively, and only proceeds when it’s confident nothing will go catastrophically wrong.
When you build a migration skill for Claude Code, you’re not automating away responsibility. You’re encoding best practices, validation gates, and safety checks into a reusable system. You’re saying: “I trust you to write SQL, but I don’t trust you to be paranoid enough about data loss. Let’s build that paranoia in.” You’re creating a system that’s smarter about databases than any individual developer, and more paranoid about risk.
The next time you need to change your schema in production, you’re not manually writing SQL in vim and praying it works. You’re describing your intent to a system designed to be paranoid about data loss, reversibility, and validation. You’re using tooling that forces you to think through every edge case, test every path, and document every decision. That tooling does the mechanical work. You focus on architecture.
That’s the migration skill. That’s how you sleep well when production schema changes are happening. That’s how you build confidence that database work is something to embrace, not something to fear.
-iNet