cleveragents/cleveragents-core
4
0
Fork 3
Code Issues 6.0k Pull Requests 115 Actions Packages Projects Releases Wiki Activity

refactor(db): migrate from create_all() to Alembic-managed schema migrations #941

New Issue
Closed
opened 2026-03-14 01:17:30 +00:00 by freemo · 1 comment
freemo commented 2026-03-14 01:17:30 +00:00
Owner
Copy Link
Copy Source

Background and Context

The specification requires managed schema migrations for database evolution (spec §Architecture > Storage). The system must support schema upgrades between versions without data loss, with versioned migration scripts that can be applied forward and rolled back.

The current implementation in src/cleveragents/infrastructure/database/ uses SQLAlchemy's Base.metadata.create_all() for schema initialization. This approach:

  • Cannot handle schema changes — Adding/removing/modifying columns requires dropping and recreating tables
  • No migration history — No record of what schema version is deployed
  • No rollback capability — Cannot revert a schema change
  • Legacy table fragmentation — There are both v3_plans and plans tables; the split between legacy and current schemas is handled ad-hoc
  • No data migration support — Can't transform existing data when schema changes

Affected files

  • src/cleveragents/infrastructure/database/models.py — 38 ORM models
  • src/cleveragents/infrastructure/database/engine.py — Database engine initialization (calls create_all())
  • src/cleveragents/infrastructure/database/session.py — Session factory
  • New: alembic/ directory with migration scripts

Expected Behavior

Database schema must be managed by Alembic with versioned migration scripts. Schema changes between releases must be applied automatically with alembic upgrade head. Rollback to previous schema versions must be possible.

Acceptance Criteria

  • Alembic configured with alembic.ini and alembic/env.py
  • Initial migration script that matches current create_all() schema exactly
  • Application startup runs alembic upgrade head instead of create_all()
  • Migration history table (alembic_version) tracks current schema version
  • Existing databases with create_all() schema can be "stamped" with initial revision
  • Legacy v3_plans/plans table split resolved into a single coherent migration chain
  • cleveragents db migrate CLI command for generating new migration scripts
  • cleveragents db upgrade and cleveragents db downgrade CLI commands
  • At least one example data migration (transforming existing column data)

Metadata

  • Commit message: refactor(db): migrate from create_all() to Alembic-managed schema migrations
  • Branch: refactor/db-alembic-migrations
  • Parent Epic: None (standalone task)
  • Blocks: None
  • Blocked by: None

Subtasks

  • Add alembic to project dependencies
  • Configure alembic.ini and alembic/env.py with project database URL
  • Generate initial migration from current ORM models (alembic revision --autogenerate)
  • Validate initial migration matches create_all() schema exactly
  • Replace create_all() in engine initialization with alembic upgrade head
  • Implement "stamp" logic for existing databases (detect pre-Alembic schema)
  • Resolve v3_plans/plans table split into coherent migration chain
  • Implement cleveragents db migrate CLI command
  • Implement cleveragents db upgrade CLI command
  • Implement cleveragents db downgrade CLI command
  • Add migration testing: apply all migrations forward, then roll back to initial
  • Tests (Behave): Add scenarios for migration lifecycle
  • Tests (Unit): Add tests for stamp detection and upgrade path
  • Verify coverage >=97% via nox -s coverage_report
  • Run nox (all default sessions), fix any errors

Definition of Done

This issue is complete when the database schema is fully managed by Alembic, create_all() is removed, existing databases can be upgraded in place, and migration scripts exist for the full current schema.

