Relations (v2.5.0) Implementation Plan

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

Goal: Add referential integrity to smartbotic-database - per-relation on_delete policies over a document model, with a reverse index, a dry-run API, and atomic cascade.

Architecture: Relations are documents in a _relations system collection, keyed by qualified <project>:<name>, mirroring how _views works. A reverse index sub-db per relation (_relidx_<name>) maps <parentId>\0<childId> so a parent's children are a cursor range scan. restrict is a read-and-refuse and needs no atomicity; cascade and set_null run as one LMDB WriteTxn spanning parent, children and index sub-dbs, preceded by WAL entries.

Tech Stack: C++20, LMDB, nlohmann/json, yyjson, gRPC/Protobuf, spdlog, CMake + Ninja.

Spec: docs/superpowers/specs/2026-08-03-relations-design.md

Global Constraints

No em-dashes or en-dashes in any output - code, comments, commit messages, log strings, docs. Use plain ASCII hyphens.
Relations are keyed by qualified <project>:<name> in _relations, from the first commit. v2.4.2 fixed exactly this bug in views; do not repeat it.
Never use the word "orphan" for a dangling reference. v2.4.4 gave _orphans_<subdb> a different meaning (a misfiled row). Say "dangling reference".
Every new sub-db carries the v2.4.4 identity sentinel. write_subdb_identity on first write-open, verify_subdb_identity on cached-handle reuse, and is_identity_key skipped in every scan and count.
Cross-project relations are rejected. No LMDB transaction spans two envs.
References name _id only. No candidate keys, no ON UPDATE.
NEVER open a second MDB_env on a path this process already has open. LMDB coordinates readers with POSIX record locks, and POSIX locks are per-process, not per-descriptor: closing ANY fd on the file releases every lock the process holds on it. The first v2.4.4 build opened a second env for its placement audit and closed it, which destroyed the service's own lock state and returned EINVAL from every mdb_txn_begin for the life of the process - all LMDB reads were down for ~90 seconds in production. RelationIndex therefore takes an LmdbEnv& and borrows the project's existing env. Do NOT add a path-taking constructor, and do not open an env anywhere outside the existing registry.
Test scripts derive their own root. tests/load_test/*.sh must compute ROOT="$(cd "$(dirname "$0")/../.." && pwd)" rather than hardcoding /data/smartbotic-database, so a script run from a git worktree builds and tests that worktree's binaries instead of silently exercising the main checkout's.
Build: cmake -B build -G Ninja && cmake --build build -j$(nproc)
Test: cd build/tests && ctest --output-on-failure
Tests use real check functions, never bare assert() in new files. (-UNDEBUG is set on test targets as of v2.4.3, but write checks that fail loudly regardless.)
Target version: VERSION becomes 2.5.0 in the final task, not before.

File Structure

File	Responsibility
`service/src/relations/reference_extract.{hpp,cpp}`	Pure functions: pull reference ids out of a document given a dot-path. No storage deps.
`service/src/relations/relation_manager.{hpp,cpp}`	`_relations` persistence + cache. Mirrors `ViewManager`.
`service/src/storage/relation_index.{hpp,cpp}`	Reverse index sub-db over LMDB. Composite keys, sentinel-aware.
`service/src/relations/relation_engine.{hpp,cpp}`	Delete orchestration: policy resolution, WAL sequencing, cascade txn.
`tests/test_reference_extract.cpp`	Unit: extraction across scalar/array/nested/null shapes.
`tests/test_relation_index.cpp`	Unit: index over a real LmdbEnv.
`tests/test_relation_manager.cpp`	Unit: declaration, validation, project scoping, legacy re-key.
`tests/load_test/test_relations_e2e.sh`	End-to-end over the real RPC boundary. Mandatory.

Task 1: Reference extraction

Pure functions with no storage dependency, so they can be tested exhaustively before anything touches LMDB. Every later task depends on these semantics being exactly right.

Files:

Create: service/src/relations/reference_extract.hpp
Create: service/src/relations/reference_extract.cpp
Test: tests/test_reference_extract.cpp
Modify: tests/CMakeLists.txt

Interfaces:

Consumes: nothing.
Produces: smartbotic::database::extractReferences(const nlohmann::json& doc, const std::string& path) -> std::vector<std::string> and smartbotic::database::removeReference(nlohmann::json& doc, const std::string& path, const std::string& id) -> bool.
[ ] Step 1: Write the failing test

Create tests/test_reference_extract.cpp:

// Reference extraction semantics. A reference names a parent document's _id.
// The field may be a scalar, an array of ids, or live at a dot-path. Absent
// and null are NOT references - they never block a delete and never count as
// dangling.
#include "relations/reference_extract.hpp"

#include <iostream>
#include <nlohmann/json.hpp>
#include <string>
#include <vector>

using smartbotic::database::extractReferences;
using smartbotic::database::removeReference;

namespace {

int failures = 0;

void check(bool cond, const std::string& msg) {
    std::cout << (cond ? "  PASS  " : "  FAIL  ") << msg << "\n";
    if (!cond) ++failures;
}

void test_scalar_reference() {
    nlohmann::json d = {{"workflow_id", "wf-1"}, {"name", "run"}};
    auto refs = extractReferences(d, "workflow_id");
    check(refs.size() == 1 && refs[0] == "wf-1", "scalar reference yields one id");
}

void test_absent_field_is_not_a_reference() {
    nlohmann::json d = {{"name", "run"}};
    check(extractReferences(d, "workflow_id").empty(), "absent field yields no ids");
}

void test_null_is_not_a_reference() {
    nlohmann::json d = {{"workflow_id", nullptr}};
    check(extractReferences(d, "workflow_id").empty(), "null yields no ids");
}

void test_array_of_references() {
    nlohmann::json d = {{"credential_ids", {"c-1", "c-2", "c-3"}}};
    auto refs = extractReferences(d, "credential_ids");
    check(refs.size() == 3, "array yields every id");
    check(refs[0] == "c-1" && refs[2] == "c-3", "array preserves ids");
}

void test_array_skips_nulls_and_non_strings() {
    nlohmann::json d = {{"credential_ids", {"c-1", nullptr, 42, "c-2"}}};
    auto refs = extractReferences(d, "credential_ids");
    check(refs.size() == 2, "array skips null and non-string entries");
}

void test_nested_dot_path() {
    nlohmann::json d = {{"config", {{"credential_id", "c-9"}}}};
    auto refs = extractReferences(d, "config.credential_id");
    check(refs.size() == 1 && refs[0] == "c-9", "dot-path resolves nested scalar");
}

void test_nested_missing_intermediate() {
    nlohmann::json d = {{"name", "n"}};
    check(extractReferences(d, "config.credential_id").empty(),
          "missing intermediate yields no ids");
}

void test_traversal_stops_at_arrays() {
    // Views' projection semantics stop at arrays for traversal. A path THROUGH
    // an array is not resolved; only a terminal array of ids is.
    nlohmann::json d = {{"steps", {{{"credential_id", "c-1"}}}}};
    check(extractReferences(d, "steps.credential_id").empty(),
          "traversal does not descend into arrays");
}

void test_empty_string_is_not_a_reference() {
    nlohmann::json d = {{"workflow_id", ""}};
    check(extractReferences(d, "workflow_id").empty(), "empty string yields no ids");
}

void test_remove_scalar_sets_null() {
    nlohmann::json d = {{"workflow_id", "wf-1"}};
    check(removeReference(d, "workflow_id", "wf-1"), "remove reports a change");
    check(d["workflow_id"].is_null(), "scalar reference becomes null");
}

void test_remove_scalar_ignores_other_id() {
    nlohmann::json d = {{"workflow_id", "wf-1"}};
    check(!removeReference(d, "workflow_id", "wf-2"), "non-matching id is a no-op");
    check(d["workflow_id"] == "wf-1", "non-matching id leaves value intact");
}

void test_remove_array_pulls_element() {
    nlohmann::json d = {{"credential_ids", {"c-1", "c-2", "c-3"}}};
    check(removeReference(d, "credential_ids", "c-2"), "pull reports a change");
    check(d["credential_ids"].size() == 2, "pull removes exactly one element");
    check(d["credential_ids"][0] == "c-1" && d["credential_ids"][1] == "c-3",
          "pull preserves order of survivors");
}

void test_remove_array_pulls_all_duplicates() {
    nlohmann::json d = {{"credential_ids", {"c-1", "c-2", "c-1"}}};
    check(removeReference(d, "credential_ids", "c-1"), "pull reports a change");
    check(d["credential_ids"].size() == 1, "pull removes every duplicate");
}

void test_remove_array_to_empty_keeps_array() {
    // cascade and set_null both PULL for arrays. The document survives, and an
    // emptied array stays an empty array rather than becoming null.
    nlohmann::json d = {{"credential_ids", {"c-1"}}};
    check(removeReference(d, "credential_ids", "c-1"), "pull reports a change");
    check(d["credential_ids"].is_array() && d["credential_ids"].empty(),
          "emptied array stays an empty array");
}

void test_remove_nested() {
    nlohmann::json d = {{"config", {{"credential_id", "c-9"}}}};
    check(removeReference(d, "config.credential_id", "c-9"), "nested remove reports a change");
    check(d["config"]["credential_id"].is_null(), "nested scalar becomes null");
}

}  // namespace

int main() {
    test_scalar_reference();
    test_absent_field_is_not_a_reference();
    test_null_is_not_a_reference();
    test_array_of_references();
    test_array_skips_nulls_and_non_strings();
    test_nested_dot_path();
    test_nested_missing_intermediate();
    test_traversal_stops_at_arrays();
    test_empty_string_is_not_a_reference();
    test_remove_scalar_sets_null();
    test_remove_scalar_ignores_other_id();
    test_remove_array_pulls_element();
    test_remove_array_pulls_all_duplicates();
    test_remove_array_to_empty_keeps_array();
    test_remove_nested();

    std::cout << (failures ? "\nFAILED: " + std::to_string(failures) + " check(s)\n"
                           : "\ntest_reference_extract: all passed\n");
    return failures ? 1 : 0;
}

Add to tests/CMakeLists.txt, immediately before the foreach(_t ...) block that applies -UNDEBUG:

# Reference extraction unit test (pure functions, no storage deps).
add_executable(test_reference_extract
    test_reference_extract.cpp
    ${CMAKE_CURRENT_SOURCE_DIR}/../service/src/relations/reference_extract.cpp
)
target_include_directories(test_reference_extract PRIVATE
    ${CMAKE_CURRENT_SOURCE_DIR}/../service/src
)
if(TARGET nlohmann_json::nlohmann_json)
    target_link_libraries(test_reference_extract PRIVATE nlohmann_json::nlohmann_json)
else()
    target_include_directories(test_reference_extract PRIVATE ${NLOHMANN_JSON_INCLUDE_DIRS})
endif()

Add test_reference_extract to the foreach(_t ...) list, and add below the other add_test lines:

add_test(NAME reference_extract COMMAND test_reference_extract)

Step 2: Run test to verify it fails

Run: cmake -B build -G Ninja && cmake --build build -j$(nproc) --target test_reference_extract Expected: FAIL to compile - relations/reference_extract.hpp does not exist.

Step 3: Write the implementation

Create service/src/relations/reference_extract.hpp:

// Reference extraction for relations.
//
// A reference names a parent document's _id. The child field may hold a single
// id or an array of ids, and may live at a dot-path. Absent, null and empty
// string are NOT references: they never block a delete and never count as a
// dangling reference.
//
// Traversal stops at arrays, matching the dot-notation semantics views already
// use (service/src/views/projection.cpp). A TERMINAL array of ids is resolved;
// a path THROUGH an array is not.
#pragma once

#include <nlohmann/json.hpp>
#include <string>
#include <vector>

namespace smartbotic::database {

// Every reference id held at `path`. Empty when the field is absent, null,
// empty, or not id-shaped.
std::vector<std::string> extractReferences(const nlohmann::json& doc,
                                           const std::string& path);

// Remove `id` from the reference at `path`. A scalar becomes null; an array
// has every matching element pulled and stays an array even when emptied -
// for arrays, cascade and set_null are both a pull, and the document survives.
// Returns true if the document changed.
bool removeReference(nlohmann::json& doc,
                     const std::string& path,
                     const std::string& id);

}  // namespace smartbotic::database

Create service/src/relations/reference_extract.cpp:

#include "relations/reference_extract.hpp"

#include <sstream>

namespace smartbotic::database {

namespace {

std::vector<std::string> splitPath(const std::string& path) {
    std::vector<std::string> parts;
    std::stringstream ss(path);
    std::string part;
    while (std::getline(ss, part, '.')) {
        if (!part.empty()) parts.push_back(part);
    }
    return parts;
}

// Walk to the container holding the terminal key. Returns nullptr when any
// intermediate is missing or is not an object - traversal never descends into
// an array.
const nlohmann::json* resolveParent(const nlohmann::json& doc,
                                    const std::vector<std::string>& parts) {
    const nlohmann::json* cur = &doc;
    for (size_t i = 0; i + 1 < parts.size(); ++i) {
        if (!cur->is_object()) return nullptr;
        auto it = cur->find(parts[i]);
        if (it == cur->end()) return nullptr;
        cur = &(*it);
    }
    return cur->is_object() ? cur : nullptr;
}

bool isIdShaped(const nlohmann::json& v) {
    return v.is_string() && !v.get<std::string>().empty();
}

}  // namespace

std::vector<std::string> extractReferences(const nlohmann::json& doc,
                                           const std::string& path) {
    std::vector<std::string> out;
    const auto parts = splitPath(path);
    if (parts.empty()) return out;

    const nlohmann::json* parent = resolveParent(doc, parts);
    if (parent == nullptr) return out;

    auto it = parent->find(parts.back());
    if (it == parent->end()) return out;

    if (isIdShaped(*it)) {
        out.push_back(it->get<std::string>());
        return out;
    }
    if (it->is_array()) {
        for (const auto& e : *it) {
            if (isIdShaped(e)) out.push_back(e.get<std::string>());
        }
    }
    return out;
}

bool removeReference(nlohmann::json& doc,
                     const std::string& path,
                     const std::string& id) {
    const auto parts = splitPath(path);
    if (parts.empty()) return false;

    nlohmann::json* cur = &doc;
    for (size_t i = 0; i + 1 < parts.size(); ++i) {
        if (!cur->is_object()) return false;
        auto it = cur->find(parts[i]);
        if (it == cur->end()) return false;
        cur = &(*it);
    }
    if (!cur->is_object()) return false;

    auto it = cur->find(parts.back());
    if (it == cur->end()) return false;

    if (isIdShaped(*it)) {
        if (it->get<std::string>() != id) return false;
        *it = nullptr;
        return true;
    }
    if (it->is_array()) {
        nlohmann::json kept = nlohmann::json::array();
        bool changed = false;
        for (const auto& e : *it) {
            if (isIdShaped(e) && e.get<std::string>() == id) {
                changed = true;
                continue;
            }
            kept.push_back(e);
        }
        if (changed) *it = std::move(kept);
        return changed;
    }
    return false;
}

}  // namespace smartbotic::database

Step 4: Run test to verify it passes

Run: cmake --build build -j$(nproc) --target test_reference_extract && ./build/tests/test_reference_extract Expected: PASS, 15 checks, "test_reference_extract: all passed".

[ ] Step 5: Commit

git add service/src/relations/reference_extract.hpp service/src/relations/reference_extract.cpp tests/test_reference_extract.cpp tests/CMakeLists.txt
git commit -m "feat(relations): reference extraction from documents

A reference names a parent _id. The field may be scalar, an array of ids,
or at a dot-path. Absent, null and empty string are not references, so
they never block a delete nor count as dangling.

Traversal stops at arrays, matching views' dot-notation semantics. A
terminal array of ids resolves; a path through an array does not.

removeReference pulls every matching element from an array and leaves the
array in place when emptied - for arrays cascade and set_null are both a
pull and the child document survives."

Task 2: Reverse index sub-db

Files:

Create: service/src/storage/relation_index.hpp
Create: service/src/storage/relation_index.cpp
Test: tests/test_relation_index.cpp
Modify: tests/CMakeLists.txt

Interfaces:

Consumes: LmdbEnv, WriteTxn, ReadTxn from storage/lmdb_env.hpp and storage/lmdb_txn.hpp; write_subdb_identity, verify_subdb_identity, is_identity_key from storage/subdb_identity.hpp.
Produces: class smartbotic::db::storage::RelationIndex with add, remove, children_of, count_children, clear, and the free function relation_index_subdb_name(std::string_view relation) -> std::string.
[ ] Step 1: Write the failing test

Create tests/test_relation_index.cpp:

// Reverse index over a real LmdbEnv.
//
// Key layout is the composite <parentId>\0<childId> with an empty value, so a
// parent's children are a cursor range scan over the <parentId>\0 prefix and
// concurrent sibling writes never contend on a shared value. A list-valued
// index would turn every child insert into a read-modify-write on one hot key.
#include "storage/lmdb_env.hpp"
#include "storage/relation_index.hpp"

#include <algorithm>
#include <filesystem>
#include <iostream>
#include <string>
#include <unistd.h>
#include <vector>

namespace fs = std::filesystem;

using smartbotic::db::storage::LmdbEnv;
using smartbotic::db::storage::LmdbEnvOpts;
using smartbotic::db::storage::RelationIndex;
using smartbotic::db::storage::relation_index_subdb_name;

namespace {

int failures = 0;

void check(bool cond, const std::string& msg) {
    std::cout << (cond ? "  PASS  " : "  FAIL  ") << msg << "\n";
    if (!cond) ++failures;
}

std::string tmpdir(const char* tag) {
    static int n = 0;
    std::string p = "/tmp/sbdb-relidx-" + std::to_string(::getpid()) + "-" +
                    std::to_string(n++) + "-" + tag;
    std::error_code ec;
    fs::remove_all(p, ec);
    return p;
}

struct TmpEnv {
    std::string path;
    LmdbEnv env;
    explicit TmpEnv(const char* tag)
        : path(tmpdir(tag)), env(LmdbEnvOpts{path, 64ULL << 20, 256, 126, false}) {}
    ~TmpEnv() { std::error_code ec; fs::remove_all(path, ec); }
};

void test_subdb_name() {
    check(relation_index_subdb_name("executions_workflow") == "_relidx_executions_workflow",
          "index sub-db name is prefixed");
}

void test_add_and_list_children() {
    TmpEnv t("add-list");
    RelationIndex idx(t.env, "r1");
    idx.add("wf-1", "ex-1");
    idx.add("wf-1", "ex-2");
    idx.add("wf-2", "ex-3");

    auto kids = idx.children_of("wf-1");
    std::sort(kids.begin(), kids.end());
    check(kids.size() == 2, "children_of returns only that parent's children");
    check(kids[0] == "ex-1" && kids[1] == "ex-2", "children ids are correct");
    check(idx.children_of("wf-2").size() == 1, "second parent is independent");
    check(idx.children_of("wf-3").empty(), "unknown parent yields none");
}

void test_add_is_idempotent() {
    TmpEnv t("idem");
    RelationIndex idx(t.env, "r1");
    idx.add("wf-1", "ex-1");
    idx.add("wf-1", "ex-1");
    check(idx.children_of("wf-1").size() == 1, "duplicate add does not duplicate");
}

void test_remove() {
    TmpEnv t("remove");
    RelationIndex idx(t.env, "r1");
    idx.add("wf-1", "ex-1");
    idx.add("wf-1", "ex-2");
    check(idx.remove("wf-1", "ex-1"), "remove reports it existed");
    check(!idx.remove("wf-1", "ex-1"), "second remove reports absent");
    auto kids = idx.children_of("wf-1");
    check(kids.size() == 1 && kids[0] == "ex-2", "remove takes only the named child");
}

void test_count_children() {
    TmpEnv t("count");
    RelationIndex idx(t.env, "r1");
    for (int i = 0; i < 5; ++i) idx.add("wf-1", "ex-" + std::to_string(i));
    check(idx.count_children("wf-1") == 5, "count_children counts them");
    check(idx.count_children("wf-none") == 0, "count of unknown parent is zero");
}

void test_prefix_isolation() {
    // A parent id that is a string prefix of another must not bleed. The NUL
    // separator is what guarantees this.
    TmpEnv t("prefix");
    RelationIndex idx(t.env, "r1");
    idx.add("wf-1", "ex-1");
    idx.add("wf-10", "ex-2");
    check(idx.count_children("wf-1") == 1, "wf-1 does not pick up wf-10's children");
    check(idx.count_children("wf-10") == 1, "wf-10 is independent");
}

void test_identity_sentinel_not_a_child() {
    // The sub-db carries the v2.4.4 sentinel. It must never surface as a child
    // nor inflate a count.
    TmpEnv t("sentinel");
    RelationIndex idx(t.env, "r1");
    idx.add("wf-1", "ex-1");
    check(idx.count_children("wf-1") == 1, "sentinel does not inflate count");
    for (const auto& c : idx.children_of("wf-1")) {
        check(c == "ex-1", "sentinel never surfaces as a child id");
    }
}

void test_survives_env_reopen() {
    // Handles live in the env's shared table; reopening the env is the only
    // faithful restart simulation (see v2.4.3).
    const std::string path = tmpdir("reopen");
    const LmdbEnvOpts opts{path, 64ULL << 20, 256, 126, false};
    {
        LmdbEnv env(opts);
        RelationIndex idx(env, "r1");
        idx.add("wf-1", "ex-1");
    }
    {
        LmdbEnv env(opts);
        RelationIndex idx(env, "r1");
        check(idx.count_children("wf-1") == 1, "index survives an env reopen");
    }
    std::error_code ec;
    fs::remove_all(path, ec);
}

void test_clear() {
    TmpEnv t("clear");
    RelationIndex idx(t.env, "r1");
    idx.add("wf-1", "ex-1");
    idx.add("wf-2", "ex-2");
    idx.clear();
    check(idx.count_children("wf-1") == 0, "clear empties the index");
    check(idx.count_children("wf-2") == 0, "clear empties every parent");
}

}  // namespace

int main() {
    test_subdb_name();
    test_add_and_list_children();
    test_add_is_idempotent();
    test_remove();
    test_count_children();
    test_prefix_isolation();
    test_identity_sentinel_not_a_child();
    test_survives_env_reopen();
    test_clear();

    std::cout << (failures ? "\nFAILED: " + std::to_string(failures) + " check(s)\n"
                           : "\ntest_relation_index: all passed\n");
    return failures ? 1 : 0;
}

Add to tests/CMakeLists.txt (before the -UNDEBUG foreach):

# Relation reverse index test (needs a real LmdbEnv).
add_executable(test_relation_index
    test_relation_index.cpp
    ${CMAKE_CURRENT_SOURCE_DIR}/../service/src/storage/relation_index.cpp
    ${CMAKE_CURRENT_SOURCE_DIR}/../service/src/storage/lmdb_env.cpp
    ${CMAKE_CURRENT_SOURCE_DIR}/../service/src/storage/lmdb_txn.cpp
    ${CMAKE_CURRENT_SOURCE_DIR}/../service/src/storage/subdb_identity.cpp
)
target_include_directories(test_relation_index PRIVATE
    ${CMAKE_CURRENT_SOURCE_DIR}/../service/src
    ${LMDB_INCLUDE_DIR}
)
target_link_libraries(test_relation_index PRIVATE ${LMDB_LIBRARY})
if(TARGET spdlog::spdlog)
    target_link_libraries(test_relation_index PRIVATE spdlog::spdlog)
else()
    target_link_libraries(test_relation_index PRIVATE ${SPDLOG_LIBRARIES})
    target_include_directories(test_relation_index PRIVATE ${SPDLOG_INCLUDE_DIRS})
endif()
if(TARGET nlohmann_json::nlohmann_json)
    target_link_libraries(test_relation_index PRIVATE nlohmann_json::nlohmann_json)
else()
    target_include_directories(test_relation_index PRIVATE ${NLOHMANN_JSON_INCLUDE_DIRS})
endif()

Add test_relation_index to the -UNDEBUG foreach list and:

add_test(NAME relation_index COMMAND test_relation_index)

Step 2: Run test to verify it fails

Run: cmake -B build -G Ninja && cmake --build build -j$(nproc) --target test_relation_index Expected: FAIL to compile - storage/relation_index.hpp does not exist.

Step 3: Write the implementation

Create service/src/storage/relation_index.hpp:

// Reverse index for a relation: parent id -> child ids.
//
// One LMDB sub-db per relation, `_relidx_<relation>`, 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).
//
// The NUL separator is load-bearing: without it, parent "wf-1" would pick up
// "wf-10"'s children on a prefix scan.
//
// A list-valued index (parentId -> [childIds]) is deliberately NOT used. It
// would turn every child insert into a read-modify-write on a single key
// shared by all siblings.
#pragma once

#include <cstdint>
#include <string>
#include <string_view>
#include <vector>

namespace smartbotic::db::storage {

class LmdbEnv;
class WriteTxn;

// Sub-db name for a relation's index.
std::string relation_index_subdb_name(std::string_view relation);

class RelationIndex {
public:
    RelationIndex(LmdbEnv& env, std::string_view relation);

    // Record that `child` references `parent`. Idempotent.
    void add(std::string_view parent, std::string_view child);

    // As above, inside a caller-supplied transaction, so index maintenance can
    // share the atomic unit of the document write.
    void add(WriteTxn& txn, std::string_view parent, std::string_view child);

    // Drop the edge. Returns true if it existed.
    bool remove(std::string_view parent, std::string_view child);
    bool remove(WriteTxn& txn, std::string_view parent, std::string_view child);

    // Every child of `parent`.
    std::vector<std::string> children_of(std::string_view parent);

    // Number of children, without materialising the ids.
    uint64_t count_children(std::string_view parent);

    // Drop every edge. Used by DropRelation and by index rebuild.
    void clear();

private:
    unsigned int open_dbi(WriteTxn& txn);

    LmdbEnv& env_;
    std::string relation_;
    std::string subdb_;
};

}  // namespace smartbotic::db::storage

Create service/src/storage/relation_index.cpp:

#include "storage/relation_index.hpp"

#include "storage/lmdb_env.hpp"
#include "storage/lmdb_txn.hpp"
#include "storage/subdb_identity.hpp"

#include <lmdb.h>

#include <stdexcept>
#include <string>

namespace smartbotic::db::storage {

namespace {

constexpr char kSep = '\0';

std::string composite(std::string_view parent, std::string_view child) {
    std::string k;
    k.reserve(parent.size() + 1 + child.size());
    k.append(parent);
    k.push_back(kSep);
    k.append(child);
    return k;
}

std::string prefix_of(std::string_view parent) {
    std::string p;
    p.reserve(parent.size() + 1);
    p.append(parent);
    p.push_back(kSep);
    return p;
}

MDB_val to_val(std::string_view s) {
    return MDB_val{s.size(), const_cast<char*>(s.data())};
}

std::string_view to_sv(const MDB_val& v) {
    return std::string_view(static_cast<const char*>(v.mv_data), v.mv_size);
}

void check_rc(int rc, const char* what) {
    if (rc != MDB_SUCCESS) {
        throw std::runtime_error(std::string("LMDB ") + what + ": " +
                                 mdb_strerror(rc));
    }
}

}  // namespace

std::string relation_index_subdb_name(std::string_view relation) {
    return "_relidx_" + std::string(relation);
}

RelationIndex::RelationIndex(LmdbEnv& env, std::string_view relation)
    : env_(env),
      relation_(relation),
      subdb_(relation_index_subdb_name(relation)) {}

unsigned int RelationIndex::open_dbi(WriteTxn& txn) {
    MDB_dbi dbi = 0;
    check_rc(mdb_dbi_open(txn.raw(), subdb_.c_str(), MDB_CREATE, &dbi),
             "dbi_open (relation index)");
    // v2.4.4 - stamp and verify. A misbound handle must fail at the point of
    // harm rather than write index edges into a stranger's sub-db.
    write_subdb_identity(txn, dbi, subdb_);
    verify_subdb_identity(txn, dbi, subdb_);
    return dbi;
}

void RelationIndex::add(std::string_view parent, std::string_view child) {
    WriteTxn txn(env_);
    add(txn, parent, child);
    txn.commit();
}

void RelationIndex::add(WriteTxn& txn, std::string_view parent,
                        std::string_view child) {
    const unsigned int dbi = open_dbi(txn);
    const std::string key = composite(parent, child);
    MDB_val k = to_val(key);
    MDB_val v{0, nullptr};
    check_rc(mdb_put(txn.raw(), dbi, &k, &v, 0), "put (relation index)");
}

bool RelationIndex::remove(std::string_view parent, std::string_view child) {
    WriteTxn txn(env_);
    const bool existed = remove(txn, parent, child);
    txn.commit();
    return existed;
}

bool RelationIndex::remove(WriteTxn& txn, std::string_view parent,
                           std::string_view child) {
    const unsigned int dbi = open_dbi(txn);
    const std::string key = composite(parent, child);
    MDB_val k = to_val(key);
    const int rc = mdb_del(txn.raw(), dbi, &k, nullptr);
    if (rc == MDB_NOTFOUND) return false;
    check_rc(rc, "del (relation index)");
    return true;
}

std::vector<std::string> RelationIndex::children_of(std::string_view parent) {
    std::vector<std::string> out;
    ReadTxn rtxn(env_);

    MDB_dbi dbi = 0;
    const int orc = mdb_dbi_open(rtxn.raw(), subdb_.c_str(), 0, &dbi);
    if (orc == MDB_NOTFOUND) return out;
    check_rc(orc, "dbi_open (relation index read)");

    MDB_cursor* cur = nullptr;
    check_rc(mdb_cursor_open(rtxn.raw(), dbi, &cur), "cursor_open (relation index)");
    struct Guard {
        MDB_cursor* c;
        ~Guard() { if (c) mdb_cursor_close(c); }
    } guard{cur};

    const std::string pfx = prefix_of(parent);
    MDB_val k = to_val(pfx);
    MDB_val v{0, nullptr};

    int rc = mdb_cursor_get(cur, &k, &v, MDB_SET_RANGE);
    while (rc == MDB_SUCCESS) {
        const std::string_view key = to_sv(k);
        if (key.size() < pfx.size() || key.compare(0, pfx.size(), pfx) != 0) break;
        if (!is_identity_key(key)) {
            out.emplace_back(key.substr(pfx.size()));
        }
        rc = mdb_cursor_get(cur, &k, &v, MDB_NEXT);
    }
    if (rc != MDB_SUCCESS && rc != MDB_NOTFOUND) {
        check_rc(rc, "cursor_get (relation index)");
    }
    return out;
}

uint64_t RelationIndex::count_children(std::string_view parent) {
    return static_cast<uint64_t>(children_of(parent).size());
}

void RelationIndex::clear() {
    WriteTxn txn(env_);
    const unsigned int dbi = open_dbi(txn);
    // mdb_drop with del=0 empties the sub-db but keeps the handle valid. The
    // identity sentinel is dropped with everything else, so restamp it.
    check_rc(mdb_drop(txn.raw(), dbi, 0), "drop (relation index)");
    write_subdb_identity(txn, dbi, subdb_);
    txn.commit();
}

}  // namespace smartbotic::db::storage

Step 4: Run test to verify it passes

Run: cmake --build build -j$(nproc) --target test_relation_index && ./build/tests/test_relation_index Expected: PASS, all checks, "test_relation_index: all passed".

[ ] Step 5: Commit

git add service/src/storage/relation_index.hpp service/src/storage/relation_index.cpp tests/test_relation_index.cpp tests/CMakeLists.txt
git commit -m "feat(relations): reverse index sub-db

One LMDB sub-db per relation, _relidx_<name>, keyed by the composite
<parentId>NUL<childId> with an empty value. A parent's children are a
cursor range scan over the <parentId>NUL prefix, so lookup is O(children)
rather than O(collection).

The NUL separator is load-bearing: without it a prefix scan for wf-1
would also match wf-10's children. Covered by a test.

A list-valued index was rejected - it would make every child insert a
read-modify-write on one key shared by all siblings.

Carries the v2.4.4 identity sentinel and verifies it on every write-open,
because a stale MDB_dbi silently misfiled 31 production rows."

Task 3: RelationManager - declaration and persistence

Files:

Create: service/src/relations/relation_manager.hpp
Create: service/src/relations/relation_manager.cpp
Test: tests/test_relation_manager.cpp
Modify: tests/CMakeLists.txt

Interfaces:

Consumes: MemoryStore (for _relations persistence), resolveCollection from project_addressing.hpp.
Produces: struct RelationInfo and class RelationManager with loadFromStore, createRelation, dropRelation, getRelation, listRelations, relationsForParent(qualifiedCollection), relationsForChild(qualifiedCollection).
[ ] Step 1: Write the failing test

Create tests/test_relation_manager.cpp:

// Relation declaration, validation and project scoping.
//
// Relations are keyed by their qualified <project>:<name> form from the first
// commit. v2.4.2 fixed exactly this bug in views: a name-keyed registry added
// without project awareness meant lookups silently returned nothing for two
// releases.
#include "memory_store.hpp"
#include "relations/relation_manager.hpp"

#include <iostream>
#include <string>

using smartbotic::database::MemoryStore;
using smartbotic::database::RelationInfo;
using smartbotic::database::RelationManager;

namespace {

int failures = 0;

void check(bool cond, const std::string& msg) {
    std::cout << (cond ? "  PASS  " : "  FAIL  ") << msg << "\n";
    if (!cond) ++failures;
}

MemoryStore::Config testConfig() {
    MemoryStore::Config cfg;
    cfg.nodeId = "test";
    cfg.maxMemoryBytes = 64ULL * 1024 * 1024;
    return cfg;
}

RelationInfo makeRelation(const std::string& name = "default:executions_workflow") {
    RelationInfo r;
    r.name = name;
    r.child = "default:executions";
    r.childField = "workflow_id";
    r.parent = "default:workflows";
    r.onDelete = "restrict";
    r.validateOnWrite = false;
    return r;
}

void test_create_and_get() {
    MemoryStore store(testConfig());
    RelationManager mgr(store);
    std::string err;
    check(mgr.createRelation(makeRelation(), err), "createRelation succeeds");
    auto got = mgr.getRelation("default:executions_workflow");
    check(got.has_value(), "getRelation finds it by qualified name");
    check(got->childField == "workflow_id", "field round-trips");
    check(got->onDelete == "restrict", "policy round-trips");
}

void test_default_policy_is_restrict() {
    RelationInfo r = makeRelation();
    r.onDelete.clear();
    MemoryStore store(testConfig());
    RelationManager mgr(store);
    std::string err;
    check(mgr.createRelation(r, err), "empty policy is accepted");
    check(mgr.getRelation("default:executions_workflow")->onDelete == "restrict",
          "empty policy defaults to restrict");
}

void test_rejects_unknown_policy() {
    RelationInfo r = makeRelation();
    r.onDelete = "explode";
    MemoryStore store(testConfig());
    RelationManager mgr(store);
    std::string err;
    check(!mgr.createRelation(r, err), "unknown policy is rejected");
    check(err.find("explode") != std::string::npos, "error names the bad policy");
}

void test_rejects_cross_project() {
    RelationInfo r = makeRelation();
    r.parent = "other:workflows";
    MemoryStore store(testConfig());
    RelationManager mgr(store);
    std::string err;
    check(!mgr.createRelation(r, err), "cross-project relation is rejected");
    check(err.find("project") != std::string::npos, "error explains why");
}

void test_rejects_duplicate() {
    MemoryStore store(testConfig());
    RelationManager mgr(store);
    std::string err;
    check(mgr.createRelation(makeRelation(), err), "first create succeeds");
    check(!mgr.createRelation(makeRelation(), err), "duplicate is rejected");
}

void test_same_name_in_two_projects() {
    // The whole point of qualified keys.
    MemoryStore store(testConfig());
    RelationManager mgr(store);
    std::string err;

    RelationInfo a = makeRelation("default:by_workflow");
    RelationInfo b;
    b.name = "acme:by_workflow";
    b.child = "acme:executions";
    b.childField = "workflow_id";
    b.parent = "acme:workflows";
    b.onDelete = "restrict";

    check(mgr.createRelation(a, err), "default project relation created");
    check(mgr.createRelation(b, err), "acme project relation with same local name created");
    check(mgr.getRelation("default:by_workflow")->child == "default:executions",
          "default resolves to its own child");
    check(mgr.getRelation("acme:by_workflow")->child == "acme:executions",
          "acme resolves to its own child");
}

void test_relations_for_parent_and_child() {
    MemoryStore store(testConfig());
    RelationManager mgr(store);
    std::string err;
    mgr.createRelation(makeRelation(), err);
    check(mgr.relationsForParent("default:workflows").size() == 1,
          "relationsForParent finds the relation");
    check(mgr.relationsForChild("default:executions").size() == 1,
          "relationsForChild finds the relation");
    check(mgr.relationsForParent("default:executions").empty(),
          "child collection is not a parent");
}

void test_drop() {
    MemoryStore store(testConfig());
    RelationManager mgr(store);
    std::string err;
    mgr.createRelation(makeRelation(), err);
    check(mgr.dropRelation("default:executions_workflow", err), "drop succeeds");
    check(!mgr.getRelation("default:executions_workflow").has_value(),
          "dropped relation is gone");
    check(!mgr.dropRelation("default:executions_workflow", err),
          "dropping twice reports failure");
}

void test_list_is_project_filtered_by_caller() {
    MemoryStore store(testConfig());
    RelationManager mgr(store);
    std::string err;
    mgr.createRelation(makeRelation("default:r1"), err);
    RelationInfo b = makeRelation("acme:r2");
    b.child = "acme:executions";
    b.parent = "acme:workflows";
    mgr.createRelation(b, err);
    check(mgr.listRelations().size() == 2, "listRelations returns all projects");
}

}  // namespace

int main() {
    test_create_and_get();
    test_default_policy_is_restrict();
    test_rejects_unknown_policy();
    test_rejects_cross_project();
    test_rejects_duplicate();
    test_same_name_in_two_projects();
    test_relations_for_parent_and_child();
    test_drop();
    test_list_is_project_filtered_by_caller();

    std::cout << (failures ? "\nFAILED: " + std::to_string(failures) + " check(s)\n"
                           : "\ntest_relation_manager: all passed\n");
    return failures ? 1 : 0;
}

Add to tests/CMakeLists.txt (before the -UNDEBUG foreach), copying the source list pattern used by test_timestamp_precision:

# Relation declaration/persistence test.
add_executable(test_relation_manager
    test_relation_manager.cpp
    ${CMAKE_CURRENT_SOURCE_DIR}/../service/src/relations/relation_manager.cpp
    ${CMAKE_CURRENT_SOURCE_DIR}/../service/src/memory_store.cpp
    ${CMAKE_CURRENT_SOURCE_DIR}/../service/src/config/collection_config_manager.cpp
    ${CMAKE_CURRENT_SOURCE_DIR}/../service/src/persistence/history_store.cpp
    ${CMAKE_CURRENT_SOURCE_DIR}/../service/src/persistence/wal.cpp
    ${CMAKE_CURRENT_SOURCE_DIR}/../service/src/json_parse.cpp
    ${CMAKE_CURRENT_SOURCE_DIR}/../service/src/doc_binary.cpp
)
target_include_directories(test_relation_manager PRIVATE
    ${CMAKE_CURRENT_SOURCE_DIR}/../service/src
)
if(TARGET nlohmann_json::nlohmann_json)
    target_link_libraries(test_relation_manager PRIVATE nlohmann_json::nlohmann_json)
else()
    target_include_directories(test_relation_manager PRIVATE ${NLOHMANN_JSON_INCLUDE_DIRS})
endif()
if(TARGET spdlog::spdlog)
    target_link_libraries(test_relation_manager PRIVATE spdlog::spdlog)
else()
    target_link_libraries(test_relation_manager PRIVATE ${SPDLOG_LIBRARIES})
    target_include_directories(test_relation_manager PRIVATE ${SPDLOG_INCLUDE_DIRS})
endif()
find_package(Threads REQUIRED)
target_link_libraries(test_relation_manager PRIVATE Threads::Threads ${yyjson_LIBRARIES})
target_include_directories(test_relation_manager PRIVATE ${yyjson_INCLUDE_DIRS})

Add test_relation_manager to the -UNDEBUG foreach list and:

add_test(NAME relation_manager COMMAND test_relation_manager)

Step 2: Run test to verify it fails

Run: cmake -B build -G Ninja && cmake --build build -j$(nproc) --target test_relation_manager Expected: FAIL to compile - relations/relation_manager.hpp does not exist.

Step 3: Write the implementation

Create service/src/relations/relation_manager.hpp:

// RelationManager - loads, caches and manages relation declarations.
//
// Relations are persisted as documents in the `_relations` system collection,
// exactly as views are in `_views`. Keys are the QUALIFIED <project>:<name>
// form from the first commit: v2.4.2 fixed a bug where views were keyed on a
// bare name while every lookup sent a qualified one, so lookups silently
// missed for two releases.
#pragma once

#include "../document.hpp"

#include <optional>
#include <shared_mutex>
#include <string>
#include <unordered_map>
#include <vector>

namespace smartbotic::database {

class MemoryStore;

// on_delete policy values.
inline constexpr const char* kOnDeleteRestrict = "restrict";
inline constexpr const char* kOnDeleteCascade  = "cascade";
inline constexpr const char* kOnDeleteSetNull  = "set_null";
inline constexpr const char* kOnDeleteNoAction = "no_action";

struct RelationInfo {
    std::string name;        // qualified <project>:<name>
    std::string child;       // qualified child collection
    std::string childField;  // dot-path; scalar or array of parent _ids
    std::string parent;      // qualified parent collection
    std::string onDelete = kOnDeleteRestrict;
    bool validateOnWrite = false;
    uint64_t createdAt = 0;
    uint64_t updatedAt = 0;
};

class RelationManager {
public:
    static constexpr const char* SYSTEM_COLLECTION = "_relations";

    explicit RelationManager(MemoryStore& store);

    // Populate the cache. Call once at startup AFTER MemoryStore has loaded
    // persisted state.
    void loadFromStore();

    // Declare a relation. Fails on: unknown policy, cross-project pair, empty
    // required field, duplicate name, or a name whose local part starts with _.
    bool createRelation(const RelationInfo& rel, std::string& errorOut);

    bool dropRelation(const std::string& qualifiedName, std::string& errorOut);

    std::optional<RelationInfo> getRelation(const std::string& qualifiedName) const;

    std::vector<RelationInfo> listRelations() const;

    // Relations where `qualifiedCollection` is the PARENT - consulted on delete.
    std::vector<RelationInfo> relationsForParent(const std::string& qualifiedCollection) const;

    // Relations where `qualifiedCollection` is the CHILD - consulted on write,
    // for index maintenance and validate_on_write.
    std::vector<RelationInfo> relationsForChild(const std::string& qualifiedCollection) const;

private:
    MemoryStore& store_;
    mutable std::shared_mutex cacheMutex_;
    std::unordered_map<std::string, RelationInfo> cache_;
};

}  // namespace smartbotic::database

Create service/src/relations/relation_manager.cpp:

#include "relations/relation_manager.hpp"

#include "memory_store.hpp"
#include "project_addressing.hpp"

#include <chrono>
#include <nlohmann/json.hpp>
#include <spdlog/spdlog.h>

namespace smartbotic::database {

namespace {

uint64_t nowMs() {
    return std::chrono::duration_cast<std::chrono::milliseconds>(
        std::chrono::system_clock::now().time_since_epoch()).count();
}

bool isKnownPolicy(const std::string& p) {
    return p == kOnDeleteRestrict || p == kOnDeleteCascade ||
           p == kOnDeleteSetNull  || p == kOnDeleteNoAction;
}

nlohmann::json toJson(const RelationInfo& r) {
    return {
        {"name", r.name},
        {"child", r.child},
        {"child_field", r.childField},
        {"parent", r.parent},
        {"on_delete", r.onDelete},
        {"validate_on_write", r.validateOnWrite},
        {"created_at", r.createdAt},
        {"updated_at", r.updatedAt},
    };
}

RelationInfo fromJson(const nlohmann::json& j) {
    RelationInfo r;
    r.name = j.value("name", "");
    r.child = j.value("child", "");
    r.childField = j.value("child_field", "");
    r.parent = j.value("parent", "");
    r.onDelete = j.value("on_delete", std::string(kOnDeleteRestrict));
    r.validateOnWrite = j.value("validate_on_write", false);
    r.createdAt = j.value("created_at", uint64_t{0});
    r.updatedAt = j.value("updated_at", uint64_t{0});
    return r;
}

}  // namespace

RelationManager::RelationManager(MemoryStore& store) : store_(store) {}

void RelationManager::loadFromStore() {
    std::unique_lock<std::shared_mutex> lock(cacheMutex_);
    cache_.clear();

    CollectionOptions opts;
    store_.createCollection(SYSTEM_COLLECTION, opts);

    Query q;
    auto result = store_.find(SYSTEM_COLLECTION, q);
    for (const auto& doc : result.documents) {
        RelationInfo r = fromJson(doc.data());
        if (r.name.empty()) continue;
        cache_[r.name] = r;
    }
    spdlog::info("RelationManager: loaded {} relations from {}",
                 cache_.size(), SYSTEM_COLLECTION);
}

bool RelationManager::createRelation(const RelationInfo& rel, std::string& errorOut) {
    RelationInfo r = rel;
    if (r.onDelete.empty()) r.onDelete = kOnDeleteRestrict;

    if (r.name.empty())       { errorOut = "relation name is required"; return false; }
    if (r.child.empty())      { errorOut = "child collection is required"; return false; }
    if (r.parent.empty())     { errorOut = "parent collection is required"; return false; }
    if (r.childField.empty()) { errorOut = "child_field is required"; return false; }

    if (!isKnownPolicy(r.onDelete)) {
        errorOut = "unknown on_delete policy '" + r.onDelete +
                   "' (expected restrict, cascade, set_null or no_action)";
        return false;
    }

    const auto nameRc   = resolveCollection(r.name);
    const auto childRc  = resolveCollection(r.child);
    const auto parentRc = resolveCollection(r.parent);

    if (nameRc.collection.front() == '_') {
        errorOut = "relation name cannot start with '_' (reserved)";
        return false;
    }

    // No LMDB transaction spans two envs, so a cascade could never be atomic
    // across projects. Reject rather than offer a guarantee we cannot keep.
    if (childRc.project != parentRc.project || nameRc.project != childRc.project) {
        errorOut = "relation must stay within one project: name=" + nameRc.project +
                   " child=" + childRc.project + " parent=" + parentRc.project;
        return false;
    }

    if (r.child == r.parent) {
        errorOut = "self-referencing relations are not supported";
        return false;
    }

    r.name = nameRc.qualified;
    r.child = childRc.qualified;
    r.parent = parentRc.qualified;

    {
        std::shared_lock<std::shared_mutex> rlock(cacheMutex_);
        if (cache_.contains(r.name)) {
            errorOut = "relation '" + r.name + "' already exists";
            return false;
        }
    }

    r.createdAt = nowMs();
    r.updatedAt = r.createdAt;

    Document doc;
    doc.id = r.name;
    doc.set_data(toJson(r));
    try {
        if (store_.insert(SYSTEM_COLLECTION, doc).empty()) {
            errorOut = "failed to persist relation definition";
            return false;
        }
    } catch (const std::exception& e) {
        errorOut = std::string("failed to persist relation definition: ") + e.what();
        return false;
    }

    {
        std::unique_lock<std::shared_mutex> wlock(cacheMutex_);
        cache_[r.name] = r;
    }
    spdlog::info("RelationManager: created relation '{}' ({} -> {} on {}, on_delete={})",
                 r.name, r.child, r.parent, r.childField, r.onDelete);
    return true;
}

bool RelationManager::dropRelation(const std::string& qualifiedName, std::string& errorOut) {
    const auto rc = resolveCollection(qualifiedName);
    {
        std::shared_lock<std::shared_mutex> rlock(cacheMutex_);
        if (!cache_.contains(rc.qualified)) {
            errorOut = "relation '" + rc.qualified + "' does not exist";
            return false;
        }
    }
    store_.remove(SYSTEM_COLLECTION, rc.qualified);
    {
        std::unique_lock<std::shared_mutex> wlock(cacheMutex_);
        cache_.erase(rc.qualified);
    }
    spdlog::info("RelationManager: dropped relation '{}'", rc.qualified);
    return true;
}

std::optional<RelationInfo>
RelationManager::getRelation(const std::string& qualifiedName) const {
    const auto rc = resolveCollection(qualifiedName);
    std::shared_lock<std::shared_mutex> lock(cacheMutex_);
    auto it = cache_.find(rc.qualified);
    if (it == cache_.end()) return std::nullopt;
    return it->second;
}

std::vector<RelationInfo> RelationManager::listRelations() const {
    std::shared_lock<std::shared_mutex> lock(cacheMutex_);
    std::vector<RelationInfo> out;
    out.reserve(cache_.size());
    for (const auto& [_, r] : cache_) out.push_back(r);
    return out;
}

std::vector<RelationInfo>
RelationManager::relationsForParent(const std::string& qualifiedCollection) const {
    const auto rc = resolveCollection(qualifiedCollection);
    std::shared_lock<std::shared_mutex> lock(cacheMutex_);
    std::vector<RelationInfo> out;
    for (const auto& [_, r] : cache_) {
        if (r.parent == rc.qualified) out.push_back(r);
    }
    return out;
}

std::vector<RelationInfo>
RelationManager::relationsForChild(const std::string& qualifiedCollection) const {
    const auto rc = resolveCollection(qualifiedCollection);
    std::shared_lock<std::shared_mutex> lock(cacheMutex_);
    std::vector<RelationInfo> out;
    for (const auto& [_, r] : cache_) {
        if (r.child == rc.qualified) out.push_back(r);
    }
    return out;
}

}  // namespace smartbotic::database

Step 4: Run test to verify it passes

Run: cmake --build build -j$(nproc) --target test_relation_manager && ./build/tests/test_relation_manager Expected: PASS, "test_relation_manager: all passed".

[ ] Step 5: Commit

git add service/src/relations/relation_manager.hpp service/src/relations/relation_manager.cpp tests/test_relation_manager.cpp tests/CMakeLists.txt
git commit -m "feat(relations): _relations system collection and manager

Relations are documents in _relations, keyed by the qualified
<project>:<name> form, mirroring how views live in _views. The qualified
key is there from the first commit because v2.4.2 fixed exactly this bug
in views - a bare-name registry with qualified lookups missed silently
for two releases.

Validation rejects unknown policies, cross-project pairs (no LMDB
transaction spans two envs, so a cascade could never be atomic across
them), self-references, and names whose local part is reserved.

on_delete defaults to restrict. A delete that quietly removes documents
from another collection is a surprise the caller should opt into."

Task 4: Wire RelationManager into DatabaseService

Files:

Modify: service/src/database_service.hpp
Modify: service/src/database_service.cpp
Modify: service/CMakeLists.txt

Interfaces:

Consumes: RelationManager from Task 3.
Produces: DatabaseService::relationManager() -> RelationManager&, and a RelationIndex accessor per project: DatabaseService::relationIndex(const std::string& project, const std::string& relationName) -> std::unique_ptr<RelationIndex>.
[ ] Step 1: Add the sources to the service build

In service/CMakeLists.txt, add to the service source list (alongside views/view_manager.cpp):

    src/relations/relation_manager.cpp
    src/relations/reference_extract.cpp
    src/storage/relation_index.cpp

Step 2: Declare the member

In service/src/database_service.hpp, add near the view_manager.hpp include:

#include "relations/relation_manager.hpp"

Add next to the viewManager() accessor:

    /**
     * Access the relation manager (for RPC handlers, migrations, etc.).
     */
    RelationManager& relationManager() { return *relation_manager_; }

