SQL Reference

RelataDB extends ANSI SQL with operators for bi-temporal reads, provenance, identity resolution, graph traversal, biometric search, and hybrid (BM25 + vector) retrieval. The SQL surface is the same across every protocol door — psql, gRPC, HTTP /query, MCP query, Arrow Flight, and SPARQL bridge.

Source of truth for what runs today: docs/src/end-users/limits.md. This page mirrors the verified behaviour. Anything labelled target in the limits page is not listed here.

Statement shape


[EXPLAIN POLICY]            -- prefix: return the ACL decision tree
[PURPOSE '<id>']            -- optional prefix (ADR-125); recorded for audit when present
SELECT <projection>
FROM <Type | TVF>           -- a Relata type or a table-valued function
[AS OF '<timestamp>']       -- bi-temporal point-in-time
[WHERE <predicate>]
[ORDER BY <col [ASC|DESC]>]
[LIMIT n [AFTER '<cursor>']]
[WITH PROVENANCE]           -- trailing modifier

The mandatory-in-spirit purpose

The PURPOSE '<id>' prefix is optional at the SQL layer (ADR-125) — when omitted the query still runs. Purpose enforcement lives in ACL + organisation isolation. When declared, it is recorded in the audit log.


SELECT * FROM Person LIMIT 10                                    -- runs
PURPOSE 'analytics' SELECT * FROM Person LIMIT 10                -- audited
EXPLAIN POLICY PURPOSE 'analytics' SELECT * FROM Person LIMIT 10 -- plan only

Per-tenant policy can re-tighten this — see your PurposeRegistry. Common values: analytics, audit, compliance_review, security_incident, research, operations.

Bi-temporal: `AS OF`


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

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

Timestamps are i64 nanoseconds UTC, but the parser accepts UTC ISO-8601 (YYYY-MM-DD[THH:MM:SS[.fff][Z]]), quoted or bare. Explicit non-UTC offsets are rejected.

Provenance: `WITH PROVENANCE`

A trailing modifier (must come after LIMIT):


SELECT * FROM Event
LIMIT 10
WITH PROVENANCE

Adds prov_source, prov_agent, and prov_hash columns to every row.

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 — return the ACL decision tree + cell-mask plan for the requesting principal.
EXPLAIN PATH — return {strategy, graph_node_count, pll_warm} JSON for a graph path probe.
EXPLAIN REPLAY — re-derive a logged exhibit link’s seal byte-identically.

EXPLAIN POLICY parses as a prefix but the flag is not consulted on the execute path; use the explain_policy MCP tool instead.

Keyset pagination: `LIMIT n AFTER`


SELECT * FROM Person
LIMIT 100 AFTER '1798765432100000000'

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

WHERE expressions

