SoW: Unbounded History Pagination — remaining work

Fills and trades are the only history endpoints that still reject time ranges wider than 7 days. This SoW migrates them to unbounded cursor pagination, removes the GUI code that works around the 7-day limit, and then removes the limit from the codebase. Transactions, funding transactions, funding rates, and orders already meet the requirements below.

Tracking: TBD.

Requirements for a history endpoint

The client-facing contract is docs/internal/overview/pagination.mdx. On the server, every history endpoint meets these five requirements:

The time range is optional. A request may omit either bound or both.
The cursor owns position. A page may be short or empty and still carry a next_cursor. Iteration ends only when next_cursor is absent. A handler whose scan runs long returns early with a scan-boundary cursor ({timestamp_ns}).
Every accepted filter combination is pruned by the primary key, a skip index, or a projection. The endpoint rejects combinations it cannot prune with a 400.
No FINAL and no per-page COUNT(*). The handler deduplicates rows on read. total_count is absent or a lower bound.
Every query carries SELECT_SETTINGS (max_execution_time=4). A timeout returns a 400 that tells the caller to narrow the range or add a filter. The handler detects the timeout by downcasting to KlickhouseError::ServerException { code, .. }, not by matching error text.

Current state

Server. get_historical_fills and get_historical_trades (rs/api-gateway/src/utils.rs) serve the user /fills route and the admin fills and trades tabs. They are the only callers of ensure_within_max_window(MAX_HISTORICAL_QUERY_WINDOW_NS). Their queries (rs/sdk-internal/clickhouse/src/trades.rs) read trades FINAL, run a COUNT(*) … FINAL on every page, carry no max_execution_time, and use a cursor predicate without a range conjunct.

Table. trades is a ReplacingMergeTree with PRIMARY KEY (symbol, timestamp_ns, trade_id), monthly partitions, and bloom indexes on the two order-id columns only. The account filter (taker_account_id = ? OR maker_account_id = ?) prunes no granules.

GUI. These clamps and walkers remain:

Location	Behavior	Endpoint has a cap
App and california order history: `timeframeToQueryParams`, `isRangeTooWide` in `TimeframeFilter`	Clamps a picked range to the latest 7 days; rejects picks wider than 7 days	No
Admin `useOrders`, `useTransactions`, `useFunding`: `normalizeHistoryRangeNs`	Clamps ranges wider than 7 days to the latest 7 days	No
Admin `useLendingPoolActivity`: `normalizeHistoryRangeNs`	Same clamp	Confirm in PR 1
Admin `useFills`, `useTrades`	Same clamp, plus the "walk back 7 days" controls	Yes, until PR 3
App `useFills` with `useWindowedCursorPagination` and `stepWindowedQuery`	Walks 7-day windows client-side, up to 90 days back	Yes, until PR 3

gui/packages/admin/src/utils/filterTime.ts labels MAX_HISTORY_WINDOW_MS as "backend cap: 7 days", and the admin preset list stops at 7d.

Alerting. The clickhouse-query-timeouts/order-gateway rule watches clickhouse_query_timeouts_total for order_gateway. api_gateway, which serves fills and trades, has no metrics recorder and no rule.

Work

Order: PR 1, PR 2, and PR 5 have no dependencies. PR 3 needs PR 2 live on the target environment. PR 4 needs PR 3 deployed. PR 6 needs PR 3 and PR 4.

PR 1 — Remove 7-day clamps on uncapped endpoints (GUI)

App and california order history: timeframeToQueryParams passes the picked range through unchanged. A single bound stays a single bound. Remove the isRangeTooWide check from TimeframeFilter.
Admin useOrders, useTransactions, and useFunding stop calling normalizeHistoryRangeNs. Do the same for useLendingPoolActivity after confirming its endpoint accepts wide ranges.
Until PR 4, only admin useFills and useTrades clamp.
Correct the "backend cap: 7 days" comments in filterTime.ts. Add 30d and All presets to the tabs whose endpoints have no cap.

PR 2 — `trades` account indexes (DDL and ops)

Add bloom_filter(0.01) skip indexes on maker_account_id and taker_account_id in db/clickhouse/init.sql. Apply with just migrate-db <env>.
Existing parts carry no index until materialized. Add a one-off db/clickhouse/a-XXXX-materialize-trades-account-idx.sql that runs MATERIALIZE INDEX for both indexes, and run it on demo and prod.
Verify on each environment: EXPLAIN indexes=1 on an account-scoped trades query for a low-activity account shows skipped granules, and the same query finishes inside the 4-second budget.

