# Building Fallback Chains for Missing Guild Rate Tables in Python

A missing guild rate table is a narrower failure than a down endpoint, and it needs a narrower answer. The primary feed responds cleanly, but it has no row for the guild, jurisdiction, contract tier, and work date you asked for — a freshly ratified amendment the vendor has not published yet, a new filming zone added mid-schedule, or a crew classification that never existed in the table. A KeyError here is not a bug to swallow; it is the exact moment a payroll batch either stalls or invents an indefensible number. This page builds the missing-rate-table case that the Compliance Fallback Chains architecture frames: how to synthesize a replacement rate from cached and rulebook sources, attach a confidence score, and write a provenance record precise enough that a completion guarantor can reconstruct exactly how the number was derived when no authoritative row existed.

# Prerequisites and Context

This page extends the Compliance Fallback Chains reference, which specifies the tiered state machine as a whole; here we focus solely on the tier logic that fires when a lookup returns no matching row. The implementation targets Python 3.11+ for zoneinfo in the standard library. It leans on Pydantic v2 for boundary validation via model_validate and field_validator, and on the standard-library decimal, hashlib, sqlite3, and zoneinfo modules for currency-safe arithmetic, deterministic audit hashing, a versioned local cache, and timezone-aware timestamps. Monetary values are always Decimal, never float — a fractional-cent drift compounded across thousands of Pension & Health (P&H) contribution lines is precisely the variance a guarantor will make you explain.

The contract clause that governs everything below is effective_from: a collective bargaining agreement (CBA) is a succession of dated amendments, so a rate is valid only if the agreement version in force brackets the date the work was performed. A replacement table assembled from a stale season is not a fallback — it is a repricing error waiting to be flagged. The chain assumes its inputs are already normalized upstream: heterogeneous timecards are cleaned by the Cost Ingestion & Data Parsing Workflows subsystem, and each hour is keyed to a single fund code through Cost Code Standardization before it ever reaches this resolver.

# The Resolution Hierarchy for a Missing Row

When the authoritative lookup returns nothing, the resolver must not guess and must not retry blindly against a source that has no data to give. It advances through four tiers in strict order — live API, versioned local cache, rulebook-grounded interpolation, and a hardcoded contract floor — writing an append-only audit entry at each tier before either auto-posting or quarantining the record based on its confidence score. A circuit breaker guards Tier 1 so that a genuinely unavailable source is skipped rather than hammered, but a present-yet-incomplete source still routes forward on the missing key.

The cascade below shows each tier activating only when the prior tier cannot supply a valid row, logging the transition before advancing, and gating the final rate on a confidence threshold.

Two properties make this defensible rather than merely functional. Failure is classified — a missing row routes differently from a network timeout, and both are distinguishable in the log from a stale-version rejection. And every tier is idempotent: re-running the resolver against the same inputs yields the same rate and the same payload hash, which is what lets a production accountant replay a disputed week without duplicating a single contribution.

# Step-by-Step: Assembling a Replacement Rate Table

The resolver models each candidate rate as a Pydantic v2 object validated at the boundary, resolves through the tiers as an explicit sequence, and writes a hashed provenance entry at every hop. Interpolation is deliberately conservative: it over-accrues toward the nearest valid historical baseline and never emits a value below the ratified minimum, because an over-accrual is reconcilable whereas a short P&H remittance surfaces as a delinquency.

import hashlib
import json
import logging
import sqlite3
import time
from datetime import date, datetime
from decimal import Decimal
from enum import Enum
from typing import Any
from zoneinfo import ZoneInfo

from pydantic import BaseModel, ConfigDict, Field, field_validator

logging.basicConfig(
    level=logging.INFO,
    format="%(message)s",
    handlers=[logging.FileHandler("rate_fallback_audit.jsonl", mode="a")],
)
logger = logging.getLogger("missing_rate_fallback")

# Audit instants are stamped in UTC; render against an IANA zone only for
# human-facing reports — never with a fixed offset.
AUDIT_TZ = ZoneInfo("America/Los_Angeles")
MIN_CONFIDENCE = Decimal("0.75")


class FallbackTier(str, Enum):
    LIVE_API = "live_api"
    VERSIONED_CACHE = "versioned_cache"
    RULEBOOK_INTERPOLATION = "rulebook_interpolation"
    CONTRACT_FLOOR = "contract_floor"


