Wave D: the apps (API v1, web app, Compose, terminal app) (design)

Principles

The owner set them for the redesign, and they rank above everything below:

• The package is the product; the playground only demonstrates it. The web app talks only to nl2sql-api's public /api/v1 routes. The terminal app talks only to the Python facade. Neither holds feature logic: a chart is chosen by engine.results.chart, a dashboard by engine.dashboards.generate, a follow-up by engine.conversations.ask. Every gap this page found is a facade method plus a route, listed explicitly.
• No active users: correct, not compatible. Routes are renamed where their names are wrong, the playground's private server is deleted, and nl2sql demo keeps no flag whose behaviour is gone. No aliases.
• Never over-engineer. One process, one worker, jobs in memory, one Docker image, one Compose service, plain React and plain CSS. The apps expose what Waves A to C build and nothing they cut. The cut list is part of the design.
• Both apps have a demo mode and a your-data mode. Demo mode is the sample databases with recorded answers and no key; your-data mode is the caller's nl2sql.yaml.
• The UI must not look AI-generated. The playground's "Porcelain" design system (web/playground/src/styles.css: its tokens, type scale, the run spine, the raised card, the 44px controls) is the baseline for every new page. No component library, no gradient, no icon font.

Decisions

The owner was asleep when this design was written and asked that its recommendations be used, each marked for confirmation. Wave A uses D9 to D35, Wave B D36 to D61 and Wave C D100 to D130, so Wave D starts at D200. After an independent review several were amended; those are marked so. The final redesign's decisions D13 to D16 are cited by the brief for this wave; their numbers collide with Wave A's, so this page calls them final-design D13 to D16 and records how each is applied.

Where 0.9 will leave us

What the code has today (0.6), plus what Waves A to C add, as their designs describe them.

1. Goals and non-goals

What ships

Not in Wave D

2. Order of work

flowchart LR
  CI["CI0. Web tests in CI"]
  CI --> DB["D0b. Web foundation"]
  DA["D0a. API foundation<br/>and engine shapes"]
  DA --> G["Engine gaps<br/>G1 B13 and ratings<br/>G2 overrides, replay, demo<br/>G3 approvals, progress, readiness"]
  DA --> DB
  G --> A["API routes<br/>A1 to A4, one router each"]
  A --> W["Web app<br/>W1 to W4, one area each"]
  DB --> W
  G --> T["T1. Terminal app"]
  DA --> K["K1. Image and Compose"]
  W --> X["X1. Cutover, rehearsed<br/>on a scratch Space"]
  K --> X
  X --> R["R1. Owner re-records,<br/>rehearses again"]
  R --> REL["Release"]
  T --> REL

3. The API: /api/v1 at parity

Route conventions

The error envelope

# nl2sql_api/errors.py
class ApiError(BaseModel):
    code: str                     # an NL2SQLError's code, or NOT_FOUND, INVALID_REQUEST,
                                  # UNAUTHENTICATED, FORBIDDEN, CONFLICT, LIMIT_REACHED, NOT_READY, INTERNAL
    message: str                  # one sentence a person can act on
    fix: str | None = None
    details: list[dict] = []      # 422: one entry per problem, {"loc": [...], "msg": ...}; never a value
    request_id: str | None = None # 500 only: the id in the server log

class ErrorBody(BaseModel):
    error: ApiError

One base class (D202). The engine gains nl2sql.NL2SQLError(Exception) with code, message, fix and status (the HTTP status it means: 404, 409, 422 or 403). The facade's named exceptions derive from it as well as from the built-in they are today, so a Python caller's except LookupError still works:

A pipeline outcome is not an error: POST /query answers 200 with a QueryResult whose status is error, needs_clarification, needs_approval or stopped, as today. D0a removes the per-route except Exception blocks of the old routes so the one handler applies to every route from then on. test_no_route_returns_exception_text raises, through every registered route with a stub engine, a ValueError, a pydantic.ValidationError (with an input_value), a KeyError and a PermissionError, each carrying a fake connection string, and checks that each is a 500 whose body holds no part of it (B200).

Identity and auth

nl2sql_api/identity.py replaces auth.py. One dependency, caller, resolves the request's UserContext; admin adds the role check.

