Live Dashboard

A dashboard that updates in real time as a background job recomputes its stats. The job runs on a cron schedule, writes a single cached row, and notifies a dashboard channel. Every browser on the dashboard page listens on that channel; when the notification arrives, the template re-fetches the cached row from the rpc and reassigns the value, and the rendered numbers update without a page reload.

The pattern layers two ideas: the cron-driven aggregate cache from elements man recipes cron-cache (same single-row stats table, same job shape) plus a Channel push so connected browsers learn about new values as soon as the job commits. The channel carries a "something changed" signal; the actual value comes from an rpc the browser re-runs on each notification.

Migration

elements create migration 'add live stats' -tables=users,orders,stats

app/migrations/<timestamp>-add-live-stats.migration.sql:

-- add live stats

-- Auto-update updatedAt on row changes.
create or replace function touchUpdatedAt()
returns trigger
language plpgsql
as $$
begin
  new.updatedAt = now();
  return new;
end;
$$;

create table users (
  id uuid primary key default uuidGenerateV7(),
  createdAt timestamptz not null default now(),
  updatedAt timestamptz not null default now(),
  handle text not null unique,
  lastSeenAt timestamptz
);

create trigger usersTouchUpdatedAt
  before update on users
  for each row execute function touchUpdatedAt();

create table orders (
  id uuid primary key default uuidGenerateV7(),
  createdAt timestamptz not null default now(),
  updatedAt timestamptz not null default now(),
  userId uuid not null references users(id) on delete cascade,
  amountCents integer not null
);

create index ordersCreatedAtIdx on orders (createdAt desc);

create trigger ordersTouchUpdatedAt
  before update on orders
  for each row execute function touchUpdatedAt();

create table stats (
  id integer primary key default 1 check (id = 1),
  computedAt timestamptz not null default now(),
  totalUsers integer not null default 0,
  activeNow integer not null default 0,
  revenueTodayCents integer not null default 0
);

insert into stats (id) values (1);

The same single-row stats pattern as cron-cache. The columns are tuned for "live" indicators: activeNow instead of weekly active, revenueTodayCents instead of monthly. The cron fires more often (every 30 seconds) and the channel pushes the change immediately.

Services

The module is shared because the channel has two callers: the /dashboard page (subscribes via dashboardChannel.listen() and re-fetches on notify) and the RefreshDashboardJob (calls dashboardChannel.notify(...) after each write). A job is not a page, so the consumer count by the page-vs-shared rule is one page plus one job. The channel is the bridge between them, so its declaration plus the helpers that operate on the same row (Stats, loadStats, fetchStats) cluster together in app/shared/services/. If the page never had a server-driven refresh path (no job, no channel), the entire module would move to app/pages/dashboard/services.ts.

app/shared/services/stats.ts:

import { Channel, sql } from "@elements/app";

export interface Stats {
  computedAt: Date;
  totalUsers: number;
  activeNow: number;
  revenueTodayCents: number;
}

export const dashboardChannel = new Channel<{ event: "refresh" }>("dashboard");

export function loadStats(): Stats {
  return sql<Stats>(
    `select computedAt, totalUsers, activeNow, revenueTodayCents
     from stats where id = 1`,
  ).firstOrThrow();
}

/** @rpc */
export function fetchStats(): Stats {
  return loadStats();
}

loadStats is a regular function used by the route's first render. fetchStats is the rpc the browser calls on each notification. They run the same SQL but with different call paths.

dashboardChannel is the broadcast channel. The job below notifies it after every cache write; the page below listens.

Refresh job

elements create job RefreshDashboard

app/jobs/refresh-dashboard.ts:

import { Job, sql } from "@elements/app";
import { dashboardChannel } from "#app/shared/services/stats";

/** @job */
export class RefreshDashboardJob extends Job {
  run() {
    let row = sql<{
      totalUsers: number;
      activeNow: number;
      revenueTodayCents: number;
    }>(
      `select
         (select count(*)::int from users) as totalUsers,
         (select count(*)::int from users where lastSeenAt > now() - interval '5 minutes') as activeNow,
         (select coalesce(sum(amountCents), 0)::int from orders where createdAt >= date_trunc('day', now())) as revenueTodayCents`,
    ).firstOrThrow();

    sql(
      `update stats
       set computedAt = now(),
           totalUsers = ${row.totalUsers},
           activeNow = ${row.activeNow},
           revenueTodayCents = ${row.revenueTodayCents}
       where id = 1`,
    );

    dashboardChannel.notify({ event: "refresh" });
  }
}

