# sodabar 🥤

**An MCP server that puts open data on tap — four tools that let any LLM client search, inspect, and query 30,000+ civic datasets, with guardrails designed for a model on the other end.**

[![CI](https://github.com/UsmarHaider/sodabar/actions/workflows/ci.yml/badge.svg)](https://github.com/UsmarHaider/sodabar/actions/workflows/ci.yml)
[![Python](https://img.shields.io/badge/python-3.11%2B-blue)](https://www.python.org/)
[![MCP](https://img.shields.io/badge/MCP-2.0-8A2BE2)](https://modelcontextprotocol.io/)
[![FastAPI](https://img.shields.io/badge/FastAPI-playground-009688)](https://fastapi.tiangolo.com/)
[![License: MIT](https://img.shields.io/badge/license-MIT-green)](LICENSE)

**Live demo:** [usmar-sodabar.static.hf.space](https://usmar-sodabar.static.hf.space/) — the same console, answered in your browser straight from the live NYC Open Data API.

- [The problem — and why it matters](#the-problem--and-why-it-matters)
- [What an agent session looks like](#what-an-agent-session-looks-like)
- [The four tools](#the-four-tools)
- [The playground](#the-playground)
- [Key design decisions](#key-design-decisions)
- [Limitations](#limitations)
- [Reproduce it](#reproduce-it)
- [Project layout](#project-layout)

## The problem — and why it matters

Socrata powers the open-data portals of NYC, Chicago, Seattle, and hundreds of other governments — tens of thousands of live, queryable datasets. But an LLM can't use any of it directly: it doesn't know which datasets exist, what their columns are called, or how to write a [SoQL](https://dev.socrata.com/docs/queries/) query against them. And if you naively hand a model a raw HTTP tool, it will page 500,000 rows into its own context window or paste an HTML error page into its reasoning.

`sodabar` is a [Model Context Protocol](https://modelcontextprotocol.io/) server that closes that gap. It exposes the catalog → schema → query workflow as four typed tools, with the sharp edges filed off *server-side*: row caps a tool call cannot exceed, dataset-id validation that fails before a request leaves the machine, and upstream errors rewritten into messages a model can act on ("check the dataset id and domain") rather than tracebacks it will hallucinate around.

Point Claude Desktop, Claude Code, or any MCP client at it:

```json
{
  "mcpServers": {
    "sodabar": {
      "command": "/path/to/sodabar/.venv/bin/python",
      "args": ["-m", "sodabar.server"]
    }
  }
}
```

## What an agent session looks like

[`docs/agent-demo.md`](docs/agent-demo.md) is a committed transcript of **Gemini driving the server through the real MCP stdio transport**. Given only the four tools and the question *"Which NYC borough has logged the most 'Rodent' 311 complaints so far in 2026?"*, the model planned three calls on its own:

1. `search_datasets("311 Complaints")` → found `erm2-nwe9`
2. `get_schema("erm2-nwe9")` → learned `complaint_type`, `created_date`, `borough`
3. `query_dataset(select="borough, count(*)", where="complaint_type = 'Rodent' AND created_date BETWEEN …", group="borough", order="… DESC", limit=3)`

and answered: **Brooklyn 5,521 · Manhattan 3,528 · Queens 3,079**. No SoQL was written by a human at any point.

[`docs/demo-transcript.md`](docs/demo-transcript.md) is the scripted equivalent — every tool exercised over a real stdio subprocess session, regenerated with `make demo`.

## The four tools

| Tool | What it answers |
|---|---|
| `search_datasets(query, domain, limit)` | *"What datasets exist about X?"* — full-text catalog search |
| `get_schema(dataset_id, domain)` | *"What columns can I query, and what are their types?"* |
| `query_dataset(dataset_id, select, where, group, order, limit, offset, domain)` | SQL-shaped aggregation and filtering via SoQL |
| `profile_column(dataset_id, column, top)` | *"What values does this column take?"* — vocabulary before `where` clauses |

`domain` defaults to `data.cityofnewyork.us` but accepts any Socrata portal (`data.seattle.gov`, `data.cityofchicago.org`, …), so one server covers hundreds of cities.

## The playground

`make serve` starts a FastAPI app whose REST routes mirror the MCP tools one-to-one — the console shows the exact `tools/call` envelope and the exact result an LLM client would see:

![The sodabar console running a live aggregation: 311 complaints by borough in 2026, charted](docs/ui-query.png)

Guardrails are part of the demo. A malformed tool call gets a readable, actionable error — not a traceback:

![The same console fed an invalid dataset id, answering with a friendly validation error](docs/ui-guardrail.png)

The [live static deployment](https://usmar-sodabar.static.hf.space/) serves the identical HTML with a 4 KB fetch shim that answers the `/api/*` routes in-browser, straight from the Socrata APIs (which send `Access-Control-Allow-Origin: *`) — a zero-backend demo of a backend project.

## Key design decisions

- **Guardrails live server-side, not in the prompt.** `$limit` is clamped to 1,000 rows no matter what the model asks for; dataset ids must match Socrata's `xxxx-xxxx` form (which also blocks path traversal through the resource URL); domains must be bare hostnames. A prompt can be ignored — a clamp cannot.
- **Errors are written for the model that reads them.** A 404 becomes "not found — check the dataset id and domain"; a SoQL 400 surfaces Socrata's own message with "check your SoQL syntax". The retry policy distinguishes transient failures (429/5xx: three attempts with backoff) from semantic ones (400/404: fail immediately).
- **One client, three consumers.** The MCP server, the FastAPI playground, and the demo scripts share one `SocrataClient`, so timeout, retry, and error behavior can't drift between what's tested and what's deployed.
- **Tool descriptions teach the workflow.** The server's `instructions` and each tool's docstring steer a model toward `search → schema → query` and toward aggregating with `group` instead of paging raw rows — the difference between a 6-row answer and a 6,000-row context spill.
- **Tests mock the transport, not the code.** All 54 tests run against `httpx.MockTransport` — CI needs no network and finishes in under a second, while retry logic, error mapping, and the FastAPI lifespan wiring are exercised for real.

## Limitations

- SoQL clauses are passed through to Socrata after shape checks, not parsed — a syntactically valid but expensive query (e.g. `group` on a high-cardinality column) is bounded by the row cap and Socrata's own timeouts, nothing stricter.
- Catalog search relies on Socrata's relevance ranking, which favors title matches; an agent may need two searches with different phrasings.
- Anonymous (keyless) Socrata access is throttled upstream; sustained heavy use would need an app token, which the client doesn't currently send.
- The committed transcripts hit the live API, so re-running `make demo` will show current counts, not the committed ones.

## Reproduce it

```bash
git clone https://github.com/UsmarHaider/sodabar && cd sodabar
make venv        # python3 -m venv + editable install
make test        # 54 tests, no network needed
make demo        # scripted MCP stdio session → docs/demo-transcript.md (live API)
make serve       # playground at http://127.0.0.1:8012

cp .env.example .env   # then fill in GEMINI_API_KEY to run:
make agent-demo  # Gemini plans the tool calls → docs/agent-demo.md
```

## Project layout

```
sodabar/
├── sodabar/
│   ├── soql.py            # query validation: 4x4 ids, domain shape, row caps
│   ├── client.py          # shared Socrata HTTP client: retries, error translation
│   ├── server.py          # the MCP server (4 tools, stdio transport)
│   ├── service.py         # FastAPI playground mirroring the tools over REST
│   └── web/index.html     # self-contained console UI (no build step, no CDN)
├── scripts/
│   ├── demo_session.py    # scripted MCP client session → docs/demo-transcript.md
│   ├── agent_demo.py      # Gemini function-calling over the MCP session
│   ├── screenshot.sh      # headless-Chrome captures of the console
│   └── deploy_space.py    # builds + publishes the static HF Space demo
├── tests/                 # 54 tests, all offline (httpx.MockTransport)
└── docs/                  # committed transcripts + UI screenshots
```