TLDR: If your Supabase aggregation numbers look plausible but wrong, check the row count first. PostgREST silently caps at 1,000. Push the math into a SQL function and call it via rpc().

the setup

I was building a supply chain dashboard for an ecommerce business — inventory levels, sales velocity, a ledger of stock movement by day.

The data lived in Supabase (Postgres as a service, with a REST layer called PostgREST on top).

I pulled the rows into Next.js and ran .reduce() over them in JavaScript. Clean, readable, totally felt fine.

what broke

The client demo was two days out. I checked the numbers.

report_sales_daily showed data covering just 13 days.

I knew that was wrong — we'd shipped far more units in that window.

The data shape looked right. The types were right. The total was just… off by 6×.

I spent an hour chasing the filter logic before I thought to look at the row count.

It was exactly 1,000.

why that number is a red flag

Supabase's PostgREST has a max_rows setting. Default: 1,000. No error. No warning in the response. It just stops.

My report_ledger_daily(180) query was 16 SKUs × 180 days = 2,880 rows. PostgREST handed back 1,000 of them.

Here's the evil part: the SQL sorted day ASC, so truncation cut the newest rows — exactly the rows a "last 90 days" chart needs. My client-side time filter then stripped the old rows too. Empty chart. "Apply migration 029" empty state staring back at me.

The number I'd been computing in JavaScript covered just 13 days of data instead of the full 81.

Looked plausible. Was COMPLETELY wrong.

what I tried that didn't work

  • Bumped max_rows to 50,000 in Supabase Studio → Project Settings → Data API. Fixes the immediate symptom. Doesn't fix the foot-gun — you just raised the height of the cliff.
  • Tried .range(0, 99999) on supabase.rpc(). Nope. The project-level cap ignores the Range header entirely. I thought that would work. It does not.

the fix that actually worked

I moved every aggregation into Postgres.

Three SQL functions: report_velocity, report_ledger, report_forecast. All the grouping, summing, window functions — server-side, inside the database, where the data actually lives.

-- Before: 2,880 rows → JS reduce → wrong number
-- After: ~98 rows (one per product) → render
SELECT * FROM report_velocity();

Called via supabase.rpc('report_velocity', { args }).

I also used MATERIALIZED CTEs inside each function so Postgres doesn't re-plan on every call. The commit message I wrote at 1am: fix(reports): move all aggregation into Postgres SQL functions. One of those commits that should have been commit number one.

why this matters to me

There's a principle here I keep re-learning: the database is better at reducing your data than your application layer.

The failure mode is sneaky because everything looks fine — the data shape is right, the types are right, the numbers are just quietly wrong.

Now, any time I see a Supabase total that feels low, the first thing I do is log the raw row count. If it's suspiciously round — 1,000, 10,000 — I stop and push the aggregation down.

The rule I wrote for myself: if you're reducing in JS, ask whether Postgres could do it with a single pass and hand you back 10 rows instead of 10,000.

Almost always: yes.

P.S. Not saying push everything server-side. For sort keys on paginated, filterable queries, a generated column often beats an rpc() call — because RPC can't compose with the supabase-js builder chain. Every layer has its tradeoffs. But for aggregation over unbounded row sets? SQL wins, every time.