Move boot-time schema sync off the server startup path (DB_SYNC gate + explicit migrations)

[arantespp]

Context

src/server.ts runs sequelize.sync({ alter: true }) behind a Postgres advisory lock (syncSchemaWithAdvisoryLock in src/db.ts) and awaits it before app.listen. The HTTP server — including /health — therefore does not accept connections until the schema sync finishes.

Problem

• On a schema-changing release against a populated database, sync({ alter }) can run for minutes. The server can't answer health checks until it completes, so an orchestrator that health-gates a rollout (ECS/Kubernetes/etc.) can kill the task before it ever starts listening — a rollout can then fail even though the app is fine.
• Concurrently-starting instances serialize on the advisory lock: a waiter blocks for the holder's entire migration before it begins its own boot, compounding the startup delay.
• sync({ alter: true }) is implicit, unreviewable schema management — no migration history, no rollback story, and it cannot express "add nullable → backfill → tighten constraint → drop" as ordered steps. It emits one DDL statement per column diffed straight from the current model, which is exactly what makes destructive/constrained changes on populated tables fail or silently lose data (see "Consolidated from" below).

Proposed solution

1. Gate the boot-time sync behind a DB_SYNC env var (default off) so ordinary boots bind the port in ~seconds.
2. Replace sync({ alter: true }) with versioned migrations (e.g. Umzug) run as a discrete, one-shot step that completes before the service rolls (a pre-deploy job / init step), not on every task boot. Migrations are tracked (applied-once) and support expand/contract sequencing across releases.
3. With migration guaranteed complete before new tasks serve, /health can return 200 as soon as the port binds — an honest liveness/readiness split.

Interim mitigations already in place

• The boot sync now bounds the advisory-lock wait via @ttoss/postgresdb's lockTimeoutMs (fail-fast instead of an unbounded deadlock if a lock holder dies mid-sync) — configurable via SCHEMA_SYNC_LOCK_TIMEOUT_MS.
• Deployment health-check grace periods have been widened to cover migration time.

These keep deploys safe but still serialize every schema-changing release behind a multi-minute boot; this issue tracks removing the slow step from the boot path entirely.

Consolidated from #790 / #791

Both issues were concrete instances of this same gap — sync({ alter: true })'s inability to express ordered, data-safe schema steps — hitting the TaskTransition.actor_* → principal_* rename (#787):

• sync --alter cannot add TaskTransition.principal_kind to a non-empty task_transitions, so the 0.18.0 upgrade path is broken for every long-lived environment #790: sync --alter cannot add a NOT NULL column with no default (principal_kind) to a populated task_transitions table in one shot — Postgres rejects ADD COLUMN ... NOT NULL on non-empty tables (23502).
• The 0.18.0 principal_id null-out irreversibly destroys orchestration-run provenance on rows where actor_id held the run id but orchestration_run_id is NULL #791: The prescribed null-out + DROP COLUMN actor_kind, actor_id is lossy on rows where actor_id held an orchestration-run/generation id that was never duplicated into orchestration_run_id/generation_id. Worse, the drop is not an operator-controlled step — the server's own boot-time sync performs add and drop automatically, so there is no window to rescue the data manually before it disappears.

Both are closed as subsumed by this issue rather than tracked separately, since the correct fix for each is a migration, not a workaround bolted onto sync. The first real migration written under this issue must implement, as ordered steps:

• Add principal_kind / principal_id nullable (safe on populated tables)
• Rescue orphaned actor_id values into orchestration_run_id (LIKE 'orch_run_%') / generation_id (LIKE 'gen_%') where those are still NULL
• Fail the migration loudly if any automation row still has an unrecoverable actor_id after the rescue, instead of silently nulling it out
• Backfill principal_kind = actor_kind, principal_id = actor_id (NULL for automation)
• ALTER COLUMN principal_kind SET NOT NULL
• Drop actor_kind / actor_id in a separate, later migration/release (contract step), not the same one as the rescue/backfill, so a rolling deploy window has both old and new writers working throughout

Acceptance criteria

• DB_SYNC gate in src/server.ts (default off)
• Versioned migration runner + a documented pre-deploy step
• Boot no longer blocks app.listen on schema DDL by default
• The TaskTransition.actor_* → principal_* migration (above) implemented as the first real migration under the new runner, expand and contract as separate steps
• Docs updated (deployment + schema management)

🤖 Generated with Claude Code