The Town of Eagle DMR tool

Each reporting period, 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. Most of the six forms report monthly, and the rest run on their own cadences. NetDMR takes no submission from an API, so the last step is always a person typing into its web form, and the tool's job is to make that step mechanical: it ingests lab and flow data, runs the permit-defined calculations, flags exceedances early, manages review, and produces outputs shaped exactly like the form being typed into, so filing is transcription rather than judgment.

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. What none of it added up to was a DMR tool: nothing it produced could be filed as-is, nothing ingested the lab and flow data it ran on, and the interface was not one an operator could work in.

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 a CSV laid out like the NetDMR form itself
• —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 inherited numbers were assumed correct at first: they were the numbers the town had been submitting for years. The defects surfaced during the rebuild, when reverse-engineering the legacy views against the state's guidance stopped agreeing with them, and every one was confirmed in validation afterward: 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.