Aggregating Violation Metrics for State Compliance Reports

12 min read

This guide sits under Building Compliance Reporting Dashboards for Municipal Audits, part of the Violation Tracking & Compliance Enforcement track, and it narrows to one exacting task: computing the specific numbers a state agency demands and packaging them so the submission is provably correct.

The Task and Why It Is Exacting

State compliance reporting is not internal analytics with a nicer chart. A state housing, building, or code-enforcement agency publishes a rigid metric specification — counts by code category, a median cure time, a repeat-offender rate — and a jurisdiction that reports the wrong denominator, the wrong window, or a number that cannot be reconciled against its own case files risks a finding, a withheld grant, or a public black mark. The compliance officer who submits it is personally attesting the figures are accurate.

Three properties separate a defensible state submission from a spreadsheet someone typed up. First, the metric definitions must match the state’s, exactly — “open violations” might mean open on the last day of the period, or open at any point during it, and picking the wrong one silently doubles the count. Second, the window must be deterministic: a rolling 12-month figure computed on the 3rd of the month must use the same boundaries as one computed on the 5th. Third, the submission must be reconcilable and tamper-evident — the total the state receives must tie back to the source-of-truth case list, and the exact bytes submitted must be re-verifiable months later when an auditor asks. The inputs are the frozen violation snapshot produced by the parent dashboard; the output is a small, hashed, machine-checkable payload the state portal accepts.

From frozen snapshot to a reconciled, hashed state submission A frozen violation snapshot enters a rolling-window filter that selects only rows inside the state's reporting period. The filtered rows feed three parallel metric computations: counts by code category, median cure time, and repeat-offender rate. Their results pass through a reconciliation gate that compares the metric denominators against an independent source-of-truth case count and blocks submission on any mismatch. The reconciled metrics are serialized in canonical form and hashed with SHA-256 for the state portal, and each submission is recorded in an append-only ledger with its cutoff, hash, and reconciliation status. Frozen snapshot Rolling window period boundaries deterministic Counts by code category rollup Median cure time opened → resolved Repeat-offender rate by parcel Reconcile vs source of truth block on mismatch Hash & submit canonical SHA-256 state portal recorded to run ledger

Step 1 — Bound the Rolling Reporting Window

Most state specs ask for a rolling window — a trailing 12 months, or a specific fiscal quarter — and the boundaries must be computed the same way no matter what day the report is generated. Never derive the window from datetime.now() at call time; pass an explicit anchor date and compute half-open [start, end) boundaries so a case timestamped exactly at midnight lands in exactly one period.

from __future__ import annotations
from dataclasses import dataclass
from datetime import datetime, timezone
from dateutil.relativedelta import relativedelta
import pandas as pd


@dataclass(frozen=True)
class StatePeriod:
    end: datetime            # exclusive upper bound, tz-aware UTC
    months: int = 12         # trailing window length the state specifies

    @property
    def start(self) -> datetime:
        return self.end - relativedelta(months=self.months)


def clip_to_window(df: pd.DataFrame, period: StatePeriod) -> pd.DataFrame:
    """Keep violations *opened* within [start, end). Half-open avoids the
    double-count that happens when consecutive periods share a boundary instant."""
    opened = df["opened_at"]
    mask = (opened >= period.start) & (opened < period.end)  # inclusive start, exclusive end
    return df.loc[mask].copy()

Using a half-open interval is not pedantry: if two adjacent quarterly reports both include the boundary midnight, a violation opened at that instant is counted in both, and the annual roll-up no longer equals the sum of the quarters — a discrepancy a state reconciler will catch immediately.

Step 2 — Compute Counts by Code Category

States report violations grouped by their own category taxonomy, not your internal code strings, so the aggregation has two parts: map each local code to the state category, then count. Keep the mapping as data and count every local code — including ones with no state category — so nothing silently vanishes from the total.

# Local code -> state-defined category. Lives in version control, reviewed on
# each annual state-spec update, mirroring how the platform versions its taxonomies.
STATE_CATEGORY: dict[str, str] = {
    "PM-101": "PROPERTY_MAINTENANCE",
    "PM-115": "PROPERTY_MAINTENANCE",
    "BLD-5":  "BUILDING_STRUCTURAL",
    "ZON-3":  "ZONING",
}


