Building a Customer Usage Dashboard

The decision this page resolves is narrow and expensive to get wrong: does your customer-facing usage view query raw events at read time, or does it read pre-aggregated rollups behind a short cache? Part of the Tracking API Usage and Analytics guide, this article picks the second option and shows the endpoint, the quota panel, the cache layer, and the exposure rules that keep a dashboard both fast and safe.

The commercial argument comes first. A dashboard is the cheapest support-ticket deflection you will ever ship. Every "why was my invoice $340 this month?" email costs you fifteen minutes of founder time and a dent in trust. A self-serve view that answers the question at 2am costs one endpoint, one cache key, and about eighty lines of Python. The trap is that the naive version — a SELECT over your raw event table every time someone opens the page — turns your smallest revenue feature into the heaviest query on your database, on exactly the day a big customer audits their spend.

Raw scan versus rollup: the read path that decides everything

Assume the event pipeline from the parent guide: one immutable row per request in usage_event, and an usage_hourly table rebuilt every ten minutes. The dashboard has three candidate sources, and they differ by two orders of magnitude in cost.

Those numbers come from a customer doing 3.6 million calls a month on a 2 vCPU managed Postgres instance. The raw scan touches roughly 3.6 million rows across two partitions; the hourly rollup touches about 4,300; the cached response touches nothing. Read that as a cost table, not a latency table: the raw scan burns I/O your paying request path needs, so one enterprise customer's refresh loop measurably slows the API they pay for.

Pick the rollup plus cache path and never look back. The only cost is freshness, and freshness is a product decision you can state in one line of UI copy. Customers accept "usage updates every few minutes" without blinking; they do not accept a spinner.

The read-only usage endpoint

Build the API before the UI. Your dashboard should be a client of the same public endpoint your customers can call themselves, because a documented GET /v1/usage is a feature that shows up in your OpenAPI documentation and lets a customer wire your numbers into their own finance tooling. Two implementations for the price of one.

Three rules govern this endpoint. It is read-only, so it never accepts a body and never mutates a counter. It is scoped by the authenticated identity, never by a customer_id query parameter — the API key authentication layer resolves the caller, and a caller can only ever see themselves. And it caps the range, because an unbounded days parameter is a denial-of-service vector disguised as a feature.

export USAGE_DATABASE_URL="postgresql+asyncpg://api:secret@db.internal:5432/apiprod"
export USAGE_REDIS_URL="redis://cache.internal:6379/2"
export USAGE_DASHBOARD_CACHE_TTL="60"
export USAGE_DASHBOARD_MAX_DAYS="62"
export USAGE_QUOTA_ALERT_RATIO="0.8"

# usage/dashboard.py
import os
from datetime import datetime, timedelta, timezone

from fastapi import APIRouter, Depends, HTTPException, Query, Response
from pydantic import BaseModel
from sqlalchemy import text

from usage.auth import current_customer  # resolves the presented key to a customer id
from usage.flusher import Session

router = APIRouter(prefix="/v1/usage", tags=["usage"])

MAX_RANGE_DAYS = int(os.getenv("USAGE_DASHBOARD_MAX_DAYS", "62"))
CACHE_TTL = int(os.getenv("USAGE_DASHBOARD_CACHE_TTL", "60"))

SERIES_SQL = """
SELECT {bucket_expr} AS bucket,
       sum(calls)::bigint    AS calls,
       sum(billable)::bigint AS billable,
       sum(errors)::bigint   AS errors,
       round(sum(duration_sum)::numeric / greatest(sum(calls), 1), 1) AS avg_ms
FROM usage_hourly
WHERE customer_id = :customer_id
  AND bucket >= :start AND bucket < :end
GROUP BY 1
ORDER BY 1
"""


class UsagePoint(BaseModel):
    bucket: datetime
    calls: int
    billable: int
    errors: int
    avg_ms: float


class UsageSeries(BaseModel):
    granularity: str
    as_of: datetime
    points: list[UsagePoint]


