Make database migration safe and obvious for Vercel installs

[zaridan]

Make database migration safe and obvious for Vercel installs.

Current state:

• Migrations exist and are run manually through scripts/migrate.ts.
• External operators need a safe, documented way to initialize and upgrade the database.

Acceptance:

• Fresh install path includes a clear migration step or safe first-run migration mechanism.
• Migrations are idempotent and concurrency-safe in the deployment path.
• Failures surface as actionable setup/admin diagnostics, not generic 500s.
• Upgrade docs explain when and how migrations run.
• The approach works with Supabase Postgres in Vercel/serverless conditions.

[zaridan]

Field evidence from the first real deploy of a schema change (2026-08-02)

Deployed HT-101 (#165) to production and applied migrations 27–29. Everything below is observed, not hypothesised.

1. The deploy → migrate window is real, and it is an outage

src/composition/root.ts deliberately does not migrate on cold start — scripts/migrate.ts's own doc says schema changes are "an operator step, not something every serverless instance re-runs." That is a defensible choice, but it means the sequence is:

1. New code goes live.
2. Somebody remembers to migrate.

Between those, new code runs against the old schema. Tonight that window contained an imap-fetch cron firing every 2 minutes against three tables that did not exist yet. It was minutes because I was watching. Nothing enforces that.

The acceptance criterion "failures surface as actionable setup/admin diagnostics, not generic 500s" is precisely right, and today unmet: the cron just errors.

Worth considering: a startup check that compares the code's highest migration id against _migrations and fails loudly with "database is at 26, this build needs 29 — run npm run migrate" rather than letting every request discover it separately. That is a diagnostic, not auto-migration, so it keeps the deliberate no-auto-migrate posture.

2. DATABASE_URL is not retrievable by the person doing the deploy

The documented path is DATABASE_URL='postgres://…' npx tsx scripts/migrate.ts. On this project DATABASE_URL is marked sensitive in Vercel, so vercel env pull returns it empty. The operator who can deploy cannot necessarily run the migration.

I completed it by applying the same SQL through a Supabase connection — same statements, same advisory lock, same _migrations inserts — but that is a workaround, not the documented path, and it bypasses the runner that the docs point at.

For an external operator this is worse: they will follow the runbook, get an empty variable, and have no obvious next step. The upgrade docs need to say where the connection string comes from (Supabase → Project → Connect), not just how to use it.

3. What already works well, and should not be changed

Verified in the process:

• The advisory lock and one-transaction design are sound. All three migrations applied atomically; a mid-run failure would have rolled back cleanly.
• Idempotency held under real conditions. Migration 27's ENABLE ROW LEVEL SECURITY statements ran against tables where RLS was already enabled out of band, and were no-ops.
• Migration 27's verification guard is the right pattern. It counts leftover anon/authenticated/PUBLIC privileges after its REVOKEs and RAISE EXCEPTIONs rather than reporting a lockdown it did not achieve. More migrations that make security claims should verify them the same way.

4. A migration-id collision nearly shipped silently

Separately worth recording here because it is a property of the id-keyed design: #170 took id 27 on main while #165 was in review, and #165 also used 27. Because id is the applied-once key, a second 027 would have been recorded as already-applied and skipped — no tables, no error. Caught by a merge conflict, not by anything systematic.

A CI check that fails when two migrations share an id, or when a PR's migration ids overlap main's, would have caught it deterministically. That belongs in this issue's "safe and obvious" scope.

Suggested additions to acceptance

• A version-skew diagnostic that names the gap and the fix, rather than per-request errors.
• Upgrade docs state where to obtain the connection string, and note that Vercel may not surface it.
• CI rejects duplicate or main-overlapping migration ids.