version: "1.0.3" name: rushdb-query-builder description: Build RushDB queries, searches, filters, and aggregations. Use whenever an agent needs to list or filter records, count or group data, traverse relationships, run semantic/vector search, construct a findRecords call, discover live labels and properties, or recall canonical EPISODE and MEMORY_FACT records with mandatory authorization scope prefilters.

RushDB Query Builder

A discovery-first workflow for safely and correctly querying RushDB.

Never guess label names, property names, or field values. Always discover them first.

Prerequisites

• RushDB MCP server must be connected — it provides getSchemaMarkdown, findRecords, and all other tools used in this skill. Setup: npx @rushdb/mcp-server (requires RUSHDB_API_KEY env var). See https://docs.rushdb.com/mcp-server/quickstart
• If the MCP tools are not available in the current session, tell the user the MCP server is not configured and link them to the quickstart above.

Mandatory Workflow (always follow this order)

Step 0 — Schema (every session, first call)

Call getSchemaMarkdown before any other tool. It returns:

• All label names (case-sensitive — use them exactly)
• All property names and types per label
• Array fields shown as type[]; in structured getSchema, isArray: true marks primitive-array properties
• Value ranges for numeric/datetime fields
• The full relationship map between labels
• A Semantic Search column per property: non-— value means the property is indexed and queryable via vectorSearch

Do not call findLabels, findProperties, or findRecords before this.

getSchemaMarkdown()

If the schema looks stale (e.g. new labels or properties were added recently), pass { force: true } to bypass the 1-hour cache and force a fresh recalculation.

Step 1 — Classify Intent

Before building a query, identify what is being asked:

⚠ Aggregation intent: NEVER fetch raw records and count them manually. ALWAYS use select + groupBy on findRecords.

Step 2 — Load Query Spec (for complex queries)

Before calling findRecords with any of these, call getSearchQuerySpec:

• Date/time filters or date ranges
• select + groupBy (metrics, aggregations)
• Relationship traversal (where keys that are label names)
• Vector/semantic similarity

getSearchQuerySpec returns the complete operator reference, select/groupBy syntax, late-ordering rules, and annotated examples. It is the source of truth — do not guess syntax.

Step 3 — Build and Execute

Use only label and property names from the schema. Labels are case-sensitive.

Root-label rule:

• labels contains only the root record type(s) you want returned as rows.
• Related record types belong inside where traversal blocks, not beside the root in labels.
• If a related record is needed in select or groupBy, declare $alias on that related label in where.

Top-N rule:

• "Top N records by a scalar field on the same label" is a listing: use orderBy + limit, no select.
• "Which parent has most/more/least/less/fewer/fewest related children" is an aggregation: root the parent label, traverse the child label with $alias, count the child alias, group by the parent's display property from the schema.
• Related-count direction: most/more/highest/largest/greatest => desc; least/less/fewer/fewest/lowest/smallest => asc.
• Condition-verb exception: when the ranking condition ("won", "wrote", "approved", …) is expressed only by a scalar reference property on the related label (author_id / winner_* / approvedBy style — confirm via discovery) and no relationship type expresses it, root on the label that owns that property and groupBy it instead. where cannot correlate two records — no $record.*/$alias.* values and no $ref ($ref is select-only).
• Do not let the related/filter label become the root just because it owns the filtered field. If the requested parent-to-related traversal path is absent, do not silently switch roots; state the path is unavailable or use a closest valid fallback with an explicit assumption.

Named-reference rule:

• When filtering a display property with free text the user typed, resolve the property from schema discovery first (often name or title — never assume one exists), then default to { $contains: "<user text>" }. This applies to filters on any label, including related labels traversed with $alias.
• Use exact equality only when the value is an ID, or the user explicitly asks for an exact match (e.g. "named exactly").
• If you need to confirm a canonical value before filtering, use discovery (list/search a few records) rather than guessing an exact string.

Example:

findRecords({
  labels: ['DEPARTMENT'],
  where: { PROJECT: { $alias: '$project' } },
  select: {
    department: '$record.name',
    projects: { $count: '$project' }
  },
  groupBy: ['$record.name'],
  orderBy: { projects: 'desc' }
})

