Modeling Append-Only Lease Ledgers for Audit Trails

The Amendment Versioning & Ledgers guide establishes why a lease is modeled as an ordered history and shows the resolver that folds that history to an as-of state. This page answers the question one layer down: what does the table actually look like, and what stops someone — a bug, a rushed migration, or a bad actor — from quietly rewriting a rent figure after an auditor has already relied on it? The sibling guide on resolving amendment conflicts owns the precedence algorithm; this page owns the schema and persistence design — the columns, the immutability constraints, the tamper-evidence chain, and the retention story an ASC 842 or IFRS 16 audit is going to lean on.

The distinction matters because an append-only ledger is only as defensible as its storage layer. A resolver that folds events correctly is worthless if the events underneath it can be edited in place, if two rows can claim the same position in history, or if a value can appear with no document behind it. The design goal here is a table where “append-only” and “tamper-evident” are enforced by the schema, the database grants, and a cryptographic chain — not by a code review promise.

Architectural context

This layer sits directly beneath the resolver inside Core Architecture & Lease Taxonomy. Amendment events arrive already aligned to canonical keys by the metadata normalization standards, so the ledger stores a delta whose field names it can trust. The resolved snapshot it produces conforms to the lease data models that billing and reporting read. And the immutable log it maintains is exactly what security & access boundaries exposes to time-bound, read-only auditor tokens — and what a downstream ASC 842 compliance export traces every schedule line back to.

Two ideas do all the work in the schema below. First, immutable event rows: a row is written once and never updated or deleted, so the history is a growing list, not a mutable set. Second, two independent time axes — an effective_at for when a term takes economic force and a recorded_at for when the system learned of it — because a backdated amendment signed late has an effective date in the past and a recorded date in the present, and every retroactive query depends on keeping them apart. A hash chain binds the rows together so any after-the-fact edit becomes detectable.

Storage strategy: append-only ledger vs. the alternatives

Before the columns, the shape. Three storage patterns compete for the lease record, and only one survives an audit that asks “prove what you billed, and prove nobody changed it.”

The append-only ledger with a materialized snapshot is the only column that is both fast to read and defensible to audit. It pays a small write-time and storage cost for an immutable, tamper-evident history — a trade every ASC 842 / IFRS 16 program makes gladly, because the alternative is being unable to answer a regulator.

Bitemporal and provenance columns, at a glance

Recommended implementation

The primary implementation is Python. Each row is a frozen pydantic model that self-verifies its hash at construction, so a tampered row cannot even be instantiated. Money lives in the payload as a string-encoded Decimal, never a float. The append_event helper computes the chained hash; verify_chain walks the whole history and raises on the first break.

from __future__ import annotations

import hashlib
import json
from datetime import datetime, timezone
from decimal import Decimal
from typing import Any, Optional

from pydantic import BaseModel, Field, field_validator, model_validator

GENESIS_HASH = "0" * 64


class LedgerIntegrityError(Exception):
    """Raised when the hash chain or sequence invariant is violated."""


def _canonical_bytes(material: dict[str, Any]) -> bytes:
    """Deterministic serialization of the tamper-evident fields only."""
    return json.dumps(material, sort_keys=True, separators=(",", ":"),
                      default=str).encode("utf-8")


class LedgerEntry(BaseModel):
    """One immutable, bitemporal, hash-chained lease event row."""
    model_config = {"frozen": True}  # rows can never be mutated in place

    seq: int = Field(ge=1)
    lease_id: str
    event_type: str                       # lease.created | rent.stepped | cam.added | clause.redacted
    effective_at: datetime                # when the term takes economic force
    recorded_at: datetime                 # when the system learned of it
    payload: dict[str, Any]               # sparse delta; money as string-encoded Decimal
    source_document_id: str               # provenance: which instrument
    source_page: Optional[int] = None
    source_clause: Optional[str] = None
    prev_hash: str = Field(min_length=64, max_length=64)
    row_hash: str = Field(min_length=64, max_length=64)

    def material(self) -> dict[str, Any]:
        """The exact fields the row_hash commits to."""
        return {
            "seq": self.seq,
            "lease_id": self.lease_id,
            "event_type": self.event_type,
            "effective_at": self.effective_at.isoformat(),
            "recorded_at": self.recorded_at.isoformat(),
            "payload": self.payload,
            "source_document_id": self.source_document_id,
            "prev_hash": self.prev_hash,
        }

    @field_validator("effective_at", "recorded_at")
    @classmethod
    def _tz_aware(cls, v: datetime) -> datetime:
        if v.tzinfo is None:
            raise ValueError("timestamps must be timezone-aware; persist UTC")
        return v.astimezone(timezone.utc)

    @model_validator(mode="after")
    def _self_verify(self) -> "LedgerEntry":
        expected = hashlib.sha256(_canonical_bytes(self.material())).hexdigest()
        if expected != self.row_hash:
            raise LedgerIntegrityError(f"row_hash mismatch at seq {self.seq}: tampered or malformed")
        return self


