Production Schema Design for Immutable, Audit-Ready Budget Tracking

A film budget stays reconcilable only when every transaction resolves to a validated, versioned cost-code node before it reaches the general ledger. Production schema design is the layer that guarantees this by construction: it defines how purchase orders, timecards, vendor invoices, and petty-cash disbursements are typed, related, persisted, and made tamper-evident so that the ledger a completion guarantor reads on Friday is byte-for-byte the ledger the payroll service and the line producer are working from. This is a data-engineering problem, not a spreadsheet one — the schema must reject malformed input at ingestion, preserve full history under an append-only contract, and emit a cryptographic audit trail that survives multi-jurisdictional co-productions and a bond lender’s forensic review. This page sits within Core Production Architecture & Taxonomy and specifies the relational, version-controlled model that classification and compliance rules depend on.

Prerequisites

This architecture assumes a Python 3.12+ runtime and the following libraries and inputs:

  • Pydantic v2 (pydantic>=2.6) for strict schema validation at the ingestion boundary — model_validate, field_validator, and ConfigDict(extra="forbid") are used throughout.
  • SQLAlchemy 2.0 for the relational core, foreign-key enforcement across units, departments, and cost_codes, and the append-only ledger tables.
  • zoneinfo from the standard library for timezone-aware timestamps keyed to IANA identifiers (for example America/Los_Angeles), never naive datetime objects or fixed UTC offsets — production days cross daylight-saving boundaries and multiple shooting jurisdictions.
  • decimal.Decimal for every monetary field; binary float is prohibited because a single fractional cent compounded across thousands of line items becomes a variance a guarantor will question.
  • Data inputs: heterogeneous vendor CSV exports, EP/Showbiz payroll manifests, PO and deal-memo records, and daily timecards — each carrying a candidate cost code that must be validated against the studio-approved chart of accounts.

The schema is the contract every one of those inputs must satisfy. The canonical code set it validates against is defined by Cost Code Standardization, and the concurrent fetching and dead-letter routing that deliver the raw records belong to Cost Ingestion & Data Parsing Workflows.

Architecture: the write path from transaction to versioned ledger

The core rule runs through every subsystem: nothing enters the general ledger until it has resolved to a known taxonomic key and survived validation. A raw record arrives, is normalized, is validated against the schema and the derived compensation tier, and is then either committed to an immutable, versioned ledger or diverted to a quarantine queue for manual reconciliation. There is no side door. Corrections to a committed entry are never in-place edits — they are offsetting entries that preserve the original lineage, so that the historical state at any reporting milestone can be reconstructed exactly.

The write path below shows how a single transaction moves from ingestion to a versioned, hashed ledger entry, with validation failures diverted rather than silently dropped.

Write path from raw transaction to a versioned, hashed ledger entry A raw transaction dict is normalized, then passed through a single validation gate that runs Pydantic model_validate and derives the compensation tier from the cost code. Valid records commit to an append-only ledger as a new version that never overwrites prior state, then emit a SHA-256 audit event to write-once storage. Invalid records divert to a quarantine staging table holding the raw payload, its hash and a reason code, which raises a webhook alert to accounting and feeds manual reconciliation. Only validated, hashed, versioned rows reach the ledger — there is no side door. valid invalid Raw transaction (dict payload) Normalize fields model_validate + tier valid? Commit to append-only ledger new version — never overwrites Emit SHA-256 audit event write-once storage Quarantine staging table raw payload + hash + reason Webhook alert → accounting manual reconciliation No side door — only validated, hashed, versioned rows reach the ledger.
The write path — a single validation gate derives the tier and admits only well-typed records; valid rows append a new ledger version and a write-once SHA-256 audit event, while failures divert to quarantine rather than entering the ledger silently.

Because schema design owns how state and history are persisted, it is deliberately narrow: it does not decide whether a payroll record is compliant — that reasoning belongs to Guild Compliance & Rule Validation Automation — and it does not decide who is allowed to mutate a record, which is the concern of Security & Access Boundaries. Schema design guarantees only that whatever is committed is well-typed, correctly tiered, fully versioned, and provably unaltered.

Core implementation: modeling the immutable schema in Python

The ingestion boundary is a strict Pydantic v2 model. Three properties make it audit-ready: extra fields are forbidden so that unexpected keys cannot smuggle unvalidated data through; the compensation tier is derived from the cost code rather than accepted from the caller, closing the gap where a mis-tagged input lands above the line when the agreement puts it below; and the timestamp is coerced to a timezone-aware value so that day boundaries — which drive meal penalties and turnaround — are unambiguous.

import hashlib
from decimal import Decimal
from datetime import datetime
from zoneinfo import ZoneInfo
from typing import Any, Optional

from pydantic import BaseModel, ConfigDict, field_validator, ValidationError