## Background and Context The specification requires managed schema migrations for database evolution (spec §Architecture > Storage). The system must support schema upgrades between versions without data loss, with versioned migration scripts that can be applied forward and rolled back. The current implementation in `src/cleveragents/infrastructure/database/` uses SQLAlchemy's `Base.metadata.create_all()` for schema initialization. This approach: - **Cannot handle schema changes** — Adding/removing/modifying columns requires dropping and recreating tables - **No migration history** — No record of what schema version is deployed - **No rollback capability** — Cannot revert a schema change - **Legacy table fragmentation** — There are both `v3_plans` and `plans` tables; the split between legacy and current schemas is handled ad-hoc - **No data migration support** — Can't transform existing data when schema changes ### Affected files - `src/cleveragents/infrastructure/database/models.py` — 38 ORM models - `src/cleveragents/infrastructure/database/engine.py` — Database engine initialization (calls `create_all()`) - `src/cleveragents/infrastructure/database/session.py` — Session factory - New: `alembic/` directory with migration scripts ## Expected Behavior Database schema must be managed by Alembic with versioned migration scripts. Schema changes between releases must be applied automatically with `alembic upgrade head`. Rollback to previous schema versions must be possible. ## Acceptance Criteria - [ ] Alembic configured with `alembic.ini` and `alembic/env.py` - [ ] Initial migration script that matches current `create_all()` schema exactly - [ ] Application startup runs `alembic upgrade head` instead of `create_all()` - [ ] Migration history table (`alembic_version`) tracks current schema version - [ ] Existing databases with `create_all()` schema can be "stamped" with initial revision - [ ] Legacy `v3_plans`/`plans` table split resolved into a single coherent migration chain - [ ] `cleveragents db migrate` CLI command for generating new migration scripts - [ ] `cleveragents db upgrade` and `cleveragents db downgrade` CLI commands - [ ] At least one example data migration (transforming existing column data) ## Metadata - **Commit message**: `refactor(db): migrate from create_all() to Alembic-managed schema migrations` - **Branch**: `refactor/db-alembic-migrations` - **Parent Epic**: None (standalone task) - **Blocks**: None - **Blocked by**: None ## Subtasks - [ ] Add `alembic` to project dependencies - [ ] Configure `alembic.ini` and `alembic/env.py` with project database URL - [ ] Generate initial migration from current ORM models (`alembic revision --autogenerate`) - [ ] Validate initial migration matches `create_all()` schema exactly - [ ] Replace `create_all()` in engine initialization with `alembic upgrade head` - [ ] Implement "stamp" logic for existing databases (detect pre-Alembic schema) - [ ] Resolve `v3_plans`/`plans` table split into coherent migration chain - [ ] Implement `cleveragents db migrate` CLI command - [ ] Implement `cleveragents db upgrade` CLI command - [ ] Implement `cleveragents db downgrade` CLI command - [ ] Add migration testing: apply all migrations forward, then roll back to initial - [ ] Tests (Behave): Add scenarios for migration lifecycle - [ ] Tests (Unit): Add tests for stamp detection and upgrade path - [ ] Verify coverage >=97% via `nox -s coverage_report` - [ ] Run `nox` (all default sessions), fix any errors ## Definition of Done This issue is complete when the database schema is fully managed by Alembic, `create_all()` is removed, existing databases can be upgraded in place, and migration scripts exist for the full current schema.
freemo added the
MoSCoW
Must have
Points
8
Priority
High
State
Verified
Type
Task
 labels 2026-03-14 01:18:16 +00:00
freemo added this to the v3.5.0 milestone 2026-03-14 01:18:24 +00:00
freemo self-assigned this 2026-03-14 04:27:20 +00:00
freemo added
State
In Progress
 and removed
State
Verified
 labels 2026-03-17 19:22:33 +00:00
freemo commented 2026-03-18 02:49:51 +00:00
Author
Owner
Copy Link
Copy Source

Implementation Notes — Day 37

Summary

Added the agents db CLI command group and fixed the legacy stamp logic in MigrationRunner.