def counts_by_category(df: pd.DataFrame) -> dict[str, int]:
    mapped = df.assign(
        category=df["code"].map(STATE_CATEGORY).fillna("UNCATEGORIZED")
    )
    counts = mapped.groupby("category")["violation_id"].size()
    result = counts.to_dict()
    # An UNCATEGORIZED bucket > 0 means the mapping is stale — surface it, never hide it.
    if result.get("UNCATEGORIZED", 0):
        result["_warning"] = "unmapped codes present; update STATE_CATEGORY"
    return {k: int(v) if isinstance(v, (int, float)) else v for k, v in result.items()}

The UNCATEGORIZED bucket is a deliberate tripwire. A silent dropna() would make the category counts sum to less than the total case count, and the first thing a state reconciler does is check that the parts add up to the whole.

Step 3 — Compute Median Cure Time

Cure time is the interval from when a violation is opened to when it is resolved, and states almost universally want the median because enforcement distributions are heavily right-skewed. Compute it only over cases actually resolved within the window, measure in whole days per the typical spec, and guard the empty case.

import numpy as np


def median_cure_days(df: pd.DataFrame, period: StatePeriod) -> float:
    resolved = df[df["status"] == "resolved"].copy()
    # State spec: count cases resolved *within* the reporting window.
    in_window = resolved[
        (resolved["resolved_at"] >= period.start) & (resolved["resolved_at"] < period.end)
    ]
    if in_window.empty:
        return 0.0
    cure = (in_window["resolved_at"] - in_window["opened_at"]).dt.total_seconds() / 86400
    # Clamp defensive: a negative interval means dirty data (resolved before opened).
    cure = cure[cure >= 0]
    return round(float(np.median(cure)), 1) if len(cure) else 0.0

The negative-interval clamp matters more than it looks: a data-entry error where resolved_at precedes opened_at produces a nonsensical negative cure time that would drag the median down and hand an auditor an obvious reason to reject the whole submission.

Step 4 — Compute the Repeat-Offender Rate

The repeat-offender rate — the share of parcels (or owners) with more than one violation in the window — is a common state metric because it measures whether enforcement is actually deterring recurrence. Define “repeat” against the exact grouping key the state specifies (usually parcel, sometimes owner), and report both numerator and denominator so the rate is reconcilable.

def repeat_offender_rate(df: pd.DataFrame, key: str = "parcel_id") -> dict[str, float]:
    per_key = df.groupby(key)["violation_id"].size()
    total_parcels = int(per_key.size)
    repeat_parcels = int((per_key > 1).sum())
    rate = round(repeat_parcels / total_parcels, 4) if total_parcels else 0.0
    return {
        "distinct_parcels": total_parcels,     # denominator, stated explicitly
        "repeat_parcels": repeat_parcels,       # numerator, stated explicitly
        "repeat_rate": rate,
    }

Emitting the numerator and denominator, not just the ratio, is what lets a state reviewer reconcile the rate against the raw case list. A bare “0.18” is unverifiable; “142 of 789 parcels” can be checked against the source of truth directly.

Step 5 — Reconcile, Serialize, and Hash the Submission

Before anything is sent, prove the metrics tie back to the source of truth: the sum of the category counts must equal the independently counted number of violations in the window. Only after reconciliation passes do you serialize the payload canonically and hash it, so the exact submitted bytes are re-verifiable.

import hashlib
import json


def build_submission(df: pd.DataFrame, period: StatePeriod) -> dict:
    categories = counts_by_category(df)
    category_total = sum(v for k, v in categories.items()
                         if isinstance(v, int) and not k.startswith("_"))
    source_of_truth = int(len(df))          # independently counted window rows

    if category_total != source_of_truth:   # hard reconciliation gate
        raise ValueError(
            f"reconciliation failed: categories sum to {category_total}, "
            f"source of truth is {source_of_truth}"
        )

    payload = {
        "period_start": period.start.isoformat(),
        "period_end": period.end.isoformat(),
        "counts_by_category": {k: v for k, v in categories.items() if not k.startswith("_")},
        "median_cure_days": median_cure_days(df, period),
        "repeat_offender": repeat_offender_rate(df),
        "total_violations": source_of_truth,
    }
    # Canonical JSON: sorted keys, no incidental whitespace -> reproducible hash.
    canonical = json.dumps(payload, sort_keys=True, separators=(",", ":"))
    payload["submission_sha256"] = hashlib.sha256(canonical.encode("utf-8")).hexdigest()
    return payload

