The agent loop

The agent in this demo is about 140 lines, and it does not know which model it is talking to. What makes it reliable is not the loop — it is that the only thing the model produces is a query spec, and a spec is checkable.

The five tools

There is no write_sql tool, and no warehouse tool. The model cannot reach the database except through a planned statement.

The schema the model writes against

The run_query tool takes a title, a chart kind and a spec. The spec schema is the one from the query spec, narrowed to what this demo needs, declared once in Zod and used twice — once to generate the JSON Schema the model is given, once to validate what comes back:

const spec = z
  .object({
    name: z.string().optional().describe('A short name for the query.'),
    projections: z.array(projection).min(1).describe('The columns, in order. At least one.'),
    calculations: z.array(projection).optional().describe('Formulas, appended after the projections.'),
    filters: z.array(filter).optional().describe('Conditions, ANDed together.'),
    segments: z.array(segment).optional().describe('Populations that constrain the query.'),
    limit: z.number().int().positive().max(5000).optional(),
  })
  .strict();

function define(name: ToolName, description: string): ToolSpec {
  return {
    name,
    description,
    schema: z.toJSONSchema(schemas[name], { target: 'draft-7' }) as Record<string, unknown>,
  };
}

A tool definition carries nothing vendor-specific; each provider driver reshapes it for its own API. Two things follow from .strict() and the .describe() calls. The enums (predicate, the decorator type, grain) mean the model picks from the planner’s actual vocabulary instead of guessing at one. And the validation step catches the two ways a tool input goes wrong — a model mistake, and an input truncated by max_tokens — before anything is planned:

const parsed = schema.safeParse(rawInput);
if (!parsed.success) {
  return {
    content: `Those arguments did not validate:\n${z.prettifyError(parsed.error)}`,
    isError: true,
    summary: 'invalid arguments',
  };
}

The field catalogue comes from the API

The system prompt carries the deployed fields, read from the discovery route at runtime rather than written into the prompt by hand:

At 532 fields it cannot all go inline, so the catalogue is curated by two rules rather than truncated:

const visible = fields.filter((f) => !f.hidden);
const measures = visible.filter((f) => f.kind === 'measure');
const dimensions = visible.filter((f) => f.kind === 'dimension');
const coreDimensions = dimensions.filter((f) => CORE_TABLES.has(homeTable(f)));
const otherDimensions = dimensions.filter((f) => !CORE_TABLES.has(homeTable(f)));

out.push('## Measures, by the table they belong to');
out.push(
  'Measures from different tables combine only where the model joins them. Prefer a published measure to deriving one.',
);
for (const [table, group] of byTable(measures)) {
  out.push('', `### ${table}`, ...group.map(line));
}

out.push('', '## Dimensions on the contact star');
for (const [table, group] of byTable(coreDimensions)) {
  out.push('', `### ${table}`, ...group.map(line));
}

// The rest are named by table and left to search_fields.
out.push('', '## Dimensions on other tables', `Not listed here — call \`search_fields\` for them, by name or by what they describe. ${otherDimensions.length} fields across: ${counts}`);

Every measure goes inline, grouped by the table it sits on. Measures are the scarce half — 106 of the 532 — they carry the business definitions, and which fact a measure belongs to is exactly what the agent must know before combining two of them. Dimensions go inline for the contact star and are named by table elsewhere, because a dimension is easy to find by guessing at its name, which is what search_fields is for; listing 350 of them buys nothing but tokens.

Worth saying what this is not: marking a field hidden: true in the model would drop it from /fields altogether, which hides it from search_fields too and makes it genuinely unreachable. Curating at the prompt keeps every field discoverable — the agent just has to ask.

Deploy a new measure and the next conversation knows about it. There is no prompt to edit and no list to keep in sync, which is the same argument for a semantic layer that applies to your BI tool.

Memoizing and sorting matter for a second reason: the prompt prefix has to be byte-identical between requests for prompt caching to hit. The catalogue and the tool list sit in front of the breakpoint, so after the first turn the bulk of the prompt is a cache read.

The prompt

The rules worth having are the ones that keep the model inside the grain the planner expects:

You do not write SQL. You write query specs and call `run_query`; 0sql plans the
SQL from the deployed semantic model — it picks the tables, the joins and the
grain, and it compiles row-level security into the statement. A spec that the
model cannot answer comes back as an error naming what went wrong; read it and
try a corrected spec.

- Field names must come from the model. The catalogue below is the full list;
  call `search_fields` when you want a field's description, synonyms or exact
  spelling.
