faostat-mcp-server

<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, over MCP. STDIO & Streamable HTTP.</b>
  <div>6 Tools • 0 Resources • 0 Prompts</div>
  </p>
</div>

<div align="center">

[![Version](https://img.shields.io/badge/Version-0.2.4-blue.svg?style=flat-square)](./CHANGELOG.md) [![License](https://img.shields.io/badge/License-Apache%202.0-orange.svg?style=flat-square)](./LICENSE) [![MCP SDK](https://img.shields.io/badge/MCP%20SDK-^2.0.0-green.svg?style=flat-square)](https://modelcontextprotocol.io/) [![npm](https://img.shields.io/npm/v/@cyanheads/faostat-mcp-server?style=flat-square&logo=npm&logoColor=white)](https://www.npmjs.com/package/@cyanheads/faostat-mcp-server) [![TypeScript](https://img.shields.io/badge/TypeScript-^7.0.2-3178C6.svg?style=flat-square)](https://www.typescriptlang.org/) [![Bun](https://img.shields.io/badge/Bun-v1.4.0-blueviolet.svg?style=flat-square)](https://bun.sh/)

[![Install in Claude Desktop](https://img.shields.io/badge/Install_in-Claude_Desktop-D97757?style=for-the-badge&logo=anthropic&logoColor=white)](https://github.com/cyanheads/faostat-mcp-server/releases/latest/download/faostat-mcp-server.mcpb) [![Install in Cursor](https://cursor.com/deeplink/mcp-install-dark.svg)](https://cursor.com/en/install-mcp?name=faostat-mcp-server&config=eyJjb21tYW5kIjoibnB4IiwiYXJncyI6WyIteSIsIkBjeWFuaGVhZHMvZmFvc3RhdC1tY3Atc2VydmVyIl19) [![Install in VS Code](https://img.shields.io/badge/VS_Code-Install_Server-0098FF?style=for-the-badge&logo=visualstudiocode&logoColor=white)](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)

[![Framework](https://img.shields.io/badge/Built%20on-@cyanheads/mcp--ts--core-67E8F9?style=flat-square)](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 FAOSTAT bulk-download corpus — crop and livestock production, agricultural trade, food balances, food security and nutrition, land use, fertilizer use, and agrifood-systems emissions for 245+ countries and territories from 1961 to the present. Discover a domain, resolve area/item/element codes, then query the cube; large or merged result sets spill to a DataCanvas SQL surface for `GROUP BY`, ranking, and time-series analysis. Runs as a stdio process, a local Streamable HTTP server, or the public hosted endpoint above.

### 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. |

## 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`

---

### `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) for 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`, `invalid_year_range`

---

### `faostat_commodity_profile` <sub>tool</sub>

- Resolves `item_query` to up to 5 item codes, then ranks top producers/exporters/importers and returns an annual production trend in one call
- 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); the merged observation set spills to a DataCanvas table for further SQL
- Typed errors: `no_match`, `index_not_ready`, `invalid_year_range`

---

### `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
- `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 creation/expiry (2-hour sliding TTL)
- `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`

## 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.

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

### Public Hosted Instance

A public instance is available at `https://faostat.caseyjhand.com/mcp` — no installation required. Point any MCP client at it via Streamable HTTP:

```json
{
  "mcpServers": {
    "faostat-mcp-server": {
      "type": "streamable-http",
      "url": "https://faostat.caseyjhand.com/mcp"
    }
  }
}
```

### Self-Hosted / Local

Add the following to your MCP client configuration file. The server runs entirely on a local mirror, so [build the mirror](#building-the-mirror) once before querying.

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

Or with npx (no Bun required):

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

Or with Docker:

```json
{
  "mcpServers": {
    "faostat-mcp-server": {
      "type": "stdio",
      "command": "docker",
      "ar