SC ReconciliationReconciliation Automation
  1. Home
  2. Architecture
  3. Monitoring and Alerting for Reconciliation Pipelines
  4. Alerting on Exception Queue Depth and Age

Alerting on Exception Queue Depth and Age

↑ Part of Monitoring and Alerting for Reconciliation Pipelines.

When the reconciliation exception queue backs up, the first metric most teams reach for is depth: how many unresolved discrepancies are sitting in the table right now. Depth is cheap to compute and easy to chart, but it is a weak proxy for the thing procurement operations actually cares about — whether any single exception is about to blow through its resolution SLA. A queue can hold a stable 400 records for weeks with triage keeping pace, or it can hold the same 400 records because nobody has touched the oldest ones in six days. Depth alone cannot distinguish those two states. This page defines two gauges — queue depth and oldest-record age — the trigger conditions that turn either into a page, and the burn-rate math that keeps a backlog spike from becoming a false alarm.

Operational Trigger Signals

Each signal below is derived directly from the exporter’s own telemetry, so the decision to page is a threshold crossing, not a judgement call made at 2 a.m.:

  1. Sustained depth breach — queue_depth > N held for a sustained window (for example, more than 500 open P1 exceptions for 15 consecutive minutes), scoped per tier. The sustained window exists specifically so a normal end-of-batch ingest spike does not fire a page before triage has had a chance to work it down.
  2. Oldest-record age exceeds SLA — oldest_age_seconds > SLA_threshold (for example, 14400 seconds / 4 hours for a P1 tier). This is the harder gate: it should page regardless of what depth is doing, because a queue with low depth but one nine-day-old exception has still failed its SLA.
  3. Positive net age growth (burn rate at or above 1) — the oldest record’s age is increasing at wall-clock speed or faster across the observation window, meaning nothing has been pulled off the head of the queue. A burn rate below 1 means triage is still winning even if depth looks alarming.
  4. Projected SLA breach inside the lead time — a projected time-to-breach, computed from the current age and its growth rate, falls under a lead-time horizon (for example, under 2 hours). This is the early warning that fires before the hard SLA gate in signal 2, giving on-call time to intervene.

Signal 2 is the one that should actually wake someone up. Signals 1, 3, and 4 exist to give lead time and to keep the age alert from being the only thing anyone sees. Queue design decisions — tiering, priority ordering, what counts as “open” — are covered in Exception Queue Design and Triage; this page assumes that design is already in place and focuses purely on when to alert on it.

Step-by-Step Implementation

Build the alerting layer as a thin exporter plus a small set of Alertmanager rules, in this order:

  1. Instrument two gauges — queue_depth (current open-exception count) and oldest_age_seconds (age of the oldest open record), both labeled by tier, following the general pattern in Instrumenting Custom Pipeline Metrics with Prometheus.
  2. Deploy the exporter and confirm scraping — run it as a sidecar or scheduled job with its own /metrics endpoint, and verify both gauges show up with the correct tier labels before writing any alert rules.
  3. Add burn-rate recording rules — precompute the age-growth rate over a short and a long window so alert expressions stay cheap and consistent across dashboards and rules.
  4. Set the static depth threshold — pick a per-tier depth_warning value and pair it with a for: duration long enough to absorb normal batch spikes.
  5. Add the multi-window burn-rate alert for age — require agreement across both the short and long windows before paging, the same technique used for SLO error-budget burn-rate alerts.
  6. Configure Alertmanager routing and a flap guard — route by severity, add an inhibition rule so a firing age alert suppresses the redundant depth alert for the same tier, and confirm resolution notifications reach the same channel as the page.

The exporter below queries the exception table directly rather than trusting an application-level counter, because a counter that only increments on write will not reflect exceptions resolved out-of-band by a manual override:

PYTHON
import logging
import time
from dataclasses import dataclass

import psycopg
from prometheus_client import Gauge, start_http_server

logger = logging.getLogger(__name__)

QUEUE_DEPTH = Gauge(
    "reconciliation_exception_queue_depth",
    "Number of unresolved rows in the exception queue.",
    ["tier"],
)
OLDEST_AGE_SECONDS = Gauge(
    "reconciliation_exception_oldest_age_seconds",
    "Age in seconds of the oldest unresolved exception.",
    ["tier"],
)


@dataclass(frozen=True)
class QueueMetrics:
    tier: str
    depth: int
    oldest_age_seconds: float


def fetch_queue_metrics(conn: psycopg.Connection, tier: str) -> QueueMetrics:
    """Query current depth and oldest-record age for one exception tier."""
    query = """
        SELECT count(*) AS depth,
               COALESCE(EXTRACT(EPOCH FROM (now() - min(created_at))), 0) AS oldest_age
        FROM reconciliation_exceptions
        WHERE status = 'OPEN' AND tier = %s
    """
    with conn.cursor() as cur:
        cur.execute(query, (tier,))
        depth, oldest_age = cur.fetchone()
    logger.info("tier=%s depth=%d oldest_age_seconds=%.0f", tier, depth, oldest_age)
    return QueueMetrics(tier=tier, depth=depth, oldest_age_seconds=float(oldest_age))


