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

Relations: referential integrity for a document store

STATUS: not implemented. No _relations collection, RelationManager or relation RPC exists in the codebase. This design and its companion plan (docs/superpowers/plans/2026-08-04-relations-v2.5.0.md) were never executed. The "v2.5.0" target is wrong - v2.5.0 was skipped entirely (2.4.5 → 2.6.0). Re-validate against the current write path before using either: both predate the v2.8.0 fix for caching an MDB_dbi before commit.

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, _relidx_<name>, inside the project env. Key is the composite <parentId>\0<childId>; value empty. LMDB orders keys, so a parent's children are a cursor MDB_SET_RANGE over the <parentId>\0 prefix - O(children), not O(collection).

A list-valued index (parentId -> [childIds]) is rejected: it turns every child insert into a read-modify-write on one key shared by all siblings.

Index sub-dbs carry the v2.4.4 identity sentinel like any other sub-db, and count() and scan() skip the sentinel 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.

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.