cleveragents/cleveragents-core

Fork 3

feat(db): implement missing `correction_attempts` table per spec DDL #920

New issue

Closed

opened 2026-03-14 00:05:18 +00:00 by freemo · 2 comments

freemo commented

2026-03-14 00:05:18 +00:00

Owner

Background

The specification (lines 45486-45499) defines a correction_attempts table for tracking decision correction workflows (revert/append modes). This entire table has no corresponding SQLAlchemy model in the codebase.

Spec DDL (line 45486)

CREATE TABLE IF NOT EXISTS correction_attempts (
    correction_attempt_id TEXT PRIMARY KEY,  -- ULID
    plan_id              TEXT NOT NULL REFERENCES plans(plan_id),
    original_decision_id TEXT NOT NULL REFERENCES decisions(decision_id),
    new_decision_id      TEXT REFERENCES decisions(decision_id),
    mode                 TEXT NOT NULL,  -- 'revert' | 'append'
    guidance             TEXT NOT NULL,
    archived_artifacts_path TEXT,
    state                TEXT NOT NULL DEFAULT 'pending',
        -- 'pending' | 'executing' | 'complete' | 'failed'
    created_at           TEXT NOT NULL,
    completed_at         TEXT
);

CREATE INDEX idx_corrections_plan ON correction_attempts(plan_id);

This table supports the plan lifecycle correction workflow where a user can revert or amend a decision, with full state tracking (pending → executing → complete/failed).

Acceptance Criteria

CorrectionAttemptModel SQLAlchemy model created in infrastructure/database/models.py
All columns match spec DDL (correction_attempt_id, plan_id, original_decision_id, new_decision_id, mode, guidance, archived_artifacts_path, state, created_at, completed_at)
FK constraints to v3_plans and decisions tables
Index idx_corrections_plan on plan_id
Alembic migration created
Repository/service layer can create and query correction attempts
Existing tests pass

Metadata

Suggested commit message: feat(db): add correction_attempts table per specification DDL
Suggested branch name: feat/correction-attempts-table

Definition of Done

Code merged to main, correction_attempts table exists with all spec-defined columns, indexes, and FK constraints.

## Background The specification (lines 45486-45499) defines a `correction_attempts` table for tracking decision correction workflows (revert/append modes). **This entire table has no corresponding SQLAlchemy model in the codebase.** ### Spec DDL (line 45486) ```sql CREATE TABLE IF NOT EXISTS correction_attempts ( correction_attempt_id TEXT PRIMARY KEY, -- ULID plan_id TEXT NOT NULL REFERENCES plans(plan_id), original_decision_id TEXT NOT NULL REFERENCES decisions(decision_id), new_decision_id TEXT REFERENCES decisions(decision_id), mode TEXT NOT NULL, -- 'revert' | 'append' guidance TEXT NOT NULL, archived_artifacts_path TEXT, state TEXT NOT NULL DEFAULT 'pending', -- 'pending' | 'executing' | 'complete' | 'failed' created_at TEXT NOT NULL, completed_at TEXT ); CREATE INDEX idx_corrections_plan ON correction_attempts(plan_id); ``` This table supports the plan lifecycle correction workflow where a user can revert or amend a decision, with full state tracking (pending → executing → complete/failed). ## Acceptance Criteria - [ ] `CorrectionAttemptModel` SQLAlchemy model created in `infrastructure/database/models.py` - [ ] All columns match spec DDL (correction_attempt_id, plan_id, original_decision_id, new_decision_id, mode, guidance, archived_artifacts_path, state, created_at, completed_at) - [ ] FK constraints to `v3_plans` and `decisions` tables - [ ] Index `idx_corrections_plan` on `plan_id` - [ ] Alembic migration created - [ ] Repository/service layer can create and query correction attempts - [ ] Existing tests pass ## Metadata - **Suggested commit message:** `feat(db): add correction_attempts table per specification DDL` - **Suggested branch name:** `feat/correction-attempts-table` ## Definition of Done Code merged to `main`, `correction_attempts` table exists with all spec-defined columns, indexes, and FK constraints.

freemo added the

labels

2026-03-14 00:06:06 +00:00

freemo added this to the v3.5.0 milestone

2026-03-14 00:06:15 +00:00

freemo commented

2026-03-14 00:06:27 +00:00

Author

Owner

Dependencies:

Related to #921 (plans table schema alignment — correction_attempts references plans and decisions)
Related to #922 (resource/decision schema fixes — decisions table also needs alignment)

**Dependencies:** - Related to #921 (plans table schema alignment — correction_attempts references plans and decisions) - Related to #922 (resource/decision schema fixes — decisions table also needs alignment)

freemo referenced this issue

2026-03-14 00:06:28 +00:00

fix(db): align v3_plans table schema with spec DDL (nullability, defaults, missing columns) #921

CoreRasurae was assigned by freemo

2026-03-14 04:27:12 +00:00

CoreRasurae referenced this issue from a commit

2026-03-24 03:33:13 +00:00

feat(db): add correction_attempts table per specification DDL

~~CoreRasurae referenced this issue 2026-03-24 03:33:38 +00:00~~

feat(db): add correction_attempts table per specification DDL #1145

CoreRasurae commented

2026-03-24 03:44:42 +00:00

Member

Implementation Notes

Summary

Added CorrectionAttemptModel SQLAlchemy model with all 10 spec-defined columns, FK constraints to v3_plans and decisions tables, idx_corrections_plan index, Alembic migration, and full repository CRUD layer.

Files Modified/Created (10 files)

Domain: CorrectionAttemptRecord model, CorrectionAttemptState enum in correction.py
Infrastructure: CorrectionAttemptModel in models.py, CorrectionAttemptRepository in repositories.py, wired in unit_of_work.py
Alembic: m8_001_correction_attempts_table.py
Tests: correction_attempt_persistence.feature (13 Behave scenarios), Robot integration tests

Design Decisions

New domain model: Created CorrectionAttemptRecord as a separate Pydantic model rather than reusing existing CorrectionAttempt class — spec DDL table has fundamentally different semantics.
Separate enum: CorrectionAttemptState matches exactly the spec DDL states (pending, executing, complete, failed) rather than reusing CorrectionStatus.
FK ondelete: CASCADE for plan_id and original_decision_id, SET NULL for new_decision_id (optional).
Session-factory pattern: Repository follows ADR-007 with retry logic.

Quality Gate Results

Stage	Result
lint	PASSED
typecheck	PASSED (0 errors)
unit_tests	PASSED (464 features, 12,303 scenarios)
integration_tests	PASSED (3,278 tests)
coverage	98% (exceeds 97% threshold)

PR

PR #1145 — feat/correction-attempts-table → master
Commit: 1022b71e

## Implementation Notes ### Summary Added `CorrectionAttemptModel` SQLAlchemy model with all 10 spec-defined columns, FK constraints to `v3_plans` and `decisions` tables, `idx_corrections_plan` index, Alembic migration, and full repository CRUD layer. ### Files Modified/Created (10 files) - **Domain**: `CorrectionAttemptRecord` model, `CorrectionAttemptState` enum in `correction.py` - **Infrastructure**: `CorrectionAttemptModel` in `models.py`, `CorrectionAttemptRepository` in `repositories.py`, wired in `unit_of_work.py` - **Alembic**: `m8_001_correction_attempts_table.py` - **Tests**: `correction_attempt_persistence.feature` (13 Behave scenarios), Robot integration tests ### Design Decisions 1. **New domain model**: Created `CorrectionAttemptRecord` as a separate Pydantic model rather than reusing existing `CorrectionAttempt` class — spec DDL table has fundamentally different semantics. 2. **Separate enum**: `CorrectionAttemptState` matches exactly the spec DDL states (`pending`, `executing`, `complete`, `failed`) rather than reusing `CorrectionStatus`. 3. **FK ondelete**: `CASCADE` for `plan_id` and `original_decision_id`, `SET NULL` for `new_decision_id` (optional). 4. **Session-factory pattern**: Repository follows ADR-007 with retry logic. ### Quality Gate Results | Stage | Result | |---|---| | lint | PASSED | | typecheck | PASSED (0 errors) | | unit_tests | PASSED (464 features, 12,303 scenarios) | | integration_tests | PASSED (3,278 tests) | | coverage | **98%** (exceeds 97% threshold) | ### PR PR #1145 — `feat/correction-attempts-table` → `master` Commit: `1022b71e`

CoreRasurae added

and removed

labels

2026-03-24 03:45:06 +00:00

CoreRasurae referenced this issue from a pull request that will close it,

2026-03-24 03:46:44 +00:00

feat(db): add correction_attempts table per specification DDL #1145

CoreRasurae referenced this issue from a commit