Audit / Event Log

Design the schema for an append-only audit log capturing actor, action, target, and metadata. Retention is 7 years for compliance. The product must answer 'what did user X do' and 'what happened to object Y' both quickly. Writes vastly outnumber reads.

Clarifying questions

Is this for compliance (immutable, signed) or product analytics (mutable, indexed)?
What's the write rate ceiling — 10k/s or 1M/s? Drives partitioning granularity.
Can older partitions move to cold storage (S3, object lake) after some interval?
Do we need cryptographic chaining (hash of previous row) for tamper evidence?
Is GDPR scrubbing of specific actors required, and does that break immutability guarantees?

Core requirements

Sustain very high insert throughput with bounded latency.
Answer 'show all actions by user X in date range D' efficiently.
Answer 'show all events targeting object Y' efficiently.
Retain 7 years of data with cheap archival of cold partitions.
Guarantee append-only semantics with no UPDATE / DELETE at the app layer.
Support structured metadata that varies per action type.

Canonical schema (PostgreSQL)

CREATE TABLE events (
  id BIGSERIAL,
  occurred_at TIMESTAMPTZ NOT NULL,
  actor_type TEXT NOT NULL,
  actor_id UUID NOT NULL,
  action TEXT NOT NULL,
  target_type TEXT,
  target_id UUID,
  tenant_id UUID,
  ip INET,
  payload JSONB NOT NULL DEFAULT '{}'::jsonb,
  prev_hash BYTEA,
  row_hash BYTEA,
  PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_q2 PARTITION OF events
  FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

CREATE TABLE events_2026_q3 PARTITION OF events
  FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');

CREATE TABLE actor_index (
  actor_id UUID NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL,
  event_id BIGINT NOT NULL,
  PRIMARY KEY (actor_id, occurred_at DESC, event_id)
);

CREATE TABLE target_index (
  target_type TEXT NOT NULL,
  target_id UUID NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL,
  event_id BIGINT NOT NULL,
  PRIMARY KEY (target_type, target_id, occurred_at DESC, event_id)
);

REVOKE UPDATE, DELETE ON events FROM PUBLIC;

Key indexes

CREATE INDEX idx_events_tenant_time ON events(tenant_id, occurred_at DESC)

Tenant-scoped exports and admin dashboards range-scan by recency within a tenant.

CREATE INDEX idx_events_action_time ON events(action, occurred_at DESC)

'All login_failed events in the last hour' is the canonical security query.

CREATE INDEX idx_events_payload_gin ON events USING GIN (payload jsonb_path_ops)

Forensic search on specific payload keys without exploding columns per action type.

CREATE INDEX idx_actor_index_recent ON actor_index(actor_id, occurred_at DESC) INCLUDE (event_id)

Covering index for 'last 50 actions by user X' — no heap fetch needed.

Key decisions to defend

Range-partition by occurred_at with quarterly partitions — bounded retention deletes become DROP TABLE instead of vacuum-thrashing DELETE.
Maintain separate actor_index and target_index tables instead of multiple wide indexes on events — the events table stays narrow for inserts, and indexes can live on faster storage.
REVOKE UPDATE/DELETE at the role level — immutability is enforced by the database, not by app discipline.
Hash chain (prev_hash, row_hash) for tamper evidence — costs ~64 bytes per row and lets auditors detect retroactive edits.
GDPR scrubbing is the one exception: a privileged crypto-shred path zeros the payload column for a specific actor without breaking the chain (hash recomputed with a tombstone marker).

Likely follow-ups

· Audit consumers want sub-second tailing — how do you expose a stream?
· Move partitions older than 90 days to S3 — what stays queryable in Postgres?
· Add a derived 'session' grouping (events that share a request id) — table or view?
· Customer demands their data be 'forgotten' — how do you reconcile with immutability?

Dive deeper

Audit logs are a partitioning question dressed as a schema question. The discriminating moves are: time-based range partitioning so retention is metadata-only, separate per-axis lookup tables so the write path stays cheap, and database-enforced immutability via REVOKE. The GDPR-vs-immutability tension is a senior signal — you cannot have both, so you scope a controlled escape valve rather than waving the conflict away.

All database design prompts