/flowsheet/search: total and totalPages count rows remaining after the cursor, not the match set

[jakebromberg]

On every cursor-paginated page, GET /flowsheet/search reports a total (and totalPages) that counts only the rows remaining after the cursor, not the size of the match set. The numbers shrink as a client walks forward.

Cause

In apps/backend/services/search.service.ts, the cursor predicate is appended to fullWhere:

if (parsedCursor) {
  fullWhere = sql`${fullWhere} AND (${flowsheet.add_time}, ${flowsheet.id}) ${cmp} (...)`;
}

and the count query is then built from that same clause:

const countQuery = sql`SELECT COUNT(*)::int AS total FROM (SELECT 1 ${fullWhere} LIMIT ${COUNT_CAP + 1}) AS capped`;

Sharing fullWhere is right for the data query and wrong for the count. total is documented and consumed as the size of the result set.

Why it matters now

Until #2344, the first page never emitted a nextCursor, so no client could reach a second cursor page and the defect was unreachable in practice. #2344 makes it reachable.

dj-site is not affected today by luck rather than design: src/hooks/playlistSearchHooks.ts reads data?.pages?.[0]?.total — the cursor-free first page. Any consumer reading the latest page, or a refactor that changes which page dj-site reads from, gets a count that decreases as the user scrolls.

Desired end state

total and totalPages describe the match set and are stable across a cursor walk.

Suggested approach

Keep the cursor predicate out of the count. Build the base clause once (filters only), use it for the count, and derive the data query's clause by appending the cursor predicate to a copy. The capped-count semantics from #1681 (LIMIT COUNT_CAP + 1, COUNT_CAP + 1 as a "10000+" sentinel) should be unchanged.

Constraints

• COUNT_CAP exists because an exact count on this table is unaffordable — do not replace the capped count with an exact one.
• The Promise.allSettled degraded-total branch computes total = offset + results.length when the count query fails. In cursor mode offset is 0, so that fallback already collapses to the current page size; whatever shape the fix takes, the degraded branch should stay coherent with it.

Acceptance criteria

• A cursor walk over a fixed result set reports the same total on every page
• The reported total matches the equivalent offset-mode request's total for the same query
• Capped-count behaviour at COUNT_CAP is unchanged
• A test asserts what the count query counts, not just what a mocked count returns — the existing test in tests/unit/services/search.service.test.ts mocks the count result and therefore pins nothing about this

Related

• GET /flowsheet/search never emits nextCursor on the first page, capping the Previous Sets archive at one page of 50 #2344 — makes this reachable
• PROD: GET /flowsheet/search returns 500 for every query (empty and non-empty) #1681 — the capped-count design this must preserve