SC ReconciliationReconciliation Automation
  1. Home
  2. Exceptions

Exception Handling & Discrepancy Resolution

Every record a matching and reconciliation engine cannot clear on its own has to land somewhere deterministic. A record that fails the exact tier, drifts outside a tolerance window, scores below the fuzzy threshold, or simply arrives malformed does not get to disappear into a spreadsheet someone “will look at later” — it has to be classified, given a fate, and tracked until that fate is executed. This is the engineering problem this subsystem solves: build a receiving service for every unmatched, out-of-tolerance, malformed, or transiently-failed record, and guarantee that each one is classified, routed, resolved, written back to the source ERP, and logged with the same rigor as a successful match. An exception queue that silently grows, or one where two identical variances get two different outcomes depending on who happened to click first, is not an edge case — it is a control failure.

The subsystem described here sits downstream of the tiered matcher and upstream of the general ledger. It receives an envelope the moment a tier gives up on a record, assigns it a reason code and a priority score, routes it into exactly one of a small number of deterministic lanes, and does not consider the record closed until an audit-log entry exists proving what happened and why. The deep implementation detail for each stage lives in the sections below and in the dedicated guides on exception queue design and triage, dead-letter queues and retry orchestration, discrepancy root-cause classification, and variance write-back and ERP adjustment posting. This page is the architectural spine connecting them.

Exception handling pipeline overview Left to right: the matching engine emits four exception types (unmatched, out-of-tolerance, malformed payload, transient failure) into a classify stage that assigns a reason code and priority score, then a route stage that deterministically splits records into auto-adjust, manual review, or retry-to-dead-letter lanes, then a resolve stage that applies the outcome, then an ERP write-back stage that posts the adjustment using an idempotent upsert key. An append-only audit log spans the bottom, capturing a state transition written from every stage. Matching engine Unmatched Out-of-tolerance Malformed payload Transient failure Classify assign reason code & priority score Route Auto-adjust Manual review Retry / dead letter deterministic by reason code Resolve adjustment or retry outcome ERP write-back Adjustment posted Idempotent upsert key Append-only audit log · a state transition written from every stage canonical key · classification · reason code · routing decision · resolver identity · UTC timestamp

Pipeline Architecture & State Management

The exception subsystem is not a queue bolted onto the matcher’s failure path — it is its own small pipeline with the same idempotency and replay guarantees the rest of the reconciliation platform relies on. The moment any tier gives up on a record, it opens an exception envelope rather than logging a warning and moving on. That envelope carries the canonical key the matching engine already computed, so an exception and its eventual resolution can always be traced back to the exact source records that produced it, no matter how many retries or reclassifications happen in between.

State is explicit and finite. Every envelope moves through a small, named set of states — new, triaged, in review, retrying, resolved, or aged out — and the pipeline never allows an implicit or undocumented state. A record is never “kind of resolved” or “probably fine”; it is in exactly one state, and every change of state is itself a row in the audit log, not an in-place field update. This is the same append-only discipline the matching engine’s audit trail applies to matched records, extended to cover the records that did not match cleanly.

PYTHON
import logging
from dataclasses import dataclass, field
from datetime import datetime, timezone
from enum import Enum

logger = logging.getLogger("exceptions.pipeline")


class ExceptionState(str, Enum):
    NEW = "NEW"
    TRIAGED = "TRIAGED"
    IN_REVIEW = "IN_REVIEW"
    RETRYING = "RETRYING"
    RESOLVED = "RESOLVED"
    AGED_OUT = "AGED_OUT"


@dataclass
class StateTransition:
    """One immutable audit-log row for a single exception envelope."""
    canonical_key: str
    prior_state: ExceptionState
    new_state: ExceptionState
    reason_code: str
    actor: str  # service identity or operator id
    occurred_at: datetime = field(default_factory=lambda: datetime.now(timezone.utc))


def transition(
    canonical_key: str,
    prior_state: ExceptionState,
    new_state: ExceptionState,
    reason_code: str,
    actor: str,
) -> StateTransition:
    """Advance one exception's lifecycle state and emit its audit row.

    The transition is a pure function of its inputs; nothing here mutates
    a prior audit row, matching the append-only guarantee the write-back
    stage depends on.
    """
    if prior_state == new_state:
        logger.warning("no-op transition for %s at state %s", canonical_key, prior_state)
    row = StateTransition(canonical_key, prior_state, new_state, reason_code, actor)
    logger.info("%s: %s -> %s (%s)", canonical_key, prior_state.value, new_state.value, reason_code)
    return row

