PostgreSQL backend#

BitExact records to local files by default. Setting BITEXACT_DATABASE_URL points the same product at PostgreSQL instead:

export BITEXACT_DATABASE_URL=postgresql://bitexact@db.internal:5432/bitexact
pip install --no-index --find-links wheels --require-hashes -r requirements-postgres.lock   # in your platform bundle (installing.md)
pip install --no-index --no-deps bitexact_sdk-<version>-*.whl

Nothing above the storage layer changes. The hash chain, the bundle format, and the verifier are identical either way.

What the database is, and is not#

PostgreSQL is the control plane and the fast index. It is not the trust root. The chain is: each entry hash-links to its predecessor over RFC 8785 canonical bytes, and heads are anchored off-box (WORM object-lock, RFC 3161 timestamps). That is what makes tampering detectable.

This distinction is load-bearing because the database administrator is in the threat model. In-database immutability is defense in depth: an operator who can drop a trigger can drop the guard. What the guards buy is that no ordinary mistake and no casual statement can quietly shorten a run or repoint a link. What proves history was not edited is re-walking the chain and comparing heads against their external anchors.

An exported bundle verifies with the standalone verifier, which shares no code with the product, reads no configuration, and opens no database connection. A relying party never needs access to this database — and that is deliberate.

Requirements#

PostgreSQL 18, the version the test suite runs against
One recording service as the sole writer (agents hold no database credentials; they reach the recorder over the proxy)

Schema#

Fifteen tables, created automatically, in one transaction, on the first connection to an empty database, plus a monthly partition per month of run_entries.

Table	Holds
`runs`	one row per run: chain head, the derived index projection
`run_entries`	the chain, one row per step, keyed `(month, run_id, seq)`, partitioned by month
`run_sidecars`	checkpoints, anchors, custody events, recording failures
`entity_ledgers`	the chained ledgers: workflows, cases, refusals, roles, capture expectations
`documents`	suites and their saved results, saved evidence packs, and the viewer's and events' operational records
`recorder_documents`	what only the recorder writes: its record of each act a peer submitted (claimed, written or refused), the runs it owes a seal, the transparency log's checkpoint and leaf index, signed purge records, its TLS key-exchange note, the collectors it found sending raw values, and each capture policy entries were recorded under
`access_log`	the access chain: who viewed, listed or retried what
`archived_runs`	the catalog of months moved out by `bitexact archive`
`tlog_leaves`	the transparency log: every signed checkpoint, in signing order
`run_reports`	each run's regress and fork-matrix reports
`event_outbox`	signed event notifications waiting to leave
`event_deliveries`	one delivery row per event and destination: state, attempts, lease
`event_leases`	named leases (the alert round, a dispatcher) on the database clock
`capture_sources`	each collector, proxy and SDK process and when it last spoke
`capture_overflow`	the count of capture sources evicted past the bound

When you provision a least-privilege role, grant it on all fifteen and on the sequences access_log_id_seq and capture_sources_touch, and make it the owner of run_entries with CREATE on the schema. The service creates a month's partition of run_entries when that month's first entry arrives, and bitexact archive drops a month it has moved out; PostgreSQL lets only the owner of the partitioned table do either. A role without them records nothing in a new month: every call that month is a declared storage_unavailable gap.

The viewer connects as a role of its own, never the recorder's. bitexact db-grant-viewer ROLE, run with the recorder's DSN, lets that role read every table and write only documents, run_reports, event_outbox, event_deliveries and event_leases: its saved packs, suites and their results, reports and event deliveries. Granting takes back any wider write the role held before. Every chain table is the recorder's - the access log too, since a viewer that names a recorder hands it each access and requeue as an act - and so is recorder_documents, so a viewer connected as its role can neither append evidence nor forge or erase the recorder's record that an act was written or refused. Give that viewer BITEXACT_RECORDER: without a recorder it has no one to write its access events. On the file backend the recorder's documents live under recorder/ in the data directory, apart from every other document.

Entries are stored as text, not jsonb#

