Skip to content

Lists, paging and search

A list screen is a promise: this is what exists. These rules make the promise testable.

Rules: PLT-050 … PLT-058.

1. Why this exists

An audit found fetchBookings() asking for one 300-row page, and six screens treating that page as "all bookings" — the ops approval queue, the corrections queue, finance's payment workflow, airline seat reconciliation, the group booking picker and the sidebar badge. Nothing failed. The queues were simply short.

It was not only bookings. PostgREST runs with max_rows = 1000 (supabase/config.toml), so every list read without an explicit window was silently truncated there — no error, no signal — and the customers, visa, ticketing, requests, leads, quotations, group-roster and portal screens all filtered, sorted and counted that capped page in the browser. The audit log was worse: a hard .limit(500) with no offset or cursor, so compliance evidence older than the newest 500 events was unreachable.

2. The shape

src/lib/paging.ts is the whole contract, shared byte-for-byte across workstreams:

interface PagedResponse<T> {
  data: T[];
  nextCursor: string | null;   // null means last page, and nothing else
  total?: number;
  totalIsEstimate?: boolean;
}

interface PagedQuery {
  cursor?: string | null;
  limit?: number;
  sort?: string;
  dir?: 'asc' | 'desc';
  q?: string;
  withTotal?: boolean;
}

const DEFAULT_PAGE_SIZE = 50;
const MAX_PAGE_SIZE = 200;

Every caller either pages to exhaustion or states, on screen, that it is showing a page of a larger set.

On the web, a list states it with the paging bar (src/components/common/ListPager.tsx): rows per page, "1–25 of 312" and first, previous, next and last page, shown even on one page. src/hooks/usePageWindow.ts shows the loaded rows a page at a time and calls the list's loadMore when the reader moves past what is loaded, so the server's cursor pages and the reader's numbered pages are independent. The phone app keeps a Load more button.

3. Keyset, not offset

Paging walks (sortColumn, id) with a tuple comparison, not OFFSET.

With OFFSET a row inserted while an operator pages through an approval queue shifts every later page, so a booking can be shown twice or skipped entirely — in a queue, "skipped" means "never approved".

Mechanics:

  • The reader asks for limit + 1 rows. The extra row is the has-more probe and is dropped.
  • MAX_PAGE_SIZE is 200, comfortably under PostgREST's 1,000, so a full page can never be a server truncation in disguise. A caller asking for more gets 200, not a silent cut.
  • limit is clamped; a nonsense limit falls back to DEFAULT_PAGE_SIZE.
  • The sort column is NOT NULL and comes from the endpoint's whitelist. Any other sort value is a 400, never silently ignored — so a sort key can never name a column the caller was not meant to order or probe by.
  • A malformed cursor is rejected, never treated as "start from the top".
  • A read that still uses the legacy unpaged shape logs a breadcrumb when it comes back at the max_rows cap (warnIfTruncated()). No .limit(N) above 1000 exists anywhere.

A cursor is a position, never a capability

A cursor carries a sort value and a row id the caller was already shown. It grants nothing: every page re-applies the same tenant scope (agentId, customerId) and the same RLS, so a cursor minted by one partner selects nothing for another (ACC-010). Cursors are never signed, never trusted, and never used in place of a permission check. A filter value is quoted into the PostgREST predicate, so a comma or a bracket in a customer's name cannot be read as filter syntax. (PLT-058)

4. Filtering, searching and counting happen in the database

Filters and free-text search run server-side against the whole table, not over the rows the browser happens to hold. Counters and KPI tiles come from a count query with the same filters as the list — never from rows.length (PLT-051).

Exact counts are opt-in (withTotal=1), because an exact count is a full scan. The default is a planner estimate, flagged as such by totalIsEstimate.

Bookings are the worked example: one SQL predicate with ten bound parameters serves booking_page, booking_stats, booking_filter_ids and booking_filter_count, so a page, its count, its KPI tiles and its bulk target set cannot drift apart. Search is substring and trigram rather than a stored search_vector, because a denormalised column on Booking cannot read Customer or BookingPassenger rows and would be silently wrong. The bookings list itself calls booking_list_page, which runs booking_page and returns the page's rows with everything they show, and the total, in one round trip — see A screen is one call.

Scope is not a permission the caller can lift: a partner's agentId is applied on every page regardless.

Dates and order

A list takes from and to as YYYY-MM-DD, both included, on the Indian calendar (UX-030). Anything else is a 400. The server turns them into the range the column needs:

The list's date is from becomes to becomes
an instant (createdAt, stored UTC) >= midnight IST that day, as UTC (istDayStartUtc: 29/09 → 2026-09-28T18:30:00.000) < midnight IST the day after
a calendar date (a departure, a payment date) >= the date < the day after

applyDateRange(query, paging, column, kind) does this for a PostgREST list; pagedQuery takes dateColumn / dateKind. The one-call lists (customers_list_page, leads_list_page) get the instants from listPageArgs and compare >= / <; visa_list_screen gets them from /screens/visa and returns each row's sortKey, which is the next page's cursor whatever the order. The bookings list also accepts an instant for from / to (used as sent, to exclusive), for the older callers.

Before this, four lists compared createdAt <= to with a bare date, which is midnight at the start of that day: "to 29/09" silently dropped the whole of the 29th. They now go through applyDateRange.

sort / dir are whitelisted per list (PLT-056) and part of the query key on the screen, so a page read in one order is never shown in another. On screen, useListParams keeps q, the filters, from / to and sort / dir in the address.

5. Bulk actions resolve their target set on the server

A bulk action carries either an explicit id list, or a filter plus exclusions — never "the rows currently on screen". When it carries a filter the server resolves the set, and the confirmation shows the count the server returned (PLT-052, UX-001 at the typed-confirmation level for cancel and delete). It runs in batched server calls with the permission checked per batch, not one HTTP request per row.

Any .in('column', ids) built from a caller-supplied list is chunked at ID_BATCH_SIZE (100). An unbounded list grows the request URI with the data set and fails wholesale once it is too long, which reads as a broken page rather than a too-large query (PLT-053).

6. Exports fail loudly

src/lib/exportAll.ts:

An export that silently stops at the first page ceiling is worse than no export at all. Every loop in this file therefore ends in one of two states: the server said "no more pages" (complete), or we throw. Never a truncated result.

exportAll() and eachPage() throw PageCeilingError when the page ceiling (1,000 pages) is reached with a cursor still pending, when the row ceiling (200,000) is passed, when a cursor repeats, when paging makes no forward progress, or when the caller aborts.

fromOffset() adapts the endpoints that still page by offset, and adds two more refusals: an endpoint that returns rows it has already served — "it does not support offset paging, so the export cannot be proven complete" — and a page longer than the one requested.

The server-side equivalent is fetchAllPages(), used by the reports that page in TypeScript. It throws rather than returning what it has:

This read passed 200,000 rows without reaching the end. Refusing to return a partial result — narrow the date range or filters and try again.

7. A capped section says what it is hiding

Where a section keeps a fixed cap for good reason — Customer 360's requests (50) and messages (20) tabs, a group's "needs attention" rollup, an incident list — it returns the total and a way to page the rest, so nothing disappears without the reader knowing. A rollup that aggregates an unbounded set takes its window on the outer keyset before the expensive aggregation runs (PLT-057).

8. Where to look

Concern Path
The contract src/lib/paging.ts
Generic keyset reader src/lib/api.ts — parsePaging, applyCursorFilter, pagedQuery, toPagedResponse
Id chunking src/lib/chunk.ts
Exports src/lib/exportAll.ts
Interactive paging hooks src/hooks/usePagedQuery.ts, usePagedList.ts
Bookings page, stats, bulk ids supabase/migrations/20260920130000_bookings_paging_search.sql
Incidents, journey board, Customer 360 tabs supabase/migrations/20260920120300_list_paging_rpcs.sql
Customers and leads pages in one call (the same keyset in SQL, cursor still built by api.ts; PRF-010) supabase/migrations/20260930080000_customers_are_one_call.sql
Indexes supabase/migrations/20260920120200_list_paging_indexes.sql
Tests supabase/tests/list_paging.sql, bookings_paging.sql, finance_paging.sql, customers_are_one_call.sql, src/lib/exportAll*.test.ts, src/lib/api.paging*.test.ts, src/lib/chunk.test.ts

The numbers beside the menu items come from one request, GET /badge-counts, answered by badge_counts() (PLT-051). Each count uses the definition its list uses — requests open or submitted, suppliers active, leads new or contacted — and runs under the signed-in person's own row policies, so a badge never shows a total they could not list. A person gets badges only for lists they may open.

On the role dashboard and the phone home the same badge_counts() answer arrives inside dashboard_screen() (PRF-002), and the sidebar makes no request of its own while that screen is open.

Until 27 September 2026 ten of these were counted in the browser from whole lists fetched every minute. Three of those lists are paged, so their badges stopped at the page size; and the requests badge counted statuses that do not exist and so never showed anything. If one counter fails now, it shows no badge and is named in the response's errors — the others still count.