SQL Reference#

RelataDB extends ANSI SQL with bi-temporal reads, provenance, identity resolution, graph traversal, hybrid search, and agent memory verbs. The same SQL runs across every protocol door: psql, gRPC, HTTP /query, MCP query, Arrow Flight, and the SPARQL bridge.

What runs today: this page documents verified behaviour. Items marked not implemented or partial are not available. See Limits for the full status table.

Statement shape#

[EXPLAIN POLICY]
[PURPOSE '<id>']
SELECT <projection>
FROM   <Type | TVF>
[AS OF '<timestamp>']
[AS OF SYSTEM TIME '<timestamp>']
[WHERE <predicate>]
[ORDER BY <col> [ASC|DESC]]
[LIMIT n [AFTER '<cursor>']]
[WITH PROVENANCE]

All clauses are order-sensitive. WITH PROVENANCE must be last.

PURPOSE#

PURPOSE '<id>' is an optional prefix. When omitted the query runs; when declared it is recorded in the audit log. Per-tenant ACL policy can require it.

-- runs, no audit record
SELECT * FROM Person LIMIT 10
 
-- runs, audited under purpose "analytics"
PURPOSE 'analytics' SELECT * FROM Person LIMIT 10
 
-- returns the ACL decision tree without executing
EXPLAIN POLICY PURPOSE 'analytics' SELECT * FROM Person LIMIT 10

Common values: analytics, audit, compliance_review, security_incident, research, operations.

AS OF — bi-temporal snapshots#

AS OF returns rows whose valid_from &lt;= ts < valid_to. AS OF SYSTEM TIME uses system-recorded time instead.

-- valid-time snapshot
SELECT * FROM Document AS OF '2026-01-01T00:00:00Z'
 
-- system-time snapshot
SELECT * FROM Document AS OF SYSTEM TIME '2026-01-01T00:00:00Z'

Timestamp formats accepted: UTC ISO-8601 (YYYY-MM-DD, YYYY-MM-DDTHH:MM:SS, YYYY-MM-DDTHH:MM:SS.fffZ). Non-UTC offsets are rejected. Underlying storage is i64 nanoseconds UTC.

WITH PROVENANCE#

Trailing modifier — must come after LIMIT:

SELECT * FROM Event
LIMIT 10
WITH PROVENANCE

Adds a parallel provenance array to the response (one entry per row). Each entry exposes source, method, confidence, recorded_at, and derived_from (hex ProvenanceRef of the source row, or null for genesis).

Keyset pagination: LIMIT n AFTER#

SELECT * FROM Person
LIMIT 100 AFTER '1798765432100000000'

The cursor is the previous page's last system_from value (decimal nanoseconds). Cannot combine with ORDER BY.

EXPLAIN#

EXPLAIN POLICY PURPOSE 'analytics' SELECT * FROM Person LIMIT 10
EXPLAIN_PATH('alice@example.com', 'bob@example.com')
EXPLAIN_REPLAY('<exhibit-id>', SEQ => 5)

EXPLAIN POLICY is parsed as a prefix but the flag is not consulted on the execute path. Use the explain_policy MCP tool for a live ACL decision.

WHERE expressions#

MATCH modes: default resolves from the BM25 posting list; PHRASE uses the positional index; FUZZY and STEMMED fall back to substring scan; BOOLEAN evaluates uppercase AND/OR/NOT operators as posting-list set operations (a bare space means OR). Phonetic predicates (SOUNDEX/METAPHONE/COLOGNE) encode the right-hand literal to the phonetic key at parse time and support only = / !=.

Aggregation and JOIN#

Identity operators#

-- Universal lookup by value (phone, email, IMEI, IBAN, IP, BTC address, …)
SELECT * FROM LOOKUP_IDENTITY('+919876543210')
 
-- Resolution modes: cluster (default), canonical, fuse
SELECT * FROM RESOLVE_IDENTITY('alice@example.com')
SELECT * FROM RESOLVE_IDENTITY('alice@example.com', MODE => 'canonical')
SELECT * FROM RESOLVE_IDENTITY('alice@example.com', MODE => 'fuse')

RESOLVE_IDENTITY(..., MODE => 'fuse') dispatches the EnrichmentRule chain. Returns an error when no rules are registered.

The SQL keyword is the singular RESOLVE_IDENTITY. The DataFusion TVF form SELECT * FROM resolve_identity(...) is also accepted and translated to the governed keyword form.

Entity merge / dedup#

-- Ontological merge — link two identities as one person
FUSE_IDENTITIES('alice@example.com', 'bob@example.com')
 
-- Ontological unmerge — reverse a fuse
SPLIT_IDENTITIES('alice@example.com', 'bob@example.com')
 
-- SmartIngest auto-detect identities in free text
DETECT_IDENTITIES('call bob at +971501234567')
 
-- GDPR Art. 17 erasure — irreversible
ERASE SUBJECT 'alice@example.com' REASON 'gdpr-art17' CERTIFY

Graph operators#

-- Shortest paths (Pregel BFS)
SELECT * FROM PATHS_BETWEEN('alice', 'bob', 3)
SELECT * FROM PATHS_BETWEEN('person-123', 'org-456', MAX_HOPS => 4)
 
-- Network expansion
SELECT * FROM NETWORK_EXPAND('entity-X', MAX_HOPS => 3)
 
-- Per-node degree
SELECT id, DEGREE(id) AS degree FROM Person ORDER BY degree DESC LIMIT 10

NETWORK_EXPAND takes the seed identifier as the first positional arg and the hop cap via the MAX_HOPS => n named arg (default 2), with an optional LINK_TYPES => 'a,b' filter.

Additional graph TVFs (all SQL-reachable):

