Database Migration (ORM)

Use this skill when

• Creating or reviewing ORM migrations (schema + data transforms)
• Planning safe, staged rollouts (expand/contract) with backfills
• Debugging migration failures, drift, or inconsistent environments

Do not use this skill when

• The project uses forward-only SQL migration files as the primary mechanism
• The task is only query tuning or general performance analysis

Required inputs

• ORM tool and version, plus migration runner constraints
• Database engine/version and online DDL capabilities
• Change type (schema only, data backfill, or mixed)
• Data volume and acceptable lock/downtime window
• Deployment model (single app, multi-service, version skew tolerance)
• Rollback tolerance and irreversible operations (if any)

Workflow

1. Confirm context and constraints

• Capture ORM tool, DB engine/version, change type, and downtime limits.
• Output: a one-paragraph migration context summary.

2. Select rollout strategy (decision points)

• If zero-downtime is required, use expand/contract with backward compatibility.
• If data backfill is large, run a batched job outside the migration runner.
• If the change is irreversible, flag it as forward-only.
• Output: strategy outline with explicit gates and decision rationale.

3. Draft migration artifacts

• Define schema changes (expand step) and data backfill mechanics.
• Specify application read/write toggles or dual-write windows.
• Output: artifact list (migration files, backfill job plan, app toggle steps).

4. Safety checks and rollback plan

• Analyze locks, timeouts, long transactions, and migration ordering.
• Describe rollback/repair steps for each stage and what is not reversible.
• Output: rollback matrix with reversible vs forward-only items.

5. Verification and runbook

• Rehearse on staging or production-like datasets when possible.
• Track backfill progress, error rates, and schema drift checks.
• Output: verification checklist + runbook steps for production execution.

Common pitfalls

• Dropping columns before all services stop using them
• Adding default values that rewrite large tables without planning

Examples

Example prompt "We use Prisma and need to add a non-null status column with a backfill. We cannot take downtime. What is the migration plan and rollback?"

Example response outline

1. Migration Context: Prisma, Postgres, zero-downtime, large table.
2. Strategy & Decision Points: expand/contract, batched backfill job.
3. Artifacts & Execution Steps: add nullable column, deploy dual-write, backfill batches, enforce NOT NULL, drop old logic.
4. Rollback Matrix: revert schema changes, pause backfill, restore old code paths.
5. Verification & Monitoring: staging rehearsal, backfill progress metrics, post-cutover checks.

Resources

• References index: references/README.md