Implementing Point-in-Time Tariff Resolution in SQL

Your rate query returns the newest contract version instead of the one that was active on the shipment date, so every historical and back-dated invoice is silently mispriced against a tariff that did not exist when the freight moved.

The failure you are hitting

The audit passes. No exception fires, no row is dropped, and the query returns exactly one rate — the wrong one. You notice weeks later, when a carrier reconciliation shows a systematic variance concentrated on invoices with a ship_date earlier than the last rate change. Three observable shapes point at the same defect:

• A back-dated invoice for a shipment tendered in August prices against the October general rate increase, inflating the expected charge and generating a phantom overbill dispute the carrier correctly rejects.
• A historical replay of last quarter’s freight — run after a mid-cycle amendment landed — reprices every line at the current rate, so a re-run of a previously-clean audit suddenly flags thousands of shipments.
• Two invoices with the same lane and same ship date resolve to different rates depending on when the query ran, because “newest” is a moving target that changes every time an amendment is inserted.

This resolver sits inside Contract Versioning & Effective Dating; when it returns the wrong version, every priced stage downstream inherits a wrong-but-plausible number that raises no alarm.

Root cause analysis

The defect is almost never in the database. It is in a query that selects by recency instead of by the shipment’s own date. Three conditions turn that into silent mispricing:

1. No date predicate at all. The query is ORDER BY effective_start DESC LIMIT 1 — it asks “what is the latest version?” not “what was in force at ship_date?” It happens to be correct only while auditing same-day invoices, which is exactly why it survives testing and fails in reconciliation.
2. Half-open vs closed interval confusion. The window is [effective_start, effective_end) — start inclusive, end exclusive — but the query is written ship_date BETWEEN effective_start AND effective_end, which is closed on both ends. On the seam day where one version ends and the next begins, BETWEEN matches both, producing a duplicate.
3. NULL effective_end mishandled. The current, open-ended version has effective_end IS NULL. A predicate of ship_date < effective_end evaluates to NULL (not TRUE) for that row, so the open version is silently excluded and every present-day shipment falls through to None or to an older closed version.

Reproducible diagnostic

Before touching the query, confirm the version store itself is sane and that the resolver is the culprit. This diagnostic returns every version that a given ship_date matches — if it returns more than one row, you have overlap on top of the predicate bug:

-- diagnostic: how many versions does this ship_date actually match?
SELECT contract_version_id, version_no, effective_start, effective_end
FROM contract_version
WHERE contract_id = 'RDWY-SE-2026'
  AND carrier_scac = 'RDWY'
  AND effective_start <= TIMESTAMPTZ '2026-03-04 00:00:00+00'
  AND (effective_end IS NULL OR TIMESTAMPTZ '2026-03-04 00:00:00+00' < effective_end)
ORDER BY version_no DESC;

Read the row count against what your current resolver returns:

If the diagnostic itself returns two rows, the windows overlap and the predicate cannot save you — close the overlap first via Preventing Effective-Date Overlap in Contract Versions.

Resolution path

The fix is a predicate that tests the shipment’s own date against a half-open window, treats an open end as unbounded, and tie-breaks deterministically. Then it is generalized to resolve a whole batch in one pass with a lateral join.

Step 1 — Write the correct single-row predicate

Replace recency selection with containment. Start inclusive, end exclusive, NULL end treated as open:

-- resolve one shipment against its point-in-time tariff
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)  -- half-open [start, end)
ORDER BY version_no DESC        -- deterministic backstop if windows ever overlap
LIMIT 1;

Two clauses carry the fix. effective_start <= ship_ts uses <= so a shipment tendered at the exact opening instant belongs to the new version. effective_end IS NULL OR ship_ts < effective_end uses strict < so the closing instant belongs to the next version, never to both — and the IS NULL disjunct keeps the open version in play instead of letting three-valued logic drop it.

Step 2 — Resolve a whole batch with a lateral join

Historical replays resolve millions of invoices. Running Step 1 per row is a round-trip storm. A LATERAL join applies the same predicate once per invoice inside a single statement, so the planner can index-serve every lookup:

-- resolve an entire batch of invoices in one pass
SELECT i.invoice_id, i.ship_ts, v.contract_version_id, v.version_no
FROM staged_invoice AS i
LEFT JOIN LATERAL (
    SELECT cv.contract_version_id, cv.version_no
    FROM contract_version AS cv
    WHERE cv.contract_id = i.contract_id
      AND cv.carrier_scac = i.carrier_scac
      AND cv.effective_start <= i.ship_ts
      AND (cv.effective_end IS NULL OR i.ship_ts < cv.effective_end)
    ORDER BY cv.version_no DESC
    LIMIT 1
) AS v ON TRUE;                 -- LEFT keeps unmatched invoices as resolution gaps

The LEFT JOIN LATERAL ... ON TRUE is deliberate: an invoice with no matching version yields a NULL contract_version_id rather than vanishing from the result, so resolution gaps are counted and routed rather than silently dropped.

Step 3 — Wrap it with a Python resolver that never guesses

The application boundary enforces the contract that a missing version is a routable event, not a fallback to the newest rate. This wrapper is what the pricing stages call:

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

RESOLVE_ONE = """
SELECT contract_version_id, version_no, rate_matrix
FROM contract_version
WHERE contract_id = %(cid)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
LIMIT 1
"""


