[stats] Daily rollup — the only way to get a series for agent-defined collections (and a gap is not a zero)

[serge-ivo]

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 EXISTS on the primary key — the same single-flight shape claimDelivery
  and runDueTriggers already 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.

[serge-ivo]

Landed in fedc8a3. Migration 0089 agent_stats_daily, the shape you specified, plus
idx_agent_stats_daily_day for 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 line cards are rolled up; a card removed from the schema simply stops being extended, and its
rows stay until the cap.

The rule. buildSeries returns Array<{day, value: number | null}> and never coalesces. A test
asserts a stored 0 and an absent day come out different — stated as three separate expectations,
because toEqual on the array would still pass if both silently became 0. A row whose payload will
not 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 EXISTS on the PK is exactly right and no row is ever
wrong-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
throughDay to say so. The honest cost: "leads found today" is not visible in a trend (it is
visible in a number card, which is live).

On the guard, stated precisely: WHERE NOT EXISTS stops the second tick from doing the work; the
primary 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:

• ✅ Two ticks in the same minute → one row (and the first value wins; a completed day is immutable).
• ✅ An idle instance has no row, and the series carries null — asserted distinctly from a real
  zero.
• ✅ A card removed from the schema stops being rolled up; history retained to the ~400-day cap,
  swept in the same pass, RETENTION_BATCH = 500, no second cron entry.
• ✅ Cost scales with ACTIVE instances: the candidate query is a UNION over the six timestamped
  tables for that day, filtered to instances with no row yet, LIMIT 50 per tick — so the
  backlog drains in a few ticks after midnight and then costs nothing.
• ✅ No backfill; historyStart (MIN(day) for the instance) is returned by both stats endpoints so
  a 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 and
ms-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 statsPeriod only 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).