# Comparing Temporal Tables, Event Sourcing, and Document Stores for Cost Ledgers

An auditable production cost ledger has one hard requirement that ordinary application storage does not: the number a weekly cost report showed a completion guarantor in April must still be reconstructable, to the cent, in October — even after a dozen corrections have landed on the same budget line. That single constraint eliminates in-place updates and forces a choice between three durable shapes for the same immutable history: system-versioned (validity-range) temporal tables in a relational store, an append-only event log with derived projections, or a hybrid of append-only JSON documents. This page compares those three against the concrete demands of bond reporting and guild audit, gives a decision matrix, and shows a SQLAlchemy temporal implementation with Decimal-exact money. It extends the modeling approach in Production Schema Design and assumes the append-only discipline established in Designing Immutable Cost Code Hierarchies for Multi-Unit Shoots.

# Prerequisites and Context

The examples target Python 3.11+ for standard-library zoneinfo, use SQLAlchemy 2.0 with typed Mapped[...] declarative models, and rely on decimal for every monetary field and hashlib for tamper-evident fingerprints. Money is always Decimal, built from strings and quantized to cents with ROUND_HALF_UP at the payable point — a float cost figure is a variance a guarantor’s auditor will eventually make you explain. Timestamps are timezone-aware IANA instants (for example "America/Los_Angeles"), never bare UTC offsets, because a second unit in Europe/Budapest books against the same production day as a main unit in Los Angeles.

Two vocabulary notes frame the comparison. A cost ledger here is the running record of committed, actualized, and forecast cost per cost code; an immutable ledger records every correction as a new fact rather than an overwrite, so the state as-of any past instant remains queryable. All three approaches below satisfy immutability — the difference is what they cost you in query complexity, storage, and operational overhead, and how directly each answers the question a bond examiner actually asks: what did this line read on the date of that report? The audit spine every option writes into is the one described in Audit Logging & Immutability, and the reports these ledgers ultimately feed are the guarantor deliverables covered in Completion Bond Reporting & Guarantor Analytics.

# Three Shapes for the Same Immutable History

The three approaches differ mostly in where the history lives and what you reconstruct versus what you store. A temporal table keeps every version of a row as its own record bounded by a validity range, so point-in-time state is a WHERE clause. Event sourcing stores only the deltas — a committed purchase order, a fringe true-up, a bond cutback — and computes current state by folding the log into a projection. A document-store hybrid appends an immutable JSON snapshot per change and treats the newest document as current, keeping the relational engine for indexing and joins.

The consequence of that difference is where the cost of an audit lands. A temporal table pays a modest, predictable storage tax on every write and hands you cheap reads forever after — the reconstruction work is done up front, once, at insert time. Event sourcing inverts that trade: writes are tiny and the log is the cleanest possible audit artifact, but every read of current or historical state pays a fold, and the correctness of that fold is now code you must version and defend. The document-store hybrid pushes the cost into storage — a full snapshot per change is the most space-hungry option — but buys back the simplest possible reconstruction, since each document already is the complete state as-of its stamp with nothing to recompute.

The diagram maps each shape as its own data layout so the storage-versus-reconstruction trade-off is visible at a glance.

# Decision Matrix

The five dimensions below are the ones that actually decide the choice for a cost ledger under bond and guild scrutiny. The ratings are relative, not absolute — every option is auditable if built with discipline; the matrix ranks how much engineering each demands to get there.

The pattern the matrix reveals: temporal tables are the lowest-friction default when your history is a set of revisable line items and your primary query is “state as-of a date.” Event sourcing wins when the sequence of business actions is itself the thing you must defend — every commit, transfer, and cutback in order — and you can absorb the projection tooling. The document-store hybrid is the pragmatic middle when upstream data already arrives as heterogeneous JSON and you want each snapshot to travel with its own hash, accepting higher storage in exchange for self-contained records.

# A SQLAlchemy Temporal-Table Reference

For most production cost ledgers the temporal table is the right first choice, so it is the one worth showing concretely. The model gives each cost-code version its own row bounded by an aware valid_from / valid_to range; a correction closes the open row and inserts a new one in a single transaction, so no UPDATE ever touches a monetary figure. The Numeric column maps to Decimal, and a SHA-256 fingerprint over the canonical fields makes each version independently verifiable. The SQLAlchemy typing idioms here follow the SQLAlchemy ORM declarative documentation.

