cleveragents/cleveragents-core

Fork 3

bug(db): database stored at <cwd>/.cleveragents/db.sqlite — spec requires ~/.local/state/cleveragents/ #8731

New issue

Open

opened 2026-04-13 22:37:41 +00:00 by HAL9000 · 4 comments

HAL9000 commented

2026-04-13 22:37:41 +00:00

Owner

Metadata

Commit Message: fix(db): relocate SQLite database to ~/.local/state/cleveragents/ per spec
Branch: fix/db-location-xdg-state-home

Background and Context

The CleverAgents product specification states that the SQLite database for local mode should be located at ~/.local/state/cleveragents/ (XDG state home convention). However, the current implementation stores the database at <cwd>/.cleveragents/db.sqlite — a project-local path relative to the current working directory.

Spec requirement (Database Persistence section):

Database is located at ~/.local/state/cleveragents/ or similar

Current implementation (src/cleveragents/application/container.py, get_database_url()):

# Fallback to file-based SQLite in the current working directory
db_path = Path.cwd() / ".cleveragents" / "db.sqlite"
return f"sqlite:///{db_path.absolute()}"

This means:

Each project directory gets its own isolated database
The database is NOT in the XDG state home directory as the spec requires
Running agents project list from different directories will show different projects
The spec's intent of a shared user-level database at ~/.local/state/cleveragents/ is not fulfilled

Current Behavior

The database is created at <cwd>/.cleveragents/db.sqlite where <cwd> is the current working directory when the CLI is invoked.

Expected Behavior

The database should be stored at ~/.local/state/cleveragents/cleveragents.db (or equivalent XDG state home path), making it a user-global database accessible from any working directory.

Impact

Users working across multiple directories see different project lists
The agents project list command shows different results depending on where it is run
Spec-compliant tooling expecting a global database at ~/.local/state/cleveragents/ will not find the database

Acceptance Criteria

get_database_url() in src/cleveragents/application/container.py uses ~/.local/state/cleveragents/ (XDG state home) as the default database location
The directory is created if it does not exist
CLEVERAGENTS_DATABASE_URL env var override still works
agents project list shows the same projects regardless of the current working directory
Migration path documented for users with existing <cwd>/.cleveragents/db.sqlite databases

Supporting Information

Affected file: src/cleveragents/application/container.py — get_database_url() function
The TUI milestone (v3.7.0) spec already references ~/.local/state/cleveragents/tui.db for the TUI database, confirming the intended location pattern
XDG Base Directory Specification: $XDG_STATE_HOME defaults to ~/.local/state

Subtasks

Update get_database_url() to use ~/.local/state/cleveragents/cleveragents.db as default
Ensure directory creation logic is in place
Update any tests that assume <cwd>/.cleveragents/db.sqlite
Add migration note to CHANGELOG.md
Run nox (all default sessions), fix any errors
Verify coverage >= 97% via nox -s coverage_report

Definition of Done

This issue is complete when:

All subtasks above are completed and checked off.
A Git commit is created where the first line of the commit message matches the Commit Message in Metadata exactly, followed by a blank line, then additional lines providing relevant details about the implementation.
The commit is pushed to the remote on the branch matching the Branch in Metadata exactly.
The commit is submitted as a pull request to master, reviewed, and merged before this issue is marked done.

Automated by CleverAgents Bot
Supervisor: UAT Test Pool | Agent: uat-test-pool-supervisor
Worker: [AUTO-UAT-12]

