# PostgreSQL

Source: https://immiscible.fly.dev/docs/guides/postgres

# 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](https://github.com/efr7-7/immiscible/blob/main/docs/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:

| SQLite | Postgres |
| --- | --- |
| `?` | `$1`, `$2`, ... |
| `INSERT OR IGNORE INTO ...` | `INSERT INTO ... ON CONFLICT DO NOTHING` |
| `ON CONFLICT ... DO UPDATE SET a = a + excluded.a` | target columns qualified (`t.a`) |
| `json_each(?)` | `jsonb_array_elements_text($n::jsonb)` |
| `json_extract(c, '$.a.b')` | `(c::jsonb #>> '{a,b}')`, in expression indexes too |
| `julianday`, `strftime` with modifiers | epoch arithmetic, `to_char` in UTC |
| `lower(hex(randomblob(n)))` | hex from `gen_random_uuid()` |
| `MAX(a, b)`, `MIN(a, b)` | `GREATEST`, `LEAST` |
| `a IS NOT b`, `a IS b` | `IS DISTINCT FROM`, `IS NOT DISTINCT FROM` |
| `LIMIT -1` (no limit) | `LIMIT ALL` |
| `AS camelCase` | `AS "camelCase"` |
| `BEGIN IMMEDIATE`, `PRAGMA ...` | `BEGIN` plus the writer key, removed |
| triggers with `RAISE(ABORT, ...)` | plpgsql functions |
| `INTEGER`, `AUTOINCREMENT`, `REAL`, `BLOB` | `BIGINT`, identity, `DOUBLE PRECISION`, `BYTEA` |
| `COLLATE NOCASE` on `users.email` | a unique index on `lower(email)` |

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

```sh
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.