# EP/Showbiz Sync Parsing: Deterministic Ingestion, Cost-Code Validation, and Bond-Grade Audit Trails

Entertainment Partners (EP) and Showbiz Budgeting are the two accounting platforms most feature and episodic productions actually run their money through, and both export the day’s transactions as flat files or JSON payloads that were shaped for a human accountant to eyeball — not for a ledger to consume unattended. Column indices shift between studio templates, delimiters leak into free-text vendor names, cost codes arrive in three different punctuation styles, and a single un-escaped carriage return can silently split one payroll row into two. When a production accountant patches those defects by hand every night, the nightly close slips, budget variance is discovered days late, and a completion guarantor’s cost-to-complete report is stale before it is generated. This page specifies the parsing layer that removes the hand-patching: a deterministic path from a raw EP or Showbiz export to a validated, FX-normalized ledger feed, where every field is coerced explicitly, every rejection is fingerprinted and quarantined instead of guessed at, and every dollar that reaches the general ledger (GL) can be traced back to the exact byte it came from.

# Prerequisites and Expected Inputs

The reference implementation targets Python 3.11+, both for modern union-type syntax and for zoneinfo in the standard library. The dependency set is deliberately small: Pydantic v2 for boundary schema validation through model_validate and field_validator; polars (or pandas, shown here for familiarity) to read wide exports as raw strings before any coercion; and the standard-library decimal, hashlib, and zoneinfo modules for currency-safe arithmetic, deterministic audit hashing, and timezone-aware timestamps. The validated records are typically committed to an append-only ledger fronted by SQLAlchemy 2.0. Never coerce money through float: a single fractional cent, compounded across the tens of thousands of transactions a shoot generates, becomes exactly the variance a guarantor’s auditor will ask you to explain.

Three input shapes arrive in practice. EP most often delivers a fixed-template CSV over SFTP — one row per transaction, with columns for the transaction id, account (cost) code, department tag, gross amount, currency, post date, and vendor id. Showbiz Budgeting exports a wider, looser CSV or Excel-derived flat file whose column order tracks the operator’s on-screen layout and therefore drifts between productions. Newer EP SmartAccounting endpoints emit RESTful JSON. This parsing layer sits directly downstream of transport and directly upstream of the ledger: the idempotent fetching, retry, and back-pressure semantics it depends on are specified in CSV & API Sync Pipelines, it runs its per-row validation under the concurrency model of Async Batch Processing, and the boundary contracts it enforces are the same ones drawn in Schema Validation & Error Handling. All three are subsystems of the broader Cost Ingestion & Data Parsing Workflows reference architecture that every parsed record must ultimately satisfy.

# Architecture: From Raw Export to Normalized Ledger Feed

The core design decision is that parsing is a series of explicit gates, not a single permissive read. The file is never trusted on arrival: it is stamped, hashed, read field-by-field as raw strings, and only then coerced under a strict schema. Anything that fails a gate is diverted — never dropped, never guessed — so a malformed export can never halt a clean batch or silently corrupt the daily cost report.

The path has four stages. First, transport intake and lineage: the inbound file is stamped with a batch identifier, a timezone-aware source timestamp, and a SHA-256 checksum of its bytes, so its provenance is fixed before a single field is read. Second, raw read: every column is pulled as a string with coercion disabled, which keeps type conversion inside the schema where it can be audited rather than hidden inside a CSV reader’s guesswork. Third, validation and normalization: each row is run through a Pydantic contract that coerces the amount through Decimal, checks the cost code against the EP/Showbiz account-code pattern, normalizes the post date to a production-anchored timezone, and — for international units — pins an FX rate at the transaction timestamp. Fourth, the split: valid records flow to the ledger feed; failures flow to a quarantine queue carrying their row index, error detail, raw payload, and a SHA-256 fingerprint. The lineage stamp and every quarantine entry are written to an append-only audit store that a bond auditor can replay end to end.

The diagram below traces a raw EP/Showbiz export from transport intake through hashing, validation, and the split between ledger-bound records and the quarantine queue.

# Core Implementation: A Deterministic EP/Showbiz Parser

The implementation below composes three responsibilities that stay strictly separated: the schema that defines a valid transaction, the byte-level hasher that fixes lineage, and the batch parser that reads raw strings, validates each row through model_validate, and routes failures to quarantine without ever mutating the original payload. Read it as three steps.

