Design an Immutable Audit Log System for a Multi-Tenant Compliance Product

1. Problem Statement

Design an audit log system for a multi-tenant compliance product. The system records security- and compliance-relevant actions such as user changes, control updates, evidence exports, integration changes, policy modifications, and administrator activity.

The audit log must be:

• Append-only: events are inserted, never updated or deleted from the source of truth.
• Tenant-isolated: every event belongs to exactly one customer tenant.
• Tamper-evident: unauthorized mutation should be detectable.
• Queryable: customers and internal users can filter by actor, action, target, and date range.
• Exportable: auditors can receive signed exports for a scoped audit period.
• Privacy-aware: sensitive fields are redacted, hashed, or retained according to policy.

A strong design optimizes for trust, correctness, and operational reliability over convenience.

---

2. Clarifying Questions

Ask these before designing:

1. Which actions are considered audit-worthy?
2. Is the audit log customer-facing, internal-only, or both?
3. What is the expected write volume per tenant and globally?
4. What query latency is required for recent events?
5. How long do audit logs need to be retained?
6. Are customers allowed to configure retention policy?
7. Do auditors require CSV, JSON, PDF, API export, or signed ZIP files?
8. Should before/after values be stored, redacted, or hashed?
9. Do we need region-specific storage or customer-managed keys?
10. Is strict immutability required, or is tamper-evidence acceptable?

---

3. Requirements

Functional Requirements

• Record audit events for important user and system actions.
• Include actor, action, target, timestamp, tenant ID, request ID, IP address, user agent, and metadata.
• Support before/after diffs for important mutations.
• Support query by actor, target object, action, and date range.
• Support auditor exports.
• Support retention and legal hold.
• Support system-generated actions, service accounts, API keys, and integrations as actors.
• Provide tamper-evidence through immutable archive and hash chaining.

Non-Functional Requirements

• High write durability.
• Low write latency on the product path.
• Eventual queryability within seconds to a few minutes.
• Tenant-level authorization on every read.
• Idempotent event writes.
• Rebuildable query indexes.
• Strong observability for ingestion lag, write failures, and export failures.
• PII minimization.

---

4. Core Event Model

export type ActorType = 'user' | 'service_account' | 'api_key' | 'integration' | 'system';

export type AuditEvent = {
  event_id: string;
  tenant_id: string;

  actor_id: string | null;
  actor_type: ActorType;

  action: string;

  target_type: string;
  target_id: string;

  timestamp: string;
  request_id: string;

  ip_address?: string;
  user_agent?: string;

  metadata: Record<string, unknown>;

  previous_value_hash?: string;
  new_value_hash?: string;

  previous_value_redacted?: Record<string, unknown>;
  new_value_redacted?: Record<string, unknown>;

  event_hash: string;
  previous_event_hash?: string;

  schema_version: number;
};

Required Fields

Field	Purpose
event_id	Idempotency and unique event identity
tenant_id	Tenant isolation and authorization boundary
actor_id	Who performed the action
actor_type	User, system, integration, API key, service account
action	What happened, for example control.override.created
target_type	Object type affected, for example control, evidence, integration
target_id	Object identifier affected
timestamp	Event time
request_id	Correlates product logs, API logs, and audit events
ip_address	Security investigation metadata
user_agent	Security investigation metadata
metadata	Action-specific details
previous_value_hash	Tamper-aware before-state fingerprint
new_value_hash	Tamper-aware after-state fingerprint
event_hash	Hash of canonical event payload
previous_event_hash	Hash-chain pointer for tamper evidence

---

5. Example Audit Event

