Water System Risk & Funding Priority Index
An explainable public-data screening system for prioritizing Ohio public drinking water systems for review using compliance history, enforcement history, vulnerability, drought exposure, funding context, small-system factors, and data quality.
Evidence
Screenshots from the live dashboard, map evidence, and the scored Ohio prototype.
No large system reached the high-review tier
Screening observations taken directly from the scored Ohio prototype. These are signals from public data, not regulatory findings about any individual system.
- County concentration. Columbiana County had the largest number of high-review records (13), followed by Mahoning and Summit counties (11 each). High-review records spread across many counties rather than concentrating in a single metro area.
- Size pattern in the highest tiers. Every one of the 188 High Review records was a small, very small, or medium system; no large system reached the High Review tier. Small systems showed the highest high-review rate (2.5%, versus 1.0% for very small and 0% for large), consistent with the focus on smaller systems that often have less staff and grant capacity.
- Geometry source is tracked, not hidden. Only 1,077 of 16,339 records (6.6%) have an EPA service-area polygon (207 system-sourced, 870 modeled); the rest fall back to county centroids or are unmatched and are labeled "Approximate Location." Each record carries an explicit geometry-source tier so modeled or centroid placement is never overinterpreted as a verified service-area boundary.
Problem
Public drinking water data is spread across regulatory downloads, geographic boundaries, vulnerability datasets, drought feeds, and funding records. The signals are useful, but they are not naturally organized into a single review-priority workflow.
The project needed a transparent way to identify which small systems may deserve earlier review for compliance support, technical assistance, infrastructure funding research, or resilience planning while clearly stating what the model can and cannot claim.
Users and decisions
State and local infrastructure planners, utility-support programs, and funding analysts are the intended users. The tool helps them prioritize records for review, inspect why a system was scored, and identify geography or source-data gaps before deeper due diligence.
The dashboard is a portfolio screening model. It is not a regulatory determination, legal finding, engineering siting tool, official risk assessment, or statement that any water system is unsafe.
Public sources to a scored, mapped, filterable dashboard
The system builds a scored Ohio water-system dataset from public sources, validates the outputs, preserves service-area and source-water protection geography, loads the dashboard data into Postgres, and serves filtered summaries, maps, system lists, and detail views through a FastAPI backend.
The live frontend keeps filtering, sorting, pagination, map boundaries, and source-water overlays server-side or on demand so the browser only requests the records and geometry needed for the current view.
Architecture
Data flow
Raw source files are cleaned into consistent water-system, compliance, enforcement, geography, funding, and drought/vulnerability features. The scoring step normalizes each component to a 0-100 scale, applies documented weights, assigns review tiers, and writes validation-ready outputs.
The web export creates an application dataset, the API loader seeds Postgres, and the frontend queries focused endpoints for metadata, summary cards, tier counts, ranked systems, single-system detail, and lightweight map points.
Tools used
Key features
- Scored 16,339 Ohio public water system records.
- Real EPA service-area boundary polygons (1,077 systems) on the map, with a geometry-source hierarchy from system-sourced and modeled boundaries down to county-centroid approximations.
- Ohio EPA source-water protection (SWAP) areas as a separate overlay - 7,390 protection polygons covering 3,751 systems - kept semantically distinct from service areas (where supply is protected vs. who receives water).
- Transparent weighting for compliance, enforcement, vulnerability, drought, funding gap, small-system context, and data quality.
- Server-side search, filters, sorting, pagination, map points, map boundaries, and detail endpoints.
- Map layer controls and a per-system geography-evidence panel (boundary type, provider, PWSID match, area, confidence, limitations).
- Validation report expanded from 13 to 22 checks (adding geometry-source, boundary dissolve, count reconciliation, simplification-quality, and SWAP source-water protection checks) passing before publication.
- Model validation: a held-out backtest against the 24 months after a 2023-12-31 cutoff (index ROC AUC 0.740), benchmarked against a prior-violation-count baseline (0.734) that it does not beat, plus a weight-sensitivity analysis and a fairness audit showing the compliance signal is uncorrelated with social vulnerability. All of it documented in a model card, including the negative result.
Tradeoffs and constraints
The model favors transparency and explainability over black-box prediction. That makes the reasoning easier to inspect, but the weights remain analytical assumptions that would need subject-matter review before operational use.
Many systems can be scored from SDWA records, but not every system has high-confidence service-area geometry. The dashboard surfaces geography source, confidence, and limitation notes so users can distinguish map convenience from verified service-area precision.
Methodology
Appropriate use: portfolio demonstration of public-data engineering, transparent scoring, API-backed dashboard delivery, and limitation-aware analytics.
Inappropriate use: regulatory decisions, legal findings, engineering siting, official safety conclusions, or claims that a specific system is unsafe.
The weighted index does not beat counting prior violations
The finished platform demonstrates the full path from public source data to validated analytical outputs, API delivery, and an interactive dashboard that states its limits clearly.
Evidence against
0.740 vs 0.734ROC AUC of the weighted index versus counting prior violations and nothing else. The paired difference is +0.007, 95% CI −0.015 to +0.029. The index does not beat the one-feature baseline.Evidence against
0.781ROC AUC of the index's own compliance component used alone, which is higher than the full index. The paired difference of −0.040 has a 95% CI of −0.060 to −0.021, so the remaining components measurably hurt.Evidence against
0.447ROC AUC with compliance and enforcement removed. Below chance. The vulnerability, drought, funding, and small-system terms carry no independent ranking signal in this backtest.Limitations
Source data is not real-time. County-level vulnerability and drought context are fallback indicators, not household-level exposure measures. County centroid mapping is suitable for screening only where service-area geometry is unavailable and is labeled as approximate.
Unmatched funding records do not prove a system received no funding, and a high review-priority score is a screening signal rather than a finding about water safety.
The index does not beat counting prior violations. The baseline comparison has now been run: ranking every system by its prior 36-month violation count and nothing else scores ROC AUC 0.734 against the full weighted index's 0.740. The paired difference is +0.007 with a 95 percent interval of −0.015 to +0.029, so the index wins in 72 percent of bootstrap resamples and that is not a difference anyone should act on. Roughly 45 percent of the index weight is prior compliance and enforcement history, which is most of the explanation.
The composite is worse than one of its own parts. The compliance component alone scores 0.781, higher than the full index, and the paired interval on that gap excludes zero in every one of 2,000 resamples. Strip compliance and enforcement out entirely and the remaining terms score 0.447, below chance. One of those terms explains itself: funding_gap_component is constant at 35.0 for all 16,339 systems because no Ohio state revolving fund export was ever staged, so it carries 15 percent of the weight and exactly zero ranking information. The honest summary is that this index is a well-documented delivery vehicle for a compliance-history signal, and the weighting on top of that signal currently subtracts from it.
"Leakage-free" overstates the design. Only the compliance and enforcement components are recomputed as of the cutoff date. The five static components are read from the current scored file, which is built partly from post-cutoff data. That anachronism happens to work against the index rather than for it, since the static terms score below chance, but the label should not have been applied to a backtest where five of seven inputs are not time-aware.
Decisions and rejected alternatives
Transparent additive weights over gradient boosting. A tree ensemble would almost certainly score higher. I chose a scoring model a regulator or utility manager can read line by line, because the intended use is deciding where to send scarce review capacity, and a ranking nobody can interrogate does not survive that conversation. The price is legible in the numbers: ROC AUC 0.740, and the backtest since showed the hand-set weights do not even beat the single feature they are built on. Interpretability was worth paying for. It was not worth paying this much, and the fix is to cut the weighting rather than to abandon the transparency.
County centroid mapping where service-area geometry is missing. The alternative was to drop those systems entirely. Dropping them would have silently excluded exactly the small rural systems the index exists to surface, so I kept them and labeled the geometry as approximate on the map. Selection bias was the worse failure here than spatial imprecision.
What I got wrong: publishing lift without a competing baseline. The index consumes prior compliance history, so lift over nothing was never evidence of value. The baseline has now been run and the index does not clear it. The repository's own model card and limitations notes already said the model "roughly ties" a prior-violations baseline and warned against overselling it; the public case study did not, and for a while this page claimed more than the project itself did. That gap between what the docs knew and what the marketing said is the actual failure here.
What I would do next, in order. Drop or re-weight the components that measurably hurt, rather than keeping them because the composite looks more sophisticated. Stage the Ohio revolving-fund data so the funding term stops being a constant occupying 15 percent of the weight. Make the static components time-aware so the backtest deserves the word leakage-free. Then re-run the comparison, and if the index still cannot beat prior-violation-count, ship the baseline and keep the interface, because the interface is the part that was actually working.
Still open. Deeper SRF project matching, tract-level SVI where geometry supports it, PostGIS-backed spatial processing, and automated refresh checks.