Add next to the view_manager_ member declaration:

    std::unique_ptr<RelationManager> relation_manager_;

Step 3: Construct and load it

In service/src/database_service.cpp, immediately after the line that constructs the view manager (view_manager_ = std::make_unique<ViewManager>(*store_);):

    relation_manager_ = std::make_unique<RelationManager>(*store_);

Immediately after view_manager_->loadFromStore();:

    relation_manager_->loadFromStore();

Step 4: Build and run the suite

Run: cmake -B build -G Ninja && cmake --build build -j$(nproc) && cd build/tests && ctest --output-on-failure Expected: build succeeds, all tests pass, and a fresh server boot logs RelationManager: loaded 0 relations from _relations.

[ ] Step 5: Commit

git add service/CMakeLists.txt service/src/database_service.hpp service/src/database_service.cpp
git commit -m "feat(relations): wire RelationManager into DatabaseService

Constructed alongside ViewManager and loaded after persistence recovery,
so the cache is populated from _relations before any request is served."

Task 5: Index maintenance on child writes

Every insert, update and delete of a child document must keep the reverse index in step. This runs inside MemoryStore's per-collection lock next to the existing LMDB mirror calls, so the index and the document move together.

Files:

Modify: service/src/memory_store.hpp
Modify: service/src/memory_store.cpp
Modify: service/src/database_service.cpp
Test: tests/load_test/test_relations_e2e.sh (created in Task 10; this task adds no new test binary)

