MORINAGA

Posted on Jun 26 Edited on Aug 27

How I built the OSS alternatives directory: GitHub ETL, Turso, and the UPSERT trap I hit

#opensource #typescript #turso #claude

When I launched three programmatic directory sites in April 2026, the open-source alternatives site had the most interesting data model. The AI tools directory indexes HuggingFace models — that's a pull from one API. The indie games directory reads Steam. But the OSS alternatives site has to answer a different question: for this SaaS product, which open-source repos actually cover the same use case, and how do they compare?

Getting that right required a two-phase ETL approach, a careful UPSERT strategy I initially got wrong, and some deliberate choices about where to use Claude Haiku and where to use a fallback template.

What the data model looks like

Three tables in libSQL — the client is Turso-ready, but no hosted Turso database is wired up: the refresh workflow passes no TURSO_DATABASE_URL, so every run opens the local file:./data/local.db fallback:

saas — the SaaS tool being replaced (Datadog, Notion, Figma, etc.)
alternatives — GitHub repos that serve the same use case, linked by saas_slug
saas_content — Claude-generated per-entry text: an intro, comparison notes, and migration tips

The alternatives table stores everything the GitHub API returns that matters for a directory: stars, forks, language, license, last_pushed, description. The saas_content table stores only what Claude adds — the editorial layer that turns raw repo metadata into something useful.

The full export lives in a JSON file that Astro reads at build time. No database connection at build. The ETL pipeline and the Astro build are separate processes.

Phase 1: seeding from JSON

The first time the site runs on a new machine, there's no database. Rather than block a local build on a live GitHub API pass, I wrote a seed.ts script that bootstraps the database from src/data/saas.json — the export the previous run committed, so it already carries stars, forks, license, last_pushed, URLs and generated content.

There's a second, smaller JSON next to it: the hand-curated src/data/seed-saas.json, which is what run.ts reads before the live fetch. That one contains only SaaS name, slug, homepage, category, and a list of owner/repo strings — stars, forks, license and last_pushed are deliberately left out there, because they come from the GitHub pass. What the exported saas.json adds on top is the polished content for entries where the default output was weak.

for (const e of entries) {
  await db.execute({
    sql: `INSERT INTO saas (slug, name, homepage, category, fetched_at)
          VALUES (?, ?, ?, ?, ?)
          ON CONFLICT(slug) DO NOTHING`,
    args: [e.slug, e.name, e.homepage, e.category, now],
  });

  for (const a of e.alternatives) {
    await db.execute({
      sql: `INSERT INTO alternatives (saas_slug, repo, name, description, ...)
            VALUES (?, ?, ?, ?, ...)
            ON CONFLICT(saas_slug, repo) DO NOTHING`,
      args: [e.slug, a.repo, a.name, a.description, ...],
    });
  }
}

DO NOTHING on conflict for alternatives is correct: once GitHub data is live, the seed shouldn't clobber fresh stars counts with the static values from the JSON. But for saas_content, I initially used the same DO NOTHING — and that was a mistake I'll get to below.

Phase 2: live GitHub data

fetch-alternatives.ts calls the GitHub REST API for every owner/repo in the database and upserts the live fields. Unlike the seed, this is DO UPDATE — we want fresh data.

