Audit Trail and Domain History Pattern

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.


1. Domain Audit Ledger vs. Application Logs vs. Full Event Sourcing

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
StrategyAtomic with Business Mutation?Queryable inside Product UI?Schema Coupling & Migration CostIdeal Use Case
Raw Application Logs (stdout JSON)No (can drop or succeed after DB rollback)No (stuck in log aggregator; short TTL)LowOperational debugging, latency traces, HTTP access telemetry
PostgreSQL CDC / Triggers (audit_trigger)YesModerate (loses HTTP request reason, ticket ID, impersonator)Very low app code; high trigger maintenanceCatch-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 SourcingYes (events are the DB)Yes via projectionsHigh (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.


2. Quantifying Retention Growth, Storage Budgets, and Hash-Chain Overhead

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):

N_{\text{day}} = 150\text{ events/s} \times 86{,}400\text{ s/day} = 12{,}960{,}000 \approx 1.30 \times 10^7\text{ rows/day}
S_{\text{day}} = 12{,}960{,}000 \times 1.48\text{ KB} \approx 19.18\text{ GB/day} \approx 575\text{ GB/month}

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.


3. Immutable Append-Only Schema, Actor Attribution, and PII Redaction

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;

Structured Audit Diff Helper with Secret/PII Masking

// 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.


4. Production Failure Modes and Compliance Blind Spots

  1. Out-of-band 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.
  2. Storing unredacted credentials or secrets inside JSONB snapshots: Dumping raw 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.
  3. Missing 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).
  4. Unbounded export endpoints crashing API pods: Enterprise auditors regularly export 6 months of history as CSV/JSON. Calling 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.

5. Agent Implementation & Output Contract

When an automated coding agent adds stateful enterprise CRUD, security policy modifications, financial mutations, or agentic workflow actions, require the following audit structure:

Required Architectural Outputs

  1. Atomic audit helper inside the transaction: Every state-mutating write slice (Vertical Slice Architecture) invokes appendDomainAudit(tx, ctx, { aggregateType, aggregateId, action, changedFields, reasonCode }) inside the same PostgreSQL transaction (withTenantTransaction)—never outside it.
  2. Mandatory field set & redaction: Every record populates tenant_id, actor_type, actor_id, optional impersonator_id, correlation_id, action, and sanitized changed_fields (with secrets/credentials hashed or [REDACTED]).
  3. Read-timeline API contract: Provide a tenant-scoped, cursor-paginated timeline endpoint (GET /api/audit-events?aggregateType=...&aggregateId=...&cursor=...) protected by Fine-Grained Authorization Pattern (audit:read) returning chronological human-readable deltas.

Mandatory Test Cases to Generate

  1. Transaction rollback parity test: Force an exception immediately after appendDomainAudit(...) before COMMIT -> assert zero rows inserted in domain_audit_events.
  2. Immutability enforcement test: Attempt UPDATE or DELETE on domain_audit_events as the runtime DB role (app_runtime) -> assert PostgreSQL raises permission denied.
  3. Redaction & attribution test: Mutate a record containing a sensitive token field under impersonation context -> assert changed_fields stores [REDACTED] instead of raw secret and both actor_id and impersonator_id are persisted accurately.