File-based migrations

The planner handles structural diffs — column additions, drops, renames, type changes — where the OLD and NEW shapes can be computed from declared schema vs live introspection. It refuses to plan migrations that need DATA SEMANTICS the diff can't see:

• Splitting one table into two (extract address fields to a new addresses table with FK back)
• Merging two tables (move both personal_emails + work_emails into one emails table tagged by type)
• Atomic data moves across tables (move all orders of status archived into archived_orders with a different schema)
• Type changes that need a custom transformation (parse a JSON column into structured columns)

For these, write an explicit migration file.

#File convention

apps/api/migrations/
├── 20260415_120000_split_address_out.ts
├── 20260520_093000_merge_emails.ts
└── 20260603_140000_normalize_orders.ts

Filename = <UTC-timestamp>_<slug>.ts. The timestamp orders applies — sortable + globally unique without coordination. Slugs are for humans + filed alongside the timestamped name in _voltro_migration_plans.id for findability.

The file exports a default migration():

// apps/api/migrations/20260415_120000_split_address_out.ts
import { migration } from '@voltro/database'

export default migration({
  id:          '20260415_120000_split_address_out',
  description: 'Move users.address* fields into a separate addresses table with FK back.',

  up: async ({ sql, log }) => {
    // 1. CREATE the new table:
    await sql.unsafe(`
      CREATE TABLE addresses (
        id          text PRIMARY KEY,
        userId      text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        street      text NOT NULL,
        city        text NOT NULL,
        postalCode  text NOT NULL,
        createdAt   timestamptz NOT NULL DEFAULT now()
      )
    `)
    await sql.unsafe(`CREATE INDEX addresses_user_idx ON addresses(userId)`)

    // 2. Move the data set-wise with one INSERT…SELECT — the file
    //    context exposes the @effect/sql SqlClient, not the DataStore:
    await sql.unsafe(`
      INSERT INTO addresses (id, "userId", street, city, "postalCode")
      SELECT
        gen_random_uuid()::text,
        id,
        "addressStreet",
        "addressCity",
        COALESCE("addressPostalCode", '')
      FROM users
      WHERE "addressStreet" IS NOT NULL AND "addressCity" IS NOT NULL
    `)
    log.info('migrated address fields → addresses')

    // 3. DROP the old columns:
    await sql.unsafe(`ALTER TABLE users DROP COLUMN "addressStreet"`)
    await sql.unsafe(`ALTER TABLE users DROP COLUMN "addressCity"`)
    await sql.unsafe(`ALTER TABLE users DROP COLUMN "addressPostalCode"`)
  },

  down: async ({ sql }) => {
    // Reverse — required. The runner won't accept a migration without one.
    await sql.unsafe(`ALTER TABLE users ADD COLUMN addressStreet text`)
    await sql.unsafe(`ALTER TABLE users ADD COLUMN addressCity text`)
    await sql.unsafe(`ALTER TABLE users ADD COLUMN addressPostalCode text`)
    await sql.unsafe(`
      UPDATE users SET
        addressStreet     = a.street,
        addressCity       = a.city,
        addressPostalCode = a.postalCode
      FROM addresses a
      WHERE users.id = a.userId
    `)
    await sql.unsafe(`DROP TABLE addresses`)
  },
})

The up body runs inside a transaction (on dialects that support it; see multi-dialect for MySQL forward-roll semantics). down runs the same way for rollback.

#What the runner provides

up: async (ctx) => {
  ctx.sql       // @effect/sql SqlClient with sql.unsafe / sql tagged-template / sql.onDialectOrElse
  ctx.log       // structured logger scoped to this migration (info / warn)
  ctx.appliedAt // ISO-8601 string — when this migration started (deterministic across the up/down pair)
}

The file context is { sql, log, appliedAt } — there is NO store handle here. The escape hatch is deliberately SQL-level: you're doing the structural moves the typed DataStore can't express, so you drive them with sql.unsafe(...) for raw DDL and sql\...`/sql.onDialectOrElse(...)for parameterised statements. Move data set-wise withINSERT … SELECT/UPDATE … FROM` rather than a per-row JS loop — it's one round-trip and stays inside the migration's transaction on the dialects that support transactional DDL.

