SC ReconciliationReconciliation Automation
  1. Home
  2. Architecture
  3. Audit Trail and Compliance for Reconciliation
  4. Designing a SOX-Compliant Audit Log Schema

Designing a SOX-Compliant Audit Log Schema

↑ Part of Audit Trail and Compliance for Reconciliation.

An auditor reviewing a reconciliation control does not ask “did the pipeline run”; they ask “show me the exact state of this invoice line before and after every automated decision, who or what made the change, under which configuration, and prove nothing since has been altered.” A generic application log table — free-text message, timestamp, level — cannot answer that. This page defines the concrete field-level schema an audit-log table needs to satisfy that review: a stable event identity, a canonical key back to the business record, a strict prior_state → new_state delta, a closed reason_code, an unambiguous actor identity, the config_version that governed the decision, a UTC timestamp, and an optional hash-chain link for tamper evidence.

Operational Trigger Signals

Build (or rebuild) the audit-log schema when any of the following gaps show up during an internal-control walkthrough or a real incident review. Each signal points to a specific missing column, not a vague “log more” instruction:

  1. A reviewer cannot reconstruct the before-value. The application log records that a match status changed to TOLERANCE_ADJUSTED but not what it was before, so a control tester cannot verify the delta was within the approved band. This means prior_state is absent or optional when it must be captured for every mutating event.
  2. A decision cannot be replayed. An auditor asks why a line auto-matched six months ago and the answer depends on a threshold value that has since changed three times, with no record of which value was active at decision time. Without a stored config_version, the decision is un-replayable — it is a fact you assert, not one you can prove.
  3. Actor identity resolves to a shared service account. Every automated write is attributed to svc-recon-worker with no distinction between the deterministic matcher, a scheduled tolerance job, and a human override through an admin console. SOX review requires separating system-generated decisions from human intervention, which means actor_id alone is insufficient without an actor_type discriminator.
  4. Rows can be edited or deleted after the fact. If a DBA (or a bug) can UPDATE or DELETE an existing audit row, the log is a diary, not evidence. The absence of an append-only constraint and a hash-chain link means gaps and edits are structurally undetectable, not just unlikely.
  5. Timestamps drift between event time and write time, or are stored in local time. Ordering audit events across services by a non-UTC or ambiguous timestamp produces a replay sequence that does not match reality, which breaks any chain-of-custody argument during review.

When two or more of these signals recur across quarterly control testing, treat the schema itself as the defect — patching individual event-emission call sites without fixing the table definition just reproduces the gap in the next feature.

Step-by-Step Implementation

Design the table so that every column exists to answer a specific audit question, and so that the database itself — not application discipline — enforces append-only behavior.

  1. Define the canonical event identity. Every row gets a monotonic event_id and a canonical_key pointing back to the reconciled business entity (PO line, invoice line, ledger entry) so events can be grouped and replayed per entity.
  2. Capture the full state delta. Store prior_state and new_state as structured JSON, not a free-text diff. new_state is always populated; prior_state is NULL only for the entity’s first INSERT-equivalent event.
  3. Attach a closed reason code and actor identity. reason_code comes from an enumerated, versioned taxonomy (never free text), and actor_id is paired with actor_type (SYSTEM, SCHEDULED_JOB, HUMAN_OVERRIDE) so review can filter automated decisions from manual ones.
  4. Pin the config version. Every write records the identifier of the threshold/config set active at decision time, so a reviewer can pull that exact configuration and replay the decision deterministically.
  5. Enforce insert-only at the database layer. Revoke UPDATE and DELETE from the application role and, where the row must be provably unaltered, chain each row’s hash to the previous row’s hash — the deeper mechanics of enforcing this at the database level are covered in Append-Only Ledger Design in PostgreSQL.
  6. Index for replay, not just for insert speed. Auditors query by entity over a time window far more often than by primary key, so the replay index must lead with canonical_key.

The DDL below defines the table with those constraints made explicit — NOT NULL on every field required for review, a CHECK that only the first event per entity may omit prior_state, and a foreign-key-free prev_row_hash that a writer resolves at insert time:

SQL
CREATE TABLE reconciliation_audit_log (
    event_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    canonical_key    TEXT        NOT NULL,          -- business entity, e.g. 'PO-88421-L003'
    event_type       TEXT        NOT NULL,           -- e.g. 'MATCH_COMMIT', 'TOLERANCE_ADJUST'
    prior_state      JSONB,                          -- NULL only for the entity's first event
    new_state        JSONB       NOT NULL,
    reason_code      TEXT        NOT NULL,            -- closed taxonomy, FK to reason_code_dim
    actor_id         TEXT        NOT NULL,            -- service principal or operator id
    actor_type       TEXT        NOT NULL
        CHECK (actor_type IN ('SYSTEM', 'SCHEDULED_JOB', 'HUMAN_OVERRIDE')),
    config_version   TEXT        NOT NULL,            -- pinned threshold/config set id
    event_ts         TIMESTAMPTZ NOT NULL DEFAULT now(),  -- always UTC
    prev_row_hash    CHAR(64),                        -- NULL only for chain genesis row
    row_hash         CHAR(64)    NOT NULL,             -- sha256(prev_row_hash || canonical payload)
    CONSTRAINT chk_first_event_no_prior
        CHECK (prior_state IS NOT NULL OR event_type = 'ENTITY_CREATED')
);