PR 3 — Migrate the fills and trades server path

Rewrite ChTradeFilters, ChTradesQuery, and both handlers to the shape used by rs/sdk-internal/clickhouse/src/transactions.rs:

Remove the ensure_within_max_window calls. The range becomes optional.
Remove FINAL. Deduplicate by trade_id within the page, and overfetch enough rows to fill the page after duplicates collapse.
Remove the per-page COUNT(*).
Add SETTINGS {SELECT_SETTINGS}. Map the timeout to a 400 by downcasting, and call record_query_timeout() in that branch.
Add the range conjunct to the cursor predicate (ts <= $c AND (…) for descending, ts >= $c AND (…) for ascending).
Assemble pages with Page::from_overfetch and return a scan-boundary cursor when the scan stops early.
Update the utoipa docs on /fills and the admin routes: the range is optional, pages may be partial, and clients iterate on next_cursor.

Both handlers share these helpers, so one PR covers the user route and both admin tabs.

Tests: cursor round-trip across a page boundary that contains collapsed duplicates; timeout returns 400; unbounded account-scoped query; continuation from an empty page that carries a boundary cursor.

PR 4 — Delete the fills walkers (GUI)

App useFills.tsx uses useCursorPagination, the same way useHistoricalOrders does.
Delete useWindowedCursorPagination.ts, and delete stepWindowedQuery, HISTORY_MAX_LOOKBACK_NS, and the rest of the windowing code in gui/packages/app/util/historyWindow.ts with its tests.
Admin useFills and useTrades pass ranges through unclamped and page with an infinite query and "Load more". Remove the "walk back 7 days" controls.
Show "N+" where total_count is absent.

PR 5 — Timeout alerting for `api_gateway`

Give api_gateway a metrics recorder so that clickhouse_query_timeouts_total is exported.
Add a clickhouse-query-timeouts/api-gateway rule to configs/grafana/alerter-prod.yaml.tpl and alerter-demo.yaml.tpl, matching the order-gateway rule.

This PR has no dependencies. Deploy it before PR 3 so that a slow access path raises an alert.

PR 6 — Remove the 7-day constant

Delete MAX_HISTORICAL_QUERY_WINDOW_NS and ensure_within_max_window from rs/sdk/src/protocol/time_range.rs, with their tests. After PR 3 they have no callers. Both are public SDK items, so this is a breaking SDK change and needs a changelog entry.
Delete normalizeHistoryRangeNs, MAX_HISTORY_WINDOW_MS, and the allowWideRange option from the admin GUI once nothing calls them.
Delete this SoW.

Decisions to confirm in PR 3

Question	Proposed answer
Admin fills or trades request with no account, symbol, or order filter and an unbounded range	Reject with a 400 that asks for a filter or a bounded range. `validate_historical_orders_bounds` does this for orders.
`total_count` on fills and trades	Omit it. Transactions omits it, and the admin tables render without it.

Performance check after PR 3

Measure p99 latency of unbounded account-scoped fills, trades, and orders requests on prod for one week, along with the daily count of timeout 400s per endpoint. Two items depend on the result:

Item	Do it when
Add a `(timestamp_ns, trade_id)` projection to `trades`. This requires `deduplicate_merge_projection_mode` on the table and a rebuild of existing parts.	Requests with no symbol filter spend most of their budget sorting.
Add the scan-boundary early return to the orders handler. Today an orders scan that exceeds the budget returns a 400.	Account-scoped orders requests still time out with `proj_account_ts` in place.

Gates

The account indexes are materialized on demo and prod, and EXPLAIN indexes=1 shows skipped granules on an account-scoped trades query.
/fills, admin fills, and admin trades accept an unbounded range, return a 400 on timeout, run no per-page count, and page correctly across collapsed duplicates.
stepWindowedQuery, useWindowedCursorPagination, and normalizeHistoryRangeNs do not exist. No code in app, admin, or california clamps a range to 7 days.
An admin can page one customer's full fills, trades, orders, and transactions history to exhaustion in the admin UI, and the app shows a user's full history with no truncation.

Out of scope

A versioned or opaque cursor format, and a per-request cap on scan slices.
Endpoints that paginate by limit and offset (users, risk profiles). They keep total_count.
read_overflow_mode='break' partial results.