Overview

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.

Tools

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

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
• code for an exact domain lookup (e.g. "RL") — one full record, without a topic search that can match unrelated domains
• offset + limit to page the catalog; the response reports totalMatches, truncated, and the nextOffset to resume at. Domain descriptions are long, so a browse call is bounded by default — raise limit (max 200) to pull the whole catalog in one response

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
• limit caps the inline page (default 200, max 1000); a match that outgrows the page is staged to the canvas table in full, and when no table is staged the response reports how many matched — so a low limit trims the response without putting rows out of reach
• 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 at most five item codes, then queries the production (QCL) and trade (TCL) domains and merges the results — the response discloses how many items the name matched in total, so a broad term like "milk" never silently narrows
• Ranks top producers, top exporters, and top importers by a per-country sum across the resolved items, each country taken at its own latest year with data and grouped by unit so incomparable quantities are never added — countries only, aggregates excluded
• Returns the annual production trend inline as year/value points. Rankings and trend are aggregated in SQL over the complete filtered match, so neither is bounded by the canvas staging cap
• Returns a partial profile with a notice naming the gap when a required domain (e.g. trade) is not indexed, rather than failing
• Rejects a reversed year_start / year_end range with invalid_year_range instead of returning an empty profile
• 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).
• faostat_dataframe_describe lists newest-first and pages: pass name for one table, or offset + limit (default 20, max 100) and follow the reported nextOffset while truncated is true.

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