-- Replay index: pull an entity's full history in event order.
CREATE INDEX idx_audit_replay
    ON reconciliation_audit_log (canonical_key, event_ts);

-- Review index: filter a review window by actor class without a full scan.
CREATE INDEX idx_audit_actor_window
    ON reconciliation_audit_log (actor_type, event_ts);

-- Insert-only enforcement: the application role can never mutate history.
REVOKE UPDATE, DELETE ON reconciliation_audit_log FROM app_write_role;
GRANT INSERT, SELECT ON reconciliation_audit_log TO app_write_role;
Hash-chained append-only audit write path A reconciliation decision produces an event with prior_state, new_state, reason_code, actor identity, and config_version. The writer looks up the previous row's hash, computes a new SHA-256 row hash over that previous hash plus the canonical payload, and inserts the row. The insert-only grant prevents update or delete, and the replay index makes the resulting chain queryable per canonical_key in event order. Reconciliation event Assemble payload delta · reason · actor · config_version Fetch prev_row_hash last row for canonical_key Compute row_hash sha256(prev || payload) Log + INSERT insert-only role grant Audit row chained + indexed Replay index canonical_key, event_ts

Tamper evidence comes from chaining, not from any single column. Each row’s hash covers the previous row’s hash plus the canonical payload of the current row:

hn=SHA256⁡(hn−1∥payloadn)h_n = \operatorname{SHA256}\bigl(h_{n-1} \parallel \text{payload}_n\bigr)

An altered or deleted historical row breaks every subsequent hash in the chain, which is what turns “we believe this log is complete” into a verifiable claim during review.

The writer below assembles the payload, resolves the prior hash, computes the chained hash, and inserts the row — a single, typed, logged code path so no call site can accidentally skip a required field:

PYTHON
import hashlib
import json
import logging
from dataclasses import dataclass, field
from datetime import datetime, timezone
from typing import Optional

import psycopg

logger = logging.getLogger("recon.audit")


@dataclass(frozen=True)
class AuditEvent:
    """One state transition to be committed to the audit log."""
    canonical_key: str
    event_type: str
    new_state: dict
    reason_code: str
    actor_id: str
    actor_type: str  # "SYSTEM" | "SCHEDULED_JOB" | "HUMAN_OVERRIDE"
    config_version: str
    prior_state: Optional[dict] = None
    event_ts: datetime = field(default_factory=lambda: datetime.now(timezone.utc))


def _canonical_payload(event: AuditEvent) -> str:
    """Deterministic JSON so the hash is stable across writer versions."""
    return json.dumps(
        {
            "canonical_key": event.canonical_key,
            "event_type": event.event_type,
            "prior_state": event.prior_state,
            "new_state": event.new_state,
            "reason_code": event.reason_code,
            "actor_id": event.actor_id,
            "actor_type": event.actor_type,
            "config_version": event.config_version,
            "event_ts": event.event_ts.isoformat(),
        },
        sort_keys=True,
        separators=(",", ":"),
    )


def _fetch_prev_hash(conn: psycopg.Connection, canonical_key: str) -> Optional[str]:
    """Look up the most recent row_hash for this entity, or None for a genesis row."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT row_hash FROM reconciliation_audit_log
            WHERE canonical_key = %s
            ORDER BY event_ts DESC, event_id DESC
            LIMIT 1
            """,
            (canonical_key,),
        )
        row = cur.fetchone()
        return row[0] if row else None


