Agreement Schema Design

The agreement schema is the deterministic contract between commercial intent and financial execution. Inside the broader core architecture and promotion mapping discipline, this page owns one specific sub-problem: how a negotiated trade agreement — with its tiers, windows, channel scopes, and settlement cadence — becomes a normalized, queryable, version-controlled record that every downstream stage can trust. Get the schema wrong and the failure modes are systemic: claims validate against the wrong contract version, retroactive amendments silently re-rate closed periods, and a single agreement loaded twice doubles the accrual. Get it right and reconciliation becomes a function of data rather than of an analyst’s memory. The audience is concrete — Python ETL developers who model and ingest the contract, trade finance analysts who defend the accrual it produces, and vendor managers who onboard and amend it without breaking active pipelines.

This is the canonical source of truth for promotion-to-transaction binding. A single vendor contract may govern multiple program types, channel-specific accruals, and overlapping volume tiers across staggered effective dates. The schema abstracts that into a structure that survives ingestion, transformation, and settlement, and it establishes explicit parent-child lineage between master agreements, amendment addenda, and SKU-level execution rules — so every invoice line traces deterministically back to the governing clause that authorized it.

Positioning Within the Reconciliation Architecture

The schema sits upstream of every rule the system evaluates. The eligibility rule framework reads its predicates to decide which transactions qualify; payout structure modeling reads its tiers to turn qualifying volume into money; and the claim validation rule engine references a specific agreement version when it adjudicates a vendor’s claim. Because all three consume the same artifact, the schema’s contract is load-bearing: a rename here is a breaking change everywhere, and an ambiguous tier boundary here becomes a payout dispute three stages downstream.

Two design commitments make that safe. First, immutability with temporal precision — every record carries a stable primary key, a version hash, and an explicit half-open effective interval, so a contract amended mid-cycle never re-validates history under new terms. Second, deterministic precedence — overlapping validity windows resolve through declared rules, never ad-hoc overrides, which is what prevents double-counting at month-end close and gives finance a clean trail for accrual reversals.

Entity Topology and Schema Specification

A resilient schema separates static contract metadata from dynamic execution parameters, and it separates the agreement (an abstract contract) from the agreement version (the immutable thing claims actually reference). Static metadata changes rarely and identifies the contract; execution parameters — tiers, predicates, rates — change on every amendment and are therefore versioned as a unit. Amendment tracking carries a parent_agreement_id and a monotonic version_sequence so lineage survives renegotiation, and a status flag (draft, active, suspended, expired) gates which versions are eligible for transaction matching.

The fields below are the minimum contract the downstream stages depend on. Treat them as a versioned interface: adding a nullable field is backward-compatible, while renaming or retyping one is a breaking change that bumps the schema version and forces a re-hash of affected records.

The version_hash is what makes ingestion idempotent and what lets the claim engine pin a claim to exactly the terms in force when the sale occurred. It is computed over a canonical serialization of the version’s terms — sorted keys, normalized decimal strings, UTC timestamps — so two structurally identical agreements hash identically and a single semantic edit produces a new hash.

Conditional Logic and Rule Integration

Promotional agreements are inherently conditional, so the schema must encode eligibility boundaries in a form an evaluation engine parses without ambiguity. Channel constraints, geographic territories, product hierarchies, and minimum purchase thresholds are modeled as an array of predicates — each a (field, operator, value) triple — combined under explicit logical operators (AND, OR, NOT) rather than implied by field adjacency. This keeps the encoded scope machine-readable and lets the eligibility framework resolve the applicable rule set by matching transactional attributes (store_id, sku_class, invoice_date) against the predicate tree.

{
  "scope": {
    "op": "AND",
    "predicates": [
      {"field": "channel", "op": "in", "value": ["GROCERY_RETAIL", "CLUB"]},
      {"field": "territory", "op": "eq", "value": "US"},
      {"field": "sku_hierarchy", "op": "descendant_of", "value": "BEV-CARBONATED"},
      {"field": "min_qty", "op": "gte", "value": "5000"}
    ]
  }
}