Step 1 — the boundary schema. TransactionRecord is the single contract every row must satisfy before the ledger will accept it. Monetary precision is protected by typing amount as Decimal and coercing through a string in a field_validator, so thousands separators are stripped and binary-float rounding never enters the pipeline. A second field_validator enforces the EP/Showbiz account-code pattern; the department-range check is left to the mapping matrix downstream.

Step 2 — lineage. compute_file_hash reads the file in fixed chunks and returns a SHA-256 digest of its exact bytes. That digest is stamped on the batch before any field is parsed, so the record of what arrived is fixed independently of what parsed.

Step 3 — parse and route. process_export_batch reads every column as a string, attempts TransactionRecord.model_validate on each row, and on failure preserves the original row verbatim alongside its ValidationError detail, its row index, and a per-row fingerprint. The valid records are returned for the ledger feed; the quarantine list is surfaced for reconciliation.

import hashlib
import logging
from datetime import datetime
from decimal import Decimal, InvalidOperation
from pathlib import Path
from re import fullmatch
from zoneinfo import ZoneInfo

import pandas as pd
from pydantic import BaseModel, ValidationError, field_validator

logger = logging.getLogger("production_accounting.ingest")

# Cost data is reconciled against a studio's operating day, not a bare UTC offset,
# so every temporal value is anchored to an IANA zone via zoneinfo.
PRODUCTION_TZ = ZoneInfo("America/Los_Angeles")

# EP/Showbiz account codes follow a four-segment decimal hierarchy:
# XXXX.YY.ZZ.WW (e.g. 2050.03.01.07 for an Art Department sub-account).
COST_CODE_PATTERN = r"\d{4}\.\d{2}\.\d{2}\.\d{2}"


class TransactionRecord(BaseModel):
    transaction_id: str
    cost_code: str
    department: str
    amount: Decimal  # Decimal preserves exact monetary precision; never use float for money
    currency: str
    post_date: datetime
    vendor_id: str | None = None

    @field_validator("amount", mode="before")
    @classmethod
    def coerce_amount(cls, v: object) -> Decimal:
        # Strip thousands separators and coerce through str; never route money through float.
        try:
            return Decimal(str(v).strip().replace(",", ""))
        except (InvalidOperation, ValueError) as exc:
            raise ValueError(f"Invalid monetary amount: {v!r}") from exc

    @field_validator("cost_code")
    @classmethod
    def validate_cost_code(cls, v: str) -> str:
        code = v.strip()
        if not fullmatch(COST_CODE_PATTERN, code):
            raise ValueError(
                f"EP/Showbiz cost code must match XXXX.YY.ZZ.WW, got: {v!r}"
            )
        return code

    @field_validator("post_date")
    @classmethod
    def anchor_timezone(cls, v: datetime) -> datetime:
        # A naive post date silently drifts across a daylight-saving boundary;
        # anchor it to the production zone so the ledger books it on the right day.
        return v.replace(tzinfo=PRODUCTION_TZ) if v.tzinfo is None else v


def compute_file_hash(file_path: Path) -> str:
    sha256 = hashlib.sha256()
    with open(file_path, "rb") as f:
        for chunk in iter(lambda: f.read(8192), b""):
            sha256.update(chunk)
    return sha256.hexdigest()


def _fingerprint(row: dict) -> str:
    import json
    canonical = json.dumps(row, sort_keys=True, default=str, separators=(",", ":"))
    return hashlib.sha256(canonical.encode("utf-8")).hexdigest()


def process_export_batch(
    file_path: Path, batch_id: str
) -> tuple[list[TransactionRecord], list[dict]]:
    file_hash = compute_file_hash(file_path)
    logger.info("Batch %s ingested. SHA-256: %s", batch_id, file_hash)

    # Read every field as a string so coercion stays explicit and auditable.
    df = pd.read_csv(file_path, dtype=str, on_bad_lines="warn")
    valid_records: list[TransactionRecord] = []
    quarantine_queue: list[dict] = []

    for idx, row in df.iterrows():
        raw = row.to_dict()
        try:
            record = TransactionRecord.model_validate(
                {
                    "transaction_id": raw.get("txn_id", ""),
                    "cost_code": raw.get("acct_code", ""),
                    "department": raw.get("dept", ""),
                    "amount": raw.get("amount", "0"),
                    "currency": raw.get("currency", "USD"),
                    "post_date": datetime.strptime(raw.get("post_date", ""), "%Y-%m-%d"),
                    "vendor_id": raw.get("vendor_id"),
                }
            )
            valid_records.append(record)
        except (ValidationError, ValueError, TypeError) as exc:
            quarantine_queue.append(
                {
                    "batch_id": batch_id,
                    "file_hash": file_hash,
                    "row_index": int(idx),
                    "error": str(exc),
                    "raw_data": raw,
                    "hash": _fingerprint(raw),
                    "quarantined_at": datetime.now(PRODUCTION_TZ).isoformat(),
                }
            )

    if quarantine_queue:
        logger.warning("Batch %s: %s rows quarantined.", batch_id, len(quarantine_queue))

    return valid_records, quarantine_queue

