USCanada
ENFR

privatewelldata / Data rules

Data rules (deterministic; no AI in the data path)

Hard rule: every cleaning / normalising / mapping step is a script in scripts/. The live site uses no AI at all. AI never writes,

rewrites, translates or maps a data field; the only AI use is an offline draft of French interface text (see the end), pending human review.

The French cross-review covers UI strings (lib/i18n.ts) and guide prose only.

Provenance

Every labs row stores source (name), source_url, license, fetched_at (raw file mtime = download date) and updated (ingest date).

Raw official files are kept in data/raw/ (git-ignored) so every value can be re-checked.

Validation and quarantine (`scripts/ingest_labs.py`, `validate()`)

A row is flagged=1 with a flag_reason when any of these fail; flagged rows are stored but never published

(site queries read the pub_labs view = labs WHERE flagged = 0):

  • name present, ≥ 3 chars; source_url and fetched_at present
  • phone, if present, has 10 digits (11 with leading 1) or a recognisable NNN-NNN-NNNN pattern
  • coordinates, if present, inside the country box (US 18–72°N, 180–64°W; CA 41–84°N, 142–52°W)
  • US postal = 5 digits; Canadian postal = A1A 1A1
  • city or address present

Test badges (`TESTS` regexes in `ingest_labs.py`)

A badge appears only when the official scope text names the analyte (case-insensitive):

| badge | pattern |

|---|---|

| bacteria | coliform, e. coli, escherichia, heterotroph / hétérotroph |

| nitrate | nitrat, nitrit |

| arsenic | arsenic |

| lead | lead, plomb (whole word) |

| metals | iron/fer, mangan, copper/cuivre, uranium, antimon, barium/baryum, mercur, cadmium, chrom, selen/sélén |

| organics | organic (not "inorganic"), VOC, volatile, pesticid, herbicid, atrazine, benzene, hydrocarbures, trihalom, EPA 524/525 |

| pfas | pfas, pfoa, pfos, perfluor, EPA 533/537 |

| radio | radio, gross alpha / alpha brut, radium, tritium, gross beta |

Category words alone ("Inorganic", "Microbiological") do NOT create analyte badges. Ontario licence classes are categories,

so Ontario labs show their classes as text and only get lead/radio badges where the class names them.

Quebec badges come from the decoded parameter names of each accredited domain code (below).

Public-system-only flag (`public_only`)

public_only=1 if (a) the source's own lab-type field says "Public Water System", "Public Wastewater System", "Treatment, Storage" or "Municipal"

(California ELAP SLABTYPE, first 120 chars of scope), or (b) the lab name matches PUBLIC_RX

(water plant/treatment/utility/authority/district, wastewater, sewer, public works, city/town/village/county water, WTP/WWTP, municipal…).

Override: if the source states the lab accepts samples from private citizens (Ontario field), public_only=0.

These labs are hidden from search/featured by default and labelled when shown. It is a heuristic label, shown as "likely".

Quebec domain codes (`scripts/build_qc_domains.py`)

Decoded from the official MELCCFP/CEAEQ document *Champs et domaines d'accréditation en vigueur (DR-12-CDA)*

(data/raw/cda.pdf → pdftotext -layout). Parser rules are in the script docstring; output data/qc_domains.json

plus data/qc_domains.meta.json (source URL, document date, fetch date). The script fails loudly if any code in the page-1 index is not decoded.

Verification (`scripts/check_labs.py`, run after every load)

D1 row count == seed count; zero rows without provenance; published == total − flagged; 20 random published labs per database

re-checked against the raw official file (name, phone digits, city, postal must appear verbatim).

PDF state lists (`scripts/parse_states.py` → `data/states/*.json` → `ingest_labs.py`)

Parsing rules are in each parser's docstring (pdftotext -layout; column/position rules only). Each record keeps the exact source text

block (raw) for review. Extra validation for PDF records: street required, and a name ending in a bare number (name/address boundary

unclear) is flagged. Badges:

  • PA (DEP Accredited Labs Report, drinking-water matrix only): TESTS regexes over the listed analyte names.
  • MN (MDH private-well list): the five table columns map 1:1 (coliform→bacteria, nitrate, arsenic, lead, manganese→metals). X*

(subcontracted, per legend) still counts and is stated in the scope text; a cell printed as both "X" and "cell" is ambiguous and not claimed.

All MN labs are marked "accepts private samples" because that is the list's definition.

  • OH (Ohio EPA combined list): bacteria only from the microbiological methods (legend: E. coli and Total Coliform by MMO-MUG,

Quanti-Tray, Membrane Filtration); nitrate only when the category literally says Nitrate; pfas when "PFAS". Broad categories

("Standard Chemistry", "Trace Metals") are shown as text but do not create analyte badges.

CALA (BC, AB, NS, NB)

Lab list = the JS objects embedded in https://directory.cala.ca/directory-search; scope = each lab's public-scope page (fetched 1 at a time,

3 s gaps, 120 s back-off, stop after 3 failures). Stored: name, address, city, province, postal, phone, water appendices, valid-to date.

