[P1] Replace mock data with database queries in GET /api/projects/{id}/tasks

[frankbria]

Overview

The /api/projects/{id}/tasks endpoint returns hardcoded mock data instead of querying the database.

Current State

File: codeframe/ui/routers/projects.py lines 220-229

@router.get("/{project_id}/tasks")
async def get_tasks(project_id: int, status: str | None = None, limit: int = 50):
    """Get project tasks."""
    # TODO: Query database with filters
    return {
        "tasks": [
            {
                "id": 27,
                "title": "JWT refresh token flow",
                # ... hardcoded mock data

Expected Behavior

• Query tasks table from database with filters
• Support filtering by status, assignee, priority
• Apply limit and offset for pagination
• Return actual task data as Task objects
• Handle empty results gracefully

Implementation Requirements

1. Import Database instance (from FastAPI dependency injection)
2. Call db.get_tasks_for_project(project_id, filters={'status': status})
3. Convert Task objects to dictionaries for JSON response
4. Add pagination parameters (limit, offset)
5. Handle database errors gracefully

Acceptance Criteria

• Endpoint queries actual database instead of mocking
• Filtering by status works correctly
• Pagination parameters respected
• Empty results return empty array (not error)
• Integration tests verify database integration

Priority

P1 - Critical: Dashboard needs real data to display task progress.

References

• Issues Analysis: CODEFRAME_ISSUES_ANALYSIS.md lines 76-82
• User Documentation: CODEFRAME_USER_DOCUMENTATION.md lines 242

[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

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

🔗 Similar Issues

Related Issues

• Backend: Checkpoint creation endpoint ignores project_id parameter #89
• Redesign database error handling test for project creation API #40
• [P1] Implement LeadAgent.assign_task() logic #100
• [P1] Implement WorkerAgent.execute_task() with Anthropic API integration #98
• Fix checkpoint panel visibility - CheckpointList not rendering items #85

🔗 Related PRs

#3 - feat(sprint-4): Multi-Agent Coordination System - P0 & P1 Complete [merged]
#79 - Fix database error handling in project creation [merged]

👤 Suggested Assignees

• frankbria

---

🧪 Issue enrichment is currently in open beta.

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 endpoint GET /api/projects/{project_id}/tasks at lines 220-238 in file:codeframe/ui/routers/projects.py currently returns hardcoded mock data. The codebase already has a Database.get_project_tasks() method that retrieves all tasks for a project, but it lacks filtering and pagination support. Other endpoints like get_project_blockers in file:codeframe/ui/routers/blockers.py demonstrate the established pattern: validate project existence, inject database dependency, apply filters, and handle errors gracefully.

Approach

Replace the mock data implementation with actual database queries following the existing codebase patterns. The endpoint will inject the Database dependency, validate the project exists, retrieve tasks using db.get_project_tasks(), apply client-side filtering for status (since the database method doesn't support it), implement pagination with limit/offset, and return properly formatted results. This approach maintains consistency with other endpoints while avoiding unnecessary database schema changes.

Implementation Steps

1. Update the Endpoint Signature and Add Database Dependency

File: file:codeframe/ui/routers/projects.py (lines 220-238)

Modify the get_tasks endpoint to:

• Add db: Database = Depends(get_db) parameter for database dependency injection
• Add offset: int = 0 parameter for pagination support (in addition to existing limit: int = 50)
• Keep existing status: str | None = None parameter for filtering

2. Add Project Existence Validation

Before querying tasks, validate that the project exists:

• Call db.get_project(project_id) to retrieve the project
• If project is None, raise HTTPException(status_code=404, detail=f"Project {project_id} not found")
• This follows the same pattern used in get_project_status, get_project_prd, and get_project_issues endpoints

3. Query Tasks from Database

Replace the hardcoded mock data with actual database query:

• Call db.get_project_tasks(project_id) to retrieve all tasks for the project
• This method returns a list of task dictionaries ordered by task_number
• Wrap the database call in a try-except block to handle potential sqlite3.Error exceptions

4. Apply Status Filtering

Since get_project_tasks() doesn't support status filtering, implement it in the endpoint:

• If status parameter is provided, filter the tasks list: tasks = [t for t in tasks if t.get("status") == status]
• If status is None, use all tasks without filtering
• This approach avoids modifying the database layer while meeting the requirement

5. Calculate Total Count and Apply Pagination

Before slicing for pagination:

• Store total = len(tasks) to return the total count of filtered tasks
• Apply pagination: tasks = tasks[offset:offset + limit]
• This ensures the total field reflects all matching tasks, not just the paginated subset

6. Return Formatted Response

Return a dictionary matching the expected API contract:

• {"tasks": tasks, "total": total}
• The tasks field contains the paginated list of task dictionaries
• The total field contains the total count of tasks matching the filter (before pagination)
• Empty results return {"tasks": [], "total": 0} without raising an error

7. Add Error Handling

Wrap the database operations in try-except:

• Catch sqlite3.Error exceptions from database operations
• Log the error using logger.error(f"Database error fetching tasks: {e}", exc_info=True)
• Raise HTTPException(status_code=500, detail="Error fetching tasks") to return a proper error response
• This follows the pattern used in get_activity endpoint

8. Update Endpoint Documentation

Update the docstring to reflect the actual implementation:

• Document the db parameter as "Database instance (injected)"
• Document the offset parameter as "Number of tasks to skip for pagination (default: 0)"
• Document the return value structure: {"tasks": List[Dict], "total": int}
• Document the 404 error case when project is not found
• Remove the TODO comment

Expected Behavior After Implementation

sequenceDiagram
    participant Client
    participant Endpoint as GET /api/projects/{id}/tasks
    participant Database
    
    Client->>Endpoint: Request with project_id, status?, limit?, offset?
    Endpoint->>Database: get_project(project_id)
    alt Project not found
        Database-->>Endpoint: None
        Endpoint-->>Client: 404 Not Found
    else Project exists
        Database-->>Endpoint: Project data
        Endpoint->>Database: get_project_tasks(project_id)
        Database-->>Endpoint: List of all tasks
        Endpoint->>Endpoint: Filter by status (if provided)
        Endpoint->>Endpoint: Calculate total count
        Endpoint->>Endpoint: Apply pagination (offset, limit)
        Endpoint-->>Client: 200 OK {tasks: [...], total: N}
    end

Loading

Testing Considerations

The implementation should be verified with:

• Empty database: Returns {"tasks": [], "total": 0} without errors
• Status filtering: Only returns tasks matching the specified status
• Pagination: Correctly applies limit and offset parameters
• Project not found: Returns 404 with appropriate error message
• Database errors: Returns 500 with error message and logs the exception

Reference existing test patterns in file:tests/api/test_endpoints_database.py for similar endpoint tests.

Import In IDE

🤖 Prompt for AI Agents

## Observations

The endpoint `GET /api/projects/{project_id}/tasks` at lines 220-238 in `file:codeframe/ui/routers/projects.py` currently returns hardcoded mock data. The codebase already has a `Database.get_project_tasks()` method that retrieves all tasks for a project, but it lacks filtering and pagination support. Other endpoints like `get_project_blockers` in `file:codeframe/ui/routers/blockers.py` demonstrate the established pattern: validate project existence, inject database dependency, apply filters, and handle errors gracefully.

## Approach

Replace the mock data implementation with actual database queries following the existing codebase patterns. The endpoint will inject the `Database` dependency, validate the project exists, retrieve tasks using `db.get_project_tasks()`, apply client-side filtering for status (since the database method doesn't support it), implement pagination with limit/offset, and return properly formatted results. This approach maintains consistency with other endpoints while avoiding unnecessary database schema changes.

## Implementation Steps

### 1. Update the Endpoint Signature and Add Database Dependency

**File**: `file:codeframe/ui/routers/projects.py` (lines 220-238)

Modify the `get_tasks` endpoint to:
- Add `db: Database = Depends(get_db)` parameter for database dependency injection
- Add `offset: int = 0` parameter for pagination support (in addition to existing `limit: int = 50`)
- Keep existing `status: str | None = None` parameter for filtering

### 2. Add Project Existence Validation

Before querying tasks, validate that the project exists:
- Call `db.get_project(project_id)` to retrieve the project
- If project is `None`, raise `HTTPException(status_code=404, detail=f"Project {project_id} not found")`
- This follows the same pattern used in `get_project_status`, `get_project_prd`, and `get_project_issues` endpoints

### 3. Query Tasks from Database

Replace the hardcoded mock data with actual database query:
- Call `db.get_project_tasks(project_id)` to retrieve all tasks for the project
- This method returns a list of task dictionaries ordered by `task_number`
- Wrap the database call in a try-except block to handle potential `sqlite3.Error` exceptions

### 4. Apply Status Filtering

Since `get_project_tasks()` doesn't support status filtering, implement it in the endpoint:
- If `status` parameter is provided, filter the tasks list: `tasks = [t for t in tasks if t.get("status") == status]`
- If `status` is `None`, use all tasks without filtering
- This approach avoids modifying the database layer while meeting the requirement

### 5. Calculate Total Count and Apply Pagination

Before slicing for pagination:
- Store `total = len(tasks)` to return the total count of filtered tasks
- Apply pagination: `tasks = tasks[offset:offset + limit]`
- This ensures the `total` field reflects all matching tasks, not just the paginated subset

### 6. Return Formatted Response

Return a dictionary matching the expected API contract:
- `{"tasks": tasks, "total": total}`
- The `tasks` field contains the paginated list of task dictionaries
- The `total` field contains the total count of tasks matching the filter (before pagination)
- Empty results return `{"tasks": [], "total": 0}` without raising an error

### 7. Add Error Handling

Wrap the database operations in try-except:
- Catch `sqlite3.Error` exceptions from database operations
- Log the error using `logger.error(f"Database error fetching tasks: {e}", exc_info=True)`
- Raise `HTTPException(status_code=500, detail="Error fetching tasks")` to return a proper error response
- This follows the pattern used in `get_activity` endpoint

### 8. Update Endpoint Documentation

Update the docstring to reflect the actual implementation:
- Document the `db` parameter as "Database instance (injected)"
- Document the `offset` parameter as "Number of tasks to skip for pagination (default: 0)"
- Document the return value structure: `{"tasks": List[Dict], "total": int}`
- Document the 404 error case when project is not found
- Remove the TODO comment

## Expected Behavior After Implementation

```mermaid
sequenceDiagram
    participant Client
    participant Endpoint as GET /api/projects/{id}/tasks
    participant Database
    
    Client->>Endpoint: Request with project_id, status?, limit?, offset?
    Endpoint->>Database: get_project(project_id)
    alt Project not found
        Database-->>Endpoint: None
        Endpoint-->>Client: 404 Not Found
    else Project exists
        Database-->>Endpoint: Project data
        Endpoint->>Database: get_project_tasks(project_id)
        Database-->>Endpoint: List of all tasks
        Endpoint->>Endpoint: Filter by status (if provided)
        Endpoint->>Endpoint: Calculate total count
        Endpoint->>Endpoint: Apply pagination (offset, limit)
        Endpoint-->>Client: 200 OK {tasks: [...], total: N}
    end
```

## Testing Considerations

The implementation should be verified with:
- **Empty database**: Returns `{"tasks": [], "total": 0}` without errors
- **Status filtering**: Only returns tasks matching the specified status
- **Pagination**: Correctly applies limit and offset parameters
- **Project not found**: Returns 404 with appropriate error message
- **Database errors**: Returns 500 with error message and logs the exception

Reference existing test patterns in `file:tests/api/test_endpoints_database.py` for similar endpoint tests.

---

Execution Information

Branch: main
Commit: 1a87e4e

---

💡 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]

Closed with PR #131