CherryHQ-cherry-studio/docs/references/data/data-pagination-guide.md

Pagination Guide

Canonical spec for paginating any list endpoint in the DataApi system. It is the single home for the cross-cutting pagination concepts; the type signatures, hook APIs, and the server-side codec each keep their own authoritative doc and are linked from here. This is the sibling of Ordering Guide — ordering and pagination are the two list-shaping concerns, and a list endpoint often uses both at once.

If you only need one fact: pagination has two modes — offset and cursor. A given endpoint is one or the other, fixed in its schema, and the type system enforces the matching hook and response shape end to end.

1. Two Modes — Pick One Per Endpoint

Mode	Request params	Response	Renderer hook	Use it for
Offset	page + limit	{ items, total, page }	usePaginatedQuery	Page navigation, tables, "page 3 of 12", anything that needs a total count
Cursor (keyset)	cursor + limit	{ items, nextCursor? }	useInfiniteQuery	Infinite scroll, chat history, feeds, large/append-only data where offset would drift under concurrent writes

The mode is a property of the endpoint, declared once in its API schema, and is not caller-configurable. Mixing them is a compile-time error, not a runtime hang: usePaginatedQuery rejects a cursor path and useInfiniteQuery rejects an offset path (the path generic is constrained via OffsetPaginatedPath / CursorPaginatedPath, both derived from InferPaginationMode).

Choosing offset vs cursor. Prefer cursor for anything that grows without bound or is read newest-first while being written to (messages, sessions, translate/painting history) — offset's page * limit window silently skips or repeats rows when items are inserted between requests. Prefer offset when the UI shows discrete page controls or needs an exact total (knowledge bases, assistants, files, MCP servers).

2. The Four Layers (Quickstart)

Adding pagination to a list endpoint touches the same four layers as ordering. Each layer has an authoritative doc — this guide is the map.

Layer	What you write	Authoritative doc
1. API schema	query = OffsetPaginationParams / CursorPaginationParams (compose with SortParams / SearchParams); response = OffsetPaginationResponse<T> / CursorPaginationResponse<T>	api-types.md § Pagination Types
2. Server service	Offset: (page-1)*limit + count(*). Cursor: the shared keysetCursor codec + keysetOrdering (never hand-roll the cursor or the ORDER BY)	data-api-in-main.md, services/utils README — keysetCursor.ts
3. Renderer hook	Offset: usePaginatedQuery. Cursor: useInfiniteQuery + useInfiniteFlatItems	data-api-in-renderer.md
4. Query-param wire format	page+limit, cursor+limit, sortBy+sortOrder, search	api-design-guidelines.md § Query Parameters

3. Wire Contract

Request parameters

These four composable param interfaces live in src/shared/data/api/apiTypes.ts (see api-types.md § Pagination Types for the full table):

Type	Fields	Notes
OffsetPaginationParams	page?, limit?	page is 1-based
CursorPaginationParams	cursor?, limit?	cursor is an opaque, exclusive boundary token
SortParams	sortBy?, sortOrder?	sortOrder is 'asc' / 'desc'
SearchParams	search?	Compose as needed

Compose them in a route's query with &:

// Offset list with sort + search
query?: OffsetPaginationParams & SortParams & SearchParams & { type?: string }
response: OffsetPaginationResponse<Item>

// Cursor feed
query?: CursorPaginationParams & { userId: string }
response: CursorPaginationResponse<Message>

Response shapes

Type	Fields	Description
OffsetPaginationResponse<T>	items, total, page	Page-based results
CursorPaginationResponse<T>	items, nextCursor?	nextCursor absent ⇒ no more data
PaginationResponse<T>	union of both	Use only when either mode is acceptable; narrow with isOffsetPaginationResponse / isCursorPaginationResponse

Endpoints frequently extend these base responses with extra top-level metadata — e.g. TranslateHistoryListResponse extends CursorPaginationResponse<T> adds total, and BranchMessagesResponse (GET /topics/:id/messages) adds activeNodeId / rootId / assistantId. The items array and the pagination fields stay exactly as above; useInfiniteFlatItems reads only items while consumers read the extras off pages[0] (see § 5).

Cursor semantics — exclusive boundary

The cursor marks an exclusive boundary: the cursor item itself is never included in the response. Direction is per-endpoint:

Pattern	Use case	Behaviour
"after cursor"	Forward pagination, newer items	Returns items after the cursor
"before cursor"	Backward / historical loading	Returns items before the cursor

For example, GET /topics/:id/messages uses "before cursor" to walk backward through history; other endpoints may page forward. The concrete direction is the endpoint's documented contract.

// Illustrative — load most-recent messages, then older ones
const res1 = await api.get('/topics/123/messages', { query: { limit: 20 } })
// res1: { items: [msg80...msg99], nextCursor: 'msg80-id', activeNodeId: '...' }

const res2 = await api.get('/topics/123/messages', {
  query: { cursor: res1.nextCursor, limit: 20 }
})
// res2: { items: [msg60...msg79], nextCursor: 'msg60-id' }
// msg80 is NOT in res2 — the cursor is exclusive.

Client-side derivations

// OffsetPaginationResponse
const pageCount = Math.ceil(total / limit)
const hasNext = page * limit < total
const hasPrev = page > 1

// CursorPaginationResponse
const hasNext = nextCursor !== undefined

The renderer hooks compute these for you (see § 5) — derive by hand only when calling DataApiService directly.

4. Server Implementation

Offset

Compute offset = (page - 1) * limit, run the page query and a count(*) in one Promise.all, and return { items, total, page }. The canonical real example is AssistantService.list (src/main/data/services/AssistantService.ts, backing GET /assistants):

async list(query: ListAssistantsQuery): Promise<{ items: Assistant[]; total: number; page: number }> {
  const { page, limit } = query
  const offset = (page - 1) * limit
  const [rows, [{ count }]] = await Promise.all([
    this.db.select().from(assistantTable).where(whereClause)
      .orderBy(...orderByClauses).limit(limit).offset(offset),
    this.db.select({ count: sql<number>`count(*)` }).from(assistantTable).where(whereClause)
  ])
  return { items: rows.map(rowToEntity), total: Number(count), page }
}

Keep the same whereClause on both queries so the count matches the page. When filtering against a related table, filter via a WHERE subquery (not a JOIN) so count(*) does not multiply rows.

Cursor (keyset)

List endpoints that page by a (sortKey, id) tuple must use the shared codec and ordering builder in src/main/data/services/utils/keysetCursor.ts — never hand-write the cursor encode/decode, the keyset WHERE tuple, or the ORDER BY. Doing it by hand is how the WHERE predicate and the ORDER BY drift apart and silently skip or repeat rows at the page boundary.

import { asNumericKey, decodeListCursor, encodeCursor, keysetOrdering } from './utils/keysetCursor'

// One direction spec yields BOTH the WHERE predicate and its matching ORDER BY.
const ordering = keysetOrdering(table.createdAt, table.id, { major: 'desc', tie: 'asc' })
const cursor = decodeListCursor(query.cursor, asNumericKey, 'translate-history')

const conditions: SQL[] = [...filterConditions]
if (cursor) conditions.push(ordering.where(cursor))

const rows = await db.select().from(table)
  .where(and(...conditions))
  .orderBy(...ordering.orderBy)   // cannot drift from ordering.where — same dir spec
  .limit(limit + 1)               // fetch one extra to detect "has next"

const hasNext = rows.length > limit
const pageRows = hasNext ? rows.slice(0, limit) : rows
const tail = pageRows.at(-1)
const nextCursor = hasNext && tail ? encodeCursor(tail.createdAt, tail.id) : undefined
return { items: pageRows.map(rowToEntity), nextCursor }

TranslateHistoryService (src/main/data/services/TranslateHistoryService.ts) is the canonical real implementation of this pattern (it returns an extended response that also carries total — see § 3). The codec's full export surface, the <key>:<id> wire format, the empty-key guard, and the list-vs-search decode policy split are documented in services/utils README — keysetCursor.ts.

Decode policy — fall back, don't throw. decodeListCursor treats an absent cursor as "first page" (no warn) and a malformed cursor as a warn-and-fall-back to the first page. A server-issued opaque token going stale must never throw and lock the renderer. (Full-text search uses the opposite policy — see § 6.)

Multi-band cursors are not routable through keysetOrdering. A cursor that encodes more than a single (key, id) tuple — e.g. TopicService.listByCursor, which pages a pinned section then an unpinned section with a first-page sentinel — cannot be expressed as one tuple and keeps its own codec. Do not force such endpoints through the shared helper.

