socrata-mcp-server

---

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

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 comes back null and the listing still returns

---

socrata_find_datasets _tool

• Full-text query across dataset names/descriptions; scope with domain, filter by categories/tags, restrict only to an asset type (datasets, maps, files, calendars, stories)
• Sort by relevance, page views, created date, or updated date; up to 100 per page with offset pagination
• 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
• 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: rate_limited (retryable) when the Discovery API returns 429, 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)
• 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.
• Up to 5000 rows per call with offset pagination; total_count returned when a plain row query is truncated (absent for grouped/aggregate queries)
• 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 CANVAS_PROVIDER_TYPE=duckdb and the page fills limit, 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
• 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)

---

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)
• Only meaningful when CANVAS_PROVIDER_TYPE=duckdb is set
• 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)
• Up to 10,000 rows per call, default 1000
• 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), 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: 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 rows fill the limit, 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, canvas_id_required, canvas_not_found, table_not_found, sql_rejected, canvas_disabled) with actionable recovery text

Getting started

Add the following to your MCP client configuration file.

Public Hosted Instance

A public instance is available at https://socrata.caseyjhand.com/mcp — no installation required. It serves Streamable HTTP; each client takes its own config shape.

For Claude Code:

claude mcp add --transport http socrata-mcp-server https://socrata.caseyjhand.com/mcp

For Cline (cline_mcp_settings.json):

{
  "mcpServers": {
    "socrata-mcp-server": {
      "type": "streamableHttp",
      "url": "https://socrata.caseyjhand.com/mcp"
    }
  }
}

For Cursor (mcp.json):

{
  "mcpServers": {
    "socrata-mcp-server": {
      "url": "https://socrata.caseyjhand.com/mcp"
    }
  }
}

Claude Desktop adds remote servers under Settings → Connectors.

Self-Hosted / Local

{
  "mcpServers": {
    "socrata-mcp-server": {
      "type": "stdio",
      "command": "bunx",
      "args": ["@cyanheads/socrata-mcp-server@latest"],
      "env": {
        "MCP_TRANSPORT_TYPE": "stdio",
        "MCP_LOG_LEVEL": "info"
      }
    }
  }
}

Or with npx (no Bun required):

{
  "mcpServers": {
    "socrata-mcp-server": {
      "type": "stdio",
      "command": "npx",
      "args": ["-y", "@cyanheads/socrata-mcp-server@latest"],
      "env": {
        "MCP_TRANSPORT_TYPE": "stdio",
        "MCP_LOG_LEVEL": "info"
      }
    }
  }
}

Or with Docker:

{
  "mcpServers": {
    "socrata-mcp-server": {
      "type": "stdio",
      "command": "docker",
      "args": [
        "run", "-i", "--rm",
        "-e", "MCP_TRANSPORT_TYPE=stdio",
        "ghcr.io/cyanheads/socrata-mcp-server:latest"
      ]
    }
  }
}

For Streamable HTTP, set the transport and start the server:

MCP_TRANSPORT_TYPE=http MCP_HTTP_PORT=3010 bun run start:http
# Server listens at http://localhost:3010/mcp

Prerequisites

• Bun v1.4.0 or higher (or Node.js v24+).
• Optional: A Socrata app token — register for free at any portal (e.g. data.seattle.gov) to get higher rate limits (10 req/s per token vs. shared throttled pool without one).

Installation

1. Clone the repository:

git clone https://github.com/cyanheads/socrata-mcp-server.git

2. Navigate into the directory:

cd socrata-mcp-server

3. Install dependencies:

bun install

4. Configure environment:

cp .env.example .env
# edit .env and set SOCRATA_APP_TOKEN if you have one

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
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).	<project-root>/logs
STORAGE_PROVIDER_TYPE	Storage backend: in-memory, filesystem, supabase, cloudflare-kv/r2/d1.	in-memory
OTEL_ENABLED	Enable OpenTelemetry instrumentation.	false
LOG_TOOL_FAILURE_PAYLOADS	Log each failed tool call's arguments and result (key-name redaction only).	false

See .env.example for the full list of optional overrides.

Running the server

Local development

• Build and run:

  # One-time build
  bun run rebuild
  
  # Run the built server
  bun run start:stdio
  # or
  bun run start:http
  

• Run checks and tests:

  bun run devcheck   # Lint, format, typecheck, security audit
  bun run test       # Vitest test suite
  

Docker

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.
tests/	Unit and integration tests mirroring src/.

Development guide

See 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:

bun run devcheck
bun run test

License

Apache-2.0 — see LICENSE for details.