Build1 publisher3 min readPublished
One 403 among five fetches lets a nightly prune delete a repo that still exists
A refresh job builds its keep list from successful GitHub responses, so a throttled request looks exactly like a repo that was curated out of the seed. The published fix is a per-slug failure counter that suppresses the prune.
The Engineer · Build desk
What happened
- A nightly job behind an OSS alternatives directory upserts every repo it can fetch from GitHub's API and deletes the rows whose repos are no longer in the current seed list.
- The prune is a DELETE FROM alternatives WHERE saas_slug = ? AND lower(repo) NOT IN (keep), where keep holds only the full_name values of fetches that returned successfully.
- A 403 or 429 on one of a slug's five alternatives keeps that repo out of keep, so the DELETE removes a row that is still listed in the seed.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- constraint Coupling the prune to fetch success means a repo that fails on every run keeps its whole slug's stale rows alive, so the safe version of the job can no longer clean up on its own.
- exposure Any throttling upstream is now in the delete path of jobs that reconcile by difference, and the same silenced 403 pattern in a Reddit source cost the author 94 days of detection lag.
- decision Anyone running reconcile-by-difference has to pick which error is worse for their table: a row deleted while it still exists, or a row that survives after it should have gone.
The `keep` array is a list of successes, not a list of observations. A 403 or 429 from GitHub never reaches it, and the DELETE has no way to separate a repo that was curated out of the seed from one whose fetch was throttled [3]. Both states are the same symptom: the repo is missing from the set the `NOT IN` clause compares against [2].
Take a slug with five alternatives. One rate-limited response leaves four names in `keep` and the prune deletes the fifth, 20 percent of that page's rows, until a later run fetches it cleanly and reinserts it [16][3]. The author describes the visible result as flickering: present one day, absent the next, present again, hard to notice unless you are watching row counts [4].
The fix in the post is a counter. If `failed` is greater than zero for a slug, the `DELETE ... NOT IN` prune is skipped entirely for that slug, and the warning prints "skipping stale-row prune for this slug" [5][6]. Rows then survive until a clean pass, meaning one where every fetch in that slug's loop succeeded [5].
That counter treats every failure the same. A repo that has genuinely been removed upstream keeps failing, so the slug never gets a clean pass and its actually-stale rows stay in the table run after run [5]. For a directory of links, I would take an indefinite stale row over a silent delete. For a table where a surviving row grants access to something, the same trade goes the wrong way, and the failure needs to be classified before it is counted.
The same shape had already cost the author elsewhere. A Reddit source returned 403 and its catch handler returned an empty array instead of re-throwing, which produced a 94-day detection lag in one pipeline [7]. The author wrote that the fix in both cases is identical: 'distinguish "successfully queried and found nothing" from "query failed, and I don't know what's there."' [8]
The second failure mode in the post is about identity. The seed file lists alternatives by repo path, such as `Requarks/wiki` and `calcom/cal.com`, while GitHub's API returns a `full_name` carrying the current owner and exact casing, and repos get renamed [9]. Using the seed path as the database key produced duplicates: a 2026-09 audit of the directory pages found 4 pages with duplicate alternative listings from casing drift, and two pages where an alternatives row pointed back to the page's own SaaS product [10][11][12]. The rewrite keys on `r.full_name` with `ON CONFLICT(saas_slug, repo) DO UPDATE` [13]. A second statement is meant to remove rows stored under an old name or different casing, but the post ends mid-snippet and its SQL is not shown [14].
The author runs the same rule in a shelf-scanning project on a Raspberry Pi 3, where a detection is confirmed only if it appears in at least 2 of the last 3 scans [15].
What to watch
- Whether the failed counter learns to separate a permanent 404 from a transient 429, so a genuinely deleted repo stops blocking the prune for its whole slug.
- The truncated second DELETE: whether the old-casing cleanup still runs on a slug whose slug-level prune has been suppressed.
- Published row counts before and after the fix, which would show whether the flickering rows are actually gone.