---
name: scorpio-migration
description: Create a database migration the right way for the project's ORM/migration tool — edit the schema, generate the migration, review the SQL, then apply it. Use instead of hand-writing one-off SQL or ad-hoc fix scripts.
disable-model-invocation: true
---

# scorpio-migration

The canonical flow for schema changes. Resolve the project's stack from `scorpio.config.json` `stackProfile` (`orm`, `db`) and use *its* migration tooling and commands; the commands below show **Drizzle Kit** (`drizzle-kit`, generated SQL under `drizzle/`) as one worked example — substitute the equivalents for Prisma Migrate, TypeORM, Alembic, Rails, etc. The migration-generate / migrate / drift-check commands come from the project's `gates`/scripts. **Never** hand-roll an ad-hoc `*.sql`/`*.cjs` fix script for a schema change — use the tracked migration flow.

## Flow

1. **Edit the schema** in the schema source (the ORM's table/model definitions). Make the change additive where possible (new nullable column / new table) so the running app keeps working during rollout.

2. **Generate the migration** — do NOT write SQL by hand. Run the project's migration-generate command:
   ```bash
   npm run db:generate   # example (Drizzle Kit) — use the project's equivalent
   ```
   This writes a new migration file to the project's migration dir.

3. **Review the generated SQL.** Read the new file. Confirm it matches intent and watch for:
   - `DROP`/narrowing/`NOT NULL`-without-default → needs a backfill step, split across migrations
   - new indexes on large tables → prefer a non-locking create where the DB supports it (e.g. Postgres `CREATE INDEX CONCURRENTLY`)
   For anything destructive or risky, hand off to the `migration-reviewer` (fresh-context reviewer) before applying.

4. **Apply to the local/dev database** with the project's migrate command:
   ```bash
   npm run db:migrate    # example (Drizzle Kit) — use the project's equivalent
   ```
   (A direct "push" command, where the tool offers one, is for quick dev prototyping only — prefer generated migrations for anything that ships.)

5. **Verify** there's no drift with the project's schema-check command(s):
   ```bash
   npm run check:schema && npm run db:check   # example — use the project's equivalent
   ```

## Output

Report: the schema change made, the generated migration filename, the review notes (especially any destructive ops), and the result of the drift checks. If the change is destructive or touches a large/hot table, recommend running it through the `migration-reviewer` (fresh-context reviewer) and lay out the safe multi-step rollout. Do not run migrations against production.