Query Engine

architecture draft Updated May 18, 2026

Starting Point

The platform’s query engine lets application authors filter and load entity instances using a text-based filter grammar — the same grammar used in security rules, workflow guards, and function conditions:

Text Filter → QueryParser.Parse() → AST (QueryNode)
                                        ↓
            ┌───────────────────────────┼───────────────────────────┐
            ↓                           ↓                           ↓
    SqlCompiler.Compile()     FilterEvaluator.EvaluateAsync()   (same AST)
    → SQL + Parameters        → bool (in-memory)
    → DB execution            → Security rules, transition conditions
            ↓
    IncludeResolver.ResolveAsync()
    → Eager-loaded related entities

Two evaluation paths for the same grammar: the SQL compiler generates SQL for database queries, and the filter evaluator evaluates in memory for security rules and flow conditions.

This worked well for loading entity instances, but had two categories of problems: bugs we discovered during a thorough review, and fundamental gaps in what the engine could express.

The Review: What We Found

A structured review of the query pipeline — roughly 2,200 lines across the SQL compiler, filter evaluator, include resolver, and query parser — uncovered bugs ranging from security-relevant to cosmetic.

Critical Bugs

1. Vector similarity threshold hardcoded. The SQL compiler hardcoded a 0.3 distance threshold for vector similarity (SIMILAR TO) queries, even though a configurable threshold existed in the query options. All semantic search queries used the same fixed cutoff, defeating any tuning.

2. BETWEEN with NULL returned true. The in-memory filter evaluator’s comparison function returned 0 for null values, so null BETWEEN 10 AND 20 evaluated as 0 >= 0 && 0 <= 0 = true. This was a security filter edge case — a null value could bypass a range-based security rule.

3. SIMILAR TO not implemented in the filter evaluator. The SQL compiler handled vector similarity, but the filter evaluator threw a NotImplementedException. If a security rule used vector similarity, it would crash at runtime.

4. Include resolver read original values instead of pending values. For entities created in the current transaction but not yet committed, foreign key values lived in a local change buffer, not the core store. The include resolver read from the core store, so it couldn’t find recently-set relationships.

5. Missing null check on foreign key columns. If a relation had no column name mapped (a dangling relation), the SQL compiler logged an error but continued, leading to a null dereference crash during SQL generation.

The Dual-Path Consistency Problem

The most architecturally interesting finding was the dual-path consistency gap. Because the same filter grammar is evaluated by two completely different engines (SQL compiler for database queries, filter evaluator for in-memory security checks), divergences between them create subtle bugs:

Aspect	SQL Compiler	Filter Evaluator	Risk
SIMILAR TO	Generates SQL	Not implemented	Crash if used in security rules
LIKE `_` wildcard	SQL standard (single char)	Not supported (literal underscore)	Different matching behavior
BETWEEN + NULL	SQL: no match	Returns true	Security filter bypass
Type coercion	Strict (pre-parse)	Broad (string fallback)	Different results for same filter
Collections	EXISTS subqueries	Not supported	Can’t use in security filters

A filter that works correctly in SQL could fail or return wrong results when evaluated in memory. The fix priority was clear: start with the security-relevant divergences (BETWEEN NULL, LIKE wildcards), then work toward full parity.

Performance Concerns

No depth limit on include resolution. A path like A.B.C.D.E.F.G.H generates 8 sequential queries. With large result sets, this could load millions of rows with no protection against pathological paths.

Manual IN-clause construction. The include resolver built IN (...) clauses by string-concatenating IDs rather than using parameterized queries. Safe for GUIDs but architecturally wrong, and problematic for large parent sets (1000+ IDs generating huge IN clauses).

The Gap Analysis: What Was Missing vs. What Was Untested

A readiness review identified five gaps. On closer inspection, two were partially wrong — the implementation existed but had zero test coverage:

Reported Gap	Actual State
No subquery evaluation	EXISTS subqueries existed for collection hops. Zero dedicated tests.
No aggregation functions	SUM, AVG, COUNT, MIN, MAX were parsed and compiled to HAVING clauses within EXISTS scopes. Zero tests.
No window function support	True — not implemented, not needed
No performance optimization testing	True — no benchmarks, no EXPLAIN analysis
No filter optimization/rewriting	True — no AST rewriting pass

The real priority became: test what exists, add what’s missing for real scenarios, explicitly defer what doesn’t fit.

A Security Vulnerability in Collection Hops

