[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

[serge-ivo]

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.

[serge-ivo]

Fixed and verified in production. 7852232.

What was wrong

Exactly as filed, plus one measurement the issue left open. D1's compound-SELECT ceiling is 5, measured against the production pags database:

terms	result
5	executes
6	D1_ERROR: too many terms in compound SELECT: SQLITE_ERROR

True with or without ALL, and whether or not each term is wrapped as SELECT * FROM (…). SQLite's compile-time default for SQLITE_MAX_COMPOUND_SELECT is 500; D1 sets it to 5, which is why nobody expected it. It is a parse failure, so the six-arm query never executed once, anywhere.

The fix

Option 1 from the issue: six separate statements, merged into a Set in TypeScript. The sources are now a declared table, so a seventh is a line in an array rather than an outage. SELECT DISTINCT supplies what UNION was providing, and LIMIT moved to the merged set — the trap the issue flagged.

Trimming to five arms was rejected for the reason given: it leaves the ceiling one commit away and the failure is invisible until production.

Each of the six statements was executed against the production database before commit — all six parse and return rows (6 / 1 / 4 / 6 / 2 / 0 distinct instances for 2026-08-07). That mattered more than usual: the old query had never run anywhere, so a wrong column name in any arm would have been exactly as invisible as the ceiling was.

Verified in production

• Deploy landed 05:28:25Z. Last flood row: 05:28:07Z, 18 seconds earlier.
• At 05:56Z — ~28 minutes, ~28 cron ticks, zero new rows. Count frozen at 1811.
• Log composition since the deploy: 2 × chat, 2 × client:api, and my own [audit] Do we log all failures? Five verified holes — transient infra and pre-auth failures are never recorded, and 97% of the log is one bug #424 probe. The 97% signature is gone from the live feed.

Acceptance criteria

• activeInstancesForDay completes without a D1 error against production-shaped data.
• Adding a seventh activity source does not reintroduce a ceiling — findCompoundSelectOverruns + a tree-wide guard in sql.test.ts, verified to flag the pre-fix query at the exact line (stats-rollup.ts:75, 5 unions / 6 terms). It reads plain quoted strings as well as templates, since half this Worker's SQL is written that way.
• A sweep failing every tick produces bounded log volume, and its repetition is visible as a count — done in [audit] Do we log all failures? Five verified holes — transient infra and pre-auth failures are never recorded, and 97% of the log is one bug #424 (8c55639, migration 0103): an identical failure inside an hour increments repeat_count instead of inserting a row.
• /v1/admin/errors/summary no longer shows this signature growing.
• agent_stats_daily receives rows; a trend card renders real data. — not achievable today, and not for a code reason. Zero agents declare a statsSchema and zero instances carry a stats override (verified: SELECT COUNT(*) FROM agents WHERE config LIKE '%statsSchema%' → 0). So resolveStatsCards returns [] for every instance, trendCards is empty, and there is genuinely nothing to roll up. The table staying empty is now correct behaviour, not a failure — but it means the [stats] Per-agent Stats tab — declarative card schema over a closed, owner-scoped source vocabulary #310/[stats] Configure stats from MCP and from the agent itself — one validator, no privileged path #312/[stats] Daily rollup — the only way to get a series for agent-defined collections (and a gap is not a zero) #313 cards still have never had data, and the issue's point 4 ("they may have latent bugs masked by the empty table") stands unaddressed. Worth a follow-up that seeds a trend card on a real agent.

The coverage gap is closed: activeInstancesForDay now has direct tests (per-source statements, owner scoping, day bounds, set merge, merged-limit, and that text bounds go to text columns while epoch-ms bounds go to ms columns).

One thing found on the way, NOT fixed here

unionAll in lib/instance-work.ts builds one UNION ALL branch per instance, so recentWorkForInstances and recentActsForInstances hit this identical ceiling for a supervisor with six or more subordinates. I measured the wrapped SELECT * FROM (…) shape specifically: it fails at six exactly like the bare one.

Not fixed under this issue — it is a different feature (supervision) and wants its own ticket rather than silent scope growth. A static scan cannot see a runtime-built union; the guard's doc comment and its failure message both name the shape so it is not lost. Needs a chunk-at-construction cap.