Contract Versioning & Effective Dating

A freight audit engine is only as trustworthy as its answer to one temporal question: which tariff was in force on the day this shipment moved? Every rate lookup in the pipeline reduces to that question, and answering it correctly requires a temporal data model — not a single “current” rate row that the last amendment overwrote. This part of Freight Contract Architecture & Rate Mapping covers the version records, effective windows, amendment lineage, and point-in-time resolution that turn a carrier’s tangle of base agreements, general rate increases, and lane-specific riders into a deterministic function of (carrier, lane, ship_date).

The scope here is deliberately narrow and load-bearing. It sits between the rate-extraction modules that digitize tariffs and the validation tier that prices shipments. When an invoice arrives back-dated by six weeks, or a reconciliation replays last quarter’s freight after a mid-cycle renegotiation, the resolver defined below must return the version that was actually effective at tender — never the newest one on file. Get the temporal model right and back-dated invoices, historical replays, and audit re-runs all price identically every time; get it wrong and the error is silent, because a wrong-but-plausible rate raises no exception.

Prerequisites

This layer assumes the rate matrices themselves already exist and only concerns itself with when each one applies. Before wiring the resolver in, confirm the upstream contracts and dependencies are in place.

Two config keys govern behaviour: contract.window_semantics, which must be half_open (start inclusive, end exclusive) everywhere, and contract.tz, the single timezone in which every effective boundary is interpreted. Both are set once and never varied per carrier — mixing window semantics is the root of most resolution defects.

Architecture: a version timeline resolved by ship date

A contract is not one row; it is an ordered sequence of immutable version records, each owning a non-overlapping slice of time. The current version has an open-ended window (effective_end IS NULL) until an amendment closes it and opens the next. Resolution is the act of dropping a vertical line at ship_date across that timeline and reading off the single bar it intersects.

The version record is the atomic unit. Its temporal fields are what resolution reads; everything else is the payload the validation tier consumes once a version is chosen.

Step-by-step implementation

The build order is: define the version record, persist it in a temporal table with a range guard, write the resolver, then make amendments close the prior window atomically.

Step 1 — Model the version record

Validate the temporal invariants in the model so a malformed window can never reach the store. The key rule is strictly-after — a zero-width or inverted window matches nothing and is a silent gap.

# models/contract_version.py
from datetime import datetime
from enum import Enum
from typing import Optional, Any
from pydantic import BaseModel, model_validator


class AmendmentType(str, Enum):
    BASE = "BASE"
    GRI = "GRI"                     # general rate increase
    LANE_ADJ = "LANE_ADJ"
    ACCESSORIAL = "ACCESSORIAL"


class ContractVersion(BaseModel):
    contract_version_id: str
    contract_id: str
    carrier_scac: str
    version_no: int
    effective_start: datetime            # tz-aware, always
    effective_end: Optional[datetime] = None
    amendment_type: AmendmentType = AmendmentType.BASE
    supersedes_id: Optional[str] = None
    rate_matrix: dict

    @model_validator(mode="after")
    def check_window(self) -> "ContractVersion":
        # Both boundaries must be timezone-aware so comparisons are total.
        if self.effective_start.tzinfo is None:
            raise ValueError("effective_start must be timezone-aware")
        if self.effective_end is not None:
            if self.effective_end.tzinfo is None:
                raise ValueError("effective_end must be timezone-aware")
            # Half-open [start, end): end strictly after start, never equal.
            if self.effective_end <= self.effective_start:
                raise ValueError("effective_end must be strictly after effective_start")
        return self

Common mistake: accepting naive datetimes. A window built from a naive date silently pins to the server’s local zone, so the same shipment resolves differently on a machine in a different region. Force tz-awareness at the boundary.

Step 2 — Persist as a temporal table

Store each version as a row keyed by an explicit half-open window. The generated daterange/tstzrange column is what indexing and the overlap guard both key on; carrying it as a real column keeps the resolver’s predicate and the constraint reading the same source of truth.

