Audit Log

This document records data-quality issues discovered during pre-launch and post-launch audits, what was done about each one, and what remains open. Entries are dated and listed newest-first.

The purpose is twofold: an internal record so issues don't get lost between work sessions, and a transparent account for external readers (environmental attorneys, watchdog groups, journalists) who want to understand how the underlying data is verified.

Permit Minder's defensibility standard: every number on the platform should be something an environmental attorney would be comfortable forwarding to opposing counsel. This log is part of how that standard is maintained.

2026-07-14 — 49 readings were compared against their permit limit in the wrong direction, and have been withdrawn

A permit limit has a direction. Most are ceilings: the reading must stay below the limit. Some are floors: the reading must stay aboveit — a treatment plant required to remove at least 89.25% of a pollutant, for example.

Direction is not something we are free to infer. It comes from a field the regulator publishes alongside the reading, called the statistical basis. Our rule, written down before this happened, is that if the source does not establish the direction, we do not publish a comparison.

The rule was not enforced in the code. Where the statistical basis was missing, the software did not stop — it treated the row as a ceiling. That is not a neutral assumption. On a floor limit it reverses the answer:

A facility required to remove at least 89.25% of a pollutant reported removing 9.09%. Compared as a ceiling, the software asked “is 9.09 greater than 89.25?”, found that it was not, and recorded the reading as within its limit.

49 readings were affected, across 4 Pennsylvania permits, for monitoring periods in 2021. All 49 are minimum percent-removal parameters (CBOD20, CBOD5, BOD5, and Total Suspended Solids). All 49 were reported below their limit and displayed as though they were not.

The error ran in one direction: it understated. It did not create an exceedance against any facility; it concealed readings that appear to fall short of a minimum.

Why the 49 are being withdrawn rather than corrected. We went back to the source. Pennsylvania DEP publishes the statistical basis field emptyfor these rows — we confirmed this against the original data files as we received them. There is nothing to correct them from. The parameter names say “Minimum,” and the federal secondary-treatment standard sets minimum removal rates, but neither of those is the regulator’s statement about this reading. Asserting a direction on that basis would mean publishing a comparison the source never supported — which is the same mistake in the opposite direction.

So we assert nothing. The 49 readings are being withdrawn from the site as this entry is published. They remain in our records, unpublished, so that this account can be checked.

What has changed.The software now stops on any reading whose direction the source did not establish, and holds it instead of guessing. The database no longer supplies a placeholder value when the basis is missing; a reading that arrives without one is now rejected outright rather than quietly labeled “UNKNOWN.” A standing check runs against the live database and fails if any published reading carries that placeholder again.

What remains open.These 49 readings may represent real shortfalls against a minimum treatment requirement. We cannot say so on this data. Whether EPA’s national ICIS-NPDES dataset carries these same permit-periods with the basis populated is being checked; if it does, the readings can be restored with a direction the regulator actually stated. Until then they stay withdrawn.

2026-07-14 — Two earlier entries described a collection schedule that no longer runs

The entries dated 2026-06-13 and 2026-06-23 describe how Pennsylvania’s long-period records were collected. They say, in the present tense, that a per-permit five-year pass “is now scheduled to run daily” across 54 shards, “completing a full rotation in about 54 days,” and that a newly-closed semi-annual or annual period may therefore lag “up to about one 54-day rotation.”

That is no longer how Pennsylvania is collected, and it has not been since 2026-07-12.

Those passes existed to work around a limitation of the PA DEP eDMR system: its reporting endpoint returned a monitoring period only when the query window fully contained it, so semi-annual and annual periods were invisible to a monthly query and needed a separate per-permit sweep. That collection path was retired on 2026-07-12. Pennsylvania is now collected from the EPA’s ICIS-NPDES bulk discharge-monitoring feed — the same federal feed used for every other state currently published — on a quarterly cadence.

The federal feed publishes every monitoring period in the same file, whatever its length. So the sweep is not merely stopped — the problem it solved does not arise in the current path, and the 54-day rotation lag described in those entries no longer applies.

Why this correction is recorded rather than edited into the older entries. Those entries were accurate when they were written, and this log’s value depends on it being possible to see what was said and when. Rewriting them would make the record tidier and less true. They stand, each now carrying a pointer to this entry; this entry supersedes them on the question of how Pennsylvania is collected today.

The earlier entries remain correct on their own subject — the alert-suppression guarantees they describe (historical records recovered by a backfill are never emailed) are unchanged and still enforced.

A related sync problem, recorded because it is the more useful finding. This page and the internal log it mirrors had drifted apart. The 2026-05-19 entry’s scheduling paragraph was corrected in the internal log on 2026-06-16, when the long-period passes were put on a recurring schedule — and this page’s copy was not touched. So from 2026-06-16 until this entry — 28 days — this page told readers that recurring scheduling of the long-period passes “remains an open item” and that periods were “not yet refreshed automatically,” while the internal record said the opposite. That is a statement about how completely we collect, and it was wrong in the direction of understating our coverage.

Because that edit was made in place, the two files now hold different text for the same paragraph: the internal log carries the June rewrite, this page carries the 2026-05-19 original. Neither is rewritten again here — each was accurate when it was written — and both now carry a pointer to this entry. The two surfaces are required to move together, and this is the record of the one time they did not.

2026-07-13 — Pennsylvania restored from the federal feed; records briefly removed in error, and reinstated

This entry covers a sequence of changes made on one day, including a mistake and its correction. It is written in the order the events happened.

1. Pennsylvania was re-collected from the federal feed. After the withdrawal described in the entry below, Pennsylvania was collected from the EPA's ICIS-NPDES feed — the source every other state is collected from — which reports the value qualifier in its own column rather than as a symbol inside the value. 7,702 exceedance records were published.

2. A duplicate period was identified, and records were removed on a mistaken basis. The re-collection covered federal fiscal year 2025 (October 2024 through September 2025), a period the retired Pennsylvania DEP corpus already covered. On the understanding that the feed republished the same discharges, the DEP records for that period (8,010) were withdrawn.

That understanding was wrong, and the check that would have shown it had not been run. The two sources are not equivalent. For the same permit and period they use different parameter names, report values and limits in different units — in some cases differing by a factor of 1,000 (micrograms against milligrams per litre) — and assert different permit limits. Of the 8,010 DEP records withdrawn, only 134 had any counterpart in the feed. 7,876 exceedances were removed from the platform that the feed does not report.

3. The records were reinstated. All 8,010 were restored the same day. They were never deleted — a withdrawal marks records superseded and leaves them in the underlying table — so the reinstatement returned them exactly as they were.

4. The reinstatement was five records too wide, and was corrected. 8,005 records had been withdrawn in step 2, but 8,010 were reinstated. The five extra had been withdrawn earlier, for unrelated and correct reasons, and the reinstatement did not distinguish them: four pH measurements, which this platform withholds under 40 CFR 401.17 and does not report as exceedances at all; and one measurement of 11.0 against a limit of 45.0, which is not an exceedance and had been corrected away in June.

They were identified against a database backup taken the previous day and withdrawn again. They were served for approximately twenty minutes. The published total now reconciles exactly: 42,235 records before the re-collection, plus 7,702 from the feed, is the 49,937 served today.

What the platform now serves. Both sources are published for fiscal year 2025 and neither is treated as authoritative over the other, because which of them is correct is not yet established.

Exceedance records currently served, all states:

A known duplication. 134 discharges are currently reported twice — once from each source. Of those, 118 are the same measurement expressed in different units. The remaining 16 have not been reconciled. All 134 are left in place rather than removed, because removing them correctly requires normalizing units across the two sources first, and a removal performed without that step is what caused the error described above. This is disclosed rather than quietly corrected.

What remains open. Which source is authoritative for Pennsylvania — and in particular which permit limit is enforceable where the two disagree — is an open question. Until it is answered, both sources are shown.

2026-07-13 — Three qualifier codes the federal feed publishes were not recognized, and would have been recorded as measurements

What happened. Reviewing every value qualifier the EPA feed publishes — 9.4 million records across seven states — found seven distinct codes. Three of them carry meaning the platform was not reading.

Why it matters. “<=” is a censored reading: the laboratory is reporting a bound, not a measurement. Recording “<= 3” as a plain “3” asserts a number the laboratory did not report — the same defect described in the entry below, which withdrew 21,568 Pennsylvania records the same day. “E” is an estimate, and we do not know what it was estimated from.

Whether it affected published data. No. No Pennsylvania record carries any of these codes, and Pennsylvania is the only state currently published. The finding is a defect in what the platform would have published: about 4,753 records had the remaining states been loaded without this fix.

What was done.

• “<=” is now treated as a non-detect, and “>=” as a reading above the detection limit. Both are screened out of headline counts and alerts, as “<” and “>” already were.
• A distinction was drawn that the platform had not made. A qualifier bounds the true value; it does not state it. Against a maximum limit of 10, a reading of “> 10” proves an exceedance (the true value is strictly above 10), but “>= 10” does not (the true value may be10). Against a minimum limit of 2, “< 2” proves an exceedance but “<= 2” does not. The two forms differ in exactly one case — the reported number equal to the limit — and the platform now reports an exceedance only where the source proves one.
• “E” (estimated) values are withheld rather than guessed at. They are counted and reported on every collection run: “E” is routine on flow parameters, so the withheld volume is expected to be substantial, and a withheld record that nobody can see is indistinguishable from one that was never received.
• Every qualifier is displayed with its value on the permit page, the CSV export, and the PDF export.

What remains open. The meaning of “E” in this data set is not established, so those records are withheld rather than published. Establishing it is tracked separately.

2026-07-13 — Qualifiers were not preserved on a set of Pennsylvania records, and those readings were stored as measurements

What happened. On 2026-07-13, 21,568 Pennsylvania records were loaded into the platform. None of them carried a value qualifier. All were stored as though the reading had been measured directly.

That is not what the source says. Pennsylvania operators report a value that may carry a leading less-than or greater-than symbol — “< 3”, “> 2420” — and roughly a quarter of the rows in the source files do. The collection step read the number and discarded the symbol. A later step, finding no qualifier on the record, supplied “measured” in its place.

Why it matters. A reading the laboratory reported as belowa detection limit (“< 3”) was stored as a measurement atthat limit (3). A reading reported as above the range of the test (“> 2420”, the ceiling of the standard IDEXX Quanti-Tray method for fecal coliform) was stored as an exact measurement of 2420. A detection limit stored as a measurement can be compared against a permit limit and produce an exceedance the laboratory never reported.

This is the defect the 2026-05-08 entry below assumed could not reach per-row data. That entry states that the permit detail page, exports, and per-row data show the value the laboratory actually reported, with the qualifier preserved alongside the number. For these 21,568 records, that was not true.

The mislabel did not defeat our safeguards. It made the rows ineligible for their protection. This is the part worth understanding, because it is not what a “missing column” sounds like.

Permit Minder treats a non-detect deliberately and carefully. A non-detect whose substituted value would otherwise exceed the permit limit is classified as non-detect screening: kept out of headline exceedance counts, kept out of alert digests, and never characterized as a measured exceedance (see “Non-detect values” in the methodology). A non-detect below the limit is not an exceedance at all. Every one of those safeguards was working correctly throughout.

A reading relabelled “measured” does not trip them. It walks past them — because each one asks what kind of reading it is, and the row now gives the wrong answer. The screening classification, the headline exclusion, the alert filter: all functioning, all bypassed, because the reading misstated its own kind at the point where those decisions are made.

