Charlotte Food Grades

Interactive map of food-service inspection grades across Charlotte / Mecklenburg County, NC, backed by scraped public inspection data, plus a live MCP server over the same dataset.

• Map: deployed on Vercel (Git integration; project root directory = site/)
• Data source: Mecklenburg County Environmental Health via the state's public CDPEHS lookup (see docs/DATA-SOURCE.md for the CDPEHS-vs-EHIDS decision and the reverse-engineered data paths)
• Spec: docs/SPEC.md

How it works

CDPEHS (public.cdpehs.com, county 60)
   │  CSV export (date-chunked)  +  grid walk → violation pages
   ▼
pipeline/  ──►  data/db.duckdb  ──►  site/data/facilities.geojson ──► Vercel (MapLibre site)
   ▲                    │
   │                    └──► GitHub release asset `data-latest` ──► MCP server (Fly.io)
   └── weekly GitHub Action

• Weekly refresh (.github/workflows/refresh.yml): downloads the previous DuckDB from the data-latest release, grid-walks the last ~35 days of inspections (learning internal CDP ids and fetching violation details), geocodes new facilities via the Census Bureau batch geocoder, exports GeoJSON, republishes the DB, and commits the refreshed site/data/ — which triggers a Vercel redeploy.
• History is kept from ~2014 (earliest data in CDPEHS for Mecklenburg).

Pipeline

python3.12 -m venv .venv && .venv/bin/pip install -r requirements.txt
.venv/bin/python -m pipeline.ingest backfill --start 2014-01-01   # one-time seed
.venv/bin/python -m pipeline.ingest update --days 35              # incremental + violations
.venv/bin/python -m pipeline.ingest geocode                       # Census batch geocoder
.venv/bin/python -m pipeline.ingest export                        # site/data/*.geojson
.venv/bin/python -m pipeline.ingest weekly                        # update+geocode+export

Each source window is fetched independently — one failed window doesn't kill a run. Every record carries fetched_at; the site shows per-facility provenance.

Map site

Static, no backend: MapLibre GL JS over OpenFreeMap vector tiles, grade-colored clustered points (green A / yellow B / red C-or-below / gray ungraded), a detail panel with score sparkline, latest violations, and a link to the official county record. Filters: facility type, grade, name/address search, recently-inspected. Serve locally with any static server:

python3 -m http.server -d site 8000

MCP server

Remote MCP server (streamable HTTP at /mcp) over the same DuckDB file — read-only, no auth, IP rate-limited. Tools: search_establishments, get_establishment, nearby_establishments, score_distribution.

cd mcp-server
pip install -r requirements.txt
DB_PATH=../data/db.duckdb python server.py       # local
fly launch --no-deploy && fly deploy             # Fly.io; set DB_URL secret first

The deployed server boots by downloading the DuckDB file from the data-latest GitHub release and re-checks every 24 h.

Disclaimer

This is an unofficial visualization of public Mecklenburg County inspection records, refreshed weekly. A facility's grade can change between refreshes; the county lookup is the authoritative record. Inspection type and permit status are not exposed by the public county system and are omitted.

Resolved decisions (2026-07-05)

1. CDPEHS vs. EHIDS — CDPEHS; EHIDS's API requires state CAS login (details).
2. Facility types in v1 — full Food & Facilities Sanitation scope, filterable on the map.
3. Phase 2 civic layers — deferred (Charlotte Code Enforcement, Quality of Life Explorer).
4. MCP hosting — Fly.io.
5. MCP access — open read-only + rate limiting.