Skip to content

SKILL.md

Latest commit

 

History

History
399 lines (316 loc) · 15.3 KB

SKILL.md

File metadata and controls

399 lines (316 loc) · 15.3 KB
Raw
name objectstack-query
description Construct ObjectQL queries — filters, sorting, pagination, aggregation, relation expansion, and full-text search. Use when the user is writing a query DSL expression or picking a pagination strategy. Do not use for defining objects / fields / relationships (see objectstack-data), for designing the API endpoint that exposes a query (see objectstack-api), or for a list view's filter rules / dashboard datasets (see objectstack-ui).
license Apache-2.0
compatibility Requires @objectstack/spec 17.x (Zod v4 schemas)
metadata
author version domain tags
objectstack-ai
1.4
query
query, filter, sort, paginate, aggregate, ObjectQL, full-text

Query Design — ObjectStack Query DSL

Calling Convention — object is the FIRST ARGUMENT

Surface Shape Legal option keys
engine find / findOne engine.find('task', {…}, { context }) context, where, fields, orderBy, limit, offset, search, searchFields, expand — plus the six driver passthrough keys transaction, tenantId, tenantIds, timezone, bypassTenantAudit, preserveAudit
engine aggregate engine.aggregate('deal', {…}) context, where, groupBy, aggregations, having, timezone
engine count engine.count('task', {…}) context, where
protocol / REST findData({ object: 'task', query: {…} }) object sits OUTSIDE the query
nested expand value a QueryAST — { object, fields, where } (see Expand)

ENGINE_FIND_OPTION_KEYS / ENGINE_AGGREGATE_OPTION_KEYS are closed sets: a key outside the row is refused by name (find('task') does not recognise option 'bogus'), never ignored. A standalone { object: 'account', limit: 20 } literal is therefore a QueryAST — legal as findData's query or an expand value — not an engine option bag. top folds to limit, filter to where, before that check.

The passthrough six ride along on find/findOne (and on update/delete) because there the option bag IS the base of the driver options, which is how an explicit tenantId reaches the driver. count and aggregate never forward the bag, so on those two the same keys are deliberately ILLEGAL — accepting them would be the silently-ignored option this check exists to close.

Which filter dialect?

Writing… Dialect Owner
an ObjectQL where the $ operators below this skill
a list view / nav-item filter [{ field, operator, value }] over the 20-operator VIEW_FILTER_OPERATORS enum (equals, icontains, is_null, before, between, …) — unknown operators are refused at parse objectstack-ui
a dataset measure filter the measure's own filter objectstack-ui

Execution Context (context)

The RLS / system-read escape hatch. A hook, job or endpoint that reads without one runs as whatever identity the caller carried — org-scoping hooks then return fewer rows, indistinguishable from "there is no data".

const SYS = { isSystem: true } as const;
const [row] = await engine.find('project', { where: { name }, limit: 1, context: SYS });

Pass any SUBSET of the execution envelope (identity, tenant, transaction): { isSystem: true } for a system read, { flowRunId } for provenance alone. On the READ methods it may sit in the query bag (above) OR in the trailing options argument, engine.find(obj, query, { context }); the trailing one wins when both are given. Writes take only the trailing argument.

Removed Keys → Live Replacement

Removed key Live replacement
query.cursor keyset paging — where on the sort key + orderBy + limit
query.joins expand, or a nested relation filter
query.distinct groupBy the fields — each unique combination is one row
query.windowFunctions report/dashboard metadata (objectstack-ui), or rank / accumulate in app code
aggregation distinct: true count_distinct
aggregation array_agg / string_agg none — read the rows with fields and shape them in the caller, or materialise the roll-up as a stored field

All six are tombstoned in @objectstack/spec 17: tsc types them never, and a query carrying one fails to parse with the upgrade prescription. The retirement procedure and the full tombstone register are objectstack-upgrade.

Quick Reference — Detailed Rules

  • Filters — all operators, logical combinations, nested relations, date macros and session tokens
  • Aggregation — groupBy, date bucketing, functions, having, per-measure filter
  • Pagination — offset vs keyset, best practices, performance

Filter Operators

Implicit Equality (Shorthand)

The simplest filter — field equals value:

{ where: { status: 'active' } }
// SQL: WHERE status = 'active'

Comparison Operators