Queue partitioning by state, not by age alone, keeps the operational surface manageable. A single flat queue mixing a two-minute-old transient failure with a two-week-old high-value variance forces every consumer to re-derive priority on every poll. Instead, each state owns its own physical or logical partition — a retry lane with its own backoff scheduler, a manual-review lane sorted by priority score, an aged-out lane that exists purely for escalation and reporting. The mechanics of sizing and indexing these partitions so triage stays fast at volume are covered in exception queue design and triage.

Idempotent re-entry matters as much here as it does at ingestion. A record can legitimately re-enter the exception pipeline more than once — a retried transient failure that fails again, a resolved variance that a later correction re-opens. The envelope’s canonical key plus its exception_id let the pipeline recognize “this is the same underlying issue recurring” rather than creating a duplicate case, which would otherwise double the reported queue depth and confuse anyone trying to reconstruct what actually happened to a given record.

Canonical Data Mapping & Type Coercion

Every stage downstream of the matcher operates on one typed structure: the exception envelope. Defining it precisely — and defining a closed, versioned set of reason codes rather than free-text strings — is what turns “the reconciliation engine failed on this record” into something a router, a dashboard, and an auditor can all reason about identically.

The envelope separates what kind of failure occurred from why it occurred. ExceptionType is a small, stable enum (unmatched, out-of-tolerance, malformed, transient failure) that maps directly to which tier or stage emitted the record. ReasonCode is a larger, still-closed enum that captures the specific cause — a missing FX rate, a fuzzy score below threshold, a retry cap exceeded. Keeping these as two separate closed vocabularies, rather than one long free-text field, is what makes discrepancy root-cause classification tractable at scale: you can group, count, and trend by reason code without ever running text analysis on a description field.

PYTHON
import logging
from dataclasses import dataclass, field
from datetime import datetime, timezone
from decimal import Decimal
from enum import Enum

logger = logging.getLogger("exceptions.envelope")


class ExceptionType(str, Enum):
    UNMATCHED = "UNMATCHED"                  # no candidate cleared any tier
    OUT_OF_TOLERANCE = "OUT_OF_TOLERANCE"     # keys matched, value outside window
    MALFORMED = "MALFORMED"                   # failed schema validation / coercion
    TRANSIENT_FAILURE = "TRANSIENT_FAILURE"   # dependency unavailable, retryable


class ReasonCode(str, Enum):
    NO_CANDIDATE_FOUND = "NO_CANDIDATE_FOUND"
    QTY_OUT_OF_TOLERANCE = "QTY_OUT_OF_TOLERANCE"
    PRICE_OUT_OF_TOLERANCE = "PRICE_OUT_OF_TOLERANCE"
    SCHEMA_COERCION_FAILURE = "SCHEMA_COERCION_FAILURE"
    FX_RATE_MISSING = "FX_RATE_MISSING"
    FUZZY_BELOW_THRESHOLD = "FUZZY_BELOW_THRESHOLD"
    DUPLICATE_SUSPECTED = "DUPLICATE_SUSPECTED"
    LATE_ARRIVAL = "LATE_ARRIVAL"
    RETRY_CAP_EXCEEDED = "RETRY_CAP_EXCEEDED"


@dataclass
class ExceptionEnvelope:
    """The canonical unit every downstream stage reads and writes.

    One envelope wraps one unresolved record for its entire lifecycle,
    from the tier that emitted it through resolution and write-back.
    """
    exception_id: str
    canonical_key: str          # same identity the matching engine used
    exception_type: ExceptionType
    reason_code: ReasonCode
    source_tier: str            # "exact" | "tolerance" | "fuzzy" | "ingest"
    variance_value: Decimal | None
    currency: str | None
    attempt_count: int = 0
    priority_score: float = 0.0
    state: str = "NEW"
    first_seen_at: datetime = field(default_factory=lambda: datetime.now(timezone.utc))
    last_seen_at: datetime = field(default_factory=lambda: datetime.now(timezone.utc))
    resolver_identity: str | None = None
    resolution_note: str | None = None


def open_envelope(
    canonical_key: str,
    exception_type: ExceptionType,
    reason_code: ReasonCode,
    source_tier: str,
    variance_value: Decimal | None = None,
    currency: str | None = None,
) -> ExceptionEnvelope:
    """Construct a fresh envelope the moment a tier cannot clear a record."""
    envelope = ExceptionEnvelope(
        exception_id=f"EXC-{canonical_key[:12]}",
        canonical_key=canonical_key,
        exception_type=exception_type,
        reason_code=reason_code,
        source_tier=source_tier,
        variance_value=variance_value,
        currency=currency,
    )
    logger.info("opened %s type=%s reason=%s tier=%s",
                envelope.exception_id, exception_type.value, reason_code.value, source_tier)
    return envelope

