04 — Data Model

Canonical event & BigQuery schema

One envelope normalizes every source. Common fields are strongly typed columns for fast filtering; source-specific payloads live in a single JSON column.

Fields

Field	Description	Type	Req
event_id	Unique event identifier (UUID)	STRING	●
occurred_at	When the event occurred at the source	TIMESTAMP	●
received_at	When the API received the log · partition key · API-enriched	TIMESTAMP	●
source_system	Source system name (jenkins, vault…)	STRING	●
source_instance	Specific instance / environment of the source	STRING	○
action	Action / event name performed	STRING	●
outcome	Result of the action (SUCCESS, FAILURE, DENIED)	STRING	○
severity	Severity level (INFO, WARN, ERROR)	STRING	○
actor_id	Subject / account that initiated the action	STRING	○
target_id	Target resource affected by the action	STRING	○
correlation_id	Trace ID linking operations across services	STRING	○
ingest_client_id	Zitadel client authenticated by the API · from JWT subject	STRING	○
metadata	Source-specific fields in extensible JSON	JSON	○

BigQuery DDL

CREATE TABLE audit.audit_logs (
  event_id          STRING    NOT NULL,
  occurred_at       TIMESTAMP NOT NULL,
  received_at       TIMESTAMP NOT NULL,
  source_system     STRING    NOT NULL,
  source_instance   STRING,
  action            STRING    NOT NULL,
  outcome           STRING,
  severity          STRING,
  actor_id          STRING,
  target_id         STRING,
  correlation_id    STRING,
  ingest_client_id  STRING,
  metadata          JSON
)
PARTITION BY DATE(received_at)
CLUSTER BY source_system, action;

PARTITION	Daily on received_at — prunes scans & enables retention expiry.
CLUSTER	By source & action — most filters hit these, cutting bytes read.