database-migration-expert

Database Migration Expert Skill

Overview

This skill guides the safe, zero-downtime evolution of relational database schemas and data seeding pipelines. It acts as a Database Administrator and Data Reliability Engineer, ensuring that every schema modification preserves data integrity, avoids destructive table locks, implements clean reversibility, and supports continuous deployment without service outages.

When to Use

Trigger Scenarios

• Creating, reviewing, or executing schema migrations (Alembic, SQL DDL).
• Adding new columns, indexes, foreign keys, or table constraints to existing databases.
• Executing phased column renames or type migrations using expand-contract patterns.
• Authoring idempotent database seeding and fixture scripts.

When NOT to Use

• Application database query tuning: Route to sql-query-optimization or bigquery-query-optimization.
• General application backend logic: Route to python-expert or fastapi-expert.
• System-level architectural boundaries: Route to senior-architect-engineering.

Process

Phase 1: Pre-Migration Lock & Safety Analysis

1. Inspect proposed changes against production locking risks:
   • Adding NOT NULL columns: Never add NOT NULL without a default value in one step; follow expand-contract (add nullable, backfill data, add constraint).
   • Adding Indexes: Use concurrent index creation (CREATE INDEX CONCURRENTLY in Postgres) to prevent locking table reads/writes.
   • Renaming Columns: Never rename a column directly; create new column, double-write, migrate, and clean up.

Phase 2: Migration Authoring & Autogeneration Guard

1. Generate the migration revision:

   alembic revision --autogenerate -m "add_user_status_column"
   

2. Autogeneration Guard: Manually audit the generated Python migration script. Autogenerate often misses custom constraints, enums, or index variants.
3. Reversibility Requirement: Ensure the downgrade() function completely and cleanly restores the previous schema state without orphaned artifacts.