fix(backfill): #4529's freshness gate reads a clock unrelated jobs keep bumping — the open-data crawl has been skipped since 2026-07-09 and recent_merged_pull_requests is dead

[JSONbored]

Summary

recent_merged_pull_requests has had no new rows since 2026-07-09T23:26Z. The writer was never removed, never flag-gated, and never throws — the entire 4-segment open-data backfill fan-out (labels, open_issues, open_pull_requests, recent_merged_pull_requests) has been skipped on every 30-minute cron tick since that timestamp, because the freshness gate added in #4529 reads a clock that unrelated, high-frequency jobs keep rewriting.

Three of the four segments hide the damage (their tables have webhook or sweep writers that keep them current). recent_merged_pull_requests is the only one whose sole writer is the backfill, so it is the only one that visibly froze.

Evidence (edge-nl-01 Postgres, 2026-07-31)

repo_sync_segments, the three installed repos:

segment	status	mode	completed_at
open_pull_requests	complete	light	2026-07-31T11:46:11Z
pull_request_files / pull_request_reviews / check_summaries	partial	resume	2026-07-31T11:46:22Z
labels	not_modified	light	2026-07-09T23:25:15Z
open_issues	complete	light	2026-07-09T23:25:29Z
recent_merged_pull_requests	sampled	light	2026-07-09T23:26:01Z

repo_sync_state for all three: status = success/partial, last_completed_at = minutes ago.

The remaining 15 repos in the table are is_installed = 0 and froze separately on 2026-07-13 when the cron fan-out (which filters on isInstalled) stopped selecting them. That part is expected, not a bug.

Root cause

