Sourcing and Pinning Daily FX Rates for Reproducible Conversion

Currency conversion is only as reproducible as the rate table behind it, and a rate table fetched live at report time is not reproducible at all: the same cost line converts to a different figure every time the market moves. The edge case this page solves is the one that makes a cross-border cost report defensible — turning a volatile, mutable rate feed into an immutable daily snapshot, hashed so it cannot drift and keyed so a cost incurred in April resolves against April’s rate no matter when the report runs. The work is mostly discipline: fetch once, validate hard, freeze the result, hash it, and resolve every conversion by the work date of the cost rather than the wall clock of the run. Do that and a completion guarantor (the completion-bond company underwriting delivery) can reconstruct a conversion done months ago to the cent; skip it and the report becomes an artifact nobody can reproduce, which a lender treats as a reporting-integrity failure regardless of the underlying numbers.

Prerequisites and Context

This page extends the subsystem documented in Currency & FX Normalization; it owns the sourcing and pinning half of the problem, upstream of the arithmetic worked through in Normalizing Multi-Currency Cost Reports with Decimal. It targets Python 3.11+ for the standard-library zoneinfo module, and uses the same deliberate dependency set as the rest of Cost Ingestion & Data Parsing Workflows: the decimal module for every rate, zoneinfo for mapping a cost instant to the correct rate date (never a bare UTC offset), hashlib for the snapshot fingerprint, and Pydantic v2 (model_validate, field_validator) for validating each incoming rate row. The rate feed itself — a central-bank reference series, a commercial data vendor, or a bank’s published daily fixing — is out of scope here except as a source of raw rows; what matters is that whatever arrives is validated and frozen before any converter is allowed to touch it. This freezing discipline is the same append-only, hash-verified contract that Schema Validation & Error Handling applies to cost rows, applied instead to the reference data those rows are priced against.

Step-by-Step Implementation

Pinning has four steps: validate each raw rate row at the boundary, assemble them into an immutable snapshot for one publication date, compute a canonical SHA-256 over the snapshot so any later change is detectable, and expose a resolver that maps a cost’s work instant to the correct rate date. The snapshot is frozen — once built, it is never mutated in place; a corrected feed produces a new snapshot with a new hash, never an edit of the old one.

from __future__ import annotations

import hashlib
import json
from datetime import date, datetime
from decimal import Decimal
from typing import Any
from zoneinfo import ZoneInfo

from pydantic import BaseModel, ConfigDict, Field, field_validator


class RateRow(BaseModel):
    """One validated rate: reporting-currency units per 1 unit of quote."""

    model_config = ConfigDict(frozen=True)

    quote_currency: str           # ISO 4217 foreign currency, e.g. "GBP"
    rate: Decimal = Field(gt=Decimal("0"))
    rate_date: date               # the publication date this rate is effective for

    @field_validator("rate", mode="before")
    @classmethod
    def reject_float(cls, v: Any) -> Decimal:
        if isinstance(v, float):
            raise ValueError("rate must be str/Decimal, never float")
        return Decimal(str(v))

    @field_validator("quote_currency")
    @classmethod
    def normalize_iso(cls, v: str) -> str:
        if len(v) != 3 or not v.isalpha():
            raise ValueError("quote_currency must be a 3-letter ISO 4217 code")
        return v.upper()


class PinnedSnapshot(BaseModel):
    """An immutable daily rate table, hashed for tamper-evidence."""

    model_config = ConfigDict(frozen=True)

    reporting_currency: str
    rate_date: date
    source_id: str
    rows: tuple[RateRow, ...]
    snapshot_hash: str
    pinned_at: str


def build_snapshot(reporting_currency: str, rate_date: date, source_id: str,
                   raw_rows: list[dict]) -> PinnedSnapshot:
    reporting = reporting_currency.upper()
    validated = [RateRow.model_validate(r) for r in raw_rows]

    # Reject duplicates and off-date rows before freezing the table.
    seen: set[str] = set()
    for row in validated:
        if row.rate_date != rate_date:
            raise ValueError(f"{row.quote_currency} row dated {row.rate_date} "
                             f"does not match snapshot date {rate_date}")
        if row.quote_currency in seen:
            raise ValueError(f"duplicate rate for {row.quote_currency}")
        seen.add(row.quote_currency)

    ordered = tuple(sorted(validated, key=lambda r: r.quote_currency))
    # Canonical hash over currency+rate pairs; order-independent by construction.
    material = json.dumps(
        {"reporting": reporting, "date": rate_date.isoformat(),
         "source": source_id,
         "rates": {r.quote_currency: str(r.rate) for r in ordered}},
        sort_keys=True, separators=(",", ":"),
    ).encode("utf-8")
    snapshot_hash = hashlib.sha256(material).hexdigest()

    return PinnedSnapshot(
        reporting_currency=reporting, rate_date=rate_date, source_id=source_id,
        rows=ordered, snapshot_hash=snapshot_hash,
        pinned_at=datetime.now(tz=ZoneInfo("UTC")).isoformat(),
    )


def resolve_rate_date(cost_instant: datetime, shoot_tz: str) -> date:
    # A cost's rate date is the calendar date in the shoot's own zone, not UTC.
    zone = ZoneInfo(shoot_tz)
    return cost_instant.astimezone(zone).date()