Determinism under ties. keysetOrdering always appends the id tiebreaker ([<major> keyCol, <tie> idCol]), so page-walking stays deterministic even when two rows share the same sort key (e.g. an order_key collision). This is by construction, not a fix for an observed skip/dup — see Ordering Guide § 8 FAQ — fractional-indexing collisions.

5. Renderer Consumption

Offset — usePaginatedQuery

import { usePaginatedQuery } from '@data/hooks/useDataApi'

const { items, page, total, hasNext, hasPrev, nextPage, prevPage } =
  usePaginatedQuery('/assistants', { limit: 10 })   // limit defaults to 10

It manages the page/limit query params internally and resets to page 1 when the rest of the query changes. The full result also exposes isLoading, isRefreshing, error, refresh, and reset. Rejects cursor-paginated paths at compile time.

Cursor — useInfiniteQuery + useInfiniteFlatItems

useInfiniteQuery exposes the raw pages array; consumers derive a flat item list with useInfiniteFlatItems, explicitly picking the order that matches the endpoint and the container layout — never assume page-load order equals display order.

import { useInfiniteQuery, useInfiniteFlatItems } from '@data/hooks/useDataApi'

// Simple feed: page 0 newest, within-page descending — page order == display order
const { pages, hasNext, loadNext, isLoading } = useInfiniteQuery('/feed')
const items = useInfiniteFlatItems(pages)

// Branch-walk in a `column-reverse` chat container: flip each page so the flat
// output is newest-first and feeds straight into the reversed layout.
const { pages, hasNext, loadNext } = useInfiniteQuery('/topics/:topicId/messages', {
  params: { topicId }
})
const messages = useInfiniteFlatItems(pages, { reverseItems: true })
const activeNodeId = pages[0]?.activeNodeId ?? null   // top-level metadata, no cast

// Time-ascending render in a non-`column-reverse` container: flip page order
const ascItems = useInfiniteFlatItems(pages, { reversePages: true })

pages is reference-stable across rerenders while SWR's underlying data is unchanged, so useInfiniteFlatItems(pages) skips recomputation. Rejects offset-paginated paths at compile time. See data-api-in-renderer.md for the hook signatures and the hook-choosing table.

Reorder + pagination

useReorder works on paginated lists transparently. Both OffsetPaginationResponse ({ items, total, page }) and CursorPaginationResponse ({ items, nextCursor }) fall under the same { items } cache branch — metadata fields pass through unchanged on optimistic writes, and any visible row's id is a valid drag anchor even when the list never fits on screen. See Ordering Guide § 4.3 Supported cache shapes.

6. Full-Text Search Pagination

FTS5 search endpoints paginate with the same <key>:<id> cursor format but a different decode policy: a malformed search cursor is a client contract violation and throws 422 (ftsSearch.decodeSearchCursor), whereas a malformed list cursor warns and falls back to the first page (decodeListCursor, § 4). The two share parseCursor / encodeCursor and differ only in the throw policy. The FTS core (candidate filtering, bounded offset scanning, next-cursor assembly) lives in src/main/data/services/utils/ftsSearch.ts; see services/utils README — ftsSearch.ts.

7. Gotchas

• Cursor is exclusive — the cursor row is never re-returned. Off-by-one bugs come from assuming inclusivity.
• Never hand-roll keyset SQL — use keysetOrdering so the WHERE tuple and ORDER BY derive from one direction spec and cannot disagree.
• Always append the id tiebreaker — a sort on the major key alone is non-deterministic under ties and breaks keyset page-walking.
• Keep whereClause identical on the offset page query and its count(*), or total won't match the page.
• List cursors warn-and-fall-back; search cursors throw 422 — don't copy one policy into the other.
• Multi-band cursors keep their own codec — TopicService.listByCursor is not routable through keysetOrdering.
• Page-load order ≠ display order — choose reversePages / reverseItems in useInfiniteFlatItems deliberately.

8. See Also

• api-types.md § Pagination Types — type signatures, guards, Infer* helpers
• data-api-in-renderer.md — usePaginatedQuery / useInfiniteQuery / useInfiniteFlatItems
• data-api-in-main.md — service-layer patterns
• services/utils README — keysetCursor.ts codec + ftsSearch.ts
• api-design-guidelines.md § Query Parameters — wire-format conventions
• Ordering Guide — the sibling list-shaping concern; reorder cache shapes and tie determinism