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.

#voltro serve refuses to boot while any are pending

Serve's schema guard is a DECLARATIVE fingerprint diff — the declared schema against the last applied plan. A file-based migration exists for the changes a state diff cannot infer: a data move, a backfill, a cross-table rewrite. Those move no fingerprint at all, so the guard passed and production ran un-migrated with nothing said.

On a real deploy environment (NODE_ENV=production / staging), against a SQL store, voltro serve now refuses to boot while any migration file has never run against that database, and names the pending ids:

serve: refusing to boot — 2 pending file-based migration(s) have never run
against this database. They perform the changes a schema diff cannot infer
(data moves, backfills, table splits), so the declarative fingerprint check
below cannot see them.

  Run them from your pre-deploy job — `voltro db migrate .` (schema + files)
  or `voltro db files .` (files alone).

It does not apply them, and that is deliberate: a rolling deploy starts N replicas, each would try, and the migration lock turns that into N-1 processes blocked on boot. VOLTRO_AUTO_MIGRATE=0 bypasses this exactly as it already bypassed the fingerprint check — one switch for "no boot-time schema checks".

A local voltro serve is untouched: voltro dev applies migrations there, so a preview serve has nothing to report.

#Remote databases: boot will not apply them unattended

voltro dev applies pending migration files at boot. Against a local database that is the whole point of the escape hatch. Against a remote one it means that saving a file is enough to change production — before review, before CI, without typing a command.

So when the configured database is not local and there are pending migration files, dev boot refuses instead:

file-based migrations: refusing to auto-apply 1 file-based migration(s)
to a REMOTE database (ep-cool-dawn.eu-central-1.aws.neon.tech).

  Pending:
    • 20260725_150000_drop_legacy_subscriptions

A database counts as local when its host is loopback (localhost, 127.0.0.1, ::1), a private LAN address (10.x, 192.168.x, 172.16–31.x), host.docker.internal, a .local / .localhost name, a file: / sqlite: URL, or a bare hostname like postgres or db — only a container network resolves those, so docker compose up keeps working untouched. Everything else — a managed provider, any dotted public hostname, an unparseable DB_URL — counts as remote.

Three ways forward:

# 1. Point the app at a local database (what dev boot assumes)
DB_URL=postgres://app:app@localhost:5432/app voltro dev

# 2. Apply them deliberately, once
voltro db files .

# 3. Accept unattended applies for this environment
VOLTRO_REMOTE_MIGRATIONS_OK=1 voltro dev

The gate is silent when nothing is pending, which is the normal case — running dev against a remote database is unaffected until the moment a file would actually execute against it. It refuses rather than skipping quietly: a skipped migration leaves the database in a shape the app does not expect (a half-done table split, a column the handlers already read), and the failures that follow point everywhere except at the cause.

It does not try to detect destructive SQL. In arbitrary SQL that is not decidable, so such a check would be either leaky or noisy. What the gate separates is the two things boot used to conflate: saving a file and applying it to production.

#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.