Refactor database.py to be smaller

[frankbria]

The file codeframe/persistence/database.py is too large. Refactor it by breaking it up into smaller modules so that each file or module is small enough to be reviewable according to acceptable coding practice.

[coderabbitai]

📝 CodeRabbit Plan Mode

Generate an implementation plan and prompts that you can use with your favorite coding agent.

• Create Plan

Examples

• Example 1
• Example 2

---

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord or schedule a call!

[traycerai]

Plan

Observations

The file:codeframe/persistence/database.py file is 4160+ lines containing a monolithic Database class that handles 20+ different responsibilities including projects, issues, tasks, agents, blockers, memory, context, checkpoints, git branches, test results, lint results, code reviews, quality gates, token usage, and more. This violates the Single Responsibility Principle and makes the code difficult to maintain, test, and review. The file is currently imported by 30+ modules across agents, routers, services, and core components.

Approach

Refactor the monolithic Database class into a modular architecture using the Repository pattern with domain-specific repositories. Each repository will handle a specific domain (e.g., projects, agents, blockers) and will be composed into a main Database facade class that maintains backward compatibility. This approach allows incremental migration, preserves existing imports, and creates reviewable modules of 200-400 lines each.

Implementation Steps

1. Create Repository Base Infrastructure

Create file:codeframe/persistence/repositories/base.py to define the base repository class:

• Define BaseRepository class with shared connection management
• Accept sqlite3.Connection in constructor
• Provide utility methods for common database operations (execute, fetchone, fetchall)
• Include row-to-dict conversion helpers
• Add transaction management utilities

2. Create Domain-Specific Repository Modules

Create separate repository files in file:codeframe/persistence/repositories/ directory:

Project Repository (file:codeframe/persistence/repositories/project_repository.py):

• Extract methods: create_project, get_project, list_projects, update_project, delete_project, _calculate_project_progress, get_project_tasks, get_project_stats
• Extract helper: _row_to_project
• Estimated size: ~300 lines

Issue Repository (file:codeframe/persistence/repositories/issue_repository.py):

• Extract methods: create_issue, get_issue, get_project_issues, get_issues_with_tasks, list_issues_with_progress, get_issue_with_task_counts
• Extract helper: _row_to_issue
• Estimated size: ~350 lines

Task Repository (file:codeframe/persistence/repositories/task_repository.py):

• Extract methods: create_task, get_task, update_task, create_task_with_issue, get_tasks_by_issue, get_pending_tasks, create_task_dependency, get_task_dependencies
• Extract helper: _row_to_task
• Estimated size: ~400 lines

Agent Repository (file:codeframe/persistence/repositories/agent_repository.py):

• Extract methods: create_agent, get_agent, update_agent, list_agents, assign_agent_to_project, get_agents_for_project, get_projects_for_agent, remove_agent_from_project, reassign_agent_role, get_agent_assignment, get_available_agents
• Estimated size: ~350 lines

Blocker Repository (file:codeframe/persistence/repositories/blocker_repository.py):

• Extract methods: create_blocker, get_blocker, resolve_blocker, list_blockers, get_pending_blocker, expire_stale_blockers, get_blocker_metrics
• Estimated size: ~300 lines

Memory Repository (file:codeframe/persistence/repositories/memory_repository.py):

• Extract methods: create_memory, get_memory, get_project_memories, get_conversation
• Estimated size: ~200 lines

Context Repository (file:codeframe/persistence/repositories/context_repository.py):

• Extract methods: create_context_item, list_context_items, update_context_item_tier, archive_cold_items
• Estimated size: ~250 lines

Checkpoint Repository (file:codeframe/persistence/repositories/checkpoint_repository.py):

• Extract methods: create_checkpoint, get_checkpoints
• Estimated size: ~150 lines

Git Repository (file:codeframe/persistence/repositories/git_repository.py):

• Extract methods: create_git_branch, get_branch_for_issue, mark_branch_merged, mark_branch_abandoned, get_branch_statistics
• Estimated size: ~250 lines

Test Results Repository (file:codeframe/persistence/repositories/test_repository.py):

• Extract methods: create_test_result, get_test_results_by_task
• Estimated size: ~150 lines

Lint Repository (file:codeframe/persistence/repositories/lint_repository.py):

