Audit and Compliance Platform

Topology Notes

• ·PostgreSQL is the authoritative audit store. All tables are append-only: no UPDATE or DELETE statements are permitted at the application layer. This must be enforced at the database role level (the audit writer role has INSERT only, never UPDATE or DELETE) in addition to application-layer controls.
• ·The cryptographic integrity chain is maintained at the partition level. Each audit event stores SHA-256(previous_event_hash || event_payload). The chain tip for each partition is tracked in a separate chain_tips table, updated atomically with the INSERT in the same transaction. Chain verification scans partition records in sequence and recomputes hashes: it is a separate offline process, not part of the write path.
• ·Kafka carries audit events from the PostgreSQL outbox relay to downstream consumers: SIEM integrations, real-time security dashboards, and the ClickHouse ingestion consumer. Topic retention must be at minimum 7 days. Consumer offset lag for the SIEM consumer is a primary operational SLA metric: a stalled SIEM consumer is a security incident.
• ·ClickHouse serves aggregate compliance queries: actor access reports, resource access timelines, event volume by source system, anomaly threshold calculations. It is not the authoritative store: it is a query-optimized materialization of the PostgreSQL audit log. Historical Parquet archives on S3 extend the queryable range beyond the PostgreSQL hot window via ClickHouse external tables.
• ·Redis caches hot compliance query results (e.g., actor summary reports, recent event counts by category) with a TTL of 60–300 seconds depending on staleness tolerance. Investigator-facing queries bypass Redis and hit ClickHouse directly to ensure accurate real-time data during active security incidents.