UAT: `decisions` table column names use `_json` suffix — deviates from spec DDL column names #2950

New issue

Open

opened 2026-04-05 02:55:10 +00:00 by freemo · 1 comment

freemo commented

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

Owner

Metadata

Branch: fix/m4-decisions-table-column-names
Commit Message: fix(database): align decisions table column names with spec DDL
Milestone: v3.7.0
Parent Epic: #394

Background

During UAT testing of the Decision Tree & Corrections feature area, the decisions table schema was compared against the specification DDL. The migration and ORM model use _json suffixes on several columns that the spec defines without that suffix.

The _json suffix is an internal ORM naming convention but deviates from the spec's canonical column names (spec DDL at docs/specification.md line 45599–45620). This creates a discrepancy between the spec DDL and the actual database schema, which would affect any direct SQL queries, database inspection tools, or future migrations that reference the spec DDL.

Additionally, the migration includes actor_reasoning and correction_reason columns that are absent from the spec DDL at line 45599 (though present in the earlier spec DDL at line 18753). The spec DDL at line 45599 also omits downstream_decision_ids entirely.

Expected Behaviour (from spec)

The spec DDL (docs/specification.md line 45608) defines:

CREATE TABLE decisions (
    ...
    alternatives_considered TEXT,    -- JSON array of alternative options
    ...
    context_snapshot TEXT NOT NULL,  -- JSON: hot_context_hash, relevant_resources, actor_state_ref
    ...
    downstream_plan_ids TEXT,        -- JSON array of child plan ULIDs
    artifacts_produced TEXT,         -- JSON array of artifact references
    ...
);

Actual Behaviour

The migration (alembic/versions/m4_001_decision_tables.py) and ORM model (src/cleveragents/infrastructure/database/models.py) use:

Actual column name	Spec column name
`alternatives_considered_json`	`alternatives_considered`
`context_snapshot_json`	`context_snapshot`
`downstream_decision_ids_json`	(not present in spec DDL at line 45599 — extra column)
`downstream_plan_ids_json`	`downstream_plan_ids`
`artifacts_produced_json`	`artifacts_produced`

Affected Files

alembic/versions/m4_001_decision_tables.py — column definitions in migration
src/cleveragents/infrastructure/database/models.py — DecisionModel class (line 2666)
docs/specification.md — line 45599 (canonical DDL, source of truth)

Subtasks

Audit all _json-suffixed columns in alembic/versions/m4_001_decision_tables.py against spec DDL at line 45599
Audit DecisionModel class in src/cleveragents/infrastructure/database/models.py against spec DDL
Determine canonical disposition of downstream_decision_ids (extra column not in spec DDL at line 45599)
Determine canonical disposition of actor_reasoning and correction_reason (present in spec DDL at line 18753 but absent at line 45599)
Write a new Alembic migration to rename the _json-suffixed columns to their spec-canonical names
Update DecisionModel ORM attributes to match the renamed columns
Update all references to the renamed columns throughout the codebase (queries, repositories, services)
Update or add Behave unit tests in features/ to cover the corrected column names
Verify no direct SQL strings reference the old _json-suffixed column names

Definition of Done

All _json-suffixed column names in the decisions table are renamed to match the spec DDL at docs/specification.md line 45599
A new Alembic migration exists that performs the column renames cleanly
DecisionModel ORM class attributes reflect the spec-canonical column names
All codebase references to the old column names are updated
The disposition of downstream_decision_ids, actor_reasoning, and correction_reason is resolved and documented (either removed or reconciled with the spec)
All nox stages pass
Coverage >= 97%

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

## Metadata - **Branch**: `fix/m4-decisions-table-column-names` - **Commit Message**: `fix(database): align decisions table column names with spec DDL` - **Milestone**: v3.7.0 - **Parent Epic**: #394 ## Background During UAT testing of the Decision Tree & Corrections feature area, the `decisions` table schema was compared against the specification DDL. The migration and ORM model use `_json` suffixes on several columns that the spec defines without that suffix. The `_json` suffix is an internal ORM naming convention but deviates from the spec's canonical column names (spec DDL at `docs/specification.md` line 45599–45620). This creates a discrepancy between the spec DDL and the actual database schema, which would affect any direct SQL queries, database inspection tools, or future migrations that reference the spec DDL. Additionally, the migration includes `actor_reasoning` and `correction_reason` columns that are absent from the spec DDL at line 45599 (though present in the earlier spec DDL at line 18753). The spec DDL at line 45599 also omits `downstream_decision_ids` entirely. ## Expected Behaviour (from spec) The spec DDL (`docs/specification.md` line 45608) defines: ```sql CREATE TABLE decisions ( ... alternatives_considered TEXT, -- JSON array of alternative options ... context_snapshot TEXT NOT NULL, -- JSON: hot_context_hash, relevant_resources, actor_state_ref ... downstream_plan_ids TEXT, -- JSON array of child plan ULIDs artifacts_produced TEXT, -- JSON array of artifact references ... ); ``` ## Actual Behaviour The migration (`alembic/versions/m4_001_decision_tables.py`) and ORM model (`src/cleveragents/infrastructure/database/models.py`) use: | Actual column name | Spec column name | |---|---| | `alternatives_considered_json` | `alternatives_considered` | | `context_snapshot_json` | `context_snapshot` | | `downstream_decision_ids_json` | *(not present in spec DDL at line 45599 — extra column)* | | `downstream_plan_ids_json` | `downstream_plan_ids` | | `artifacts_produced_json` | `artifacts_produced` | ## Affected Files - `alembic/versions/m4_001_decision_tables.py` — column definitions in migration - `src/cleveragents/infrastructure/database/models.py` — `DecisionModel` class (line 2666) - `docs/specification.md` — line 45599 (canonical DDL, source of truth) ## Subtasks - [ ] Audit all `_json`-suffixed columns in `alembic/versions/m4_001_decision_tables.py` against spec DDL at line 45599 - [ ] Audit `DecisionModel` class in `src/cleveragents/infrastructure/database/models.py` against spec DDL - [ ] Determine canonical disposition of `downstream_decision_ids` (extra column not in spec DDL at line 45599) - [ ] Determine canonical disposition of `actor_reasoning` and `correction_reason` (present in spec DDL at line 18753 but absent at line 45599) - [ ] Write a new Alembic migration to rename the `_json`-suffixed columns to their spec-canonical names - [ ] Update `DecisionModel` ORM attributes to match the renamed columns - [ ] Update all references to the renamed columns throughout the codebase (queries, repositories, services) - [ ] Update or add Behave unit tests in `features/` to cover the corrected column names - [ ] Verify no direct SQL strings reference the old `_json`-suffixed column names ## Definition of Done - [ ] All `_json`-suffixed column names in the `decisions` table are renamed to match the spec DDL at `docs/specification.md` line 45599 - [ ] A new Alembic migration exists that performs the column renames cleanly - [ ] `DecisionModel` ORM class attributes reflect the spec-canonical column names - [ ] All codebase references to the old column names are updated - [ ] The disposition of `downstream_decision_ids`, `actor_reasoning`, and `correction_reason` is resolved and documented (either removed or reconciled with the spec) - [ ] 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:55:16 +00:00

freemo added a new dependency

2026-04-05 02:55:26 +00:00

#394 Epic: Decision Framework

freemo commented

2026-04-05 03:01:12 +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

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

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