ADR 0058 — Unified Customer Audit: Single Table vs Federated Tables
Status: Accepted (updated v2 — 2026-05-09 UTC)
Date: 2026-05-09 UTC
Deciders: software-architect, operator (Kristerpher)
Refs: customer-audit-unified/design.md, ADR-0022, ADR-0064, workflow-uuid-tracing.md
Context
Five audit streams exist today with no shared schema or join key. Each new surface ships its own audit writer. As iron-condor, Track B, Reasonator, and passkey management land, the divergence compounds. The operator elevated unified audit to a v1 architectural foundation before more surfaces ship.
Two structural approaches were considered: a single unified table (customer_audit_events) or a federated model (each surface owns its audit table, joined by a shared replay_uuid index).
Decision
Single unified table: customer_audit_events.
One table in Raptor's Postgres receives all events across all three dimensions (customer self, system automated, operator interaction). A dimension enum column distinguishes event classes.
v2 additions to this decision (operator + security feedback, 2026-05-09):
Phase 0 Prerequisite — Role Separation
Before the unified table can provide meaningful DDL-level append-only protection, the application must connect as the restricted raptor_app role rather than the Heroku-provisioned owner. The owner credential (DATABASE_URL) is reserved for migrations only. This is a blocking prerequisite for Phase 1 schema creation. See design.md §11 and #1455.
HMAC-SHA-256 + AWS KMS for event_hash
The v1 design used SHA-256 for event_hash. SHA-256 proves chain consistency but not authenticity — an actor with DB write access can delete rows N–M, insert forged rows, and recompute a valid SHA-256 chain. The integrity checker cannot distinguish the forged chain from the original (T-PEN-3, composite score 9).
v2 replaces SHA-256 with HMAC-SHA-256 using a key managed in AWS KMS. The application calls kms:GenerateMac before INSERT; the integrity checker calls kms:VerifyMac. An attacker without KMS access cannot forge a valid HMAC. KMS cost: ~$1/month key + ~$1.07/month API calls at 1K customers. Approved as v1 dependency. See #1454.
SOC-2 Retention SLA
Operator confirmed SOC-2 as v1 scope. Retention SLA updated to align with SEC 17a-4 posture pending BLR confirmation:
| Data class | Hot | Warm | Cold |
|---|---|---|---|
| Trade-affecting audit events | 30 days | 90 days | 7 years |
| Non-financial audit events | 30 days | 90 days | 2 years |
| Operator dim-3 events | 30 days | 90 days | 7 years |
| Shadow table rows | 30 days | 90 days | 2 years |
Cold storage: S3 Glacier Instant Retrieval. WORM (Object Lock Compliance) pending BLR determination on SEC 17a-4(f) applicability.
See ADR-0064 for the full SOC-2 scope decision including auditor role and attestation cadence.
Capacity Planning
Three-tier scale model (see ADR-0063 and design.md §9):
- Tier 1 (synchronous triggers, v1–~1K customers): sustained writes 0.01–0.14/sec, burst 0.5–5/sec. p99 INSERT latency < 8 ms.
- Tier 2 (async pg_notify, 1K–10K): upgrade when p99 > 50 ms sustained 7 days.
- Tier 3 (WAL CDC, 10K+): at 100M rows or 10K customers.
Consequences
Positive
- One RBAC gate controls access to all audit data for a customer.
- HMAC-SHA-256 chain with KMS key provides authenticity, not just consistency. An attacker cannot forge a valid chain without KMS access.
- Per-customer chain covers all three dimensions in a single verifiable sequence.
- The
replay_uuidpivot endpoint is a single indexed query. - GDPR DSR erasure pseudonymizes one table.
- Writer registration is simpler: any new surface calls
POST /api/customer-audit/event. - SOC-2 retention SLA is built into the table's archival policy from Day 1.
Negative
- Single table is a write-path bottleneck under high volume. Mitigation: TimescaleDB hypertables + three-tier upgrade path (ADR-0063).
- Migration requires adapting five existing writers (dual-write phase). One-time cost.
customer_audit_eventsdoes not replace the Console-internalaudit_log. That table remains for operator-on-operator events.- HMAC-SHA-256 adds a KMS API call per INSERT. Adds ~5–10 ms on the KMS call path (network round-trip to KMS). Acceptable at v1 volume; negligible at burst rates.
- Phase 0 role separation is a prerequisite that adds a pre-migration step.
Alternatives Considered
Federated tables (one per surface)
Rejected: five separate DSR erasure jobs, five separate RBAC gates, complex cross-table hash chain verification. Refactor cost savings from deferring convergence are less than the compounding complexity cost.
Separate tables per dimension
Rejected: cross-dimension replay queries require joins. GDPR DSR requires coordinating across three tables. No material benefit over the single-table approach.