Real-time river level, flow, gage height, and temperature data aggregated from government agencies (USGS, NOAA, USACE, USBR, IDWR) for the Willamette Kayak and Canoe Club.
Live site: levels.wkcc.org
USGS / NOAA / USACE / USBR / IDWR APIs
|
levels pipeline (Python — runs hourly via systemd)
fetch → fetch-usgs-ogc → calc-rating → update-gauge-cache → calculator → build
| |
SQLite DB public_html/
(kayak.db) (static HTML/CSV)
| |
PHP layer <-------- nginx ------->+
(dynamic pages, (static pages,
plots, editing, per-state tables)
API endpoints)
Python pipeline fetches data from government APIs, parses it through source-specific parsers, stores observations in a normalized SQLite database, and generates static HTML pages with inlined SVG sparklines.
PHP web layer handles interactive features — reach descriptions with time-series plots, data browsing, the reach picker, and a reach editor.
Both layers share the same SQLite database. See docs/database-schema.md for the full schema (ER diagram).
# 1. Install into a venv (one-time)
python3 -m venv .venv
.venv/bin/pip install -e ".[dev]"
# 2. Activate it — every subsequent step assumes `levels` resolves to .venv/bin/levels.
source .venv/bin/activate
# 2b. Clone the metadata repo and point the code at it. The metadata CSVs +
# reaches*.json live in the separate `kayak_data` repo (data-repo split),
# NOT in this code repo (only src/kayak/data/db/migrations/ stays here). The code
# finds them via DATASET_DIR.
git clone git@github.com:mousebrains/kayak_data.git ../kayak_data
export DATASET_DIR="$(cd ../kayak_data && pwd)" # or persist in ~/.config/kayak/.env
# 2c. Pick a build output dir OUTSIDE the repo and dataset (required — S3h:
# `levels build`, which `levels pipeline` runs, has no default and refuses
# the engine/dataset trees).
export OUTPUT_DIR=~/public_html_dev # or persist in ~/.config/kayak/.env
# 3. Create the schema (empty tables + stamped migrations; schema only —
# the former sources.yaml seeding is gone)
levels init-db
# 4. Load state/gauge/reach/source metadata from the kayak_data repo (DATASET_DIR).
# init-db creates no rows: without this step every source is an orphan, the
# pipeline's orphan-check fails and the site
# renders empty. sync-metadata applies the CSVs (matched by stable id); the
# import_metadata step then applies the reach geom/gradient JSON sidecars
# (excluded from reach.csv), so the pipeline produces a populated site.
levels sync-metadata
python scripts/import_metadata.py
# 5. Run the full pipeline (fetch live data, generate HTML)
levels pipeline
# 6. Emit the runtime config the PHP layer requires. Every PHP page reads
# /etc/kayak/runtime-config.json (or $KAYAK_CONFIG_PATH) and returns
# HTTP 500 if it's missing — a bare `php -S` without this step serves
# only the static pages. Re-run after changing ~/.config/kayak/.env.
levels emit-config --out ~/.config/kayak/runtime-config.json
# 7. Serve locally
KAYAK_CONFIG_PATH=~/.config/kayak/runtime-config.json php -S localhost:8000 -t "$OUTPUT_DIR"
OUTPUT_DIRis required.levels build(whichlevels pipelineruns) has no default output directory and refuses the engine or dataset trees (S3h) — setOUTPUT_DIRto a non-repo path (e.g.~/public_html_dev) in~/.config/kayak/.envand serve that —KAYAK_CONFIG_PATH=… php -S localhost:8000 -t "$OUTPUT_DIR"(config step 6 above). See.env.exampleand CLAUDE.md for the full rationale.
Prefer fully-qualified paths over source .venv/bin/activate if your
shell config makes activation noisy: replace every levels … with
/path/to/.venv/bin/levels …. Production runs that way — see
deploy/SETUP.md for the prod layout.
| Command | Purpose |
|---|---|
levels init-db |
Create tables and stamp all known migrations (schema only; metadata loads via sync-metadata) |
levels migrate |
Apply pending src/kayak/data/db/migrations/*.sql files (tracked in schema_migrations) |
levels pipeline |
Run full pipeline: fetch → fetch-usgs-ogc → calc-rating → update-gauge-cache → calculator → build |
levels fetch |
Fetch observations from all active sources (standalone — also runs as pipeline stage 1) |
levels fetch-usgs-ogc |
Fetch USGS continuous data via the OGC API for gauges linked to a USGS source |
levels calc-rating |
Interpolate missing flow/gage values using rating tables |
levels calculator |
Evaluate calculated expressions (synthetic gauges) |
levels build |
Generate static HTML/CSV/text to $OUTPUT_DIR (required; outside the repo) |
levels decimate |
Thin old observations (keeps 90d full, 1h/365d, 6h/archive) |
levels seed-maintainer --email … |
Create or promote an editor row to status=maintainer |
levels trace --putin … --takeout … |
Trace a reach along NHD HR flowlines |
levels assign-huc |
Assign HUC12 codes to reaches via WBD polygons (requires [geo] extra) |
| Endpoint | Purpose |
|---|---|
/api.php?id=N&type=T&days=D |
JSON time-series data for a source |
/latest.php |
JSON latest observations for all gauges |
/description.php?id=N |
Reach detail page with plots and metadata |
/plot.php?id=N&type=T |
SVG time-series chart |
/reach.php |
Reach browser with navigation |
/gauge.php?id=N |
Gauge details and associated sources/reaches |
/source.php?id=N |
Source metadata and recent observations |
/data.php?id=N |
Raw observation data inspector |
/picker.php |
Interactive reach picker |
/edit.php?id=N |
Reach editor (maintainer-only, editor-session cookie auth) |
/custom.php |
Custom levels page builder |
# Testing
pytest # Run all tests (in-memory SQLite)
pytest --cov=kayak # With coverage report
pytest -k test_store_observation # Run a single test
# Linting
ruff check src/ tests/ # Lint
ruff format src/ tests/ # Format
mypy src/ # Type check
# All checks (via Makefile)
make check # lint + typecheck + testSee CONTRIBUTING.md for development workflow and guidelines.
Production runs on Debian 13 with nginx + PHP-FPM + systemd timers. See deploy/SETUP.md for the full deployment guide.
Key systemd timers:
- kayak-pipeline — hourly at :12 (fetch + build)
- kayak-healthcheck — hourly at :45 (data-freshness check, emails on staleness)
- kayak-backup-hourly — every hour at :38 (sqlite
.backup+ WAL checkpoint; 24-copy retention; RPO ≤ 1h) - kayak-decimate — daily at 02:32 (thin old observations)
- kayak-cert-expiry — daily at 06:30 (Let's Encrypt cert health probe; pages on <21 days remaining)
- kayak-editor-retention — daily at 03:45 (prune expired editor sessions + magic links)
- kayak-status — daily at 03:30 (renders the
/_internal/statusoperator dashboard tovar/status.html) - kayak-fetch-osmb — daily at 03:30 (fetches configured map overlay GeoJSON; legacy service name)
- kayak-cert-renewal-test — weekly Monday 04:15 (
certbot renew --dry-run) - kayak-backup-weekly — weekly Sunday 03:15 (4-copy retention; chains to off-site upload via
OnSuccess=) - kayak-audit-gauges — weekly Sunday 03:29 (orphan-gauge + reach-mapping audit, emails on drift)
- kayak-config-drift — weekly Sunday 05:30 (diffs repo
conf//deploy//systemd/against/etc/, alerts on drift) - kayak-heartbeat — weekly Sunday 06:00 (confirms alert pipeline)
- kayak-recap — weekly Monday 07:00 (pipeline-activity recap email from journald events)
| Document | Contents |
|---|---|
| CLAUDE.md | Architecture, dev setup, conventions, key patterns |
| CONTRIBUTING.md | Development workflow, testing, adding parsers |
| deploy/SETUP.md | Production deployment (Hetzner CPX11) |
| docs/database-schema.md | Full schema reference (25 ORM tables + schema_migrations) |
| docs/schema-overview.svg | ER diagram |
| docs/security/posture.md | Current security posture (controls, accepted findings, operator obligations) |
The project ships under four complementary licenses, reflecting the different origins and curatorial labor of each layer:
| Layer | License |
|---|---|
| Code (Python + PHP) | GPL v3 or later |
| Database metadata | CC BY-NC 4.0 |
| Calculated gauge series | CC BY-NC 4.0 |
| Observation time-series | Public domain at source (USGS, NOAA, USACE, USBR, IDWR, state agencies) |
See LICENSE for the full code-license text and LICENSE-DATA for the full data-license terms.