Skip to main content

ADR-0064: DB-enforced audit_events immutability (retention-first)

Status: Accepted Date: 2026-06-16 Supersedes: none Superseded by: none Deciders: Adrian (Soft4U), Claude Opus 4.8

Decision context:

  • Latency: a per-row BEFORE UPDATE/DELETE trigger check (negligible) + one FK swap; an audit-count pre-check on the rarely-used hard-delete endpoint.
  • Dependency surface: no new packages. Migration 066 (trigger + function + REVOKE + FK alter); a 409 guard in delete_case.
  • Debuggability: tampering attempts raise a clear, regulation-citing exception; case-delete blocks with a clear 409.
  • Reversibility: alembic downgrade -1 drops the trigger/function and restores the CASCADE FK.
  • Blast radius: the hard-delete-case endpoint now 409s for cases with audit history (deliberate); normal ops + INSERT unaffected.
  • Alternative considered: REVOKE-only / trigger-only / GUC-gated purge (see Alternatives).

Context​

audit_events was append-only by convention only — no REVOKE, no trigger; trustrelay_app held UPDATE/DELETE grants. AMLR Art. 77 (5-year record-keeping) + EU AI Act Art. 12 require a tamper-evident audit log. A complication: migration 042 made the cases → audit_events FK ON DELETE CASCADE, and a live DELETE /cases/{workflow_id} endpoint relies on it — so a naive DELETE-blocking trigger breaks case deletion mid-cascade.

Decision (retention-first)​

The audit trail is immutable and retained:

  1. Trigger audit_events_immutable() — BEFORE UPDATE OR DELETE on audit_events, unconditional RAISE EXCEPTION (fires for ALL roles incl. superuser).
  2. REVOKE UPDATE, DELETE on audit_events from trustrelay_app (least-privilege; guarded for a missing role in CI/local).
  3. FK CASCADE → RESTRICT (cases → audit_events): a case with audit history cannot be hard-deleted; the audit trail is retained. This makes the schema itself express the retention intent and yields a clean FK error rather than a confusing mid-cascade trigger error.
  4. Graceful endpoint: delete_case pre-checks for audit rows and returns 409 with a clear retention message (before terminating the workflow / purging MinIO — so a blocked delete is atomic), instead of a raw 500. Cases with history are closed/soft-deleted, not erased.
  5. Migration DDL runs under the migration/superuser URL (ADR-0050). A deliberate correction would require a superuser to drop the trigger — itself a traceable action.

Consequences​

Positive​

  • The audit log is tamper-evident and retained at the DB layer — defence-in-depth beyond app discipline; satisfies AMLR Art. 77 / EU AI Act Art. 12. Aligns with AML records being exempt from GDPR erasure.

Negative​

  • Hard-deleting a case with audit history is no longer possible (409) — intended; consumers must soft-delete/close. A legitimate audit correction requires a deliberate superuser action.

Neutral​

  • INSERT (append) is unaffected. The stretch before/after-diff schema on details is deferred.

Alternatives Considered​

Alternative 1: REVOKE-only (no trigger)​

Rejected — a superuser/DBA or a mis-granted role could still UPDATE/DELETE; the trigger catches everyone.

Alternative 2: Trigger-only (no REVOKE)​

Rejected — least-privilege matters; the app role should not even hold the grant.

Alternative 3: GUC-gated purge (allow DELETE when an app.allow_audit_purge GUC is set)​

Rejected — preserves the hard-delete endpoint but weakens the immutability guarantee and contradicts AMLR retention (the audit trail should survive case deletion). Retention-first (FK RESTRICT + 409) is the compliant posture; cases are soft-deleted, audit history is kept.