Interfaces:

Consumes: RelationIndex, extractReferences, RelationManager::relationsForChild.
Produces: MemoryStore::setRelationHook(std::function<void(const std::string& collection, const std::string& id, const nlohmann::json* newData, const nlohmann::json* oldData)>).
[ ] Step 1: Declare the hook

In service/src/memory_store.hpp, next to the existing document-store mirror setter, add:

    // v2.5.0 - relation index maintenance. Invoked inside the per-collection
    // write lock, right beside the LMDB document mirror, so the index and the
    // document it describes move together.
    //
    // newData is null for a delete; oldData is null for an insert. Both are
    // non-null for an update, which is what lets the hook move an index edge
    // when a child re-points from one parent to another.
    using RelationHook = std::function<void(const std::string& collection,
                                            const std::string& id,
                                            const nlohmann::json* newData,
                                            const nlohmann::json* oldData)>;
    void setRelationHook(RelationHook hook) { relation_hook_ = std::move(hook); }

And as a private member:

    RelationHook relation_hook_;

Step 2: Call the hook at each write site

In service/src/memory_store.cpp, immediately after each existing mirrorWriteToDocStore(...) call:

In insert (new document, no previous state):

    if (relation_hook_) {
        const auto newJson = doc.data();
        relation_hook_(collection, doc.id, &newJson, nullptr);
    }