Design Decisions

  1. CLI command structure: Used Typer subcommands delegating to MigrationRunner. The upgrade command calls init_or_upgrade() for the head target, and delegates to alembic.command.upgrade() for specific revisions.

  2. Stamp at head instead of 001_initial_schema: When detecting a pre-Alembic database (tables exist but no alembic_version), the old code stamped at 001_initial_schema then tried to run all subsequent migrations. This failed because create_all already created all tables — migrations that CREATE TABLE would fail with "already exists". Stamping at head correctly records that the current schema is at the latest state.

  3. Transaction commit after stamp: Added connection.commit() after command.stamp() to ensure the alembic_version row is visible to subsequent operations on the same in-memory database.

  4. Deferred init_database migration: The full migration of init_database() from create_all to Alembic-only is deferred because migration scripts include constraints (e.g., UniqueConstraint on action_arguments) that the ORM models don't declare. This causes integrity errors in the action update flow. A follow-up issue should reconcile ORM model constraints with migration scripts.

Key Code Locations

  • db.py CLI commands — src/cleveragents/cli/commands/db.py (commit 84586d81)
  • MigrationRunner.init_or_upgrade() stamp fix — src/cleveragents/infrastructure/database/migration_runner.py
  • Lifecycle tests — features/db_migration_lifecycle.feature

Quality Gates

  • Lint: ✅ | Typecheck: ✅ 0 errors | Unit tests: ✅ 385 features, 10968 scenarios | Integration: ✅ all passed | Coverage: ✅ 97%
## Implementation Notes — Day 37 ### Summary Added the `agents db` CLI command group and fixed the legacy stamp logic in `MigrationRunner`. ### Design Decisions 1. **CLI command structure**: Used Typer subcommands delegating to `MigrationRunner`. The `upgrade` command calls `init_or_upgrade()` for the `head` target, and delegates to `alembic.command.upgrade()` for specific revisions. 2. **Stamp at head instead of 001_initial_schema**: When detecting a pre-Alembic database (tables exist but no `alembic_version`), the old code stamped at `001_initial_schema` then tried to run all subsequent migrations. This failed because `create_all` already created all tables — migrations that CREATE TABLE would fail with "already exists". Stamping at `head` correctly records that the current schema is at the latest state. 3. **Transaction commit after stamp**: Added `connection.commit()` after `command.stamp()` to ensure the `alembic_version` row is visible to subsequent operations on the same in-memory database. 4. **Deferred init_database migration**: The full migration of `init_database()` from `create_all` to Alembic-only is deferred because migration scripts include constraints (e.g., `UniqueConstraint` on `action_arguments`) that the ORM models don't declare. This causes integrity errors in the action update flow. A follow-up issue should reconcile ORM model constraints with migration scripts. ### Key Code Locations - `db.py` CLI commands — `src/cleveragents/cli/commands/db.py` (commit 84586d81) - `MigrationRunner.init_or_upgrade()` stamp fix — `src/cleveragents/infrastructure/database/migration_runner.py` - Lifecycle tests — `features/db_migration_lifecycle.feature` ### Quality Gates - Lint: ✅ | Typecheck: ✅ 0 errors | Unit tests: ✅ 385 features, 10968 scenarios | Integration: ✅ all passed | Coverage: ✅ 97%
freemo added
State
In Review
 and removed
State
In Progress
 labels 2026-03-18 02:49:54 +00:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
auto/needs-reevaluation
Controller deferred this PR; awaiting Phase 6+ scope-evaluator or operator re-enablement.
 controller-managed
Auto-agents controller manages this PR/issue (see tools/controller/deploy/RUNBOOK.md). Remove this label to abandon controller management.
 overdue
auto/blocked-by-deps
PR blocked by an open issue dependency. Operator must close the dep (or remove the dependency link) before the merge driver can act. Auto-cleared by merge_drive when no open deps remain.
 auto/ci-timeout
Most recent merge cycle hit CI timeout. Driver excludes this PR while last merge_cycle row is < 30 min old; label persists thereafter as visible history.
 auto/claimed-implementer
Currently being processed by an implementer worker.
 auto/claimed-merge