Type coercion failures are themselves an exception type, not a crash. A row that cannot be coerced to a canonical schema — a quantity field with garbage characters, a currency code that does not exist — never reaches the matcher at all; it opens a MALFORMED envelope with SCHEMA_COERCION_FAILURE at the mapping boundary, before matching logic ever runs. This keeps the malformed-payload volume visible as its own signal rather than mixed into the matcher’s unmatched count, which would make the two failure modes impossible to distinguish on a dashboard.

Money and time stay strict inside the envelope, too. variance_value is a Decimal, never a float, because a priority score computed from a rounding-corrupted variance can misroute a genuinely material discrepancy into the low-priority lane. Timestamps are UTC-normalized at the moment the envelope opens, so an envelope’s age is always computed against the same clock the retry scheduler and the queue-age SLA use — a mismatch here is exactly the kind of silent bug that makes an aging alert fire late.

Matching Logic & Exception Handling

Upstream, each tier of the matcher treats “cannot clear this record” as a first-class outcome rather than a fallthrough. The exact tier emits UNMATCHED when no candidate joins on canonical keys. The tolerance tier emits OUT_OF_TOLERANCE when a key matches but the quantity or price deviation exceeds the configured window. The fuzzy tier emits UNMATCHED with a FUZZY_BELOW_THRESHOLD reason when its best candidate scores below τ. None of these emissions are exceptions in the programming-language sense — they are ordinary, expected outputs of a matcher that is working correctly, which is precisely why they need a downstream service built for volume rather than an ad hoc error handler.

Once an envelope opens, routing has to be deterministic: the same exception type, variance, age, and criticality must always produce the same lane, or the audit trail cannot explain why two similar-looking records ended up with different outcomes. Production routers lean on a single priority score that blends financial materiality, queue age against its service-level target, and business criticality into one comparable number:

P=α⋅∣ΔV∣M  +  β⋅tqueuetSLA  +  γ⋅ccriticality,α+β+γ=1P = \alpha \cdot \frac{|\Delta V|}{M} \;+\; \beta \cdot \frac{t_{\text{queue}}}{t_{\text{SLA}}} \;+\; \gamma \cdot c_{\text{criticality}}, \qquad \alpha + \beta + \gamma = 1

where ΔV\Delta V is the variance value, MM is the materiality cutoff below which a discrepancy is operationally immaterial, tqueuet_{\text{queue}} is the time the record has already spent unresolved, tSLAt_{\text{SLA}} is the queue-age service target, and ccriticalityc_{\text{criticality}} is a normalized 0–1 score for vendor or business-unit importance. An exception escalates to manual review whenever P≥θreviewP \ge \theta_{\text{review}}; below that threshold it clears through automated adjustment instead of consuming reviewer time. Raising θreview\theta_{\text{review}} trades reviewer load for slower catch of borderline-material variances; lowering it does the reverse. The full calibration approach — including how to derive α\alpha, β\beta, and γ\gamma from historical review outcomes — is the subject of prioritizing exceptions by financial materiality.

Transient failures skip the priority score entirely and route on a separate axis: retryability. A missing FX rate or an unreachable counterpart system is not a discrepancy to prioritize, it is a dependency to wait out, capped by an attempt limit so a persistent outage escalates to a hard failure instead of retrying forever. The backoff scheduling and dead-letter promotion that implement this lane are detailed in dead-letter queues and retry orchestration.

PYTHON
import logging
from dataclasses import dataclass
from decimal import Decimal

logger = logging.getLogger("exceptions.router")


@dataclass(frozen=True)
class RoutingDecision:
    queue: str          # "auto_adjust" | "manual_review" | "retry" | "dead_letter"
    priority_score: float
    reason_code: str


def compute_priority(
    variance_value: Decimal,
    materiality_cutoff: Decimal,
    queue_age_minutes: float,
    sla_minutes: float,
    criticality: float,
    weights: tuple[float, float, float] = (0.5, 0.3, 0.2),
) -> float:
    """Materiality + age + criticality score used to gate manual review.

    Bounded to roughly [0, 2] in practice; callers compare it to theta_review.
    """
    alpha, beta, gamma = weights
    materiality_term = float(abs(variance_value) / materiality_cutoff) if materiality_cutoff else 0.0
    age_term = queue_age_minutes / sla_minutes if sla_minutes else 0.0
    score = alpha * materiality_term + beta * age_term + gamma * criticality
    return round(score, 4)


