Shared Postgres for per-host state — design

Date: 2026-08-26 · Status: in progress (group 1)

Problem

Every piece of durable state is a local SQLite file, so it belongs to
whichever host the container runs on. On 24 Aug the two stacks were split
across two machines and public.sqlite silently diverged: the admin console
listed 1 stale account while the public surface had 9, and every staff
"reset password" / "disable account" landed on a copy nobody read. Nothing
errored. The console said berhasil.

That was closed on 26 Aug by colocating all four stacks on ahu-int-01, which
works only for as long as they share a host. The planned move of the public
surface to ahu-dmz-01 reopens it exactly as before.

Colocation is a deployment constraint standing in for a storage guarantee. This
replaces it with the guarantee.

What actually has to move

file	tables	rows	written by	read by	risk
public.sqlite	public_users, public_threads, public_thread_messages	10 / 7 / 26	both apps	both apps	realised — this is the incident
policy.sqlite	policies	7	internal	public	latent
config.sqlite	config	1	internal	public	latent
staff.sqlite	staff_users	2	internal	internal	none
audit.sqlite	audit	63	internal	internal	none

Only the first three groups are cross-app. staff_users and audit are
written and read by one app, so a local file is not wrong for them — they are
explicitly out of scope rather than forgotten. Moving state that has no
sharing problem buys nothing and costs a migration.

Order of work, by realised risk:

1. group 1 — public.sqlite (this change): the tables that actually
   diverged, plus PublicAccountAdminStore, which reads the same tables from
   the other app and therefore cannot be left behind.
2. group 2 — policy + config: same write-here/read-there shape, not yet
   bitten only because nobody edited a policy between 24 and 26 Aug.
3. out of scope — staff_users, audit.

Target

The Postgres already on the host: ahu-dash-db-shared, PostgreSQL 18.1,
database ai. The agent's tables live in schema ai; the public schema is
empty. Chatbot state goes in its own schema, chatbot, so the two are
obviously separate and can be dumped or dropped independently.

One CHATBOT_DB_URL for both apps. It being the same string in both env files
is the whole point, and scripts/check-conventions.mjs already fails a build
when a shared state var is declared for one app and not the other.

The cost: the stores become async

better-sqlite3 is synchronous; every Postgres driver is not. So each store
method grows an await, and ~85 call sites go with it. Most already sit in
async route handlers, so the change is mechanical — but it is the bulk of
the work and the main source of risk, which is why this is staged by group
rather than done in one sweep.

No dual-backend flag

Tempting, and rejected. A flag means every store carries two implementations
and every bug reproduces on one backend but not the other — while the entire
purpose of the change is to stop having two sources of truth. Cutover is a
single deploy, and rollback is redeploying the previous image plus the retained
SQLite files, which are left in place untouched.

Migration

scripts/migrate-sqlite-to-pg.mjs, idempotent: creates the schema, applies
migrations, then copies rows with ON CONFLICT DO NOTHING so a re-run cannot
duplicate. It reports per-table counts on both sides and exits non-zero if
they disagree — a migration that half-worked has to fail loudly, because a
silently-partial copy is the same class of bug as the divergence being fixed.

Sequences are reset after the copy so the first Postgres insert does not
collide with a migrated id.