PostgreSQL refuses CREATE OR REPLACE FUNCTION when the owning role has no
EXECUTE on it, which is the CNPG shape (the runtime role owns the schema
and worm-audit:6 revokes its EXECUTE). worm-audit:13 now grants EXECUTE
back to the migrating role only when missing, replaces the function, and
revokes it again inside the same changeset transaction.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
Liquibase formatted SQL parses any line beginning with '-- changeset' as a
directive, so the worm-audit:13 comment broke the changelog. Reword it and
guard the rendered DDL in CanonicalAndCryptoTest.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
The chain writer re-read the whole late-detail lookback every 30 s and
probed the GIN array index once per detail (18.4 s, 1.78M buffer hits per
tick in production), and the coverage trigger's EXISTS overlap check ran as
a cached generic plan that sequentially scanned worm_audit_chain for every
inserted link.
- worm-audit:13: worm_audit_chain_detail (detail_id PK, chain_id), one
append-only row per covered detail, written by the chain trigger in the
same statement as the link and backfilled under the chain advisory lock.
WORM guards and grants like the other tables; no foreign keys.
- The trigger inserts coverage rows and lets the primary key reject a
detail covered twice. The purge deletes coverage before its links.
- lateDetails probes the coverage primary key; both sweeps read only ids
above per-JVM sweep floors, with a full sweep after restart and hourly.
- Verification aggregates the chain's own detail lists once and breaks if
the coverage table disagrees. Link hash inputs are unchanged.
- Memory and H2 backends: the writer records coverage rows itself.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
worm-audit:11 indexes and worm-audit:12 adds foreign keys, ON DELETE
RESTRICT only, so nothing cascades into or out of audit evidence:
- worm_audit_chain.audit_id -> core audit header. worm_audit_purge deletes
details, then links, then headers (links and headers in one statement),
which the constraint allows; the purge acceptance test passes with it.
- worm_audit_export.from/to_checkpoint_id -> worm_audit_checkpoint and
worm_audit_verification.export_id -> worm_audit_export: neither parent is
ever deleted, and both are written before the row that names them.
References to chain links (checkpoint/export/verification ranges,
first_break_link_id, the purge anchor) stay unconstrained: the retention
purge deletes the sealed prefix those rows outlive. The guardrail test
lists them with reasons and fails on any new unconstrained reference.
Rows whose parent is already missing are evidence: they are copied to
qqq_fk_quarantine (action KEPT), never changed, and leave that constraint
NOT VALID (still enforced for new rows). The block is idempotent, as the
rendered DDL is applied more than once.
With the key, a plain TRUNCATE of a referenced table is refused by
PostgreSQL before the guard trigger; TRUNCATE ... CASCADE reaches the
guards. The grants test now checks both.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
Owner decisions 2026-10-14:
- worm_audit_settings: one audited, editable row (changeset worm-audit:10,
seeded insert-only) holding audit retention years, export lock mode
(GOVERNANCE default, COMPLIANCE), export retention days (30), checkpoint
cadence, chain interval, settle horizon, late-detail lookback and the
export/verify schedules; edits are FIELD-audited and therefore chained;
schedules apply after restart, the rest on the next run (30 s cache);
- exporter and verifier honour the recorded lock mode and retain-until;
the home shows the mode and warns while GOVERNANCE is in use or the
export retention is shorter than audit retention;
- README: Settings and Switching sealed exports to COMPLIANCE; DESIGN
addendum; screenshots of the home and the Settings record.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
Phase (b) of docs/DESIGN.md:
- daily and on-demand exports of contiguous chain ranges up to the latest
signed checkpoint: links.jsonl, checkpoints.jsonl and a manifest that
chains to the previous export, each put through qbit-object-storage with
COMPLIANCE retention (default 6 years) and checked by head; sealed
object version ids are recorded;
- refuses and alerts (worm_audit.export.failed) on buckets without Object
Lock or without versions; heartbeat on scheduled runs;
- Verify sealed export: manifest hashes, lock and retention, the manifest
chain, link and checkpoint recomputation, overwrite detection against the
pinned versions, and comparison with the live chain and audit rows;
- MinIO Testcontainers round trip, retention, delete refusal, overwrite and
live tamper detection, and the unsupported-bucket refusal.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
Phase (a) of docs/DESIGN.md:
- Postgres DDL template and renderer: write-once triggers on the core
audit tables and worm_audit_* tables, lookup id/name protection, the
SECURITY DEFINER retention purge (call-stack guarded, chain prefix only,
recorded) and the INSERT/SELECT-only grant model;
- capability guard and a clear write-once error;
- RFC 8785 canonical audit content, a single-writer chainer (advisory
lock, settle horizon, contiguous ids, late arrivals and late details),
Ed25519 checkpoints with Merkle roots;
- a paged verifier naming the exact break, the Verify audit chain table
action, and the daily verification with QAlerts break/heartbeat;
- the assistant deny pattern for worm_audit_* (reflective until RC.6);
- tests: Postgres Testcontainers (triggers, grants, purge, tamper,
concurrency, time zone), PHI canary, chain and checkpoint units.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
james.maesandClaude Opus 5.5