The reconciliation gate is the single most important line in this file: it refuses to build a submission whose parts do not sum to the whole, converting a class of silent reporting errors into a loud failure before anything reaches the state. The sort_keys plus fixed separators give a byte-stable hash — the same discipline the parent dashboard uses for its exports.

Parameter and Flag Reference

Setting Recommended value Rationale for state reporting
Window interval half-open [start, end) Prevents boundary double-counting so quarters sum to the year.
Anchor date source explicit period.end Never datetime.now(); regeneration must reproduce the same window.
Cure-time statistic median (whole days) Matches typical state spec; resists right-skew from stale disputes.
Category mapping versioned dict Reviewed each annual spec update; unmapped codes raise a warning.
Repeat grouping key parcel_id (or owner_id) Must match the state’s exact definition of a repeat offender.
Reconciliation hard gate, raises Category sum must equal source-of-truth count before submission.
Serialization json.dumps(sort_keys=True) Canonical bytes yield a reproducible, re-verifiable submission hash.
Negative-interval handling clamp / drop A resolved-before-opened row is dirty data, not a real cure time.

Common Failure Patterns and Fixes

Metric definition drifts from the state spec

The state redefines “open violation” from end-of-period to any-time-in-period and the count silently doubles. Pin each metric’s definition in a docstring that quotes the spec version, and add a fixture test whose expected value comes straight from the state’s own worked example, so a definition change fails the suite.

Category counts do not sum to the total

A dropna() or a silent groupby drops unmapped codes, so the parts are less than the whole and the reconciliation gate — or the state reviewer — rejects the file. Route every unmapped code to an explicit UNCATEGORIZED bucket (Step 2) and let the Step 5 gate catch any residual mismatch before submission.

Window computed from wall-clock time

Deriving the period from datetime.now() means a report regenerated a day later covers a different window and produces a different hash, so it can never be reproduced. Always pass an explicit anchor end date and compute boundaries from it.

Non-reproducible submission hash

Two runs over identical metrics produce different hashes because json.dumps emitted keys in insertion order with default spacing. Use sort_keys=True and fixed separators (Step 5); add a test asserting the hash is stable across two builds of the same data.

Timezone mismatch at the boundary

Source timestamps in local time compare wrongly against a UTC window boundary, pulling cases into the neighbouring period near midnight. Normalize all timestamps to tz-aware UTC upstream (in the snapshot) and assert tz-awareness at the top of the aggregation rather than coercing silently.

Audit and Logging Guidance

Log one immutable record per submission attempt — successful or blocked. Capture the period boundaries, the submission_sha256, the reconciliation outcome (pass, or the exact mismatch counts), the source-of-truth total, and the version of both the category mapping and the underlying violation lifecycle model that produced the cases. Write it append-only to the same run ledger the parent dashboard maintains, and never overwrite a prior submission’s entry. A blocked submission is the most valuable log line you will keep: it is the paper trail proving the jurisdiction caught a reconciliation error before it reached the state, which is exactly the kind of internal control a state audit looks for. Retain these records — and the exact submitted payloads — for the full state records-retention period, and verify the ledger’s hash chain on a schedule so tampering surfaces in days rather than at audit time. When the submission travels to the state over an API, that path should carry the same controls described in securing municipal API endpoints for third-party integrations, so the report cannot be intercepted or altered in transit.

Frequently Asked Questions

Why a half-open window instead of an inclusive date range?

A half-open [start, end) interval guarantees every timestamp belongs to exactly one period, so a violation opened at the boundary midnight is counted once — not in both the closing and the opening quarter. That property is what makes the four quarterly figures sum to the annual figure, which is the first reconciliation a state reviewer performs. An inclusive [start, end] range double-counts the boundary instant and quietly breaks that identity.

How do I reconcile the metrics against a source of truth?

Count the violations in the window independently of the grouped metrics, then assert the category counts sum to that total before building the submission. The reconciliation gate in Step 5 raises if they disagree, converting a silent undercount into a loud, pre-submission failure. Reporting explicit numerators and denominators for every rate lets the state re-verify each figure against the raw case list rather than trusting a bare ratio.

What makes a state submission tamper-evident?

Serialize the finished metrics as canonical JSON — sorted keys, fixed separators — and hash the bytes with SHA-256. Store that hash with the submission and in the run ledger. Because the same metrics always produce the same canonical bytes and therefore the same hash, an auditor months later can recompute the digest from the archived payload and confirm the numbers submitted to the state were never altered afterward.