nvi — analytics agent

Overview

A text-to-SQL analytics agent over a real warehouse — structured so the agent layer is legible, not buried in a framework.

nvi answers business questions over the BIRD financial warehouse (PKDD'99 Czech bank: accounts, transactions, loans, cards, clients, districts, orders, dispositions). It does this by composing a small set of Analyses — each its own mini-agent — orchestrated through a langgraph wiring layer with full Langfuse observability.

The shape mirrors the JD's three building blocks (text-to-SQL, reasoning agents, planners) as distinct layers rather than one omnibus prompt — Plan (L3), Analyses (L2), and deterministic atomic actions (L1) — resting on a recon foundation (L0): the dataset's typed knowledge graph. That gives every part a name and a tight contract, and lets each Analysis pick the simplest reasoning pattern (CoT, ReAct, plan-and-execute) for what it does instead of forcing one over everything.

The load-bearing decision: the LLM never authors SQL. It picks a typed shape from finite, recon-derived sets; the composer walks recon and emits the query deterministically. Column–table binding, joins, and quoting are graph traversals, not model guesses — so a question the system can't express fails cleanly instead of producing plausible-but-wrong SQL.

Architecture

Four layers — Plan composes Analyses; Analyses are mini-agents; atomic actions are deterministic; recon is the foundation they all consult.

Plan (L3): One LLM call that turns a question into an ordered set of Analysis invocations — each with args, an intended interpretation, and an optional fallback. It sees only brief table/metric names and the Analysis catalog, never raw DDL. Stateless: it emits a plan and never observes execution.
Analyses (L2): Self-contained mini-agents. Each picks the simplest reasoning pattern it needs — CoT for a one-shot move, ReAct for ones that iterate over results, plan-and-execute for compound ones — and owns its loop behind a uniform contract, run(args, question) → Finding. The LLM work lives here: choosing a typed Pick and interpreting rows, never writing SQL.
Atomic actions (L1): Deterministic, no LLM. compose(pick, recon) → SQL authors the query by walking recon's join graph; execute_sql runs it read-only with a row cap and timeout. Invalid SQL isn't expressible — the composer binds columns and joins from recon, not from model output.
Recon (L0): The dataset's typed knowledge graph — tables, columns, metrics, relationships, storage encodings — merged from sparse human YAML and extracted DDL. The foundation every layer above consults. (Sometimes called L4: the setup that exists before any run, not a step inside one.)

How the layers interconnect

The layers read general → specific (Plan → Analyses → atomic actions), but execution is a graph, not a one-way cascade. Later steps depend on earlier intermediate results: drill_down loops, choosing its next dimension from the previous slice's rows; a step's fallback Analysis fires based on whether the primary's Finding came back empty or errored. And L0 recon is consulted at every level — the planner reads its brief names, the Pick validates against it, the composer walks it. So the layers are interconnected through both the data they share (recon) and the results they pass forward.

Plan layer

L3 — one LLM call that decides which Analyses to run.

The planner (api/plan/planner.py) gets the user question plus three context blocks rendered from recon: brief table names, brief metric names, and the catalog of Analyses with their args_schema. Deliberately no DDL — it decides which Analyses to run, not how to query. One LLM call returns a structured Plan.

Each step in the plan carries:

analysis — which Analysis to invoke (must be in the registry).
args — the arguments dict, shaped by that Analysis's args_schema.
why — a one-line rationale, surfaced in the live trace.
fallback — an optional alternate Analysis the runtime runs if the primary returns empty or errors.

The planner never calls a tool or touches data — it's a pure composition step. The runtime (below) walks the steps in order.

Analyses layer

L2 — each Analysis is its own mini-agent.

Analyses are the agent layer. Each wraps a piece of analytical intent and decides how to satisfy it — sometimes one LLM call is enough, sometimes a ReAct loop over query results is the right fit. The base class (api/analyses/base.py) enforces only a uniform external contract, run(args, question) → Finding; the internal pattern is a per-Analysis decision and no framework types leak out.

Within an Analysis, a free-form question becomes a typed Pick via one LLM call (pick_for_question) over recon-narrowed candidate sets — that is the only place the model makes a structural choice, and it chooses from finite known sets, not free text. The Pick then goes to the deterministic composer (L1).

Implemented Analyses

direct_answer: CoT, one shot. pick → compose → execute → interpret. For single-query lookups and aggregations.
compare_periods: CoT over two queries. One Pick, cloned with two period filters → compose ×2 → execute ×2 → interpret the pair.
drill_down: ReAct loop. decide_next (LLM) picks a dimension from the candidate list → build Pick → compose → execute → loop on the slice's results. Zero LLM in the slice path itself — only the dimension choice is a model decision, and it's guarded against the candidate list.
find_outliers · correlate: Planned. Additive — new Analyses register without touching the runtime or the layers below.

Atomic actions

L1 — deterministic, no LLM inside. The composer is the SQL author.

There is no LLM in this layer — and notably no text_to_sql tool. An earlier design had the model author SQL with schema-RAG context, sqlglot validation, and retries; it was removed once the composer landed (see Decisions). The two atomic actions are:

compose: compose(pick, recon) → SQL (api/composer/). Builds one sqlglot expression tree from a typed Pick by walking recon: resolve the metric's table, bind each group-by/filter column to its owning table, compute join paths, render filters and dates, emit quoted SQL. Split by concern — aliases, joins, filters, metrics, dates, order, encodings. If a Pick can't be expressed against recon it raises PickValidationError; it never fabricates SQL.
execute_sql: execute_sql(sql) → rows (api/tools/execute_sql.py). Runs read-only against Postgres (SELECT/WITH only) with a row cap and a statement timeout, returning columns + rows + truncation metadata.

Because column–table binding and joins come from recon rather than model output, the composer cannot emit SQL that references a column that doesn't exist. The validator that used to catch the model's mistakes after the fact is demoted to a paranoid self-check on the composer.

Recon

L0 — the dataset's typed knowledge graph, and the only dataset-specific surface.

Recon (api/recon/) is the foundation every other layer consults. It's built in two stages and cached as recon.json:

DDL extraction — SQLAlchemy introspects Postgres for tables, columns, types, nullability → extracted_schema.json (auto, gitignored).
Augmentation merge — sparse human YAML in api/datasets/<name>/ (schema_docs.yaml descriptions + relationships, metrics.yaml SQL fragments, optional encodings) merges onto the extracted schema.

The merged Recon exposes the queries downstream code needs without going back to the model: resolve_column (bare or table.col → owning table), join_path (shortest table sequence between two tables), the metric catalog, and storage encodings (e.g. how a YYMMDD-int column coerces to a date). Rebuild with make recon after changing the warehouse or the YAML.

Everything dataset-specific lives here. The Plan, Analyses, and atomic actions reference no dataset name — point them at a different api/datasets/<name>/ and the same machinery answers questions over a new warehouse.

Runtime

langgraph wires the graph; nvi's domain code stays legible on its own.

The orchestration is built on langgraph — it's the right tool for the graph wiring, parallel execution, and streaming state we need, and reaching for a custom DAG runner here would be reinventing the wheel.

The intent is the opposite, though: nvi's domain code (Plan, Analyses, Tools) presents a clean surface that's readable on its own terms. Understanding what nvi does should not require first learning langgraph's API. The framework wires nodes; nvi's code defines what the nodes are, and that's where the interesting reading is.

Concretely the graph is small and linear:

START → plan → execute → synthesize → END

plan runs the planner; execute walks the plan steps, dispatching each to its Analysis (and to a fallback Analysis when the primary returns empty or errors); synthesize folds the Findings into one answer. The whole run streams SSE events (run_start, plan_ready, analysis_start/end, tool_call_start/end, analysis_fallback) that drive the live trace UI, and every LLM call opens a named Langfuse generation span.

api/runtime/runner.py is the one place langgraph appears — StateGraph is imported there and nowhere else. api/plan/, api/analyses/, api/composer/, and api/tools/ contain the domain code, and none of those files expose langgraph types in their public interfaces.

Run it locally

Prerequisites: lng Langfuse cluster reachable, soleprint-ui framework propagated by spr, .env with Anthropic + Langfuse keys.

make install        # uv sync
make kind           # create kind-nvi cluster
make tilt-up        # bring up postgres, api, ui, docs, gateway
make seed           # load BIRD financial into postgres

open http://nvi.local.ar

*.local.ar resolves via dnsmasq; the system Caddy at ~/wdir/ppl/local/Caddyfile proxies nvi.local.ar to the kind NodePort. Reload after first install: sudo systemctl reload caddy.

Architecture decisions

The LLM never authors SQL. It picks a typed shape from finite, recon-derived sets; the composer walks recon and emits SQL deterministically. Schema-in-the-prompt is a suggestion the model can ignore; composition makes it a constraint. The trade is fewer question shapes for correctness by construction. (See def/schema-as-constraint.md.)
Four layers, not one omnibus prompt. Plan composes Analyses; each Analysis picks its own reasoning pattern (CoT / ReAct / plan-and-execute); atomic actions are deterministic; recon is the shared foundation. Borrowing elements from agent frameworks without committing the whole system to one.
langgraph for orchestration, nvi for domain. Framework owns graph wiring and streaming; nvi's plan/, analyses/, tools/ own the interesting code and are readable without reading langgraph.
BIRD financial as the warehouse. Fits Nivii's stated Finance vertical; ships with golden SQL pairs so the eval set is free and verifiable.
Langfuse hosted independently by the lng project; reached over WireGuard. Same env var works in dev and any future deploy.
Postgres dedicated to nvi. Persisted via .data/postgres hostPath mount so the BIRD load survives cluster restarts.

Evals

BIRD financial dev set, run by api/evals/run_evals.py (make evals), traces pushed to Langfuse.

BIRD ships golden question/SQL pairs, so the eval set is free and verifiable. Each run replays questions through the full stack (plan → analyses → compose → execute) and records the trace in Langfuse for inspection — token usage per layer, which Analysis fired, the composed SQL. The composer refactor's own check is the unit suite (uv run pytest): golden SQL strings plus validation rejections, so structural correctness is pinned independent of the model.