- Measures are already aggregated (`Contacts` is a count, `Talk Duration Secs`
  is a sum). Never wrap a measure in an aggregate and never add a GROUP BY —
  projecting a dimension groups by it.
- For a trend, project the date dimension with a `truncate` decorator and order
  it `asc`. For a ranking, order the measure `desc` and add a `top_n` filter
  when the user asked for a top N.
- Relative dates are strings: `28d`, `3m`, `1y`. Use them instead of hardcoding
  today's date.
- Ratios belong in `calculations`: {"calculation": true, "alias": "SLA Rate",
  "sql": "[Answered In SLA Count]@m / nullif([Answered Count]@m, 0)"}. Reference
  measures as [Name]@m and never divide two measures in a projection.

Nothing in there is about security, because nothing in there could be. The context is attached by the server.

The loop

for (let turn = 0; turn < MAX_TURNS; turn += 1) {
  const result = await driver.turn({
    system,
    tools: toolSpecs,
    turns: history,
    onText: (text) => emit({ type: 'text', text }),
    signal,
  });

  history.push({
    role: 'assistant',
    text: result.text,
    calls: result.calls,
    provider: driver.id,
    raw: result.raw,
  });

  if (result.calls.length === 0) return history;

  for (const call of result.calls) {
    emit({ type: 'tool_call', id: call.id, name: call.name, input: call.input });
  }

  // Tools run in parallel when the model asked for several, and every result
  // goes back in one turn — a driver that splits them teaches the model to
  // stop calling tools in parallel.
  const results = await Promise.all(
    result.calls.map(async (call) => {
      const outcome = await runTool(call.name, call.input, toolContext, call.id);
      return {
        id: call.id,
        name: call.name,
        content: outcome.content,
        ...(outcome.isError ? { isError: true } : {}),
      };
    }),
  );

  history.push({ role: 'tool', results });
}

Any model that can call a function

The loop above never names a provider. It hands a driver a system prompt, the tool list and the conversation; the driver answers with the text the model produced and the tool calls it wants made, and owns the translation to its own API:

export interface Driver {
  readonly id: string;
  readonly model: string;
  turn(request: TurnRequest): Promise<TurnResult>;
}

The demo ships two: providers/anthropic.ts (Claude, the default — adaptive thinking, eager_input_streaming on the tools, a cache breakpoint on the system prompt) and providers/openai.ts (the Responses API with store: false). One key picks itself; two keys and LLM_PROVIDER decides.

That seam costs about 120 lines, and it is cheap for the reason this whole pattern exists: what the model produces is a query spec against a published JSON Schema, not prose that one vendor’s model happens to write well. Swapping the model swaps the thing writing specs. It changes nothing about who decides the join route, the grain, the dialect, or the rows this caller may see — that is the semantic layer, and it is identical under both.

The conversation is stored provider-neutrally for the same reason, with one concession to fidelity: an assistant turn keeps the provider’s own representation under raw, so a replay on the provider that produced it is byte-faithful (Claude’s thinking blocks must go back unchanged), and any other driver rebuilds the turn from the text and the calls.

run_query is where the three systems meet. Plan, run, hand both the rows and the statement back:

const planned = await planSql(querySpec, ctx.context);   // 0sql
const result = await runSql(planned.sql);                // your warehouse
const kind = inferChart(wanted, result);

ctx.emit({ type: 'query', id: callId, title, chart: kind, spec: querySpec, sql: planned.sql, result });

The browser gets the chart from that emit; the model gets the first twelve rows, the column names, the row count and the statement, so it can say what the numbers show and explain the shape of the query if asked.

inferChart is the one place the app overrules the model, and only downwards: a chart kind the result cannot carry becomes a table. One row and one measure is a number, not a bar. Past three measures the fixed palette would have to invent a colour. And a spec with two dimensions in it — weekly handle time by call center — comes back as 218 rows of week × site, which drawn as one line would repeat the x labels and join points belonging to different sites, so it is shown as a table until someone writes the pivot.

A turn, in full

“Which call centers have the longest average handle time?” The model has the catalogue, so it does not need search_fields here. It writes:

{
  "name": "aht_by_call_center",
  "projections": [
    { "field": "Call Center", "alias": "Call Center" },
    { "field": "Average Contact Duration Secs", "alias": "Average Handle Time Secs", "order_by": "desc" }
  ],
  "limit": 10
}

That goes to POST /projects/customer-service/branches/main/sql with the caller’s context. Two things the spec does not say and did not have to: that Average Contact Duration Secs lives on cs_contact_f while Call Center lives on cs_call_center_d, and that the two are joined on call_center_id. The planner knows both from rel.contact.yml, and returns one statement:

-- datasource: CS Warehouse
SELECT
	T1."call_center_desc" AS "Call Center",
	sum(T0."contact_duration_secs") * 1.0 / nullif(count(*), 0) AS "Average Handle Time Secs"
FROM
	cs_contact_f T0
	LEFT JOIN cs_call_center_d T1
		ON T0.call_center_id = T1.call_center_id
GROUP BY
	T1."call_center_desc"
ORDER BY
	sum(T0."contact_duration_secs") * 1.0 / nullif(count(*), 0) desc

The join came from the model, the GROUP BY came from projecting a dimension beside a measure, and the LEFT JOIN came from join: left on the relationship — so contacts whose site is unknown are still counted. The measure’s own formula, sum(contact_duration_secs) * 1.0 / nullif(count(*), 0), is in tbl.contact.yml: an average of an average is a bug, and the model is where that gets settled once. The spec’s limit: 10 ends the statement as LIMIT 10 — which caps the rows but does not rank them, so a genuine top N wants a top_n filter instead.

The app runs it, charts it, and the model gets the rows back to summarize: “The longest average handle times are in: 1. Hyderabad – 917.4 seconds, 2. Cebu – 820.9 seconds, 3. Tokyo – 770.9 seconds…”. One turn, one tool call, one statement.

When the spec is wrong

A spec is checkable, so a mistake is an error with a name rather than a number nobody questions. These are the planner’s own answers to four broken specs against this model:

FieldNotFound: No field named 'Agent Tenure' in this model.
  Did you mean: Agent Response Count, Agent Response Secs, Agent Role Code?

InvalidValue: Validation failed: only date/datetime types can be
  date truncated (Call Center)

InvalidSpec: 'sounds_like' is not a predicate
  (filter on Call Center)

InvalidSpec: unknown attribute 'bogus' on the truncate
  decorator of Call Center

Each comes back as a tool error, class first, which is usually enough for the model to fix it on the next turn without another round of discovery. The chat shows this happening, because the tool trace is not hidden — a line reading run_query → InvalidValue followed by run_query → 9 rows · duckdb is the agent reading the planner’s complaint and correcting its own spec, with nothing wrong ever reaching the chart:

if (error instanceof ZsqlError) {
  // The interesting case: 0sql refused the spec. The class says why, and
  // the model can usually fix it on the next turn.
  return {
    content: `0sql refused this request — ${error.errorClass}: ${error.message}`,
    isError: true,
    summary: error.errorClass,
  };
}

The error classes the agent meets in practice:

Near misses never get that far: a reference that is close enough resolves to the right field and comes back under corrections, which the demo passes to the model so it can mention the substitution.

What it does instead of guessing

The catalogue in the prompt and the errors behind it combine into something better than recovery: the agent mostly declines to invent. Two unedited answers from the running demo, both for questions this model genuinely cannot serve.

Asked for average handle time by agent tenure band — a dimension that does not exist — it never writes a spec at all:

The data model behind this dashboard doesn’t have an “agent tenure” or “tenure band” field (there’s Agent User ID and Agent Role Code, but nothing about hire date or tenure). Because of that, I can’t break out Average Handle Time by tenure band with the fields available. […] If “tenure band” exists in another table or under another name that isn’t in this field list, you’d need it added to the semantic model first; then I can slice AHT by that dimension.

Asked for the median handle time per call center, and the share of each call center in total contacts, it answers the half the model supports — contribute gives share of total — and says plainly which half it cannot:

I can’t calculate a true median with the current semantic model, so I’m using the available handle-time metric: Average Contact Duration Secs (mean handle time). […] Manila handles the largest share of contacts (~19.7%), while Hyderabad has the highest average handle time (~917 seconds).

That is the shape of the whole argument. “Which fields exist” and “what they mean” are answerable questions when a semantic model is deployed, so an agent in front of one has something to be wrong against — at worst a 422, at best a straight refusal to make a number up. Without one, the same question produces SQL referencing agent_tenure_band, and whatever comes back goes on a dashboard.

Cost

The catalogue is the largest thing in the prompt — every measure in a 532-field model, with its table and data type — and the tool definitions are the next largest. Both sit in front of the cache breakpoint and neither changes between requests, so a conversation pays for them once and reads them from the cache after that. The app streams what each turn actually cost alongside the answer, cache reads included, which is the number to watch rather than any figure quoted here.

output_config: { effort: 'medium' } keeps a chat answer from turning into an essay; raise it for genuinely hard analysis, drop it to low for a cheaper, quicker agent.

Next

• Tiles, security and deployment
• The query spec, key by key
• Errors, every class the planner raises