No alerts were sent. Alert delivery was suppressed for the entire load, so no subscriber was emailed about any of these rows. It is worth being precise about why: these rows were eligibleto alert, because the alert filter passes rows marked “measured” — and they had been marked “measured” in error. Suppression is what held, and it held only because it does not ask what kind of reading it is. The filter that does ask had already been given the wrong answer.

This is why the repair is a refusal rather than a better default. A missing value fails closed — it stops. A wrong label fails open, through every check that trusts the label.

A second, wider gap was found while repairing the first. Pennsylvania is now collected from the EPA’s national feed, which reports the qualifier in a dedicated column rather than as a symbol inside the value. That column was being read and classified correctly — and then discarded three times in a row, by three separate lists of fields to carry forward, before it reached the store. The classification was never missing. It was computed, correct, and dropped. Had a national load run, every state would have been published the same way.

What was done. A record that does not carry its own qualifier is now refused rather than stored with an assumed one; absence of a qualifier is not evidence of a clean numeric reading. All three lists now carry the qualifier through to the store, along with the value exactly as the source reported it — symbol and all — so a reader can check a published number against what the laboratory actually said.

The affected records have been withdrawn. On 2026-07-13 at 14:24 UTC, all 21,568 were superseded. They are no longer served: they do not appear on the site, in search results, or in the alert digest, each of which reads only non-superseded records. This was verified three ways — the write path's own report, a direct count of the table, and the alert query's own filter — which agree: 0 affected records still served, 21,568 withdrawn, and the number of records served falling from 63,803 to 42,235.

They were withdrawn, not deleted. Each remains in the table, marked as superseded, with its values intact. That is deliberate: this entry discloses that those records were published, and deleting them would destroy the evidence of the thing being disclosed. A reader who followed a link to one of them can still be shown what happened to it.

What remains open. Pennsylvania is being re-collected from the EPA feed, which carries the qualifier. The affected records could not be corrected in place, because the qualifier is not present in the stored intermediate data they were built from. Re-collecting was never going to withdraw them, which is why withdrawal came first: the re-collected records are attributed to the EPA feed while the affected records are attributed to the retired state scrape, so a re-collection would have sat alongside them rather than replacing them, and Pennsylvania would have appeared twice — once correct, once not, with nothing visible to tell a reader which was which. The re-collected records are expected to contain both non-detect readings (a reading below a detection limit is still below a minimum limit, so it is reported) and above-range readings; a rebuild showing none of either has not worked, because that is what the affected load looked like. No further state loads will run until the rebuild is verified.

How this was found. A test that checks the distribution of qualifier kinds against expected bounds. It found the readings after they were published, not before. The bounds were not adjusted to accommodate the new data.

2026-07-12 — An unreadable “less-than” reading no longer produces an exceedance

Scope. A correction to how the pipeline handles a malformed detection-limit reading. No rows in the live Pennsylvania data are known to be affected — a survey of the current records found no reported value beginning with “<” that could not be read. The change is preventive: it closes a path by which a value the source never supplied could have been reported as an exceedance.

What the issue was. A reported value beginning with “<” denotes a non-detect at a stated detection limit — “<0.05” means below 0.05 — and the pipeline records half that limit. When the text after the “<” could not be read as a number, the pipeline recorded zero instead.

Zero is not a neutral placeholder. Some permit limits are minimums rather than maximums: dissolved oxygen, for instance, must stay above a floor. Against such a limit a zero falls below the limit, so the record would have been reported as an exceedance, and the percentage would have been rendered as 100% below the limit. That figure would have been an artifact of the placeholder, not a measurement the source ever provided. This is the same category as the detection-limit handling addressed in the 2026-05-08 entry below (“Headline exceedance percentages now exclude non-detect readings”).

What was done. A value that cannot be read is now recorded as not comparable rather than as zero, and no exceedance determination is made, because there is nothing to compare against the limit. A reading that is not an exceedance is not carried in the exceedances table, so an unreadable reading now produces no entry there, in the same way any within-limit reading produces none. The underlying source record is unaffected and remains checkable at the state agency.

Well-formed non-detects are unchanged, and a genuine reading below a floor limit is still reported. The corrected behavior is covered by tests so it cannot change unnoticed.

2026-07-10 — Pennsylvania pH hold: 15 residual rows held after a pre-fix ingest

Scope. A same-day follow-through on the 2026-07-09 correction below. Between that correction and the moment the retrofitted Pennsylvania path reached production, an ingest ran on the earlier code and re-computed 15 Pennsylvania pH rows across 9 permits. They were held the same day. The active Pennsylvania exceedance count moved from 42,202 to 42,187. No other parameter or state was affected.

Why. These rows are the same category held under 40 CFR 401.17 (see the entry below): a monthly DMR pH value cannot establish an excursion-duration determination, so it is not a defensible exceedance. They existed only because the ingest that produced them predated the retrofit reaching production.

What was done. The 15 rows were held using the same mechanism the site already uses to exclude a superseded record from search and facility pages; no rows were deleted and the action is reversible. Verified afterward: no active Pennsylvania pH rows remain on the public search or facility pages, and pH no longer appears in the parameter list. The retrofit that prevents recurrence is now live in production, so later ingests run on the corrected path.

2026-07-09 — Pennsylvania pH exceedances retired under 40 CFR 401.17 (1,753 rows removed)

Scope. A data-quality correction. 1,753 Pennsylvania pH rows were removed from the exceedances dataset; every affected row is preserved in a backup table. The Pennsylvania exceedance count moved from 43,943 to 42,190. No other parameter or state was affected.

Why. pH is not a simple over/under-limit parameter. Under 40 CFR 401.17, brief pH excursions outside the permitted range are allowable for facilities with continuous monitoring, subject to duration limits — so a reported pH value outside the range is not automatically a reportable exceedance. A monthly DMR summary carries only the value, not the excursion duration, so it cannot establish whether a given out-of-range reading qualifies. Flagging these as exceedances asserted a determination the source data cannot support — which fails the standard this log exists to protect.

How this arose. The pH hold was already implemented on the newer multi-state classifiers, which hold pH and never flag it, but was never retrofitted onto the older Pennsylvania ingest path. Pennsylvania was the only path still computing pH exceedances.

What was done. The affected rows were backed up (reversible), then removed from the live dataset (verified: none remain for Pennsylvania). The Pennsylvania ingest path was retrofitted to hold pH, with unit tests added so a re-ingest cannot recreate pH exceedances.

2026-07-02 — Chain-of-custody corrections log applied (migration 079; zero production data change)

Scope. An infrastructure record, not a data-quality finding. Migration 079 added an append-only chain-of-custody log (exceedance_corrections) and extended the single write function apply_dmr_rows to populate it. No permit data was added, removed, or modified: the migration contains no statement that writes production rows, and a post-apply check confirmed zero test residue.

