The OSS alternatives directory I'm building pairs each SaaS product with its open-source alternatives by fetching live data from GitHub's API. Every night a refresh job runs: it upserts the repos it can reach and deletes the ones that are no longer in the current seed list.
Deleting stale data is the right default. A repo that disappears from the seed was removed for a reason — the SaaS page it belonged to is gone, or the alternative was curated out. But the delete logic I had originally couldn't distinguish between "no longer in the seed" and "temporarily unreachable." Those are different states that produce the same symptom: the repo is missing from the keep array the DELETE uses as its NOT IN set.
I found three distinct ways that deletion was silently wrong. None of them produced an error. All of them would eventually cause data loss.
Failure 1: An incomplete keep-list from one failed fetch
The refresh loop works like this: for each SaaS entry, fetch each alternative from GitHub, push the successful full_name values into a keep array, then run DELETE FROM alternatives WHERE saas_slug = ? AND lower(repo) NOT IN (keep).
If GitHub returns a 403 or 429 for one of five alternatives, that repo doesn't make it into keep. The DELETE then removes it from the database even though it's in the seed and the only reason it's missing is a transient API error.
The next run — if it fetches successfully — re-inserts the entry. So the visible symptom is flickering: an alternative is present one day, absent the next, present again. Hard to notice unless you're watching row counts.
The fix is a failed counter per SaaS slug:
if (failed > 0) {
console.warn(
` ! ${s.slug}: ${failed} fetch failure(s) — skipping stale-row prune for this slug`
);
} else if (keep.length > 0) {
const placeholders = keep.map(() => "lower(?)").join(", ");
await db.execute({
sql: `DELETE FROM alternatives WHERE saas_slug = ? AND lower(repo) NOT IN (${placeholders})`,
args: [s.slug, ...keep],
});
}
If any fetch in the slug's loop fails, the DELETE NOT IN prune is skipped entirely for that slug. The entries stay in the database until a clean pass — one where every fetch succeeds — confirms the current ground truth.
This failure pattern appears in other forms. The 94-day detection lag in one of my pipelines came from a Reddit source that returned 403 and whose catch handler returned an empty array instead of re-throwing. An empty array is not obviously wrong. A 403 that's caught and silenced isn't obviously wrong either. The fix in both cases is identical: distinguish "successfully queried and found nothing" from "query failed, and I don't know what's there."
I run the same principle in a different domain: a shelf-scanning project running on a Raspberry Pi 3. Rather than alerting on any single scan that detects a gap, the system uses a temporal majority vote — a detection is only confirmed if it appears in at least 2 of the last 3 scans (the three post-processing layers are described here). The GitHub ETL's failed counter is the same principle: don't commit a DELETE based on a set of observations you know is incomplete.
Failure 2: Seed casing vs GitHub canonical naming
The seed file lists alternatives by GitHub repo path — Requarks/wiki, calcom/cal.com, and so on. GitHub's API returns a full_name that reflects the current canonical spelling, including the current owner and exact casing. The problem is that repos get renamed, owners change, and the seed spelling drifts away from what GitHub considers the authoritative identifier.
Original code used the seed path as the database primary key. Two bugs followed:
Duplicate rows: A repo appears as calcom/cal.com in one seed version and differently later. The next run inserts under the new spelling without removing the old row. A 2026-09 audit of the directory pages found 4 pages with duplicate alternative listings from exactly this casing drift.
Self-referencing pages: Two pages had an alternatives row pointing back to the same product as the page's SaaS entry — a repo ended up in its own product's list. These are in the audit notes as "自己参照 2 頁" (self-reference 2 pages).
The fix uses r.full_name from the API response as the database key:
const canonical = r.full_name; // resolved after GitHub rename/transfer
await db.execute({
sql: `INSERT INTO alternatives (saas_slug, repo, ...) VALUES (?, ?, ...)
ON CONFLICT(saas_slug, repo) DO UPDATE SET
name = excluded.name, stars = excluded.stars, ...`,
args: [s.slug, canonical, ...],
});
// Remove any row stored under a different casing or old name for this repo
await db.execute({
sql: `DELETE FROM alternatives
WHERE saas_slug = ? AND repo <> ?
AND (lower(repo) = lower(?) OR lower(repo) = lower(?))`,
args: [s.slug, canonical, canonical, repoFull],
});
The second DELETE fires on every successful fetch. It catches the case where the DB held the seed spelling and the API returned the canonical — removing the stale-casing row immediately after writing the canonical one. This is different from the NOT IN prune in failure 1: it's a per-repo cleanup on success, not a per-slug cleanup at the end of a loop.
The relationship to ON CONFLICT patterns is worth naming. DO UPDATE SET is correct here because the API's response (current stars, last push date) is fresher than what was in the DB. Using DO NOTHING instead would mean repos that existed under the old casing would never get their data refreshed. The right strategy depends on which version of the data is authoritative, and the API is authoritative over the seed for live metrics.
The GitHub API license fields I described earlier have a similar property: what the API says about a license is authoritative over what I infer from other signals, and the DB should reflect the API's answer, not my seed's guess.
Failure 3: Unguarded bulk delete when the seed file shrinks
A separate function, pruneStaleSaas, handles SaaS-level cleanup: if a product is removed from the seed, its rows (SaaS record, content, all alternatives) should be removed from the database.
The logic is straightforward: read all SaaS slugs from the DB, filter out those present in the current seed, delete the rest. This works when the seed is intact. When the seed file is accidentally truncated — a merge conflict that ate half the JSON, an editing mistake — the "stale" list becomes most of the database.
The fix is a ratio guard:
const MAX_STALE_SAAS_RATIO = 0.1;
const MAX_STALE_SAAS_FLOOR = 3;
const limit = Math.max(
MAX_STALE_SAAS_FLOOR,
Math.floor(rows.rows.length * MAX_STALE_SAAS_RATIO)
);
if (stale.length > limit) {
console.error(
` ! ${stale.length} saas rows not in seed exceeds prune limit ${limit} — skipping prune (check seed-saas.json)`
);
return;
}
If more than 10% of DB rows appear stale — or more than 3 rows when the database is small — the prune is skipped and an error is logged. Between runs, the seed almost never loses more than 10% of entries legitimately. A stale count that high is almost certainly a data anomaly.
The floor of 3 handles early-stage databases: at 5 rows, 10% would be 0, which would fire the guard on a single legitimate removal. The floor ensures small databases can still prune.
This is a circuit-breaker pattern, not a repair. It stops the damage and logs something visible. The three approaches to silent failure detection I've written about all share this structure: detect the anomaly, stop, make noise. The code doesn't try to infer whether the seed file is valid — it just checks whether the prune looks plausible given current DB state.
The shared principle
Looking at the three fixes together: before any DELETE, verify that the input driving the DELETE is trustworthy.
- Failure 1: the
keeplist must be complete (no failed fetches). If it's not, skip the delete. - Failure 2: the repo identity must be resolved through the API's canonical name, not the seed's spelling. Use the API answer as the key.
- Failure 3: the "absent from seed" count must be plausible given current DB size. If it's not, skip the bulk prune.
None of these changed the delete logic itself. They're preconditions: checks on whether the delete instruction is based on trustworthy inputs before it runs.
A practical rule from this: any DELETE that uses an externally derived set (API results, a seed file, a user-supplied list) should have an explicit check on whether that set is complete before running. If completeness can't be verified, defer to the next run. A stale row persisting an extra night is much cheaper than a valid row vanishing without an error message.
FAQ
Why not just log errors instead of skipping deletes?
The errors are logged — console.warn names the slug and the failed count. But logging doesn't protect the rows. The guard and the logging are both needed: the guard prevents data loss, the log makes the gap visible.
What if the failed counter fires on a genuinely deleted repo?
If GitHub returns 404 for a repo that's actually gone (not temporarily unavailable), the failed counter fires and the prune is skipped, leaving the deleted repo in the database for one more night. The tradeoff is deliberate: a falsely persistent row is recoverable; a falsely deleted row isn't (without log archaeology). The next time the seed is updated to remove that repo, the per-slug cleanup will handle it.
How does the ratio guard handle a legitimate large cleanup?
If a batch seed cleanup removes more than 10% of entries at once, the guard blocks the prune. The fix is to either raise the ratio temporarily or run the prune manually. The guard is a circuit breaker on unexpected deletions, not a permanent cap on how many entries can be legitimately removed.
Does canonical full_name resolution require an extra API call?
No. The full_name is in the same response as stars, description, and license — there's no additional call per repo. The 100ms sleep between calls was already present for rate-limit reasons, not added for the canonical lookup. For reference, GitHub's authenticated REST API rate limit is 5,000 requests per hour, so 100ms (36,000 per hour at max throughput) is well inside the safe range.
Part of an ongoing 6-month experiment running three AI-curated directory sites. The technical claims here are real; this article was AI-assisted.
Top comments (1)
The failed-counter guard is the part most people get wrong, because the bug is invisible: a 403 that gets caught and turned into an empty list is indistinguishable from a genuine "no alternatives", so the prune deletes confidently instead of failing. Your rule "don't commit a DELETE on a set of observations you know is incomplete" is the same one I apply to any reconcile step now, and the fact that it showed up again as a 94-day detection lag on a silenced catch says the failure mode is not exotic.
One thing I'd be careful about: a slug that keeps failing stays unpruned indefinitely, so a genuinely-removed alternative never leaves the table. Do you cap the number of consecutive skipped passes per slug, or does the clean-pass requirement eventually force a manual review? I've found that the skip path needs its own alarm, otherwise you've traded silent deletion for silent staleness and neither one pages you.