socrata-mcp-server

<div align="center"> <h1>@cyanheads/socrata-mcp-server</h1> <p><b>Search and query government open-data portals (Socrata SODA API) via MCP. STDIO or Streamable HTTP.</b> <div>7 Tools • 2 Resources • 1 Prompt</div> </p> </div> <div align="center">

</div> <div align="center">

Public Hosted Server: https://socrata.caseyjhand.com/mcp

</div>

---

Overview

Government open-data portals — searched and queried via the Socrata SODA 2.1 API and Discovery API. Discover portals and datasets, inspect typed column schemas, and run SoQL queries or DuckDB-powered SQL over large result sets, from any MCP client. Runs as a stdio process, a local Streamable HTTP server, or the public hosted endpoint above.

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
• 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: 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, and file-reading functions (read_csv, read_parquet) are rejected
• 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 not set), canvas_not_found, table_not_found, sql_rejected (non-SELECT, system catalog access, or a denied function)
• Works out of the box when CANVAS_PROVIDER_TYPE=duckdb is set — DuckDB ships as a regular dependency

---

socrata_dataframe_drop _tool

• Opt-in: listed as disabled 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 not set), 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, or