feat: append-only audit tables, hash chain, checkpoints and verification #6

2026-10-14T01:28:28Z

KofTwentyTwo commented

2026-10-14 01:28:28 +00:00

(Migrated from github.com)

Summary

Phase (a) of docs/DESIGN.md: append-only enforced in the database, the hash chain, signed checkpoints and verification.

DDL (ddl/postgres/worm-audit.sql, rendered default docs/ddl/worm-audit-postgres.sql, WormAuditDdl):
- write-once row and TRUNCATE triggers on audit, audit_detail and the five worm_audit_* tables, with the error worm_audit: audit rows are write-once; UPDATE of "audit" is not allowed (id=…);
- lookup tables: id and name changes are rejected;
- grants: the app role gets INSERT and SELECT only;
- worm_audit_purge(), a SECURITY DEFINER function executable only by worm_audit_purger. It purges only after retention and only a chain prefix, records PURGED and REFUSED rows, and the trigger also checks PG_CONTEXT, so a single-role host can't spoof the flag.
- The DDL assumes snake_case columns (documented).
Guard: update and delete capabilities are removed from these tables in QQQ, and Postgres P0001 maps to a user-facing error.
Chain:
- RFC 8785 canonical content (§5.1, agreed with secured-data);
- a single-writer chainer: advisory lock, settle horizon, contiguous ids, late arrivals and late details through a GIN-indexed coverage check, batched reads;
- Ed25519 checkpoints with Merkle roots, capped per checkpoint.
Verify:
- a verifier paged by keyset with bounded memory, plus SQL completeness anti-joins, naming the exact break;
- "Verify audit chain", a streamed-ETL table action with Review/Result lines;
- the daily scheduled verification, which raises and resolves worm_audit.chain.break and sends a heartbeat through core QAlerts.
Assistant wall: worm_audit_* is registered reflectively until the qbit builds on RC.6. Core default: qqq#1163 (merged).

Stand-ins:

the secured-data audit token hook is mimicked in PhiCanaryTest; I'll switch to the real AuditValueProtectorInterface once the core PR is up (secured-data agent);
ClientChannelsMetaData is called reflectively (RC.6).

Tests (CI=true ./mvnw verify locally with Docker): 50 tests pass. They include 7 Postgres 16 Testcontainers tests: triggers, grants, the purge (refusal, anchor, spoofed flag rejected), real-backend tamper detection, 8 writers with 3 chainers in parallel, and the America/Chicago JVM zone. Coverage is 95.5% instructions and 100% classes.

🤖 Generated with Claude Code

## Summary Phase (a) of `docs/DESIGN.md`: append-only enforced in the database, the hash chain, signed checkpoints and verification. - **DDL** (`ddl/postgres/worm-audit.sql`, rendered default `docs/ddl/worm-audit-postgres.sql`, `WormAuditDdl`): - write-once row and TRUNCATE triggers on `audit`, `audit_detail` and the five `worm_audit_*` tables, with the error `worm_audit: audit rows are write-once; UPDATE of "audit" is not allowed (id=…)`; - lookup tables: id and name changes are rejected; - grants: the app role gets INSERT and SELECT only; - `worm_audit_purge()`, a SECURITY DEFINER function executable only by `worm_audit_purger`. It purges only after retention and only a chain prefix, records PURGED and REFUSED rows, and the trigger also checks `PG_CONTEXT`, so a single-role host can't spoof the flag. - The DDL assumes snake_case columns (documented). - **Guard:** update and delete capabilities are removed from these tables in QQQ, and Postgres P0001 maps to a user-facing error. - **Chain:** - RFC 8785 canonical content (§5.1, agreed with secured-data); - a single-writer chainer: advisory lock, settle horizon, contiguous ids, late arrivals and late details through a GIN-indexed coverage check, batched reads; - Ed25519 checkpoints with Merkle roots, capped per checkpoint. - **Verify:** - a verifier paged by keyset with bounded memory, plus SQL completeness anti-joins, naming the exact break; - "Verify audit chain", a streamed-ETL table action with Review/Result lines; - the daily scheduled verification, which raises and resolves `worm_audit.chain.break` and sends a heartbeat through core `QAlerts`. - **Assistant wall:** `worm_audit_*` is registered reflectively until the qbit builds on RC.6. Core default: qqq#1163 (merged). **Stand-ins:** - the secured-data audit token hook is mimicked in `PhiCanaryTest`; I'll switch to the real `AuditValueProtectorInterface` once the core PR is up (secured-data agent); - `ClientChannelsMetaData` is called reflectively (RC.6). **Tests** (`CI=true ./mvnw verify` locally with Docker): 50 tests pass. They include 7 Postgres 16 Testcontainers tests: triggers, grants, the purge (refusal, anchor, spoofed flag rejected), real-backend tamper detection, 8 writers with 3 chainers in parallel, and the America/Chicago JVM zone. Coverage is 95.5% instructions and 100% classes. 🤖 Generated with [Claude Code](https://claude.com/claude-code)

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: QRun-IO/qbit-worm-audit#6