Encoding scope as data rather than as branching code is what lets vendor managers onboard a new promotion without a pipeline deploy, and it is what gives deterministic precedence its raw material: when two active versions could both match a transaction, the resolver ranks them by a declared key (specificity of scope, then version_sequence) so the outcome never depends on row order. The scope_hash over this predicate tree lets the engine cache eligibility decisions and detect when an amendment actually changed who qualifies versus merely restating terms.

Financial Settlement Layer

Once eligibility is confirmed, the schema drives accurate financial modeling. The settlement layer defines how qualifying volume becomes monetary value — accrual rates, caps, floors, and cadence — and it deliberately keeps calculation logic separate from storage format so finance can simulate accruals under varying volume scenarios before committing a vendor settlement. Tiered structures are stored as ordered arrays with explicit boundary conditions (lower_bound, upper_bound, rate), rates held as strings and parsed via decimal.Decimal at evaluation time to eliminate floating-point accumulation across a quarter-end run.

The distinction that drives the most disputes is how a crossed boundary re-rates volume. Incremental tiers rate each band at its own rate; retroactive tiers re-rate the entire cumulative volume at the highest earned rate the moment a breakpoint is crossed. The schema must store rate_type explicitly per agreement so the modeling layer never guesses.

from decimal import Decimal, ROUND_HALF_UP

def incremental_rebate(volume: Decimal, tiers: list["Tier"]) -> Decimal:
    """Each band earns its own rate; only volume within the band is rated at that band."""
    total = Decimal("0")
    for t in tiers:
        if volume <= t.lower_bound:
            break
        upper = min(volume, t.upper_bound or volume)
        total += (upper - t.lower_bound) * t.rate
    return total.quantize(Decimal("0.0001"), rounding=ROUND_HALF_UP)

def retroactive_rebate(volume: Decimal, tiers: list["Tier"]) -> Decimal:
    """Crossing a breakpoint re-rates the ENTIRE cumulative volume at the highest earned rate."""
    earned = next(t.rate for t in reversed(tiers) if volume >= t.lower_bound)
    return (volume * earned).quantize(Decimal("0.0001"), rounding=ROUND_HALF_UP)

For the full schema patterns behind progressive, flat, and retroactive tiers — including the JSON layout and ordering guarantees — see how to map vendor rebate tiers in JSON. Properly modeled tiers eliminate rounding discrepancies and ensure a retroactive tier jump applies consistently across historical transaction batches rather than only to records processed after the crossing.

ETL Implementation Patterns

Schema enforcement begins at ingestion. Model the contract with Pydantic v2 so type constraints, required fields, and custom validators for temporal windows and currency codes fail fast at the boundary instead of surfacing as a bad accrual weeks later. The validator below rejects an inverted interval and an unordered tier array — two of the most common causes of silent mis-rating.

from datetime import date
from decimal import Decimal
from pydantic import BaseModel, model_validator

class Tier(BaseModel):
    lower_bound: Decimal
    upper_bound: Decimal | None = None
    rate: Decimal

class AgreementVersion(BaseModel):
    agreement_id: str
    version_sequence: int
    effective_start: date
    effective_end: date
    currency: str
    tiers: list[Tier]

    @model_validator(mode="after")
    def _check(self) -> "AgreementVersion":
        if self.effective_end <= self.effective_start:
            raise ValueError("effective_end must be after effective_start (half-open interval)")
        bounds = [t.lower_bound for t in self.tiers]
        if bounds != sorted(bounds):
            raise ValueError("tiers must be ordered by ascending lower_bound")
        return self

Ingestion writes through idempotent upserts keyed on (agreement_id, version_hash). Because the hash is deterministic over canonicalized terms, re-loading an unchanged contract is a no-op and a genuine amendment lands as a new version row rather than overwriting history — which is exactly what the data ingestion and normalization pipelines stage needs to keep its loads replay-safe. Schema evolution follows semantic versioning: backward-compatible additions ship with a dual-read compatibility layer during the migration window so legacy contracts and modernized agreements coexist without a forced structural migration. Aligning the published contract with the JSON Schema specification lets vendor portals, BI tools, and the reconciliation engine consume one definition without per-consumer transformation.

Drift Detection and Validation