def append_event(prev: Optional[LedgerEntry], *, lease_id: str, event_type: str,
                 effective_at: datetime, payload: dict[str, Any],
                 source_document_id: str, source_page: int | None = None,
                 source_clause: str | None = None) -> LedgerEntry:
    """Build the next immutable row, chaining it to `prev`."""
    seq = 1 if prev is None else prev.seq + 1
    prev_hash = GENESIS_HASH if prev is None else prev.row_hash
    recorded_at = datetime.now(timezone.utc)
    material = {
        "seq": seq,
        "lease_id": lease_id,
        "event_type": event_type,
        "effective_at": effective_at.astimezone(timezone.utc).isoformat(),
        "recorded_at": recorded_at.isoformat(),
        "payload": payload,
        "source_document_id": source_document_id,
        "prev_hash": prev_hash,
    }
    row_hash = hashlib.sha256(_canonical_bytes(material)).hexdigest()
    return LedgerEntry(
        seq=seq, lease_id=lease_id, event_type=event_type,
        effective_at=effective_at, recorded_at=recorded_at, payload=payload,
        source_document_id=source_document_id, source_page=source_page,
        source_clause=source_clause, prev_hash=prev_hash, row_hash=row_hash,
    )


def verify_chain(entries: list[LedgerEntry]) -> None:
    """Single O(n) pass; raises on the first tamper-evidence violation."""
    expected_prev = GENESIS_HASH
    for i, e in enumerate(entries):
        if e.seq != i + 1:
            raise LedgerIntegrityError(f"sequence gap at index {i}: got seq {e.seq}")
        if e.prev_hash != expected_prev:
            raise LedgerIntegrityError(f"broken link at seq {e.seq}: prev_hash does not match prior row")
        recomputed = hashlib.sha256(_canonical_bytes(e.material())).hexdigest()
        if recomputed != e.row_hash:
            raise LedgerIntegrityError(f"row tampered at seq {e.seq}")
        expected_prev = e.row_hash


def as_of(entries: list[LedgerEntry], *, effective_at: datetime,
          recorded_at: datetime) -> dict[str, Any]:
    """Bitemporal fold: state effective for a date, as known at a recorded time."""
    state: dict[str, Any] = {}
    ordered = sorted(entries, key=lambda e: (e.effective_at, e.seq))
    for e in ordered:
        if e.recorded_at <= recorded_at and e.effective_at <= effective_at:
            state.update(e.payload)  # sparse delta: absent keys are left untouched
    return state


# --- Usage: build a chained history and reconstruct a mid-2024 snapshot -------
genesis = append_event(
    None, lease_id="L-1001", event_type="lease.created",
    effective_at=datetime(2022, 1, 1, tzinfo=timezone.utc),
    payload={"base_rent": str(Decimal("4500.00")), "cam_recovery": False},
    source_document_id="DOC-BASE-1001", source_page=1,
)
step = append_event(
    genesis, lease_id="L-1001", event_type="rent.stepped",
    effective_at=datetime(2024, 1, 1, tzinfo=timezone.utc),
    payload={"base_rent": str(Decimal("4750.00"))},
    source_document_id="DOC-AMD-2", source_page=3, source_clause="4(b)",
)
history = [genesis, step]
verify_chain(history)  # no exception => chain intact

snapshot = as_of(history,
                 effective_at=datetime(2024, 7, 1, tzinfo=timezone.utc),
                 recorded_at=datetime.now(timezone.utc))
assert Decimal(snapshot["base_rent"]) == Decimal("4750.00")
assert snapshot["cam_recovery"] is False  # from genesis; never re-stated

The physical table enforces the same invariants the code does, so a stray UPDATE fails at the database boundary rather than corrupting the chain silently:

CREATE TABLE lease_ledger (
    lease_id           UUID        NOT NULL,
    seq                BIGINT      NOT NULL,
    event_type         TEXT        NOT NULL,
    effective_at       TIMESTAMPTZ NOT NULL,               -- economic force
    recorded_at        TIMESTAMPTZ NOT NULL DEFAULT now(), -- system knowledge
    payload            JSONB       NOT NULL,               -- money as string-encoded numeric
    source_document_id UUID        NOT NULL,               -- provenance
    source_page        INT,
    source_clause      TEXT,
    prev_hash          CHAR(64)    NOT NULL,
    row_hash           CHAR(64)    NOT NULL,
    PRIMARY KEY (lease_id, seq),
    UNIQUE (lease_id, row_hash)
);

-- Append-only at the boundary: the app role may insert, never rewrite history.
REVOKE UPDATE, DELETE ON lease_ledger FROM app_writer;
GRANT  INSERT, SELECT ON lease_ledger TO app_writer;

-- Covering index for fast as-of reads on the bitemporal columns.
CREATE INDEX idx_ledger_bitemporal
    ON lease_ledger (lease_id, effective_at, recorded_at);

