Skip to content

SKILL.md

Latest commit

 

History

History
565 lines (443 loc) · 14.3 KB

SKILL.md

File metadata and controls

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

Query Design — ObjectStack Query DSL

Expert instructions for constructing data queries using the ObjectStack Query DSL. This skill covers filter expressions, sorting, pagination, aggregation, joins, window functions, full-text search, and the expand system for related records.

Skill Boundaries

Need Use instead
Define objects, fields, or relationships objectstack-data
Define REST API endpoints or auth objectstack-api
Build views, dashboards, or apps objectstack-ui
Create a plugin or register services objectstack-platform

When to Use This Skill

  • You are constructing a filter expression for record retrieval
  • You need to sort or paginate query results
  • You are writing aggregation queries (count, sum, avg, group by)
  • You need to expand related records through lookups
  • You are implementing full-text search across fields
  • You need window functions for analytical queries
  • You are choosing between offset vs cursor pagination

Core Concepts

Query Structure (QueryAST)

Every ObjectStack query follows the QuerySchema structure:

{
  object: 'account',           // Target object (required)
  fields: ['name', 'email'],   // SELECT — fields to retrieve
  where: { status: 'active' }, // WHERE — filter conditions
  orderBy: [{ field: 'created_at', order: 'desc' }],  // ORDER BY
  limit: 20,                   // LIMIT — max records
  offset: 0,                   // OFFSET — skip records
}

Key rule: object is the only required property. Everything else is optional.

Quick Reference — Detailed Rules

For comprehensive documentation with incorrect/correct examples:

  • Filters — All operators, logical combinations, nested relations
  • Aggregation — GroupBy, aggregation functions, HAVING, window functions
  • Pagination — Offset vs cursor, best practices, performance

Filter Operators

ObjectStack uses a declarative, database-agnostic filter DSL inspired by Prisma, Strapi, and MongoDB.

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

{ where: { created_at: { $gt: '2025-01-01' } } }
// SQL: WHERE created_at > '2025-01-01'

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'] } } }
// SQL: WHERE status IN ('active', 'pending')

{ where: { amount: { $between: [100, 500] } } }
// SQL: WHERE amount BETWEEN 100 AND 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 '%?' 
{ where: { email: { $contains: '@company.com' } } }
// SQL: WHERE email LIKE '%@company.com%'

Null & Existence Operators

Operator Purpose SQL / NoSQL
$null Is null check IS NULL / IS NOT NULL
$exists Field exists (NoSQL) MongoDB $exists 
{ where: { deleted_at: { $null: true } } }
// SQL: WHERE deleted_at IS NULL

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:

// Filter accounts where the related contact has a verified profile
{
  object: 'account',
  where: {
    contact: {                    // Relation field name
      profile: {                  // Nested relation
        verified: true
      }
    }
  }
}

Field References (Cross-Field Comparisons)

Compare two fields using $field:

// Where actual_revenue > estimated_revenue
{
  where: {
    actual_revenue: { $gt: { $field: 'estimated_revenue' } }
  }
}

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 Pagination (Simple)

{
  object: 'account',
  limit: 20,
  offset: 40,   // Skip first 40 records (page 3)
}

When to use: UI pages, small datasets (<100K records), when you need "jump to page N".

Pitfall: Offset pagination degrades on large offsets — the database still scans skipped rows.

Cursor Pagination (Performant)

{
  object: 'account',
  limit: 20,
  cursor: { id: 'last-seen-id' },
  orderBy: [{ field: 'id', order: 'asc' }],
}

When to use: Infinite scroll, APIs, large datasets, real-time feeds.

Rule: The cursor fields must match orderBy fields. The engine uses them to generate WHERE id > ? instead of OFFSET.

OData Compatibility

top is an alias for limit (for OData-style APIs):

{ object: 'account', top: 50 }
// Equivalent to: { object: 'account', limit: 50 }

Aggregation

Basic Aggregation Functions

Function Purpose SQL
count Count rows COUNT(*) or COUNT(field)
sum Sum values SUM(field)
avg Average AVG(field)
min Minimum MIN(field)
max Maximum MAX(field)
count_distinct Unique count COUNT(DISTINCT field)
array_agg Collect into array ARRAY_AGG(field)
string_agg Concatenate strings STRING_AGG(field, ',')

GroupBy + Aggregation

// Total revenue per region
{
  object: 'deal',
  fields: ['region'],
  aggregations: [
    { function: 'sum', field: 'amount', alias: 'total_revenue' },
    { function: 'count', alias: 'deal_count' },
  ],
  groupBy: ['region'],
  orderBy: [{ field: 'total_revenue', order: 'desc' }],
}
// SQL: SELECT region, SUM(amount) AS total_revenue, COUNT(*) AS deal_count
//      FROM deal GROUP BY region ORDER BY total_revenue DESC

