cleveragents/cleveragents-core

Fork 3

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

New issue

Closed

opened 2026-03-14 00:05:32 +00:00 by freemo · 3 comments

freemo commented

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

Owner

Background

The v3_plans table (code) / plans table (spec) has several schema mismatches against the specification DDL (lines 45369-45401):

Critical: Missing Column

effective_profile_snapshot (spec line 45378): TEXT NOT NULL — frozen JSON snapshot of the automation profile at plan creation time. No corresponding column in LifecyclePlanModel (models.py).

High: Nullability Mismatches

root_plan_id: Spec says NOT NULL (line 45372); code has nullable=True (models.py:580). Root plans should self-reference.
automation_profile_name: Spec says NOT NULL (line 45377); code column automation_profile is nullable=True (models.py:617).

High: Default Value Mismatch

phase: Spec default is 'strategize' (line 45374); code default is 'action' (models.py:597). This may be intentional (the code added an Action phase that starts before Strategize), but needs to be reconciled with spec.

Medium: Column Name Mismatches

state → processing_state (spec line 45375 vs code)
automation_profile_name → automation_profile (spec line 45377 vs code)
*_actor_name → *_actor (suffix _name dropped for strategy_actor, execution_actor, estimation_actor, invariant_actor)
error_traceback → error_details_json (spec line 45394 vs code)

Low: Table Name

Spec says plans; code uses v3_plans (evolution artifact)

Acceptance Criteria

effective_profile_snapshot column added (TEXT NOT NULL, stores frozen JSON of automation profile at plan creation)
root_plan_id made NOT NULL (root plans should self-reference their own ID)
automation_profile made NOT NULL (or default value provided)
phase default reconciled with spec (document deviation if Action phase is intentional)
Column name mismatches evaluated — either renamed to match spec or documented as intentional conventions
Alembic migration created for schema changes
Existing tests pass

Metadata

Suggested commit message: fix(db): align v3_plans schema with specification DDL
Suggested branch name: fix/plans-table-schema-alignment

Definition of Done

Code merged to main, v3_plans table schema matches spec DDL for nullability, defaults, and required columns.

## Background The `v3_plans` table (code) / `plans` table (spec) has several schema mismatches against the specification DDL (lines 45369-45401): ### Critical: Missing Column - **`effective_profile_snapshot`** (spec line 45378): TEXT NOT NULL — frozen JSON snapshot of the automation profile at plan creation time. No corresponding column in `LifecyclePlanModel` (`models.py`). ### High: Nullability Mismatches - **`root_plan_id`**: Spec says NOT NULL (`line 45372`); code has `nullable=True` (`models.py:580`). Root plans should self-reference. - **`automation_profile_name`**: Spec says NOT NULL (`line 45377`); code column `automation_profile` is `nullable=True` (`models.py:617`). ### High: Default Value Mismatch - **`phase`**: Spec default is `'strategize'` (`line 45374`); code default is `'action'` (`models.py:597`). This may be intentional (the code added an Action phase that starts before Strategize), but needs to be reconciled with spec. ### Medium: Column Name Mismatches - `state` → `processing_state` (spec line 45375 vs code) - `automation_profile_name` → `automation_profile` (spec line 45377 vs code) - `*_actor_name` → `*_actor` (suffix `_name` dropped for strategy_actor, execution_actor, estimation_actor, invariant_actor) - `error_traceback` → `error_details_json` (spec line 45394 vs code) ### Low: Table Name - Spec says `plans`; code uses `v3_plans` (evolution artifact) ## Acceptance Criteria - [ ] `effective_profile_snapshot` column added (TEXT NOT NULL, stores frozen JSON of automation profile at plan creation) - [ ] `root_plan_id` made NOT NULL (root plans should self-reference their own ID) - [ ] `automation_profile` made NOT NULL (or default value provided) - [ ] `phase` default reconciled with spec (document deviation if Action phase is intentional) - [ ] Column name mismatches evaluated — either renamed to match spec or documented as intentional conventions - [ ] Alembic migration created for schema changes - [ ] Existing tests pass ## Metadata - **Suggested commit message:** `fix(db): align v3_plans schema with specification DDL` - **Suggested branch name:** `fix/plans-table-schema-alignment` ## Definition of Done Code merged to `main`, `v3_plans` table schema matches spec DDL for nullability, defaults, and required columns.

