Tracking Accessibility MTTR and Compliance Drift

To compute mean time to remediation, average the interval between a remediation ticket’s open and close timestamps over the findings that closed in a window; to compute compliance drift, subtract resolved findings from newly opened ones over that same period. This page gives the exact SQL and Python to derive both from a findings warehouse, and shows why the two metrics answer different questions — MTTR measures how fast a team fixes, drift measures whether the surface is getting better or worse.

Both metrics are time-based operations measures that sit inside the Compliance Reporting Dashboards layer of the Accessibility Compliance Baseline for Enterprise Web Ops. The parent guide defines the append-only fact table these queries read; this page is the calculation reference for the two trend metrics that most often get computed wrong.

When This Applies #

Reach for this when you already have findings landing as facts with open and close timestamps and now need defensible operational numbers on top of them. It applies specifically when:

• Leadership asks “how fast are we fixing accessibility issues?” and you need a single defensible figure rather than a gut feel.
• A dashboard shows a flat total open count and hides that a team is opening and closing findings at high volume underneath — drift exposes the churn a snapshot conceals.
• You need to distinguish a team that is stable at 40 open findings from one that is quietly regressing from 5 to 40, which an open-count panel alone cannot tell apart.

It does not apply to criterion-coverage questions — “which WCAG success criteria do we actually pass?” is a structural scorecard, not a time series, and is handled by the WCAG conformance scorecard guide. Keep the two apart: MTTR and drift are about flow over time, coverage is about state at a point.

Minimal Reproducible Example #

The most common MTTR mistake is averaging over the wrong population — every finding ever seen, including still-open ones whose remediation time is undefined. It silently biases the number toward whatever is easy and recently closed.

# mttr_naive.py — WRONG. Averages only what closed, but ignores window and
# accidentally divides by findings that never closed (closed_at is None).
def mttr_days(findings):
    durations = []
    for f in findings:
        # None - opened_at explodes, or gets skipped inconsistently depending on guard.
        durations.append((f["closed_at"] - f["opened_at"]).days)
    return sum(durations) / len(durations)

Two faults. It has no time window, so it mixes a fix from two years ago with one from yesterday and never moves. And it assumes every finding closed, so a single open finding either raises a TypeError or, if guarded loosely, distorts the denominator. MTTR is only defined over findings that closed within a bounded window.

A third, subtler fault hides in the choice of average itself. Remediation times for critical and serious findings are usually short — they get prioritized — while moderate and minor findings sit for months, so a single mean over all impacts blends two populations that behave nothing alike. A useful scorecard reports MTTR per impact bucket, or at minimum reports a high percentile alongside the central figure so the slow-remediation tail is visible rather than averaged into invisibility.

Correct Implementation #

Compute MTTR over findings whose closed_at falls inside the reporting window, and report the median alongside the mean because remediation times are heavily right-skewed — one stale finding fixed after nine months drags the mean far past what a typical fix takes.

from datetime import datetime, timedelta, timezone
from statistics import mean, median

def remediation_metrics(conn, app_id: str, window_days: int = 30) -> dict:
    """MTTR and drift for one app over a trailing window, read from finding facts."""
    now = datetime.now(timezone.utc)
    since = now - timedelta(days=window_days)

    with conn.cursor() as cur:
        # MTTR population: findings CLOSED within the window only.
        cur.execute(
            """SELECT EXTRACT(EPOCH FROM (closed_at - opened_at)) / 86400.0
               FROM   finding_fact
               WHERE  app_id = %s AND closed_at >= %s AND closed_at < %s""",
            (app_id, since, now),
        )
        ttr_days = [row[0] for row in cur.fetchall()]

        # Drift population: opened vs resolved within the SAME window.
        cur.execute(
            "SELECT count(*) FROM finding_fact "
            "WHERE app_id = %s AND opened_at >= %s AND opened_at < %s",
            (app_id, since, now),
        )
        opened = cur.fetchone()[0]
        cur.execute(
            "SELECT count(*) FROM finding_fact "
            "WHERE app_id = %s AND closed_at >= %s AND closed_at < %s",
            (app_id, since, now),
        )
        resolved = cur.fetchone()[0]

    return {
        "mttr_mean_days": round(mean(ttr_days), 1) if ttr_days else None,
        "mttr_median_days": round(median(ttr_days), 1) if ttr_days else None,
        "mttr_p90_days": round(_pct(ttr_days, 90), 1) if ttr_days else None,
        "sample_size": len(ttr_days),          # never trust MTTR on a tiny sample
        "opened": opened,
        "resolved": resolved,
        "drift": opened - resolved,            # positive == regressing
    }

