Date Window Alignment Checks

Temporal misalignment is the single largest driver of accrual leakage, overpayment, and audit exposure in vendor rebate and trade promotion reconciliation. Date window alignment is the sub-problem of proving — deterministically and reproducibly — that every sales transaction, shipment record, and claim line falls inside the contractual promotion period before any money is accrued. It is the temporal predicate layer of the broader claim validation rule engine: the parent framework decides whether a claim is valid, but it cannot do so until this layer establishes a single, unambiguous timeline against which every other rule executes. Without rigorous boundary enforcement, volume-based rebates compound incorrectly, off-promo sales trigger false positives, and finance teams face material restatements during quarterly close.

This page specifies the entity schema for promotion windows, the conditional logic that encodes inclusive and exclusive boundaries, the settlement implications of proration at period edges, the ETL patterns that make alignment idempotent, and the drift-detection and dispute-routing controls that keep the timeline auditable under SOX.

Positioning within the rule engine

Date window alignment runs first in the evaluation order because every downstream rule depends on the timeline it produces. Identity resolution, channel scoping, and quantitative checks all assume that a transaction’s transaction_date has already been normalized and tested against a canonical window. When this layer is skipped or implemented loosely, errors cascade: misaligned dates inflate qualifying volume, which corrupts tier attainment, which over-accrues liability.

Concretely, this layer feeds three sibling subsystems. Volume Threshold Validation must execute strictly after window verification, because a transaction that falls outside the promotion window but is incorrectly aggregated into qualifying volume will systematically overpay tiered rebates. SKU Mapping & Deduplication depends on verified windows so that pre-promo inventory is never collapsed into promo-period sell-through during canonicalization. And Scoring & Confidence Models consume the boundary-zone flags this layer emits, assigning probabilistic weights to transactions that land inside a configured tolerance band rather than auto-rejecting them. The contractual definitions that govern these windows originate upstream in agreement schema design and are scoped by the eligibility rule framework; raw timestamps arrive from the data ingestion normalization pipelines.

Entity topology and schema specification

A promotion window is a versioned entity, not a pair of loose date strings. Every window carries explicit boundary semantics, a timezone anchor, and a configuration hash so that two reconciliation runs over the same inputs produce byte-identical results. The table below specifies the canonical fields.

Boundary conditions must be explicit in the configuration layer, never implied by code. Most trade agreements specify inclusive start dates and exclusive end dates, but vendor contracts frequently deviate based on ship_to versus bill_to recognition policies — recognition_basis makes that choice data-driven rather than a hardcoded branch. The config_hash is the linchpin of reproducibility: it is recomputed on ingest and stored alongside every accrual so auditors can prove which window definition produced a given payout. Adherence to ISO 8601 date and time formatting at the boundary eliminates cross-border parsing ambiguity and guarantees deterministic comparisons across distributed systems.

Conditional logic and rule integration

The pipeline ingests heterogeneous date formats from POS feeds, EDI 810 invoices, vendor claim portals, and internal ERP systems. Before any business logic executes, strict temporal normalization is mandatory: every timestamp is parsed into UTC, stripped of ambiguous regional formats, and truncated to a consistent day boundary aligned to the contractual fiscal calendar.

import polars as pl

def normalize_promo_dates(df: pl.DataFrame) -> pl.DataFrame:
    """
    Standardize promotion windows and transaction dates to UTC midnight
    boundaries before downstream eligibility evaluation.

    Requires promo_start, promo_end, and transaction_date columns as
    strings in "%Y-%m-%d %H:%M:%S" format (already UTC or timezone-naive
    inputs that should be treated as UTC).
    """
    fmt = "%Y-%m-%d %H:%M:%S"
    return df.with_columns([
        pl.col(c)
            .str.to_datetime(fmt, strict=True)
            .dt.replace_time_zone("UTC")
            .dt.truncate("1d")
        for c in ("promo_start", "promo_end", "transaction_date")
    ])

