smartbotic-database/docs/superpowers/specs/2026-08-03-relations-design.md

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

1. ⚠ 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.
2. ⚠ 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.
3. 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.
4. 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.

{
  "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.