How to Map Vendor Rebate Tiers in JSON

Reconciliation breaks most often when a negotiated tier table — the volume breakpoints, rate escalators, and rate_type semantics in the signed agreement — is translated into machine logic that disagrees with the contract by a fraction of a cent or a single boundary unit. This page solves one concrete task: encoding a vendor’s tiered rebate structure as deterministic, validated JSON that a settlement engine can evaluate the same way on every run. It is a hands-on companion to agreement schema design, which owns the broader contract-to-record model; here we focus only on the tier array — its layout, its ordering guarantees, and the validation that keeps it from silently mis-rating a payout.

Prerequisites

Before mapping a tier table, confirm the following are in place:

  • An active agreement version to attach to. Tiers do not exist standalone — they belong to a specific agreement_id and version_sequence produced by the agreement schema design stage. Mapping tiers without a governing version hash means claims cannot be pinned to the terms in force when the sale occurred.
  • Python 3.11+ with pydantic>=2.5. All validation snippets below use Pydantic v2 model validators. Install with pip install "pydantic>=2.5".
  • decimal.Decimal discipline. Every rate and threshold is parsed via decimal.Decimal, never a JSON float. This is non-negotiable for accruals that must reconcile to the cent.
  • A product taxonomy reference. Scope filters should resolve against an authoritative identifier system such as GS1 product identification standards so SKU and channel scopes mean the same thing in the ERP, the WMS, and the vendor portal.
  • A published JSON Schema target. Validate the serialized contract against the JSON Schema specification so vendor portals, BI tools, and the reconciliation engine consume one definition without per-consumer transformation.
  • Write access scoped to draft agreements only. Tier edits land on draft versions; posted accruals are immutable.

Step-by-Step Implementation

Step 1 — Anchor the tiers to an immutable agreement container

At the root, declare the identifiers that bind the tier table to the master contract registry and fix its temporal boundaries: agreement_id, version, effective_date_range, currency, and calculation_frequency. The tiers array is nested inside this container so every tier inherits the version hash and effective window.

json
{
  "agreement_id": "AGR-20260101-VENDOR42",
  "version": "2.1.0",
  "effective_date_range": {
    "start": "2026-01-01T00:00:00Z",
    "end": "2026-12-31T23:59:59Z"
  },
  "currency": "USD",
  "calculation_frequency": "MONTHLY",
  "tiers": []
}

Validation check: assert that effective_date_range.end is strictly after start and that currency is a valid ISO 4217 code before accepting any tiers. A container with an inverted interval must be rejected at the boundary, not at month-end.

Step 2 — Encode each tier band with explicit boundaries and rate semantics

Each tier object carries an ascending, non-overlapping band plus the semantics the modeling layer needs. Store rate as a decimal string, never a JSON number — JSON numbers are double-precision floats, and string-plus-Decimal parsing eliminates the rounding drift that compounds across millions of line items.

json
{
  "tiers": [
    {
      "min_threshold": "0",
      "max_threshold": "9999",
      "rate": "0.02",
      "rate_type": "flat",
      "scope": {"channel": ["GROCERY_RETAIL"]}
    },
    {
      "min_threshold": "10000",
      "max_threshold": "24999",
      "rate": "0.035",
      "rate_type": "incremental",
      "scope": {"channel": ["GROCERY_RETAIL"]}
    },
    {
      "min_threshold": "25000",
      "max_threshold": null,
      "rate": "0.05",
      "rate_type": "retroactive",
      "scope": {"channel": ["GROCERY_RETAIL"]}
    }
  ]
}

The fields per tier:

Field Type Meaning
min_threshold str → Decimal Minimum volume, net sales, or unit count to qualify
max_threshold str → Decimal | null Optional ceiling that caps tier applicability; null = open top
rate str → Decimal Rebate percentage or fixed amount as a decimal string
rate_type enum flat, incremental, or retroactive
scope object Product, channel, or geography filters

Validation check: confirm the min_threshold values are strictly ascending and that no band overlaps the next (tiers[i].max_threshold < tiers[i+1].min_threshold). Unordered or overlapping bands make rate application ambiguous.

