Documentation / Reporting & Data

Reports

Build saved, parameterized queries that filter, join, and aggregate data at the source — the reusable, version-controlled building block behind dashboards, exports, syncs, and AI answers in Nexus.

A Nexus report is a saved, parameterized query definition that runs against one or more of your configured data sources (bridges). A report describes what data to read, how to filter, join, and aggregate it, and which parameters callers may supply at run time. It is the reusable unit your team points dashboards, scheduled exports, data syncs, and AI assistants at: define the logic once, run it anywhere, and get the same result every time.

This page is for anyone building reporting or analytics on Nexus — analysts, BI builders, and developers wiring Nexus into other tools. It covers what a report is, how it maps to the UQL capability model, exactly where computation runs, the version-control guarantees, and the limits worth knowing before you rely on one.

What a report is (and is not)

A report is a JSON definition, not executable code. It declares a read against the UQL capability model. Because the definition only describes a read, reports are safe to share with non-admin users and to expose to AI agents: running one is bounded by the calling identity's permissions and the source systems that identity can reach. There is no arbitrary code in a report, and nothing in a report can escalate privilege.

Contrast this with a pipeline, which is Python code and executes in a sandbox. Reports are for reading and shaping data you can already reach; pipelines are for orchestration, transformation, and writes. Many teams use both: a pipeline lands data into the internal workspace, and a report serves it fast.

Anatomy of a report

Section	Purpose
`report_name`	Human-readable name (required).
`description`	Free-text summary of what the report returns.
`parameters`	Runtime inputs with types and defaults, substituted into filters.
`queries`	One or more reads, each targeting a bridge and collection (at least one required).
`joins`	Combine two query results by matching keys.
`group_by`	Group rows and compute aggregations.
`post_processing`	Final in-memory shaping: filter, sort, limit, select, pivot.
`execution_role_id`	Optional curated role the report runs under (see Execution roles).

A minimal report is a name plus one query. Everything else is layered on as the question gets richer.

Queries and `where`

Each query names a system (adapter family, e.g. postgres, netsuite), a bridge_name (the specific connection), an optional containers scope (schema/database), a collection (table/object), the fields to project, and a where clause built from a rich operator catalog. Top-level conditions are combined with AND:

"where": {
  "status": "active",
  "amount": { "$gte": 100, "$lt": 10000 },
  "region": { "$in": ["NA", "EU"] },
  "$or": [ { "tier": "gold" }, { "vip": true } ]
}

Operators cover comparison ($eq, $gt, $between), membership ($in, $not_in), emptiness ($empty, $not_empty), text patterns ($contains, $starts_with, $like, $regex), and boolean composition ($and, $or, $not). The full catalog and per-adapter support notes are in the Operators reference. Not every source supports every operator — Monday.com and QuickBooks, for example, cover a narrower set — and the engine handles the difference for you (see "Where computation actually runs" below).

Grouping and aggregation

group_by declares the group keys (fields) and a list of aggregations, each with a type, field, and output alias. Supported types are sum, avg, count, min, max, and median. Use "field": "*" with count to count rows, and an optional having clause to filter the aggregated rows.

"group_by": {
  "fields": ["region"],
  "aggregations": [
    { "type": "sum",   "field": "amount", "alias": "total_sales" },
    { "type": "count", "field": "*",      "alias": "n_orders" },
    { "type": "avg",   "field": "amount", "alias": "avg_order" }
  ],
  "having": { "total_sales": { "$gte": 1000 } }
}

