Three ways a GitHub ETL can silently delete valid alternatives — and how I fixed each

Description and use

The ETL that updates open‑source alternatives for SaaS products was silently deleting repositories due to transient API errors, name changes, and accidental seed truncation. Fixes include an error counter, using GitHub’s canonical name as the DB key, and a ratio guard to prevent bulk deletions when the seed is corrupted.

Problem

A nightly GitHub ETL job that upserts and deletes alternatives based on a seed list silently removed valid repositories due to transient API errors, casing drift, and accidental seed truncation.

Outcome

The ETL now reliably preserves valid alternatives, avoids duplicate rows, and protects against accidental bulk deletions caused by seed file corruption.

Workflow

• 1. For each SaaS entry, fetch alternatives from GitHub. 2. Collect successful full_name values into a keep array. 3. If any fetch fails, skip the delete for that SaaS slug. 4. On successful fetch, upsert alternatives using full_name as key and ON CONFLICT DO UPDATE. 5. Delete any stale rows with the same repo under different casing. 6. After processing all SaaS entries, prune stale SaaS records with a ratio guard to avoid mass deletions when the seed is truncated.

Evidence

If any fetch in the slug's loop fails, the DELETE NOT IN prune is skipped entirely for that slug.

Source

The fix uses r.full_name from the API response as the database key.

Source

A ratio guard prevents mass deletions when the seed file is truncated.

Source