Skip to main content
Glama
Waterboards MCP Server

Waterboards MCP Server

Waterboards MCP Server

An MCP 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 and uv; deployed as a Databricks App.

Design decisions and the review of the original plan: req/mcp_design_review.md (local, git-ignored).

Architecture

data.ca.gov drinking water files (+ Census ZCTA centroids)
   └─ sql/drinking_water/  Bronze → Silver → Gold   (run in a Databricks SQL warehouse)
        └─ users.suresh_kandru.v_* views  (the contract in this README)
             └─ Databricks SQL warehouse
                  └─ this server (FastMCP, Databricks Apps, /mcp over HTTP)
                       └─ MCP client / agent

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.

Related MCP server: mcp-arcgis-sacramento

Data pipeline

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:

Script

Layer

Output

sql/drinking_water/00_setup.sql

setup

volume, silver_clean_str helper

sql/drinking_water/01_bronze.sql

Bronze

raw all-string tables bronze_dw_*, bronze_zcta_centroids

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

sql/drinking_water/03_gold.sql

Gold

the v_* views below

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

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.

Tools

Tool

Gold view

Purpose

waterboards_list_datasets

information_schema.tables

What datasets are available

waterboards_find_water_systems_near_zip

v_water_systems_near_zip

Water systems near a ZIP, optionally only failing/at-risk/violating

waterboards_get_water_system

v_water_systems

Profile, SAFER status, violations for one pws_id

waterboards_get_source_chemistry_near_zip

v_source_chemistry_near_zip

Latest well chemistry near a ZIP, filter by chemical

waterboards_get_system_source_chemistry

v_source_chemistry_latest

Latest well chemistry for one pws_id

waterboards_get_school_lead_near_zip

v_school_lead_near_zip

School lead sampling near a ZIP

waterboards_get_tap_water_for_zip

v_tap_water_for_zip

Latest tap/treated-water sample results for the systems serving a ZIP

All return {results, count, offset, has_more, next_offset, source, retrieved_at}, or {error, hint}. Empty results include an actionable hint.

Gold view contract

The views must exist with at least these columns (the server does SELECT * then filters/orders on them):

View

Required columns

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

v_water_systems_near_zip

zip, distance_km + all v_water_systems columns

v_source_chemistry_latest

pws_id, well_id, chemical_name, is_non_detect, result_numeric, units, reporting_limit, last_observed, source_url

v_source_chemistry_near_zip

zip, distance_km + all v_source_chemistry_latest columns

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

v_school_lead_near_zip

zip, distance_km, school_name, action_level_exceeded, result_numeric, last_observed, source_url + school/system columns

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.

Run locally

cp .env.example .env          # set DATABRICKS_SQL_WAREHOUSE_ID
databricks auth login --host https://<workspace>.cloud.databricks.com
uv sync --group dev
uv run python main.py         # stdio

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:

{
  "mcpServers": {
    "waterboards": {
      "command": "uv",
      "args": ["run", "waterboards-mcp"],
      "cwd": "/absolute/path/to/this/repo"
    }
  }
}

HTTP mode (what Databricks Apps runs): MCP_TRANSPORT=streamable-http uv run python main.py, then curl localhost:8000/health.

Verify

uv run pytest tests/ -v
uv run ruff check . && uv run ruff format --check .

Tests mock the Databricks SDK; no workspace is needed.

Add a tool

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 Params for values. Document source, refresh cadence, and result shape in the docstring — the model reads it.

Deploy

See deploy/databricks/README.md.

Evaluate

See eval/README.md and the mcp-eval skill.

License

See LICENSE.

Available Tools

7 tools
waterboards_find_water_systems_near_zipFind public water systems near a ZIP codeA
Read-onlyIdempotent

Find drinking water systems serving the area near a ZIP code.

Source: Gold view v_water_systems_near_zip (California State Water Board SDWIS inventory + SAFER risk assessment). Refreshed when the pipeline reloads the open data; each result has data_as_of. Only systems with coordinates in the SAFER assessment can be located. Distance is from the ZIP centroid to the system's location, so a nearby system does not necessarily serve that ZIP. Ordered nearest first.

Set flagged_only to return only systems that are Failing, At-Risk, or have an MCL, E. coli, or treatment-technique violation.

