Follow-up to #1497 / #1532.
The schema is currently created once via CREATE TABLE IF NOT EXISTS in src/db/postgres/helper.ts. There is no schema_migrations / version table, no ALTER path, and no concurrency guard. Once a deployment has created its tables, future schema changes will not apply to existing databases.
Proposed
A lightweight, dependency-free runner: a schema_migrations table plus pg_advisory_xact_lock to serialise migrations across concurrently booting processes. The current APP_SCHEMA_SQL becomes migration 1.
This is a prerequisite for any schema evolution, including the repo_users normalisation.
Follow-up to #1497 / #1532.
The schema is currently created once via
CREATE TABLE IF NOT EXISTSinsrc/db/postgres/helper.ts. There is noschema_migrations/ version table, noALTERpath, and no concurrency guard. Once a deployment has created its tables, future schema changes will not apply to existing databases.Proposed
A lightweight, dependency-free runner: a
schema_migrationstable pluspg_advisory_xact_lockto serialise migrations across concurrently booting processes. The currentAPP_SCHEMA_SQLbecomes migration 1.This is a prerequisite for any schema evolution, including the
repo_usersnormalisation.