How a 30,000-unit volume is rated under flat, incremental, and retroactive rate_type semantics A number line marks three tier bands: Band A from 0 to 9,999 at 2 percent, Band B from 10,000 to 24,999 at 3.5 percent, and Band C from 25,000 up at 5 percent. A volume of 30,000 units is rated three ways. Flat and incremental rate_type both sum the marginal bands — 10,000 units at 2 percent plus 15,000 at 3.5 percent plus 5,000 at 5 percent — for a rebate of 975. Retroactive rate_type re-rates all 30,000 units at the highest earned 5 percent rate for a rebate of 1,500, a 525-unit delta that must be posted against the prior accrual. Rating one 30,000-unit volume under each rate_type volume = 30,000 Band A 0–9,999 @ 2% Band B 10,000–24,999 @ 3.5% Band C 25,000+ @ 5% 0 10,000 25,000 rebate flat — marginal per band 10,000 @ 2% 15,000 @ 3.5% 5k @ 5% 975 incremental — sum of band×rate 10,000 @ 2% 15,000 @ 3.5% 5k @ 5% 975 retroactive — highest rate on all units all 30,000 units re-rated @ 5% 1,500 Flat and incremental both sum the marginal bands; only retroactive re-rates the whole volume at the highest earned rate. Crossing into Band C lifts the result from 975 to 1,500 — the engine posts the 525 delta against the prior accrual, not a fresh full accrual.

Step 3 — Attach the tier scope to a parsable predicate, not free text

Tier qualification rarely depends on volume alone. Channel, SKU family, and the promotional window are encoded as machine-parsable objects so the eligibility rule framework can validate types and reject malformed payloads before they reach settlement.

json
{
  "eligibility": {
    "product_scope": {
      "type": "taxonomy_path",
      "values": ["CPG/Beverage/Carbonated/Soda"]
    },
    "channel_scope": ["DTC", "GROCERY_RETAIL"],
    "temporal_window": {
      "start": "2026-01-01T00:00:00Z",
      "end": "2026-03-31T23:59:59Z",
      "timezone": "UTC"
    }
  }
}

Deterministic precedence resolves overlapping scopes: explicit UPC/EAN beats brand family, which beats category path, which beats the global default. When multiple tiers intersect, the engine selects the highest qualified rate unless the contract caps retroactive stacking.

Validation check: every product_scope.values entry must resolve to a known taxonomy node, and temporal_window must fall inside the agreement’s effective_date_range. A scope that extends past the contract window is drift, not a tier.

Step 4 — Validate the structure with a Pydantic v2 model

Enforce the schema at ingestion so a bad tier table fails fast at the boundary instead of surfacing as a bad accrual weeks later. The validator rejects the two most common defects — float-typed rates and unordered bands.

python
from decimal import Decimal
from enum import Enum
from pydantic import BaseModel, field_validator, model_validator

class RateType(str, Enum):
    flat = "flat"
    incremental = "incremental"
    retroactive = "retroactive"

class Tier(BaseModel):
    min_threshold: Decimal
    max_threshold: Decimal | None = None
    rate: Decimal
    rate_type: RateType

    @field_validator("rate", "min_threshold", "max_threshold", mode="before")
    @classmethod
    def _reject_float(cls, v):
        if isinstance(v, float):
            raise ValueError("rates and thresholds must be strings, not floats")
        return v

class TierTable(BaseModel):
    agreement_id: str
    version: str
    tiers: list[Tier]

    @model_validator(mode="after")
    def _check_ordering(self) -> "TierTable":
        lowers = [t.min_threshold for t in self.tiers]
        if lowers != sorted(lowers):
            raise ValueError("tiers must be ordered by ascending min_threshold")
        for a, b in zip(self.tiers, self.tiers[1:]):
            if a.max_threshold is None or a.max_threshold >= b.min_threshold:
                raise ValueError(f"overlapping band at {a.min_threshold}")
        return self

Validation check: load every incoming tier table through TierTable.model_validate_json(payload). A raised ValidationError routes the payload to quarantine; a clean parse lets it proceed to rating.

Step 5 — Apply the correct payout math per rate_type

The mathematical behavior of a tier depends entirely on its rate_type, and getting it wrong is the most common source of payout disputes. The settlement math itself is owned by payout structure modeling; the tier table’s job is to declare which behavior applies.

  • Flat — the tier rate applies only to units inside the current band; units below min_threshold earn the prior band’s rate.
  • Incremental (marginal) — the total rebate is the sum of each band’s volume times that band’s rate.
  • Retroactive — once the cumulative threshold is crossed, the highest qualified rate applies to all eligible units in the window, which forces a recalculation of prior claims when the breakpoint is crossed mid-cycle.