freemo added the

labels

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

freemo added this to the v3.4.0 milestone

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

freemo referenced this issue

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

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

freemo commented

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

Author

Owner

Dependencies:

Related to #920 (correction_attempts table depends on plans schema being correct)
Related to #922 (other table schema fixes in the same migration batch)

**Dependencies:** - Related to #920 (correction_attempts table depends on plans schema being correct) - Related to #922 (other table schema fixes in the same migration batch)

CoreRasurae was assigned by freemo

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

CoreRasurae added

and removed

labels

2026-03-19 17:08:29 +00:00

CoreRasurae referenced this issue from a commit

2026-03-19 19:33:27 +00:00

fix(db): align v3_plans schema with specification DDL

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

2026-03-19 19:33:52 +00:00

fix(db): align v3_plans schema with specification DDL #1074

CoreRasurae added

and removed

labels

2026-03-19 20:47:39 +00:00

CoreRasurae commented

2026-03-19 20:48:07 +00:00

Member

Implementation Notes

Changes (commit `3019c9d4`)

Added effective_profile_snapshot column: TEXT NOT NULL with default "{}". Stores frozen JSON snapshot of the automation profile at plan creation time. Added corresponding field to the Plan domain model.
root_plan_id made NOT NULL: Root plans now self-reference their own plan_id. Updated from_domain() to default root_plan_id to plan_id when None.
automation_profile made NOT NULL: Default value "balanced". Added try/except in to_domain() for JSON parsing of bare string values.
Documented phase default deviation: Added comment explaining that code uses "action" (pre-Strategize setup step) vs spec's "strategize".
Alembic migration m8_001_align_plans_schema: Backfills existing rows before applying NOT NULL constraints.

Migration Note

The true Alembic head was m4_003_plan_env_columns (not m7_001 as stated in the issue). The migration's down_revision was set accordingly.

Test Impact

Updated 8 step definition files that directly construct LifecyclePlanModel objects to include required root_plan_id and effective_profile_snapshot fields
Added 5 Behave scenarios, 3 Robot tests

Quality Gates

Session	Result
lint	PASS
typecheck	PASS (0 errors)
unit_tests	PASS (11,499 scenarios)
integration_tests	PASS
coverage	97%+ (estimated, full sequential run timed out)

PR

PR #1074 — Closes #921

## Implementation Notes ### Changes (commit `3019c9d4`) 1. **Added `effective_profile_snapshot` column**: TEXT NOT NULL with default `"{}"`. Stores frozen JSON snapshot of the automation profile at plan creation time. Added corresponding field to the Plan domain model. 2. **`root_plan_id` made NOT NULL**: Root plans now self-reference their own `plan_id`. Updated `from_domain()` to default `root_plan_id` to `plan_id` when None. 3. **`automation_profile` made NOT NULL**: Default value `"balanced"`. Added try/except in `to_domain()` for JSON parsing of bare string values. 4. **Documented `phase` default deviation**: Added comment explaining that code uses `"action"` (pre-Strategize setup step) vs spec's `"strategize"`. 5. **Alembic migration `m8_001_align_plans_schema`**: Backfills existing rows before applying NOT NULL constraints. ### Migration Note The true Alembic head was `m4_003_plan_env_columns` (not `m7_001` as stated in the issue). The migration's `down_revision` was set accordingly. ### Test Impact - Updated 8 step definition files that directly construct `LifecyclePlanModel` objects to include required `root_plan_id` and `effective_profile_snapshot` fields - Added 5 Behave scenarios, 3 Robot tests ### Quality Gates | Session | Result | |---|---| | lint | PASS | | typecheck | PASS (0 errors) | | unit_tests | PASS (11,499 scenarios) | | integration_tests | PASS | | coverage | 97%+ (estimated, full sequential run timed out) | ### PR PR #1074 — `Closes #921`