## Metadata - **Commit Message**: `fix(db): relocate SQLite database to ~/.local/state/cleveragents/ per spec` - **Branch**: `fix/db-location-xdg-state-home` ## Background and Context The CleverAgents product specification states that the SQLite database for local mode should be located at `~/.local/state/cleveragents/` (XDG state home convention). However, the current implementation stores the database at `<cwd>/.cleveragents/db.sqlite` — a project-local path relative to the current working directory. **Spec requirement (Database Persistence section):** > Database is located at `~/.local/state/cleveragents/` or similar **Current implementation** (`src/cleveragents/application/container.py`, `get_database_url()`): ```python # Fallback to file-based SQLite in the current working directory db_path = Path.cwd() / ".cleveragents" / "db.sqlite" return f"sqlite:///{db_path.absolute()}" ``` This means: 1. Each project directory gets its own isolated database 2. The database is NOT in the XDG state home directory as the spec requires 3. Running `agents project list` from different directories will show different projects 4. The spec's intent of a shared user-level database at `~/.local/state/cleveragents/` is not fulfilled ## Current Behavior The database is created at `<cwd>/.cleveragents/db.sqlite` where `<cwd>` is the current working directory when the CLI is invoked. ## Expected Behavior The database should be stored at `~/.local/state/cleveragents/cleveragents.db` (or equivalent XDG state home path), making it a user-global database accessible from any working directory. ## Impact - Users working across multiple directories see different project lists - The `agents project list` command shows different results depending on where it is run - Spec-compliant tooling expecting a global database at `~/.local/state/cleveragents/` will not find the database ## Acceptance Criteria - [ ] `get_database_url()` in `src/cleveragents/application/container.py` uses `~/.local/state/cleveragents/` (XDG state home) as the default database location - [ ] The directory is created if it does not exist - [ ] `CLEVERAGENTS_DATABASE_URL` env var override still works - [ ] `agents project list` shows the same projects regardless of the current working directory - [ ] Migration path documented for users with existing `<cwd>/.cleveragents/db.sqlite` databases ## Supporting Information - Affected file: `src/cleveragents/application/container.py` — `get_database_url()` function - The TUI milestone (v3.7.0) spec already references `~/.local/state/cleveragents/tui.db` for the TUI database, confirming the intended location pattern - XDG Base Directory Specification: `$XDG_STATE_HOME` defaults to `~/.local/state` ## Subtasks - [ ] Update `get_database_url()` to use `~/.local/state/cleveragents/cleveragents.db` as default - [ ] Ensure directory creation logic is in place - [ ] Update any tests that assume `<cwd>/.cleveragents/db.sqlite` - [ ] Add migration note to CHANGELOG.md - [ ] Run `nox` (all default sessions), fix any errors - [ ] Verify coverage >= 97% via `nox -s coverage_report` ## Definition of Done This issue is complete when: - All subtasks above are completed and checked off. - A Git commit is created where the **first line** of the commit message matches the Commit Message in Metadata exactly, followed by a blank line, then additional lines providing relevant details about the implementation. - The commit is pushed to the remote on the branch matching the **Branch** in Metadata exactly. - The commit is submitted as a **pull request** to `master`, reviewed, and **merged** before this issue is marked done. --- **Automated by CleverAgents Bot** Supervisor: UAT Test Pool | Agent: uat-test-pool-supervisor Worker: [AUTO-UAT-12]

HAL9000 added the

labels

2026-04-13 22:38:19 +00:00

HAL9000 referenced this issue

2026-04-13 22:38:43 +00:00

[AUTO-OWNR] Status: Project Owner Pool Supervisor (Cycle 14) #8732

HAL9000 added

and removed

labels

2026-04-13 22:39:15 +00:00

HAL9000 commented

2026-04-13 22:39:33 +00:00

Author

Owner

[AUTO-OWNR-1] Triage Decision (Cycle 14)

Status: ✅ Verified

MoSCoW: Must Have
Priority: High

Rationale: The database being stored at <cwd>/.cleveragents/db.sqlite instead of ~/.local/state/cleveragents/ is a direct spec violation with real user-facing consequences. Users working across multiple directories will see different project lists, making the tool unreliable. The XDG state home convention is already established in the spec (the TUI milestone references ~/.local/state/cleveragents/tui.db), confirming this is the intended pattern. This is a data persistence correctness issue that affects every user — upgraded to Priority/High.

Next Steps: A developer should update get_database_url() in src/cleveragents/application/container.py to use ~/.local/state/cleveragents/cleveragents.db as the default path, ensure directory creation, update affected tests, and document a migration path for users with existing <cwd>/.cleveragents/db.sqlite databases. Branch: fix/db-location-xdg-state-home.

Automated by CleverAgents Bot
Supervisor: Project Owner | Agent: project-owner-pool-supervisor

## [AUTO-OWNR-1] Triage Decision (Cycle 14) **Status**: ✅ Verified **MoSCoW**: Must Have **Priority**: High **Rationale**: The database being stored at `<cwd>/.cleveragents/db.sqlite` instead of `~/.local/state/cleveragents/` is a direct spec violation with real user-facing consequences. Users working across multiple directories will see different project lists, making the tool unreliable. The XDG state home convention is already established in the spec (the TUI milestone references `~/.local/state/cleveragents/tui.db`), confirming this is the intended pattern. This is a data persistence correctness issue that affects every user — upgraded to Priority/High. **Next Steps**: A developer should update `get_database_url()` in `src/cleveragents/application/container.py` to use `~/.local/state/cleveragents/cleveragents.db` as the default path, ensure directory creation, update affected tests, and document a migration path for users with existing `<cwd>/.cleveragents/db.sqlite` databases. Branch: `fix/db-location-xdg-state-home`. --- **Automated by CleverAgents Bot** Supervisor: Project Owner | Agent: project-owner-pool-supervisor

HAL9000 referenced this issue

2026-04-13 22:39:50 +00:00

[AUTO-EVLV] Status: Agent Evolution Supervisor (Cycle 5) #8735

HAL9000 referenced this issue

2026-04-13 22:45:30 +00:00

[AUTO-OWNR] Status: Project Owner Pool Supervisor (Cycle 15) #8748

HAL9000 referenced this issue

2026-04-13 22:46:58 +00:00

[AUTO-UAT-SUP] Status: UAT Test Pool Supervisor (Cycle 1) #8509

HAL9000 commented

2026-04-13 23:00:14 +00:00

Author

Owner

[GROOMED]

Reviewed the UAT supervisor report and confirmed the database path violation is validated with clear reproduction steps and acceptance criteria; no new blockers identified.
Coverage labels already correct (Type/Bug, State/Verified, Priority/High, MoSCoW/Must have); no updates required.

Automated by CleverAgents Bot
Supervisor: Grooming | Agent: grooming-pool-supervisor
Worker: [AUTO-GROOM-BATCH-H]

[GROOMED] - Reviewed the UAT supervisor report and confirmed the database path violation is validated with clear reproduction steps and acceptance criteria; no new blockers identified. - Coverage labels already correct (Type/Bug, State/Verified, Priority/High, MoSCoW/Must have); no updates required. --- **Automated by CleverAgents Bot** Supervisor: Grooming | Agent: grooming-pool-supervisor Worker: [AUTO-GROOM-BATCH-H]

HAL9000 referenced this issue

2026-04-14 01:10:22 +00:00

[AUTO-SPEC] Status: Spec Update Pool Supervisor - Status Tracking (Cycle 1) #8823

HAL9000 referenced this issue

2026-04-14 01:13:38 +00:00

[AUTO-ARCH] Status: Architecture (Cycle 1) #8820

HAL9000 referenced this issue

2026-04-14 01:13:55 +00:00

[AUTO-SPEC] Status: Spec Update Pool Supervisor - Status Tracking (Cycle 1) #8823

CoreRasurae commented

2026-04-23 22:59:23 +00:00

Member

@HAL9000 Incorporate this update into the Ticket description and specification.

This feature will also need a command line switch to specify a different location for the .cleveragents folder, which will be especially relevant when using a Docker environment.
So if nothing is specified in the command line the default location should be ~/.local/state/cleveragents/, but if the command line switch is given to override this location to another location, then the command line specified location should be used instead.
Check in the documentation and specification.md if a comand line switch is specified for that end. If it is specified, use the specification define command line switch, if not, update the specification to include --db-env command line switch for that end.

@HAL9000 Incorporate this update into the Ticket description and specification. - This feature will also need a command line switch to specify a different location for the `.cleveragents` folder, which will be especially relevant when using a Docker environment. - So if nothing is specified in the command line the default location should be `~/.local/state/cleveragents/`, but if the command line switch is given to override this location to another location, then the command line specified location should be used instead. - Check in the documentation and specification.md if a comand line switch is specified for that end. If it is specified, use the specification define command line switch, if not, update the specification to include `--db-env` command line switch for that end.

HAL9000 commented

2026-05-15 01:34:40 +00:00

Author

Owner

Analytical Review of PR #8733 (HEAD: `17fcd45703`)

Type: first_review | Verdict: Changes Requested — Major Regression Detected

Executive Summary

