Building an Accessorial Charge Lookup Table in Postgres: Production Hardening & Debugging

This page resolves one specific failure: an accessorial rate lookup table in Postgres that returns the wrong rate — or no rate at all — because overlapping effective dates, silently dropped rows, or unguarded bulk loads have corrupted the reference layer your audit engine prices against.

The lookup table is the deterministic store the accessorial charge taxonomy mapping stage reads from after it has normalized a carrier’s idiosyncratic surcharge codes into a canonical taxonomy. When that store is sound, a LIFTGATE charge resolves to exactly one contracted rate for the shipment’s date. When it is not, downstream pricing for detention, liftgate, or residential surcharges drifts silently, audit verdicts flip, and revenue leaks invoice by invoice. This guide isolates the failure, proves it with a reproducible query, and rebuilds the table so every lookup is version-controlled, temporally exact, and CI-gated.

Failure Definition

Accessorial lookup failures rarely throw. They surface as silent mismatches that the audit engine treats as authoritative:

• A carrier rate sheet maps DETENTION_PER_HOUR while a legacy contract row stored DETENTION_HOURLY; the parser drops the unmatched row instead of quarantining it, the table stays incomplete, and the engine falls back to a zero charge.
• Two contract versions for the same SCAC and code have overlapping effective windows, so a date-bounded lookup returns two rows and the application silently takes the first.
• A bulk load of thousands of carrier contracts builds one giant in-memory INSERT list, the worker is OOM-killed mid-transaction, and only part of the rate sheet is committed.
• A renewal spikes an accessorial fee by 300%, no pre-deploy check catches it, and the inflated rate propagates straight to production audits.

The signature is a lookup that succeeds but returns a final_rate nobody contracted for, with no error in the logs to trace it back.

Root Cause Analysis

Every one of these is a missing database guarantee or pipeline guard, not a bug in the SQL itself.

Rate-sheet drift compounds all five: when a carrier reissues a contract mid-cycle — the same drift that the LTL class-to-JSON mapping stage and the dynamic fuel surcharge calculator also have to absorb — a previously clean lookup can suddenly return stale or duplicate rows.

Reproducible Diagnostic

Before rebuilding anything, confirm the table is actually returning overlapping windows. This query reports any SCAC + code pair that has more than one active row for a given audit date — the exact condition that makes a lookup non-deterministic:

-- Confirm the failure: overlapping active windows for the same key
SELECT
    carrier_scac,
    accessorial_code,
    COUNT(*) AS overlapping_rows,
    array_agg(contract_version ORDER BY effective_date) AS versions
FROM accessorial_charge_lookup
WHERE CURRENT_DATE BETWEEN effective_date AND expiration_date
GROUP BY carrier_scac, accessorial_code
HAVING COUNT(*) > 1;

Any row in the result set is a non-deterministic lookup. If the table already had an exclusion constraint this query would return nothing, because the database would have rejected the second overlapping insert. A non-empty result is proof the guarantee is missing.

Resolution Path

1. Enforce temporal uniqueness at the schema level

The fix is to make overlapping windows physically impossible to insert. PostgreSQL’s btree_gist extension lets an EXCLUDE USING gist constraint reject any new row whose daterange overlaps an existing one for the same contract and code. This DDL is the hardened foundation aligned with the Freight Contract Architecture & Rate Mapping standard:

-- Enable temporal range operators
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE accessorial_charge_lookup (
    id BIGSERIAL PRIMARY KEY,
    carrier_scac VARCHAR(4) NOT NULL,
    contract_version VARCHAR(12) NOT NULL,
    accessorial_code VARCHAR(32) NOT NULL,
    rate_amount NUMERIC(10,4) NOT NULL CHECK (rate_amount >= 0),
    currency_code CHAR(3) NOT NULL DEFAULT 'USD',
    effective_date DATE NOT NULL,
    expiration_date DATE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    metadata JSONB DEFAULT '{}'::jsonb,

    -- Prevent overlapping temporal windows for the same contract/code
    CONSTRAINT no_temporal_overlap EXCLUDE USING gist (
        carrier_scac WITH =,
        contract_version WITH =,
        accessorial_code WITH =,
        daterange(effective_date, expiration_date, '[)') WITH &&
    ),
    -- Enforce logical date boundaries
    CONSTRAINT valid_date_range CHECK (expiration_date > effective_date)
);

