UAT: Spec defines two conflicting DDL schemas for `correction_attempts` table — `attempt_id` vs `correction_attempt_id` primary key #2963

New issue

Open

opened 2026-04-05 02:58:09 +00:00 by freemo · 1 comment

freemo commented

2026-04-05 02:58:09 +00:00

Owner

Metadata

Branch: fix/spec-correction-attempts-ddl-conflict
Commit Message: docs(spec): remove conflicting DDL definition for correction_attempts table
Milestone: v3.7.0
Parent Epic: #394

Background and context

During UAT testing of the Decision Tree & Corrections feature area, two conflicting DDL definitions for the correction_attempts table were discovered in docs/specification.md. The migration implements one of them, but the spec is internally inconsistent, violating the principle that the specification is the authoritative source of truth.

The spec also references correction_attempt_id in the data storage table (line 45538): "ULID (correction_attempt_id)" — confirming Definition 2 is the intended canonical schema.

Current behavior (for bugs)

The spec contains two conflicting DDL definitions for correction_attempts:

Definition 1 (spec lines 18793–18807) — stale/incorrect:

CREATE TABLE correction_attempts (
    attempt_id TEXT PRIMARY KEY,       -- ULID
    plan_id TEXT NOT NULL,
    original_decision_id TEXT NOT NULL,
    new_decision_id TEXT,
    original_subtree_snapshot TEXT,    -- Reference to archived state
    correction_reason TEXT,
    status TEXT NOT NULL,              -- 'pending', 'executing', 'completed', 'failed'
    created_at TEXT NOT NULL,
    completed_at TEXT,
    FOREIGN KEY (plan_id) REFERENCES plans(plan_id),
    FOREIGN KEY (original_decision_id) REFERENCES decisions(decision_id),
    FOREIGN KEY (new_decision_id) REFERENCES decisions(decision_id)
);

Definition 2 (spec lines 45681–45694) — canonical/correct:

CREATE TABLE 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,                  -- filesystem path to archived originals
    state TEXT NOT NULL DEFAULT 'pending',         -- pending|executing|complete|failed
    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f', 'now')),
    completed_at TEXT
);
CREATE INDEX idx_corrections_plan ON correction_attempts(plan_id);

Key differences between the two definitions:

Primary key: attempt_id (Definition 1) vs correction_attempt_id (Definition 2)
Columns unique to Definition 1: original_subtree_snapshot, correction_reason, status
Columns unique to Definition 2: mode, guidance, archived_artifacts_path, state
Status/state values: Definition 1 uses 'completed'; Definition 2 uses 'complete'

The migration (alembic/versions/m8_001_correction_attempts_table.py) implements Definition 2 (with correction_attempt_id as primary key), which aligns with the CorrectionAttemptRecord domain model and CorrectionAttemptRepository. Definition 1 at line 18793 is a stale artefact that contradicts the implementation and the rest of the spec.

Affected files:

docs/specification.md — lines 18793–18807 (conflicting Definition 1, must be removed/replaced)
docs/specification.md — lines 45681–45694 (canonical Definition 2, correct)
alembic/versions/m8_001_correction_attempts_table.py — implements Definition 2 (correct)

Expected behavior

docs/specification.md should contain exactly one DDL definition for the correction_attempts table, matching Definition 2 (with correction_attempt_id as primary key, mode, guidance, archived_artifacts_path, state columns, and status value 'complete'). The spec must be internally consistent and agree with the migration and domain model.

Acceptance criteria

docs/specification.md contains exactly one DDL definition for correction_attempts
The retained definition uses correction_attempt_id TEXT PRIMARY KEY
The retained definition includes mode, guidance, archived_artifacts_path, and state columns
The stale Definition 1 block (lines 18793–18807) is removed or replaced with the canonical schema
No other references to attempt_id (as a primary key for this table) remain in the spec
The spec's DDL matches alembic/versions/m8_001_correction_attempts_table.py exactly

Subtasks

Locate and remove the stale DDL block at docs/specification.md lines 18793–18807
Verify no other stale references to attempt_id as a primary key for correction_attempts exist elsewhere in the spec
Confirm the canonical DDL at lines 45681–45694 is consistent with the migration and domain model
Run nox (all default sessions), fix any errors

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.
All nox stages pass.
Coverage >= 97%.

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

