# Relations: referential integrity for a document store

> **STATUS: not implemented. RE-VALIDATED 2026-08-09 against the code at v2.10.0.**
>
> The design's *reasoning* holds up. Several of its facts and one of its design
> choices do not, and there are new hard constraints it could not have known
> about. Read this section before the body; where they disagree, this section is
> right.
>
> **Still true, checked:**
> - No `_relations` collection, `RelationManager` or relation RPC exists.
> - `FilterOp` still has exactly eleven values, all single-collection predicates.
> - `MemoryStore::update` still forces `updated.id = id` (`memory_store.cpp:502`
>   and `:802`), so the "no `ON UPDATE`" non-goal still rests on solid ground.
> - MemoryStore is still in the write path, and `_`-prefixed collections are
>   still excluded from the LMDB mirror (`dual_write_mirror.hpp:43`), so
>   `_relations` lives in MemoryStore and is WAL'd/snapshotted - exactly parallel
>   to `_views`, as the body claims.
> - Test targets still build with `-UNDEBUG`, so assertions stay live in Release.
> - The restrict-race analysis and `validate_on_write` as its remedy still stand.
>
> **Wrong or stale:**
> 1. **Target version.** v2.5.0 was skipped entirely (2.4.5 → 2.6.0). The project
>    is at **2.10.0**. Every "v2.5.0" in this document and its plan is wrong.
> 2. **"Phase C" collides.** The rollout's "Phase C (later)" now clashes with the
>    abandoned v2.0 storage Phase C. Do not reuse that vocabulary - see
>    `docs/ROADMAP.md`, "Storage engine history".
> 3. **A line citation has rotted.** The body cites
>    `docs/2026-05-15-v2.0-storage-engine-design.md:316` for "SQL surface"; it is
>    now line **329**. Cite text, not line numbers.
> 4. **The non-goal "no candidate-key or unique-constraint machinery" is out of
>    date.** v2.10.0 built it - `find_duplicate_values` plus enforcement inside
>    the document's own transaction - and then deliberately left it unreachable
>    because it cannot be enforced through the mirror (see below). So references
>    to a non-`_id` field become *reachable* once the write path is fixed, which
>    is the same fix relations needs. They are one piece of work, not two.
> 5. **"Every `LmdbDocumentStore` operation opens its own `WriteTxn`" is now only
>    half true.** Internal helpers already take a `WriteTxn&` (`open_for_write`,
>    `maintainIndexes`, `markIndexMultiValued`); eight public operations still
>    open their own. The restructuring the body asks for is therefore smaller
>    than it was, and partly precedented.
>
> **New hard constraints (all post-date this spec):**
> 6. **⚠ A sub-db created at runtime MUST be registered with
>    `cacheCommittedDbi()` after its transaction commits.** Since v2.8.1 the read
>    path never calls `mdb_dbi_open` - `try_open_for_read` serves only from the
>    primed cache, and a miss means "no such sub-db". So a `_relidx_` sub-db
>    created without that call would be **invisible to every later read**, and
>    relations would silently not enforce. This is the single most likely way to
>    get relations wrong now, and nothing in the body warns about it.
> 7. **⚠ Never cache an `MDB_dbi` before its transaction commits** (v2.8.0), and
>    **never open a second `MDB_env` on a path this process already has open**
>    (v2.4.4 - POSIX locks are per-process). Both cost production outages.
> 8. **Index sub-dbs now carry TWO reserved keys**, not one: the v2.4.4 identity
>    sentinel and the v2.10.0 multivalued marker. Use `is_index_meta_key()`;
>    checking only `is_identity_key()` will miscount and mis-walk.
> 9. **Give `_relidx_` a key-format version in its name from the first commit**
>    (`_relidx1_`). v2.9.1 had to bump `_idx_` → `_idx2_` when an encoding
>    changed, precisely so a stale index is never read under new rules. Paying
>    that forward costs nothing now.
>
> **One design choice is now beaten - see "Reverse index" below for the
> replacement.**
>
> **The plan to execute is `docs/superpowers/plans/2026-08-09-relations-v2.11.0.md`**,
> re-planned from this corrected design. The older companion plan
> (`2026-08-04-relations-v2.5.0.md`, 3278 lines) is superseded: it was written
> against the pre-v2.8 write path and embeds the wrong version and the stale facts
> above. Keep it only as raw material.