min and max work on dates and text as well as numbers. When the source adapter supports group-by pushdown (all the SQL adapters, plus NetSuite's SuiteQL surface), the grouping becomes a single native GROUP BY and the database does the aggregation. When it does not, Nexus computes the same aggregation in-engine after reading the rows — a clean, correct fallback, never a silent wrong answer. One requirement to know: a group key must also appear in the query's fields so it can be projected, otherwise it comes back null.

Joins and cross-query references

A report can issue several queries and stitch them together. joins combine two query results by matching keys, and later queries can reference an earlier query's values with {{queries.<id>.<field>}} — useful when one read supplies the filter values for the next (for example, resolve a subsidiary name to an id in query A, then filter query B by that id). Joins run in the engine unless the adapter and shape qualify for join pushdown.

Parameters

Parameters make a report reusable. Each declares a name, type, and default, and is substituted into where clauses via {{parameters.NAME}}:

"parameters": [
  { "name": "min_amount", "type": "number", "default": 0 },
  { "name": "region", "type": "string", "default": "*" }
]

A useful idiom: a condition whose value resolves to "*" or an empty string is dropped, which is how you build optional filters. With region at its "*" default the report covers all regions; supplying region="EU" narrows it. The same parameterized report can therefore back a company-wide dashboard panel and a filtered drill-down without duplication. Note that a report's definition must include a parameters array even when it is empty ([]); saving without it is rejected.

Where computation actually runs

This is the part a platform team should understand before trusting a report over a large table. Nexus follows the UQL capability model (ADR-0058): each adapter declares typed capabilities and how much it can push down.

The engine hands your where clause to the adapter as a hint. It then re-applies the WHERE in memory unless the adapter declares full pushdown and every operator in the clause is pushable. This guarantees correctness: a partially-supported filter never returns extra rows.
LIMIT/OFFSET are pushed to the source only when doing so is pushdown-safe — i.e. the filtering that precedes them was fully pushed. Otherwise they are applied after the in-memory filter, so you never truncate a result before the real filter ran.
Group-by and aggregations the adapter cannot push are evaluated in-engine, bounded by a post-processing row budget so an unbounded fan-out can't exhaust memory.

The practical consequence: over a PostgreSQL, MySQL, SQL Server, or SuiteQL source, a filtered, grouped report typically compiles to one native SQL statement and runs at warehouse speed. Over QuickBooks or Monday.com you get filter and order pushdown, with aggregation finished in-engine. Over a preview adapter like Stripe, filtering is done in memory. In all cases the answer is identical; only where the work happens differs. See the capability matrix for per-adapter detail.

A complete example

This report returns total and average order value per region, with an optional minimum-amount filter, sorted by total sales:

{
  "report_name": "Sales by Region",
  "description": "Total and average order value per region, optionally filtered by min amount.",
  "parameters": [
    { "name": "min_amount", "type": "number", "default": 0 },
    { "name": "region", "type": "string", "default": "*" }
  ],
  "queries": [
    {
      "id": "orders",
      "system": "postgres",
      "bridge_name": "POSTGRES.prod",
      "containers": ["public"],
      "collection": "orders",
      "fields": ["region", "amount"],
      "where": {
        "amount": { "$gte": "{{parameters.min_amount}}" },
        "region": "{{parameters.region}}"
      }
    }
  ],
  "group_by": {
    "fields": ["region"],
    "aggregations": [
      { "type": "sum",   "field": "amount", "alias": "total_sales" },
      { "type": "count", "field": "*",      "alias": "n_orders" },
      { "type": "avg",   "field": "amount", "alias": "avg_order" }
    ]
  },
  "post_processing": {
    "order_by": [{ "field": "total_sales", "direction": "desc" }],
    "limit": 25
  }
}

Point the same definition at the internal workspace by addressing the query with "system": "postgres" and "bridge_name": "workspace" — this is the pattern behind the two-tier serving model in Data sync, where a nightly pipeline pre-aggregates into workspace tables and a report serves the result in milliseconds.

Execution: identity, permissions, and execution roles

When a report runs, it runs as the calling identity and is bounded by that identity's RBAC. A user (or AI agent) who cannot read a bridge cannot make a report read it either — the report is not a way around access control. Every execution is checked against report:execute plus the underlying resource permissions, and every run is recorded in the audit log.

For cases where a report needs to read data the caller should not hold standing access to — a finance summary that reads a restricted ledger, say — you can pin an execution role to the report. An admin curates a role with exactly the permissions the report needs and sets it as the report's execution_role_id. When run with assume-role, the report evaluates under that curated permission set instead of the caller's, with no standing elevation for the user and an admin edit-lock on any report that carries one (only admins can assign the role or edit/delete such a report). The bridge credentials used are always the service-bridge credentials, never another user's personal connection. This is how you expose a governed answer without handing out the underlying access. Full mechanics are in Execution roles.

Version control

Every save is a commit. Reports live in your tenant's per-tenant git repository (backed by Azure Files), so you get:

History — every revision of a report, who saved it, and when.
Diff — see exactly what changed between two revisions of the definition.
Restore — roll a report back to any prior revision.

This means the definition behind a recurring number is always auditable: when a board metric moves, you can prove whether the query changed or the data did. History, diff, and restore are available over both REST and MCP. See Version control.

Building and running reports

Reports use a single, unified authoring surface across the desktop app, REST API, and MCP:

Validate the definition before saving — Nexus checks it is a valid JSON object with a non-empty name and at least one query, and surfaces structural errors early.
Save it, which upserts the report and automatically versions it (one commit per save).
Execute it with optional parameters. Over MCP this is execute_report; over REST it is POST /api/reports/{id}/execute. The response is the shaped rows plus column metadata.
Discover a source's schema first with discover_schema if you are unsure of collection or field names — every adapter supports it.

Limits & guarantees

Correctness over cleverness. Unsupported filters are re-applied in memory, never dropped. You never get extra rows because an operator wasn't pushable.
Aggregation always resolves — pushed to the source when supported, computed in-engine otherwise, bounded by a post-processing row budget.
Group keys must be projected. A field used in group_by.fields must also be in the query's fields, or it returns null.
Workspace-backed reports cap at 1,000 rows unless the query sets an explicit limit. Set a limit (or post_processing.limit) when you expect more.
Parameters array is required, even if empty ([]).
Permissions are enforced on every execution. A report cannot exceed the caller's access unless an admin has pinned an execution role, and that path is edit-locked and audited.
Reports read and shape; they do not write. To write records to a target system, use Data sync or a pipeline.

Because every report is version-controlled, permission-bounded, and parameterized, the same definition powers a dashboard tile today, a scheduled export tomorrow, and an AI answer the moment a user asks a question over your data.