A schema is only trustworthy if the live data keeps matching it. Drift detection runs continuous validation of incoming transaction streams against active agreement versions, comparing expected accruals to actual payouts and flagging statistical deviations: expired versions still generating claims, rate overrides outside the contracted band, missing tier boundaries, or a sudden volume distribution that no active tier covers. Anything that fails validation is quarantined — written to a holding table with its mismatch reason intact and an exception ticket raised — rather than discarded, so no record is lost and none is silently accrued.

Catching these before month-end is what preserves margin integrity: a proactive EXPIRED_VERSION flag is a remediation task, while the same drift discovered after close is a restated accrual and an audit finding.

Fallback and Dispute Routing

When a transaction matches no active agreement version, the schema’s fallback_policy decides its fate deterministically — hold for manual review, default_pool to a declared default-rate accrual, or reject outright — and a dispute_routing field maps the case to the owning queue. Encoding the policy in the agreement rather than in pipeline code means unmatched transactions never stall settlement and never default to an analyst’s discretion. The escalation, adjudication, and audit-log mechanics of those queues are owned by the fallback routing logic cluster; the schema’s job is to name the correct policy and emit an audit entry that records which version was searched, why no match was found, and where the record was routed.

Security and Access Boundaries

Commercial terms carry sensitive pricing subject to internal compliance controls, so confidentiality is encoded at the schema level rather than bolted on at the endpoint. Field-level encryption protects rate, cap_amount, and vendor_contact at rest, and role-based access control (RBAC) tags travel with every entity so authorization is enforced per field: vendor-facing portals expose only that vendor’s contracted terms and settlement statements; ETL developers can deploy schema manifests but cannot mutate posted accruals; trade finance analysts can adjudicate exceptions and export audit trails but cannot edit a tier rate. Encryption keys and portal credentials rotate on a fixed schedule, and because the agreement version is immutable and signed, any retroactive edit to a sensitive field surfaces as a hash mismatch in reconciliation rather than passing unnoticed. Treating the agreement schema as a living, version-controlled, access-governed artifact — not a static database table — is what yields deterministic reconciliation, fewer manual exceptions, and a financial trail that survives audit.

Frequently Asked Questions

Why version the agreement instead of editing it in place? A claim references the exact terms in force when the sale occurred. If you edit an agreement in place, every historical claim silently re-validates under the new terms, which corrupts closed periods and destroys the audit trail. Versioning with an immutable version_hash pins each claim to a specific version_sequence, so an amendment creates a new row and history stays attributable to the rates that actually governed it.

What happens when a retroactive tier is crossed mid-cycle? With rate_type set to retroactive, retroactive_rebate re-rates the entire cumulative volume at the newly earned rate and the settlement layer posts the delta versus the prior accrual rather than a fresh full accrual. The original accrual record is preserved and a new timestamped record captures the re-rate, so the audit trail shows both the pre- and post-crossing state.

Why store rates as strings and parse with decimal.Decimal? Float arithmetic accumulates non-reproducible rounding drift that is immaterial per line but material across a quarter-end run, and it differs across platforms. Because IFRS 15 / ASC 606 treatment requires accruals reproducible to the cent and the version_hash must be identical on every environment, rates are held as strings at rest and parsed into Decimal with a fixed context and explicit ROUND_HALF_UP at evaluation.

How do two overlapping agreement versions get resolved deterministically? The resolver ranks matching versions by a declared precedence key — scope specificity first, then version_sequence — never by row order or insertion time. This guarantees the same transaction resolves to the same version on every run, which is what prevents double-counting and makes the eligibility decision reproducible during an audit.

Related

• Eligibility Rule Framework — how the predicates this schema stores are evaluated into qualify/disqualify decisions.
• Payout Structure Modeling — turning the tiers defined here into accruals, caps, and settlement cadence.
• Fallback Routing Logic — the adjudication queues that act on this schema’s fallback_policy.
• How to Map Vendor Rebate Tiers in JSON — the concrete JSON layout for progressive, flat, and retroactive tiers.
• Data Ingestion & Normalization Pipelines — the upstream stage that loads agreement versions idempotently.

Up one level: Core Architecture & Promotion Mapping