UAT: All 4 built-in database resource types (`postgres`, `mysql`, `sqlite`, `duckdb`) have `checkpoint: false` capability — spec requires `checkpointable: true` #6825

New issue

Open

opened 2026-04-10 02:21:12 +00:00 by HAL9000 · 0 comments

HAL9000 commented

2026-04-10 02:21:12 +00:00

Owner

Background and Context

The specification (§Database Resource Types, §Custom Resource Type YAML definition) requires that database resource types support checkpointing. The spec states:

All 4 database types use transaction_rollback sandbox strategy
The local/database custom type YAML example explicitly shows checkpointable: true
The spec's sandbox strategy table (§Sandbox Implementation Strategies) states that transaction_rollback "Wraps operations in a database transaction. Rolled back on failure, committed on apply."
The spec's full sandbox strategy table (§Sandbox Isolation) confirms transaction_rollback supports ROLLBACK as its rollback mechanism
The DatabaseResourceHandler in the codebase correctly implements create_checkpoint() and rollback_to() methods

Current Behavior

In src/cleveragents/resource/handlers/database.py, all four built-in database type definitions have:

"capabilities": {
    "read": True,
    "write": True,
    "sandbox": True,
    "checkpoint": False,   # ← BUG: should be True per spec
},

This applies to POSTGRES_TYPE_DEF, MYSQL_TYPE_DEF, SQLITE_TYPE_DEF, and DUCKDB_TYPE_DEF.

