# cms-mcp

A read-only MCP server over the DuckDB star schema built by
[cms-inpatient-warehouse](https://github.com/abhaymettu/cms-inpatient-warehouse). It gives an
agent seven typed tools for Medicare inpatient DRG data, plus one guarded SQL tool for anything
the typed tools do not cover.

## Read this first: the data covers 71.7% of national volume

CMS suppresses any hospital-DRG cell with fewer than 11 discharges, so the fact table does not
sum to the published provider-level totals. For data year 2024, CMS published 6,904,504
discharges nationally; the mart holds 4,952,481, about 71.7%. Every number this server returns is
an estimate over published cells. Small hospitals and rare DRGs are underrepresented, and no
reweighting fixes this, because the suppressed values are not published at any grain.

## Setup

1. Build the warehouse:

```bash
git clone https://github.com/abhaymettu/cms-inpatient-warehouse
cd cms-inpatient-warehouse
./run_all.sh
```

This produces `warehouse.duckdb`. Per its README, the run takes about 7 seconds after an
84 MB one-time download.

2. Install this server with uv:

```bash
uv venv
uv pip install -e .
```

or, without cloning this repo:

```bash
uv pip install git+https://github.com/abhaymettu/cms-mcp
```

3. Run it, pointed at the warehouse file:

```bash
CMS_MCP_DB=/path/to/warehouse.duckdb cms-mcp
```

This speaks MCP over stdio. `python -m cms_mcp` is equivalent to the `cms-mcp` binary.

## Client configuration

**Claude Desktop, Cursor, or any generic stdio client** (`claude_desktop_config.json`):

```json
{
  "mcpServers": {
    "cms-mcp": {
      "command": "/path/to/cms-mcp/.venv/bin/cms-mcp",
      "env": {
        "CMS_MCP_DB": "/path/to/warehouse.duckdb"
      }
    }
  }
}
```

**Claude Code**:

```bash
claude mcp add cms-mcp -e CMS_MCP_DB=/path/to/warehouse.duckdb -- /path/to/.venv/bin/cms-mcp
```

**MCP Inspector**, to list tools without a client:

```bash
npx @modelcontextprotocol/inspector --cli .venv/bin/cms-mcp -e CMS_MCP_DB=/path/to/warehouse.duckdb --method tools/list
```

See `docs/client-configs.md` for these plus a `tools/call` example, and `docs/smoke-test.md` for a
captured Inspector session against the real warehouse: every tool called once, plus the
rejection cases.

## Tools

| Tool | Arguments | Returns | Purpose |
|---|---|---|---|
| `list_tables` | none | list of table summaries | List the four exposed marts tables with grain and role |
| `describe_table` | `table` | columns, row count, notes, sample rows | Show a table's schema and how to use it correctly |
| `query` | `sql`, `max_rows` | columns, rows, truncated flag, elapsed time | Run a single guarded SELECT against the marts schema |
| `search_hospitals` | `name`, `state`, `city`, `ccn`, `limit` | list of hospitals | Find hospitals by name, location, or CCN |
| `search_drgs` | `text`, `limit` | list of DRGs | Find MS-DRGs by description or code |
| `hospital_profile` | `ccn`, `data_year` | totals and top DRGs for one hospital | Summarize one hospital's discharges, payments, and case mix |
| `drg_summary` | `drg_code`, `data_year`, `state` | national or state totals and top hospitals for one DRG | Summarize one DRG nationally or in one state |

### Schema

| Table | Grain |
|---|---|
| `marts.fct_inpatient_drg` | one row per (CCN, DRG code, data year) |
| `marts.dim_provider` | one row per hospital per version (Type 2 SCD) |
| `marts.dim_drg` | one row per MS-DRG code |
| `marts.dim_geography` | one row per state, ZIP, RUCA combination |

Two rules an agent must follow:

- Join facts to `dim_provider` on `provider_sk`, not `ccn`. `dim_provider` is a Type 2 dimension,
  so a `ccn` can match more than one row.
- `avg_*` columns are per discharge and not additive. Sum `ext_*` (extended amount, already
  multiplied by discharges), then divide, rather than averaging `avg_*` across rows.

## Guardrails

| Guardrail | Mechanism | Env var |
|---|---|---|
| Read-only file handle | DuckDB connection opened with `read_only=True` | none |
| No external access | `enable_external_access=false` and `lock_configuration=true`, so `read_csv`, `ATTACH`, and `INSTALL` fail and settings cannot be re-enabled by a query | none |
| Single-statement SELECT only | Parsed with DuckDB's own `extract_statements`; anything but exactly one `SELECT`-type statement is rejected | none |
| Row cap | Result wrapped in an outer `LIMIT`; default 500, hard ceiling 5000 regardless of what is requested, `truncated` flag set when the cap is hit | `CMS_MCP_MAX_ROWS` |
| Per-statement timeout | A timer thread calls `cursor.interrupt()` after the limit | `CMS_MCP_TIMEOUT_S` (default 10) |
| Rate limit | Token bucket shared across all tools, per server process | `CMS_MCP_RATE_PER_MIN` (default 60) |
| SQL length cap | Query text rejected past this length | `CMS_MCP_MAX_SQL_CHARS` (default 8000) |
| Memory and CPU ceiling | `memory_limit='1GB'`, `threads=2` | none |
| Warehouse file path | required to start the server | `CMS_MCP_DB` |

## Failure modes

- **Suppression bias.** Covered above. It affects every tool, not just `query`.
- **A timed-out query returns an error, but the process keeps running.** Narrow a slow analytic
  join rather than retrying it as-is.
- **The rate limit is per server process, not per client.** Two clients sharing one process share
  the same token bucket.
- **stdio only, no auth.** This is a local tool for one trusted process to talk to, not a network
  service.
- **If the warehouse file is rebuilt while the server holds it open, the server keeps serving the
  old file until restarted.**
- **Summing `avg_*` columns silently gives wrong rollups.** They are per discharge, not additive.
  Use `ext_*` for anything that needs to be summed.
- **Joining `dim_provider` on `ccn` fans out facts by version count**, because `dim_provider` is
  Type 2. Join on `provider_sk` instead.
- **A query that hits the 1GB memory limit fails with a DuckDB out-of-memory message.**
- **`PRAGMA` statements do not work inside the row-cap wrapper**, because the wrapper selects from
  the query as a subquery.
- **The real-data tests are skipped in CI**, because the warehouse file is not committed. CI
  proves the guardrails and tool contracts against a small fixture, not the actual numbers.
- **`describe_table` and `query` cannot see the `staging` or `intermediate` schemas, by design.**
  Only `marts` is exposed.

## Development

```bash
uv pip install -e ".[dev]"
pytest -q
ruff check .
```

`tests/test_real_warehouse.py` runs only when `CMS_MCP_DB` points at a real warehouse file;
otherwise it is skipped. CI runs on the small fixture built in `tests/conftest.py`.

## Licence

MIT.