N+1 query patterns in three hot list endpoints (facilitator responses, inject comments, participant inject list)

[richardmhope]

Problem

Three list endpoints issue per-row queries that grow unbounded with exercise activity — same shape as the previously fixed #22/#210, in newer code paths:

1. GET /exercises/{id}/responses — one full pending-inject scan per response. app/routers/responses.py:51-54:

return [
    response_payload(r, await response_next_inject_suggestions(session, r))
    for r in (await session.exec(q)).all()
]

Each call runs pending_next_injects → select(Inject).where(exercise_id...).where(state == pending) — the entire pending-inject set per response, plus session.get(Inject) per distinct id. A long exercise with hundreds of responses issues hundreds of inject-table queries on every facilitator dashboard load. (The ws_projector path does this once per event, which is fine; the list endpoint does it per row.)

2. GET /exercises/{id}/inject-comments — membership query per comment. app/routers/inject_comments.py:69-73: _can_see_comment per comment → require_inject_visible → exercise_group_for_user → an uncached select(ExerciseMember) (access_control.py:38-49), plus comment_payload does session.get(User) per author. ≥1 round-trip per comment.

3. GET /exercises/{id}/injects for participants/observers (app/routers/injects.py:202-208) — require_visible_bool re-runs the same per-inject ExerciseMember lookup for every inject in the list.

None of these are visible on demo-sized data; all scale linearly with responses/comments/injects, stacking on connection round-trips under the async engine.

Proposal

• Responses list: fetch the pending-inject set once, pass it into the per-response suggestion resolution (pure function over the prefetched map).
• Comments/injects lists: resolve the caller's ExerciseMember/group once per request and thread it through the visibility checks (the check functions can accept an optional pre-resolved member); batch author names with one in_() query (cf. the load_exercise_bundle loads every user in the system on every report/timeline/export #245 approach).
• A query-count assertion test on each endpoint (fixed seed: N responses/comments) to pin the ceiling.