SC ReconciliationReconciliation Automation
  1. Home
  2. Architecture
  3. Audit Trail and Compliance for Reconciliation
  4. Append-Only Ledger Design in PostgreSQL

Append-Only Ledger Design in PostgreSQL

↑ Part of Audit Trail and Compliance for Reconciliation.

A table with no UPDATE statements in the application code is not the same thing as an immutable ledger. Anyone with write access, a migration script, or a well-intentioned backfill can still issue UPDATE recon_ledger SET ... unless the database itself refuses the operation. This page builds that refusal in three enforced layers — revoked grants, a trigger that raises on any mutation attempt, and a SHA-256 hash chain that makes even a superuser’s out-of-band edit mathematically detectable — plus the partitioning and retention scheme that keeps the ledger operable at volume. It assumes the ledger already has a defined row schema; for the columns, retention classes, and evidentiary requirements that schema must satisfy, see Designing a SOX-Compliant Audit Log Schema.

Operational Trigger Signals

Build the enforced version of this pattern — not just an application-level “we don’t update this table” convention — when any of the following hold:

  1. An auditor asks “prove no row was altered” and the honest answer today is “we don’t write code that does that.” A SOX walkthrough or external audit treats an unenforced convention as a control gap, not a control.
  2. A migration or backfill script has ever run UPDATE against a ledger or history table. Even a one-time, well-reviewed correction sets a precedent that the table is mutable, and the next person to touch it will not know which edits were authorized.
  3. The Matching & Reconciliation Algorithms engine or the Variance Write-Back and ERP Adjustment Posting flow posts financial adjustments that must be individually traceable — reversing a posted adjustment must be a new row, never an edit to the original.
  4. Storage or query latency on the ledger degrades past an acceptable threshold because years of rows sit in one unpartitioned table with no retention policy, forcing every audit query to scan history it does not need.

When two or more of these are true, the fix is not a code-review policy — it is database-enforced immutability with a verifiable tamper signal, built directly into the schema. This is the mechanism-level control that the rest of Audit Trail and Compliance for Reconciliation assumes is already in place before it asks about retention policy, access review, or evidence export.

Step-by-Step Implementation

Immutability has to be layered because each layer defends against a different actor: application bugs, careless operators, and a database role with elevated privileges. Build it in this order.

  1. Define the append-only schema — a range-partitioned table keyed by recorded_at, with prev_hash and row_hash columns present from the first row.
  2. Install the blocking trigger on the partitioned parent — a BEFORE UPDATE OR DELETE trigger that raises unconditionally; on PostgreSQL 11+, a trigger defined on a partitioned table automatically applies to every existing and future partition.
  3. Revoke mutation grants from the writer role — belt-and-suspenders alongside the trigger, so a role misconfiguration alone cannot open a mutation path.
  4. Seed the genesis hash — the first row’s prev_hash is a fixed 64-character sentinel, never a null or empty string, so the chain has one unambiguous starting point.
  5. Compute the chained hash in the writer, not in SQL — canonicalize the payload, hash it with the prior row’s row_hash, and serialize concurrent appends so two transactions never both read the same “last” hash.
  6. Schedule partition rollover and retention — roll a new monthly partition ahead of need and detach expired partitions to write-once archival storage instead of deleting them.
SQL
-- 1. Append-only, partitioned schema. recorded_at drives both partitioning
-- and the global hash-chain ordering, so it must never be client-supplied.
CREATE TABLE recon_ledger (
    ledger_id     bigint GENERATED ALWAYS AS IDENTITY,
    txn_id        text NOT NULL,
    event_type    text NOT NULL,
    payload       jsonb NOT NULL,
    recorded_at   timestamptz NOT NULL DEFAULT clock_timestamp(),
    prev_hash     char(64) NOT NULL,
    row_hash      char(64) NOT NULL,
    PRIMARY KEY (ledger_id, recorded_at)
) PARTITION BY RANGE (recorded_at);

CREATE TABLE recon_ledger_2026_07 PARTITION OF recon_ledger
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE recon_ledger_2026_08 PARTITION OF recon_ledger
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

CREATE INDEX recon_ledger_txn_idx ON recon_ledger (txn_id);

-- 2. Trigger function that unconditionally rejects UPDATE and DELETE.
-- Attached to the partitioned parent, it propagates to every partition.
CREATE OR REPLACE FUNCTION recon_ledger_block_mutation()
RETURNS trigger AS $$
BEGIN
    RAISE EXCEPTION
        'recon_ledger is append-only: % on ledger_id % is not permitted',
        TG_OP, OLD.ledger_id
        USING ERRCODE = '23001';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER recon_ledger_no_update
    BEFORE UPDATE ON recon_ledger
    FOR EACH ROW EXECUTE FUNCTION recon_ledger_block_mutation();

