Documentation / UQL

Pushdown & Capability Semantics

Exactly how UQL decides what runs at the source and what runs in Nexus — the per-adapter capability profile, the ADR-0058 rule for re-applying filters in memory, pushdown-safe limits, and the bounded in-engine fallback that keeps partial-pushdown sources correct rather than silently wrong.

This page is for the engineer who does not trust "it just pushes down." UQL presents one query model over very different backends, and the honest question is: for this query against this connector, what actually executes at the source, what does Nexus finish itself, and can a mismatch ever return wrong data? This page answers all three. The short version: capabilities are declared per adapter and verified per query, unsupported work degrades to a clean in-engine evaluation (never a silent wrong answer), and the degradation is bounded so it cannot pull an unbounded result into memory.

The capability profile

Every adapter declares a typed capability profile. It is not inferred and not a marketing claim — it is a structure the engine reads before planning each query. It has two parts.

Pushdown capabilities — what the backend can execute natively:

Capability	Meaning
`where`	The source can apply the filter.
`order_by`	The source can sort.
`group_by`	The source can group.
`distinct`	The source can de-duplicate.
`having`	The source can filter grouped rows.
`join`	The source can join collections within the same bridge.
`select_expressions`	The source accepts computed/expression columns (this is what makes `$expr` meaningful).
`aggregations`	The set of aggregate functions the source runs natively (e.g. `sum`, `count`, `avg`, `min`, `max`).

Write capabilities — insert, update, delete, and merge, each a plain boolean.

Capabilities can be overridden per container. A single bridge may expose one container that supports full pushdown and another that supports none; the engine resolves the profile for the specific container a query targets, falling back to the adapter's root profile when there is no override. This is why the answer to "does connector X push down?" is sometimes "it depends which container."

How the engine plans a read

For each read the engine resolves the target container's capability profile and then decides, field of the request at a time, what to hand to the source and what to keep:

Filter (where). If the adapter declares where = true and every operator in the clause is individually pushdown-eligible, the whole filter is compiled into the source's native form (a parameterized SQL WHERE, a GraphQL ItemsQuery, an API filter body) and pushed. Otherwise the filter is treated as a hint: the source runs whatever portion it can, and Nexus re-applies the complete filter in memory over the returned rows.
Projection (select). Pushed when supported; otherwise Nexus projects the requested fields after the fact.
Sort / limit / offset. Pushed only when it is safe to do so (see below).
Group-by & aggregation. Pushed when the adapter declares group_by and the requested aggregate functions are in its aggregations set; otherwise computed in-engine.

The rule in step 1 is the important one, introduced by an internal design decision (ADR-0058): the engine hands where to the adapter as a hint, then re-applies it in memory unless the adapter declares full where pushdown AND every operator is pushable. A single non-pushable operator in an otherwise-pushable clause (for example $regex against SQL Server, which has no native regex) tips the whole clause into the residual-filter path — because pushing "most of" a filter and skipping the rest would return too many rows or, worse, the wrong ones.

Why limits are withheld from partial-pushdown sources

Naively pushing limit is a correctness bug. If a source cannot apply the filter but can apply a limit, and the engine pushes LIMIT 100 anyway, the source returns the first 100 rows before filtering — then Nexus filters those 100 down to whatever matched, silently dropping every matching row past the first 100.

UQL does not do this. limit/offset are pushed only when the source can filter first (i.e. when the filter was fully pushed). On a partial-pushdown source the engine withholds the limit from the adapter, applies the filter in memory, and then applies the limit — so you always get up to limit rows that actually match, not the first limit rows the source happened to return. This was a real class of bug in earlier partial-pushdown behavior and is now closed on all three read paths (ad-hoc query_data, saved reports, and pipeline ctx.adapters reads).

The in-engine fallback is clean, and bounded

When work cannot be pushed, Nexus performs it over the returned rows using the same operator and aggregation implementations that define the pushed-down behavior — so the in-memory result matches what the source would have produced. Concretely:

Filters are re-applied by the same operator catalog that emits the SQL, so $between, $in, $contains, and the rest mean exactly the same thing in memory as at the source. In-memory comparison is deliberately type-tolerant: a numeric field returned as a string (common on REST surfaces) is compared numerically against a numeric query value rather than raising, and a genuinely incomparable pair drops the row rather than crashing the read.
Aggregations and group-by the source cannot run are computed in-engine, including expression-based aggregations, producing the same summarized output you would get from a SQL GROUP BY.

The fallback is bounded by a post-process row budget. In-engine filtering, grouping, and aggregation operate within a configured maximum number of rows to process, so a query that degrades to in-engine work cannot pull an unbounded result set into the container's memory. If a source would return more rows than the budget allows for the post-processing step, the query is bounded rather than allowed to exhaust the process. Combine this with per-query limits and, in pipelines, the defense-in-depth row caps to keep degraded queries predictable.

This is the design's core promise: an operator or aggregation a source cannot execute does not return a wrong answer and does not take the process down. It degrades to a correct, bounded, in-engine computation.

Per-adapter capability summary

The shipped connectors fall into clear tiers. "Full" means the standard read surface — filter, sort, group-by, distinct, having, joins within the bridge, and the common aggregate functions — pushes to the source.

Connector	Read pushdown (where / order / group / agg)	Write	Notes
PostgreSQL	Full	insert / update / delete / merge	Also backs the internal workspace.
MySQL	Full	insert / update / delete / merge	Same SQL base.
SQL Server	Full	insert / update / delete / merge	T-SQL.
JDBC (generic)	group / order / distinct / having / joins / expressions / aggregates	SQL write	Configurable driver + URL, JAR upload.
NetSuite	Dual-surface: SuiteQL = full pushdown; REST record surface = none	insert / update / delete / merge	SuiteQL over SuiteAnalytics Connect (JDBC) pushes; the REST record surface filters in-engine.
QuickBooks Online	`where` + `order` only (aggregation in-engine)	insert / update / delete (no merge)	QBO query grammar.
Monday.com	`where` + `order` only, native operator subset (aggregation in-engine)	insert / update / delete	GraphQL `ItemsQuery`; see the operators reference for the exact subset.
Stripe (preview, pre-1.0)	None — filter and order run in-engine	insert / update / delete (per collection)	Pre-1.0; treat as preview.
Internal workspace	Full	insert / update / delete / merge	Per-tenant Postgres, no credentials, structurally isolated.

Do not assume uniform pushdown across connectors. A dashboard that runs comfortably against PostgreSQL because the database does the grouping will do that grouping in-engine against QuickBooks — correct, but with different performance characteristics. Design for the tier of the source you are actually querying, and stage heavy multi-step work through the internal workspace, which is full-pushdown.

Observability: capabilities are declared, plans are recorded

Because the profile is declared rather than hidden, you can reason about a query before running it:

Inspect the profile. An adapter's declared capabilities are visible through schema discovery and the admin adapter surface, so you can see which tier a connector is in and which aggregates it runs natively.
See what executed. The engine returns a plan identifier and the resolved operation with each read, and pipeline/report runs persist structured diagnostics and logs — so when a query degraded to an in-engine step, the run record reflects the path it took rather than leaving you to guess.
Reproduce it. The same capability resolution runs on all three read paths, so a filter that pushes down in a saved report pushes down identically when the same bridge is read from a pipeline or over query_data. There is no surface where the rules quietly differ.

What this does and does not do

It does guarantee that filters produce correct results whether or not the source can express them, that limits never silently drop matching rows on partial-pushdown sources, and that unsupported aggregation degrades to a bounded in-engine computation.
It does resolve capabilities per container, so a mixed bridge is handled per target rather than by a single blanket assumption.
It does not perform cross-bridge joins — one read targets one bridge; combine systems by staging through the workspace.
It does not invent pushdown a source lacks. Where a connector is a lower tier (QuickBooks, Monday, Stripe preview, the NetSuite REST record surface), heavier operations run in-engine within the row budget, and that is stated plainly rather than hidden behind the uniform query shape.