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

Feedback for the smartbotic-database project

Date: 2026-08-10 Server: 2.11.0 on zeus (zeus.fsociety.hu:9004) Client on mulan: libsmartbotic-db-client-dev 2.9.0-2 CLI on mulan: reports 2.9.0-2 to dpkg but offers the 2.11.0 relation commands

Everything below was reproduced against the live 2.11.0 instance today. Where I could not reproduce something, I say so rather than passing on a rumour.

Confirmed problems

1. relations lists nothing while a relation exists

$ smartbotic-db-cli --address zeus.fsociety.hu:9004 relations
no relations declared

$ smartbotic-db-cli --address zeus.fsociety.hu:9004 relation smartbotic-automation:executions_workflow
smartbotic-automation:executions_workflow:
  child:              smartbotic-automation:executions
  child_field:        workflowId
  parent:             smartbotic-automation:workflows
  on_delete:          cascade
  validate_on_write:  no

The relation is real - it backfilled 2,136 rows and describe-delete enforces it correctly. Only the listing cannot see it.

ListRelationsRequest.project is documented in proto/database.proto:985 as "Empty lists every project (operator/CLI use)", so a CLI sending an empty project should have listed it. Either the CLI sends something other than empty, or the empty case does not do what the comment says.

This matters more than a cosmetic listing bug: relations is how an operator answers "what is enforced on this database right now", and it currently answers "nothing" on a database with enforcement active. Somebody could reasonably conclude a declaration failed and declare it twice.

2. relation-create blames the wrong argument

The relation name must be project-qualified, which is documented (proto/database.proto:928). Passing an unqualified name gives:

$ ... relation-create executions_workflow \
      smartbotic-automation:executions workflowId smartbotic-automation:workflows cascade false
error: relation, child and parent must be in one project (no transaction spans two project envs)

Child and parent were both already qualified and both in one project. The message names the two arguments that were correct and not the one that was wrong. Naming the offending argument would have saved a round trip.

3. Unique constraints are unreachable from the CLI

index-create <collection> <field> takes no unique flag, so the headline unique-constraint feature of 2.11.0 cannot be declared by an operator at all - only from client code. We have three constraints we want (users.username, users.email, (projectId, name) on workflows) and cannot declare any of them until our client is upgraded, even though the server supports them today.

4. find has no limit or offset

find <collection> returns a fixed 100 with no way to page. Clearing 3,869 session documents took 39 rounds of find-then-remove. A [limit] [offset], or a remove-where, would turn routine cleanup from a scripted loop into one command.

5. Fields named password are encrypted in version snapshots but not in live reads

This one cost real debugging time and is not documented anywhere I could find.

A workflow whose node config contains a field named password reads back plaintext from get, and $ENC$zEiFjkEXD+... from every version, including version 1. The same ciphertext appears in all versions. $ENC$ does not appear anywhere in our source tree, and the client exposes only a collection-level encrypted flag which we do not set - so this is the daemon applying a field-name-based rule at the version-history layer.

The visible consequence for us: our editor asks "does this workflow differ from its published version?" by comparing the live document against the stored version. That comparison is now plaintext against ciphertext, so it is always different, and the editor permanently claims unpublished changes for any workflow with such a field. A badge that is always on is a badge people stop reading.

Whatever the intent, it needs to be either documented prominently or made symmetric - a version read that returns what a live read returns would remove the whole class of problem.

Not your bugs - corrections to things I previously believed

Recorded so nobody wastes time chasing them.

• Server-side projection is in the client and has been since 2.7.1 (client.hpp:425). I had it noted as "on the wire but not in the client". Our adapter still filters after fetching and its comment still says the old thing; that is our bug to fix, and it is the cheap fix for our biggest read cost.
• ListCollectionsRequest gained a project field in 2.8.0 (proto/database.proto:528). I had it noted as "not project-scoped and cannot be". Our adapter still verifies every bare collection name with a separate getCollectionInfo call, which is now unnecessary work on every listing.
• A page of large documents returning empty rather than erroring: I could not reproduce this today. 200 execution documents came back intact. Our own read path goes through a server-side summary view, which may be masking it. Not reported as current, only noted.

Questions

1. Does relation-check scan the whole child collection, or use the reverse index? We would like to run it periodically against executions (2,136 rows today, growing) and need to know whether that is cheap or a full scan.
2. Is there a way to declare a relation whose child field is nested inside an array of objects? Two references in our data cannot currently be expressed: a workflow node's config.credentialId and its type, both living inside a nodes[] array. If that is out of scope, saying so plainly would let us stop looking for it.
3. validate_on_write hard-rejects an empty-string reference. Is there a way to allow "no parent" while still validating non-empty references? We have a legitimate case - a webhook run that records no workflow id - and today the choice is all-or-nothing per relation.