Tool Reference

Discovery

Semantic search: Properties listed in getSchema with a non-empty vectorIndexes array are eligible. Use vectorSearch with the matching propertyName and labels; semanticSearch is only a deprecated compatibility alias.

Querying

Mutations (confirm before use)

Resource-Local where Rule

Do not reuse where blindly across resource types:

The same Record filter used in findRecords can be passed directly to bulkDeleteRecords, exportRecords, findLabels, or findProperties. For relationship lookup, move Record predicates into source.where or target.where.

Canonical Agent-Memory Recall

Treat canonical memory scope as authorization, not ranking. For EPISODE and MEMORY_FACT, obtain these exact values from trusted host/application metadata:

agentId
profileId
privacyScope
participantScopeHash
sandboxEligible

Pass all five in vectorSearch.where. Never infer them from query text or recalled records, and never remove predicates to increase result count.

Search the two semantic properties separately:

{
  "labels": ["EPISODE"],
  "propertyName": "summary",
  "query": "authentication decision",
  "where": {
    "agentId": "<trusted-agent-id>",
    "profileId": "<trusted-profile-id>",
    "privacyScope": "private",
    "participantScopeHash": "<trusted-hash>",
    "sandboxEligible": false,
    "externalSessionId": { "$ne": "<current-session-id>" }
  },
  "limit": 8
}

For facts, switch to labels: ["MEMORY_FACT"], propertyName: "text", and add active: true. Merge results by deterministic eventId/factId in application code. If trusted scope is unavailable, use the runtime's native memory recall rather than issuing a broader query.

Quick Operator Cheat Sheet

// ── Equality ──────────────────────────────────────────────────────────
name: "Alice"
isActive: true
age: 30
// ── String (all comparisons are case-insensitive) ─────────────────────
name: { $contains: "ali" }
name: { $startsWith: "A" }
name: { $endsWith: "e" }
name: { $ne: "deleted" }
status: { $in: ["active", "pending"] }       // matches any value in list
status: { $nin: ["archived", "deleted"] }    // matches none of these values
// ── Number ────────────────────────────────────────────────────────────
score: { $gt: 90 }
score: { $gte: 70, $lte: 100 }              // between (inclusive)
score: { $in: [85, 90, 95] }
score: { $nin: [0, -1] }
// ── Datetime ──────────────────────────────────────────────────────────
// ISO 8601 strings work for equality, $in, and range operators:
created: "2024-01-01T00:00:00Z"
created: { $gte: "2024-04-23T00:00:00Z" }            // "after a timestamp" / relative ranges
created: { $in: ["2024-01-01T00:00:00Z", "2024-06-01T00:00:00Z"] }
// Use component objects for calendar-semantic ranges (year / month / day boundaries):
created: { $gte: { $year: 2024, $month: 1 }, $lt: { $year: 2024, $month: 2 } }  // January 2024
created: { $gte: { $year: 1990 }, $lt: { $year: 2000 } }                         // the 1990s
// Available components: $year $month $day $hour $minute $second $millisecond $microsecond $nanosecond
// ── Existence / type ──────────────────────────────────────────────────
email: { $exists: true }                     // field is present and not null
phone: { $exists: false }                    // field absent
age:   { $type: "number" }                   // "string"|"number"|"boolean"|"datetime"
// ── Logical ───────────────────────────────────────────────────────────
// Implicit AND — multiple keys at the same level:
where: { status: "active", score: { $gte: 70 } }
// Explicit operators:
$and: [ { status: "active" }, { score: { $gte: 70 } } ]
$or:  [ { plan: "pro" }, { plan: "enterprise" } ]
$not: { status: "deleted" }
$nor: [ { status: "deleted" }, { status: "archived" } ]   // none must match
$xor: [ { isPremium: true }, { hasTrial: true } ]         // exactly one must match
// ── Relationship traversal — key IS the label, exactly as spelled in the schema ──
where: {
  DEPARTMENT: {
    name: "Engineering",
    headcount: { $gte: 10 }
  }
}
// Named traversal ($alias for use in select/groupBy):
where: { DEPARTMENT: { $alias: "$dept", budget: { $gte: 50000 } } }
// Constrain relationship type or direction:
where: { POST: { $relation: { type: "AUTHORED", direction: "in" } } }
// Multihop over one pattern (hierarchies, "within N degrees") — hops inside $relation:
where: { EMPLOYEE: { $relation: { type: "REPORTS_TO", direction: "out", hops: { max: 4 } } } }
// Cycle/ring detection (fraud rings, circular ownership) — the $cycle operator's value IS the traversal spec:
where: { $cycle: { type: "TRANSFERRED_TO", direction: "out", hops: { min: 2, max: 6 } } }

