Problem: Synchronisation ETL between three audit tables fell behind twice, causing compliance findings. Each table had conflicting schemas and different retention mechanisms. No single source of truth.
Solution: Append-only event log in Postgres with Kafka as write-ahead broker. Every state change published a schema-versioned Avro event before the DB write. Rows stored with chained SHA-256 hashes for tamper detection. 90-day read model in a separate schema for FINRA latency SLA. Kafka Tiered Storage offloaded topics to S3 after 90 days, Glacier at 2 years for SEC retention.
Technology: Postgres · Kafka · Avro · Schema Registry · S3 · Glacier
Optimisation pattern: three-sync-tables-to-single-append-only-log-with-read-models
Outcomes:
All 3 regulatory frameworks satisfied from one model — sync ETL retired. SEC full entity history (7 years): under 4 seconds. FINRA 90-day query P99: under 120 ms. Hash chain validation running continuously at zero operational overhead.