add-migration

Files (2)

• SKILL.md
• change-classes.md · references/

SKILL.md (first lines)

> **Tool mapping (OpenCode):** This skill uses:
> - `question` tool for user prompts (not `AskUserQuestion`)
> - `task` tool for subagents (not `Agent` or `Task` subagent syntax)
> - `skill({ name: "..." })` to load other skills (not `Skill(skill="...", args="...")`)
> - `codegraph_explore`, `codegraph_search`, `codegraph_context` for codebase exploration (preferred)
> - `todowrite` for phase/progress tracking
> - `grep`, `glob`, `read`, `bash` as fallback when `.codegraph/` not initialized
>
> **Context rule:** This skill supersedes any prior skill instructions. Follow ONLY these instructions now. Read the user's goal and any mode/include/skip from the conversation context.

# Code Add Migration

A migration that breaks production usually broke at design time, not at run time. Classify the change first; the plan follows from the class.

---

## Phase 1 — Detect Migration Tooling

Use `todowrite` to mark this phase as in_progress.

In order:

1. `drizzle.config.*` → Drizzle (`drizzle-kit generate`).
2. `prisma/schema.prisma` → Prisma (`prisma migrate dev --create-only`).
3. `knexfile.*` → Knex (`knex migrate:make`).
4. `migrations/` with bare `.sql` files → raw SQL (project's own runner).

If none detected and the user wants a migration, ask via `question` tool which they use rather than picking one.

**Exit:** the migration tool, command, and output directory are known.

---

## Phase 2 — Classify the Change

Use `todowrite` to mark this phase as in_progress.

**MANDATORY — READ [`references/change-classes.md`](references/change-classes.md)** before classifying. Every change falls into one of three classes; the deployment plan depends on the class.

```
ADDITIVE    — strictly adds. Old code keeps working unchanged.
MUTATING    — changes shape of existing rows or constraints. Old code may break.
DESTRUCTIVE — removes data or columns. Cannot be reverted from data alone.
```

State the class and the one-line reason. If the user's request mixes classes (e.g., "rename column" = drop + add + backfill), split it into separate migrations.

**Exit:** every requested change has exactly one class.

---

## Phase 3 — Plan by Class

Use `todowrite` to mark this phase as in_progress.

### ADDITIVE

Single migration. Examples: new nullable column, new table, new index, new enum value (if the DB supports concurrent enum extension).

Generate the migration. Add the index `CONCURRENTLY` if the table has any non-trivial size and the DB engine is Postgres. For Drizzle, this requires raw SQL (`drizzle-kit` does not emit `CONCURRENTLY` from schema diffs alone).

### MUTATING

Multi-step plan. Each step is its own deploy + migration:

1. **Code tolerates both shapes.** Update the application to read/write either old or new shape.
2. **Backfill / migrate data.** Run the migration. For NOT NULL adds: column is added nullable, backfilled, then constrained to NOT NULL in a third step. For type changes: add new column, dual-write, backfill, swap reads, drop old.
3. **Code requires new shape only.** Remove the dual-shape tolerance.

State each step explicitly. Do not collapse them into a single migration even if the table is small today — the pattern is what makes it safe at scale.

### DESTRUCTIVE

Multi-step plan with a wait period:

1. **Stop writing to the column/table.** Update code to neither read nor write it. Deploy.
2. **Verify in production logs/metrics** for at least one full deployment cycle (not enforced by this skill — but call it out).
3. **Drop the column/table.** Migration.

For dropping rows (data deletion), get an explicit user confirmation via `question` tool and require a backup pointer (snapshot SHA, dump filename) be recorded in the migration's accompanying notes file.

**Exit:** the plan is written down with one migration per step.

---

## Phase 4 — Generate

Use `todowrite` to mark this phase as in_progress.

Run the project's generate command. For Drizzle:

```bash
pnpm drizzle-kit generate
```

Open the generated SQL. **Read it.** drizzle-kit can produce surprising diffs (renames inferred as drop+add, default-value reordering). If the SQL doesn't match the plan, edit it directly — or adjust the schema and regenerate.

For each migration file, verify:

- Index creation on tables with > a few thousand rows uses `CREATE INDEX CONCURRENTLY` (Postgres). If the tool can't emit this, hand-edit the SQL and add a note in the migration that it must be run outside a transaction.
- Adding a NOT NULL column has a default OR is followed in a later step by the backfill + constraint.
- Default values: `DEFAULT now()` is evaluated per row; `DEFAULT '2026-01-01'` is a literal. Make sure the right one was generated.
- Foreign-key drops/adds: confirm cascade behavior matches the schema's `references(...).onDelete(...)`.

**Exit:** the migration SQL has been read and matches the plan.

---

## Phase 5 — Backfill (if MUTATING)

Use `todowrite` to mark this phase as in_progress.

For backfills that touch many rows:

```sql
-- Bad: locks the table for the entire UPDATE
UPDATE big_table SET new_col = derive(old_col);

-- Good: chunked

From Acauhi99/opencode-agent-system, MIT.

More from Acauhi99/opencode-agent-system