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: 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: 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 node carries it among its properties. 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.
Relationships cannot be written: they are derived from the model's declared joins, not stored edges. Targeting a relationship is a hard error. A junction-backed edge is no exception: the associative table behind it is itself a registered table, and rows are written to that table, not to the edge.
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, DETACH DELETE, and REMOVE are unsupported and rejected at parse time.

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.

Limitations¶

Design constraints¶

Writes are limited to CREATE, SET, and DELETE. These execute as direct table writes through the same pipeline as GraphQL and SQL mutations. See §Writes above. MERGE, DETACH DELETE, and REMOVE are rejected at parse time. APOC procedures are also rejected.
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: 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.