UAT: TransactionSandbox only implements SQLite backend — PostgreSQL, MySQL, DuckDB sandbox isolation not functional #8010

New issue

Open

opened 2026-04-12 19:34:02 +00:00 by HAL9000 · 0 comments

HAL9000 commented

2026-04-12 19:34:02 +00:00

Owner

Description

The specification (§24490–24499, line 43862) defines four database resource types — postgres, mysql, sqlite, and duckdb — all using the transaction_rollback sandbox strategy. The spec further states that for server mode "the infrastructure layer swaps in PostgreSQL via the same SQLAlchemy interface."

TransactionSandbox (src/cleveragents/infrastructure/sandbox/transaction_sandbox.py) unconditionally calls sqlite3.connect(original_path) in its create() method. There is no backend dispatch: passing a PostgreSQL URL (e.g. postgresql://localhost/testdb) causes a SandboxCreationError with unable to open database file because sqlite3 tries to treat the URL as a file path.

Reproduction Steps

from cleveragents.infrastructure.sandbox.transaction_sandbox import TransactionSandbox
sb = TransactionSandbox(resource_id='pg-res-1', original_path='postgresql://localhost/testdb')
sb.create('plan-test')
# Raises: SandboxCreationError: Failed to open database for resource pg-res-1: unable to open database file

Expected Behaviour

TransactionSandbox.create() should dispatch to the appropriate database driver based on original_path:

SQLite paths → sqlite3.connect()
postgresql:// URLs → psycopg2 / psycopg connection with BEGIN
mysql:// URLs → pymysql / mysql-connector-python connection with BEGIN
duckdb:// or file paths → duckdb.connect() with transaction

All four backends should support execute(), commit(), and rollback() through the TransactionSandbox protocol.

Actual Behaviour

All non-SQLite database resource types raise SandboxCreationError immediately on create(). PostgreSQL, MySQL, and DuckDB resources cannot be sandboxed, violating the spec's transaction_rollback contract for those types.

Evidence

src/cleveragents/infrastructure/sandbox/transaction_sandbox.py line 136: self._connection = sqlite3.connect(self._original_path, ...)
No if "postgresql" in self._original_path or equivalent dispatch exists in the file (verified by grep)
Module docstring acknowledges: "PostgreSQL and MySQL backends are stubs that raise NotImplementedError" — but no NotImplementedError is actually raised; a confusing SandboxCreationError is raised instead
pyproject.toml lists no psycopg2, pymysql, or duckdb dependencies
features/database_resources.feature tests sandbox only with SQLite (no postgres/mysql/duckdb sandbox isolation scenarios)

Impact

Any user attempting to add a postgres or mysql resource and run a plan will experience immediate sandbox creation failure. The spec's guarantee of transaction isolation for networked databases is completely unimplemented.

Automated by CleverAgents Bot
Supervisor: UAT Testing | Agent: uat-tester

## Description The specification (§24490–24499, line 43862) defines four database resource types — `postgres`, `mysql`, `sqlite`, and `duckdb` — all using the `transaction_rollback` sandbox strategy. The spec further states that for server mode "the infrastructure layer swaps in PostgreSQL via the same SQLAlchemy interface." `TransactionSandbox` (`src/cleveragents/infrastructure/sandbox/transaction_sandbox.py`) unconditionally calls `sqlite3.connect(original_path)` in its `create()` method. There is no backend dispatch: passing a PostgreSQL URL (e.g. `postgresql://localhost/testdb`) causes a `SandboxCreationError` with `unable to open database file` because `sqlite3` tries to treat the URL as a file path. ## Reproduction Steps ```python from cleveragents.infrastructure.sandbox.transaction_sandbox import TransactionSandbox sb = TransactionSandbox(resource_id='pg-res-1', original_path='postgresql://localhost/testdb') sb.create('plan-test') # Raises: SandboxCreationError: Failed to open database for resource pg-res-1: unable to open database file ``` ## Expected Behaviour `TransactionSandbox.create()` should dispatch to the appropriate database driver based on `original_path`: - SQLite paths → `sqlite3.connect()` - `postgresql://` URLs → `psycopg2` / `psycopg` connection with `BEGIN` - `mysql://` URLs → `pymysql` / `mysql-connector-python` connection with `BEGIN` - `duckdb://` or file paths → `duckdb.connect()` with transaction All four backends should support `execute()`, `commit()`, and `rollback()` through the `TransactionSandbox` protocol. ## Actual Behaviour All non-SQLite database resource types raise `SandboxCreationError` immediately on `create()`. PostgreSQL, MySQL, and DuckDB resources cannot be sandboxed, violating the spec's `transaction_rollback` contract for those types. ## Evidence - `src/cleveragents/infrastructure/sandbox/transaction_sandbox.py` line 136: `self._connection = sqlite3.connect(self._original_path, ...)` - No `if "postgresql" in self._original_path` or equivalent dispatch exists in the file (verified by grep) - Module docstring acknowledges: "PostgreSQL and MySQL backends are stubs that raise `NotImplementedError`" — but no `NotImplementedError` is actually raised; a confusing `SandboxCreationError` is raised instead - `pyproject.toml` lists no `psycopg2`, `pymysql`, or `duckdb` dependencies - `features/database_resources.feature` tests sandbox only with SQLite (no postgres/mysql/duckdb sandbox isolation scenarios) ## Impact Any user attempting to add a `postgres` or `mysql` resource and run a plan will experience immediate sandbox creation failure. The spec's guarantee of transaction isolation for networked databases is completely unimplemented. --- **Automated by CleverAgents Bot** Supervisor: UAT Testing | Agent: uat-tester

HAL9000 referenced this issue

2026-04-14 07:34:27 +00:00

test(infra): add stub keyword guard for placeholder detection #9101