feat(db): add spec-aligned action and plan tables with migrations, ORM models, and benchmarks #242

Closed
opened 2026-02-22 23:40:28 +00:00 by freemo · 1 comment
Owner

Metadata

  • Commit: feat(db): add spec-aligned action and plan tables with migrations, ORM models, and benchmarks
  • Branch: feature/m1-db-actions

Subtasks

  • Add Alembic migration skeleton with explicit down_revision dependency and naming conventions for indexes/constraints.
  • Create actions table with namespaced_name (PK), namespace, name, and actor refs (strategy/execution/review/apply/estimation/invariant).
  • Add state enum column (available/archived) with default available (no draft state).
  • Add description columns (description, long_description) and definition_of_done (rendered text).
  • Add behavioral columns (automation_profile, reusable, read_only, inputs_schema_json) and metadata (tags_json, created_by, timestamps).
  • Add action_invariants table with FK to actions by namespaced_name, invariant_text, position, and created_at.
  • Add unique index on actions.namespaced_name, index on actions.namespace, and index on actions.state for list filters.
  • Ensure downgrade path drops indexes and tables in reverse order.
  • Add Alembic migration for action_arguments table with FK to actions.namespaced_name and ordered position for deterministic argument ordering.
  • Add columns for name, arg_type (string/integer/float/boolean/list), requirement, description, default_value_json, min_value, max_value, validation_pattern.
  • Add check constraints for numeric min/max ordering and non-empty argument names.
  • Add uniqueness constraint on (action_name, name) and index on (action_name, position).
  • Add Alembic migration for v3_plans (replacing lifecycle_plans) with ULID PK and identity fields (parent_plan_id, root_plan_id, attempt).
  • Add core plan columns: namespaced_name, namespace, description, definition_of_done (rendered).
  • Add lifecycle columns: phase enum (strategize/execute/apply/applied), processing_state enum (queued/processing/errored/complete/cancelled), and phase timestamps.
  • Add action linkage columns (action_name only) and actor refs (strategy/execution/review/apply/estimation/invariant).
  • Add policy/metadata columns (automation_profile, read_only, reusable, inputs_schema_json, created_by, tags_json).
  • Add execution placeholders (changeset_id, sandbox_refs_json, validation_summary_json, decision_root_id, error_message, error_details_json).
  • Add plan_projects table with plan_id, project_name (namespaced), alias, read_only flag, and created_at.
  • Add uniqueness constraint on (plan_id, project_name) and index on (project_name) for lookups.
  • Add indexes on phase, processing_state, and namespace for list filtering.
  • Add plan_arguments table with plan_id, name, value_json, value_type, and position for stable ordering.
  • Add plan_invariants table with plan_id, invariant_text, source_scope (plan/action/project/global), optional position, and created_at.
  • Add uniqueness constraint on (plan_id, name) for arguments and (plan_id, invariant_text) for invariants.
  • Add index on (plan_id, position) for fast ordered retrieval.
  • Update LifecycleActionModel ORM: PK as namespaced_name, child relationships for arguments_rel and invariants_rel, to_domain()/from_domain() methods.
  • Add ActionInvariantModel, ActionArgumentModel child ORM models.
  • Update LifecyclePlanModel ORM: maps to v3_plans, action_name FK, processing_state, child relationships for project_links_rel, arguments_rel, invariants_rel, to_domain()/from_domain() methods.
  • Add PlanProjectModel, PlanArgumentModel, PlanInvariantModel child ORM models.
  • Update ActionRepository to use namespaced_name identity, update child table management in update().
  • Export all new child models from infrastructure.database.__init__.
  • Create docs/reference/database_schema.md with column-level details, constraints, indexes, FK relationships, and ER diagram for all 7 new tables.
  • Document argument storage, JSON serialization rules, and invariant source scopes.
  • Tests (Behave): Update migration scenarios and model coverage steps for new column names and child table patterns.
  • Tests (Robot): Update Robot integration tests for new schema (namespaced_name lookups, child table inserts).
  • Tests (ASV): Add benchmarks/db_migration_actions_bench.py for action ORM round-trip baseline.
  • Tests (ASV): Add benchmarks/db_migration_action_args_bench.py for argument serialization baseline.
  • Tests (ASV): Add benchmarks/db_migration_plans_bench.py for plan ORM round-trip baseline.
  • Run nox -s typecheck -- 0 errors, 0 warnings.
  • Run nox -s lint -- All checks passed.
  • Run nox -s unit_tests -- 130 features passed, 0 failed; 2233 scenarios passed, 0 failed.
  • Run nox -s integration_tests -- --include database -- 8 tests, 8 passed.
  • Verify coverage >=97% via nox -s coverage_report -- TOTAL 97%.

