# Relations: referential integrity for a document store 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_` 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 `:` 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_`, inside the project env. Key is the composite `\0`; value empty. LMDB orders keys, so a parent's children are a cursor `MDB_SET_RANGE` over the `\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 ` 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.