The most critical finding was a security bypass in EXISTS scopes. When querying Customer WHERE "Orders.Status = 'Shipped'", the EXISTS subquery on Order did not include Order’s security filter. A user denied read access to Order could still infer Order data through collection-hop filters.

A malicious user could probe entity data they’re denied access to by crafting collection-hop filters: "RestrictedEntity.SecretField = 'guessed_value'" would return results if the guess was correct, even though the user couldn’t query that entity directly.

The root cause: the SQL compiler had no awareness of the security system at all. It received a pre-composed root filter from the query context but had no way to look up per-entity security filters for the entities it encountered inside EXISTS scopes.

The fix: inject a security filter lookup callback into the compiler, so every scope it creates — simple collection hops, nested scopes, aggregate scopes — applies the appropriate entity’s security filter. For “deny all” entities (where the security filter is 1 = 0), the EXISTS always returns false, correctly preventing any information leakage.

The Scope Tree Rewrite

The bug fixes and gap analysis made it clear that the single-pass SQL compiler needed to be replaced. It made decisions in a single pass — encounter a collection hop, create an EXISTS scope, emit SQL immediately. This broke down in several ways:

Security must be applied per-entity inside EXISTS scopes — the vulnerability above
Aggregates spanning multiple collection hops need JOINs inside EXISTS (SUM(Orders.LineItems.Amount))
Aggregate and simple predicates sharing a scope need WHERE + HAVING in one EXISTS
OR across different scopes — two EXISTS blocks need to be OR’d, not AND’d
Aggregate projections — returning SUM/COUNT values requires JOINs, not EXISTS

Five-Phase Architecture

The replacement separates query compilation into five distinct, testable phases:

Input: filter string + security rules + projection spec

Phase 1: Parse         → AST (boolean expression tree)
Phase 2: Scope assign  → Scope Tree (predicates assigned to scope nodes)
Phase 3: Security      → Augmented Scope Tree (security predicates injected)
Phase 4: Strategy      → SQL Plan (EXISTS vs JOIN per scope)
Phase 5: Emit          → SQL string + parameters

Phase 1: Parse → AST

The existing query parser produces a boolean expression tree. No changes needed.

Phase 2: Scope Assignment → Scope Tree

Walk the AST and build a scope tree. Each collection hop in a property path creates a scope node. Predicates are assigned to the scope of their deepest collection hop.

Input AST:
  AND(
    LIKE(Customer.Name, 'A%'),
    EQ(Orders.Status, 'Active'),
    GT(SUM(Orders.LineItems.Amount), 1000),
    OR(
      EQ(Returns.Status, 'Open'),
      EQ(Customer.VipCustomer, true)
    )
  )

Scope Tree:
  root: Customer
    ├─ AND
    │   ├─ RootPredicate: Name LIKE 'A%'
    │   ├─ ScopeNode: "Orders" (Customer → Order)
    │   │    where: [Status = 'Active']
    │   │    having: [SUM(LineItems.Amount) > 1000]
    │   │    children:
    │   │      ScopeNode: "Orders.LineItems" (Order → LineItem)
    │   │        (flattened into parent for aggregate JOIN)
    │   └─ OR
    │       ├─ ScopeNode: "Returns" (Customer → Return)
    │       │    where: [Status = 'Open']
    │       └─ RootPredicate: VipCustomer = true

Key rule: The boolean structure from the AST is preserved. Scope nodes are leaves in the boolean tree, not a flat list.

Same-path merging: When two predicates reference the same scope (e.g., Orders.Status = 'Active' and SUM(Orders.Total) > 1000), they share one scope node. Simple predicates go in where, aggregates go in having.

Phase 3: Security Injection

Walk the scope tree. For each scope node, look up the entity’s security filter and AND it into the scope’s where list:

Before:
  ScopeNode: "Orders"
    where: [Status = 'Active']

After (Order security: SalesPerson = @CurrentUser):
  ScopeNode: "Orders"
    where: [Status = 'Active', SalesPerson = @CurrentUser]

Security is just another predicate at the scope level. No special handling downstream.

Important: Security predicates are always AND’d with existing predicates, regardless of the boolean structure above the scope. Security is never OR’d or NOT’d — it’s an absolute constraint.

Security filters can themselves contain collection hops (e.g., "Team.Department = 'Engineering'") — these create nested scopes within the security injection, handled recursively.

Phase 4: Strategy Selection

For each scope node, decide the SQL strategy:

Scope characteristics	Strategy	SQL pattern
Only `where` predicates, no children	`EXISTS`	`EXISTS (SELECT 1 FROM T WHERE fk = parent.pk AND ...)`
`where` + `having`, no deep children	`EXISTS_AGGREGATE`	`EXISTS (SELECT 1 FROM T WHERE ... GROUP BY fk HAVING ...)`
`having` references child scope properties	`EXISTS_JOIN_AGGREGATE`	`EXISTS (SELECT 1 FROM T JOIN Child ON ... WHERE ... GROUP BY fk HAVING ...)`
Projection mode (returning values)	`JOIN`	Root-level JOIN, GROUP BY, SELECT with aggregates

Phase 5: SQL Emission

Walk the boolean tree and emit SQL, applying the selected strategy for each scope node.

Edge Cases That Drove the Design

The edge cases were more interesting than the happy path. Each one validated a design decision in the scope tree model.

OR across different scopes:

Customer WHERE "Orders.Status = 'Active' OR Returns.Status = 'Open'"

WHERE c."Region" = 'US'                    -- root security
  AND (
    EXISTS (SELECT 1 FROM "Order" o
      WHERE o."CustomerId" = c."Id"
        AND o."SalesPerson" = @p0           -- order security
        AND o."Status" = 'Active')
    OR
    EXISTS (SELECT 1 FROM "Return" r
      WHERE r."CustomerId" = c."Id"
        AND r."Status" = 'Open')
  )

The boolean structure preserves the OR. Security is injected into each scope independently.

NOT with aggregates:

Customer WHERE "NOT SUM(Orders.Total) > 1000"

WHERE NOT EXISTS (
  SELECT 1 FROM "Order" o
  WHERE o."CustomerId" = c."Id"
    AND o."SalesPerson" = @p0               -- order security
  GROUP BY o."CustomerId"
  HAVING SUM(o."Total") > 1000
)

NOT EXISTS(... HAVING SUM > 1000) correctly includes customers with NO orders AND customers whose total is <= 1000. The security filter inside means only the user’s visible orders are counted.

Same path, different predicates in OR:

Customer WHERE "Orders.Status = 'Active' OR Orders.Total > 500"

Both reference the same Orders scope. They share ONE EXISTS with the OR inside:

WHERE EXISTS (
  SELECT 1 FROM "Order" o
  WHERE o."CustomerId" = c."Id"
    AND o."SalesPerson" = @p0               -- order security
    AND (o."Status" = 'Active' OR o."Total" > 500)
)

Not two separate EXISTS blocks OR’d together — that would give subtly different semantics.

Aggregate + non-aggregate on same path:

Customer WHERE "Orders.Status = 'Active' AND SUM(Orders.Total) > 1000"

Shared EXISTS — simple predicate in WHERE, aggregate in HAVING:

WHERE EXISTS (
  SELECT 1 FROM "Order" o
  WHERE o."CustomerId" = c."Id"
    AND o."SalesPerson" = @p0               -- order security
    AND o."Status" = 'Active'               -- WHERE predicate
  GROUP BY o."CustomerId"
  HAVING SUM(o."Total") > 1000             -- HAVING predicate
)

This means: “customers with active orders whose active-order total exceeds 1000.” The WHERE filters orders before aggregation, so only active orders are summed.

Deep path aggregate:

Customer WHERE "SUM(Orders.LineItems.Amount) > 1000"

Two collection hops. The aggregate needs a JOIN inside EXISTS:

WHERE EXISTS (
  SELECT 1 FROM "Order" o
  JOIN "LineItem" li ON li."OrderId" = o."Id"
  WHERE o."CustomerId" = c."Id"
    AND o."SalesPerson" = @p0               -- order security
  GROUP BY o."CustomerId"
  HAVING SUM(li."Amount") > 1000
)

The child scope is flattened into a JOIN because the parent scope’s HAVING references child properties.

Multiple aggregates on different paths:

Customer WHERE "COUNT(Orders.Id) > 5 AND COUNT(Returns.Id) > 0"

Two separate scope nodes, each with their own EXISTS and their own security:

WHERE EXISTS (
  SELECT 1 FROM "Order" o
  WHERE o."CustomerId" = c."Id" AND o."SalesPerson" = @p0
  GROUP BY o."CustomerId"
  HAVING COUNT(o."Id") > 5
)
AND EXISTS (
  SELECT 1 FROM "Return" r
  WHERE r."CustomerId" = c."Id"
  GROUP BY r."CustomerId"
  HAVING COUNT(r."Id") > 0
)