Form	Status
`col op literal` (=`!=`, `<`, `<=`, `>`, `>=`, `LIKE`)	Working
Arithmetic expressions	Working
`now()`	Working — resolved at parse time
`now() - INTERVAL 'N days\|hours\|minutes\|weeks'`	Working
`MATCH(col, 'q'[, PHRASE\|FUZZY\|STEMMED])`	Working — default mode resolves from the BM25 posting list (token FTS, #624); `PHRASE` resolves from the positional index; FUZZY/STEMMED fall back to substring scan

Aggregation, JOIN, streaming windows

Feature	Status
`JOIN` (INNER, hash join, O(n+m))	Working
`GROUP BY` + `COUNT(*)`	Working
`ORDER BY` (single column, ASC/DESC)	Working
Multi-column `ORDER BY`, `UNION`, CTE / `WITH … AS`	Not implemented
`TUMBLE` / `HOP` / `SESSION` streaming windows	Working — HOP overlapping bucket fan-out; SESSION per-key activity-gap grouping

Identity operators


-- Universal IdentityIndex lookup by value (phone, email, IMEI, Aadhaar, IP, BTC address…)
SELECT * FROM LOOKUP_IDENTITY('+919876543210')
 
-- Resolution modes (canonical / cluster / fuse)
SELECT * FROM RESOLVE_IDENTITIES('alice@example.com')
SELECT * FROM RESOLVE_IDENTITIES('alice@example.com', 'cluster')

RESOLVE_IDENTITIES(…, MODE => 'fuse') dispatches the EnrichmentRule chain — register rules via POST /ontology/enrichment-rules. Returns a descriptive error when no rules are registered (no silent fallback to cluster mode).

Graph operators


-- Shortest paths between two entities (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(seed_id => 'entity-X', hops => 3)
 
-- Iterative graph BFS
SELECT * FROM PREGEL_BFS(seed_id => 'entity-X', max_rounds => 4)

Biometric + multimodal similarity


-- Face search (threshold 0.92)
SELECT * FROM Person
WHERE MATCH_FACE(probe_embedding, stored_embedding, 0.92)
 
-- 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'

SIMILAR TO falls back to Jaccard token similarity when the reference has no embedding slots. The legacy single _embedding field still takes the HNSW fast-path.

Hybrid retrieval: `HYBRID_SCORE`


SELECT *, HYBRID_SCORE('terror finance') AS score
FROM Document
ORDER BY score DESC
LIMIT 25

BM25 + vector similarity fused via reciprocal-rank fusion (RRF).

Vector search (pgvector-compatible)

The pgwire door supports the standard pgvector KNN operators:

Operator	Metric	Note
`<=>`	cosine distance	preferred — ANN index is cosine-only
`<->`	L2 distance	metric-correct via over-fetch + re-rank
`<#>`	negative inner product	metric-correct via over-fetch + re-rank

Domain-specific TVFs

All reachable from SQL via governed keyword translation:

TVF	Domain	Description
`BENEFICIAL_OWNERSHIP_CHAIN(entity, max_depth)`	FinINT	Beneficial ownership chain
`SANCTIONS_SCREEN(name[, threshold => 0.7])`	FinINT	Sanctions screening (default Jaccard threshold)
`CRYPTO_TRACE(wallet, max_hops, min_amount)`	FinINT	BFS over `TransactionGraph`
`WIRE_RECONSTRUCTION(...)`	FinINT	Wire transfer reconstruction
`HAWALA_TRACE(...)`	FinINT	Hawala network trace
`GRAPH_COMMUNITY(...)`	Graph	Community detection
`GEOFENCE(...)`	Geo	Geofence lookup
`ANPR_TRACE(...)`	Intel	ANPR plate trace
`DISPATCH_PRIORITY(...)`	Ops	Dispatch priority
`CRIME_PATTERN_CLUSTER(...)`	Intel	Crime pattern clustering

The 13 ScorerOp operators are all reachable from SQL (#34):

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 (CSV enrichment at query time)


-- 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:
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;

Registers the MV and runs an initial full refresh on the server/cluster path; background refresh loop registered at startup. RELATA_MV_MAX_ROWS (default 1,000,000) caps the cached rows before eviction to base-table fallback.

DDL — the ontology is the schema

You do not CREATE TABLE over native SQL. Types are declared via the ontology and auto-registered on ingest. The only DML/DDL that runs end-to-end is over the pgwire listener for psql / pgvector client compatibility:

Statement	Status
`CREATE EXTENSION vector` / `DROP EXTENSION vector`	Working (pgwire, no-op OK tag)
`CREATE TABLE` (pgvector)	Working (pgwire) — registers the type; column list is ignored
`INSERT` / `UPDATE` / `DELETE`	Working (pgwire) — the only native SQL DML path
`ALTER TABLE … ADD/DROP COLUMN`	Parses, then 501s — use the ontology API
`CREATE INDEX` / `CREATE TYPE` / `CREATE SCHEMA`	Not implemented


ERASE SUBJECT 'person-42' CERTIFY 'governed-tombstone';

Shreds rows + orphaned blobs, destroys the per-subject DEK (KMS-wired, fail-closed), and returns a signed Art. 17 receipt. With no KMS configured it succeeds in certified_by: "governed-tombstone" mode (#827).

WATCH PURPOSE


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

Registers a watch subscription — pushes matching rows to the caller as they arrive.