Discovery: explore, fields and tables

Five read routes describe a deployed branch without planning anything you have to run. They are what a field picker, an autocomplete or an agent calls before building a spec. Like /sql, none takes a credential: they answer whoever can reach the server, so your application calls them and decides what to show to whom.

routereturns
POST .../explorethe dimensions and measures a given query can still add
GET .../fields?q=&hidden=every field, optionally searched
GET .../tablesevery table with its fields
GET .../branches/{branch}the branch summary
GET /projectsevery deployed project and branch

... is /projects/{uid}/branches/{branch} throughout.

explore

Given a query, which other fields could join it without breaking it? Send the same body as /sql (spec or expr, optional q). A context is accepted and ignored: exploring is about the model, not about rows.

curl -s http://127.0.0.1:3699/projects/tpcds/branches/main/explore \
  -H "Content-Type: application/json" \
  -d '{"expr": "category, web net paid", "q": "date"}'
zsql explore 'category, web net paid' -q date
date        dim  Date          web_sales, store_sales, date_dim  ~1.00
ws_sold_date dim Web Sold Date web_sales                         ~0.67
-- can add 2 dimensions, 0 measures · server 210 us
const res = await fetch("http://127.0.0.1:3699/projects/tpcds/branches/main/explore", {
  method: "POST",
  headers: { "Content-Type": "application/json" },
  body: JSON.stringify({ spec: currentSpec, q: userTyped }),
});
const { dimensions, measures } = await res.json();
{
  "dimensions": [
    {"uid": "date", "name": "Date", "data_type": "date", "description": "Calendar date",
     "tables": ["web_sales", "store_sales", "date_dim"], "score": 1.0},
    {"uid": "ws_sold_date", "name": "Web Sold Date", "data_type": "date", "description": "",
     "tables": ["web_sales"], "score": 0.67}
  ],
  "measures": [],
  "total_us": 210
}
  • Without q every addable field is listed, dimensions and measures separately, and score is absent.
  • With q, only fields whose name contains the term (score 1.0) or that are trigram-near it (score at least 0.5) are kept, best first.
  • Hidden fields are never listed.

Use it to drive a picker: after each selection, send the current spec and show only what comes back, so the user cannot build a query the planner will refuse. Agents get the same guarantee: a loop of explore, pick, explore converges on a plannable spec without guessing field names.

fields

curl -s "http://127.0.0.1:3699/projects/tpcds/branches/main/fields?q=paid"
{"fields": [
  {"uid": "net_paid", "name": "Net Paid", "kind": "measure", "data_type": "decimal",
   "description": "Store sales net paid", "synonyms": ["revenue"], "tags": [],
   "hidden": false, "tables": ["store_sales"], "score": 1.0},
  {"uid": "ws_net_paid", "name": "Web Net Paid", "kind": "measure", "data_type": "decimal",
   "description": "Web sales net paid", "synonyms": [], "tags": [],
   "hidden": false, "tables": ["web_sales"], "score": 1.0},
  {"uid": "ws_paid_inc_tax", "name": "Web Paid Inc Tax", "kind": "measure", "data_type": "decimal",
   "description": "", "synonyms": [], "tags": [], "hidden": false, "tables": ["web_sales"], "score": 1.0}
]}
parameffect
qcontaining matches first (score 1), then fuzzy matches with their trigram score
hiddentrue includes hidden fields

Without q the whole catalogue is returned and score is absent. kind is dimension or measure. tags are the policy trigger tags from the model. CLI: zsql fields paid, zsql fields --hidden.

tables

curl -s http://127.0.0.1:3699/projects/tpcds/branches/main/tables
{"tables": [
  {"uid": "store_sales", "name": "Store Sales", "physical_name": "store_sales", "cost": 100,
   "datasource": "warehouse", "fields": ["net_paid", "ss_sold_date", "customer_id", "..."]},
  {"uid": "web_sales", "name": "Web Sales", "physical_name": "web_sales", "cost": 100,
   "datasource": "warehouse", "fields": ["ws_net_paid", "ws_sold_date", "..."]},
  {"uid": "item", "name": "Item", "physical_name": "item", "cost": 10,
   "datasource": "warehouse", "fields": ["category", "product_name", "..."]},
  {"uid": "date_dim", "name": "Date", "physical_name": "date_dim", "cost": 1,
   "datasource": "warehouse", "fields": ["date"]}
]}

cost is the model’s planning cost (the resolver prefers cheaper tables); fields lists field uids. The table uids are what the spec’s hints key accepts. CLI: zsql tables.

branch summary

curl -s http://127.0.0.1:3699/projects/tpcds/branches/main
{"project": "tpcds", "branch": "main", "deployed_at": "2026-10-01 15:04",
 "datasources": 1, "tables": 6, "fields": 42, "joins": 5, "paths": 9, "policies": 1,
 "tests": 3, "values": 4, "warnings": []}

The same counts a deploy prints, minus the test results. values is how many dimensions have sampled values on this branch; 0 means the values route below answers 404 for every dimension, which is a different thing from a dimension that does not exist. CLI: zsql status.

values

A dimension’s sampled values, when the project deployed any.

curl -s http://127.0.0.1:3699/projects/tpcds/branches/main/values/category
{"uid": "contact-subchannel-id", "name": "Contact Subchannel ID", "table": "cs_duckdb",
 "sampled_at": "2026-10-24T11:18:33Z", "sample_size": 1000, "distinct": 6,
 "values": [{"v": "SC1", "n": 1530}, {"v": "SC4", "n": 1238}, {"v": "SC5", "n": 872}]}

{dim} is a uid or a name, resolved with the same near-match rules as the other field lookups: no match is 404 NotFound with no dimension matches '...', and several is 400 Ambiguous listing the uids. A dimension that exists but was not sampled is 404 NotFound with no sampled values for <uid>.

distinct is the column’s total and values is what came back, so a list as long as sample_size is the sign the sample is truncated rather than whole. n is a row count on the dimension’s own table, which is frequency on a fact and near-meaningless on a lookup — see the generated query.

These are the values a caller writes a filter from. CLI: zsql values DIM; over MCP, the values tool.

projects

curl -s http://127.0.0.1:3699/projects
{"deployments": [
   {"project": "tpcds", "branch": "main", "deployed_at": "2026-10-01 15:04",
    "datasources": 1, "tables": 6, "fields": 42, "joins": 5, "paths": 9, "policies": 1, "tests": 3, "values": 0, "warnings": []}
 ]}

deployments is one summary per deployed branch, every one on the server: there is no per-project access, so a service in front that serves several tenants filters this list itself. CLI: zsql deployments.

Next steps