Status: approved design, not yet implemented
Target: v2.5.0 (Phases A + B together)
Date: 2026-08-03

## Problem

smartbotic-database has no relation support of any kind. There are no foreign
keys, no joins, no cascade delete, and no way to ask whether a reference points
at anything. `FilterOp` has eleven values, all single-collection predicates; the
migration runner has twelve op types, none referential. This was deliberate -
`docs/2026-05-15-v2.0-storage-engine-design.md:316` says "SQL surface. We're a
document store, not a relational DB."

The gap that matters in practice is deletion. `smartbotic-automation` has
`workflows` referenced by `executions`, `users` referenced by `sessions`, and
`credentials` referenced from node configuration. Deleting a parent today leaves
children pointing at nothing, silently, with no way to detect it.

## Non-goals

- **A join or lookup API.** Read-time stitching is a separate, cheaper feature.
  This spec is only about integrity.
- **`ON UPDATE`.** A document's `_id` cannot change: `MemoryStore::update`
  forces `updated.id = id` over whatever the caller supplied, so the map key and
  `doc.id` can never diverge. The equivalent operation is delete + insert, which
  is governed by `on_delete`.
- **References to non-`_id` fields.** A reference names document identity. There
  is no candidate-key or unique-constraint machinery.
- **Cross-project relations.** Each project owns its own LMDB env and no
  transaction spans two envs. `CreateRelation` rejects a cross-project pair.

## Terminology

A **dangling reference** is a reference to a document that does not exist. This
spec never calls that an "orphan": v2.4.4 gave `_orphans_<subdb>` a different
meaning (a row parked because it was misfiled into the wrong sub-db), and
overloading the word would make operator-facing messages ambiguous.

## Data model

A relation is a document in the `_relations` system collection, exactly parallel
to how `_views` works.

```json
{
  "name": "executions_workflow",
  "child": "smartbotic-automation:executions",
  "child_field": "workflow_id",
  "parent": "smartbotic-automation:workflows",
  "on_delete": "restrict",
  "validate_on_write": false
}
```

`child_field` holds the parent's `_id`. Anchoring on document identity is what
keeps this document-native rather than SQL-shaped: a document store has exactly
one identity per document, so a reference has one unambiguous target and the
reverse index needs no key-migration path.

### Keying and project scope

Relations are keyed by their qualified `<project>:<name>` form in `_relations`,
and `ListRelations` takes a `project` filter (empty means all projects, for
operator and CLI use).

This is not optional polish. v2.4.2 fixed exactly this bug in views: a
name-keyed registry was added without project awareness, the client qualified
`collection` but not `name`, and view lookups silently returned zero rows for
two releases. Relations have the same registry shape and the same
client-qualifies-names surface, so they get the qualified key from the first
commit.

### Policies

| `on_delete` | Scalar reference | Array reference |
|---|---|---|
| `restrict` (default) | parent delete fails | fails if any array contains the id |
| `cascade` | child document deleted | id pulled from array, child survives |
| `set_null` | field set to `null` | id pulled from array |
| `no_action` | delete proceeds, dangling reference allowed | same |

`restrict` is the default. A delete that quietly removes documents from another
collection is a surprise, and the caller should have to opt into it.

For array-valued references, `cascade` and `set_null` collapse to the same
behavior: pull the id, keep the document. Deleting a node because one of its
three credentials went away would be worse than useless. This is the one place a
MySQL mental model actively misleads, so it must be prominent in the docs.

### Field resolution

`child_field` uses the dot-notation rules views already use
(`config.credential_id`), stopping at arrays for traversal. The terminal field
may itself be an array of ids. A field that is absent or `null` is not a
reference: it never blocks a delete and never counts as dangling.

### Consequence: re-keying a parent

Because `_id` is immutable, changing a parent's id means delete + insert. Under
`restrict` that delete is blocked while children exist, so the operator must
re-point the children first. Under `cascade` it would destroy them. This is a
legitimate workflow and the documentation must state it plainly rather than
leaving operators to discover it.

## Enforcement

### Reverse index

One LMDB sub-db per relation, `_relidx1_<name>`, inside the project env.

