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.1.2-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-^1.29.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-^6.0.3-3178C6.svg?style=flat-square)](https://www.typescriptlang.org/) [![Bun](https://img.shields.io/badge/Bun-v1.3.11-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

[FAOSTAT](https://www.fao.org/faostat/) is the UN Food and Agriculture Organization's authoritative global statistics service — 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. Each domain is a data cube of **area** (country/region) × **item** (commodity) × **element** (metric) × **year**, with a data-quality flag on every observation.

This server does not call the FAOSTAT REST query API — that endpoint is auth-gated (`HTTP 401` keyless). Instead it syncs FAOSTAT's keyless **bulk-download service** (per-domain ZIPs of normalized CSVs plus their dimension code lists) into a persistent local **SQLite mirror** (embedded, with FTS5 over the dimension labels) and serves every query from that mirror — fast, offline-capable, and free of per-request rate limits. Analytical query results spill to a **DataCanvas** (DuckDB-backed) so an agent can run SQL `GROUP BY`, rankings, joins, and time-series analysis over the full result set.

> [!IMPORTANT]
> **First run requires a mirror build.** The corpus is not bundled. Run `bun run mirror:init` once to download and index the selected FAOSTAT domains before querying. The read tools return `index_not_ready` until the first sync completes. See [Building the mirror](#building-the-mirror).

## Tools

Six tools organized around the mirror's discover → resolve → query flow, with a DataCanvas pair for SQL over large result sets:

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

Discover the catalog and what's queryable right now.

- Full FAOSTAT catalog read live from the bulk manifest, annotated with local mirror status
- Per-domain `indexed` / `index_ready` flags, local row count, and last completed sync
- `topic` substring filter over code, name, and topic (e.g. `"trade"`, `"emissions"`, `"QCL"`)
- `indexed_only` to list just the domains queryable from the local mirror

---

### `faostat_resolve_codes`

Turn names into the integer codes the cube requires — FAOSTAT is unqueryable without code resolution.

- FTS5 full-text matching (`query`, e.g. `"maize"` → item 56), substring filter (`name_contains`), or exact-code lookup (`code`)
- Resolves within a `dimension`: `area` (countries/regions), `item` (commodities), or `element` (metrics like production, yield, import quantity)
- Every area match is flagged `country` or `aggregate` (World, continents, economic groupings; codes ≥ 5000) so an agent can avoid summing a region with its member countries
- Surfaces the CPC crosswalk code for items where available
- Omit all of `query` / `name_contains` / `code` to list the whole dimension

---

### `faostat_query_observations`

The core data tool — query a domain's cube and get observations with their data-quality flag.

- Filter by `area_codes`, `item_codes`, `element_codes` (resolve them first), and a `year_start` / `year_end` range
- **Aggregate regions are excluded by default** (`include_aggregates: false`) so a naive `SUM` does not double-count a region with its members — set `include_aggregates: true` for World/continent roll-ups, or pass explicit `area_codes` to query exactly what you name
- Small result sets return inline; large ones spill to a DataCanvas table (returned `canvas_id` + `table_name`) for SQL aggregation
- Every row carries its flag (`A`=Official, `E`=Estimated, `I`=Imputed, `B`=break, `X`=external) — honor it; never treat estimated/imputed values as official

---

### `faostat_commodity_profile`

A workflow tool that assembles a global profile for one commodity in a single call.

- Accepts a commodity name, resolves it to item codes, then queries the production (`QCL`) and trade (`TCL`) domains and merges the results
- Returns top-producing countries, top exporters, and top importers (ranked by the latest year present), plus a production-trend point count — countries only, aggregates excluded
- Returns a **partial profile** with a notice naming the gap when a required domain (e.g. trade) is not indexed, rather than failing
- The full merged production + trade observation set spills to a DataCanvas table for deeper SQL

---

### `faostat_dataframe_query` / `faostat_dataframe_describe`

SQL analytics over the canvas tables (`faostat_xxxxxxxx`) that `faostat_query_observations` and `faostat_commodity_profile` stage. Call `faostat_dataframe_describe` first to discover table and column names, then `faostat_dataframe_query` for cross-country and cross-item aggregation, `GROUP BY` rankings, joins, window functions, and CTEs — standard DuckDB SQL.

- **Read-only.** Writes, DDL, `DROP`, `COPY`, `PRAGMA`, `ATTACH`, and external-file table functions are rejected by the framework SQL gate. System catalogs (`information_schema`, `sqlite_master`, `duckdb_*`) are denied so a caller can't enumerate staged tables it doesn't hold a handle for — list them via `faostat_dataframe_describe`.
- Staged-table columns: `area_code`, `area`, `item_code`, `item`, `element_code`, `element`, `year`, `unit`, `value`, `flag`. Keep `flag` in projections and honor it in interpretation.
- `canvas_id` is optional on both tools — omit it to operate on the tables staged in the current session (the common case).

All tool output is also rendered as human-readable markdown (`content[]`) alongside the structured payload, so tool-only MCP clients reach the same data.

## Features

Built on [`@cyanheads/mcp-ts-core`](https://www.npmjs.com/package/@cyanheads/mcp-ts-core):

- Declarative tool definitions — single file per tool, framework handles registration and validation
- Unified error handling — handlers throw, the framework catches, classifies, and formats; typed error contracts give agents actionable recovery hints
- Pluggable auth: `none`, `jwt`, `oauth`
- Structured logging with optional OpenTelemetry tracing
- STDIO and Streamable HTTP transports

FAOSTAT-specific:

- **Persistent local SQLite mirror** of the FAOSTAT bulk corpus via the framework `MirrorService` — one indexed table per selected domain plus shared dimension tables, with FTS5 over the dimension labels driving code resolution
- **Streaming bulk-ZIP ingester** — fetches the manifest, compares each domain's update date against the stored checkpoint to skip unchanged domains, and stream-parses the normalized CSV (∼18× decompression ratio) into SQLite without materializing the full file in memory
- **Config-driven domain selection** (`FAOSTAT_DOMAINS`) — the indexed set can grow without code changes; `faostat_list_domains` always shows the full catalog and which domains are locally queryable
- **DataCanvas SQL surface** (DuckDB) — analytical cube queries spill to a staged table for ad-hoc `GROUP BY` / ranking / time-series analysis

Agent-friendly o