Boundary inclusion is the most error-prone predicate in the entire rule engine. The window test must honour start_inclusive and end_inclusive independently — a naive start <= txn <= end collapses both bounds to inclusive and double-counts any transaction settled exactly at the end-of-window midnight. The eligibility predicate is best expressed as an explicit comparison driven by the schema flags rather than a single BETWEEN:

def in_window(txn, win) -> bool:
    lo_ok = txn >= win.promo_start if win.start_inclusive else txn > win.promo_start
    hi_ok = txn <= win.promo_end if win.end_inclusive else txn < win.promo_end
    return lo_ok and hi_ok

Channel and product scopes layer on top of the temporal predicate, but the temporal predicate is always evaluated first and short-circuits the rest of the chain when it fails. This ordering is what lets the engine treat the date window as a primary key for caching: a transaction that fails the window test never reaches volume or SKU logic, so those subsystems never see off-promo noise.

Financial settlement layer

Temporal boundaries directly shape money. When a contract amendment lands mid-cycle — a new agreement_version with a shifted promo_end — volume attribution must be split at the exact amendment timestamp, and the rebate owed on each side computed against the version that was in force at the moment of the transaction. All such arithmetic uses decimal.Decimal; floating-point accumulation across thousands of line items introduces drift that fails audit reconciliation to the cent.

from decimal import Decimal, ROUND_HALF_UP

def prorated_accrual(units: int, rate: Decimal, days_in_window: int,
                     eligible_days: int) -> Decimal:
    """Accrue only the fraction of the window a transaction is eligible for."""
    fraction = Decimal(eligible_days) / Decimal(days_in_window)
    gross = Decimal(units) * rate * fraction
    return gross.quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)

Proration at period edges is where recognition basis bites hardest. A shipment recognized on ship_to may fall inside the window while its invoice — recognized on bill_to — falls outside it. The settlement layer resolves this by accruing against the timestamp named in recognition_basis, and never silently substituting one for the other. Currency is normalized to a single settlement currency before accrual, using the contractual FX rate effective on the recognition date, again as Decimal. Tier boundary math itself lives in Volume Threshold Validation; this layer’s settlement responsibility is solely to deliver the correct eligible quantity and the correct effective rate for the window that actually applied.

ETL implementation patterns

Date window alignment is implemented as an idempotent transform: re-running it over the same inputs and the same config_hash must upsert identical rows. Pydantic v2 models validate windows on ingest, enforce the boundary and ordering constraints declaratively, and compute the deterministic hash.

from datetime import datetime
from decimal import Decimal
from enum import Enum
from hashlib import sha256
from pydantic import BaseModel, Field, model_validator, computed_field


class RecognitionBasis(str, Enum):
    ship_to = "ship_to"
    bill_to = "bill_to"
    invoice = "invoice"


class PromoWindow(BaseModel):
    promo_id: str
    agreement_version: int = Field(ge=1)
    promo_start: datetime
    promo_end: datetime
    start_inclusive: bool = True
    end_inclusive: bool = False
    recognition_basis: RecognitionBasis = RecognitionBasis.invoice
    source_tz: str
    tolerance_hours: int = Field(default=0, ge=0)
    fiscal_calendar_id: str | None = None

    @model_validator(mode="after")
    def _ordered(self) -> "PromoWindow":
        if self.promo_end <= self.promo_start:
            raise ValueError("promo_end must be strictly after promo_start")
        if self.promo_start.tzinfo is None or self.promo_end.tzinfo is None:
            raise ValueError("window boundaries must be timezone-aware (UTC)")
        return self

    @computed_field
    @property
    def config_hash(self) -> str:
        payload = "|".join(str(v) for v in (
            self.promo_id, self.agreement_version,
            self.promo_start.isoformat(), self.promo_end.isoformat(),
            self.start_inclusive, self.end_inclusive,
            self.recognition_basis.value, self.tolerance_hours,
            self.fiscal_calendar_id,
        ))
        return sha256(payload.encode()).hexdigest()

