Server-side data (SQL)

Compiles UDI grammar transformation pipelines to SQL and executes them against a configured database (StarRocks, DuckDB), so data can live on the server instead of being loaded into the browser. This is the backend half of UDI’s “server-side data” mode.

This document explains how it works (for collaborators) and how to integrate it (for downstream consumers embedding the chat or the agent library).

1. The big picture: two compilers, one grammar

A UDI visualization spec has three parts — source, transformation, representation. The transformation array is a linear pipeline of tagged ops (groupby, rollup, binby, join, derive, filter, orderby, kde). That pipeline is executed by one of two interchangeable compilers that share the same grammar and must produce the same results:

	Compiler A (client)	Compiler B (server)
Location	`packages/grammar/DataSourcesStore.ts` (`PerformDataTransformations`)	`packages/agent/src/udiagent/query/` (this module)
Engine	Arquero over in-browser tables	SQL over a database connector
Mode	interactive — live per-brush-tick filtering	remote (non-interactive) — round-trip per committed interaction
Expressions	`Expr` AST → Arquero (`exprToArquero.ts`)	`Expr` AST → SQL (`query/expr.py`)

Compiler A is the reference semantics. Compiler B must match it. Parity is enforced by golden tests: packages/grammar/scripts/gen-parity-goldens.mjs runs representative specs through the real Arquero executor headless and writes packages/agent/tests/goldens/parity.json; packages/agent/tests/test_query_parity.py replays them through this SQL engine (on DuckDB, and on live StarRocks when UDI_STARROCKS_TEST=1) and asserts identical rows. Change transformation semantics → regenerate the goldens.

The client chooses its compiler per data package: a package backed by a server declares itself remote, and the toolkit routes queryData through a QueryBackend that POSTs to this server (packages/grammar/queryBackend.ts) instead of running Arquero.

2. Module layout

query/
  expr.py         Expr AST → SQL (mirrors exprToArquero.ts, same dispatch caveats)
  compiler.py     DataTransformation[] → one CTE-chain SQL statement (PipelineCompiler)
  connectors.py   DuckDBConnector / StarRocksConnector + per-dialect quoting/median
  engine.py       QueryEngine — runs a spec, returns the client-facing result shape
  kde.py          Gaussian KDE post-processing (no SQL equivalent)
  introspect.py   Backend → dataSchema/dataDomains + MetadataCache (TTL)
  errors.py       UnsupportedQueryError

2.1 `PipelineCompiler` (`compiler.py`)

Walks the transformation array and emits one SQL statement built as a CTE chain — each op becomes a CTE that reads the previous one. It tracks a small amount of state to mirror Arquero exactly:

groupby is deferred — consumed by the next rollup (GROUP BY) or by a derive containing aggregates (PARTITION BY window).
orderby is tracked as state; window functions (rank, rolling) and the final SELECT use it.
rollup ops map to SQL aggregates; frequency = count normalized by the grand total; median is dialect-specific.
binby computes bin edges from the unfiltered pipeline prefix (a probe query with named filters skipped) so brushing doesn’t shift histogram bins — bin math is a verbatim port of Arquero’s op.bins.
derive/filter carry the structured Expr AST (never raw Arquero strings — those are rejected). Compiled by expr.py, which validates at the boundary: unknown ops/aggregates/windows raise; literals are bind parameters, never interpolated.
Named filters ({filter: {name, source, entityRelationship?, match?}}) resolve against the request’s selections. Same-entity → a predicate; cross-entity → a semi-join on entityRelationship (targetKey IN (SELECT originKey FROM source WHERE pred)); match: 'all' → GROUP BY originKey HAVING count(matching) = count(total); match: 'none' → targetKey NOT IN (SELECT originKey FROM source WHERE pred AND originKey IS NOT NULL), which drops rows with a null key. The subquery always reads the source as loaded, never a pipeline intermediate. A semi-join whose source is the filtered table itself, keyed on a subject column both sides, is a per-subject scope: it keeps every row of each subject that matches. An empty point value-list means no constraint on that field (matches Arquero), not “match nothing”.
only selects one marginal of a pre-aggregated cube: the rows where exactly the named dimensions are populated and every other dimension is null. The complement comes from the entity’s configured udi:dimensions (see §3.1), not from the spec — so {"only": ["sex"]} compiles to sex IS NOT NULL AND race IS NULL AND age IS NULL without the caller knowing the cube’s shape. {"only": []} is the grand-total row. Pass dimensions inline to use it against an entity with no configured cube roles; with neither, the query fails loudly rather than returning a wrong marginal.
kde must be the last op; the engine post-processes its rows in Python. It fetches only the kde field and the group columns, and samples is clamped to 512.

