Skip to main content

​
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. 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: 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

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?