Why auditing and lineage look different in event-driven systems
Auditing, traceability, and data lineage are often treated as separate concerns: auditing answers “who did what and when,” traceability answers “how did this value come to be,” and lineage answers “where did this data originate and what transformations touched it.” In event-driven data modeling, these concerns can be addressed directly from operational events, but only if you model and capture the right metadata alongside the business payload.
The key shift is that the primary evidence is not a mutable row in a current-state table, but a sequence of operational facts (events) plus the processing steps that derived downstream state. That means your audit story must include: (1) provenance of each event (actor, system, request), (2) integrity of the event record (immutability, tamper evidence), (3) traceability from downstream records back to the events that produced them, and (4) lineage across transformations, projections, and exports.
This chapter focuses on how to design and implement auditing and lineage using PostgreSQL features and patterns around operational events, without rehashing how to model event tables, ordering, or projections. The emphasis is on metadata, linkage, and verifiable trails.
Audit goals and threat model: what you must be able to prove
Before adding columns and triggers, define what “audit-ready” means for your domain. Common requirements include:
Accountability: identify the actor (human or service) responsible for a change, including delegated actions (impersonation, admin override).
Continue in our app.- Listen to the audio with the screen off.
- Earn a certificate upon completion.
- Over 5000 courses for you to explore!
Download the app
Non-repudiation (practical): demonstrate that a record was produced by a specific system under specific credentials, and detect tampering.
Completeness: show that all relevant events are captured (no silent updates).
Traceability: for any derived value, identify the exact input events and processing version that produced it.
Explainability: reconstruct the “why” (reason codes, policy decisions, approvals) not just the “what.”
Regulatory constraints: retention, right-to-access, and sometimes selective redaction while preserving integrity.
These goals drive what metadata you must capture at ingestion time and what you must persist during processing.
Operational event metadata: the minimum viable audit envelope
Business payloads alone rarely satisfy audit needs. Add an “audit envelope” to each event that captures provenance and context. A practical envelope includes:
Actor identity: user_id, service_id, or subject identifier; plus actor type (human/service).
Authentication context: auth method, session id, token id (or token hash), and tenant/organization id.
Request correlation: request_id, trace_id/span_id (from distributed tracing), and idempotency key if present.
Source system: producer name, version, environment, region.
Client context: IP address, user agent, device id (when relevant and lawful).
Business justification: reason code, ticket id, approval id, or policy decision reference.
Integrity fields: hash of canonical payload, signature reference, or hash chain pointers.
In PostgreSQL, you can store this envelope in typed columns for frequently queried fields (actor_id, request_id, trace_id) and keep the rest in a JSONB column such as meta. The important point is consistency: define a stable schema for metadata across event types so audit queries don’t become bespoke per event.
Example: event table with audit envelope fields
CREATE TABLE operational_events ( event_id uuid PRIMARY KEY, event_type text NOT NULL, aggregate_type text NOT NULL, aggregate_id uuid NOT NULL, occurred_at timestamptz NOT NULL, ingested_at timestamptz NOT NULL DEFAULT now(), actor_id uuid, actor_type text, request_id uuid, trace_id text, source_system text NOT NULL, source_version text, reason_code text, meta jsonb NOT NULL DEFAULT '{}'::jsonb, payload jsonb NOT NULL, payload_hash bytea, prev_event_hash bytea, event_hash bytea);This does not prescribe how you model events; it shows the audit-relevant additions: actor/request/trace, source, reason, and integrity fields.
Step-by-step: capturing provenance at ingestion time
The most common audit failure is trying to reconstruct provenance after the fact. Capture it at the boundary where the event is accepted.
Step 1: define canonical metadata and enforce presence
Create a small specification for required metadata keys per producer. For example: every event must have source_system, request_id, and either actor_id or an explicit actor_type='system'. Enforce with CHECK constraints where possible.
ALTER TABLE operational_events ADD CONSTRAINT chk_source_system_nonempty CHECK (length(source_system) > 0); ALTER TABLE operational_events ADD CONSTRAINT chk_actor_or_system CHECK (actor_id IS NOT NULL OR actor_type = 'system');Step 2: propagate correlation IDs from the application layer
Ensure your API gateway or service layer generates a request_id and trace_id and passes them into the event write. If you use PostgreSQL session variables, you can set them per request and have the database read them.
-- In the request transaction: SELECT set_config('app.request_id', 'b3a2...-uuid', true); SELECT set_config('app.actor_id', 'c1d2...-uuid', true);Then default columns from these settings using a trigger (or in the insert statement). Triggers are useful when multiple code paths insert events and you want centralized enforcement.
CREATE OR REPLACE FUNCTION fill_event_audit_fields() RETURNS trigger AS $$BEGIN IF NEW.request_id IS NULL THEN NEW.request_id := current_setting('app.request_id', true)::uuid; END IF; IF NEW.actor_id IS NULL AND current_setting('app.actor_id', true) IS NOT NULL THEN NEW.actor_id := current_setting('app.actor_id', true)::uuid; NEW.actor_type := COALESCE(NEW.actor_type, 'human'); END IF; RETURN NEW;END;$$ LANGUAGE plpgsql; CREATE TRIGGER trg_fill_event_audit_fields BEFORE INSERT ON operational_events FOR EACH ROW EXECUTE FUNCTION fill_event_audit_fields();Step 3: record “why” as first-class metadata
Auditors frequently ask for justification: “Why was this refund issued?” “Why was access granted?” If you only store the resulting event, you may miss the decision context. Add fields like reason_code and references to approvals or tickets in meta. Make them required for sensitive event types via application validation and, where feasible, database constraints (for example, a trigger that checks event_type and requires reason_code).
Making event records tamper-evident (and what PostgreSQL can and cannot do)
PostgreSQL can help you prevent accidental changes with permissions and immutability rules, but “tamper-evidence” is about detecting unauthorized modification even by privileged actors. A practical approach combines:
Immutability controls: revoke UPDATE/DELETE on event tables from application roles; only allow INSERT.
Append-only enforcement: triggers that raise exceptions on UPDATE/DELETE.
Hashing: store a cryptographic hash of the canonical event content; optionally chain hashes so reordering/removal is detectable.
External anchoring: periodically export hashes to an external system (object storage with retention, or a separate audit store) to reduce insider risk.
Step-by-step: append-only enforcement
REVOKE UPDATE, DELETE ON operational_events FROM app_role; GRANT INSERT, SELECT ON operational_events TO app_role; CREATE OR REPLACE FUNCTION prevent_event_mutation() RETURNS trigger AS $$BEGIN RAISE EXCEPTION 'operational_events is append-only';END;$$ LANGUAGE plpgsql; CREATE TRIGGER trg_no_update BEFORE UPDATE ON operational_events FOR EACH ROW EXECUTE FUNCTION prevent_event_mutation(); CREATE TRIGGER trg_no_delete BEFORE DELETE ON operational_events FOR EACH ROW EXECUTE FUNCTION prevent_event_mutation();Step-by-step: compute payload hashes and hash chains
Use pgcrypto to compute hashes. The hash should be computed over a canonical representation. For JSONB, PostgreSQL stores a normalized form, so payload::text is typically stable for identical JSONB values, but be explicit about what you hash (include key fields like event_type, occurred_at, aggregate_id, payload, meta).
CREATE EXTENSION IF NOT EXISTS pgcrypto; CREATE OR REPLACE FUNCTION compute_event_hashes() RETURNS trigger AS $$DECLARE canonical text;BEGIN canonical := jsonb_build_object( 'event_id', NEW.event_id, 'event_type', NEW.event_type, 'aggregate_type', NEW.aggregate_type, 'aggregate_id', NEW.aggregate_id, 'occurred_at', NEW.occurred_at, 'actor_id', NEW.actor_id, 'request_id', NEW.request_id, 'source_system', NEW.source_system, 'reason_code', NEW.reason_code, 'meta', NEW.meta, 'payload', NEW.payload )::text; NEW.payload_hash := digest((NEW.payload)::text, 'sha256'); -- prev_event_hash can be set by the writer or looked up per aggregate/stream NEW.event_hash := digest(coalesce(NEW.prev_event_hash, '\x'::bytea) || canonical::bytea, 'sha256'); RETURN NEW;END;$$ LANGUAGE plpgsql; CREATE TRIGGER trg_hashes BEFORE INSERT ON operational_events FOR EACH ROW EXECUTE FUNCTION compute_event_hashes();Hash chains are most meaningful when prev_event_hash refers to the immediately previous event in the same stream (for example, same aggregate). That lookup must be consistent with your stream semantics and concurrency model; many systems set it in the application when appending.
Traceability: linking derived state back to the exact events
Auditing operational events is only half the story. In practice, auditors and engineers ask questions about current state: “Why does this account have status S?” “Which actions led to this balance?” That requires traceability from read models (or operational tables) back to the events that produced them.
A robust approach is to store, alongside each derived row, a reference to the input event(s) and the processing run that produced it.
Lineage fields for projections and derived tables
For each derived table (projection, materialized view table, or operational table maintained from events), consider adding:
last_event_id: the most recent event applied to this row.
last_event_occurred_at: timestamp of that event (useful for “as-of” explanations).
applied_event_ids: optional array or separate link table when multiple events contribute (for example, a daily summary).
processor_name / processor_version: which code produced it.
processing_run_id: identifies a specific run/batch/transaction.
computed_at: when the derived value was computed.
Example: projection table with lineage columns
CREATE TABLE account_read_model ( account_id uuid PRIMARY KEY, status text NOT NULL, balance_cents bigint NOT NULL, last_event_id uuid NOT NULL, last_event_occurred_at timestamptz NOT NULL, processor_name text NOT NULL, processor_version text NOT NULL, processing_run_id uuid NOT NULL, computed_at timestamptz NOT NULL DEFAULT now());With these columns, you can answer: “Which event last changed this row?” and “Which code version computed it?” without scanning the entire event stream.
Step-by-step: record lineage during projection updates
When applying an event to update the projection, update lineage fields in the same transaction as the business fields.
-- Pseudocode SQL for applying one event to a read model row UPDATE account_read_model SET status = $new_status, balance_cents = $new_balance, last_event_id = $event_id, last_event_occurred_at = $occurred_at, processor_name = 'account-projector', processor_version = '1.7.3', processing_run_id = $run_id, computed_at = now() WHERE account_id = $account_id;If the projection is built via upserts, include these fields in the INSERT and UPDATE clauses consistently.
Many-to-many lineage: when one output depends on many events
Some derived artifacts (invoices, statements, daily aggregates, ML features) depend on many input events. Storing a single last_event_id is insufficient. Use a link table that records dependencies between an output artifact and the input events.
Example: lineage link table
CREATE TABLE derived_artifact ( artifact_id uuid PRIMARY KEY, artifact_type text NOT NULL, subject_id uuid, processor_name text NOT NULL, processor_version text NOT NULL, processing_run_id uuid NOT NULL, computed_at timestamptz NOT NULL DEFAULT now(), artifact_payload jsonb NOT NULL); CREATE TABLE artifact_event_lineage ( artifact_id uuid NOT NULL REFERENCES derived_artifact(artifact_id), event_id uuid NOT NULL REFERENCES operational_events(event_id), role text, -- e.g. 'input', 'trigger', 'correction' PRIMARY KEY (artifact_id, event_id));This structure supports queries like “show all events that contributed to invoice X” and “list all artifacts impacted by event Y.”
Data lineage across systems: exports, ETL/ELT, and analytics
Operational events often feed warehouses, search indexes, and third-party systems. Lineage must cross system boundaries. The simplest reliable technique is to propagate identifiers and store “delivery receipts” as first-class records.
Outward lineage: recording deliveries
When you export events or derived artifacts, record:
what was sent: event_id or artifact_id range/list
where it was sent: destination system, dataset, topic, bucket, table
when and by what: exporter name/version, run id
result: success/failure, retry count, destination offsets or acknowledgments
Example: export log table
CREATE TABLE export_log ( export_id uuid PRIMARY KEY, destination text NOT NULL, exporter_name text NOT NULL, exporter_version text NOT NULL, processing_run_id uuid NOT NULL, started_at timestamptz NOT NULL DEFAULT now(), finished_at timestamptz, status text NOT NULL, details jsonb NOT NULL DEFAULT '{}'::jsonb); CREATE TABLE export_log_events ( export_id uuid NOT NULL REFERENCES export_log(export_id), event_id uuid NOT NULL REFERENCES operational_events(event_id), PRIMARY KEY (export_id, event_id));This gives you a concrete lineage chain: operational event → export run → destination. If a downstream system reports an issue, you can identify exactly which export and which events were involved.
Auditing access and reads: who saw what (when required)
Some domains require auditing not only changes but also access to sensitive data (for example, support staff viewing customer records). Operational events can represent access actions too, but you must be careful about volume and privacy.
Practical approach:
Log access events at the application boundary for sensitive endpoints/actions, not every SELECT in the database.
Store minimal necessary data: subject id, actor id, reason, and a coarse “what was accessed” descriptor.
Separate access logs from business events if retention and permissions differ.
Example: access audit event
CREATE TABLE access_audit_events ( access_event_id uuid PRIMARY KEY, occurred_at timestamptz NOT NULL, actor_id uuid NOT NULL, actor_role text, action text NOT NULL, -- e.g. 'VIEW_CUSTOMER_PROFILE' subject_type text NOT NULL, subject_id uuid NOT NULL, request_id uuid, trace_id text, reason_code text, meta jsonb NOT NULL DEFAULT '{}'::jsonb);Use strict permissions for this table, and consider additional controls (row-level security, separate database) depending on sensitivity.
Handling corrections: audit-friendly reversals and annotations
Audits often involve corrections: a mistaken action, a late-arriving fact, or a compliance-driven adjustment. From an audit perspective, the goal is to preserve the original record and record the correction as a new fact, while making the relationship explicit.
Add metadata that links a correcting event to the event(s) it corrects:
correlates_to_event_id: points to the original event being corrected
correction_type: reversal, amendment, supersede
correction_reason: human-readable or coded reason
ALTER TABLE operational_events ADD COLUMN correlates_to_event_id uuid, ADD COLUMN correction_type text, ADD COLUMN correction_reason text;Then you can build audit queries that show an original event and all subsequent corrections, without deleting or rewriting history.
Audit queries you should be able to run
Design is only successful if it supports real questions. Here are common audit/lineage queries and what they rely on.
Who performed actions on a specific entity in a time window?
SELECT occurred_at, event_type, actor_id, actor_type, request_id, reason_code FROM operational_events WHERE aggregate_type = 'account' AND aggregate_id = $1 AND occurred_at >= $2 AND occurred_at < $3 ORDER BY occurred_at;Why does a read model row have its current value?
SELECT rm.account_id, rm.status, rm.balance_cents, rm.last_event_id, rm.processor_name, rm.processor_version, e.event_type, e.occurred_at, e.actor_id, e.reason_code, e.payload FROM account_read_model rm JOIN operational_events e ON e.event_id = rm.last_event_id WHERE rm.account_id = $1;Which downstream artifacts were impacted by a specific event?
SELECT a.artifact_id, a.artifact_type, a.computed_at, a.processor_name, a.processor_version FROM artifact_event_lineage l JOIN derived_artifact a ON a.artifact_id = l.artifact_id WHERE l.event_id = $1 ORDER BY a.computed_at DESC;Which events were exported to a destination during a specific run?
SELECT el.destination, el.exporter_name, el.exporter_version, el.processing_run_id, e.event_id, e.event_type, e.occurred_at FROM export_log el JOIN export_log_events ele ON ele.export_id = el.export_id JOIN operational_events e ON e.event_id = ele.event_id WHERE el.processing_run_id = $1 ORDER BY e.occurred_at;Operational controls: permissions, retention, and privacy constraints
Audit and lineage data is sensitive. Implement controls that match your risk profile:
Least privilege: application roles should not be able to modify audit/event tables; analysts may need read-only access to subsets.
Row-level security (RLS): restrict access by tenant or by role for sensitive audit logs.
Retention policies: define how long you keep raw events, access logs, and export logs. Retention may differ by type.
Redaction strategy: avoid storing secrets in payload/meta; for personal data, store references or tokenize where possible. If you must delete personal data, plan how to preserve audit integrity (for example, keep hashes and minimal identifiers while removing content).
Separation of duties: restrict who can access audit logs vs who can deploy code that writes them.
In PostgreSQL, combine schema separation (dedicated schema for audit), distinct roles, and RLS policies for multi-tenant audit access. Also consider storing especially sensitive audit logs in a separate database instance to reduce blast radius.
Processing lineage: tracking the “how” of transformations
Lineage is not only about input events; it is also about the transformation logic. Two runs applying the same events with different code can produce different outputs. Capture processing metadata systematically:
processor_version: a build identifier (git SHA, semantic version)
configuration fingerprint: hash of relevant config (feature flags, thresholds)
run identity: processing_run_id, start/end timestamps
input boundaries: which event ids/time range were considered
Example: processing run registry
CREATE TABLE processing_runs ( processing_run_id uuid PRIMARY KEY, processor_name text NOT NULL, processor_version text NOT NULL, config_hash bytea, started_at timestamptz NOT NULL DEFAULT now(), finished_at timestamptz, status text NOT NULL, input_details jsonb NOT NULL DEFAULT '{}'::jsonb);When a projection update occurs, store processing_run_id in the derived row and ensure the run record exists. This allows you to answer “which deployment produced this value?” and to reproduce results by re-running the same processor version with the same configuration.