class RateResolution(BaseModel):
    """A resolved rate plus the provenance that makes it auditable."""
    model_config = ConfigDict(frozen=True)

    crew_id: str
    jurisdiction: str
    work_date: date
    derived_rate: Decimal = Field(gt=Decimal("0"))
    tier: FallbackTier
    confidence: Decimal
    contract_version: str
    resolved_at: str

    @field_validator("derived_rate", "confidence", mode="before")
    @classmethod
    def reject_float(cls, v: Any) -> Decimal:
        if isinstance(v, float):
            raise ValueError("monetary/score fields must be str or Decimal, not float")
        return Decimal(str(v))


class MissingRateFallbackChain:
    def __init__(self, db_path: str, min_confidence: Decimal = MIN_CONFIDENCE):
        self.db_path = db_path
        self.min_confidence = min_confidence
        self.circuit = {"failures": 0, "open": False}
        self._init_cache()

    def _init_cache(self) -> None:
        with sqlite3.connect(self.db_path) as conn:
            conn.execute("PRAGMA foreign_keys=ON")
            conn.execute(
                """
                CREATE TABLE IF NOT EXISTS rate_cache (
                    jurisdiction     TEXT NOT NULL,
                    contract_version TEXT NOT NULL,
                    rate             TEXT NOT NULL,
                    effective_from   TEXT NOT NULL,
                    effective_to     TEXT NOT NULL,
                    PRIMARY KEY (jurisdiction, contract_version)
                )
                """
            )

    def resolve(self, crew_id: str, jurisdiction: str, work_date: date) -> RateResolution:
        # Tier 1 — Live API, guarded by the circuit breaker.
        if not self.circuit["open"]:
            try:
                rate = self._fetch_live(jurisdiction, work_date)  # raises KeyError on missing row
                self.circuit["failures"] = 0
                return self._log(crew_id, jurisdiction, work_date, rate,
                                 FallbackTier.LIVE_API, "1.0", "current")
            except KeyError:
                logger.info(json.dumps({"event": "missing_row", "jurisdiction": jurisdiction}))
            except Exception as exc:
                self.circuit["failures"] += 1
                if self.circuit["failures"] >= 3:
                    self.circuit["open"] = True
                logger.info(json.dumps({"event": "api_failure", "error": str(exc)}))

        # Tier 2 — Versioned cache, valid only if its dates bracket the work date.
        cached = self._cache_lookup(jurisdiction, work_date)
        if cached is not None:
            rate, version = cached
            return self._log(crew_id, jurisdiction, work_date, rate,
                             FallbackTier.VERSIONED_CACHE, "0.9", version)

        # Tier 3 — Rulebook interpolation toward the nearest valid baseline.
        interpolated = self._interpolate(jurisdiction)
        if interpolated is not None:
            return self._log(crew_id, jurisdiction, work_date, interpolated,
                             FallbackTier.RULEBOOK_INTERPOLATION, "0.78", "historical_baseline")

        # Tier 4 — Contract floor; the chain always resolves, always flags.
        floor = self._contract_floor(jurisdiction)
        return self._log(crew_id, jurisdiction, work_date, floor,
                         FallbackTier.CONTRACT_FLOOR, "0.50", "ratified_floor")

    def _log(self, crew_id: str, jurisdiction: str, work_date: date, rate: Decimal,
             tier: FallbackTier, confidence: str, version: str) -> RateResolution:
        res = RateResolution(
            crew_id=crew_id, jurisdiction=jurisdiction, work_date=work_date,
            derived_rate=rate, tier=tier, confidence=confidence,
            contract_version=version,
            resolved_at=datetime.now(tz=ZoneInfo("UTC")).isoformat(),
        )
        # Canonical serialization → a stable SHA-256 fingerprint of the decision.
        canonical = json.dumps(res.model_dump(mode="json"), sort_keys=True,
                               separators=(",", ":")).encode("utf-8")
        entry = {
            "payload_hash": hashlib.sha256(canonical).hexdigest(),
            "audit_flag": res.confidence < self.min_confidence,
            **res.model_dump(mode="json"),
        }
        logger.info(json.dumps(entry, sort_keys=True))
        return res

    def _fetch_live(self, jurisdiction: str, work_date: date) -> Decimal:
        raise KeyError(jurisdiction)  # simulate a present feed with no matching row

    def _cache_lookup(self, jurisdiction: str, work_date: date) -> tuple[Decimal, str] | None:
        with sqlite3.connect(self.db_path) as conn:
            row = conn.execute(
                """SELECT rate, contract_version FROM rate_cache
                   WHERE jurisdiction = ? AND ? BETWEEN effective_from AND effective_to
                   ORDER BY effective_from DESC LIMIT 1""",
                (jurisdiction, work_date.isoformat()),
            ).fetchone()
        return (Decimal(row[0]), row[1]) if row else None

    def _interpolate(self, jurisdiction: str) -> Decimal | None:
        # Nearest valid baseline per CBA; never below the ratified minimum.
        baseline = {"Zone_A": Decimal("450.00"), "Zone_B": Decimal("435.00")}
        return baseline.get(jurisdiction)

    def _contract_floor(self, jurisdiction: str) -> Decimal:
        return {"Zone_A": Decimal("400.00"), "Zone_B": Decimal("385.00")}.get(
            jurisdiction, Decimal("400.00"))

