TLDR: Stop aggregating in JavaScript. Move the math into a Postgres SQL function, call it via supabase.rpc(), and MATERIALIZED the shared base CTE so it's computed once. You get correct and fast in the same fix.

The Setup

We were building a live inventory + velocity dashboard for a supply-chain ops app — think SKU-level sales rates at 7d, 30d, 90d, and 180d windows, plus a little sparkline per product, all running off Supabase.

The reports loaded. The numbers looked reasonable. We shipped it.

The numbers were wrong.

The Wall

It took an embarrassingly long time to figure out why.

The query was pulling order line items, then we were doing all the aggregation in JavaScript — summing up units sold per SKU, dividing by window, calculating run rates. Pretty standard. Seemed fine.

Except Supabase's PostgREST has a silent row cap: 1,000 rows by default. No error. No warning. The response just… stops at row 1,000.

So when we had ~98 products × months of daily order rows, JS was doing math on a truncated slice. The shape was correct, the numbers were fiction.

The tell — and I wish I'd known this earlier — is suspiciously round result counts. If your row count is exactly 1,000, you've been silently truncated.

What Didn't Work First

I bumped the max_rows limit in Supabase Studio. That postpones the problem.

I tried .range(0, 99999) on the supabase.rpc() call. Doesn't help. The project-level db-max-rows cap ignores the Range header entirely.

The Fix That Worked

Move the math into Postgres.

I wrote report_velocity, report_ledger, and report_forecast as proper SQL functions in migration 009_report_functions.sql. Postgres does the aggregation, returns one summary row per product (~98 rows total), and nothing gets truncated because the result is already compact.

Call it like supabase.rpc('report_velocity', { args }). Done.

But there was a second problem: the function itself was slow.

It was re-scanning the base order data for every window — once to compute rate_7d, again for rate_30d, again for rate_90d, again for rate_180d, and then again to build the sparkline. Five passes over the same rows.

The Actual Win: MATERIALIZED CTEs

By default Postgres can inline a CTE and re-evaluate it every time it's referenced. So a shared base CTE fed into five different window derivations gets re-scanned five times.

MATERIALIZED forces Postgres to compute that base set once, stash it, and then let every window function draw from that single result.

WITH base AS MATERIALIZED (
  SELECT ...
  FROM order_line_items
  WHERE ordered_at >= NOW() - INTERVAL '90 days'
)
SELECT
  product_id,
  SUM(...) FILTER (WHERE ordered_at >= NOW() - INTERVAL '7 days')  AS rate_7d,
  SUM(...) FILTER (WHERE ordered_at >= NOW() - INTERVAL '30 days') AS rate_30d,
  -- etc.
FROM base
GROUP BY product_id

One pass. All four windows AND the sparkline derived in a single scan of base.

The commit message said it better than I will: "cap items at 90d, MATERIALIZE CTEs, single-pass sparkline." That's the whole trick.

Why This Matters

The lesson isn't really about MATERIALIZED. It's that the database is better at this than your app server — and once you accept that, a bunch of things fall into place.

Move aggregation server-side so you get correct data. MATERIALIZE the shared base so you get fast data. Let Postgres do the work it was built for, and your JS just renders what comes back.

Anytime I'm about to reduce over a big query result in JavaScript now, I stop and ask: does Postgres already know how to do this?

Nine times out of ten it does. And it does it in one pass.