This means that even though DatabaseResourceHandler.create_checkpoint() and DatabaseResourceHandler.rollback_to() are fully implemented (per issue #1241), the resource type metadata incorrectly advertises the resources as non-checkpointable. Any system component checking capabilities["checkpoint"] (e.g., the checkpoint service, plan executor, automation profile enforcement) will incorrectly conclude that database resources cannot be checkpointed and skip calling the handler's checkpoint methods.

Expected Behavior (per spec)

All four built-in database types should declare "checkpoint": True in their capabilities dict, consistent with:

The spec's YAML example showing checkpointable: true for the database type
The implemented create_checkpoint and rollback_to methods in DatabaseResourceHandler
The transaction_rollback sandbox strategy's inherent support for checkpoints via SAVEPOINTs (SQLite) and content-hash fallback (remote DBs)

Steps to Reproduce

from cleveragents.resource.handlers.database import (
    POSTGRES_TYPE_DEF, MYSQL_TYPE_DEF, SQLITE_TYPE_DEF, DUCKDB_TYPE_DEF
)
for t in [POSTGRES_TYPE_DEF, MYSQL_TYPE_DEF, SQLITE_TYPE_DEF, DUCKDB_TYPE_DEF]:
    print(t['name'], '→ checkpoint:', t['capabilities']['checkpoint'])
# Output (incorrect):
# postgres → checkpoint: False
# mysql    → checkpoint: False
# sqlite   → checkpoint: False
# duckdb   → checkpoint: False

Code Analysis

src/cleveragents/resource/handlers/database.py lines 63–268: POSTGRES_TYPE_DEF, MYSQL_TYPE_DEF, SQLITE_TYPE_DEF, DUCKDB_TYPE_DEF all set "checkpoint": False
src/cleveragents/resource/handlers/database.py lines 829–983: DatabaseResourceHandler.create_checkpoint() and rollback_to() are fully implemented for SQLite (SAVEPOINT/ROLLBACK TO SAVEPOINT) and have a content-hash fallback for remote databases
Fix is straightforward: change "checkpoint": False → "checkpoint": True in all four type definition dicts

Acceptance Criteria

All 4 database type definitions have "checkpoint": True in their capabilities
agents resource type show sqlite (and postgres/mysql/duckdb) reports checkpoint as supported
The checkpoint capability is reflected correctly when resources are added via agents resource add
Tests (Behave) cover the checkpoint capability flag in database type definitions
Tests (Robot) cover checkpoint capability in database resource integration

Subtasks

Change "checkpoint": False → "checkpoint": True in POSTGRES_TYPE_DEF, MYSQL_TYPE_DEF, SQLITE_TYPE_DEF, DUCKDB_TYPE_DEF in database.py
Verify existing Behave tests still pass; update/add tests that assert checkpoint=True
Add Robot integration test for database type checkpoint capability
Verify coverage >=97%
Run nox (all sessions), fix any errors

Definition of Done

This issue is complete when:

All subtasks are completed.
A commit is created and pushed to the correct branch.
A pull request is submitted, reviewed, and merged.

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

## Background and Context The specification (§Database Resource Types, §Custom Resource Type YAML definition) requires that database resource types support checkpointing. The spec states: 1. All 4 database types use `transaction_rollback` sandbox strategy 2. The `local/database` custom type YAML example explicitly shows `checkpointable: true` 3. The spec's sandbox strategy table (§Sandbox Implementation Strategies) states that `transaction_rollback` "Wraps operations in a database transaction. Rolled back on failure, committed on apply." 4. The spec's full sandbox strategy table (§Sandbox Isolation) confirms `transaction_rollback` supports `ROLLBACK` as its rollback mechanism 5. The `DatabaseResourceHandler` in the codebase correctly implements `create_checkpoint()` and `rollback_to()` methods ## Current Behavior In `src/cleveragents/resource/handlers/database.py`, all four built-in database type definitions have: ```python "capabilities": { "read": True, "write": True, "sandbox": True, "checkpoint": False, # ← BUG: should be True per spec }, ``` This applies to `POSTGRES_TYPE_DEF`, `MYSQL_TYPE_DEF`, `SQLITE_TYPE_DEF`, and `DUCKDB_TYPE_DEF`. This means that even though `DatabaseResourceHandler.create_checkpoint()` and `DatabaseResourceHandler.rollback_to()` are fully implemented (per issue #1241), the resource type metadata incorrectly advertises the resources as non-checkpointable. Any system component checking `capabilities["checkpoint"]` (e.g., the checkpoint service, plan executor, automation profile enforcement) will incorrectly conclude that database resources cannot be checkpointed and skip calling the handler's checkpoint methods. ## Expected Behavior (per spec) All four built-in database types should declare `"checkpoint": True` in their capabilities dict, consistent with: - The spec's YAML example showing `checkpointable: true` for the database type - The implemented `create_checkpoint` and `rollback_to` methods in `DatabaseResourceHandler` - The `transaction_rollback` sandbox strategy's inherent support for checkpoints via SAVEPOINTs (SQLite) and content-hash fallback (remote DBs) ## Steps to Reproduce ```python from cleveragents.resource.handlers.database import ( POSTGRES_TYPE_DEF, MYSQL_TYPE_DEF, SQLITE_TYPE_DEF, DUCKDB_TYPE_DEF ) for t in [POSTGRES_TYPE_DEF, MYSQL_TYPE_DEF, SQLITE_TYPE_DEF, DUCKDB_TYPE_DEF]: print(t['name'], '→ checkpoint:', t['capabilities']['checkpoint']) # Output (incorrect): # postgres → checkpoint: False # mysql → checkpoint: False # sqlite → checkpoint: False # duckdb → checkpoint: False ``` ## Code Analysis - `src/cleveragents/resource/handlers/database.py` lines 63–268: `POSTGRES_TYPE_DEF`, `MYSQL_TYPE_DEF`, `SQLITE_TYPE_DEF`, `DUCKDB_TYPE_DEF` all set `"checkpoint": False` - `src/cleveragents/resource/handlers/database.py` lines 829–983: `DatabaseResourceHandler.create_checkpoint()` and `rollback_to()` are fully implemented for SQLite (SAVEPOINT/ROLLBACK TO SAVEPOINT) and have a content-hash fallback for remote databases - Fix is straightforward: change `"checkpoint": False` → `"checkpoint": True` in all four type definition dicts ## Acceptance Criteria - All 4 database type definitions have `"checkpoint": True` in their capabilities - `agents resource type show sqlite` (and postgres/mysql/duckdb) reports checkpoint as supported - The checkpoint capability is reflected correctly when resources are added via `agents resource add` - Tests (Behave) cover the checkpoint capability flag in database type definitions - Tests (Robot) cover checkpoint capability in database resource integration ## Subtasks - [ ] Change `"checkpoint": False` → `"checkpoint": True` in `POSTGRES_TYPE_DEF`, `MYSQL_TYPE_DEF`, `SQLITE_TYPE_DEF`, `DUCKDB_TYPE_DEF` in `database.py` - [ ] Verify existing Behave tests still pass; update/add tests that assert `checkpoint=True` - [ ] Add Robot integration test for database type checkpoint capability - [ ] Verify coverage >=97% - [ ] Run `nox` (all sessions), fix any errors ## Definition of Done This issue is complete when: - All subtasks are completed. - A commit is created and pushed to the correct branch. - A pull request is submitted, reviewed, and merged. --- **Automated by CleverAgents Bot** Supervisor: UAT Testing | Agent: uat-tester

HAL9000 added the

labels

2026-04-10 02:21:28 +00:00

HAL9000 added this to the v3.4.0 milestone

2026-04-10 02:21:31 +00:00

HAL9000 referenced this issue