# 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.

## Outcome, 2026-08-11

All five were addressed in 2.11.1 (`320dfca`), and item 4 turned out never to
have been a defect at all. Verified against the live instance rather than taken
from the commit message:

| # | Finding | Outcome |
| --- | --- | --- |
| 1 | `relations` listed nothing | Fixed - lists correctly |
| 2 | `relation-create` blamed the wrong argument | Fixed - names the offending argument and prints the correction to use |
| 3 | No `unique` on `index-create` | Added - `--unique`, refused with examples when duplicates exist |
| 4 | `find` had no limit or offset | Never broken. My error, twice - see below |
| 5 | `password` ciphertext in version reads | Fixed - and checked in both directions, see below |

On 5, the symptom is gone and the fix is not merely stuck the other way:
`hasUnpublishedChanges` reads false on a published workflow and flips to true
after a real edit.

## 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 - WRONG, WITHDRAWN

**This finding was mine and it was wrong.** `--limit` already existed when I
filed it; it was simply missing from `--help`, and I reported from the help text
rather than trying it. Clearing 3,869 session documents took 39 rounds of
find-then-remove that one `--limit` would have avoided.

Worse, when I re-checked it on 2.11.1 I reported it still broken. That was a bug
in my own test: the shell here is zsh, which does not word-split an unquoted
parameter, so `find $C $args` with `args="--limit 3"` passed one argument
`"--limit 3"` and the CLI ignored it. Every row count came back 100 and looked
like confirmation. Passing the flags literally:

```
find ... --limit 3            -> 3 rows
find ... --limit 150          -> 150 rows      (not capped at 100)
find ... --limit 2 --offset 0 -> exec_0013ec98..., exec_001806a9...
find ... --limit 2 --offset 2 -> exec_00217216..., exec_0028c8c0...
```

`--offset` and `--eq FIELD VALUE` are new in 2.11.1, and `--help` now documents
all of them. Nothing to fix. The lesson is mine: test the thing, do not read the
help and infer, and be suspicious of a negative result that arrives too neatly.

### 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.