def append_audit_event(conn: psycopg.Connection, event: AuditEvent) -> str:
    """Compute the hash-chained row and insert it. Never UPDATE, never DELETE."""
    if event.prior_state is None and event.event_type != "ENTITY_CREATED":
        # Fail loudly rather than silently insert an un-replayable row.
        raise ValueError(
            f"prior_state required for event_type={event.event_type!r} "
            f"on canonical_key={event.canonical_key!r}"
        )

    prev_hash = _fetch_prev_hash(conn, event.canonical_key)
    payload = _canonical_payload(event)
    chain_input = f"{prev_hash or ''}|{payload}".encode("utf-8")
    row_hash = hashlib.sha256(chain_input).hexdigest()

    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO reconciliation_audit_log
                (canonical_key, event_type, prior_state, new_state, reason_code,
                 actor_id, actor_type, config_version, event_ts,
                 prev_row_hash, row_hash)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """,
            (
                event.canonical_key, event.event_type,
                json.dumps(event.prior_state) if event.prior_state else None,
                json.dumps(event.new_state), event.reason_code,
                event.actor_id, event.actor_type, event.config_version,
                event.event_ts, prev_hash, row_hash,
            ),
        )
    conn.commit()
    logger.info(
        "audit_append key=%s type=%s actor=%s(%s) config=%s hash=%s",
        event.canonical_key, event.event_type, event.actor_id,
        event.actor_type, event.config_version, row_hash[:12],
    )
    return row_hash

Configuration Reference

These are the columns the schema requires and why each one exists — treat any deviation as a review finding, not a style choice:

Field Type Purpose
event_id BIGINT IDENTITY Stable, monotonic row identity independent of any business key.
canonical_key TEXT NOT NULL Links the event back to the business entity for per-entity replay.
event_type TEXT NOT NULL Closed set (MATCH_COMMIT, TOLERANCE_ADJUST, MANUAL_OVERRIDE, …) driving downstream review filters.
prior_state JSONB (nullable) Full pre-change state; NULL only permitted on genesis events, enforced by a CHECK.
new_state JSONB NOT NULL Full post-change state; the delta reviewers diff against prior_state.
reason_code TEXT NOT NULL Closed taxonomy value, never free text — see the failure-reason discipline in exception handling.
actor_id TEXT NOT NULL Service principal or operator identifier that produced the write.
actor_type TEXT NOT NULL SYSTEM / SCHEDULED_JOB / HUMAN_OVERRIDE — separates automated from manual decisions.
config_version TEXT NOT NULL Pinned identifier of the threshold/config set active at decision time; enables replay.
event_ts TIMESTAMPTZ NOT NULL Always UTC; ordering key for the replay index.
prev_row_hash CHAR(64) (nullable) Previous row’s hash for this entity; NULL only on the chain’s genesis row.
row_hash CHAR(64) NOT NULL SHA256(prev_row_hash || canonical_payload); makes tampering detectable.

Access to actor_id resolution and to the raw prior_state/new_state payloads must follow the same entitlement model as the rest of the pipeline — see Data Security Boundaries for Procurement Systems and, for the concrete grant structure, Implementing Role-Based Access for Supply Chain Data Pipelines. An audit table that is fully readable by every application role fails review just as surely as one with missing columns.

Debugging & Recovery

Because the table is append-only, recovery means detecting and explaining gaps or breaks, not editing rows:

  • Hash-chain break detection — periodically recompute row_hash for a sampled or full window and compare against the stored value; a mismatch or a prev_row_hash that does not match the prior row’s row_hash means the chain was broken, and every event after the break must be treated as unverified until investigated.
  • Missing config_version on legacy rows — rows written before the schema enforced this column should be backfilled from deployment history where possible and flagged with a sentinel value (UNKNOWN_PRE_V2) where it cannot, never left NULL retroactively, since that would violate the NOT NULL contract new readers rely on.
  • Orphaned genesis rows — an entity whose first audit row is not event_type = 'ENTITY_CREATED' indicates either a backfill gap or an out-of-band write path that bypassed append_audit_event; treat this as a control gap to close, not just a data-quality note.
  • Replay divergence — if replaying a decision with the stored config_version and prior_state produces a different new_state than what is logged, the deterministic logic itself has changed since that config version, which usually means a threshold engine bug rather than a bad audit row.
  • Index bloat on high-volume entities — canonical keys with unusually deep event histories (thousands of adjustments on one PO line) slow replay queries; partition the table by event_ts month rather than pruning history, since pruning an audit log defeats its purpose.

FAQ

Should prior_state ever be nullable for anything other than the first event on an entity?

No. Every mutating event after an entity’s creation must carry a non-null prior_state, enforced by the chk_first_event_no_prior constraint. A nullable prior_state on a later event is exactly the audit gap this schema exists to prevent — it lets a reviewer see that something changed without ever seeing what it changed from.

Why store config_version as a separate column instead of folding it into reason_code?

reason_code explains why a decision was made in business terms (TOLERANCE_WITHIN_BAND, FUZZY_VALIDATED); config_version explains under what rules. Collapsing them loses the ability to answer “if I revert to last month’s threshold, does this decision still hold,” which is precisely the replay question an auditor asks. Keeping them separate also lets the same reason_code taxonomy stay stable while config_version rotates independently.

Is the hash chain mandatory for SOX compliance, or is append-only enough?

Append-only access control (revoked UPDATE/DELETE) is the baseline and satisfies most internal-control reviews on its own. The hash chain adds tamper evidence on top of tamper prevention — it lets you prove, rather than merely configure, that history is unaltered, which matters most when the audit log itself, or the database role system protecting it, is in scope for the control being tested. Treat it as strongly recommended for high-materiality reconciliation tables and required wherever the control narrative claims the log is provably immutable.