Storage

In server mode the audit trail lives in a store chosen with COUNTERSIGN_STORAGE. A store keeps the entries and nothing else; the records are what replaying the entries gives (see Architecture). This page compares the four adapters, covers PostgreSQL in depth, and explains concurrency, moving between stores and writing an adapter.

Contents

• The adapters compared
• PostgreSQL
  • Schema
  • Triggers
  • The reporting view
  • Roles and privileges
  • Connection string, pool and TLS
  • Backup and restore
• SQLite
• File
• Memory
• How concurrency is handled
• Moving from one store to another
• Writing your own adapter

The adapters compared

All four pass the same conformance tests (server/server.test.ts, requirements FR-STO-01 to FR-STO-05).

PostgreSQL

PostgreSQL 12 or newer; developed and tested against PostgreSQL 16.

export COUNTERSIGN_STORAGE=postgres
export COUNTERSIGN_DATABASE_URL=postgres://countersign_app:<password>@db.example.com:5432/countersign

Schema

The schema is the file db/postgres.sql. The server reads that file from disk and runs it when it starts, so the file a DBA reviewed is the one that runs; every statement in it is idempotent. Several servers starting at once take a PostgreSQL advisory lock (pg_advisory_lock(727001)) so that only one runs it at a time.

CREATE TABLE IF NOT EXISTS countersign_entries (
  seq         BIGINT      PRIMARY KEY CHECK (seq > 0),
  at          TIMESTAMPTZ NOT NULL,
  actor       TEXT        NOT NULL,
  action      TEXT        NOT NULL,
  record_id   TEXT        NOT NULL,
  record_type TEXT        NOT NULL,
  prev_hash   CHAR(64)    NOT NULL,
  hash        CHAR(64)    NOT NULL UNIQUE,
  entry       TEXT        NOT NULL,
  inserted_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Indexes: countersign_entries_record (record_id, seq) for a record's history, countersign_entries_at (at) for time ranges, besides the primary key and the unique index on hash.

The file is found in this order: COUNTERSIGN_SCHEMA_FILE if set, db/postgres.sql relative to the adapter's source file, db/postgres.sql in the working directory. If it is not found the server says db/postgres.sql was not found. Set COUNTERSIGN_SCHEMA_FILE to its path, or run it by hand and set COUNTERSIGN_SKIP_MIGRATE=1.

Triggers

Tried against a database with the roles of the next section:

$ psql "postgres://countersign_owner:…@localhost/countersign" -c "UPDATE countersign_entries SET actor='mallory' WHERE seq=2"
ERROR:  countersign: the audit trail is append-only (UPDATE refused)
CONTEXT:  PL/pgSQL function countersign_entries_append_only() line 3 at RAISE
$ psql "postgres://countersign_owner:…@localhost/countersign" -c "TRUNCATE countersign_entries"
ERROR:  countersign: the audit trail is append-only (TRUNCATE refused)
CONTEXT:  PL/pgSQL function countersign_entries_append_only() line 3 at RAISE
$ psql "postgres://countersign_app:…@localhost/countersign" -c "INSERT INTO countersign_entries (seq, at, actor, action, record_id, record_type, prev_hash, hash, entry) VALUES (999, now(), 'm', 'create', 'X', 'x', repeat('0',64), repeat('1',64), '{}')"
ERROR:  countersign: entry 999 does not continue the chain
CONTEXT:  PL/pgSQL function countersign_entries_chain() line 9 at RAISE

The triggers stop mistakes and a compromised application. They do not stop the table's owner or a superuser, who can disable them (ALTER TABLE … DISABLE TRIGGER). What happens then is described under Operations: integrity: the change is detected by the hash check, and a full rewrite is detected only by a head hash kept outside the database.

The reporting view

countersign_audit_trail presents the trail for reporting tools, one row per entry. Every column except the last is taken from the hashed entry JSON itself, not from the table's convenience columns, so the view shows exactly what the hash chain covers:

Example queries, with their output on the fleet demo data.

The history of one record:

SELECT seq, at, actor_name, action, record_id, version, reason
FROM countersign_audit_trail WHERE record_id = 'OV-0019' ORDER BY seq;

 seq |             at             |  actor_name  | action  | record_id | version |                             reason
-----+----------------------------+--------------+---------+-----------+---------+-----------------------------------------------------------------
 254 | 2026-09-29 15:09:49.629+00 | Alice Brandt | create  | OV-0019   |       1 |
 255 | 2026-09-30 13:51:43.422+00 | Alice Brandt | confirm | OV-0019   |       1 |
 256 | 2026-10-01 12:33:37.215+00 | Lena Fischer | approve | OV-0019   |       1 |
 275 | 2026-10-19 14:25:53.696+00 | Bob Okafor   | edit    | OV-0019   |       2 | Manufacturer bulletin 2026-14 allows 12,500 km for this engine.

Approvals per person since a date:

SELECT actor_name, count(*) AS approvals
FROM countersign_audit_trail
WHERE action = 'approve' AND at >= '2026-10-01'
GROUP BY actor_name ORDER BY approvals DESC;

  actor_name  | approvals
--------------+-----------
 Kai Tanaka   |         3
 Lena Fischer |         3
 Bob Okafor   |         2
 Alice Brandt |         1

The latest signatures with their meaning and how the signer was authenticated:

SELECT seq, at, actor_name, record_id, signature_meaning, auth ->> 'method' AS method
FROM countersign_audit_trail WHERE action = 'approve' ORDER BY seq DESC LIMIT 3;

 seq |             at             |  actor_name  | record_id |                  signature_meaning                  | method
-----+----------------------------+--------------+-----------+-----------------------------------------------------+--------
 284 | 2026-10-28 07:06:43.419+00 | Kai Tanaka   | VH-0042   | I approve retiring this record.                     |
 278 | 2026-10-22 07:55:20.661+00 | Alice Brandt | VH-0026   | I have reviewed this change and approve it for use. |
 274 | 2026-10-19 14:25:52.696+00 | Lena Fischer | OV-0025   | I have reviewed this change and approve it for use. |

(method is empty here because the demo history is generated, not signed in; entries written through the server carry it.)

An independent check of four eyes, which must return no rows:

SELECT a.seq, a.record_id, a.actor_name AS approver, c.actor_name AS author
FROM countersign_audit_trail a
JOIN countersign_audit_trail c
  ON c.record_id = a.record_id AND c.version = a.version AND c.action IN ('create', 'edit', 'retire', 'remove')
WHERE a.action = 'approve' AND a.actor_id = c.actor_id;

The head, for anchoring:

SELECT seq, hash FROM countersign_entries ORDER BY seq DESC LIMIT 1;

The view reports what is stored. It does not verify anything; use verify for that. Give reporting users SELECT on the view only.

The schema file defines the view with CREATE OR REPLACE VIEW, so privileges granted on it survive when the file is run again. The application itself does not use the view.

Roles and privileges

Use two roles: an owner that creates the schema, and an application role that can add and read entries and nothing else. With that split, the application's credentials cannot change or delete history even if the triggers were absent.

As a superuser:

CREATE ROLE countersign_owner LOGIN PASSWORD '<owner password>';
CREATE ROLE countersign_app   LOGIN PASSWORD '<application password>';
CREATE DATABASE countersign OWNER countersign_owner;
REVOKE ALL ON DATABASE countersign FROM PUBLIC;
GRANT CONNECT ON DATABASE countersign TO countersign_app;

As countersign_owner, in the new database, create the schema from the reviewed file and grant the application its two privileges:

psql "postgres://countersign_owner:<owner password>@db.example.com/countersign" -v ON_ERROR_STOP=1 -f db/postgres.sql

GRANT USAGE ON SCHEMA public TO countersign_app;
GRANT SELECT, INSERT ON countersign_entries TO countersign_app;
GRANT SELECT ON countersign_audit_trail TO countersign_app;

Then run the server and the CLI as countersign_app with

export COUNTERSIGN_SKIP_MIGRATE=1

so that they do not try to run the schema file themselves. Without it the application role fails at start, because it may not create objects:

$ npm run cli -- status
countersign: permission denied for schema public

With the grants in place the application role is refused before the triggers are even reached:

$ psql "postgres://countersign_app:…@localhost/countersign" -c "DELETE FROM countersign_entries WHERE seq=290"
ERROR:  permission denied for table countersign_entries

Notes:

• No sequence is involved (seq is supplied by the application), so no sequence privilege is needed.
• A read-only role for reporting or auditors: GRANT USAGE ON SCHEMA public and GRANT SELECT ON countersign_audit_trail.
• Keep the owner's password out of the application's environment. The owner is needed again only for an upgrade that changes db/postgres.sql. Grants on the table and the view survive a re-run of the file.
• If you let the server create the schema itself (no COUNTERSIGN_SKIP_MIGRATE), it must connect as a role that may create tables, functions and triggers, and it then owns the table. That is acceptable for evaluation; for production prefer the split.

Connection string, pool and TLS

TLS is configured in the connection URI and handled by the pg driver (node-postgres). Commonly used parameters are sslmode (require, verify-ca, verify-full), sslrootcert (path of the CA certificate), and sslcert / sslkey for client certificates:

postgres://countersign_app:<password>@db.example.com:5432/countersign?sslmode=verify-full&sslrootcert=/etc/ssl/db-ca.pem

The exact meaning of the sslmode values has changed between versions of the driver (in pg 8 sslmode=require verifies the server certificate unless uselibpqcompat=true is added). Check the node-postgres documentation for the version in your package-lock.json, and test that a connection to a server with a wrong certificate fails. In production use verify-full with the CA of your database.

Backup and restore

The whole system state is one table. A backup of the database is a backup of everything.

Backup (as the owner or another role that can read the table):

pg_dump "postgres://countersign_owner:<owner password>@db.example.com/countersign" --format=custom --file=countersign.dump
npm run cli -- status        # note "entries" and "head hash" at the time of the backup

Restore into an empty database:

psql -c "CREATE DATABASE countersign_restore OWNER countersign_owner"
pg_restore --dbname="postgres://countersign_owner:<owner password>@db.example.com/countersign_restore" --no-owner countersign.dump

pg_restore loads the rows before it creates the triggers, so the restore is not slowed down or blocked by them, and the triggers, the view and the grants to countersign_app are back afterwards (the roles must exist on the target server). A restore into a database that already holds entries is refused by the triggers; always restore into an empty database.

After every restore, verify, with the head hash you noted or anchored:

$ COUNTERSIGN_DATABASE_URL=postgres://countersign_app:…@db.example.com/countersign_restore npm run cli -- verify \
    --expect-head 1780d6e781351665621ddf081638c81c089441d8fa814422e8d4dc73879ff57d
entries read    290
hash chain      intact
replay          all 290 entries reproduce
head hash       1780d6e781351665621ddf081638c81c089441d8fa814422e8d4dc73879ff57d
expected head   matches

What to check:

Point-in-time recovery (WAL archiving) and streaming replication work as for any PostgreSQL database and are the way to reduce what a restore loses. Running servers that had already read entries the restore lost will report an integrity fault (the store holds N entries, fewer than the M already read: entries were removed) and refuse writes; restart them after the restore. The full runbook is in Operations.

SQLite

export COUNTERSIGN_STORAGE=sqlite
export COUNTERSIGN_DATA_FILE=/var/lib/countersign/countersign.sqlite   # default ./data/countersign.sqlite

• Uses Node's built-in node:sqlite; nothing to install. The folder is created if it is missing.
• The table and index are the same as in PostgreSQL (with SQLite's types). Triggers refuse UPDATE and DELETE (countersign: the audit trail is append-only) and rows that do not continue the chain. There is no reporting view.
• Opened with journal_mode = WAL, synchronous = FULL and a busy timeout of 5 seconds. Next to the file you will see -wal and -shm files while it is open; they belong to it.
• One machine. The CLI can work on the file while the server runs (SQLite locks per write), but do not put the file on a network file system and do not point servers on different machines at it.
• Backup: stop the server and copy the file (with its -wal and -shm files if they exist), or use SQLite's online backup (sqlite3 countersign.sqlite ".backup backup.sqlite"). Also export the trail as JSONL regularly; it is a complete, tool-independent copy (see below). Verify a restored file with verify --expect-head.
• The file is not encrypted; protect it with file permissions and disk encryption.

File

export COUNTERSIGN_STORAGE=file
export COUNTERSIGN_DATA_FILE=/var/lib/countersign/countersign.jsonl    # default ./data/countersign.jsonl

• One audit entry per line, as JSON, appended and flushed to disk (fsync) one at a time. The whole file is read into memory at start.
• Exactly the format that export --what audit --format jsonl writes. An exported trail can be used as a file store directly, and a file store can be read with any text tool.
• One server process only. There is no locking between processes: do not run two servers on the same file, and do not run CLI commands that write (import, config apply) while the server is running. Reading commands (status, verify, export) are safe at any time.
• Nothing prevents someone with write access from editing the file. Edits are detected by verify and at the next start, not prevented. Use operating-system permissions, and an anchored head hash.
• Backup: copy the file.

Memory

export COUNTERSIGN_STORAGE=memory

The trail lives in the server process and is lost when it stops. For the tests and for trying the server path (sign-in, API) without a database. Combine with COUNTERSIGN_CONFIG=preset:fleet and COUNTERSIGN_DEMO_DATA=1 to have something to look at. The CLI cannot reach it.

How concurrency is handled

The store interface has one writing method, and it is conditional:

append(entry: AuditEntry): Promise<boolean>   // false, and nothing stored, unless entry.seq === head + 1
                                              // and entry.prevHash === head hash

So two people who act at the same instant on two servers get entries N and N+1 in some order, each computed on the state before it. If the second action is no longer allowed after the first (both approved the same version), the second person gets the engine's refusal. The conformance test FR-STO-05 runs eight concurrent commands through two services on one store and checks that the numbers are 1 to 9 without gaps and the chain is unbroken.

Moving from one store to another

There is no load command in the CLI. export writes the audit trail; nothing reads one back into a database. The routes that exist today:

Same kind of store

Use the database's own tools: pg_dump / pg_restore between PostgreSQL servers, a file copy for SQLite and the file store. Verify afterwards with --expect-head.

Any store to a file store (supported by the CLI alone)

The JSONL export is a valid file store:

$ npm run cli -- status | grep "head hash"
head hash      7cdb4906fe01bb67f5e417696f80a7da76d3738b1445d2643fd21bc67741d708
$ npm run cli -- export --what audit --format jsonl --out /var/lib/countersign/countersign.jsonl
wrote /var/lib/countersign/countersign.jsonl
$ COUNTERSIGN_STORAGE=file COUNTERSIGN_DATA_FILE=/var/lib/countersign/countersign.jsonl npm run cli -- verify \
    --expect-head 7cdb4906fe01bb67f5e417696f80a7da76d3738b1445d2643fd21bc67741d708
entries read    16
hash chain      intact
replay          all 16 entries reproduce
head hash       7cdb4906fe01bb67f5e417696f80a7da76d3738b1445d2643fd21bc67741d708
expected head   matches

Any store to PostgreSQL or SQLite

Not a CLI command. Two ways that work with what is in the repository; both were tried while writing this page, and both end with the same verify.

With SQL (PostgreSQL target). Create the schema, load the JSONL lines into a staging table and insert them in order. The triggers check the chain row by row as the entries go in.

-- psql, connected as the owner to the new, empty database, after running db/postgres.sql
CREATE TEMP TABLE stage (line text);
\copy stage FROM 'trail.jsonl' WITH (FORMAT csv, QUOTE E'\x01', DELIMITER E'\x02')
INSERT INTO countersign_entries (seq, at, actor, action, record_id, record_type, prev_hash, hash, entry)
SELECT (j ->> 'seq')::bigint, (j ->> 'at')::timestamptz, j ->> 'actor', j ->> 'action',
       j ->> 'recordId', j ->> 'recordType', j ->> 'prevHash', j ->> 'hash', line
FROM (SELECT line, line::jsonb AS j FROM stage) s
ORDER BY 1;

(The odd QUOTE and DELIMITER make \copy take each line as one value without interpreting quotes or backslashes.)

With a short script against the Store interface (any target). Save it in scripts/, run it with the target store's COUNTERSIGN_* variables set. It is not part of the repository.

// scripts/load.ts: node scripts/load.ts trail.jsonl
import fs from "node:fs";
import { readSettings } from "../server/settings.ts";
import { openStore } from "../server/storage/index.ts";

const store = await openStore(readSettings());
await store.init();
if ((await store.head()).seq !== 0) throw new Error("The target store is not empty.");
for (const line of fs.readFileSync(process.argv[2], "utf8").split("\n").filter(Boolean))
  if (!(await store.append(JSON.parse(line)))) throw new Error(`Entry refused: ${line.slice(0, 60)}`);
console.log(await store.head());
await store.close();

The procedure around it

1. Stop all servers on the old store (or make it read-only).
2. status on the old store: note the entry count and head hash.
3. Export and load by one of the routes above.
4. verify --expect-head <noted hash> on the new store. The head hash is the same in every store, because it depends only on the entries.
5. Point the servers at the new store and start them. Keep the old store until the new one has been backed up.

Because moving a regulated system's records is a change, do it under change control and file the two status outputs and the verify output as evidence.

Writing your own adapter

A store implements five methods (server/storage/types.ts):

export interface Head {
  seq: number;   // number of the newest entry; 0 when the store is empty
  hash: string;  // its hash; 64 zeros when the store is empty
}

export interface Store {
  readonly kind: string;
  init(): Promise<void>;                                         // create tables or files; safe to call on every start
  head(): Promise<Head>;
  read(after: number, limit?: number): Promise<AuditEntry[]>;    // entries with seq > after, in order
  append(entry: AuditEntry): Promise<boolean>;                   // see below
  close(): Promise<void>;
}

The contract:

• append is atomic and conditional. It stores the entry and returns true only if entry.seq is exactly the head's seq plus one and entry.prevHash is the head's hash. Otherwise it stores nothing and returns false. The check and the write must not be separable by another writer: use the storage system's own conditional write (a unique key, a transaction, a compare-and-set). Errors other than "someone else was first" should be thrown.
• Store the entry losslessly. What read returns must hash to the same value. Keep the JSON text as given; do not let the storage system reformat numbers or drop properties. Key order may change (the hash sorts keys).
• Never update or delete. If the storage system can enforce that, make it.
• init is called on every start and by every CLI command; it must be idempotent and safe when several processes call it at once.

How to add it, with the tests, is in Developer setup.