Scope freeze — water_quality_station Locationdex (10th Locationdex kind)

Frozen 2026-06-28. Source pasted by Mike: data.gov record for EPA ScienceHub DOI 10.23719/1407630, "Assessing Dungeness River BMP Effectiveness Using an Ecological Function Approach." Mike chose the shape: option A — an expandable water_quality_station kind (over a watershed-specific dungeness_water_quality kind).

What it is

An expandable, multi-study registry of measured water-quality monitoring stations. The slot is one fixed monitoring station; the STUDY / watershed it came from is a slot FIELD, not the key. This is the same registry shape Mike chose for coral_reef_station: future EPA water-quality studies (or a broader STORET/WQX pull) extend the SAME kind rather than minting a new one each time.

Slot = a monitoring station (Locationdex)

• Slot id: <study_id>-<station_id>, namespaced so a future study's station "716" can never overwrite this study's station 716.
• The place: station name, latitude, longitude, datum, org, state, county, HUC, station_type.
• Embedded measured record: a per-characteristic summary (n, detected vs non-detect counts, units, detected min/median/max, year range) plus the raw samples list. A precipitation station instead embeds annual precip totals (raw hourly is summarized, not embedded wholesale).

First study loaded — Dungeness River, WA (study_id = dungeness)

One small watershed near Sequim, WA (Clallam County, HUC 17110020), monitored by Clallam County, the Jamestown S'Klallam Tribe, WA Dept. of Ecology and EPA. 84 station slots:

• 80 STORET water-quality stations (station_type = water_quality), 1999-2014, ~16,500 grab-sample results across 316 characteristics (fecal coliform, water temperature, flow, nutrients, turbidity, pH, pesticide panels, ...).
• 1 USGS gauge USGS-12048000 (station_type = usgs_wq_gauge), water chemistry + flow back to 1959.
• 3 NOAA COOP precipitation gauges (station_type = precipitation), Port Angeles area, back to 1948.

Second study loaded — CIMEK, a partially mined eastern Kentucky watershed (study_id = cimek)

EPA ORD, DOI 10.23719/1407636 ("CIMEK water chemistry"). 60 stream station slots (station_type = water_quality), sampled ~8 times Dec 2012 – Aug 2014 (487 samples), 47 measured characteristics per station: field params (temperature, specific conductance, pH, turbidity, DO, flow), major dissolved ions (sulfate, chloride, alkalinity, hardness, N species, TDS/TSS), and a full dissolved-metals panel (Al, As, Ba, Cd, Ca, Cr, Co, Cu, Fe, Pb, Mg, Mn, Ni, Se, Na, Sr, Zn, ...).

CIMEK arrives as a wide analyte matrix (one row per sample, each analyte column trailed by a DQ* data-qualifier column), not long STORET rows, so it has a dedicated reader (build_cimek_stations) that melts each row to per-characteristic samples and feeds the shared summarize_characteristics / station_summary aggregators. The reader pairs analyte→qualifier by POSITION (leading field params have none; the duplicated DQ19/DQ25 header names never collide) and drops the header row repeated once per campaign block.

Each slot also carries watershed_metrics joined from the study's "Site AVGs" sheet: watershed area (km²), HUC12, river km, and pct_mined — the mining-impact gradient that is the study's whole point (0 % at unmined headwaters up the watershed).

Third study loaded — Three Bays estuary, Cape Cod MA (study_id = threebays)

EPA ORD (CEMM-ACESD), DOI 10.23719/1520968 ("Summary of benthic conditions in the Three Bays estuary as of 2019"). 25 estuary station slots (station_type = estuary_benthic), one September-2019 survey visit each, in Barnstable, MA. Each station carries two things:

• In-situ water quality + sediment (melted to per-characteristic samples so they flow through the shared summarize_characteristics / station_summary aggregators): shallow & deep temperature, salinity, dissolved oxygen (mg/L and %sat), Secchi depth, and sediment sand/fines/ gravel fractions plus total organic carbon.
• A benthic_community block — the macroinvertebrate community: total_individuals, the full taxa list (species, count, NODC code, Gillett-2015 ecological group), and derived_metrics (density/m², Pielou's evenness, AMBI, species richness, Shannon-Weiner H, M-AMBI, and the US M-AMBI condition class). The derived metrics are arithmetic transforms of the measured organism counts (feedback_measured_reality_only: arithmetic-on-measured is IN, like a diversity index or a tidal potential from measured positions), carried as clearly-labelled DERIVED fields, never as a projection of a future state.

Three Bays arrives as a station-summary table + a separate long taxa table (a third file shape after Dungeness STORET-long and CIMEK wide-matrix), so it has its own reader (build_threebays_stations) that melts the summary row's water/sediment columns to samples and joins the taxa table by station number (3B-<n> slot id ← the summary's integer station column). The note/header rows of the taxa file are dropped (STATION not an integer), and NA cells are skipped per "data is data" rather than zero-filled (a station that measured only shallow water carries only shallow characteristics; an NA condition class stays null).

