Multi-Tenant Audit Logs Service

0) Problem Restatement

Design an audit log for a SaaS product (asked at Snowflake). Every security-relevant action is recorded: logins, permission changes, data exports, settings changes, API key creation. It says who did what to which resource, when, and from where. Customers (tenants) search their own audit logs and export them to their security tools (SIEM), and auditors must be able to trust the logs were not changed or deleted.

1) Requirements

• Ingest audit events from all services. No event may be lost.
• Query by tenant, time range, actor, action and resource, with pagination.
• Tamper-evident: any change or deletion can be detected.
• Retention per tenant or plan (e.g., 1 year, 7 years), then deletion.
• Export and stream to external SIEMs (Splunk, Datadog).
• Strict access: only authorized tenant admins can read their own logs.

1.1 Scale

• 10K tenants, 2B events/day (~25K/sec). Each ~1 KB → 2 TB/day raw, compressed about 5–10x.

2) Event Format

{ "event_id": "uuid", "tenant_id": "t-42", "ts": "2026-09-19T10:15:02Z",
  "actor": { "type": "user", "id": "u-7", "ip": "203.0.113.5", "user_agent": "..." },
  "action": "role.grant", "resource": { "type": "warehouse", "id": "wh-3" },
  "result": "success", "details": { "role": "admin", "grantee": "u-9" },
  "request_id": "r-abc" }

A shared schema with a fixed list of action names, so queries and exports are consistent across services.

3) Architecture

flowchart LR
    S["Product services"] -->|"same transaction"| OB[("Outbox table")]
    OB --> REL["Outbox relay"]
    REL --> K[("Kafka - audit topic, by tenant")]
    K --> W["Writer - hash chain per tenant"]
    W --> HOT[("Hot store - search, 90 days")]
    W --> ARC[("Object storage - WORM, long retention")]
    W --> DIG[("Signed daily digests")]
    K --> EXP["SIEM exporters"]
    ADM["Tenant admin UI / API"] --> Q["Query service - authz"]
    Q --> HOT
    Q --> ARC

4) Key Design Points

• No lost events: the outbox pattern. A service writes the audit event into an outbox table in the same DB transaction as the action itself. A relay publishes outbox rows to Kafka. If the action committed, the event will be delivered, even if Kafka was briefly down. Duplicates are removed by event_id.
• Tamper evidence: hash chains. For each tenant, each stored event includes hash = SHA256(prev_hash + event). Changing or deleting any event breaks the chain from that point. Every day, sign the latest hash (a digest) with a private key and publish or store it separately. Auditors verify the chain against signed digests.
• Write-once storage: archive files go to object storage with object lock (WORM), meaning write once, read many, so even admins can't delete them before retention ends.
• Partitioning: by tenant_id and time (day). Queries always include the tenant, which keeps them fast and isolated.
• Two tiers: a search-optimized hot store (e.g., OpenSearch or ClickHouse) for 90 days, and compressed columnar files (Parquet) in object storage for years, queryable more slowly (e.g., via Athena-style engines).

5) Querying and Export

• The API requires tenant_id (from the caller's auth, never from user input), plus filters. It uses cursor pagination by (ts, event_id).
• Reading audit logs is itself audited ("admin X exported logs").
• SIEM streaming: per-tenant exporters push events (HTTPS, syslog) with retries, and track a per-tenant cursor so they resume after failures.

6) Retention and Deletion

• Retention per tenant plan. When expired, delete whole daily partitions (cheap), and let WORM locks expire first.
• Personal data inside audit logs may need special handling for privacy laws. Keep a minimal actor ID and resolve names at read time where possible.

7) Trade-offs & Alternatives

8) Wrap-Up

Have services write audit events through a transactional outbox into Kafka, so every committed action is logged. A writer adds a per-tenant hash chain and daily signed digests, storing events in a hot search store for recent queries and in write-once object storage for long retention. Serve tenant-scoped, audited queries with cursor pagination, stream to customer SIEMs with resumable exporters, and apply retention by dropping expired partitions.