from __future__ import annotations

import hashlib
import json
from datetime import datetime
from decimal import Decimal, ROUND_HALF_UP
from zoneinfo import ZoneInfo

from sqlalchemy import Numeric, String, DateTime, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

CENTS = Decimal("0.01")
# The "open" end of a validity range — a version in force until superseded.
OPEN_END = datetime(9999, 12, 31, tzinfo=ZoneInfo("UTC"))


class Base(DeclarativeBase):
    pass


class CostLedgerVersion(Base):
    """One immutable version of one cost code's running figure."""

    __tablename__ = "cost_ledger_version"

    version_id: Mapped[int] = mapped_column(primary_key=True)
    cost_code: Mapped[str] = mapped_column(String(32), index=True)
    amount: Mapped[Decimal] = mapped_column(Numeric(14, 2))
    valid_from: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    valid_to: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    entry_hash: Mapped[str] = mapped_column(String(64))
    operator_id: Mapped[str] = mapped_column(String(64), default="SYS_AUTO")


def _quantize(amount: Decimal) -> Decimal:
    return amount.quantize(CENTS, rounding=ROUND_HALF_UP)


def _fingerprint(cost_code: str, amount: Decimal, valid_from: datetime) -> str:
    payload = json.dumps(
        {
            "cost_code": cost_code,
            "amount": str(amount),
            "valid_from": valid_from.astimezone(ZoneInfo("UTC")).isoformat(),
        },
        sort_keys=True,
        separators=(",", ":"),
    ).encode("utf-8")
    return hashlib.sha256(payload).hexdigest()


def post_correction(
    session: Session, cost_code: str, new_amount: str, effective: datetime,
    operator_id: str = "SYS_AUTO",
) -> CostLedgerVersion:
    """Close the currently open version and append a new one atomically."""
    if effective.tzinfo is None or effective.utcoffset() is None:
        raise ValueError("effective timestamp must be timezone-aware")

    amount = _quantize(Decimal(str(new_amount)))

    # Find the version currently in force for this cost code, if any.
    open_row = session.scalar(
        select(CostLedgerVersion)
        .where(CostLedgerVersion.cost_code == cost_code)
        .where(CostLedgerVersion.valid_to == OPEN_END)
    )
    if open_row is not None:
        # Bound the prior version's range; never touch its amount.
        open_row.valid_to = effective

    version = CostLedgerVersion(
        cost_code=cost_code,
        amount=amount,
        valid_from=effective,
        valid_to=OPEN_END,
        entry_hash=_fingerprint(cost_code, amount, effective),
        operator_id=operator_id,
    )
    session.add(version)
    session.commit()
    return version


def amount_as_of(
    session: Session, cost_code: str, instant: datetime
) -> Decimal | None:
    """Reconstruct the figure a report would have shown at `instant`."""
    row = session.scalar(
        select(CostLedgerVersion)
        .where(CostLedgerVersion.cost_code == cost_code)
        .where(CostLedgerVersion.valid_from <= instant)
        .where(CostLedgerVersion.valid_to > instant)
    )
    return row.amount if row is not None else None

amount_as_of is the whole point: the figure any past report showed is a single indexed query, not a replay. The same entry_hash discipline and the same closing-not-overwriting rule port directly to the other two approaches — an event carries the hash of its payload, a JSON snapshot carries the hash of its own bytes — so the audit guarantee is identical regardless of shape.

# Mapping the Choice to Bond and Guild-Audit Requirements

Two reproducibility requirements dominate the decision, and both come from outside engineering. A completion guarantor re-derives cost-to-complete and contingency drawdown from figures your ledger produced weeks earlier; if a line cannot be reconstructed as-of the report date, the reconciliation covered in Completion Bond Reporting & Guarantor Analytics stalls and the draw is questioned. A guild audit — for the Screen Actors Guild – American Federation of Television and Radio Artists (SAG-AFTRA), the Directors Guild of America (DGA), or the International Alliance of Theatrical Stage Employees (IATSE) — traces a specific penalty or fringe accrual back to the source figures and the exact moment they were booked.