The sleep interval is 100ms between GitHub API calls. For an authenticated token that rate limit is conservative (GitHub's REST API allows 5000 requests per hour for authenticated users, so 100ms is well under the minimum gap needed). Unauthenticated would be 60 per hour, which is 60 seconds per call — completely impractical at scale. The monorepo authenticates with a secret in GitHub Actions.

Errors per-repo are caught and logged but don't abort the batch:

for (const repoFull of s.alternatives) {
  const [owner, name] = repoFull.split("/");
  try {
    const r = await getRepo(owner, name);
    await db.execute({
      sql: `INSERT INTO alternatives (saas_slug, repo, name, description, stars,
              forks, language, license, last_pushed, url, fetched_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(saas_slug, repo) DO UPDATE SET
              description = excluded.description,
              stars = excluded.stars,
              forks = excluded.forks,
              language = excluded.language,
              license = excluded.license,
              last_pushed = excluded.last_pushed,
              fetched_at = excluded.fetched_at`,
      args: [
        s.slug, repoFull, r.name, r.description,
        r.stargazers_count, r.forks_count,
        r.language, r.license?.spdx_id ?? null,
        r.pushed_at, r.html_url, now,
      ],
    });
    await sleep(100);
  } catch (err) {
    console.error(`  ! Failed ${repoFull}:`, err instanceof Error ? err.message : err);
  }
}

One field worth noting: r.license?.spdx_id returns null when GitHub sees a license file but can't identify the SPDX identifier. That happens more than you'd expect with non-standard licenses. I render those rows with "see repo" instead of a badge so I'm not misleading visitors about the license type.

Content generation: the dormant Haiku branch and the fallback that actually runs

After the GitHub data is fresh, generate-content.ts queries for SaaS entries that either have no content row or whose model_used column is 'fallback-template' or 'seeded-from-json'. For each, it asks Claude Haiku for the three fields below — but only when ANTHROPIC_API_KEY is present, which in production it deliberately isn't (more on that in a moment):

intro — 2 sentences on what the SaaS is and why teams seek OSS alternatives
comparison_notes — 2-3 sentences on actual tradeoffs (self-hosting overhead, feature gaps)
migration_tips — a 2-4 item array of concrete migration steps

That branch uses the shared Claude Haiku client with system-prompt caching. The system prompt is identical for every call in a pass, so caching it would save input tokens on all subsequent calls — on a 50-entry run that difference would be real. I say "would" because the branch is currently dormant: .github/workflows/refresh-content.yml intentionally leaves ANTHROPIC_API_KEY unset, so the daily refresh makes zero API calls and costs nothing.

The fallback template — which runs when ANTHROPIC_API_KEY is absent, i.e. every production run — generates deterministic placeholder text. This matters for CI: the Astro build needs a content row for every SaaS entry. Missing content produces a blank page, which would then trigger the noindex gate I use for thin programmatic pages.

The real editorial text doesn't come from that loop at all. A weekly polish routine running on my Claude Code subscription rewrites the entries and stamps them claude-routine-polish — all 80 SaaS entries in the current export carry that marker, not claude-haiku-4-5.

The three-tier content quality ladder I described earlier puts these generated entries at the middle tier — better than the raw repo description, worse than hand-edited content.

The UPSERT trap

Original seed.ts for saas_content:

INSERT INTO saas_content (saas_slug, intro, comparison_notes, migration_tips, generated_at, model_used)
VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT(saas_slug) DO NOTHING

That looked safe. But the problem was subtle. The old seed.ts hardcoded model_used = 'seeded-from-json' for every row, ignoring whatever the JSON said — so polished entries lost their provenance on the way in. Then generate-content.ts queried:

SELECT slug FROM saas s
LEFT JOIN saas_content c ON c.saas_slug = s.slug
WHERE c.saas_slug IS NULL
   OR c.model_used IN ('fallback-template', 'seeded-from-json')

Every seeded row matched that second condition — 'seeded-from-json' is right there in the IN list. So the generator picked up all of them and overwrote them with fallback templates. And because the seed used DO NOTHING, the polished JSON content couldn't get back in on the next run either: the (now fallback) row already existed, so the insert was a no-op. Weekly polish work would report "upgraded N entries" on Sunday and be gone by Monday.

The fix was two parts:

Seed.ts now uses DO UPDATE for saas_content, not DO NOTHING. Polished JSON content always wins.
Seed.ts passes e.model_used ?? 'seeded-from-json' instead of hardcoding the value, so a 'claude-routine-polish' marker survives the round-trip through JSON. The generator's WHERE clause still selects 'seeded-from-json' rows — it's only the polished marker that keeps a row out of the loop.

ON CONFLICT(saas_slug) DO UPDATE SET
  intro = excluded.intro,
  comparison_notes = excluded.comparison_notes,
  migration_tips = excluded.migration_tips,
  generated_at = excluded.generated_at,
  model_used = excluded.model_used

This pattern — using model_used as a status field to coordinate between ETL phases — also showed up in the AI tools directory's fallback entry upgrade work. The lesson there was the same: never let an ETL pass silently overwrite a row because the status field was written inconsistently.

The Astro page structure

Each SaaS entry renders as a static page at /alternatives/[saas]/. The renderer reads from saas.json, assembles a grid of alternatives sorted by stars, and inlines the Claude-generated comparison notes. Each entry shows a license badge, language indicator, and the last_pushed date as a plain YYYY-MM-DD string (Updated {a.last_pushed.slice(0, 10)}) — no relative-time formatting.

The grid intentionally doesn't paginate at the SaaS level. I cap entries per SaaS at 8, though that's a rule I apply by hand while curating seed-saas.json, not something the code enforces — the biggest list today is 7. More than that becomes noise: the directory's value is curation, not exhaustiveness. The E-E-A-T transparency pages don't document that cap; what they do spell out is the curation gate — minimum number of alternatives, a star floor on the top entry, and a minimum intro length before a page is indexed at all.

What I'd change

Store raw GitHub JSON alongside derived columns. Currently each ETL adds derived fields: stars, forks, license, last_pushed. When I later wanted a "has_recent_releases" signal, I had to add a full new API call. If I'd kept the raw response in a JSONB/TEXT column, json_extract(raw, '$.has_wiki') would have been enough.

Add a deprecated_at field. When a repo gets deleted or renamed, the ETL call returns a 404 and the code just logs it. The row stays in the database with increasingly stale data. A deprecated_at timestamp would let the page renderer show a warning and let the content team decide whether to replace or remove the entry.

Parallelize generate-content with a rate-limit counter — if I ever turn the API branch back on. As it runs today the loop is sequential but makes no network calls at all (no API key, deterministic templates), so it isn't the bottleneck. If I did re-enable Haiku for a cold run with 100+ entries, batching ~10 concurrent calls behind a shared counter that throttles at the API limit is the change I'd make.

FAQ

Why libSQL instead of a hosted Postgres?
Mostly because there's no runtime database to host. Astro reads an exported JSON file at build time, and the ETL runs against a local SQLite file inside the GitHub Actions job — I never provisioned a hosted Turso instance, so this tier costs $0. libSQL gets me the same file format and the same client on my laptop and in CI, with a hosted Turso URL as a drop-in if I ever need real remote reads. The full comparison is here.

Do you need a paid GitHub plan to avoid rate limits?
No. A free personal access token gives 5000 requests per hour — enough to fetch metadata for several hundred repos in a single daily cron run. The 60/hr unauthenticated limit would not work at any meaningful scale.

How do you prevent Claude costs from escalating?
The blunt lever is that the daily pipeline runs without an API key, so it costs $0 — the polish work happens separately on a subscription. If I do run the API branch, cacheSystem: true amortises the system prompt across the pass and maxTokens: 1024 caps each response. The model_used status field is the other guard: entries already marked claude-routine-polish don't get regenerated.

What happens if a GitHub repo is deleted?
Right now the row goes stale silently. The fetch fails, the error is logged, and the next build still renders the row with whatever data the last successful fetch stored. Adding a 404-specific handler that sets deprecated_at is on the backlog.

DEV Community