This guarantees that every transaction passes a strict validation gate before it can enter the ledger. Reading the file as raw strings keeps coercion visible; typing amount as Decimal keeps money exact; anchoring post_date to an IANA zone keeps a transaction booked on the correct production day even across a daylight-saving boundary. When a shoot spans multiple locations, each unit’s export carries its own production zone and the ledger normalizes on read — never by mutating the ingested value.

# Mapping cost codes to a canonical account schema

The four-segment code XXXX.YY.ZZ.WW is only half the contract: the first four digits identify a primary department (for example 1100 for Story, 2050 for Art Department), and a production’s own GL may number those departments differently from the EP or Showbiz template it received. Pattern-matching the code proves it is well-formed; proving it is correct means resolving it against an approved account matrix. That resolution — including how to hold the mapping immutable across a multi-unit shoot — is the subject of Cost Code Standardization, and the concrete migration from EP/Showbiz account codes into a custom database is walked through step by step in How to Map EP/Showbiz Sync Cost Codes to Custom Databases. Whether a resolved code books above or below the line — which drives how it rolls up in the guarantor’s report — is governed by Above/Below-the-Line Mapping.

# Multi-currency normalization

International shoots generate transactions in the local currency of every location, and those cannot post to a base-currency GL untouched. The parser pins a daily reference exchange rate at the transaction’s own timestamp, applies it as a Decimal operation, and records both the original and the converted amount — a dual-currency ledger line that preserves a transparent FX variance trail for the unit production manager and keeps converted totals reconcilable against the source document during audit. Pinning the rate at the transaction timestamp rather than at ingestion time is what makes a re-run reproducible; the per-transaction rate-pinning and dual-write mechanics are detailed in Async Batch Processing for Multi-Currency Shoots.

# Guild and Contract Specifics: What the Codes Have to Respect

A parsed transaction is not merely well-typed — if it is payroll-derived, it must be compliant before it reaches the ledger, because the cost code it carries encodes a collective bargaining agreement (CBA) obligation. Three agreements dominate what an EP or Showbiz payroll export must respect. The International Alliance of Theatrical Stage Employees (IATSE) governs crew overtime, meal-penalty triggers, and turnaround, and its accounts carry the fringe rates — pension, health, and vacation/holiday — layered on top of gross. The Screen Actors Guild–American Federation of Television and Radio Artists (SAG-AFTRA) governs performer scale, overtime, and the pension-and-health (P&H) contribution basis. The Directors Guild of America (DGA) governs directorial-unit turnaround windows and overtime thresholds.

In practice this means the parser does not stop at pattern-matching a cost code; a payroll-derived code that maps to a union account is validated against a rate table keyed by union category, contract year, and jurisdiction, in the same pass that runs schema validation. A rate-table entry typically carries a base scale rate, an overtime multiplier (commonly 1.5x past an eight- or ten-hour threshold and 2x beyond a second threshold), a meal-penalty increment that accrues in fixed steps once a break is missed, and a fringe percentage. Because every one of those multipliers applies to money, every one is a Decimal operation — a fringe computed in floating point disagrees with the guild’s own figure by cents that compound into an audit finding. The turnaround and overtime thresholds a directorial payroll line must satisfy are specified in DGA Overtime & Turnaround Rules, and the fringe math that rides on every validated gross belongs to Pension & Health Fund Calculations.

When a payroll row references a union category, contract year, or jurisdiction that the loaded rate table does not contain, the parser must not guess and must not stall. It routes that record through the deterministic secondary-and-cached path specified in Compliance Fallback Chains, so a missing rate table becomes an auditable exception rather than a halted close. Encoding these thresholds directly into the validation step means a non-compliant payroll run is quarantined before it can reach the ledger — the whole subject of Guild Compliance & Rule Validation Automation — instead of being discovered weeks later during a guild audit.

