How I built a three-tier content quality ladder for programmatic directory ETL

The three directory sites I launched in April — Top AI Tools, Find Games Like, and Open Alternative To — all build editorial content the same way: fetch metadata from an external API, optionally send it through Claude Haiku 4.5, write the result to libSQL (Turso when TURSO_DATABASE_URL is set, a local file:./data/local.db when it isn't), export to JSON. The "optionally" is the whole point of this article. My nightly refresh workflow deliberately doesn't set ANTHROPIC_API_KEY — API spend on these sites is $0 — so every scheduled run takes the no-key path, and the editorial upgrade happens in a separate weekly pass. What matters for a programmatic site at scale is exactly that: what happens when Claude can't run.

The answer is a content quality ladder, tracked by a single model_used column.

The three tiers (and the fourth value)

Every content table across all three sites has a model_used column. The ladder I designed has three rungs, but nothing constrains the column at the schema level — it's whatever string the writer put there — and a fourth value ended up in the data:

Value	Origin	Quality
seeded-from-json	Loaded from a curated JSON file at bootstrap with no model_used of its own	Minimal — structured but thin
fallback-template	Claude unavailable or API key absent	Acceptable — technically correct, not editorial
claude-routine-polish	Weekly scripts/polish.py pass over the exported JSON	Better — deterministic templates built from each entry's real fields
claude-haiku-4-5	Generated by Claude Haiku 4.5	Target — editorial summaries, named examples, nuanced caveats

The honest state of the ladder today: of the 1,137 AI-tools rows in the repo, 800 are claude-routine-polish and 337 are fallback-template; the game and OSS data sets are entirely claude-routine-polish. Not one shipped row says claude-haiku-4-5, because the key that would produce it isn't in the nightly job. The Haiku path described in the rest of this article is real code that runs whenever a key is present — it just isn't what filled the content currently on my live pages.

Seeded content exists because each site ships with a JSON file of curated entries. Those entries have names, descriptions, and metadata from their upstream source (HuggingFace, Steam, GitHub), but no editorial layer yet. The page renders — but it reads like a database dump, not a directory.

Fallback-template content is what you get when the API key isn't present or when a Claude call fails. For the AI tools site, the fallback for a model named qwen2-7b in the text-generation pipeline looks like this:

qwen2-7b is an open-source text-generation model available on HuggingFace.
Details are sourced from the public model registry.

That's not wrong. It just doesn't help anyone decide whether to use the model.

Claude Haiku content is the target state. A good generation for the same model says something like: "Qwen2-7B is a 7-billion parameter instruction-tuned model from Alibaba Cloud optimized for multilingual generation, showing strong performance on Chinese and English benchmarks while fitting in 16GB of VRAM." The difference is editorial voice and specificity — neither of which template-filling can produce.

The upgrade query

The ETL generation step doesn't blindly regenerate everything on each run. It targets only entries that need work:

SELECT m.id, m.name, m.pipeline_tag, m.tags
FROM models m
LEFT JOIN model_content c ON c.model_id = m.id
WHERE c.model_id IS NULL
   OR c.model_used IN ('fallback-template', 'seeded-from-json')
ORDER BY m.downloads DESC
LIMIT ?

Three things happen simultaneously here:

1. LEFT JOIN ... WHERE c.model_id IS NULL catches brand-new entries added by the nightly fetch that have no content row yet.
2. OR c.model_used IN ('fallback-template', 'seeded-from-json') catches existing rows that were written with lower-quality content.
3. ORDER BY m.downloads DESC means when the LIMIT is hit, the most-downloaded (most-visited) entries are upgraded first.

The same pattern appears in all three sites with different table names: models/model_content for AI tools, games/game_content for indie games, saas/saas_content for OSS alternatives. Not quite identical, though — only the AI tools query has the ORDER BY. The games and OSS queries take whatever rows the LIMIT happens to return, which is a real (if quiet) difference in which entries get upgraded first, and one I only noticed writing this. The abstraction was a late realization — I wrote it three times before noticing it was the same thing. A shared buildUpgradeQuery(tableName, pkField, contentTable) helper would have been the right call from the start.

The fallback chain

Inside the generation loop, every entry goes through the same decision tree:

const hasApiKey = !!process.env.ANTHROPIC_API_KEY;

if (hasApiKey) {
  try {
    const result = await generate({
      systemPrompt: SYSTEM_PROMPT,
      userPrompt,
      cacheSystem: true,
      maxTokens: 1024,
    });
    content = parseOrFallback(result.text, fb);
    modelUsed = "claude-haiku-4-5";
    generated++;
  } catch (err) {
    console.error(`! Claude error for ${id}:`, err instanceof Error ? err.message : err);
    content = fb;
    fallback++;
  }
} else {
  content = fb;
  fallback++;
}

The cacheSystem: true flag marks the system prompt block with cache_control: { type: "ephemeral" }. All three sites have fixed system prompts — the same AI tools instruction across every model generation, the same game critic instruction across every game — so the first call in a batch primes the cache and the remaining ~99 calls read it at the reduced input rate. I covered the mechanics in the article on the shared Haiku client. With a ~900-token system prompt and a 100-entry batch, that's roughly 90,000 input tokens moved from the full input rate to the cache-read rate — on runs where the key is actually set. On the scheduled runs it saves exactly nothing, because no call is made at all. Anthropic's prompt caching documentation has the exact pricing for cache creation vs cache read tokens.

The error path is deliberately non-throwing. Any Claude failure — rate limit, network timeout, malformed response — drops through to content = fb and increments fallback. The run continues. If 10 of 100 Claude calls fail due to transient rate limits, 90 get written with claude-haiku-4-5 and the 10 failures get fallback-template. Those 10 rows surface in the next night's upgrade query automatically.

The upsert write

Every content row is written with INSERT ... ON CONFLICT ... DO UPDATE SET:

INSERT INTO game_content
  (appid, summary, similar_games, good_for, avoid_if, generated_at, model_used)
VALUES (?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(appid) DO UPDATE SET
  summary = excluded.summary,
  similar_games = excluded.similar_games,
  good_for = excluded.good_for,
  avoid_if = excluded.avoid_if,
  generated_at = excluded.generated_at,
  model_used = excluded.model_used

The upsert makes the ETL fully idempotent: running it twice produces the same state as running it once. More importantly, it means the model_used column gets overwritten when an upgrade succeeds. A row that was fallback-template becomes claude-haiku-4-5 in-place, without any explicit "mark upgraded" step. The column just reflects what actually produced the current content.

The compare-page ETL uses a different pattern: check-before-insert with an explicit SELECT 1 to skip already-generated pairs. Both patterns are valid. Check-before-insert is better when reprocessing is expensive (large Claude calls, multi-step generation). Upsert-overwrite is better when you always want the latest generation to win regardless of what was there before.

The noindex safety valve

One consequence of shipping a three-tier system is that some pages launch with genuinely thin content. For the indie games site, the threshold is explicit in the game page component:

const noindex =
  game.good_for.length === 0 &&
  game.avoid_if.length === 0 &&
  game.similar_games.length === 0;

If a game entry has no good_for audience signals, no avoid_if caveats, and no similar game suggestions — which happens when the content row is missing entirely, not just fallback-template — the page gets noindex in its robots meta. The page renders fine for direct visitors; it just isn't submitted to Search Console until content exists.

In practice, the fallback templates do populate good_for and avoid_if with generic strings like "Indie game enthusiasts" and "You prefer AAA production values," so most fallback-template entries still pass the noindex check. (That was the state at publication. Weeks later I removed those fixed strings: on 2026-06-11 the fallback started returning empty lists for all three fields, and on 2026-06-25 good_for was rewired to derive from the game's own genres while similar_games and avoid_if stay empty.) The valve fires mainly on completely-missing rows, which are brief windows between when the fetch ETL adds a new game and when the generation ETL runs next.

The export step

After generation, a separate export.ts script dumps the content tables to static JSON files that Astro reads at build time. This is the architectural detail that makes the quality ladder safe to run asynchronously.

If the Anthropic API is down for an entire nightly run, the export runs with whatever's in the DB, the Astro build succeeds with existing content, and the deployed site doesn't have zero-content pages. The upgrade queue just has a larger backlog the following night.

The static SSG approach I'm running across all three sites is partly justified by this property. Dynamic rendering from a live DB would mean a Claude outage or Turso blip directly impacts page load time for real users. The ETL → export → build pipeline adds ~24 hours of content staleness in exchange for availability that doesn't depend on the API being up at request time. For a directory site where model descriptions change rarely, that tradeoff is easy to accept.

What I'd do differently

The generation loop is strictly sequential. One call, await, write to DB, next entry. For 100 entries at roughly 1–1.5 seconds per call that's about 2 minutes per run — fine for the current scale.

At 1,000 entries it would be 20+ minutes, which starts blocking the rest of the GitHub Actions job. The fix is a semaphore-bounded batch:

import PQueue from "p-queue";
const queue = new PQueue({ concurrency: 5 });

const tasks = pending.rows.map((row) =>
  queue.add(() => generateAndWrite(row))
);
await Promise.all(tasks);

Five concurrent workers would bring a 1,000-entry run down to under 5 minutes without risking the Anthropic rate limit. I've kept the sequential version because it's simpler to debug, and because with no API key in the nightly job each iteration is just a template fill and a DB write — fast enough that the loop has never been the bottleneck. That's luck, not planning: the AI tools site is already past 1,100 entries and GENERATE_LIMIT is set to 1000 in the workflow, so the day I put a key back in that job, the sequential loop turns into the 20-plus-minute step the 1–1.5s-per-call estimate above implies. That number is an extrapolation, not a measurement — I've never timed a keyed run at that size — but either way the queue is overdue rather than upcoming.

I also wish I'd started with better fallback copy. The initial seed templates are technically correct but thin, and some of that thin content shipped live to indexable pages before the ETL had a chance to upgrade it. A cleaner v1 strategy: run the full ETL before the first Astro build so every page that ships has at least a real Claude generation. The seeded-from-json tier exists because I moved too fast at launch; it's not architecturally necessary.

FAQ

Can I run the ETL without an API key during local development?

Yes. The hasApiKey check means every generation falls through to fallback-template. All DB writes still happen, the export still runs, and the Astro build succeeds. Once you add a real key, the next ETL run upgrades all fallback-template rows automatically without any manual intervention.

How do I check the current upgrade ratio?

SELECT model_used, COUNT(*) as cnt
FROM game_content
GROUP BY model_used;

A healthy site a week after launch should have mostly upgraded rows — claude-haiku-4-5 if the key is set, claude-routine-polish in my case — with the fallback-template count trending toward zero. The generated_at timestamp on each row also lets you see how recently content was last upgraded.

What happens when Claude returns malformed JSON?

Each site's parseOrFallback() function extracts the outermost {...} block with a regex before parsing — this handles the common case where Haiku prepends an explanation like "Here is the entry:" before the actual JSON. The array fields are guarded with Array.isArray() and fall back individually, but summary is only parsed.summary ?? fb.summary — a wrong-typed summary would slip straight through, which is a hole I should close. The row gets written either way. And one caveat on the column: if the HTTP call succeeded but the body was unparseable, modelUsed is still set to claude-haiku-4-5 even though parseOrFallback() handed back the template. So model_used records which tier was reached, not always which one filled the text.

Does the cache persist between separate nightly runs?

No. Anthropic's ephemeral cache TTL is 5 minutes. Within a single run of 100 entries, the 99 calls after the first hit the cache. Across runs scheduled hours apart, the cache has expired and the first call re-primes it. The savings are per-batch, not cross-run — still meaningful for batches of 100, but not a persistent cost reduction over time.

Why Turso for this instead of Postgres?

I covered the comparison in detail in the Turso vs Cloudflare D1 article. The short version for this use case: @libsql/client works identically in Node.js ETL scripts and at Astro serverless/edge, with no separate driver or connection-pooling setup for each environment. For a project where the same getClient() call needs to work in GitHub Actions jobs and Vercel edge functions, that's the practical reason to use it.

---

Related: How I built a shared Claude Haiku client with system-prompt caching | How I built pairwise AI model compare pages

Part of an ongoing 6-month experiment running three AI-curated directory sites. The technical claims here are real; this article was AI-assisted.