@cyanheads/faostat-mcp-server
by cyanheads
README.md
<div align="center">
<h1>@cyanheads/faostat-mcp-server</h1>
<p><b>Global food & agriculture statistics from the UN FAOSTAT bulk-download corpus, served from a local SQLite mirror with a DataCanvas SQL surface, via MCP. STDIO or Streamable HTTP.</b>
<div>7 Tools • 1 Skill Resource • 0 Prompts</div>
</p>
</div>
<div align="center">
[](./CHANGELOG.md) [](./LICENSE) [](https://modelcontextprotocol.io/) [](https://www.npmjs.com/package/@cyanheads/faostat-mcp-server) [](https://www.typescriptlang.org/) [](https://bun.sh/)
[](https://github.com/cyanheads/faostat-mcp-server/releases/latest/download/faostat-mcp-server.mcpb) [](https://cursor.com/en/install-mcp?name=faostat-mcp-server&config=eyJjb21tYW5kIjoibnB4IiwiYXJncyI6WyIteSIsIkBjeWFuaGVhZHMvZmFvc3RhdC1tY3Atc2VydmVyIl19) [](https://vscode.dev/redirect?url=vscode:mcp/install?%7B%22name%22%3A%22faostat-mcp-server%22%2C%22command%22%3A%22npx%22%2C%22args%22%3A%5B%22-y%22%2C%22%40cyanheads%2Ffaostat-mcp-server%22%5D%7D)
[](https://www.npmjs.com/package/@cyanheads/mcp-ts-core)
</div>
<div align="center">
**Public Hosted Server:** [https://faostat.caseyjhand.com/mcp](https://faostat.caseyjhand.com/mcp)
</div>
---
## Overview
Global food and agriculture statistics from the UN's FAOSTAT: production, trade, food balances, food security, land use, fertilizer, and emissions for 245+ countries and territories since 1961. Resolve commodity and country codes, query the data, and run SQL over large results.
### Tools
| Tool | Description |
|:---|:---|
| `faostat_list_domains` | Discover FAOSTAT statistical domains with codes, descriptions, last-update date, upstream row count, and local index status. The entry point — every query keys on a domain code. |
| `faostat_resolve_codes` | Resolve human terms to the opaque integer codes a query needs (areas, items, elements), flagging each area as a country or an aggregate region. |
| `faostat_query_observations` | Query a domain's cube by area(s), item(s), element(s), and year range. Inline preview for small results; large sets spill to a DataCanvas table. |
| `faostat_commodity_profile` | Workflow: assemble top producers, the production trend, and trade flows for one commodity from the production and trade domains in a single call. |
| `faostat_dataframe_query` | Run a read-only SQL `SELECT` against the canvas tables staged by the analytical tools. |
| `faostat_dataframe_describe` | List the canvas tables staged this session, each with provenance, row count, and column schema. |
| `faostat_dataframe_drop` | Drop one staged canvas table before its 2-hour TTL. Opt-in — callable only with `FAOSTAT_DATAFRAME_DROP_ENABLED=true`. |
## Capability reference
### `faostat_list_domains` <sub>tool</sub>
- Full FAOSTAT catalog (~69 domains) read live from the bulk manifest, annotated with local mirror status
- `code` for an exact domain lookup; `topic` substring filter over code/name/topic; `indexed_only` to list only domains queryable from the local mirror
- `offset` + `limit` (max 200, default 20) page the catalog — response reports `totalMatches`, `truncated`, and `nextOffset`
- Each entry reports `indexed` / `index_ready` flags, local row count, and last completed sync
---
### `faostat_resolve_codes` <sub>tool</sub>
- FTS5 full-text `query`, substring `name_contains`, or exact `code` lookup within a `dimension`: `area`, `item`, or `element`
- Item/element matches are scoped to codes present in the given `domain`'s cube; area codes are shared across domains
- Every area match is flagged `country` or `aggregate` (codes ≥ 5000, plus curated sub-threshold roll-ups such as China=351)
- `limit` (max 200, default 50) + `offset` page the match set
- Typed errors: `unknown_domain`, `index_not_ready`, `query_timeout`
---
### `faostat_query_observations` <sub>tool</sub>
- Filters by `area_codes` / `item_codes` / `element_codes` and an inclusive `year_start` / `year_end` range
- Aggregate regions excluded by default (`include_aggregates: false`); explicit `area_codes` bypass the exclusion
- `limit` caps the inline page (default 200, max 1000); a match that exceeds it spills in full to a DataCanvas table (50,000-row staging cap) — `faostat_dataframe_describe` for its columns, then SQL via `faostat_dataframe_query`
- Every row carries its data-quality `flag` (`A`/`E`/`I`/`B`/`M`/`T`/`X`, others per domain) — never dropped
- Typed errors: `domain_not_indexed`, `index_not_ready`, `canvas_disabled`, `canvas_not_found`, `invalid_year_range`, `query_timeout`
---
### `faostat_commodity_profile` <sub>tool</sub>
- Selects one QCL item with `item_code` or `item_query` (code takes precedence), then ranks top producers/exporters/importers and returns an annual production trend
- Query selection prefers a unique exact label after trimming, collapsing whitespace, and case folding; otherwise requires a unique relevance match. For ambiguous terms such as `maize` or `milk`, call `faostat_resolve_codes` with `domain: "QCL"`, `dimension: "item"`, and the query, then pass the selected `item_code` (e.g. `56` for maize grain)
- Rankings are per-country sums grouped by unit, each country at its own latest reporting year; countries only (aggregates excluded)
- Returns a partial, production-only profile with a notice — rather than failing — when the trade domain (TCL) isn't indexed or still syncing
- `top_n` caps each ranked list (max 50); when the merged observation set is too large to inline it spills to a DataCanvas table — `faostat_dataframe_describe` for its columns, then `faostat_dataframe_query` for further SQL
- Every staging outcome is stated in the notice: the staged table, a set that fit inline, a staging failure, or a disabled canvas
- Typed errors: `missing_item`, `ambiguous_item`, `no_match`, `domain_not_indexed`, `index_not_ready`, `invalid_year_range`, `canvas_not_found`, `query_timeout`
---
### `faostat_dataframe_query` <sub>tool</sub>
- Single-statement read-only `SELECT` over staged `faostat_xxxxxxxx` tables — joins, aggregates, window functions, and CTEs all work
- Writes, DDL, `DROP`, `COPY`, `PRAGMA`, `ATTACH`, external-file functions, and system catalogs (`information_schema`, `sqlite_master`, `duckdb_*`) are rejected
- Engine-setting introspection, `query_table`, dynamic `query()` SQL, and file table references are rejected; `query()` with a single literal read-only `SELECT` remains supported
- `row_limit` caps the response (default 1000, max 10000); `truncated` means more rows exist, with no exact total computed on this path
- Typed errors: `canvas_disabled`, `canvas_not_found`, `missing_table`, `system_catalog_access`, `invalid_sql`
---
### `faostat_dataframe_describe` <sub>tool</sub>
- Lists staged tables with source tool, query params, row count, column schema, and recorded creation/expiry timestamps
- `name` describes one table outright; otherwise `offset` + `limit` (max 100, default 20) page the listing newest-first
- Typed errors: `canvas_disabled`, `canvas_not_found`, `missing_table`
---
### `faostat_dataframe_drop` <sub>tool</sub>
- Opt-in: registered disabled — listed on the landing page with its enable hint, absent from `tools/list` — unless `FAOSTAT_DATAFRAME_DROP_ENABLED=true`
- Drops one staged `faostat_xxxxxxxx` table and its provenance from the session canvas, or from the canvas a `canvas_id` names
- Idempotent: a name not staged on that canvas returns `dropped: false` with a notice and changes nothing
- Typed errors: `canvas_disabled`, `canvas_not_found`
## Features
The [faostat-guide](./skills/faostat-guide/SKILL.md) covers comparable reporting years, country versus regional totals, units, coverage, and complete SQL analysis across staged results. It ships with plugin, npm, Docker, and MCPB installs and is served through the MCP Skills extension as a skill resource.
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.
FAOSTAT-specific:
- Persistent local SQLite mirror of the FAOSTAT bulk corpus via the framework `MirrorService`, with FTS5 over the dimension labels driving code resolution
- Streaming bulk-ZIP ingester — skips domains whose upstream update date hasn't advanced, and stream-parses the normalized CSV into SQLite without materializing the full file in memory
- Config-driven domain selection (`FAOSTAT_DOMAINS`) — the indexed set can grow without code changes, and the full catalog stays browsable regardless
- DataCanvas SQL surface (DuckDB) for `GROUP BY`, ranking, and time-series analysis over spilled result sets
Agent-friendly output:
- Country-vs-aggregate classification on every area, with aggregates excluded from sums by default — guards against double-counting World/continent rows with their member countries
- Data-quality provenance — every observation carries its FAOSTAT flag (`A`/`E`/`I`/`B`/`M`/`T`/`X`, others per domain), never dropped from output
- Graceful partial results — `faostat_commodity_profile` returns a production-only profile with a notice, rather than failing, when the trade domain isn't indexed
- Typed error contracts — `index_not_ready`, `domain_not_indexed`, `canvas_disabled`, and others each carry a concrete recovery hint
## Getting started
Connect to the hosted instance, no install:
```sh
claude mcp add --transport http faostat-mcp-server https://faostat.caseyjhand.com/mcp
```
Or run it locally after [building the mirror](#building-the-mirror):
```sh
claude mcp add faostat-mcp-server -- npx -y @cyanheads/faostat-mcp-server
```
For other clients, add this to the MCP config file:
```json
{
"mcpServers": {
"faostat-mcp-server": {
"command": "npx",
"args": ["-y", "@cyanheads/faostat-mcp-server"]
}
}
}
```
The bulk-download service is public and keyless. For Docker or a source build, see [Running the server](#running-the-server).
## Configuration
| Variable | Description | Default |
|:---------|:------------|:--------|
| `FAOSTAT_DOMAINS` | Comma-separated FAOSTAT domain codes to index into the local mirror. Domains outside this set appear in `faostat_list_domains` but are not queryable until added and re-synced. | `QCL,TCL,FBS,FS,RL,GLE,RFN,QV` |
| `FAOSTAT_MIRROR_PATH` | Directory holding the per-domain SQLite stores and the shared dimension database. Created if absent. | `./.faostat-mirror` |
| `FAOSTAT_BULK_BASE_URL` | FAOSTAT bulk-download service base URL (manifest + per-domain ZIPs). | `https://bulks-faostat.fao.org/production` |
| `FAOSTAT_REFRESH_CRON` | Cron for the in-process incremental refresh (HTTP transport only). Omit to disable and run `mirror:refresh` out-of-band. | — |
| `CANVAS_PROVIDER_TYPE` | DataCanvas engine. `duckdb` enables the SQL surface; set `none` to disable analytical staging (the `dataframe_*` tools then report `canvas_disabled` and large queries refuse to spill). | `duckdb` |
| `FAOSTAT_DATAFRAME_DROP_ENABLED` | Set `true` to make `faostat_dataframe_drop` callable. Off, the tool is registered disabled: absent from `tools/list`, shown on the landing page with this hint. | `false` |
| `MCP_TRANSPORT_TYPE` | Transport: `stdio` or `http`. | `stdio` |
| `MCP_SESSION_MODE` | HTTP session mode: `stateless`, `stateful`, or `auto` (resolves to `stateful`). The server declares `stateless` in `src/index.ts` — no tool asks the caller for input mid-handler — and setting this overrides that declaration. | `stateless` |
| `MCP_HTTP_PORT` | Port for the HTTP server. | `3010` |
| `MCP_AUTH_MODE` | Auth mode: `none`, `jwt`, or `oauth`. | `none` |
| `MCP_LOG_LEVEL` | Log level (RFC 5424). | `info` |
| `LOGS_DIR` | Directory for log files (Node.js only). | `<project-root>/logs` |
See [`.env.example`](./.env.example) for the full list of optional overrides.
## Running the server
### Local development
Requires [Bun v1.4.0](https://bun.sh/) or higher (or Node.js v24+), plus disk for the mirror. The default domain set (`QCL,TCL,FBS,FS,RL,GLE,RFN,QV`, ∼37M rows) needs a few GB; drop `TCL` (∼17M rows) from `FAOSTAT_DOMAINS` on a constrained host.
```sh
git clone https://github.com/cyanheads/faostat-mcp-server.git
cd faostat-mcp-server && bun install
cp .env.example .env
# Override the default domain set, mirror path, or refresh cron in .env.
```
- **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
bun run test # Vitest suite under Bun, then under Node
bun run lint:mcp # Validate MCP definitions against spec
```
### Building the mirror
The corpus is not bundled. Before the data tools can answer queries, sync the selected domains into the local mirror:
```sh
bun run mirror:init # one-time bootstrap — downloads and indexes the FAOSTAT_DOMAINS set
bun run mirror:refresh # re-sync domains whose upstream update date has advanced; build missing planner statistics
bun run mirror:verify # report sync status, local row counts, and sample reads
```
`mirror:init` is idempotent and resumable per domain — re-running after an interrupt re-streams only the unfinished domain ZIP. `FAOSTAT_DOMAINS` selects which domains are indexed; everything else in the catalog shows in `faostat_list_domains` with `indexed: false` until added and re-synced. On HTTP transport, set `FAOSTAT_REFRESH_CRON` to refresh in-process on a schedule; on stdio, run `mirror:refresh` out-of-band. The read tools return `index_not_ready` until the first sync completes.
`mirror:refresh` also builds query-planner statistics (`ANALYZE`) for any selected domain that has none, even when its data is unchanged upstream — a mirror synced by an older release, or one whose last build failed. Otherwise the server builds them itself, in the background on a worker thread, the first time a data read touches that domain, and keeps answering meanwhile.
### Docker
```sh
docker run --rm -p 3010:3010 -v faostat-mirror:/usr/src/app/.faostat-mirror ghcr.io/cyanheads/faostat-mcp-server:latest
```
Build locally with `docker build -t faostat-mcp-server .`. For stdio from an MCP client, use `"command": "docker"` with `"args": ["run", "-i", "--rm", "-e", "MCP_TRANSPORT_TYPE=stdio", "-v", "faostat-mirror:/usr/src/app/.faostat-mirror", "ghcr.io/cyanheads/faostat-mcp-server:latest"]`. The image defaults to HTTP transport, stateless session mode, and logs to `/var/log/faostat-mcp-server`. A dedicated dependency stage installs the production tree for the target platform (DuckDB's prebuilt native binding included), so the slim runtime image carries no build toolchain and a multi-arch build never runs Bun under emulation. OpenTelemetry peer dependencies are installed by default — build with `--build-arg OTEL_ENABLED=false` to omit them. Mount a volume at the mirror path to persist the corpus across container recreations, and bootstrap it inside the container:
```sh
docker exec <container> bun run mirror:init # one-time bootstrap
docker exec <container> bun run mirror:verify # sync status + sample reads
docker exec <container> bun run mirror:refresh # re-sync updated domains; build missing planner statistics
```
## Project structure
| Directory | Purpose |
|:----------|:--------|
| `src/index.ts` | `createApp()` entry point — registers the seven tools (`faostat_dataframe_drop` behind its opt-in flag), wires the mirror and canvas in `setup()`, schedules the HTTP refresh. |
| `src/config` | Server-specific environment variable parsing and validation with Zod. |
| `skills/faostat-guide` | Published agent guide, also served as an MCP skill resource. |
| `src/mcp-server/tools/definitions` | Tool definitions (`*.tool.ts`). |
| `src/services/faostat-mirror` | The bulk-download mirror service — manifest discovery, streaming ZIP ingester, CSV parsing, dimension store, SQLite-backed `MirrorService` wiring, and the two-worker read pool (`read-pool.ts`, `read-worker.ts`) that runs cube reads and statistics builds off the main thread. |
| `src/services/canvas-accessor.ts`, `canvas-staging.ts` | DataCanvas accessor and the spill/query/describe/drop staging layer. |
| `scripts/faostat-mirror-*.ts` | `mirror:init` / `mirror:refresh` / `mirror:verify` CLIs. |
| `tests/` | Unit and integration tests mirroring `src/`. |
## Development guide
See [`CLAUDE.md`/`AGENTS.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
- Register new tools in the `createApp()` array in `src/index.ts`
- Wrap external data: validate raw → normalize to domain type → return output schema; never fabricate missing fields, and carry the data-quality flag through
## Data attribution
Data is sourced from [FAOSTAT](https://www.fao.org/faostat/), the statistics division of the Food and Agriculture Organization of the United Nations (FAO). FAOSTAT data is published under [CC BY-4.0](https://creativecommons.org/licenses/by/4.0/); cite FAO as the source in downstream use. This project is not affiliated with or endorsed by the FAO.
## 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.
This server cannot be deployed
Maintenance
ActivityMaintained
ResponsivenessResponsive