-- Materialized snapshot for hot as-of(now, now) reads; refreshed on append.
CREATE MATERIALIZED VIEW lease_current AS
SELECT DISTINCT ON (lease_id) lease_id, seq, payload, row_hash
FROM lease_ledger
WHERE recorded_at <= now()
ORDER BY lease_id, effective_at DESC, recorded_at DESC;

Edge cases specific to commercial leases

• Clock skew on recorded_at. If append servers set recorded_at from local clocks, a row can be recorded “before” the row it chains to, which corrupts “what did we know on day X” queries. Stamp recorded_at from a single authority — the database now() — not the application host, and keep it monotonic per lease so recorded order never contradicts sequence order.
• Back-dated effective dates. A lease amendment signed in December but effective the prior January has effective_at far behind recorded_at. That is legal and common; the schema must never reject it. Historical rent rolls resolve by effective_at, so the back-dated row correctly rewrites the past economic state while audit “as-known” queries resolve by recorded_at.
• Hash-chain break detection. A failed verify_chain means either a row was edited in place, a row was deleted, or an event was inserted out of order. Because prev_hash binds each row to its predecessor, a single altered byte anywhere invalidates every hash after it — so the break also tells you the earliest seq that was touched.
• Large-fold cost. A lease with hundreds of CAM reconciliations makes a full fold expensive on a hot dashboard. Materialize the snapshot at fiscal boundaries and fold forward from the nearest one; the immutable log stays authoritative, the snapshot is only a cache keyed on the max seq.
• GDPR / PII redaction vs. immutability. A right-to-erasure request collides head-on with “never delete a row.” Resolve it by appending a clause.redacted event that tombstones the sensitive value and storing the redacted PII behind a separately-keyed vault referenced by source_document_id — so you can crypto-shred the key without ever mutating the ledger or breaking the chain. The row stays; the readable secret does not.

When to escalate

The ledger is designed to make tampering visible, not to adjudicate it. Escalate out of the automated path when:

• verify_chain raises in production — treat it as a potential integrity incident, not a data glitch. Freeze appends for the affected lease and hand the earliest broken seq to a security & access boundaries audit, which owns who could have written to the store and when.
• A field surfaces with no source_document_id — a value entered the ledger without an instrument behind it, which fails an audit on sight; divert it to review rather than letting it reach a compliance export.
• A snapshot disagrees with a fresh fold — the materialized view is stale or corrupt; rebuild it from the log, and if the log itself fails verification, escalate as above.
• An erasure request targets a value load-bearing for ASC 842 — legal and compliance decide together, because redacting a rent input can change a reported schedule.

Frequently asked questions

How do I make a lease ledger tamper-evident without a blockchain? Chain each row to the previous one with a hash: compute row_hash = sha256(seq, effective_at, recorded_at, payload, source_document_id, prev_hash) and store both row_hash and the prev_hash it extends. Any later edit to a row changes its hash and breaks every link after it, so a single verify_chain pass detects tampering. It is the audit-trail benefit of a chain without the operational weight of a distributed ledger.

Why store both effective_at and recorded_at instead of one timestamp? Because they answer different questions. effective_at reconstructs the economic state on a past date — what rent was actually in force — while recorded_at reconstructs what the system knew on a past date. A back-dated amendment has them far apart, and retroactive rent rolls, restatements, and “as-known” audit queries all depend on keeping the two axes distinct.

How do I honor a GDPR erasure request in an append-only ledger? Never delete the row. Append a redaction event that tombstones the value and keep the sensitive data in a separately-keyed vault referenced by source_document_id; destroying that key crypto-shreds the readable PII while the ledger row and its hash chain stay intact. Immutability of the audit trail and erasure of the secret are reconciled by moving the secret out of the row, not the row out of the log.

What breaks if two rows share a sequence number? The tamper-evidence and the ordering both collapse: prev_hash can no longer point at a unique predecessor, and the resolver’s tie-break stops being deterministic. Enforce a (lease_id, seq) primary key and a monotonic, gap-free sequence per lease so every row has exactly one place in history.

Do I need to re-verify the whole chain on every read? No. Verify each row’s self-hash cheaply at construction, run a full verify_chain on a schedule (nightly, and before any audit export), and serve normal reads from the materialized snapshot. Reserve the end-to-end walk for integrity checks and for the moment an auditor asks you to prove the history is intact.

Related

• Resolving Amendment Conflicts with Latest-Effective-Wins — the precedence algorithm that folds the rows this page persists.
• Amendment Versioning & Ledgers — the parent guide framing lease change-over-time as an event-sourced history.
• Lease Data Models — the canonical shape the resolved as-of snapshot conforms to.
• Security & Access Boundaries — who may write to the store and how the immutable log is exposed to auditors.

← Back to Amendment Versioning & Ledgers

← Back to Amendment Versioning & Ledgers