An Analytics Dashboard with Plain Postgres (No BI Tool)

An analytics dashboard with Postgres and no BI tool is a handful of SQL functions — one for headline KPIs, one per breakdown, one for each time series — called from your server and drawn with a chart library. Postgres already has everything a product dashboard needs: count(*) filter, percentile_cont, date_trunc, window functions and generate_series. Keep the functions security invoker so row-level security scopes each user's numbers, index the columns you group and filter by, and you get reports that are fast, correct and free.

Why skip the BI tool

A BI tool is excellent for analysts exploring data. For numbers inside your product — a manager's "how is my team doing?" page — it's often the wrong shape:

• Permissions duplicate. The BI tool needs its own copy of who can see what; your database already knows.
• Data goes stale. Most BI setups copy data on a schedule.
• Cost and another login. Embedded BI is frequently a paid feature.
• Your queries are simple. Counts, rates and percentiles over one or two tables.

Postgres answers these in milliseconds with the right indexes, and your app renders them in its own design.

Pattern: one SQL function per report

LetRelay's analytics are SQL functions called through Supabase's RPC endpoint. The headline KPIs:

create or replace function public.analytics_kpis()
returns table (
  open_count bigint, breached_count bigint, resolved_count bigint, total_count bigint,
  resolution_rate double precision,
  avg_first_response_seconds double precision, avg_resolution_seconds double precision,
  resolved_today bigint, unassigned_count bigint
)
language sql stable
as $$
  select
    count(*) filter (where status not in ('resolved', 'closed')),
    count(*) filter (where status not in ('resolved', 'closed') and sla_due_at < now()),
    count(*) filter (where status in ('resolved', 'closed')),
    count(*),
    count(*) filter (where status in ('resolved', 'closed'))::double precision / nullif(count(*), 0),
    avg(extract(epoch from (first_response_at - created_at))) filter (where first_response_at is not null),
    avg(extract(epoch from (resolved_at - created_at)))      filter (where resolved_at is not null),
    count(*) filter (where resolved_at >= date_trunc('day', now())),
    count(*) filter (where assignee_id is null and status not in ('resolved', 'closed'))
  from public.requests;
$$;

One pass over the table, nine numbers. count(*) filter (where …) is the workhorse: many conditional counts in a single scan, instead of nine queries.

Security: invoker, not definer

That function has no security definer, so it runs as the calling user — and row-level security on requests applies inside it. An agent gets their team's numbers; an admin gets the organization's; nobody gets another organization's. One function serves every role correctly. A security definer version would bypass RLS and have to re-implement scoping, which is exactly where cross-tenant leaks come from. The model is explained in row-level security vs app-layer authorization.

Averages vs percentiles

The function above reports averages, which are cheap and fine for a headline. But averages hide the long tail: a few requests that waited a week drag the average up, and a healthy-looking average can hide those people entirely. Google's SRE book makes the same argument for latency. For response and resolution time, add percentiles:

select
  percentile_cont(0.5) within group (order by resolved_at - created_at) as p50,
  percentile_cont(0.9) within group (order by resolved_at - created_at) as p90
from requests
where resolved_at >= now() - interval '30 days';

percentile_cont interpolates; percentile_disc returns an actual observed value. Either works for dashboards. Which metrics to show at all is covered in help desk metrics that matter.

Breakdowns

Per team, per category, per assignee — a group by with a join for the name:

select t.name,
       count(*) filter (where r.status not in ('resolved', 'closed'))            as open,
       count(*) filter (where r.resolved_at > r.sla_due_at)                       as breached,
       round(100.0 * count(*) filter (where r.resolved_at <= r.sla_due_at)
             / nullif(count(*) filter (where r.resolved_at is not null), 0), 1)  as attainment_pct
from requests r join teams t on t.id = r.team_id
where r.created_at >= now() - interval '30 days'
group by t.name
order by breached desc;

nullif(x, 0) avoids division-by-zero errors for teams with no resolved requests yet.

Time series without gaps

A chart of requests per day needs a row for every day, including days with zero requests. Grouping alone skips empty days, so the chart silently compresses time. Generate the days and left-join:

select d::date as day, count(r.id) as created
from generate_series(date_trunc('day', now()) - interval '29 days', date_trunc('day', now()), interval '1 day') d
left join requests r on r.created_at >= d and r.created_at < d + interval '1 day'
group by d
order by d;

Whose "day"?

date_trunc('day', now()) uses the database session's timezone — usually UTC. For a team in Kathmandu (UTC+5:45) or California, "today" in UTC is wrong for part of every day. Pass the viewer's IANA timezone and truncate in it: date_trunc('day', created_at at time zone p_tz). Validate the timezone name and fall back to UTC if it's unknown. LetRelay learned this the hard way: its attendance "today" list used the server's midnight until we passed the viewer's zone into the query.

Trends with window functions

Week-over-week change and running totals are window functions:

select week, created,
       created - lag(created) over (order by week) as change_vs_last_week,
       sum(created) over (order by week)            as running_total
from (
  select date_trunc('week', created_at) as week, count(*) as created
  from requests group by 1
) w
order by week;

Keeping it fast

• Index what you filter and group by: created_at, resolved_at, status, team_id, category_id, and the tenant column. Composite indexes that lead with the tenant help every query.
• Bound every query by time. "Last 30 days" by default; "all time" only on request.
• Use explain analyze on each report once the table has realistic volume.
• Precompute only when needed. A materialized view refreshed by pg_cron every few minutes is the next step when live queries get slow — but for tens or hundreds of thousands of rows, live queries are usually fine.

Freshness: live, cached or scheduled

Decide per report how fresh it must be:

Snapshots have a second benefit: history stops changing. If a category is renamed or a request is re-categorized next quarter, last quarter's report still says what it said at the time — which matters when the numbers were used to justify a hire.

Exports

Managers will eventually ask for "the data in a spreadsheet". The same SQL functions can back a CSV export from a Route Handler: call the function with the user's session (so RLS still applies), stream rows as CSV, and set a Content-Disposition header. Cap the date range and row count so an export can't become a denial of service, and escape values that start with =, +, - or @, which spreadsheet programs may interpret as formulas.

Rendering

The server calls the functions (in parallel) and passes plain arrays to chart components. LetRelay uses Recharts; any library works. Two UI rules:

• Always handle empty and error states. A new organization has no data; show "No requests yet — numbers appear after your first week", not an empty axis.
• Label units and windows. "Median resolution, last 30 days: 5h 12m" beats "5.2".

FAQ

Can Postgres replace a BI tool?

For in-product dashboards with known questions, often yes. For open-ended exploration by analysts across many data sources, a BI tool is still the better fit.

How do I respect permissions in dashboard queries?

Write reports as security invoker functions (the default) so row-level security applies inside them, and every user sees numbers only for rows they may read.

How do I show days with zero activity in a chart?

Generate the full date range with generate_series and left-join your data to it, so every day appears even when its count is zero.

Should dashboard metrics use averages or percentiles?

Show percentiles (median and 90th) for durations; averages hide long waits.

Sources

• PostgreSQL — Aggregate functions (percentile_cont)
• PostgreSQL — Window functions tutorial
• PostgreSQL — Date/Time functions
• Supabase — Database functions
• Google SRE Book — Service Level Objectives
• Recharts