/schema

Audit Log Schema Reference

Purpose: Design append-only audit-log tables that record who did what, when, and to what — with before/after images, correlation IDs, retention policy, and tamper-evidence. Used for compliance (SOC2 / HIPAA / PCI-DSS / GDPR), security forensics, and operational debugging.

Scope Boundary

• schema audit-log: Append-only audit table design (this document).
• schema event-sourcing (elsewhere): Event store as system-of-record (different intent).
• Cloak (elsewhere): PII handling rules; audit logs that contain PII inherit Cloak constraints.
• Canon[regulatory] (elsewhere): Regulatory mapping (SOC2 CC7.2 / HIPAA §164.312(b) / PCI-DSS 10).
• Beacon (elsewhere): Operational logs (different from audit logs).
• Vigil (elsewhere): Detection rules over audit logs.

Audit Log vs Other Logs

Audit logs MUST be append-only at the schema/storage level — never UPDATE, never DELETE.

Core Fields

Schema (Postgres example)

CREATE TYPE audit_actor_type AS ENUM ('user', 'service', 'system', 'anonymous');
CREATE TYPE audit_result    AS ENUM ('success', 'failure', 'denied');

CREATE TABLE audit_log (
  audit_id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  occurred_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  actor_type      audit_actor_type NOT NULL,
  actor_id        TEXT,
  actor_ip        INET,
  actor_user_agent TEXT,
  action          TEXT NOT NULL,
  target_type     TEXT NOT NULL,
  target_id       TEXT NOT NULL,
  before_image    JSONB,
  after_image     JSONB,
  result          audit_result NOT NULL,
  failure_reason  TEXT,
  correlation_id  UUID,
  request_id      UUID,
  session_id      UUID,
  tenant_id       TEXT,
  metadata        JSONB,
  prev_hash       BYTEA,
  row_hash        BYTEA
)
PARTITION BY RANGE (occurred_at);

-- Monthly partition example
CREATE TABLE audit_log_2026_04 PARTITION OF audit_log
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

-- Indexes
CREATE INDEX ix_audit_actor   ON audit_log (actor_type, actor_id, occurred_at DESC);
CREATE INDEX ix_audit_target  ON audit_log (target_type, target_id, occurred_at DESC);
CREATE INDEX ix_audit_action  ON audit_log (action, occurred_at DESC);
CREATE INDEX ix_audit_corr    ON audit_log (correlation_id) WHERE correlation_id IS NOT NULL;
CREATE INDEX ix_audit_tenant  ON audit_log (tenant_id, occurred_at DESC) WHERE tenant_id IS NOT NULL;

-- Append-only enforcement
REVOKE UPDATE, DELETE ON audit_log FROM PUBLIC;
-- Only allow INSERT for application role; admin/superuser cannot UPDATE/DELETE without explicit grant

Capture Strategies

1. Application-layer (recommended default)

App emits audit events as part of the same transaction as the business write.

• Pros: full business context, easy to add metadata.
• Cons: discipline required; missed sites = audit gaps.
• Pattern: middleware or aspect layer that wraps mutating calls.

2. Database trigger-based

AFTER INSERT/UPDATE/DELETE triggers populate audit table.

• Pros: cannot be bypassed; works for any client.
• Cons: limited business context (no actor / IP unless set as session var); migration-heavy.
• Pattern: current_setting('app.actor_id') set per session.

3. CDC / logical replication (Debezium)

Read WAL → topic → audit consumer → audit table.

• Pros: zero app overhead; eventually consistent.
• Cons: eventually consistent; dual storage cost; delete-before-CDC race.
• Pattern: Stream agent owns the pipeline.

4. Postgres temporal tables / SQL:2011

System-period temporal tables (SQL:2011-style) automatically maintain history.

• PostgreSQL 18 status (2026-05): built-in PRIMARY KEY ... WITHOUT OVERLAPS and FOREIGN KEY ... PERIOD cover application-time / valid-time. System-time / transaction-time (FOR SYSTEM_TIME AS OF) is not yet built in — community proposals are in review for PG 19. Today, approximate with a recorded_at / invalidated_at pair plus an append-only audit table.
• Pros: standards-aligned; valid-time uniqueness no longer needs EXCLUDE USING gist.
• Cons: less explicit actor context; transaction-time half still DIY.

Selection matrix

Combine app-layer + trigger-based as belt-and-suspenders for high-stakes systems.

Retention & WORM

WORM (Write-Once-Read-Many) options:

• AWS S3 Object Lock (Compliance mode).
• Azure Immutable Blob Storage.
• GCS Bucket Lock.
• Postgres partitioning + revoke modify on closed partitions + tape/cold storage.

Tamper Evidence (Hash Chain / Merkle Tree)

For high-trust contexts (financial, healthcare, government, KYC) the 2026 baseline is:

Linear HMAC chain (simplest)

row_hash_n  = HMAC(secret, canonicalize(row_n))
prev_hash_n = HMAC(secret, row_hash_{n-1})

-- Verification: walk the chain, recompute, compare.

• Pros: cheap to compute, append-only friendly.
• Cons: O(N) verification; one-row corruption forces full re-verify.

Merkle tree per partition (current best practice for high-volume audit logs)

