io.github.cyanheads/faostat-mcp-server

Assemble a global profile for one QCL item in a single call: top-producing countries, the annual production trend, and trade flows (top exporters and importers). Supply item_code or item_query; code wins when both are supplied. A query selects a unique normalized exact label, otherwise a unique relevance match; ambiguous queries require an explicit code. Each ranking uses the selected item at each country's own latest year with data, grouped by unit. The trend is returned inline as year/value points. Country-level only (aggregates excluded). QCL is required; unavailable TCL yields a production-only profile with a notice. When the merged observation set is too large to inline, it spills to a DataCanvas table (canvas_id + table_name) — call faostat_dataframe_describe for its columns, then faostat_dataframe_query for deeper SQL.

Run a single-statement SELECT against the canvas tables staged by faostat_query_observations and faostat_commodity_profile (table names look like faostat_xxxxxxxx). Use this for cross-country and cross-item aggregation, GROUP BY rankings, joins, and time-series analysis over the full result set the inline preview only sampled. Standard DuckDB SQL — joins, aggregates, window functions, CTEs all work. Read-only: writes, DDL, DROP, COPY, PRAGMA, ATTACH, and external-file table functions are rejected; system catalogs (information_schema, sqlite_master, duckdb_*) are denied — list staged tables via faostat_dataframe_describe. Every row carries its data-quality `flag` — commonly A=Official, B=time-series break, E=Estimated, I=Imputed, M=Missing (value cannot exist), T=Unofficial, X=from an international organization, plus others FAOSTAT defines per domain — keep it in projections, treat any unrecognized flag as informational, and never assume it is official.

Discover FAOSTAT statistical domains (production, trade, food balances, food security, land use, agri-emissions, prices, value) with their codes, descriptions, last-update date, upstream row count, and local index status. Every query keys on a domain code from here. The `indexed` flag tells you which domains are queryable right now; un-indexed domains exist in the catalog but must be added to FAOSTAT_DOMAINS and re-synced before faostat_query_observations can read them. The catalog runs to ~69 domains with long descriptions, so responses are paged: narrow with `topic` / `indexed_only`, pass `code` to fetch one domain outright, or page with `offset` + `limit` — when the response reports `truncated`, pass the returned `nextOffset` to fetch the rest.

List the canvas tables (faostat_xxxxxxxx) staged by faostat_query_observations and faostat_commodity_profile, each with its source tool, the query parameters that produced it, creation/expiry timestamps, row count, and column schema. Call this before faostat_dataframe_query to discover the exact table and column names to reference in SQL. Tables are listed newest-first and paged: pass `name` to describe one table outright, or page with `offset` + `limit` — when the response reports `truncated`, pass the returned `nextOffset` to fetch the rest.

Query a FAOSTAT domain's data cube by area(s), item(s), element(s), and year range, returning observations (area, item, element, year, value, unit, and the data-quality flag). Resolve codes first with faostat_resolve_codes — the cube is unqueryable without them. Aggregate regions (World, continents, economic groupings) are EXCLUDED by default so a naive SUM does not double-count a region with its member countries; set include_aggregates=true to get the regional 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) — call faostat_dataframe_describe for its columns, then faostat_dataframe_query for GROUP BY / ranking / time-series analysis. Every row carries its flag — commonly A=Official, B=time-series break, E=Estimated, I=Imputed, M=Missing (value cannot exist), T=Unofficial, X=from an international organization, plus others FAOSTAT defines per domain — so honor it, treat any unrecognized flag as informational, and never assume an estimated, imputed, or unrecognized value is official.

Resolve human terms to the opaque integer codes faostat_query_observations needs, within a dimension: areas (countries/regions), items (commodities), or elements (metrics like production, yield, import quantity). Pass `query` for fuzzy full-text matching ("maize" → item 56), `name_contains` for a substring filter, or `code` for an exact-code lookup; omit all three to list the whole dimension. Item and element results are scoped to the requested domain — only codes present in that domain's cube are returned, so a resolved code is always queryable there (areas are shared across domains). Page large listings with `offset` + `limit`: when the response reports `truncated`, pass the returned `nextOffset` to fetch the next page. Every area match is flagged `country` or `aggregate` — aggregates (World, continents, economic groupings — codes ≥ 5000 plus a few curated sub-threshold roll-ups such as China=351, which sums mainland + Taiwan + Hong Kong + Macao) double-count if summed with their member countries, so resolve before querying and exclude aggregates unless you want the regional roll-up.