How one reading travels from a sensor in a river to a number on a customer dashboard, which system owns each fact, and the invariants that must hold at every join. Written for whoever is next to touch it, human or model.
_fw environment variable.lume-inventory-db/api/site-series applies shared/ecoli-model.js and returns per-site series.thelume.ai.Site and time attribution live here, not in inventory. The join that attributes a raw sample to a site is a time-bounded one, which does in-water clipping and multi-sensor stitching at once:
api_organization (= the customer; org_name must EXACTLY equal contracts.customer)
|
api_meter (organization_id)
|
api_watersystem (the site: site_name, latitude, longitude, timezone)
|
api_installation (gateway_id, water_system_id, installed_time, removed_time)
| removed_time NULL = still in water
api_gateway (barcode)
|
api_modem <-- api_payload --> api_samplesraw / api_tlfdiagnosticsA reading is only valid for a site if its timestamp falls inside that site's
[installed_time, removed_time) window for the sensor that was installed there at that
time. Sensors move between sites and sites swap sensors. Pairing a lab result to a sensor outside
its window silently corrupts the calibration set — it does not error.
api_samplesraw has no gateway_id. Join through
api_payload, and api_payload carries modem_id, not
gateway_id: api_gateway.modem_id = api_payload.modem_id.api_tlfdiagnostics keys its timestamp as time, not timestamp, and
its rows arrive in different payloads from the samples. Match temperature to a sample
by time (±15 min), not by payload_id — that join returns nothing.barcode comes back as a number. A JavaScript
Set<string>.has(barcode) silently fails to match. Cast with ::text.led_power = 512
and the sipm_bias nearest 3000 (acceptance window 2960–3040).
Never average across biases — that mixes gains.net = mon2_val − mon2_dark. mon2_dark is ambient
light and is exactly removable. It says nothing about whether the cap is on.mon1 and mon2 are not separate detectors. Do not ratio them
or read agreement between them as corroboration.mon2.Pumphaus has no query filtering on its REST API, so the Worker keeps a bounded mirror in D1 and projects a flat read model from it. Three tables, three jobs:
| Table | Owns | Written by |
|---|---|---|
sensors | Barcode, customer, status, notes, clean-water baseline window, install_tof_baseline | Inventory UI; the fleet page's Notes column writes the same field |
sensor_installations | Mirror of Pumphaus api_installation, keyed by installation_id | Worker write-through on installation edits |
site_installations | Flat read model the dashboards query | rebuildSiteInstallations(db, customer), regenerated wholesale from the mirror |
sensor_baselines | One row per clean-water capture: tlf_mon2, tof_sps, temperature | Lab capture, logged manually |
contracts | customer and dashboard_pin | Contracts UI |
rebuildSiteInstallations reads only from sensor_installations.
Writing straight to Pumphaus without updating the mirror leaves the read model blind: the site exists,
the sensor reports, and the dashboard is empty. Any Pumphaus write must be followed by a mirror update
and a rebuild.
Every sensor has a lab-collected clean-water baseline giving its own TLF and ToF offsets. These are canonical — do not synthesise one when a lab capture exists.
mon2 at LED 512 / bias 3000 over
[clean_water_baseline_at, clean_water_baseline_end_at].install_tof_baseline hides real fouling, because the detector compares
the daily ToF floor against it.Single source of truth: shared/ecoli-model.js. Three consumers — a Pages Function that
emits a browser IIFE at /js/ecoli-model.js, the Worker (bundled directly), and the dashboards.
Never copy coefficients into a page.
predictEcoliCfu picks a path in this order, and which path a sensor lands on is decided
by whether it has a baseline pair:
| Path | Chosen when | Temperature term |
|---|---|---|
| Chicago | CHICAGO_MODEL.fe[bc] exists | on T − 20 |
| Field (mixed effects) | FIELD_MODEL.base20[bc] and btof[bc] exist | −0.0205 × (T − 20) |
| Generic | everything else | −0.157 × T (absolute) |
Its temperature term is applied to absolute T and is large: at 26.5 °C it
contributes −4.16 against an intercept of +3.54. A sensor sitting exactly at its own clean-water
baseline therefore goes negative above 22.6 °C and clamps to the 0.1 CFU floor
regardless of what TLF is doing — and warm water quenches TLF, which is precisely when
mon2 − baseline is least able to compensate. On 2026-08-06 this had seven installed
sensors reporting “0 CFU, safe”, including a paying customer.
The fix was not new model structure. FIELD_MODEL already has a designed
fallback for units with no grabs — fe_default plus the population slopes. What those
sensors lacked was only the per-sensor baseline pair, which is what routes a sensor onto
that path at all. Supplying base20/btof from each unit's own lab capture moved
them onto the field path. Entries added this way are listed in
FIELD_MODEL.synthetic_base, and isLocallyCalibrated() deliberately reports them
as not grab-calibrated: routed onto the field path is not the same as validated against
local grabs.
Sensor readings are one half of the system; the other is ground truth. Every lab result
we calibrate against lands in one D1 table, validation_grabs, and is served read-only.
There is no login and no key.
| Source | Covers | Timezone handling |
|---|---|---|
mwater | Boulder and Denver Colilert grabs, from the mWater datagrid | Stored in the data-entry device's Mountain clock; the sample's own timezone field is the correction key |
chicago | H2NOW Chicago programme grabs, from the programme Google Sheet | CDT (UTC−5) → add 5 h for UTC |
email | Results delivered directly by a partner | As recorded |
An hourly cron refreshes the table from its sources with idempotent upserts. Manual exclusions survive the refresh.
Open validation.thelume.ai/colilert/ and scroll to Field Calibration Data. The table is served live from the database, with Source and Method columns and both local and UTC times.
https://lume-inventory-api.evan-thomas-3d8.workers.dev/api/validation/grabs# All grabs (Boulder + Denver + Chicago), including swept/deduped rows
curl "https://lume-inventory-api.evan-thomas-3d8.workers.dev/api/validation/grabs?status=all"
# Chicago only
curl "https://lume-inventory-api.evan-thomas-3d8.workers.dev/api/validation/grabs?source=chicago&status=all"
# Chicago since a date
curl "https://lume-inventory-api.evan-thomas-3d8.workers.dev/api/validation/grabs?source=chicago&since=2026-06-01"The response is { "grabs": [ ... ], "count": N }.
| Filter | Values |
|---|---|
source | mwater / chicago / email |
method | colilert / mf |
customer | e.g. Chicago |
barcode | sensor barcode |
since | ISO date, e.g. 2026-06-01 |
status | active (default) or all |
present | 1 (default, live rows) or all to include deduped |
Key fields per row: sample_at (UTC), sample_local_raw + sample_tz,
site_name, barcode, ecoli_cfu, method,
source, customer, present.
For Chicago, site_name is the H2NOW site — North Branch, Wild Mile, Main Stem,
South Branch, Cal-Sag Channel, Little Calumet — and barcode is populated
only where a sensor was validly installed at that site at that time.
As of 2026-08-06 that is 26 of 47 Chicago rows; the other 21 predate an install or fall
outside a window, and are deliberately left unpaired rather than guessed.
Match a grab to sensor data within ±20 minutes, taking
exactly one reading: LED 512, sipm_bias nearest 3000. The
2960–3040 band is an acceptance guard, not a set to average over.
The pairing is only valid if the grab time falls inside that site's install window for the sensor that was actually there (section 2). A grab with no in-window reading stays unmatched. Leaving it unmatched is correct; reaching for the nearest sensor instead is how a calibration set quietly acquires wrong rows.
Normalise every timestamp to UTC before the ±20 min test and the window test.
Of 212 Colilert grabs carrying a measured result, 174 pair to grab-calibrated field sensors and 26 to the Chicago model — leaving only 12 on the generic path, from three barcodes. That is why the generic path has almost no recreational validation, and why the ceiling described in section 5 went unnoticed. Detection limits differ by method and must not be mixed: Colilert/Quanti-Tray tops out at 2,419.6 MPN/100 mL (a censored “>” value, not a measurement), while Aquagenx CBT tops out at 100 CFU/100 mL and needs censored handling.
These are asserted by scripts/audit_pipeline.js in the virridy-lume-summary repo.
It is read-only and touches nothing.
node scripts/audit_pipeline.js # human readable
node scripts/audit_pipeline.js --json # machine readable
node scripts/audit_pipeline.js --strict # exit 1 on any ERROR (use in CI)org_name must equal contracts.customer exactly. Anything else resolves the PIN to zero sites.rebuildSiteInstallations.site_installations out of step with the mirror: a rebuild was skipped.base20 without btof (or the reverse) yields NaN inside predictEcoliCfu.api_modem + api_gateway if the device is new.api_organization (name must match contracts.customer exactly)
→ api_meter → api_watersystem → api_installation.
Dry-run inside BEGIN/ROLLBACK first.installed_time to when the sensor entered the water, not when it was
powered on. See the recovery method below.sensor_installations mirror row, then rebuild
site_installations.sensors.status = 'Deployed' and the correct customer.base20/btof pair./api/site-series returns data for the PIN.Units are typically powered on indoors and installed later — sometimes days later. Entering water
drops both the TLF and ToF channels sharply in a single 15-minute sample, because water
absorbs the 940 nm ToF IR and kills the dry-chamber backscatter that inflates mon2.
The reliable discriminator is the daily range of enclosure temperature:
roughly 0.7–1.2 °C indoors (climate controlled), 2–6 °C once in a river, with a
one-day spike on the transport day. Do not use
chamber_status.dark_dry — it fired true once in 341,575 readings and stayed
false through a confirmed air exposure. It is inert.
removed_time), open a new one. Never edit a site name in place
to mean a move — that rewrites history and silently re-attributes every past reading.shared/ecoli-model.js only, and bump MODEL_VERSION./js/ecoli-model.js reports the new MODEL_VERSION.