PostgreSQL

Immiscible runs on one SQLite file by default. With DATABASE_URL set to a postgres:// URL it runs on Postgres instead: the same code, the same migrations, the same tests. This page explains how, what it costs, and what is different. Deploying it is in self-host.md.

#How it works

src/platform/storage.js opens the database: SQLite (db.js) by default, Postgres (db-pg-sync.js) when DATABASE_URL is a postgres:// URL. Both have the same synchronous interface (get, all, run, exec, tx, q, group commit, durable()), so nothing above that file knows which it has.

#Synchronous, on purpose

The service layer calls the database synchronously in about a thousand places, many inside transactions whose bodies read, decide and write: the evidence ledger append, workspace state versions, the gate’s checks. Making every one of those async is a five to six week port, and every converted transaction body becomes a place where other requests can interleave.

Instead, PgSyncDatabase keeps the synchronous contract. A worker thread (db-pg-worker.js) holds one Postgres connection; each statement is posted to it and the main thread waits on a shared flag (Atomics.wait) until the answer is back. Nothing else in the process runs during that wait, exactly as nothing runs during a SQLite page read, so a transaction body stays atomic within the process. Across processes, the writer lock below does the same job.

The cost: every statement waits a network round trip on the event loop. On a local Postgres the test file test/agents.test.js takes about 135 s against 81 s on SQLite. Over a network the round trip dominates, so run the database in the same region, ideally the same zone, as the server.

#What is kept from SQLite

• One writer at a time. A write transaction (tx(), or the group commit’s transaction) begins with pg_advisory_xact_lock on one key for the database. That is SQLite’s BEGIN IMMEDIATE: whoever holds the key is the only writer, in any process, until it commits or rolls back.
• Group commit. The writes made in one synchronous run of code share one transaction, committed at the end of the run; durable() waits for the commit before a response leaves.
• A failed statement fails alone. Each write inside a transaction runs under a savepoint, so a constraint failure the code catches does not abort the transaction, as in SQLite.
• Rows look the same. BIGINT and NUMERIC come back as numbers (past 2^53 they stay strings rather than lose precision), booleans as 1 and 0, and a unique violation reads “UNIQUE constraint failed”, which the ledger’s sequence-conflict check recognises.
• rowid. The code orders, deletes and exports by SQLite’s rowid. On Postgres every table gets a real identity column of that name, and rows leave it out unless the query selects it, as SELECT * does on SQLite.

#The evidence ledger stays serial

Each workspace’s ledger is a hash chain: record n holds the hash of record n - 1, and (workspace_id, seq) is the primary key. The append reads the head and inserts the next record inside one write transaction, so under the writer key no two appends can read the same head.

test/postgres-ledger.test.js proves it: eight processes append 100 records each to one workspace at the same moment, through EvidenceLedger over PgSyncDatabase, then the chain is read back and every link checked (800 records, contiguous sequence numbers, each prev equal to the previous hash, each hash recomputed, all eight writers present). It runs with and without group commit, and in both runs no process ever meets another’s record at its sequence number. A control run with the key turned off meets thousands of sequence conflicts (2,838 in one run), and under that contention some appends exhaust the ledger’s eight retries and are lost (137 of 800); the primary key still refuses any fork.

#Translation

src/platform/sql-dialect.js rewrites the SQLite the code uses into Postgres, after masking strings and comments:

Anything it cannot translate faithfully (INSERT OR REPLACE, GLOB) is refused rather than run differently. The two INSERT OR REPLACE statements in chat.js are now portable upserts, and the audit log’s kind filter, a JavaScript SQL function on SQLite, is the same rules as regular expressions on Postgres (AUDIT_KIND_SQL, checked against auditKind() by test/postgres.test.js).

JSON stays in TEXT columns and timestamps stay ISO strings, so ordering and comparison behave as on SQLite.

#Migrations

The server migrates on start, under a session advisory lock, so several processes starting together migrate once. Each migration runs in its own transaction with its version number. immiscible-server db migrate runs the same migrations by hand; immiscible-server db translate [n] prints the Postgres SQL for review.

#The driver

pg, pinned exactly (8.23.1) in optionalDependencies, following the SAML library’s precedent. It is required only by the worker thread, only when DATABASE_URL is a postgres:// URL. The Docker image installs it. If it is missing, the server refuses to start and says how to install it.

A hand-written wire-protocol client was considered and not chosen: SCRAM authentication, TLS negotiation, the extended query protocol and type parsing are a lot of security-sensitive code to own, and pg is the most used Node client.

#Differences from SQLite

• Spend totals. The gate’s running spend totals are kept on SQLite by triggers that call JavaScript. On Postgres they are not kept: the gate always computes spend with the full query, which is the same answer, slower for very busy agents.
• Backups. immiscible-server backup, restore, Litestream and the built-in bucket upload are SQLite-only. On Postgres use the provider’s backups and point-in-time recovery.
• /readyz reports backend: "postgres" instead of file and disk sizes; it still checks that the database takes a write and the schema is current.
• INSERT OR IGNORE on SQLite also ignores NOT NULL and CHECK failures; ON CONFLICT DO NOTHING ignores only unique conflicts. On Postgres those failures raise.
• LIKE is case-sensitive on Postgres. The uses in the code match machine prefixes, where case does not arise.

#Testing on Postgres

TEST_DATABASE_URL="postgres://user:${PGPASSWORD}@host:5432/postgres" npm run test:postgres
TEST_DATABASE_URL=... npm run test:postgres -- test/agents.test.js

The user must be allowed to create databases. The script runs the test files in batches, each in a fresh database it drops afterwards; inside a batch, every database a test opens becomes a schema of its own, so the suite runs unchanged. test/postgres-ledger.test.js and test/postgres.test.js run only this way and skip themselves under plain npm test.

#What is not done

• Several server processes sharing one Postgres. The database keeps writes serial across processes and the ledger proof runs across processes, but the server keeps per-workspace state in memory and has only been run as one process per database. The Helm chart defaults to one replica.
• Moving an existing SQLite deployment to Postgres. There is no copy command yet; a new Postgres install starts empty.
• The async port that would let one process overlap many database round trips. Only worth doing if the round trip becomes the bottleneck.