ADR 0014: ALCOA++ audit envelope, database-computed hashes and an append-only application role

Status: Accepted
Date: 2026-09-14
Deciders: Founder

Context

audit_events recorded action, target, actor user id and a diff, with SET NULL foreign keys and an application role that owned the schema. Agents acting on a person’s approval, signatures with meaning, inspection-style export and tamper evidence were not representable. The regulatory frame (E6(R3) 4.2, PIC/S PI 041 section 9.6, 21 CFR 11.10(e)) expects attributable, time-stamped, reason-bearing, unalterable entries; the pilot is positioned as non-GxP but the schema must not need a rewrite later.

Decision

Envelope columns on audit_events: actor_kind human|agent|system, actor_agent_id, agent_run_id, interrupt_id, approved_by_user_id, signature_id, reason, proposal_hash, approved_hash, occurred_at; entry_seq, row_hash and created_at are set by a BEFORE INSERT trigger (row_hash = sha256(JCS(row minus entry_seq and row_hash)), RFC 8785 canonical JSON produced in SQL; the exporter and the verifier use the same function).
Every FK on audit_events, audit_chain and signatures is ON DELETE NO ACTION: users are deactivated and programs archived, never deleted.
Two immutability controls: BEFORE UPDATE OR DELETE triggers that raise, and a role split. The application connects as trovensa_app, which can only SELECT and INSERT on audit, chain and signature tables; the owner role is used by the migrate Job only. release.sh refuses the 0002 migrate Job while the api or worker DSN names the owner role.
Signatures: signer_user_id NOT NULL (agents are not users), meaning derived by the server from the route, record_hash bound to canonical content, re-authentication with auth_time within 300 s and single use; signed records refuse updates (409 record_signed) and change only by a new version.
A per-organisation hash chain (audit_chain, R2) links entries off the request path with daily checkpoint anchors.

Consequences

write_audit gains the envelope keyword arguments and every mutation passes actor_kind.
Local development runs on the same role split (compose creates both roles); 0002 guards its GRANT statements so make check works without the role.
Exports (GET P/audit/export, /orgs/current/audit/export) carry entry_seq and row_hash and a verifier can recompute both.

Alternatives considered

Application-computed hashes: the application could write inconsistent hashes and key order; rejected in favour of the trigger.
Triggers alone: while the application owns the schema it can disable them; rejected as the only control.
External WORM storage from day one: valuable later; the chain plus role split is proportionate for a non-GxP pilot.

Follow-ups

Quarterly restore test that recomputes the chain and signature validity (light validation package evidence).
Consider an external anchor for daily checkpoints (R3).