@cyanheads/socrata-mcp-server
Search and query government open-data portals (Socrata SODA API) via MCP. STDIO or Streamable HTTP.
7 Tools • 2 Data Resources • 1 Prompt • 1 Skill
[](./CHANGELOG.md) [](./LICENSE) [](https://github.com/users/cyanheads/packages/container/package/socrata-mcp-server) [](https://modelcontextprotocol.io/) [](https://www.npmjs.com/package/@cyanheads/socrata-mcp-server) [](https://www.typescriptlang.org/) [](https://bun.sh/)
[](https://github.com/cyanheads/socrata-mcp-server/releases/latest/download/socrata-mcp-server.mcpb) [](https://cursor.com/en/install-mcp?name=socrata-mcp-server&config=eyJjb21tYW5kIjoibnB4IiwiYXJncyI6WyIteSIsIkBjeWFuaGVhZHMvc29jcmF0YS1tY3Atc2VydmVyIl19) [](https://vscode.dev/redirect?url=vscode:mcp/install?%7B%22name%22%3A%22socrata-mcp-server%22%2C%22command%22%3A%22npx%22%2C%22args%22%3A%5B%22-y%22%2C%22%40cyanheads%2Fsocrata-mcp-server%22%5D%7D)
[](https://www.npmjs.com/package/@cyanheads/mcp-ts-core)
**Public Hosted Server:** [https://socrata.caseyjhand.com/mcp](https://socrata.caseyjhand.com/mcp)
---
## Overview
Query city, county, state, and federal open-data portals built on Socrata. Discover portals and datasets, inspect column schemas, and run SoQL queries, or SQL over large result sets.
### Tools
| Tool | Description |
|:---|:---|
| `socrata_list_portals` | List known Socrata-powered government open-data portals with domain, organization name, and approximate dataset count |
| `socrata_find_datasets` | Search for datasets across all Socrata portals or scope to one portal via the Discovery API |
| `socrata_get_dataset` | Fetch full metadata and typed column schema for a dataset by ID — required before writing SoQL queries |
| `socrata_query_dataset` | Execute a SoQL query against any dataset: search, select, where, group, having, order, with DataCanvas spillover |
| `socrata_dataframe_describe` | List registered tables in a DataCanvas session — schema, row count, column names |
| `socrata_dataframe_query` | Run SELECT-only SQL against DataCanvas tables populated by `socrata_query_dataset` |
| `socrata_dataframe_drop` | Drop a DataCanvas canvas, or one table on it — opt-in via `SOCRATA_DATAFRAME_DROP_ENABLED=true` |
### Resources
| Resource | Description |
|:---|:---|
| `socrata://datasets/{domain}/{datasetId}` | Fetch full metadata and column schema for a dataset by stable URI — same payload as `socrata_get_dataset` |
| `socrata://portals` | Paginated list of known Socrata portals with organization name and approximate dataset count |
All resource data is also reachable via tools. Use the corresponding tool for agent workflows — resources are for clients that support URI-addressable data.
### Prompts
| Prompt | Description |
|:---|:---|
| `explore_open_data` | Structured six-step civic data investigation workflow: find portal → discover datasets → inspect schema → query → aggregate → synthesize |
### Agent skill
[`socrata-guide`](skills/socrata-guide/SKILL.md) covers dataset coverage checks, SoQL and DuckDB query syntax, and comparisons across staged tables. It ships with plugin installs, npm, Docker, and the MCPB bundle, and is available over MCP as `skill://socrata-guide/SKILL.md`.
## Capability reference
### `socrata_list_portals` tool
- Curated catalog of 39 well-known city, county, state, and federal portals — every member verified live in the Discovery catalog
- Per-portal dataset counts fetched live from the Discovery API, cached ~24 hours (`0` means the portal exposes no dataset assets to the catalog; `null` means the count is temporarily unavailable)
- Client-side substring filtering on domain or organization name; pagination up to 200 per page with offset
- Returns domain (pass to `socrata_find_datasets`), organization name, and approximate dataset count; the count includes datasets a portal federates from another Socrata tenant (Austin, Illinois, Mesa, and San Francisco publish through a data hub; Seattle catalogs under a sibling tenant)
- Never fails on an upstream error — a count that cannot be fetched, or that the call's 50-second deadline cuts off, keeps its last known value, or comes back `null` when it has none, and the listing still returns
- Past-the-end notice: an empty page at `offset > 0` names the offset to page below
---
### `socrata_find_datasets` tool
- Full-text query across dataset names/descriptions; scope with `domain`, filter by `categories`/`tags` (several values of one filter are ORed), restrict `only` to an asset type (datasets, maps, files, calendars, stories)
- Sort by relevance, page views, created date, or updated date; `limit` up to 10,000 with `SOCRATA_CLAMP_LIMITS` on, served 100 per call with a notice naming the offset that continues (the schema maximum is 100 with it off)
- Discovery serves only the first 10,000 matches of a search: `offset` must stay below 10,000, and `totalCount` stops at 10,000 with `totalCountCapped: true` marking that figure a lower bound
- Returns dataset IDs, names, domains, tags, update timestamps, and `column_names` — the API field names SoQL takes (`cuisine_description`, not the display label `CUISINE DESCRIPTION`), computed-region columns dropped — call `socrata_get_dataset` for typed schema before writing queries
- Recovery hints on empty results — echoes applied filters and suggests how to broaden; a page past the last match says so and names the offset to page below. The notice also covers a `limit` served below the one requested
- `domain` takes a bare hostname; URL forms (`https://data.cdc.gov/`) are reduced to the host. A scoped search covers every dataset the portal publishes, including ones federated from another Socrata tenant, reported under the portal's own domain
- Typed errors: `result_window_exceeded` when `offset` is 10,000 or more, `rate_limited` (retryable) when the Discovery API returns 429, `retry_deadline_exceeded` (retryable) when the call passes its 50-second deadline, `unknown_domain` when the Discovery catalog does not index the domain, `invalid_domain` when the domain is not a hostname
---
### `socrata_get_dataset` tool
- Returns field names, Socrata data types, descriptions, row count (with `row_count_source` provenance), and licensing when available
- Column `data_type` determines WHERE clause syntax: `Number` → bare literals (`year=2023`), `Text` → single-quoted strings (`year='2023'`)
- Excludes computed region columns (`:@computed_region_*`) to reduce noise; includes per-column non-null counts when available
- Dataset IDs are portal-scoped — pass the `domain` from the same `socrata_find_datasets` result; URL-form domains are reduced to the host
- Typed errors: `invalid_id` (malformed four-by-four ID), `not_found` (no such dataset on the domain queried — the message names the ID and domain, and the recovery names the portal that holds the ID when the Discovery catalog knows it), `unknown_domain` (the domain is not serving the Socrata API — it does not resolve, is not a Socrata portal, or redirects elsewhere; fails on the first attempt), `invalid_domain` (not a hostname), `rate_limited` (retryable; honors the upstream `Retry-After`), `retry_deadline_exceeded` (retryable; the call passed its 50-second deadline)
- Always call before writing a `socrata_query_dataset` WHERE clause
---
### `socrata_query_dataset` tool
- `search` for quick full-text lookup (`$q`), or combine `select`/`where`/`group`/`having`/`order` for full analytical control — clauses reference columns by API field name (`field_name` from `socrata_get_dataset`), never the display label; operators `=`, `!=`, `>`, `<`, `LIKE`, `IN(...)`, `BETWEEN`, `IS NULL`, `starts_with()`, `contains()`, `AND`, `OR`, `NOT`
- Aggregation via `count(*)`, `sum()`, `avg()`, `min()`, `max()` with `group`/`having`
- `not_tabular` identifies assets without queryable rows; search with `only: "datasets"`. `query_rejected` identifies a gateway refusal of query clauses when a bare row request succeeds; simplify the query and use SoQL `case(condition, value, true, fallback)` for conditional aggregates.
- `limit` up to 50,000 with `SOCRATA_CLAMP_LIMITS` on, but one call returns at most 5,000 rows: a larger `limit` is served as 5,000, with a notice naming the offset that continues when more rows match (off, the schema maximum is 5,000). Page with `offset`
- `total_count` counts the query as written — groups for a grouped query, distinct values for a distinct select — through a bounded SoQL `$query` pipe recount. It is present when the match holds more rows than the response, and absent when the response holds the whole match or when the recount failed or ran past 15 seconds. `truncated` is set only when `total_count` exceeds `offset + row_count`, or when the page fills `limit` and no count exists
- An empty page at `offset > 0` gets a past-the-end notice naming the match size, or a may-be-past-the-end notice when the count failed
- `assembled_query` echoes the SoQL string for learning the syntax; all SODA 2.1 row values are strings except geo/location columns, which return nested objects
- When the server has a canvas and more rows match past the page, up to 50,000 matching rows spill to a DataCanvas table whatever the `limit` (`canvas_id`, `table_name`, `canvas_row_count`) — list its columns with `socrata_dataframe_describe`, then run SQL with `socrata_dataframe_query`. A small `limit` (e.g. 10) stages a large match without a large inline page. Staging is one SODA request; if it fails or runs out of time the call still returns its inline rows, with no `canvas_id` and a notice saying nothing was staged
- `table_name` names the staged table (default derived from `dataset_id`, e.g. `ijzp_q8t2_rows`). Spilling under a name the canvas already holds replaces that table, and the notice reports the replacement; pass a distinct `table_name` with the same `canvas_id` to keep several filtered extracts of one dataset side by side. When a named call stages nothing because the whole match fits the page, or the server has no canvas, the notice says so; a `limit` below the page's row count stages a match that fits
- Every request of a call shares one 50-second deadline, retries and backoffs included
- Typed errors: `not_tabular`, `query_rejected`, `invalid_id`, `not_found` (names the ID, the domain queried, and the portal holding the ID when known), `unknown_domain`, `invalid_domain`, `soql_error` (bad SoQL, unknown column, or type mismatch — carries the upstream `socrataCode` and, when upstream names it, the offending `column`; the recovery hint matches the code: API field names for a parse error, both fixes for an unknown identifier, the quoting rule for a type mismatch), `rate_limited` (retryable; honors the upstream `Retry-After`), `retry_deadline_exceeded` (retryable; the call passed its 50-second deadline)
---
### `socrata_dataframe_describe` tool
- Requires `canvas_id` from a prior `socrata_query_dataset` spill — canvases cannot be enumerated, so omitting it fails with `canvas_id_required` rather than listing tables
- Shows table name, row count, and DuckDB column types for each registered table (SODA `number` → `DOUBLE`)
- Typed errors: `canvas_id_required`, `canvas_not_found` (expired or unknown token — re-run `socrata_query_dataset` to stage a fresh canvas)
---
### `socrata_dataframe_query` tool
- SELECT-only SQL against a `canvas_id` table staged by `socrata_query_dataset`; DDL, DML, file references, file-reading functions, and engine-setting functions (`current_schema`, `current_setting`) are rejected. Use direct table references instead of `query_table`; `query()` accepts only a single SQL string literal. Qualify a staged column named `current_schema` with its table (`t.current_schema`).
- Spilled columns are typed from the SODA response headers: `number` columns (aggregate aliases included) are `DOUBLE`, so numeric comparisons work without a cast (`year > 2020`, `amount < 500`); text and timestamp columns stay `VARCHAR` — compare times with `CAST(date AS TIMESTAMP)`
- `limit` up to 50,000 with `SOCRATA_CLAMP_LIMITS` on (default 1000), but one call returns at most 10,000 rows: a larger `limit` is served as 10,000. When more rows match, the notice gives the paging recipe — `ORDER BY` a unique column with `LIMIT`/`OFFSET` in the SQL. With the switch off, the schema maximum is 10,000
- `content[]` shows the `**SQL:**` echo as a code span, or as a fenced `sql` block when the SQL spans lines; `structuredContent` carries it unchanged
- Typed errors: `canvas_disabled` (`CANVAS_PROVIDER_TYPE=none`), `canvas_not_found`, `table_not_found`, `sql_rejected` (non-SELECT, system catalog access, or a denied function)
- Works out of the box — DuckDB ships as a regular dependency and is enabled by default; `CANVAS_PROVIDER_TYPE=none` disables it
---
### `socrata_dataframe_drop` tool
- Opt-in: omitted from the tool list until `SOCRATA_DATAFRAME_DROP_ENABLED=true`; also needs `CANVAS_PROVIDER_TYPE=duckdb`
- Without `table_name`, drops the whole canvas — its `canvas_id` stops resolving for `socrata_dataframe_describe` and `socrata_dataframe_query`. With `table_name`, drops that one table and leaves the canvas and its other tables in place
- Reports the dropped tables with their row counts and the tables still on the canvas
- Deletes only the staged copy — `socrata_query_dataset` can stage the data again
- Typed errors: `canvas_disabled` (`CANVAS_PROVIDER_TYPE=none`), `canvas_not_found` (unknown, expired, or already dropped), `table_not_found` (carries the canvas's `availableTables`)
---
### `socrata://datasets/{domain}/{datasetId}` resource
- Returns the same payload as `socrata_get_dataset` — field names, data types, descriptions, row count, licensing
- `domain` and `datasetId` come from `socrata_find_datasets`; `datasetId` must match the four-by-four pattern (e.g. `kzjm-xkqj`)
- Fails validation on a malformed ID, and not-found when the dataset doesn't exist on the domain (naming the portal that holds the ID when the Discovery catalog knows it)
---
### `socrata://portals` resource
- Curated catalog of 39 known Socrata portals, cursor-paginated (`cursor` param, default 50 per page, capped at 200)
- Returns domain, organization name, and approximate dataset count (`0` = no dataset assets, `null` = temporarily unavailable; a count the 50-second call deadline cuts off keeps its last known value, or is `null` when it has none), cached ~24 hours
- Pass `domain` to `socrata_find_datasets` to scope a search to one portal
---
### `explore_open_data` prompt
- Arguments: `topic` required; `portal` and `geography` optional to skip discovery or scope WHERE clauses
- Returns one user message walking a six-step workflow: find portal → discover datasets → inspect schema → query → aggregate → synthesize
## Features
Built on [`@cyanheads/mcp-ts-core`](https://github.com/cyanheads/mcp-ts-core): stdio and Streamable HTTP transports, pluggable auth (`none` / `jwt` / `oauth`), swappable storage (`in-memory`, `filesystem`, `Supabase`, `Cloudflare KV/R2/D1`), structured logging with optional OpenTelemetry tracing.
Socrata-specific:
- Full Socrata SODA 2.1 API integration — SoQL query builder with select, where, group, having, order, search, limit, offset
- Discovery API for cross-portal dataset search and per-portal dataset counts (curated 39-portal catalog, counts cached ~24h)
- App token support (`SOCRATA_APP_TOKEN`) for higher per-IP rate limits
- Configurable default portal domain via `SOCRATA_DEFAULT_DOMAIN`
- DataCanvas spillover (DuckDB, bundled) — large query results register as SQL tables for analytical queries
Agent-friendly output:
- Assembled SoQL string echoed in every `socrata_query_dataset` response so agents can learn and refine syntax
- Recovery hints on empty results — echoes applied filters with specific suggestions for broadening
- Truncation disclosure — `truncated`/`shown`/`cap` fields when more rows match past the page, with guidance to page or raise the limit, naming the staged table and both dataframe tools when the result spilled
- Typed error reasons across every tool (`invalid_id`, `not_found`, `not_tabular`, `query_rejected`, `unknown_domain`, `invalid_domain`, `soql_error`, `rate_limited`, `retry_deadline_exceeded`, `result_window_exceeded`, `canvas_id_required`, `canvas_not_found`, `table_not_found`, `sql_rejected`, `canvas_disabled`) with actionable recovery text
## Getting started
Connect to the hosted instance, no install:
```sh
claude mcp add --transport http socrata-mcp-server https://socrata.caseyjhand.com/mcp
```
Or run it locally:
```sh
claude mcp add socrata-mcp-server -- npx -y @cyanheads/socrata-mcp-server
```
For other clients, add this to the MCP config file:
```json
{
"mcpServers": {
"socrata-mcp-server": {
"command": "npx",
"args": ["-y", "@cyanheads/socrata-mcp-server"]
}
}
}
```
An optional `SOCRATA_APP_TOKEN` from a [Socrata portal](https://data.seattle.gov) raises rate limits. For Docker or a source build, see [Running the server](#running-the-server).
## Configuration
All configuration is validated at startup via Zod schemas in `src/config/server-config.ts`. Key environment variables:
| Variable | Description | Default |
|:---|:---|:---|
| `SOCRATA_APP_TOKEN` | Socrata app token (X-App-Token header). Without a token, requests share a throttled pool per source IP. | — |
| `SOCRATA_DEFAULT_DOMAIN` | Default portal domain when `domain` is omitted from tool calls. | `data.seattle.gov` |
| `MCP_TRANSPORT_TYPE` | Transport: `stdio` or `http`. | `stdio` |
| `MCP_HTTP_PORT` | Port for HTTP server. | `3010` |
| `MCP_SESSION_MODE` | Session handling: `stateful`, `stateless`, or `auto` (schema default `auto` resolves to stateful). This server sets it explicitly to stateless. | `stateless` |
| `MCP_AUTH_MODE` | Auth mode: `none`, `jwt`, or `oauth`. | `none` |
| `MCP_LOG_LEVEL` | Log level (RFC 5424): `debug`, `info`, `notice`, `warning`, `error`. | `info` |
| `SOCRATA_CLAMP_LIMITS` | Serve a `limit` above a tool's per-call cap at the cap with a notice naming the offset that continues; `false` rejects it and restores each cap as the schema maximum. | `true` |
| `CANVAS_PROVIDER_TYPE` | DataCanvas spillover for large result sets; `none` disables it. DuckDB ships with the server — no additional install required. | `duckdb` |
| `SOCRATA_DATAFRAME_DROP_ENABLED` | Set to `true` to enable `socrata_dataframe_drop`. Off, the tool is omitted from the tool list. | `false` |
| `LOGS_DIR` | Directory for log files (Node.js only). | `/logs` |
| `STORAGE_PROVIDER_TYPE` | Storage backend: `in-memory`, `filesystem`, `supabase`, `cloudflare-kv/r2/d1`. | `in-memory` |
| `OTEL_ENABLED` | Enable [OpenTelemetry instrumentation](https://github.com/cyanheads/mcp-ts-core/tree/main/docs/telemetry). | `false` |
| `LOG_TOOL_FAILURE_PAYLOADS` | Log each failed tool call's arguments and result (key-name redaction only). | `false` |
See [`.env.example`](./.env.example) for the full list of optional overrides.
## Running the server
Requires [Bun v1.4.0+](https://bun.sh/) or Node.js v24+.
```sh
git clone https://github.com/cyanheads/socrata-mcp-server.git
cd socrata-mcp-server
bun install
cp .env.example .env
# Set SOCRATA_APP_TOKEN in .env if you have one.
```
### Local development
- **Build and run:**
```sh
# One-time build
bun run rebuild
# Run the built server
bun run start:stdio
# or
bun run start:http
```
- **Run checks and tests:**
```sh
bun run devcheck # Lint, format, typecheck, security audit
bun run test # Vitest test suite
```
### Docker
```sh
docker build -t socrata-mcp-server .
docker run --rm -e MCP_TRANSPORT_TYPE=http -p 3010:3010 socrata-mcp-server
```
The Dockerfile defaults to HTTP transport, stateless session mode, and logs to `/var/log/socrata-mcp-server`. OpenTelemetry peer dependencies are installed by default — build with `--build-arg OTEL_ENABLED=false` to omit them.
## Project structure
| Directory | Purpose |
|:---|:---|
| `src/index.ts` | `createApp()` entry point — registers tools, resources, prompts, and inits the Socrata service. |
| `src/config` | Server-specific environment variable parsing and validation with Zod. |
| `src/mcp-server/tools` | Tool definitions (`*.tool.ts`). Seven tools covering portal listing, dataset search, schema fetch, SoQL query, and DataCanvas SQL and cleanup. |
| `src/mcp-server/resources` | Resource definitions (`*.resource.ts`). Dataset metadata and portal catalog resources. |
| `src/mcp-server/prompts` | Prompt definitions (`*.prompt.ts`). Civic data investigation workflow prompt. |
| `src/services/socrata` | Socrata service layer — SODA 2.1 API client, Discovery API, query builder, type normalization. |
| `skills/` | Published guide for agents investigating civic data with the server. |
| `tests/` | Unit and integration tests mirroring `src/`. |
## Development guide
See [`CLAUDE.md`](./CLAUDE.md) for development guidelines and architectural rules. The short version:
- Handlers throw, framework catches — no `try/catch` in tool logic
- Use `ctx.log` for request-scoped logging, `ctx.state` for tenant-scoped storage
- Call `socrata_get_dataset` before writing WHERE clauses — `field_name` is what SoQL references and column `data_type` determines quoting
- Wrap external API calls: validate raw → normalize to domain type → return output schema; never fabricate missing fields
## Contributing
Issues are welcome. Run checks and tests before submitting:
```sh
bun run devcheck
bun run test
```
## License
Apache-2.0 — see [LICENSE](LICENSE) for details.