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

说明与用途

ETL 在更新 SaaS 产品的开源替代品时，由于临时 API 错误、仓库名称更改和种子文件意外截断，导致仓库被错误删除。解决方案包括错误计数器、使用 GitHub 的规范名称作为数据库键，以及在种子损坏时防止批量删除的比例保护。

问题

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.

结果

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

工作流

• 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.

证据

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

来源

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

来源

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

来源