Performance: N+1 queries in list_tasks and list_projects causing slow API responses

[mjunaidca]

Problem

API endpoints are slow due to N+1 query patterns. Listing 50 tasks triggers ~50 additional queries instead of 1-2.

Root Causes

1. N+1 Query in list_tasks (tasks.py:163-183)

# Current: For each task, does a separate query for assignee
for task in tasks:
    if task.assignee_id:
        assignee = await session.get(Worker, task.assignee_id)  # N queries!

Impact: 50 tasks = 1 task query + 50 assignee queries = 51 queries

2. N+1 Query in list_projects (projects.py:46-71)

# Current: For each project, runs 2 count queries
for project in projects:
    member_count = await session.exec(member_count_stmt)  # N queries!
    task_count = await session.exec(task_count_stmt)       # N more queries!

Impact: 10 projects = 1 project query + 20 count queries = 21 queries

3. Missing Database Indexes

Frequently filtered columns lack indexes:

• parent_task_id - used for subtask queries
• status - used in list filters
• priority - used in list filters
• project_id on tasks - used in all task queries

Location: packages/api/src/taskflow_api/models/task.py

4. Frontend Sequential API Calls

Dashboard makes multiple API calls sequentially instead of in parallel.

Recommended Fixes

Backend - Eager Loading

# Use selectinload to batch-load related objects
from sqlalchemy.orm import selectinload

stmt = select(Task).options(selectinload(Task.assignee)).where(...)

Backend - Aggregated Counts

# Single query with window functions or subqueries
from sqlalchemy import func, select

stmt = (
    select(
        Project,
        func.count(distinct(ProjectMember.id)).label('member_count'),
        func.count(distinct(Task.id)).label('task_count')
    )
    .outerjoin(ProjectMember)
    .outerjoin(Task)
    .group_by(Project.id)
)

Backend - Add Indexes

class Task(SQLModel, table=True):
    parent_task_id: int | None = Field(default=None, index=True)
    status: str = Field(default="pending", index=True)
    priority: str = Field(default="medium", index=True)

Frontend - Parallel Fetching

// Instead of sequential
const projects = await fetchProjects();
const tasks = await fetchTasks();

// Use parallel
const [projects, tasks] = await Promise.all([
    fetchProjects(),
    fetchTasks()
]);

Performance Impact

Scenario	Current	After Fix
List 50 tasks	~51 queries	2 queries
List 10 projects	~21 queries	1-2 queries
Dashboard load	~500ms	~100ms

Labels

• performance
• backend
• database