Follow-up to #2187 (Copilot review finding).
Problem
The store-backed ListTasks handles an all-sessions query (empty contextId) by loading every session for the user, then every task per session, then filtering, sorting, and paginating in memory. Latency and memory scale with the user's total task count even for a small page, and it issues N+1 queries (one ListSessions plus one ListTasksForSession per session).
Proposed fix
Add a single sqlc query that does the work server-side:
-- name: ListTasksForUser :many
SELECT task.*, COUNT(*) OVER() AS total
FROM task
JOIN session ON session.id = task.session_id
WHERE session.user_id = $1
AND task.deleted_at IS NULL
AND session.deleted_at IS NULL
ORDER BY task.id
LIMIT $2 OFFSET $3;
and a ListTasksForUser method on the database client, then have collectUserTasks use it for the all-sessions path. The single-session path can reuse the same shape with a session_id predicate.
Open question: status filters
status and statusTimestampAfter live inside task.data, which is a TEXT column, so filtering them in SQL needs either:
(data::jsonb -> 'status' ->> 'state') casts per row (no index, and the cast errors on any malformed row), or- a migration adding generated columns (e.g.
status_state, status_timestamp) or converting data to JSONB, with indexes.
Option 2 is the durable one but needs a new numbered migration (migration files are immutable in CI). A middle ground that already removes the N+1 and unbounded scan: push only the join + LIMIT/OFFSET down and keep the two JSON filters in memory on the fetched page window when no filter is set; when a filter is set, fall back to the current in-memory path until the schema supports it.
Follow-up to #2187 (Copilot review finding).
Problem
The store-backed
ListTaskshandles an all-sessions query (emptycontextId) by loading every session for the user, then every task per session, then filtering, sorting, and paginating in memory. Latency and memory scale with the user's total task count even for a small page, and it issues N+1 queries (oneListSessionsplus oneListTasksForSessionper session).Proposed fix
Add a single sqlc query that does the work server-side:
and a
ListTasksForUsermethod on the database client, then havecollectUserTasksuse it for the all-sessions path. The single-session path can reuse the same shape with asession_idpredicate.Open question: status filters
statusandstatusTimestampAfterlive insidetask.data, which is aTEXTcolumn, so filtering them in SQL needs either:(data::jsonb -> 'status' ->> 'state')casts per row (no index, and the cast errors on any malformed row), orstatus_state,status_timestamp) or convertingdatatoJSONB, with indexes.Option 2 is the durable one but needs a new numbered migration (migration files are immutable in CI). A middle ground that already removes the N+1 and unbounded scan: push only the join + LIMIT/OFFSET down and keep the two JSON filters in memory on the fetched page window when no filter is set; when a filter is set, fall back to the current in-memory path until the schema supports it.