JSON Audit Trail: Event Schema, Immutability & GDPR-Compliant Design

Last updated: May 20, 2026

Written and reviewed by the Jsonic editorial team — every guide is verified against the official spec or runtime before publication.

An audit trail is only as trustworthy as its design. Most teams treat audit logging as an afterthought — appending raw strings or unstructured blobs — and discover too late that they cannot answer a forensic question like "what did this user change and when, and has any record been altered?" A JSON audit trail solves this by encoding every event as a structured, typed document with a canonical schema, cryptographic linkage to its predecessor, and storage controls that prevent retroactive modification. This guide covers the complete lifecycle: designing the event schema with the right fields, enforcing immutability through append-only tables and Merkle hash chains, making the trail GDPR-compliant by pseudonymizing actor identities, capturing before/after diffs in RFC 6902 JSON Patch format, choosing the right storage tier for the query pattern, reconstructing resource history from a sequence of events, and satisfying the retention requirements of SOC 2, HIPAA, and PCI-DSS. Every section includes concrete JSON examples and SQL you can run today.

JSON Audit Event Schema Design

The event schema is the foundation of your audit trail. Every field must serve a specific forensic or compliance purpose. An event that lacks a stable eventId cannot be deduplicated across retry attempts; one without a structured action cannot be filtered by event type; one with a raw email in actorId cannot be GDPR-compliant. The schema below is the canonical starting point, with every field justified.

// Canonical JSON audit event schema
{
  "eventId":    "01HQ7N3P4FGXR8KYWZ5M6D2J0T",   // ULID — sortable, unique, no collision
  "actorId":    "actor_a3f9b2c1",                  // pseudonymized reference, NOT email
  "actorType":  "user",                             // "user" | "service" | "system"
  "action":     "order:update",                     // namespace:verb pattern
  "resource":   "Order",                            // entity type
  "resourceId": "ord_78432",                        // entity identifier
  "timestamp":  "2026-02-06T14:32:00.000Z",         // ISO 8601, always UTC with Z
  "context": {
    "ip":        "198.51.100.x",                    // last octet masked for GDPR
    "userAgent": "Mozilla/5.0 ...",
    "sessionId": "sess_9d3f2a",
    "requestId": "req_b7c4e1"                       // for log correlation
  },
  "diff": {
    "patch": [                                       // RFC 6902 JSON Patch
      { "op": "replace", "path": "/status", "value": "shipped" },
      { "op": "add",     "path": "/shippedAt", "value": "2026-02-06T14:30:00Z" }
    ],
    "before": { "status": "paid",    "amount": 149.99 },  // optional full snapshot
    "after":  { "status": "shipped", "amount": 149.99 }
  },
  "prevHash":   "e3b0c44298fc1c149afbf4c8996fb924...",  // SHA-256 of previous event
  "eventHash":  "a665a45920422f9d417e4867efdc4fb8..."   // SHA-256 of this event's fields
}

-- PostgreSQL: append-only audit table with immutability trigger
CREATE TABLE audit_events (
    id          bigserial PRIMARY KEY,
    data        jsonb     NOT NULL,
    inserted_at timestamptz NOT NULL DEFAULT now()
);

-- Trigger blocks UPDATE and DELETE at the database level
CREATE OR REPLACE FUNCTION prevent_audit_mutation()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'audit_events is append-only: % is not allowed', TG_OP;
END;
$$;

CREATE TRIGGER no_audit_mutation
BEFORE UPDATE OR DELETE ON audit_events
FOR EACH ROW EXECUTE FUNCTION prevent_audit_mutation();

-- Index for common query patterns
CREATE INDEX idx_audit_resource     ON audit_events ((data->>'resource'), (data->>'resourceId'));
CREATE INDEX idx_audit_actor        ON audit_events ((data->>'actorId'));
CREATE INDEX idx_audit_action       ON audit_events ((data->>'action'));
CREATE INDEX idx_audit_timestamp    ON audit_events (((data->>'timestamp')::timestamptz));

