Query cookbook

Reference examples for querying Relata from every supported surface. Each example is labelled verified (runs against the current binary) or target syntax (planned, not yet runnable).

Part 1: SQL query forms

1. Basic purpose-scoped query

Status: verified

PURPOSE 'analytics'
SELECT * FROM Person
LIMIT 10

CLI:

relata query "PURPOSE 'analytics' SELECT * FROM Person LIMIT 10"
# or from file:
relata query --file queries/persons.sql

2. Query without PURPOSE (optional)

Status: verified

relata query "SELECT * FROM Person LIMIT 5"

PURPOSE is optional; when provided it is recorded in the audit log. When omitted, the query still runs — purpose is not enforced on the read path.

3. Time-scoped query (valid-time)

Status: verified

SELECT * FROM Document
AS OF '2026-01-01T00:00:00Z'
LIMIT 10

Returns rows whose valid_from ≤ ts < valid_to at the given timestamp. AS OF SYSTEM TIME '<ts>' uses system time instead of valid time.

4. Query with provenance

Status: verified

WITH PROVENANCE is a trailing modifier (after LIMIT):

SELECT * FROM Event
LIMIT 10
WITH PROVENANCE

Adds a parallel provenance array to the response — one entry per row with source, method, confidence, recorded_at, and derived_from.

5. Explain policy

Status: verified (via HTTP POST /query)

EXPLAIN POLICY is a prefix (before the optional PURPOSE):

EXPLAIN POLICY
PURPOSE 'analytics'
SELECT * FROM Person LIMIT 10

Returns the ACL decision tree and cell-mask plan for the requesting principal.

6. Filtered query

SELECT * FROM Person
WHERE name = 'Alice'
LIMIT 5

7. Operator TVFs (verified)

-- Graph: paths between two entity IDs, up to N hops.
SELECT * FROM paths_between('alice', 'bob', 3);
 
-- Identity lookup — same surface as LOOKUP_IDENTITY.
SELECT * FROM lookup_identity('+919876543210');
 
-- Identity resolution modes: canonical / cluster / fuse.
SELECT * FROM resolve_identity('alice@example.com');
SELECT * FROM resolve_identity('alice@example.com', 'cluster');
 
-- Finint — beneficial ownership chain.
SELECT * FROM beneficial_ownership_chain('acme_inc', 5);
 
-- Finint — sanctions screening (default Jaccard threshold 0.75).
SELECT * FROM sanctions_screen('alice');
 
-- Crypto trace — BFS over TransactionGraph.
SELECT * FROM crypto_trace('0xabc', 6, 1000.0);

TVFs are governed keyword operators: the parser accepts the keyword form (e.g. CRYPTO_TRACE(...), SANCTIONS_SCREEN(...)) and the equivalent DataFusion TVF form (SELECT * FROM crypto_trace(...)), translating the latter back to the keyword form so purpose + ACL + org-isolation run identically. The translation requires SELECT * over a single TVF call — projections, JOINs, and subqueries over a TVF are rejected; run two queries and join in the client instead.

8. Custom ranking rules — RANK BY clause

Blend time-decay, popularity, or exact-match signals into the result ordering:

-- Rank articles by recency (half-life 24 h) and popularity field.
SELECT * FROM Article
WHERE MATCH(content, 'quantum computing')
RANK BY RECENCY(published_at, 86400), CUSTOM(popularity, 0.3)
LIMIT 20
 
-- Boost exact category match.
SELECT * FROM Product
WHERE MATCH(description, 'laptop')
RANK BY EXACT(category, 2.0), RECENCY(updated_at, 3600)
LIMIT 10

Rules:

  • RECENCY(field, half_life_secs) — exp(-elapsed / half_life) using an integer timestamp field (ns UTC).
  • CUSTOM(field, weight) — multiplies a numeric field by the weight.
  • EXACT(field, boost) — adds boost when the field value case-insensitively matches the search term.

Scores are additive; results are sorted descending. An explicit ORDER BY following RANK BY overrides the ranking order.

Per-type defaults via env:

# Apply recency + popularity ranking for all Article queries that omit RANK BY.
export RELATA_RANKING_Article="recency:published_at:86400,custom:popularity:0.3"