PR #8733 is intended to add the Subplan System specification (v3.3.0) to docs/specification.md. However, a structural problem with the branch's history means it has effectively overwritten 6+ security fixes and quality improvements that were committed to master since the divergent base commit (f2d1f4efe7). The spec additions are sound, but the implementation commits regress production code.

Critical Issues (Blockers)

C1. Path traversal regressions — BaseResourceHandler.is_safe_path() and validate_sandbox_path()
Files: src/cleveragents/resource/handlers/_base.py, src/cleveragents/skills/builtins/file_ops.py

The diff reverses a working Path.relative_to(root) check to str(target).startswith(str(root) + os.sep).
This is the prefix-collision bypass exemplified by /tmp/foo matching path /tmp/foobar/secret, which should be rejected.
Master commit 4fdfee6150 (fix #7478) already corrected this — PR overwrites it.

C2. LSP subprocess leak regression (StdioTransport.start())
File: src/cleveragents/lsp/transport.py (~lines 108–135)

Removes _process = None, self.stop(), and catch-all exception handlers. On OSError post-fork, a zombie process can remain in the caller's address space.
This was the specific purpose of master commit c768e8c4f53f.

C3. PluginLoader arbitrary code execution vulnerability re-introduced
File: src/cleveragents/plugins/loader.py

Reverts commits b588de1, c58ceb, c84ae3 — which fixed a critical flaw where plugin constructors execute side effects during protocol validation before any sandbox.
The current code calls klass() first (instantiating the unknown class), exactly what the reverts were meant to prevent.

C4. ReactiveEventBus context manager and exc_info removed
File: src/cleveragents/events/reactive.py

Removes _closed flag, __enter__/__exit__, the RuntimeError guard on emit(), and critically exc_info=True in error handler logging — exception details silently dropped at INFO level.

C5. Devcontainer auto-discovery unwired from handlers
File: src/cleveragents/resource/handlers/git_checkout.py (+ fs_directory_handler)

Removes the entire devcontainer discovery block and its discover_devcontainers() import, eliminating live functionality from issue #4740. Also removes it from fs-directory handler.

C6. Deleted MCP stdio transport implementation (252 lines)
File: src/cleveragents/mcp/stdio_transport.py — completely deleted. Replaces with bare MCPTransport() that does not implement JSON-RPC 2.0 over subprocess stdio.

CI Assessment

Check	Status	Root Cause
lint	FAILING	Fixture mismatches in test files
typecheck	PASSING	—
security	PASSING	—
build	PASSING	—
e2e_tests	PASSING	—
unit_tests	FAILING	`assert_plan_result_success` fixture has `_witnesses`/`test_wins` param removed; callers pass `test_wins=True`, causing TypeError
integration_tests	FAILING	Downstream of unit test failure
coverage	SKIPPED	Blocked by unit_tests

Spec Content Assessment (the intended contribution)

The Subplan System specification text in docs/specification.md is:

Technically sound: module boundaries, data models (Subplan, SubplanResult, SubplanTree) are well-defined
Consistent with existing spec: the 8-step spawning algorithm, concurrency control via max_parallel, and error types all align with the earlier subplan architecture section already present in master
Properly structured: PostgreSQL schema includes appropriate indexes (idx_subplans_parent_plan, root_plan, status)
Well-documented: integration points table shows clear coupling to PlanExecutor, Three-Way Merge, DecisionRecorder, CheckpointSystem

Recommendations

Rebase cleanly from latest master on a fresh branch — do not merge/force-push over master commits touching the same files
Restore reverted production code (_base.py, file_ops.py, transport.py, loader.py, reactive.py, git_checkout.py, fs_directory.py, stdio_transport.py) to their latest master versions, then layer only the spec additions on top
Fix BDD step binding mismatch in test fixtures causing unit_tests failure
Ensure commit footers include ISSUES CLOSED: #8725

Submitted by pr-review-worker for transparent traceability. Primary assessment already captured in HAL9001's earlier review chain (#5348 → #8905).

## Analytical Review of PR #8733 (HEAD: 17fcd457032c9e95) **Type**: first_review | **Verdict**: Changes Requested — Major Regression Detected --- ### Executive Summary PR #8733 is intended to add the Subplan System specification (v3.3.0) to `docs/specification.md`. However, a structural problem with the branch's history means it has effectively **overwritten 6+ security fixes and quality improvements** that were committed to master since the divergent base commit (`f2d1f4efe7`). The spec additions are sound, but the implementation commits regress production code. --- ### Critical Issues (Blockers) **C1. Path traversal regressions — `BaseResourceHandler.is_safe_path()` and `validate_sandbox_path()`** Files: `src/cleveragents/resource/handlers/_base.py`, `src/cleveragents/skills/builtins/file_ops.py` - The diff reverses a working `Path.relative_to(root)` check to `str(target).startswith(str(root) + os.sep)`. - This is the **prefix-collision bypass** exemplified by `/tmp/foo` matching path `/tmp/foobar/secret`, which should be rejected. - Master commit `4fdfee6150` (fix #7478) already corrected this — PR overwrites it. **C2. LSP subprocess leak regression (`StdioTransport.start()`)** File: `src/cleveragents/lsp/transport.py` (~lines 108–135) - Removes `_process = None`, `self.stop()`, and catch-all exception handlers. On OSError post-fork, a zombie process can remain in the caller's address space. - This was the specific purpose of master commit `c768e8c4f53f`. **C3. PluginLoader arbitrary code execution vulnerability re-introduced** File: `src/cleveragents/plugins/loader.py` - Reverts commits `b588de1`, `c58ceb`, `c84ae3` — which fixed a critical flaw where plugin constructors execute side effects during protocol validation before any sandbox. - The current code calls `klass()` first (instantiating the unknown class), exactly what the reverts were meant to prevent. **C4. ReactiveEventBus context manager and exc_info removed** File: `src/cleveragents/events/reactive.py` - Removes `_closed` flag, `__enter__`/`__exit__`, the RuntimeError guard on `emit()`, and critically **`exc_info=True`** in error handler logging — exception details silently dropped at INFO level. **C5. Devcontainer auto-discovery unwired from handlers** File: `src/cleveragents/resource/handlers/git_checkout.py` (+ fs_directory_handler) - Removes the entire devcontainer discovery block and its `discover_devcontainers()` import, eliminating live functionality from issue #4740. Also removes it from `fs-directory` handler. **C6. Deleted MCP stdio transport implementation (252 lines)** File: `src/cleveragents/mcp/stdio_transport.py` — completely deleted. Replaces with bare `MCPTransport()` that does not implement JSON-RPC 2.0 over subprocess stdio. --- ### CI Assessment | Check | Status | Root Cause | |---|---|---| | lint | FAILING | Fixture mismatches in test files | | typecheck | PASSING | — | | security | PASSING | — | | build | PASSING | — | | e2e_tests | PASSING | — | | unit_tests | FAILING | `assert_plan_result_success` fixture has `_witnesses`/`test_wins` param removed; callers pass `test_wins=True`, causing TypeError | | integration_tests | FAILING | Downstream of unit test failure | | coverage | SKIPPED | Blocked by unit_tests | --- ### Spec Content Assessment (the intended contribution) The Subplan System specification text in `docs/specification.md` is: - **Technically sound**: module boundaries, data models (`Subplan`, `SubplanResult`, `SubplanTree`) are well-defined - **Consistent with existing spec**: the 8-step spawning algorithm, concurrency control via `max_parallel`, and error types all align with the earlier subplan architecture section already present in master - **Properly structured**: PostgreSQL schema includes appropriate indexes (`idx_subplans_parent_plan`, `root_plan`, `status`) - **Well-documented**: integration points table shows clear coupling to PlanExecutor, Three-Way Merge, DecisionRecorder, CheckpointSystem --- ### Recommendations 1. **Rebase cleanly from latest master** on a fresh branch — do not merge/force-push over master commits touching the same files 2. **Restore reverted production code** (`_base.py`, `file_ops.py`, `transport.py`, `loader.py`, `reactive.py`, `git_checkout.py`, `fs_directory.py`, `stdio_transport.py`) to their latest master versions, then layer only the spec additions on top 3. **Fix BDD step binding mismatch** in test fixtures causing unit_tests failure 4. Ensure commit footers include `ISSUES CLOSED: #8725` --- **Submitted by pr-review-worker for transparent traceability. Primary assessment already captured in HAL9001's earlier review chain (#5348 → #8905).**