selfhost(stats): an Orb reports totals.handled = 0 while its ledger holds 2,123 verdicts

[JSONbored]

Found by turning on LOOPOVER_PUBLIC_STATS on edge-nl-01 to test #9940's option C. Caught immediately by the public verifier (#9723) and reverted within minutes — the flag is back off and nothing contradictory was left published.

The contradiction

$ npx -p @loopover/mcp loopover-verify --base-url https://shots.loopover.ai
FAIL  Published headline stats agree with the ledger-derived parity rollups
      parity rollups report 2123 verdict(s) over their window, exceeding the all-time handled count of 0

On the same deployment, in the same payload:

• reviewParity.verdicts = 2123 — read from decision_records, matching the ledger's ~2,200 rows
• totals.handled = 0

An Orb that has decided 2,123 pull requests reporting zero handled is not a rounding difference; one of the two reads is looking at the wrong place.

Why it matters beyond cosmetics

This is the deployment that actually performs reviews. If self-hosters enable public stats, they publish a headline of 0 next to a parity block describing thousands of verdicts — and accuracyPct derives from the same handled/auto-action population, so it is suspect too. That is worse than publishing nothing, which is why the flag was reverted rather than left on.

Likely cause

totals.handled is built from audit_events (github_app.pr_public_surface_published, agent.action.*) joined to the pull_requests cache and folded with orb_pr_outcomes, while reviewParity reads decision_records directly. On this Orb the primary store is Postgres, not Cloudflare D1 — so the audit/outcome side of the stats query is very likely reading a store that is empty here, while the decision_records side reads the populated one.

If that is right, every totals.*-derived figure (handled, reviewed, merged, closed, accuracyPct, minutesSaved) is wrong in self-host mode, and only the reviewParity block is correct.

Acceptance

• On a self-hosted Orb with a populated ledger, totals.handled reflects reality rather than 0.
• loopover-verify --base-url <orb> reports the stats-parity claim as PASS.
• A regression test pins that totals.handled and reviewParity.verdicts cannot disagree by orders of magnitude on the same payload — the invariant the verifier checks.
• Until then, LOOPOVER_PUBLIC_STATS on an Orb stays off; see fairness: the anchored ledger lives on the ORB, which is not publicly reachable — the public API serves an empty one #9940 for why the two surfaces are separate.

[linear-code]

JSO-6952

[JSONbored]

Root cause is not the D1/Postgres store split hypothesised above. Traced it end to end on edge-nl-01; the store is fine.

What it actually is

totals.* is gated on publicStatsProjects(env), which reads LOOPOVER_PUBLIC_STATS_REPOS. On the ORB that variable is not set, so projects is [] and getPublicStats takes its own early-return branch:

const [dispositions, ...] = projects.length === 0
  ? await Promise.all([Promise.resolve([]), ...])   // <- every totals.* input is empty
  : await Promise.all([ safeAll(...disposition query...), ... ]);

totals.handled then sums nothing and reports 0. reviewParity reads decision_records directly and is not gated by that allowlist, so it correctly reports 2,123. Two halves of one payload, two different gates.

The function even documents the zero as intended: "Empty allowlist => the own-ledger side reports zero (still fails safe)". It fails safe in isolation and self-contradictory next to a parity block that answers ungated.

Evidence the store is fine

The disposition query runs correctly against the ORB’s Postgres and returns real data:

JSONbored/awesome-claude | 1260 | 950  | 310  | 0
JSONbored/loopover       | 4618 | 2899 | 1719 | 0
JSONbored/metagraphed    | 3779 | 1991 | 1786 | 2      -> handled = 9,657

I also checked the two portability traps that would have produced the same symptom, and both are already handled:

• instr() genuinely does not exist in Postgres (ERROR: function instr(text, unknown) does not exist) — but pg-dialect.ts rewrites it. I ran the real translateInstr/translateFunctions over PUBLISHED_PR_KEYS and confirmed correct strpos() output.
• The unaliased subquery in FROM (...) is accepted by this Postgres (16 relaxed that requirement).

So the query, the dialect layer and the store are all sound. The bug is the gating asymmetry.

Consequence for the fix

Setting LOOPOVER_PUBLIC_STATS_REPOS on the ORB would make the number correct, but it does not remove the trap: any deployment that leaves it unset still publishes 0 beside thousands of parity verdicts, and accuracyPct derives from the same empty population.

The durable fix is the invariant the acceptance criteria already name — the two halves must not be able to contradict each other. Either they share one gate (parity goes quiet when the own-ledger side is unconfigured) or totals derives from the same ledger the parity block reads. I read the privacy intent behind the allowlist as favouring the first, since it never widens what gets published.