In update and updateIfVersion (capture the previous document BEFORE it->second = updated;):

    if (relation_hook_) {
        const auto oldJson = previous.data();
        const auto newJson = updated.data();
        relation_hook_(collection, id, &newJson, &oldJson);
    }

where previous is a copy of it->second taken before the assignment:

    Document previous = it->second;   // for the relation hook; taken under the lock

In remove (after mirrorWriteToDocStore(collection, id, std::nullopt, EventType::DELETE);):

    if (relation_hook_) {
        const auto oldJson = removedDoc.data();
        relation_hook_(collection, id, nullptr, &oldJson);
    }

where removedDoc is a copy taken before coll->documents.erase(it);:

    Document removedDoc = it->second;  // for the relation hook; taken under the lock

Step 3: Install the hook

In service/src/database_service.cpp, after relation_manager_->loadFromStore();:

    // v2.5.0 - keep every relation's reverse index in step with child writes.
    // Runs inside MemoryStore's per-collection lock, beside the LMDB mirror.
    store_->setRelationHook(
        [this](const std::string& collection, const std::string& id,
               const nlohmann::json* newData, const nlohmann::json* oldData) {
            const auto rels = relation_manager_->relationsForChild(collection);
            if (rels.empty()) return;
            const auto rc = smartbotic::database::resolveCollection(collection);
            for (const auto& rel : rels) {
                const auto relRc = smartbotic::database::resolveCollection(rel.name);
                smartbotic::db::storage::RelationIndex idx(
                    *lmdbEnvForProject(rc.project), relRc.collection);
                try {
                    if (oldData) {
                        for (const auto& p : extractReferences(*oldData, rel.childField)) {
                            idx.remove(p, id);
                        }
                    }
                    if (newData) {
                        for (const auto& p : extractReferences(*newData, rel.childField)) {
                            idx.add(p, id);
                        }
                    }
                } catch (const std::exception& e) {
                    spdlog::error("relation index update failed rel={} child={}/{}: {}",
                                  rel.name, collection, id, e.what());
                }
            }
        });

Add a private helper on DatabaseService returning the project's LmdbEnv*, mirroring how docStore(project) already resolves a store:

smartbotic::db::storage::LmdbEnv* DatabaseService::lmdbEnvForProject(const std::string& project) {
    return project_stores_ ? project_stores_->envFor(project) : nullptr;
}

declared in database_service.hpp as:

    smartbotic::db::storage::LmdbEnv* lmdbEnvForProject(const std::string& project);

If ProjectStoreRegistry has no envFor, add it next to its existing store accessor, returning the LmdbEnv& it already owns per project.

Step 4: Build and run the suite

Run: cmake --build build -j$(nproc) && cd build/tests && ctest --output-on-failure Expected: build succeeds, 16/16 tests pass. No behavior change yet - no relations exist, so the hook returns immediately.

[ ] Step 5: Commit