The job runs the aggregates, writes the cached row, then notifies the channel. The notify call is the last step, so every browser that receives it reads a row that has already been updated. The event payload is intentionally minimal: the channel only signals that a refresh happened; the new values come from fetchStats, not the payload.

Page

elements create page dashboard

app/pages/dashboard/index.ts:

import dashboard from "./template";
import { dashboardChannel, loadStats } from "#app/shared/services/stats";

export default function route(req, res) {
  return new dashboard({
    initial: loadStats(),
    listener: dashboardChannel.listen(),
  });
}

The route returns the current cached stats for the initial server render plus a listener for the dashboard channel. First paint shows numbers immediately; the listener handles every subsequent update.

app/pages/dashboard/template.html:

import "./style.css";
import type { Listener } from "@elements/app";
import { Stats, fetchStats } from "#app/shared/services/stats";

function onNotify(stats: { value: Stats }) {
  stats.value = fetchStats();
}

<html class="dashboard"
      (initial: Stats,
       listener: Listener<{ event: "refresh" }>,
       private stats: { value: Stats } = { value: initial })
      oninit={() => listener.on("notify", () => onNotify(stats))}>
  <h1>live stats</h1>

  <ul class="cards">
    <li>
      <span class="label">total users</span>
      <span class="value">{stats.value.totalUsers.toLocaleString()}</span>
    </li>
    <li>
      <span class="label">active now</span>
      <span class="value">{stats.value.activeNow.toLocaleString()}</span>
    </li>
    <li>
      <span class="label">revenue today</span>
      <span class="value">${(stats.value.revenueTodayCents / 100).toLocaleString()}</span>
    </li>
  </ul>

  <p class="freshness">last computed {stats.value.computedAt.toLocaleString()}</p>
</html>

stats is wrapped in { value: Stats } so the onNotify handler can reassign stats.value from a function parameter. The template's oninit wires the listener once; every subsequent notify event fires onNotify, which re-runs the rpc and reassigns the value. Reading stats.value in the bindings tracks reactively, so every dependent cell updates on each reassignment.

Routes

Register the page and the cron entry in index.ts:

import dashboard from "#app/pages/dashboard";
import { RefreshDashboardJob } from "#app/jobs/refresh-dashboard";

// ...
app.route("/dashboard", dashboard);
app.cron("every 30s", "refresh dashboard", () => new RefreshDashboardJob().schedule());

The cron fires every 30 seconds. Postgres arbitrates the leader across the deployment so exactly one machine schedules each tick.

Notes

• Why notify with "refresh" and not the new values. The payload could carry the new Stats directly and skip the rpc round-trip. The "refresh" signal is more flexible: different listeners may want different views of the data (admin vs user, scoped vs global), and each re-fetch can apply its own auth or scope. For a single fixed view, inlining the payload is the simpler choice; do that by typing the channel as Channel<Stats> and passing the new row to notify.
• Why not LiveTable. LiveTable broadcasts row-level changes. The stats table has one row that updates in place; the LiveTable's optimistic mutator and auto-fill add nothing here. The channel matches what's happening: one event per recompute, no row identity to track.
• Cron cadence. 30-second updates are aggressive for a live dashboard. Tune up to every 5s for high-frequency UIs or down to every 5m if the data doesn't actually change that fast. The cost is a Postgres aggregate per tick; profile against your actual data volumes.
• Push from anywhere. Anything on the server can call dashboardChannel.notify(...). An order-fulfillment rpc that wants to bump the revenue counter immediately on commit can notify itself, bypassing the cron cadence. Pages still re-fetch through the same path, so the UX is identical regardless of who triggered the update.
• Per-user dashboards. When each user sees their own numbers, the rpc reads where userId = ${session.getOrThrow('userId')} (or similar) and the channel signal can stay global ("something changed; everyone re-fetch their own slice"). For tighter targeting, filter the listener: dashboardChannel.listen({ filter: (e) => e.userId === currentUserId }).