Use ULID (Universally Unique Lexicographically Sortable Identifier) for eventId instead of UUID v4 — ULIDs sort chronologically by default, which makes range scans on eventId align with time-based queries without a separate timestamp index. The action field uses a namespace:verb convention rather than free-text so that wildcard queries like action LIKE 'order:%' return all order-related events without schema knowledge. See our JSON audit logging guide for application-layer instrumentation patterns.

Immutability: Ensuring Audit Records Cannot Be Altered

Immutability is not a single control — it is a defense-in-depth stack. A database trigger alone can be bypassed by a superuser. WORM storage alone does not prove the data was never altered before archiving. A Merkle hash chain alone requires trusting the verification process. Together, these controls create overlapping guarantees that satisfy auditors from SOC 2 to HIPAA.

-- ── Layer 1: PostgreSQL append-only + row-level security ────────────
-- Grant INSERT only to the audit service role — no UPDATE, no DELETE
GRANT INSERT ON audit_events TO audit_service;
REVOKE UPDATE, DELETE ON audit_events FROM audit_service;

-- Row-level security: audit_service can only see its own writes (optional)
ALTER TABLE audit_events ENABLE ROW LEVEL SECURITY;
CREATE POLICY audit_insert_only ON audit_events
  FOR INSERT TO audit_service WITH CHECK (true);

-- ── Layer 2: Merkle hash chain in Node.js ────────────────────────────
import crypto from 'crypto';

interface AuditEventInput {
  eventId: string;
  actorId: string;
  action: string;
  resourceId: string;
  timestamp: string;
  diff: object;
}

async function appendAuditEvent(input: AuditEventInput): Promise<void> {
  // Fetch the hash of the most recent event (or genesis hash for first event)
  const { rows } = await db.query(
    "SELECT data->>'eventHash' AS prev_hash FROM audit_events ORDER BY id DESC LIMIT 1"
  );
  const prevHash = rows[0]?.prev_hash ?? '0'.repeat(64);  // genesis sentinel

  // Compute this event's hash over a canonical field set
  const canonical = JSON.stringify({
    eventId:    input.eventId,
    actorId:    input.actorId,
    action:     input.action,
    resourceId: input.resourceId,
    timestamp:  input.timestamp,
    prevHash,
  });
  const eventHash = crypto.createHash('sha256').update(canonical).digest('hex');

  const event = { ...input, prevHash, eventHash };

  // INSERT — trigger blocks any UPDATE/DELETE
  await db.query('INSERT INTO audit_events (data) VALUES ($1)', [JSON.stringify(event)]);
}

// ── Layer 3: WORM storage export (AWS S3 Object Lock) ──────────────
// After inserting, export to S3 with Object Lock in Compliance mode.
// Even root cannot delete the object during the retention period.
import { S3Client, PutObjectCommand } from '@aws-sdk/client-s3';

const s3 = new S3Client({ region: 'us-east-1' });

async function archiveToWORM(event: object): Promise<void> {
  const key = `audit/${new Date().toISOString().slice(0,10)}/${event.eventId}.json`;
  await s3.send(new PutObjectCommand({
    Bucket: 'my-audit-worm-bucket',
    Key:    key,
    Body:   JSON.stringify(event),
    ContentType: 'application/json',
    ObjectLockMode: 'COMPLIANCE',
    ObjectLockRetainUntilDate: new Date(Date.now() + 365 * 7 * 24 * 60 * 60 * 1000), // 7 years
  }));
}

// ── Layer 4: Chain verification job (run daily) ─────────────────────
async function verifyChainIntegrity(): Promise<void> {
  const events = await db.query(
    "SELECT data FROM audit_events ORDER BY id ASC"
  );
  let expectedPrevHash = '0'.repeat(64);

  for (const row of events.rows) {
    const ev = row.data;
    const canonical = JSON.stringify({
      eventId: ev.eventId, actorId: ev.actorId, action: ev.action,
      resourceId: ev.resourceId, timestamp: ev.timestamp, prevHash: ev.prevHash,
    });
    const computedHash = crypto.createHash('sha256').update(canonical).digest('hex');

    if (ev.prevHash !== expectedPrevHash) {
      throw new Error(`Chain break at eventId ${ev.eventId}: prevHash mismatch`);
    }
    if (ev.eventHash !== computedHash) {
      throw new Error(`Tampering detected at eventId ${ev.eventId}: hash mismatch`);
    }
    expectedPrevHash = ev.eventHash;
  }
  console.log('Audit chain integrity verified: all hashes match');
}

