The Audit Trail and Domain History Pattern records an immutable, actor-attributed timeline of what business state changed, who initiated or delegated the change, why it happened, and what the before-and-after values were, stored atomically alongside domain mutations. While technical SIEM logs (Security Logging and Audit Trails, Structured Logging) track authentication events and perimeter denials, domain history answers regulatory, financial, and customer-facing accountability questions inside the product itself ("Show me every discount threshold override on Contract #4812, who approved it, under whose impersonation session, and restore Tuesday's draft").
For coding agents building enterprise SaaS, fintech, healthcare, or workflow platforms, slapping updated_at and updated_by columns onto mutable tables destroys causal history on the second edit, while building full Event Sourcing across every CRUD entity introduces massive projection complexity. A structured Transactional Domain Audit Log gives organizations queryable history, compliance evidence (Security Compliance Frameworks), and safe undo/time-travel semantics without rewriting every aggregate as an event stream. For related architectural foundations, consult Architecture Hub, Database Design, and Multi-Tenant Isolation Pattern.
Teams frequently conflate application observability logs, entity change history, and event-sourced state persistence. Each solves a different correctness and retention requirement:
graph TD
Mutation["Domain Command Inside DB Transaction"] --> State["Update Current Entity Row (orders / invoices)"]
Mutation --> AuditRow["Insert Append-Only audit_events Row (Same SQL Tx)"]
AuditRow --> Chain["Optional Per-Tenant Hash Chain (prev_hash -> entry_hash)"]
AuditRow --> Outbox["Optional Outbox Relay -> Cold WORM Archive / SIEM"]
UI["Enterprise History Drawer / Compliance Export"] -->|"Read timeline by aggregate_id"| AuditRow
click Mutation "/wiki/VerticalSliceArchitecture" "Vertical Slice Architecture"
click State "/wiki/DatabaseDesign" "Database Design"
click Outbox "/wiki/OutboxPattern" "Outbox Pattern"
UI --> AuditRow
| Strategy | Atomic with Business Mutation? | Queryable inside Product UI? | Schema Coupling & Migration Cost | Ideal Use Case |
|---|---|---|---|---|
Raw Application Logs (stdout JSON) | No (can drop or succeed after DB rollback) | No (stuck in log aggregator; short TTL) | Low | Operational debugging, latency traces, HTTP access telemetry |
PostgreSQL CDC / Triggers (audit_trigger) | Yes | Moderate (loses HTTP request reason, ticket ID, impersonator) | Very low app code; high trigger maintenance | Catch-all forensic table change capture without app instrumentation |
| Transactional Domain Audit Ledger (Recommended) | Yes (same ACID commit) | Yes (rich UI timelines + compliance CSV) | Low–moderate (audit_events + explicit domain diffs) | Enterprise CRUD, workflow approvals, billing/policy changes, SOC 2/HIPAA |
| Event Sourcing | Yes (events are the DB) | Yes via projections | High (upcasting, snapshotting, eventual consistency) | Double-entry financial ledgers, collaborative docs, complex temporal domains |
Unlike raw database triggers—which see only DB_USER = app_runtime and have no knowledge of the business reason, OAuth impersonation chain (admin_user acting_as customer_user), or upstream trace_id—a Transactional Domain Audit Ledger passes explicit actor/intent context via PostgreSQL transaction session parameters or direct repository helpers inside the exact SQL transaction that modifies state.
Enterprise compliance frameworks (SOC 2 Type II, ISO 27001, SOX, HIPAA) commonly mandate 1 to 7 years of retained audit history, making row sizing and partitioning mandatory design calculations upfront.
Consider a B2B SaaS platform serving \lambda = 400 write transactions/second at business-hour peak (averaging 150\text{ writes/s} over a 24-hour day):
audit_events row occupies B_{\text{row}} = 1.1\text{ KB} (UUID keys, metadata, JSONB before/after changed-fields delta, plus B-tree index overhead of \approx 35\%, totaling \approx 1.48\text{ KB/row}):Without monthly time-range partitioning and tiered cold archival (streaming partitions older than 90 days to immutable Parquet on object storage with WORM Object Lock), an unpartitioned single table hits 7\text{ TB/year}, grinding VACUUM, index scans, and backups to a halt. Recording changed-field deltas (changed_fields: {"limitCents": {"old": 50000, "new": 250000}}) rather than dumping full 20\text{ KB} JSON snapshots on every minor status flip cuts storage footprint by 80–92% and simplifies UI diff rendering.
If tamper-evidence is required against compromised DB credentials, computing a SHA-256 chain entry_hash = SHA256(prev_hash || canonical_json) adds < 0.05 ms CPU per write, though serializing prev_hash strictly per-tenant requires a lightweight lock on the tenant's hash-head row—partitioning the chain key by (tenant_id, bucket_shard) (e.g., 16 hash chains per high-throughput tenant) keeps write contention negligible while preserving cryptographic verification across every shard.
Two database details distinguish genuine enterprise audit tables from cosmetic log tables: append-only SQL privileges (app_runtime gets INSERT, SELECT only—UPDATE and DELETE are revoked even for the application role!) and explicit impersonation + PII handling.
-- Append-only partitioned domain audit ledger (PostgreSQL 15+)
CREATE TABLE domain_audit_events (
tenant_id uuid NOT NULL,
event_id uuid NOT NULL DEFAULT gen_random_uuid(),
occurred_at timestamptz NOT NULL DEFAULT now(),
aggregate_type text NOT NULL, -- e.g. 'PurchaseOrder', 'RoleAssignment'
aggregate_id text NOT NULL,
action text NOT NULL, -- e.g. 'po.limit_updated', 'role.granted'
actor_type text NOT NULL CHECK (actor_type IN ('USER', 'SERVICE_ACCOUNT', 'SYSTEM', 'AI_AGENT')),
actor_id text NOT NULL,
impersonator_id text NULL, -- non-null during customer-support impersonation
reason_code text NULL, -- user-supplied justification or workflow ticket ID
correlation_id text NOT NULL, -- matches OpenTelemetry trace_id / X-Request-ID
client_ip_hash text NULL, -- keyed HMAC or truncated subnet if privacy rules apply
changed_fields jsonb NOT NULL, -- delta map: {"field": {"before": ..., "after": ...}}
entry_hash bytea NULL, -- optional SHA-256 tamper-evidence digest
PRIMARY KEY (tenant_id, occurred_at, event_id)
) PARTITION BY RANGE (occurred_at);
CREATE INDEX idx_audit_tenant_aggregate
ON domain_audit_events (tenant_id, aggregate_type, aggregate_id, occurred_at DESC);
CREATE INDEX idx_audit_tenant_actor
ON domain_audit_events (tenant_id, actor_id, occurred_at DESC);
-- Enable tenant RLS & enforce strict append-only permissions
ALTER TABLE domain_audit_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE domain_audit_events FORCE ROW LEVEL SECURITY;
CREATE POLICY audit_tenant_isolation ON domain_audit_events
AS RESTRICTIVE
FOR ALL
TO app_runtime
USING (NULLIF(current_setting('app.current_tenant_id', true), '')::uuid = tenant_id)
WITH CHECK (NULLIF(current_setting('app.current_tenant_id', true), '')::uuid = tenant_id);
REVOKE UPDATE, DELETE, TRUNCATE ON domain_audit_events FROM PUBLIC, app_runtime;
GRANT SELECT, INSERT ON domain_audit_events TO app_runtime;
// Shared domain audit recorder — computes clean deltas while redacting secrets/tokens
const REDACTED_KEYS = new Set([
"passwordHash",
"apiSecret",
"clientSecret",
"accessToken",
"refreshToken",
"taxId",
"ssn",
]);
export interface FieldDiff {
readonly before: unknown;
readonly after: unknown;
}
export function computeRedactedDiff(
beforeObj: Record<string, unknown>,
afterObj: Record<string, unknown>
): Record<string, FieldDiff> {
const keys = new Set([...Object.keys(beforeObj), ...Object.keys(afterObj)]);
const diffs: Record<string, FieldDiff> = {};
for (const key of keys) {
const prev = beforeObj[key] ?? null;
const next = afterObj[key] ?? null;
if (JSON.stringify(prev) === JSON.stringify(next)) {
continue;
}
if (REDACTED_KEYS.has(key)) {
diffs[key] = { before: prev === null ? null : "[REDACTED]", after: next === null ? null : "[REDACTED]" };
} else {
diffs[key] = { before: prev, after: next };
}
}
return diffs;
}
When GDPR or CCPA right-to-erasure requests occur, never DELETE historical audit entries (which breaks accounting continuity and hash chains). Instead, store personal identifiers (email, fullName) in an encrypted per-user crypto-shredding lookup or replace only direct PII fields via a single privileged compliance job that logs a compliance.subject_erased tombstone referencing the subject token while leaving financial amounts, timestamps, and action semantics intact.
async logging that lies after a rollback: Writing audit events over HTTP/Kafka before COMMIT, or in a detached try { ... } catch {} after COMMIT, guarantees phantom audit records ("logs say status changed to APPROVED, database still says REJECTED") or missing records during pod crashes. Commit the domain_audit_events row inside the exact same ACID transaction as the domain state update.req.body or database rows into changed_fields silently archives plaintext webhook secrets, temporary passwords, or OAuth tokens into an append-only compliance table where retention lasts 7 years! Always run a deterministic key redaction/allowlist sanitizer before serialization.impersonator_id and actor_type = AI_AGENT attribution: When support engineers impersonate an enterprise admin—or when an autonomous AI agent triggers a mutation after human approval (Workflow Approval and Human-in-the-Loop Pattern)—recording only the target user's ID destroys accountability. Preserve both actor_id (effective identity) and impersonator_id / delegated_by + reason_code (support ticket or approval ID).SELECT * ... LIMIT 1000000 synchronously into Node/Python memory causes OOM kills. Stream paged keyset queries (occurred_at, event_id) < ($1, $2) or execute exports asynchronously via Background Job Processing and pre-signed object storage URLs.When an automated coding agent adds stateful enterprise CRUD, security policy modifications, financial mutations, or agentic workflow actions, require the following audit structure:
appendDomainAudit(tx, ctx, { aggregateType, aggregateId, action, changedFields, reasonCode }) inside the same PostgreSQL transaction (withTenantTransaction)—never outside it.tenant_id, actor_type, actor_id, optional impersonator_id, correlation_id, action, and sanitized changed_fields (with secrets/credentials hashed or [REDACTED]).GET /api/audit-events?aggregateType=...&aggregateId=...&cursor=...) protected by Fine-Grained Authorization Pattern (audit:read) returning chronological human-readable deltas.appendDomainAudit(...) before COMMIT -> assert zero rows inserted in domain_audit_events.UPDATE or DELETE on domain_audit_events as the runtime DB role (app_runtime) -> assert PostgreSQL raises permission denied.changed_fields stores [REDACTED] instead of raw secret and both actor_id and impersonator_id are persisted accurately.