Security Model

The rewritten security model follows clear rules:

Security is per-entity, always AND’d — never OR’d or NOT’d with user predicates
Root entity security → WHERE clause on the root SELECT
Scope entity security → WHERE clause inside EXISTS (entity queries)
JOIN entity security → JOIN ON clause (projection queries)
Deny-all (1 = 0) → EXISTS becomes always-false / JOIN produces no rows
No security policy → no additional predicate
Nested scopes → security applied at each level independently

Security filters are compiled using the same compiler pipeline, targeting each scope entity independently. This means security filters can themselves contain collection hops — handled recursively with their own scope trees.

Aggregate Projections: Returning Values, Not Just Filtering

With the scope tree handling entity queries correctly, the next gap was fundamental: the engine could filter by aggregates (“find customers where SUM > X”) but couldn’t return aggregate values (“what’s the total revenue by region?”).

Real applications need:

“Revenue by region this quarter” — aggregate + group + filter
“Top customers by order count” — aggregate + sort + limit
“Order list with customer name and salesperson” — flatten related entities into columns
“Average time to close by deal stage” — aggregate over computed paths

Today these require either raw SQL (not available to app authors) or loading all entities client-side and computing in JavaScript (doesn’t scale).

Two Modes: Flat and Aggregate

The presence of aggregate functions determines the query mode:

Flat mode (no aggregates) — each row is a flattened view of one entity:

SELECT o."OrderNumber", c."Name" AS "CustomerName", o."Total"
FROM "order_table" o
JOIN "customer_table" c ON o."Customer" = c."Id"
WHERE o."Status" != @p0
  AND <security row filter on Order>
ORDER BY o."CreatedAt" DESC

Aggregate mode — rows are grouped summaries:

SELECT c."Region", SUM(o."Total") AS "Revenue", COUNT(o."Id") AS "OrderCount"
FROM "order_table" o
JOIN "customer_table" c ON o."Customer" = c."Id"
WHERE o."Status" = @p0
  AND <security row filter on Order>
GROUP BY c."Region"
ORDER BY SUM(o."Total") DESC

Key differences from entity queries:

JOINs instead of EXISTS — groupBy/select paths that cross entity boundaries need JOINs
Explicit SELECT list — only the requested aggregates and group-by columns
No entity hydration — results are scalar rows, not entity instances
Security filters still applied — you can’t aggregate rows you can’t read

Metadata Preservation

The platform’s existing query pipeline returns entities with full property metadata — the client knows every column’s type, display name, formatting, and choice options. Aggregate results must carry equivalent metadata so the client can format values correctly, display choice labels, and let AI agents reason about the data.

Each result column carries enough metadata to be self-describing:

Column Source	Example	How Metadata Is Resolved
GroupBy — direct property	`SalesPerson.Name`	Real property metadata from the SalesPerson entity
GroupBy — choice property	`Order.Status`	Property metadata + choice ID for label resolution
GroupBy — via relation	`Customer.Region`	Resolve path, last segment’s property metadata
SUM/AVG/MIN/MAX	`SUM(Total)`	Source property’s type (Decimal), tagged with aggregate function
COUNT	`COUNT(Id)`	Always Int64, no source property

Wire format:

{
  "columns": [
    { "name": "Name", "type": "String", "sourceEntity": "SalesPerson", "sourceProperty": "Name" },
    { "name": "Revenue", "type": "Decimal", "aggregateFunction": "SUM", "sourceEntity": "Order", "sourceProperty": "Total" },
    { "name": "OrderCount", "type": "Int64", "aggregateFunction": "COUNT" }
  ],
  "rows": [
    ["Alice", 45000.00, 12],
    ["Bob", 18500.00, 7]
  ]
}

Security in Projections

Query projections apply row-level security on every table in the JOIN tree, not just the root entity. A JOIN that bypasses a related entity’s security policy is a data leak.

When the compiler creates a JOIN for an entity type, it looks up that entity’s security filter and adds it to the JOIN’s ON condition:

-- User has:
--   Order policy:    rowFilter = "Region = 'US'"
--   Customer policy: rowFilter = "IsActive = true"

SELECT c."Region", SUM(o."Total") AS "Revenue"
FROM "order_table" o
JOIN "customer_table" c
  ON o."Customer" = c."Id"
  AND c."IsActive" = true                    -- Customer security filter on JOIN