#Ordering vs the planner

File-based migrations are applied IN TIMESTAMP ORDER, BEFORE the planner-based diff runs. So a typical boot looks like:

[voltro:dev] migrations: file-based pending → 1
[voltro:dev] migration 20260415_120000_split_address_out applying
[voltro:dev] migrated 1247 users → addresses
[voltro:dev] migration 20260415_120000_split_address_out applied in 482ms
[voltro:dev] auto-migrate: planning schema dialect=postgres env=dev tables=23
[voltro:dev] auto-migrate: schema up to date fingerprint=8f507ba1e1aadad5

The file ran first, dropped the columns, created the new table. The planner then diffs the (now mutated) live shape against the declared schema — and finds it up to date, because the declared schema also has addresses as a separate table + users without the address columns.

This ordering is critical: file-based migrations MUTATE state the planner sees. Sequence:

1. Edit users.entity.ts to remove the address* columns + add the new addresses entity file
2. Write the file-based migration that physically moves the data + drops the columns
3. Boot — file-based runs first (writes the new state), planner runs second (sees a clean diff against the new declared schema, no-op)

If you skip step 2 + just edit the schema, the planner refuses to plan: dropping addressStreet is lossy (no dropped() marker), creating addresses is safe. The plan would refuse + the boot would fail until you add dropped() markers... but then you'd lose the data. The file-based migration moves the data BEFORE the planner sees the columns are gone.

#Tracking

File-based migrations land in the same _voltro_migration_plans table as planner-based ones, with source: 'file':

plan_mig_5k78  fp=...  env=dev  src=file        ops=1  3.2s   2026-04-15 12:00:00  by=boot:dev
plan_mig_5k79  fp=...  env=dev  src=auto-diff   ops=0  12ms   2026-04-15 12:00:03  by=boot:dev

The voltro db plans command shows both side by side in the same timeline. Drift detection compares against the latest fingerprint regardless of source.

#When NOT to use file-based migrations

The escape hatch is for situations the diff genuinely can't infer. Don't reach for it for:

• ADD NOT NULL column — that's .backfill() on the column declaration
• Rename column — that's .renamedFrom() on the new column
• Drop column — that's dropped()
• Drop table — remove from declared set + VOLTRO_DESTRUCTIVE_OK=1 for the apply

A file-based migration for any of these defeats the planner's safety story. The DSL annotations carry their fix-hint into the developer's editor; the file is just "trust me, this works".

#Idempotency

File-based migrations are NOT auto-idempotent. The runner checks _voltro_migration_plans for a row with the same id and skips if found. The migration body itself must NOT assume it ran from a clean slate IF you're going to edit it after applying (the framework refuses to re-apply a modified file silently — see Drift docs).

Practical rule: once a file-based migration is applied in any env, it's frozen. Subsequent corrections are NEW migrations with NEW timestamps that read the half-applied state + finish the job.

#Rollback

voltro db rollback-file <id> invokes a file-based migration's down body. The <id> is the positional migration id (the basename minus .ts):

voltro db rollback-file 20260415_120000_split_address_out

This is the planner-side file runner (migration() → down). It is a DIFFERENT subcommand from voltro db rollback, which drives the separate defineMigration step-based runner and takes --to, not a positional id — see the overview for the two runners. rollback-file refuses on NODE_ENV=production (rollback runs as an explicit deploy step there).

If down throws, the rollback is treated as failed — the schema stays in the half-rolled-back state + the operator handles it manually. The framework can't auto-recover from a broken inverse.

#Applying file-based migrations from the CLI

voltro db files applies pending file-based migrations under <root>/migrations/ — the same runner the boot path invokes, exposed as a CLI command for when you've set VOLTRO_AUTO_MIGRATE=0 and apply schema as an explicit step:

voltro db files

It's a distinct command from voltro db apply (which runs the planner-based auto-diff). The two histories both land in _voltro_migration_plans — file-based rows carry source: 'file', planner rows source: 'auto-diff' — so voltro db plans shows them inline. On a normal boot, file-based migrations run FIRST (before the planner diff), so by the time voltro db apply would run, the file has already mutated the live shape.

The prod pipeline page covers the deploy-step apply flow for planner-based changes.