def resolve_tariff(
    conn: psycopg.Connection, contract_id: str, scac: str, ship_ts: datetime
) -> Optional[dict]:
    """Return the tariff in force at ship_ts, or None for a resolution gap."""
    if ship_ts.tzinfo is None:
        # A naive ship_ts compared against timestamptz coerces to the server
        # zone and shifts the boundary — reject it at the boundary instead.
        raise ValueError("ship_ts must be timezone-aware")
    with conn.cursor() as cur:
        cur.execute(RESOLVE_ONE, {"cid": contract_id, "scac": scac, "ship_ts": ship_ts})
        row = cur.fetchone()
    if row is None:
        return None                 # caller quarantines; it must NOT fall back to newest
    return {"contract_version_id": row[0], "version_no": row[1], "rate_matrix": row[2]}

Any caller tempted to replace return None with “just use the latest version” reintroduces the exact bug this page fixes. A gap means the contract for that date was never digitized — the invoice waits in quarantine until it is.

Verification

Prove the seam behaves and that history is stable across re-runs. These assertions belong in the integration suite that runs on every contract load:

# tests/test_point_in_time.py
from datetime import datetime, timezone

UTC = timezone.utc


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


def test_backdated_invoice_prices_at_historical_version(pg_conn, seeded):
    # v2 was effective 2026-01 .. 2026-06; a March shipment must resolve to v2,
    # not the current v4, even after later amendments landed.
    got = resolve_tariff(pg_conn, "RDWY-SE-2026", "RDWY", at(2026, 3, 4))
    assert got["version_no"] == 2


def test_end_boundary_belongs_to_next_version(pg_conn, seeded):
    # The instant v2 closes is v3's first instant — exclusive upper bound.
    got = resolve_tariff(pg_conn, "RDWY-SE-2026", "RDWY", at(2026, 6, 1))
    assert got["version_no"] == 3


def test_present_day_hits_open_version(pg_conn, seeded):
    got = resolve_tariff(pg_conn, "RDWY-SE-2026", "RDWY", at(2026, 7, 15))
    assert got["version_no"] == 4        # effective_end IS NULL


def test_replay_is_stable(pg_conn, seeded):
    # Same input, two calls, identical version — no dependence on "now".
    a = resolve_tariff(pg_conn, "RDWY-SE-2026", "RDWY", at(2026, 3, 4))
    b = resolve_tariff(pg_conn, "RDWY-SE-2026", "RDWY", at(2026, 3, 4))
    assert a["contract_version_id"] == b["contract_version_id"]

In production the signal is a resolution-gap rate that stays flat: a spike means a contract lapsed or an amendment was never loaded, not that the predicate needs loosening. Alert on the gap rate per carrier_scac, never suppress it.

Preventive configuration

Encode the window semantics as configuration so no future query can quietly reintroduce closed-interval or latest-wins logic:

# config/resolution.yml
point_in_time:
  window_semantics: half_open        # [effective_start, effective_end)
  open_end_is_unbounded: true         # NULL effective_end matches all future dates
  on_resolution_gap: quarantine       # never fall back to newest version
  tie_break: version_no_desc          # deterministic backstop only
  ship_ts_must_be_tz_aware: true      # reject naive datetimes at the boundary
  forbid_between_operator: true        # CI lint: no BETWEEN on effective windows

A CI lint that greps the query layer for BETWEEN against effective columns and for ORDER BY effective_start DESC without a ship predicate catches both regressions before they ship. The window-integrity guard that makes the single-row guarantee hold lives in Preventing Effective-Date Overlap in Contract Versions.

FAQ

Why does my query return the newest contract instead of the one active on the shipment date?

Because it selects by recency, not by containment. ORDER BY effective_start DESC LIMIT 1 with no date predicate always returns the latest version. Add effective_start <= ship_ts AND (effective_end IS NULL OR ship_ts < effective_end) so the query filters to the window that actually contains the shipment date before it orders anything.

Should I use BETWEEN for the effective-date range?

No. BETWEEN is closed on both ends, so on the seam day where one version ends and the next begins it matches both and returns a duplicate. Effective windows are half-open — start inclusive, end exclusive — so write effective_start <= ship_ts AND ship_ts < effective_end explicitly.

How do I handle the current version whose effective_end is NULL?

Treat NULL as unbounded. A bare ship_ts < effective_end evaluates to NULL for the open version and three-valued logic drops it, so present-day shipments resolve to nothing. Guard it with (effective_end IS NULL OR ship_ts < effective_end) so the open window matches every future date.

What should the resolver do when no version matches the shipment date?

Return a resolution gap and route the invoice to quarantine. It must never fall back to the newest version — that is the original bug in disguise. A gap means the contract for that date was never digitized, so the invoice waits until it is, preserving a clean audit trail.

Related

• Contract Versioning & Effective Dating — the temporal model this resolver reads from.
• Preventing Effective-Date Overlap in Contract Versions — the constraint that guarantees the single-row match this query depends on.
• Matching Shipment Lanes to Contracted Rate Tables Using Python — the downstream join that consumes the resolved tariff for each lane.
• Freight Contract Architecture & Rate Mapping — the parent architecture that owns the versioned rate store.

Up one level: Contract Versioning & Effective Dating · Section: Freight Contract Architecture & Rate Mapping