Fourth study loaded — Pensacola Bay, FL (study_id = pensacola)

EPA ORD (Gulf Ecology Division), Nestlerode et al. 2018, DOI 10.23719/1500941. A Summer-2016 benthic survey across a Pensacola Bay salinity gradient. 9 estuary station slots (station_type = estuary_benthic, pensacola-P02 … pensacola-P08 plus P05-MID/P05-EAST), same slot shape as Three Bays. Each slot melts its measured water + sediment to per-characteristic samples: bottom salinity (CTD, ppt), water depth (m), sediment grain size (% silt/clay/sand/organic/ inorganic), and a 12-element trace-metal panel (Antimony…Zinc, mg/kg). The benthic_community block carries the macrofauna community (taxa with pooled abundance + individuals/m²) plus derived_metrics (Primer-E richness/Margalef/Pielou/Brillouin/Fisher/Shannon/Simpson) and the GOM B-IBI index scores (LS / HS-SF / HS-xF classes; the applicable class per station stored alongside).

Pensacola arrives as a multi-sheet workbook (a fourth file shape, after Dungeness STORET-long, CIMEK wide-matrix, and Three Bays summary+taxa), with a dedicated reader per sheet: coordinates, a long macrofauna table, sediment grain size, a transposed metals table (metals as rows, stations as columns), Primer-E community indices, and GOM B-IBI. A single canonical station-id helper (_pens_sid) both unifies case across sheets (P05-east vs P05-East → P05-EAST, which would otherwise split into duplicate slots) and filters junk rows (Table 4's transposed metric-name rows like "Fisher's alpha" don't match the P<digits> pattern, so they never leak as slots). Integrity check on build: each station's summed taxa pooled abundance equals its Primer-E N (e.g. P02 = 248). The multi-year (2003–2015) DO-profile sheet is deferred (it is context for the 2016 survey, not part of it).

Fifth study loaded — Georgia Coastal Plain streams (study_id = georgia_cp)

EPA ORD, DOI 10.23719/1526438. A benthic-macroinvertebrate + physiochemical + land-use survey of 76 small wadeable streams in the Georgia Coastal Plain (the "CP-HStR" set), each sampled once in Feb–Mar 2019. 76 stream station slots (station_type = stream_benthic, georgia_cp-<SiteID>), across three Level IV ecoregions (Dougherty Plain 65g, Tifton Upland 65h, Atlantic Southern Loam Plains 65l) in 29 Georgia counties. Two workbooks join on SiteID:

• luphyschem drives each slot: in-situ field water quality (temperature, dissolved oxygen, specific conductance, pH, turbidity) and physical-habitat scores (instream / bottom-substrate / total) melt to per-characteristic samples; watershed basin area (km²) plus NLCD-2016 land-cover percentages (forest / wetland, whole-watershed and 50 m riparian zone) go in watershed_metrics; county comes from here.
• bio adds the benthic_community.derived_metrics block: genus richness plus functional-feeding-group and habit proportions (%EPT, collector-gatherer/filterer, burrower, climber, clinger, shredder, predator, sprawler, swimmer, scraper). The source ships per-site community metrics, not a per-site taxa list, so taxa is empty and level = "genus".

No coordinates in the source — lat/lon are null and backfillable (feedback_data_is_data_partial_coverage); location is carried as Georgia county + Level IV ecoregion. Because the slot is stream_benthic (not estuary_benthic), the validator's coordinate requirement does not apply. This is a fifth file shape: two paired workbooks joined on SiteID, with a header-normalizer that strips a trailing non-breaking space from County and a SiteID normalizer that removes internal whitespace so the bio sheet's 6460329 alt joins the luphyschem sheet's 6460329alt.

Sixth study loaded — Arid West stream reaches (study_id = arid_west_sdam)

EPA ORD, HydroShare DOI 10.4211/hs.f970981d5dde4e60a01beff47197670a. The field-data-and-code set behind EPA's Beta Streamflow Duration Assessment Method (SDAM) for the Arid West — a rapid field protocol that classifies a stream reach as perennial / intermittent / ephemeral. 145 stream station slots (station_type = stream_benthic, arid_west_sdam-<SITECODE>) across seven Arid West states (AZ, CA, NV, CO, NM, WY, UT), sampled 2019–2021. Sites carry coordinates (unlike Georgia). Two files inside the study zip join on the site globalid:

• Input/mydf2.csv drives each slot: the field-observed flow-duration determination (Determination_Final → flow_duration_class, with flow_duration_status = Preferred when set by two or more lines of evidence, else Acceptable), geomorphic field measurements (mean bankfull width, valley slope, and the New Mexico Level-1 ordinal scores for sinuosity, substrate sorting, benthic-macroinvertebrate and fish abundance) melt to per-characteristic samples, and the benthic derived_metrics (taxa richness, total abundance, EPT taxa/abundance, mayfly abundance, perennial indicator taxa/abundance, non-insect and Odonata/Coleoptera/Hemiptera taxa) come from here.
• Input/NewData/aquaticinvertebrate_...csv supplies the per-site benthic taxa list (taxon + semi-quantitative abundance class + indicator role), pooled per reach.

Revisits collapse to one place. Twelve reaches were sampled on two dates; because a Locationdex slot is a place, both visits merge into one slot (n_visits) that carries the field measurements as a dated series and the taxa pooled across visits, exactly as a Dungeness station carries a multi-date record. Nothing is dropped or double-counted. A reach where the crew found no aquatic invertebrates records the field sentinel taxon = "None"; that reach's measured zero lives in the all-zero derived_metrics, so the sentinel is filtered and taxa is left empty (not a taxon named "None"). This is a sixth file shape: two CSVs read from inside a HydroShare zip, grouped by SITECODE.

Seventh study loaded — Narragansett Bay seven-decade benthic trends (study_id = ne_estuary_trends)

EPA ORD, ScienceHub DOI 10.23719/1520128. The data behind a manuscript on how the benthic macroinvertebrate community of urbanized Narragansett Bay, RI changed across seven decades (1950s–2010s). The workbook is organized as figure-source sheets, not a flat station table, and its "stations" are transient per-decade grab-sample codes (~1,100 of them, no persistent identity and no coordinates). The stable, meaningful place is the named subarea, so — Mike's call 2026-07-03 — the slot is the subarea, not the grab code. 4 estuary-benthic slots (station_type = estuary_benthic, ne_estuary_trends-<code>):

• Greenwich Bay (GB), Providence River & Upper Bay (PRUB), Mt Hope Bay (MHB), Upper West Passage (UWP).

Each slot carries the subarea's full record, assembled from three sheets:

• Fig 2&5 (Station / Subarea / Decade / ES_50) → the diversity trajectory: per decade, the mean/min/max and station count of ES(50) (Hurlbert's expected taxa in 50 individuals, arithmetic on measured grab counts). Also carried as one measured/derived sample per station-decade estimate.
• Fig 4 / 6 / 7 / 8 (one presence/absence taxa matrix per subarea) → the benthic_community: the cumulative distinct-taxa list, each taxon tagged with the decades it appears, plus per-decade richness. Presence/absence, so no abundances (the validator's abundance-sum check is a no-op here).

Coordinates. The source ships none. Because estuary_benthic slots must be geolocated (the validator enforces it), each subarea is anchored at its named-bay centroid (a real place, not invented precision on an anonymous grab); coordinate_note records that the point is a subarea centroid. This is a seventh file shape: figure-source sheets aggregated from grab code up to the named subarea.

Eighth study loaded — Taylorsville & Harsha reservoir flyover (study_id = taylorsville_harsha)

EPA ORD, ScienceHub DOI 10.23719/1412548. Coincident surface-water observations collected as the in-situ ground truth for a satellite chlorophyll-a algorithm portability study (Harmful Algae 76:35–46, 2018): during airborne hyperspectral flights over two reservoirs, boat crews sampled dense site grids within ~2 h of image acquisition. The imagery and derived algorithm are not in this file; the measured water chemistry is. 114 water-quality slots (station_type = water_quality, taylorsville_harsha-<site id>) across two lakes on two June-2014 dates:

• Taylorsville Lake, KY (Lake = T, Spencer County), sampled 2014-06-18.
• Harsha Lake, OH (Lake = H, Clermont County), sampled 2014-06-27.

The ChemistryData sheet is one row per site; ~36 measured columns melt to per-characteristic samples: lab analytes (organic carbon TOC/DOC/POC, the full N/P nutrient suite, extracted chlorophyll pigments RCHLa/b/c + phaeophytin-corrected via the trichromatic + phaeophytin equations, suspended solids, titrated alkalinity/hardness), the in-situ sonde readings (temperature, pH, specific conductance, turbidity, chlorophyll, blue-green-algae phycocyanin, dissolved oxygen), and the ELISA microcystin-LR. A companion AlgaeIE sheet adds a phytoplankton_community block for the 39 sites submitted for algae identification/enumeration: per-taxon microscope density + biovolume and a microcystin_producer flag (with an n_microcystin_producer_taxa roll-up).

• Coordinates prefer the GPS position recorded during sampling and fall back to the planned target position (noted in coordinate_note); every field site is geolocated.
• Replicate rows. 11 sites carry two rows with identical coordinates + timestamp (field/lab duplicates, e.g. two T10 at TP 20.6 and 21.7 ugP/L). They merge into one slot carrying both samples ("data is data"), not overwrite; 125 field rows → 114 distinct-site slots.
• QA/QC control rows (site id like QAC) are dropped (not field sites). This is an eighth file shape: a wide lab + sonde chemistry table with a companion phytoplankton sheet.

Ninth study loaded — New England stream stable isotopes (study_id = smucker_isotopes)

EPA ORD, ScienceHub DOI 10.23719/1434636 (Smucker et al. 2018, Ecological Indicators 90:295–304). A survey of 77 wadeable streams across an urbanization gradient in Massachusetts + Rhode Island. 77 stream_benthic slots (smucker_isotopes-<site>), coordinates present (unlike the Georgia study). The Raw_Data sheet is one row per site; measured columns melt to per-characteristic samples:

• Water chemistry — DOC, TP, TN, NOx, NH₄, PO₄, Cl.
• Carbon & nitrogen stable isotopes (activity_type = lab/IRMS) of periphyton and each invertebrate functional-feeding group (predator/omnivore/collector/detritivore/scraper), a new measured layer for the registry (a food-web tracer). nd cells are not-measured and skipped.

The companion Invertebrate_taxa_list sheet carries the per-site, per-family isotopes (22 families), embedded as the benthic_community block (level = family, metric = stable_isotope — each taxon carries a δ¹⁵N/δ¹³C pair rather than an abundance, so the validator's abundance-sum check is a no-op). The taxa site ids are whitespace-normalized (90 E2 → 90E2) to join the Raw_Data table (the Georgia _ga_norm_sid pattern); 60 of 77 slots carry the block, the rest being sites with no matching family row. Watershed land cover (area, % impervious/forest/natural) rides as watershed_metrics. The source states no sampling date, so survey_date/sample dates are null. This is a ninth file shape: a stream survey table plus a long per-family isotope sheet.

Measured reality vs held out (bright line)

feedback_measured_reality_only. All three files are physical measurements: grab-sample lab/field results, gauge flow readings, rain-gauge precipitation.

• OUT — the Taylorsville/Harsha HCO3- (est) column. Bicarbonate stoichiometrically computed from measured alkalinity + pH: an estimate, not a measurement, held out on the same footing as the NWIS-computed TDS-from-conductance. Dropped, and recorded per slot as n_computed_held_out = 1. (The raw sonde RFU fluorescence columns are skipped as raw sensor units redundant with their calibrated ug/L and cells/mL siblings — a de-duplication, not a bright-line exclusion.)

• OUT — the Smucker watershed_population + population_density columns. Census counts areally allocated onto each stream's watershed polygon: a model, on the same footing as the held-out Narragansett "population in watershed" layer and PRISM interpolation, so not ingested. The watershed land-cover percentages (fractions of the observed NLCD map) stay, same as the Georgia study. The two d15N_nms* NMS-ordination columns are also skipped, as an ancillary multivariate statistic rather than a raw per-characteristic measurement.

• OUT — the Narragansett "population in watershed" driver (Fig 9) and the NAO index (Fig S2). Fig 9 is a modeled areal allocation of census counts onto each subarea's watershed (fractional values; e.g. Warwick RI's ~82.7k census population resolves to 38.3k "in-watershed"), plus it carries ALL TOWNS subtotal rows. Areal apportionment is a model, on the same footing as PRISM interpolation, so it is not ingested. Fig S2's North Atlantic Oscillation index is an exogenous climate driver of unstated station-vs-reanalysis provenance and is not part of this phenomenon's measured benthic record. The dex organizes the measured benthic reality of each place; a downstream paper supplies its own measured urbanization/climate driver.

• OUT — the SDAM model the Arid West set was built to fit. The set's purpose is to train a random-forest classifier that predicts flow-duration class from field indicators. The model objects (Output/*.Rdata), its predictions (Output/all_predictions.csv), the .R model code, and the PRISM interpolated-climate covariates are model/estimate layers and are not ingested. What is taken IN is the field observations that fed the model: the crew's own flow-duration determination (Determination_Final, from direct lines of evidence, not a model guess), the invertebrate community, and the geomorphic field measurements.

• OUT — USGS rows whose method is "Computation by NWIS algorithm" (e.g. total dissolved solids computed from specific conductance, parameter 70301): an algorithm's estimate, not a measurement. 133 such rows dropped from the Dungeness USGS gauge; the count is recorded on the slot (n_computed_held_out). A measured TDS value (param 70300) is kept.

• IN — the Georgia land-use columns. The NLCD-2016 forest/wetland percentages are fractions of an observed satellite land-cover classification (a measured map of what the ground cover was), not a forecast or projection, so they are carried as watershed context. Same footing as the community metrics, which are arithmetic on measured genus-level counts.

feedback_data_is_data_partial_coverage: non-detect samples are kept (value nulled, detect = "non-detect") rather than dropped; a station with no coordinates (the precip gauges, and every Georgia stream site, whose source ships none) is kept with lat/lon null. Pesticide-screen stations that are almost entirely non-detect (e.g. station 716, 310 of 331 samples below quantification) are carried in full, the non-detection itself being a measured result. In CIMEK the same rule maps a U data-qualifier to a non-detect (the numeric cell is the detection limit, not a measurement): value nulled, detect = "non-detect", sample kept.

Storage

Locationdex sibling storehouse data/location_storehouse/water_quality_station/<slot_id>.json, file-per-slot (594 slots: 84 Dungeness + 60 CIMEK + 25 Three Bays + 9 Pensacola Bay + 76 Georgia Coastal Plain streams + 145 Arid West stream reaches + 4 Narragansett Bay subareas + 114 Taylorsville/Harsha reservoir sites + 77 New England isotope streams), via event_storehouse.write_dossier + rebuild_index_from_disk. The build clears the kind dir first so removed/renamed slots never linger.

Build-time validation (shared validator)

The build self-validates at the end via terrapulse.monitor.dex_validator.validate_kind_or_raise, the shared data-layer check every dex build runs. pytest logic tests verify the parse functions on mock rows; the validator verifies the ACTUAL built storehouse: the index count matches the dossiers on disk, every slot's event_id equals its filename and is unique, required fields (event_id/station_id/study_id/station_type) are present, ids match ^[a-z0-9_]+-\S (so a junk row can never leak as a slot), and a kind-specific slot_check enforces that every estuary_benthic slot carries coordinates and that its embedded taxa counts reconcile with the reported total. The coordinate requirement is scoped to estuary_benthic precisely so the coordinate-less stream_benthic (Georgia) and precipitation (COOP) slots pass legitimately. It runs in well under a second and fails the build loudly on any hard violation.

This caught a real issue on first run: Pensacola station P06's source workbook is internally inconsistent — its macrofauna abundance sheet lists 12 taxa summing to 73 individuals, while its Primer-E community sheet reports S=13, N=78. That is a fact about the source, not a build bug, so both numbers are carried faithfully on the slot (total_individuals = the reported Primer-E N; taxa_abundance_sum = the sum of the embedded per-taxon counts) and the reconciliation mismatch is surfaced as a build WARNING rather than a hard error. We neither invent the missing five individuals nor drop the reported N.

Expanding the kind

Add a sibling study by adding one entry to STUDIES (label, watershed, HUC, file URLs) and, if it is not STORET-shaped, a small reader feeding the shared summarize_characteristics / station_summary aggregators. Same-shaped studies (STORET WQX exports) need only the registry entry. Slot ids are namespaced by study_id, so studies never collide.

Deferred

• Coordinates for the 3 COOP precip gauges (the rainfall file carries no per-COOP lat/lon).
• Raw hourly precipitation (only annual totals embedded in v1).
• Cross-reference of USGS-12048000 to the streamgauge Locationdex slot for the same gauge.
• Sibling EPA water-quality studies / a broader STORET/WQX national pull (the point of option A).
• Spatial sweep / cross-match (deferred for every Locationdex kind until one needs it).
• No live edge: the Dungeness study is a closed 1948-2014 archive.