boxel: Skill for Claude Code

Index query engine — architecture

The engine (packages/runtime-common/index-query-engine.ts) ingests a Query (filter tree + sort + page, defined in query.ts) and compiles it to one SQL statement that must run identically on Postgres and SQLite. Everything about its design follows from two constraints:

1. Field semantics live in card definitions, which resolve asynchronously (DefinitionLookup — a cache read, or a module prerender on a miss). SQL can't be produced until the schema walk happens.
2. SQLite is the floor. No arrays, no unnest, no @> containment, no ANY_VALUE, single-expression COUNT(DISTINCT …). Plural (containsMany / linksToMany) traversal therefore uses json_tree() — the lowest common denominator — and Postgres mirrors it with a custom jsonb_tree() function so one generated query shape serves both.

The pipeline

Query (filter tree)
  │  filterCondition()            stage 0 — sync tree expansion
  ▼
CardExpression                    tokens + DEFERRED schema-dependent nodes
  │  makeExpression()             pass 1 — async, definition-driven
  ▼
Expression                        only strings, params, resolved nodes
  │  expressionToSql()            pass 2 — mechanical rendering (expression.ts)
  ▼
SQL text + binds  ──sqlite──▶  SqliteAdapter.adjustSQL() text rewrites

Stage 0 (filterCondition and the per-operator builders) converts the filter tree into a CardExpression: a flat token array of SQL strings, param() bind markers, and deferred nodes. This is where a predicate is separated into left side / operator / right side:

• left = fieldQuery(path, onRef, …) — the dotted field path, to become a JSON accessor;
• operator = a literal (=, IS NULL, ILIKE, IN (…), range operators);
• right = fieldValue(path, [param(v)], onRef, …) — deferred so the leaf field's serializer can format the bind (formatQuery);
• the whole predicate wraps in fieldArity({ type, path, value, … }), which defers the plural-vs-singular decision.