[Bug]: command table grows unbounded (no cleanup/TTL) → SurrealDB transaction conflicts cause sync/async source creation to fail

Is this a reproducible bug?

• I confirm this is a reproducible bug, not a feature request, idea, question, or contribution proposal.

What did you do when it broke?

1. Created a text source via POST /api/sources (both async_processing=false and true).
2. After ~7 weeks of normal production usage (single-node, rocksdb storage, -single image), the command table had grown to 99,773 records (no cleanup ever run).
3. Creating a new source consistently failed.

How did it break?

• Expected: source is created with full_text populated (sync) or a queued job that completes and populates full_text (async).
• Actual: _create_source_sync_path always timed out after exactly 300s and returned {"detail": "Error creating source"}. The async path returned immediately but the underlying process_source command stayed status: "running" indefinitely and the source record was eventually deleted (never processed).
• Root cause traced to commands/source_commands.py process_source_command → Source.get(input_data.source_id) raising NotFoundError: source with id source:xxx not found immediately after the same source was just created and saved in _create_source_sync_path/_create_source_async_path. The retry decorator's own comment (# Handle deep queues (workaround for SurrealDB v2 transaction conflicts)) suggests this is a known class of issue.
• INFO FOR TABLE command shows the table is fully schemaless with zero indexes (fields: {}, indexes: {}), so every command-status query does a full scan; at ~100k rows a simple GROUP BY status took 3+ seconds and DELETEs took ~27s.
• Deleting old status = 'completed' command rows (kept last 7 days: 99,773 → 158) immediately fixed it — subsequent source creations (including a 208KB text payload) completed in 1-1.6s with full_text correctly populated. This strongly points to the command table's unbounded growth (no TTL/cleanup) driving SurrealDB (rocksdb, single-node) into a state where recently-written records intermittently/consistently aren't visible to a subsequent read from a different connection (db_connection() opens+closes a brand-new AsyncSurreal connection per call).

Logs or Screenshots

File "/app/commands/source_commands.py", line 76, in process_source_command source = await Source.get(input_data.source_id) File "/app/open_notebook/domain/base.py", line 126, in get raise NotFoundError(f"{table_name} with id {id} not found") open_notebook.exceptions.NotFoundError: source with id source:whreljeqd1v1pf67fpev not found SELECT count() FROM command GROUP ALL; -- 99773 (before cleanup) INFO FOR TABLE command; -- { events: {}, fields: {}, indexes: {}, lives: { "...": 'LIVE SELECT * FROM command' }, tables: {} }

Open Notebook Version: v1.14.0 (-single image), also reproduced on local checkout at v1.14.0-10-ga63b438

Open Notebook Version

v1-latest-single (Docker, deprecated)

Environment

Ubuntu (Docker), single-node rocksdb storage backend, SURREAL_COMMANDS_MAX_TASKS/OPEN_NOTEBOOK_WORKER_MAX_TASKS at default (5)

Additional Context

Two related, smaller issues found alongside:

1. The -single image's compose docs suggest SURREAL_COMMANDS_MAX_TASKS, but the actual worker command line (supervisord.conf) reads OPEN_NOTEBOOK_WORKER_MAX_TASKS (default 5) — the documented/commonly-used var name has no effect.
2. No built-in mechanism (cron, TTL, or admin endpoint) to prune old command records — operators have to do it manually via direct SurrealDB access.

Suggested fix direction: periodic cleanup of status IN ['completed','failed','canceled'] command rows past a retention window (configurable), plus an index on status for cheaper admin/maintenance queries.

Contribution

• I am a developer and would like to work on fixing this issue (pending maintainer approval)