-- schema/contract_version.sql
CREATE TABLE contract_version (
    contract_version_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    contract_id         text        NOT NULL,
    carrier_scac        char(4)     NOT NULL,
    version_no          int         NOT NULL,
    effective_start     timestamptz NOT NULL,
    effective_end       timestamptz,                 -- NULL = open-ended
    amendment_type      text        NOT NULL DEFAULT 'BASE',
    supersedes_id       uuid REFERENCES contract_version (contract_version_id),
    rate_matrix         jsonb       NOT NULL,
    version_hash        char(64)    NOT NULL,
    -- Half-open window materialized for indexing and exclusion.
    effective_window    tstzrange GENERATED ALWAYS AS (
        tstzrange(effective_start, effective_end, '[)')
    ) STORED,
    UNIQUE (contract_id, version_no)
);

Step 3 — Resolve point-in-time

The resolver takes (carrier, contract, ship_date) and returns exactly one version. The predicate is the whole game: start inclusive, end exclusive, and NULL end treated as open. Because Step 2 guarantees non-overlap, a correct predicate can only match one row — no tie-break is needed in the query, but the ORDER BY version_no DESC LIMIT 1 is kept as a defensive backstop.

# resolution/resolver.py
from datetime import datetime
from typing import Optional
import psycopg

RESOLVE_SQL = """
SELECT contract_version_id, version_no, rate_matrix
FROM contract_version
WHERE contract_id = %(contract_id)s
  AND carrier_scac = %(scac)s
  AND effective_start <= %(ship_ts)s
  AND (effective_end IS NULL OR %(ship_ts)s < effective_end)
ORDER BY version_no DESC        -- backstop only; non-overlap makes this a no-op
LIMIT 1
"""


def resolve_version(
    conn: psycopg.Connection, contract_id: str, scac: str, ship_ts: datetime
) -> Optional[dict]:
    """Return the single contract version effective at ship_ts, or None."""
    if ship_ts.tzinfo is None:
        raise ValueError("ship_ts must be timezone-aware for point-in-time resolution")
    with conn.cursor() as cur:
        cur.execute(RESOLVE_SQL, {"contract_id": contract_id, "scac": scac, "ship_ts": ship_ts})
        row = cur.fetchone()
    if row is None:
        return None                 # resolution gap — route to quarantine, do not guess
    return {"contract_version_id": row[0], "version_no": row[1], "rate_matrix": row[2]}

Common mistake: writing ORDER BY effective_start DESC LIMIT 1 without the date predicate. That returns the newest version regardless of ship_date, which is exactly the latest-wins bug that misprices every historical invoice — the failure walked through in Implementing Point-in-Time Tariff Resolution in SQL.

Step 4 — Amend by closing the prior window atomically

An amendment is never an in-place UPDATE of the rate matrix. It closes the currently-open version by stamping its effective_end, then inserts a new open-ended version — both in one transaction so the timeline is never momentarily overlapping or gapped.

# amendment/apply.py
from datetime import datetime
import uuid
import psycopg


def apply_amendment(
    conn: psycopg.Connection, contract_id: str, scac: str,
    effective_at: datetime, new_matrix: dict, amendment_type: str, version_hash: str,
) -> str:
    """Close the open version at effective_at and open the successor atomically."""
    with conn.transaction():                       # all-or-nothing
        with conn.cursor() as cur:
            cur.execute(
                """
                UPDATE contract_version
                SET effective_end = %(at)s
                WHERE contract_id = %(cid)s
                  AND effective_end IS NULL         -- the single open version
                RETURNING contract_version_id, version_no
                """,
                {"at": effective_at, "cid": contract_id},
            )
            prior = cur.fetchone()
            prior_id, prior_no = (prior[0], prior[1]) if prior else (None, 0)

            new_id = str(uuid.uuid4())
            cur.execute(
                """
                INSERT INTO contract_version (
                    contract_version_id, contract_id, carrier_scac, version_no,
                    effective_start, effective_end, amendment_type, supersedes_id,
                    rate_matrix, version_hash)
                VALUES (%(id)s, %(cid)s, %(scac)s, %(no)s,
                        %(at)s, NULL, %(kind)s, %(sup)s, %(matrix)s, %(hash)s)
                """,
                {"id": new_id, "cid": contract_id, "scac": scac, "no": prior_no + 1,
                 "at": effective_at, "kind": amendment_type, "sup": prior_id,
                 "matrix": psycopg.types.json.Json(new_matrix), "hash": version_hash},
            )
    return new_id

The supersedes_id written here is the lineage chain: following it backwards reconstructs the full amendment history of a contract, which is what a SOX reviewer asks for. Enforcing that no two windows ever overlap is the subject of Preventing Effective-Date Overlap in Contract Versions.

