Use Audit and Compliance Platform as the Foundational Architecture Pattern
Deterministic ADR derived from topology, simulation, and advisor intelligence for Audit and Compliance Platform. Traceable to YAML knowledge entities.
Context
Compliance and security audit systems must balance three competing constraints: high ingestion throughput (every application action generates an audit record), long retention with range query performance (compliance queries span months or years), and tamper evidence (every record must be verifiable as unmodified after write). Traditional mutable databases expose all three failure points: records can be deleted, queries degrade with table size, and there is no structural mechanism to detect after-the-fact modification. The write path must handle bursts of concurrent audit events without contention; the read path must serve time-range and actor-scoped queries across hundreds of millions of records without full-table scans; and the integrity chain must be maintained without becoming a write serialization bottleneck. Primary operational risks include: Integrity chain write serialization: the cryptographic hash of each new record depends on the hash of the previous record in the same partition. Under concurrent ingestion, computing the chain hash before insert requires reading the most recent record, creating a per-partition read-before-write that serializes throughput. At high ingestion rates, this becomes the binding bottleneck before any hardware limit is reached.; Partition pruning failure on unindexed actor queries: compliance investigators often query by actor ID or resource ID across a time range. If the partition key is event_time only, an actor-scoped query must scan all partitions, materializing hundreds of millions of rows. Without a secondary index table keyed by (actor_id, event_time), these queries degrade into full-partition scans that compete with the ingestion write path.; ClickHouse replication lag during compliance query spikes: a compliance audit export triggering large ClickHouse scans concurrently with the CDC ingestion consumer creates I/O contention on ClickHouse data nodes. The ingest consumer falls behind Kafka offset, causing the compliance data visible in ClickHouse to lag behind the PostgreSQL authoritative log by minutes to hours..
Decision
We will adopt the **Audit and Compliance Platform** architecture pattern. This is a high-complexity architecture appropriate for teams at experienced backend team level or above. The advisor rates this pattern as 'advanced' operational maturity.
Rationale
An append-only audit log architecture for capturing system actions: user operations, data access events, configuration changes, and financial operations: with cryptographic integrity chaining, immutable storage, and dual-path query serving. PostgreSQL stores the authoritative append-only event log in time-partitioned tables; ClickHouse serves historical aggregate queries and retention analytics; Kafka streams audit events in real time to SIEM integrations and security dashboards; Redis caches hot audit query results for compliance-facing read paths. No record is ever updated or deleted: only inserts are permitted. Each record includes a hash of the previous record, forming a tamper-evident chain verifiable without external tooling. Core technology stack: postgresql, kafka, redis, clickhouse.
Accepted Tradeoffs
- ⚠Append-only partitioned storage eliminates update/delete contention and makes tamper detection structurally possible, but storage grows without bound: partitions older than the retention policy must be archived to object storage rather than dropped, or regulatory replay is impossible
- ⚠Cryptographic integrity chaining provides strong tamper evidence without external hardware, but requires that chain verification scans the entire partition in sequence: partial verification is not possible, and verification is O(n) in partition size
- ⚠ClickHouse serves aggregate compliance analytics with p99 < 500ms across billions of rows, but it is eventually consistent with PostgreSQL via CDC; compliance queries have an inherent staleness window that must be disclosed in audit tooling UX
- ⚠PostgreSQL time-range partitioning keeps per-partition table sizes manageable and enables fast DROP of expired partitions, but cross-partition range queries (spanning months) require partition pruning to work correctly: queries without a partition key predicate are full-table scans
Risks
PostgreSQL WAL (Write-Ahead Log) generation rate exceeds wal_buffers flush capacity or downstream replica/WAL archive bandwidth, causing write transactions to stall waiting for WAL flush and replication lag to grow unboundedly.
Each logical application write triggers multiple physical writes through index maintenance, WAL generation, MVCC versioning, and replication, causing actual disk IOPS to exceed the provisioned I/O ceiling while the logical write rate appears modest.
Concurrent writers to the same rows serialize behind each other's row locks, so latency is set not by the work a transaction does but by how long it waits for the writers ahead of it. On a hot row the queue depth, and therefore the tail latency, grows with concurrency while throughput flattens. Blocked writers hold connections open, so a single contended row can drain the connection pool as a secondary failure.
The storage device reaches its IOPS or throughput ceiling, causing all disk- dependent database operations to queue behind I/O requests, driving latency from sub-millisecond to hundreds of milliseconds and degrading all database operations simultaneously.
Asynchronous replicas fall behind the primary under write load and serve reads from an older version of the data. Reads keep succeeding, so nothing errors; what breaks is one of three specific consistency guarantees (read-after-write, monotonic reads, or consistent prefix), each with a distinct user-visible anomaly.
Alternatives Considered
AI Retrieval-Augmented Generation Platform shares core technology (kafka, postgresql) with the chosen architecture but applies different structural patterns; Audit and Compliance Platform is a better fit for the identified workload profile.
Analytics Data Platform shares core technology (clickhouse, kafka) with the chosen architecture but applies different structural patterns; Audit and Compliance Platform is a better fit for the identified workload profile.
API Gateway Platform shares core technology (kafka, postgresql) with the chosen architecture but applies different structural patterns; Audit and Compliance Platform is a better fit for the identified workload profile.
Content Management Platform shares core technology (postgresql, redis) with the chosen architecture but applies different structural patterns; Audit and Compliance Platform is a better fit for the identified workload profile.
Scaling Thresholds
Signals indicating the architecture is approaching its scaling limits:
Tier 1: Integrity Chain Write Serialization
Signal: PostgreSQL write p99 > 20ms with low connection count; pg_stat_activity showing transactions serialized on the same partition's chain-tip read; ingestion throughput plateauing well below hardware limits; auto_explain showing sequential scan on audit_events for "SELECT hash FROM audit_events ORDER BY id DESC LIMIT 1"
Evolution: Introduce partition-level chain sequence tables: a single row per partition tracking the current chain tip with an advisory lock, eliminating the full table read. Alternatively, shard the integrity chain by source system or tenant, accepting per-shard chains rather than a single global chain. Use PostgreSQL INSERT ... RETURNING with sequence-assigned IDs to eliminate the pre-insert read entirely, deferring chain hash computation to an async integrity sealer that appends hashes in order without blocking the write path.
Tier 2: Actor Query Full-Partition Scan
Signal: Compliance investigator queries returning in > 30s; PostgreSQL showing high sequential scan counts on audit_events partitions; investigator-facing API p99 > 10s; pg_stat_statements showing actor_id-scoped queries without partition pruning in the query plan
Evolution: Build a secondary index table audit_events_by_actor(actor_id, event_time, event_id) populated synchronously on insert. Accept the additional write per event as the cost of O(log n) actor-scoped queries. Alternatively, route actor-scoped queries to ClickHouse where columnar storage makes actor_id filters efficient without a secondary B-tree index.
Tier 3: Partition Archive and Storage Pressure
Signal: PostgreSQL data volume growing > 100GB/month; disk utilization > 70%; VACUUM taking > 10 minutes on large audit partitions; oldest compliance query range spanning partitions that cannot be dropped without regulatory risk
Evolution: Implement time-partitioned archival: partitions older than the hot-query window (typically 90 days for operational queries, 1 year for compliance queries) are exported to Parquet on S3, validated against the cryptographic chain, and then detached. ClickHouse external tables can query S3 Parquet directly for historical range queries. PostgreSQL retains only the hot window.
Tier 4: Multi-Tenant Write Path Isolation
Signal: A single high-volume tenant (e.g., a financial services customer generating 500k+ events/hour) causing write contention that affects audit ingestion latency for other tenants; per-tenant query SLAs diverging; partition layout making tenant-scoped data export impractical
Evolution: Introduce tenant-scoped write sharding: high-volume tenants get dedicated partition groups with their own chain sequences and their own ClickHouse materialization table. Low-volume tenants share a pooled partition group. This enables per-tenant storage tiering, export, and independent integrity chain management.
Migration Path
Application-level audit log in mutable table with update/delete allowed → Append-only partitioned audit log with cryptographic integrity chain
Compliance audit finding that audit records were modified after write; regulatory requirement (SOC 2 Type II, SOX, HIPAA) for tamper-evident audit log; inability to reconstruct historical actor activity from mutable state
PostgreSQL full-text queries for compliance reports → ClickHouse for aggregate compliance analytics with CDC-based replication
Compliance report generation taking > 5 minutes against PostgreSQL; month-end audit export queries competing with write path and causing ingestion latency spikes; need for fast aggregate queries across 12+ months of audit history
Single Kafka topic for all audit events → Per-source or per-severity topic partitioning with dedicated SIEM consumers
SIEM consumer lag causing it to fall behind retention window during high-volume security events; high-priority security events (authentication failures, privilege escalations) mixed with low-priority operational events causing SIEM triage latency
Operational Requirements
- Minimum team maturity: Experienced Backend Team: This scenario has high operational complexity. It is recommended for Experienced Backend Team teams or higher.
- Runbooks and alerting for high-severity risks: 5 high-severity risks identified. Each requires a documented runbook, alerting threshold, and on-call response procedure before running in production.
- Event stream operations expertise: This architecture includes event stream infrastructure (Kafka, Kinesis, or similar). Operations requires consumer group management, partition assignment, dead-letter handling, and lag monitoring.
- Cache sizing and eviction policy configuration: Redis or equivalent cache requires correct maxmemory configuration, eviction policy selection (allkeys-lru is common), and cold-start warming strategy after restarts.