• Extract methods: create_lint_result, get_lint_results_for_task, get_lint_trend
• Estimated size: ~200 lines

Code Review Repository (file:codeframe/persistence/repositories/review_repository.py):

• Extract methods: create_code_review, get_code_reviews_for_task
• Estimated size: ~150 lines

Quality Gate Repository (file:codeframe/persistence/repositories/quality_repository.py):

• Extract methods: check_quality_gate, get_quality_gate_status
• Estimated size: ~200 lines

Token Usage Repository (file:codeframe/persistence/repositories/token_repository.py):

• Extract methods: track_token_usage, get_token_usage_stats
• Estimated size: ~150 lines

Correction Repository (file:codeframe/persistence/repositories/correction_repository.py):

• Extract methods: create_correction_attempt, get_correction_attempts
• Estimated size: ~150 lines

Activity Repository (file:codeframe/persistence/repositories/activity_repository.py):

• Extract methods: get_recent_activity, get_prd
• Estimated size: ~200 lines

3. Create Schema and Migration Manager

Create file:codeframe/persistence/schema_manager.py:

• Extract schema creation logic from _create_schema method
• Extract migration logic from _run_migrations method
• Organize schema definitions by domain (projects, issues, tasks, etc.)
• Estimated size: ~600 lines

4. Refactor Main Database Class

Modify file:codeframe/persistence/database.py:

• Keep initialization, connection management, and async support
• Instantiate all repository classes in __init__ or initialize
• Delegate method calls to appropriate repositories (e.g., create_project → self.projects.create_project)
• Maintain backward compatibility by keeping all public method signatures
• Keep context managers (__enter__, __exit__, __aenter__, __aexit__)
• Keep connection lifecycle methods (close, close_async, initialize_async, _get_async_conn)
• Estimated final size: ~400 lines

5. Create Repository Package Structure

Create file:codeframe/persistence/repositories/__init__.py:

• Export all repository classes
• Provide convenient imports for internal use
• Document the repository architecture

6. Update Imports (No Changes Required)

Since the Database class maintains the same interface and all methods are delegated to repositories, no changes are needed in consuming code:

• file:codeframe/persistence/__init__.py already exports Database
• All existing imports (from codeframe.persistence.database import Database) continue to work
• All method calls remain unchanged

7. Verification Strategy

After refactoring, verify the changes:

• Run existing test suite in file:tests/persistence/test_database.py to ensure all functionality works
• Run integration tests in file:tests/api/test_endpoints_database.py
• Run correction tests in file:tests/persistence/test_correction_database.py
• Verify all routers in file:codeframe/ui/routers/ continue to function
• Verify all agents in file:codeframe/agents/ continue to function
• Verify all services in file:codeframe/ui/services/ continue to function

Architecture Diagram

graph TD
    A[Database Class<br/>~400 lines] --> B[SchemaManager<br/>~600 lines]
    A --> C[ProjectRepository<br/>~300 lines]
    A --> D[IssueRepository<br/>~350 lines]
    A --> E[TaskRepository<br/>~400 lines]
    A --> F[AgentRepository<br/>~350 lines]
    A --> G[BlockerRepository<br/>~300 lines]
    A --> H[MemoryRepository<br/>~200 lines]
    A --> I[ContextRepository<br/>~250 lines]
    A --> J[CheckpointRepository<br/>~150 lines]
    A --> K[GitRepository<br/>~250 lines]
    A --> L[TestRepository<br/>~150 lines]
    A --> M[LintRepository<br/>~200 lines]
    A --> N[ReviewRepository<br/>~150 lines]
    A --> O[QualityRepository<br/>~200 lines]
    A --> P[TokenRepository<br/>~150 lines]
    A --> Q[CorrectionRepository<br/>~150 lines]
    A --> R[ActivityRepository<br/>~200 lines]
    
    C --> S[BaseRepository]
    D --> S
    E --> S
    F --> S
    G --> S
    H --> S
    I --> S
    J --> S
    K --> S
    L --> S
    M --> S
    N --> S
    O --> S
    P --> S
    Q --> S
    R --> S
    
    T[Routers] --> A
    U[Agents] --> A
    V[Services] --> A
    W[Core] --> A

Loading

File Size Summary