git add service/src/memory_store.hpp service/src/memory_store.cpp service/src/database_service.hpp service/src/database_service.cpp
git commit -m "feat(relations): maintain the reverse index on child writes

The hook fires inside MemoryStore's per-collection write lock, next to
the existing LMDB document mirror, so an index edge and the document it
describes move together.

It receives both the old and the new document body, which is what lets a
child re-pointing from parent A to parent B move its edge rather than
leaving a stale one behind.

A collection with no relations returns immediately, so the cost on
unrelated writes is one cache lookup."

Task 6: restrict and no_action on parent delete

Files:

Create: service/src/relations/relation_engine.hpp
Create: service/src/relations/relation_engine.cpp
Modify: service/src/database_grpc_impl.cpp
Modify: service/CMakeLists.txt

Interfaces:

Consumes: RelationManager, RelationIndex, LmdbEnv.
Produces: struct RelationImpact { std::string relation; std::string policy; uint64_t childCount; std::vector<std::string> sampleChildIds; } and RelationEngine::describeDelete(collection, id) -> std::vector<RelationImpact>, RelationEngine::checkRestrict(collection, id, std::string& errorOut) -> bool.
[ ] Step 1: Write the failing test

Create tests/test_relation_engine.cpp. This drives the real engine against a real index, so it genuinely fails before the engine exists:

// RelationEngine against a real LmdbEnv and a real reverse index.
//
// restrict must refuse while children exist, and the refusal must name the
// relation, the count and a sample of ids - an operator should learn what to
// do next from the message rather than from the source.
#include "memory_store.hpp"
#include "relations/relation_engine.hpp"
#include "relations/relation_manager.hpp"
#include "storage/lmdb_env.hpp"
#include "storage/relation_index.hpp"

#include <filesystem>
#include <iostream>
#include <string>
#include <unistd.h>

namespace fs = std::filesystem;

using smartbotic::database::MemoryStore;
using smartbotic::database::RelationEngine;
using smartbotic::database::RelationInfo;
using smartbotic::database::RelationManager;
using smartbotic::db::storage::LmdbEnv;
using smartbotic::db::storage::LmdbEnvOpts;
using smartbotic::db::storage::RelationIndex;

namespace {

int failures = 0;

void check(bool cond, const std::string& msg) {
    std::cout << (cond ? "  PASS  " : "  FAIL  ") << msg << "\n";
    if (!cond) ++failures;
}

std::string tmpdir(const char* tag) {
    static int n = 0;
    std::string p = "/tmp/sbdb-relengine-" + std::to_string(::getpid()) + "-" +
                    std::to_string(n++) + "-" + tag;
    std::error_code ec;
    fs::remove_all(p, ec);
    return p;
}

MemoryStore::Config testConfig() {
    MemoryStore::Config cfg;
    cfg.nodeId = "test";
    cfg.maxMemoryBytes = 64ULL * 1024 * 1024;
    return cfg;
}

// The engine resolves a project name to an env through a callback so it never
// opens one itself - see the Global Constraint on second MDB_env instances.
LmdbEnv* envResolver(void* ctx, const std::string&) {
    return static_cast<LmdbEnv*>(ctx);
}

RelationInfo restrictRelation() {
    RelationInfo r;
    r.name = "default:exec_wf";
    r.child = "default:executions";
    r.childField = "workflow_id";
    r.parent = "default:workflows";
    r.onDelete = "restrict";
    return r;
}

void test_restrict_allows_when_no_children() {
    const std::string path = tmpdir("no-children");
    LmdbEnv env(LmdbEnvOpts{path, 64ULL << 20, 256, 126, false});
    MemoryStore store(testConfig());
    RelationManager mgr(store);
    std::string err;
    mgr.createRelation(restrictRelation(), err);

    RelationEngine engine(mgr, &envResolver, &env);
    std::string relErr;
    check(engine.checkRestrict("default:workflows", "wf-1", relErr),
          "restrict allows the delete when no children reference the parent");
    check(relErr.empty(), "no error message when allowed");

    std::error_code ec;
    fs::remove_all(path, ec);
}

void test_restrict_blocks_and_explains() {
    const std::string path = tmpdir("blocks");
    LmdbEnv env(LmdbEnvOpts{path, 64ULL << 20, 256, 126, false});
    MemoryStore store(testConfig());
    RelationManager mgr(store);
    std::string err;
    mgr.createRelation(restrictRelation(), err);

    RelationIndex idx(env, "exec_wf");
    idx.add("wf-1", "ex-1");
    idx.add("wf-1", "ex-2");

    RelationEngine engine(mgr, &envResolver, &env);
    std::string relErr;
    check(!engine.checkRestrict("default:workflows", "wf-1", relErr),
          "restrict blocks the delete while children exist");
    check(relErr.find("default:exec_wf") != std::string::npos,
          "the message names the relation");
    check(relErr.find("2") != std::string::npos,
          "the message reports the child count");
    check(relErr.find("ex-1") != std::string::npos,
          "the message samples a blocking child id");

    std::error_code ec;
    fs::remove_all(path, ec);
}

void test_describe_delete_reports_impact() {
    const std::string path = tmpdir("describe");
    LmdbEnv env(LmdbEnvOpts{path, 64ULL << 20, 256, 126, false});
    MemoryStore store(testConfig());
    RelationManager mgr(store);
    std::string err;
    mgr.createRelation(restrictRelation(), err);

    RelationIndex idx(env, "exec_wf");
    idx.add("wf-1", "ex-1");

    RelationEngine engine(mgr, &envResolver, &env);
    auto impacts = engine.describeDelete("default:workflows", "wf-1");
    check(impacts.size() == 1, "one relation applies");
    check(impacts[0].relation == "default:exec_wf", "impact names the relation");
    check(impacts[0].policy == "restrict", "impact reports the policy");
    check(impacts[0].childCount == 1, "impact counts the children");
    check(impacts[0].blocks, "impact says it blocks");
    check(impacts[0].sampleChildIds.size() == 1, "impact samples the child id");

    std::error_code ec;
    fs::remove_all(path, ec);
}

void test_no_action_never_blocks() {
    const std::string path = tmpdir("no-action");
    LmdbEnv env(LmdbEnvOpts{path, 64ULL << 20, 256, 126, false});
    MemoryStore store(testConfig());
    RelationManager mgr(store);
    RelationInfo r = restrictRelation();
    r.onDelete = "no_action";
    std::string err;
    mgr.createRelation(r, err);

    RelationIndex idx(env, "exec_wf");
    idx.add("wf-1", "ex-1");

    RelationEngine engine(mgr, &envResolver, &env);
    std::string relErr;
    check(engine.checkRestrict("default:workflows", "wf-1", relErr),
          "no_action never blocks even with children present");

    std::error_code ec;
    fs::remove_all(path, ec);
}

}  // namespace

int main() {
    test_restrict_allows_when_no_children();
    test_restrict_blocks_and_explains();
    test_describe_delete_reports_impact();
    test_no_action_never_blocks();

    std::cout << (failures ? "\nFAILED: " + std::to_string(failures) + " check(s)\n"
                           : "\ntest_relation_engine: all passed\n");
    return failures ? 1 : 0;
}

Add a test_relation_engine target to tests/CMakeLists.txt with the same source list as test_relation_manager plus ../service/src/relations/relation_engine.cpp, ../service/src/relations/reference_extract.cpp, ../service/src/storage/relation_index.cpp, ../service/src/storage/lmdb_env.cpp, ../service/src/storage/lmdb_txn.cpp, ../service/src/storage/subdb_identity.cpp, linking ${LMDB_LIBRARY} and including ${LMDB_INCLUDE_DIR}. Add it to the -UNDEBUG foreach and register add_test(NAME relation_engine COMMAND test_relation_engine).

Step 2: Run test to verify it fails

Run: cmake -B build -G Ninja && cmake --build build -j$(nproc) --target test_relation_engine Expected: FAIL to compile - relations/relation_engine.hpp does not exist.

Step 3: Write the engine

Create service/src/relations/relation_engine.hpp:

// RelationEngine - resolves what a delete means under the declared relations.
//
// restrict and no_action need no cross-collection atomicity: they are a read
// of the reverse index followed by a refusal or a shrug. cascade and set_null
// (Task 8) need one LMDB WriteTxn spanning parent, children and index sub-dbs.
#pragma once

#include <cstdint>
#include <string>
#include <vector>

namespace smartbotic::db::storage {
class LmdbEnv;
}

namespace smartbotic::database {

class RelationManager;

// What one relation would do to one delete.
struct RelationImpact {
    std::string relation;               // qualified relation name
    std::string childCollection;        // qualified child collection
    std::string policy;                 // on_delete value
    uint64_t childCount = 0;            // matching children
    std::vector<std::string> sampleChildIds;  // at most kSampleLimit
    bool blocks = false;                // true when policy is restrict and childCount > 0
};

class RelationEngine {
public:
    static constexpr size_t kSampleLimit = 5;

    RelationEngine(RelationManager& relations,
                   smartbotic::db::storage::LmdbEnv* (*envForProject)(void*, const std::string&),
                   void* envCtx);

    // What would deleting this document do? Never mutates anything.
    std::vector<RelationImpact> describeDelete(const std::string& qualifiedCollection,
                                               const std::string& id);

    // True when the delete may proceed. On false, errorOut names the relation,
    // the child count and a sample of ids.
    bool checkRestrict(const std::string& qualifiedCollection,
                       const std::string& id,
                       std::string& errorOut);

private:
    RelationManager& relations_;
    smartbotic::db::storage::LmdbEnv* (*envForProject_)(void*, const std::string&);
    void* envCtx_;
};

}  // namespace smartbotic::database

Create service/src/relations/relation_engine.cpp:

#include "relations/relation_engine.hpp"

#include "project_addressing.hpp"
#include "relations/relation_manager.hpp"
#include "storage/relation_index.hpp"

#include <spdlog/spdlog.h>

namespace smartbotic::database {

RelationEngine::RelationEngine(
    RelationManager& relations,
    smartbotic::db::storage::LmdbEnv* (*envForProject)(void*, const std::string&),
    void* envCtx)
    : relations_(relations), envForProject_(envForProject), envCtx_(envCtx) {}

std::vector<RelationImpact>
RelationEngine::describeDelete(const std::string& qualifiedCollection,
                               const std::string& id) {
    std::vector<RelationImpact> out;
    const auto rels = relations_.relationsForParent(qualifiedCollection);
    if (rels.empty()) return out;

    const auto rc = resolveCollection(qualifiedCollection);
    auto* env = envForProject_(envCtx_, rc.project);
    if (env == nullptr) return out;

    for (const auto& rel : rels) {
        const auto relRc = resolveCollection(rel.name);
        smartbotic::db::storage::RelationIndex idx(*env, relRc.collection);

        RelationImpact impact;
        impact.relation = rel.name;
        impact.childCollection = rel.child;
        impact.policy = rel.onDelete;

        auto kids = idx.children_of(id);
        impact.childCount = kids.size();
        for (size_t i = 0; i < kids.size() && i < kSampleLimit; ++i) {
            impact.sampleChildIds.push_back(kids[i]);
        }
        impact.blocks = (rel.onDelete == kOnDeleteRestrict) && impact.childCount > 0;
        out.push_back(std::move(impact));
    }
    return out;
}

bool RelationEngine::checkRestrict(const std::string& qualifiedCollection,
                                   const std::string& id,
                                   std::string& errorOut) {
    for (const auto& impact : describeDelete(qualifiedCollection, id)) {
        if (!impact.blocks) continue;
        std::string sample;
        for (size_t i = 0; i < impact.sampleChildIds.size(); ++i) {
            if (i) sample += ", ";
            sample += impact.sampleChildIds[i];
        }
        errorOut = "relation '" + impact.relation + "' (on_delete=restrict) blocks this delete: " +
                   std::to_string(impact.childCount) + " document(s) in '" +
                   impact.childCollection + "' still reference it";
        if (!sample.empty()) {
            errorOut += " (e.g. " + sample;
            if (impact.childCount > impact.sampleChildIds.size()) errorOut += ", ...";
            errorOut += ")";
        }
        errorOut += ". Re-point or remove them first, or declare on_delete=cascade.";
        return false;
    }
    return true;
}

}  // namespace smartbotic::database

Add src/relations/relation_engine.cpp to the service source list in service/CMakeLists.txt.

Step 4: Enforce it in the Delete handler

In service/src/database_grpc_impl.cpp, inside the Delete handler, before any mutation:

    // v2.5.0 - relation policy. restrict is a read of the reverse index and a
    // refusal, so it needs no cross-collection atomicity.
    {
        std::string relErr;
        if (!service_.relationEngine().checkRestrict(targetCollection, request->id(), relErr)) {
            return grpc::Status(grpc::StatusCode::FAILED_PRECONDITION, relErr);
        }
    }

Construct the engine in DatabaseService::setupComponents() after the relation manager, and expose RelationEngine& relationEngine().

Step 5: Build, test and commit

Run: cmake --build build -j$(nproc) && cd build/tests && ctest --output-on-failure Expected: all tests pass.

git add service/src/relations/relation_engine.hpp service/src/relations/relation_engine.cpp service/src/database_grpc_impl.cpp service/src/database_service.hpp service/src/database_service.cpp service/CMakeLists.txt tests/test_relation_manager.cpp
git commit -m "feat(relations): enforce restrict on delete

restrict needs no cross-collection atomicity: it reads the reverse index
and refuses. Blocked deletes return FAILED_PRECONDITION naming the
relation, the child count and a sample of ids, so an operator learns what
to do next from the message rather than from the source.

Also adds describeDelete, which reports the same impact without mutating
anything - the basis for the DescribeDelete RPC."

Task 7: Proto and RPC surface

Files:

Modify: proto/database.proto
Modify: service/src/database_grpc_impl.hpp
Modify: service/src/database_grpc_impl.cpp

Interfaces:

Produces: RPCs CreateRelation, DropRelation, ListRelations, GetRelationInfo, DescribeDelete.
[ ] Step 1: Add the messages

In proto/database.proto, add to the service block next to the view RPCs:

    // Relations (v2.5.0)
    rpc CreateRelation(CreateRelationRequest) returns (CreateRelationResponse);
    rpc DropRelation(DropRelationRequest) returns (DropRelationResponse);
    rpc ListRelations(ListRelationsRequest) returns (ListRelationsResponse);
    rpc GetRelationInfo(GetRelationInfoRequest) returns (GetRelationInfoResponse);
    rpc DescribeDelete(DescribeDeleteRequest) returns (DescribeDeleteResponse);