The verification job should run on a schedule (daily at minimum) and alert on any chain break. Store the latest verified eventHash in a separate, locked table so the verification job can resume from the last confirmed point rather than traversing the full chain on every run. For long-term archival, see JSON storage strategies.

GDPR-Compliant Audit Trails

GDPR's right to erasure (Article 17) and the legal obligation to maintain audit records seem mutually exclusive. The resolution is architectural: separate PII from audit events at the schema level so that erasing personal data does not require touching immutable audit records. The audit events themselves contain no PII — only opaque references.

-- ── PII lookup table (mutable — supports erasure) ────────────────────
CREATE TABLE actor_identity (
    actor_id    text PRIMARY KEY,              -- e.g. "actor_a3f9b2c1"
    user_id     bigint REFERENCES users(id),
    display_name text,
    email       text,
    erased_at   timestamptz                   -- set on GDPR erasure, NULL = active
);

-- Map real user to pseudonymized actorId on first event
INSERT INTO actor_identity (actor_id, user_id, display_name, email)
VALUES ('actor_a3f9b2c1', 42, 'Alice Smith', 'alice@example.com')
ON CONFLICT (actor_id) DO NOTHING;

-- ── GDPR erasure: remove PII, keep audit reference ────────────────────
-- When user exercises right to erasure:
UPDATE actor_identity
SET
    display_name = '[erased]',
    email        = '[erased]',
    erased_at    = now()
WHERE user_id = 42;

-- Audit events are untouched and immutable.
-- Querying actor_identity for actor_a3f9b2c1 now returns "[erased]".
-- The audit trail still shows that actor_a3f9b2c1 performed action X at time T —
-- it just cannot be resolved to a real person anymore.

-- ── IP address: mask before storing ──────────────────────────────────
-- Store only first 3 octets + ".x" — sufficient for geolocation, not for PII
function maskIp(ip: string): string {
  const parts = ip.split('.');
  if (parts.length === 4) return parts.slice(0, 3).join('.') + '.x';  // IPv4
  // IPv6: keep first 4 groups only
  return ip.split(':').slice(0, 4).join(':') + ':x:x:x:x';
}

// ── Data retention policy: auto-expire old events ─────────────────────
-- PostgreSQL: delete events older than retention window
-- (run via a scheduled job — do NOT add a DELETE trigger to the table)
DELETE FROM audit_events
WHERE inserted_at < now() - INTERVAL '7 years'
  AND (data->>'resource') IN ('HealthRecord', 'PatientData');  -- HIPAA scope

DELETE FROM audit_events
WHERE inserted_at < now() - INTERVAL '1 year'
  AND (data->>'resource') IN ('PaymentCard');  -- PCI-DSS scope

-- ── Sensitive field masking before diff capture ───────────────────────
// In application code — mask before building the before/after snapshot
function sanitizeForAudit(obj: Record<string, unknown>): Record<string, unknown> {
  const MASKED_FIELDS = new Set(['password', 'token', 'secret', 'cvv', 'ssn']);
  return Object.fromEntries(
    Object.entries(obj).map(([k, v]) =>
      MASKED_FIELDS.has(k) ? [k, '[MASKED]'] : [k, v]
    )
  );
}

The pseudonymization pattern requires that actorId values be generated consistently for the same user across sessions — use a keyed HMAC: HMAC-SHA256(secret_key, user_id) truncated to 8 hex chars, prefixed with actor_. The secret key must be stored separately from the audit database so that an attacker who exfiltrates audit events cannot reverse the actor IDs without also obtaining the HMAC key. Document this pattern in your GDPR Data Protection Impact Assessment (DPIA).