def route_exception(
    exception_type: str,
    variance_value: Decimal,
    materiality_cutoff: Decimal,
    queue_age_minutes: float,
    sla_minutes: float,
    criticality: float,
    attempt_count: int,
    retry_max_attempts: int,
    theta_review: float = 0.65,
) -> RoutingDecision:
    """Pure function: the same envelope state always produces the same route.

    Determinism here is what lets an auditor reproduce, months later, why
    a given exception landed in manual review instead of auto-adjust.
    """
    if exception_type == "TRANSIENT_FAILURE":
        if attempt_count >= retry_max_attempts:
            decision = RoutingDecision("dead_letter", 1.0, "RETRY_CAP_EXCEEDED")
        else:
            decision = RoutingDecision("retry", 0.0, "TRANSIENT_DEPENDENCY")
        logger.info("routed %s -> %s (attempt %d/%d)",
                    exception_type, decision.queue, attempt_count, retry_max_attempts)
        return decision

    score = compute_priority(
        variance_value, materiality_cutoff, queue_age_minutes, sla_minutes, criticality
    )
    if score >= theta_review:
        decision = RoutingDecision("manual_review", score, "HIGH_PRIORITY_VARIANCE")
    else:
        decision = RoutingDecision("auto_adjust", score, "LOW_PRIORITY_VARIANCE")
    logger.info("routed %s -> %s (priority=%.4f)", exception_type, decision.queue, score)
    return decision
Exception state machine A new exception is classified into the triaged state. Triaged branches to in-review when it needs human judgment, or to retrying when it is a transient, retryable failure. Retrying loops on itself while attempts remain below the cap, promotes to aged-out once attempts meet or exceed the cap, and moves to resolved when a retry succeeds. In-review moves to resolved when an adjustment is approved, or to aged-out when the queue-age SLA is breached. Resolved and aged-out both terminate at the final state. classify needs judgment transient / retryable adjustment approved SLA breach attempt < cap retry succeeds attempts ≥ cap New Triaged In review Retrying Resolved Aged out

Configuration & Threshold Reference

The router above is only as trustworthy as the values feeding it. These are the parameters that most affect how much of the exception volume clears automatically versus consumes reviewer time, with ranges observed across procurement and logistics reconciliation pipelines of moderate scale. Calibrate them from your own historical queue, not from this table alone — the linked deep-dive pages own the per-domain tuning.

Parameter Purpose Typical range Notes
materiality_cutoff (M) Denominator in the priority score; below this a variance is immaterial $50–$500 Align with finance’s materiality policy, not the matcher’s tolerance floor
high_value_threshold Hard cutoff forcing manual review regardless of score $1,000–$10,000 Bypasses the priority formula entirely for whale variances
theta_review (θ) Priority score gating auto-adjust vs. manual review 0.55–0.75 Higher θ reduces reviewer load, slower to catch borderline variances
queue_age_sla_minutes Target time-to-resolution before an exception is “late” 240–1,440 min Tighter for high-value lanes; feeds the age term in the priority score
retry_max_attempts Attempts before a transient failure promotes to dead letter 3–6 Pair with exponential backoff, not fixed-interval retry
retry_backoff_base_seconds Base delay for exponential backoff 15–60s Add jitter to avoid synchronized retry storms
dead_letter_review_sla_hours Time a dead-lettered record may sit before forced triage 4–24h Prevents the dead-letter queue from becoming a second silent backlog
alert_queue_depth_threshold Queue depth that triggers a paging alert 250–2,000 records Set per lane; a shared threshold across lanes causes alert fatigue
criticality_weight (γ) Weight of vendor/business-unit criticality in the priority score 0.1–0.3 Keep low relative to materiality unless a strategic-vendor policy overrides it
write_back_retry_limit Attempts to post an approved adjustment to the ERP before escalating 3–5 A write-back failure is not the same exception type as the original variance

Weighting materiality, age, and criticality against each other is a policy decision as much as an engineering one — prioritizing exceptions by financial materiality covers how to derive defensible weights from a sample of past review outcomes rather than guessing.

Security, Compliance & Operational Resilience

An exception queue holds exactly the records finance cares most about — the ones where the numbers did not agree — which makes it a higher-scrutiny surface than the matched ledger, not a lower one.

Access controls follow the same least-privilege model as the rest of the pipeline. Service identities open and route envelopes automatically; only role-gated human operators can approve a manual-review adjustment or force-close an aged-out record, and both actions are themselves logged as state transitions with the actor’s identity attached. A configuration change to theta_review or high_value_threshold is a privileged operation for the same reason a tolerance-window change is: it silently redraws which variances a human ever sees.

