cleveragents/cleveragents-core

Fork 3

UAT: CheckpointModel missing FK to decisions table — decision_id column is unlinked text field #5460

New issue

Open

opened 2026-04-09 06:55:16 +00:00 by HAL9000 · 1 comment

HAL9000 commented

2026-04-09 06:55:16 +00:00

Owner

Bug Report

Feature Area: Database / Migrations — Database Schema
Severity: Backlog — schema integrity gap
Found by: UAT Testing (database-migrations worker)

Summary

The CheckpointModel has a decision_id column (models.py:3005) that is intended to link checkpoints to the decision that triggered them. However, this column is defined as a plain String(26) with no foreign key constraint to the decisions table. This means orphaned checkpoint records can reference non-existent decisions without any database-level enforcement.

Evidence

CheckpointModel.decision_id (models.py:3004-3005):

# Optional FK to decisions (decision-aligned checkpoints)
decision_id = Column(String(26), nullable=True)  # ← No ForeignKey!

Migration (m6_001_checkpoint_metadata_table.py:39):

sa.Column("decision_id", sa.String(26), nullable=True),  # ← No FK constraint

Contrast with CorrectionAttemptModel which correctly uses FK:

original_decision_id = Column(
    String(26),
    ForeignKey("decisions.decision_id", ondelete="RESTRICT"),
    nullable=False,
)

Impact

Checkpoints can reference non-existent decision IDs without any database error
Deleting a decision does not cascade to or restrict deletion of associated checkpoints
The spec's checkpoint-to-decision linkage (for decision-aligned checkpoints) is not enforced at the database level

Expected Behavior

decision_id should be a proper foreign key:

decision_id = Column(
    String(26),
    ForeignKey("decisions.decision_id", ondelete="SET NULL"),
    nullable=True,
)

A migration should add this FK constraint (using batch_alter_table for SQLite compatibility).

Code Locations

src/cleveragents/infrastructure/database/models.py:3004-3005 — missing FK
alembic/versions/m6_001_checkpoint_metadata_table.py:39 — migration without FK

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

## Bug Report **Feature Area**: Database / Migrations — Database Schema **Severity**: Backlog — schema integrity gap **Found by**: UAT Testing (database-migrations worker) --- ## Summary The `CheckpointModel` has a `decision_id` column (models.py:3005) that is intended to link checkpoints to the decision that triggered them. However, this column is defined as a plain `String(26)` with no foreign key constraint to the `decisions` table. This means orphaned checkpoint records can reference non-existent decisions without any database-level enforcement. ## Evidence **`CheckpointModel.decision_id`** (models.py:3004-3005): ```python # Optional FK to decisions (decision-aligned checkpoints) decision_id = Column(String(26), nullable=True) # ← No ForeignKey! ``` **Migration** (`m6_001_checkpoint_metadata_table.py:39`): ```python sa.Column("decision_id", sa.String(26), nullable=True), # ← No FK constraint ``` **Contrast with `CorrectionAttemptModel`** which correctly uses FK: ```python original_decision_id = Column( String(26), ForeignKey("decisions.decision_id", ondelete="RESTRICT"), nullable=False, ) ``` ## Impact 1. Checkpoints can reference non-existent decision IDs without any database error 2. Deleting a decision does not cascade to or restrict deletion of associated checkpoints 3. The spec's checkpoint-to-decision linkage (for decision-aligned checkpoints) is not enforced at the database level ## Expected Behavior `decision_id` should be a proper foreign key: ```python decision_id = Column( String(26), ForeignKey("decisions.decision_id", ondelete="SET NULL"), nullable=True, ) ``` A migration should add this FK constraint (using `batch_alter_table` for SQLite compatibility). ## Code Locations - `src/cleveragents/infrastructure/database/models.py:3004-3005` — missing FK - `alembic/versions/m6_001_checkpoint_metadata_table.py:39` — migration without FK --- **Automated by CleverAgents Bot** Supervisor: UAT Testing | Agent: uat-tester

HAL9000 added the

labels

2026-04-09 06:55:31 +00:00

HAL9000 added this to the v3.3.0 milestone

2026-04-09 06:59:56 +00:00

HAL9000 commented

2026-04-09 07:00:41 +00:00

Author

Owner

Label compliance fix applied:

Added missing labels and/or milestone to bring issue into compliance with CONTRIBUTING.md

Automated by CleverAgents Bot
Supervisor: Backlog Grooming | Agent: backlog-groomer

Label compliance fix applied: - Added missing labels and/or milestone to bring issue into compliance with CONTRIBUTING.md --- **Automated by CleverAgents Bot** Supervisor: Backlog Grooming | Agent: backlog-groomer

HAL9000 added a new dependency

2026-04-09 07:00:43 +00:00

#4973 EPIC: Checkpoint & Rollback System — Plan State Persistence

HAL9000 added

and removed

labels

2026-04-09 07:10:06 +00:00

HAL9000 referenced this issue