# nl2sql.yaml (the api section; the engine validates it and never reads it)
api:
  auth: api_keys              # api_keys | proxy | static | none (default: none)
  proxy:                      # auth: proxy only
    trusted_from: [127.0.0.1/32, "::1/128"]   # CIDRs the proxy connects from
    user_header: X-Forwarded-User
    tenant_header: null
    roles_header: X-NL2SQL-Roles
  static_role: analyst        # auth: static only
  request_llm: false          # callers may send their own key and models (D210)
  cors_origins: []

The hosted demo's anonymous sessions are not a value of api.auth (D203): serve --demo --hosted installs them itself (The hosted demo). api.trust_body_role, api.role_header and api.role, and the body's user_context, are deleted. The engine's Api model (config/models.py) carries the new fields so nl2sql config check reports a typo.

Results bound to the user (B13)

B13 is fixed in the engine (D204), so the facade, the API, MCP and the terminal app all get it:

1. The result writer records the run's tenant_id and user_id in the result manifest (execution/artifacts/results.py), beside the columns it already records; the trace document records them too.
2. engine.results.manifest, page, csv and Wave C's chart take user_context=None (replacing chart's tenant_id); with a context, they compare it with the stored pair, and a mismatch raises ResultNotFound. engine.traces.get(trace_id, *, user_context=None) does the same and returns None.
3. user_context=None is the trusted in-process operator: it reads any run of the engine's tenant, as nl2sql trace <id> (cli/commands/trace.py) and nl2sql run need, and as Wave A's D24 trusts the context an in-process caller passes.
4. Every app passes the caller. test_apps_always_pass_the_caller scans nl2sql_api/, nl2sql/mcp/ and nl2sql/cli/chat/ (AST) for calls of results.*, traces.get and results.chart without user_context.
5. Wave C's built-in chart tool and dashboard runs pass the tool's or the tile's caller (nl2sql/chat_tools/chart.py, nl2sql/dashboards/).
6. Results written before the change have no owner and are read only by None; the release notes say so.

test_another_users_result_is_not_found, test_another_users_trace_is_not_found, test_cli_trace_reads_any_run, test_ownerless_result_is_read_only_by_the_operator (engine) and test_results_route_hides_another_users_rows (API).

Streams

nl2sql_api/sse.py is the one place an SSE response is built:

def event_stream(open_events: Callable[[], Iterator[Event]], request: Request,
                 *, token: CancellationToken | None = None) -> StreamingResponse

The streams: POST /query/stream (Wave A's caller-safe allow-list, ending run.finished with data.result), POST /conversations/{id}/turns/stream (Wave C's run events with conversation.rewritten, ending conversation.turn), and GET /jobs/{id}/events, which is an async generator over the in-memory job and holds no thread.

Jobs

# nl2sql_api/jobs.py
JobKind = Literal["index", "profile", "eval", "dashboard"]

class JobProgress(BaseModel):
    at: datetime
    step: str                    # "chinook", "case 12 of 80", "tile 3 of 8"
    done: int
    total: int | None

class Job(BaseModel):
    id: str                      # 22 url-safe characters
    kind: JobKind
    status: Literal["queued", "running", "succeeded", "failed", "cancelled"]
    params: IndexJob | ProfileJob | EvalJob | DashboardJob   # the request body, typed per kind
    created_by: str | None       # the caller's user_id
    created_at: datetime
    started_at: datetime | None
    finished_at: datetime | None
    progress: list[JobProgress]
    result: IndexResult | list[ProfileReport] | EvalReport | DashboardCreated | None   # typed per kind
    error: ApiError | None

IndexResult is the index build's report; DashboardCreated is the new dashboard's id and its first DashboardRun. Each lands in openapi.json, so the web app reads a typed eval report.

test_jobs_run_one_at_a_time_in_order, test_job_progress_replays_then_follows, test_job_events_hold_no_worker_thread, test_cancel_running_job_stops_between_items, test_second_job_for_a_caller_is_a_conflict, test_two_users_generate_for_one_datasource, test_another_users_job_is_not_found, test_eval_job_is_admin_only, test_api_runs_one_worker.

Health and readiness

nl2sql-api no longer refuses to start when a required datasource fails: it starts, /ready is 503, and the reason is one admin request away, which an orchestrator's restart loop would otherwise hide.

test_ready_fails_on_a_required_datasource, test_ready_passes_with_an_optional_failure, test_ready_fails_on_a_missing_index, test_ready_passes_with_request_keys, test_ready_details_only_for_admin, test_meta_without_a_caller_lists_no_datasource.

The routes

Every route below is new unless marked. "Admin" is the admin dependency; everything else needs a caller. A name in bold in the facade column is a gap (section 11). caller is the request's UserContext.

