SC ReconciliationReconciliation Automation
  1. Home
  2. Architecture
  3. Audit Trail and Compliance for Reconciliation

Audit Trail and Compliance for Reconciliation

↑ Part of Core Architecture & Data Mapping for Reconciliation.

A reconciliation pipeline that produces the right answer is not, by itself, defensible. When an external auditor or an internal controls reviewer asks why a specific invoice line moved from exception to matched on a specific date, “the code did it correctly” is not an answer — they need the exact prior state, the exact new state, who or what triggered the transition, under which reason code, and at what UTC instant. If that provenance lives only in a mutable status column that gets overwritten on every update, the question is unanswerable the moment the row changes a second time. Every trace of the decision that mattered is gone.

This page treats auditability as a first-class architectural property, not a logging afterthought bolted on after the fact. The design choice that makes reconciliation decisions provable is the same one that underlies the matching and reconciliation algorithms that produce those decisions in the first place: never destroy history. A state transition is recorded as an immutable, append-only event; the “current state” is a materialized view derived by replaying those events, never an independently mutated field. That single discipline is what turns a reconciliation system from something an auditor has to trust into something an auditor can verify.

Core Concept & Decision Criteria

The failure mode this page exists to prevent is the in-place status update: a reconciliation row carries a status column, a matching job flips it from pending to matched, an override job later flips it to manually_cleared, and each write physically overwrites the prior value. The database can tell you what the row is; it cannot tell you what the row was, who changed it, or why — unless a separate audit mechanism captured that at write time, and even then a mutable log can itself be edited or truncated. For SOX Section 404 internal-controls purposes, this is disqualifying: the control objective is not “the ledger is currently correct,” it is “every change to the ledger is attributable and irreversible after the fact.”

The alternative is an append-only event log where every state transition is its own row, keyed by the same canonical mapping identifier used everywhere else in the pipeline, and the table itself is architected so that UPDATE and DELETE are not just discouraged by convention but structurally impossible for the roles that write to it. “Current state” becomes a query — the latest event per canonical key — rather than a stored fact, which means the full transition history is always present and never has to be reconstructed from backups.

Decision axis Mutable in-place status Append-only audit event log
Write semantics UPDATE overwrites prior value INSERT only; prior rows untouched
Historical visibility Only current value survives Full transition history queryable
Attribution Requires a separate, often optional, audit table Every row carries actor, role, reason code by construction
Tamper evidence None — an UPDATE leaves no trace of what it replaced Hash-chained sequence makes silent edits detectable
Replay / reconstruction Not possible without external backups Current state is always a deterministic replay of events
SOX control mapping Weak — control relies on trusting the application layer Strong — control is enforced at the storage/grant layer
Segregation of duties Bolted on in application logic, easily bypassed Enforceable in the schema (actor_id <> approver_id)
Storage growth Flat Grows with transition volume; requires retention policy
Query cost for “current state” O(1) — direct read O(n) worst case, mitigated by a materialized latest-event view

The controls a SOX audit actually tests map directly onto specific fields in the event schema, which is the reason the schema cannot be an afterthought: canonical_key satisfies traceability to the underlying transaction, prior_state/new_state satisfy completeness of the transition, reason_code satisfies the requirement that a judgment call be classified rather than free-text, actor_id/actor_role satisfy attribution, event_ts in UTC satisfies chronological ordering across time zones, and approver_id (populated only on override paths) satisfies segregation of duties. The detailed column-level schema, retention, and indexing strategy is covered in Designing a SOX-Compliant Audit Log Schema; the storage-engine mechanics that make the table physically append-only are covered in Append-Only Ledger Design in PostgreSQL. This page focuses on the writer contract and the operational discipline that sits between them.

Implementation

The audit writer is the single choke point every state transition must pass through — the matching engine, the exception-resolution workflow, and any manual override all call the same function rather than writing to the state table directly. Each event is hash-chained to the previous event for its canonical key, so a prior_event_hash mismatch on verification proves the chain was altered out of band:

Hn=SHA256⁡( Hn−1 ∥ canonical_keyn ∥ prior_staten ∥ new_staten∥ reason_coden ∥ actor_idn ∥ event_tsn)\begin{aligned} H_n = \operatorname{SHA256}\bigl(&\, H_{n-1} \,\Vert\, \text{canonical\_key}_n \,\Vert\, \text{prior\_state}_n \,\Vert\, \text{new\_state}_n \\ &\Vert\, \text{reason\_code}_n \,\Vert\, \text{actor\_id}_n \,\Vert\, \text{event\_ts}_n\bigr) \end{aligned}
PYTHON
import hashlib
import logging
from dataclasses import dataclass
from datetime import datetime, timezone
from enum import Enum
from typing import Optional