WHERE o."Region" = 'US'                      -- Order (root) security filter on WHERE
GROUP BY c."Region"

Edge cases:

Scenario	Behavior
Root entity has deny-all	Entire query returns empty
Joined entity has deny-all	JOIN matches nothing → rows excluded
Joined entity has no security policy	No additional filter on that JOIN
Deep path (A → B → C)	Each JOIN gets its own entity’s security filter
Security filter references `@CurrentUser`	Resolved at compilation time
Aggregate over secured collection	Security on child entity filters rows before aggregation

The Query Block: Projections as Metadata

The platform is metadata-driven — the right answer for projections is to make them metadata too. A query block defines a projection as a first-class DSL construct: the compiler validates it, the runtime executes it, views can bind to it, and the designer can edit it visually.

Syntax

query RevenueByRegion for Order:
  columns:
    Customer.Region
    SUM(Total) as Revenue
    COUNT(Id) as OrderCount
    AVG(Total) as AvgOrderValue
  filter: "Status = 'Completed'"
  groupBy: Customer.Region
  orderBy: Revenue DESC

query OrderList for Order:
  columns:
    OrderNumber
    Customer.Name as CustomerName
    SalesPerson.Name as SalesPerson
    Total
    Status
    CreatedAt
  filter: "Status != 'Draft'"
  orderBy: CreatedAt DESC

query TopCustomers for Order:
  columns:
    Customer.Name
    Customer.Tier
    SUM(Total) as TotalRevenue
    COUNT(Id) as OrderCount
    MAX(CreatedAt) as LastOrderDate
  groupBy: Customer.Name, Customer.Tier
  orderBy: TotalRevenue DESC
  limit: 20

Anatomy

Clause	Required	Description
`for Entity`	Yes	The root entity — all paths are relative to this
`columns:`	Yes	Property paths and/or aggregate expressions, one per line
`filter:`	No	Filter expression (same grammar as entity queries, security rules, workflow guards)
`groupBy:`	No	Comma-separated property paths. Required when any column is an aggregate
`orderBy:`	No	Column name or alias, optionally `ASC`/`DESC` (default ASC)
`having:`	No	Filter expression applied after aggregation. Only valid with `groupBy:`
`limit:`	No	Maximum number of rows returned

Columns

Each column is either a property path (direct or through relations) or an aggregate expression:

columns:
  # Direct property — from the root entity
  OrderNumber

  # Related property — through an EntityRef
  Customer.Name as CustomerName

  # Deep path — multiple hops
  Customer.AccountManager.Email

  # Aggregates — require groupBy
  SUM(Total) as Revenue
  COUNT(Id) as OrderCount
  AVG(Total) as AvgOrderValue
  MIN(CreatedAt) as FirstOrder
  MAX(CreatedAt) as LastOrder

  # Aggregate over related collection
  SUM(LineItems.Quantity) as TotalQty

COUNT(Id) counts non-NULL values. COUNT(*) counts all rows regardless of NULLs. Both are valid syntax.

The as Alias is optional for direct properties (defaults to the property name) and required for aggregates.

Collection Aggregate Fan-Out

When a query JOINs to multiple collections, the JOIN creates a fan-out that inflates counts and sums. If an Order has 3 LineItems, the JOIN produces 3 rows per order — SUM(Total) triple-counts each order’s total.

v1 strategy: Restrict. A query can aggregate over at most one collection path. Mixing root-level aggregates with collection aggregates in the same query is a compiler error. The user writes two separate queries. This is safe and simple — silently producing wrong numbers would be far worse than a clear compiler error.

Dynamic Filters (Parameters)

Queries can declare parameters bound at execution time:

query SalesReport for Order:
  parameters:
    StartDate: DateTime
    EndDate: DateTime
    Region: String?           # nullable = optional
  columns:
    Customer.Region
    SUM(Total) as Revenue
    COUNT(Id) as OrderCount
  filter: "CreatedAt >= @StartDate AND CreatedAt <= @EndDate AND (@Region IS NULL OR Customer.Region = @Region)"
  groupBy: Customer.Region
  orderBy: Revenue DESC

Parameters use @Name syntax in filter expressions. Nullable parameters (String?) are optional — if not provided, they’re bound as NULL (the filter should handle that with IS NULL OR patterns).

Parameters are what make the query builder UI interactive — a date range picker binds to @StartDate / @EndDate, a dropdown binds to @Region.

The Having Clause