Questions, results, traces and feedback (routes/query.py)

The answer view's diagram is the conversation's diagram tool; there is no /diagram/{trace_id} route.

Approvals (routes/approvals.py, D215)

Conversations and chat tools (routes/conversations.py; every Wave C method takes user_context=caller)

A busy conversation is a 409 (ConversationBusy). A turn with status needs_clarification is answered with choice (an option id) in the next turn's body (Wave C's D104).

Dashboards (routes/dashboards.py; D224; every method takes user_context=caller)

Datasources, Connect, index and drift (routes/datasources.py, routes/index.py)

Knowledge and proposals (routes/knowledge.py)

The review file (proposals.export and apply) stays a CLI workflow: a file in git is its point. Wave C's D121 has no draft route; drafting is the profile job with draft: true, and its output is proposals.

Evals (routes/evals.py; admin)

evals.gate, regenerate and seed stay in the CLI: they write files or compare with a baseline file in the repository.

Settings, keys and events (routes/settings.py)

Approvals over REST

Wave A's approval holds nothing: the app that asked keeps the ApprovalRequest, and the approver's Approval names who approved. In process that works because one program sees both people. Over HTTP the approver and the asker are two callers, at different times, on different connections: the asker's request cannot prove that someone else approved, and the approver must not re-run the question as the asker (Wave A binds the run to the asker). So D215 keeps the grant in the engine, keyed by the request's id:

# engine.approvals (state store, beside the review queue); the caller is required (D204)
def list(self, *, user_context, status="pending") -> list[ApprovalRecord]: ...
def grant(self, request_id: str, *, by: UserContext) -> ApprovalRecord: ...
def deny(self, request_id: str, *, by: UserContext, reason: str | None = None) -> ApprovalRecord: ...

In the web app the asker sees "Waiting for approval" with the reason; an approver sees the request under Approvals (the plan in English, the SQL, the reason) and grants it; the asker's Run it now calls retry with the id.

The replay provider

Demo mode must answer with no key, through the same pipeline a key would use (D209):

# llm.yaml in the demo project
provider: replay
recordings: null        # null: the recordings packaged with the engine (nl2sql/datasets/recordings/)

Keys and models in the web app

D210 makes one rule for every mode: a key typed into the browser lives in that tab's sessionStorage and is sent with each request; the model per step likewise. The hosted page already works this way (web/playground/src/hostedKey.js, hostedModels.js); the local page's "save key to .env" and "write models to llm.yaml" go. The cost is that a local user types the key once per tab (or puts it in .env once); getting_started/demo.md says so.

nl2sql_api/overrides.py (in D0a) turns the headers into the engine's LLMOverride(keys={provider: key}, models={agent: (provider, model)}) and passes it as llm_override= (a new keyword, so it never means two types beside Wave A's evals.run(llm=)) to run_query, stream_query and conversations.ask, stream and retry.

test_request_key_headers_refused_without_request_llm, test_model_override_without_request_key_is_422_even_when_server_has_key, test_request_key_never_in_a_response_log_or_trace.

Saving a datasource (final-design D14)

Connect tests a connection, registers it with datasources.add, and saves it only through Wave C's writer (D212):

4. Serving the web app and nl2sql demo

nl2sql-api serve

nl2sql_api/serve.py, run as nl2sql-api serve (the console script, replacing server.py) or python -m nl2sql_api serve:

nl2sql-api serve [--config PATH] [--host 127.0.0.1] [--port 8000]
nl2sql-api serve --demo [--dir nl2sql-demo] [--host] [--port]
nl2sql-api serve --demo --hosted [--dir] [--host] [--port] [--questions-per-minute 6] [--questions-per-session 30]

The page, its chunks, the social card and the clips are package data of nl2sql_api (web/static/, web/assets/), served at /, /assets/, /social-card.png and /clips/{name} (routes/web.py). Any other path under / that is not a file answers the page, so the hash router owns navigation.

nl2sql demo, a launcher (D211)

cli/commands/demo.py shrinks to about 40 lines. It checks that nl2sql_api is installed (importlib.util.find_spec), else prints pip install "nl2sql-engine[demo]" and exits 1. It then starts [sys.executable, "-m", "nl2sql_api", "serve", "--demo", ...] with subprocess.Popen, opens the browser unless --no-browser, and waits; a KeyboardInterrupt is forwarded to the child (CTRL_BREAK_EVENT on Windows, SIGINT elsewhere) and the launcher exits with the child's code. Its flags are --dir, --host, --port and --no-browser. --hosted and the limits are gone from it (the Space runs nl2sql-api serve directly), and so are --record, --api-key and --allow-settings, with no alias: recording is a script, a key is typed into the page or put in .env, and the settings gate is D205's loopback rule.