Filtering a cube. A prepended predicate cannot work: a per-sex chart reads the marginal where race IS NULL, so race IN (…) on top of it is unsatisfiable and returns nothing. Filtering means reading a different marginal and re-aggregating — expand → filter → contract:

[
  { "only": ["sex", "race"] },
  { "filter": { "name": "race-brush" } },
  { "groupby": ["sex"] },
  { "rollup": { "cnt": { "op": "sum", "field": "cnt" } } }
]

The chat builds this automatically for cube sources (features/dashboard/utils/cubeFilters.ts). Contraction is exact for additive measures and min/max only; means, medians and distinct counts cannot be re-aggregated from cells, and the chat declines to filter such a visualization rather than render a plausible wrong number.

2.2 `QueryEngine` (`engine.py`)

QueryEngine(connector, table_map, row_cap=5000, entity_schemas=None, package_extras=None).

table_map: entity name → physical table/view name (entity names may contain spaces; table names are SQL-safe).
entity_schemas: per-entity metadata that can’t be introspected from the database — merged into the resource by introspect() (see §4). primaryKey/foreignKeys land in the schema and drive the chat’s cross-entity filtering; descriptions are what the LLM reads; the cube roles (udi:cube, udi:dimensions, udi:measures, udi:measure_aggregations) land on the resource and drive only, as does udi:linked_dimensions, which drives the chat’s filtering between cubes and line-level tables.
package_extras: package-level descriptor keys, likewise un-introspectable — merged into the package descriptor by introspect(). Today that is udi:prefer, which tells the agent whether a package holding both line-level tables and cubes answers from the line-level tables or the cubes by default. It cannot replace a key introspection sets (name, udi:path, resources).

run_query(source, transformation?, selections?, display_data_only?, offset?) returns the client-facing result:

{
  "displayData": [ ...rows... ],      // the filtered/brushed result
  "isSubset": true,                    // pipeline CONTAINS a named filter (Arquero parity)
  "aggregated": true,                  // rollup/kde output (a "cube"), vs row-level
  "extent": [ ...rows... ],            // OPTIONAL: unfiltered rows for stable axis domains
  "truncated": { "cap": 5000, "sampled": false }  // OPTIONAL: row-level result was capped
}

display vs extent mirrors the toolkit’s displayData/allData: the extent pass (named filters skipped) is only run when the pipeline has a named filter and the caller didn’t opt out. display_data_only defaults to true when the pipeline ends in a rollup (the aggregate’s unfiltered twin is rarely consumed).
Row cap: aggregated results are small by construction and never capped; row-level results are limited to row_cap and flagged truncated. A kde reads at most 50,000 input rows (whichever the backend returns first) and is flagged truncated with that cap when it hits it. offset pages past the cap (“load more”) — deterministic only when the spec orders by a unique key.

run_batch(queries, selections) runs many specs in one call and returns { vizId: result }. Per-viz error isolation: a spec that fails (e.g. an unsupported construct) yields { "error": "..." } for that vizId only; the rest of the batch still returns.

2.3 Connectors (`connectors.py`)

Each connector exposes execute(sql, params) -> list[dict] plus a dialect (identifier quoting, bind placeholder, median). Both DB drivers are optional extras (lazy-imported).

DuckDBConnector(database=":memory:", views={entity: csv_path}) — in-process DuckDB; views registers CSV/Parquet files as views. Doubles as the parity test backend. Extra: duckdb.
StarRocksConnector(host, port=9030, user="root", password="", database=None) — MySQL wire protocol via pymysql; backtick quoting, %s placeholder, PERCENTILE_APPROX median. The connection is long-lived and self-heals (ping-with-reconnect + one retry, lock-serialized for the FastAPI threadpool). Extra: starrocks.

2.4 Introspection (`introspect.py`)

