rushdb-query-builder

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:

Intent	Pattern	Tool
Aggregation	count / total / sum / avg / breakdown / per X / top N by metric / distribution / grouped	findRecords with select + groupBy
Related count ranking	which/what parent has most/more/least/less/fewer/fewest related child records	findRecords with parent labels, child traversal in where, select count alias, groupBy parent field
Listing	show / list / find / search / get	findRecords with where + limit + orderBy
Single record	get by ID, find one unique	getRecord / findOneRecord / findUniqRecord
Relationships	connected to / linked / related	findRelationships
Mutation	create / update / delete	confirm + preview first

⚠ 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

Tool	When to use
getSchemaMarkdown	Step 0 — always first, once per session
getSchema	Same as above but structured JSON; use when you need propertyId values or vectorIndexes per property
findLabels	Skip if getSchemaMarkdown already ran
findProperties	Discover field names + types for a specific label when not in schema
findRelationships	Inspect relationships; where filters edge type/properties, source/target filter endpoint records; no aggregate/groupBy
propertyValues	List distinct values for a property (needs propertyId from getSchema or findProperties)

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

Tool	When to use
findRecords	Primary query/read/list/search tool — listing, filtering, aggregation, groupBy, semantic search
findOneRecord	Return the first matching record
findUniqRecord	Return exactly one record (errors if multiple match)
getRecord	Fetch a single record by ID
getRecordsByIds	Fetch multiple records by IDs
vectorSearch	Direct semantic retrieval over one indexed property; where activates exact prefilter mode
exportRecords	Use only when the user explicitly asks for CSV export/download; do not substitute it for findRecords

Mutations (confirm before use)

Tool	Notes
createRecord	Store a new record
updateRecord	Patch fields on an existing record
setRecord	Replace all fields on an existing record
deleteRecord / deleteRecordById	Destructive — preview first
bulkCreateRecords	Batch insert via nested JSON
bulkDeleteRecords	Destructive batch — always preview with findRecords first
attachRelation	Create a relationship between two records
detachRelation	Remove a relationship

---

Resource-Local where Rule

Do not reuse where blindly across resource types:

Method	Notes
findRecords	where applies to Records. Full query: where + select + groupBy + pagination
findRelationships	where applies to relationship edges (type + user-defined edge properties). Use source/target for endpoint Records; no select/groupBy
findLabels	where applies to Records before label counts are returned
findProperties	where applies to Records before property metadata is returned
exportRecords	where + labels apply to Records; streams CSV only when explicitly requested
bulkDeleteRecords	where + labels apply to Records; destructive — always preview first

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:
: 
: { :  }            
: { : [, ] }


: { : { : , :  }, : { : , :  } }  
: { : { :  }, : { :  } }                         



: { :  }                     
: { :  }                    
:   { :  }                   



: { : , : { :  } }


: [ { :  }, { : { :  } } ]
:  [ { :  }, { :  } ]
: { :  }
: [ { :  }, { :  } ]   
: [ { :  }, { :  } ]         


: {
  : {
    : ,
    : { :  }
  }
}


: { : { : , : { :  } } }


: { : { : { : , :  } } }


: { : { : { : , : , : { :  } } } }


: { : { : , : , : { : , :  } } }

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"] }

---

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