SKILL

---
name: database-migrations
description: Write safe, reversible, backward-compatible database migrations. Avoid downtime, locks, and "oops we lost data".
category: architecture
version: 0.1.0
triggers: ["migration", "alter table", "schema change", "DB upgrade"]
applies_to: [openclaw, cursor, claude-code]
---

# Database Migrations

You evolve a live schema without breaking running code or losing data. Migrations are
deployed incrementally; they must be **backward-compatible with the currently-running
application** until the new code is fully rolled out.

## When to use

- Adding / removing / renaming columns or tables
- Changing types or constraints
- Adding indexes on production data
- Backfilling or transforming data
- Splitting a column into multiple, or merging columns

Do not invoke for initial schema design (`database-schema`) or for query problems (`database-review`).

## Scope

In scope:
- Migration mechanics: order of operations, expand/contract pattern
- Naming and numbering conventions
- Lock-aware DDL on Postgres / MySQL / SQL Server
- Rollback strategy
- Splitting schema migrations from data migrations

Out of scope:
- Schema design itself (use `database-schema`)
- Choosing migration tooling (Prisma, Knex, Alembic, sqlx, golang-migrate, raw SQL) —
  match what the project uses

## Inherits

- [`meta/engineering-principles`](../../meta/engineering-principles/SKILL.md)
- [`meta/reuse-before-create`](../../meta/reuse-before-create/SKILL.md) — before introducing a new migration helper, naming convention, or backfill pattern, check the most recent migrations for an established one and follow it.
- [`meta/token-discipline`](../../meta/token-discipline/SKILL.md) — read the most recent migrations only, not the whole history.
## Token discipline (specific)

- Read the most recent **5–10 migration files** to learn naming, style, tooling.
- Read the migration runner config (e.g. `prisma/migrations/`, `alembic.ini`,
  `apply-migrations.mjs`) once.
- Do NOT re-read every historical migration — they're noise for current work.

## Golden rules

1. **No destructive change in the same deploy as the code change.** Add the new shape,
   ship code that handles both, backfill, switch reads, then remove the old shape.
2. **Every migration is reversible** in concept — if the rollback isn't safe (e.g. data loss),
   say so explicitly in the migration comment.
3. **Schema migrations and data migrations are separate files.** Don't mix DDL and large
   `UPDATE` in the same step.
4. **Long-running migrations don't run inside the deploy step.** Use a job runner or a
   manual operator step.
5. **Locks matter.** A 30-second `ALTER TABLE` on a busy table = an outage. Use the
   non-blocking variants where the DB supports them.

## Expand / contract pattern (the default)

For any non-trivial schema change, split into 3+ deploys: