smartbotic/docs/superpowers/analysis/2026-08-10-database-relations.md

Database relations (v2.11.0): what we hand-roll today, and what to declare

Written 2026-08-10. Read-only analysis, no code changed. Context: the upstream smartbotic-database daemon on zeus is 2.11.0 (relations, unique constraints); our client on mulan is still 2.9.0 with no relation API, so nothing here can be used yet. This is groundwork for when the client upgrade lands.

Sources read in full before writing this: /data/smartbotic-database/proto/database.proto (from line 919) and /data/smartbotic-database/client/include/smartbotic/database/client.hpp (from line 800).

1. Where we hand-roll referential integrity today

#	Delete site	Cleans up	Mechanism	Complete?
1	workflow_controller.cpp:673-696 deleteWorkflow	its own scheduled triggers, then executions (workflowId match) and its private wf_<id> collection and its files	updateScheduledTriggers(id,false), then retention_.retire(id) (retention_service.cpp:42-50) queues an async job: restamp matching executions/documents/files to a short TTL, drop the wf_ collection after	No. retire() restamps only executions matching workflowId at the moment of the call, via an async worker. Anything that failed to restamp (worker not running yet, restart mid-sweep, or - the actual history here - workflows deleted before this retention machinery existed) is never revisited. Confirmed by the hand sweep below.
2	project_controller.cpp:303-349 deleteProject	nothing directly; instead refuses the delete while workflows, credentials or workflow_groups still carry that projectId (ownedCollections(), project_controller.cpp:20-25)	manual restrict-style check, page_size=1000 per collection	Complete for its stated job (never deletes a project holding work), but it is a hand-written restrict relation over exactly the three collections in ownedCollections() - nothing else with a projectId (e.g. a future collection) is covered without remembering to add it here.
3	user_controller.cpp:183-227 deleteUser	sessions (auth_store.cpp:247-252 -> invalidateAllSessions, auth_store.cpp:427-438, deletes each sessions row for the user); the user's personal project, but only if it holds no workflows/credentials/workflow_groups (user_controller.cpp:203-221)	explicit per-row storage_.remove("sessions", ...); explicit query + conditional storage_.remove("projects", ...)	Leaves orphans. Sessions are the only thing cleaned. Not touched: credentials.sharedWith arrays containing the deleted user's id (verified no code path strips it - see credential_controller.cpp:107-148, only add/remove-by-request exist); ownerId/createdBy on workflows, credentials, workflow_groups, projects (dangling attribution, arguably intentional - see relation table); collection_permissions and api_keys are unused in this codebase today (see Part 3), so nothing references a user there yet.
4	workflow_group_controller.cpp:320-379 deleteGroup	with force=true: reassigns every child workflow's groupId to "" and every child subgroup's parentId to the deleted group's own parent (workflow_group_controller.cpp:334-368); without force, refuses if hasChildren(id)	manual set_null-style reassignment	Complete for its own children. This is exactly what a set_null relation with child_field=groupId/parentId would do automatically.
5	credential_controller.cpp:383-408 deleteCredential	nothing	none	Leaves orphans. No check that a workflow node still references the credential's id (config.credentialId, embedded inside workflow.nodes[]), and no cleanup after delete. A workflow keeps pointing at a credentialId that resolves to nothing; the node then fails at run time with whatever "credential not found" error the node author wrote (if any).
6	node_controller.cpp:399-416 deleteNode	nothing	none	Leaves orphans. No check that any workflow still has a node of that type. Note: this reference cannot be expressed as a v2.11.0 relation at all - see the "not relation-able" note under Part 2.

The orphan sweep. Commit a0cf96e (2026-08-09, "feat: clear orphaned data, refuse dropping a collection that holds documents") is the hand sweep referenced in the task. Its own numbers: 8,538 of 10,159 execution records belonged to workflows that no longer existed ("mostly test workflows deleted over the past days"), plus 67 entirely-null rows from writes that never completed; executions went from 481 MB to 74 MB. The commit message states the direct cause: retention's cascade only finds executions whose workflowId matches a current delete call, so a workflow deleted before retire() existed (the whole per-workflow retention feature landed 2026-08-08/09, commits 8a52564 through 802d30c) has no mechanism that will ever revisit it. The same commit also found four watch_cursors that looked orphaned by field-matching but were not (watch_cursors is keyed by the collection it watches, not by a workflow id) - a caution about guessing a "child field" instead of reading the schema.