And the messages, after the view messages:

message RelationDefinition {
    string name = 1;               // qualified <project>:<name>
    string child = 2;              // qualified child collection
    string child_field = 3;        // dot-path; scalar or array of parent _ids
    string parent = 4;             // qualified parent collection
    string on_delete = 5;          // restrict | cascade | set_null | no_action
    bool validate_on_write = 6;
    uint64 created_at = 7;
    uint64 updated_at = 8;
}

message CreateRelationRequest {
    string name = 1;
    string child = 2;
    string child_field = 3;
    string parent = 4;
    string on_delete = 5;
    bool validate_on_write = 6;
}

message CreateRelationResponse {
    bool success = 1;
    string error = 2;
}

message DropRelationRequest { string name = 1; }
message DropRelationResponse {
    bool success = 1;
    string error = 2;
}

message ListRelationsRequest {
    // Restrict to one project. Empty lists every project (operator/CLI use).
    // Clients always set it so a workspace never enumerates another's.
    string project = 1;
}
message ListRelationsResponse { repeated RelationDefinition relations = 1; }

message GetRelationInfoRequest { string name = 1; }
message GetRelationInfoResponse {
    bool found = 1;
    RelationDefinition relation = 2;
}

// What one relation would do to one delete.
message RelationImpact {
    string relation = 1;
    string child_collection = 2;
    string policy = 3;
    uint64 child_count = 4;
    repeated string sample_child_ids = 5;
    bool blocks = 6;
}

message DescribeDeleteRequest {
    string collection = 1;
    string id = 2;
}

message DescribeDeleteResponse {
    repeated RelationImpact impacts = 1;
    bool would_succeed = 2;     // false if any impact blocks
    string blocked_reason = 3;  // populated when would_succeed is false
}

Step 2: Declare the handlers

In service/src/database_grpc_impl.hpp, next to the view RPC declarations:

    grpc::Status CreateRelation(
        grpc::ServerContext* context,
        const pb::CreateRelationRequest* request,
        pb::CreateRelationResponse* response) override;

    grpc::Status DropRelation(
        grpc::ServerContext* context,
        const pb::DropRelationRequest* request,
        pb::DropRelationResponse* response) override;

    grpc::Status ListRelations(
        grpc::ServerContext* context,
        const pb::ListRelationsRequest* request,
        pb::ListRelationsResponse* response) override;

    grpc::Status GetRelationInfo(
        grpc::ServerContext* context,
        const pb::GetRelationInfoRequest* request,
        pb::GetRelationInfoResponse* response) override;

    grpc::Status DescribeDelete(
        grpc::ServerContext* context,
        const pb::DescribeDeleteRequest* request,
        pb::DescribeDeleteResponse* response) override;

Step 3: Implement the handlers

In service/src/database_grpc_impl.cpp:

namespace {

void fillRelationProto(const smartbotic::database::RelationInfo& r,
                       pb::RelationDefinition* out) {
    out->set_name(r.name);
    out->set_child(r.child);
    out->set_child_field(r.childField);
    out->set_parent(r.parent);
    out->set_on_delete(r.onDelete);
    out->set_validate_on_write(r.validateOnWrite);
    out->set_created_at(r.createdAt);
    out->set_updated_at(r.updatedAt);
}

}  // namespace

grpc::Status DatabaseGrpcImpl::CreateRelation(
    grpc::ServerContext* /*context*/,
    const pb::CreateRelationRequest* request,
    pb::CreateRelationResponse* response
) {
    if (service_.isReadOnly()) {
        response->set_success(false);
        response->set_error("database is in read-only mode: " + service_.readOnlyReason());
        return grpc::Status::OK;
    }
    smartbotic::database::RelationInfo r;
    r.name = request->name();
    r.child = request->child();
    r.childField = request->child_field();
    r.parent = request->parent();
    r.onDelete = request->on_delete();
    r.validateOnWrite = request->validate_on_write();

    std::string err;
    const bool ok = service_.relationManager().createRelation(r, err);
    response->set_success(ok);
    if (!ok) response->set_error(err);
    return grpc::Status::OK;
}

grpc::Status DatabaseGrpcImpl::DropRelation(
    grpc::ServerContext* /*context*/,
    const pb::DropRelationRequest* request,
    pb::DropRelationResponse* response
) {
    if (service_.isReadOnly()) {
        response->set_success(false);
        response->set_error("database is in read-only mode: " + service_.readOnlyReason());
        return grpc::Status::OK;
    }
    std::string err;
    const bool ok = service_.relationManager().dropRelation(request->name(), err);
    response->set_success(ok);
    if (!ok) response->set_error(err);
    return grpc::Status::OK;
}

grpc::Status DatabaseGrpcImpl::ListRelations(
    grpc::ServerContext* /*context*/,
    const pb::ListRelationsRequest* request,
    pb::ListRelationsResponse* response
) {
    const std::string& wantProject = request->project();
    for (const auto& r : service_.relationManager().listRelations()) {
        if (!wantProject.empty() &&
            smartbotic::database::resolveCollection(r.name).project != wantProject) {
            continue;
        }
        fillRelationProto(r, response->add_relations());
    }
    return grpc::Status::OK;
}

grpc::Status DatabaseGrpcImpl::GetRelationInfo(
    grpc::ServerContext* /*context*/,
    const pb::GetRelationInfoRequest* request,
    pb::GetRelationInfoResponse* response
) {
    auto r = service_.relationManager().getRelation(request->name());
    response->set_found(r.has_value());
    if (r) fillRelationProto(*r, response->mutable_relation());
    return grpc::Status::OK;
}

grpc::Status DatabaseGrpcImpl::DescribeDelete(
    grpc::ServerContext* /*context*/,
    const pb::DescribeDeleteRequest* request,
    pb::DescribeDeleteResponse* response
) {
    const auto impacts =
        service_.relationEngine().describeDelete(request->collection(), request->id());
    bool wouldSucceed = true;
    for (const auto& i : impacts) {
        auto* out = response->add_impacts();
        out->set_relation(i.relation);
        out->set_child_collection(i.childCollection);
        out->set_policy(i.policy);
        out->set_child_count(i.childCount);
        out->set_blocks(i.blocks);
        for (const auto& s : i.sampleChildIds) out->add_sample_child_ids(s);
        if (i.blocks) wouldSucceed = false;
    }
    response->set_would_succeed(wouldSucceed);
    if (!wouldSucceed) {
        std::string reason;
        service_.relationEngine().checkRestrict(request->collection(), request->id(), reason);
        response->set_blocked_reason(reason);
    }
    return grpc::Status::OK;
}

Step 4: Build and run the suite

Run: cmake -B build -G Ninja && cmake --build build -j$(nproc) && cd build/tests && ctest --output-on-failure Expected: build succeeds, all tests pass.

[ ] Step 5: Commit

git add proto/database.proto service/src/database_grpc_impl.hpp service/src/database_grpc_impl.cpp
git commit -m "feat(relations): CreateRelation/DropRelation/ListRelations/GetRelationInfo/DescribeDelete

ListRelations takes a project filter so a workspace never enumerates
another's relations; empty means all projects for operator and CLI use.

DescribeDelete reports which relations apply, how many children each
matches, which policy fires and whether the delete would be blocked,
without mutating anything. A relational schema exposes its constraints
through DDL; a document store has none to read, so discoverability has to
be a query."

Task 8: Txn-accepting store overloads, WAL sequencing, cascade and set_null

This is the Phase B core. It touches the same write path that produced the v2.4.3 and v2.4.4 incidents, so it lands as one reviewable task with the atomicity test attached.

Files:

Modify: service/src/storage/document_store_lmdb.hpp
Modify: service/src/storage/document_store_lmdb.cpp
Modify: service/src/relations/relation_engine.hpp
Modify: service/src/relations/relation_engine.cpp
Modify: service/src/database_grpc_impl.cpp
Test: tests/test_relation_index.cpp (extend)

Interfaces:

Consumes: WriteTxn, RelationIndex::add/remove(WriteTxn&, ...), removeReference.
Produces: LmdbDocumentStore::put(WriteTxn&, collection, id, doc), LmdbDocumentStore::del(WriteTxn&, collection, id), and RelationEngine::applyCascade(collection, id, std::string& errorOut) -> bool.
[ ] Step 1: Write the failing test

Add to tests/test_document_store.cpp, before main. This spans a DOCUMENT sub-db and an INDEX sub-db in one transaction, which is the property cascade depends on and which cannot compile until the txn-accepting store overloads exist:

// v2.5.0 - one WriteTxn spanning a document sub-db and a relation index
// sub-db. Either both changes land or neither does. Cascade is built on this:
// a child document and the index edge describing it must never disagree.
void test_document_and_index_move_in_one_transaction() {
    TmpEnv t("txn-span");
    LmdbDocumentStore store(t.env);
    smartbotic::db::storage::RelationIndex idx(t.env, "r1");

    store.put("executions", "ex-1", make_doc("ex-1", "executions",
                                             nlohmann::json{{"workflow_id", "wf-1"}}));
    idx.add("wf-1", "ex-1");
    check(store.get("executions", "ex-1").has_value(), "seed document present");
    check(idx.count_children("wf-1") == 1, "seed index edge present");

    // Aborted: neither the document nor the edge may change.
    {
        smartbotic::db::storage::WriteTxn txn(t.env);
        store.del(txn, "executions", "ex-1");
        idx.remove(txn, "wf-1", "ex-1");
        // Dropped without commit - WriteTxn's destructor aborts.
    }
    check(store.get("executions", "ex-1").has_value(),
          "aborted txn leaves the document in place");
    check(idx.count_children("wf-1") == 1,
          "aborted txn leaves the index edge in place");

    // Committed: both must land together.
    {
        smartbotic::db::storage::WriteTxn txn(t.env);
        store.del(txn, "executions", "ex-1");
        idx.remove(txn, "wf-1", "ex-1");
        txn.commit();
    }
    check(!store.get("executions", "ex-1").has_value(),
          "committed txn removed the document");
    check(idx.count_children("wf-1") == 0,
          "committed txn removed the index edge");
}

Call it from main, and add these includes at the top of the file:

#include "storage/lmdb_txn.hpp"
#include "storage/relation_index.hpp"

Add ../service/src/storage/relation_index.cpp and ../service/src/storage/subdb_identity.cpp to test_document_store's source list in tests/CMakeLists.txt.

Step 2: Run test to verify it fails

Run: cmake -B build -G Ninja && cmake --build build -j$(nproc) --target test_document_store Expected: FAIL to compile - LmdbDocumentStore has no del(WriteTxn&, ...) overload.

Step 3: Add txn-accepting store overloads

In service/src/storage/document_store_lmdb.hpp, add to the public section:

    // v2.5.0 - operate inside a caller-supplied transaction so a cascade can
    // span the parent sub-db, every affected child sub-db and the index
    // sub-dbs as ONE atomic unit. The convenience forms above are unchanged
    // and simply wrap these.
    void put(WriteTxn& txn,
             std::string_view collection,
             std::string_view id,
             const smartbotic::database::Document& doc);

    bool del(WriteTxn& txn, std::string_view collection, std::string_view id);

In service/src/storage/document_store_lmdb.cpp, refactor the existing bodies so the convenience forms delegate:

void LmdbDocumentStore::put(std::string_view collection,
                             std::string_view id,
                             const smartbotic::database::Document& doc) {
    WriteTxn wtxn(env_);
    put(wtxn, collection, id, doc);
    wtxn.commit();
}

void LmdbDocumentStore::put(WriteTxn& txn,
                             std::string_view collection,
                             std::string_view id,
                             const smartbotic::database::Document& doc) {
    std::string payload = encode_document(doc);
    unsigned int dbi = open_for_write(txn, collection);
    MDB_val k = to_val(id);
    MDB_val v = to_val(payload);
    mdb_check(mdb_put(txn.raw(), dbi, &k, &v, 0), "put");
}

Apply the same split to del.

Step 4: Implement cascade

Add to service/src/relations/relation_engine.hpp:

    // Apply cascade and set_null for every relation whose parent is this
    // document. Returns false and populates errorOut on failure; the caller
    // must not proceed with the parent delete.
    //
    // WAL entries for every child mutation are written and fsynced BEFORE the
    // LMDB transaction commits. MemoryStore is rebuilt at boot from snapshot +
    // WAL replay, not from LMDB, so an LMDB-only cascade would have its child
    // deletions resurrected on the next restart.
    bool applyCascade(const std::string& qualifiedCollection,
                      const std::string& id,
                      std::string& errorOut);

And in relation_engine.cpp:

bool RelationEngine::applyCascade(const std::string& qualifiedCollection,
                                  const std::string& id,
                                  std::string& errorOut) {
    const auto rels = relations_.relationsForParent(qualifiedCollection);
    if (rels.empty()) return true;

    const auto rc = resolveCollection(qualifiedCollection);
    auto* env = envForProject_(envCtx_, rc.project);
    if (env == nullptr) {
        errorOut = "no storage environment for project '" + rc.project + "'";
        return false;
    }

    // Phase 1 - collect the work. Read-only, so a restrict failure costs
    // nothing and no transaction is held while we decide.
    struct Mutation {
        std::string relationLocal;
        std::string childCollectionLocal;
        std::string childId;
        bool deleteDocument = false;      // cascade on a scalar reference
        nlohmann::json rewritten;         // set_null, or an array pull
    };
    std::vector<Mutation> work;

    for (const auto& rel : rels) {
        if (rel.onDelete == kOnDeleteNoAction || rel.onDelete == kOnDeleteRestrict) continue;

        const auto relRc   = resolveCollection(rel.name);
        const auto childRc = resolveCollection(rel.child);
        smartbotic::db::storage::RelationIndex idx(*env, relRc.collection);

        for (const auto& childId : idx.children_of(id)) {
            auto doc = store_->get(childRc.collection, childId);
            if (!doc) continue;

            nlohmann::json body = doc->data();
            const auto refs = extractReferences(body, rel.childField);
            const bool isArray = body.contains(rel.childField) &&
                                 body[rel.childField].is_array();

            Mutation m;
            m.relationLocal = relRc.collection;
            m.childCollectionLocal = childRc.collection;
            m.childId = childId;

            if (rel.onDelete == kOnDeleteCascade && !isArray) {
                m.deleteDocument = true;
            } else {
                // set_null on a scalar, or a pull on an array. For arrays,
                // cascade and set_null are the same operation and the child
                // document survives.
                removeReference(body, rel.childField, id);
                m.rewritten = std::move(body);
            }
            work.push_back(std::move(m));
        }
    }
    if (work.empty()) return true;

    // Phase 2 - WAL first. See the header comment: MemoryStore recovers from
    // snapshot + WAL, so LMDB alone is not durable for its view.
    for (const auto& m : work) {
        if (m.deleteDocument) {
            wal_->appendDelete(m.childCollectionLocal, m.childId);
        } else {
            wal_->appendPut(m.childCollectionLocal, m.childId, m.rewritten);
        }
    }
    wal_->sync();

    // Phase 3 - one transaction for every child mutation and every index edge.
    try {
        smartbotic::db::storage::WriteTxn txn(*env);
        for (const auto& m : work) {
            smartbotic::db::storage::RelationIndex idx(*env, m.relationLocal);
            if (m.deleteDocument) {
                store_->del(txn, m.childCollectionLocal, m.childId);
            } else {
                auto doc = store_->get(m.childCollectionLocal, m.childId);
                if (doc) {
                    doc->set_data(m.rewritten);
                    store_->put(txn, m.childCollectionLocal, m.childId, *doc);
                }
            }
            idx.remove(txn, id, m.childId);
        }
        txn.commit();
    } catch (const std::exception& e) {
        errorOut = std::string("cascade failed: ") + e.what();
        spdlog::error("relation cascade failed parent={}/{}: {}",
                      qualifiedCollection, id, e.what());
        return false;
    }

    // Phase 4 - the cache follows. LMDB is durable already; MemoryStore is a
    // cache, and WAL replay would repair it if this were interrupted.
    for (const auto& m : work) {
        if (m.deleteDocument) {
            memory_->remove(m.childCollectionLocal, m.childId);
        } else {
            memory_->loadDocumentBody(m.childCollectionLocal, m.childId, m.rewritten);
        }
    }
    spdlog::info("relation cascade: {} child mutation(s) from {}/{}",
                 work.size(), qualifiedCollection, id);
    return true;
}

