Freight Broker Customer Contracts: Minimum Volume Commitments & Shortfall Engines

Core Database Schema Patterns

The following simplified schema illustrates the relationships required for an enterprise MVC engine. All dates are stored in UTC with proper time-zone conversion at the application layer.

CREATE TABLE customer_contracts (
    contract_id BIGINT PRIMARY KEY,
    shipper_id BIGINT NOT NULL,
    contract_start DATE NOT NULL,
    contract_end DATE NOT NULL,
    commitment_type VARCHAR(20) NOT NULL, -- 'LOADS', 'SPEND', 'LANE_MIX'
    measurement_period VARCHAR(10) NOT NULL, -- 'MONTHLY', 'QUARTERLY'
    currency_code CHAR(3) DEFAULT 'USD',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE contract_commitments (
    commitment_id BIGINT PRIMARY KEY,
    contract_id BIGINT REFERENCES customer_contracts(contract_id),
    lane_id BIGINT,
    baseline_quantity DECIMAL(12,2) NOT NULL,
    baseline_uom VARCHAR(20) NOT NULL, -- 'LOADS', 'USD'
    shortfall_rate DECIMAL(8,4),
    shortfall_type VARCHAR(20), -- 'PER_LOAD', 'PERCENTAGE'
    make_good_days INT,
    active BOOLEAN DEFAULT true
);

CREATE TABLE weekly_tender_summary (
    summary_id BIGINT PRIMARY KEY,
    shipper_id BIGINT NOT NULL,
    commitment_id BIGINT REFERENCES contract_commitments(commitment_id),
    week_start DATE NOT NULL,
    tendered_quantity DECIMAL(12,2) NOT NULL,
    recognized_revenue DECIMAL(14,2),
    shortfall_calculated DECIMAL(14,2),
    invoice_line_id BIGINT,
    UNIQUE(shipper_id, commitment_id, week_start)
);

CREATE TABLE shortfall_events (
    event_id BIGINT PRIMARY KEY,
    commitment_id BIGINT REFERENCES contract_commitments(commitment_id),
    period_end DATE NOT NULL,
    calculated_shortfall DECIMAL(14,2) NOT NULL,
    billed_amount DECIMAL(14,2),
    status VARCHAR(20) NOT NULL, -- 'PENDING', 'BILLED', 'DISPUTED', 'WAIVED'
    dispute_reason TEXT,
    resolved_at TIMESTAMP
);

Indexes on shipper_id, commitment_id, and week_start are critical for sub-second compliance queries across thousands of active contracts.