Two design choices carry the guarantee. The snapshot is a frozen Pydantic model whose rows are a tuple, so it is immutable at the type level — nothing downstream can append or overwrite a rate after the hash is computed. And the hash is taken over a canonical projection of the currency-to-rate mapping with sorted keys, so it depends on the content of the table and not on the order rows happened to arrive; two feeds that publish the same rates in a different order pin to the same hash, and a single altered rate changes it. The resolve_rate_date helper is where the timezone discipline lives: it converts the cost instant into the shoot’s own zone before taking the calendar date, so a late-night purchase does not get priced against the wrong day.

The lifecycle is a straight line from a mutable feed to a frozen, addressable snapshot, and then a lookup that a converter resolves against by work date.

Fetch, validate, pin, and resolve a daily FX rate snapshot Raw rate rows are fetched from a rate feed, validated at the boundary so each carries a positive Decimal rate and an ISO 4217 currency, then assembled into an immutable daily snapshot for one publication date. A canonical SHA-256 hash is computed over the snapshot's currency-to-rate mapping, making the frozen table tamper-evident and addressable. A converter resolves a rate by mapping a cost's work instant, through its shoot timezone, to a calendar rate date and looking that date up in the pinned snapshot; a work date with no pinned row returns nothing rather than a guessed rate. Fetch raw rate rows rate feed · mutable Validate each row Decimal rate · ISO 4217 Assemble daily snapshot frozen · one publication date Pin + SHA-256 hash immutable · tamper-evident Cost work instant → shoot-tz calendar date Resolve by (currency, rate date) hit → pinned rate · miss → no guess

Because build_snapshot validates, de-duplicates, and freezes before returning, and because the resolver reads only from the frozen table, the whole path is deterministic: the same feed pinned twice produces the same hash, and every conversion done against a given snapshot is reproducible for as long as that snapshot is retained. Retaining it is the point — the pinned snapshot is archived write-once alongside the cost ledger, so a report is always reproducible against the exact rates it was built on rather than against whatever the feed says today.

Audit Trail Requirements

A pinned snapshot is itself an audit artifact, and it must be persisted to write-once storage the moment it is built, before any conversion references it. At minimum the retained record must carry: the reporting_currency; the rate_date; the source_id identifying which feed and fixing it came from; the full set of validated rows; the snapshot_hash; and the UTC pinned_at timestamp. Storing the snapshot immutably is what lets every downstream normalized cost line carry only the lightweight snapshot_hash and still be fully reconstructable — the converter records which table it used, and the table itself is fetched by hash from the archive during a review. A corrected rate feed never edits a pinned snapshot; it produces a new one with a new hash and a new pinned_at, and any conversion that needs the correction is re-run against the new snapshot as a distinct, clearly flagged entry. The snapshots feed the same reproducibility guarantee the Completion Bond Reporting & Guarantor Analytics layer depends on: a guarantor testing a cost report against budget can pull the exact rate table each foreign line was priced against and confirm the arithmetic independently.

Gotchas and Production Edge Cases

Timezone of the rate date. A rate table is dated by a publication day, and a cost has to be matched to the right one — but “which day” depends on where the money was spent, not where the server runs. A purchase made at 11 p.m. in Europe/London is a different calendar day in America/Los_Angeles, and taking the rate date from the server’s UTC clock silently prices some evening costs against the next day’s fixing. Resolve the rate date by converting the cost instant through the shoot’s own IANA zone with zoneinfo, as resolve_rate_date does, and store the shoot zone on the cost record rather than inferring it from the host.

Missing days, weekends, and holidays. Most reference feeds do not publish on weekends or market holidays, so a Saturday location cost has no same-day fixing. This is a policy decision that must be explicit and documented, never an accident of a missing key: either carry forward the last published rate (a common convention, but one that must be recorded so the report states which prior date was used) or hold the line for manual resolution. What is never acceptable is silently substituting a nearby currency or defaulting to zero. Because the resolver returns nothing on a miss rather than guessing, the missing-day policy is applied in one visible place instead of being scattered through the converter.

Idempotency and re-pinning. Fetching the same feed twice must not produce two different snapshots. Because the hash is content-addressed over the canonical currency-to-rate mapping, re-pinning an identical feed yields an identical hash, so the archive can deduplicate on (source_id, rate_date, snapshot_hash) and a retried fetch is a no-op rather than a second, competing table. If the hash differs, the feed genuinely changed — a correction or a restatement — and that is a new snapshot to be reviewed, not an overwrite of the one already in use.

Float and precision drift in the feed. Rate feeds are frequently delivered as JSON numbers, which many parsers hand back as float. Parsing 1.2345 as a float and then building a Decimal from it imports binary rounding error into the reference data itself, contaminating every conversion downstream. Read rate strings before they are coerced to float, and reject a float rate at the boundary, so the pinned table holds the exact published figure rather than a binary approximation of it.

Precision of the stored rate. Reference rates are often published to more places than a converted cent needs, and truncating them at pin time quietly changes conversions. Pin the rate at the full published precision and let the conversion quantize the result to cents, rather than rounding the rate on the way in — the rounding belongs at the payable figure, in the converter, not in the reference table.

Up one level: Currency & FX Normalization, part of Cost Ingestion & Data Parsing Workflows.