orb(db): the review-effort metric is silently broken on Postgres — avg(text) errors and the failure is swallowed

[JSONbored]

Verified live

$ SELECT avg((metadata_json::jsonb ->> 'reviewEffortMinutes')) FROM audit_events LIMIT 1;
ERROR:  function avg(text) does not exist

json_extract(metadata_json, '$.reviewEffortMinutes') is translated by src/selfhost/pg-dialect.ts ~87 to ((metadata_json)::jsonb ->> 'reviewEffortMinutes'), which yields text. The enclosing AVG(minutes) then resolves to avg(text) — which does not exist in Postgres.

Both call sites swallow it: src/review/public-stats.ts ~358-371 wraps it in safeAll, and src/review/stats.ts ~424-440 uses .catch(() => ({results:[]})). So on the Postgres self-host the "review effort / minutes saved" metric is permanently zero and nobody is told — a published number that silently reports nothing.

The codebase already documents this exact ->>-returns-text hazard at src/db/repositories.ts ~3111-3117; this pair of call sites was missed.

Fix: AVG(NULLIF(json_extract(...), '')::numeric) on the PG path, or a dialect rule that emits ->> … ::numeric.

Same family, worth checking together

• CAST(substr(target_key, …) AS INTEGER) (public-stats.ts ~281/329/363, stats.ts ~429, public-accuracy-trend.ts ~119) is lenient on SQLite but fatal on Postgres. target_key is not uniformly two-segment — regateRepairTargetKey mints repo#pr#headSha (src/queue/processors.ts ~1097). One 3-segment key among the filtered event types aborts the entire public-stats query and safeAll returns [] — homepage counters silently go to zero.
• Timestamp formats are split three ways (ISO with Z, space-separated CURRENT_TIMESTAMP, numeric epoch-ms). Because ' ' sorts before 'T', public-accuracy-trend.ts ~153 comparing orb_pr_outcomes.occurred_at >= <full ISO> silently drops the entire boundary day. src/orb/analytics.ts ~211-213 avoids this by truncating to .slice(0,10) and documents why; loadOrbDayRows never got the same treatment.
• LOWER(col) defeats six indexes, notably listAuditEventsForTarget (repositories.ts ~3562) which has no other selective predicate — a guaranteed full scan of a 521 MB table, from the maintainer UI. The codebase already carries a "do not reintroduce a function-wrapped comparison on target_key" warning from a prior D1 CPU-limit outage (src/orb/outcomes.ts ~69-77); six sites still do it.

Note PG 16.14 means the max(ctid) PG≤13 concern does not apply here.

[linear-code]

JSO-6285