Admin tab counts read every row of the posts table on every page load

[royalpinto007]

Problem

GET /api/admin/posts in app/api/admin/posts/route.ts computes the pending/approved/rejected tab counts like this:

const { data: counts } = await supabase.from("posts").select("status");

const tabCounts = { pending: 0, approved: 0, rejected: 0 };
for (const row of counts ?? []) { ... }

That selects one row per post in the entire table, ships them all to the worker, and counts them in JavaScript, on every single admin page load and every tab switch.

Why it matters

It is O(all posts) work for three integers, and it gets worse forever as submissions accumulate. On Cloudflare Workers this is also wasted CPU time and memory on a hot path. There is a second problem hiding in it: Supabase applies a default row limit to select(), so once the table exceeds that limit the counts silently become wrong rather than slow, and nothing in the code would tell you.

Suggested approach

Replace the scan with three head count queries, or a single grouped count:

const [pending, approved, rejected] = await Promise.all(
  (["pending", "approved", "rejected"] as const).map((s) =>
    supabase.from("posts").select("*", { count: "exact", head: true }).eq("status", s),
  ),
);

or add a small Postgres function or view under supabase/migrations/ returning the grouped counts in one round trip, which is the cheaper option and matches the existing consume_rate_limit RPC pattern.

Also note the data: counts destructure discards the error. Handle it.

Done when

• No unbounded select() over posts remains in the admin route.
• Counts are correct regardless of table size.
• A test asserts the counts returned for a seeded mix of statuses.

---

If you want to take this on, comment on the issue to claim it and it will be assigned. Please keep to a maximum of 2 open claims per person at a time so other contributors get a chance.