introspect(engine, package_name) produces {dataSchema, dataDomains} in exactly the shape the browser used to compute from CSVs — so the orchestrator and chat need no changes. Per table: DESCRIBE + one stats pass (COUNT(*), per-column cardinality, numeric min/max) + one DISTINCT query per categorical with at most 500 distinct values (DEFAULT_DISTINCT_CAP, as many as a chart can draw, so a filter on one can list them all). Cardinality is exact below 100k rows, approximate (APPROX_COUNT_DISTINCT) above. MetadataCache wraps it with a TTL.

Foreign keys and primary keys are merged in from entity_schemas, because the database stores no FK constraints — this is what makes cross-entity filtering work in remote mode. So are descriptions and titles: a schema’s description and title become the resource’s, and its fields ([{name, description, title}]) fill in the matching introspected columns’ description and title and their domains’ fieldDescription. Titles are the labels the chat shows for a table and its columns. A schema’s "udi:hide_count": true lands on the resource too, keeping that table’s row count out of the chat.

3. Integration: standing up a query backend

3.1 Configure backends (`UDI_QUERY_BACKENDS`)

Set the env var to a JSON file mapping package name → backend spec. The reference server loads it at startup (server/app.py _load_query_engines). The key default (or a package with no explicit match) serves requests whose package doesn’t match a configured one. A host on another web framework builds its own {package: QueryEngine} and serves the two endpoints with udiagent.api.metadata and udiagent.api.query.

{
  // Simple single-table package — what the DuckDB quickstart seeds
  // (sample-data/penguins, in the repo).
  "penguins": {
    "type": "duckdb", // or "starrocks"
    "database": "/abs/penguins.duckdb", // duckdb: path to the seeded file
    "tables": {
      // entity name → physical table
      "Penguins": "penguins",
    },
    "rowCap": 5000, // optional (default 5000)
  },

  // Multi-table package: a StarRocks connection + `schemas` so the compiler
  // can resolve cross-entity brush filters. The seeders carry these
  // foreignKeys/primaryKey over from the directory's datapackage.json — the
  // database itself stores no FK constraints.
  "my_cohort": {
    "type": "starrocks",
    "connection": {
      // starrocks: pymysql kwargs
      "host": "127.0.0.1",
      "port": 9030,
      "user": "root",
      "password": "",
      "database": "my_cohort",
    },
    "tables": { "donors": "donors", "samples": "samples" },
    "schemas": {
      // optional but REQUIRED for cross-entity filtering, and for `only`
      // on a pre-aggregated cube. Cube roles are resource-level keys and
      // are served back on the resource by /v1/yac/metadata, so the chat
      // recognises a remote cube; everything else lands in the schema.
      "encounter_cube": {
        "udi:cube": true,
        "udi:dimensions": ["sex", "race", "age_group"],
        "udi:measures": ["cnt"],
      },
      "samples": {
        "foreignKeys": [
          {
            "fields": ["donor_id"],
            "reference": { "resource": "donors", "fields": ["id"] },
          },
        ],
      },
      "donors": {
        "primaryKey": ["id"],
        // optional: what the LLM is told the table and its columns mean
        "description": "One row per tissue donor.",
        // optional: the label the chat shows for the table (and, per field, a column)
        "title": "Donors",
        // optional: keep this table's row count out of the chat
        "udi:hide_count": true,
        "fields": [
          {
            "name": "age",
            "description": "Age at donation, in years.",
            "title": "Age",
          },
        ],
      },
    },
    // optional: the chat's "Try an example" prompts for this package, served by
    // GET /v1/yac/examples?package=my_cohort in place of the global list
    "examplePrompts": ["How many donors are in each age group?"],
    // optional: package-level keys served with the introspected schema
    // (`package_extras`); a package mixing line-level tables and cubes says
    // which to answer from by default
    "package": { "udi:prefer": "line_level" },
  },
}

DuckDB has two forms:

Seeded file (recommended) — { "type": "duckdb", "database": "/abs/penguins.duckdb", "tables": { "Penguins": "penguins" }, "schemas": { ... } }. Tables are pre-loaded with the same cleaning as StarRocks (sentinel-nulling, typing), so results match. Produced by seed_duckdb.py.
In-memory CSV views — { "type": "duckdb", "database": ":memory:", "views": { "event": "/abs/event.csv" }, "tables": { "Event": "event" } }. Reads CSVs live via read_csv_auto — no seeding, but no sentinel cleaning, so mixed numeric/placeholder columns (e.g. "Not Available" in a date column) type as text and won’t brush. Handy for quick tests.

