Make large lists fast to browse and stable while new data streams in

[mihow]

Summary

People browsing a large project — one with millions of captures or occurrences — hit two problems on our list views. First, the lists are slow: rendering "page X of Y" requires an exact COUNT(*) over the whole filtered set, which is the single most expensive query on these endpoints, and jumping to a deep page makes the database scan and discard everything before it. Second, because we are continuously ingesting data, the contents shift under the reader: with offset/page-number pagination, rows inserted between page loads cause captures to be skipped or shown twice.

This epic moves the big list endpoints to keyset (cursor) pagination — "previous / next" instead of numbered pages. Cursor pages load in constant time no matter how deep you go, stay stable while new data streams in, and don't need a total count at all, which removes the most expensive query from those views.

Effect for the user

• Large lists load quickly even far down the list (no deep-offset scan).
• Browsing stays stable while a project is actively ingesting — no skipped or duplicated rows between pages.
• The expensive total-count query disappears on the converted lists.

Scope — hybrid, shipped incrementally per endpoint

• Cursor: SourceImage (captures) and Occurrence — the two biggest lists, where the count is the measured cost and a suitable index exists or is nearly ready.
• Keep offset + precision cap (Stop large list pages from running an unbounded total-count query #1233) for the small tables (Event, Project, Deployment) where the count is cheap and jump-to-page is still useful.
• Detection: fast-follow — it has no scope-aligned cursor index yet, so it isn't a quick win.

Key constraints found during a measurement + code-read pass

1. The frontend is the bulk of the work. The UI currently builds offsets itself, ignores the next/previous links, and computes numbered jump-to-page from the count. All of that must change to consume cursors.
2. Not every sort can be cursor-paged. Many list ordering options are computed annotations (e.g. detections_count, taxa_count, duration, first_appearance_*), not real columns, so they cannot back a cursor and must be excluded from cursor mode.
3. The default Occurrence sort is the hardest case. determination_score / detection_score are nullable floats; float cursors are prone to precision drift at page boundaries, and the NULLs compound it.
4. NULLS placement must match. The ordering filter forces NULLS LAST; any cursor index must match that exact ORDER BY … NULLS LAST shape or Postgres silently ignores it and falls back to a sort.
5. DRF needs custom wiring. CursorPagination does not read the ?ordering= param. Supporting user-selectable cursor orderings requires a custom paginator that maps ?ordering= to an allow-listed cursor field plus an id tiebreaker, with NULLS/float handling and our permission overlay.

Relationship to #1233 (precision cap)

Land #1233 first. Its count response plumbing and the UI handling for a non-exact count are a shared prerequisite cursor also needs, and it gives immediate relief before any cursor index exists. Cursor then removes the total entirely on the lists it covers, narrowing #1233's role to the offset-retained small tables.

Phased checklist

• Land Stop large list pages from running an unbounded total-count query #1233 precision cap (shared prerequisite)
• Build a custom cursor paginator: ?ordering= → allow-listed cursor field + id tiebreaker, NULLS-LAST aware, with the permission overlay
• SourceImage cursor (sort by timestamp, index exists) behind the allow-list
• Occurrence cursor (updated_at first; then -determination_score after Speed up the occurrence list on large projects #1351), handling the nullable-float / NULLS-LAST case carefully
• Frontend: consume next/previous cursors; replace numbered jump-to-page with prev/next + continuous scroll on cursor lists
• Exclude computed-annotation orderings from cursor mode (disallow or fall back to offset)
• Detection cursor (fast-follow, once a scope-aligned index exists)

Part of #928.