produced-water-map

# Produced Water Map **An interactive map of 113,000+ USGS produced-water samples with a built-in Claude data analyst.** Ask a question in plain English — *"Which region has the most samples in the USGS database, and what's the average TDS there?"* — and an embedded Claude agent writes the SQL, queries the data live, and explains the answer. All 113,131 samples render and filter in the browser in real time.  ## Why Produced water — the salty water that comes up alongside oil and gas — is a major environmental-management challenge in basins like the Permian, and a potential resource if treated. The USGS publishes an excellent geochemical database of it, but as a 113k-row, 209-column spreadsheet. The people who need it (water researchers, treatment engineers, regulators) mostly don't live in SQL. So this project started as a data-visualization tool and grew a second half: an AI analyst that lets a domain expert interrogate the data conversationally. ## What it does **The map** — every sample in the continental US, rendered with deck.gl over MapLibre, colored by play type, filterable by region/basin/play, with dark, street, and satellite basemaps. The entire dataset ships to the browser as a 25 MB SQLite database and is queried client-side in a WebAssembly worker — pan, zoom, and filter are instant, and there's no server in the hot path.  **The analyst** — a Claude agent (Claude Agent SDK) embedded in a chat window beside the map, with two tool servers: - **SQL over the wells data** (MCP): schema inspection, query planning, read-only SELECTs against the USGS database - **Jupyter notebooks** (MCP): the agent can build and execute notebooks for deeper analysis and hand you the executed result The agent runs with a locked-down tool policy — only this app's MCP tools, no shell, no file writes, no network fetch, isolated from any Claude settings on the host machine. ## Architecture ``` apps/frontend SvelteKit + Svelte 5 · MapLibre GL + deck.gl · SQLite WASM worker apps/backend FastAPI · Claude Agent SDK · SSE streaming chat ├── MCP: query_wells (read-only SQL over USGS data) └── MCP: jupyter (notebook build + execute) data/ USGS National Produced Waters Geochemical Database v3.0 (CC0) converted to optimized SQLite (essential: 25 MB / full: 43 MB) ``` ## Running it ```bash # prerequisites: node + pnpm, python + uv, an Anthropic API key (or Claude Code auth) pnpm install # frontend only (map works standalone) pnpm dev # → http://localhost:5173 # full stack (map + AI analyst) export ANTHROPIC_API_KEY=sk-ant-... pnpm dev:full ``` ## Data **USGS National Produced Waters Geochemical Database v3.0** (Dec 2023) — 113,131 samples, 209 fields, 2002–2022 coverage, CC0 public domain. Conversion scripts in `scripts/` and `data/README.md`. ## Honest status & what I'd do differently This is a working prototype, not a product. Where it stands: - Map, filtering, client-side SQL, streaming agent chat with SQL tools: **working** - Notebook generation: works, but the Jupyter service is the most fragile piece - An in-progress schema migration (`play_type` → multi-valued `play_types`) lives on a branch of its own; the main line stays on the stable schema until it's finished What I'd do differently, having built it once: - **Backend-first, sooner.** The flashy map came first; the durable value turned out to be the cleaned, queryable dataset and the agent's SQL access to it. If I built it again, I'd invert that order. - **Pin the agent SDK and lock the tool policy from day one.** An unpinned SDK plus an evolving CLI broke streaming while the project was shelved, and the default tool config was far too permissive for something that fronts a web endpoint. Both are fixed, but they should never have been defaults. - **Fewer moving parts.** Frontend codegen (OpenAPI → client) plus a monorepo plus a windowed UI was a lot of surface for a prototype. The interesting 20% didn't need the other 80%. ## License / attribution Data: USGS, CC0 1.0. Map tiles © their respective providers. Code: personal project by [Matt Dicataldo](https://github.com/matthewdicataldo).