-- Optimized lookup index for active-rate queries
CREATE INDEX idx_accessorial_lookup_active
ON accessorial_charge_lookup (carrier_scac, accessorial_code, effective_date DESC)
WHERE expiration_date >= CURRENT_DATE;

COMMENT ON TABLE accessorial_charge_lookup IS
    'Deterministic accessorial rate reference layer. All inserts must pass CI threshold validation.';

The exclusion constraint uses daterange() rather than tsrange() because effective_date and expiration_date are DATE columns, and btree_gist must expose the daterange operator class for it to compile. Verify before deploying with SELECT * FROM pg_opclass WHERE opcname = 'gist_date_ops';. The partial index accelerates active-rate lookups while ignoring expired archives, cutting planner overhead under high-concurrency audit traffic.

2. Stream the ingest so bulk loads cannot exhaust memory

Loading carrier contracts through a DataFrame-to-INSERT path is what triggers the OOM kills. Stream instead: this psycopg2 loader uses COPY FROM STDIN, caps memory at batch_size, and quarantines malformed rows rather than dropping them. It is the same memory-bounded discipline the async batch invoice processing layer applies to document parsing.

import io
import logging
import psycopg2
from datetime import date
from typing import Iterator, Tuple

logger = logging.getLogger("accessorial_ingest")

def stream_accessorial_records(raw_rows: Iterator[dict]) -> Iterator[Tuple]:
    """Yield validated tuples, routing malformed data to quarantine."""
    for row in raw_rows:
        try:
            yield (
                row["carrier_scac"].strip().upper(),
                row["contract_version"].strip(),
                row["accessorial_code"].strip().upper(),
                float(row["rate_amount"]),
                row.get("currency_code", "USD"),
                date.fromisoformat(row["effective_date"]),
                date.fromisoformat(row["expiration_date"]),
                row.get("metadata", "{}"),
            )
        except (ValueError, KeyError, TypeError) as e:
            # Never drop silently — a dropped row is an incomplete table
            logger.warning(
                "QUARANTINE_ROW",
                extra={"carrier": row.get("carrier_scac"), "error": str(e), "payload": row},
            )

def load_accessorial_batch(conn, records: Iterator[Tuple], batch_size: int = 5000):
    """Memory-safe COPY ingestion with explicit transaction control."""
    buffer = io.StringIO()
    count = 0

    def flush(buf, n):
        buf.seek(0)
        with conn.cursor() as cur:
            cur.copy_expert(
                """COPY accessorial_charge_lookup
                   (carrier_scac, contract_version, accessorial_code,
                    rate_amount, currency_code, effective_date, expiration_date, metadata)
                   FROM STDIN WITH (FORMAT text, DELIMITER E'\\t', NULL '\\N')""",
                buf,
            )
        conn.commit()
        logger.info("BATCH_COMMIT", extra={"rows_loaded": n})

    for record in records:
        line = "\t".join(str(v) if v is not None else "\\N" for v in record) + "\n"
        buffer.write(line)
        count += 1

        if count >= batch_size:
            flush(buffer, count)
            buffer = io.StringIO()
            count = 0

    if count > 0:
        flush(buffer, count)

This caps resident memory at one batch, uses Postgres’s native COPY protocol for throughput, and keeps each batch in its own transaction so a failure rolls back cleanly instead of half-committing a rate sheet.

Verification

Confirm the rebuild holds in three checks. First, re-run the diagnostic from above — it must now return zero rows, because the constraint rejects overlaps at insert time. Second, prove the constraint actively fires by attempting a deliberate overlap; the second insert must raise exclusion_violation (SQLSTATE 23P01):