The having: clause filters groups after aggregation:

query HighRevenueRegions for Order:
  columns:
    Customer.Region
    SUM(Total) as Revenue
    COUNT(Id) as OrderCount
  filter: "Status = 'Completed'"
  groupBy: Customer.Region
  having: "Revenue > 50000"
  orderBy: Revenue DESC

The compiler resolves aliases back to their aggregate expressions (e.g., Revenue → SUM(o."Total")). The same alias resolution strategy is used for both ORDER BY and HAVING — resolve to full expressions rather than relying on SQL alias support, which varies across databases.

Compiler Validation

Rule	Severity	Description
Root entity must exist	Error	`query X for NonExistent:`
Column paths must resolve	Error	`Customer.Foo` where Customer has no Foo property
Aggregate without groupBy	Error	`SUM(Total)` present but no `groupBy:` clause
Non-aggregate column not in groupBy	Error	Column not aggregated and not in groupBy list
orderBy references unknown column	Error	`orderBy: Foo DESC` where Foo is not a column alias
Parameter type must be valid	Error	Unknown type in parameter declaration
Filter references unknown parameter	Error	`@Foo` in filter but no parameter named Foo
Duplicate column alias	Error	Two columns with the same name/alias
`having:` without `groupBy:`	Error	`having:` clause present but no `groupBy:`
`having:` references non-aggregate non-grouped column	Error	References a column that isn’t an aggregate alias or groupBy column
Mixed root + collection aggregates	Error	Fan-out would inflate root aggregates

Column Metadata — Stored in the Query, Not a Separate Entity

There is no separate “projected entity type.” The query definition and its column definitions are the metadata. Each column stores a reference to its source property — the client resolves formatting, choice labels, and type information directly from that reference.

Each column definition stores:

SourcePath — the property path from the root entity (e.g., "Customer.Region", "Total")
AggregateFunction — SUM, AVG, COUNT, MIN, MAX, or null for plain columns
Name — the alias / display name

At execution time, the runtime resolves each source path against the metadata model. The resolved property metadata provides type, choice, formatting, and source entity. These resolved values are included in the query result wire format so the client can render each column correctly — but they’re computed fresh on each execution, not stored on the column definition. If a property’s type or choice changes, the next execution picks up the new metadata automatically.

View Binding

Views can bind to query results:

query RevenueByRegion for Order:
  columns:
    Customer.Region
    SUM(Total) as Revenue
    COUNT(Id) as OrderCount
  filter: "Status = 'Completed'"
  groupBy: Customer.Region
  orderBy: Revenue DESC

view RevenueReport for query RevenueByRegion:
  layout:
    grid:
      column Region
      column Revenue
      column OrderCount

The compiler validates that referenced columns exist in the query. The grid renders using the query’s column metadata — types, formatting, choice labels all work.

Query Views vs Entity Views

Aspect	Entity view (`for Order`)	Query view (`for query RevenueByRegion`)
Data source	Entity table	Compiled projection
Row identity	Each row has an entity Id	Rows have no identity (unless Id is explicitly included)
CRUD	Create, edit, delete supported	Read-only
Navigation	Click row → open entity detail	Click row → optional drill-down
Security	Row-level security on entity	Row-level security on root entity (filter applied before aggregation)

Drill-Down

A query view can define drill-down behavior — clicking a row navigates to a filtered entity view:

view RevenueReport for query RevenueByRegion:
  drillDown: OrdersByRegion
  drillFilter: "Customer.Region = $row.Region"
  layout:
    grid:
      column Region
      column Revenue
      column OrderCount

Clicking the “US” row navigates to the OrdersByRegion view with filter Customer.Region = 'US'. The $row.Region reference binds to the clicked row’s column value. The $row. prefix distinguishes row references from @Name query parameter references — drill-down filters are evaluated at navigation time on the client, not at query execution time.

API Surface

REST Endpoint

POST /api/{appSlug}/query/{queryName}
{
  "parameters": { "StartDate": "2026-01-01", "EndDate": "2026-03-31" },
  "limit": 50,
  "offset": 0
}

Response:
{
  "columns": [
    { "name": "Region", "type": "String", "sourceEntity": "Customer", "sourceProperty": "Region" },
    { "name": "Revenue", "type": "Decimal", "aggregateFunction": "SUM", "sourceEntity": "Order", "sourceProperty": "Total" },
    { "name": "OrderCount", "type": "Int64", "aggregateFunction": "COUNT" }
  ],
  "rows": [
    ["US", 125000.00, 342],
    ["EU", 89000.00, 218]
  ],
  "totalCount": 5
}