Module	Lines	Responsibility
database.py (refactored)	~400	Connection management, facade, delegation
schema_manager.py	~600	Schema creation, migrations
base.py	~100	Base repository utilities
project_repository.py	~300	Project CRUD and metrics
issue_repository.py	~350	Issue CRUD and queries
task_repository.py	~400	Task CRUD, dependencies
agent_repository.py	~350	Agent CRUD, assignments
blocker_repository.py	~300	Blocker management
memory_repository.py	~200	Memory and conversation
context_repository.py	~250	Context items and tiers
checkpoint_repository.py	~150	Checkpoint management
git_repository.py	~250	Git branch tracking
test_repository.py	~150	Test results
lint_repository.py	~200	Lint results and trends
review_repository.py	~150	Code reviews
quality_repository.py	~200	Quality gates
token_repository.py	~150	Token usage tracking
correction_repository.py	~150	Correction attempts
activity_repository.py	~200	Activity and PRD
Total	~4,200	(similar to original, but modular)

Benefits

1. Reviewability: Each repository file is 150-400 lines, easily reviewable in a single session
2. Maintainability: Changes to one domain (e.g., blockers) don't affect other domains
3. Testability: Each repository can be tested independently with focused unit tests
4. Backward Compatibility: Existing code continues to work without modifications
5. Incremental Migration: Repositories can be refactored one at a time
6. Clear Separation of Concerns: Each repository has a single, well-defined responsibility
7. Easier Onboarding: New developers can understand one domain at a time

Import In IDE

🤖 Prompt for AI Agents

## Observations

The `file:codeframe/persistence/database.py` file is 4160+ lines containing a monolithic `Database` class that handles 20+ different responsibilities including projects, issues, tasks, agents, blockers, memory, context, checkpoints, git branches, test results, lint results, code reviews, quality gates, token usage, and more. This violates the Single Responsibility Principle and makes the code difficult to maintain, test, and review. The file is currently imported by 30+ modules across agents, routers, services, and core components.

## Approach

Refactor the monolithic `Database` class into a modular architecture using the Repository pattern with domain-specific repositories. Each repository will handle a specific domain (e.g., projects, agents, blockers) and will be composed into a main `Database` facade class that maintains backward compatibility. This approach allows incremental migration, preserves existing imports, and creates reviewable modules of 200-400 lines each.

## Implementation Steps

### 1. Create Repository Base Infrastructure

Create `file:codeframe/persistence/repositories/base.py` to define the base repository class:
- Define `BaseRepository` class with shared connection management
- Accept `sqlite3.Connection` in constructor
- Provide utility methods for common database operations (execute, fetchone, fetchall)
- Include row-to-dict conversion helpers
- Add transaction management utilities

### 2. Create Domain-Specific Repository Modules

Create separate repository files in `file:codeframe/persistence/repositories/` directory:

**Project Repository** (`file:codeframe/persistence/repositories/project_repository.py`):
- Extract methods: `create_project`, `get_project`, `list_projects`, `update_project`, `delete_project`, `_calculate_project_progress`, `get_project_tasks`, `get_project_stats`
- Extract helper: `_row_to_project`
- Estimated size: ~300 lines

**Issue Repository** (`file:codeframe/persistence/repositories/issue_repository.py`):
- Extract methods: `create_issue`, `get_issue`, `get_project_issues`, `get_issues_with_tasks`, `list_issues_with_progress`, `get_issue_with_task_counts`
- Extract helper: `_row_to_issue`
- Estimated size: ~350 lines

**Task Repository** (`file:codeframe/persistence/repositories/task_repository.py`):
- Extract methods: `create_task`, `get_task`, `update_task`, `create_task_with_issue`, `get_tasks_by_issue`, `get_pending_tasks`, `create_task_dependency`, `get_task_dependencies`
- Extract helper: `_row_to_task`
- Estimated size: ~400 lines

**Agent Repository** (`file:codeframe/persistence/repositories/agent_repository.py`):
- Extract methods: `create_agent`, `get_agent`, `update_agent`, `list_agents`, `assign_agent_to_project`, `get_agents_for_project`, `get_projects_for_agent`, `remove_agent_from_project`, `reassign_agent_role`, `get_agent_assignment`, `get_available_agents`
- Estimated size: ~350 lines