Section: #### Section 3 Notes
Status: Completed

## Metadata - **Commit**: `feat(db): add spec-aligned action and plan tables with migrations, ORM models, and benchmarks` - **Branch**: `feature/m1-db-actions` ## Subtasks - [x] Add Alembic migration skeleton with explicit down_revision dependency and naming conventions for indexes/constraints. - [x] Create `actions` table with **namespaced_name (PK)**, `namespace`, `name`, and actor refs (strategy/execution/review/apply/estimation/invariant). - [x] Add `state` enum column (`available`/`archived`) with default `available` (no draft state). - [x] Add description columns (`description`, `long_description`) and `definition_of_done` (rendered text). - [x] Add behavioral columns (`automation_profile`, `reusable`, `read_only`, `inputs_schema_json`) and metadata (`tags_json`, `created_by`, timestamps). - [x] Add `action_invariants` table with FK to actions by namespaced_name, `invariant_text`, `position`, and created_at. - [x] Add unique index on `actions.namespaced_name`, index on `actions.namespace`, and index on `actions.state` for list filters. - [x] Ensure downgrade path drops indexes and tables in reverse order. - [x] Add Alembic migration for `action_arguments` table with FK to `actions.namespaced_name` and ordered `position` for deterministic argument ordering. - [x] Add columns for `name`, `arg_type` (string/integer/float/boolean/list), `requirement`, `description`, `default_value_json`, `min_value`, `max_value`, `validation_pattern`. - [x] Add check constraints for numeric min/max ordering and non-empty argument names. - [x] Add uniqueness constraint on (action_name, name) and index on (action_name, position). - [x] Add Alembic migration for `v3_plans` (replacing `lifecycle_plans`) with ULID PK and identity fields (parent_plan_id, root_plan_id, attempt). - [x] Add core plan columns: `namespaced_name`, `namespace`, `description`, `definition_of_done` (rendered). - [x] Add lifecycle columns: `phase` enum (strategize/execute/apply/applied), `processing_state` enum (queued/processing/errored/complete/cancelled), and phase timestamps. - [x] Add action linkage columns (`action_name` only) and actor refs (strategy/execution/review/apply/estimation/invariant). - [x] Add policy/metadata columns (`automation_profile`, `read_only`, `reusable`, `inputs_schema_json`, `created_by`, `tags_json`). - [x] Add execution placeholders (`changeset_id`, `sandbox_refs_json`, `validation_summary_json`, `decision_root_id`, `error_message`, `error_details_json`). - [x] Add `plan_projects` table with plan_id, project_name (namespaced), alias, read_only flag, and created_at. - [x] Add uniqueness constraint on (plan_id, project_name) and index on (project_name) for lookups. - [x] Add indexes on `phase`, `processing_state`, and `namespace` for list filtering. - [x] Add `plan_arguments` table with plan_id, name, value_json, value_type, and `position` for stable ordering. - [x] Add `plan_invariants` table with plan_id, invariant_text, source_scope (plan/action/project/global), optional `position`, and created_at. - [x] Add uniqueness constraint on (plan_id, name) for arguments and (plan_id, invariant_text) for invariants. - [x] Add index on (plan_id, position) for fast ordered retrieval. - [x] Update `LifecycleActionModel` ORM: PK as `namespaced_name`, child relationships for `arguments_rel` and `invariants_rel`, `to_domain()`/`from_domain()` methods. - [x] Add `ActionInvariantModel`, `ActionArgumentModel` child ORM models. - [x] Update `LifecyclePlanModel` ORM: maps to `v3_plans`, `action_name` FK, `processing_state`, child relationships for `project_links_rel`, `arguments_rel`, `invariants_rel`, `to_domain()`/`from_domain()` methods. - [x] Add `PlanProjectModel`, `PlanArgumentModel`, `PlanInvariantModel` child ORM models. - [x] Update `ActionRepository` to use `namespaced_name` identity, update child table management in `update()`. - [x] Export all new child models from `infrastructure.database.__init__`. - [x] Create `docs/reference/database_schema.md` with column-level details, constraints, indexes, FK relationships, and ER diagram for all 7 new tables. - [x] Document argument storage, JSON serialization rules, and invariant source scopes. - [x] Tests (Behave): Update migration scenarios and model coverage steps for new column names and child table patterns. - [x] Tests (Robot): Update Robot integration tests for new schema (namespaced_name lookups, child table inserts). - [x] Tests (ASV): Add `benchmarks/db_migration_actions_bench.py` for action ORM round-trip baseline. - [x] Tests (ASV): Add `benchmarks/db_migration_action_args_bench.py` for argument serialization baseline. - [x] Tests (ASV): Add `benchmarks/db_migration_plans_bench.py` for plan ORM round-trip baseline. - [x] Run `nox -s typecheck` -- 0 errors, 0 warnings. - [x] Run `nox -s lint` -- All checks passed. - [x] Run `nox -s unit_tests` -- 130 features passed, 0 failed; 2233 scenarios passed, 0 failed. - [x] Run `nox -s integration_tests -- --include database` -- 8 tests, 8 passed. - [x] Verify coverage >=97% via `nox -s coverage_report` -- TOTAL 97%. **Section**: #### Section 3 Notes **Status**: Completed
freemo added this to the v3.0.0 milestone 2026-02-22 23:40:28 +00:00
freemo self-assigned this 2026-02-22 23:40:28 +00:00
Author
Owner

