/migrate

Schema migrations are open-heart surgery. This one comes with rollback, validation and a risk protocol - as a generated file package, never as execution.

What this is for

• From two schema states the package emerges that nobody writes by hand: forward migration, tested rollback, validation queries, risk protocol with explicit data-loss warnings.

PROTECTION RULE - never execute, never copy into target project without approval

This skill NEVER executes a migration against a database. It generates files exclusively. No access to live databases.

The migration package is ALWAYS first created in migration-<date>/ in the current working directory and shown to the user - never written directly into the target project. Only after explicit user approval may the package additionally be copied into the target project (edit action, no script).

What You Must Do When Invoked

During analysis, assign a confidence level to each finding: proven (confirmed by evidence), likely (strong signal, needs review), or suspected (weak signal).

If /migrate -help or /migrate -h (without further arguments) is invoked: output the ## Usage section unchanged and stop.

Otherwise follow these steps in order, skipping none.

Step 1 - Help check

If invoked with -help or -h, output the ## Usage section unchanged and stop.

Step 2 - Clarify target

Clarify -OldSchema + -NewSchema (files or directories) and the SQL dialect (postgres|mysql|sqlite|mssql - required, ask if unknown; for Prisma read from the datasource block). Get confirmation.

Step 3 - Diff

& "<SKILL_DIR>/scripts/schema-diff.ps1" -OldSchema "<old>" -NewSchema "<new>"

Format mix (errors, exit code != 0): forward message, stop. Identical schemas (0 changes): report "No changes" - do not generate an empty package.

Step 4 - Rename clarification

Present each renameCandidate to the user individually (rename = data preserved; drop+add = data lost - that is the entire difference). Without user response: treat as drop+add, but mark RED in the risk protocol.

Step 5 - Risk classification

Classify each change:

• lossless: add table/column/index, widen column.
• lossy: remove column/table, narrow type, add NOT NULL on existing data, add UNIQUE on existing data.
• lock-risky: operations that can hold long locks on large tables (name dialect-specifically).

Step 6 - Generate package (5 files, migration-<date>/)

1. 01-forward.sql: constraint-safe order (columns first, then FKs; drops last). Lossy steps individually, with comment-block WARNING before each. NOT NULL introduction as three-step (nullable column + backfill placeholder + SET NOT NULL), backfill as marked TODO with suggestion.
2. 02-rollback.sql: exact reversal in reverse order. Non-reversible steps (dropped data) comment as such - rollback restores the schema, data only via backup (notice block at top).
3. 03-validate-pre.sql: counts before migration, checks for new constraints (how many existing rows violate the future NOT NULL/UNIQUE NOW - run before migration), orphan checks for new FKs.
4. 04-validate-post.sql: counts after migration for comparison.
5. 00-protocol.md: diff summary, classification of each change with evidence (diff entry), data-loss section PROMINENT (even if empty: explicitly state "no lossy operations detected"), recommended order (pre-validate -> backup -> forward -> post-validate), open questions (renames without answer, unparsed statements).

unparsed not empty: read the raw texts of both states and diff manually, mark as "manually checked" in the protocol instead of ignoring.

Dialect feature missing (e.g. sqlite cannot DROP COLUMN before 3.35): generate dialect-specific alternative (e.g. table rebuild) or mark as manual work.

Evidence requirement: each generated migration line must be traceable to a diff entry - generate nothing that the diff does not show.

Step 7 - Summarize

State the package path, summarize the risk protocol, ALWAYS explicitly mention data-loss warnings (even if none exist).

Usage

/migrate                              # interactive
/migrate <old> <new> <dialect>        # diff + generate package
/migrate -help