The frozen=True config makes every resolution immutable once validated, so nothing downstream can mutate a rate after it has been hashed into the log. Because serialization is canonical — sorted keys, no incidental whitespace — the hash is a stable fingerprint: two runs that resolve the same inputs produce the same hash, and any tampering with a cached snapshot changes it. That fingerprint is the spine of the whole audit story.

# Audit Trail Requirements

Bond lenders and union auditors require complete traceability for a synthesized rate — more so than for a live one, because no authoritative source stands behind it. Every resolution must emit exactly one append-only JSON Lines record capturing, at minimum:

• resolved_at — UTC instant of the routing decision
• crew_id and jurisdiction
• work_date — the date whose CBA amendment governs the rate
• tier — which source supplied the number (live_api … contract_floor)
• derived_rate — serialized as a string to preserve Decimal precision
• confidence and audit_flag — the score and whether it fell below the 0.75 threshold
• contract_version — the ratified agreement or historical_baseline / ratified_floor
• payload_hash — the SHA-256 of the canonical payload

Write this log to write-once storage: an append-only file on a WORM-backed volume, or an object store with an object-lock retention policy, so the compliance archive cannot be silently rewritten. Disable log rotation on the archive stream — a rotated-away line is an audit gap. Production accountants replay these records to reconstruct a batch, verify P&H contributions, and prove no crew member was paid below a union minimum even on a day when the rate table had no row for them.

# Gotchas and Production Edge Cases

Contract-ratification drift is the whole reason this page exists. The missing row is usually not corruption — it is a newly ratified amendment the vendor has not published. Do not let a Tier 2 cache paper over it silently: a cached snapshot whose effective_to predates the work date must fail the date-bracket check and route to interpolation, and the resulting audit_flag is the signal that a human should confirm the new agreement before the weekly run.

Multi-location shoots split one crew member across jurisdictions in a single week. Resolve per work date and per jurisdiction, never per person — a grip who works Zone A on Monday and Zone B on Thursday needs two independent resolutions, and collapsing them to one rate is a reconciliation failure waiting to surface in a cost report.

DST boundaries corrupt the work_date key if you derive it from a naive timestamp. Always attach the shoot location’s IANA zone at ingestion and take the calendar date in that zone; a wrap logged at 2026-11-01T00:30 in America/Los_Angeles belongs to the prior work date on a fall-back night, and mis-dating it can select the wrong CBA amendment entirely.

Idempotency protects the P&H funds. Because the resolver is deterministic and every entry is keyed by its payload hash, re-running a batch after a partial failure must not create a second contribution. Deduplicate downstream on (crew_id, work_date, jurisdiction, payload_hash) before posting, so a retried run is a no-op rather than a double remittance.

The circuit breaker must distinguish “down” from “incomplete.” A 5xx or timeout increments the failure counter and can open the circuit; a clean response missing one row must not, or a single new jurisdiction would trip the breaker and needlessly downgrade every other lookup to cache. The reference resolver above catches KeyError separately from every other exception for exactly this reason. The penalty and premium math that a resolved base rate later feeds — for example the turnaround premiums in DGA Overtime & Turnaround Rules — assumes a defensible base; the fallback’s only job is to guarantee that base while the authoritative row is pending.

# Related

• Compliance Fallback Chains — the parent architecture whose tiered state machine this page implements for the missing-row case.
• Guild Compliance & Rule Validation Automation — the reference architecture that routes normalized timecards through every compliance engine.
• SAG-AFTRA Residuals Logic — where conservative provisional accruals are later reconciled against verified exhibition data.
• Pension & Health Fund Calculations — why the terminal tier resolves to an over-accruing floor rather than a null contribution.
• Schema Validation & Error Handling — the boundary and quarantine discipline a rejected replacement table reuses.

Up: Compliance Fallback Chains