Two more things worth flagging that are not deletions but sit right next to this problem:

• execution_controller.cpp:113-131 canSeeExecution explicitly handles both a deleted-workflow execution ("The workflow has been deleted. Its history outlives it...") and an execution with an empty workflowId ("Some runs record no workflow id - the webhook path is one"). The empty-workflowId case was an active bug until 2026-08-08 (see Part 2's hazard section) - the comment is now a defensive leftover for historical rows, not a live data path, but those historical rows still exist.
• webserver_service.cpp:661-706 reconcile (runner restart handling) proactively closes executions rows stuck in status: running on a runner that no longer claims them, logging them as orphans it repairs rather than ignores. This is the one piece of "orphan" handling in the codebase that is actually complete for its scope.

2. Proposed relation table

Child collection	Child field	Parent collection	on_delete	validate_on_write	Why
executions	workflowId	workflows	no_action	off	This is the one place cascade/set_null looks tempting and is wrong twice over: (a) it is exactly the relation whose child (executions) already carries a per-row TTL restamped after the fact by retention_service.cpp - see the TTL hazard below, this time from the child side, since a cascade delete has to walk the child's rows while the TTL sweep may be mutating them concurrently; (b) validate_on_write would hard-reject the empty-string workflowId that historical rows carry and that the webhook path produced until 2026-08-08 (followups-from-tier-2 memory: "FIXED 2026-08-08... An execution recorded an empty workflowId whenever the run used the published version"). Recommend no_action and leave the existing app-level retire() sweep as the cleanup path, or move to restrict-then-manual-retire if the team wants delete-time visibility. Do not use cascade.
workflows	projectId	projects	restrict	on	Replaces the hand-rolled check in project_controller.cpp:320-337. validate_on_write is safe: nothing found that inserts a workflow with an empty or dangling projectId (workflow creation always assigns one, workflow_controller.cpp:357 sets ownerId, and projectId comes from the same request).
workflow_groups	projectId	projects	restrict	on	Same reasoning as above; currently covered by the same ownedCollections() check.
credentials	projectId	projects	restrict	on	Same; currently covered by ownedCollections().
workflows	groupId	workflow_groups	set_null	on	Matches the existing manual behaviour in deleteGroup force-path (workflow_group_controller.cpp:334-345) exactly - a declared relation would let that hand-written loop be deleted.
workflow_groups	parentId	workflow_groups (self)	set_null	on	Matches deleteGroup's subgroup reparenting (workflow_group_controller.cpp:347-367). Self-referential relations are not called out as unsupported in the proto/client docs, but neither are they described as tested - verify with upstream before relying on this one, flagged as a soft caution rather than a hard unsafe.
credentials	sharedWith	users	cascade (array field, so this pulls the id and keeps the credential - equivalent to set_null here per the proto note on array fields)	on	Closes gap #3/#5 above: today a deleted user is never stripped from credentials.sharedWith, leaving a dangling id in the array forever. validate_on_write is safe as long as sharing continues to check auth_store_.getUser first, which credential_controller.cpp:100-116 already does.
sessions	userId	users	cascade	on	Would let AuthStore::deleteUser's explicit per-row loop (auth_store.cpp:247-252, 427-438) be deleted entirely - the relation does the same thing atomically with the parent delete. validate_on_write is safe: a session is only ever created from an authenticated login against an existing user (auth_store.cpp:282-320).
workflows / credentials / workflow_groups / projects / nodes	ownerId / createdBy	users	no_action	off	These are attribution fields, not ownership in the referential-integrity sense - the app already treats a dangling owner as fine (e.g. canSeeExecution's deleted-workflow branch). Declaring anything stronger than no_action would be a behavior change nobody asked for: deleting a user would either destroy their workflows (cascade, clearly wrong) or silently blank out who made them (set_null, loses history). Leave alone; no_action only adds the reverse index for future describeDelete() queries, which is a pure upside.
api_keys	(n/a)	users	-	-	Not a real candidate today. api_keys is a reserved system-collection name (database_controller.cpp:300, auth_store.hpp/system_collections.hpp:43) with no controller, no writer, and (checked live) zero documents and not even listed among the instance's existing collections. Nothing to declare a relation over until the feature is built.
collection_permissions	(n/a)	users	-	-	Not a real candidate. It is a single settings document keyed "settings" (collection_permissions.cpp:39 storage_.get("collection_permissions", "settings")), not a per-user row. No child field to point at a user.
workflow node config.credentialId	-	credentials	(not expressible)	-	Cannot be declared as a v2.11.0 relation at all. child_field is a dot-path that resolves to a single id or an array of ids on the child document itself. A workflow's credential references live inside nodes[].config.credentialId, i.e. one string per element of an array of objects, not an array of ids. The relation contract has no path syntax for "the credentialId field of every element of this array." This is exactly the gap behind hand-rolled cleanup gap #5 (deleteCredential cleans up nothing) - relations do not fix it; only app code walking workflow.nodes can.
workflow node type	-	nodes	(not expressible)	-	Same shape of problem as above, same conclusion: not relation-able, still needs app code if deleteNode (gap #6) is ever to check for in-use node types.

Unsafe / flagged candidates, summarized:

1. executions.workflowId -> workflows with cascade or set_null is unsafe and not recommended. executions carries retention TTLs that retention_service.cpp actively restamps (restamp(), retention_service.cpp:187-283, called from both apply() and retire()). The upstream warning is about a race hitting a cascade/set_null relation whose parent carries a renewed-after-expiry TTL; here the shoe is on the other foot - executions is the child, and it is the child rows that are being TTL-renewed out from under a delete-time cascade walk over the parent (workflows). Either direction is the same underlying hazard: a delete-time relation walk and an independent TTL-renewal sweep touching the same rows concurrently. Recommend no_action, keep retire() as-is (or route it through describeDelete once the client has it, for visibility), and rely on periodic sweeps like a0cf96e for anything retire() misses.
2. validate_on_write on executions.workflowId is currently safe going forward (the empty-string bug was fixed 2026-08-08) but would reject re-inserting or re-processing any of the historical empty-workflowId rows that predate the fix, and would reject the row shape canSeeExecution still defends against. Recommend leaving validate_on_write off for this field until a migration confirms no code path can still produce workflowId: "" (see Part 4 - the listPending path pulls raw executions and would be worth auditing at the same time).
3. workflow_groups.parentId self-relation - not proven unsafe, just unverified against a self-referential case; test against a real 2.11.0 instance before relying on it for the group hierarchy.

3. Unique constraint candidates

Checked against the live instance at http://localhost:8090 (admin/admin) on 2026-08-10.

Field	Candidate collection	Live duplicates found	Declarable today?
username	users	0 (only 1 user exists: admin)	Yes, trivially - but with only one row this is a weak signal. Re-check once the instance has more than one account.
email	users	0 (same caveat)	Yes, same caveat.
refreshToken	sessions	19 (given - task states this is already known)	No. Needs a cleanup pass (dedupe or drop stale sessions) before unique=true can be declared; the server will refuse it while duplicates exist.
id	nodes	0 across 86 node definitions	Moot - id is already the document's own primary key (_id), so a unique index is redundant, not a new guarantee.
(projectId, name)	credentials	1 pair: two credentials named noreply@shopcall.ai in project prj_89c532cf-... (cred_53c04169-... and cred_a668d3b5-...)	No. Needs the duplicate renamed or merged first. Only 7 credentials total in this instance, so the fix is a two-minute manual rename, not a migration.
(projectId, name)	workflows	0 across 12 workflows	Yes, declarable today.

No slug-style field was found anywhere in the schema (workflows, projects, credentials, node definitions all key by generated id, not a human slug), so there is nothing to check there.

4. The fat execution document

What it looks like today. ExecutionResult::toJson() (workflow_engine.cpp:210-273) embeds every node's output as nodeExecutions[] directly inside the execution document, plus an optional full workflowSnapshot for the pinning feature. Two truncation regimes already exist as a stopgap: a finished execution's node outputs are truncated by truncateLargeValues (workflow_engine.cpp:251), but a Waiting execution keeps every node output whole and untruncated (workflow_engine.cpp:234-240) - deliberately, because a later resume seeds itself from that stored state. That is precisely the shape of document that gets largest.

Where the current pain actually shows up, confirmed by reading the read paths rather than assuming:

• The list endpoint (GET /executions, execution_controller.cpp:286) does not suffer this today - it already queries a server-side view, executions_summary (webserver_service.cpp:460-493), that projects out nodeExecutions and workflowSnapshot entirely at the database, listing only ten summary fields. This view is the reason listing 336 KB documents doesn't currently cost what it could.
• ExecutionController::listPending (execution_controller.cpp:545-573, GET /executions/pending), by contrast, queries the raw executions collection with a status=waiting filter and no field projection and no page size cap. Every waiting execution is exactly the untruncated, full-node-output case described above. This is the most likely candidate for "a page of large documents can come back empty because the gRPC message limit is hit silently" - it is the one remaining raw, unprojected, unpaginated read of full execution documents in the controller layer.
• Single-execution GET /executions/:id and the resume path (workflow_engine.cpp:1029, 1060-1111) legitimately need the full document - resume literally reconstructs node_results by reading record["nodeExecutions"] back off the stored row.

What a split would cost.

• Schema: new execution_node_results (or similar) collection, one row per (executionId, nodeId) (or one row per execution holding the array - either way the relation is execution_node_results.executionId -> executions). Given the TTL hazard identified in Part 2 item 1, this new child collection would sit in the exact configuration the upstream implementor warned about: its parent (executions) is TTL-bearing and is actively restamped after creation by retention_service.cpp. A cascade or set_null relation here inherits the same race the executions-to-workflows relation was flagged for above, except now it is the primary, intended use of the split, not an edge case - so it would need no_action plus an explicit sweep (mirroring what already exists for workflow deletion) rather than relying on the relation to keep the two collections in sync.
• Write path: WorkflowEngine::execute and every node-completion callback (workflow_engine.cpp:1921, 1955, 1981, 2001, 2023, and the loop-body variants) would need to write node results as a second collection call instead of building one in-memory ExecutionResult and serializing it once. That is a real increase in write volume and a new failure mode (execution row written, node-results write fails or vice versa - no longer atomic without a transaction the storage client doesn't offer across collections).
• Read paths that would have to change: GET /executions/:id (join in the child rows), resume (workflow_engine.cpp:1060-1111, currently reads record["nodeExecutions"] directly off the stored execution - would need a second query), the pinning/diff feature that reads workflowSnapshot and node outputs together, listPending (would become the thing that benefits most, since it could stop pulling full documents), and the retention preview/measure code (retention_service.cpp:114-159) which would need to measure and restamp two collections in lockstep instead of one.
• Migration: every existing row in executions (2,125 documents live today, 74 MB post-sweep) would need its nodeExecutions array split out and reinserted as child rows, or the split would only apply going forward and every read path would need an if (has child rows) else (read inline) branch indefinitely. Given how young this collection already is (rebuilt by the 2026-08-09 sweep) a clean cutover is realistic, but it is still a one-way migration with no simple rollback once child rows are written and the inline field is dropped.

Recommendation: not yet, and only partly for the reason originally deferred. The wait was "for relations" so the split has referential integrity to lean on

• but Part 2 and this section both land on the same conclusion: the relation this split would actually use has a TTL-bearing parent, which is on the upstream implementor's own list of races to avoid for cascade/set_null. Declaring the relation as no_action (the safe choice) removes the main thing relations were expected to buy here - automatic child cleanup - leaving a second collection to keep in sync by hand, which is the same kind of sweep a0cf96e just had to run once already. The listing-side problem the split was meant to solve is already half-fixed by the executions_summary view; the concrete remaining offender (listPending) is a much smaller, much cheaper fix on its own - add a page size cap and a field projection to that one query - and would remove most of the "gRPC limit hit silently" risk without touching the schema, the write path, or every read path at once. Revisit the full split once (a) the client is actually on 2.11.0, (b) describeDelete/checkRelation give an operator a way to see what a no_action relation is quietly leaving dangling, and (c) listPending-style raw reads have been audited across the codebase to confirm this is the only one - if it is not, the case for the split gets stronger.