Cron Cache

Stats that would be expensive to compute on every request, cached in a single-row table. A cron job recomputes the row every five minutes; pages read the cached row with one cheap select. The pattern decouples the expensive aggregate work from the request path, so user-facing latency stays predictable even as the underlying data grows.

The recipe shows a "site stats" panel: total user count, active-this-week count, revenue this month. The aggregates run inside a RefreshStatsJob triggered by app.cron, which writes the latest values into a single stats row keyed by a fixed id. The dashboard route reads that one row.

Migration

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

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

-- add 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,
  activeThisWeek integer not null default 0,
  revenueThisMonthCents integer not null default 0
);

insert into stats (id) values (1);

The stats table holds exactly one row. The check (id = 1) constraint plus default 1 ensures every insert and every update operates on the same row. The seed insert in the migration creates that row, so the first page load doesn't have to handle an empty cache. The cron job below upserts the same row by id.

Stats job

elements create job RefreshStats

app/jobs/refresh-stats.ts:

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

/** @job */
export class RefreshStatsJob extends Job {
  run() {
    let row = sql<{
      totalUsers: number;
      activeThisWeek: number;
      revenueThisMonthCents: number;
    }>(
      `select
         (select count(*)::int from users) as totalUsers,
         (select count(*)::int from users where lastSeenAt > now() - interval '7 days') as activeThisWeek,
         (select coalesce(sum(amountCents), 0)::int from orders where createdAt >= date_trunc('month', now())) as revenueThisMonthCents`,
    ).firstOrThrow();

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

The job runs three aggregates in one SQL round-trip and then a single update against the cached row. Each aggregate has its own where clause, which keeps the query readable and gives Postgres independent indexes to use. The job is fieldless (extends Job with no <Fields> type parameter) because the cron tick is the only trigger; no payload to pass.

Dashboard page

elements create page dashboard

app/pages/dashboard/services.ts:

export interface Stats {
  computedAt: Date;
  totalUsers: number;
  activeThisWeek: number;
  revenueThisMonthCents: number;
}

app/pages/dashboard/index.ts:

import { sql } from "@elements/app";
import dashboard from "./template";
import { Stats } from "./services";

export default function route(req, res) {
  let stats = sql<Stats>(
    `select computedAt, totalUsers, activeThisWeek, revenueThisMonthCents
     from stats where id = 1`,
  ).firstOrThrow();

  return new dashboard({ stats });
}

The route does one select against a single-row table. The query is constant-time regardless of how many users or orders the app has accumulated. The page renders with the values as of the last cron tick.

app/pages/dashboard/template.html:

import "./style.css";
import { Stats } from "./services";

<html class="dashboard" (stats: Stats)>
  <h1>site stats</h1>

  <ul class="cards">
    <li>
      <span class="label">total users</span>
      <span class="value">{stats.totalUsers.toLocaleString()}</span>
    </li>
    <li>
      <span class="label">active this week</span>
      <span class="value">{stats.activeThisWeek.toLocaleString()}</span>
    </li>
    <li>
      <span class="label">revenue this month</span>
      <span class="value">${(stats.revenueThisMonthCents / 100).toLocaleString()}</span>
    </li>
  </ul>

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

The template renders the cached values plus the computedAt timestamp so the reader knows how stale the snapshot is.

Routes

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

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

// ...
app.route("/dashboard", dashboard);
app.cron("every 5m", "refresh site stats", () => new RefreshStatsJob().schedule());

The cron entry fires every five minutes; the callback schedules a RefreshStatsJob. Postgres arbitrates the cron leader so exactly one machine fires each tick across the whole deployment. The job itself runs through the standard worker pool, with retries and timeouts handled by the job system.

Notes

• Why one row, not one row per snapshot. Storing every snapshot would grow the stats table without bound and force the route to find the latest row on every read. The single-row pattern bounds storage and makes the read a constant-id lookup. If history matters, write to a separate statsHistory table from the job in addition to updating stats.
• Why a job, not raw SQL in the cron callback. The cron callback is supposed to be a one-liner that schedules a job. Job retries, timeouts, and worker-pool scaling all kick in for the actual aggregation. If RefreshStatsJob.run() errors, the job system retries with exponential backoff; an error inside the cron callback itself would not retry.
• Picking the cron cadence. Five minutes is a balance: fresh enough for a dashboard, infrequent enough that the aggregates stay cheap. For per-second freshness, see elements man recipes live-dashboard, which adds a channel and reactive re-fetch on top of this same pattern. For nightly summaries, drop to every day at 2am.
• Initial value. The migration inserts the seed row so the first page load works before the cron has ever fired. Without the seed, the route would throw NotFoundError until the first tick lands.
• Per-tenant stats. Replace the check (id = 1) constraint with a tenantId uuid primary key foreign key. The job loops over tenants and writes one row per tenant. Pages read by where tenantId = ${session.get('tenantId')}.