**Blocker Repository** (`file:codeframe/persistence/repositories/blocker_repository.py`):
- Extract methods: `create_blocker`, `get_blocker`, `resolve_blocker`, `list_blockers`, `get_pending_blocker`, `expire_stale_blockers`, `get_blocker_metrics`
- Estimated size: ~300 lines

**Memory Repository** (`file:codeframe/persistence/repositories/memory_repository.py`):
- Extract methods: `create_memory`, `get_memory`, `get_project_memories`, `get_conversation`
- Estimated size: ~200 lines

**Context Repository** (`file:codeframe/persistence/repositories/context_repository.py`):
- Extract methods: `create_context_item`, `list_context_items`, `update_context_item_tier`, `archive_cold_items`
- Estimated size: ~250 lines

**Checkpoint Repository** (`file:codeframe/persistence/repositories/checkpoint_repository.py`):
- Extract methods: `create_checkpoint`, `get_checkpoints`
- Estimated size: ~150 lines

**Git Repository** (`file:codeframe/persistence/repositories/git_repository.py`):
- Extract methods: `create_git_branch`, `get_branch_for_issue`, `mark_branch_merged`, `mark_branch_abandoned`, `get_branch_statistics`
- Estimated size: ~250 lines

**Test Results Repository** (`file:codeframe/persistence/repositories/test_repository.py`):
- Extract methods: `create_test_result`, `get_test_results_by_task`
- Estimated size: ~150 lines

**Lint Repository** (`file:codeframe/persistence/repositories/lint_repository.py`):
- Extract methods: `create_lint_result`, `get_lint_results_for_task`, `get_lint_trend`
- Estimated size: ~200 lines

**Code Review Repository** (`file:codeframe/persistence/repositories/review_repository.py`):
- Extract methods: `create_code_review`, `get_code_reviews_for_task`
- Estimated size: ~150 lines

**Quality Gate Repository** (`file:codeframe/persistence/repositories/quality_repository.py`):
- Extract methods: `check_quality_gate`, `get_quality_gate_status`
- Estimated size: ~200 lines

**Token Usage Repository** (`file:codeframe/persistence/repositories/token_repository.py`):
- Extract methods: `track_token_usage`, `get_token_usage_stats`
- Estimated size: ~150 lines

**Correction Repository** (`file:codeframe/persistence/repositories/correction_repository.py`):
- Extract methods: `create_correction_attempt`, `get_correction_attempts`
- Estimated size: ~150 lines

**Activity Repository** (`file:codeframe/persistence/repositories/activity_repository.py`):
- Extract methods: `get_recent_activity`, `get_prd`
- Estimated size: ~200 lines

### 3. Create Schema and Migration Manager

Create `file:codeframe/persistence/schema_manager.py`:
- Extract schema creation logic from `_create_schema` method
- Extract migration logic from `_run_migrations` method
- Organize schema definitions by domain (projects, issues, tasks, etc.)
- Estimated size: ~600 lines

### 4. Refactor Main Database Class

Modify `file:codeframe/persistence/database.py`:
- Keep initialization, connection management, and async support
- Instantiate all repository classes in `__init__` or `initialize`
- Delegate method calls to appropriate repositories (e.g., `create_project` → `self.projects.create_project`)
- Maintain backward compatibility by keeping all public method signatures
- Keep context managers (`__enter__`, `__exit__`, `__aenter__`, `__aexit__`)
- Keep connection lifecycle methods (`close`, `close_async`, `initialize_async`, `_get_async_conn`)
- Estimated final size: ~400 lines

### 5. Create Repository Package Structure

Create `file:codeframe/persistence/repositories/__init__.py`:
- Export all repository classes
- Provide convenient imports for internal use
- Document the repository architecture

### 6. Update Imports (No Changes Required)

Since the `Database` class maintains the same interface and all methods are delegated to repositories, no changes are needed in consuming code:
- `file:codeframe/persistence/__init__.py` already exports `Database`
- All existing imports (`from codeframe.persistence.database import Database`) continue to work
- All method calls remain unchanged

### 7. Verification Strategy

After refactoring, verify the changes:
- Run existing test suite in `file:tests/persistence/test_database.py` to ensure all functionality works
- Run integration tests in `file:tests/api/test_endpoints_database.py`
- Run correction tests in `file:tests/persistence/test_correction_database.py`
- Verify all routers in `file:codeframe/ui/routers/` continue to function
- Verify all agents in `file:codeframe/agents/` continue to function
- Verify all services in `file:codeframe/ui/services/` continue to function

