Seventeen subqueries in one statement, because our connection pool has five slots

Nakodo has an operator panel at /admin: accounts, plans, campaigns, the inbound mail that failed to forward, the job queue, and a run log of the background pipeline. Its front page is a wall of numbers, and the first version of it was the obvious thing.

Fifteen counts, each its own query, all fired at once:

const [accounts, newAccounts, onboarded, free, pro, business /* ... */] = await Promise.all([
  db.select({ n: count() }).from(authUsers),
  // ... fourteen more
]);

That is idiomatic, it reads well, and it is wrong for this app for one reason that has nothing to do with SQL.

The pool has five connections

// Reuse one client across hot reloads in dev. `prepare: false` is required by
// Supabase's transaction pooler (port 6543), which is what serverless should use.
const client = globalForDb.pg ?? postgres(env("DATABASE_URL")!, { prepare: false, max: 5 });

Five. That is not a number to tune upward: this runs on serverless functions against Supabase's transaction pooler, and every concurrent instance of the app has its own pool. A high max per instance multiplied by however many instances a traffic spike creates is how you exhaust a Postgres connection limit with a modest amount of traffic.

Fifteen parallel queries into five slots means ten of them wait, three deep. That alone is only slow. The part that is worse is what happens when the render is abandoned, which in a server component world is routine: the user navigates away, a Suspense boundary is discarded, a request times out. The promises already dispatched carry on holding connections for a page that nobody is going to see, and the page that replaced it is now queueing behind work for a dead render.

So the whole overview became one statement:

// One statement, because fifteen parallel counts on a five-connection pool
// queue three deep and strand connections when a render is abandoned. The
// windows are computed in Postgres: a Date bound into a raw sql template
// isn't tied to a column, so drizzle never serialises it and postgres.js
// throws on the bare object.
export async function overview(): Promise<Overview> {
  const [row] = await db.execute<Record<string, string | null>>(sql`
    select
      (select count(*) from auth.users) as accounts,
      (select count(*) from auth.users where created_at >= now() - interval '7 days') as new_accounts7,
      (select count(*) from ${profiles} where onboarded_at is not null) as onboarded,
      (select count(*) from ${subscriptions} where plan = 'free') as plan_free,
      (select count(*) from ${subscriptions} where plan = 'pro') as plan_pro,
      (select count(*) from ${subscriptions} where plan = 'business') as plan_business,
      (select count(*) from ${subscriptions} where plan <> 'free' and status in ('active', 'trialing', 'past_due')) as paying,
      (select count(*) from ${campaigns}) as campaigns,
      (select count(*) from ${campaigns} where status = 'active') as active_campaigns,
      (select count(*) from ${emailPreferences} where marketing) as marketing,
      (select count(*) from ${outreachThreads} where first_sent_at >= now() - interval '30 days') as contacted30,
      (select count(*) from ${outreachThreads} where handed_off_at >= now() - interval '30 days') as introduced30,
      (select count(*) from ${outreachThreads} where closed_reason in ('bounced', 'complained') and closed_at >= now() - interval '30 days') as bounced30,
      (select count(*) from ${inboundEmails} where received_at >= now() - interval '7 days') as inbound7,
      (select count(*) from ${inboundEmails} where forwarded_to is null) as inbound_failed,
      (select count(*) from ${jobs} where status = 'failed') as failed_jobs,
      (select max(started_at) from ${runs}) as last_run_at
  `);
  const n = (key: string) => Number(row?.[key] ?? 0);

Seventeen uncorrelated scalar subqueries, one connection, one round trip. Postgres plans each one separately, which is fine: they are all index or sequential counts over small tables, and the planner has no cross-subquery work to get wrong. On a service this size the whole thing comes back in the time one of the fifteen used to take.

Three things in there are worth more than the pattern itself.

Interpolating tables, not values. ${profiles} in a drizzle sql template serialises to the table name, which means a column rename in the schema still breaks the build rather than the page. The strings auth.users, onboarded_at and created_at are the exception: that table lives in Supabase's auth schema, and the column names inside my own interpolated tables are plain text here, which is the honest cost of dropping to raw SQL.

The time windows are SQL, not JavaScript. now() - interval '7 days' rather than a bound Date. The comment says why: inside a raw template a Date is not attached to a column, drizzle does not know to serialise it, and postgres.js refuses the bare object. There is a second, better reason in a different query in the same file:

// Compared in SQL: a JS Date drops the microseconds Postgres keeps.

timestamptz holds microseconds and a JavaScript Date holds milliseconds, so a value read out and sent back in is not the same value. Any boundary comparison built from a round tripped timestamp is wrong on its edge. Doing the arithmetic in the database makes both problems disappear, and "the last 7 days" is also then measured against the database clock rather than whichever region the function woke up in.

Everything comes back as a string. db.execute with a raw statement gives you Record<string, string | null>, because count(*) is bigint and the driver will not silently narrow it. Hence the tiny n() helper, and hence last_run_at being parsed with new Date(...) explicitly. A ?? 0 on every count means a shape change produces a zero on a dashboard rather than a NaN in a chart.

Why this file is allowed to do that

Raw SQL over seventeen tables, returning other people's email addresses, is not something I want anywhere near a customer facing route. So it lives behind a comment that states the boundary:

// Read models for the admin panel. Every caller is behind requireAdmin, so
// these return whole-service data: other people's email addresses, plans and
// campaigns. Nothing here is reachable from the rest of the app.

and behind a gate whose only interesting property is its failure mode:

// The gate every admin page calls as its first statement, before it awaits any
// data. A signed-out visitor is sent to sign in and brought back; a signed-in
// account that isn't on the list gets a 404, not a 403, so the panel never
// confirms itself to anyone who merely has an account.
export const requireAdmin = cache(async (returnTo = "/admin"): Promise<SessionUser> => {
  const user = await getUser();
  if (!user) redirect(`/login?next=${encodeURIComponent(returnTo)}`);
  if (!isAdminEmail(user.email)) notFound();
  return user;
});

A 403 tells a signed-in stranger that there is something there. A 404 tells them nothing, and costs nothing, because the only person who would ever see it is me with the wrong account. cache() from React means the three or four components on one page that each want the operator all get one lookup.

You can watch the signed-out half from a terminal:

$ curl -sI https://nakodo.app/admin/runs | grep -iE '^(HTTP|location)'
HTTP/2 307
location: /login?next=%2Fadmin%2Fruns

Who counts as an operator is a hardcoded list in compiled code rather than a column or an environment variable, which I have written about before after the environment variable version of exactly that broke in another app: an unset variable produces an empty allowlist, which 404s at its own operator with nothing in the logs to explain why.

The search box, since it is next to the numbers

The account list has one input, and it has to accept a UUID from a log line, a half remembered surname, and a company's email domain:

const q = query.trim();
const term = `%${q.replace(/[%_]/g, (c) => `\\${c}`)}%`;
const where = !q
  ? undefined
  : UUID.test(q)
    ? eq(authUsers.id, q)
    : or(ilike(authUsers.email, term), ilike(profiles.fullName, term), ilike(profiles.companyName, term));

A full UUID is an id lookup, because a UUID typed into a search box is never a substring of anything. Anything else is a case insensitive substring across three columns, so @acme.com finds a whole company. The % and _ escaping is the bit people forget: without it, a search for 100% or a_b is a wildcard pattern, and the query that ignores your filter and returns every account is a bad surprise on a page that shows email addresses.

The run log

The other page worth mentioning is the run log, and the reason it is in the operator panel rather than in the product is one line:

// The background pipeline across every account, which is why it lives in the
// panel: one list of everyone's work is not something a customer may see.

It is force-dynamic, it fetches four things in parallel (quota, the last 40 runs, queue counts grouped by job type and status, and today's source counters), and it is the only place where a stop reason is spelled out in English:

const STOP_REASONS: Record<string, string> = {
  no_work: "Queue empty",
  time_budget: "Time limit, continues next run",
  quota_reserve: "Daily quota budget reached",
  quota_exhausted: "YouTube quota used up",
  error: "Error",
};

Every run of the pipeline writes one of those five, and "Time limit, continues next run" being an ordinary, expected, non-alarming outcome is the single most useful thing that table taught me. A cron pipeline that always finishes its queue is a pipeline with not enough work in it.

The customer facing half of the same information is public: the pricing page has the daily limits those counters enforce, and how it works explains the search budget, the 200 businesses a day and what happens when a source is paused until tomorrow. The panel just shows me whether any of it is true today.