Cypher Query Support¶

Provisa translates a subset of openCypher to SQL via the provisa/cypher/ module. Queries are parsed by a custom recursive-descent parser (no external Cypher library), schema-resolved against the model, and emitted as SQL, then routed to the target execution engine.

Implemented Features¶

Clauses¶

Clause	Status	Notes
`MATCH (n:Label)`	✓	Node patterns with labels, variables, inline properties
`OPTIONAL MATCH`	✓	Emits LEFT JOIN
`WHERE`	✓	Full expression support; applied after MATCH
`RETURN`	✓	Star, property access, expressions, aliases
`RETURN DISTINCT`	✓	Emits SELECT DISTINCT
`WITH`	✓	Emits a named CTE (`_w0`, `_w1`, …); supports `WITH … WHERE`
`ORDER BY`	✓	ASC / DESC
`SKIP` / `LIMIT`	✓	Maps to SQL OFFSET / LIMIT
`UNION` / `UNION ALL`	✓	Recursive union across sub-ASTs
`CALL { … }`	✓	Top-level call subquery decomposition via `cypher_calls_to_sql_list`
`CALL { WITH x … }`	✓	Correlated subquery → `CROSS JOIN LATERAL`; see §Correlated CALL
`CALL db.labels()`	✓	Returns node labels from the model; no SQL translation
`CALL db.relationshipTypes()`	✓	Returns relationship types from the model
`CALL db.propertyKeys()`	✓	Returns all property key names across all node types
`UNWIND`	✓	Array-to-rows expansion; first item becomes FROM, subsequent become CROSS JOIN UNNEST

Match Patterns¶

Pattern	Status	Notes
`(n)` (unlabeled node)	✓	UNION ALL over all known types
`(n:Label)`	✓	Maps to the registered table for that GraphQL type
`(n:Label {prop: val})`	✓	Inline property filter becomes WHERE
`(a)-[:TYPE]->(b)`	✓	Directed, single hop
`(a)<-[:TYPE]-(b)`	✓	Backward traversal; join columns reversed
`(a)-[]->(b)`	✓	Any directed relationship a→b; UNION ALL if multiple types match
`(a)-[]-(b)`	✓	Bidirectional; expands to UNION ALL of all forward and backward relationships
`(a)-[:TYPE*..N]->(b)`	✓	Variable-length with upper bound; recursive CTE for self-referential, flat JOIN otherwise
`(a)-[]->(b)-[]->(c)`	✓	Multi-hop chained JOINs
`(n:DomainLabel)`	✓	Domain label → UNION ALL subquery over all types in the domain
`(n:A\\|B)`	✓	Label alternation → ad-hoc domain injected into label map; UNION ALL over matching types
`shortestPath(…)`	✓	Flat JOIN for heterogeneous endpoints; WITH RECURSIVE CTE for same-type/self-referential
`allShortestPaths(…)`	✓	Same as shortestPath without LIMIT 1

Expressions and Predicates¶

Feature	Status	SQL mapping
Property access `n.prop`	✓	`n."prop"`
Parameters `$name`	✓	Positional `$N`
Legacy parameters `{name}`	✓	Normalised to `$name` at parse time
Comparison `=`, `<>`, `<`, `>`, `<=`, `>=`	✓	Direct
`AND`, `OR`, `NOT`	✓	Direct
`IS NULL` / `IS NOT NULL`	✓	Direct
`IN [list]`	✓	SQL IN; Cypher `[...]` bracket syntax rewritten to `(...)`
`STARTS WITH`	✓	`starts_with(col, val)`
`ENDS WITH`	✓	`col LIKE CONCAT('%', val)`
`CONTAINS`	✓	`strpos(col, val) > 0`
`=~` regex	✓	`regexp_like(col, pattern)`
`exists(n.prop)`	✓	`(n.prop) IS NOT NULL`
`EXISTS { MATCH … }`	✓	Correlated `EXISTS (SELECT 1 FROM …)` subquery
`COUNT { MATCH … }`	✓	Correlated `(SELECT count(*) FROM …)` subquery
`COLLECT { MATCH … RETURN x }`	✓	Correlated `ARRAY(SELECT x FROM …)` subquery
`id(n)`	✓	Resolved to the node's configured ID column
`labels(n)`	✓	`ARRAY['Label']`
`keys(n)`	✓	`ARRAY['prop1', 'prop2', …]`
`type(r)`	✓	Resolved at compile time to `'REL_TYPE'` string literal; no runtime column
`length(p)`	✓	`_t.hops` for recursive CTE paths; `1` for flat JOIN paths
`CASE WHEN … THEN … ELSE … END`	✓	Direct (searched and simple forms)
Implicit GROUP BY	✓	Non-aggregated RETURN items become GROUP BY keys when any item has an aggregate