Capturing Before/After State in JSON

The diff field answers the most important forensic question: what exactly changed? There are two approaches — full snapshots and RFC 6902 JSON Patch. The right choice depends on document size, query patterns, and whether you need to replay changes programmatically.

// ── Approach 1: Full before/after snapshot ────────────────────────────
// Simple to read, expensive for large documents
{
  "diff": {
    "before": {
      "status": "pending",
      "amount": 149.99,
      "assignee": null
    },
    "after": {
      "status": "paid",
      "amount": 149.99,
      "assignee": "warehouse_team"
    }
  }
}

// ── Approach 2: RFC 6902 JSON Patch (compact, replayable) ─────────────
// Each operation: { op, path, value? }
// ops: "add" | "remove" | "replace" | "move" | "copy" | "test"
{
  "diff": {
    "patch": [
      { "op": "replace", "path": "/status",   "value": "paid" },
      { "op": "replace", "path": "/assignee", "value": "warehouse_team" }
    ]
  }
}

// ── Generating JSON Patch in Node.js (fast-json-patch library) ─────────
import { compare, applyPatch } from 'fast-json-patch';

const before = { status: 'pending', amount: 149.99, assignee: null };
const after  = { status: 'paid',    amount: 149.99, assignee: 'warehouse_team' };

const patch = compare(before, after);
// [
//   { op: 'replace', path: '/status',   value: 'paid' },
//   { op: 'replace', path: '/assignee', value: 'warehouse_team' }
// ]

// ── Recommended hybrid: patch + snapshots for critical resources ───────
function buildDiff(before: object, after: object, isCritical: boolean) {
  const sanitizedBefore = sanitizeForAudit(before);
  const sanitizedAfter  = sanitizeForAudit(after);
  const patch = compare(sanitizedBefore, sanitizedAfter);

  return isCritical
    ? { patch, before: sanitizedBefore, after: sanitizedAfter }  // full snapshots
    : { patch };                                                   // patch only
}

// ── Replaying patches to reconstruct historical state ─────────────────
// Fetch all events for a resource in ascending timestamp order
const events = await getAuditEvents({ resource: 'Order', resourceId: 'ord_78432' });

// Start from the earliest known full snapshot in before
let state = events[0].diff.before;

// Apply each subsequent patch to walk the state forward
for (const event of events.slice(1)) {
  if (event.diff?.patch) {
    const result = applyPatch(state, event.diff.patch, true, false);
    state = result.newDocument;
  }
}
// state is now the current state of ord_78432 as reconstructed from audit history

// ── Size trade-off guidance ────────────────────────────────────────────
// Document size  | Recommendation
// < 1 KB         | Full before/after snapshots — easy to read, negligible cost
// 1–100 KB       | JSON Patch only — reduces storage 60–90%
// > 100 KB       | JSON Patch + store diff.changedPaths only (list of paths)

Always compute the diff within the same database transaction as the write, or from a database trigger that captures OLD and NEW row values. Computing the diff asynchronously after the fact risks capturing a state that was further modified before the diff job ran, producing an inaccurate audit record. For immutable PostgreSQL triggers, OLD.* and NEW.* provide the before and after states at the exact moment of the write.

Audit Trail Storage Options

No single storage tier is optimal for all audit trail access patterns. Recent events need sub-second query latency for real-time dashboards; historical events need cost-effective compression for 7-year compliance retention; analytical events need columnar scan performance for aggregate reporting. A tiered architecture uses the right tool for each pattern.

-- ── Tier 1: PostgreSQL (0–90 days) — transactional, immediately queryable ──
-- Append-only table with jsonb column and compound indexes
-- Best for: real-time dashboards, actor/resource lookups, recent forensics
-- Capacity: ~100M events/month manageable with proper indexing
-- Cost: ~$0.10/GB/month (RDS), higher but query-capable