HAVING Clause

Filter groups after aggregation:

{
  object: 'deal',
  fields: ['region'],
  aggregations: [
    { function: 'sum', field: 'amount', alias: 'total_revenue' },
  ],
  groupBy: ['region'],
  having: { total_revenue: { $gt: 100000 } },
}
// SQL: ... HAVING SUM(amount) > 100000

Filtered Aggregation

Apply a filter to a specific aggregation only:

{
  object: 'order',
  aggregations: [
    { function: 'count', alias: 'total_orders' },
    { function: 'count', alias: 'high_value_orders',
      filter: { amount: { $gt: 1000 } } },
  ],
}
// SQL: COUNT(*) AS total_orders,
//      COUNT(*) FILTER (WHERE amount > 1000) AS high_value_orders

Expand (Related Records)

Load related records through lookup/master_detail fields:

{
  object: 'task',
  fields: ['title', 'status'],
  expand: {
    assignee: {
      object: 'user',
      fields: ['name', 'email'],
    },
    project: {
      object: 'project',
      fields: ['name'],
      expand: {
        org: { object: 'org', fields: ['name'] }  // Nested expand
      }
    }
  }
}

Rules:

  • 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 full QueryAST — you can filter, sort, and paginate within it

Joins (Advanced)

For cross-object queries beyond what expand provides:

{
  object: 'order',
  fields: ['id', 'amount'],
  joins: [
    {
      type: 'inner',       // 'inner' | 'left' | 'right' | 'full'
      object: 'customer',
      alias: 'c',
      on: { 'order.customer_id': { $eq: { $field: 'c.id' } } },
    }
  ],
}

Join Strategy Hints

Strategy When to use
auto Default — engine decides
database Both objects on same datasource
hash Cross-datasource, moderate data
loop Small right-side lookup table

Full-Text Search

{
  object: 'article',
  search: {
    query: 'machine learning',
    fields: ['title', 'content'],
    fuzzy: true,
    boost: { title: 2.0 },
    highlight: true,
  },
  limit: 10,
}

Options:

  • fuzzy: true — tolerates typos
  • boost — field-specific relevance weighting
  • operator: 'and' | 'or' — match all terms or any term
  • minScore — minimum relevance threshold
  • language — text analysis language

Window Functions (Analytics)

Window functions compute values across row sets without collapsing results:

// Rank products by sales within each category
{
  object: 'product',
  fields: ['name', 'category', 'sales'],
  windowFunctions: [
    {
      function: 'row_number',
      alias: 'category_rank',
      over: {
        partitionBy: ['category'],
        orderBy: [{ field: 'sales', order: 'desc' }],
      }
    }
  ],
}

Available Window Functions

Function Purpose
row_number Sequential number within partition
rank Rank with gaps for ties
dense_rank Rank without gaps
lag / lead Access previous/next row value
first_value / last_value First/last value in window
sum / avg / count / min / max Running aggregates

Common Patterns

Expand vs Join: Which to Use?

Scenario Use
Load lookup fields for display expand
Filter parent by child conditions Nested relation filter
Cross-datasource joins joins with strategy: 'hash'
Analytical queries across tables joins
Simple parent→child navigation expand

Pagination Pattern for APIs

// Page-based API response
{
  object: 'account',
  where: { status: 'active' },
  fields: ['id', 'name', 'email'],
  orderBy: [{ field: 'name', order: 'asc' }],
  limit: 20,
  offset: (page - 1) * 20,
}

Dashboard Aggregation Pattern

// KPI dashboard: multiple aggregations on same object
{
  object: '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' } },
  ],
}

CRM Analytics Query Blueprint

Use dashboards/reports metadata as the practical query pattern source:

Query Need CRM Reference Pattern
KPI widgets dashboards/sales.dashboard.ts Filtered aggregates (sum, count, avg) over opportunity. Add compareTo: 'previousPeriod' | 'previousYear' on the widget for a one-line period-over-period delta.
Time-series chart dashboards/sales.dashboard.ts Date filters + categoryGranularity: 'day' | 'week' | 'month' | 'quarter' | 'year' for server-side bucketing — never bucket by hand on the client. Pair with compareTo for an aligned YoY overlay.
Matrix report reports/opportunity.report.ts groupingsDown + groupingsAcross + dateGranularity: 'quarter'
Funnel summary reports/opportunity.report.ts Multi-level grouping (owner -> stage) + aggregated measures
Operational filter dashboard/report filters Prefer declarative operators ($ne, $nin, $gte) over hardcoded SQL

For metadata app development, model analytics in report/dashboard metadata first; only fall back to custom query code when schema limits require it.

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.