Preventing Effective-Date Overlap in Contract Versions

Two contract versions claim overlapping effective windows, so point-in-time resolution finds two matching tariffs and the one that wins changes from one re-run to the next.

The failure you are hitting

The resolver was supposed to return exactly one version for any shipment date. Instead, on a band of dates where two windows overlap, it returns two candidate rows — and because nothing breaks the tie deterministically, the winner depends on physical row order, which shifts after a VACUUM, a replica promotion, or an unrelated insert. The symptoms are maddening precisely because they are intermittent:

• A reconciliation that was clean on Monday flags a 4% overbill on Wednesday against the same invoices, because the resolver silently switched from the base version to the amended one on the overlap band.
• Two auditors querying the identical ship_date get different expected charges, and neither query is “wrong” — the store genuinely contains two versions that both claim that instant.
• A LIMIT 1 resolver masks the problem entirely: it returns one row, looks healthy, and quietly alternates which one it returns as the plan changes.

This is a data-integrity defect in Contract Versioning & Effective Dating, and no amount of query tuning fixes it — the store must make an overlapping window impossible to insert in the first place.

Root cause analysis

Overlap is created at write time, not read time. Three production conditions let two versions come to own the same instant:

1. No exclusion constraint. The table enforces UNIQUE (contract_id, version_no) but nothing stops two rows for the same contract from having intersecting [effective_start, effective_end) ranges. The database will happily store a contradiction it has no rule against.
2. Manual amendment entry. An analyst inserts the new version with its effective_start but forgets to stamp the prior version’s effective_end, leaving the old window open-ended and the new one active — both match every date from the new start onward.
3. Open-ended windows never closed. Every version is inserted with effective_end = NULL (“current”), so the store accumulates several perpetually-open versions for one contract. Point-in-time resolution then matches all of them for any recent date.

Reproducible diagnostic

Before adding the constraint, find the contracts that already violate it — a constraint cannot be created while overlapping rows exist. This self-join surfaces every pair of versions whose windows intersect:

-- find existing overlaps: any two versions of one contract sharing an instant
SELECT a.contract_id,
       a.version_no  AS ver_a, a.effective_start AS start_a, a.effective_end AS end_a,
       b.version_no  AS ver_b, b.effective_start AS start_b, b.effective_end AS end_b
FROM contract_version AS a
JOIN contract_version AS b
  ON a.contract_id = b.contract_id
 AND a.contract_version_id < b.contract_version_id      -- each pair once
 AND tstzrange(a.effective_start, a.effective_end, '[)')
  && tstzrange(b.effective_start, b.effective_end, '[)')  -- && = ranges intersect
ORDER BY a.contract_id, start_a;

Interpret the result before you remediate:

Run this and reduce every overlap to a clean boundary before Step 2, or the ALTER TABLE ... ADD CONSTRAINT will fail on the existing data.

Resolution path

The durable fix is a database-level guarantee that overlap cannot be inserted, plus an amendment routine that closes and opens windows as one atomic operation, plus an application guard that fails fast with a clear message.

Step 1 — Enable the range operator-class extension

Exclusion constraints that mix an equality column (contract_id) with a range-overlap operator (&&) need GiST support for scalar types, which btree_gist provides:

CREATE EXTENSION IF NOT EXISTS btree_gist;

Step 2 — Add the exclusion constraint

The constraint says: within one contract_id, no two rows may have overlapping effective windows. Postgres enforces it on every insert and update, so an overlap becomes impossible rather than merely discouraged:

-- reject any two versions of the same contract whose windows intersect
ALTER TABLE contract_version
    ADD CONSTRAINT no_overlapping_versions
    EXCLUDE USING gist (
        contract_id WITH =,                                      -- same contract
        tstzrange(effective_start, effective_end, '[)') WITH &&  -- overlapping window
    );

The [) bound makes the range half-open, matching the resolver’s predicate exactly: version A ending at the same instant version B starts do not overlap, so clean adjacent windows are allowed while any true intersection is rejected.

Step 3 — Amend atomically so a valid state is never skipped

The only safe way to introduce a new version is to close the current one and open the successor in a single transaction. Doing it in two statements outside a transaction leaves a window where either both are open (overlap) or neither covers the boundary (gap):

# amendment/atomic.py
from datetime import datetime
import uuid
import psycopg
from psycopg import errors