Each result has pws_id, system_name, county, city, federal_type, total_population, distance_km, safer_status, is_failing, violation flags/analytes, risk levels, and source_url. Use pws_id with waterboards_get_water_system for the full profile. SAFER status is a regulatory screening, not a tap-water safety determination.

Inputs: zip_code (required), pagination, flagged_only, radius_miles. Returns: object

waterboards_get_school_lead_near_zipGet school drinking water lead results near a ZIP codeA
Read-onlyIdempotent

Get lead-in-drinking-water results at K-12 schools near a ZIP code.

Source: Gold view v_school_lead_near_zip (California State Water Board school lead sampling, 2020 onward; the data on file stops at the newest last_observed, so say so when results are old). Distance is between ZIP centroids. Ordered nearest first, then most recent.

exceedances_only returns only samples the state flagged as above the lead action level.

Each result has school_name, district, school_zip, sample_location, result_numeric and units (UG/L), reporting_limit, is_non_detect (result_numeric is null when true), action_level_exceeded, follow_up_action, last_observed (sample date), pws_id, and source_url. These are point-in-time samples, not a health determination.

Inputs: zip_code (required), pagination, radius_miles, exceedances_only. Returns: object

waterboards_get_source_chemistry_near_zipGet drinking water source chemistry near a ZIP codeA
Read-onlyIdempotent

Get latest source-water chemistry for wells near a ZIP code.

Source: Gold view v_source_chemistry_near_zip (DDW/GAMA drinking water source chemistry, 2020 to present). Refreshed when the pipeline reloads the open data; each result has last_observed. Ordered nearest well first, then most recent.

chemical is a case-insensitive name fragment (e.g. 'arsenic', 'nitrate', 'PFOA'); omit it to see every chemical, which is large. Set detected_only to skip non-detects.

Each result is the latest sample for one well and chemical: pws_id, system_name, well_id, chemical_name, result_numeric, units, reporting_limit, is_non_detect, last_observed (sample date), sample_count, and source_url. When is_non_detect is true, result_numeric is null and the chemical was not found above reporting_limit. This is raw source water before treatment, not tap water, and not a health determination; units vary by chemical (UG/L, NG/L, MG/L, ...) so never compare across units.

Inputs: zip_code (required), chemical, pagination, radius_miles, +1 more. Returns: object

waterboards_get_system_source_chemistryGet source chemistry for a water systemA
Read-onlyIdempotent

Get latest source-water chemistry for one water system's wells.

Source: Gold view v_source_chemistry_latest (DDW/GAMA drinking water source chemistry, 2020 to present). Refreshed when the pipeline reloads the open data; each result has last_observed. Ordered by chemical, then well. Get pws_id from waterboards_find_water_systems_near_zip.

chemical is a case-insensitive name fragment; detected_only skips non-detects.

Each result is the latest sample for one well and chemical: pws_id, system_name, well_id, chemical_name, result_numeric, units, reporting_limit, is_non_detect, last_observed (sample date), sample_count, and source_url. When is_non_detect is true, result_numeric is null and the chemical was not found above reporting_limit. This is raw source water before treatment, not tap water, and not a health determination; units vary by chemical (UG/L, NG/L, MG/L, ...) so never compare across units.

Inputs: pws_id (required), chemical, pagination, detected_only. Returns: object

waterboards_get_tap_water_for_zipGet tap water sample results for a California ZIP codeA
Read-onlyIdempotent

Get the latest tap-water test results for a California ZIP code.

Source: Gold view v_tap_water_for_zip (DDW SDWIS sample results, 2023 to present, joined to water-system service-area boundaries). Refreshed when the pipeline reloads the open data; each result has last_observed. Systems are ordered by how much of the ZIP they serve (pct_of_zip_covered, highest first); a ZIP can be served by several systems, so use the largest coverage as the likely supplier.

stage is 'distribution' (samples from the pipes that reach customers, the closest to tap water; default) or 'treated' (leaving the treatment plant). chemical is a case-insensitive name fragment (e.g. 'lead', 'arsenic', 'pfas', 'nitrate'). detected_only (default true) hides non-detects; exceeds_mcl_only shows only results above the legal limit. min_coverage_pct (default 10) hides systems covering less of the ZIP than that; lower it to 0 to see every overlapping system.