MCP Tool Surface

Queries are exposed as MCP tools for AI agent consumption:

{
  "name": "execute_query",
  "description": "Execute a named query defined in the application",
  "parameters": {
    "queryName": "RevenueByRegion",
    "parameters": { "StartDate": "2026-01-01" },
    "limit": 10
  }
}

An agent can also discover available queries via a list_queries tool, inspect their parameters, execute with appropriate values, and reason about results using column metadata. The self-describing column metadata means an agent can understand that “Revenue is a SUM of Order.Total (Decimal), grouped by SalesPerson.Name (String)” without any additional schema documentation.

Client Rendering

The client receives column metadata alongside rows and builds grids automatically:

String columns → text cells
Decimal columns → right-aligned, currency formatted (if source property has formatting)
DateTime columns → date formatted
Choice columns → render choice labels (choice ID provided for lookup)
Aggregate columns → shown with aggregate badge in column header

Query Builder UI

The query builder is the designer for query metadata — same pattern as the entity designer edits entity metadata:

Root entity picker — select the starting entity
Column palette — shows available properties from root entity + relations. Drag to add.
Aggregate toggle — click a column to wrap it in SUM/COUNT/AVG/MIN/MAX
Filter builder — visual filter expression builder (same component as security rule editor)
GroupBy — auto-populated from non-aggregate columns when aggregates are present
Sort & limit — dropdown for sort column, direction, row limit
Parameter definitions — add typed parameters for dynamic filtering
Live preview — executes the query and shows results in a grid below the builder

Pagination

For flat mode queries over large tables, offset-based pagination (OFFSET N) degrades at depth because the database still scans skipped rows. v1 uses offset pagination — simple, works fine for aggregate queries and moderate flat result sets.

Cursor-based pagination uses the orderBy column value as the cursor:

-- For ascending order by CreatedAt:
WHERE ("CreatedAt", "Id") > (@cursor_created_at, @cursor_id)
ORDER BY "CreatedAt" ASC, "Id" ASC
LIMIT @page_size

The cursor is a composite of (sort column value, entity ID) to guarantee uniqueness even when sort values are identical. The query API is designed to accept a future cursor parameter alongside the existing limit/offset.

Dashboard Composition (Future)

A query block is a single data source. A dashboard composes multiple queries into a visual layout:

# Individual queries
query RevenueByRegion for Order:
  columns:
    Customer.Region
    SUM(Total) as Revenue
  groupBy: Customer.Region

query OrdersByStatus for Order:
  columns:
    Status
    COUNT(Id) as Count
  groupBy: Status

query RecentOrders for Order:
  columns:
    OrderNumber
    Customer.Name as Customer
    Total
    Status
    CreatedAt
  orderBy: CreatedAt DESC
  limit: 10

# Dashboard view composes multiple queries
view SalesDashboard for page:
  layout:
    Container direction="row" gap=4:
      chart RevenueByRegion type="bar":
        x: Region
        y: Revenue
      chart OrdersByStatus type="pie":
        segment: Status
        value: Count
    grid RecentOrders:
      column Customer
      column Total
      column Status

Each chart/grid in a dashboard binds to a named query, which provides both the data and the column metadata for rendering. The query builder UI naturally extends to dashboard building: pick queries, arrange them in a layout, bind chart axes to columns.

Test Specifications

The test suite doubles as a specification. Here are representative examples showing expected behavior.

Compilation Tests

test "Flat query compiles":
  compile:
    query OrderList for Order:
      columns:
        OrderNumber
        Customer.Name as CustomerName
        Total
      orderBy: CreatedAt DESC

test "Aggregate without groupBy is error":
  compile:
    query Bad for Order:
      columns:
        OrderNumber
        SUM(Total) as Revenue
  expect error: "Aggregate column 'Revenue' requires a groupBy clause"

test "Non-grouped non-aggregate column is error":
  compile:
    query Bad for Order:
      columns:
        OrderNumber
        SUM(Total) as Revenue
      groupBy: Customer.Region
  expect error: "Column 'OrderNumber' must be aggregated or included in groupBy"

test "Having without groupBy is error":
  compile:
    query Bad for Order:
      columns:
        SUM(Total) as Revenue
      having: "Revenue > 50000"
  expect error: "having clause requires groupBy"

Runtime Tests