RelationEngine gains DocumentStore* store_, WriteAheadLog* wal_ and MemoryStore* memory_ constructor parameters. MemoryStore::loadDocumentBody(collection, id, json) replaces a cached document body without re-firing the persist callback - add it next to the existing loadDocument.

Step 5: Call it from the Delete handler

In service/src/database_grpc_impl.cpp, in Delete, after the checkRestrict block and before the parent is removed:

    {
        std::string relErr;
        if (!service_.relationEngine().applyCascade(targetCollection, request->id(), relErr)) {
            return grpc::Status(grpc::StatusCode::INTERNAL, relErr);
        }
    }

Step 6: Build, test and commit

Run: cmake --build build -j$(nproc) && cd build/tests && ctest --output-on-failure Expected: all tests pass.

git add service/src/storage/document_store_lmdb.hpp service/src/storage/document_store_lmdb.cpp service/src/relations/relation_engine.hpp service/src/relations/relation_engine.cpp service/src/database_grpc_impl.cpp service/src/memory_store.hpp service/src/memory_store.cpp tests/test_relation_index.cpp
git commit -m "feat(relations): atomic cascade and set_null

LmdbDocumentStore gains txn-accepting put/del overloads so one WriteTxn
can span the parent sub-db, every affected child sub-db and the index
sub-dbs. The convenience forms delegate to them, so existing callers are
unchanged.

WAL entries for every child mutation are written and fsynced BEFORE the
transaction commits. MemoryStore is rebuilt at boot from snapshot + WAL
replay, not from LMDB, so a cascade that wrote only LMDB would have its
child deletions resurrected on the next restart.

For array-valued references cascade and set_null are both a pull and the
child document survives. Deleting a node because one of its three
credentials went away would be worse than useless."

Task 9: validate_on_write

Files:

Modify: service/src/database_grpc_impl.cpp
Modify: service/src/relations/relation_engine.hpp
Modify: service/src/relations/relation_engine.cpp

Interfaces:

Produces: RelationEngine::validateChildWrite(qualifiedCollection, const nlohmann::json& body, std::string& errorOut) -> bool.
[ ] Step 1: Implement the check

In relation_engine.hpp:

    // Reject a child write whose parent does not exist, for relations with
    // validate_on_write set. This is also the remedy for the restrict race:
    // a child insert can otherwise land just after a parent delete commits,
    // because LMDB's single writer only orders the two transactions, it does
    // not make the loser re-check its premise.
    bool validateChildWrite(const std::string& qualifiedCollection,
                            const nlohmann::json& body,
                            std::string& errorOut);

In relation_engine.cpp:

bool RelationEngine::validateChildWrite(const std::string& qualifiedCollection,
                                        const nlohmann::json& body,
                                        std::string& errorOut) {
    const auto rels = relations_.relationsForChild(qualifiedCollection);
    if (rels.empty()) return true;

    for (const auto& rel : rels) {
        if (!rel.validateOnWrite) continue;
        const auto parentRc = resolveCollection(rel.parent);
        for (const auto& parentId : extractReferences(body, rel.childField)) {
            if (!store_->exists(parentRc.collection, parentId)) {
                errorOut = "relation '" + rel.name + "' requires '" + rel.parent +
                           "/" + parentId + "' to exist";
                return false;
            }
        }
    }
    return true;
}

Step 2: Call it from the write handlers

In Insert, Update, Patch and Upsert in service/src/database_grpc_impl.cpp, after the document body is parsed and before it is stored:

    {
        std::string relErr;
        if (!service_.relationEngine().validateChildWrite(targetCollection, docJson, relErr)) {
            return grpc::Status(grpc::StatusCode::FAILED_PRECONDITION, relErr);
        }
    }

Step 3: Build, test and commit

Run: cmake --build build -j$(nproc) && cd build/tests && ctest --output-on-failure Expected: all tests pass. validate_on_write defaults to false, so no existing write changes cost.

git add service/src/relations/relation_engine.hpp service/src/relations/relation_engine.cpp service/src/database_grpc_impl.cpp
git commit -m "feat(relations): validate_on_write

Per-relation opt-in, default off, so no existing write pays for a check
it did not ask for and bulk loads that insert children before parents
keep working.

This is also the documented remedy for the restrict race: a child insert
can land just after a parent delete commits, because LMDB's single writer
orders the two transactions without making the loser re-check its
premise. With validation on, the child's own transaction re-checks."

Task 10: Client library

Files:

Modify: client/include/smartbotic/database/client.hpp
Modify: client/src/client.cpp

Interfaces:

Produces: Client::RelationDefinition, Client::createRelation, dropRelation, listRelations, getRelationInfo, describeDelete.
[ ] Step 1: Declare the surface

In client/include/smartbotic/database/client.hpp, next to the view declarations:

    /**
     * Relation definition returned by listRelations() and getRelationInfo().
     */
    struct RelationDefinition {
        std::string name;
        std::string collection;      // child collection
        std::string field;           // child field holding the parent _id
        std::string parent;
        std::string onDelete;        // restrict | cascade | set_null | no_action
        bool validateOnWrite = false;
        uint64_t createdAt = 0;
        uint64_t updatedAt = 0;
    };

    /**
     * What one relation would do to one delete.
     */
    struct RelationImpact {
        std::string relation;
        std::string childCollection;
        std::string policy;
        uint64_t childCount = 0;
        std::vector<std::string> sampleChildIds;
        bool blocks = false;
    };

    struct DeleteImpact {
        std::vector<RelationImpact> impacts;
        bool wouldSucceed = true;
        std::string blockedReason;
    };

    bool createRelation(const std::string& name,
                        const std::string& childCollection,
                        const std::string& childField,
                        const std::string& parentCollection,
                        const std::string& onDelete = "restrict",
                        bool validateOnWrite = false);

    bool dropRelation(const std::string& name);

    [[nodiscard]] std::vector<RelationDefinition> listRelations();

    [[nodiscard]] std::optional<RelationDefinition> getRelationInfo(const std::string& name);

    /**
     * Report what deleting this document would do, without doing it.
     */
    [[nodiscard]] DeleteImpact describeDelete(const std::string& collection,
                                              const std::string& id);

Step 2: Implement, qualifying every name

In client/src/client.cpp, in Client::Impl:

    bool createRelation(const std::string& name,
                        const std::string& childCollection,
                        const std::string& childField,
                        const std::string& parentCollection,
                        const std::string& onDelete,
                        bool validateOnWrite) {
        smartbotic::databasepb::CreateRelationRequest request;
        // Every name-shaped field is qualified. v2.4.2's views bug was exactly
        // this: `collection` was qualified and `name` was not, so the registry
        // key and the lookup key could never meet.
        request.set_name(qualify(name));
        request.set_child(qualify(childCollection));
        request.set_child_field(childField);
        request.set_parent(qualify(parentCollection));
        request.set_on_delete(onDelete);
        request.set_validate_on_write(validateOnWrite);

        smartbotic::databasepb::CreateRelationResponse response;
        grpc::ClientContext context;
        setDeadline(context);
        auto status = stub_->CreateRelation(&context, request, &response);
        if (!status.ok()) {
            spdlog::error("Client::createRelation failed: {}", status.error_message());
            return false;
        }
        if (!response.success()) {
            spdlog::error("Client::createRelation rejected: {}", response.error());
        }
        return response.success();
    }

    bool dropRelation(const std::string& name) {
        smartbotic::databasepb::DropRelationRequest request;
        request.set_name(qualify(name));
        smartbotic::databasepb::DropRelationResponse response;
        grpc::ClientContext context;
        setDeadline(context);
        auto status = stub_->DropRelation(&context, request, &response);
        if (!status.ok()) {
            spdlog::error("Client::dropRelation failed: {}", status.error_message());
            return false;
        }
        return response.success();
    }

    std::vector<Client::RelationDefinition> listRelations() {
        smartbotic::databasepb::ListRelationsRequest request;
        request.set_project(config_.project);
        smartbotic::databasepb::ListRelationsResponse response;
        grpc::ClientContext context;
        setDeadline(context);

        std::vector<Client::RelationDefinition> out;
        auto status = stub_->ListRelations(&context, request, &response);
        if (!status.ok()) {
            spdlog::error("Client::listRelations failed: {}", status.error_message());
            return out;
        }
        for (const auto& pb : response.relations()) {
            Client::RelationDefinition r;
            // Round-trip: the caller created "by_workflow", so list it as that.
            r.name = unqualify(pb.name());
            r.collection = unqualify(pb.child());
            r.field = pb.child_field();
            r.parent = unqualify(pb.parent());
            r.onDelete = pb.on_delete();
            r.validateOnWrite = pb.validate_on_write();
            r.createdAt = pb.created_at();
            r.updatedAt = pb.updated_at();
            out.push_back(std::move(r));
        }
        return out;
    }

    std::optional<Client::RelationDefinition> getRelationInfo(const std::string& name) {
        smartbotic::databasepb::GetRelationInfoRequest request;
        request.set_name(qualify(name));
        smartbotic::databasepb::GetRelationInfoResponse response;
        grpc::ClientContext context;
        setDeadline(context);
        auto status = stub_->GetRelationInfo(&context, request, &response);
        if (!status.ok() || !response.found()) return std::nullopt;

        Client::RelationDefinition r;
        r.name = unqualify(response.relation().name());
        r.collection = unqualify(response.relation().child());
        r.field = response.relation().child_field();
        r.parent = unqualify(response.relation().parent());
        r.onDelete = response.relation().on_delete();
        r.validateOnWrite = response.relation().validate_on_write();
        r.createdAt = response.relation().created_at();
        r.updatedAt = response.relation().updated_at();
        return r;
    }

    Client::DeleteImpact describeDelete(const std::string& collection,
                                        const std::string& id) {
        smartbotic::databasepb::DescribeDeleteRequest request;
        request.set_collection(qualify(collection));
        request.set_id(id);
        smartbotic::databasepb::DescribeDeleteResponse response;
        grpc::ClientContext context;
        setDeadline(context);

        Client::DeleteImpact out;
        auto status = stub_->DescribeDelete(&context, request, &response);
        if (!status.ok()) {
            spdlog::error("Client::describeDelete failed: {}", status.error_message());
            return out;
        }
        for (const auto& pb : response.impacts()) {
            Client::RelationImpact i;
            i.relation = unqualify(pb.relation());
            i.childCollection = unqualify(pb.child_collection());
            i.policy = pb.policy();
            i.childCount = pb.child_count();
            i.blocks = pb.blocks();
            for (const auto& s : pb.sample_child_ids()) i.sampleChildIds.push_back(s);
            out.impacts.push_back(std::move(i));
        }
        out.wouldSucceed = response.would_succeed();
        out.blockedReason = response.blocked_reason();
        return out;
    }

Add the five forwarding methods on Client itself, next to the view forwarders.

Step 3: Build and commit

Run: cmake --build build -j$(nproc) Expected: build succeeds.

git add client/include/smartbotic/database/client.hpp client/src/client.cpp
git commit -m "feat(relations): client library surface

Every name-shaped field is qualified on the way out and un-qualified on
the way back, so callers keep using bare names. v2.4.2's views bug was
exactly this asymmetry - collection qualified, name not - and it made
view lookups miss silently for two releases.

listRelations scopes to the client's project so a workspace never
enumerates another's relations."

Task 11: End-to-end test over the RPC boundary

Mandatory. The v2.4.2 views bug survived two releases because in-process unit tests passed while the client/server boundary was broken. Relations have the same shape.

Files:

Create: tests/load_test/test_relations_e2e.sh
[ ] Step 1: Write the test

Create tests/load_test/test_relations_e2e.sh, modelled on tests/load_test/test_views_multiproject.sh:

#!/usr/bin/env bash
# Relations over the real RPC boundary.
#
# The v2.4.2 views bug lived in exactly this gap: unit tests passed in-process
# while the client qualified some name fields and not others, so the registry
# key and the lookup key never met. Relations have the same shape, so the
# boundary gets covered directly.
#
# Scenarios:
#   1. restrict blocks a parent delete and names the blocking children
#   2. describeDelete predicts the block without performing it
#   3. deleting the children unblocks the parent
#   4. cascade removes children
#   5. array references are PULLED, the child document survives
#   6. set_null nulls a scalar reference
#   7. two projects, same relation name, no bleed
#   8. validate_on_write rejects a child with a missing parent
set -euo pipefail

cd "$(dirname "$0")"
# Derive the repo root from this script's own location, so running from a git
# worktree builds and tests THAT worktree's binaries rather than silently
# exercising the main checkout's.
ROOT="$(cd "$(dirname "$0")/../.." && pwd)"
DIR=/tmp/sbdb-relations-e2e
PORT=9081