# Error Handling and Quarantine: The Exception Queue

The quarantine queue is what lets the parser guarantee zero data loss while still refusing to admit a bad row. Its contract is strict: a rejected row is never dropped, never silently repaired inline, and never mutated. It is preserved exactly as read, stamped with a timezone-aware quarantined_at, annotated with the machine-readable error that explains why it failed and the row index that locates it in the source file, and fingerprinted with a SHA-256 hash of its canonical serialization. That fingerprint — carried alongside the whole-file SHA-256 stamped at intake — is the linchpin of the audit story: it lets even a malformed row be matched back to the original EP or Showbiz submission without manual reconstruction, and it makes re-ingestion idempotent. A corrected row that reproduces the same canonical bytes reproduces the same hash, so a reconciliation tool can prove whether a fix was genuinely applied or a duplicate slipped in.

Two failure classes are handled differently on purpose. Row-level failures — a malformed cost code, an un-parseable amount, a missing department tag, a date that does not match the export’s format — are validation outcomes and go to the queue with full context. File-level failures — a truncated download, a torn SFTP transfer, a checksum mismatch against the sender’s manifest — are transport outcomes; they are caught upstream at the boundary described in CSV & API Sync Pipelines and the whole file is rejected before a single row is parsed, so a partial file can never post half a day’s costs. A production accountant reviews the queue export in real time, corrects the offending codes or supplies a missing rate table, and re-ingests only the affected rows without restarting the batch. This quarantine discipline is the same boundary contract that Schema Validation & Error Handling enforces at the point of entry; EP/Showbiz parsing simply applies it against the specific defects these two platforms are known to emit.

# Verification: Confirming the Ledger Feed and Audit Output

A parsing run is only trustworthy if its output can be checked without re-reading the source file. Verification rests on three artifacts. First, the ledger feed: every accepted TransactionRecord should appear once, with its Decimal amount preserved to full precision, its cost code resolved to a canonical account, its currency and — for international units — its converted amount recorded, and its timezone-aware post date booked against the correct production day. A reconciliation over the run should show that the count of ledger records plus the count of quarantine entries equals the count of rows read from the export — no row is ever both admitted and quarantined, and none simply vanishes.

Second, the audit log fields: each admitted and each quarantined row must carry, at minimum, the batch identifier, the whole-file SHA-256 stamped at intake, its own canonical-payload fingerprint, its source row index, and a timezone-aware timestamp — written to append-only storage before the corresponding ledger transaction commits, so a crash mid-batch leaves a replayable record of intent rather than a gap. Because completion-bond lenders require explicit reconciliation of every dollar, those two hashes are what let a guarantor’s auditor tie any ledger line — or any rejected line — back to the exact export and the exact row that produced it.

Third, the reconciliation report shape: a per-run summary listing total rows read, total admitted, total quarantined grouped by error reason code, and the source file’s SHA-256 and manifest status. When those figures agree with the guarantor’s expected daily cost movement, the run is provably complete. The strict-typing and custom-validator behavior the schema relies on is documented in the Pydantic documentation, and the byte-level hashing that underpins lineage is specified in the standard-library hashlib documentation. Verified this way, EP/Showbiz parsing turns the nightly close from a manual cleanup chore into a deterministic compliance engine: every departmental purchase order, vendor invoice, and FX-adjusted payroll line is processed with speed, precision, and a traceable path back to its origin. The full end-to-end walkthrough of eliminating manual cleanup on these exports lives in Parsing EP/Showbiz Sync Exports Without Manual Cleanup.

# Related Guides

• Parsing EP/Showbiz Sync Exports Without Manual Cleanup — the step-by-step walkthrough that turns a defect-ridden export into a clean ledger feed with no hand editing.
• CSV & API Sync Pipelines — the idempotent transport, retry, and checksum layer that delivers the exports this parser consumes.
• Schema Validation & Error Handling — the Pydantic boundary contracts and quarantine semantics this page applies to EP/Showbiz defects.
• How to Map EP/Showbiz Sync Cost Codes to Custom Databases — resolving a well-formed account code to a correct, approved GL account.
• Compliance Fallback Chains — deterministic secondary and cached routing when a guild rate table is missing at parse time.

Up one level: Cost Ingestion & Data Parsing Workflows.