Map Projections¶

Syntax	SQL mapping
`n { .prop1, .prop2 }`	`MAP(ARRAY['prop1','prop2'], ARRAY[n."prop1",n."prop2"])`
`n { .* }`	`MAP(ARRAY[all props...], ARRAY[n."col",...])`, expanded from schema
`n { .*, extra: expr }`	All schema props plus named key; combined MAP
`n { key: expr }`	`MAP(ARRAY['key'], ARRAY[expr])`

Object properties. A column holding a declared object (a remote GraphQL type's embedded object, or any column registered with object fields) is a map property: RETURN n.address gives it as a map, whatever form the engine returns it in, and the node n carries it among its properties. A remote GraphQL field pointing at a registered type, one object or a list, is not a property; it is reached over its relationship.

Aggregation Functions¶

Cypher	SQL
`count(*)`, `count(x)`	direct
`count(DISTINCT x)`	`count(DISTINCT x)`
`collect(x)`	`array_agg(x)`
`avg`, `sum`, `min`, `max`	direct
`stDev(x)`	`stddev_samp(x)`
`stDevP(x)`	`stddev_pop(x)`
`percentileCont(x, p)`	`approx_percentile(x, p)`
`percentileDisc(x, p)`	`approx_percentile(x, p)`

String Functions¶

Cypher	SQL
`toLower(x)`	`lower(x)`
`toUpper(x)`	`upper(x)`
`ltrim(x)`, `rtrim(x)`, `trim(x)`	direct
`replace(x, a, b)`	direct
`reverse(x)`	direct
`split(x, d)`	direct
`left(x, n)`	`left(x, n)`
`right(x, n)`	`right(x, n)`
`substring(x, start, len)`	`substr(x, start+1, len)` (0→1 index)
`size(string)`	`char_length(string)`
`size(list)`	`cardinality(list)`

Type Conversion Functions¶

Cypher	SQL
`toString(x)`	`CAST(x AS VARCHAR)`
`toInteger(x)`	`TRY_CAST(x AS BIGINT)`
`toFloat(x)`	`TRY_CAST(x AS DOUBLE)`
`toBoolean(x)`	`TRY_CAST(x AS BOOLEAN)`
`toStringOrNull`, `toIntegerOrNull`, `toFloatOrNull`, `toBooleanOrNull`	`TRY_CAST` variants

Math Functions¶

Cypher	SQL
`log(x)`	`ln(x)` (natural log)
`log2(x)`	`log2(x)`
`range(start, end)`	`sequence(start, end)`
`abs`, `sqrt`, `ceil`, `floor`, `round`, `sign`	passed through

List Functions¶

Cypher	SQL
`head(list)`	`element_at(list, 1)`
`last(list)`	`element_at(list, -1)`
`tail(list)`	`slice(list, 2, cardinality(list))`
`isEmpty(list)`	`cardinality(list) = 0`

List Comprehensions¶

Syntax	SQL mapping
`[x IN list \\| f(x)]`	`transform(list, x -> f(x))`
`[x IN list WHERE p(x)]`	`filter(list, x -> p(x))`
`[x IN list WHERE p(x) \\| f(x)]`	`transform(filter(list, x -> p(x)), x -> f(x))`
`any(x IN list WHERE p(x))`	`any_match(list, x -> p(x))`
`all(x IN list WHERE p(x))`	`all_match(list, x -> p(x))`
`none(x IN list WHERE p(x))`	`none_match(list, x -> p(x))`
`single(x IN list WHERE p(x))`	`cardinality(filter(list, x -> p(x))) = 1`
`reduce(acc = init, x IN list \\| expr)`	`reduce(list, init, (acc, x) -> expr, acc -> acc)`

Pattern Comprehensions¶

Syntax	SQL mapping
`[(a)-[:R]->(b) \\| b.prop]`	`ARRAY(SELECT b."prop" FROM ... WHERE a.fk = b.pk)`
`[(a)-[]->(b:Label) \\| b.prop]`	type-inferred from the model; same ARRAY subquery form

Correlated CALL Subqueries¶

CALL { WITH x MATCH (x)-[:R]->(n) RETURN n.prop AS alias } translates to CROSS JOIN LATERAL (SELECT n."prop" AS alias FROM ... WHERE x."pk" = n."fk"). Rules:

The outer-scope variable (x) must appear in WITH
Multiple imported vars (WITH a, b) are supported
The first relationship in the inner MATCH whose source is a lateral-bound var determines the inner FROM and join condition
Non-correlated top-level CALL { ... } blocks (without WITH) are handled by cypher_calls_to_sql_list

Writes¶

Cypher supports three write patterns through the /data/cypher endpoint, executed by provisa/cypher/write_translator.py.

Cypher	SQL
`CREATE (n:Label {props})`	`INSERT INTO catalog.schema.table (cols) VALUES (vals)`
`MATCH (n:Label) WHERE … DELETE n`	`DELETE FROM catalog.schema.table WHERE …`
`MATCH (n:Label) WHERE … SET n.prop = val, …`	`UPDATE catalog.schema.table SET col = val, … WHERE …`

Property names map to columns via domain-prefix stripping and alias resolution; Cypher scalar values are coerced to the target column type. The response body carries an affected_rows count.

A node's property is its column's published name: the column's alias when it has one, else the naming convention's name for it, the name GraphQL and SQL read it by, in MATCH, RETURN, CREATE, SET and MERGE alike.

A name that is no property of the label is null wherever it is read (WHERE, RETURN, ORDER BY, any expression), as a missing property is in Neo4j. A column the label does not publish (a governed object column) or the role may not see reads the same way, as null with no sign that it exists. A write is never silent. CREATE or SET of such a name is refused with <Label> has no property <name>.

Every node has one built-in property, _id, which is its key. It is the key column's value, or for a compound key the node's identity, Label|v1|v2 (each key column in key order, a | or \ in a value escaped by a \). _id works in WHERE, RETURN and ORDER BY, and every returned model node carries it among its properties. A node a graph session created has no key a statement wrote. It is answered with the properties it was given (CREATE (:X {prop: 42}) returns (:X {prop: 42})), and its _id is the session's own handle on it. Where a source's own _id column is the key, as a MongoDB collection's is, the built-in reads the same value.

Rules:

The label must resolve to exactly one registered table. Ambiguous or unknown labels are hard errors; no fuzzy matching. New labels or types cannot be created through Cypher.
Every write is gated on the target table's write grants: the role needs the write right and a grant for the operation, and the columns written must be in the grant. A role without them is rejected at compile time.
The backing source connector must support DML. Read-only sources (Trino-federated, Iceberg without a Delta connector) reject writes at translation time.
A relationship backed by a foreign key or by columns on one of its end tables is read-only. Writing one is refused by name.
A relationship backed by a registered junction table is writable as an edge. CREATE (a)-[:TYPE]->(b) inserts the junction row when both ends are already matched in the statement; DELETE of such an edge deletes that row; SET of its properties updates it. The write is governed exactly like any other write on the junction table: write grants, row rules, masks and the role's admission all apply. A relationship type the model does not register, or one registered between other labels than the two nodes it joins, is refused by name. Both end nodes must be matched in the same statement; a relationship write never creates its end nodes.
Writes run through the full write pipeline: RLS injection and post-mutation hooks (response-cache invalidation, materialized-view stale marking, Kafka change events, hot-table reload).
MERGE rewrites to MATCH then CREATE under the same write rules above. It is a governed model write when the result label names exactly one model table, and ephemeral otherwise.
DETACH DELETE of a model node is governed when its label names exactly one model table and each relationship it detaches is a registered junction row. A foreign-key relationship cannot be detached and is refused by name.
REMOVE n:Label takes a session label off a node. REMOVE of a model node's properties is refused.

Protocol Access¶

Cypher reaches the same governed pipeline over two transports:

HTTP. POST /data/cypher with a JSON body ({"query": "...", "params": {...}}). Returns typed rows, or affected_rows for writes. Graph variables in the RETURN clause serialize as JSON: nodes carry id, label, tableLabel, and properties; edges carry identity, start, end, type, properties, startNode, and endNode; paths carry nodes, edges, and length/hops. Registered commands are also callable here via CALL fn(args) YIELD col1, col2, and positional args map to the command's declared argument names in order.
Bolt. A Neo4j-compatible binary protocol server (PackStream codec, chunked framing) that lets Neo4j Browser, Bloom, and Bolt drivers run Cypher over the federated graph. It starts when PROVISA_BOLT_PORT is set to a non-zero value and is disabled by default; set PROVISA_BOLT_CERT / PROVISA_BOLT_KEY for TLS. Bolt auth maps principal to user and database to role: SHOW DATABASES lists one entry per (view × role) pair, named provisa_<role> (business domains) or provisa_ops_<role> (with system/meta/ops domains); :use selects the active role and view. Relationships receive durable integer IDs via a rel_ids table, mirroring the node_ids design. Registered commands are callable with CALL command(args), and positional args map to declared argument names in order; CALL dbms.* / CALL db.* procedures take precedence.

Graph Analytics¶

POST /data/graph-analytics runs a Cypher query, builds an in-memory NetworkX graph from the resulting nodes and edges, executes a named algorithm, and merges a _analytics dict into each node and edge before returning them as JSON with an elapsed_ms field. The _analytics keys vary by algorithm: centrality yields score; community detection yields cluster; k-core yields core_number; degree centrality adds in_degree and out_degree. The endpoint rejects graphs above a configurable size (default 10,000 nodes / 50,000 edges) with HTTP 413; Girvan-Newman is capped at 500 nodes unless the caller passes force=true.

Graph Session¶

Each principal has one session graph, shared by its Bolt connections, HTTP Cypher requests and MCP calls. Within that session you can create nodes and relationships that exist only in the session, visible to that principal alone, and read them beside your governed model data in the same query.

Creating ephemeral nodes¶

A CREATE that names a label the model does not register produces an ephemeral node. Labels follow the same Domain:Table convention as model nodes, with one reserved domain, Session:

CREATE (:Session:Note {text: 'follow up tomorrow'})
CREATE (:Note {text: 'draft'})           -- resolves to Session:Note

The first free label in a CREATE becomes the node's storage table in the session domain. Each further label is a tag: it goes in a session node_labels table so MATCH (n:Tag) finds the node and labels(n) lists all its labels. A CREATE naming a domain label is refused by name; session nodes never join a governed domain.

A CREATE naming exactly one model table and no free labels is a governed model write, as before.

Creating ephemeral relationships¶

An ephemeral relationship may carry any type name. When its type name matches a registered junction type and both end nodes are matched model nodes, it is a governed junction write (see §Writes). Every other relationship, with one end ephemeral or a type the model does not register, is session-scoped.

MATCH (c:Customer {id: 42})
CREATE (n:Note {text: 'call back'})
CREATE (c)-[:HAS_NOTE]->(n)

The Customer row is governed; the Note and HAS_NOTE are session-only.

Reading session data beside governed data¶

A MATCH on a label with session matches expands to include those matches alongside the model's rows, under the role's governance. Labels with no session matches compile exactly as before, and nothing changes for a session that holds no ephemeral objects.

MATCH (c:Customer)-[:HAS_NOTE]->(n:Note) RETURN c.name, n.text

If the session holds Note nodes, this returns model customers joined to their session notes. The governed Customer rows are read under the role's row rules and masks as always.

labels(n) on a model node returns its Domain:Table plus any session tags you added with SET n:Tag. On an ephemeral node it returns the labels the CREATE gave it.

Adding and removing labels on live nodes¶

SET n:Label adds a label to any node in the session. On a model node it records a session tag; the model itself is not changed. REMOVE n:Label removes a session tag from a model node, or any label from an ephemeral node. Label writes on model nodes end with the session.

Session scope¶

The session belongs to the principal on every surface: Bolt, HTTP Cypher and MCP. It is stored per principal and kept for temp_tables.graph_session_retention_days after its last use. It ends when the principal clears it, when the principal's rights change, or when the retention expires.

A SQL temporary table belongs to its own connection and is not part of the graph session; only the graph session is shared across a principal's connections.

Clearing the session¶

Clear the session from the Cypher page (DELETE /data/cypher/session) or through the MCP tool. Bolt has no procedure for it.

Limitations¶

Design constraints¶

Write clauses supported:
CREATE
MATCH … SET
MATCH … DELETE
MERGE
DETACH DELETE

Each executes as a governed table write. MERGE runs MATCH then CREATE under the same write rules. DETACH DELETE is governed when the node has an exact model target and each detached relationship is a registered junction. REMOVE n:Label removes a session label; REMOVE of a model node's properties is refused. APOC procedures are not supported.

Relationship properties exist only on junction-backed edges. An edge declared over a foreign-key column pair exists only as join metadata in the model and stores no attributes, so WHERE r.since > 2020 or RETURN r.weight means nothing on it. An edge declared over a junction table does carry them. The associative table's remaining columns are the relationship's properties, and RETURN r returns them. A WHERE on one compiles to a predicate on the junction alias, so it restricts the traversal itself instead of filtering rows after assembly. The junction table is dropped from the node side of the graph schema, because it's an edge here and a table everywhere else.
Bidirectional traversal (a)-[]-(b) rewrites to the forward and backward UNION ALL of every matching directed relationship in the model. Every relationship in the model has a direction, and the bidirectional syntax is sugar that expands to both. The extra branches are emitted at the outermost query level. A later MATCH pattern in the same query isn't duplicated across the branches, which limits bidirectional patterns in a query with several MATCH clauses.
Recursive paths require a bound. Variable-length patterns ([*]) must include an upper bound (e.g. [*..10]). Unbounded traversal is rejected at parse time to prevent runaway recursive CTEs.

Behaviour notes¶

shortestPath on non-self-referential paths uses a flat JOIN with no hops ordering. When the start and end types differ and the schema has no self-referential relationship, the translator emits a flat JOIN chain along the shortest schema path. That code path doesn't track hops, so it emits no ORDER BY hops. The result is the structurally shortest schema path. It may differ from the shortest path through the data across multiple rows.
Multiple schema paths produce UNION ALL. When two schema paths of equal hop count connect the same start and end types (e.g. Person -[WORKS_AT]-> Company and Person -[MANAGES]-> Company), both are emitted as UNION ALL branches. Deduplication of rows that appear in both branches is not performed.
One RelationshipMapping per source→target pair and rel_type combination. If two GraphQL fields on the same source type produce the same rel_type string (after uppercasing) to the same target type, the second registration overwrites the first in CypherLabelMap.relationships. The relationship key includes source and target type names, so distinct source/target pairs with the same type name each get their own entry and are not affected.
WITH clause CTEs are named _w0, _w1, … Names are assigned positionally within a single translation call. Composing multiple translated queries (e.g. in a batch) can produce colliding CTE names if they are concatenated naively.

Expression and pattern coverage¶

Cypher expressions are parsed into an AST and lowered node-to-node to SQL (provisa/cypher/expr_parser.py, provisa/gql/expr_visitor.py). The grammar follows the openCypher oC_Expression precedence tower. Supported: literals, parameters, property access, n.prop, index and slice, arithmetic (+ - * / % ^), comparison, IN, STARTS WITH / ENDS WITH / CONTAINS / =~, IS [NOT] NULL, boolean AND / OR / XOR / NOT, CASE, list and map literals, list and pattern comprehensions (including the p = (…) path binding), map projection, reduce, the all / any / none / single quantifiers, existential subqueries, and function calls.

Labels are fixed; you cannot create object types through Cypher. A label resolves to a known domain, a known object type or a qualified domain:object_type, which is the closed set the registered schema defines. Cypher never introduces a new label or type. You can create instances only of types already defined in a writable data source: CREATE writes rows into such a table (see §Writes) and can't define a new label or type. Both label forms are accepted and mean the same test, the postfix n:Label and the verbose n IS :Label (with its negation n IS NOT :Label). A qualified label is written n:domain:object_type.
shortestPath and allShortestPaths work only inside MATCH. In a pattern (MATCH p = shortestPath((a:Person)-[:KNOWS*..5]->(b:Person))) they translate to a WITH RECURSIVE CTE and need labeled source and target nodes. They aren't supported in expression position, for example RETURN shortestPath((a)-[*]->(b)) or WHERE length(shortestPath((a)-[*]->(b))) < 5. The recursive rewrite is driven off the MATCH clause, and there's no correlated subquery to carry it.
List comprehensions, REDUCE and quantifiers run against list values; pattern comprehensions traverse. reduce(...), all/any/none/single(...) and the list comprehension [x IN list | …] work over a list expression and lower to the engine's higher-order list functions. They don't walk the graph themselves. The pattern comprehension [(a)-[:R]->(b) WHERE p | e] does traverse: its graph pattern is addressed as a correlated subquery, so its source is a traversal. To feed traversal results into the list forms, use nodes(p) / relationships(p) / collect(...), or use a pattern comprehension directly.