Operator Purpose SQL Equivalent Types
$eq Equal = Any
$ne Not equal <> Any
$gt Greater than > Number, Date
$gte Greater than or equal >= Number, Date
$lt Less than < Number, Date
$lte Less than or equal <= Number, Date 
{ where: { age: { $gte: 18 } } }
// SQL: WHERE age >= 18

Set & Range Operators

Operator Purpose SQL Equivalent
$in In list IN (...)
$nin Not in list NOT IN (...)
$between Inclusive range BETWEEN ? AND ? 
{ where: { status: { $in: ['active', 'pending'] } } }
{ where: { amount: { $between: [100, 500] } } }

String Operators

Operator Purpose SQL Equivalent
$contains Contains substring LIKE '%?%'
$notContains Does not contain NOT LIKE '%?%'
$startsWith Starts with prefix LIKE '?%'
$endsWith Ends with suffix LIKE '%?'
$icontains Contains, case-blind LIKE '%?%' folded
$like Whole-value pattern, caller binds % / _ LIKE ?
$ilike $like, case-blind ILIKE ?

$contains / $notContains / $startsWith / $endsWith compare CASE-SENSITIVELY; $icontains is the case-INSENSITIVE twin (ASCII folding only). So the user-facing cases want $icontains:

{ where: { email: { $icontains: '@company.com' } } }

Full table, $like portability and the $ilike boundary: filter rules.

Null & Existence Operators

Operator Purpose SQL / NoSQL
$null Is null check IS NULL / IS NOT NULL
$exists Has a value IS NOT NULL / IS NULL 
{ where: { deleted_at: { $null: true } } }

Logical Operators

Combine conditions with $and, $or, and $not:

// OR: active accounts OR accounts with high revenue
{ where: { $or: [{ status: 'active' }, { revenue: { $gt: 1000000 } }] } }

// AND + OR combined
{
  where: {
    $and: [
      { type: 'enterprise' },
      { $or: [{ region: 'us' }, { region: 'eu' }] },
    ]
  }
}

// NOT: exclude closed accounts
{ where: { $not: { status: 'closed' } } }

Nested Relation Filters

Filter through relationships without an explicit join:

// Accounts whose related contact has a verified profile
{ object: 'account', where: { contact: { profile: { verified: true } } } }

Cross-field comparisons

{ $field: '...' } compares two columns of the same row, in a comparison position only — see filter rules → Field References.

Sorting

Sort with orderBy — an array of sort nodes:

{
  object: 'account',
  orderBy: [
    { field: 'priority', order: 'desc' },
    { field: 'name', order: 'asc' },      // Secondary sort
  ]
}

Rules:

  • Order of array elements defines sort priority
  • Default order is 'asc' — you can omit it for ascending sorts
  • Sort fields should be indexed for performance (see objectstack-data indexing rules)

Pagination

// Offset paging — page 3
{ object: 'account', limit: 20, offset: 40 }

When to use: UI pages, small datasets, "jump to page N". It degrades on large offsets — the database still scans the skipped rows.

For keyset paging (infinite scroll, APIs, large datasets, real-time feeds), filter past the last row you saw with a where on the sort key, and always orderBy that same field in that same direction — the pattern, the direction rule and the pitfalls are pagination rules.

Aggregation

The six functions (count, sum, avg, min, max, count_distinct), date bucketing, having, and the per-measure filter are aggregation rules. The call shape:

// Total revenue per region
const rows = await engine.aggregate('deal', {
  groupBy: ['region'],
  aggregations: [
    { function: 'sum', field: 'amount', alias: 'total_revenue' },
    { function: 'count', alias: 'deal_count' },
  ],
});
// SQL: SELECT region, SUM(amount) AS total_revenue, COUNT(*) AS deal_count
//      FROM deal GROUP BY region

fields and orderBy are NOT in ENGINE_AGGREGATE_OPTION_KEYS — do not put them in an aggregate bag. Grouped fields are auto-selected into the result rows; read each measure under its alias, and reference that same name from having. groupBy entries may be objects for date bucketing — { field: 'closed_at', dateGranularity: 'quarter' }.

Expand (Related Records)

Load related records through lookup / master_detail fields. Keep the foreign key in fields — the relation is carried by that column:

const tasks = await engine.find('task', {
  fields: ['title', 'status', 'assignee', 'project'],   // the FK columns stay
  expand: {
    assignee: { object: 'user', fields: ['name', 'email'] },
    project: {
      object: 'project',
      fields: ['name'],
      expand: { org: { object: 'org', fields: ['name'] } },   // nested expand
    },
  },
});