Both demands reduce to the same test: given a cost code and a timestamp, return the exact figure and prove it was not altered afterward. Temporal tables answer it with a WHERE clause and win on operational simplicity, which is why they are the default for a single relational cost ledger. Event sourcing answers it by replaying an ordered log and wins when the order and provenance of business actions is itself under audit — a sequence of transfers and cutbacks a guarantor wants to walk step by step. The document-store hybrid answers it by fetching a self-contained, hashed snapshot and wins when records must travel between systems intact. None of the three is disqualifying; choosing against your dominant query pattern is.

A practical corollary: the choice is rarely all-or-nothing. Many production ledgers run a temporal table as the system of record for the point-in-time query the guarantor lives in, while emitting an append-only event alongside each correction to feed downstream forecasting and to give an examiner an ordered narrative. What breaks reproducibility is not mixing shapes — it is letting any one of them permit an in-place edit of a posted figure, or letting two of them disagree because a correction landed in one and not the other. Pick one shape as the authoritative source for the as-of-date read, derive the others from it deterministically, and stamp every derived figure with the source entry_hash so a discrepancy is detectable rather than silent.

# Audit-Trail Requirements

Whichever shape you pick, the persisted history must carry the same minimum fields for it to survive scrutiny. Every version, event, or document must record: the cost_code it pertains to; the amount as a Decimal string; the timezone-aware instant the change took effect (both shoot-local and normalized UTC); an operator_id (SYS_AUTO for automated posts, a real identity for manual overrides); and a entry_hash — a SHA-256 fingerprint over the canonical serialization of those fields, so any later tampering changes the digest. Persist to write-once storage: object storage with an immutability lock, or a relational table whose only permitted mutation is closing a validity range, never editing an amount. A correction is always a new fact — a new row, a compensating event, or a fresh snapshot — never an in-place edit, so the figure a prior report was built on stays recoverable. This is the same write-once, hash-anchored contract detailed in Audit Logging & Immutability, and it is what lets an examiner read an unbroken chain rather than trusting a current-state snapshot.

# Gotchas and Edge Cases

Float contamination at the storage boundary. SQLAlchemy’s Numeric returns Decimal, but a careless float(row.amount) in a report layer reintroduces binary rounding error. Keep money as Decimal end to end and build every Decimal from a string, Decimal(str(x)), so a fractional cent never compounds into the variance a guarantor asks you to explain.

Validity-range gaps and overlaps. In a temporal table, a correction that sets valid_to on the prior row and inserts the new row must run in one transaction; a crash between the two leaves either an overlap (two rows in force at once) or a gap (no row in force), and amount_as_of silently returns the wrong figure or None. Wrap the close-and-append in a single commit and add a constraint or test that no two versions of a cost code share an overlapping range.

Daylight Saving Time on the effective instant. A correction stamped in wall-clock local time near a spring-forward or fall-back transition can sort incorrectly against neighboring versions. Anchor every valid_from to an IANA zone through zoneinfo and compare on the normalized UTC value, while still storing the shoot-local form for the report. A multi-unit shoot that books in Europe/Budapest and America/Los_Angeles on the same calendar day depends on this ordering being unambiguous.

Idempotency on replay. Re-running an ingestion batch after a partial failure must not double-post a correction. Because entry_hash is deterministic, deduplicate on (cost_code, valid_from, entry_hash) before appending; without that guard a retried chunk inserts a duplicate version and corrupts the point-in-time read. In event sourcing the same guard lives on the event’s payload hash, and in the document store on the snapshot hash.

Projection drift in event sourcing. A derived projection that is rebuilt from a changed fold function will not match figures reported under the old logic. Version the projection logic alongside the event schema and record which version produced each published figure, exactly as the residual and turnaround engines stamp a rule_version, so a replayed quarter reproduces the historical number rather than today’s interpretation of it.

# Related

• Production Schema Design — the parent modeling approach these three ledger shapes all specialize for auditable cost history.
• Designing Immutable Cost Code Hierarchies for Multi-Unit Shoots — the append-only, hashed node model that any of these storage shapes sits underneath.
• Audit Logging & Immutability — the write-once, hash-anchored audit contract every version, event, or document must satisfy.
• Completion Bond Reporting & Guarantor Analytics — the guarantor deliverables whose point-in-time reproducibility requirement drives this choice.
• Cost Code Standardization — the canonical keys that let structurally identical ledger entries hash and reconcile cleanly.

Up one level: Production Schema Design, part of Core Production Architecture & Taxonomy.