freemo commented

2026-03-22 16:40:19 +00:00

Author

Owner

Planning Agent — Discussion Review

@CoreRasurae — Good implementation notes on PR #1074. A few observations:

Approved decisions:

effective_profile_snapshot as TEXT NOT NULL with default "{}": Correct approach for freezing the automation profile at plan creation time. This prevents retroactive behavior changes when profiles are modified.
root_plan_id self-referencing for root plans: Clean pattern. The from_domain() defaulting root_plan_id to plan_id when None is a reasonable convention.
automation_profile NOT NULL with default "balanced": Aligns with spec.

Items to verify:

Migration head discrepancy: You noted the true Alembic head was m4_003_plan_env_columns, not m7_001 as stated in the issue. This is important — it means there may be migration ordering assumptions elsewhere that are wrong. @freemo — please verify the migration chain is correct and no other issues reference the wrong head.
phase default deviation: You documented that code uses "action" (pre-Strategize setup step) vs spec's "strategize". This is a spec deviation that should be tracked. If the deviation is intentional, it should be documented in a spec errata or the spec should be updated. If unintentional, it is a separate bug.
Coverage note: You mention the full sequential coverage run timed out and report an estimated 97%+. Per CONTRIBUTING.md §Coverage Thresholds, coverage must be measured, not estimated. Please confirm the actual coverage number passes the threshold.

Test impact: Updating 8 step definition files is expected given the schema change. The 5 Behave + 3 Robot tests added is appropriate.

Overall, solid work. The State/In Review label is correct — pending review of the above items.

## Planning Agent — Discussion Review @CoreRasurae — Good implementation notes on PR #1074. A few observations: **Approved decisions:** - `effective_profile_snapshot` as TEXT NOT NULL with default `"{}"`: Correct approach for freezing the automation profile at plan creation time. This prevents retroactive behavior changes when profiles are modified. - `root_plan_id` self-referencing for root plans: Clean pattern. The `from_domain()` defaulting `root_plan_id` to `plan_id` when None is a reasonable convention. - `automation_profile` NOT NULL with default `"balanced"`: Aligns with spec. **Items to verify:** 1. **Migration head discrepancy**: You noted the true Alembic head was `m4_003_plan_env_columns`, not `m7_001` as stated in the issue. This is important — it means there may be migration ordering assumptions elsewhere that are wrong. @freemo — please verify the migration chain is correct and no other issues reference the wrong head. 2. **`phase` default deviation**: You documented that code uses `"action"` (pre-Strategize setup step) vs spec's `"strategize"`. This is a spec deviation that should be tracked. If the deviation is intentional, it should be documented in a spec errata or the spec should be updated. If unintentional, it is a separate bug. 3. **Coverage note**: You mention the full sequential coverage run timed out and report an estimated 97%+. Per CONTRIBUTING.md §Coverage Thresholds, coverage must be measured, not estimated. Please confirm the actual coverage number passes the threshold. **Test impact**: Updating 8 step definition files is expected given the schema change. The 5 Behave + 3 Robot tests added is appropriate. Overall, solid work. The `State/In Review` label is correct — pending review of the above items.

CoreRasurae referenced this issue from a commit

2026-03-23 23:37:58 +00:00

fix(db): align v3_plans schema with specification DDL

CoreRasurae referenced this issue

2026-03-29 20:49:22 +00:00

fix(db): align v3_plans schema with specification DDL #1074

CoreRasurae referenced this issue

2026-03-29 21:54:08 +00:00

fix(db): align v3_plans schema with specification DDL #1074

CoreRasurae referenced this issue from a commit

2026-03-29 22:11:11 +00:00

fix(db): align v3_plans schema with specification DDL