Bucket rows by partition (daily / hourly), compute a Merkle root per bucket, store the root in a separate immutable audit_merkle_roots table.

CREATE TABLE audit_merkle_roots (
  bucket_start TIMESTAMPTZ PRIMARY KEY,
  root_hash    BYTEA       NOT NULL,
  leaf_count   BIGINT      NOT NULL,
  anchored_at  TIMESTAMPTZ,
  anchor_ref   TEXT       -- TSA token / S3 Object Lock id / chain tx hash
);

• O(log N) verification for any single row.
• A corrupted row invalidates only the affected leaf path, not the whole log.
• Common pattern in 2025–2026 fintech / KYC stacks; aligns with how AWS QLDB (deprecated 2024-07; AWS recommends Aurora PostgreSQL with verifiable hash chains as the replacement) modelled the journal.

External anchoring (independent third party)

Periodically (hourly / daily) commit the latest root hash to:

• RFC 3161 Time-Stamp Authority — lightweight, accepted by most regulators (eIDAS in EU, JIPDEC in JP).
• S3 Object Lock (Compliance mode) cross-region copies — same provider but enforced retention.
• Public blockchain anchoring (e.g., OpenTimestamps over Bitcoin) — heavy but maximum independence; usually reserved for KYC/AML systems.

PII Handling

Audit logs often contain PII (names, emails, IP). Coordinate with Cloak:

• Explicit PII fields: tag at schema level.
• Pseudonymize where possible (hashed user_id).
• For GDPR right-to-erasure: replace PII with placeholder, keep audit row + reference; do NOT delete audit rows.
• Region-pinning: keep EU subjects' audit data in EU region.

Workflow

INVENTORY     →  list mutating actions to audit (CRUD on critical entities)
              →  classify: PII / financial / config / auth / admin

CHOOSE        →  capture strategy (app / trigger / CDC / temporal)
              →  partitioning (monthly default for high-volume)

DESIGN        →  fields per row (use core 20 fields above + per-domain extensions)
              →  before/after image granularity (full row / changed-cols only / event-shaped)

GUARD         →  REVOKE UPDATE/DELETE
              →  WORM / immutable storage
              →  hash chain if regulatory or tamper concern

INDEX         →  by actor / target / correlation / tenant + time-DESC
              →  partition pruning verified

RETAIN        →  partition rotation + cold archive
              →  legal-hold mechanism

CLOAK         →  PII tagging + GDPR erasure pathway
              →  region pinning for sovereignty

VERIFY        →  inject synthetic audit events; verify capture
              →  retention policy applies
              →  attempt UPDATE/DELETE → must fail

HANDOFF       →  Canon[regulatory]: regulatory mapping
              →  Cloak: PII rules
              →  Beacon: operational log separation
              →  Vigil: detection rules over audit
              →  Stream: CDC pipeline if chosen
              →  Builder: middleware / triggers

Output Template

## Audit Log Schema: [System / Tenant]

### Inventory
| Action | Entity | PII? | Strategy |
|--------|--------|------|----------|
| user.create | user | Yes | app + trigger |
| payment.refund | payment | No | app |
| ... | ... | ... | ... |

### Schema
[DDL]

### Capture Strategy
- **Default**: [app-layer / trigger / CDC / temporal]
- **Combined?**: [yes/no, rationale]

### Append-Only Guards
- [ ] REVOKE UPDATE/DELETE
- [ ] WORM tier configured
- [ ] Hash chain (if applicable)

### Retention
- **Online**: [N months]
- **Archive**: [N years]
- **WORM**: [yes/no, mechanism]
- **Legal hold**: [process]

### PII Handling
- Tagged fields: [list]
- GDPR erasure pathway: [pseudonymize / placeholder]
- Region pinning: [yes/no]

### Indexing & Partitioning
- Partition: [monthly by occurred_at]
- Indexes: [list]

### Verification Plan
- [ ] Synthetic events captured
- [ ] Retention auto-rotation
- [ ] UPDATE/DELETE attempts fail

### Handoffs
- Canon[regulatory]: SOC2/HIPAA/PCI mapping
- Cloak: PII rules
- Beacon: log separation
- Vigil: detection rules
- Stream: CDC pipeline
- Builder: middleware

Anti-Patterns

Deliverable Contract

When audit-log completes, emit:

• Inventory of audited actions with PII flag.
• Schema DDL with append-only guards.
• Capture strategy with rationale.
• Retention + WORM plan.
• PII handling + GDPR erasure pathway.
• Index + partitioning strategy.
• Verification plan.
• Handoffs: Canon[regulatory], Cloak, Beacon, Vigil, Stream, Builder.

References

• SQL:2011 — System-Period Temporal Tables
• PostgreSQL — pg_audit extension, temporal_tables extension
• AWS S3 Object Lock — Compliance mode (WORM)
• NIST SP 800-92 — Guide to Computer Security Log Management
• ISO/IEC 27037 — Identification, collection, acquisition of digital evidence
• RFC 3161 — Time-Stamp Protocol (TSP)
• Debezium — CDC connector documentation
• Martin Fowler — Audit Log pattern
• O'Reilly — Designing Data-Intensive Applications (audit + change-data chapters)
• SOC2 CC7.2, HIPAA §164.312(b), PCI-DSS Requirement 10