# Features AGE does not support

## Problem

The README lists "anything AGE does not support", with examples: multiple labels per
node, pattern comprehensions, temporal and spatial types, indexes and constraints
through Cypher. These are very different in size, so this plan treats each as a
separate part that can be done (or dropped) on its own. In order of value per effort:

- [D. Indexes and constraints](#d-indexes-and-constraints-through-cypher): mostly
  translation to SQL DDL; high value.
- [B. Pattern comprehensions](#b-pattern-comprehensions): a generalisation of an
  existing rewrite.
- [C. Temporal types](#c-temporal-types): useful, but needs a convention for
  storing values.
- [A. Multiple labels](#a-multiple-labels-per-node): invasive; needs a real parser.
- [E. Spatial types](#e-spatial-types): least demand; do only on request.

For all parts, boltage should keep failing clearly (`FeatureNotSupported` with a
message that names the feature) for whatever is still unsupported, rather than
passing AGE's sometimes cryptic errors through.

Where an approach depends on AGE behaviour that is not yet verified, the plan says
"spike": check it against the AGE version in the image (`apache/age:release_PG18_1.8.0`)
before building on it. Also check AGE's issue tracker for each part: if AGE adds the
feature itself, boltage only has to pass it through.

---

## D. Indexes and constraints through Cypher

Today `SHOW INDEXES` and `SHOW CONSTRAINTS` return empty results
(`boltage/backend.py` `_run_system`), and `CREATE INDEX` reaches AGE, which rejects it.
Label tables are ordinary PostgreSQL tables, so indexes and constraints can be real
PostgreSQL indexes and constraints on them.

### Commands to support

| Cypher | SQL |
| --- | --- |
| `CREATE INDEX [name] [IF NOT EXISTS] FOR (n:L) ON (n.p [, n.q])` | `CREATE INDEX name ON g."L" (<expr p> [, <expr q>])` |
| `CREATE INDEX ... FOR ()-[r:T]-() ON (r.p)` | same, on the edge table `g."T"` |
| `CREATE CONSTRAINT [name] FOR (n:L) REQUIRE n.p IS UNIQUE` | `CREATE UNIQUE INDEX name ON g."L" (<expr p>)` |
| `... REQUIRE (n.p, n.q) IS NODE KEY` | unique index on both, plus a `CHECK` that both exist |
| `... REQUIRE n.p IS NOT NULL` | `ALTER TABLE g."L" ADD CONSTRAINT name CHECK (<p exists>)` |
| `DROP INDEX name [IF EXISTS]`, `DROP CONSTRAINT name [IF EXISTS]` | `DROP INDEX` / `ALTER TABLE ... DROP CONSTRAINT` |
| `SHOW INDEXES`, `SHOW CONSTRAINTS` | read `pg_index` / `pg_constraint` for the graph's schema |

Full-text, vector, point and `TEXT` indexes, and property type constraints, stay
unsupported with a clear error. `RANGE` indexes map to the default b-tree.

### Steps

1. **Spike: the index expression.** An index only helps if it matches the expression
   AGE generates for `n.p` in a `WHERE`. Use `EXPLAIN` on
   `SELECT * FROM cypher('g', $$ MATCH (n:L) WHERE n.p = 1 RETURN n $$) AS (n agtype)`
   to find it (believed to be
   `agtype_access_operator(VARIADIC ARRAY[properties, '"p"'::agtype])`), and check that
   an index on it is used for `=`, range comparisons, and for `MATCH (n:L {p: 1})`
   (which may need a GIN index on `properties` instead). Record the findings in the
   code, since they are AGE implementation details.
2. **Label creation.** `CREATE INDEX` on a label that does not exist yet creates it
   first with `create_vlabel` / `create_elabel`, as Neo4j allows indexes on labels with
   no nodes.
3. **Names.** Use the Cypher name as the PostgreSQL index/constraint name; generate
   one like Neo4j does (`index_<hash>`, `constraint_<hash>`) when none is given. Store
   the Cypher-level kind (`RANGE` index, `UNIQUENESS` constraint, ...) in a
   `COMMENT ON INDEX` so `SHOW` can report it without guessing.
4. **`SHOW INDEXES` / `SHOW CONSTRAINTS`.** Query the catalog for indexes and
   constraints on the graph's label tables, map table → label through `ag_label`,
   and parse the property name back out of the indexed expression (or the comment).
   Indexes AGE creates itself (on `id`, `start_id`, `end_id`) are shown as Neo4j's
   built-in `LOOKUP` indexes or hidden; decide after looking at what neo4j-browser
   does with them. Also fill `indexes` / `constraints` in `db.schema.visualization`.
5. **Errors.** A unique violation from AGE's `CREATE`/`MERGE` already maps to
   `ConstraintValidationFailed` (sqlstate 23, `failure_from_pg`); make the message
   name the constraint and property like Neo4j does. Creating a unique constraint on
   data that has duplicates fails in PostgreSQL; map it to
   `Neo.ClientError.Schema.ConstraintCreationFailed`.
6. **Privileges.** Only the table owner can create indexes; map the permission
   error to `Neo.ClientError.Security.Forbidden`.
7. Parsing: these commands have a fixed shape, so regexes in `_run_system` (like the
   existing `CREATE DATABASE` handling) are enough.

### Tests

Smoke test: create an index and see it used (via `EXPLAIN` once plan 01 lands, or
via `SHOW INDEXES`); a unique constraint rejects a duplicate `CREATE` with
`ConstraintError`; `IF NOT EXISTS` / `IF EXISTS`; drop both.

---

## B. Pattern comprehensions

`[(a)-[r:T]->(b) WHERE b.x > 1 | b.name]` is a list built from a sub-pattern.
boltage rewrites one specific neo4j-browser query (`rewrite_for_age`,
`boltage/cypher.py:208`); everything else fails in AGE.

### Approach

Rewrite a pattern comprehension in clause `C` into an `OPTIONAL MATCH` plus
`collect()` before `C`, carrying the variables in scope:

```
MATCH (a) RETURN a.name, [(a)-->(b) | b.name] AS friends
```

becomes

```
MATCH (a)
OPTIONAL MATCH (a)-->(boltage_b1) WITH a, collect(boltage_b1.name) AS boltage_pc1
RETURN a.name, boltage_pc1 AS friends
```

1. Find pattern comprehensions: a `[` whose contents start with a pattern
   (`(`…`)` followed by a relationship) and contain a top-level `|`. List
   comprehensions (`[x IN list | ...]`) must not match.
2. Rename the comprehension's own new variables (here `b`) so they cannot clash
   with outer variables.
3. Use the scope tracker from the [shared groundwork](README.md#shared-groundwork) to
   list the variables to carry in the `WITH`.
4. Each comprehension gets its own `OPTIONAL MATCH ... WITH ... collect()` step, one
   after the other, so several comprehensions in one clause do not multiply rows.
5. A `WHERE` inside the comprehension becomes the `WHERE` of the `OPTIONAL MATCH`.
6. Replace the existing neo4j-browser-specific regex with the general rewrite, and
   keep its smoke test.

### Semantic differences to handle or document

- `collect()` drops nulls, a comprehension keeps them: `[(a)-->(b) | b.missing]`
  gives `[null, null]` in Neo4j. Collect a wrapper (`collect([b.missing])`) and unwrap
  it with a list comprehension, if AGE supports list comprehensions (spike);
  otherwise document the difference.
- `OPTIONAL MATCH` with no match yields one row with nulls, which `collect()` turns
  into `[]`: correct.
- Comprehensions inside aggregations, `WHERE` clauses, `ORDER BY`, or nested in
  another comprehension: support the `WITH`/`RETURN`/`WHERE` projection positions
  first; reject the rest with `FeatureNotSupported`.
- A `WITH` that carries the variables also ends the scope for aggregation in the
  original clause; when `C` itself aggregates (`RETURN a, count(*), [(a)-->(b)|b]`),
  the rewrite must keep grouping keys intact. Reject that case at first.

Pattern **expressions** used as predicates (`WHERE (a)-->(b)`) and `EXISTS { }` /
`COUNT { }` subqueries are related; check which ones AGE accepts and extend the
same rewrite where it does not.

### Tests

Unit tests for detection (pattern vs list comprehension, strings) and the rewrite
text; smoke tests comparing results with hand-written `OPTIONAL MATCH` equivalents.

---

## C. Temporal types

AGE has no date, time or duration values. Neo4j clients send and receive them as
Bolt structures (`Date`, `Time`, `LocalTime`, `DateTime`, `LocalDateTime`,
`Duration`), which boltage's packstream does not handle: a temporal parameter
reaches `literal()` (`boltage/cypher.py`) as an unknown `Structure` and fails.

The README already recommends storing points in time as Unix timestamps or ISO 8601
strings with a UTC offset. The plan builds on that convention.

### Phase 1: accept temporal parameters

1. Unpack Bolt temporal structures in `packstream.py` into Python
   `date`/`time`/`datetime` values and a small duration type: `D` (Date), `T` (Time),
   `t` (LocalTime), `d` (LocalDateTime), `I` / `i` (DateTime with offset / zone id,
   Bolt 5 and 4.4 with the `utc` patch), `F` / `f` (the legacy DateTime variants of
   Bolt 4.4 without the patch) and `E` (Duration).
2. `literal()` turns them into ISO 8601 strings (with offset for zoned values, the
   way the README recommends). `Duration` becomes an ISO 8601 duration string
   (`P1DT2H`).
3. Document that the values come back as strings.

This alone makes drivers able to write temporal values, and the README's views and
generated columns work on them.

### Phase 2: temporal functions in Cypher

1. **Spike:** AGE can call PostgreSQL functions from Cypher when they are
   schema-qualified and take/return `agtype`. Check this works, and its performance.
2. Ship a small SQL extension script (a `boltage` schema with `boltage.datetime(agtype)`,
   `boltage.date(agtype)`, `boltage.duration(agtype)`, `boltage.datetime_truncate`,
   ...) that returns normalised ISO 8601 strings, implemented with PostgreSQL's
   `timestamptz` arithmetic. Install it from the container's init script; for other
   setups document `psql -f`.
3. Rewrite calls to `date()`, `datetime()`, `localdatetime()`, `time()`,
   `duration()` and `duration.between()` in the Cypher text to the qualified
   functions (outside strings, using `scan()`). Leave `timestamp()`, which AGE has.
4. Property access such as `d.year` on a temporal value cannot be rewritten
   reliably, because boltage does not know which values are temporal. Support the
   function forms (`date.truncate`, ...) and document that accessors are not
   available.

ISO strings normalised to UTC compare and sort correctly as strings, so `ORDER BY`
and range filters keep working.

### Phase 3 (optional): typed output

Returning strings means drivers give users `str` instead of `datetime`. To return
real temporal values, the type has to be stored with the value, for example as a
map `{"__boltage_type": "datetime", "value": "..."}`. That breaks string comparison
and the README's SQL views, so only do this behind a setting, if users ask for it.

### Tests

Smoke test: write a `datetime` and a `date` parameter and read them back as ISO
strings; `RETURN datetime('2024-01-02T03:04:05+01:00')` returns the normalised UTC
string; ordering by a datetime property.

---

## A. Multiple labels per node

In AGE a label is a table and a vertex lives in exactly one. Supporting
`CREATE (:A:B)`, `MATCH (n:B)` where `B` is a second label, `SET n:B` and
`REMOVE n:B` needs boltage to keep extra labels elsewhere and rewrite queries.

### Approach

- Keep the first label as the AGE label (the table), and extra labels in a reserved
  property, `__labels` (a list).
- Rewrite:
  - `CREATE (n:A:B {..})` → `CREATE (n:A {.., __labels: ['B']})`
  - `MATCH (n:B)` → `MATCH (n) WHERE (label(n) = 'B' OR 'B' IN n.__labels)`
  - `MATCH (n:A:B)` → `MATCH (n:A) WHERE 'B' IN n.__labels`
  - `n:B` as a predicate → the same expression
  - `SET n:B` → `SET n.__labels = coalesce(n.__labels, []) + 'B'` (when `B` is not
    the table label); `REMOVE n:B` similarly.
- Output: `agtype.parse` merges `__labels` into the node's labels and hides the
  property; `keys(n)`, `properties(n)` and `n {.*}` must hide it too (rewrite to
  filter it out).
- Catalog: `db.labels`, `SHOW` and counts must include labels that only exist in
  `__labels` (needs a scan, or a GIN index on `properties`).

### Why it is last

Every rewrite needs to understand patterns, predicates and projections exactly;
regexes on masked text will not be correct enough. This needs a real Cypher parser
(for example generated from the openCypher grammar), which is a larger change than
everything else in these plans together. `MATCH (n:B)` for a secondary label also
turns into a scan of all vertices. Do this only if there is real demand, and look
at AGE's own plans for multiple labels first.

---

## E. Spatial types

Same shape as temporal types: AGE has no point type, and Bolt has `Point2D` (`X`)
and `Point3D` (`Y`) structures.

1. Unpack Bolt points and store them as maps `{srid, x, y[, z]}` via `literal()`.
2. Optionally, behind a setting, pack maps with exactly those keys back into Bolt
   points, so neo4j-browser and drivers show points.
3. Provide `point()` and `point.distance()` as `boltage` schema functions (as in
   part C), using plain Euclidean distance for Cartesian SRIDs and haversine for
   WGS-84 (4326/4979).

Spatial indexes would need PostGIS and are out of scope.

## README changes

Per part as it lands: replace the item in the Limitations bullet with a short
section under "How it works" (indexes and constraints, pattern comprehension
rewriting, how temporal values are stored).