Query API

Request/Response Models

Source: packages/api/src/nl2sql_api/models/query.py

`QueryRequest`

field	type	required	meaning
`natural_language`	`str`	yes	User query.
`datasource_id`	`Optional[str]`	no	Datasource override.
`execute`	`bool`	no	Execute SQL against datasource (default `true`).
`user_context`	`Optional[Dict[str, Any]]`	no	Ignored unless `api.trust_body_role` is `true` (local testing only); then an RBAC payload such as `{"roles": ["admin"]}`. See Caller role.

`SubQueryResponse`

Derives from nl2sql.SubQueryResult, so it has exactly the engine's fields.

field	type	required	meaning
`id`	`str`	no	Sub-query identifier.
`intent`	`str`	no	Semantic intent of the sub-query.
`sql`	`str`	no	SQL generated for the sub-query.
`datasource_id`	`str`	no	Datasource the sub-query targets.
`schema_version`	`str`	no	Schema version used for planning.
`plan`	`Optional[Dict[str, Any]]`	no	The validated plan, dumped.
`validation`	`List[ValidationCheck]`	no	Validation checks (`name`, `passed`, `message`).
`rows`	`Optional[RowSample]`	no	Capped row sample (`columns`, `rows`, `total_rows`).
`status`	`str`	no	`"success"` or `"error"` for this sub-query, from its final attempt: `"success"` when it ended with SQL and, if executed, a result; otherwise `"error"`. A retry that recovers reports `"success"`. `"dropped"` when a hook denied it with `scope="sub_query"`: the run went on without it.
`retry_count`	`int`	no	Plan/SQL refinement attempts made.
`plan_source`	`"llm" \\| "cache" \\| "verified" \\| "supplied" \\| "approved" \\| "chosen"`	no	`"cache"` when the plan came from the plan cache (no planner call; still validated and executed), `"verified"` when a verified query answered (there is no plan; `sql` is the verified query's own, with its `:name` placeholders), `"supplied"` or `"approved"` for a pinned plan (see Query API → Running a plan you hold), otherwise `"llm"`.
`row_cap`	`Optional[int]`	no	The row limit that bounded this sub-query's SQL, or `null` when the plan's own `LIMIT` was smaller.
`truncated`	`bool`	no	True when the result reached `row_cap`, so rows may be missing.

`QueryResponse`

Derives from nl2sql.QueryResult (only sub_queries is narrowed to SubQueryResponse), so the HTTP response cannot drift from what the engine returns.

field	type	required	meaning
`sub_queries`	`List[SubQueryResponse]`	no	One entry per decomposed sub-query, each with its SQL.
`final_answer`	`Optional[Dict[str, Any]]`	no	Answer synthesizer payload (`summary`, `format_type`, `content`, `warnings`, `truncated`).
`errors`	`List[Dict[str, Any]]`	no	Pipeline errors (`node`, `message`, `error_code`, `severity`, `provider`, `provider_response`). `provider` and `provider_response` are set for a `PROVIDER_*` code and `null` otherwise; see provider failures.
`trace_id`	`str`	no	Trace identifier.
`reasoning`	`List[Dict[str, Any]]`	no	Reasoning events/logs.
`warnings`	`List[RunWarning]`	no	What the answer should say without failing the run: `code`, `node`, `message`, `sub_query_id`, `detail`. See Warnings.
`artifact_refs`	`Dict[str, ArtifactRef]`	no	Result artifact references keyed by execution node id.
`status`	`str`	no	`"success"`, `"error"` or `"plan_only"` for the run.
`timings`	`Dict[str, float]`	no	Wall-clock seconds per graph node.
`usage`	`QuestionUsage`	no	LLM calls, input/cached/output/reasoning tokens and model time per node (`nodes`) and for the question (`total`), plus every call (`calls`) and the number of plans served from the plan cache (`plan_cache_hits`). The same model as `QueryResult.usage`; see the core query API.
`trace_path`	`Optional[str]`	no	Where the run's trace file was written on the server, or `null`. See Debugging a Run.
`truncated`	`bool`	no	True when any sub-query's result was cut at its row limit.
`result_manifest`	`List[ResultSummary]`	no	The run's saved results, one per terminal DAG node: `node_id`, `columns`, `row_count` (the whole result's) and `truncated` (a sub-query feeding it was cut at its row limit). Never the rows: read those with the two result routes below, `result_id` being the `node_id`. Empty when nothing was saved (`execute: false`, or the run failed before aggregation).
`source`	`AnswerSource`	no	Where the answer came from: `{"type": "verified", "id", "datasource_id", "params"}` when a verified query from `curated.yaml` answered, with the values it ran with; `{"type": "generated"}` (the other fields `null`) otherwise. The last event of `POST /query/stream` carries it too.