> **RE-VALIDATED: use `MDB_DUPSORT`, not composite keys.** This section
> originally specified key = `<parentId>\0<childId>` with an empty value, walked
> with `MDB_SET_RANGE` over the `<parentId>\0` prefix. That was right when
> nothing else existed. Since v2.9.0 the secondary-index machinery does exactly
> this shape as **key = parent id, data = child id, DUPSORT** - built, tested and
> measured on production-sized data.
>
> Three reasons to switch:
> 1. **`mdb_cursor_count` gives a parent's child count without reading the
>    children.** That is precisely what `restrict` and `DescribeDelete` need, and
>    it is O(1)-ish. The composite-key form has to walk the range to count.
> 2. Removing one id from a parent's set is `mdb_del(key, data)`, which deletes
>    just that pair - no read-modify-write, so the rejection of a list-valued
>    index below is satisfied without a bespoke encoding.
> 3. The surrounding discipline already exists and is tested: reserved-key
>    handling (`is_index_meta_key`), handle caching after commit
>    (`cacheCommittedDbi`), boot priming, and the identity sentinel.
>
> A list-valued index (`parentId -> [childIds]` as one value) stays rejected, for
> the reason the original text gives: it turns every child insert into a
> read-modify-write on one key shared by all siblings. DUPSORT is not that - it
> stores the set as separate data items.

Index sub-dbs carry the v2.4.4 identity sentinel like any other sub-db, and
`count()` and `scan()` skip **every** reserved key - as of v2.10.0 there are two
(identity, and the multivalued marker), so use `is_index_meta_key()`. Without
this a stale `MDB_dbi` could write index entries into an unrelated sub-db, which
is precisely the failure that misfiled 31 production rows.

### Write-path change

Every `LmdbDocumentStore` operation currently opens its own `WriteTxn`. Atomic
cascade needs one transaction spanning the parent sub-db, each affected child
sub-db, and the index sub-dbs, so those operations gain txn-accepting overloads.
This is additive: existing single-operation callers keep the convenience form.

### Delete sequence

For a document that is a parent in at least one relation:

1. Resolve children through the reverse index.
2. `restrict` -> abort, return `FAILED_PRECONDITION` naming the relation, the
   child count, and a sample of blocking ids.
3. Write WAL entries for the parent and every child mutation; fsync.
4. One LMDB `WriteTxn`: mutate children, update index entries, delete parent,
   commit.
5. Apply the same mutations to MemoryStore, taking each affected collection's
   lock in turn.

**Step 3 is mandatory.** MemoryStore is rebuilt at boot from snapshot + WAL
replay, not from LMDB. A cascade that wrote only LMDB would have its child
deletions resurrected on the next restart. WAL-first also makes a crash
mid-sequence recoverable, since replaying an already-applied delete is a no-op.

### Child re-pointing

Updating a child's reference from parent A to parent B is an ordinary write, but
the index entry must move from A to B inside the same transaction as the
document write, and `validate_on_write` (when enabled) checks B exists.

### Known limitation: the restrict race

A child insert racing a `restrict` check can still land after the parent is
gone. LMDB's single writer serializes the two transactions, but the loser simply
commits second. `validate_on_write` closes the gap, because the child's own
transaction re-checks the parent. This is the concrete reason to enable it on
relations where dangling references are a real problem, and it must be
documented rather than hidden.

### Bootstrap

`CreateRelation` scans the child collection once to build the index and to
validate existing data against the policy. On a large collection this blocks, so
it belongs in a migration rather than a live call. The scan is idempotent.

## Per-collection enable/disable

**Requested 2026-08-09.** Both relation enforcement and uniqueness must be
switchable per collection.

Home: **`CollectionCfg`, in the `_collection_meta` system collection.** That is
where `timestampPrecision`, `versioningEnabled` and (since v2.9.0)
`indexedFields` already live, and the reason is durability: `_collection_meta` is
an ordinary collection, so it is WAL'd and snapshotted for free. Putting these in
`CollectionOptions` instead would need a new WAL op to survive a restart between
snapshots - the same reasoning recorded for `versioningEnabled` in v2.4.5.

```
struct CollectionCfg {
    std::string timestampPrecision = "ns";
    bool versioningEnabled = true;
    std::vector<std::string> indexedFields;
    std::vector<std::string> uniqueFields;      // per-collection by construction
    bool relationsEnforced = true;              // new
};
```