Commit a6fb46ab — fix(backfill): scheduled per-repo backfill respects freshness/error-backoff (#4529), closing #4497 — landed 2026-07-09T23:27:09Z. The last open-data crawl completed 2026-07-09T23:26:01Z, sixty-eight seconds earlier. Nothing has run since.

That commit added syncFreshnessSkipReason to enqueueRepositoryOpenDataBackfill (src/github/backfill.ts:338, called at :485), which is the function the real scheduled cron routes through. It skips the fan-out when:

status ∈ {success, partial, capped} && (now - repo_sync_state.lastCompletedAt) < FRESH_SYNC_MS  // 6h

The problem is repo_sync_state.lastCompletedAt. It is a repo-wide clock written by refreshRepoSyncStateFromSegments (src/github/backfill.ts:1984), which is called at the end of every segment write path — including two that run far more often than every six hours and have nothing to do with open-data:

1. refreshOpenPullRequestsForScheduledSweep (src/queue/processors.ts:1094) calls backfillRepositorySegment("open_pull_requests", force: true) on the ~2-minute re-gate sweep cadence. That call ends in refreshRepoSyncStateFromSegments (backfill.ts:589).
2. On completion it enqueues backfill-pr-details (backfill.ts:587), and backfillOpenPullRequestDetails ends in the same call (backfill.ts:724).

So lastCompletedAt is refreshed every few minutes, permanently inside the 6-hour window, and the gate #4529 introduced never opens. labels, open_issues, and recent_merged_pull_requests are never dispatched again. open_pull_requests survives only because it has its own independent refresher that bypasses the gate with force: true.

The skip is also silent: enqueueRepositoryOpenDataBackfill returns the reason as a warnings[] string that its cron caller (src/queue/job-dispatch.ts:202) discards. Nothing is logged, counted, or audited — which is why three weeks of a dead crawl produced no signal anywhere.

This is the same class as #377 (closed): a freshness rollup that quietly swallowed the merged-history crawl. #377 fixed the rollup's inputs; this is the rollup's consumers reading a clock too coarse to mean what they need.

What degraded

listRecentMergedPullRequests (200-row cap, mergedAt desc) has been returning a window that ends 2026-07-09 for three weeks. Consumers:

• src/queue/copycat-detection.ts:99 — the copycat containment engine wired into the live gate (feat(review): wire the copycat containment engine into the gate #5999). Its candidate set is merged PRs; nothing merged after 07-09 can be matched against, so recent copy-of-a-recent-merge cases are invisible to it.
• src/review/repo-culture-profile.ts:238,310 — worse than stale. The cache-invalidation signal is countRecentMergedPullRequests; with the count frozen the profile never regenerates, so it is pinned to a three-week-old snapshot by design of the invalidation check.
• src/review/maintainer-recap-wire.ts:201 — the maintainer recap reports merged-PR activity that stops on 07-09.
• src/services/{issue-quality,maintainer-lane,maintainer-noise,repo-outcome-patterns,contributor-issue-draft,agent-orchestrator}.ts — every one of these derives stats/history from the frozen window.
• src/queue/signal-snapshot.ts:114, src/queue/processors.ts:6401,16495.
• src/api/routes.ts (7 call sites) and src/mcp/server.ts (6 call sites) — public API and MCP surfaces serving a three-week-old picture as current.

Separately: listContributorRecentMergedPullRequests (src/db/repositories.ts:5411) has zero production callers — only test/unit/data-spine.test.ts references it. It is dead code independent of this bug.

Is the table superseded by pull_requests?

No. pull_requests only holds PRs the review pipeline has actually observed; recent_merged_pull_requests holds backfilled merged history, including PRs that predate installation and repos the pipeline never processed:

repo	recent_merged_pull_requests	pull_requests merged	of which merged ≤ 07-09
JSONbored/loopover	2185	3655	1327
JSONbored/metagraphed	1767	2703	910
JSONbored/awesome-claude	1193	979	327
we-promise/sure	755	0	0
gittensor-vanguard/vanguarstew	347	0	0
(7 more uninstalled repos)	3–237 each	0	0

Nine of twelve repos have zero merged rows in pull_requests. For the three installed repos the pre-07-09 history in recent_merged_pull_requests is ~2.5x what pull_requests retains. 5,383 of 6,927 rows also carry hydrated changed_files_json, which is what the copycat engine matches on.

Restore the writer. Do not drop the table.

Proposed fix

1. Gate the open-data fan-out on an open-data-specific freshness anchor instead of the repo-wide lastCompletedAt — the per-segment timestamps already on repo_sync_state (labelsSyncedAt, issuesSyncedAt, pullRequestsSyncedAt, mergedPullRequestsSyncedAt), taking the oldest of them. This preserves exactly what fix(backfill): scheduled per-repo backfill ignores freshness/error-backoff, re-syncs every repo every 30 min forever #4497 asked for (a repo that genuinely completed an open-data crawl still backs off for 6h) while making a segment that has not run in three weeks read as stale. The error-backoff branch keeps using status/lastCompletedAt unchanged.
2. Make the skip observable: emit a counter/log on the skip path in enqueueRepositoryOpenDataBackfill so a permanently-skipped repo is visible instead of silent.
3. Delete listContributorRecentMergedPullRequests and its test (dead code).

Backfilling the missed 2026-07-09 → now window needs no special work: the segment crawls /pulls?state=closed&sort=updated&direction=desc from page 1 and upserts, so the first unblocked run picks the gap up.

[linear-code]

JSO-7118

[JSONbored]

Follow-up: the table is also on a 30-day deletion clock, and the gap does not fully self-heal

Two corrections/additions to the finding above, both found while verifying that the fix in #10203 actually recovers the data.

1. Retention will empty the table by ~2026-08-10.

src/db/retention.ts:97 prunes recent_merged_pull_requests at 30 days on updated_at. Live range on edge-nl-01 right now:

min(updated_at) = 2026-07-01T17:05:59Z
max(updated_at) = 2026-07-11T06:25:29Z
count           = 6927

Nothing has refreshed updated_at since 07-11 because the upsert is the only thing that writes it. The oldest rows are at the window boundary today; the entire table ages out around 2026-08-10.

The retention policy's own rationale (same file, the comment above the entry) is that these are "re-derivable GitHub mirrors… re-fetched from GitHub on the next sync of the PR that needs them." That is exactly the assumption this bug broke. With the writer dead, retention stops being a bound on growth and becomes an unopposed deletion path — the stale-data problem quietly converts into a data-loss problem on a fixed timer. Restoring the writer stops the countdown, because an upsert refreshes updated_at.

2. The missed window does not fully self-heal on the first unblocked run.

I said above that no backfill was needed. That is only true for the recent end of the gap. A scheduled run fetches the top SEGMENT_PAGE_BUDGET pages of /pulls?state=closed&sort=updated&direction=desc — 2 pages (200 PRs) on light, 10 (1000) on the 6-hourly full window. recent_merged_pull_requests is excluded from the resume re-send at src/github/backfill.ts:625, and progressiveHistory maps "complete + hasMore" to sampled rather than running, so a scheduled run never paginates past its page budget. Since merged PRs are rarely updated after merge, successive runs re-cover roughly the same recent window rather than walking backward.

For JSONbored/loopover there are ~2,300 PRs merged since 07-09, so the deeper portion (roughly 07-09 → mid-July) needs one forced full/resume crawl through the admin segment endpoint after the fix ships. Worth doing before the retention boundary rather than after.

Rows for the nine uninstalled repos in the table (1,782 rows) will age out regardless — they are not dispatched at all — which is consistent with the table being a rolling mirror rather than an evidentiary trail, so I have not treated that as in scope.

[JSONbored]

Resolved — fix merged, and production recovered manually ahead of the image

#10203 merged to main at 2026-07-31T12:34:15Z (verified present on origin/main: the freshness anchor, the skip metric/log, and the removal of the dead reader all landed intact through a concurrent-merge race).

Production was recovered without waiting for the image. The running ORB is still orb-v3.7.0-beta.7, which carries the bug, so the gate would have kept starving the crawl until the next beta snapshot. Since force: true bypasses the broken gate on the current image, I ran one full crawl per installed repo through /v1/internal/jobs/backfill-repo-segment/run:

repo	rows before	rows after	newest merge before	newest merge after
JSONbored/loopover	2185	2923	2026-07-09T23:13Z	2026-07-31T12:31Z
JSONbored/metagraphed	1767	2466	2026-07-09T23:12Z	2026-07-31T12:08Z
JSONbored/awesome-claude	1193	1758	2026-07-09T23:14Z	2026-07-30T20:44Z

The read window every consumer actually sees (listRecentMergedPullRequests is ORDER BY merged_at DESC LIMIT 200) is now 2026-07-29 → 2026-07-31 and fully populated, so the copycat engine, the culture profile, the recap, the services, and the API/MCP surfaces are serving current data again as of now rather than after the next deploy.

The retention clock is stopped. max(updated_at) moved from 2026-07-11T06:25Z to 2026-07-31T12:34Z on all three repos, so the ~2026-08-10 wipe described above no longer applies to the rows the crawl touched.

One hole remains and is deliberately left. 2026-07-10 → 2026-07-23 is still empty (~1,700 PRs on JSONbored/loopover). The full run's 10-page budget covered 07-24 → now; everything older is unreachable, and I verified experimentally that it is unreachable permanently — a resume run with an explicit cursor: "11" against the sampled segment returned nextCursor: "11" with fetchedCount unchanged, i.e. it restarted at page 1. That is a separate latent defect, now filed as #10209. I did not force it open by editing segment bookkeeping in the production database, because the hole sits entirely outside the 200-row read window, no consumer can observe it, and those rows would age out under the 30-day retention within about two weeks regardless.

Closing. Follow-up: #10209.

[JSONbored]

Correction: the damage was wider than reported — 490 issues were stuck open locally

I wrote above that the other three open-data segments were "masked" by webhook writers and that only recent_merged_pull_requests visibly froze. That was wrong for open_issues, and the error understated the impact.

Webhooks keep the issues table's rows current, but they do not perform the reconciliation that only runs at the end of a complete open_issues crawl (markUnseenOpenIssuesClosed, wired through reconcileOnComplete). With the crawl starved since 2026-07-09, any issue whose close event was missed or never delivered stayed state='open' locally forever, with nothing to sweep it up.

Forcing the segment on edge-nl-01 just now closed the backlog:

repo	stale open rows reconciled
JSONbored/loopover	233
JSONbored/metagraphed	205
JSONbored/awesome-claude	52
total	490

Local open-issue counts now match GitHub exactly (141 / 40 / 4 against is:issue is:open), where before they were inflated by roughly 2.5x–6x.

This matters more than the merged-PR staleness did, because the contributor pipeline reads open issues directly — src/services/issue-quality.ts, src/services/contributor-issue-draft.ts, and src/services/maintainer-lane.ts were all computing against a set where the large majority of "open" issues on metagraphed and awesome-claude had actually been closed for weeks.

labels was also starved and is now re-crawled. Note that segment is upsert-only with no reconcileOnComplete, so labels deleted on GitHub are never pruned locally — repo_labels still holds 44/57/56 rows against 24/30/50 live. That is pre-existing behaviour rather than a consequence of this bug, so I have not filed it; flagging it in case the divergence matters to anything reading repo_labels without windowing on last_seen_at.

All four open-data segments are now current on all three installed repos. The permanent fix is on main; this recovery was manual because the running image (orb-v3.7.0-beta.7) predates it.