python
from decimal import Decimal, ROUND_HALF_UP

def rate_tier_table(volume: Decimal, tiers: list[Tier]) -> Decimal:
    """Dispatch on the top qualifying tier's rate_type."""
    qualifying = [t for t in tiers if volume >= t.min_threshold]
    if not qualifying:
        return Decimal("0")
    top = qualifying[-1]
    if top.rate_type == RateType.retroactive:
        total = volume * top.rate
    else:  # flat / incremental sum each marginal band
        total = Decimal("0")
        for t in qualifying:
            upper = min(volume, t.max_threshold or volume)
            total += (upper - t.min_threshold) * t.rate
    return total.quantize(Decimal("0.0001"), rounding=ROUND_HALF_UP)

Validation check: for a retroactive crossing, assert the engine emits an adjustment entry against the prior accrual rather than a fresh full accrual, and flags the event for trade finance review. Track cumulative volume keyed on (agreement_id, scope_hash, period) so the running aggregate is replay-safe.

Common Failure Modes and Fixes

1. Floating-point drift from numeric rates. Storing "rate": 0.035 as a JSON number reintroduces double-precision rounding that diverges across platforms and accumulates across a quarter-end run. Fix: keep rates as strings at rest and parse via Decimal; the _reject_float validator in Step 4 blocks the regression at ingestion.

2. Overlapping or unordered bands. A tier whose max_threshold meets or exceeds the next tier’s min_threshold makes rate selection non-deterministic. Fix: enforce the _check_ordering model validator; reject the payload rather than guess which band wins.

3. Missing or stale version hash. Editing a tier in place without bumping version silently re-rates closed periods under new terms. Fix: treat tier edits as a new agreement version — every change produces a fresh version_sequence and hash, and claims pin to the version in force at sale time.

4. Retroactive crossing not back-applied. When a retroactive breakpoint is crossed mid-cycle, rating only the new units underpays the vendor and triggers a dispute. Fix: on every crossing, recompute the full cumulative volume at the new rate and post the delta versus the prior accrual; preserve the original record for audit.

5. Scope creep past the contracted window. A temporal_window that extends beyond effective_date_range, or a product_scope broader than negotiated, accrues claims the contract never authorized. Fix: validate scope containment at ingestion and route violations to the quarantine queue with an exception ticket rather than auto-settling. Unmatched claims fall through to fallback routing logic with an explicit fallback_rate (typically "0.0" or a contractual floor) while the original payload is preserved for manual adjudication.

Operational Checklist

Frequently Asked Questions

Why store rebate rates as strings instead of JSON numbers? JSON numbers are double-precision floats. Float arithmetic accumulates non-reproducible rounding drift that is immaterial per line but material across a quarter-end run, and it differs across platforms. Holding rates as decimal strings at rest and parsing them with decimal.Decimal and an explicit ROUND_HALF_UP context keeps accruals reproducible to the cent and keeps the version hash identical across environments.

What happens when a retroactive tier is crossed mid-cycle? The highest qualified rate re-rates the entire cumulative volume for the window, not just the units above the breakpoint. The pipeline recomputes prior claims keyed on (agreement_id, scope_hash, period), posts the delta versus the existing accrual, and flags the crossing for trade finance review. The original accrual record is preserved so the audit trail shows both pre- and post-crossing state.

How do flat, incremental, and retroactive tiers differ in the math? Flat rates only the units inside the current band at that band’s rate. Incremental sums each band’s volume times its own rate. Retroactive applies one rate — the highest earned — to all qualifying volume once the breakpoint is crossed. Because the dispute risk is highest here, rate_type must be stored explicitly per tier rather than inferred.

How do overlapping tier scopes resolve to a single rate? The engine applies a specificity hierarchy — explicit UPC/EAN beats brand family, which beats category path, which beats the global default — and then selects the highest qualified rate unless the contract caps retroactive stacking. Encoding scope as parsable predicate data rather than free text is what makes this resolution deterministic and reproducible during an audit.

Up one level: Agreement Schema Design

Back to