Only a capped sample of the rows is inlined, in sub_queries[].rows. The full result set is written to artifact storage and addressed through artifact_refs (uri, format, row_count, columns). The run's combined results are listed in result_manifest and read back, a page at a time or as CSV, with GET /api/v1/query/{request_id}/results/{result_id} and .../{result_id}.csv.

`ResultPage`

field	type	meaning
`request_id`	`str`	The run's `trace_id`.
`result_id`	`str`	The result's `node_id` from `result_manifest`.
`columns`	`List[str]`	Column names, in row order.
`rows`	`List[List[Any]]`	`rows[i]` is row `offset + i` of the result.
`offset`	`int`	The first row returned, from 0.
`limit`	`int`	The page size used, after the cap of 1,000.
`total_rows`	`int`	Rows in the whole result.
`next_offset`	`Optional[int]`	Where the next page starts, or `null` when this page reaches the end.

An errors[] entry for a key the provider rejected:

{
  "node": "datasource_resolver",
  "message": "OpenAI rejected the API key.",
  "error_code": "PROVIDER_AUTH_FAILED",
  "severity": "ERROR",
  "provider": "OpenAI",
  "provider_response": "HTTP 401 (invalid_api_key): Incorrect API key provided: [redacted key]. You can find your API key at https://platform.openai.com/account/api-keys."
}

Branch on error_code, show message, and keep provider_response for a "provider response" disclosure. The codes are listed under provider failures.

Endpoints

`POST /api/v1/query`

Source: packages/api/src/nl2sql_api/routes/query.py

Request model: QueryRequest

Response model: QueryResponse

Execution flow: - The get_user_context dependency supplies the caller's UserContext (see Caller role); with no role it answers HTTP 401 before the engine runs. - Delegates to engine.run_query(...), which returns a QueryResult. - Returns that QueryResult as a QueryResponse (its subclass); there is no field mapping.

The handler is declared with def, not async def: the pipeline performs blocking LLM and database calls, so Starlette runs it in its threadpool instead of on the event loop.

Errors: - Pipeline failures are a normal HTTP 200 carrying errors; they are not HTTP failures. - Genuinely unexpected failures return HTTP 500 with a generic detail; the traceback is logged server-side rather than returned to the client. - No role for the request returns HTTP 401 with a message naming the settings. - An invalid request body returns HTTP 422 (FastAPI validation).

Example response:

{
  "sub_queries": [
    {
      "id": "sq-1",
      "intent": "top customers by revenue",
      "sql": "SELECT s.customer, SUM(s.revenue) AS revenue FROM sales AS s GROUP BY s.customer ORDER BY SUM(s.revenue) DESC, s.customer LIMIT 5",
      "datasource_id": "warehouse",
      "schema_version": "v3",
      "plan": {"tables": [{"name": "sales", "alias": "s"}]},
      "validation": [
        {"name": "plan_present", "passed": true, "message": "Plan received from the planner"},
        {"name": "structure_and_schema", "passed": true, "message": "Tables, columns and joins resolve against the retrieved schema"},
        {"name": "policy", "passed": true, "message": "Role 'admin' may read every table in the plan"}
      ],
      "rows": {"columns": ["customer", "revenue"], "rows": [["acme", 42]], "total_rows": 5},
      "status": "success",
      "retry_count": 0,
      "plan_source": "llm",
      "row_cap": null,
      "truncated": false
    }
  ],
  "final_answer": {
    "summary": "Acme leads the top 5 customers by revenue, with 42.",
    "format_type": "text",
    "content": "Acme leads with 42. The full list of 5 customers is in the table.",
    "warnings": [],
    "truncated": false
  },
  "errors": [],
  "trace_id": "0f1c...",
  "reasoning": [],
  "warnings": [],
  "artifact_refs": {
    "sq-1": {
      "uri": "file:///artifacts/sq-1.parquet",
      "format": "parquet",
      "row_count": 5,
      "columns": ["customer", "revenue"]
    }
  },
  "status": "success",
  "source": {"type": "generated", "id": null, "datasource_id": null, "params": null},
  "timings": {"ast_planner": 0.02, "logical_validator": 0.001, "generator": 0.006, "executor": 0.038},
  "usage": {
    "total": {"calls": 3, "input_tokens": 13200, "cached_input_tokens": 0, "cache_write_input_tokens": 0,
              "output_tokens": 590, "reasoning_tokens": 0, "total_tokens": 13790, "latency_s": 4.1, "cost": null},
    "nodes": {
      "decomposer": {"calls": 1, "input_tokens": 1900, "cached_input_tokens": 0, "cache_write_input_tokens": 0,
                     "output_tokens": 250, "reasoning_tokens": 0, "total_tokens": 2150, "latency_s": 1.3, "cost": null},
      "ast_planner": {"calls": 1, "input_tokens": 11000, "cached_input_tokens": 0, "cache_write_input_tokens": 0,
                      "output_tokens": 300, "reasoning_tokens": 0, "total_tokens": 11300, "latency_s": 2.2, "cost": null},
      "answer_synthesizer": {"calls": 1, "input_tokens": 300, "cached_input_tokens": 0, "cache_write_input_tokens": 0,
                             "output_tokens": 40, "reasoning_tokens": 0, "total_tokens": 340, "latency_s": 0.6, "cost": null}
    },
    "calls": [
      {"node": "decomposer", "model": "gpt-4o-2024-08-06", "input_tokens": 1900, "cached_input_tokens": 0,
       "cache_write_input_tokens": 0, "output_tokens": 250, "reasoning_tokens": 0, "total_tokens": 2150,
       "latency_s": 1.3, "cost": null, "usage_reported": true, "error": null}
    ],
    "plan_cache_hits": 0
  }
}

(Illustrative numbers. usage.calls is shortened to one entry; a real response lists all three.)

`POST /api/v1/query/stream`

The same run as POST /api/v1/query, reported as it happens, as Server-Sent Events (Content-Type: text/event-stream). The request body, the caller role and the 401 are exactly those of /query. Delegates to engine.stream_query(...) (see the core query API).

The events are the engine's own run events (Events), from the caller-safe allow-list. Each is an event: line naming it and one data: line holding the whole event as JSON: name, at (UTC), trace_id (the run's, its request_id for the result routes), tenant_id and data. The table lists each event's data:

event	data	when
`run.started`	`question`, `user_id`, `tenant_id`, `roles`, `datasource_id`, `execute`	The run began. Always first.
`node.started`	`node`, `sub_query_id`	A pipeline step began. The SQL agent's steps (`ast_planner`, `logical_validator`, `generator`, `executor`) come once per sub-query, and again on a retry.
`node.finished`	`node`, `sub_query_id`, `seconds`, `ok`	The step ended; `ok` is `false` when it raised.
`result.manifest`	`results`	The aggregator saved the run's results. `results` is the `result_manifest` the final response will carry, so a client can start paging the rows (with the event's `trace_id` as `request_id`) before the answer is written.
`answer.delta`	`field`, `text`	The next piece of the answer's `summary` or `content`, as the answer model writes it. Joined in order, the pieces of a field are that field of `final_answer`.
`sanity.flagged`, `run.limit_reached`	see Events	A sanity check's finding; a cost or call cap reached.
`run.finished`	the run's summary, plus `result`: the `QueryResponse`	The last event of a run that finished: `result` is exactly what `/query` returns for it. A refusal or a provider failure is a `run.finished` with `status: "error"`, as `/query` returns it.
`run.failed`	`message` (a key in it is redacted), `error_code`; and `user_id`, `roles` when the engine ended the run	The last event of a run that could not finish (an unexpected failure; the traceback is logged on the server).

Every stream ends in exactly one run.finished or one run.failed. Policy denials, hook decisions and model calls are never on it; they are an operator's, through engine.events.subscribe.

event: run.started
data: {"name": "run.started", "at": "2026-10-15T09:12:03.120Z", "trace_id": "0f1c...", "tenant_id": null, "data": {"question": "Who are the top 5 customers by revenue?", ...}}

event: node.started
data: {"name": "node.started", ..., "data": {"node": "datasource_resolver", "sub_query_id": null}}

event: node.finished
data: {"name": "node.finished", ..., "data": {"node": "datasource_resolver", "sub_query_id": null, "seconds": 0.41, "ok": true}}

...

