feat(ddl): RESTRICT foreign keys for chain, export and verification #11

2026-10-14T21:33:27Z

KofTwentyTwo commented

2026-10-14 21:33:27 +00:00

(Migrated from github.com)

Summary

Referential integrity for the WORM tables, ON DELETE RESTRICT only (nothing cascades into or out of evidence). New changesets worm-audit:11 (indexes) and worm-audit:12 (one idempotent DO block) in the template and the rendered docs/ddl/worm-audit-postgres.sql.

Child column	Parent	Why it is safe
`worm_audit_chain.audit_id`	core `audit` header	The chainer only links committed headers. `worm_audit_purge` deletes details, then links and headers (one CTE statement); the RI check runs at end of statement. Verified by the existing purge acceptance test and on a kof22-shaped schema.
`worm_audit_export.from_checkpoint_id`, `to_checkpoint_id`	`worm_audit_checkpoint`	Checkpoints are never deleted. An export is only stored after `inspect` passes, which requires real checkpoint ids (the `0` sentinel can never be persisted).
`worm_audit_verification.export_id`	`worm_audit_export`	Exports are never deleted; `SealedExportVerifier.verify` saves its run for a stored export.

Not FKs, by design (listed with reasons in the guardrail test): checkpoint/export/verification link ranges, first_break_link_id and worm_audit_purge.anchor_link_id. The retention purge deletes the sealed prefix of links these rows outlive, and the anchor is the last link that same purge deleted.

Orphans on upgrade are evidence: they are copied to qqq_fk_quarantine (action KEPT, full row JSON), never updated or deleted, and that one constraint is left NOT VALID so new rows are still checked. The quarantine DDL matches the other qbits'.

Behaviour note: a plain TRUNCATE audit (or checkpoints or exports) is now refused by PostgreSQL's FK check before the guard trigger runs; TRUNCATE ... CASCADE reaches the guards, which refuse it. grantsTriggersLookupRulesAndSpoofedFlag now asserts both.

Tests

everyReferenceHasARestrictForeignKeyOrAReason: every possible-value reference of the WORM tables, plus the chain's audit header, has a validated RESTRICT FK or an exemption with a reason. Red without worm-audit:11-12 (4 references reported). Green with them.
orphanEvidenceIsKeptAndTheConstraintIsNotValidated: a link whose header is gone is quarantined as KEPT and left unchanged; the constraint is NOT VALID and still refuses a new orphan; re-applying the DDL is a no-op.
All PostgreSQL acceptance tests (purge, refusal, anchor, tamper, concurrent writers and chainers) and the MinIO export acceptance test pass with the FKs. ./mvnw verify green locally.

🤖 Generated with Claude Code

## Summary Referential integrity for the WORM tables, `ON DELETE RESTRICT` only (nothing cascades into or out of evidence). New changesets `worm-audit:11` (indexes) and `worm-audit:12` (one idempotent `DO` block) in the template and the rendered `docs/ddl/worm-audit-postgres.sql`. | Child column | Parent | Why it is safe | |---|---|---| | `worm_audit_chain.audit_id` | core `audit` header | The chainer only links committed headers. `worm_audit_purge` deletes details, then links and headers (one CTE statement); the RI check runs at end of statement. Verified by the existing purge acceptance test and on a kof22-shaped schema. | | `worm_audit_export.from_checkpoint_id`, `to_checkpoint_id` | `worm_audit_checkpoint` | Checkpoints are never deleted. An export is only stored after `inspect` passes, which requires real checkpoint ids (the `0` sentinel can never be persisted). | | `worm_audit_verification.export_id` | `worm_audit_export` | Exports are never deleted; `SealedExportVerifier.verify` saves its run for a stored export. | **Not FKs, by design** (listed with reasons in the guardrail test): checkpoint/export/verification link ranges, `first_break_link_id` and `worm_audit_purge.anchor_link_id`. The retention purge deletes the sealed prefix of links these rows outlive, and the anchor is the last link that same purge deleted. **Orphans on upgrade** are evidence: they are copied to `qqq_fk_quarantine` (action `KEPT`, full row JSON), never updated or deleted, and that one constraint is left `NOT VALID` so new rows are still checked. The quarantine DDL matches the other qbits'. **Behaviour note:** a plain `TRUNCATE audit` (or checkpoints or exports) is now refused by PostgreSQL's FK check before the guard trigger runs; `TRUNCATE ... CASCADE` reaches the guards, which refuse it. `grantsTriggersLookupRulesAndSpoofedFlag` now asserts both. ## Tests - `everyReferenceHasARestrictForeignKeyOrAReason`: every possible-value reference of the WORM tables, plus the chain's audit header, has a validated RESTRICT FK or an exemption with a reason. Red without `worm-audit:11-12` (4 references reported). Green with them. - `orphanEvidenceIsKeptAndTheConstraintIsNotValidated`: a link whose header is gone is quarantined as KEPT and left unchanged; the constraint is NOT VALID and still refuses a new orphan; re-applying the DDL is a no-op. - All PostgreSQL acceptance tests (purge, refusal, anchor, tamper, concurrent writers and chainers) and the MinIO export acceptance test pass with the FKs. `./mvnw verify` green locally. 🤖 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#11