Format: rule:field:value[,rule:field:value,...]

Search for words by their ending or middle substring using wildcard patterns:

-- Suffix search: terms ending with 'son' (Johnson, Jackson, Thompson)
SELECT * FROM Person WHERE MATCH(name, '*son') LIMIT 20
 
-- Infix search: terms containing 'iversi' (university, diversity)
SELECT * FROM Person WHERE MATCH(name, '*iversi*') LIMIT 20
 
-- Explicit mode keyword (same as auto-detect from '*')
SELECT * FROM Log WHERE MATCH(path, '*tion', SUFFIX) LIMIT 10
SELECT * FROM Log WHERE MATCH(message, '*ering*', INFIX) LIMIT 10

How it works:

  • '*suffix' (leading *) → suffix search via reverse-trigram index
  • '*infix*' (both *) → infix search via forward trigram index with substring verification
  • Short patterns (<3 chars) fall back to a dictionary scan

Wildcard detection is automatic — the SUFFIX/INFIX mode keyword is optional.

10. Degree queries — DEGREE() function

DEGREE(column, direction) returns the O(1) in/out/both edge count for each node from the incremental degree index. No full graph rebuild is needed.

-- Out-degree: how many calls did this phone make?
SELECT number, DEGREE(number, 'out') AS out_degree
FROM Phone
WHERE DEGREE(number, 'out') > 5
ORDER BY out_degree DESC
LIMIT 10;
 
-- Combined in + out degree:
SELECT name, DEGREE(id, 'both') AS degree FROM Person ORDER BY degree DESC LIMIT 5;

direction values: 'out', 'in', 'both' (default 'both'). Returns 0 when no link store is attached or the node has no edges.

11. Lookup tables — CSV enrichment at query time

Register a lookup table from a CSV file, then enrich query results:

-- Register once (survives until restart):
REGISTER LOOKUP cmdb_assets FROM '/data/cmdb.csv'
  KEY (ip) FIELDS (owner, criticality, environment)
  REFRESH EVERY 5 MINUTES;
 
-- Enrich query results with owner/criticality from the CMDB:
SELECT
  src_ip,
  LOOKUP cmdb_assets(src_ip) -> owner       AS src_owner,
  LOOKUP cmdb_assets(src_ip) -> criticality AS src_crit,
  bytes
FROM NetworkFlow
WHERE ts > now() - 1h
LIMIT 100;

Or via REST:

curl -X POST http://localhost:9090/lookup/register \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"name":"cmdb","path":"/data/cmdb.csv","key":"ip","fields":["owner","criticality"]}'

Part 2: Agent memory surface