Each result: zip, pct_of_zip_covered, pws_id, system_name, total_population, water_stage, analyte_name, result_numeric, units, is_non_detect, reporting_limit, mcl, exceeds_mcl, any_exceeds_mcl, max_result_numeric, sample_count, last_observed, sampling_point, method, source_url. When is_non_detect is true, result_numeric is null and the analyte was below reporting_limit.

These are system-level samples, not results for an individual address or home. A single sample above the MCL is not a violation by itself (many limits apply to running averages), and this is not a health determination. Units vary by analyte; never compare across units.

Inputs: zip_code (required), stage, chemical, pagination, +3 more. Returns: object

waterboards_get_water_systemGet a public water system profileA
Read-onlyIdempotent

Get the profile and compliance/risk summary of one water system.

Source: Gold view v_water_systems (California State Water Board SDWIS inventory + SAFER risk assessment). Refreshed when the pipeline reloads the open data; the result carries data_as_of.

The single result has system name, county, city, type, owner, source type, population, service connections, last sanitary survey, safer_status, is_failing, economic_status (DAC/SDAC), violation flags with analytes (primary/secondary MCL, E. coli, treatment technique, monitoring), risk levels by category, and source_url. has_risk_assessment is false when the system is not in the SAFER file, in which case the violation/risk fields are null (unknown, not "none"). This is a regulatory screening, not a tap-water safety determination.

Inputs: pws_id (required). Returns: object

waterboards_list_datasetsList waterboard datasetsA
Read-onlyIdempotent

List the water datasets (Gold views) available through this server.

Source: Unity Catalog information_schema for the configured Gold schema. Refresh cadence: reflects the current catalog; underlying data refresh varies by dataset (see each view's comment).

Returns a pagination envelope; each result has table_name and table_comment.

Inputs: pagination. Returns: object

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelog…

    TDQS

    A4.2/5.0

    Scored across 7 tools

    Disambiguation4/5

    Each tool maps to a distinct dataset/query axis: catalog listing, systems near a ZIP, single-system profile, source chemistry by ZIP vs by system, school lead, and tap water. The only near-pair is get_source_chemistry_near_zip vs get_system_source_chemistry, which descriptions do separate by lookup key (ZIP vs pws_id). Source vs tap water vs school lead are clearly delineated in the descriptions.

    Naming Consistency4/5

    All names use the same waterboards_ prefix and snake_case verb_noun form, which is highly predictable. Minor inconsistency: 'get_source_chemistry_near_zip' places the qualifier at the end while its sibling is 'get_system_source_chemistry', so the pair reads less symmetrically than the rest.

    Tool Count5/5

    Seven tools is well-scoped for a read-only public-data server; each tool corresponds to one backing Gold view or catalog operation. No redundant or padding tools are present.

    Completeness4/5

    The surface covers discovery, system lookup, source chemistry (two access paths), school lead, and tap water, which is a strong read-only lifecycle for the domain. Gaps are minor: no name/county search for a water system, no way to resolve a ZIP's serving systems beyond distance-based proximity, and no violation-detail listing beyond flags in the system profile.

    Maintenance

    ActivityMaintained
    ResponsivenessNo recent community issues

    Related MCP Connectors

    Related MCP Servers

    • A
      license
      A
      quality
      C
      maintenance
      Enables querying USGS water data including real-time and historical streamflow, gage height, and water temperature from USGS gauges across the United States.
      3
      MIT
    • A
      license
      Not graded
      quality
      C
      maintenance
      Enables querying and exploring City of Sacramento GIS open geospatial datasets (parcels, zoning, public works) via ArcGIS Feature Services.
      94 npm
      MIT
    • A
      license
      Not graded
      quality
      C
      maintenance
      Enables search and retrieval of calibrated SWAT+ watershed models, national groundwater lithology and PFAS inventories, plus API-key-secured ordering and downloading of custom watershed models.
      1
      MIT
    • A
      license
      Not graded
      quality
      C
      maintenance
      Enables querying and retrieving US water-chemistry data, including station metadata, sample results, and controlled vocabularies from the Water Quality Portal.
      75 npm
      MIT