Skip to content

ADR-0012: One Postgres Database for Governance and Application Storage

Target architecture — not the current operating contract

This decision records why the platform's storage moved from two SQLite files to one PostgreSQL database. For the operating procedures, see Current release, Migrate from SQLite and Backup and restore.

Status

Accepted — 2026-09-23

Context

Until this release, every governance and application record lived in two better-sqlite3 files that both the Console and the gateway opened: the control store (DATABASE_PATH) and the application store (APPLICATION_DATABASE_PATH). That shape had four recurring costs.

  • The busy-snapshot race. A deferred transaction that read and then wrote failed at once with SQLITE_BUSY_SNAPSHOT when another process committed in between; the gateway's 5-second heartbeat was enough to trigger it. Administrators saw spurious "could not be updated" refusals when saving Team allowances, memberships or Agent drafts, and the end-to-end suite saw "database is locked" failures. Most write sites used such transactions, so fixing them one at a time was not practical.
  • Cross-store transactions. Bundle publication spanned both files, which no transaction covered: bodies committed to the application store first, tracked in an expiry registry that a cleanup job pruned when the control transaction failed.
  • Native modules. better-sqlite3 had to be compiled in the release builder and probed on the host, which tied the release artifact to the host's C library and Node ABI.
  • Operator tooling built around files. Two paths that had to be distinct, non-alias regular files; WAL and snapshot preflight; storage-generation markers; an offline cutover CLI with its own manifests; and sqlite3 on the host for backups.

Decision

  • One PostgreSQL database. The control and application tables live in one PostgreSQL 16+ database and one schema, reached through one DATABASE_URL. The former cross-store publication is an ordinary transaction. The driver is pg (node-postgres), a pure-JavaScript module; no native database module remains.
  • One storage facade with a global write lock. Services issue statements through one asynchronous facade (all, get, run, transaction). A transaction is ambient: statements inside transaction() run on its connection, and a nested call is a savepoint. Every transaction that may write takes one database-wide advisory lock at BEGIN, behind an in-process FIFO write queue. Write transactions are therefore serialized, one at a time across processes, as SQLite's single writer serialized them, so no read-then-write site inside a transaction needs individual analysis and the busy-snapshot race cannot occur. A write outside transaction() is one statement that takes neither the queue nor the lock, so it is not serialized against write transactions: it must validate itself (for example where state = …), and a write whose correctness depends on an earlier read belongs inside transaction(). A consistent multi-statement read uses a read transaction without the lock.
  • Stored representations preserved. Epoch-millisecond timestamps stay integers (bigint). JSON stays text, because bundle checksums and size limits are computed over the exact stored bytes. Booleans become boolean and blobs bytea. Tables that code orders or checkpoints by insertion carry an explicit seq identity column in place of SQLite's rowid.
  • A fresh baseline. The schema starts from one generated baseline migration plus one migration of hand-written PL/pgSQL triggers that raise the same messages as the SQLite triggers; later migrations are additive. The SQLite migration history is not carried over.
  • Explicit migration, guarded startup. pnpm --filter @mission-control/database db:migrate migrates the database, and refuses one whose collation, character type or locale provider is not C, C and libc. The gateway, the Console and connector:rotate-credentials never migrate; they refuse to start with STORAGE_MIGRATION_REQUIRED when any bundled migration is missing. Bootstrap migrates before it writes.
  • Deployment through standard Postgres tools. The deployment helper takes a pg_dump custom-format backup before migrating, records its catalog and SHA-256, runs db:migrate once, and verifies the guard before promotion. rollback_vps.sh --restore-database restores such a dump: it checks the dump's migrations against the target release before stopping anything, takes a safety dump of the current database, and replaces the drizzle and public schemas in one transaction.
  • A one-time import. import-sqlite copies the two SQLite files into a freshly migrated database with verified per-table counts.

Consequences

For operators:

  • One DATABASE_URL in shared.env replaces DATABASE_PATH and APPLICATION_DATABASE_PATH. The database is created by the Postgres superuser with C collation and owned by a dedicated login role; the connection string is a secret.
  • Backups are pg_dump custom-format dumps, verified with pg_restore --list and a full read. A restore replaces the whole database, either in one transaction through rollback_vps.sh --restore-database or, as the superuser, into a freshly created database. The host needs psql, pg_dump and pg_restore, and sqlite3 until the cutover is done: the cutover backs up the SQLite stores with it.
  • Rollback to an earlier Postgres release is code-only by default, because migrations are additive. A database restore is an explicit option. A rollback to the last SQLite release is a manual procedure that restores the previous shared.env from the cutover backup, checks the SQLite files, and points the release links back at the SQLite release.
  • pg_stat_activity names each process through application_name (for example mission-control-gateway and mission-control-console).
  • Write transactions remain serialized database-wide, as they were on SQLite. Scoping the lock per Organization is a possible later optimization.

For contributors:

  • Test suites need a PostgreSQL server: pnpm db:test:up starts a throwaway one on 127.0.0.1:5433 (MISSION_CONTROL_TEST_DATABASE_URL). Each test clones a migrated template database. The Playwright journeys use MISSION_CONTROL_E2E_DATABASE_URL.
  • Statements are sql template values with interpolated parameters; services do not reach the driver directly.
  • A failed statement aborts its whole transaction on Postgres, where SQLite let the transaction continue. Code that carries on after a statement error catches it around a nested transaction(), a savepoint that rolls back alone; an outermost transaction whose body caught one fails with storage_transaction_aborted instead of committing nothing silently.

Behavior users can observe:

  • Text ordering is bytewise, as before: the database uses C collation.
  • The six checks that read inside a JSON document (Team allowance models, tools and deployments; key policy; inference funding; experiment manifest) refuse a document that contains a \u0000 escape or a lone surrogate. SQLite accepted such documents, and the other JSON columns still store them. The one-time import refuses them in every JSON column.
  • People and audit searches fold ASCII letter case as before. Pattern filters over machine values, such as error-code and origin prefixes, are case-sensitive; those values are lowercase constants.
  • Trigger messages are unchanged. When one write breaks two guarded rules at once, the message may name the other rule than SQLite did, because Postgres fires co-firing triggers in a different order.

What was retired

  • DATABASE_PATH, APPLICATION_DATABASE_PATH, DEPLOY_DATABASE_PATH, DEPLOY_APPLICATION_DATABASE_PATH and the test-only MISSION_CONTROL_TEST_DIALECT.
  • The storage-generation marker, the path and sidecar preflight, and their startup errors STORAGE_RESET_REQUIRED and STORAGE_PATHS_REFUSED.
  • The offline cutover command (cutover prepare, cutover verify, cutover restore) and the reset tooling.
  • db:migrate:application, the 107 SQLite migrations (89 control and 18 application) and better-sqlite3.

Rejected alternatives

  • Keep SQLite and fix the racing transactions one by one. Rejected because most write sites used deferred read-then-write transactions, and the cross-store transaction and the native module would remain.
  • Row-level locking and optimistic retries instead of a global write lock. Rejected for this release because every read-then-write site would need individual analysis and new retry paths; the global lock keeps the behavior the services were written for.
  • Native jsonb and timestamp columns. Rejected for this release because jsonb re-serializes documents and would change the stored bytes that checksums and size limits are computed over.
  • Two Postgres databases, one per former store. Rejected because it would keep the cross-store transaction without any separation benefit; the table names do not collide.

Relationship to earlier decisions

ADR-0011 kept the governance record in SQLite and noted that the decision record was designed to move unchanged. It has: request_decision and the rest of the governance plane are now Postgres tables, and ClickHouse remains the span store.