def update_gauges(metrics: QueueMetrics) -> None:
    """Publish one tier's metrics to the Prometheus registry and log a soft warning."""
    QUEUE_DEPTH.labels(tier=metrics.tier).set(metrics.depth)
    OLDEST_AGE_SECONDS.labels(tier=metrics.tier).set(metrics.oldest_age_seconds)
    if metrics.oldest_age_seconds > 4 * 3600:
        logger.warning(
            "tier=%s oldest_age_seconds=%.0f exceeds 4h soft ceiling",
            metrics.tier, metrics.oldest_age_seconds,
        )


def poll_forever(dsn: str, tiers: list[str], interval_seconds: int = 30) -> None:
    """Poll the exception table on a fixed interval and keep gauges current."""
    with psycopg.connect(dsn) as conn:
        while True:
            for tier in tiers:
                metrics = fetch_queue_metrics(conn, tier)
                update_gauges(metrics)
            time.sleep(interval_seconds)


if __name__ == "__main__":
    logging.basicConfig(level=logging.INFO)
    start_http_server(9308)
    poll_forever(dsn="postgresql://recon:***@localhost/recon", tiers=["P1", "P2", "P3"])

Poll on a fixed interval rather than on every write so the SELECT min(created_at) cost stays bounded even when the table holds tens of thousands of resolved-and-retained rows; index (status, tier, created_at) so the query stays a fast range scan.

Queue depth and oldest-age alerting flow The exception table in Postgres feeds two gauges: queue depth and oldest-record age. The depth gauge is checked against a sustained static threshold; the age gauge is checked against a multi-window burn-rate condition requiring agreement across a one-hour and a six-hour window. Either condition can fire the Alertmanager rule, which is held behind a ten-minute flap guard before routing by severity to Slack for warnings or PagerDuty for critical SLA breaches. Exception table (Postgres) queue_depth Gauge count where status=OPEN oldest_age_seconds Gauge now - min(created_at) depth > N sustained 15m ? age burn rate ≥ 1 1h & 6h ? Alertmanager rule fires rules.yml for: 10m flap guard severity: warning route to Slack severity: critical route to PagerDuty

The burn rate used in signal 3 is the rate of change of the oldest-record age over a window ww:

g(t)=a(t)−a(t−w)wg(t) = \frac{a(t) - a(t-w)}{w}

where a(t)a(t) is oldest_age_seconds at time tt. Since wall-clock time always advances at 1 second per second, g(t)≥1g(t) \ge 1 means the oldest record is aging at least as fast as time itself — nothing is being pulled off the head of the queue. The projected time to breach the SLA is:

tbreach=SLA−a(t)g(t)t_{breach} = \frac{SLA - a(t)}{g(t)}

Alert on signal 4 when g(t)>0g(t) > 0 and tbreacht_{breach} falls under the lead time. This is the same shape as SLO error-budget burn-rate alerting, applied to a single queue instead of an aggregate success ratio.

YAML
groups:
  - name: reconciliation-exception-queue
    rules:
      - record: reconciliation:oldest_age_growth:1h
        expr: delta(reconciliation_exception_oldest_age_seconds[1h]) / 3600
      - record: reconciliation:oldest_age_growth:6h
        expr: delta(reconciliation_exception_oldest_age_seconds[6h]) / 21600
      - alert: ExceptionQueueDepthSustained
        expr: reconciliation_exception_queue_depth{tier="P1"} > 500
        for: 15m
        labels:
          severity: warning
          tier: P1
        annotations:
          summary: "P1 exception queue depth above 500 for 15m"
          description: "queue_depth= on tier P1; check triage throughput before escalating."
      - alert: ExceptionOldestAgeSLABreach
        expr: reconciliation_exception_oldest_age_seconds{tier="P1"} > 14400
        for: 5m
        labels:
          severity: critical
          tier: P1
        annotations:
          summary: "Oldest P1 exception has exceeded the 4h SLA"
          description: "oldest_age_seconds=; this is the hard SLA gate, not a capacity signal."
      - alert: ExceptionAgeBurnRateHigh
        expr: >
          reconciliation:oldest_age_growth:1h{tier="P1"} >= 1
          and reconciliation:oldest_age_growth:6h{tier="P1"} >= 1
        for: 10m
        labels:
          severity: warning
          tier: P1
        annotations:
          summary: "P1 oldest-exception age is growing at wall-clock speed"
          description: "Nothing is being triaged off the head of the P1 queue across both windows."