@router.get("", response_model=UsageSeries)
async def usage_series(
    response: Response,
    days: int = Query(30, ge=1),
    granularity: str = Query("day"),
    customer_id: str = Depends(current_customer),
) -> UsageSeries:
    if days > MAX_RANGE_DAYS:
        raise HTTPException(status_code=422, detail=f"days must be <= {MAX_RANGE_DAYS}")
    match granularity:
        case "hour":
            bucket_expr = "bucket"
        case "day":
            bucket_expr = "date_trunc('day', bucket)"
        case _:
            raise HTTPException(status_code=422, detail="granularity is 'hour' or 'day'")

    end = datetime.now(timezone.utc).replace(minute=0, second=0, microsecond=0)
    end += timedelta(hours=1)
    start = end - timedelta(days=days)
    async with Session() as session:
        rows = await session.execute(
            text(SERIES_SQL.format(bucket_expr=bucket_expr)),
            {"customer_id": customer_id, "start": start, "end": end},
        )
        points = [UsagePoint(**row._mapping) for row in rows]

    response.headers["Cache-Control"] = f"private, max-age={CACHE_TTL}"
    return UsageSeries(granularity=granularity, as_of=end, points=points)

The .format() call on SQL text looks alarming until you notice bucket_expr can only ever be one of two literals chosen by the match statement. Every value that comes from the caller travels as a bound parameter. Keep that discipline: the moment someone adds a route LIKE :pattern filter, it goes in the parameter dictionary, not the format string.

Note what the endpoint does not do: it never joins to usage_event, never computes money, and never accepts a customer identifier. Those three omissions are the whole security model. Confirm the reads also share the pool discipline of the rest of your async SQLAlchemy access — a dashboard that opens its own engine is a classic route to pool exhaustion during a spike.

Quota progress customers actually trust

The series chart is nice. The quota bar is what people open the page for. It answers one question — how close am I to the wall — and it has to be right, because a customer who is told they used 62% and then gets rate-limited will not trust anything else on the page.

There are two numbers in play and they disagree. Redis holds the live counter your enforcement path increments on every request. Postgres holds the billed truth derived from the durable event log. The Redis number leads by up to one flush interval; the Postgres number lags but is authoritative. Show the live number, label it as live, and reconcile against Postgres whenever the Redis key is missing or the period rolls over.

# usage/quota.py
import os
from datetime import datetime, timezone

import redis.asyncio as redis
from sqlalchemy import text

from usage.flusher import Session

client = redis.from_url(os.environ["USAGE_REDIS_URL"], decode_responses=True)
ALERT_RATIO = float(os.getenv("USAGE_QUOTA_ALERT_RATIO", "0.8"))
PLAN_LIMITS = {"free": 1_000, "starter": 50_000, "growth": 500_000}

BILLED_SQL = """
SELECT coalesce(sum(billable), 0)::bigint
FROM usage_hourly
WHERE customer_id = :customer_id AND bucket >= :period_start
"""


def _period_start(now: datetime) -> datetime:
    return now.replace(day=1, hour=0, minute=0, second=0, microsecond=0)


def _period_end(now: datetime) -> datetime:
    year, month = (now.year + 1, 1) if now.month == 12 else (now.year, now.month + 1)
    return now.replace(year=year, month=month, day=1, hour=0,
                       minute=0, second=0, microsecond=0)


async def quota_snapshot(customer_id: str, plan: str) -> dict[str, object]:
    now = datetime.now(timezone.utc)
    limit = PLAN_LIMITS.get(plan, PLAN_LIMITS["free"])
    key = f"quota:{customer_id}:{now:%Y%m}"

    live = await client.get(key)
    if live is None:
        async with Session() as session:
            result = await session.execute(
                text(BILLED_SQL),
                {"customer_id": customer_id, "period_start": _period_start(now)},
            )
            used = int(result.scalar_one())
        await client.set(key, used, ex=40 * 86_400)
    else:
        used = int(live)

    ratio = min(used / limit, 1.0) if limit else 0.0
    return {
        "used": used,
        "limit": limit,
        "percent": round(ratio * 100, 1),
        "resets_at": _period_end(now).isoformat(),
        "alert": ratio >= ALERT_RATIO,
        "source": "live" if live is not None else "reconciled",
    }