CREATE TABLE audit_events (
    id          bigserial     PRIMARY KEY,
    data        jsonb         NOT NULL,
    inserted_at timestamptz   NOT NULL DEFAULT now()
) PARTITION BY RANGE (inserted_at);

-- Monthly partitions for efficient DROP of old data
CREATE TABLE audit_events_2026_02
  PARTITION OF audit_events
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- ── Tier 2: ClickHouse (90 days–1 year) — columnar, analytics-optimized ──
-- Best for: aggregate queries ("how many order:update events per actor per week?")
-- Ingest via Kafka topic or pg_logical replication from PostgreSQL Tier 1
-- ClickHouse JSON: use JSONExtractString(), JSONExtractFloat() functions

CREATE TABLE audit_events_clickhouse (
    event_id    String,
    actor_id    String,
    action      LowCardinality(String),
    resource    LowCardinality(String),
    resource_id String,
    timestamp   DateTime64(3, 'UTC'),
    patch       String,    -- JSON Patch stored as raw string
    inserted_at DateTime DEFAULT now()
) ENGINE = MergeTree()
ORDER BY (resource, resource_id, timestamp)
PARTITION BY toYYYYMM(timestamp);

-- Example analytic query: top 10 actors by event count this month
SELECT actor_id, count() AS event_count
FROM audit_events_clickhouse
WHERE timestamp >= toStartOfMonth(now())
GROUP BY actor_id
ORDER BY event_count DESC
LIMIT 10;

-- ── Tier 3: S3 Glacier (1–7 years) — cold archive, WORM ───────────────
-- Daily export from Tier 1/2 to Parquet files on S3
-- Object Lock Compliance mode: immutable for retention period
-- Cost: ~$0.004/GB/month (Glacier Instant Retrieval)
-- Retrieval: minutes to hours — not for real-time queries

// Daily export job
import { S3Client, PutObjectCommand } from '@aws-sdk/client-s3';
const events = await db.query(
  "SELECT data FROM audit_events WHERE inserted_at::date = current_date - 1"
);
const ndjson = events.rows.map(r => JSON.stringify(r.data)).join('\n');
await s3.send(new PutObjectCommand({
  Bucket: 'audit-glacier',
  Key: `audit/${yesterday}.ndjson.gz`,
  Body: gzip(ndjson),
  ObjectLockMode: 'COMPLIANCE',
  ObjectLockRetainUntilDate: retentionDate,
}));

-- ── OpenTelemetry + SIEM integration ─────────────────────────────────
// Emit audit events as OpenTelemetry log records for SIEM ingestion
import { logs } from '@opentelemetry/api-logs';
const logger = logs.getLogger('audit');

logger.emit({
  severityText: 'INFO',
  body: JSON.stringify(auditEvent),
  attributes: {
    'audit.action':     auditEvent.action,
    'audit.actorId':    auditEvent.actorId,
    'audit.resourceId': auditEvent.resourceId,
  },
});
// SIEM (Splunk, Datadog, Elastic Security) ingests via OTLP receiver

The partition strategy on the PostgreSQL tier is critical for retention management: dropping a monthly partition is an O(1) metadata operation, while a DELETE WHERE inserted_at < date on a single large table is O(n) and generates significant write amplification on WORM-adjacent storage. Size partitions by your expected event volume — if you ingest 10M events/day, use daily partitions.

Querying and Reconstructing History from JSON

The value of a JSON audit trail is realized at query time — when a compliance auditor asks "show me everything user X did between January and February" or an incident responder asks "what was the state of order Y at 14:30 on the 6th?" These queries require efficient indexes and a reconstruction algorithm that replays diffs forward from a known base state.

-- ── Timeline for a specific resource ────────────────────────────────
SELECT
    data->>'eventId'    AS event_id,
    data->>'actorId'    AS actor_id,
    data->>'action'     AS action,
    data->>'timestamp'  AS timestamp,
    data->'diff'->'patch' AS changes
FROM audit_events
WHERE data->>'resource'   = 'Order'
  AND data->>'resourceId' = 'ord_78432'
ORDER BY (data->>'timestamp')::timestamptz ASC;