Validation & testing

The tests that matter exercise the boundaries — the instant a window opens, the instant before it closes, and the gaps between versions. A resolver that passes mid-window cases can still be off-by-one at the seam.

# tests/test_resolution.py
from datetime import datetime, timezone
import pytest

UTC = timezone.utc


def ts(y, m, d):
    return datetime(y, m, d, tzinfo=UTC)


def test_resolves_to_containing_version(pg_conn, seeded_timeline):
    # ship_date inside v2's window returns v2, not the newest v3.
    v = resolve_version(pg_conn, "EXLA-NE-2025", "EXLA", ts(2025, 9, 15))
    assert v["version_no"] == 2


def test_start_boundary_is_inclusive(pg_conn, seeded_timeline):
    # A shipment at the exact effective_start belongs to the new version.
    v = resolve_version(pg_conn, "EXLA-NE-2025", "EXLA", ts(2025, 6, 1))
    assert v["version_no"] == 2


def test_end_boundary_is_exclusive(pg_conn, seeded_timeline):
    # The instant v2 closes belongs to v3, never both.
    v = resolve_version(pg_conn, "EXLA-NE-2025", "EXLA", ts(2026, 1, 1))
    assert v["version_no"] == 3


def test_open_ended_version_matches_future(pg_conn, seeded_timeline):
    v = resolve_version(pg_conn, "EXLA-NE-2025", "EXLA", ts(2026, 8, 1))
    assert v["version_no"] == 3


def test_pre_history_returns_none(pg_conn, seeded_timeline):
    # Before any version existed there is no active contract — a resolution gap.
    assert resolve_version(pg_conn, "EXLA-NE-2025", "EXLA", ts(2024, 12, 31)) is None

The seam tests (test_start_boundary_is_inclusive and test_end_boundary_is_exclusive) are the ones that catch the half-open/closed-interval confusion. Keep a fixture whose windows abut exactly so the two assertions together prove the boundary belongs to precisely one side.

Performance & tuning

Resolution runs on every invoice, so the predicate must be index-served. Two indexes cover it: a GiST index on the materialized range for containment, and a plain B-tree for the equality columns that partition the timeline.

-- GiST answers "which window contains this timestamp" in log time.
CREATE INDEX ix_cv_window ON contract_version USING gist (contract_id, effective_window);
-- B-tree serves the carrier/contract equality + start-ordering backstop.
CREATE INDEX ix_cv_lookup ON contract_version (contract_id, carrier_scac, effective_start DESC);

Because most invoices in a batch cluster on the same few open versions, a small resolution cache keyed by (contract_id, ship_date_bucket) absorbs the majority of lookups. Uncached resolution against a cold table is fine at single-invoice latency but dominates a million-row historical reconciliation, which is where batch resolution earns its place.

Failure modes

Four recurring defects account for nearly every mis-resolution in this layer. Each has a distinct signature.

A resolution gap and an overlap are opposite bugs with the same origin — an amendment that did not close and open windows as one atomic pair. Both disappear once Step 4 is the only path that writes versions.

Integration points

The resolver is the single point-in-time gateway that every priced stage funnels through. Its output — a contract_version_id and the rate_matrix it wraps — is stamped onto the invoice and carried downstream unchanged.

Because the contract_version_id is immutable, any downstream verdict is reproducible: re-running the audit months later resolves the identical version and computes the identical variance. That reproducibility is the whole reason the temporal model exists.

In this section

• Implementing Point-in-Time Tariff Resolution in SQL — fixing the query that returns the newest contract instead of the one active on the shipment date, including half-open vs closed intervals, NULL effective-end handling, and a lateral-join pattern for batch resolution.
• Preventing Effective-Date Overlap in Contract Versions — using a PostgreSQL daterange exclusion constraint with btree_gist, an atomic amendment routine, and a Python guard so two versions can never claim the same instant.

Related

• Freight Contract Architecture & Rate Mapping — the parent architecture whose rate matrices this layer stamps with effective windows.
• Mileage-Band Rate Lookups — distance-priced lanes that carry the same effective-dating semantics.
• LTL Rate Sheet Digitization — the class-based matrices each contract version wraps.
• Lane Matching Algorithms — the downstream consumer that anchors shipment lanes to the resolved rate table.

---

Up: Freight Contract Architecture & Rate Mapping