The audit trail is the deliverable, not a side effect. Every transition — envelope opened, classified, routed, retried, resolved, aged out — is an append-only row carrying the canonical key, prior and new state, reason code, actor identity, and a UTC timestamp, following the same append-only ledger design used elsewhere in the platform. The practical test is the same one auditors apply to the matched ledger: given any line on the exception report, you can reconstruct exactly which reason code and threshold produced its outcome, and who or what actor closed it.

Monitoring turns the queue from a mystery into an operable service. The signals worth alerting on are queue depth and age per lane, retry-to-dead-letter promotion rate, manual-review approval rate, and the gap between opened and resolved counts over a rolling window. Monitoring and alerting for reconciliation pipelines covers instrumenting these as first-class metrics rather than inferring them from ad hoc queries against the queue table — a distinction that matters the first time someone needs the numbers during an incident, not after.

Write-back to the ERP has its own resilience requirement. Posting an approved adjustment is itself a network call to a system of record that can fail, time out, or partially apply. Every write-back carries the envelope’s idempotency key so a retried post never double-adjusts the ledger, and a write-back failure opens its own exception rather than silently leaving the envelope in a resolved-but-not-posted limbo state. The posting mechanics, reversal handling, and general-ledger mapping are covered in variance write-back and ERP adjustment posting.

Failure Modes & Remediation

Queue overflow. Upstream volume spikes — a supplier system migration, a mass EDI re-send — dump far more exceptions than the review lane can absorb in a normal day, and the manual-review queue backs up past its SLA. Remediation: alert on queue depth per lane (not a single global count), and have a pre-approved surge policy that temporarily raises theta_review or widens materiality_cutoff for a bounded window, logged explicitly as a policy change rather than a quiet threshold edit.

Stuck dead-letter queue. Records promoted after exceeding retry_max_attempts are meant to force human attention, but without a dead_letter_review_sla_hours target they accumulate indefinitely — the queue that was supposed to guarantee visibility becomes a second silent backlog. Remediation: apply the same age-based alerting to the dead-letter lane as any other queue, and treat a growing dead-letter count as a signal to investigate the upstream dependency, not just clear the backlog. The scheduling and promotion mechanics are detailed in dead-letter queues and retry orchestration.

Mis-routed high-value variance. A bug in the priority-score inputs — a stale FX rate feeding variance_value, or a criticality lookup returning a default instead of the real score — sends a genuinely material discrepancy into the auto-adjust lane, where it clears without a human ever seeing it. Remediation: enforce the high_value_threshold hard cutoff independently of the weighted score, so no combination of age and criticality inputs can route a variance above that dollar amount into auto-adjust; sample-audit auto-adjusted records near the threshold on a fixed cadence.

Alert fatigue. A single queue-depth threshold shared across all lanes fires constantly on the high-churn retry lane while staying silent on a slow-building manual-review backlog, until operators start ignoring the channel entirely. Remediation: set alert_queue_depth_threshold per lane based on that lane’s normal throughput, and page on rate-of-change (depth growing for N consecutive intervals) rather than a static count, which is the approach covered in monitoring and alerting for reconciliation pipelines.

Reclassification drift. A record’s reason code gets corrected during triage — what looked like OUT_OF_TOLERANCE turns out to be SCHEMA_COERCION_FAILURE on closer inspection — and if the correction overwrites the original classification in place, root-cause counts silently lose history. Remediation: reclassification is itself a logged state transition with both the old and new reason code, never a field overwrite, so root-cause trends in discrepancy root-cause classification reflect what was actually seen at each point in time.

Silent write-back failure. An approved adjustment posts to the ERP, the network call times out before the response arrives, and the envelope is marked resolved even though the ledger never received the entry. Remediation: the write-back step only marks an envelope resolved after a confirmed acknowledgment carrying the ERP’s own posting reference; a timeout or error opens a distinct write-back exception rather than assuming success, and write_back_retry_limit governs the retry before that escalates to manual intervention.

Conclusion

Exception handling is where a reconciliation platform either earns or loses its claim to being auditable. Anyone can match the easy records; the design decisions that matter are what happens to the ones that do not match — whether every unresolved record gets a deterministic classification and route, whether retries are capped instead of infinite, whether a high-value variance can ever slip through on a scoring bug, and whether the resolution can be reconstructed months later from an append-only log rather than someone’s memory of a Slack thread. Build the envelope schema and router as strictly as the matcher itself, keep the audit trail unbroken through every retry and reclassification, and the exception queue stops being the place where reconciliation confidence goes to die.

← Home