Currently being processed by the merge driver.
 auto/claimed-reviewer
Currently being processed by a reviewer worker.
 auto/driver-down
Merge driver heartbeat stale; pipeline halted. Closed automatically on next clean tick.
 auto/invariant-violation
Detected master commit violating the strict merge invariant. Tracked as an issue (not a PR label); kept here for label completeness.
 auto/last-attempt-tier-0
In-cycle escalation: most recent attempt ran at the Tier 0 slot (`tier-0`). Slot's model defined in .opencode/models/tiers.yaml.
 auto/last-attempt-tier-1
In-cycle escalation: most recent attempt ran at the Tier 1 slot (`tier-1`). Slot's model defined in .opencode/models/tiers.yaml.
 auto/last-attempt-tier-2
In-cycle escalation: most recent attempt ran at the Tier 2 slot (`tier-2`). Slot's model defined in .opencode/models/tiers.yaml. Gated behind IMPLEMENTER_ESCALATION_TIER2_ENABLED.
 auto/last-attempt-tier-min
In-cycle escalation: most recent attempt ran at the Tier -1 slot (`tier-min`). Slot's model defined in .opencode/models/tiers.yaml. Suffix is ``-min`` (not ``--1``) so the Forgejo UI reads naturally.
 Automation Tracking
Tracking issues used by the AI Automation system for agents to communicate and report.
 auto/needs-conflict-resolution
Rebase conflict needs LLM conflict-resolver.
 auto/needs-implementer
Failing CI needs implementer attention.
 auto/postmortem
Documenting a driver incident or rollback.
 auto/ready-to-merge
Reviewer has APPROVED this PR and no later REQUEST_CHANGES is outstanding. The merge driver requires this label to even consider a PR for merging. Set by the reviewer worker on APPROVE; cleared on REQUEST_CHANGES.
 auto/restart-throttled
Train repeatedly lost master-tempo races. Driver excludes via merge_cycle until cooldown elapses; label persists as visible history.
 auto/revert
Revert PR backing out an invariant violation. Fast-tracked through the merge driver.
 auto/sentinel
Sentinel PR duplicated from upstream into a personal fork by tools/duplicate_prs_to_fork.py for pipeline testing. Lives only in the fork; the canonical pipeline never sees it.
 auto/stale-inactivity
No implementer activity for N days. Flagged for human review. Auto-cleared on next push to head branch.
 auto/unstable
Repeatedly fails on current master (>= 3 ci-fail-on-rebased-sha releases in 12 h). Excluded from driver until human triage.
 Blocked
A ticket in a blocked state and unable to complete until some other task is completed first.
Bounty
$100
A bounty of $100 for any open-source contributor who provides a MR that solves this issue
Bounty
$1000
A bounty of $1000 for any open-source contributor who provides a MR that solves this issue
Bounty
$10000
A bounty of $10000 for any open-source contributor who provides a MR that solves this issue
Bounty
$20
A bounty of $20 for any open-source contributor who provides a MR that solves this issue
Bounty
$2000
A bounty of $2000 for any open-source contributor who provides a MR that solves this issue
Bounty
$250
A bounty of $250 for any open-source contributor who provides a MR that solves this issue
Bounty
$50
A bounty of $50 for any open-source contributor who provides a MR that solves this issue
Bounty
$500
A bounty of $500 for any open-source contributor who provides a MR that solves this issue
Bounty
$5000
A bounty of $5000 for any open-source contributor who provides a MR that solves this issue
Bounty
$750
A bounty of $750 for any open-source contributor who provides a MR that solves this issue
MoSCoW
Could have
Could have feature in order to satisfy the epic/legendary.
MoSCoW
Must have
Must have feature in order to satisfy the epic/legendary.
MoSCoW
Should have
Should have feature in order to satisfy the epic/legendary.
Needs Feedback
There are questions in the ticket that can not be completed until the project owner provides clarity.
Points
1
1 man-hours worth of work for an expert with no learning curve.
Points
13
13 man-hours worth of work for an expert with no learning curve.
Points
2
2 man-hours worth of work for an expert with no learning curve.
Points
21
21 man-hours worth of work for an expert with no learning curve.
Points
3
3 man-hours worth of work for an expert with no learning curve.
Points
34
34 man-hours worth of work for an expert with no learning curve.
Points
5
5 man-hours worth of work for an expert with no learning curve.
Points
55
55 man-hours worth of work for an expert with no learning curve.
Points
8
8 man-hours worth of work for an expert with no learning curve.
Points
88
88 man-hours worth of work for an expert with no learning curve.
Priority
Backlog
This ticket has backlogged priority and is not to be worked on yet
Priority
CI Blocker
Critical priority issue that blocks CI/CD pipeline and prevents PR merges
Priority
Critical
The priority is critical
Priority
High
The priority is high
Priority
Low
The priority is low
Priority
Medium
The priority is medium
Signed-off: Owner
When an epic or legendary is in review it must be signed off by owner, tech lead, and scrum master before being marked as completed.
 Signed-off: Scrum Master