The engine's pyproject.toml replaces demo = ["fastapi", "uvicorn"] with demo = ["nl2sql-api~=X.Y.Z"] (the release-please version marker, as nl2sql-api pins the engine), and drops the package data for nl2sql.cli.demo and nl2sql.cli.demo.playground. The two distributions now depend on each other; publish_pypi.yaml uploads nl2sql-api before or with the engine, so pip install "nl2sql-engine[demo]" never resolves in between, and docs/development/releasing.md says so. The recordings move to nl2sql/datasets/recordings/, beside the sample databases they answer. Wave C's nl2sql launch ends by printing a command that serves the project; that line becomes nl2sql-api serve --config DIR/nl2sql.yaml.

Demo mode and your-data mode

The hosted demo

The Space keeps its URL, its limits and its keyless start (D221):

deploy/huggingface/Dockerfile installs nl2sql-engine[demo] (which brings nl2sql-api), builds the demo project and index as today, and runs nl2sql-api serve --demo --hosted --dir /home/demo/demo --host 0.0.0.0 --port $PORT .... Its healthcheck and scripts/fresh_install_check.py move to /api/v1/health, /api/v1/meta, /api/v1/datasources/{id}/schema (with a session) and /api/v1/index; publish_pypi.yaml's space-smoke and test.yml's fresh-install keep their shape. The cutover is rehearsed on a scratch Space before the release (X1).

5. The web app

Architecture

flowchart LR
  subgraph Browser
    P["Pages: Home, Chat, Dashboards,<br/>Connect, Knowledge, Evals, Settings,<br/>Pipeline, Retrieval"]
    C["src/api/*.js<br/>one file per area"]
    P --> C
  end
  C -->|"JSON, SSE"| API["nl2sql-api /api/v1"]
  API --> F["NL2SQL facade"]
  T["nl2sql chat (Textual)"] --> F

Who owns which existing file

D0b moves what several areas share into src/lib/ and src/components/, so no W-PR deletes a file another imports:

Pages

Charts, diagrams and reports

Testing the web app

6. Docker Compose self-host

The image

packages/api/Dockerfile, one image (D213):

• python:3.12-slim; Microsoft's package repository for msodbcsql18 and unixodbc with ACCEPT_EULA=Y;
• nl2sql-engine[postgres,mysql,mssql,duckdb,anthropic] and nl2sql-api from the repository;
• the three sample databases, written at build time by nl2sql init --demo /opt/nl2sql/samples, and the local embedding model fetched into the image's cache, as the Space's image does;
• a non-root user, ENV NL2SQL_CONFIG=/etc/nl2sql/nl2sql.yaml;
• CMD ["nl2sql-api", "serve", "--host", "0.0.0.0", "--port", "8000"], one worker;
• HEALTHCHECK on /api/v1/ready.

test.yml's api-image job builds it for linux/amd64, imports both packages, runs pyodbc.drivers() to see the SQL Server driver, and records the image size in the job summary. K1 checks the arm64 package before any arm64 image is published (D213).

deploy/compose/

# deploy/compose/docker-compose.yml (shape)
services:
  api:
    image: nl2sql-api:local
    build: { context: ../.., dockerfile: packages/api/Dockerfile }
    env_file: .env
    volumes: [ "./config:/etc/nl2sql:ro", "nl2sql-data:/data" ]
    ports: [ "8000:8000" ]
    healthcheck:
      test: ["CMD", "python", "-c", "import urllib.request; urllib.request.urlopen('http://127.0.0.1:8000/api/v1/ready', timeout=4)"]
      interval: 30s
      start_period: 30s
volumes:
  nl2sql-data: {}

docs/deployment/compose.md (new) is the page: up, index, the first key, the web app with the key, connecting your database, backups of the volume, upgrading.

7. The terminal app: nl2sql chat

nl2sql chat [--config PATH | --demo [--dir nl2sql-demo]] [--as ROLE] [--user NAME] [--datasource ID]

8. Capability parity

The terminal app is a chat app; the CLI's commands already cover the admin work, so it does not repeat them.

9. Bugs Wave D fixes

10. The registry and config after Wave D