# Prefix → compensation tier, derived (never trusted from the payload).
ABOVE_THE_LINE = ("ATL-",)
BELOW_THE_LINE = ("BTL-", "POST-", "LOC-")
APPROVED_PREFIXES = ABOVE_THE_LINE + BELOW_THE_LINE
PRODUCTION_TZ = ZoneInfo("America/Los_Angeles")


class ExpensePayload(BaseModel):
    model_config = ConfigDict(extra="forbid")

    transaction_id: str
    cost_code: str
    amount: Decimal          # Money is Decimal to avoid binary-float rounding drift.
    department: str
    unit_id: str
    timestamp: datetime
    payload_hash: Optional[str] = None

    @field_validator("cost_code")
    @classmethod
    def validate_cost_code(cls, value: str) -> str:
        # In production, replace this prefix check with a lookup against the
        # studio-approved chart of accounts loaded from Cost Code Standardization.
        if not value.startswith(APPROVED_PREFIXES):
            raise ValueError("cost_code does not match an approved hierarchy prefix")
        return value

    @field_validator("timestamp")
    @classmethod
    def require_tz_aware(cls, value: datetime) -> datetime:
        # Reject naive datetimes outright, then normalize to the production zone
        # so downstream day-boundary logic is deterministic across DST shifts.
        if value.tzinfo is None or value.utcoffset() is None:
            raise ValueError("timestamp must be timezone-aware (IANA zone)")
        return value.astimezone(PRODUCTION_TZ)

    @property
    def above_the_line(self) -> bool:
        return self.cost_code.startswith(ABOVE_THE_LINE)

    def generate_audit_hash(self) -> str:
        # Quantize the amount so the hash is deterministic and reproducible.
        normalized_amount = self.amount.quantize(Decimal("0.01"))
        raw = (
            f"{self.transaction_id}|{self.cost_code}|{normalized_amount}|"
            f"{self.unit_id}|{self.timestamp.isoformat()}"
        )
        return hashlib.sha256(raw.encode("utf-8")).hexdigest()


def route_transaction(raw_payload: dict[str, Any]) -> dict[str, Any]:
    # Validation happens at construction, so the quarantine branch wraps
    # model_validate rather than the hashing step.
    try:
        payload = ExpensePayload.model_validate(raw_payload)
    except ValidationError as exc:
        # Quarantine, log, and trigger the reconciliation webhook downstream.
        return {"status": "quarantined", "errors": exc.errors()}

    payload.payload_hash = payload.generate_audit_hash()
    return {
        "status": "approved",
        "tier": "ATL" if payload.above_the_line else "BTL",
        "hash": payload.payload_hash,
    }

Validation alone is not persistence. The relational core ties units, departments, and cost codes to mutable transactions while writing immutable, versioned snapshots and a hashed audit event on every commit. The entity model below is the hardest concept on the page — how a live transaction relates to the frozen history a lender reads.

Relational model linking units, cost codes, transactions, versioned snapshots and audit events A mutable relational core forms a one-to-many chain: a unit contains departments, a department owns cost codes, a cost code classifies line items, and a line item accrues transactions. Each transaction is versioned into one or more budget snapshots that live in a separate append-only, immutable history panel, and each snapshot emits exactly one audit event carrying the payload hash. Crow's-foot notation marks the single-bar one side and the many side of every relationship, distinguishing the live transaction from the frozen history a lender reads. APPEND-ONLY HISTORY · IMMUTABLE MUTABLE RELATIONAL CORE contains owns classifies accrues versioned into emits UNIT unit_id · string name · string DEPARTMENT department_id · string COST_CODE code · string prefix · string above_the_line · bool LINE_ITEM line_item_id · string TRANSACTION transaction_id · string amount · decimal timestamp · datetime BUDGET_SNAPSHOT snapshot_id · string version · int immutable · bool AUDIT_EVENT event_id · string payload_hash · string
The relational model — a one-to-many taxonomy chain feeds each live transaction, which is versioned into an append-only snapshot that emits exactly one hashed audit event. Crow's-foot marks the one and many ends; the dashed panel is the frozen history a lender reconstructs.

In SQLAlchemy 2.0, the immutability contract is expressed as an append-only table plus foreign keys that make an orphaned transaction impossible. Snapshots are never updated in place; a new row with an incremented version supersedes the previous one, and the audit event carries the payload hash forward.

from datetime import datetime
from decimal import Decimal

from sqlalchemy import ForeignKey, Numeric, String, Integer, DateTime
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class CostCode(Base):
    __tablename__ = "cost_codes"
    code: Mapped[str] = mapped_column(String, primary_key=True)
    unit_id: Mapped[str] = mapped_column(ForeignKey("units.unit_id"), nullable=False)
    above_the_line: Mapped[bool] = mapped_column(nullable=False)