## Architecture Diagram

```mermaid
graph TD
    A[Database Class<br/>~400 lines] --> B[SchemaManager<br/>~600 lines]
    A --> C[ProjectRepository<br/>~300 lines]
    A --> D[IssueRepository<br/>~350 lines]
    A --> E[TaskRepository<br/>~400 lines]
    A --> F[AgentRepository<br/>~350 lines]
    A --> G[BlockerRepository<br/>~300 lines]
    A --> H[MemoryRepository<br/>~200 lines]
    A --> I[ContextRepository<br/>~250 lines]
    A --> J[CheckpointRepository<br/>~150 lines]
    A --> K[GitRepository<br/>~250 lines]
    A --> L[TestRepository<br/>~150 lines]
    A --> M[LintRepository<br/>~200 lines]
    A --> N[ReviewRepository<br/>~150 lines]
    A --> O[QualityRepository<br/>~200 lines]
    A --> P[TokenRepository<br/>~150 lines]
    A --> Q[CorrectionRepository<br/>~150 lines]
    A --> R[ActivityRepository<br/>~200 lines]
    
    C --> S[BaseRepository]
    D --> S
    E --> S
    F --> S
    G --> S
    H --> S
    I --> S
    J --> S
    K --> S
    L --> S
    M --> S
    N --> S
    O --> S
    P --> S
    Q --> S
    R --> S
    
    T[Routers] --> A
    U[Agents] --> A
    V[Services] --> A
    W[Core] --> A
```

## File Size Summary

| Module | Lines | Responsibility |
|--------|-------|----------------|
| `database.py` (refactored) | ~400 | Connection management, facade, delegation |
| `schema_manager.py` | ~600 | Schema creation, migrations |
| `base.py` | ~100 | Base repository utilities |
| `project_repository.py` | ~300 | Project CRUD and metrics |
| `issue_repository.py` | ~350 | Issue CRUD and queries |
| `task_repository.py` | ~400 | Task CRUD, dependencies |
| `agent_repository.py` | ~350 | Agent CRUD, assignments |
| `blocker_repository.py` | ~300 | Blocker management |
| `memory_repository.py` | ~200 | Memory and conversation |
| `context_repository.py` | ~250 | Context items and tiers |
| `checkpoint_repository.py` | ~150 | Checkpoint management |
| `git_repository.py` | ~250 | Git branch tracking |
| `test_repository.py` | ~150 | Test results |
| `lint_repository.py` | ~200 | Lint results and trends |
| `review_repository.py` | ~150 | Code reviews |
| `quality_repository.py` | ~200 | Quality gates |
| `token_repository.py` | ~150 | Token usage tracking |
| `correction_repository.py` | ~150 | Correction attempts |
| `activity_repository.py` | ~200 | Activity and PRD |
| **Total** | **~4,200** | **(similar to original, but modular)** |

## Benefits

1. **Reviewability**: Each repository file is 150-400 lines, easily reviewable in a single session
2. **Maintainability**: Changes to one domain (e.g., blockers) don't affect other domains
3. **Testability**: Each repository can be tested independently with focused unit tests
4. **Backward Compatibility**: Existing code continues to work without modifications
5. **Incremental Migration**: Repositories can be refactored one at a time
6. **Clear Separation of Concerns**: Each repository has a single, well-defined responsibility
7. **Easier Onboarding**: New developers can understand one domain at a time

---

Execution Information

Branch: main
Commit: a8ace58

---

💡 Tips

Supported Commands (Inside Comments)

• Use @traycerai generate to iterate on the previous version of the implementation plan.

Supported Commands (Inside Description)

• Add @traycerai ignore anywhere in the ticket description to prevent this ticket from being processed.
• Add @traycerai branch:<branch-name> anywhere in the ticket description to specify the target branch for the implementation plan.

Community

• Join our Discord Community to get help, request features, and share feedback.
• Follow us on X/Twitter for updates and announcements.

[frankbria]

Do Issue #141 first in order to keep consistency.

[frankbria]

Rework the plan given that issue #141 has been closed with pr #142 and thus the database.py file has changed.