Implementation Notes — Persistence Layer (A5)

2026-02-09: Stage A5.3 + A5.4 Complete - v3 SQLAlchemy Models

  • Created LifecycleActionModel (table: actions_v3) and LifecyclePlanModel (table: lifecycle_plans) with full spec alignment.
  • Used __allow_unmapped__ = True on both models for from __future__ import annotations compatibility.
  • Legacy note: These v3 ORM models use action_id/project_ids and draft-style fields; they are superseded by the A5 rebaseline.

2026-02-09: Stage A5.6 Complete - ActionRepository (Luis tasks A5.6a-A5.6j)

  • Created ActionRepository with session-factory pattern, 10 methods, @database_retry on all public methods.
  • All mutating methods flush (do NOT commit) — caller/UnitOfWork handles commit.
  • Uses Any type hints for domain Action objects to avoid circular import issues.

(Migrated from docs/implementation-notes.md)

## Implementation Notes — Persistence Layer (A5) **2026-02-09**: Stage A5.3 + A5.4 Complete - v3 SQLAlchemy Models - Created `LifecycleActionModel` (table: `actions_v3`) and `LifecyclePlanModel` (table: `lifecycle_plans`) with full spec alignment. - Used `__allow_unmapped__ = True` on both models for `from __future__ import annotations` compatibility. - **Legacy note**: These v3 ORM models use `action_id`/`project_ids` and draft-style fields; they are superseded by the A5 rebaseline. **2026-02-09**: Stage A5.6 Complete - ActionRepository (Luis tasks A5.6a-A5.6j) - Created `ActionRepository` with session-factory pattern, 10 methods, `@database_retry` on all public methods. - All mutating methods flush (do NOT commit) — caller/UnitOfWork handles commit. - Uses `Any` type hints for domain Action objects to avoid circular import issues. *(Migrated from `docs/implementation-notes.md`)*
Sign in to join this conversation.
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Reference
cleveragents/cleveragents-core#242
No description provided.