The upsert key is (promo_id, agreement_version, config_hash). Because the hash folds in every boundary-affecting field, a re-ingested window with unchanged terms maps to the same row and the operation is a no-op; a window whose terms changed produces a new hash and a new immutable record, preserving the prior version for replay. Schema evolution follows the same discipline as agreement schema design: new optional fields default safely, and any field that affects accrual is versioned rather than mutated in place. The step-by-step join logic that matches normalized transactions to these windows is covered in aligning promotion start/end dates with sales data.

Drift detection and validation

Even a correct window definition degrades when upstream feeds shift. Drift detection monitors the distribution of transaction timestamps relative to each window and raises a quarantine ticket when the pattern deviates from the historical baseline. A sudden spike of transactions clustered in the final hour before promo_end, or a rise in records landing inside the tolerance band, signals a settlement-timing change at the retailer that must be investigated before it pollutes accruals.

Concretely, the validation stage emits three signals per batch: the share of transactions outside the window, the share inside the tolerance band, and the count of records whose source_tz could not be resolved to UTC. Each is compared against a rolling baseline; a breach routes the batch to a quarantine table with an explicit mismatch code (TZ_DRIFT, FISCAL_MISMATCH, POS_LATE_SETTLEMENT) rather than failing the whole pipeline. Records in the tolerance band are not auto-rejected — they are handed to Scoring & Confidence Models, which weigh historical vendor behaviour and source reliability to decide whether the boundary-zone transaction is likely genuine.

Fallback and dispute routing

Not all temporal discrepancies resolve programmatically. When a window conflicts with master agreement terms, or a feed contains irreconcilable timestamp gaps, the pipeline triggers fallback routing rather than blocking the close. The default policy is conservative: ambiguous transactions are held at a zero accrual pending resolution, never accrued optimistically. Fallback chains prioritize contractual override documents, historical precedent, and vendor-approved exception logs, and they inherit the same escalation contract as the broader fallback routing logic used across the reconciliation platform.

Unresolved mismatches route to a structured exception queue where vendor managers and trade finance analysts collaborate to adjust accruals or request corrected EDI submissions. Every override — a granted grace period, a manual window extension, a recognition-basis correction — is written to an immutable audit log with the operator identity, the prior and new config_hash, and a reason code. This decoupling of hard failures from soft exceptions keeps the accrual cycle moving while preserving a defensible SOX trail. Fallback logic must never silently approve a misaligned claim; it either resolves the record against a documented authority or quarantines it.

Security and access boundaries

Window definitions are financial control data and are protected accordingly. Contract timestamps and recognition policies are tagged for role-based access: trade finance analysts and vendor managers may propose window edits, but only a controller role may approve a new agreement_version, and the approval is enforced as a four-eyes check in the configuration store. Edits flow through a configuration-as-code path — versioned in Git with pull-request review — so that no boundary changes by direct database mutation.

Field-level controls protect the most sensitive attributes. Vendor-specific tolerance terms and contractual FX rates are encrypted at rest, and the secrets used to sign the immutable audit log are rotated on a fixed schedule. Every read and write of a window record is logged with actor, timestamp, and config_hash, giving auditors a complete lineage from a posted accrual back to the exact, signed window definition that produced it.

Frequently asked questions

Related

• Claim Validation & Rule Engine Configuration — the parent framework this temporal layer feeds.
• Volume Threshold Validation — runs after window verification to test qualifying volume.
• SKU Mapping & Deduplication — canonicalizes products within verified date ranges.
• Scoring & Confidence Models — adjudicates tolerance-band transactions.
• Aligning promotion start/end dates with sales data — step-by-step join implementation.

Up one level: Claim Validation & Rule Engine Configuration.