> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cfosilvia.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SUPABASE PAGINATION

# Supabase reads and the invisible 1,000-row cap

**TL;DR:** PostgREST (Supabase's API) returns at most **1,000 rows per
request** and does **not error** past the cap — it silently hands you the
first 1,000. Any read that can match more must go through
[`lib/supabase/fetch-all-pages.ts`](../lib/supabase/fetch-all-pages.ts).
Reviewers: treat an unpaged `.select()` on a per-holding/per-day table as a
bug, not a style choice.

## Why this is dangerous specifically here

This app's history tables are **per-item × per-day**: one row per holding
(or token, property, metal…) per calendar day. Row counts multiply:

| Read | Rows matched |
| - | - |
| 10 positions × 400-day chart window | 4,000 |
| 150 Coinbase tokens × 7-day change window | 1,050 |
| 143 positions × 7-day change window | 1,001 |

When the cap bites, the failure is the worst kind: **wrong numbers, no
error.** A truncated chart series under-sums the dates that lost rows (the
chart drifts from the page header — the exact class of incident from
2026-07-28); a truncated change window silently zeroes some holdings'
"today" moves. It also ambushes precisely the *best* accounts — long
histories, many positions — and only after enough history accumulates,
so it passes every fresh-account test.

## The pattern

```ts theme={null}
import { fetchAllPages } from '@/lib/supabase/fetch-all-pages'

const rows = await fetchAllPages<SnapshotRow>((from, to) =>
  supabase
    .from('snapshots_by_x')
    .select('date_synced, market_value')
    .eq('clerk_id', userId)
    .gte('date_synced', since)
    .order('id', { ascending: true }) // REQUIRED — see below
    .range(from, to),
)
```

The helper requests rows 0–999, then 1,000–1,999, … until a page comes back
short, and **throws on a failed page** rather than returning a partial set.
It also rejects a `pageSize` outside `[1, 1000]` up front — above the cap,
every page *looks* short and the loop would stop after one page, silently
reintroducing the truncation it exists to prevent.

### Rules

1. **Stable order is mandatory.** Pages are separate requests; without a
   deterministic total order, rows can repeat or vanish between them. If
   you need a domain ordering (e.g. `date_synced`), keep it and append
   `.order('id', { ascending: true })` as a tiebreaker — many rows share a
   date, and ties make the order non-deterministic across pages.
2. **Build a fresh query inside the callback.** PostgREST query builders
   are one-shot; the helper calls your factory once per page.
3. **Filters stay identical across pages** — never vary anything but the
   range.

## When you do NOT need it

* **Bounded-by-construction reads**: one row per day per user
  (`daily_snapshots_summary` over 7 days = 7 rows), single-entity lookups,
  `count`-only queries.
* **Deliberately-limited reads**: a `.limit(50)` "recent activity" list is
  a product decision, not a truncation bug. Say so in a comment, or the
  next reader can't tell the difference.
* **Server-side aggregation**: if you only need a SUM per day, a view/RPC
  that aggregates in SQL beats shipping thousands of rows to the client —
  prefer it for new heavy surfaces; `fetchAllPages` is the client-side
  answer where per-row data is genuinely needed.

## The sibling trap: unbounded `.in()` lists

Same transport layer, opposite direction. A `.in('symbol', hugeArray)`
builds one giant GET URL, and past \~a-few-thousand characters the server
answers **414** — failing the whole read. And *capping* the list instead
silently drops the tail. The fix is **chunking** (500 per request, the
`MAX_LOOKUP_SYMBOLS` convention) and merging results — see the
`tracked_tickers_latest` lookup in `use-public-investments-series.ts`.

## Where it's already used

Chart series: `use-public-investments-series` (3 legs + classification),
`use-crypto-series`, `use-property-series`, `use-precious-metals-series`.
Day-change windows: `use-holdings-daily-snapshots` (3 legs),
`use-crypto-daily-snapshots`, `use-coinbase-daily-snapshots`,
`use-plaid-crypto-holdings`.

**Checklist for a new bulk read** (agents: apply this on any PR that adds a
`.select()` over a per-item × per-day table):

* [ ] Can this match >1,000 rows for a large account? If yes → `fetchAllPages`.
* [ ] Stable `.order(...)` ending in a unique column (`id`)?
* [ ] Any `.in(...)` list derived from user data? → chunk at 500.
* [ ] If intentionally limited, is the `.limit()` commented as a product choice?


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.