-- ── Actor activity: all events by one actor in a time range ───────────
SELECT
    data->>'action'    AS action,
    data->>'resource'  AS resource,
    data->>'resourceId' AS resource_id,
    data->>'timestamp' AS timestamp
FROM audit_events
WHERE data->>'actorId' = 'actor_a3f9b2c1'
  AND (data->>'timestamp')::timestamptz
        BETWEEN '2026-02-01T00:00:00Z' AND '2026-03-01T00:00:00Z'
ORDER BY (data->>'timestamp')::timestamptz ASC;

-- ── Action-type filter: all delete events ─────────────────────────────
SELECT data->>'resourceId' AS resource_id, data->>'timestamp' AS ts
FROM audit_events
WHERE data->>'action' LIKE '%:delete'
ORDER BY (data->>'timestamp')::timestamptz DESC
LIMIT 100;

-- ── Point-in-time reconstruction via JSON Patch replay ────────────────
-- Step 1: fetch events for the resource in ascending time order
WITH resource_events AS (
  SELECT
      data,
      row_number() OVER (ORDER BY (data->>'timestamp')::timestamptz) AS rn
  FROM audit_events
  WHERE data->>'resource'   = 'Order'
    AND data->>'resourceId' = 'ord_78432'
    AND (data->>'timestamp')::timestamptz <= '2026-02-06T14:32:00Z'
)
-- Step 2: the first event's diff.before is the base state
-- Step 3: apply each patch forward in application code
SELECT data FROM resource_events ORDER BY rn;

-- ── Aggregate: event counts by action per day ─────────────────────────
SELECT
    date_trunc('day', (data->>'timestamp')::timestamptz) AS day,
    data->>'action'  AS action,
    count(*)         AS event_count
FROM audit_events
WHERE (data->>'timestamp')::timestamptz >= now() - INTERVAL '30 days'
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;

-- ── Session replay: all events from one session ───────────────────────
SELECT
    data->>'timestamp' AS ts,
    data->>'actorId'   AS actor,
    data->>'action'    AS action,
    data->>'resourceId' AS resource_id
FROM audit_events
WHERE data->'context'->>'sessionId' = 'sess_9d3f2a'
ORDER BY (data->>'timestamp')::timestamptz ASC;

-- Index to make sessionId queries fast (expression index on nested path)
CREATE INDEX idx_audit_session
ON audit_events ((data->'context'->>'sessionId'));