Then run the server with the extras and the env var:

UDI_QUERY_BACKENDS=/path/to/backends.json INSECURE_DEV_MODE=1 \
  uv run --project packages/agent --extra server --extra starrocks \
  fastapi dev packages/agent/src/udiagent/server/app.py --port 8007

Related env var: UDI_METADATA_TTL_SECONDS (default 3600) — introspection cache TTL.

Generating the config for a CSV dataset. Two seeders write this config for you, carrying foreignKeys/primaryKey, descriptions, titles and udi:hide_count from the directory’s datapackage.json into schemas and applying identical CSV cleaning:

packages/agent/scripts/seed_duckdb.py → a local .duckdb file + duckdb-backends.json. No container — the easiest option. See dev/duckdb/README.md.
packages/agent/scripts/seed_starrocks.py → a running StarRocks database + starrocks-backends.json. See dev/starrocks/README.md.

3.1a Per-user database authentication (JWT passthrough)

By default every caller’s queries run as the one service credential in the backend spec: the agent authenticates the caller, but the database sees a single shared account, so authorization stops at the API boundary.

Set jwtPassthrough and the agent instead forwards each caller’s own bearer token to StarRocks as the connection password. StarRocks verifies it against the same JWKS the agent used and applies that user’s own grants and row/column policies, so the database becomes the authority on who sees what and the audit trail names real users.

{
  "my_cohort": {
    "type": "starrocks",
    "jwtPassthrough": true,
    // Claim whose value is the StarRocks username. MUST match `principal_field`
    // in the security integration. Keycloak's `sub` is a UUID, so a realistic
    // deployment wants `preferred_username` or `email`. Default: "sub".
    "principalField": "preferred_username",
    "connection": {
      "host": "starrocks.internal",
      "port": 9030,
      "database": "my_cohort",
      // No password: the caller's token is the credential. A `user` here is
      // used only as a fallback under INSECURE_DEV_MODE, which issues no token.
    },
    "tables": { "donors": "donors" },
  },
}

Requirements and caveats:

StarRocks >= 3.5.0 — JWT authentication does not exist before it. Verified against 3.5.21.
Every caller needs a StarRocks user, named by the principalField claim, plus grants. There is no automatic provisioning; this is usually the long pole in a rollout, not the agent-side configuration.
The required_audience of the StarRocks security integration must match the agent’s JWT_AUDIENCE, or one token cannot satisfy both.
Revocation ceiling. MySQL binds identity at CONNECT time and StarRocks does not re-check exp mid-session, so the connector recycles a cached connection once the token that opened it expires. A grant revoked inside a live session is not seen until then — at most one token lifetime.
jwtPassthrough is rejected on duckdb, which has no concept of a user, and rejected at startup under INSECURE_DEV_MODE unless connection.user gives it a fallback. It never silently degrades to a shared credential.
Metadata is cached per principal, because dataDomains holds the actual distinct values of each column and would otherwise leak across users.

StarRocks side, once per realm:

CREATE SECURITY INTEGRATION keycloak PROPERTIES (
  "type" = "authentication_jwt",
  "jwks_url" = "https://idp.example/realms/udi/protocol/openid-connect/certs",
  "principal_field" = "preferred_username",
  "required_issuer" = "https://idp.example/realms/udi",
  "required_audience" = "udi-yac"
);

To exercise it locally, dev/starrocks/setup_jwt_auth.py provisions the equivalent (a throwaway JWKS plus two users with different grants) against the dev container.

3.2 HTTP contract (for a non-toolkit consumer)

The full request and response schemas are in the HTTP API reference (generated from the server into openapi.json); this section explains how the two endpoints are meant to be used.

Two JWT-guarded endpoints (dev: INSECURE_DEV_MODE=1, Authorization: Bearer dev). With jwtPassthrough set on a backend, that same token is also what authenticates the query to the database; a token the database refuses yields 403 for the whole request (never a per-visualization error inside a 200).

GET /v1/yac/metadata?package=<name>&refresh=<0|1> — introspected schema for a package:

{ "package": "penguins", "interactive": false,
  "dataSchema":  { "name": "...", "resources": [ ... ] },   // frictionless + udi: fields
  "dataDomains": [ { "entity": "...", "field": "...", "type": "point|interval",
                     "domain": { "values": [...] } | { "min": n, "max": n } } ] }

POST /v1/yac/query — stateless batched query (every visible viz + the current selection state, in one request):

// request
{ "package": "penguins",
  "selections": {                              // name → ActiveDataSelection
    "brush1": { "dataSourceKey": "Penguins", "type": "interval",
                "selection": { "body_mass_g": [3000, 4000] } } },
  "queries": [
    { "vizId": "q1",
      "source": [ { "name": "Penguins", "source": "penguins" } ],
      "transformation": [ /* named filter refs brush1, groupby, rollup, ... */ ],
      "displayDataOnly": false,                // optional
      "offset": 0 } ] }                        // optional (load-more paging)

// response
{ "results": {
    "q1": { "displayData": [...], "isSubset": true, "aggregated": true,
            "extent": [...], "truncated": null }
    // or, for a failed spec:  "q1": { "error": "unsupported transformation ..." }
} }

The toolkit’s remote backend (packages/grammar/queryBackend.ts, createRemoteBackend) speaks exactly this — it batches all queryData calls in one tick into a single request and fans results back out by vizId.

3.3 Wiring the chat (`udi-yac`)

Set VITE_UDI_REMOTE_PACKAGE=<name> (standalone: env; embedded: remotePackage in UDIChatConfig). On load the chat calls GET /v1/yac/metadata, installs the remote QueryBackend, and every query — including brush cross-filtering (committed on mouse-up, with a loading indicator) — runs on the server via POST /v1/yac/query. No CSVs enter the browser. Takes precedence over dataPackage/dataPackagePath. apiBaseUrl points at this server. (Canonical description of every chat variable: packages/chat/.env.example, generated from packages/chat/src/app/envVars.ts.)

3.4 Using the engine directly (library)

from udiagent.query import DuckDBConnector, QueryEngine
engine = QueryEngine(
    DuckDBConnector(views={"donors": "hubmap/donors.tsv"}),
    table_map={"donors": "donors"},
)
engine.run_query(
    source={"name": "donors", "source": "donors"},
    transformation=[{"groupby": "sex"}, {"rollup": {"n": {"op": "count"}}}],
)

4. Adding a new backend dialect

Add a Dialect subclass in connectors.py (override quote_char, placeholder, median).
Add a Connector class exposing execute(sql, params) -> list[dict] and a dialect attribute; lazy-import the driver; normalize values with _normalize_value.
Register it in server/app.py _engine_from_config under a new type.
Add the driver as an optional extra in pyproject.toml.
Run the parity suite against a live instance (gate it like UDI_STARROCKS_TEST) — this is the acceptance test for a new dialect.

5. Known semantic ceilings (documented in code as `ponytail:`)

kde is a Gaussian sum (Silverman bandwidth), visually equivalent to the toolkit’s fast-kde but not bit-identical — excluded from strict parity.
median on StarRocks is PERCENTILE_APPROX (approximate).
rolling windows / offset paging are only deterministic when ordered by a unique key (SQL tie order is unspecified).
joins spell their projection out instead of SELECT *, because both halves of the naming have to be done by hand: a same-named join key is kept once (from the left), and a non-key name present on both sides is renamed <name>_1 / <name>_2 as Arquero does. USING is not enough — DuckDB merges the key columns, StarRocks keeps both, and every later reference to the key is then ambiguous.
% compiles to MOD(a, b), never the operator: pymysql builds StarRocks statements with Python %-formatting, so a bare % in the SQL raises “unsupported format character”.
No multi-hop entity paths — cross-entity filtering resolves direct FKs and shared-parent siblings only (see sample-data/readme.md).
Row-level projection — remote row tables still SELECT *; the “relevant fields” table view is a client-side projection.

Server-side data (SQL)

1. The big picture: two compilers, one grammar

2. Module layout

2.1 PipelineCompiler (compiler.py)

2.2 QueryEngine (engine.py)

2.3 Connectors (connectors.py)

2.4 Introspection (introspect.py)