import psycopg
from pydantic import BaseModel, field_validator

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


class ReasonCode(str, Enum):
    """Closed taxonomy — free-text reasons are not auditable."""
    AUTO_MATCH_EXACT = "auto_match_exact"
    AUTO_MATCH_TOLERANCE = "auto_match_tolerance"
    ROUTED_TO_EXCEPTION = "routed_to_exception"
    MANUAL_OVERRIDE = "manual_override"
    DUAL_CONTROL_APPROVAL = "dual_control_approval"
    REVERSAL = "reversal"


class AuditEvent(BaseModel):
    """One immutable state transition for a single canonical key."""
    canonical_key: str
    prior_state: Optional[str]
    new_state: str
    reason_code: ReasonCode
    actor_id: str
    actor_role: str
    approver_id: Optional[str] = None
    event_ts: datetime

    @field_validator("event_ts")
    @classmethod
    def must_be_utc(cls, v: datetime) -> datetime:
        if v.tzinfo is None or v.utcoffset() != timezone.utc.utcoffset(v):
            raise ValueError(f"event_ts must be timezone-aware UTC, got {v!r}")
        return v


@dataclass
class SegregationViolation(Exception):
    """Raised when a single identity both proposes and approves an override."""
    canonical_key: str
    actor_id: str


def enforce_segregation_of_duties(event: AuditEvent) -> None:
    """Overrides and dual-control approvals must carry a distinct approver."""
    requires_approver = event.reason_code in (
        ReasonCode.MANUAL_OVERRIDE,
        ReasonCode.DUAL_CONTROL_APPROVAL,
    )
    if requires_approver and event.approver_id == event.actor_id:
        logger.error(
            "segregation_violation key=%s actor=%s reason=%s",
            event.canonical_key, event.actor_id, event.reason_code.value,
        )
        raise SegregationViolation(event.canonical_key, event.actor_id)


def compute_event_hash(prior_hash: Optional[str], event: AuditEvent) -> str:
    """Chain each event to the prior event for its canonical key."""
    basis = "|".join([
        prior_hash or "GENESIS",
        event.canonical_key,
        event.prior_state or "",
        event.new_state,
        event.reason_code.value,
        event.actor_id,
        event.event_ts.isoformat(),
    ])
    return hashlib.sha256(basis.encode("utf-8")).hexdigest()