**`uniqueFields`** is inherently per-collection - it names fields of one
collection - so it needs no separate switch. It must be a **subset of
`indexedFields`**: the check reads the index, so uniqueness without an index has
nothing to read. Declaring uniqueness over data that already contains duplicates
is **refused**, with examples, rather than accepted: a constraint that is false
from the moment it is created would fail later writes for reasons the caller
never caused. `find_duplicate_values()` already does this.

**`relationsEnforced`** defaults to **true**, because declaring a relation names
its child and parent collections explicitly - the declaration *is* the opt-in,
and a declared constraint that silently does nothing would be worse than no
constraint. The switch is an operator escape hatch for the cases that genuinely
need one: a bulk import, or a collection under write pressure where the
`restrict` check costs more than the integrity is worth.

Two things this must get right, both learned the hard way here:

- **Disabling is not retroactive and must say so.** Turning enforcement off then
  deleting parents creates dangling references that turning it back on will not
  detect - only the `relations check` command will. The RPC response and the CLI
  must state that at the point of use, not only in documentation.
- **Re-arm on boot.** `DatabaseService::applyIndexDeclarations()` already re-reads
  `indexedFields` at startup and exists for exactly this reason: a declaration
  that is persisted but not applied leaves the write path not maintaining
  something the read path still trusts. Relation and uniqueness switches need the
  same treatment in the same place, and it is load-bearing, not bookkeeping.

Both switches belong on `ConfigureCollection`, which is already a **partial
update**: absent means "leave unchanged". Use `optional bool` for
`relations_enforced` - a plain proto3 bool defaults to false and would silently
disable enforcement on any unrelated config call, which is the exact trap v2.4.5
hit with `versioning_enabled`.

## Surface

**RPCs**, mirroring the view surface: `CreateRelation`, `DropRelation`,
`ListRelations`, `GetRelationInfo`. A `create_relation` migration op so
relations are versioned alongside the rest of the schema.

**`DescribeDelete(collection, id)`** reports what a delete would do: which
relations apply, how many children each matches, which policy fires, and whether
it would be blocked. This is the feature that makes the behavior discoverable.
A relational schema exposes its constraints through DDL; a document store has no
schema to read, so the system has to answer "what happens if I delete this?" as
a query. Expect this to matter more day to day than cascade itself.

**CLI**: `smartbotic-db-cli relations` lists declarations, `relations check
<name>` reports dangling references without changing anything.

**Errors** name the relation, the child count, and a sample of ids. An operator
should learn what to do next from the message.

## Testing

An **end-to-end test over the real RPC boundary is mandatory**, modelled on
`tests/load_test/test_views_multiproject.sh`. The v2.4.2 views bug survived two
releases because in-process unit tests passed while the client/server boundary
was broken. Relations share that shape and would fail the same way.

Unit tests use real check functions, never bare `assert()`. Test targets build
with `-UNDEBUG` as of v2.4.3, so assertions stay live in Release, but the habit
matters independently.

Coverage must include: scalar and array references, nested dot-paths, absent and
null references, each of the four policies, child re-pointing, cascade
atomicity under a simulated crash between WAL and commit, index bootstrap on a
non-empty collection, cross-project rejection, and per-project isolation of
identically-named relations.

## Rollout

Phases A and B ship together as v2.5.0. A protocol change warrants the minor
bump, and v2.5 is already the marker for extracting the v1.x migration path into
its own package.

- **Phase A** - `_relations`, declaration, reverse index, `restrict` and
  `no_action`, `DescribeDelete`, dangling-reference check. No destructive
  policy, no write-path restructuring.
- **Phase B** - txn-accepting `LmdbDocumentStore` overloads, WAL sequencing,
  `cascade` and `set_null`, and `validate_on_write`. The last one ships here
  rather than later because it is the documented remedy for the restrict race;
  shipping the limitation without its mitigation would leave operators no way
  to close the gap.
- **Phase C** (later) - repair command for existing dangling references.

## Open risks

- The write-path restructuring in Phase B touches the same code that produced
  the v2.4.3 and v2.4.4 incidents. That code is now covered by the sub-db
  identity sentinel and a boot-time placement audit, which should catch a
  regression early, but it deserves care.
- MemoryStore remains in the write path. Cascade treats LMDB as the atomic unit
  and MemoryStore as a cache that follows. If the two diverge, existing
  `mirror_healthy_` handling applies. The full simplification arrives with the
  MemoryStore decommission, which is out of scope here.