Download, transform, and geocode Uisce Éireann (Irish Water) supply and works notices, and infer each notice's end time from its text with a local LLM. The result is a single SQLite database, rebuilt by CI twice a day and published as a GitHub release, plus a statuspage-style static site with per-county supply availability and A–F grades.
The website that this repo generates is rebuilt from the latest published DB after every data build and on every push to main, so a UI change is live in about a minute without waiting for the next data build.
What the time figures mean. This project does not measure outage duration and cannot: the feed never records when supply was actually lost. What it measures is the span from when a notice was published to the end that notice reports (notice_to_end_seconds), and the site publishes the subset where that end is an observed "works are now complete" update rather than a schedule. That published median is a floor on true length. The availability percentages are not: about one disruption in twenty reports no usable end, and since 2026-08-15 those are charged the typical observed span for their kind of works rather than counted as zero — a total has to put a number on every event, and omitting one asserts it lasted no time at all. See notes/data-quality.md and notes/statuspage-methodology.md.
Grab the latest uisce.db from releases - no setup needed:
scripts/fetch-db.sh
It wraps gh release download --pattern uisce.db; every data build publishes its own
release, so the latest one always holds a complete DB.
Tables:
cases— one row per published notice pin (title, description, dates, status, impact flags, WGS84 coordinates).work_categoryis a slug normalised from the title (burst_main,essential_works, …);work_type(Planned/Unplanned) is taken from the feed but overridden for categories where the label is unambiguous (a burst main is never planned).geocode_cache— reverse-geocoded address per rounded coordinateinferred_cases— LLM-extracted end-time signal and computednotice_to_end_secondsper case
Before leaning on start_date/end_date or per-case counts, read notes/data-quality.md — several fields don't mean what they appear to mean.
The cases schema is declared once, in create_db, and stamped into PRAGMA user_version (SCHEMA_VERSION, currently 4). The published DB is downloaded and updated in place each build, so check_schema_version runs every time and carries older DBs forward via MIGRATIONS.
Migration is deliberately narrow: additive nullable columns only, which SQLite applies without rewriting a row. A DB missing any v1 column is refused with instructions to rebuild rather than migrated. That asymmetry is on purpose — the DB is an accumulating archive of a feed with no history, so a rebuild costs every case the feed no longer serves plus the geocode cache. Take a copy before rebuilding.
cases.closed_at (v2) records when a build first observed a case stop being Open. The feed publishes only current status, so this is the sole record of the transition. Three consequences for anyone querying it:
- It is observation time, not event time — resolution is the build cadence.
NULLis ambiguous: either still open, or closed before the column existed (every case closed prior to v2). Pair it withstatusrather than readingNULLas open.- It is a floor. Cases created and closed between two builds are never observed open, so no transition exists to record. Under the original Mon/Wed/Fri cadence that was 12% of newly-appearing cases (measured 2026-07-21); daily builds cut it to 1.9%, and the twice-daily cadence since 2026-07-31 to ~1.1% at best — the residual is Uisce Éireann's own administrative lag, which no build frequency can close. See notes/data-quality.md.
History from before v2 can be partially recovered by replaying the published release DBs, each of which is a full snapshot. Run the Build DB workflow with replay_closed_at ticked — it does the whole thing in one build, after the pipeline has migrated the DB and stamped its own transitions.
The replay recovers the same measurement the live path makes (first build observing the case non-Open), never overwrites an existing stamp, and is idempotent, so it is safe to re-run — worth doing if the DB is ever restored from an older release. It reached 24% of closed cases on 2026-07-21; the rest closed before the earliest published snapshot.
Locally, against a directory of downloaded snapshots named <release-tag>.db:
uv run uisce-replay-closed-at --snapshots snaps # dry run
uv run uisce-replay-closed-at --snapshots snaps --writecases.first_start_date (v3) stamps the start_date seen on the first download of a case and never advances it — the feed re-stamps start_date in place, so the original is otherwise lost. It is first-observed, not earliest-seen, and nothing computes a published number from it yet; it is an instrument accumulating history. See notes/data-quality.md.
cases.vanished_at (v4) stamps the first build that found a case missing from the feed, and is cleared if it returns. A case dropped while Open never sends the transition closed_at records, so this is its only close: the site treats such a case as closed with no end signal. The stamp is only trusted because the pipeline first checks the download against the feed's own count (returnCountOnly) and refuses a short one. See notes/data-quality.md ("Cases that vanish from the feed").
Requirements: uv (any recent version; it manages Python itself) and, for the geocoding step, a free LocationIQ API key.
git clone https://github.com/baz8080/uisce
cd uisce
uv sync
echo 'LOCATIONIQ_API_KEY=your_key_here' > .env
uv run uisce-pipeline
uisce-pipeline downloads all cases from the ArcGIS feed, maps and geocodes them, and builds out/uisce.db. Geocoding results are cached in the DB, so the first run makes one LocationIQ request per unique coordinate (rate limited to 1/s — expect a couple of hours from scratch) and later runs only geocode new coordinates. Start from a released DB (see above) to skip most of that.
Two options for working without the paid geocoding step:
uisce-pipeline --skip-geocode— refresh cases from the ArcGIS feed but skip LocationIQ; new coordinates get placeholder geocode rows (retried on the next real run). Handy for seeing the current source data quickly. Don't publish the result — those cases have no location yet.uisce-backfill— re-derive the computed columns (trimmed title,work_category,work_type) on the existingout/uisce.dbwith no network at all. Run it after editing the category rules to re-apply them to data you've already downloaded.
uv run uisce-site
Reads out/uisce.db and writes a fully static site to out/site/ (serve it with any file server, e.g. python -m http.server -d out/site, or just open index.html — it needs no server). Per county and month it shows day-by-day status bars, population-weighted supply availability, and an A–F grade — only hard supply outages (bursts, plant/reservoir/pump interruptions, unplanned repairs) count against availability; restrictions, discolouration and non-disruptive works are shown but never accrue downtime.
Clicking a county drills into it (#county/Kildare): the same figures per named area, plus the cases open right now grouped by area and the cases observed to close that month. Availability there is measured against the area's own population, which makes it far harsher than the county figure and is the whole point; there are no letter grades at that level, because the A–F thresholds are calibrated to county-months.
Every pin lands in one of three kinds of area, all of them Census geography:
- the settlement its affected population centres on, so
Newbridge,Mount Carmel, NewbridgeandNewbridge,collapse onto one row with a real population; - a Local Electoral Area, where the settlement is too big to read as one row — the Census counts a city and its suburbs as a single settlement, and
Dublin city and suburbsis one area of 1.26 million that held 83% of Dublin's cases. Splitting the five agglomerations over 50,000 turns Dublin into 40 rows. Be warned that LEA names are electoral compounds rather than the names people use:ClontarfandDundrumread naturally,Kimmage-Rathminesdoes not; Around <Electoral Division>for the ~40% of cases outside any settlement, since most of the network runs between towns rather than through one. The prefix matters: a rural Electoral Division is the parish around the town it is named for, and that town has its own row.
The weighting uses Census 2022 Small Area populations (data/sa_pop.csv, committed; regenerate with uv run uisce-fetch-sa-pop) and the area each Small Area belongs to (data/sa_towns.csv, committed; regenerate with uv run uisce-fetch-towns). Both come from attributes on the CSO Small Area layer, so the area populations reproduce every published Census settlement figure exactly. Before reading too much into the numbers, see the notes:
- notes/statuspage-methodology.md — every modelling decision and its rationale
- notes/water-sla-benchmarks.md — Ofwat/CRU service levels and why the grades can't borrow them
- notes/population-data-sources.md — the CSO/Tailte open-data join
- notes/data-quality.md — what the source fields actually mean
Duration inference reads each notice and extracts the end-time signal: CPU rules first (src/uisce/rules.py, which answer the templated ~93% of notices), falling back to a local model (currently gemma-4-12b-qat) behind an OpenAI-compatible API, e.g. LM Studio, for everything the rules abstain on. See notes/rules-vs-llm-end-times.md for the measurements behind the split.
CI runs the rules half on every data build (uisce-infer --rules-only) and commits the results, so the local run only ever has the abstained residue — recurring windows, lifts, Irish, the ambiguous rest — to send to the model.
git pull— CI appends todata/inferred_end_times.jsonl;.gitattributesmerges a concurrent local append rather than conflicting- Start the LLM server on :1234
scripts/fetch-db.shuv run uisce-infer— appends results todata/inferred_end_times.jsonl(committed to the repo; only new/changed descriptions are processed); commit and push- (Local check only — CI rebuilds the table itself)
uv run uisce-build-inferred
notes/how-it-works.md is the orientation map — the four flows, what each module owns, and where to go to change a given thing. Start there when picking the project back up.
src/uisce/
pipeline.py download, map, geocode, load cases (uisce-pipeline)
inference.py end-time extraction to JSONL: (uisce-infer)
rules first, LLM fallback
rules.py CPU rules for the templated majority
build.py build inferred_cases from the JSONL (uisce-build-inferred)
site.py generate the static status site (uisce-site)
sa_pop.py fetch Census Small Area populations (uisce-fetch-sa-pop)
towns.py map Small Areas to named areas (uisce-fetch-towns)
site.html front end copied into out/site/
config.py shared paths, constants, HTTP session
tests/ pytest suite (no network access needed)
notes/ how it works, data-quality findings, pipeline caveats
The commands are console entry points declared in pyproject.toml; run them from the repo root, since data paths (out/, data/) are relative.
uv sync --group dev
uv run pytest
uv run ruff check
CI lints and tests on every push. The Build DB workflow runs the pipeline twice a day and publishes the refreshed DB as a release; the Build site workflow then rebuilds the site from it, and also runs on every push to main so UI changes deploy on their own. The freshness banner reads the DB's last feed sighting (data_as_of_iso), not the site build's clock, so a UI-only deploy cannot mask a failed data build. The cadence is not just freshness: the gap between builds is the resolution of cases.closed_at, and a case that opens and closes within one gap is never observed open at all — see notes/data-quality.md ("Twice-daily builds: why, and why not three") for the measured tradeoff.