The rebuild-on-miss branch is the important part. A Redis restart wipes the counter, and without that fallback every customer sees zero usage afterwards — which reads as a billing bug even though nothing was lost. Rebuilding from usage_hourly costs one indexed query and self-heals the key for the rest of the month. Expose alert so the UI turns the bar amber at 80% and offers an upgrade link; that pairs directly with the tier ladder from Stripe metered billing configuration.

Caching without lying about freshness

Sixty seconds of cache absorbs the refresh-key-mashing that dashboards attract, and it does so without any invalidation logic at all — the data is append-only, so a short time-to-live is strictly correct. That is the rare case where TTL beats event-driven invalidation; the trade-offs are laid out properly in cache invalidation strategies, but for an append-only series you should not build anything cleverer.

Key the cache on the customer, the granularity, and the range, then hash it so a long key never bloats Redis. Always ship an as_of timestamp inside the payload and render it in the UI as "updated 40 seconds ago". A stale number with an honest timestamp is trustworthy; a stale number presented as live is a support ticket.

# usage/cache.py
import hashlib
import json
import os
from collections.abc import Awaitable, Callable
from typing import Any

import redis.asyncio as redis

client = redis.from_url(os.environ["USAGE_REDIS_URL"], decode_responses=True)
TTL = int(os.getenv("USAGE_DASHBOARD_CACHE_TTL", "60"))


async def cached_json(
    parts: tuple[str, ...],
    build: Callable[[], Awaitable[dict[str, Any]]],
) -> tuple[dict[str, Any], str, str]:
    digest = hashlib.sha256("|".join(parts).encode()).hexdigest()
    key = f"dash:{digest[:24]}"
    hit = await client.get(key)
    if hit is not None:
        return json.loads(hit), "HIT", f'W/"{digest[:16]}"'
    payload = await build()
    body = json.dumps(payload, default=str, sort_keys=True)
    await client.set(key, body, ex=TTL)
    etag = hashlib.sha256(body.encode()).hexdigest()[:16]
    return payload, "MISS", f'W/"{etag}"'

Return the weak ETag and answer 304 Not Modified when the client sends a matching If-None-Match. A dashboard polling every thirty seconds then transfers a few hundred bytes of headers instead of a 40 KB JSON body — real savings once you pay egress on a metered host. Add Cache-Control: private so no shared proxy ever stores one customer's numbers.

What to expose and what to hide

This is where builders leak. Usage data sits next to cost data in the same tables, and it is trivially easy to hand a customer your gross margin by shipping one extra column. Draw the line explicitly.

Two subtleties. Hide your upstream vendors: if an endpoint costs you $0.004 because you resell a model provider, publishing per-call cost tells a customer your markup and who to call instead — keep it in the internal margin report from calculating cost per API request. And always split error counts by class: showing that 5,900 calls were 4xx and only 12 were 5xx converts "your API is broken" into "my integration has a bug" before anyone opens a ticket.

When to build this, and when to skip it

Build the dashboard when any of these is true: you charge by usage rather than by seat, you have more than roughly twenty paying accounts, or you have answered the same "how much have I used" email twice in a month. Usage-priced products need it earliest, because the customer's spend is unpredictable and a hidden meter feels like a taxi with the display taped over.

Skip it, for now, if you are pre-revenue or under about ten customers. At that scale a saved SQL query and a two-line reply is faster and gives you conversations you actually want to be having. Skip it too if your traffic is so low that raw scans finish in 20 ms — you do not need rollups until a customer's month exceeds roughly 200,000 events, and premature aggregation just gives you a rollup job to babysit.

The threshold that forces the rollup path is easy to watch: track the dashboard endpoint's p95 in your monitoring and logging setup and move within the week once it crosses 300 ms. The curve is superlinear, so you have less runway than the graph suggests.