Search operators#

-- Full-text search (BM25, custom engine — not Tantivy)
SELECT * FROM Person WHERE MATCH(name, 'Ahmed Khalil')
 
-- Hybrid BM25 + vector (reciprocal-rank fusion)
HYBRID_SEARCH FROM Document QUERY 'terror finance' LIMIT 25
 
-- Embedding-vector similarity against a seed row (multi-vector max-pool)
SIMILAR TO Document WHERE id = 'doc-42' LIMIT 10
 
-- pgvector-compatible KNN operators (pgwire door only)
SELECT id FROM docs ORDER BY embedding <=> '[0.9,0.1,0]' LIMIT 5

Multimodal similarity#

-- Similar-to a reference (multi-vector max-pool cosine over _emb_* slots)
SIMILAR TO Person WHERE id = 'person-42'
SIMILAR TO MultimodalAsset WHERE id = 'asset-7'
 
-- Near-duplicate / rough-visual-similarity image search (perceptual hash + Hamming index)
SIMILAR_IMAGE('media-42')
SIMILAR_IMAGE('media-42', THRESHOLD => 0.6, INDEX => 'ncmec')

SIMILAR TO falls back to Jaccard token similarity when the reference has no embedding slots. Face and voice search are exposed as the FACE_SEARCH / VOICE_MATCH operators and the MCP face_match / voice_match tools. SIMILAR_IMAGE(media_ref, THRESHOLD => n, INDEX => corpus) matches by perceptual hash rather than embedding — THRESHOLD defaults to 0.9; INDEX optionally scopes the Hamming-index lookup to a named corpus (e.g. 'ncmec' for CSAM near-dup triage).

Agent memory verbs#

REMEMBER 'Alice plans to renew in Q3.' SESSION 'sess-1' CONFIDENCE 0.9
RECALL   'Alice renewal' TOP_K 5 AS OF '2026-01-01'
REFLECT  ON SESSION 'sess-1'
CONSOLIDATE MEMORY 'mem-uuid-old' WITH 'Alice confirmed renewal for Q3.'
FORGET   MEMORY 'mem-uuid' RETAIN_DAYS 90
ASSOCIATE MEMORY 'mem-a' WITH 'mem-b' AS 'contradicts' CONFIDENCE 0.8
RESOLVE  MEMORY 'mem-uuid' POLICY 'highest_confidence'

The same verbs are available via MCP tools and HTTP /memory/* endpoints.

Domain TVFs#

All reachable from SQL under the governed keyword translation:

Social-media analytics scorers#

All 13 ScorerOp operators are SQL-reachable:

SENTIMENT_SCORE, STANCE_SCORE, BIAS_SCORE, AUTHENTICITY_SCORE, POSTING_PATTERN, STYLE_FINGERPRINT, BOT_AMPLIFICATION_SCORE, NARRATIVE_TRACE, PERSONA_CLUSTER_DETECT, INAUTHENTIC_BEHAVIOUR_SCAN, COORDINATED_AMPLIFICATION, CROSS_PLATFORM_ACCOUNT, INFLUENCE_TIER.

Lookup tables#

-- 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 at run time
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() - INTERVAL '1 hour'
LIMIT 100;

Materialized views#

CREATE MATERIALIZED VIEW active_persons AS
  SELECT * FROM Person WHERE status = 'active'
REFRESH INCREMENTAL EVERY 60;

Background refresh runs every 60 seconds. RELATA_MV_MAX_ROWS (default 1,000,000) caps cached rows before eviction to base-table fallback.

WATCH#

WATCH PURPOSE 'security_incident' SELECT * FROM Event WHERE severity = 'high'

Registers a subscription — pushes matching rows to the caller as they arrive. Available over the SSE /watch/stream endpoint.

ERASE SUBJECT (GDPR Art. 17)#

ERASE SUBJECT 'person-42' REASON 'gdpr-art17' CERTIFY;

REASON '<reason>' is required; the trailing CERTIFY keyword acknowledges the operation is destructive and irreversible. Shreds rows, orphaned blobs, and the per-subject DEK (KMS-wired, fail-closed). Returns a signed Art. 17 receipt.

DDL#

RelataDB is ontology-governed. Types are declared via the ontology, not CREATE TABLE. DDL is supported only via the pgwire door for pgvector compatibility.

Clause grammar & capability matrix#

Code-verified per-clause status. "Working" produces correct results under cargo test; "Partial" parses but output is incomplete; "Not implemented" returns a 400 or empty result. See Limits for deeper caveats.

SELECT projection#

FROM, JOIN, subquery#

WHERE predicates#

ORDER BY, LIMIT, pagination#

GROUP BY, windows, HAVING#

EXPLAIN variants#

Cluster fan-out aggregates#

On cluster profile with peers, fan-out merge supports SUM(col)/SUM(*) (merged across shards), AVG(col)/AVG(*) (weighted merge), and COUNT(*) (summed across shards).

DDL (pgwire path only)#

These statements only work over the pgwire listener (port 5433 by default), not /query:

Ingest body formats#

POST /ingest?object_type=<Type>&purpose=<P> auto-detects the body format (no Content-Type change required — the server inspects the first byte):

The purpose parameter is validated against ^[a-zA-Z_][a-zA-Z0-9_]{0,63}$ — use underscores, not hyphens (e.g. threat_intel, not threat-intel); max 64 chars. A rejected purpose returns 400 with a hint showing the underscore-normalised form.

Custom types#

Detection-rule SQL guard#

Detection-rule conditions are validated at create + eval time: rejects ;, --, /* */ markers; rejects UNION/INTERSECT/EXCEPT adjacent to punctuation; 2048-byte length cap on condition text.

Not yet implemented#