Why. PA DEP amends eDMR records in place after initial publication (for example, when a facility submits a corrected DMR). The pipeline already handled these amendments correctly and stamped each affected row as corrected, but the prior value was overwritten in place — the record proved a correction happened, not what the value was before it. From this change forward, every correction also appends a log row recording the old and new reported value, permit limit, exceedance percentage, and monitoring-period bounds, together with the raw source strings (the state's own rendering of the value at each ingest), the identity of the ingestion run that caught the amendment, and the timestamp of the correction. The log entry is written in the same database transaction as the correction itself, so the two can never diverge.

Enforcement. The log is append-only by database trigger, not by convention: updates, deletes, and truncation are refused unconditionally, and a row with recorded custody history cannot be deleted from the main table without explicitly clearing the log first. The log is internal audit data and is not exposed through any public read path.

Verification. The migration's built-in verification block walked the full amendment scenario on a synthetic permit before committing: original ingests and unchanged re-ingests write no log rows; each correction type writes exactly one log row with the correct before/after values; and edits or deletions against the log are refused. Any failed assertion would have rolled back the entire apply. A post-apply verification by direct database query confirmed the table, triggers, index, and access posture are live, the log starts empty, no synthetic rows remain, and production data was untouched (43,717 total rows / 43,714 active, unchanged by the apply).

Alerting unchanged. Alert eligibility semantics are byte-for-byte identical: corrections to already-reported rows still do not trigger new email alerts. Whether a correction of a given magnitude should notify subscribers is a separate product decision; this log is the record such a feature would draw on if built.

Status. Applied and verified on 2026-07-02; no rollback required. From this date forward, Permit Minder retains a tamper-evident record of every state amendment it observes, alongside the existing per-row source links back to PA DEP eDMR.

2026-06-23 — Pre-national-baseline safety migrations applied (076 + 077 + 078; zero production data change)

Scope. Three backward-compatible schema and write-function hardening migrations were applied to the production exceedances table and its read/write functions, ahead of any future multi-state (EPA ECHO/ICIS-NPDES) data load. This is an infrastructure record, not a data-quality finding: no permit data was added, removed, or modified. A full-row content fingerprint taken before and after the apply was byte-for-byte identical (43,174 rows), and the data-composition counts were unchanged. The production table remained 100% Pennsylvania (PA DEP eDMR) throughout — zero EPA ICIS rows and zero non-PA rows.

Why these three. The national-baseline machinery had been merged but held behind unapplied migrations so the live database could never silently mislabel a row's source. Each migration removes a way a future non-PA row could be stored without an explicit, correct source.

076 — optional state filter. Adds an optional p_state_code filter to the search_exceedances read functions. Default behavior is unchanged: omitting the parameter (as the live site does) returns exactly what it did before; p_state_code => 'PA' returns the same set, and an unknown state returns zero.

077 — drops the source column default. Removes the data_source column default. An omitted source is now null (treated as Pennsylvania legacy by the read side) rather than being asserted as a specific federal source. Every existing row already carries an explicit data_source, so no row changed.

078 — source guard on the write path. Adds a guard to the single write function apply_dmr_rows so that a row carrying non-PA / EPA-ICIS indicators (a non-PA state_code, a version_nmbr, or a limit_set_designator) with an omitted source is refused rather than silently defaulting to the Pennsylvania source. Pure-Pennsylvania rows keep the existing fallback unchanged.

Pre-apply safety. A row-exact snapshot of the exceedances table (43,174 rows) was captured before applying, and scoped, drift-verified rollback artifacts were captured for each migration. Each migration was applied in its own transaction and self-committed only if its own hard verification block passed.

Verification. The full-row fingerprint was identical before and after; data composition was unchanged (zero EPA ICIS rows, zero non-PA rows); alert eligibility was unchanged; and the read-path smoke test returned the expected first page of results and site statistics (2,510 permits / 43,171 exceedances). All three migrations were independently confirmed live through a separate connection.

Status. Applied and verified; no rollback required. These migrations are now the live schema baseline for subsequent multi-state work, which remains separately gated. No ingest was run, no non-PA data was loaded, and no alert, cron, storage, or frontend change was made.

2026-06-13 — Alert-eligibility safety: historical suppression, cleanups, cron verification, and durable hardening

Scope. A set of alert-safety corrections ensuring that historical exceedance rows recovered by the long-period backfill are never emailed to subscribers as if newly published. No public permit data was changed: the only affected column is alert_eligible_at, which governs whether a row is eligible for an email alert. All measurement values, monitoring periods, exceedance percentages, and per-row source links are unchanged.

Background — why this matters. The daily alert digest decides whether an exceedance is “new” to a subscriber. Rows recovered by the quarterly/semi-annual/annual long-period backfill carry a recent ingest timestamp, so without a dedicated eligibility signal they could be sent as current events. Mailing an environmental attorney a years-old exceedance as “newly available” is a defensibility failure; the work below makes alert eligibility explicit and historical-safe.

1. Alert-eligibility column (migrations 073/074). A dedicated alert_eligible_at timestamp was added to public.exceedances. NULL means a row is never alert-eligible (a suppressed historical recovery); a timestamp means the row is a current publication a subscriber may be alerted about. The alert sender now keys on this column, not on the ingest timestamp, so a suppressed row is excluded at the database layer regardless of how recently it was ingested.

2. Historical-backfill suppression cleanup (2026-06-12). The May 2026 five-year historical bulk load had left 39,185 older rows alert-eligible. These were set to alert_eligible_at = NULL in a single guarded transaction, reducing the alert-eligible set from 41,125 to 1,940 rows and preserving eligibility only on genuinely recent rows. A row-exact pre-change snapshot was retained as a rollback anchor.

3. Daily alert-cron verification (2026-06-13, PASS). The first daily alert run after the cleanup was verified read-only: it completed successfully (0 sent, 1 skipped, 0 errors); zero long-period rows were alert-eligible; no historical row was poised to send; and the previously-noted incident row (PA0254967, a February 2021 monitoring period) remained suppressed (alert_eligible_at = NULL).

4. Tier-3 short-span residual cleanup (2026-06-13). A read-only review found 14 additional rows — old 2021 monitoring periods recovered by the per-permit long-period sweep — that remained alert-eligible because the initial suppression rule keyed on monitoring-period length(treating month-length periods as current) rather than period age. None had been emailed and none matched any active subscription. They were set to alert_eligible_at = NULL in a guarded, ID-pinned transaction, reducing the alert-eligible set from 1,940 to 1,926. A row-exact snapshot was retained.

5. Durable hardening (code; PR #107, merged 2026-06-13). The two long-period ingest paths — statewide quarterly (Tier 2) and per-permit five-year (Tier 3) — now refuse to run unless an explicit alert-suppression mode is selected, and suppression is computed by monitoring-period age against a fixed freshness window, not by period length. This prevents the same class of row from being introduced as alert-eligible by any future long-period sweep. The recurring monthly ingest (Tier 1) is unchanged and remains alert-eligible by default.

Current verified state (2026-06-13). Production holds 1,249 active rows whose monitoring period spans more than one calendar month (quarterly, semi-annual, and annual periods recovered by Patch E); all 1,249 are alert-suppressed (alert_eligible_at = NULL). The active alert-eligible set is 1,926 rows, all recent monthly-period rows. Zero long-period rows and zero old historical short-period rows are alert-eligible. Production totals: 42,375 total / 42,374 active / 1 superseded.

Recurring long-period coverage — now scheduled. The monthly statewide pass (Tier 1) runs weekly. The quarterly (Tier 2) pass now runs on a recurring schedule (scrape_quarterly.yml), and the per-permit five-year (Tier 3) pass is now scheduled to run daily (scrape_tier3.yml, cron 0 8 * * *) across 54 deterministic crc32 shards, completing a full rotation in about 54 days. Tier 2 and Tier 3 both run with mandatory period-aware alert suppression, so historical rows they recover are alert-suppressed (alert_eligible_at = NULL) and are never emailed. The remaining limitation is timing, not absence: a newly-closed quarterly, semi-annual, or annual period may lag until the relevant scheduled sweep reaches it — for Tier 3, up to about one 54-day rotation. Coverage is not real-time and rows are not all instantly present; this lag is stated plainly rather than implied as continuous coverage. [Superseded 2026-07-14: the Tier 3 sweep and its 54-day rotation no longer run — Pennsylvania is collected from the EPA ICIS-NPDES feed. See the 2026-07-14 entry above.]

Method. Guarded, single-transaction SQL updates affecting only the alert_eligible_at column, each preceded by a read-only identity check and a retained row-exact snapshot; read-only verification queries; and a merged code change (PR #107) with green CI. No schema changes beyond the 073/074 column addition, no snapshot tables were dropped, no secrets were changed.

Status: RESOLVED for the historical alert-safety gap (suppression in place, both cleanups complete, daily cron verified, durable guard merged), and recurring long-period scheduling is now in place (Tier 2 and Tier 3 both scheduled). Remaining caveat: timing/lag until the relevant scheduled sweep or Tier 3 rotation (about 54 days) reaches a newly-closed long-period. [Superseded 2026-07-14: the Tier 3 sweep and its 54-day rotation no longer run — Pennsylvania is collected from the EPA ICIS-NPDES feed. See the 2026-07-14 entry above.]

2026-06-04 — Backup-table RLS remediation (rollback snapshot locked down; no production data change)

Scope. A security-posture remediation applied to a single backup table, public.exceedances_backup_20260603_pre_073_074. This table is the pre-migration snapshot of public.exceedances captured before migrations 073/074 (the Phase 1 alert_eligible_at work) and is retained as a rollback anchor. The remediation enabled row-level security on the backup table so it is no longer readable through the public API. No production data was changed: the live public.exceedances table, application code, alert/cron behavior, and table grants were all left untouched.

Background. The Supabase security advisor flagged this backup table with a high-severity rls_disabled_in_public finding: the table sits in the API-exposed publicschema with row-level security disabled, which left it readable by the anonymous API role. The table holds the same public permit-exceedance data already served on the site (no personal or subscriber data), so the confidentiality impact was limited — but the posture is corrected to match the other backup tables in the project.

Action (exact SQL executed):

alter table public.exceedances_backup_20260603_pre_073_074 enable row level security;

No row-level security policy was added. An RLS-enabled, zero-policy table is reachable only by the service/admin role, which is the intended locked-down state for a backup.

Verification.

• Row count unchanged: 42,344 before and after.
• Row-level security enabled: confirmed true.
• Policies added: zero.
• Anonymous read: an anonymous-role SELECT returned 0 rows (read blocked).
• Anonymous write: an anonymous-role DELETE affected 0 rows and an anonymous-role INSERT was rejected with a row-level-security policy error (write blocked/neutralized). Both tests ran inside transactions that were rolled back; no data was modified.
• Service/admin rollback access preserved: a service-role SELECT returned all 42,344 rows, so the snapshot remains fully usable for any future rollback of migrations 073/074.

Advisor outcome. The high-severity rls_disabled_in_public finding for this table is resolved. The advisor now reports only an INFO-level rls_enabled_no_policy notice for the table, which is the intended end-state for a locked-down backup (row-level security on, no policies, service/admin-only access) and matches the posture of the other retained backup tables in the project.

Verification method. Read-only SQL against pg_class, pg_policies, and information_schema; role-scoped read/write probes inside rolled-back transactions; and the Supabase security advisor. The only mutating statement was the single ALTER TABLE … ENABLE ROW LEVEL SECURITY shown above. No migrations were applied, no application or alert/cron behavior was changed, no grants were altered, and no file edits were made other than this audit-log entry pair.

Status: RESOLVED. The rollback snapshot is locked down and the high-severity advisor finding is cleared. No data-quality defect is recorded by this entry; it documents a security-posture remediation on a retained backup table.

2026-05-30 — Launch data-correctness verification: 4-permit source-to-DB spot-check plus live search-query checks (no mismatch found)

Scope. A targeted, read-only spot-check comparing Permit Minder's stored public.exceedancesrows against PA DEP eDMR source data for four permits, plus an end-to-end recomputation of the exceedance classifier and three live search-query checks against the public site. This was a four-permit spot-check and a small set of live search queries — not a full or exhaustive audit of the dataset. It does not establish that the dataset is complete, official, guaranteed, comprehensive, or presented in real time.

Method. Source rows were pulled from the PA DEP eDMR SSRS endpoint via pa_dep_fetch.fetch_dmr_window and classified with the production prepare_launch_ready_dmr routine; stored rows were read from public.exceedances (active = superseded_at IS NULL); search counts were read from the same search_exceedances_count RPC the live site uses, with the public site confirmed serving (www.permitminder.org/ and /search returned HTTP 200, not the maintenance page). No database writes, no code changes, no workflow changes, no configuration changes.

Permits checked (one recent monitoring period each):

• PA0026671 — Philadelphia Water Dept, Southwest WPC (urban POTW), period ending 2026-02-28: 9 stored rows vs 9 source-classified exceedances, exact match.
• PA0008869 — Pixelle Specialty Solutions, Spring Grove Mill (industrial, three monitoring locations), period ending 2025-11-30: 3 vs 3, exact match.
• PA0012637 — Trainer Refinery (industrial), period ending 2025-11-30: 2 vs 2, exact match.
• PA0028673 — Gallitzin Borough WWTP (small/rural POTW), period ending 2026-03-31: 1 vs 1, exact match; plus the annual-period Total Phosphorus row (period 2024-10-01 to 2025-09-30) present and source-exact.

The comparison was two-directional: every source-classified exceedance was found in the stored data with matching reported_value, permit_limit, units, and monitoring_location, and the stored data contained no extra rows for those periods. Rows reported in two units under one identity key (lbs/day and mg/L) and rows at multiple monitoring locations resolved correctly under the seven-tuple conflict key.

End-to-end calculation trace. Three classifier paths were traced from the source value to the stored exceedance_pct: a numeric ceiling (Total Suspended Solids, 191,390 vs 75,060 lbs/day → 154.98%), a numeric floor / minimum percent-removal (CBOD5 percent removal, 82.82 vs 89.25 → 7.20%, with the floor direction applied correctly), and an above-detection-limit qualifier (CBOD5 reported as > 42750against a 29,700 limit → 43.94%). Each recomputed value matched the stored value.

Search-query checks. County = Allegheny returned 101 facilities, matching 101 distinct stored permit numbers for that county; permit = PA0026671 returned 1; parameter = Total Suspended Solids returned 1,299 facilities — each internally consistent with the stored data.

Operational note (no stored-data gap observed). During source retrieval, one wide single-window fetch (a single request spanning 2024-01-01 to 2026-04-30 for one permit) returned rows only through period-end 2025-12-31, silently omitting the most recent periods, which do exist in the source. Re-fetching those periods with narrower windows, and re-fetching with the standard five-year per-permit window (2021-05-01 to 2026-04-30), each returned the full span including 2026 periods. This appears to be a transient or inconsistent response from the source endpoint on that one wide request rather than a systematic limit. It was not observed to have created any gap in the stored datain this verification — the recent and annual-period rows for the checked permits are all present and source-accurate. It is recorded here as a source-retrieval robustness item worth a future completeness re-probe, not a defect found in stored data.

Outcome. No mismatches were found across the four permits, the three calculation traces, or the three search-query checks. This clears the corresponding Phase 1 data-verification gating items in the internal task list (spot-check 3–5 permits against source; walk one exceedance calculation end-to-end; run live search queries against source). It remains a spot-check rather than a guarantee about the entire dataset.

Verification. Read-only pa_dep_fetch source fetches; read-only SQL against public.exceedances and the search_exceedances_count RPC; unauthenticated GET requests against https://www.permitminder.org. No DB writes, no env changes, and no file edits other than this audit-log entry pair and the task-list check-offs.

2026-05-22 — Second live scheduled alert cron fire CLEAR (Friday non-Monday; subscriber state preserved)

Context. Second live scheduled fire of the Vercel cron at 0 12 * * * UTC. Per the route's isMonday = new Date().getUTCDay() === 1 gate, Friday is non-Monday, so only daily-frequency subscribers are iterated. The single daily subscriber (id 23) had no new matching exceedance rows since the 2026-05-20 reset cutoff because no Sunday scrape has run yet (next scheduled scrape: 2026-05-24 07:00 UTC). Algebraic expectation: 0 sent, 1 skipped, 0 errors.

Disposition. public.cron_runs row id 28 landed at ran_at = 2026-05-22 12:00:29.02+00 (created_at = 2026-05-22 12:00:29.100042+00) with job_name = "alert_send", status = "success", and details = "0 sent, 1 skipped, 0 errors". The details string carries neither the [PAUSED] prefix (which would indicate ALERTS_ENABLED != "true") nor the [DRY] prefix (which would indicate the ?dry=1 preview path). Row id 28 is the only alert_send row for 2026-05-22; the prior row id 27 belongs to the 2026-05-21 fire recorded in the entry below. Result matches the algebraic expectation exactly.

Subscriber state preserved. last_alerted_at for ids 1, 2, 19, and 23 all remained at 2026-05-20 14:41:24.27715+00 after the fire — the same reset cutoff the 2026-05-21 fire preserved. The route's zero-match skip branch did not advance last_alerted_at for id 23.

Public route smoke. All seven verification paths returned HTTP 200: /, /search, /alerts, /methodology, /audit-log, /pricing, /permit/PA0243949. The homepage rendered the launch-ready homepage (“Pennsylvania Water Discharge Data” hero text present in the served bytes; the maintenance-page strings were both absent — i.e. no maintenance-page rewrite). /pricingrendered the corrected Free/Pro copy shipped in PR #71 (“1 watchlist with weekly email digest”, “Up to 5 watchlists”, “Daily email digest option”, and “Enhanced CSV with Exceedance % column” all present).

Verification. Read-only DB queries against public.cron_runs (single row for 2026-05-22 inspected) and public.alert_subscriptions (ids 1, 2, 19, 23 timestamps confirmed unchanged). Public-route smoke via unauth GETs against https://www.permitminder.org. No DB writes; no env changes; no file edits other than this audit-log entry pair.

Status: CLEAR. Alert cron continues to operate on schedule and produces the algebraically-expected results. The suppression mechanism set by the 2026-05-20 last_alerted_atreset continues to hold across two consecutive scheduled fires. Next scheduled alert fire: 2026-05-23 12:00 UTC (Saturday, non-Monday, identical expectation under current subscriber state). Next scheduled data-mutating event: 2026-05-24 07:00 UTC Tier 1 statewide weekly scrape. No data-quality defect is recorded by this entry — it documents the second live scheduled fire and the continued integrity of the subscriber reset cutoff.

2026-05-21 — Pre-launch readiness gates 1, 6, 7 closed (first live alert cron CLEAR; Gate 5 copy remediation deployed; snapshot/rollback posture documented)

Context. This entry records the verified outcomes of three constrained pre-launch readiness gates exercised against production on 2026-05-21. Each gate was scoped narrowly and read-only against the live system, with a single text-only PR (PR #69) the only mutating action.

Gate 1 — first live scheduled alert cron fire. The Vercel cron at 0 12 * * * UTC fired at 2026-05-21 12:00:38 UTC. The route returned HTTP 200. public.cron_runs row id 27 landed at 2026-05-21 12:00:39.766+00 with job_name = "alert_send", status = "success", and details = "0 sent, 1 skipped, 0 errors". The details string carries neither the [PAUSED] prefix (which would indicate ALERTS_ENABLED != "true") nor the [DRY] prefix (which would indicate the ?dry=1preview path). 2026-05-21 was a Thursday UTC, so per the route's isMonday = new Date().getUTCDay() === 1 gate, only daily-frequency subscribers were iterated. Subscriber id 23 (the sole daily subscriber, filter permit_numbers = ['PA0254967']) entered the per-subscriber loop, found zero exceedance rows with created_at > 2026-05-20 14:41:24.27715+00 AND superseded_at IS NULL AND exceedance_pct > 0 AND value_qualifier_kind IN ('measured','above_dl') (count verified by direct DB query: 0), and hit the zero-match skip branch. last_alerted_at for ids 1, 2, 19, and 23 remained at 2026-05-20 14:41:24.27715+00 after the fire, exactly matching the reset cutoff set on 2026-05-20 to suppress catch-up digest risk. Public routes (/, /search, /alerts, /methodology, /permit/PA0243949, /audit-log) all returned 200 after the fire. No error-level Vercel runtime logs were emitted by the route. Outcome: CLEAR.

Gate 5 — public copy alignment with live operational state. A pre-deploy read-only review found that three public surfaces still rendered “pre-launch / paused / waitlist” copy that contradicted the live alert system (active since 2026-05-20 16:55 UTC, see entry below) and the post-Patch-E tiered ingest (1,249 active rows with monitoring periods > 31 days, see 2026-05-19 entry below). PR #69 (squash-merged at 2e3cca8ff73518b4d2fee3bd5b43428fe0f154cf) corrected the drift with text-only edits to three files: frontend/src/app/page.tsx(hero copy, alert CTA, “Monitor” card), frontend/src/app/alerts/page.tsx(h1 from “Email Alert Waitlist” to “Email Alerts”; amber callout from “Alert delivery is currently paused” to “How alerts work”; requires-verify and submitted states reworded; expander summary updated), and frontend/src/app/methodology/page.tsx (“Source of every field” table row for monitoring_period_start; “Monitoring-period dates” section rewritten to describe the tiered ingest of monthly Tier 1 + quarterly Tier 2 + per-permit five-year Tier 3 windows, with explicit reference to source-published Monitoring_Period_Begin_Datefor non-monthly rows; “Data freshness and ingest lag” section updated to remove “alert delivery remains paused” and the “May 22 public preview” wording; “Last Updated” bumped to 2026-05-21). Diff stat: 3 files, +74 / −51. No DB, schema, RPC, API route, alert-logic, cron, or pipeline files were changed. CI on PR #69 was clean across all four checks (guards color/language guard SUCCESS, pytest SUCCESS, Vercel preview SUCCESS, Vercel Preview Comments SUCCESS). Squash-merged with mergeStateStatus = CLEAN and mergeable = MERGEABLE.

Gate 6 — production deploy and public smoke test. Production deployment dpl_B8F5QqPvni69VoygVGTo2or67Mjh (main HEAD 2e3cca8) reached state = READY at 2026-05-21 13:49:56 UTC. All eight smoke-test paths returned HTTP 200 with no redirects: /, /search, /alerts, /methodology, /audit-log, /permit/PA0243949, /permit/PA0253308, /manage. Stale-phrase scan against the served bytes for waitlist, paused, will resume, once alerts resume, alerts are coming soon, all been calendar-month, May 22 public preview, Email Alert Waitlist, and alert email delivery remains paused: zero hits across all eight pages. The /alerts page is a React client component; a follow-up scan of all 10 JavaScript chunks loaded by that page confirmed all six current-state strings (Email Alerts, How alerts work, newly added Permit Minder records, Alert preferences saved, activate your email alert subscription, Subscribe to email digests of newly added) are present in the deployed bundle, and zero of the six tracked stale strings remain. Methodology current-state copy verified in SSR text: Monthly statewide windows, Quarterly statewide windows, Per-permit five-year windows, Monitoring_Period_Begin_Date, Weekly PA DEP ingest runs on Sundays, Last Updated: 2026-05-21. Banned-term scan returned zero new occurrences; the only hits were in the pre-existing methodology “Exceedance vs. violation” disclosure section, all in negation contexts. Export endpoint (/api/permit/{PA0243949,PA0253308}/export with and without format=csv|pdf) returned the expected HTTP 403without a signed token — fail-closed behavior. Outcome: CLEAR.

Gate 7 — snapshot, rollback, and operational posture. Production data baseline at gate-entry time: public.exceedances total = 42,342 / active = 42,341 / superseded = 1. Latest active created_at is 2026-05-20 12:42:51.469657+00 (the April 2026 monthly catch-up cohort, recorded in its own entry below). Active rows by value_qualifier_kind: measured 39,588 / above_dl 1,491 / nondetect 1,262. Active rows by span bucket: 0–31 days 41,092 / 32–92 days 1,006 / 93–183 days 166 / 184–366 days 77 / over 366 days 0. Active rows by data_source: PA_DEP 42,341 (single-source). 13 snapshot/backup tables across the archive.* and public.* schemas were inventoried; every row count matches a documented operational anchor. Current live-state rollback anchor: public.exceedances_backup_pre_scrape_reenable_2026_05_20 at 42,342 rows (row-exact match to the live public.exceedances count). Pre-2026-05-20-manual-catch-up anchor: public.exceedances_backup_phase4_pre_2026_05_20 at 42,200 rows (the Patch E.4 close-out baseline). Pre-G5 statewide Tier 3 sweep anchor: archive.exceedances_pre_patch_e_tier3_g5_2026_05_11 at 40,393 rows, named in CLAUDE.md as the Patch E rollback anchor and remains a strict subset of the live table. Pre-2026-05-04-rescrape historical anchor: archive.exceedances_full_snapshot_20260504 at 109,862 rows (the original contaminated state retained as legal history). Scheduled-job posture: Vercel cron for /api/alerts/send is active at 0 12 * * * UTC (next fire 2026-05-22 12:00 UTC; alert path does not mutate public.exceedances). .github/workflows/scrape.yml is active at 0 7 * * 0 UTC (next fire 2026-05-24 07:00 UTC; this is the next event scheduled to mutate public.exceedances). .github/workflows/echo_scrape.yml schedule remains disabled (cron commented out since 2026-05-04). Rollback posture for the upcoming 2026-05-24 Sunday scrape is unambiguous: the exceedances_backup_pre_scrape_reenable_2026_05_20 anchor is named for that fire and any post-scrape delta is recoverable by created_at-window DELETE with anti-join verification against the anchor. Frontend rollback posture: Vercel retains nine prior production deployments marked isRollbackCandidate = true, including dpl_7ZmuF9JVCKdm562Yynrx2RFj5Ai6 (the immediately prior production build). Outcome: CLEAR.

Verification. Direct DB queries against public.cron_runs (Gate 1 row id 27 inspected), public.alert_subscriptions (ids 1, 2, 19, 23 timestamps confirmed unchanged), public.exceedances (baseline counts confirmed), information_schema.tables (13 snapshot tables enumerated), and individual snapshot tables (every row count confirmed against documented anchor). Production deployment metadata pulled from Vercel; deployment ID and githubCommitSha confirmed against main HEAD 2e3cca8. Public route 200 sweep via unauthenticated GETs; HTML scans run against fetched bytes and against the deployed JS chunk URLs extracted from the alerts page SSR shell. Read-only across all gates; zero DB mutations; zero file edits except the PR #69 commit (now merged) and this audit-log entry pair.

Status: Gates 1, 5, 6, and 7 each closed CLEAR. Production is on main HEAD 2e3cca8 via dpl_B8F5QqPvni69VoygVGTo2or67Mjh. Alert cron is operating on schedule and producing the algebraically-expected results. Public copy is internally consistent with the live alert system and the post-Patch-E tiered ingest. Snapshot/rollback coverage is row-exact for every operational state transition since 2026-05-04. Next scheduled data-mutating event is the 2026-05-24 07:00 UTC Tier 1 statewide weekly scrape; the live-state rollback anchor is named for that event. No data-quality defect is recorded by this entry — it documents the closure of a read-only pre-launch verification sequence plus the one text-only public-copy PR (PR #69) that arose from it.

2026-05-20 — April 2026 monthly catch-up ingest (Tier 1, manual; 142 rows; explains 42,200 → 42,342 baseline delta)

Context. During the post-Patch-E.4 maintenance window on 2026-05-20 (after the 2026-05-19 G5 + Patch E.4 close-out, and before the C7 alert hold was lifted later the same day at 16:55:46 UTC), a Tier 1 monthly catch-up scrape against the April 2026 PA DEP reporting period was executed by the operator out-of-cycle from the scheduled scrape.yml Sunday fire. This entry records the ingest as a discrete event so the post-Patch-E.4 baseline (42,200 / 42,199 / 1, recorded in the 2026-05-19 entry below) and the live baseline at the time the C7 hold was lifted (42,342 / 42,341 / 1, recorded in the 2026-05-20 alert-activation entry below) reconcile through a single named cohort rather than appearing as unexplained drift.

Disposition. 142 rows inserted into public.exceedances in a single RPC batch at created_at = 2026-05-20 12:42:41.261988+00. The rows occupy id range 432534–432675 inclusive. All 142 rows carry data_source = "PA_DEP". All 142 rows carry monitoring_period_start = 2026-04-01 and monitoring_period_end = 2026-04-30(single calendar-month cohort; 0–31 day span bucket; Tier 1 only). The cohort spans 75 distinct permit_number values. Distribution by value_qualifier_kind: measured 129, above_dl 4, nondetect 9 — consistent with a normal Tier 1 statewide month and the same classification pipeline verified at Gate 4. Distribution by stat_base_code: Average Monthly 63, Instantaneous Maximum 22, Daily Maximum 18, Weekly Average 16, Instantaneous Minimum 9, Geometric Mean 8, Daily Minimum 4, Annual Average 1, Minimum 1. Zero rows in the cohort carry corrected_on IS NOT NULL; zero rows in the cohort were superseded. The cohort was classified through the same prepare_launch_ready_dmr → apply_dmr_rows path verified at Gate 4 and Gate 4.5; all spot-checked rows recomputed the stored exceedance_pct exactly under the documented ceiling/floor formula. Patch E.4 F1 (drop limit_type = monitor_only at the ingest boundary) was in effect for this ingest; zero monitor_only rows entered the active set.

Subscriber catch-up risk mitigated. The ingest landed at 12:42:41 UTC, before the subscriber last_alerted_at reset at 14:41:24 UTC and well before the C7 hold was lifted at 16:55:46 UTC. The reset cutoff at 2026-05-20 14:41:24.27715+00 was set precisely to suppress this cohort (plus the broader post-Patch-E.4 backfill window of recovered long-period rows from G5) from going out as a single catch-up digest blast to the four eligible subscribers when alerts were re-enabled. The first live scheduled alert fire on 2026-05-21 12:00:39 UTC observed 0 sent, 1 skipped, 0 errors— confirming the suppression mechanism worked exactly as designed. See the 2026-05-21 entry above for the Gate 1 outcome.

Verification. Direct DB queries confirmed: id range 432534–432675 inclusive (142 rows); single created_at timestamp (2026-05-20 12:42:41.261988+00); single monitoring-period cohort (April 2026); single data_source (PA_DEP); zero corrections, zero superseded, zero monitor_only rows. Anchor reconciliation: prod_total − pre_catch_up_total = 42,342 − 42,200 = 142 = new_rows_count; prod_active − pre_catch_up_active = 42,341 − 42,199 = 142. Pre-catch-up snapshot anchor public.exceedances_backup_phase4_pre_2026_05_20 is preserved at exactly 42,200 rows and remains a strict subset of public.exceedances. Post-catch-up snapshot anchor public.exceedances_backup_pre_scrape_reenable_2026_05_20 is preserved at exactly 42,342 rows, a row-exact match to the current live table.

Rollback. If a defect is later discovered in this cohort, restore via DELETE FROM public.exceedances WHERE id BETWEEN 432534 AND 432675 with anti-join verification against public.exceedances_backup_phase4_pre_2026_05_20 (target row count: 42,200). The cohort is uniquely identifiable by id range, by created_at timestamp, and by the monitoring_period_start = 2026-04-01 AND monitoring_period_end = 2026-04-30 filter — any of the three is sufficient.

Status: Closed. Cohort is in the active set; baseline reconciliation is row-exact in both directions; suppression mechanism for catch-up alerts validated by Gate 1. No data-quality defect is recorded by this entry — it documents an out-of-cycle but in-pattern Tier 1 ingest and the row-level anchor reconciliation that ties the pre- and post-state to named snapshot tables.

2026-05-20 — Alert subscription delivery enabled (cron + ALERTS_ENABLED gate flipped)

Context. The pre-launch alert operational hold (“C7 hold”) had kept email digest dispatch disabled since 2026-05-08 while seven dependency blockers were closed: digest catch-up / last_alerted_at reset (a), dry-run preview mode (b), ALERTS_ENABLED env gate (c), CRON_SECRET rotation and dry-run verification (d), ECHO short-code classification audit confirming zero leak candidates in the active set (e), Resend deliverability with SPF/DKIM/DMARC all passing and inbox delivery verified (f), and the cron-entry plus env-flip activation step (g). All seven are now closed.

Disposition. Three coordinated changes against main enabled the first live alert cycle:

1. Alert digest cron schedule added via PR #67 (squash-merged at fbd818366b8b6013f26d5f44752f3eacdac06d74). frontend/vercel.json changed from {"crons":[]} to a single entry {"path":"/api/alerts/send","schedule":"0 12 * * *"} — daily at 12:00 UTC. No other files changed in that PR.
2. CRON_SECRET rotated in Vercel Production from a "" placeholder to a working 64-character hex value via a shell-memory flow (openssl rand -hex 32 → vercel env add CRON_SECRET production --sensitive from a held shell variable → used immediately for the authorized dry-run curl → unset from the shell at the end). The secret was never written to disk, never pasted into chat, and never re-read after use.
3. ALERTS_ENABLED=trueadded to Vercel Production env via the Vercel dashboard (the CLI's env add over piped stdin produced ""placeholders for non-sensitive values on CLI 54.2.0 — a Vercel CLI bug discovered during this work). Production was then redeployed via vercel redeploy --target production --non-interactive so the Lambda functions cold-started with the new env. The final production deployment is dpl_5CdsaeY8jY6WKqQFkNtB5R7bfgU6, state READY, githubCommitSha = fbd818366b8b6013f26d5f44752f3eacdac06d74.

The route's three independent gates are now all aligned in the live position: vercel.json cron entry present, CRON_SECRET Bearer auth working, ALERTS_ENABLED = "true" in production env. The ?dry=1preview mode and the env gate's fail-closed semantics (shipped in PR #66) remain in effect as in-code defenses against accidental send.

Verification. End-to-end dry-run executed at 2026-05-20 16:21:53 UTC via GET https://www.permitminder.org/api/alerts/send?dry=1 with the rotated Bearer token. Response: HTTP 200, body {"dryRun":true,"eligibleCount":1,"wouldSend":0,"skipped":1,"errors":0,"summaries":[]}. A cron_runs row landed at 2026-05-20 16:21:53.072+00 with status = success and details = "[DRY] 0 would-send, 1 skipped, 0 errors", exercising the full eligibility / matching / digest-building path without invoking sendEmail and without advancing last_alerted_at for any subscriber. No subscriber row was mutated. No email was sent during any part of the setup (rotation, env flip, redeploys, or dry-run). Public routes (/, /search, /alerts, /methodology, /permit/PA0243949) all returned 200 after the final redeploy.

Subscriber state at activation: four eligible rows (active = true ∧ verified = true ∧ paused_at IS NULL ∧ unsubscribed_at IS NULL), all reset earlier in the session to last_alerted_at = 2026-05-20 14:41:24.27715+00 to suppress a catch-up digest blast that would otherwise have shipped roughly 1,591-row digests to two Lancaster-county subscribers covering the post-Patch-E.4 backfill window. One unverified, inactive subscriber row exists and is correctly excluded from the eligible set. The reset cutoff guarantees the first live cycle includes only rows ingested after activation.

First scheduled live alert cycle: 2026-05-21 12:00:00 UTC. Today is Thursday UTC; per route logic (isMonday = new Date().getUTCDay() === 1), only daily-frequency subscribers are iterated on a non-Monday fire. The single daily subscriber (filter is permit PA0254967) currently has zero matching exceedances since the reset cutoff, so the first fire is expected to produce a cron_runs row with details = "0 sent, 1 skipped, 0 errors" and zero emails delivered through Resend. Weekly subscribers (Lancaster county filters and PA0218197 permit filter) are first eligible at 2026-05-25 12:00:00 UTC, the next Monday fire.

Rollback. Fastest path: edit ALERTS_ENABLED in Vercel Production from true to false (or remove the entry entirely) and redeploy. Existing warm Lambda functions may continue to serve the prior env briefly; the redeploy guarantees fresh Lambdas pick up the new value. Slower path: a PR reverting frontend/vercel.json to {"crons":[]}— stops Vercel Cron from invoking the route at all. Nuclear path: rotate CRON_SECRET to a new value, which 401s the next cron fire. Subscriber state, exceedance data, and the last_alerted_at reset are independent of any rollback step and require no separate action.

Status: C7 alert operational hold lifted as of the redeploy READY at 2026-05-20 16:55:46 UTC. All seven pre-launch alert blockers closed. Production public.exceedances baseline unchanged from the pre-activation state (total = 42,342 / active = 42,341 / superseded = 1). Scrape workflows unchanged: scrape.yml remains active (next scheduled fire 2026-05-24 07:00 UTC); echo_scrape.ymlremains manually disabled. No data-quality defect is recorded by this entry — the entry documents an operational state transition.

2026-05-19 — Patch E long-period sweep (G5) completion and Patch E.4 monitor_only collision remediation

Discovered during: Patch E proper was discovered on 2026-05-10 during manual verification of permit PA0243949 (LANDIS BLOCK & CONCRETE COMPANY — TELFORD PLT.) against PA DEP eDMR. The 10/01/2024–12/31/2024 Annual Average Total Suspended Solids row (DMR value 99.0 mg/L against a permit limit of 50.0 mg/L) was visible at source but absent from public.exceedances. A diagnostic sweep of the active dataset confirmed all 38,967 active rows carried monitoring periods ≤ 31 days; zero rows in the active set carried a monitoring-period span > 31 days (this is the 2026-05-10 discovery-state figure; after the Patch E recovery sweep the active set now contains 1,249 long-period rows — see the 2026-06-13 entry above for the current verified state). Patch E.4 was discovered on 2026-05-19 during post-G5 review, while spot-checking PA0244708 (KINDER MORGAN FAIRLESS HILLS FACILITY): a single row id=432256 was in the superseded state with limit_type = monitor_only and NULL permit_limit, sharing the canonical 7-tuple identity with what should have been an Annual Average Total Dissolved Solids exceedance for the 2021 calendar year (21,186.5 lbs/day against a 5,000 lbs/day permit limit).

Issue: The PA DEP eDMR SSRS endpoint applies full-period containment to the P_BEGIN_DATE / P_END_DATE parameters: a row is returned only when both Monitoring_Period_Begin_Date >= P_BEGIN_DATE and Monitoring_Period_End_Date <= P_END_DATE. Permit Minder's existing backfill orchestrator iterated strictly month-by-month, which mechanically dropped every quarterly, semi-annual, and annual-period row from the entire five-year active span. A 7-window decision-matrix probe against PA0243949 was consistent only with full containment; intersection, period-start, and period-end behaviors were each ruled out by at least one window's evidence. Separately, the apply_dmr_rowsRPC's 7-tuple uniqueness key — (permit_number, parameter, monitoring_period, outfall, stat_base_code, units, monitoring_location) — does not include limit_type or permit_limit. PA DEP eDMR returns both “monitor and report” rows (no permit limit; a reported measurement only) and limited rows (numeric permit limit) under the same canonical key. The original ingest path persisted both kinds. When the long-period orchestrator later fetched and re-ingested the canonical full-calendar-year row for PA0244708 / TDS / Annual Average / 2021, the RPC's per-row state machine fired multiple actions in one call against the existing monitor_only-shaped row, leaving id 432256 in a superseded monitor_only state and the canonical numeric exceedance absent from the active set.

Affected scope: All PA DEP eDMR data with monitoring periods longer than 31 days — quarterly (32–92 days), semi-annual (93–184 days), and annual (185–366 days) reporting periods — across the full five-year active span. Permit detail page row counts on affected permits omitted long-period rows; search-results aggregates inherited the omission. EPA ECHO data was not affected (separate source; separate ingest path). The Patch E.4 monitor_only collision was confined to one row in public.exceedances (id 432256 for PA0244708 / TDS / Annual Average / 2021-12-31 / outfall 002 / Final Effluent / lbs/day). A post-remediation full-table sweep confirmed zero monitor_only rows remain in the active set and zero monitor_only-vs-canonical 7-tuple split-pairs exist elsewhere in the table.

Disposition (long-period sweep): Three windowed source-query passes were run against PA DEP eDMR to recover the missing long-period rows: (1) calendar-month statewide queries (existing Tier 1, backfill_orchestrator.py), (2) calendar-quarter statewide queries (Tier 2, backfill_orchestrator.py --cadence quarterly), and (3) per-permit five-year queries against the deduped union of the EPA ECHO ICIS-NPDES Pennsylvania permit registry, distinct active permit numbers in public.exceedances, and any operator-supplied permit-list files (Tier 3, long_period_orchestrator.py). The Tier 3 pass is per-permit because the SSRS endpoint does not serve statewide windows longer than approximately three months (confirmed via probe; connections drop beyond ~92-day statewide spans). All three passes write through the same idempotent apply_dmr_rows RPC; previously-ingested rows are no-ops under the 7-tuple key.

Scheduling (as of this 2026-05-19 entry). Of these passes, only the calendar-month statewide pass (Tier 1) runs on a recurring schedule. The quarterly (Tier 2) and per-permit five-year (Tier 3) passes were run as one-time recovery sweeps, not on a schedule. Recurring scheduling of the long-period passes remains an open item, so newly-closed quarterly/semi-annual/annual periods are not yet refreshed automatically — see the 2026-06-13 entry above. [Superseded twice. The long-period passes WERE put on a recurring schedule in June 2026, so the “open item” described here was closed within a month of this entry. That schedule has since been retired in turn: as of 2026-07-12 Pennsylvania is collected from the EPA ICIS-NPDES bulk feed, which publishes every monitoring period whatever its length, so no separate long-period sweep is needed. See the 2026-07-14 entry above. This paragraph is left as written because it was accurate on 2026-05-19.]

The full statewide Tier 3 sweep (G5) was approved on 2026-05-11 after Patch E.3 leading-zero canonicalization verification (PR #58) and ran against the full 37,431-permit universe (37,336 from data/reference/permit_facility_type_lookup.csv ∪ 2,419 distinct active public.exceedances.permit_number, deduped). G5 started at 2026-05-11T19:11:26Z and completed all 37,431 permits at 2026-05-19T17:49:48Z. The orchestrator process was restarted once during the run (original PID 71494 ended; resumed under PID 98230 at approximately 2026-05-14T11:40Z) with idempotent resume from the progress JSON; no state was lost across the restart. Final orchestrator counters: inserted = 1,807, noop = 36,245, skipped = 4,066,080, updated = 3, superseded = 3, unsuperseded = 2, unknown_actions = [], last_error = None, batches = 12,928, rows_submitted = 4,104,140. The sanity equation inserted + updated + noop + skipped + superseded + unsuperseded = rows_submitted holds exactly (4,104,140 = 4,104,140). The orchestrator exited cleanly; the bound caffeinate process self-released its power assertions via the -w 98230 binding.

Disposition (Patch E.4 monitor_only collision fix and targeted remediation): The ingest pipeline was patched to drop rows with limit_type = 'monitor_only' before they are passed to apply_dmr_rows. The rationale: “monitor and report” rows carry no permit limit and cannot represent an exceedance under any classification rule; allowing them into the RPC under a key that does not include limit_typecreates a same-key collision with the canonical numeric row whenever both exist for the same monitoring period. Dropping them at the ingest boundary preserves the canonical numeric row's identity end-to-end.

The single affected production row was remediated under a precise rollback-artifact protocol. A pre-action full-row snapshot of id 432256 was captured to prod_run_2026_05_11_patch_e_tier3_g5/remediation/row_432256_snapshot.json with capture metadata and an explicit re-insert path keyed on the original id value; a pg_constraint sweep confirmed no foreign-key references to id 432256. The superseded monitor_only row was then removed with a single DELETE keyed on id = 432256, and the canonical Annual Average row was re-ingested through the patched pipeline. The re-ingest returned exit 0 with inserted = 1, rows_submitted = 1, batches = 1, all other counters 0, and unknown_actions = []. The new row was assigned id 432533, captured to prod_run_2026_05_11_patch_e_tier3_g5/remediation/restored_row_snapshot.json, and reflects the corrected full-calendar-year monitoring period (2021-01-01..2021-12-31 vs the original 2021-12-01..2021-12-31), the corrected limit_type = numeric_ceiling (vs the original monitor_only), the corrected permit_limit = 5000.0lbs/day (vs the original NULL “Monitor and Report”), and the resulting exceedance_pct = 323.73% against the same reported_value = 21186.5 lbs/day. A fresh PA DEP source spot-check confirmed exact match on every substantive field for the restored row.

Verification: Post-G5 invariants on public.exceedances (read-only at completion, before Patch E.4 remediation): production total moved 40,393 → 42,200 against the pre-G5 snapshot anchor archive.exceedances_pre_patch_e_tier3_g5_2026_05_11 (40,393 rows); anti-join new_rows_count = 1,807 matched orchestrator inserted exactly. Active 7-tuple duplicate groups across the full table (using the actual ingest key) = 0. I-Tier3-2 active leading-zero / stripped split-pair count across the full table (ltrim(permit_number, '0') sweep) = 0. NULL value_qualifier_kind, NULL monitoring_period_start, and NULL monitoring_period_endon new rows each = 0. Outside-universe new rows = 0. The Patch E hypothesis was confirmed on production: recovered rows surfaced in both the semiannual (93–184 day) and annual (185–366 day) period buckets — the categories Patch E was designed to recover. Post-Patch-E.4 invariants on public.exceedances (read-only at completion of remediation): id 432256 present count = 0 (DELETE confirmed); id 432533 present and active count = 1 (re-ingest confirmed); active canonical-7-tuple count for the targeted PA0244708 slice = 1; active 7-tuple duplicate groups against id 432533 = 0 and across the full table = 0; monitor_only rows in the active set, full table = 0; NULL permit_limit rows in the active set, full table = 0; both shape-consistency checks (monitor_only with non-NULL limit, non-monitor_only with NULL limit) = 0; outside-universe format check on id 432533 clean; full-table snapshot-subset check returned 40,393 / 40,393 anchor ids present in production (snapshot anchor remains a strict subset and a valid rollback anchor). Anchor reconciliation: prod_total − snap_total = 42,200 − 40,393 = 1,807 = new_rows_count (anti-join); prod_active − snap_active = 42,199 − 40,392 = 1,807. No rollback was required. Both rollback artifacts (row_432256_snapshot.json and restored_row_snapshot.json) are preserved under prod_run_2026_05_11_patch_e_tier3_g5/remediation/.

Status: G5 complete. Patch E (long-period sweep) and Patch E.4 (monitor_only collision) closed against production. Production baseline at audit-entry time: public.exceedances total = 42,200 / active = 42,199 / superseded = 1. Cron remains disabled (frontend/vercel.json crons: []). Maintenance-mode posture is unchanged from the pre-G5 state. C7 alert operational hold remains in force; alert dispatch has not been re-enabled. Re-enabling cron, lifting the alert hold, and clearing maintenance-mode posture are each gated on separate explicit approval.

[Superseded 2026-05-20: the alert cron was re-enabled and the C7 hold lifted; the cron has run daily since. This paragraph describes the 2026-05-19 point-in-time state only — see the 2026-06-13 and 2026-05-20 entries above for current alert posture.]

2026-05-11 — Tier 3 leading-zero permit_number canonicalization (Patch E.3)

Discovered during: Pre-launch production Tier 3 pilot (G4) on 2026-05-11. The Patch E long-period orchestrator (long_period_orchestrator.py) issued a per-permit fetch against the PA DEP eDMR SSRS endpoint with the canonical zero-padded permit number 0104402. The response carried PERMIT_NUMBER=104402— PA DEP's SSRS endpoint silently strips leading zeros from numeric PERMIT_NUMBER values in permit-filtered responses. The orchestrator persisted the response value as written. The result on production was 160 new rows on public.exceedances; 136 of those rows (across 21 distinct facilities) were written under the stripped-zero form, fragmenting facility identity across two permit_number values for the same facility. A parallel staging pilot (G2) on the same date had introduced the same defect on staging across 18 distinct facilities.

Issue: The 7-tuple uniqueness key used by the apply_dmr_rows RPC includes permit_number as a string, so 0104402 and 104402 are treated as distinct facility identifiers. Standard invariant checks (NULL value_qualifier_kind, NULL monitoring-period bounds, active 7-tuple duplicates, TEST_071_% rows, unexpected supersedes) all passed because none of them detect leading-zero string drift. A source spot-check exposed the defect: querying PA DEP eDMR with the stored stripped value returned zero rows, meaning the displayed permit_number on the platform would not round-trip to the source data the platform cites. Email alert subscriptions are keyed on permit_number, so subscribers to a zero-padded facility identifier would not receive alerts for new exceedances under the stripped form for the same facility.

Affected scope: Tier 3 per-permit fetches only. The legacy direct scraper, Tier 1 (statewide monthly), and Tier 2 (statewide quarterly) paths issue PA DEP queries without P_PERMIT and do not trigger leading-zero stripping at the response layer; those paths were not affected. PA-prefix permits (e.g. PA0243949, PAG058397) are not affected because PA DEP does not strip the PA prefix. The defect was confined to digit-prefix permits with leading zeros (e.g. 0104402, 0102406, 0214403).

Disposition (recovery): Production rolled back via R2 on 2026-05-11 — a precise DELETE of the 160 G4 pilot rows by created_at window, with a belt-and-suspenders anti-join against the archive.exceedances_pre_patch_e_tier3_2026_05_11 snapshot. Production was restored to 40,364 total rows / 40,363 active rows. Staging rolled back via R3 — a DELETE of 195 rows across two created_at windows covering the G2 lex-first-100 pilot (165 rows) and the G2-bis 4-permit semi-annual/annual probe (30 rows). Staging was restored to 39,166 total / 39,165 active.

Disposition (fix): R4 committed at 6baeddee on branch fix/tier3-permit-number-canonicalization. The fix in long_period_orchestrator._process_permit reasserts the queried universe permit_numberon the response DataFrame before classification and ingest, so canonical zero-padded identifiers are preserved end-to-end. The fix is narrowly scoped — pa_dep_fetch.py, launch_ready_columns.py, ingest.py, and backfill_orchestrator.py are unchanged. Tier 1 and Tier 2 statewide paths do not invoke _process_permit and remain unaffected. Three tests were added (leading-zero canonicalization, PA-prefix preservation, empty-response no-write); the full repository test suite passes (192 passed, 4 skipped).

Verification (R5 staging): R5 staging leading-zero re-probe on 2026-05-11 against four leading-zero permits (0104402, 0214403, 0998419, 0999405) ran the fixed code path. The orchestrator inserted 16 new rows; every inserted row carried the canonical zero-padded permit_number value. The noop count of 41 exactly matched the pre-existing canonical row sum (5 + 10 + 14 + 12 = 41) for these four permits, demonstrating idempotence under the canonical key. No stripped variants appeared in the table. A full-table sweep for any zero-padded/stripped split pair returned zero rows. A source spot-check against PA DEP eDMR for two of the new inserts (0104402 Fecal Coliform Daily Maximum 2022 Q1; 0214403 Total Nitrogen Daily Maximum 2025 Q4) confirmed all measurement values (sample value, permit limit, units, monitoring location) match source exactly, while the stored permit_number carries the canonical zero-padded form. R5 inserts were then rolled back via R5-rollback so staging matches the strict R3 baseline at 39,166 total / 39,165 active.

Verification (renewed G4 production pilot): On 2026-05-11, after R4 was squash-merged to main as 4eb82d5b03e9614ebdd9342ff9d02253ace25e2e (PR #58), a fresh 40,364-row snapshot anchor archive.exceedances_pre_patch_e_tier3_2026_05_11_post_r5 was taken and the renewed G4 production Tier 3 pilot was run against a 100-permit universe. The orchestrator completed all 100 permits over approximately 31 minutes with exit status 0. Aggregate result: inserted = 29, noop = 902, skipped = 52,393, updated = 0, superseded = 0, unsuperseded = 0, unknown_actions= []. A3 post-pilot verification against the snapshot anchor: production total moved 40,364 → 40,393 (+29), active moved 40,363 → 40,392 (+29), superseded held at 1. All 29 inserted rows were active and carried data_source = PA_DEP. The mandatory Tier 3 invariants both held — I-Tier3-1 outside-universe count = 0, and I-Tier3-2 active leading-zero / stripped split-pair count across the full table (using ltrim(permit_number, '0')) = 0. Active 7-tuple duplicate groups = 0. NULL value_qualifier_kind, NULL monitoring_period_start, and NULL monitoring_period_endon new rows each = 0. The Option X §6 cross-stage materiality comparison passed: staging G2 and production G4 each produced 0 rows in the semiannual (93–184 day) and annual (185–366 day) period buckets within the Tier 3 target slice; no permit-set divergence and no aggregate divergence. A fresh PA DEP source-only spot-check on inserted row 430725 (permit 4600421, parameter Fecal Coliform, monitoring period 2021-02-01..2021-02-28, outfall 001, monitoring location Final Effluent, stat_base_code Geometric Mean, reported_value 2800.0 CFU/100 ml against permit_limit 200.0, value_qualifier_kind = measured, data_source = PA_DEP) returned an exact source match on every substantive field. The outfall difference — PA DEP source returned numeric 1; the database stores canonical 3-digit 001 via the prepare_launch_ready_dmrnormalization step — is a deterministic ingest transformation rather than a value drift, and is the only representational difference between the source and the stored row. The prior defective G4 attempt that had inserted 160 rows on production (136 of them stripped-zero across 21 distinct facilities) was rolled back via R2 before this session; the renewed G4 reintroduced zero stripped-zero rows. The R4 leading-zero canonicalization fix is verified end-to-end on both staging and production.

New invariants added to the Tier 3 verification SQL (mandatory before any renewed Tier 3 pilot):

• For permit-filtered Tier 3 runs, every newly inserted row's permit_number must equal a member of the queried permit universe.
• No active rows may exist where one permit_number is the zero-padded form and another permit_number is the corresponding leading-zero-stripped form for the same facility.

Status: R4 fix verified end-to-end on staging (R5 + G2 retry) and on production (renewed G3 → G4 → A3 + fresh PA DEP source spot-check on row 430725). PR #58 squash-merged to main at 4eb82d5b03e9614ebdd9342ff9d02253ace25e2e. Production baseline after the renewed G4 pilot: public.exceedances total = 40,393 / active = 40,392 / superseded = 1. Snapshot anchor archive.exceedances_pre_patch_e_tier3_2026_05_11_post_r5 (40,364 rows) is now a strict subset of public.exceedances and remains a valid rollback anchor. Cron remains disabled. Maintenance-mode posture is unchanged. Alerts remain in their pre-gate posture. The full statewide Tier 3 sweep (G5) is not approved and remains gated by separate explicit approval.

2026-05-08 — Headline exceedance percentages now exclude non-detect readings

Discovered during: Pre-launch defensibility review of the search-results card and the homepage statistics. Permit PA0253308 was the canonical example: laboratories report polychlorinated biphenyls (PCBs) using non-detect notation such as <250 ng/L when the substance is below the analytical detection limit. Against a permit limit of 0.064 ng/L, the database arithmetic produced an apparent exceedance of 195,212.5%. That number was a property of detection-limit math (the lab can only state the substance was somewhere below 250 ng/L), not a property of the facility's discharge.

Issue: The aggregate fields that drive headline numbers — max_exceedance_pct on the search-results card, and over_100_pct on the homepage — were computed across every row regardless of how the underlying value was reported. Rows where the lab reported “less than detection limit” (non-detect) were included in the same MAX and COUNT aggregates as rows where the lab reported a measured concentration. Per-row exceedance values on the permit detail page were not affected — the issue was confined to the aggregates shown on the search and homepage surfaces.

Affected scope: Search-results card max_exceedance_pct column. Homepage over_100_pct count. Search results filtering by min_exceedance_pct (count semantics). The permit detail page, CSV/PDF exports, and per-row data were not affected — those surfaces show the value the laboratory actually reported, with the qualifier (e.g., <, >) preserved alongside the number.

Alert delivery was not affected by migration 070 because the alert path reads directly from the public.exceedances table and does not depend on the four RPCs that this migration changed (search_exceedances, search_exceedances_count, get_permit_detail_enhanced, get_site_stats). The alert digest is already non-detect-aware via two layers of protection: (a) the send route's SQL-level filter .in("value_qualifier_kind", ["measured", "above_dl"]) excludes non-detect rows from the digest entirely, shipped in PR #49; and (b) a code-level defense-in-depth filter inside groupByFacility() in lib/email.ts ensures the per-facility headline max_exceedance_pct cannot be driven by a non-detect row even if a future caller passes raw rows into buildAlertDigestEmail(). The SQL-layer filter is a stronger guarantee than migration 070's RPC-level aggregate FILTER, because non-detect rows are removed from the digest entirely rather than merely excluded from a MAX.

Disposition: Migration 069 added a row-level classification column, value_qualifier_kind, that records whether each row's reported value is measured, nondetect (a <X report), above_dl (a >X report at the detection-limit floor), or other. Migration 070, applied to production on 2026-05-08, updated the four read-side database functions (search_exceedances, search_exceedances_count, get_permit_detail_enhanced, get_site_stats) so the headline aggregates exclude non-detect rows. Specifically, max_exceedance_pct and over_100_pct are computed only across rows where value_qualifier_kind is measured or above_dl. Above-detection-limit rows are retained in the aggregates because a >X report carries a known floor — the actual concentration is at least the displayed value, so including it is the conservative choice.

These migrations are read-side only and did not rewrite, delete, or supersede any underlying exceedance records. The same row-level data the platform always stored remains intact; only the calculation that produces the headline display number changed. Underlying row counts, exports, and the per-row exceedance figures on the permit detail page are unchanged. Alert delivery is unchanged because the alert path does not depend on the four RPCs migration 070 modified; the alert digest's existing two-layer non-detect protection (SQL-level filter from PR #49 plus the code-level defense-in-depth filter in groupByFacility()) means the email surface already matches the search-card and homepage non-detect- aware behavior.

Verification. After migration 070 was applied, the canonical pathological case PA0253308's headline max_exceedance_pct changed from 195,212.5 to 597.33 — the highest measured exceedance for that permit, with non-detect rows no longer driving the figure. The site-wide over_100_pct count changed from 14,740 to 14,255, the difference being 485 rows whose exceedance_pct > 100 was driven by non-detect arithmetic and which are now excluded from the over-100% count.

Why this matters for legal defensibility. A non-detect reading establishes only that the substance was below the laboratory's detection limit. Treating that detection-limit ceiling as if it were a measured value produces percentages that cannot be defended as the facility's actual discharge. Excluding non-detect rows from headline aggregates is the conservative reading: the platform now reports the highest measured exceedance, plus where applicable the highest known floor (above-detection-limit reports), and surfaces non-detect rows separately as row-level data on the permit detail page so a reader can examine them with the qualifier intact.

Status: Shipped. Migration 070 is live in production as of 2026-05-08. The frontend reads the corrected aggregates automatically. A future enhancement may surface per-permit counts of measured / non-detect / above-detection-limit rows on the search-results card so readers can see, at a glance, how many rows of each kind contributed to a permit's record; the underlying data is already available from the RPC and that work is tracked separately.

2026-05-07 — Non-detect screening separation + cross-surface streak removal

During pre-launch defensibility review we identified three independent issues affecting how Permit Minder presents laboratory non-detect samples and a removed feature. Fixes are implemented on the feature branch and pending production deployment.

Non-detect samples ranked alongside measured exceedances. When a laboratory cannot detect a substance at the stated detection limit, the operator reports a value like < 250 ng/L. For percent-over-limit calculations, Permit Minder applies the conventional half-detection-limit substitution. For some parameters and permits — most prominently PCBs at permit PA0253308, with a permit limit of 0.064 ng/L and a reported < 250 ng/L — that substitution mathematically exceeds the permit limit and produced an exceedance percentage of 195,212.5%. The actual concentration is unknown and may well have been at or below the permit limit. Approximately 1,160 rows in the active dataset carry this pattern.

Disposition. A new internal classification field separates measured exceedances from non-detect screening rows. The permit detail page now renders non-detect rows in a distinct "Non-detect Screening" section. The PDF export does the same. The CSV export adds a "Value Type" column. Email digests exclude non-detect screening rows entirely. The search results page no longer ranks them alongside measured exceedances. The methodology page documents the substitution and the separation. Database migrations to support this are authored and pending production application; the changes will land together with the methodology update.

Cross-surface streak inconsistency. An earlier design decision removed rolled-up streak summaries from the permit detail page, but several downstream surfaces had not been updated to match. The PDF export had a streak column, the methodology page described streaks as a feature, the search results page displayed streak filters and badges, and the alerts subscription form collected a streak threshold that was no longer used. All of this has been removed across eight files; the permit detail page's historical-design comments are preserved verbatim to document why the feature is intentionally absent. Permit Minder does not display, compute, or describe consecutive-exceedance streaks on any shipped surface.

Methodology coverage figure corrected. The methodology page previously stated approximately 23% of permits had no receiving-stream value. The current figure is approximately 6%, after recent backfills. The page has been replaced with a new draft that includes a field-by-field "Source of every field" table, a non-detect values section, and the corrected coverage figure.

Status. Implementation work landed on the feature branch with all local tests green. Production data migrations are authored and pending application per a separate operator runbook. Status remains under maintenance mode until production verification completes.

2026-05-04 — Data quality cleanup; corrected coverage count

During pre-launch verification we identified approximately 47,700 rows in our exceedances table that should not have been there: about 24,800 were measurements that complied with permit limits (for example, a pH reading well within the allowed range) that had been incorrectly retained as if they were exceedances; about 21,900 were rows where the permit limit or measured value was missing entirely, with nothing to compare; and about 1,000 were rows from a previously-known column-shift ingestion bug that had recurred via a CSV propagation issue.

Coverage count correction. Because some of the bad rows had garbage permit identifiers, our total permits count was inflated by 987. The corrected count is 2,322 permits, down from a previously-displayed 3,309. This is a correction of an over-count, not a reduction in what we cover — we have always tracked the same set of real PA NPDES permits.

What this means for prior exports. If you previously exported data or generated reports for specific permits, the underlying exceedance counts are unchanged; only the permit-detail row counts (which included compliant measurements alongside exceedances) and the site-wide total permits figure were affected.

Verification. We compared our data against EPA ECHO source records for three sample permits (2,988 floor-code source rows) and confirmed the contamination was compliant measurements being incorrectly retained, not real exceedances being missed. Exceedance counts, alert emails, and search results were already filtered to actual exceedances and were not affected.

Root cause. A maintenance script preserved cleaned-up rows in an intermediate CSV file, which subsequent scheduled loads then re-inserted. Both the script and the loader have been patched to filter correctly.

Resolution (verified). The cleanup was executed and the patched ingest path has run on every scheduled scrape since. Read-only verification against production confirmed the contamination is gone: zero EPA ECHO short-code rows, zero floor-code rows, and zero compliant rows stored as exceedances remain in the active set. The legacy direct-to-database write paths were removed from both scrapers, so the only write path to the exceedances table is now the single filtered ingest routine. The original contaminated state is retained as a snapshot for legal history. This item is resolved.

2026-05-01 — Permit detail “Periods Within Permit Limits” card removed (label/denominator mismatch)

Discovered during: Pre-launch surface audit, while reviewing the permit detail page rendering for permit PA0001406 (which displayed “0 of 24 records — 0.0% Periods Within Permit Limits” alongside “Total Exceedances: 24”).

Issue: A summary stat card on every permit detail page reported a percentage labeled “Periods Within Permit Limits”. The label suggests a compliance rate across the permittee's full monitoring history. The card's actual computation was (rows we ingested for this permit with usable data) − (rows with exceedance_pct > 0), divided by the same denominator — i.e., the share of the rows our pipeline displayed for this permit that were not flagged as exceedances. The two are not the same. Three structural gaps between label and number: the denominator depended on what we ingested, not on the permit's full monitoring history; rows with both permit_limit and reported_value null (the 4,687-row placeholder issue separately tracked) were silently filtered out before the calculation; and for 439 permits (14.3% of the 3,079 in production) the card always showed 0.0% because every compliant signal we have for those permits is in the null/null placeholder set. A reader of an affected permit page would reasonably infer “this facility never complied” when the actual statement was “every compliant reading we have for this permit was filtered out before this card rendered.”

Affected scope: Permit detail page only. Methodology, audit log, search results, homepage stats, alerts, and exports were not affected.

Disposition: Card removed pre-launch. The supporting filteredAllRows memo, which only fed this card, was removed as dead code in the same change. The underlying allRows filter is retained because it still feeds the “Data through {latestPeriod}” page header (a defensible use — the latest monitoring period in our data for this permit, regardless of compliance status).

Status: Removed pre-launch. A defensible compliance-rate display would require data-model work to (a) define what counts as a monitoring period for a given permit, (b) ingest non-exceedance periods completely or document the coverage gap, and (c) decide how to present coverage gaps to attorneys and journalists. Open question post-launch.

2026-05-01 — Search results card: max_exceedance_pct and top_parameter computed independently

Discovered during: Scoping for direction-explicit display copy on the permit detail and search surfaces (PR #33).

Issue: The search_exceedances RPC aggregates per permit and returns two columns that the search results card visually associates with each other but that are computed by separate aggregations. max_exceedance_pct is MAX(exceedance_pct) across all rendered rows for the permit, while top_parameter is the most-frequent parameter for the permit, picked by a separate LATERAL subquery. The two values do not necessarily come from the same underlying row. A permit whose most-frequent parameter is Total Suspended Solids (a ceiling parameter) but whose single highest-percentage row is a Dissolved Oxygen floor reading would show, on the search card, the parameter from the first aggregation alongside the percentage from the second — visually implying a relationship that does not exist in the underlying data. The defect was invisible in ceiling-only display copy and surfaced when the direction-explicit rewrite (PR #33) needed the stat_base_code of the row that produced max_exceedance_pct in order to classify direction, and discovered that field is not exposed in the RPC return at all.

Affected scope: Search results card and the search-results CSV export. The permit detail page renders per-row data with full row context (parameter, stat_base_code, qualifier, permit_limit, reported_value all available per row) and is unaffected.

Disposition: Permit detail surfaces shipped direction-explicit copy in PR #33 using the per-row classifier. Search card rendering remains the previous ceiling-only “+X% over reported limit” wording until a post-launch follow-up migrates search_exceedances to expose a max_exceedance_direction column computed inside the aggregate. The methodology page (PR #32) discloses that direction-aware detection applies and notes the historical recovery work in flight, which covers the gap until the migration lands.

Status: Open. Pending post-launch RPC migration.

2026-05-01 — Same logical exceedance ingested multiple times (cross-source and intra-source)

Discovered during: PR #33 verification, while cross-checking the one row with stat_base_code = 'UNKNOWN' in production against the rendered preview.

Issue: The exceedances table has no uniqueness constraint on the natural key (permit_number, parameter, monitoring_period_end, permit_limit, reported_value). The same logical exceedance can appear multiple times when a row is ingested from PA DEP and again from EPA ECHO with the same numeric value but different stat_base_code (PA DEP sometimes records 'UNKNOWN' where ECHO has the proper short code), or when ECHO ingestion writes the same row twice with slightly different field state under different id values (qualifier '<=' vs null, monitoring_period_end populated vs null).

Platform-wide totals across rendered rows (exceedance_pct > 0): 155 distinct natural-key tuples appear in more than one row; 334 total rows are part of those duplicated tuples; about 1.1% of all rendered rows are duplicate-attributable. Cross-source duplication accounts for 1 of the 155 tuples; the remaining 154 are intra-source duplications, mostly within ECHO.

Worst-affected permits by extra-row count: PA0254967 (38 extra rows), PA0053091 (21), PA0001937 (20), PA0215520 (10), PA0008281 (7).

User-visible symptom on the permit detail page: the same parameter and period appear in the exceedance table multiple times, with the same numbers but different Limit Type values (a “—” row from PA DEP plus an “Instantaneous Maximum” row from ECHO, for example).

Affected scope: Permit detail page (visible duplicate rows for affected permits). Search-card aggregates that count rows (exceedance_count, and longest_streak indirectly) inherit small over-counts on these permits. Methodology and audit-log surfaces are unaffected.

Disposition: Dedup logic is post-launch. Two paths under consideration: a uniqueness constraint on the natural key with a tie-break preferring the source that has the more specific stat_base_code, or a view that collapses duplicates at read time. Either approach requires deciding the canonical row — likely PA DEP for permit-level metadata and ECHO for the stat code when PA DEP has 'UNKNOWN'. Until then, users on the affected permits will see duplicate rows on the permit detail page.

Status: Open. Pending post-launch dedup work.

2026-04-30 — ECHO ingestion: floor-parameter override dead at raw-column stage

Discovered during: Phase 1 fixture-verification work (regression tests for direction-aware exceedance detection).

Issue: The ingestion pipeline has a parameter-name override that classifies certain parameters (Dissolved Oxygen, % Removal, Stream Flow Minimum) as floor limits regardless of stat code, because those parameters are always-floor by EPA convention. The override was silently inactive at the ECHO ingestion stage: the function checked for a column named PARAMETER, but raw ECHO data uses PARAMETER_DESC until column-mapping runs later in the pipeline. So for ECHO rows where parameter ∈ {DO, % Removal, Stream Flow Min} AND the stat code field was empty AND the qualifier wasn't ">=", the override didn't fire and the row was misclassified as a ceiling limit. Real floor exceedances in that slice were silently dropped.

Affected scope: ECHO data only. PA DEP data was not affected (its scraper has always been direction-aware and uses post-map column names). Slice size: 0 rows visibly miscategorized in the current database (every override-pattern parameter already had a recognized stat code via the IB/DC/ME/MJ path or a >= qualifier). Upper bound is unknown without re-pulling raw ECHO bulk data, since dropped rows were never inserted. Phase 4's diff report will surface the actual count.

Disposition: Fixed. Patched _is_always_floor_parameter to accept PARAMETER_DESC as a fallback when PARAMETER isn't present. Pure widening — when PARAMETER exists, behavior is identical to pre-patch; when only PARAMETER_DESC exists (raw ECHO before map_columns), the override now fires. Verified against fixture: previously-dropped DO no-stat row now correctly flagged; compliant DO no-stat row still correctly dropped (no over-flagging). Phase 1 regression tests assert post-fix behavior. Historical recovery of dropped rows is part of Phase 4's ECHO re-pull.

Status: Fix shipped. Historical recovery pending Phase 4.

2026-04-30 — Homepage stats included null/null placeholder rows

Discovered during: Phase 0 audit of UNKNOWN-direction bucket.

Issue: The get_site_stats() database function did not filter on exceedance_pct > 0, so it counted 4,687 PA DEP rows with null permit_limit and null reported_value (placeholder rows from a separate data-quality issue). Every other surface on the platform — search, streaks, permit detail, alerts, exports — already filtered on exceedance_pct > 0 and excluded these rows. The homepage stats were the only inconsistent surface. Pre-fix counts were inflated: total exceedances by 45.9%, total permits by 24.6%, parameters tracked by 28.3%.

Affected scope: Homepage display only. No alerts, exports, or permit-detail pages were affected.

Disposition: Migration 060 updated get_site_stats() to filter on exceedance_pct > 0. Verified post-migration counts match expected: total exceedances 30,022, total permits 2,322, parameters tracked 129.

Status: Shipped. The 4,687 placeholder rows themselves are a separate data-quality issue tracked in the internal post-launch backlog.

Pre-2026-04-30 — Historical ECHO ingestion was direction-blind

Discovered during: Pre-launch defensibility audit.

Issue: Historical ECHO ingestion compared reported_value > permit_limit uniformly, without considering whether a permit limit was a ceiling (maximum) or a floor (minimum). For Minimum-type limits (Dissolved Oxygen, pH lower bound, % Removal, Stream Flow Min, and any limit with stat codes IB/DC/ME/MJ), this meant every compliant reading was incorrectly flagged as an exceedance, and every reading that should have been flagged as a floor-limit exceedance was incorrectly recorded as compliant. Roughly 660 ECHO-only permits were affected.

Affected scope: ECHO data only. PA DEP data was not affected. Current pipeline is direction-aware; only historical ingestion was affected.

Disposition: Cleanup migrations 026, 031, and 054 removed the false-positive exceedance flags. False negatives — floor-limit exceedances that were never inserted because the direction-blind comparison rejected them — could not be recovered from the existing database; those rows must be re-pulled from ECHO source data. Estimated recovery: ~17,936 historical floor-limit exceedances. Re-pull is Phase 4 of the multi-phase fix, scheduled post-launch with a methodology-disclosure fallback if the re-pull doesn't pass sample validation.

Status: False positives cleaned. False-negative recovery pending Phase 4.

How this document is maintained

Entries are added when a data-quality issue is discovered, regardless of whether it's user-visible. Each entry covers: what was found, what was affected, what was done, and current status. Entries are not deleted when issues close — closure is recorded in the Status field.

Future audit findings follow the same template: Discovered during / Issue / Affected scope / Disposition / Status.