Never stored: emails, contact names. A lab is listed only if at least one appendix has a Matrix containing "Water" (an appendix without a

Matrix line inherits the previous one, as printed). "Water" in CALA includes wastewater and non-potable water, so the label says

"CALA accreditation (water)" and the call-ahead warning applies. clientsServed = "Specified Clients"/"Parent Institution Only" → public_only.

ME / NJ / MA / NH / MI (browser downloads, 2026-10-17)

  • ME: AllLabs.xlsx (Maine CDC) + CommercialListMaineLabs.pdf (5/16/2026). Badges = TESTS regexes over active drinking-water analytes.
  • NJ: NJDEP Data Miner export. No street/ZIP in source; located by municipality centroid. No badges.
  • MA: MassDEP private-well survey (12/18/2025). Microbiology→bacteria, Radiochemistry→radio; accepts_private from the "residential customers" column.
  • NH: NHDES labs-private-wells.pdf. accepts_private=1. Two labs printed with no blank line are split at a second phone number. City only accepted when the last address chunk is exactly "City, ST"; otherwise flagged.
  • MI: EGLE chemistry/micro/lead-copper PDFs plus radiological, out-of-state, PFAS (in/out-of-state) and Cryptosporidium PDFs (second layout, parse_states.mi_extra), merged by EGLE lab number. Badges = TESTS over analytes; being on the PFAS list adds pfas, on the radiological list adds radio. PFAS analyte cells wrap across lines, so only the list name is kept for those files. Out-of-state labs keep their own state as region. PDF bullet glyph U+F0B7 is stripped from streets.
  • ZIP checks: ZIP's Census ZCTA state must equal the stated state ("ZIP belongs to another state").
  • geo seed uses INSERT OR REPLACE: d1.py retries a batch after a connection error, and a batch that had already committed then hit the primary key.
  • New flag for every US record: ZIP's Census gazetteer place is >40 km from the stated city → "ZIP does not match city".

County well-water pages (US, /us/county/)

Built by scripts/build_county_risk.py from Water Quality Portal Result downloads (per county, siteType=Well; WQX 2.2 narrowResult, plus WQX 3.0

basicPhysChem when the box can download it). Unit/speciation conversions, non-detect handling, dedupe, "latest result per well" and the

10-well minimum are in the script docstring. Each row keeps the WQP query URL and download date. Wells include monitoring/research wells, and

the pages say so. Plausibility ceiling: arsenic 1000 µg/L, nitrate 100 mg/L as N; when a well's latest value is above it the county

"Highest" is withheld and the page says how many wells were set aside for review (they still count as above the limit). Spot-check: Adams County WI arsenic recomputed by hand from the raw CSV (10 wells, 5 non-detects, median 0.34, max 2.0) matched.

Quebec municipal well pages (/ca/quebec-wells/)

Built by scripts/build_qc_wells.py from the PACES compiled hydrogeological database (Données Québec, CC-BY 4.0). All 2019 PACES regional

files carry the same compilation (12 projects), so one file is read; the 2025–2026 files (Estrie, Laurentides, Lanaudière, Mauricie est,

BSL_KRT) use other schemas and are not included yet. Analytes and Quebec norms (RQEP Q-2 r.40, annexe 1): arsenic 0.010, manganese 0.12,

uranium 0.020, fluoride 1.50, barium 1.0 mg/L. Nitrate is not summarised (compiled fields do not state N vs NO3). Value parsing,

non-detects (incl. the negative-value "-DL" convention), placeholder dates (1900/1901/9000), dedupe, latest-per-well, the 10-well minimum and

the plausibility ceiling (100x norm, fluoride 10x; max withheld) are in the script docstring. Owner names and addresses in the source are

never read into the output. Spot-check: Val-d'Or arsenic recomputed from the raw rows (55 wells, 2 above 0.010 mg/L: 0.018 and 0.021) matched.

Quebec PACES 2025–26 files (added 2026-10-18)

Estrie and Laurentides-Les Moulins are merged into qc_wells (rules in scripts/build_qc_wells.py docstring). Lanaudière and Mauricie-Est result

tables have empty parameter and site columns, and BSL_KRT has maps only, so they are not used. Spot-checks against the raw GeoPackages:

Mascouche barium (17 wells, 1 above 1.0 mg/L, max 3.0, Laurentides file) and Austin arsenic (1 Estrie site + 9 compiled sites) matched.

Prices (/[loc]/test-costs/)

scripts/prices.py fetches fixed official pages and stores a price only when a fixed regex matches the page text; the matched text is kept as

the quote with URL and date. No match = hidden (WSLH homeowner package is currently hidden this way). Nova Scotia: the NSH web page lists

bacteria P/A at $34.50 while the January 2025 requisition PDF lists $30.00; the web page is used.

Translations

scripts/ai_fr.py: DeepSeek draft + second DeepSeek review pass against docs/glossaire-qc.md, then OQLF typography fixed by script.

Used only for UI strings and legal/guide prose (i18n/*_extra.json, i18n/*_legal.json). Data fields are never translated.