class BudgetSnapshot(Base):
    # Append-only: corrections arrive as a new version, never an UPDATE.
    __tablename__ = "budget_snapshots"
    snapshot_id: Mapped[str] = mapped_column(String, primary_key=True)
    transaction_id: Mapped[str] = mapped_column(
        ForeignKey("transactions.transaction_id"), nullable=False
    )
    cost_code: Mapped[str] = mapped_column(
        ForeignKey("cost_codes.code"), nullable=False
    )
    amount: Mapped[Decimal] = mapped_column(Numeric(14, 2), nullable=False)
    version: Mapped[int] = mapped_column(Integer, nullable=False)
    payload_hash: Mapped[str] = mapped_column(String, nullable=False)
    committed_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))

Storing amount as Numeric(14, 2) rather than a floating column keeps the database representation aligned with the Decimal used in Python, and DateTime(timezone=True) preserves the timezone-aware instant the ingestion layer produced.

Guild and contract specifics

The above/below-the-line boundary the schema derives is not accounting cosmetics — it drives contractual obligations under four agreements, and each cost-code node carries metadata flags that route a transaction to the correct rule engine. The compensation-tier resolution itself is specified by Above/Below-the-Line Mapping; this schema persists the resolved tier and the fringe metadata that downstream calculators consume.

  • Screen Actors Guild-American Federation of Television and Radio Artists (SAG-AFTRA): performer records carry flags for pension and health contribution eligibility and residual basis, feeding the calculators in SAG-AFTRA Residuals Logic and Pension & Health Fund Calculations.
  • Directors Guild of America (DGA): timecard-linked line items expose worked and rest-period timestamps so that the penalties in DGA Overtime & Turnaround Rules can be evaluated against the schema’s timezone-aware timestamp field.
  • Writers Guild of America (WGA): script and story cost codes flag residual-bearing material for later basis calculation.
  • International Alliance of Theatrical Stage Employees (IATSE): below-the-line crew records carry fringe multipliers and health-and-welfare cap fields.

A rate table referenced by these flags is keyed by (guild, jurisdiction, effective_date) and returns scale rate, fringe multiplier, and overtime and meal-penalty thresholds. Because rates change mid-shoot, the effective-date dimension must be temporal: a transaction stamped with a given production date resolves against the rate row in force on that date, not the latest one. Bond lenders require this mapping to remain immutable once the budget is locked, so that a contingency draw is always traceable to the compensation tier and rate table that authorized it. When a required rate row is missing, resolution defers to the ordered strategy in Compliance Fallback Chains rather than silently defaulting.

Error handling and quarantine

Validation failures are never dropped and never retried inline. When model_validate raises — an unrecognized prefix, a naive timestamp, a mismatched departmental tag, an extra field — the transaction is written to a staging table with three mandatory artifacts: the original raw payload, a SHA-256 hash of that payload for tamper-evidence, and the machine-readable Pydantic error list as a reason code. An alert is then dispatched to the accounting team by webhook. This is the same quarantine contract the ingestion side enforces in Schema Validation & Error Handling; the schema simply refuses to let a partially-valid record contaminate the main ledger.

Two production behaviours make the quarantine safe. First, idempotency: the SHA-256 payload hash doubles as a dedupe key, so a re-delivered manifest cannot create duplicate quarantine or ledger rows. Second, controlled bypass for genuine emergencies — weather-forced location changes, safety-mandated crew adjustments — routes through a dedicated emergency ledger that demands dual-approval tokens and a time-bound authorization window, then flags a mandatory post-incident reconciliation. The authorization side of that bypass is governed by the role model in Security & Access Boundaries; the schema’s role is to record the override as a first-class, hashed audit event rather than an untraceable edit.

Verification

Correct output is verifiable, not assumed. For every approved transaction the following must hold:

  • Ledger entries: exactly one budget_snapshot row exists at the highest version for the transaction, its amount matches the quantized Decimal from ingestion, and its cost_code foreign key resolves to an active code on the stated unit_id.
  • Audit log fields: each commit emits one audit_event carrying event_id, transaction_id, payload_hash, the derived tier (ATL/BTL), and the committing principal — written to write-once storage so it cannot be back-dated.
  • Reconciliation report shape: the nightly job compares staged transactions against approved budget allocations and produces, per cost code, the committed total, the variance against the locked budget, and a list of quarantined records with their reason codes. Any variance above the defined threshold is surfaced for a completion-guarantor-ready cost report rather than absorbed.

A schema that passes these checks yields the property lenders demand: cash drawdowns, contingency utilization, and cost-to-complete projections reconcile to the exact financial state at each milestone, with zero reconciliation drift. The immutable hierarchy that guarantees this across parallel shoots — where a single picture runs several units and split schedules at once — is worked through in Designing Immutable Cost-Code Hierarchies for Multi-Unit Shoots.

Up one level: Core Production Architecture & Taxonomy.