rm -rf "$DIR"; mkdir -p "$DIR/data"

cat > "$DIR/config.json" <<EOF
{
  "storage": {
    "data_directory": "$DIR/data",
    "listeners": [
      { "bind": "127.0.0.1", "port": $PORT,
        "tls": { "enabled": false }, "auth": { "required": false } }
    ],
    "encryption": { "enabled": false },
    "migrations": { "enabled": false },
    "replication": { "enabled": false }
  }
}
EOF

"$ROOT/build/service/smartbotic-database" --config "$DIR/config.json" \
    > "$DIR/server.log" 2>&1 &
PID=$!
trap "kill $PID 2>/dev/null || true" EXIT
sleep 3

cat > "$DIR/driver.cpp" <<'CPP'
#include <smartbotic/database/client.hpp>
#include <iostream>
#include <string>

using smartbotic::database::Client;

static int failures = 0;
static void check(bool ok, const std::string& what) {
    std::cout << (ok ? "  PASS  " : "  FAIL  ") << what << "\n";
    if (!ok) ++failures;
}

static Client makeClient(const std::string& project) {
    Client::Config cfg;
    cfg.address = "127.0.0.1:9081";
    cfg.project = project;
    return Client(cfg);
}

int main() {
    Client c = makeClient("default");
    if (!c.connect()) { check(false, "connect"); return 1; }

    c.createCollection("workflows");
    c.createCollection("executions");
    c.insert("workflows", {{"_id", "wf-1"}, {"name", "build"}});
    c.insert("executions", {{"_id", "ex-1"}, {"workflow_id", "wf-1"}});
    c.insert("executions", {{"_id", "ex-2"}, {"workflow_id", "wf-1"}});

    check(c.createRelation("exec_wf", "executions", "workflow_id", "workflows", "restrict"),
          "createRelation(restrict)");

    // 2. describeDelete predicts the block WITHOUT performing it.
    auto impact = c.describeDelete("workflows", "wf-1");
    check(!impact.wouldSucceed, "describeDelete says the delete would be blocked");
    check(impact.impacts.size() == 1, "one relation applies");
    check(impact.impacts[0].childCount == 2, "it reports 2 blocking children");
    check(!impact.blockedReason.empty(), "a human-readable reason is given");
    check(c.get("workflows", "wf-1").has_value(), "describeDelete did not delete anything");

    // 1. restrict blocks.
    bool blocked = false;
    try {
        c.remove("workflows", "wf-1");
    } catch (const std::exception& e) {
        blocked = std::string(e.what()).find("restrict") != std::string::npos;
    }
    check(blocked, "restrict blocks the parent delete and says why");

    // 3. removing children unblocks.
    c.remove("executions", "ex-1");
    c.remove("executions", "ex-2");
    check(c.describeDelete("workflows", "wf-1").wouldSucceed, "unblocked once children are gone");
    c.remove("workflows", "wf-1");
    check(!c.get("workflows", "wf-1").has_value(), "parent deletes once unblocked");

    // 4. cascade.
    c.dropRelation("exec_wf");
    check(c.createRelation("exec_wf_c", "executions", "workflow_id", "workflows", "cascade"),
          "createRelation(cascade)");
    c.insert("workflows", {{"_id", "wf-2"}, {"name", "deploy"}});
    c.insert("executions", {{"_id", "ex-3"}, {"workflow_id", "wf-2"}});
    c.remove("workflows", "wf-2");
    check(!c.get("executions", "ex-3").has_value(), "cascade removed the child");

    // 5. array references are PULLED; the child survives.
    c.createCollection("credentials");
    c.createCollection("nodes");
    c.insert("credentials", {{"_id", "cr-1"}});
    c.insert("credentials", {{"_id", "cr-2"}});
    c.insert("nodes", {{"_id", "nd-1"}, {"credential_ids", {"cr-1", "cr-2"}}});
    check(c.createRelation("node_cred", "nodes", "credential_ids", "credentials", "cascade"),
          "createRelation on an array field");
    c.remove("credentials", "cr-1");
    auto node = c.get("nodes", "nd-1");
    check(node.has_value(), "array cascade did NOT delete the child document");
    if (node) {
        check((*node)["credential_ids"].size() == 1, "the deleted id was pulled");
        check((*node)["credential_ids"][0] == "cr-2", "the surviving id remains");
    }

    // 6. set_null on a scalar.
    c.createCollection("sessions");
    c.insert("workflows", {{"_id", "wf-3"}});
    c.insert("sessions", {{"_id", "se-1"}, {"workflow_id", "wf-3"}});
    check(c.createRelation("sess_wf", "sessions", "workflow_id", "workflows", "set_null"),
          "createRelation(set_null)");
    c.remove("workflows", "wf-3");
    auto sess = c.get("sessions", "se-1");
    check(sess.has_value(), "set_null kept the child document");
    if (sess) check((*sess)["workflow_id"].is_null(), "the reference became null");

    // 7. project isolation, same relation name.
    Client a = makeClient("acme");
    if (a.connect()) {
        a.createCollection("workflows");
        a.createCollection("executions");
        a.insert("workflows", {{"_id", "wf-1"}});
        a.insert("executions", {{"_id", "ex-1"}, {"workflow_id", "wf-1"}});
        check(a.createRelation("exec_wf_c", "executions", "workflow_id", "workflows", "restrict"),
              "same relation name in a second project is accepted");
        check(a.listRelations().size() == 1, "listRelations shows only this project's");
        auto ai = a.describeDelete("workflows", "wf-1");
        check(!ai.wouldSucceed, "acme's relation governs acme's data");
    } else {
        check(false, "acme client connected");
    }

    // 8. validate_on_write.
    c.createCollection("tasks");
    check(c.createRelation("task_wf", "tasks", "workflow_id", "workflows", "restrict", true),
          "createRelation(validate_on_write)");
    bool rejected = false;
    try {
        c.insert("tasks", {{"_id", "tk-1"}, {"workflow_id", "does-not-exist"}});
    } catch (const std::exception&) {
        rejected = true;
    }
    check(rejected, "validate_on_write rejects a child with a missing parent");

    std::cout << "\n" << (failures ? "FAILED: " + std::to_string(failures) + " check(s)"
                                   : "ALL CHECKS PASSED") << "\n";
    return failures ? 1 : 0;
}
CPP

g++ -std=c++20 -O2 \
    -I"$ROOT/client/include" \
    "$DIR/driver.cpp" \
    -L"$ROOT/build/client" -lsmartbotic-db-client \
    $(pkg-config --libs grpc++) \
    -lspdlog -lfmt -pthread \
    -Wl,-rpath,"$ROOT/build/client" \
    -o "$DIR/driver"

set +e
"$DIR/driver"
RC=$?
set -e

echo ""
echo "=== server log: relation lines ==="
grep -iE "RelationManager|relation " "$DIR/server.log" | tail -10 || echo "(none)"

exit $RC

Step 2: Run it

Run: chmod +x tests/load_test/test_relations_e2e.sh && bash tests/load_test/test_relations_e2e.sh Expected: ALL CHECKS PASSED. Any failure here is a real boundary bug - fix the cause, not the test.

[ ] Step 3: Commit

git add tests/load_test/test_relations_e2e.sh
git commit -m "test(relations): end-to-end over the RPC boundary

Covers restrict blocking with a named reason, describeDelete predicting
the block without performing it, cascade, array references being pulled
while the child survives, set_null, per-project isolation of
identically-named relations, and validate_on_write.

This test exists because the v2.4.2 views bug survived two releases
behind passing in-process unit tests. Relations share the
client-qualifies-names shape and would fail the same way."

Task 12: Migration op, CLI, docs and release

Files:

Modify: service/src/migrations/migration_runner.cpp
Modify: cli/main.cpp
Modify: docs/integration-guide.md
Modify: CLAUDE.md
Modify: VERSION
[ ] Step 1: Add the migration op

In service/src/migrations/migration_runner.cpp, after the create_view block:

    if (type == "create_relation") {
        RelationInfo r;
        r.name = operation.value("name", "");
        r.child = operation.value("child", "");
        r.childField = operation.value("child_field", "");
        r.parent = operation.value("parent", "");
        r.onDelete = operation.value("on_delete", std::string("restrict"));
        r.validateOnWrite = operation.value("validate_on_write", false);

        if (r.name.empty() || r.child.empty() || r.parent.empty() || r.childField.empty()) {
            spdlog::error("create_relation: name, child, child_field and parent are all required");
            return false;
        }

        std::string err;
        if (!relations_.createRelation(r, err)) {
            if (err.find("already exists") != std::string::npos) {
                spdlog::debug("create_relation '{}': already exists (idempotent)", r.name);
                return true;
            }
            spdlog::error("create_relation '{}': {}", r.name, err);
            return false;
        }
        spdlog::info("create_relation '{}' ({} -> {}): ok", r.name, r.child, r.parent);
        return true;
    }

MigrationRunner takes a RelationManager& alongside its existing ViewManager&; update the constructor and its call site at database_service.cpp.

Step 2: Add the CLI commands

In cli/main.cpp, add to the command dispatch and the help text:

    if (cmd == "relations") {
        for (const auto& r : client.listRelations()) {
            std::cout << "  " << r.name << "  " << r.collection << "." << r.field
                      << " -> " << r.parent
                      << "  on_delete=" << r.onDelete
                      << (r.validateOnWrite ? "  validate_on_write" : "") << "\n";
        }
        return 0;
    }

    if (cmd == "describe-delete") {
        if (args.size() < 3) {
            std::cerr << "usage: describe-delete <collection> <id>\n";
            return 1;
        }
        auto impact = client.describeDelete(args[1], args[2]);
        std::cout << (impact.wouldSucceed ? "Would succeed.\n" : "Would be BLOCKED.\n");
        for (const auto& i : impact.impacts) {
            std::cout << "  " << i.relation << "  policy=" << i.policy
                      << "  children=" << i.childCount
                      << (i.blocks ? "  [blocks]" : "") << "\n";
        }
        if (!impact.blockedReason.empty()) std::cout << "\n" << impact.blockedReason << "\n";
        return 0;
    }

Help text additions:

  relations                            List relation declarations
  describe-delete <collection> <id>    Report what deleting a document would do

Step 3: Document it

Add a "Relations" section to docs/integration-guide.md after the Views section, covering: the _relations declaration shape, the policy table (including that arrays are pulled and the child survives), the re-keying consequence under restrict, validate_on_write and the restrict race, and describeDelete. Add a v2.5.0 entry to the Key Features list in CLAUDE.md.

[ ] Step 4: Bump the version and verify everything

echo "2.5.0" > VERSION
cmake -B build -G Ninja && cmake --build build -j$(nproc)
./build/service/smartbotic-database --version    # expect 2.5.0
cd build/tests && ctest --output-on-failure       # expect all green
cd /data/smartbotic-database
bash tests/load_test/test_relations_e2e.sh        # expect ALL CHECKS PASSED
bash tests/load_test/test_views_multiproject.sh   # expect ALL CHECKS PASSED
bash tests/load_test/test_v24_tls_auth.sh         # expect 4/4

[ ] Step 5: Commit and tag

git add service/src/migrations/migration_runner.cpp cli/main.cpp docs/integration-guide.md CLAUDE.md VERSION service/src/database_service.cpp service/src/database_service.hpp
git commit -m "release(v2.5.0): relations

create_relation migration op so relations are versioned alongside the
rest of the schema, and CLI commands to list declarations and to ask what
a delete would do before doing it.

Relations give a document store referential integrity without a SQL
surface: references name document identity, on_delete is configurable per
relation with restrict as the safe default, and for array-valued
references cascade and set_null both pull the id and keep the child."
git tag -a v2.5.0 -m "v2.5.0: relations - referential integrity for a document store"
git push origin main && git push origin v2.5.0

Self-Review

Spec coverage: Data model -> Task 3. Project-scoped keys -> Tasks 3, 7, 10. Policies -> Tasks 6, 8. Field resolution (scalar/array/nested/null) -> Task 1. Re-keying consequence -> Task 12 docs. Reverse index -> Task 2. Identity sentinel -> Task 2. Write-path overloads -> Task 8. Delete sequence with WAL-first -> Task 8. Child re-pointing -> Task 5. Restrict race -> Task 9. Bootstrap -> see gap below. RPC surface -> Task 7. DescribeDelete -> Tasks 6, 7, 10. CLI -> Task 12. Testing -> Tasks 1, 2, 3, 11. Rollout -> Task 12.

Gap found and closed: the spec's index bootstrap (scanning an existing child collection when a relation is declared over populated data) had no task. It belongs in RelationManager::createRelation, after validation and before the cache insert:

    // Bootstrap - build the index over data that already exists. Blocks on a
    // large collection, which is why declaration belongs in a migration rather
    // than a live call. Idempotent: RelationIndex::add is a put.
    if (bootstrapHook_) {
        if (!bootstrapHook_(r, errorOut)) return false;
    }

with RelationManager::setBootstrapHook(std::function<bool(const RelationInfo&, std::string&)>) installed by DatabaseService to scan rel.child via the project's DocumentStore::scan, extract references from each document, and add an index edge per reference. Implement this as part of Task 3 (declaration) and cover it with an e2e scenario in Task 11: declare a relation over a collection that already has children, then assert describeDelete reports them.

Placeholder scan: clean. Every code step carries real code.

Type consistency: RelationInfo fields (name, child, childField, parent, onDelete, validateOnWrite) are used consistently in Tasks 3, 5, 6, 8, 9, 12. RelationImpact matches between relation_engine.hpp (Task 6), the proto (Task 7) and the client (Task 10). extractReferences / removeReference signatures match between Task 1 and their uses in Tasks 5 and 8.

2026-08-04-relations-v2.5.0.md 119 KB Lịch sử Raw

Relations (v2.5.0) Implementation Plan

Global Constraints

File Structure

Task 1: Reference extraction

Task 2: Reverse index sub-db

Task 3: RelationManager - declaration and persistence

Task 4: Wire RelationManager into DatabaseService

Task 5: Index maintenance on child writes

Task 6: restrict and no_action on parent delete

Task 7: Proto and RPC surface

Task 8: Txn-accepting store overloads, WAL sequencing, cascade and set_null

Task 9: validate_on_write

Task 10: Client library

Task 11: End-to-end test over the RPC boundary

Task 12: Migration op, CLI, docs and release

Self-Review

2026-08-04-relations-v2.5.0.md 119 KB

Lịch sử Raw