Repository navigation
[stats] Daily rollup — the only way to get a series for agent-defined collections (and a gap is not a zero) #313
Description
Storage half of #310. Split out because it is a distinct chunk — a table, a cron pass, and one
semantic rule — and because the reason for it is not the one you would guess.
Why a rollup, and why it is not a perf optimisation
For agent-defined collections a daily snapshot is the only way to get a time series at all.
The lead-finder's leads collection holds 116 records under a schema the agent defined. You can
count them now. "Leads found per day" needs either a timestamp you control on every record —
which you do not, the shape is the agent's — or a snapshot taken each day. There is no third
option, and no amount of querying the collection later recovers a history nobody recorded.
Platform-owned sources (ai_usage, agent_loop_runs, agent_events) do carry timestamps and
could be aggregated live. Rolling those up too is then a genuine perf win, but it is the secondary
reason.
Shape
CREATE TABLE agent_stats_daily (
instance_id TEXT NOT NULL,
user_id TEXT NOT NULL,
card_id TEXT NOT NULL,
day TEXT NOT NULL, -- UTC date, YYYY-MM-DD
value_json TEXT NOT NULL,
computed_at TEXT NOT NULL DEFAULT (datetime('now')),
PRIMARY KEY (instance_id, card_id, day)
);- The cron is a single
* * * * *entry. Guard on the UTC day with
INSERT … WHERE NOT EXISTSon the primary key — the same single-flight shapeclaimDelivery
andrunDueTriggersalready use. No second cron entry, and overlapping minutes cannot
double-write. - Only instances with activity that day. Bounds the work, and is what was asked for.
- Retention cap (~400 days) swept in the same pass, so the table stays bounded.
The rule that must not be got wrong
A missing day is a gap, not a zero.
If the agent did not run, there is no row. The series type is therefore:
Array<{ day: string; value: number | null }>and null must render as a break in the line, never as 0. Reporting 0 says "you found no leads
on Tuesday" when the truth is "nothing ran on Tuesday" — a plausible value standing in for absent
information. That is the same class as #243 (an unparseable limit silently meaning drop
everything) and #252 (idle rendered over live work), both of which shipped and both of which were
only found by reading.
Worth a test asserting the two are distinguishable end to end, because it is exactly the kind of
thing that gets "simplified" to ?? 0 by a later change.
Consequences for #310's schema
Cards split into two families, declared:
| family | read path | example |
|---|---|---|
| trend | agent_stats_daily |
leads per day, runs per day |
| point-in-time | live query | current board counts, collection size now |
Only trend cards need history, and for them the cost section of #310 largely dissolves — a keyed
read replaces an aggregate. Point-in-time cards keep the bounded-window discipline.
No backfill
History starts the day this ships. The tab should say so rather than presenting a three-day series
as though it were the whole picture — an honest empty state beats a chart that implies the agent
did nothing before Tuesday.
Verification
- Two cron ticks in the same minute produce one row, not two.
- An instance idle for a day has no row for that day, and the series carries
null— asserted
distinctly from a real zero. - A card removed from the schema stops being rolled up; its history is retained until the cap.
- Rollup cost scales with active instances, not with total instances.
Activity
feat(stats): cards as data over a closed source vocabulary, and a rol…
serge-ivo commentedon Aug 6, 2026
Landed in
fedc8a3. Migration 0089agent_stats_daily, the shape you specified, plusidx_agent_stats_daily_dayfor the retention sweep (the primary key already serves the read).What it aggregates, and what it does not claim. It snapshots ONE scalar per (card, day) computed
from tables that already exist —
ai_usage,agent_loop_runs,pipeline_runs,agent_trigger_events, and the instance's own DO collections. No parallel record of what happened.Only
linecards are rolled up; a card removed from the schema simply stops being extended, and itsrows stay until the cap.
The rule.
buildSeriesreturnsArray<{day, value: number | null}>and never coalesces. A testasserts a stored
0and an absent day come out different — stated as three separate expectations,because
toEqualon the array would still pass if both silently became 0. A row whose payload willnot parse to a number also becomes a gap rather than a zero: we recorded something we can no longer
read, and a gap says unknown where a 0 would claim knowledge.
One design decision worth flagging: the sweep writes YESTERDAY's rows, never today's. A completed
UTC day is immutable, so
INSERT … WHERE NOT EXISTSon the PK is exactly right and no row is everwrong-because-partial. The alternative — writing today's partial value — puts a systematically low
final point on every chart, which reads as a collapse in the metric. That is the same lie as
gap-rendered-as-zero in a different shape, so today is deliberately absent and the API reports
throughDayto say so. The honest cost: "leads found today" is not visible in a trend (it isvisible in a
numbercard, which is live).On the guard, stated precisely:
WHERE NOT EXISTSstops the second tick from doing the work; theprimary key is what guarantees one row if two ticks interleave between the check and the insert.
The test uses a D1 stub that actually enforces the key, so it would fail a broken implementation
rather than a stub that always says
changes: 1.Verification:
null— asserted distinctly from a realzero.
swept in the same pass,
RETENTION_BATCH = 500, no second cron entry.tables for that day, filtered to instances with no row yet,
LIMIT 50per tick — so thebacklog drains in a few ticks after midnight and then costs nothing.
historyStart(MIN(day)for the instance) is returned by both stats endpoints soa surface can disclose where history begins instead of implying the agent did nothing before
Tuesday.
One subtlety recorded in the code: the six source tables mix
datetime('now')text, ISO text andms-epoch integers. Text bounds are always midnight-aligned, and at a day boundary both text
shapes order identically against it — so the range compares are format-agnostic. A bound at any
other time of day would not be, which is why
statsPeriodonly ever builds midnight boundaries.CI is in a declared outage; no CI run verified this. Verified locally only (typecheck, 4157 unit
tests, biome unchanged from baseline, docs:drift, size:check).
feat(console): the Stats surface, where a gap stays a gap (#311)
fix(api,runner,console): a terminal-driven CLI is unmetered, not free (…