{
  "event_id": "evt_123",
  "tenant_id": "tenant_456",
  "actor_id": "user_789",
  "actor_type": "user",
  "action": "control.override.created",
  "target_type": "control",
  "target_id": "control_soc2_mfa_required",
  "timestamp": "2026-07-02T20:15:00Z",
  "request_id": "req_abc",
  "ip_address": "203.0.113.10",
  "user_agent": "Mozilla/5.0",
  "metadata": {
    "reason": "Temporary exception for contractor onboarding",
    "expires_at": "2026-08-01T00:00:00Z"
  },
  "previous_value_hash": "sha256:old_hash",
  "new_value_hash": "sha256:new_hash",
  "previous_value_redacted": {
    "status": "passing"
  },
  "new_value_redacted": {
    "status": "overridden"
  },
  "event_hash": "sha256:event_hash",
  "previous_event_hash": "sha256:previous_event_hash",
  "schema_version": 1
}

---

6. High-Level Architecture

Product Services
  -> Audit SDK / Middleware
  -> Local Transactional Outbox
  -> Audit Publisher Worker
  -> Audit Ingestion API
  -> Durable Log
  -> Immutable Archive
  -> Query Index
  -> Audit Query API
  -> UI / Auditor Export

Components

Component	Responsibility
Product Services	Perform business actions and emit audit events
Audit SDK / Middleware	Enrich events with actor, tenant, request ID, IP, user agent
Outbox Table	Reliably couples business mutation with audit event creation
Publisher Worker	Publishes pending audit events to the ingestion layer
Audit Ingestion API	Validates schema, enforces idempotency, writes to durable log
Durable Log	Kafka/Kinesis/PubSub source for downstream consumers
Immutable Archive	S3/GCS/Object Lock source of truth
Query Index	ClickHouse/OpenSearch/Postgres for fast filtering
Audit Query API	Enforces tenant-scoped queries and authorization
Export Worker	Generates auditor exports from archive or reconciled query store
Retention Service	Applies retention policy, legal hold, and field redaction

---

7. Write Path

Example: a customer admin disables an MFA policy.

1. API request enters the product service.
2. Auth layer resolves tenant_id, actor_id, role, IP address, user agent, and request_id.
3. Product service validates authorization.
4. Product service updates the business object.
5. In the same database transaction, product service writes an audit_outbox row.
6. Outbox worker reads pending events.
7. Worker publishes event to the Audit Ingestion API or directly to the durable log.
8. Ingestion validates schema, canonicalizes payload, computes hash, and deduplicates by event_id.
9. Consumers write to immutable archive and query index.
10. Event becomes searchable and exportable.

Why Use Outbox?

Without an outbox, this can happen:

Business mutation succeeds.
Audit event write fails.
System has no reliable record of the sensitive change.

With an outbox, the mutation and event creation commit together.

CREATE TABLE audit_outbox (
  outbox_id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  event_id UUID NOT NULL,
  payload JSONB NOT NULL,
  status TEXT NOT NULL, -- pending, delivered, failed
  retry_count INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL,
  delivered_at TIMESTAMP
);

---

8. Storage Design

Source of Truth: Immutable Archive

Use object storage with immutability controls.

Examples:

• S3 Object Lock
• GCS Bucket Lock
• Azure immutable blob storage

Partitioning strategy:

/audit-events/tenant_id=<tenant_id>/year=2026/month=07/day=02/hour=20/part-000.parquet

The immutable archive is optimized for:

• Audit exports
• Reprocessing
• Query index rebuilds
• Tamper-resistant long-term retention

Query Store

Options:

Store	Best For	Trade-Off
OpenSearch	Flexible filtering and text search	Higher operational cost at large volume
ClickHouse	High-volume analytical filtering	Less natural full-text search
Postgres	Simplicity for smaller scale	May hit scale limits
BigQuery/Snowflake	Historical analytics and exports	Higher latency / cost for interactive UI

Recommended interview answer:

I would use immutable object storage as the source of truth and ClickHouse or OpenSearch as the query-serving index. If the index is corrupted, stale, or deleted, it can be rebuilt from the archive.

---

9. Query Model

Common customer and auditor queries:

• All actions by an actor.
• All changes to a target object.
• All events in a date range.
• All integration changes.
• All control override actions.
• All export/download events.
• All admin privilege changes.

Example API:

GET /audit-events?actor_id=user_123&start=2026-01-01&end=2026-03-31
GET /audit-events?target_type=control&target_id=control_456
GET /audit-events?action=integration.disconnected
GET /audit-events?request_id=req_abc