## Metadata - **Branch**: `fix/spec-correction-attempts-ddl-conflict` - **Commit Message**: `docs(spec): remove conflicting DDL definition for correction_attempts table` - **Milestone**: v3.7.0 - **Parent Epic**: #394 ## Background and context During UAT testing of the Decision Tree & Corrections feature area, two conflicting DDL definitions for the `correction_attempts` table were discovered in `docs/specification.md`. The migration implements one of them, but the spec is internally inconsistent, violating the principle that the specification is the authoritative source of truth. The spec also references `correction_attempt_id` in the data storage table (line 45538): "ULID (`correction_attempt_id`)" — confirming Definition 2 is the intended canonical schema. ## Current behavior (for bugs) The spec contains two conflicting DDL definitions for `correction_attempts`: **Definition 1 (spec lines 18793–18807) — stale/incorrect:** ```sql CREATE TABLE correction_attempts ( attempt_id TEXT PRIMARY KEY, -- ULID plan_id TEXT NOT NULL, original_decision_id TEXT NOT NULL, new_decision_id TEXT, original_subtree_snapshot TEXT, -- Reference to archived state correction_reason TEXT, status TEXT NOT NULL, -- 'pending', 'executing', 'completed', 'failed' created_at TEXT NOT NULL, completed_at TEXT, FOREIGN KEY (plan_id) REFERENCES plans(plan_id), FOREIGN KEY (original_decision_id) REFERENCES decisions(decision_id), FOREIGN KEY (new_decision_id) REFERENCES decisions(decision_id) ); ``` **Definition 2 (spec lines 45681–45694) — canonical/correct:** ```sql CREATE TABLE 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, -- filesystem path to archived originals state TEXT NOT NULL DEFAULT 'pending', -- pending|executing|complete|failed created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f', 'now')), completed_at TEXT ); CREATE INDEX idx_corrections_plan ON correction_attempts(plan_id); ``` Key differences between the two definitions: 1. **Primary key**: `attempt_id` (Definition 1) vs `correction_attempt_id` (Definition 2) 2. **Columns unique to Definition 1**: `original_subtree_snapshot`, `correction_reason`, `status` 3. **Columns unique to Definition 2**: `mode`, `guidance`, `archived_artifacts_path`, `state` 4. **Status/state values**: Definition 1 uses `'completed'`; Definition 2 uses `'complete'` The migration (`alembic/versions/m8_001_correction_attempts_table.py`) implements Definition 2 (with `correction_attempt_id` as primary key), which aligns with the `CorrectionAttemptRecord` domain model and `CorrectionAttemptRepository`. Definition 1 at line 18793 is a stale artefact that contradicts the implementation and the rest of the spec. **Affected files:** - `docs/specification.md` — lines 18793–18807 (conflicting Definition 1, must be removed/replaced) - `docs/specification.md` — lines 45681–45694 (canonical Definition 2, correct) - `alembic/versions/m8_001_correction_attempts_table.py` — implements Definition 2 (correct) ## Expected behavior `docs/specification.md` should contain exactly one DDL definition for the `correction_attempts` table, matching Definition 2 (with `correction_attempt_id` as primary key, `mode`, `guidance`, `archived_artifacts_path`, `state` columns, and status value `'complete'`). The spec must be internally consistent and agree with the migration and domain model. ## Acceptance criteria - [ ] `docs/specification.md` contains exactly one DDL definition for `correction_attempts` - [ ] The retained definition uses `correction_attempt_id TEXT PRIMARY KEY` - [ ] The retained definition includes `mode`, `guidance`, `archived_artifacts_path`, and `state` columns - [ ] The stale Definition 1 block (lines 18793–18807) is removed or replaced with the canonical schema - [ ] No other references to `attempt_id` (as a primary key for this table) remain in the spec - [ ] The spec's DDL matches `alembic/versions/m8_001_correction_attempts_table.py` exactly ## Subtasks - [ ] Locate and remove the stale DDL block at `docs/specification.md` lines 18793–18807 - [ ] Verify no other stale references to `attempt_id` as a primary key for `correction_attempts` exist elsewhere in the spec - [ ] Confirm the canonical DDL at lines 45681–45694 is consistent with the migration and domain model - [ ] Run `nox` (all default sessions), fix any errors ## 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. - All nox stages pass. - Coverage >= 97%. --- **Automated by CleverAgents Bot** Supervisor: UAT Testing | Agent: ca-uat-tester

freemo added this to the v3.7.0 milestone

2026-04-05 02:58:17 +00:00

freemo added a new dependency

2026-04-05 02:58:32 +00:00

#394 Epic: Decision Framework

freemo commented

2026-04-05 03:00:30 +00:00

Author

Owner

Issue triaged by project owner:

State: Verified
Priority: Confirmed
MoSCoW: Should Have

Valid finding verified during batch triage.

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

Issue triaged by project owner: - **State**: Verified - **Priority**: Confirmed - **MoSCoW**: Should Have Valid finding verified during batch triage. --- **Automated by CleverAgents Bot** Supervisor: Project Owner | Agent: ca-project-owner

freemo referenced this issue from a commit

2026-04-05 04:10:04 +00:00

feat(autonomy): automation profile resolution precedence correct (#1196)

freemo referenced this issue

2026-04-05 06:15:22 +00:00

[CA-AUTO] UAT Pool Supervisor — v3.2.0–v3.7.0 — Session Tracker #3094