# 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

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

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

```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`:

```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

```bash
elements create job RefreshDashboard
```

`app/jobs/refresh-dashboard.ts`:

```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

```bash
elements create page dashboard
```

`app/pages/dashboard/index.ts`:

```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`:

```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`:

```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 })`.