Escalating Aging Queries with Python SLA Timers

A query raised on a Friday afternoon should not be “two days overdue” on Monday morning, a study running sites in Boston and Tokyo cannot age every query on one wall clock, and an escalation sweep that fires on every run floods a CRA manager with a hundred duplicate emails for the same aging query. Escalating aging queries correctly means computing age in business days against the right calendar, mapping that age to a tiered escalation ladder, and emitting exactly one notification per genuine threshold crossing. This deep-dive is the aging-and-escalation half of Automated Query Lifecycle Management, within the broader Clinical Query Generation & Discrepancy Management discipline. Where lifecycle management defines the SLA clocks, this guide turns an overdue clock into an attributable, non-duplicating escalation that surfaces the query to the right person before it leaves the monitoring window — every escalation recorded as a regulated event under 21 CFR Part 11.

The Escalation Ladder #

Escalation is driven by a business-day aging sweep, not by inbound events. When a query’s business-day age crosses the next tier threshold, it escalates one rung up the ladder — site coordinator or CRA, then CRA manager, then CDM lead — and emits a single debounced notification. A genuine site response resets the clock.

Root Cause: Why Naive Aging Over- and Under-Escalates #

A calendar-day age computed as now - opened_ts breaks in three predictable ways in a clinical setting. It counts weekends and site holidays against a coordinator who was never expected to work them, so queries breach SLAs that were defined in business days and escalate to a CRA manager who then dismisses them as noise. It anchors on a single timezone, so a query opened at 23:00 UTC ages differently depending on which regional site actually holds it. And because aging is a function of the wall clock rather than of any event, a sweep that re-derives “overdue” on every run re-escalates a query it already escalated — an escalation storm that trains recipients to ignore the alerts entirely.

The fix is threefold: measure age in business days against a per-site holiday calendar; anchor the clock to the site’s timezone; and make each escalation idempotent so a query crosses a given threshold exactly once. The SLA windows themselves come from severity, calibrated by Discrepancy Threshold Tuning; this guide is concerned with turning an elapsed window into an escalation without over- or under-firing.

Step-by-Step Implementation #

Each step is a runnable building block. Compose them into an escalation sweeper that runs on a schedule against the open-query set produced by the lifecycle layer.

1. Compute business-day age against a holiday calendar #

Count only working days between assignment and now, excluding weekends and the site’s observed holidays. numpy.busday_count with an explicit holiday list keeps the arithmetic exact and reproducible:

# ALCOA+ relevance: Accurate + Consistent — business-day aging against a pinned,
# version-controlled holiday calendar reproduces the same age on every re-run.
from datetime import date
import numpy as np

# Per-site observed holidays, reviewed as version-controlled config, not code.
SITE_HOLIDAYS = {
    "1001": ["2026-07-03", "2026-09-07", "2026-11-26"],   # US site
    "2007": ["2026-05-04", "2026-08-11"],                  # JP site
}


def business_day_age(assigned_on: date, as_of: date, site_id: str) -> int:
    holidays = np.array(SITE_HOLIDAYS.get(site_id, []), dtype="datetime64[D]")
    return int(np.busday_count(assigned_on, as_of, holidays=holidays))

2. Map business-day age to an escalation tier #

Tier thresholds are ordered data, not branching logic, so a data manager can retune the ladder as a reviewable commit. The mapping returns the highest tier the age qualifies for, with tier 0 meaning “not yet due”:

# 21 CFR Part 11 relevance: the tier ladder is version-controlled config; every change
# to a threshold or recipient is a tracked, signed-off change-management event.
TIERS = [
    {"tier": 1, "min_bd": 3,  "notify_role": "site_cra"},
    {"tier": 2, "min_bd": 6,  "notify_role": "cra_manager"},
    {"tier": 3, "min_bd": 11, "notify_role": "cdm_lead"},
]


def tier_for_age(age_bd: int) -> dict | None:
    matched = None
    for rung in TIERS:                       # ordered ascending; take the highest met
        if age_bd >= rung["min_bd"]:
            matched = rung
    return matched                           # None while inside the SLA window

3. Debounce so a query escalates each tier only once #

Escalation storms come from re-firing a tier the query already reached. Compare the query’s current escalation level against the tier its age now warrants, and emit only on a genuine upward crossing:

# 21 CFR Part 11 relevance: idempotent escalation — a re-run creates no duplicate
# notifications. The last_tier watermark makes each threshold crossing fire once.
def escalations_due(open_queries: list[dict], as_of: date) -> list[dict]:
    due = []
    for q in open_queries:
        age = business_day_age(date.fromisoformat(q["assigned_on"]), as_of, q["site_id"])
        rung = tier_for_age(age)
        if rung is None:
            continue
        if rung["tier"] <= q.get("last_tier", 0):     # already escalated this far
            continue
        due.append({"query_id": q["query_id"], "age_bd": age, **rung})
    return due                                        # only true upward crossings

4. Reset the clock when a genuine site response arrives #

An inbound response must reset aging so a site that answered is not escalated for the reviewer’s backlog. Reset the anchor and clear the tier watermark only for a substantive response, never for a status flip alone:

# ALCOA+ relevance: Contemporaneous — the aging clock reflects the site's actual last
# action, so escalation targets true inaction, not the CRA's review queue.
def apply_response(query: dict, response, as_of: date) -> dict:
    if response.is_substantive:                        # real answer, not a status ping
        query = {**query, "assigned_on": as_of.isoformat(), "last_tier": 0}
    return query

5. Emit an immutable escalation and notification record #

Every escalation is a regulated event, and the notification it generates is part of the audit story. Write an append-only record binding the query, the tier crossed, the recipient role, the age in business days, and a UTC timestamp, hashed for reconstruction:

# 21 CFR Part 11 relevance: immutable escalation audit — Attributable + Enduring. The
# batched notification digest references these records rather than duplicating alerts.
import hashlib
import json
from datetime import datetime, timezone


def escalation_record(item: dict) -> dict:
    rec = {
        "query_id": item["query_id"], "escalated_to_tier": item["tier"],
        "notify_role": item["notify_role"], "age_business_days": item["age_bd"],
        "actor": "escalation-sweeper",
        "occurred_at": datetime.now(timezone.utc).isoformat(),
    }
    rec["record_hash"] = hashlib.sha256(
        json.dumps(rec, sort_keys=True, separators=(",", ":")).encode()
    ).hexdigest()
    return rec                                # append-only; feeds the batched digest

Aging dashboards read directly from these records: counts of queries per tier, median business-day age by site, and the count of tier-3 escalations open against a CDM lead are all folds over the append-only escalation log, so the metrics can never disagree with the audit trail.

Verification and Audit Trail #

Escalation is GxP-relevant, so “the query was escalated on time” must be provable from the log. Capture a structured entry per escalation within the boundaries a read-only consumer may record under Audit Trail Boundaries in EDC Systems.

To confirm the logic, assert three properties against fixtures: a query aged across a weekend gains zero business days for those days; running the sweep twice on the same day produces the escalation on the first run and zero on the second; and a substantive response resets last_tier to 0 so the next threshold crossing re-fires cleanly. Run a retuned tier ladder in shadow mode against a historical open-query snapshot, comparing the escalations it would have emitted to the recorded manual escalations before it drives live notifications.

Edge Cases and Vendor-Specific Gotchas #

Timezone and holiday-calendar drift. Anchor aging to the site’s timezone and its observed holiday list, not the server’s. A query held by a Tokyo site must not age on US holidays, and vice versa. Version-control the per-site calendar alongside the tier ladder so a mid-study holiday correction is a tracked change, and pin it to the database-lock milestone so historical aging replays exactly.

Escalation storms on sweep re-runs. Without a last_tier watermark, every scheduled run re-derives “overdue” and re-notifies, and recipients quickly filter the alerts to trash. Debounce on the watermark so each tier fires once, and batch the run’s escalations into a single per-recipient digest rather than one message per query — the notification record in step 5 is the source the digest reads from.

Clock reset on non-substantive pings. A status change with no real answer must not reset aging, or a site can indefinitely defer escalation by toggling a flag. Gate the reset on a substantive response — a value change or a genuine query answer — mirroring the resolution test used in Automating Query Closure with Response Parsing.

Frequently Asked Questions #

Related #

• Automated Query Lifecycle Management — the parent guide that defines the SLA clocks this escalation ladder acts on.
• Automating Query Closure with Response Parsing — the sibling that resolves queries before they age out, and shares the substantive-response test.
• Discrepancy Threshold Tuning — calibrates the severity-driven SLA windows that seed aging.
• Deterministic Query Routing Workflows for CRAs — assigns the owner whose inaction the escalation ladder measures.
• Clinical Query Generation & Discrepancy Management — the parent reference for this discipline.