Migrating from raw queries to rollups

If you already shipped a dashboard that scans raw events, the switch takes an afternoon and needs no downtime.

1. Create usage_hourly and backfill it once with the same aggregation the scheduled job runs, chunked by day so a single statement never locks for minutes: INSERT INTO usage_hourly SELECT ... WHERE occurred_at >= :day AND occurred_at < :day + interval '1 day' looped over your retention window.
2. Start the rollup job on its ten-minute schedule and let it run for an hour alongside the existing dashboard.
3. Verify equivalence: for five real customers, compare the raw-scan total and the rollup total for the last complete day. They must match exactly. If they differ, your rollup window is dropping late events, not your maths.
4. Flip the endpoint to read usage_hourly behind a feature flag from os.getenv, and keep the raw path available for one week.
5. Add the cache last, once the numbers are proven. Caching a wrong answer just makes it wrong for longer.
6. Shorten raw retention only after two clean billing periods, and never before your usage event log in Postgres has survived a real dispute.

Builder verdict

Pre-aggregated rollups behind a 60-second cache win, and it is not close. The raw-scan dashboard is seductive because it is fifteen lines of code and always fresh, but it converts your cheapest support feature into your most expensive query. The rollup path costs one extra table, one cron entry, and a cache helper — three hours of work — and it delivers a 3 ms response that stays 3 ms whether the customer did 10,000 calls or 10 million. Ship the JSON endpoint first, render a server-side page over it with no client framework at all, and put the quota bar with its 80% alert above the fold. That single bar deflects tickets and sells upgrades, which beats the return on most weeks of feature work. Then publish the same endpoint in your developer portal and let customers build their own views on it.

FAQ

How much does a customer usage dashboard cost to run at 1M requests a month? Effectively nothing beyond what the tracking pipeline already costs. The rollup job for 1M events runs in under two seconds every ten minutes, the cached endpoint serves from Redis in about 3 ms, and the cache holds a few kilobytes per active customer. Budget under a dollar a month of compute. The raw-scan alternative is what costs money: sustained sequential scans on a managed Postgres instance push you into the next instance tier far sooner.

Should customers see the same usage number I bill them from? Bill from the Postgres event log, display the live Redis counter, and label the display as live with an as_of timestamp. The two will differ by a fraction of a percent inside a flush interval. What you must never do is invoice from the cached dashboard number — a cache that survives a bad deploy would then bake an error into a real invoice.

Does exposing usage data increase my churn risk? It reduces it. Customers who can see consumption trends catch their own runaway loops, and the 80% quota alert converts to upgrades rather than surprise overage disputes. The only genuine risk is exposing cost or margin fields, which tells a customer exactly how much room they have to negotiate or to rebuild the thing themselves.

How do I keep one customer from seeing another's numbers? Scope every query by the identity resolved from the presented credential and never accept a customer identifier as a parameter. Include the customer id in the cache key so a cache collision cannot cross the boundary, set Cache-Control: private, and write one integration test that calls the endpoint with customer A's key while requesting a range in which customer B has traffic, asserting the totals match A's alone.

What is the migration risk if I already have a raw-query dashboard? Low, because both paths can run simultaneously. Backfill the rollup, run the job for an hour, and reconcile five customers' daily totals against the raw scan before flipping a os.getenv flag. Keep the raw path warm for a week. The only irreversible step is shortening raw retention, so leave that until two billing periods have closed cleanly.

Related

• Tracking API Usage and Analytics — the event pipeline, Redis counters, and rollup job this dashboard reads from.
• Logging API Usage Events to Postgres — partitioning and index tuning that keeps the rollup job under two seconds.
• Caching Python API Responses with Redis — the caching layer behind the 60-second dashboard cache, with pooling and serialization details.
• Calculating Cost per API Request — the internal margin numbers that must stay out of the customer payload.
• Fixing Connection Pool Exhaustion — what happens when dashboard reads open their own engine during a traffic spike.