The Town of Eagle DMR tool

Every month, the operator of the Town of Eagle's wastewater treatment plant files a Discharge Monitoring Report in EPA's NetDMR portal: a legally binding account of what the plant discharged, computed under Colorado's precisely defined statistical rules. This tool automates everything up to the submission itself: it ingests lab and flow data, runs the permit-defined calculations, flags exceedances early, manages review, and produces submission-ready Excel, PDF, and NetDMR-importable outputs.

I inherited it mid-flight, after the previous developer and the previous project manager had both left. What the handoff contained: a half-built database pipeline with no concept of a permit, a form, a limit, or a season; an abandoned R Shiny front end, with the operator still assembling reports by hand; no documentation; no permit document, which had to be re-sourced from the state; and a flow record that simply ended in April 2022. Two years of it were recovered by extracting a CSV out of the deployed Shiny app bundle.

A clean-room rebuild in its own Postgres schema, with the legacy objects frozen and still serving the old pages untouched:

• —Permit-as-data configuration: permits, permitted features, the six reporting forms, parameters, and limits, with stat bases, seasonal tiers, effective-date ranges, and no-data codes all modeled as rows. A limit change is a data update, not a code change
• —A per-client adapter view normalizing four raw sources (two labs, the plant's daily sheet, manual entry) into one canonical samples contract: non-detects, qualifier exclusions, unit conversions, aliasing, dedup
• —Layered statistics views for daily, monthly, calendar-week, quarterly, and 24-month-rolling values, loading, percent removal, and instream metrics, each citing the state guidance page for the rule it implements
• —A compute engine that crosses the statistics with the permit configuration to fill each form's cells: seasons, geometric means, all-non-detect handling, excursion counting
• —The DMR Builder: a form shaped like the federal report, per monitoring period, with exceedance flags, click-through from any cell to its underlying samples, override and no-data editing behind mandatory audited reasons, and a Draft to Reviewed to Submitted workflow
• —Import with preview, chunked commit, a sha256 duplicate guard, rollback on failure, and batch provenance; exports that fill the client's real Excel templates, a print-optimized PDF, and the NetDMR CSV
• —Trend charts of monthly values and 24-month rolling averages against the permit's limit lines, so drift toward a limit is visible before it is a violation

All of it on a live shared multi-client database with no staging, under Postgres 10: every row scoped by permit, migrations hand-authored as reviewed, idempotent, commented SQL (twenty of them) and executed only manually, by a human.

The audit came first, because the inherited numbers could not be trusted. Reverse-engineering the legacy views against the state's guidance found material defects, every one later confirmed in validation: the “2-year rolling average” was in fact a ~30-year average; mercury was reported a thousandfold too high through a unit relabeling; the 7-day averages used the wrong method entirely; estimated and lab-rejected results were averaged into legally reported values.

The harder problem was missing ground truth: no permit document, no historical files. The unlock was that the permit record is public. EPA's ECHO service publishes both the limits and the town's full submitted-DMR history, so the permit configuration was seeded from real submission records plus ECHO data, every row flagged unverified until the permit could confirm it. The cross-check matched 22 of 26 limits exactly, and revealed a sixth reporting form the handoff had never mentioned.

The same public record became a regression suite. A read-only script recomputes five-plus years of historical periods and diffs them against every value the town actually submitted to EPA: roughly 330 exact or close matches, every remaining difference classified as a data-vintage effect or a deliberate correction, and two genuine findings surfaced to the client rather than silently fixed. When the permit document finally arrived, it served as the final verification pass instead of the starting point, and it still caught the one thing ECHO could not: an ammonia limit tier structure that was materially wrong.

And it is built as a product, not a one-off. The definition of done for the architecture is that onboarding the second district is configuration rows plus one adapter view, with zero application code changes, and the onboarding runbook for that already exists.

Built with the spec-and-loop workflow, in a setting that shows why the guardrails are the point. The repo's CLAUDE.md opens with the hard rule: agents never run migrations or write to the database. They author reviewed, idempotent, commented SQL files; I execute them manually against the live system. The plan and spec documents are the source of truth agents must read first, and the primary sources (the permit, the fact sheet, the state guidance PDF, the client's real submission records and Excel templates) live in the repository as context, which is why every calculation could cite the page it came from.