CREATE TRIGGER recon_ledger_no_delete
    BEFORE DELETE ON recon_ledger
    FOR EACH ROW EXECUTE FUNCTION recon_ledger_block_mutation();

-- 3. Grants: the writer role can INSERT and SELECT only.
REVOKE UPDATE, DELETE, TRUNCATE ON recon_ledger FROM recon_writer;
GRANT INSERT, SELECT ON recon_ledger TO recon_writer;

The chained hash is the third layer: it does not prevent a mutation from a role that still has superuser or table-owner privileges, but it makes any such mutation mathematically detectable, because changing one row invalidates every row_hash that follows it. Each row’s hash is a function of the previous row’s hash and the current row’s canonical content:

Hn=SHA-256(Hn−1 ∥ txnn ∥ eventn ∥ payloadn ∥ tn)H_n = \mathrm{SHA\text{-}256}\bigl(H_{n-1} \,\Vert\, \text{txn}_n \,\Vert\, \text{event}_n \,\Vert\, \text{payload}_n \,\Vert\, t_n\bigr)

where Hn−1H_{n-1} is the prior row’s row_hash, ∥\Vert is concatenation, and payloadn\text{payload}_n is serialized deterministically so the same logical content always hashes to the same value. Compute this in the application layer, not in a SQL trigger, so the exact hashing logic is testable, versioned, and independent of database upgrades:

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

import psycopg

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

GENESIS_HASH = "0" * 64
CHAIN_LOCK_KEY = "recon_ledger_chain"  # advisory-lock namespace, one per ledger table


@dataclass(frozen=True)
class LedgerEntry:
    """One unit of work to append; never mutated once constructed."""
    txn_id: str
    event_type: str
    payload: dict[str, Any]


def canonical_payload(payload: dict[str, Any]) -> str:
    """Deterministic JSON so identical logical content always hashes identically."""
    return json.dumps(payload, sort_keys=True, separators=(",", ":"), default=str)


def compute_row_hash(
    prev_hash: str,
    txn_id: str,
    event_type: str,
    payload: dict[str, Any],
    recorded_at: datetime,
) -> str:
    """SHA-256 over the row's immutable fields, chained to the prior row's hash."""
    digest_input = "|".join(
        [prev_hash, txn_id, event_type, canonical_payload(payload), recorded_at.isoformat()]
    )
    return hashlib.sha256(digest_input.encode("utf-8")).hexdigest()


def fetch_last_hash(conn: psycopg.Connection) -> str:
    """Read the latest row_hash under a transaction-scoped advisory lock.

    The lock serializes concurrent appenders so two writers never both read
    the same "last" hash and fork the chain into two valid-looking branches.
    """
    with conn.cursor() as cur:
        cur.execute("SELECT pg_advisory_xact_lock(hashtext(%s))", (CHAIN_LOCK_KEY,))
        cur.execute(
            "SELECT row_hash FROM recon_ledger ORDER BY recorded_at DESC, ledger_id DESC LIMIT 1"
        )
        row = cur.fetchone()
        return row[0] if row else GENESIS_HASH


def append_entry(conn: psycopg.Connection, entry: LedgerEntry) -> str:
    """Append one entry to the hash-chained ledger inside a single transaction."""
    with conn.transaction():
        prev_hash = fetch_last_hash(conn)
        recorded_at = datetime.now(timezone.utc)
        row_hash = compute_row_hash(
            prev_hash, entry.txn_id, entry.event_type, entry.payload, recorded_at
        )
        with conn.cursor() as cur:
            cur.execute(
                """
                INSERT INTO recon_ledger
                    (txn_id, event_type, payload, recorded_at, prev_hash, row_hash)
                VALUES (%s, %s, %s, %s, %s, %s)
                """,
                (
                    entry.txn_id,
                    entry.event_type,
                    json.dumps(entry.payload, default=str),
                    recorded_at,
                    prev_hash,
                    row_hash,
                ),
            )
        logger.info(
            "ledger_append txn=%s event=%s prev=%s row=%s",
            entry.txn_id, entry.event_type, prev_hash[:12], row_hash[:12],
        )
        return row_hash

The advisory lock is not optional. Without it, two concurrent transactions can both read the same prev_hash, each compute a valid-looking row_hash, and commit — producing two rows that both claim the same parent and silently forking the chain. pg_advisory_xact_lock scopes the lock to the transaction, so it releases automatically on commit or rollback even if the writer process crashes.

