Design Rationale — Audit
Non-obvious design choices for the audit module — the WHY behind each decision.
Audit Module (Locked 2026-06-10)
Per-tenant async hash chain — not global, not synchronous
Decision: audit_log rows carry sequence_number + row_hash + prev_hash chained per tenant (not a global sequence across all tenants). Hash linking is async: the row is inserted immediately with row_hash IS NULL; the ingest pipeline sets row_hash/prev_hash in a tight subsequent step.
Why: Tamper-evidence is the core requirement of an audit log — an editable log isn't an audit log. Per-tenant chaining avoids cross-tenant write serialization (a global chain would make every tenant's writes wait on every other tenant's). Async linking avoids blocking the insert on the hash computation.
Rejected: Global chain (serializes all tenants at high scale); synchronous chain (insert bottleneck under load); no chain at all (an audit log you can silently edit isn't one).
Guard: row_hash IS NULL is acceptable ONLY for the tail of a tenant's sequence — rows awaiting the async chain step within the expected SLA. A row where row_hash IS NULL and any later-sequenced row for the same tenant has row_hash set is a chain break — the daily verification job must alert. Do NOT treat NULL row_hash as benign without checking sequence position. The UNIQUE (tenant_id, sequence_number) guarantees ordering even before the hash links, so a gap is always detectable.
Reference-don't-copy — audit_log points at existing trails, never duplicates them
Decision: audit_log captures change events that have NO authoritative trail in another module. For domains with existing append-only trails (crm.customer_consent, identity.identity_access_event, inventory.stock_movement, billing.ar_payment_application, payments.stripe_event_log, pos.sale_line_tax), audit_log records a POINTER via source_table + source_ref — it does NOT copy or re-log the event. Single source of truth per trail.
Why: Re-logging consent, access, stock movements, and payment events would duplicate half the database and create two conflicting sources of truth for compliance queries. The pointer approach — one audit_log row with source_table = 'crm.customer_consent' and source_ref = <row uuid> — gives a unified timeline query surface without duplicating the authoritative trail.
Rejected: "Log everything into one surface regardless" — creates a two-source-of-truth problem the moment a correction or reconciliation is needed against the authoritative trail.
Guard: source_table IS NOT NULL means before_state and after_state MUST be NULL — enforced by a DB-level CHECK. Pointer mode and copy mode are mutually exclusive. Do NOT populate before_state/after_state when pointing at an existing trail — that would silently duplicate the data the guard exists to prevent. Do NOT remove this CHECK as a "simplification."
Store full, mask at read — PII in before_state/after_state, masking is a query layer
Decision: audit_log.before_state and after_state store the full entity state including any PII present at time of write. No write-time redaction. AuditService applies role-based PII masking at query time. granularity_level controls WHAT is logged (full / sensitive-only / minimal), not whether it is masked.
Why: A write-masked audit trail is forensically useless precisely when needed most — e.g., a breach investigation where you need to know exactly what data was visible at the moment of the event. Masking at write permanently destroys the evidence. Masking at read preserves forensic integrity while controlling who can see what.
Rejected: Write-time redaction (destroys forensic detail permanently).
Guard: audit_log holds historical PII by design — this is a deliberate architectural choice, not an oversight. Access is role-gated; masking is applied by AuditService at query time, not at the table level. Do not add write-time redaction "for safety" — it defeats the purpose of the audit log. granularity_level is about SCOPE of what's logged, not about masking.
subprocessor is its own table — not JSONB on dpa_agreement
Decision: audit.subprocessor is a first-class table with its own lifecycle, indexes, and status column. It is NOT a JSONB column or embedded array on dpa_agreement.
Why: GDPR Article 28 requires controllers to maintain and disclose a subprocessor list. Regulators and auditors request this list explicitly and specifically — it must be queryable, filterable by status, and linked to its governing DPA. It has its own lifecycle (added → pending review → active → removed) independent of any single DPA version. A JSONB-on-DPA approach would have no per-subprocessor indexing, no status lifecycle, and no way to list active subprocessors across all DPA versions.
Rejected: JSONB column on dpa_agreement (not queryable as a standalone list); embedding in dpa_agreement rows (one DPA can govern multiple subprocessors and a subprocessor can outlive a DPA version).
Audit owns compliance reporting — not the Reporting module
Decision: GDPR/SAR response exports, breach notification bundles, regulator-bound audit exports, and compliance task reports live in the Audit module (audit_export_job, compliance workflow tables). The Reporting module (Module 6) owns operational business-intelligence reports only.
Why: Compliance reporting requires access to the audit schema and must satisfy regulators, not business analysts. The audience (compliance officers, DPOs, regulators) and the data shape (full audit trails, legal-deadline tracking, signed tamper-evident exports) are categorically different from operational BI (sales trends, inventory turns, customer spend). Mixing them creates scope confusion and access-control complexity.
Guard: Do NOT build compliance reports in the Reporting module. If a report requires data from audit_log, data_subject_request, or data_breach_incident, it belongs in Audit. Reporting owns dashboards, sales analytics, and inventory reports — not regulator-facing documentation.
audit_log is insert-then-link-once — not flatly append-only, not insert-once-status-updated
Decision: audit_log has no updated_at and no deleted_at. Rows are never modified or deleted after the async hash-chain step completes. The sole post-insert write is row_hash/prev_hash transitioning NULL → value exactly once. This is "insert-then-link-once" — a distinct pattern.
Why: The hash chain requires a post-insert write to set row_hash and prev_hash — these cannot be computed before the row exists (the hash covers the row's own content). But after that one-time link, the row is permanently immutable. Calling this "append-only" is accurate for the operational lifecycle (no deletes, no status updates) but would be imprecise if interpreted as "zero post-insert writes."
Rejected: "Fully append-only with no post-insert writes" — impossible to implement an integrity chain that way; synchronous hash at insert is rejected on performance grounds. "Insert-once-status-updated" (the payments.stripe_event_log pattern) — the hash link is NOT a status update; there is no lifecycle progression; the row reaches its final state after one post-insert write and never changes again.
Guard: Do NOT add updated_at or deleted_at to audit_log. Do NOT add any mutable columns beyond row_hash/prev_hash. Any feature that would require "updating" an audit log entry is an architectural error — append a correction record instead. See also: payments.stripe_event_log (insert-once-status-updated) vs. audit_log (insert-then-link-once) — similar but distinct; the difference is whether lifecycle progression continues after the first post-insert write.
'overdue' is computed, not stored — compliance_task
Decision: compliance_task.status CHECK does NOT include 'overdue'. Overdue state is derived at query time: status IN ('open','in_progress') AND due_date < now().
Why: A stored derived status silently lies when the batch job that would set it lags, fails, or runs infrequently. If a task's due_date has passed but the batch job hasn't run, the task shows 'open' — correct — but would show 'overdue' only after the job runs. The query-computed approach is always accurate because due_date < now() is evaluated against the actual clock at query time.
Rejected: Store 'overdue' as a status value, populated by a scheduled batch job (silently wrong when the job lags; requires a separate job as an operational dependency for a fact that can be derived for free).
Guard: Never store a status value that can be derived from a timestamp and the current clock. The pattern is always: store the deadline (due_date), derive the state at query. This applies to any "expired," "overdue," or "lapsed" concept across the entire schema.