[bug] The stats rollup has failed on every cron tick since it shipped — 1780 identical D1 compound-SELECT errors in 29h, and it is 97% of the error log #423

The stats rollup has never run successfully, and it is drowning the error log

activeInstancesForDay fails on every cron tick with D1_ERROR: too many terms in compound SELECT. It is the rollup's first query, so runStatsRollup dies before writing anything.

Measured, production, /v1/admin/errors/summary:

sample     : Error: D1_ERROR: too many terms in compound SELECT: SQLITE_ERROR
count      : 1780
firstSeen  : 2026-08-06 23:21:57
lastSeen   : 2026-08-08 04:57:07   (still failing as of filing)
users      : 0        (outer catch — no instance was ever reached)

Continuous, ~once a minute, for ~29.6 hours. The next-largest signature in the entire log has 15 occurrences, so this one bug is ~97% of the error log.

Stack, from a live row (id 945646f4-…):

Error: D1_ERROR: too many terms in compound SELECT: SQLITE_ERROR
    at async activeInstancesForDay (index.js:21121:23)
    at async runStatsRollup   (index.js:21175:20)

user_id = null on every row confirms the outer catch at stats-rollup.ts:175-177, not the per-instance one at :169-171. So the batch never begins: agent_stats_daily has received no rows since the feature shipped.

Where it is

workers/api/src/lib/stats-rollup.ts:74-100 — a CTE whose active arm is 6 SELECTs joined by 5 UNIONs:

WITH active AS (
     SELECT instance_id, user_id FROM ai_usage              WHERE …
     UNION SELECT instance_id, user_id FROM agent_trigger_events   WHERE …
     UNION SELECT instance_id, user_id FROM instance_runtime_tasks WHERE …
     UNION SELECT instance_id, user_id FROM agent_events           WHERE …
     UNION SELECT instance_id, user_id FROM agent_loop_runs        WHERE …
     UNION SELECT instance_id, user_id FROM pipeline_runs          WHERE …
   )
SELECT … FROM active a WHERE … LIMIT ?6

git log -L 74,100 shows all six branches arrived together in fedc8a3 "feat(stats): cards as data over a closed source vocabulary, and a rollup that admits a gap (#310, #312, #313)" (2026-08-07 07:06 +10:00 ≈ 2026-08-06 21:06 UTC). firstSeen 23:21 UTC is the deploy of that commit. It has never worked in production — not a regression, a launch that failed silently.

Why nobody noticed

The docstring on runStatsRollup (:148-151) says it "Swallows and logs its own failures — it is one of several independent sweeps sharing the * * * * * entry, and a broken rollup must not stop trigger delivery from draining."

That isolation decision is correct and should stay. The gap is that "logs it" was treated as sufficient: a sweep that fails 1780 times in a row is indistinguishable, in the log, from one that failed once. Nothing escalates, and the volume actively hides everything else.

What to do — cheapest first

1. Stop using a wide compound SELECT. The exact D1 compound-term ceiling is not verified by me (SQLite's compile-time SQLITE_MAX_COMPOUND_SELECT default is 500; D1 evidently sets it far lower — 6 branches is over it). So do not fix this by trimming to N branches and hoping: that re-breaks the next time someone adds a source. Structural options, in preference order:

• Six separate queries, unioned in TypeScript into a Set keyed by instance_id|user_id. Six indexed point-range reads, no compound SELECT, no ceiling, and each branch stays independently readable. LIMIT moves to the merged set.
• Or a single scan of one canonical activity table if one exists that already covers all six sources — but I did not find one, so I am not proposing it as the answer.

2. Guard the ceiling. Whatever the shape, add a test that fails when a new UNION branch is added to a D1 query beyond a known-safe count. sql.test.ts already scans SQL strings for a different invariant and is the natural home.

3. Make a repeating swept failure escalate. The isolation is right; the silence is not. Cheapest form: when the same sweep signature repeats beyond a threshold, log it once with the count instead of once per tick. That both stops the flooding and makes "this has failed 1780 times" the visible fact rather than something you only see by summing rows.

4. Verify the feature after the fix. agent_stats_daily should start filling; the #310/#312/#313 stats cards should be checked against a real instance, because they have never had data in production and may have latent bugs masked by the empty table.

Alternatives considered and rejected

• Reduce to 5 branches by dropping a source. Rejected: silently narrows what counts as an "active instance", changing product behaviour to dodge a SQL limit, and leaves the ceiling one commit away.
• Wrap in a retry. Rejected: deterministic failure, not transient. Retrying makes it worse.
• Remove the outer try/catch so it surfaces loudly. Rejected: it would let a broken rollup take down trigger delivery, which is precisely what the comment at :148-151 protects against.
• Raise the D1 limit. Not available — it is a platform constant.

Acceptance criteria

• activeInstancesForDay completes without a D1 error against production-shaped data.
• agent_stats_daily receives rows on the next tick; a trend card renders real data.
• Adding a seventh activity source does not reintroduce a compound-SELECT ceiling (test).
• A sweep failing every tick produces bounded log volume, and its repetition is visible as a count.
• /v1/admin/errors/summary no longer shows this signature growing.

Regression risk

• Splitting into six queries multiplies round-trips per tick; each is indexed and bounded by LIMIT, but confirm the tick still fits the cron budget. LIMIT semantics change (per-branch vs merged) — apply it after the merge or the batch silently shrinks.
• stats-rollup.test.ts covers buildSeries/completedDay/enumerateDays/insertDailyOnce/trendCards — not activeInstancesForDay, which is why a query that never executes shipped green. That gap is the thing to close.

Why this is filed now

Found while investigating a voice report (#420, #421). It is unrelated to voice, but it is why the durable error log was near-useless for that investigation: a q=voice query returned 4 rows against a log that is 97% one repeating cron failure. Fixing this restores the log as a debugging instrument, which is worth more than the stats feature it also unblocks.