selfhost: boot migrations take no cross-instance lock — concurrent boots can re-run DML backfills; boot ALTERs have no lock_timeout

[JSONbored]

Summary

Boot-time schema work has no cross-instance mutual exclusion, so two overlapping boots against the shared Postgres can re-execute DML backfills and corrupt data — and the boot-time column widening takes ~20 ACCESS EXCLUSIVE locks with no lock_timeout on every start.

docker-compose.yml:14 advertises "multi-instance capable", and overlapping boots are reachable today during a redeploy-companion container recreate or a healthcheck-triggered restart.

Mechanism (verified at HEAD, 776c414)

src/selfhost/migrate.ts:124-222 takes no advisory lock: it reads the ledger at :135 and, on failure, falls into a tolerant per-statement path at :208-217. It is called unguarded at src/server.ts:554-557.

Sequence with two instances and a pending migration:

1. A applies the migration file atomically.
2. B's execTransaction fails with "already exists" ⇒ treated as drift ⇒ tolerant per-statement path.
3. CREATE TABLE is tolerated — but a table-rebuild INSERT … SELECT or UPDATE backfill re-executes, producing duplicate or corrupted rows.
4. B's ledger INSERT then dies on duplicate key value violates unique constraint, which matches neither duplicate column nor already exists (migrate.ts:197, :214) ⇒ B crash-loops one cycle.

#9027 made migrations atomic against the crash case; the concurrency case was not covered.

Separately, widenGithubIdColumnsToBigint (src/selfhost/pg-adapter.ts:167-208) takes ~20 ACCESS EXCLUSIVE locks on every boot with no lock_timeout. On first application it fully rewrites webhook_events (high-volume, 14-day window, 111 MB / 302,588 rows measured on edge-nl-01). With a sibling serving traffic, the lock queue stalls both the boot and the sibling's queries indefinitely.

Deliverables

• Wrap runSelfHostMigrations and the boot-time ALTERs in a pg_advisory_lock on the Postgres backend (sqlite is single-process and needs nothing).
• Set lock_timeout around the idempotent boot ALTERs so a contended lock fails fast and retries instead of stalling the deployment.
• Make the tolerant per-statement fallback refuse to re-run DML (backfills) — tolerate idempotent DDL only. Re-running a backfill is never safe, and the current code cannot tell the difference.
• Add duplicate key value violates unique constraint on the ledger insert to the recognised "already applied" set, so a losing instance exits cleanly rather than crash-looping.

Tests

• Two concurrent runSelfHostMigrations against the same database ⇒ exactly one applies; the other waits and exits cleanly; no backfill runs twice.
• A migration containing both DDL and a backfill, applied twice ⇒ the backfill executes once.

[linear-code]

JSO-6550