Every query must be tenant-scoped:

SELECT *
FROM audit_events
WHERE tenant_id = :authenticated_tenant_id
  AND event_timestamp BETWEEN :start AND :end
ORDER BY event_timestamp DESC
LIMIT :limit;

Useful indexes:

CREATE INDEX idx_audit_tenant_time
ON audit_events (tenant_id, event_timestamp DESC);

CREATE INDEX idx_audit_tenant_actor_time
ON audit_events (tenant_id, actor_id, event_timestamp DESC);

CREATE INDEX idx_audit_tenant_target_time
ON audit_events (tenant_id, target_type, target_id, event_timestamp DESC);

CREATE INDEX idx_audit_tenant_action_time
ON audit_events (tenant_id, action, event_timestamp DESC);

CREATE INDEX idx_audit_request_id
ON audit_events (request_id);

---

10. Tamper Resistance

Use layered tamper resistance.

Level 1: Append-Only Application Semantics

The audit API supports insert only.

No update endpoint.
No delete endpoint.
No user-controlled mutation of historical events.

Level 2: Permission Isolation

Only the audit ingestion service can write audit events. Product services cannot directly modify the audit archive or query index.

Level 3: Hash Chaining

For each tenant, maintain a chain:

Event 1 hash
  -> Event 2 previous_event_hash
  -> Event 3 previous_event_hash

If an old event is changed or removed, the chain no longer validates.

Level 4: Immutable Archive

Write audit event batches to object storage with write-once-read-many behavior.

Level 5: Periodic Checkpoints

Periodically store a root hash checkpoint in a separate security account or ledger.

export type AuditHashCheckpoint = {
  tenant_id: string;
  window_start: string;
  window_end: string;
  event_count: number;
  root_hash: string;
  created_at: string;
};

---

11. Before / After Diff Strategy

Do not blindly store full payloads. Audit logs are long-lived and may contain sensitive data.

Recommended strategy:

Data Type	Storage Strategy
Non-sensitive fields	Redacted before/after diff
Sensitive fields	Hash only
Secrets/tokens/passwords	Never store raw value or hash if hash enables guessing
Large objects	Store object reference and content hash
Deleted users	Store stable deleted actor reference, not full PII

Example redacted diff:

{
  "previous_value_redacted": {
    "mfa_required": true,
    "policy_status": "enabled"
  },
  "new_value_redacted": {
    "mfa_required": false,
    "policy_status": "disabled"
  }
}

---

12. Auditor Export Design

Exporting audit logs is itself audit-worthy.

Export Flow

1. User requests export with filters.
2. System creates async export job.
3. Worker reads from immutable archive or reconciled query store.
4. Worker generates CSV, JSON, or signed ZIP.
5. File is encrypted and stored with a short-lived download URL.
6. Export creation and download are written as audit events.

Example export events:

audit_log.export.created
audit_log.export.downloaded
audit_log.export.failed

Export filters:

• Tenant
• Date range
• Actor
• Action
• Target type
• Control/framework
• Integration

---

13. Retention Policy

Use retention tiers.

Data	Hot Retention	Cold Retention	Notes
Query index	1-2 years	N/A	Fast UI queries
Immutable archive	N/A	7 years	Compliance/audit evidence
IP address/user agent	30-365 days	Optional hash/redaction	Depends on customer policy
Debug metadata	30-90 days	Remove or redact	Avoid retaining unnecessary PII
Export files	7-30 days	Optional	Regenerate from archive when possible

Important principle:

Retention should preserve proof of activity while minimizing long-term sensitive data exposure.

For deleted users, keep enough audit continuity without retaining unnecessary PII:

actor_id = deleted_user_123
actor_type = user
actor_display_name = null or redacted

---

14. Multi-Tenancy and Authorization

Tenant isolation is non-negotiable.

Rules:

1. Every event includes tenant_id.
2. Every query is scoped by authenticated tenant.
3. Internal support access requires break-glass approval.
4. Break-glass access is itself audit logged.
5. Large tenants can receive dedicated partitions or indexes.
6. Enterprise tenants may require region-specific storage and customer-managed encryption keys.

Authorization example:

User can view audit events only if:
- user belongs to tenant_id
- user has audit_log.read permission
- requested filters are within allowed scope

---

15. Failure Handling

Failure Cases

• Product mutation succeeds but audit event is not emitted.
• Audit ingestion API is unavailable.
• Durable log is unavailable.
• Archive write fails.
• Query index is delayed or corrupted.
• Export job fails.
• Duplicate events are published.
• Malformed events enter the pipeline.

Mitigations

Failure	Mitigation
Product mutation without audit event	Transactional outbox
Duplicate events	Idempotency by event_id
Ingestion failure	Retry with exponential backoff
Malformed events	Dead-letter queue
Archive failure	Alert and pause checkpoint advancement
Query index corruption	Rebuild from immutable archive
Export failure	Async retry and failure audit event
Delayed indexing	Show freshness timestamp in UI

---

16. Monitoring and Data Quality

Track:

audit_events_written_per_minute
audit_ingestion_error_rate
audit_outbox_pending_count
audit_outbox_oldest_pending_age
audit_log_consumer_lag_seconds
immutable_archive_write_success_rate
query_index_lag_seconds
export_job_failure_rate
dlq_event_count
hash_chain_validation_failure_count

Alert on:

• No audit events for an active tenant.
• Outbox backlog is growing.
• Query index lag exceeds SLA.
• Archive write failure.
• DLQ spike.
• Hash-chain validation failure.
• Export failure spike.

---

17. Correctness Guarantees

Define guarantees explicitly:

Area	Guarantee
Write durability	Critical events are captured through outbox and durable log
Query freshness	Events searchable within seconds to minutes
Source of truth	Immutable archive, not query index
Idempotency	Duplicate event IDs are ignored
Tenant isolation	All reads and writes are tenant-scoped
Tamper evidence	Hash chain + immutable archive + checkpoints
Export correctness	Exports generated from archive or reconciled query store

---

18. Design Trade-Offs

Decision	Option A	Option B	Recommendation
Write mode	Synchronous audit write	Async outbox	Use outbox for reliability without adding user-facing latency
Before/after storage	Full payload	Redacted diff + hash	Prefer redacted diff + hash
Query store	OpenSearch	ClickHouse	Choose based on query shape; OpenSearch for flexible search, ClickHouse for analytics
Immutability	Application-level insert-only	WORM archive	Use both
Retention	Keep everything	Tiered retention	Tiered retention with PII minimization
Tamper evidence	Hash each event	Hash chain per tenant	Hash chain per tenant with checkpoints

---

19. Interview-Ready Summary

I would design the audit log as an append-only event pipeline. Product services emit structured audit events through an SDK or middleware, including tenant ID, actor, action, target, timestamp, request ID, IP address, user agent, and redacted before/after metadata. For critical product mutations, I would use a transactional outbox so the business change and audit event are committed together. Events flow into a durable log, then into immutable object storage as the source of truth and into a query-optimized index for fast filtering.

For tamper resistance, the system uses insert-only APIs, restricted write permissions, per-tenant hash chaining, immutable object storage, and periodic hash checkpoints. The query index is not trusted as the source of truth and can always be rebuilt from the archive. Auditor exports run asynchronously, are encrypted, scoped by tenant and date range, and the export itself is logged as an audit event. Retention is tiered so we keep audit proof while minimizing long-term PII exposure.

---

20. Lead Engineer Signals

Emphasize these during the interview:

• The system must be reliable even when product services or downstream pipelines fail.
• The source of truth should be immutable and rebuildable.
• Audit logging should not leak sensitive data by retaining raw secrets or unnecessary PII.
• Tenant isolation must be enforced at every layer.
• Exports and break-glass access are themselves audit-worthy events.
• Correctness guarantees should be explicit: fresh query index vs authoritative archive.
• Operational visibility is part of the design, not an afterthought.