def amend_contract(
    conn: psycopg.Connection, contract_id: str, scac: str,
    effective_at: datetime, new_matrix: dict, version_hash: str,
) -> str:
    """Close the open version at effective_at and open its successor atomically."""
    if effective_at.tzinfo is None:
        raise ValueError("effective_at must be timezone-aware")
    try:
        with conn.transaction():                    # both writes commit together or not at all
            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
                    RETURNING contract_version_id, version_no
                    """,
                    {"at": effective_at, "cid": contract_id},
                )
                prior = cur.fetchone()
                prior_id, next_no = (prior[0], prior[1] + 1) if prior else (None, 1)

                new_id = str(uuid.uuid4())
                cur.execute(
                    """
                    INSERT INTO contract_version (
                        contract_version_id, contract_id, carrier_scac, version_no,
                        effective_start, effective_end, supersedes_id, rate_matrix, version_hash)
                    VALUES (%(id)s, %(cid)s, %(scac)s, %(no)s, %(at)s, NULL,
                            %(sup)s, %(matrix)s, %(hash)s)
                    """,
                    {"id": new_id, "cid": contract_id, "scac": scac, "no": next_no,
                     "at": effective_at, "sup": prior_id,
                     "matrix": psycopg.types.json.Json(new_matrix), "hash": version_hash},
                )
    except errors.ExclusionViolation as exc:
        # The constraint caught an overlap the application logic missed — surface it.
        raise ValueError(f"amendment would overlap an existing window: {exc}") from exc
    return new_id

Setting effective_end = start of the successor produces abutting half-open windows the constraint accepts. Because the UPDATE and INSERT share one transaction, the resolver never observes a moment where the boundary date matches zero or two versions.

Step 4 — Guard at the application boundary

Catch the intent to overlap before it reaches the database so callers get a domain error, not a raw SQL exception, and so the check also runs in unit tests without a live connection:

# amendment/guard.py
from datetime import datetime
from typing import Optional


def assert_no_overlap(
    existing: list[tuple[datetime, Optional[datetime]]],
    new_start: datetime, new_end: Optional[datetime],
) -> None:
    """Raise if [new_start, new_end) intersects any existing half-open window."""
    for start, end in existing:
        # Half-open intersection: a starts before b ends AND b starts before a ends.
        a_before_b_end = new_end is None or start < new_end
        b_before_a_end = end is None or new_start < end
        if a_before_b_end and b_before_a_end:
            raise ValueError(
                f"window [{new_start}, {new_end}) overlaps existing [{start}, {end})"
            )

The database constraint is the authority; this guard is the fast, testable first line that turns a would-be integrity error into an actionable message at the point the amendment is composed.

Verification

Prove that overlap is now impossible and that clean adjacency still works. The negative test is the important one — it asserts the database rejects the bad write:

# tests/test_overlap.py
from datetime import datetime, timezone
import pytest
from psycopg import errors

UTC = timezone.utc


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


def test_overlapping_insert_is_rejected(pg_conn):
    # v1 open from Jan; inserting v2 that starts before v1 is closed must fail.
    insert_version(pg_conn, "ABCD-MW-2026", "ABCD", 1, d(2026, 1, 1), None)
    with pytest.raises(errors.ExclusionViolation):
        insert_version(pg_conn, "ABCD-MW-2026", "ABCD", 2, d(2026, 3, 1), None)


def test_adjacent_windows_are_allowed(pg_conn):
    # v1 closed exactly where v2 opens — abutting, not overlapping.
    insert_version(pg_conn, "ABCD-MW-2026", "ABCD", 1, d(2026, 1, 1), d(2026, 3, 1))
    insert_version(pg_conn, "ABCD-MW-2026", "ABCD", 2, d(2026, 3, 1), None)   # no error


def test_amendment_leaves_single_match_on_boundary(pg_conn):
    amend_contract(pg_conn, "ABCD-MW-2026", "ABCD", d(2026, 3, 1), {"base": 1.9}, "h2")
    matches = count_versions_active_at(pg_conn, "ABCD-MW-2026", d(2026, 3, 1))
    assert matches == 1        # the boundary instant belongs to exactly one window

In production, add a nightly assertion that runs the Step-diagnostic self-join and alerts if it ever returns a row. With the constraint in place it should be permanently empty; a non-empty result means someone disabled the constraint during a bulk load and never re-enabled it.

Preventive configuration

Make the invariant part of the schema contract and the migration checklist, not a convention people remember:

# config/contract_integrity.yml
effective_dating:
  exclusion_constraint: no_overlapping_versions   # must exist on contract_version
  range_bounds: "[)"                               # half-open; adjacency allowed
  require_btree_gist: true
  amendment_path: atomic_close_then_open           # never manual two-step edits
  bulk_load:
    defer_constraint: false                        # keep enforced even during loads
    post_load_overlap_scan: required               # fail the load if any pair intersects
  guard_before_db: true                            # application-side assert_no_overlap

The single most important line is amendment_path: atomic_close_then_open: every overlap traces back to a window that was opened without closing its predecessor, and routing all amendments through the transaction in Step 3 removes that possibility. Once overlap is impossible, the single-row guarantee that Implementing Point-in-Time Tariff Resolution in SQL relies on holds by construction.

FAQ

Why is a UNIQUE constraint not enough to stop overlapping windows?

UNIQUE compares scalar equality, so it can stop two rows sharing the same version_no but it has no notion of range intersection. Two versions with different start dates whose windows overlap are perfectly unique on every scalar column. You need an EXCLUDE constraint with the range-overlap operator && to reject intersecting windows.

Do adjacent windows that touch at a single instant count as overlapping?

Not with half-open ranges. Declaring the range as [) — start inclusive, end exclusive — means a version ending at exactly the instant the next begins does not intersect it, so the exclusion constraint accepts clean adjacency while still rejecting any true overlap. That matches the resolver’s half-open predicate exactly.

How do I add the constraint when the table already has overlaps?

Postgres refuses to create an exclusion constraint that existing rows violate. Run the self-join diagnostic first to list every intersecting pair, close each prior window at the next version’s start (or delete true duplicates), and only then run ALTER TABLE ... ADD CONSTRAINT. The migration should fail loudly if any overlap remains.

What extension does the exclusion constraint need?

btree_gist. Mixing an equality predicate on a scalar column like contract_id with a range-overlap predicate in one GiST-backed exclusion constraint requires the scalar type to have a GiST operator class, which btree_gist supplies. Run CREATE EXTENSION IF NOT EXISTS btree_gist before adding the constraint.

Related

• Contract Versioning & Effective Dating — the temporal model whose integrity this constraint protects.
• Implementing Point-in-Time Tariff Resolution in SQL — the resolver that depends on the single-match guarantee this page enforces.
• Freight Contract Architecture & Rate Mapping — the parent architecture that owns the versioned contract store.

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