Rules:

  • The projection must RETAIN the foreign-key column. fields: ['title'] with expand: { project: … } resolves nothing: the engine reads the FK off each record and skips the relation when it is absent, so the call returns rows with no related data and no error.
  • Max expand depth is 3 by default
  • The engine resolves expands via batch $in queries (not N+1)
  • Keys in expand must be lookup or master_detail field names
  • Each expand value is a nested QueryAST, but the engine applies select (fields) and filter (where) only — per-parent limit / offset / orderBy are NOT applied on this path. To paginate or sort related records, query the related object directly.

Full-Text Search

The canonical form is a bare string with a sibling searchFields:

const rows = await engine.find('article', {
  search: 'machine learning',
  searchFields: ['title', 'content'],
  limit: 10,
});
// Executes as:
// { $and: [
//   { $or: [{ title: { $icontains: 'machine' } }, { content: { $icontains: 'machine' } }] },
//   { $or: [{ title: { $icontains: 'learning' } }, { content: { $icontains: 'learning' } }] },
// ]}

Each term becomes an $or of $icontains predicates across the resolved searchable fields, and whitespace-separated terms are AND-ed (every term must hit some field). select/status fields match by option label, mapped to stored values.

One knob, three spellings: emit searchFields (the engine option). The protocol normalizes $searchFields onto it, and the object form search: { query, fields } spells the same narrowing fields.

Omit it to search the object's declared searchableFields (or an auto-default of name/title + short-text fields), resolved server-side. It can only narrow that set, never widen it: over the REST/protocol ingress a name outside it is 400 INVALID_FIELD, not a silent fall-back to a full scan. The object form search: { query, fields } stays available for the Tier-2 knobs below.

⚠️ Validates, then silently ignored — never emit these. fuzzy, boost, operator, minScore, language and highlight are the whole set; their .describe() markers say so. Terms are always AND-ed; there is no relevance scoring or highlighting.

search never traverses. A dotted path is refused — searchFields: ['project_id.name'] names a column task does not declare. Mirror the related record's title into a stored field on the queried object and search that; the field, the write hooks and the lint wording are objectstack-data → Search Fields (searchableFields). To filter by a related record's column use a nested relation filter; to display it, expand.

Common Patterns

Cross-Object Queries: Which Tool to Use?

Scenario Use
Load lookup fields for display expand
Filter parent by child conditions Nested relation filter
Keyword-search by a related record's title Mirror the title into a stored field on this object and search that — search never traverses
Paginate/sort a parent's related records Query the related object directly
Analytical queries across objects Report/dashboard metadata, or separate queries combined in app code

Pagination Pattern for APIs

const page = await engine.find('account', {
  where: { status: 'active' },
  fields: ['id', 'name', 'email'],
  orderBy: [{ field: 'name', order: 'asc' }],
  limit: 20,
  offset: (pageNumber - 1) * 20,
});

Dashboard Aggregation Pattern

Every KPI on a dashboard shares one aggregate call — unconditional measures plain, conditional ones carrying their own filter. where scopes the whole call, so reach for it only when every measure wants the same scope:

const [kpis] = await engine.aggregate('deal', {
  aggregations: [
    { function: 'count', alias: 'total_deals' },
    { function: 'sum', field: 'amount', alias: 'pipeline_value' },
    { function: 'avg', field: 'amount', alias: 'avg_deal_size' },
    { function: 'count', alias: 'won_deals', filter: { stage: 'closed_won' } },
  ],
});

Dashboards and reports themselves — KPI widgets, compareTo, dateGranularity bucketing, matrix rows/columns — are metadata, not hand-written queries: model them in objectstack-ui and the renderer issues the queries.

Verify your work

Most queries run at runtime (smoke-test them with os data query or a vitest test), but query metadata — list-view filter specs and report/dashboard datasets — is validated statically. After editing those, run:

os validate     # schema + CEL predicates + widget/dataset bindings (no artifact)
# or: os build  # the same gates, plus emits dist/

A dashboard widget whose dataset / dimensions / values don't resolve fails here instead of rendering an empty chart (ADR-0021). In a scaffolded project the gate is npm run validate. See objectstack-platform → Verify your work.

References

See references/_index.md for the full list of Zod schemas (with one-line descriptions) — pointers into node_modules/@objectstack/spec/src/. Always Read the source for exact field shapes; do not rely on memory of property names.