Three callers, one RPC: when to consolidate aggregation

The same client-side aggregation showed up in three different places. The migration to fix it was 60 lines of SQL and saved ~600KB of transfer per page load.

The smell

We had a producer leaderboard with three independent surfaces: the public /producers page, the dynamic OG card at /api/og?type=leaderboard, and the rank pill that shows up on individual producer profiles. All three needed the same thing: top producers ranked by saves over a window.

And all three independently scanned saved_beats, joined to beats, and tallied by producer in JS. Three slightly-different aggregations — the OG handler computed drops separately, the public page used a different time window than the OG card, the rank pill returned the wrong rank when the producer had a unicode character in their name. Three independent bugs, one underlying duplication.

The migration

create or replace function producer_leaderboard_window(
  p_window text default '7d',
  p_limit  int  default 30
) returns table (rank int, producer text, saves int, drops int)
language sql stable security definer
as $func$
  with bounds as (
    select case
      when lower(p_window) = '24h' then now() - interval '24 hours'
      when lower(p_window) = '30d' then now() - interval '30 days'
      else now() - interval '7 days'
    end as since
  ),
  saves_in_window as (...),
  drops_in_window as (...)
  select row_number() over (order by saves desc, producer asc),
         producer, saves, coalesce(drops, 0)
  from saves left join drops using (producer)
  limit greatest(1, least(p_limit, 100));
$func$;

Two CTEs (saves_in_window, drops_in_window) joined on producer, ordered by save count, ranked with row_number(). Window param is bounded so a malformed query can't ask for "all-time" and force a full scan. Limit is capped at 100 inside the function so the same defense exists no matter who calls it.

What changed at the callers

Each caller went from "scan + aggregate in JS" to a single rpc() call. The OG handler shrank from 50 lines to 15. The public page shrank from a 500-row PostgREST select to a single RPC. The rank pill went from a 200KB transfer to a single integer.

// Before — /p/:name rank pill
const { data } = await supabase
  .from('saved_beats').select('beat:beats(producer)')
  .gte('created_at', sevenDaysAgo).limit(5000);
const tally = new Map();
for (const r of data) tally.set(r.beat?.producer,
  (tally.get(r.beat?.producer) || 0) + 1);
const ranked = [...tally.entries()].sort((a,b) => b[1]-a[1]);
const rank = ranked.findIndex(([p]) => p === me) + 1 || null;

// After
const rank = await rpc('producer_rank_in_window', {
  p_producer_name: me, p_window: '7d',
});

What it cost

One migration file, sixty lines of SQL, three caller rewrites. One afternoon. The deploy is backwards-compatible: the old handlers catch missing-RPC errors and fall back to empty results, so the migration can ship before or after the code without breaking the page.

The interesting cost was realizing it was time. The leaderboard worked at one caller. Worked at two. By the third, the duplication was screaming. The trick is noticing that pattern early enough to refactor before three turns into five.

Two callers is a coincidence. Three is a contract you haven't written yet.

The general pattern

When the same shape of aggregation shows up in three independent places, it's time to push it down. Signals to watch for:

• You catch yourself scrolling between two files to copy a query shape.
• The aggregation has at least one parameter the callers vary on (here: window).
• The data is read-only enough that a stable function handles it.
• The work is cheap with the right index but expensive without one (here: saved_beats(created_at)).
• The callers diverge on edge cases — different empty handling, different time windows, different "Unknown" producer treatment. Each divergence is a bug waiting to happen.

If you have all five, push the work down. Two CTEs and a row_number() almost always replace fifty lines of JavaScript. The database is faster, the contract is clearer, and the next time you need this shape it's already there.