Hash-chained append-only ledger with blocked mutation path A banner shows the recon_writer role granted INSERT and SELECT only, with UPDATE, DELETE, and TRUNCATE revoked. Below it, a genesis seed hash of all zeros feeds into ledger row 1, whose row_hash becomes the prev_hash of ledger row 2, whose row_hash becomes the prev_hash of ledger row 3, and the chain continues rightward. A dashed line drops from ledger row 2 to a panel showing an attempted UPDATE or DELETE being blocked by a BEFORE trigger that raises exception 23001, enforcing append-only behavior even if a grant were misconfigured. recon_writer role: GRANT INSERT, SELECT only — REVOKE UPDATE, DELETE, TRUNCATE GENESIS SEED hash 000…000 LEDGER ROW 1 txn TXN-58201 event MATCH_COMMIT prev 000…000 hash a3f1c9e2… LEDGER ROW 2 txn TXN-58202 event TOLERANCE_ADJUST prev a3f1c9e2… hash 7b2d40aa… LEDGER ROW 3 txn TXN-58203 event EXCEPTION_ROUTE prev 7b2d40aa… hash e91c05f4… … UPDATE / DELETE attempt BEFORE trigger fires RAISE EXCEPTION 23001 row rejected, chain intact chains chains blocked

Configuration Reference

Calibrate these per ledger table. The hash algorithm and lock key must stay fixed for the life of a chain — changing either mid-stream breaks verification for every row written before the change.

Parameter Accepted values Default Notes
hash_algorithm sha256, sha512 sha256 Fixed for the table’s lifetime; never rotate without starting a new chain segment.
partition_interval 1 month, 1 week, 1 quarter 1 month Monthly balances index size against partition-management overhead for typical reconciliation volume.
retention_period 3–10 years 7 years Aligned to SOX and the applicable financial-records retention statute, not a storage-cost decision.
advisory_lock_key text, one per ledger table recon_ledger_chain Passed through hashtext(); must be unique per chain to avoid cross-table lock contention.
chain_verify_cadence hourly – daily daily Full or incremental recompute-and-compare pass, scheduled off-peak.
archive_tier detach+glacier, detach+worm-bucket detach+worm-bucket Expired partitions are detached, never dropped, and moved to object storage with object-lock enabled.
correction_event_type free text, closed set recommended CORRECTION Marks a row that logically reverses an earlier one; the original row is never touched.

Debugging & Recovery

Immutability changes what “fixing a mistake” means: you can never repair a row, only add a new one and prove the chain is otherwise intact.

  • Verifying the chain. Walk the table in recorded_at, ledger_id order across all partitions — a partition boundary carries no special meaning to the chain, so verification must query the parent table, not a single partition. Recompute compute_row_hash for each row using its stored prev_hash, and compare against the stored row_hash. The first mismatch is the tamper point; every row after it is untrustworthy by construction, which is exactly the signal an auditor needs.
  • Confirming enforcement hasn’t drifted. After any migration, query information_schema.role_table_grants for recon_writer and pg_trigger for the two blocking triggers. A migration that recreates the table without reapplying the trigger and revokes is the most common way this control silently disappears.
  • Handling a legitimate correction. Never UPDATE the original row, even under emergency change control. Append a new row with event_type = 'CORRECTION' whose payload references the original ledger_id and states the corrected value. The ledger then shows both the mistake and the fix, which is stronger evidence than a silently repaired number.
  • Recovering from an interrupted append. Because append_entry wraps the read of prev_hash and the insert in one transaction, a crash mid-append simply rolls back — no partial or orphaned row can enter the chain. If a dead connection appears to be holding the advisory lock, check pg_locks joined to pg_stat_activity; a lock held by a session with no active backend is safe to wait out or terminate.
  • Backfilling historical data. A backfill of pre-ledger history is not an exception to append-only — insert it as ordinary rows with recorded_at set to the true historical timestamps and let the chain absorb them in order. Never seed a backfill with a fabricated prev_hash; compute it the same way the writer does, from the actual preceding row.

FAQ

Does the hash chain replace the REVOKE and the trigger, or do I need all three?

All three, because each defends a different actor. The REVOKE stops an application role with a bug or a compromised credential from issuing UPDATE/DELETE in the first place. The trigger is a second, independent gate that fires even if a grant is accidentally reintroduced by a future migration. The hash chain is the only layer that still protects you against a role with elevated privileges — a table owner or superuser can bypass grants and triggers, but cannot edit a row without invalidating every hash that follows it, which a routine verification pass will catch.

How do I handle a superuser who can bypass the trigger and the grants entirely?

Assume that access exists and design for detection, not prevention, at that privilege tier. Restrict superuser and table-owner roles to a narrow break-glass process with logged, approved access; run chain_verify_cadence frequently enough that an out-of-band edit is caught within one audit cycle, not one year; and archive detached partitions to object storage with object-lock (WORM) enabled, so even a superuser edit inside PostgreSQL does not touch the archived copy used for external verification.

Why chain hashes at the row level instead of just storing a single table checksum?

A single table-level checksum tells you that something changed, but not what or when — you would have to diff against a full prior backup to localize the tampering, if one even exists. Row-level chaining turns every row into a checkpoint: verification pinpoints the exact ledger_id where the chain breaks, and every row before it remains provably intact, which is the difference between “the ledger might be compromised” and “row 118,402 was altered after 2026-07-15T14:02:11Z.”