Each Relationships row in the schema is a directed pattern rooted at that label: (SELF)-[:TYPE]->(OTHER) is outgoing, (SELF)<-[:TYPE]-(OTHER) is incoming. To pin the edge, set $relation: { type: "TYPE", direction } — "out" for ->, "in" for <-. Only patterns shown in the schema are traversable; a scalar *_id property is a plain value, not an edge — never nest a label to "join" on it (root on the owning label and groupBy it instead).

Common mistakes to avoid:

• $label, $direction, $as, $of, $through, $hops — these do not exist (multihop depth is $relation.hops; $relation.hops and $cycle ARE valid)
• Inventing a semantic $relation.type when the schema shows only __RUSHDB__RELATION__DEFAULT__ edges — WRONG: copy the type verbatim from a schema Relationships row, or omit type (untyped traversal is valid)
• Changing a label's case (e.g. uppercasing departments to DEPARTMENTS) — WRONG: labels are case-sensitive; copy them exactly as the schema spells them
• { employee: { $label: "EMPLOYEE" } } — WRONG: key must be the label name
• { EMPLOYEE: { $alias: "$emp" } } — CORRECT
• labels: ["PARENT", "CHILD"] for a parent-child metric — WRONG: keep labels: ["PARENT"], put CHILD in where
• labels: ["CHILD"] for "which parent has most/fewest children" — WRONG when a parent-child path exists: keep the requested parent as root and count the child alias
• groupBy: ["$record"] or ["$child"] — WRONG: dimensional groupBy must include a property, e.g. "$record.name"; self-group uses select key names only
• { fieldA: "$record.id" } or { fieldA: { $eq: "$alias.id" } } — WRONG: where values must be literals. $record.*/$alias.* references work only in select/groupBy/aggregate; as a where value they are matched as a literal string and return nothing (RushDB has no correlated where predicate)
• Simulating a join on a scalar field that is not a relationship in the schema — WRONG: root on the label that owns the field and groupBy it, e.g. { labels: ["LABEL"], select: { count: { $count: "*" } }, groupBy: ["$record.someScalarField"] }
• name: "partial user text" for an incomplete named reference — RISKY: prefer name: { $contains: "partial user text" }
• For calendar-semantic ranges (year / month / day boundaries) use component objects, not ISO strings
• Do NOT include limit when using select (produces mathematically wrong results)

Key Limits

• limit max = 1000
• Never use `limit` with `select` — it restricts the scan and gives wrong totals
• For "how many" simple counts: read total from the findRecords response — do NOT use fn:'count'
• For groupBy self-group and dimensional groupBy: omit limit unless you want "top N"

When to Load the Full Reference

Call getSearchQuerySpec (or read references/search-query-spec.md) for:

• Datetime filters — ISO 8601 strings work with all operators; use component objects for calendar-semantic ranges (year / month / day)
• Select expressions — $count, $sum, $avg, $min, $max, $collect, $timeBucket, math ops, $ref
• groupBy modes — dimensional ($alias.property) vs self-group (select key names)
• Late-ordering rule — critical for correct totals in self-group queries
• Relationship traversal — $alias, $relation, multi-hop BFS algorithm
• Vector similarity inside `findRecords` — use legacy aggregate (not select); prefer vectorSearch for direct semantic retrieval
• Nested collect — building tree-shaped results with label-based $collect
• Validation checklist — before submitting any findRecords call