def _pct(values, p):
    """Nearest-rank percentile; p90 exposes the stale-finding tail that the
    mean hides and the median flatters."""
    s = sorted(values)
    k = max(0, min(len(s) - 1, round((p / 100) * len(s) + 0.5) - 1))
    return s[k]

Emitting the mean, median, and p90 together is what makes MTTR readable rather than a single number people argue about. The median states the typical fix, the p90 exposes how long the slowest tenth take, and a wide gap between them is itself the signal — it says the team clears routine findings quickly but lets a hard tail rot, which no single statistic conveys on its own.

The pure SQL form of drift, bucketed by week for a trend line rather than a single window, keeps the opened and resolved counts on the same period so the subtraction is meaningful:

-- Weekly opened, resolved, and net drift per team.
WITH weeks AS (
  SELECT generate_series(
           date_trunc('week', now()) - interval '11 weeks',
           date_trunc('week', now()), interval '1 week') AS wk),
o AS (SELECT date_trunc('week', opened_at) wk, count(*) n
      FROM finding_fact WHERE team_id = :team GROUP BY 1),
r AS (SELECT date_trunc('week', closed_at) wk, count(*) n
      FROM finding_fact WHERE team_id = :team AND closed_at IS NOT NULL GROUP BY 1)
SELECT weeks.wk::date              AS week,
       COALESCE(o.n, 0)           AS opened,
       COALESCE(r.n, 0)           AS resolved,
       COALESCE(o.n,0) - COALESCE(r.n,0) AS drift,
       sum(COALESCE(o.n,0) - COALESCE(r.n,0)) OVER (ORDER BY weeks.wk) AS cumulative
FROM weeks
LEFT JOIN o ON o.wk = weeks.wk
LEFT JOIN r ON r.wk = weeks.wk
ORDER BY week;

The cumulative running sum is what a program owner actually reads: a line trending up means the backlog is growing faster than the team resolves it, regardless of how the raw open count looks on any single day.

Both queries above assume one open-and-close interval per finding, which breaks the moment a remediated finding regresses and reopens. If your warehouse records remediation as a separate remediation_episode row rather than mutating finding_fact in place, MTTR should average over episodes so each fix counts once:

-- Episode-based MTTR: one row per open-to-close cycle, so a finding fixed,
-- regressed, and fixed again contributes two independent remediation times.
SELECT app_id,
       percentile_cont(0.5) WITHIN GROUP (
         ORDER BY EXTRACT(EPOCH FROM (closed_at - opened_at)) / 86400.0
       ) AS mttr_median_days,
       count(*)                          AS episodes,
       count(*) FILTER (WHERE reopened)  AS reopened_episodes
FROM   remediation_episode
WHERE  closed_at >= now() - interval '30 days'
GROUP  BY app_id;

Carrying reopened_episodes beside the median turns a hidden quality problem into a visible one: a low MTTR paired with a high reopen count means fixes are fast but fragile, which is a different and often more urgent story than a slow-but-stable team. Neither the naive average nor a single open-count snapshot can surface that distinction.

Pipeline Integration Note #

These queries read the same finding_fact table the parent compliance reporting dashboards guide builds, and they depend on close timestamps being trustworthy — which is why remediation must flow through tickets rather than being inferred. When a finding’s closed_at comes from the issue tracker closing event surfaced by the remediation routing from violation to issue tracker integration, MTTR reflects the human workflow rather than scan cadence. The close timestamps and the raw facts also age under the audit data storage and retention policies, so a trailing-year MTTR must read from a tier retained at least that long or the window will silently truncate.

Gotchas #

• Reopened findings corrupt a naive MTTR. A finding closed then reopened after a regression has two intervals; averaging only the first understates true remediation time. Either compute MTTR per closed episode keyed by (finding_key, closed_at), or exclude findings that later reopened within the window and report them as a separate churn count.
• Multi-tenant apps skew the mean unless scoped. A shared component fixed once resolves findings across every tenant route simultaneously, dumping a burst of near-zero remediation times into the window. Scope MTTR by owning team or component, not by raw route count, so one shared fix does not masquerade as dozens of fast remediations.
• Median matters more than mean on small samples. With fewer than roughly 20 closed findings in a window the mean is dominated by outliers. Always emit sample_size beside the figure and suppress or annotate MTTR when the sample is too small to be stable, rather than publishing a confident number built on three data points.

Frequently Asked Questions #

Related #

• Compliance Reporting Dashboards
• Building a WCAG Conformance Scorecard
• Remediation Routing from Violation to Issue Tracker
• Audit Data Storage & Retention Policies