System Design FAQ: Top Questions

An Audit Logging System records immutable trails of key actions within a system (e.g. logins, permission changes, data exports). These logs are critical for SOC 2, HIPAA, GDPR, and other compliance regimes.

📋 Functional Requirements

• Track who did what, when, and where
• Immutable, append-only logging
• Signed entries to prevent tampering
• Query/filter by actor, action, time, or resource

📦 Non-Functional Requirements

• High write throughput
• WORM (Write Once Read Many) guarantees
• Long-term archival and tiered retention

🏗️ Log Entry Structure

{
  "actor": {
    "id": "u-123",
    "type": "user",
    "ip": "203.0.113.9"
  },
  "action": "DELETE_USER",
  "timestamp": "2025-06-11T22:03:00Z",
  "resource": {
    "type": "user",
    "id": "u-456"
  },
  "metadata": {
    "reason": "admin action",
    "auth_method": "OAuth"
  }
}
        

🗄️ PostgreSQL Schema (Append-only)

CREATE TABLE audit_log (
  id UUID PRIMARY KEY,
  timestamp TIMESTAMPTZ NOT NULL DEFAULT now(),
  actor_id TEXT NOT NULL,
  actor_ip TEXT,
  action TEXT NOT NULL,
  resource_type TEXT,
  resource_id TEXT,
  metadata JSONB
);
        

🔐 Tamper-proofing with Hash Chain

Use a rolling hash like:

def compute_hash(log_entry, prev_hash):
    import hashlib, json
    payload = json.dumps(log_entry, sort_keys=True)
    return hashlib.sha256((payload + prev_hash).encode()).hexdigest()
        

Each entry stores the hash of the previous, forming an integrity chain. Corruption is immediately detectable.

📤 Streaming to Cold Storage

• Forward to S3/Blob/BigQuery for long-term archival
• Stream via Kafka, Kinesis, or Fluent Bit

📈 Observability

• Audit volume per actor
• Top sensitive actions over time
• Unusual spike alerts

🧰 Tools/Infra Used

• DB: PostgreSQL, Amazon QLDB (immutable), Apache Kafka
• Export: Fluent Bit, Firehose, S3, BigQuery
• Integrity: SHA-256 chained hashes

📌 Final Insight

A reliable audit trail system builds trust and satisfies legal requirements. Design it as append-only with verification and offsite storage to ensure durability, verifiability, and compliance.