test "Flat query returns projected rows":
  setup:
    create Customer: { Name: "Acme", Region: "US" }
    create Order: { Customer: Customer, OrderNumber: "ORD-1", Total: 500, Status: "Completed" }
    create Order: { Customer: Customer, OrderNumber: "ORD-2", Total: 300, Status: "Draft" }
  when:
    execute query OrderList
  then:
    rowCount: 2
    columns: [OrderNumber, CustomerName, Total]

test "Aggregate query groups and sums":
  setup:
    create Customer: { Name: "Acme", Region: "US" }
    create Customer: { Name: "Globex", Region: "EU" }
    create Order: { Customer: Acme, Total: 500, Status: "Completed" }
    create Order: { Customer: Acme, Total: 300, Status: "Completed" }
    create Order: { Customer: Globex, Total: 200, Status: "Completed" }
  when:
    execute query RevenueByRegion
  then:
    rowCount: 2
    row 0: { Region: "US", Revenue: 800, OrderCount: 2 }
    row 1: { Region: "EU", Revenue: 200, OrderCount: 1 }

test "Query respects security filter":
  when:
    execute query RevenueByRegion as @User[Role = "RegionalManager", Region = "US"]
  then:
    rowCount: 1
    row 0: { Region: "US" }

test "Having clause filters groups":
  setup:
    create Customer: { Name: "Big", Region: "US" }
    create Customer: { Name: "Small", Region: "EU" }
    create Order: { Customer: Big, Total: 30000, Status: "Completed" }
    create Order: { Customer: Big, Total: 25000, Status: "Completed" }
    create Order: { Customer: Small, Total: 5000, Status: "Completed" }
  when:
    execute query HighRevenueRegions
  then:
    rowCount: 1
    row 0: { Region: "US", Revenue: 55000 }

Aggregate Infrastructure Tests

test "SUM aggregate in filter":
  setup:
    create Customer: { Name: "Acme" }
    create Order: { Customer: Customer, Total: 500 }
    create Order: { Customer: Customer, Total: 300 }
    create Customer: { Name: "Empty Co" }
  when:
    query Customer where "SUM(Orders.Total) > 700"
  then:
    count: 1
    exists Customer: { Name: "Acme" }

test "Aggregate on empty collection returns no match":
  when:
    query Customer where "SUM(Orders.Total) > 0"
  then:
    # "Empty Co" has no orders — SUM is NULL, not 0. Should not match.
    count: 1
    exists Customer: { Name: "Acme" }

test "Collection hop with multiple matches does not duplicate parent":
  setup:
    create Customer: { Name: "Multi" }
    create Order: { Customer: Customer, Status: "Shipped" }
    create Order: { Customer: Customer, Status: "Shipped" }
  when:
    query Customer where "Orders.Status = 'Shipped'"
  then:
    # EXISTS prevents duplicates — only 1 Customer row returned
    count: 1

test "Negated collection hop":
  when:
    query Customer where "NOT Orders.Status = 'Shipped'"
  then:
    count: 1
    exists Customer: { Name: "Inactive" }

What Was Explicitly Deferred

Feature	Why
Cross-scope aggregate comparison (`SUM(A) > SUM(B)`)	Rare pattern, requires scalar subqueries
Window functions (ROW_NUMBER, RANK, PARTITION BY)	Doesn’t fit the metadata-driven model — window functions require explicit column selection which breaks the “define in metadata, query automatically” model
UNION / INTERSECT	Cross-entity set operations don’t fit the single-entity query model
Arbitrary subqueries	The platform generates EXISTS subqueries for collection hops automatically — users write `Orders.Status = 'Shipped'`, not raw SQL. Exposing arbitrary subquery syntax would break the abstraction
Raw SQL escape hatch	Security and portability risk for application authors
Filter AST rewriting / optimization	The current path produces reasonable queries. Compilation caching gives better ROI than AST optimization at this stage
LEFT JOIN aggregation (“include zero-count groups”)	Projection-mode extension for the future
Computed columns	Expressions like `Total * Quantity as LineTotal` beyond aggregates

What This Enables

Feature	How Query Blocks Help
Dashboard views	Compose multiple queries into a single layout with charts and grids
Chart controls	Bind chart axes to query columns
Scheduled reports	Execute a query on a schedule, email the results
Data export	Export query results as CSV/Excel (structured by column metadata)
MCP analytics	AI agents query application data with full metadata context
Materialized views	Cache query results for expensive aggregations