Skip to main content

Audit Log

Last Updated: June 13, 2026

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-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.

Open item — recurring long-period coverage. The scheduled ingest currently runs the monthly statewide pass (Tier 1) only. The quarterly (Tier 2) and per-permit five-year (Tier 3) passes were run as one-time recovery sweeps, not on a schedule, so newly-closed quarterly/semi-annual/annual periods are not yet refreshed automatically. Recurring long-period scheduling remains an open item; until it exists, long-period coverage for periods closing after the recovery sweep may lag. This limitation is stated plainly rather than implied as continuous coverage.

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). OPEN: recurring long-period scheduling (forward-coverage refresh).

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. PermitMinder'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 (current). 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.

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.