When an epic or legendary is in review it must be signed off by owner, tech lead, and scrum master before being marked as completed.
 Signed-off: Tech Lead
When an epic or legendary is in review it must be signed off by owner, tech lead, and scrum master before being marked as completed.
 Spike
A ticket for learning a tool or technology that is needed to be able to do future planning and design.
State
Completed
The ticket has been fully implemented, completed, and merged with the source code. This label should only be applied once a ticket is closed.
State
Duplicate
A ticket that represents the same content as an existing ticket.
State
In Progress
A ticket that is actively being developed.
State
In Review
A ticket that has had some code completed to implement but is waiting to pass peer review and is not yet merged in.
State
Paused
This ticket's work started but wasn't finished. It's on hold (likely in a feature branch) and will be resumed later, either due to a blocker or a delay.
State
Unverified
All new tickets start in this state. A developer may set it to show the ticket is unverified. This means we haven't agreed to work on it. It will either move to a verified state or be closed as wontdo.
State
Verified
The issue has been verified by a developer as legitimate. It will be worked on and verified tickets are now considered part of the backlog.
State
Wont Do
This ticket has been decided it wont be done. This may mean the bug has been determined to not be real (cant verify) or the feature is one we have decided we dont want to adopt.
Type
Automation
Any edits or discussion about the AI automated coding system.
Type
Bug
Something that doesnt work as intended.
Type
Discussion
Anytime a ticket represents a discussion about a subject and doesnt fall into one of the other categories.
Type
Documentation
An error or improvement needed in the documentation.
Type
Epic
Any first tier epic. That is, an epic which contains only issues as children and will not have sub-epics.
Type
Feature
Some new functionality not present.
Type
Legendary
A type of Epic which will contain other Epics.
Type
Refactor
A code change that restructures existing code without changing its external behavior.
Type
Support
Someone needs help using the project.
Type
Task
A generic task that doesnt fit into the other type categories.
Type
Testing
Work exclusively focusing on fixing or expanding testing.
No Label
MoSCoW
Must have
Points
8
Priority
High
State
In Review
Type
Task
Milestone
No items
No Milestone v3.5.0
Projects
Clear projects
No project
Assignees
Clear assignees
aditya (Aditya Chhabra) aleenaumair (Aleena Umair) brent.edwards (Brent Edwards) CoreRasurae (Luis Mendes) drew (Drew Morris) eugen.thaci (Eugen Thaci) freemo (Jeffrey Phillips Freeman) HAL9000 (HAL 9000) HAL9001 (HAL9001) hamza.khyari (Hamza Khyari) hurui200320 (Rui Hu) justin.morris khird (Kyle Hird) org.cleveragents
No Assignees freemo
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: cleveragents/cleveragents-core#941
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?