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 (c) of docs/DESIGN.md:
- the WORM Audit app and home: chain status, export status, retention,
recent purges and setup warnings (missing triggers, owner role, signing
and trusted keys, bucket without Object Lock, stale verification), plus
a summary card for host dashboards; no audit content is rendered;
- alert types renamed to wormaudit.* (core name pattern) and declared,
with daily heartbeat monitors;
- full verification holds one page plus one checkpoint range;
- plain field labels, record labels, full-width sections and child lists;
- a test-scope demo server and screenshots (home and records, light and
dark, 390/1440/1920) with overflow checks;
- DESIGN.md consistency edits (alert names, object_versions,
s3:GetObjectVersion, snake_case columns).
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