Relata is the governed memory layer for AI agents. Agents normally address it through the memory verbs (remember, recall, recognize, justify, consolidate, forget), not raw SQL. The verbs are exposed two ways: MCP tools (for tool-calling agents) and /memory/* HTTP endpoints (for direct clients).

The MCP verbs are governed: each requires a purpose token (recorded for audit), even though SQL PURPOSE is optional. Results carry provenance and respect ACL, org isolation, and bi-temporal history.

Memory-verb lifecycle (MCP tools)

Remember a belief, then recall, justify, consolidate, and forget it:

// 1. remember — store a governed memory item (bi-temporal + provenance)
{ "method": "tools/call", "params": { "name": "remember",
  "arguments": { "content": "Customer Alice plans to renew in Q3.",
                 "session_id": "agent-session-42", "confidence": 0.9,
                 "purpose": "account_management" } } }
 
// 2. recall — hybrid BM25+vector retrieval of relevant memories (optionally AS OF)
{ "method": "tools/call", "params": { "name": "recall",
  "arguments": { "query": "What do we know about Alice's renewal?",
                 "session_id": "agent-session-42", "top_k": 5,
                 "as_of": "2026-06-01T00:00:00Z", "purpose": "account_management" } } }
 
// 3. recognize — is this identity already known?
{ "method": "tools/call", "params": { "name": "recognize",
  "arguments": { "id": "alice@example.com", "purpose": "account_management" } } }
 
// 4. justify — provenance chain + audit trail for a memory item
{ "method": "tools/call", "params": { "name": "justify",
  "arguments": { "id": "<MemoryItem-uuid>", "purpose": "compliance_review" } } }
 
// 5. consolidate — supersede an old belief (keeps full history)
{ "method": "tools/call", "params": { "name": "consolidate",
  "arguments": { "id": "<MemoryItem-uuid>", "content": "Alice renewed in Q2.",
                 "confidence": 0.95, "purpose": "account_management" } } }
 
// 6. forget — schedule retention / legal-hold
{ "method": "tools/call", "params": { "name": "forget",
  "arguments": { "id": "<MemoryItem-uuid>", "retain_days": 90,
                 "purpose": "data_retention" } } }

Same verbs over HTTP (/memory/*)

curl -X POST http://localhost:9090/memory/remember \
  -H "Content-Type: application/json" -H "Authorization: Bearer $RELATA_BEARER_TOKEN" \
  -d '{"content":"Alice plans to renew in Q3.","purpose":"account_management"}'
 
curl -X POST http://localhost:9090/memory/recall \
  -H "Content-Type: application/json" -H "Authorization: Bearer $RELATA_BEARER_TOKEN" \
  -d '{"query":"Alice renewal","top_k":5,"purpose":"account_management"}'

Data-plane query surfaces

The same underlying engine is reachable directly via SQL across these surfaces. All examples use SELECT * FROM Person LIMIT 5.

CLI (SQL string)

relata query "SELECT * FROM Person LIMIT 5"

CLI (from file)

# Create the query file
cat > queries/persons.sql <<'EOF'
SELECT * FROM Person LIMIT 5
EOF
 
relata query --file queries/persons.sql
relata query -f queries/persons.sql   # shorthand

HTTP REST (JSON)

curl -X POST http://localhost:9090/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $RELATA_BEARER_TOKEN" \
  -d '{"sql": "SELECT * FROM Person LIMIT 5"}'

Response:

{
  "rows": [
    {"name": "Alice", "age": 30},
    {"name": "Bob",   "age": 25}
  ],
  "row_count": 2,
  "cost_units": 2
}

Postgres wire protocol (psql / any Postgres client)

psql "host=localhost port=5432 user=relata dbname=relata" \
  -c "SELECT * FROM Person LIMIT 5"

Any Postgres-compatible driver works:

# Python psycopg2
import psycopg2
conn = psycopg2.connect("host=localhost port=5432 dbname=relata user=relata")
cur = conn.cursor()
cur.execute("SELECT * FROM Person LIMIT 5")
print(cur.fetchall())

TypeScript / Node.js (REST)

import fetch from "node-fetch";
 
const res = await fetch("http://localhost:9090/query", {
  method: "POST",
  headers: {
    "Content-Type": "application/json",
    "Authorization": `Bearer ${process.env.RELATA_BEARER_TOKEN}`,
  },
  body: JSON.stringify({ sql: "SELECT * FROM Person LIMIT 5" }),
});
const data = await res.json();
console.log(data.rows);

Python (REST)

import requests, os
 
r = requests.post(
    "http://localhost:9090/query",
    json={"sql": "SELECT * FROM Person LIMIT 5"},
    headers={"Authorization": f"Bearer {os.environ.get('RELATA_BEARER_TOKEN', '')}"},
)
print(r.json()["rows"])

Go (REST)

package main
 
import (
    "bytes"
    "encoding/json"
    "fmt"
    "net/http"
)
 
func main() {
    body, _ := json.Marshal(map[string]string{
        "sql": "SELECT * FROM Person LIMIT 5",
    })
    resp, err := http.Post(
        "http://localhost:9090/query",
        "application/json",
        bytes.NewReader(body),
    )
    if err != nil {
        panic(err)
    }
    defer resp.Body.Close()
    var result map[string]interface{}
    json.NewDecoder(resp.Body).Decode(&result)
    fmt.Println(result["rows"])
}

Agent-oriented: recall from any language

Every SDK can drive the memory verbs over /memory/*. Example — recall from Python:

import requests, os
r = requests.post(
    "http://localhost:9090/memory/recall",
    json={"query": "Alice renewal", "top_k": 5, "purpose": "account_management"},
    headers={"Authorization": f"Bearer {os.environ.get('RELATA_BEARER_TOKEN', '')}"},
)
print(r.json())   # scored memory items with provenance

The same POST /memory/recall body works from TypeScript, Go, and Rust — swap the HTTP client, keep the JSON.

Part 2b: Multi-tenant queries

Scoping to an organization

Pass X-Organization-Id on HTTP requests. Rows written with that header are only returned to principals presenting the same header (or principals with a SharingAgreement).

Status: verified

# Ingest into tenant "acme"
curl -s -X POST "http://127.0.0.1:9090/ingest?object_type=Person&purpose=onboarding" \
  -H "Content-Type: text/csv" \
  -H "X-Organization-Id: acme" \
  --data-binary $'name,email\nAlice,alice@acme.com'
 
# Query scoped to "acme"
curl -s -X POST http://127.0.0.1:9090/query \
  -H "Content-Type: application/json" \
  -H "X-Organization-Id: acme" \
  -d '{"purpose":"analytics","sql":"SELECT * FROM Person LIMIT 10"}'

Without the header the query returns only unscoped rows (no tenant_id set).

Sub-tenant namespaces

Use /-separated paths to express hierarchy within an organization. A grant on a prefix covers all children.

Status: verified

acme              ← top-level tenant
acme/eu           ← regional sub-tenant
acme/eu/hr        ← team sub-tenant
acme/us           ← separate regional sub-tenant (does NOT see acme/eu)

# Write into a sub-tenant
curl -s -X POST "http://127.0.0.1:9090/ingest?object_type=Employee&purpose=hr" \
  -H "Content-Type: text/csv" \
  -H "X-Organization-Id: acme/eu/hr" \
  --data-binary $'name\nBob'
 
# A query under acme/eu sees acme/eu AND acme/eu/hr rows
curl -s -X POST http://127.0.0.1:9090/query \
  -H "Content-Type: application/json" \
  -H "X-Organization-Id: acme/eu" \
  -d '{"sql":"SELECT * FROM Employee"}'

Per-tenant read quota defaults

The server enforces these limits per agency per query (configurable via relata.toml):

LimitDefault
Max in-flight queries per tenant10
Max query duration30 s
Max scanned rows per query10 000 000

Exceeding any limit returns HTTP 429 with a Retry-After header.

Part 2d: Adaptive caching and segment pruning

The caching wave adds per-segment temperature tracking, pin-plan coordination, per-column bloom filters, and CMS-driven selectivity estimation. These work transparently — no query changes needed — but query patterns that align with the optimizations see the largest gains.

Bloom-filter-friendly predicates

Status: verified (bloom pruning is active when RELATA_BLOOM_COLUMNS includes the filtered column; segments are skipped at the manifest layer before any row reads).

Equality predicates on bloom-indexed columns avoid reading segments that cannot match:

-- tenant_id and object_type are bloom-indexed by default (RELATA_BLOOM_COLUMNS)
SELECT * FROM Transaction
WHERE tenant_id = 'acme'
  AND status = 'settled'
LIMIT 1000
-- point-in-time lookup: bloom prunes temporal segments before the AS-OF scan
SELECT * FROM CaseRecord AS OF '2026-01-15T00:00:00Z'
WHERE tenant_id = 'interpol'
LIMIT 50

CMS-informed selectivity

Status: verified (CMS sketches are maintained per RELATA_SKETCH_COLUMNS; the cost-based optimizer uses them when deciding whether to apply a secondary index).

The optimizer automatically routes high-selectivity predicates through the column index. No query hint is needed:

-- When 'acme' appears in 95% of rows, the CBO skips the tenant_id index (not selective)
-- When 'rare-org' appears in 0.1% of rows, the index is used automatically
SELECT * FROM NetworkFlow
WHERE tenant_id = 'rare-org'
  AND dst_port = 443
LIMIT 500

WITH CACHE hint verified

The WITH CACHE clause controls how the result cache treats a query:

-- pin this result set for the next 10 minutes (TTL override)
SELECT * FROM IncidentReport
WHERE status = 'open'
WITH CACHE TTL 600
LIMIT 5000
-- honour staleness: treat entries older than 60s as a miss
SELECT * FROM IncidentReport
WHERE status = 'open'
WITH CACHE STALENESS 60
LIMIT 5000
-- skip the cache and force a fresh read from storage
SELECT * FROM AuditLog
WHERE event_time > '2026-07-10T00:00:00Z'
WITH CACHE BYPASS
LIMIT 100

Part 2e: Search experience — verified

Full-text search with typo tolerance verified

-- Balanced mode (default): 1 edit distance, prefix on short tokens
SELECT * FROM Person WHERE MATCH(name, 'alice')
 
-- Fuzzy mode: 2 edit distances, catches more typos
SELECT * FROM Person WHERE MATCH(name, 'alise', FUZZY)
 
-- Phrase mode: exact phrase match
SELECT * FROM Person WHERE MATCH(notes, 'senior analyst', PHRASE)

Search highlighting verified

Highlighting is governed by the /search REST endpoint's highlight flag (and the attributesToHighlight array), not a SQL clause — snippets with <mark>-wrapped matched terms plus offsets are returned alongside each hit:

curl -X POST http://localhost:9090/search \
  -H "Authorization: Bearer $TOKEN" \
  -d '{"query": "alice", "type": "Person", "highlight": true}'

Faceted search verified

-- Returns facet value counts alongside hits for drill-down
SELECT * FROM Product WHERE MATCH(name, 'laptop') FACETS category, brand LIMIT 20

Custom ranking rules verified

-- Blend BM25 with recency (half-life 1 day) and popularity (weight 0.3)
SELECT * FROM Article WHERE MATCH(content, 'quantum')
    RANK BY recency(published_at, 86400), custom(popularity, 0.3)
    LIMIT 10

Suffix / infix search verified

-- Suffix: match names ending in 'son'
SELECT * FROM Person WHERE MATCH(name, '*son')
 
-- Infix: match names containing 'iversi'
SELECT * FROM Person WHERE MATCH(name, '*iversi*')

Dedicated /search REST endpoint verified

# Search-native JSON API — no SQL required
curl -X POST http://localhost:9090/search \
  -H "Authorization: Bearer $TOKEN" \
  -d '{"query": "alice smith", "type": "Person", "limit": 10, "facets": ["tenant_id"], "highlight": true}'

Graph operators verified

-- Weighted shortest path
SELECT * FROM GRAPH_DIJKSTRA('Transaction', FROM => 'Person/A', TO => 'Person/B')
 
-- Link prediction scores
SELECT * FROM GRAPH_LINK_PREDICT('Person', FROM => 'Person/A', TO => 'Person/B', METHOD => 'adamic_adar')
 
-- Degree query
SELECT name, DEGREE(id, 'out') AS out_degree FROM Person WHERE DEGREE(id, 'out') > 5
 
-- Strongly connected components
SELECT * FROM GRAPH_SCC('Transaction')

CDR analysis

Status: verified (requires ingested CdrRecord rows via relata cdr ingest <file.csv>)

Ingest CDRs from the terminal

# Ingest a CSV file (columns: caller, callee, duration_secs, timestamp_utc)
relata cdr ingest calls.csv [--purpose law_enforcement]

Common-contact hand-off analysis

PURPOSE 'law_enforcement'
SELECT callee, COUNT(*) AS call_count, SUM(duration_secs) AS total_secs
FROM CdrRecord
WHERE caller = '+919876543210' OR callee = '+919876543210'
GROUP BY callee
ORDER BY call_count DESC
LIMIT 20

CLI shorthand:

relata cdr analyze +919876543210

Timeline (most recent calls)

PURPOSE 'law_enforcement'
SELECT caller, callee, duration_secs, valid_from
FROM CdrRecord
WHERE caller = '+919876543210' OR callee = '+919876543210'
ORDER BY valid_from DESC
LIMIT 50

CLI shorthand:

relata cdr timeline +919876543210

Identity resolution on CDR contacts

-- Resolve which known person a CDR callee maps to
PURPOSE 'law_enforcement'
SELECT * FROM RESOLVE_IDENTITY('+447700900123') LIMIT 5

Part 3: Cookbook rules

  • Do not add examples as verified until tested against the current binary.
  • If syntax is planned but not implemented, label it target syntax.
  • Purpose is optional; include it when your query has audit/governance requirements.