← RFCs · afintech.dev

RFC: Unbounded History Pagination

Date: 2026-06-09

Status: Adopted. Stage 1 shipped as PR #2498 (transactions, 2026-06-16). On 2026-06-22 we adopted the directive below to extend the reform to all timeseries endpoints; where the directive conflicts with the design sections that follow, the directive wins.

Adopted directive (2026-06-22)

The pagination reform shipped for transactions in PR #2498 worked well; we are now applying it to all timeseries endpoints. Three principles govern each endpoint as it migrates:

  1. Accelerate every filter combination. Every combination of selects on an endpoint's ChXXXFilters struct must be accelerated, through some combination of skip indexes, primary-key selection, and alternate projections. Where it is not feasible to support all combinations, limit or disallow the combinations that are unreasonable to serve rather than serving them slowly.
  2. Partial and empty pages are legal — return early. The pagination contract (docs/internal/overview/pagination.mdx) has been updated to allow a page to come back short or empty while still carrying a next_cursor, so a handler does not have to kill itself to fill a page: if the scan is running long, return early and hand back a scan-boundary cursor ({timestamp_ns}). Ensure every filter combination can return early. Be very careful around the use of FINAL — if you don't need it, don't use it (FINAL forces a full merge-on-read and can potentially reach back unlimited in time).
  3. Backstop every query with max_execution_time. Every ClickHouse query carries max_execution_time (SELECT_SETTINGS, currently 4s) as a last-resort defense; a timeout maps to a 400 telling the caller to narrow the range or add a filter, not a 500.

Where this supersedes the sections below: the target model's item 3 claimed an empty page can only occur at true exhaustion under fill-or-exhaust walking — under the directive, short and empty pages are legal non-terminal responses on any endpoint, and the scan-boundary cursor form (reserved in item 1) is now part of the documented cursor grammar. Termination is unchanged: the absence of next_cursor, and nothing else. Fill-or-exhaust slice-walking (§Design) is no longer the required mechanism — a handler should satisfy the contract with a single index-aligned keyset query where the schema allows (what transactions shipped), returning early with a boundary cursor only where it cannot.

Background

In early June we shipped a hard 7-day cap on all history endpoints (MAX_HISTORICAL_QUERY_WINDOW_NS, rs/sdk/src/protocol/time_range.rs): both time bounds are required, and any range wider than 7 days is rejected with a 400. This was a deliberate hotfix lineage:

The cap stopped the bleeding, but on 2026-06-09 it produced its first customer-facing incident: a Flowdesk customer's withdrawals "disappeared" from the Admin UI because they were older than the UI's largest selectable window. "Show me all withdrawals for this customer" is now impossible to express against the API — by design.

Meanwhile the GUI has independently grown three client-side workarounds:

Every external API consumer (e.g. Flowdesk's integration, ops curl scripts) must rebuild the same loop. When a client-side loop must be reimplemented by every caller of an API, the loop belongs on the server.

Problem statement

The 7-day cap bounds the wrong variable. Query cost in ClickHouse is not driven by the width of the requested time range; it is driven by how many granules the query reads, which is a function of how well the query's predicates align with the table's primary key, partitioning, and skip indexes. Concretely:

The cap therefore simultaneously over- and under-protects, while exporting a window-walking loop to every client and silently hiding data in UIs that don't walk.

Industry survey (condensed)

The consensus mechanism for "unbounded history without unbounded scans" is: cursor = bounded per-page work, server-side slicing for non-indexed predicates, scan budgets as the backstop.

Target semantic model

The current public contract (docs/internal/overview/pagination.mdx) is mostly right — it already documents the time range as optional. Stage 1 restores that promise (the 7-day hotfix violated it) and makes four things precise, without adding response fields:

  1. The cursor format is retained: {timestamp_ns}:{id}, a real row position. The server walks slices to fill-or-exhaust (see Design), so a page is either full — anchored on its last row — or terminal; the cursor never has to encode a partial-scan boundary, and stays the documented row format. We still describe it to clients as "pass it back unchanged" so a later move to a versioned/opaque token (to pin an "as-of" upper bound or carry walking progress) doesn't break callers, but no such token ships now.
  2. The time range is an optional filter, subordinate to the cursor. Both bounds may be omitted; an open-ended request scans from now back to the start of history. The client no longer owns a mandatory window — the cursor owns position.
  3. Termination is the absence of next_cursor, never a short or empty page. A page with fewer than limit rows is not a stop signal; only a missing next_cursor is. No complete flag and no searched_until_ns watermark are needed — next_cursor: None carries exactly the "exhausted" signal, and fill-or-exhaust walking guarantees an empty page only occurs at true exhaustion (so it is always terminal). The current "look-ahead" wording equates a non-full page with the last page; that is wrong once the server walks slices and must be removed before third parties couple to it.
  4. Counts are lower bounds or absent. total_count on cursor pages is semantically ambiguous (count of which scope?) and costs a COUNT(*) ... FINAL per page. Omit it for wide/unbounded ranges; where provided, it means "at least N."

Design

DB layer (ClickHouse)

Align the index with the dominant query. History queries are overwhelmingly "one entity, ordered by time."

API layer (api-gateway / order-gateway)

Accept unbounded ranges; walk windows server-side to fill-or-exhaust. The handler chunks the requested (or open-ended) range into slices — MAX_HISTORICAL_QUERY_WINDOW_NS becomes the slice size instead of a rejection threshold — and queries slice-by-slice in sort order until the page fills or the range is exhausted:

resolve range:  start = requested or 0; end = requested or now (pinned)
position     =  cursor position, else range edge per sort direction
loop:
    query slice [max(start, pos − slice), pos) with remaining limit + 1
    accumulate rows; advance pos to slice boundary
    stop when limit + 1 rows collected or range exhausted
respond:
    next_cursor = last row position (page full) | absent (range exhausted)

Because the loop only stops on a full page or true exhaustion, next_cursor is always a real row position or absent — never a partial-scan boundary. That is what keeps the {ts}:{id} cursor format and lets termination ride entirely on next_cursor: None, with no complete/searched_until_ns. The cost is unbounded request latency on very sparse open-ended scans: each slice is max_execution_time-boxed, but the number of slices a single request walks is not capped.

The GUI's stepWindowedQuery is a working prototype of exactly this algorithm, written in the wrong tier; the reform ports it down the stack.

Backstops, not gates: per-slice max_execution_time bounds each query; a filter-aligned index (below) keeps each slice cheap. A whole-ledger admin scan with no entity filter remains the one genuinely unprunable query; option: require an entity, symbol, or type filter for open-ended ranges on admin endpoints. Per-query cost protection is strictly stronger than the range cap (a 7-day window over a hot month is still a heavy query today), which also addresses the admin-vs-trading-traffic isolation concern.

If tail latency bites — a request walking years of empty slices — the escape hatch is a per-request MAX_SLICES_PER_REQUEST cap (e.g. 13 ≈ 90 days). A cap means a request can stop mid-scan and hand back a slice-boundary resume position on a possibly-empty page, which in turn requires the empty-page-with- cursor contract and a versioned/opaque cursor v2 ({position: row(ts, id) | boundary(ts), as_of_end_ns, …}, base64; legacy {ts}:{id} cursors still decode to a row position). We do not ship this now; it is recorded as the known next step if fill-or-exhaust proves too slow.

GUI layer

Docs / public contract

Update docs/internal/overview/pagination.mdx (and any public derivative) to: remove the "Look-ahead pattern" section (it teaches "non-full page ⟹ done", which is wrong once the server walks slices — the limit + 1 trick survives in the implementation, just not as the documented termination rule); keep "Client-side iteration" (its "continue until next_cursor is absent" rule is already correct); state that the time range is optional and total_count may be omitted for wide/unbounded ranges. The cursor format and the rest of the contract are unchanged. The public version must describe iteration behaviorally, without naming the datastore.

Rollout

  1. Transactions endpoints first (the incident surface) — shipped as PR #2498: 7-day cap lifted, plain keyset cursor (no slice-walking needed — the timestamp-led PK short-circuits ORDER BY/LIMIT), FINAL dropped in favor of event_id dedup on read, symbol skip index added, per-page COUNT(*) dropped, max_execution_time backstop with timeout mapped to 400.
  2. All remaining timeseries endpoints (fills / trades / funding-rates / funding-transactions / orders) follow the same shape, per the adopted directive above. (historical_orders already has its projection; trades is symbol-led and may want a per-account projection or index of its own. Funding-rates started in PR #2099 — see that review for directive gaps to close: per-page COUNT(*) retained, no max_execution_time on its queries.)
  3. GUI simplification per above, including reverting the load-bearing parts of PR #2331's filter clamping to optional UX.
  4. Docs rewrite lands with stage 1, before third parties couple to the current cursor format and termination semantics.

What we are explicitly not committing to

Open questions