Deliberate, and worth stating plainly because it looks like a missed opportunity. PostgreSQL jsonb cannot represent a NUL character at all. A model response or tool output containing one is not hypothetical, and storing evidence as jsonb would make the recorder fail closed on exactly the payload it exists to capture.

The body column therefore holds the same JSON line the file backend writes — byte for byte, NUL-safe, surrogate-safe — so recorded evidence is identical across backends and export stays faithful. What the chain verifies is canonical bytes derived on read, so nothing is lost. Queryable fields (kind, ts, entry_hash, prev_hash) are written alongside as typed columns and indexed.

Concurrency#

Each append places and persists its link in one transaction. Writers serialize on the run's own head row (SELECT … FOR UPDATE), so runs never contend with each other, and two writers on the same run cannot both claim a step. One recorder per run is enforced across processes by a session advisory lock; a second recorder fails loudly rather than interleaving into the same chain.

Connections come from an app-side pool. A transaction pooler such as PgBouncer is not required and is not recommended until multiple service replicas approach max_connections, because session advisory locks and SET LOCAL lose their meaning behind one.

Guards#

Rule	Enforcement
A recorded link cannot move	trigger: run, step, hash, prev are immutable
Entries cannot be deleted	trigger: append-only, except a sanctioned purge
Attestations cannot change	trigger: sidecars reject update and delete
A sealed run cannot be reopened	trigger: no insert after a `run_end`
A legal hold outranks retention	trigger: purge refuses a run whose chain carries a `hold` with no `hold_release` after it

Redaction stays possible because it must — destroying a value on request is a legal obligation. The guard permits the payload to change while pinning the columns that fix the run's shape, which is exactly the invariant the chain relies on: a redacted entry keeps its place and its hash, and the surviving commitment still proves what the value was.

One schema, no migrations#

There is one schema and no migration chain. Schema creation is serialized by an advisory lock, so several services starting at once against an empty database create it exactly once. A database that already holds the schema is used as it is. A database that holds only part of it was not created by this build: the service refuses to start and names the missing tables, rather than failing on its first write. Point BITEXACT_DATABASE_URL at an empty database, or move existing evidence with bitexact db-transfer. Recovery is point-in-time restore.

Retention: a hot window and an archive#

Records have to be kept for as long as the regime demands — around seven years across most of finance, twenty-five in clinical research, life insurance and paediatric health. Keeping all of that in a live database is the wrong shape: it grows without bound, it slows the queries an inspector actually runs, and it leaves decades-old evidence where an operator can reach it.

run_entries is partitioned by the month a run started, so every entry of a run lands in the same partition however long the run lives and a run can always be moved or dropped as one piece.

Beyond the hot window (BITEXACT_HOT_MONTHS, default 24) a run is exported, its bundle written to the archive, and its rows dropped:

bitexact archive --to /mnt/worm/bitexact-archive
bitexact archive --to /mnt/worm/bitexact-archive --no-drop     # write only
bitexact archive --to /mnt/worm/bitexact-archive --before 2020-01

Point --to at immutable storage — object-lock, a WORM appliance, or an archival tier. The bundles written there are the long-term evidence. They verify with the standalone verifier and no database, which is exactly why cold evidence can leave the store and still be evidence.

What stays behind is the catalog (archived_runs): one small row per archived run holding its workflow, dates, chain head, bundle location and digest. Small enough to keep for twenty-five years, and enough for an auditor's "show me that run from 2021" to end in a file rather than a shrug.

Order is not negotiable: the run is exported, the written bundle is read back and checked, the catalog records it, and only then are the rows dropped. Nothing is dropped if any run in the month failed to archive, and a legal hold refuses the drop outright — a hold outranks retention, and a bulk drop is exactly where that would otherwise be forgotten.

Backup and restore#

Use continuous archiving (pgBackRest or equivalent) for point-in-time recovery. The restore test that matters is not "does the database start" but does a bundle exported from the restored database still verify offline — run that after any restore drill. Compare restored heads against the external anchors as well: the anchors are what prove the restore did not silently roll history back.