def write_audit_event(
    conn: psycopg.Connection, event: AuditEvent, prior_hash: Optional[str]
) -> str:
    """Append one event; never UPDATE or DELETE a prior row."""
    enforce_segregation_of_duties(event)
    event_hash = compute_event_hash(prior_hash, event)
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO reconciliation_audit_event
                (canonical_key, prior_state, new_state, reason_code,
                 actor_id, actor_role, approver_id, event_ts,
                 prior_event_hash, event_hash)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """,
            (
                event.canonical_key, event.prior_state, event.new_state,
                event.reason_code.value, event.actor_id, event.actor_role,
                event.approver_id, event.event_ts, prior_hash, event_hash,
            ),
        )
    conn.commit()
    logger.info(
        "audit_appended key=%s %s->%s reason=%s actor=%s hash=%s",
        event.canonical_key, event.prior_state, event.new_state,
        event.reason_code.value, event.actor_id, event_hash[:12],
    )
    return event_hash

The corresponding storage layer must physically prevent the mutation this writer never performs. The table grants INSERT and SELECT only to the application role; a trigger blocks UPDATE and DELETE even for a role that later gains those grants by mistake, so a schema privilege error is never the only line of defense:

SQL
CREATE TABLE reconciliation_audit_event (
    event_id          BIGSERIAL PRIMARY KEY,
    canonical_key     TEXT        NOT NULL,
    prior_state       TEXT,
    new_state         TEXT        NOT NULL,
    reason_code       TEXT        NOT NULL,
    actor_id          TEXT        NOT NULL,
    actor_role        TEXT        NOT NULL,
    approver_id       TEXT,
    event_ts          TIMESTAMPTZ NOT NULL,
    prior_event_hash  CHAR(64),
    event_hash        CHAR(64)    NOT NULL,
    recorded_at       TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
    CONSTRAINT actor_not_approver
        CHECK (approver_id IS NULL OR approver_id <> actor_id)
);

CREATE INDEX idx_audit_canonical_key_ts
    ON reconciliation_audit_event (canonical_key, event_ts);

-- Structural append-only enforcement: no role can UPDATE or DELETE,
-- regardless of what grants it later accumulates.
CREATE OR REPLACE FUNCTION reject_audit_mutation() RETURNS trigger AS $$
BEGIN
    RAISE EXCEPTION 'reconciliation_audit_event is append-only: % denied on event_id %',
        TG_OP, OLD.event_id;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_no_update
    BEFORE UPDATE ON reconciliation_audit_event
    FOR EACH ROW EXECUTE FUNCTION reject_audit_mutation();

CREATE TRIGGER trg_audit_no_delete
    BEFORE DELETE ON reconciliation_audit_event
    FOR EACH ROW EXECUTE FUNCTION reject_audit_mutation();

REVOKE UPDATE, DELETE ON reconciliation_audit_event FROM PUBLIC;
GRANT INSERT, SELECT ON reconciliation_audit_event TO recon_writer;

Both layers matter independently. The Python-side enforce_segregation_of_duties check stops an override from being proposed by the same identity that approves it; the SQL CHECK constraint stops it from ever being stored that way even if an application bug or a direct database session bypasses the writer. Which identity is even permitted to hold the actor_role values that can write MANUAL_OVERRIDE or DUAL_CONTROL_APPROVAL events is a role-based access question, governed by the controls in Data Security Boundaries for Procurement Systems and specifically by Implementing Role-Based Access for Supply Chain Data Pipelines, which defines the role hierarchy the actor_role field is populated from.

Audit event lifecycle for a reconciliation state transition A state transition is proposed by the matching engine, exception workflow, or a manual actor. It is classified against the closed reason-code taxonomy, then branches on whether it is a manual override or dual-control approval. Non-override transitions proceed directly to hashing. Override transitions first pass a segregation-of-duties check comparing actor_id to approver_id; a failed check raises a SegregationViolation and the transition is rejected before any row is written. Transitions that pass are hashed by chaining the event to the prior event hash for the same canonical key, appended as an INSERT-only row protected by database triggers that reject UPDATE and DELETE, and finally reflected in a materialized current-state view that is always a replay of the append-only log rather than an independently stored value. Transition proposed matcher · workflow · actor Classify reason_code closed taxonomy only Manual override or dual-control approval? no Segregation-of-duties check actor_id ≠ approver_id yes SegregationViolation rejected · no row written fail pass Compute event_hash H_n = SHA256(H_n-1 ∥ event) INSERT-only append UPDATE / DELETE rejected by trigger Materialized current-state view latest event per canonical_key · always a replay

Configuration & Threshold Calibration

The audit event schema is fixed by control requirements, but several operational parameters around it need calibration per environment and per regulatory posture:

Parameter Recommended value Rationale
reason_code taxonomy size 6–15 closed values Enough granularity for auditors to distinguish causes; too many collapses back into free text
hash_algorithm SHA-256 Collision resistance auditors and external attestors already accept
dual_control_threshold Override affecting > tolerance-band variance or above a dollar floor Below the floor, single-actor overrides are proportionate to risk
retention_period 7 years (US SOX) or per local statute Audit events must outlive the fiscal periods they attest to
clock_skew_tolerance 0 — reject, don’t clamp A clamped timestamp silently misrepresents when a decision was made
chain_verification_interval Every batch close, plus a nightly full-table pass Detects a broken hash chain before it reaches an external audit
replay_divergence_alert Any mismatch between materialized state and replayed state Signals the materialized view fell out of sync with the log

event_ts is the parameter most teams get wrong. The writer above rejects any non-UTC timestamp outright rather than converting it, because a converted timestamp hides the ambiguity of what “local” meant at capture time — a plant in one time zone and a shared-services center in another must produce audit events that sort identically regardless of where the actor sat. This mirrors the same UTC-normalization discipline used for Timezone Normalization for Global Supply Chains, applied here to the audit dimension instead of the transactional one.

dual_control_threshold should never be a single global dollar figure copied from a different control framework. Calibrate it against the same tolerance bands that drive matching decisions in Setting Quantity and Price Tolerance Windows — an override that merely re-asserts what tolerance matching would have accepted anyway carries less risk than one that forces a match outside the band, and the approval requirement should scale accordingly.

Orchestration & Integration

The audit writer sits downstream of every stage that can change a canonical key’s state — ingestion normalization, tiered matching, exception resolution, and manual override — and upstream of nothing except the materialized current-state view. It is not a side effect bolted onto those stages; it is the only mechanism by which state legitimately changes. A matching job does not “set status to matched” and separately “log an audit event” as two operations that can drift out of sync; it calls write_audit_event, and the materialized view’s next refresh reflects the new latest event. This ordering guarantee is what prevents the class of bug where a status column and its audit trail disagree because one write succeeded and the other did not.

Every stage that emits an event carries the same canonical_key used across the core architecture and data mapping layer, so a single key joins cleanly from raw ingestion through matching through audit without a translation step. Rows routed to the exception queue by the matching tier — covered in depth by the exception handling and discrepancy resolution section — write a routed_to_exception event on entry and, on resolution, a second event carrying whichever terminal reason code applies; the exception queue’s own state is therefore also fully replayable from this same log rather than tracked separately.

Health of the audit pipeline itself is an observability concern, not a compliance-only one. Chain-verification failures, a growing gap between materialized-view refresh time and the latest appended event, or an unexpected spike in manual_override events relative to auto-matched volume are exactly the kind of leading indicators the monitoring and alerting tier should surface before they become an audit finding. Wire the verification job’s pass/fail status and the override-rate ratio into that same alerting surface rather than building a parallel one.

Debugging & Pipeline Recovery

Because the audit log is append-only, recovery never means “fix the row” — it means either replay from the log or escalate, and knowing which one applies depends on where the divergence sits.

  • Materialized state disagrees with the event log: rebuild the current-state view by replaying all events for the affected canonical_key in event_ts order and recomputing the terminal state. If the replay produces a different value than what the materialized view currently shows, the view refresh — not the log — is the defective component; the log itself was never touched.
  • Hash-chain verification fails: walk the chain for the affected canonical key and locate the first event_hash that does not match SHA256(prior_hash || event). Everything before that point is provably intact; everything after it is suspect. This is a containment and escalation event, not a routine bug — do not attempt to “repair” the chain by recomputing hashes forward, since that would erase the evidence that something was altered.
  • Segregation-of-duties violation raised in production: confirm the role mapping in the identity provider matches what actor_role expects. A SegregationViolation firing on a legitimate approval usually means a role was granted to the wrong identity upstream, in the same role-based access layer that defines who may hold override authority — fix the role assignment, then resubmit the transition as a new event; never bypass the check.
  • Clock skew rejections at ingestion: an actor’s system clock drifting from UTC will surface as a wall of must_be_utc validation errors. Correct the source clock and replay the rejected transitions as new events with corrected timestamps; do not backdate a corrected event to match when the transition was originally attempted, since the event_ts must reflect when the row was actually appended.
  • Retention conflicts with a legal hold: a table configured to purge events past retention_period must exempt any canonical key under active hold. Treat this as a query-time filter on the purge job, not a schema change, so the append-only guarantee is never weakened to accommodate an exception.

Every recovery path above ends in the same place: a new event is appended, or an anomaly is escalated. Nothing in a correctly designed audit pipeline is ever edited in place, which is precisely the property being verified.

FAQ

Why not just add a created_at and updated_by column to the existing status table instead of a separate event log?

Because those two columns only ever describe the most recent change. The moment a second update happens, the identity and reason for the first change are gone — overwritten, not archived. An auditor testing a specific historical transition has no way to reconstruct it. An append-only event log keeps every transition as its own row, so the full sequence is always queryable, not just the latest state.

Does hash-chaining the event log require a blockchain or distributed ledger?

No. Hash chaining here is a single-writer, single-database integrity check — each event’s hash incorporates the previous event’s hash for the same canonical key, so any out-of-band edit breaks the chain at a detectable point. That property does not require consensus, distribution, or a token; a BEFORE UPDATE/BEFORE DELETE trigger plus a periodic verification job delivers the same tamper-evidence for a fraction of the operational complexity.

What counts as a valid reason code, and who decides?

A valid reason code is any value in the closed taxonomy owned by the controls team, not whatever string an engineer types during an incident. Codes like auto_match_exact or manual_override map directly to control tests an auditor runs; free-text reasons do not. Changing the taxonomy itself should go through the same change-control process as a schema migration, since auditors will re-test against whatever taxonomy was in effect for the period under review.

Can a single person both propose and approve an override in an emergency?

No — that is exactly the scenario segregation of duties exists to prevent, and the actor_not_approver check enforces it at both the application layer and the database constraint layer. In a genuine emergency where no second approver is reachable, the correct path is a documented emergency-access procedure that still produces two distinct identities in the log (even if one is a break-glass account with its own review trail), not a bypass of the check.

How do you prove to an external auditor that the audit log itself has not been tampered with?

Run the chain-verification job against the full table and produce its pass result as evidence, then let the auditor independently recompute a sample of hashes from raw event data rather than trusting your job’s output alone. Because event_hash is a deterministic function of the row’s own fields plus the prior hash, anyone with read access to the table can recompute and confirm it without needing write access or trusting the application that wrote it.