migrations

name	migrations
description	Use for migration tasks that convert systems or artifacts while preserving behavior: SQL schema/data migrations, Terraform or IaC migrations, serialization format migrations, rollback planning, state movement, compatibility checks, and before/after validation.

Migrations

When To Use

Use this skill when the task moves an existing behavior, schema, deployment, or artifact from one form to another. Correctness is preservation plus a safe transition plan: migrate, validate, and roll back if needed.

First Pass

1. Inventory source state and target state.
2. Identify invariants: data counts, IDs, external behavior, serialized fields, resource names, permissions, and public interfaces.
3. Choose strategy: expand/contract, in-place transform, side-by-side shadow, import/move state, or compatibility wrapper.
4. Write validation before and after migration.

Implementation Patterns

SQL Migrations

For database migrations, prefer additive changes before destructive ones.

-- expand
ALTER TABLE users ADD COLUMN display_name TEXT;

-- backfill in batches for large tables
UPDATE users
SET display_name = COALESCE(first_name || ' ' || last_name, username)
WHERE display_name IS NULL;

-- contract only after readers use the new column
-- ALTER TABLE users DROP COLUMN first_name;

When emitting fresh table definitions from a spec (e.g. PDF or schema document), use CREATE TABLE IF NOT EXISTS with explicit constraint primitives, and mirror each CREATE with a matching DROP TABLE IF EXISTS so the migration is reversible.

-- up
CREATE TABLE IF NOT EXISTS orders (
    order_id      INTEGER       PRIMARY KEY,
    customer_id   INTEGER       NOT NULL REFERENCES customers(customer_id),
    order_code    VARCHAR(32)   NOT NULL UNIQUE,
    status        VARCHAR(16)   NOT NULL DEFAULT 'pending',
    is_paid       BOOLEAN       NOT NULL DEFAULT FALSE,
    total_amount  DECIMAL(12,2) NOT NULL,
    notes         TEXT,
    created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- down
DROP TABLE IF EXISTS orders;

Type mapping cheatsheet: short identifiers / codes → VARCHAR(n); whole numbers / surrogate keys → INTEGER; flags → BOOLEAN; dates/times → TIMESTAMP; free-form strings → TEXT; money / fixed precision → DECIMAL(p,s).

When the deliverable is a JSON manifest of migrations, emit one entry per table with paired up/down statements:

{
  "orders": {
    "up":   "CREATE TABLE IF NOT EXISTS orders (...);",
    "down": "DROP TABLE IF EXISTS orders;"
  }
}

Include downgrade or rollback scripts when the framework supports them. Validate row counts, null counts, uniqueness, foreign keys, and sample records.

Terraform and IaC

For IaC migrations:

• preserve resource identity with moved blocks or terraform state mv;
• never accept a plan that destroys required resources unless destruction is the task goal;
• keep provider versions and backend configuration explicit;
• output a plan summary and validation checklist.

Serialization Migration

For model or pipeline serialization, remove unsafe or non-portable formats when requested. Save immutable configs, model parameters, feature schemas, and versioned metadata separately so the artifact can be loaded without local code side effects.

Validation

• Compare source and target counts, checksums, schemas, and representative records.
• Run load/readback tests on migrated artifacts.
• For IaC, inspect plan for create/update/delete counts and resource addresses.
• Confirm rollback path exists or document why rollback is not possible.
• Check compatibility with old readers when the task requires it.

Common Failures

• Treating migration as a rewrite and losing source invariants.
• Dropping columns or resources before all consumers move.
• Forgetting state movement in Terraform and forcing replacement.
• Producing serialized artifacts that depend on local classes or paths.
• Validating only that files exist, not that they load and preserve behavior.