Skip to content
Back to search

mcp-waterboards

MCP Server for CA Waterboards

Safety scan

A static text scan, not a guarantee. Select the badge to see what was scanned and every finding.

Checked today
Badge for your README
findsafeskills safety rating

Markdown

[![findsafeskills safety rating](/api/listings/skandru9-swrcb--mcp-waterboards/badge.svg)](/listings/skandru9-swrcb--mcp-waterboards)

HTML

<a href="/listings/skandru9-swrcb--mcp-waterboards"><img src="/api/listings/skandru9-swrcb--mcp-waterboards/badge.svg" alt="findsafeskills safety rating" /></a>

Install

i

What was scanned

The files the safety scan read, as written by the repository's author and shown as plain text. Lines with a finding are marked.

README.md · README · 110 lines
  1. # Waterboards MCP Server
  2. An [MCP](https://modelcontextprotocol.io) server that answers California **drinking water** questions — public water systems and their SAFER risk/violation status, source-water chemistry, and school lead results, by ZIP code or water system ID — from curated **Databricks Gold views** over State Water Board open data (data.ca.gov). Built with [FastMCP](https://github.com/jlowin/fastmcp) and [uv](https://docs.astral.sh/uv/); deployed as a **Databricks App**.
  3. Design decisions and the review of the original plan: [req/mcp_design_review.md](req/mcp_design_review.md) (local, git-ignored).
  4. ## Architecture
  5. ```
  6. data.ca.gov drinking water files (+ Census ZCTA centroids)
  7. └─ sql/drinking_water/ Bronze → Silver → Gold (run in a Databricks SQL warehouse)
  8. └─ users.suresh_kandru.v_* views (the contract in this README)
  9. └─ Databricks SQL warehouse
  10. └─ this server (FastMCP, Databricks Apps, /mcp over HTTP)
  11. └─ MCP client / agent
  12. ```
  13. The server is read-only and queries **only** `<catalog>.<schema>.v_*` Gold views (plus `information_schema` for dataset discovery). Every caller-supplied value is bound as a named SQL parameter.
  14. ## Data pipeline
  15. `scripts/download_drinking_water_data.py` downloads the source files. Upload them (plus the Census ZCTA gazetteer as `zcta_centroids.txt`) to `/Volumes/users/suresh_kandru/raw/drinking_water/current/`, then run in order:
  16. | Script | Layer | Output |
  17. |---|---|---|
  18. | `sql/drinking_water/00_setup.sql` | setup | volume, `silver_clean_str` helper |
  19. | `sql/drinking_water/01_bronze.sql` | Bronze | raw all-string tables `bronze_dw_*`, `bronze_zcta_centroids` |
  20. | `sql/drinking_water/02_silver.sql` | Silver | typed `dw_systems`, `dw_safer`, `dw_lead_schools`, `dw_source_chemistry(_latest)`, `dw_wells`, and 50 km ZIP pair tables |
  21. | `sql/drinking_water/03_gold.sql` | Gold | the `v_*` views below |
  22. | `sql/drinking_water/04_tap_water.sql` | Bronze/Silver/Gold | DDW SDWIS tap-water results and ZIP-to-service-area lookup → `v_tap_water_for_zip`. Inputs `tap_water_results.csv.gz` and `zip_service_areas.csv` are built by `scripts/build_tap_water_results.py` and `scripts/build_zip_service_areas.py` |
  23. Silver turns `NULL`/`NaN`/`N/A` into SQL NULL, parses dates, keeps non-detects explicit (`is_non_detect`, `result_numeric` NULL, `reporting_limit`), and drops street addresses. Systems are located by SAFER coordinates, wells by their own coordinates, and schools by the ZIP parsed from their address.
  24. ## Tools
  25. | Tool | Gold view | Purpose |
  26. |---|---|---|
  27. | `waterboards_list_datasets` | `information_schema.tables` | What datasets are available |
  28. | `waterboards_find_water_systems_near_zip` | `v_water_systems_near_zip` | Water systems near a ZIP, optionally only failing/at-risk/violating |
  29. | `waterboards_get_water_system` | `v_water_systems` | Profile, SAFER status, violations for one `pws_id` |
  30. | `waterboards_get_source_chemistry_near_zip` | `v_source_chemistry_near_zip` | Latest well chemistry near a ZIP, filter by chemical |
  31. | `waterboards_get_system_source_chemistry` | `v_source_chemistry_latest` | Latest well chemistry for one `pws_id` |
  32. | `waterboards_get_school_lead_near_zip` | `v_school_lead_near_zip` | School lead sampling near a ZIP |
  33. | `waterboards_get_tap_water_for_zip` | `v_tap_water_for_zip` | Latest tap/treated-water sample results for the systems serving a ZIP |
  34. All return `{results, count, offset, has_more, next_offset, source, retrieved_at}`, or `{error, hint}`. Empty results include an actionable `hint`.
  35. ### Gold view contract
  36. The views must exist with at least these columns (the server does `SELECT *` then filters/orders on them):
  37. | View | Required columns |
  38. |---|---|
  39. | `v_water_systems` | `pws_id, system_name, safer_status, is_failing, has_primary_mcl_violation, has_ecoli_violation, has_treatment_technique_violation, data_as_of, source_url` + profile/risk columns |
  40. | `v_water_systems_near_zip` | `zip, distance_km` + all `v_water_systems` columns |
  41. | `v_source_chemistry_latest` | `pws_id, well_id, chemical_name, is_non_detect, result_numeric, units, reporting_limit, last_observed, source_url` |
  42. | `v_source_chemistry_near_zip` | `zip, distance_km` + all `v_source_chemistry_latest` columns |
  43. | `v_tap_water_for_zip` | `zip, pct_of_zip_covered, pws_id, system_name, water_stage, analyte_name, result_numeric, units, is_non_detect, mcl, exceeds_mcl, last_observed, source_url` |
  44. | `v_school_lead_near_zip` | `zip, distance_km, school_name, action_level_exceeded, result_numeric, last_observed, source_url` + school/system columns |
  45. `zip` + `distance_km` are precomputed (pairs up to 50 km), so the server filters `distance_km <= :radius_km` (the tools take `radius_miles`, max 31, and convert to km) rather than doing geospatial math per request. Keep PII (street addresses, phone numbers) out of Gold.
  46. ## Run locally
  47. ```bash
  48. cp .env.example .env # set DATABRICKS_SQL_WAREHOUSE_ID
  49. databricks auth login --host https://<workspace>.cloud.databricks.com
  50. uv sync --group dev
  51. uv run python main.py # stdio
  52. ```
  53. Local auth uses the Databricks SDK's unified auth (CLI profile via `DATABRICKS_CONFIG_PROFILE`, or `DATABRICKS_HOST` + `DATABRICKS_TOKEN`). Connect a client with an entry like:
  54. ```json
  55. {
  56. "mcpServers": {
  57. "waterboards": {
  58. "command": "uv",
  59. "args": ["run", "waterboards-mcp"],
  60. "cwd": "/absolute/path/to/this/repo"
  61. }
  62. }
  63. }
  64. ```
  65. HTTP mode (what Databricks Apps runs): `MCP_TRANSPORT=streamable-http uv run python main.py`, then `curl localhost:8000/health`.
  66. ## Verify
  67. ```bash
  68. uv run pytest tests/ -v
  69. uv run ruff check . && uv run ruff format --check .
  70. ```
  71. Tests mock the Databricks SDK; no workspace is needed.
  72. ## Add a tool
  73. One tool per file in `src/waterboards_mcp/tools/`, exposing `register(mcp)`; add it to `register_tools` in `tools/__init__.py`. Use `ZipCode`/`RadiusMiles`/`PaginationParams` from `models.py` for validated inputs and call `warehouse.query_gold(...)` with static `where`/`order_by` SQL and `Param`s for values. Document source, refresh cadence, and result shape in the docstring — the model reads it.
  74. ## Deploy
  75. See [deploy/databricks/README.md](deploy/databricks/README.md).
  76. ## Evaluate
  77. See [eval/README.md](eval/README.md) and the `mcp-eval` skill.
  78. ## License
  79. See [LICENSE](LICENSE).

Ratings and comments

Sign in to rate this skill.

Sign in

Free to use. If it’s helped, pass it on.