import psycopg2

def assert_overlap_rejected(conn):
    """The exclusion constraint must block a second overlapping window."""
    with conn.cursor() as cur:
        cur.execute(
            """INSERT INTO accessorial_charge_lookup
               (carrier_scac, contract_version, accessorial_code,
                rate_amount, effective_date, expiration_date)
               VALUES ('ABCD', 'v1', 'LIFTGATE', 75.0, '2026-01-01', '2026-12-31')"""
        )
        try:
            cur.execute(
                """INSERT INTO accessorial_charge_lookup
                   (carrier_scac, contract_version, accessorial_code,
                    rate_amount, effective_date, expiration_date)
                   VALUES ('ABCD', 'v1', 'LIFTGATE', 90.0, '2026-06-01', '2027-06-01')"""
            )
            raise AssertionError("overlap was NOT rejected — constraint missing")
        except psycopg2.errors.ExclusionViolation:
            conn.rollback()  # expected: the second window overlaps the first

Third, confirm the active-rate index is being used rather than scanned past: EXPLAIN (ANALYZE, BUFFERS) SELECT rate_amount FROM accessorial_charge_lookup WHERE carrier_scac = 'ABCD' AND accessorial_code = 'LIFTGATE' AND CURRENT_DATE BETWEEN effective_date AND expiration_date; should report an Index Scan using idx_accessorial_lookup_active, not a Seq Scan.

Preventive Configuration

Gate every rate sheet on variance before it reaches the table

Schema constraints stop overlaps and negatives, but they cannot tell a legitimate renewal from a fat-fingered 300% spike. Add a CI gate that validates variance against the historical baseline and refuses to load a sheet that breaches it. Charges that fail this gate are the same class of anomaly that Accessorial Charge Scoring weights downstream and that Threshold Tuning & Alerting tracks on the decision stream:

import pandas as pd
from pydantic import BaseModel, ValidationError, field_validator
from typing import Optional

class AccessorialRate(BaseModel):
    carrier_scac: str
    accessorial_code: str
    rate_amount: float
    effective_date: str
    expiration_date: str
    previous_rate: Optional[float] = None

    @field_validator("rate_amount")
    @classmethod
    def check_threshold_variance(cls, v: float, info) -> float:
        prev = info.data.get("previous_rate")
        if prev and prev > 0:
            variance = abs((v - prev) / prev)
            if variance > 0.50:  # Block >50% spike pending manual sign-off
                raise ValueError(
                    f"Threshold breach: {variance:.2%} variance on "
                    f"{info.data.get('accessorial_code')}"
                )
        return v

def validate_rate_sheet(df: pd.DataFrame, historical_lookup: dict) -> bool:
    """CI gate: raise (fail the build) if any row breaches validation."""
    failed_rows = []
    for idx, row in df.iterrows():
        try:
            AccessorialRate(
                carrier_scac=row["carrier_scac"],
                accessorial_code=row["accessorial_code"],
                rate_amount=row["rate_amount"],
                effective_date=row["effective_date"],
                expiration_date=row["expiration_date"],
                previous_rate=historical_lookup.get(row["accessorial_code"]),
            )
        except ValidationError as e:
            failed_rows.append({"row": idx, "errors": e.errors()})

    if failed_rows:
        raise RuntimeError(
            f"CI Gate Failed: {len(failed_rows)} rows breached validation thresholds. "
            "Quarantine initiated."
        )
    return True

Route unmatched codes deterministically instead of defaulting to zero

When a code has no active row, the lookup must produce a tagged result, never a silent 0.00. A tiered query with an explicit fallback table guarantees a value and records where it came from, so the audit engine can filter on rate_source and queue uncertain invoices for review rather than passing them as priced:

-- Fallback table for unmatched or expired codes
CREATE TABLE fallback_accessorial_rates (
    accessorial_code VARCHAR(32) PRIMARY KEY,
    fallback_rate NUMERIC(10,4) NOT NULL,
    justification VARCHAR(255) NOT NULL,
    last_validated TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Deterministic lookup with explicit fallback routing and source tagging
SELECT
    COALESCE(acl.rate_amount, fr.fallback_rate, 0.00) AS final_rate,
    CASE
        WHEN acl.id IS NOT NULL THEN 'ACTIVE_CONTRACT'
        WHEN fr.accessorial_code IS NOT NULL THEN 'FALLBACK_ROUTED'
        ELSE 'ZERO_FALLBACK_AUDIT_FLAG'
    END AS rate_source,
    acl.contract_version,
    acl.effective_date,
    fr.justification AS fallback_reason
FROM (VALUES ('ABCD', 'LIFTGATE', CURRENT_DATE)) AS lookup(scac, code, audit_date)
LEFT JOIN accessorial_charge_lookup acl
    ON acl.carrier_scac = lookup.scac
    AND acl.accessorial_code = lookup.code
    AND lookup.audit_date BETWEEN acl.effective_date AND acl.expiration_date
LEFT JOIN fallback_accessorial_rates fr
    ON fr.accessorial_code = lookup.code;

Pair this with structured logging so every resolution is queryable during an incident. Emit one JSON line per lookup carrying a correlation_id, carrier_scac, accessorial_code, rate_source, and final_rate, then alert when rate_source = 'FALLBACK_ROUTED' exceeds ~5% of invoices — a reliable sign a contract reconciliation is overdue. For monitoring under load, watch pg_stat_activity for COPY or INSERT statements stuck in state = 'active' beyond 30 seconds and check work_mem/maintenance_work_mem if they stall. The official PostgreSQL COPY documentation and the Python logging guidelines cover the tuning knobs in depth.

FAQ

Why does my date-bounded lookup return two rows for the same charge code?

Because the table has no temporal exclusion constraint, so two contract versions with overlapping effective_date/expiration_date windows can both exist for one SCAC + code. Add the EXCLUDE USING gist (... daterange(effective_date, expiration_date, '[)') WITH &&) constraint from the resolution path; it rejects the second overlapping row at insert time, after which a date-bounded SELECT can match at most one row.

Should I use tsrange or daterange in the exclusion constraint?

Use daterange when effective_date and expiration_date are DATE columns — tsrange is for TIMESTAMP. The btree_gist extension must expose the matching operator class; verify with SELECT * FROM pg_opclass WHERE opcname = 'gist_date_ops'; before deploying, or the CREATE TABLE will fail to compile.

How do I keep a multi-thousand-contract bulk load from OOM-killing the worker?

Do not materialize the whole rate sheet into one INSERT list. Stream it through COPY FROM STDIN in fixed batches (5,000 rows is a sound default) and commit each batch in its own transaction, as in load_accessorial_batch. Peak memory then reflects one batch, not the entire upload, so concurrent contract loads stay flat.

What should the lookup return when a charge code has no active rate?

Never a silent 0.00 and never a KeyError. Route through the tiered fallback query so the result is tagged ACTIVE_CONTRACT, FALLBACK_ROUTED, or ZERO_FALLBACK_AUDIT_FLAG. The audit engine filters on that rate_source to hold uncertain invoices for review instead of pricing them as final.

Related

• Accessorial Charge Taxonomy Mapping — the normalization stage that feeds canonical codes into this lookup table.
• How to Map LTL Class Rates to JSON Schemas — versioned rate structuring that faces the same contract-drift problem.
• Calculating Dynamic Fuel Surcharges with Python Formulas — another date-indexed rate component this table sits beside.
• Accessorial Charge Scoring — weights the flagged charges this table surfaces during validation.
• Implementing Async Batch Invoice Processing with Celery — the memory-bounded ingestion discipline this loader mirrors.

---

Up one level: Accessorial Charge Taxonomy Mapping · Section: Freight Contract Architecture & Rate Mapping