YAML
route:
  receiver: recon-default
  group_by: [tier, alertname]
  routes:
    - matchers: [severity="critical"]
      receiver: recon-pagerduty
      continue: false
    - matchers: [severity="warning"]
      receiver: recon-slack
inhibit_rules:
  - source_matchers: [alertname="ExceptionOldestAgeSLABreach"]
    target_matchers: [alertname="ExceptionQueueDepthSustained"]
    equal: [tier]
receivers:
  - name: recon-pagerduty
    pagerduty_configs:
      - routing_key: "${PAGERDUTY_ROUTING_KEY}"
  - name: recon-slack
    slack_configs:
      - channel: "#recon-exceptions"
        send_resolved: true

The inhibition rule matters as much as the alert rules themselves: once the hard SLA breach is firing for a tier, the sustained-depth warning for the same tier is redundant noise and should be suppressed rather than paging twice for one underlying problem.

Configuration Reference

Calibrate every threshold per tier — a P1 (financially material) exception has a much tighter SLA than a P3 low-value variance, and a shared threshold either pages too often on P1 or never on P3:

Parameter Accepted values Default Notes
depth_warning 100 – 2000 500 Per-tier queue_depth ceiling before a warning fires.
depth_critical 500 – 5000 1500 Hard depth ceiling; rarely needed if the age alert is tuned.
sustained_for 5m – 30m 15m Alertmanager for: on the depth rule; absorbs batch spikes.
oldest_age_sla_p1 1h – 8h 4h SLA ceiling for tier P1 (financially material exceptions).
oldest_age_sla_p3 12h – 72h 24h SLA ceiling for tier P3 (low-materiality exceptions).
burn_short_window 30m – 2h 1h Fast window for burn-rate confirmation.
burn_long_window 4h – 12h 6h Slow window; both must agree before the burn-rate alert pages.
lead_time 30m – 4h 2h Early-warning horizon for projected SLA breach.
flap_guard_for 5m – 15m 10m Minimum condition duration before Alertmanager fires at all.

Debugging & Recovery

Queue-based alerts are unusually prone to flapping and fatigue because both depth and age move continuously, and both are directly affected by upstream retry behavior:

  • Flapping around a static threshold — a depth alert that hovers around depth_warning will fire and resolve repeatedly during normal batch cycles. Fix this with hysteresis: resolve at a lower value than the trigger threshold (for example, trigger at 500, resolve below 400) rather than using the same number both ways, plus the for: duration already in the rule.
  • Alert fatigue from low-materiality tiers — paging on-call for a P3 depth spike trains the team to ignore alerts. Route P3 breaches to a ticket queue instead of a page, and reserve PagerDuty for tiers where the SLA has real financial or compliance consequences.
  • False depth spikes from retry storms — a burst of failed retries can inflate queue_depth without a single new genuine exception. Cross-check against retry volume before escalating; the backoff and capping behavior that usually explains this is covered in Dead-Letter Queues and Retry Orchestration.
  • Silence known maintenance windows, don’t mute the rule — if a nightly reconciliation batch predictably spikes depth for ten minutes, add a scheduled Alertmanager silence scoped to that window rather than raising depth_warning globally, which would mask a genuine daytime backlog.
  • Confirm the exporter itself, not just the pipeline — if both gauges go flat or stop updating, the exporter’s poll loop has stalled, not the queue. Alert separately on staleness of the /metrics scrape, and cross-reference against the broader pipeline health view in Building Grafana Dashboards for Reconciliation Health.

FAQ

Should I alert on queue depth or oldest-record age first?

Age first. Depth is a capacity and leading-indicator signal — useful for spotting a growing backlog before it becomes a problem — but the actual SLA commitment is per-record, not per-queue. A queue with only ten open exceptions still fails its SLA if one of them is nine days old. Treat the oldest-age alert as the primary page and the depth alert as an early-warning signal that something is trending the wrong way.

Why use multi-window burn-rate confirmation instead of a single age-growth alert?

A single short window is noisy: a five-minute lull in triage (someone stepped away, a deploy paused consumers) can spike a one-window growth rate without indicating a real problem. Requiring agreement across a short window (1h) and a long window (6h) — the same technique used for SLO error-budget burn-rate alerts — filters out short blips while still catching a genuine, sustained stall well before the hard SLA threshold in signal 2 is breached.

How do I stop the depth alert from firing every night during batch ingestion?

Three changes together: give the rule a for: duration long enough that a normal ten-minute batch spike self-resolves before it counts (sustained_for: 15m above a ten-minute spike), use a lower resolve threshold than the trigger threshold so it doesn’t oscillate at the boundary, and add a scheduled Alertmanager silence for the known ingestion window if the spike sometimes runs longer than the for: duration. Do not simply raise depth_warning to a level that also hides a genuine daytime backlog.