ADR 0008 — Migration runner on first request

Cusp targets editors who have never opened a terminal. An update that adds a column must not require anyone to know what wrangler d1 migrations apply is. So migrations apply themselves: the Worker brings the schema up to date before serving a request.

Decision

Two paths, working together. Deploy-time apply is the optimisation: the deploy pipeline (and the Deploy to Cloudflare button) runs the migrations as part of the deploy, so when it works the request-time runner has nothing to do. Request-time apply is the safety net: the Worker checks for pending migrations on cold start and applies them before serving. Together they make the editor never run a migration command, and they converge on a working schema even for manual deploys and third-party CI that skip the deploy-time step.

How the runner works

The runner lives at src/worker/migrations/runner.ts. A module-level singleton means within one isolate only the first caller does the work and the rest await the same promise; a failed apply clears the singleton so the next request retries.

1. Acquire a coordination lock (see below).
2. Read _migration_state for the highest applied version.
3. Enumerate migrations embedded in the Worker bundle at build time as static strings — never fetched from R2 or any network source.
4. For each pending migration in file order: claim its row as applying, then submit the migration's SQL and the flip to applied as a single D1 batch — so partial application is impossible.
5. Release the lock.

_migration_state is the one table that is not a versioned entity and the only one whose own existence is bootstrapped — the runner creates it with CREATE TABLE IF NOT EXISTS before consulting it, solving the chicken-and-egg.

Coordination

Cross-isolate coordination uses a row-lock in _migration_state with a holder and an expiry, claimed atomically. A 60-second lock TTL comfortably exceeds any migration (D1 statements have their own 30-second cap). A request that cannot acquire the lock backs off and, after a bounded wait, returns 503 with Retry-After rather than serving against a half-migrated schema. A Durable Object would give stronger consistency but is heavier than this single-tenant, cold-start-frequency check needs.

Idempotency and recovery

Some migrations use bare ALTER TABLE ADD COLUMN, which SQLite has no IF NOT EXISTS form for, so the runner cannot rely on every migration being internally idempotent. Before running a migration's DDL, the runner inspects the live schema and short-circuits to applied when every target table, column, and index the migration would create is already present. This makes the runner correct even when _migration_state and the actual schema disagree — for example after a CLI-applied schema with an empty state table, or a restored backup whose state table is stale. When the schema-presence check is uncertain (an unrecognised statement shape), it conservatively runs the migration. The check parses only the DDL shapes the migrations actually use; a new shape extends the parser in the same commit.

Failure handling

A failed migration is logged with the failing SQL and the originating request ID, leaves its row in the applying state with the last error recorded, returns 503 to the in-flight request, and is retried automatically on the next request with no operator intervention. A request arriving during a long migration awaits the same promise, so the user sees a slow first response rather than a broken one.

Considered and rejected

• Deploy-time only — breaks for manual deploys and any path without the migration step, exactly where editors are most likely to be confused by a failure.
• Request-time only — slows the first request after every deploy unnecessarily; deploy-time is a free optimisation when the build environment supports it.
• Pulling migrations from R2 or a remote source — adds a network dependency and an attack surface (a malicious write would run arbitrary SQL); migrations are embedded in the bundle.
• A Durable Object for the lock — strongest consistency but heavier than needed; the row-lock is sufficient at single-tenant scale.
• Matching SQLite duplicate-column error strings to flip a migration to applied — couples the runner to error-wrapping text and decides applied only after a failure; the schema-presence check decides before the DDL runs.