11. Needs from Waves A to C

Wave D uses their shapes as their designs name them now (Wave A and Wave B as merged, Wave C as amended); where a built name differs, Wave D follows the built name. The first table amends decisions of those waves; the second lists the gaps, each a facade change with its PR.

Amendments to earlier waves

Gaps

Used as stated

12. Boundary and invariant changes

13. Testing

14. PR plan

Each PR is written test-first and updates the docs it affects in the same PR; a new docs page gets its mkdocs.yml nav entry in the PR that adds it. At most four PRs are open at once. No test calls a real model; no agent uses a key.

flowchart TD
  D["0. This design"] --> CI0["CI0. Web tests in CI"]
  D --> D0a["D0a. API foundation, engine shapes"]
  CI0 --> D0b["D0b. Web foundation"]
  D0a --> D0b
  D0a --> G1["G1. Results bound to the user, ratings"]
  D0a --> G2["G2. Overrides, replay, demo"]
  D0a --> G3["G3. Approvals, progress, readiness"]
  G1 --> A1["A1. Query, results, traces, feedback, approvals"]
  G2 --> A1
  G3 --> A1
  G1 --> A2["A2. Conversations, tools, dashboards"]
  G2 --> A2
  G3 --> A2
  G2 --> A3["A3. Datasources, Connect, index, drift, knowledge"]
  G3 --> A3
  G2 --> A4["A4. Evals, settings, keys, meta, ready, serve, hosted"]
  G3 --> A4
  A1 --> W1["W1. Chat"]
  A2 --> W1
  D0b --> W1
  A1 --> W2["W2. Outputs and Dashboards"]
  A2 --> W2
  D0b --> W2
  A3 --> W3["W3. Connect, Knowledge, review, drift"]
  D0b --> W3
  A4 --> W4["W4. Evals, Settings, Home, Pipeline, Retrieval"]
  D0b --> W4
  G1 --> T1["T1. nl2sql chat"]
  G2 --> T1
  G3 --> T1
  D0a --> K1["K1. Image and Compose"]
  W1 --> X1["X1. Cutover, rehearsed"]
  W2 --> X1
  W3 --> X1
  W4 --> X1
  K1 --> X1
  X1 --> R1["R1. Owner: re-record, rehearse, browser pass"]
  R1 --> REL["Release"]
  T1 --> REL

The waves, with at most four PRs at once:

Known overlaps, kept small:

• public_api.py and nl2sql/__init__.py: G1, G2 and G3 each add methods or exports; they land in wave order and rebase.
• api/query_api.py: G2's llm_override and G3's stored requests and ids, in separate functions; nl2sql/conversations/api.py: G2's llm_override, G3's retry ids.
• packages/api/openapi.json and web/app/src/api/fixtures/: every A-PR regenerates them. The A-PRs merge one at a time; each rebases and regenerates with the command test_openapi_snapshot_is_current prints, and each writes only its own fixture files.
• The engine's pyproject.toml: G2's package data, T1's extra, X1's demo extra.
• web/app/package.json: W2 adds mermaid; nobody else adds a dependency.
• .github/workflows/test.yml: CI0, then D0b's path change, then K1's image job; never in the same wave.

15. Release

• release-please opens the release PR as usual. Releasing is the owner's act (see Releasing); no agent merges it.
• docs/releases/X.Y.Z.md is written with the release-notes process. Its breaking-changes table covers every renamed or removed route (section 3, /llm/{agent} among them, no aliases), the error envelope, api.auth replacing role_header, role and trust_body_role, results bound to their asker (ownerless old results), feedback.record's new signature, nl2sql demo's removed flags, the engine's demo extra now installing nl2sql-api, the state store's new version, the API starting when a required datasource fails, and /ready failing where it used to pass.
• Before tagging, the owner checks the scratch Space (keyless and with a key), runs the Compose stack with a key, connects the test bed's PostgreSQL through the web app, asks three follow-ups with a chart and a pin, generates a dashboard, runs the Chinook evals in plans mode, and opens nl2sql chat --demo. After the release deploys, the live Space is checked again.

16. Open questions

None blocking. Every decision above is marked for the owner to confirm. Five are worth a look first, because they change what a user or an operator sees: D211 (the web extra moves to the engine's demo extra), D210 (a key typed into the web app is never saved, locally too), D215 (approval grants are stored, amending Waves A and C), D221 (what the hosted demo leaves on) and D224 (dashboards stay at Wave C's surface).