event: result.manifest
data: {"name": "result.manifest", ..., "trace_id": "0f1c...", "data": {"results": [{"node_id": "sq_1", "columns": ["customer", "revenue"], "row_count": 5, "truncated": false}]}}

event: answer.delta
data: {"name": "answer.delta", ..., "data": {"field": "summary", "text": "Acme leads "}}

...

event: run.finished
data: {"name": "run.finished", ..., "data": {"status": "success", "duration_s": 6.2, ..., "result": {"sub_queries": [...], "final_answer": {...}, "status": "success", ...}}}

curl -N -X POST http://localhost:8000/api/v1/query/stream \
  -H "Content-Type: application/json" -H "X-NL2SQL-Role: admin" \
  -d '{"natural_language": "Who are the top 5 customers by revenue?"}'

What streams token by token: only the answer. The answer model is asked to stream on this route (and on this route only; /query is unchanged), on both wires: OpenAI's (OpenAI, OpenRouter, Ollama and any OpenAI-compatible endpoint) streams the answer's tool call as argument pieces, Anthropic's as input_json_delta pieces, and the engine reads summary and content out of them as they grow. How finely the text arrives is the provider's choice: a provider or a local server that sends the whole answer in one piece gives one answer.delta per field. The other model steps (resolver, decomposer, planner, refiner) return a typed object the next step needs whole, so they are not streamed; their progress is their node.started and node.finished. A streamed answer reports the same token usage as an unstreamed one (stream_options.include_usage is sent on the OpenAI wire).

The route answers Cache-Control: no-cache and X-Accel-Buffering: no, so a proxy passes each event on as it comes. Closing the connection cancels the run before its next step or model call.

`GET /api/v1/query/{request_id}/results/{result_id}`

One page of a run's saved result.

parameter	in	default	meaning
`request_id`	path		The run's `trace_id`.
`result_id`	path		A `node_id` from the run's `result_manifest`.
`offset`	query	`0`	First row, from 0. Negative is `HTTP 422`.
`limit`	query	`100`	Rows per page, at least 1 (else `HTTP 422`). Above 1,000 it is cut to 1,000; `limit` in the response says which.

Response model: ResultPage. Delegates to engine.results.page(...), which reads the rows lazily from the result's parquet artifact: only the slice asked for is read. A page past the end has no rows.

curl -H "X-NL2SQL-Role: admin"   "http://localhost:8000/api/v1/query/0f1c.../results/combine_g1?offset=100&limit=100"

{"request_id": "0f1c...", "result_id": "combine_g1", "columns": ["customer", "revenue"],
 "rows": [["acme", 42], ["globex", 40]], "offset": 100, "limit": 100,
 "total_rows": 102, "next_offset": null}

`GET /api/v1/query/{request_id}/results/{result_id}.csv`

The whole of a run's saved result as text/csv (UTF-8, header first), sent as an attachment named <request_id>_<result_id>.csv. Delegates to engine.results.csv(...), which reads the parquet artifact a slice at a time and streams each slice out, so the result is never built in memory on the server.

Errors and scope of the result routes

Both need a role, as /query does: no role is HTTP 401.
Results are read from the engine's own tenant folder (TENANT_ID, the <tenant_id> of the artifact path) and nowhere else. An unknown request_id, a run of another tenant, a result_id the run's manifest does not list, and an id that is not a plain token (letters, digits, _ and -, so no /, . or ..) are all HTTP 404, so a caller cannot tell one from the other or reach a path outside the run's folder.
There is no access control beyond that yet: any caller with a role who knows a run's trace_id can read its result.

Tests

packages/api/tests/test_query_results_routes.py covers the result routes over the real facade and a local artifact store: paging, the cap, HTTP 422 for a bad offset or limit, the complete CSV, HTTP 404 for unknown, foreign and crafted ids, and HTTP 401 without a role.

packages/api/tests/test_query_routes.py and packages/api/tests/test_query_response_shape.py cover this endpoint with FastAPI's TestClient and a stubbed engine (get_engine dependency override) and a stubbed caller (get_user_context override, role admin), so no datasource, LLM or network access is required. packages/api/tests/test_auth.py covers the role sources and the 401.

packages/api/tests/test_query_stream_route.py covers the stream route the same way: the content type, the events in order, a run.finished whose result equals the /query response, the 401 and the run.failed event. packages/nl2sql/tests/e2e/test_stream_fake_llm.py streams a question on the demo databases through this route with no key.