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.

Источник