HTPBE — Internal Admin Dashboard

The Challenge

Once HTPBE went live with paying customers, operating the platform blind became a real problem. I needed to answer questions like: how many users converted to a paid plan this month? Which accounts are close to their quota limit? Did a specific user actually use the API or just signed up and disappeared?

The existing Stripe dashboard and the raw Turso database covered individual pieces, but stitching them together on every question was slow and error-prone. I needed a single internal view that combined usage data (from the checks table), subscription state (from Drizzle-mapped Stripe fields), and API key activity — without building a separate backend or adding a data-fetching library.

Key requirements:

1. Hard role guard — accessible only to iurii@rogulia.fi, no other session allowed in
2. Aggregate KPIs on one screen (total users, conversion rate, live checks this month and all-time)
3. Per-user drill-down: quota progress, daily activity, API key list, recent check history
4. Quota calculated live from source data — no counters in the user row that could drift
5. Stripe customer and subscription links directly on the page — no copy-pasting IDs into Stripe's search

The Solution

I built two nested routes under the existing (dashboard) layout group: app/(dashboard)/dashboard/admin/page.tsx for the overview and app/(dashboard)/dashboard/admin/[userId]/page.tsx for per-user detail. Both are React Server Components — they query the database directly, return rendered HTML, and ship no JavaScript for data to the browser.

Role Guard

The guard runs at the top of each Server Component before any data is fetched:

// app/(dashboard)/dashboard/admin/page.tsx
const session = await auth();
if (session?.user?.email !== "iurii@rogulia.fi") {
  redirect("/dashboard");
}

Next.js redirect() in a Server Component throws immediately, so no data queries execute for unauthorized sessions. There is no client-side conditional rendering to bypass.

Aggregating KPIs in a Single Pass

The overview page runs four parallel queries on mount: all users, monthly checks grouped by user and environment, all-time checks grouped the same way, and current subscriptions. Grouping by environment in SQL (rather than filtering in JavaScript) means the database does the work once:

// app/(dashboard)/dashboard/admin/page.tsx
const monthCheckRows = await db
  .select({
    userId: apiKeys.userId,
    environment: apiKeys.environment,
    total: sql<number>`cast(count(*) as integer)`,
  })
  .from(checks)
  .innerJoin(apiKeys, sql`${checks.apiKeyId} = ${apiKeys.id}`)
  .where(gte(checks.checkDate, monthStart))
  .groupBy(apiKeys.userId, apiKeys.environment);

The result rows are indexed into a Map<userId, { live: number; test: number }> in JavaScript before rendering — one pass, no repeated array searches per table row.

The plan breakdown bar (free / starter / growth / pro / enterprise) is derived from the same allUsers array: group by plan field, count each bucket, compute percentage. No extra query.

Per-User Quota Progress

Quota uses the same dynamic calculation as the public API — live checks this month counted from the checks table, never from a stored counter. This keeps the dashboard consistent with what the user sees in their own dashboard and avoids the class of bugs where a counter drifts out of sync after a failed transaction:

// app/(dashboard)/dashboard/admin/[userId]/page.tsx
const limit = user.requestsPerMonth; // null = unlimited (Enterprise)
const usagePct = limit ? Math.min(Math.round((monthLive / limit) * 100), 100) : 0;
 
// Progress bar colour: red ≥90%, yellow ≥70%, primary otherwise
const barColor = usagePct >= 90 ? "bg-red-500" : usagePct >= 70 ? "bg-yellow-500" : "bg-primary";

Overage (checks beyond the monthly quota) is surfaced separately so I can see accounts that went over before a plan upgrade was processed.

30-Day Activity Chart in Pure JSX

I considered Recharts and Chart.js but rejected both — a single bar chart does not justify a 200+ kB dependency in an internal tool. The chart renders in pure JSX: a flex row of proportionally scaled div elements, each with a CSS height set from the day's count relative to the month's maximum. Days with zero checks still render (height 0), which keeps the x-axis gaps honest:

// app/(dashboard)/dashboard/admin/[userId]/page.tsx
// Fill all 30 days from epoch-day arithmetic so zero-check days are explicit
const todayDay = Math.floor(Date.now() / 1000 / 86400);
const dailyMap = new Map(dailyRows.map((r) => [r.day, r.count]));
const chartDays: { label: string; count: number }[] = [];
 
for (let i = 29; i >= 0; i--) {
  const day = todayDay - i;
  const d = new Date(day * 86400 * 1000);
  chartDays.push({
    label: d.toLocaleDateString("en-GB", { day: "2-digit", month: "short", timeZone: "UTC" }),
    count: dailyMap.get(day) ?? 0,
  });
}

// Rendered chart — no library, pure JSX
<div className="flex h-32 items-end gap-0.5">
  {chartDays.map((day, i) => {
    const pct = maxCount > 0 ? (day.count / maxCount) * 100 : 0;
    return (
      <div
        key={i}
        className="group relative flex flex-1 flex-col items-center justify-end"
        title={`${day.label}: ${day.count}`}
      >
        <div
          className="w-full rounded-t bg-primary/70 transition-all group-hover:bg-primary"
          style={{ height: `${Math.max(pct, day.count > 0 ? 3 : 0)}%` }}
        />
        {day.count > 0 && (
          <div className="pointer-events-none absolute bottom-full hidden whitespace-nowrap rounded bg-black/80 px-2 py-1 text-xs text-white group-hover:block">
            {day.label}: {day.count}
          </div>
        )}
      </div>
    );
  })}
</div>

The Math.max(pct, day.count > 0 ? 3 : 0) guard ensures a bar with one check is still visible even when the month's maximum is in the hundreds.

Users Table and Detail Drill-Down

The main table shows all users sorted newest first: name, email, a plan badge, a subscription status badge, live checks this month with a quota progress bar, test checks this month, all-time live and test counts, and registration date. Each row has a "Details →" link to the per-user page.

The per-user detail page shows:

• A subscription card with plan, status, quota per month, current period dates, overage count, and direct links to the Stripe customer object and subscription object (opens in Stripe Dashboard)
• The API keys list: environment badge (live = green, test = yellow), the last four characters of the key, creation date, last-used date, and active/inactive status
• The 30-day bar chart described above
• Recent 100 checks via the shared ChecksTable component already used elsewhere in the dashboard: filename, check date, result badge, origin type, file size, client ID, key name with environment badge, and PDF creator/producer metadata

The Stripe links use the customer ID and subscription ID stored in the users table and require no Stripe API call at render time.

Results