Point-in-time reconstruction is the killer feature of a JSON Patch–based audit trail. By replaying patches forward from a base snapshot, you can answer "what did this record look like at any arbitrary timestamp?" without storing a full snapshot for every event. The application-layer replay loop (using fast-json-patch's applyPatch) takes the ordered list of events from the SQL query and folds the patches together in O(n) where n is the number of events for that resource.

Compliance Requirements for Audit Logs

Compliance frameworks mandate specific retention periods, tamper-evidence mechanisms, and access controls for audit logs. Understanding the exact requirements prevents both under-retention (audit failure) and over-retention (GDPR violation). The table below summarizes the key requirements — implement each as an explicit configuration in your retention policy, not as implicit operational practice.

-- Compliance retention matrix:
-- Framework | Minimum Retention | Online Access Required | Tamper-Evidence
-- SOC 2     | 12 months         | 3 months               | Recommended
-- PCI-DSS   | 12 months         | 3 months               | Required
-- HIPAA     | 72 months (6 yr)  | As needed              | Required
-- ISO 27001 | Policy-defined    | Policy-defined         | Recommended
-- GDPR      | Purpose-limited   | N/A                    | N/A (retention limit)

-- ── SOC 2 Type II requirements ────────────────────────────────────────
-- CC7.2: Monitor system components for anomalies
-- CC7.3: Evaluate security events to determine impact
-- Requirements:
--   - Audit logs for: authentication, authorization changes, data access
--   - Minimum 12-month retention
--   - 3 months immediately accessible to auditors
--   - Log integrity controls (hash chains satisfy this)
--   - Centralized log management with access controls

-- ── PCI-DSS v4.0 Requirements 10.3 / 10.5 ────────────────────────────
-- 10.3.2: Protect audit logs from destruction and unauthorized modifications
-- 10.3.3: Audit log files backed up to central log server or WORM media
-- 10.5.1: Retain audit log history for at least 12 months
--         with at least 3 months available for immediate analysis
-- Scope: any system that stores, processes, or transmits cardholder data

-- ── HIPAA Audit Control (45 CFR § 164.312(b)) ────────────────────────
-- Implement hardware, software, and/or procedural mechanisms that
-- record and examine activity in information systems that contain
-- or use electronic protected health information (ePHI)
-- Retention: 6 years from date of creation or last effective date

-- ── Implementation: tiered retention policy ──────────────────────────
-- Define retention per resource type in a configuration table
CREATE TABLE audit_retention_policy (
    resource        text PRIMARY KEY,
    online_days     int  NOT NULL,   -- days in PostgreSQL (immediately queryable)
    archive_days    int  NOT NULL,   -- days in S3 Glacier (retrievable)
    framework       text NOT NULL
);

INSERT INTO audit_retention_policy VALUES
    ('PaymentCard',   90,  365,   'PCI-DSS'),    -- 3mo online, 12mo archived
    ('HealthRecord',  90,  2190,  'HIPAA'),       -- 3mo online, 6yr archived
    ('UserAccount',   90,  365,   'SOC2'),        -- 3mo online, 12mo archived
    ('Order',         90,  365,   'SOC2');

-- ── Tamper-evidence controls checklist ───────────────────────────────
-- [x] Append-only table with UPDATE/DELETE trigger (blocks application layer)
-- [x] Row-level security: audit role has INSERT only
-- [x] Merkle hash chain: eventHash includes prevHash
-- [x] Daily chain verification job with alerting on break
-- [x] WORM archive (S3 Object Lock Compliance) for long-term storage
-- [x] Immutable backup with separate credentials from production
-- [x] Access logging: who queried the audit log (meta-audit)

-- ── Meta-audit: audit the auditors ──────────────────────────────────
-- Log who reads audit events — prevents insider queries from going undetected
-- Use PostgreSQL audit extension (pgaudit) for this layer:
-- ALTER SYSTEM SET pgaudit.log = 'read';  -- log all SELECT on audit_events
-- RELOAD: SELECT pg_reload_conf();

The meta-audit layer — logging who queries the audit trail — is often overlooked but required by SOC 2 CC6.3 (logical access controls) and is a strong signal for insider threat detection. Use the pgaudit PostgreSQL extension to log all reads of the audit_events table to a separate, append-only audit-of-audit table. This creates a two-level audit chain: the primary trail records business events, and the meta-audit records who accessed the primary trail.

Key Terms

Audit Trail

A chronologically ordered, tamper-evident record of events that occurred in a system — who did what, to which resource, and when. A JSON audit trail stores each event as a structured JSON document with a canonical schema, enabling machine-readable querying, programmatic diff replay, and cryptographic integrity verification. Audit trails serve forensic (incident investigation), compliance (SOC 2, HIPAA, PCI-DSS), and operational (debugging, rollback) purposes. The critical property that distinguishes an audit trail from a general log is immutability: once written, records must not be modifiable or deletable without detection. This property is enforced at multiple layers — database triggers, row-level security, WORM storage, and cryptographic hash chains.

Audit Event

The atomic unit of an audit trail — a single JSON document representing one discrete action. A well-formed audit event contains: a stable unique identifier (eventId), a pseudonymized actor reference (actorId), a namespaced action verb (action in resource:operation format), the affected entity type and identifier (resource, resourceId), an ISO 8601 UTC timestamp, request context metadata, a before/after diff, and Merkle chain hashes (prevHash, eventHash). Events are immutable by design — a correction to a previous event is represented as a new event of type resource:correct, never by modifying the original event. This append-only model preserves the complete history including mistakes and their corrections.

Immutability

The property that a record, once written, cannot be altered or deleted. For audit trails, immutability is not a single technical control but a defense-in-depth stack. At the database layer: an append-only table enforced by a trigger that raises an exception on any UPDATE or DELETE, combined with row-level security that grants INSERT-only permissions to the audit service role. At the storage layer: WORM (Write Once Read Many) storage such as AWS S3 Object Lock in Compliance mode, which prevents deletion even by the bucket owner or AWS Support during the retention period. At the cryptographic layer: a Merkle hash chain where each event's hash includes the hash of the previous event, making retroactive modification detectable through chain verification. True immutability requires all three layers — any single control can be circumvented by a sufficiently privileged attacker.

Pseudonymization

A data protection technique that replaces directly identifying information with an artificial identifier (pseudonym) that cannot be reversed without access to a separately stored mapping. For GDPR-compliant audit trails, pseudonymization means storing an opaque actorId (e.g., actor_a3f9b2c1) in the audit event instead of the user's name or email. The mapping from actorId to real identity is held in a separate, mutable PII lookup table. When a user exercises the GDPR right to erasure (Article 17), the PII lookup table row is erased — the audit events remain intact and immutable, but the actorId can no longer be resolved to a real person. Pseudonymization is distinct from anonymization: pseudonymized data is still considered personal data under GDPR (because re-identification is possible with the key), but the risk is significantly reduced.

WORM Storage (Write Once Read Many)

A storage medium or configuration where data can be written once and subsequently read any number of times, but never modified or deleted during a specified retention period — not even by administrators. For cloud-based audit trail archival, WORM is implemented via AWS S3 Object Lock in Compliance mode or Azure Immutable Blob Storage with a time-based retention policy. In Compliance mode, even the AWS root account cannot delete locked objects before the retention period expires. This satisfies the tamper-evidence requirements of PCI-DSS 10.3.3 (audit logs backed up to central server or WORM media) and provides externally verifiable proof that audit records were not altered after archival. WORM storage is a complement to, not a replacement for, database-layer immutability controls — records must be written correctly before archival.

Merkle Hash Chain

A cryptographic data structure where each record contains a hash of its own content and a hash of the previous record, forming a chain where any historical modification is detectable. For audit trails: event N stores prevHash (the eventHash of event N-1) and eventHash (SHA-256 of the event's canonical fields including prevHash). If an attacker modifies event 50, its eventHash changes — which means event 51's stored prevHash no longer matches the recomputed hash of event 50. A verification job that traverses the chain in order will detect this break at event 51. The chain can be verified by any party who has access to the events — no trusted third party is required. Publishing the latest eventHash to an external append-only ledger (e.g., a Certificate Transparency log or blockchain) provides a cryptographic timestamp that proves the chain existed in its current state at a specific moment.

Before/After Diff

The record of what changed during a mutation event, stored in the audit event's diff field. Two formats are commonly used. Full snapshots store the complete document state before and after the change — easy to read but storage-intensive for large documents. RFC 6902 JSON Patch stores the change as an array of operations: [{"op":"replace","path":"/status","value":"paid"}] — compact, human-readable, and machine-applicable (the patch can be replayed to reconstruct any historical state). The recommended hybrid approach stores a JSON Patch array for all events and adds full before/after snapshots only for critical resources. Sensitive fields (passwords, tokens, secrets) must be masked or omitted before capturing the diff — never let credentials appear in audit records.

Data Retention Policy

A formal specification of how long different categories of data must be retained and when they must be deleted. For audit trails, a data retention policy must balance two opposing legal obligations: compliance frameworks mandate minimum retention (SOC 2: 1 year, PCI-DSS: 1 year, HIPAA: 6 years), while GDPR Article 5(1)(e) prohibits retaining personal data longer than necessary for its stated purpose. The resolution is to define retention periods per resource type based on the most stringent applicable framework, automate expiration via database partition drops or storage lifecycle rules, and document the policy in a Record of Processing Activities (RoPA). Retention policies must be enforced automatically — manual deletion processes will inevitably be inconsistent and will fail audits.

FAQ