Four libSQL queries I use to catch ETL gaps in my AI model directory

My AI model directory stores 1,449 entries across two tables: models for HuggingFace metadata and model_content for the generated prose — today all of it template-assembled or metadata-derived, with an LLM tier the pipeline can use but currently doesn't. One clarification before the SQL, because I've been loose about this in earlier posts: the database is libSQL, but it is not Turso in production. The shared client reads TURSO_DATABASE_URL when it's set and falls back to file:./data/local.db, and nothing in the refresh workflow sets it — so this runs against a local SQLite file today, with Turso as the migration target. The queries below are plain SQLite and work either way. The ETL runs on a GitHub Actions cron and is silent on success. Without diagnostic queries, gaps accumulate invisibly — entries with no generated content, stale prose from models that have changed, boilerplate clustering that triggers the lint:humanization --strict check.

These four queries are the ones I run when something looks off, and every so often on purpose to catch drift before it matters.

1. Find models with no generated content

SELECT m.id, m.slug, m.pipeline_tag, m.downloads
FROM models m
LEFT JOIN model_content c ON c.model_id = m.id
WHERE c.model_id IS NULL
ORDER BY m.downloads DESC
LIMIT 50;

The LEFT JOIN combined with WHERE c.model_id IS NULL returns every row in models that has no corresponding row in model_content. Those entries render as bare stubs on the site — model name, download count, tags, but no summary or pros/cons.

Sorting by downloads DESC surfaces the gap entries that matter most. A missing entry for a model with 2 million downloads is more urgent than one with 300. After the three-tier content quality ladder runs, I expect this list to shrink by roughly 100 entries per day. If it's not shrinking, the upgrade step in the ETL is silently failing.

2. Find content that's gone stale

SELECT m.slug, m.name, c.generated_at, c.model_used,
       julianday('now') - julianday(c.generated_at) AS days_old
FROM models m
JOIN model_content c ON c.model_id = m.id
WHERE days_old > 30
  AND c.model_used NOT LIKE 'metadata-derived%'
ORDER BY days_old DESC
LIMIT 30;

The julianday arithmetic is SQLite-native and works in libSQL without modification. I exclude metadata-derived entries because those are rebuilt deterministically from HuggingFace metadata, not from an LLM run — their "age" doesn't indicate drift in the same way.

The 30-day threshold isn't about content expiry in any absolute sense. It's about model churn on HuggingFace: popular models release new versions frequently, and a summary written for qwen2-7b-instruct six weeks ago may not reflect current quantization formats or license updates. When this query returns more than 50 entries, I queue a partial regen run targeting the oldest content for the highest-download models first.

Knowing model_used per row also matters here. If the stale rows all carry the same value — every remaining claude-routine-polish row, say — that flags a generation epoch boundary: they were written before a template revision and may still repeat the old wording.

3. Detect boilerplate clustering before it accumulates

SELECT c.summary, COUNT(*) AS occurrences
FROM model_content c
WHERE c.model_used NOT LIKE 'metadata-derived%'
GROUP BY c.summary
HAVING COUNT(*) > 3
ORDER BY occurrences DESC
LIMIT 20;

This is the shape of query that would have caught the 467-instance repetition earlier if I'd run it regularly. As written it groups on exact summary text and returns any string that appears more than three times across the directory — the 467 repeats were in the pros field, so the variant described below is the one that would actually have caught that cluster.

A count above 3 for any non-trivial sentence is a signal that the generator is producing clustering. The HAVING COUNT(*) > 3 threshold is loose by design — some shared language is fine — but anything above 10 almost certainly needs to be split with the humanize-aiappdex.mjs pass or flagged for a template revision.

You can run the same query against the pros and cons fields by replacing c.summary. In practice I run all three and compare the worst-offender counts. The pros field tends to cluster most aggressively because it isn't varied per entry the way summaries are: polish.py picks a summary out of a pool using a hash of the slug, but builds pros by appending fixed sentences keyed on shared attributes (license, download volume, language coverage, framework tags) — including one line about loading via the transformers pipeline that it appends unconditionally to every model.

4. Check pipeline coverage by generation tier

SELECT 
  CASE 
    WHEN c.model_used IS NULL THEN 'no-content'
    WHEN c.model_used LIKE 'metadata-derived%' THEN 'metadata-derived'
    WHEN c.model_used LIKE 'fallback%' THEN 'fallback-template'
    WHEN c.model_used = 'claude-routine-polish' THEN 'routine-polish'
    ELSE 'llm-generated'
  END AS tier,
  COUNT(*) AS count,
  ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM models), 1) AS pct
FROM models m
LEFT JOIN model_content c ON c.model_id = m.id
GROUP BY tier
ORDER BY count DESC;

This returns a coverage breakdown across the content states the directory can be in. Today's run:

tier	count	pct
routine-polish	1198	82.7%
metadata-derived	251	17.3%
fallback-template	0	0.0%
no-content	0	0.0%

Note the explicit claude-routine-polish branch. Every one of those 1,198 rows was written by scripts/polish.py, which assembles prose from fixed template pools with no API call — the name comes from the weekly Claude Code routine that invokes the script, not from a model writing the text. Without that branch the catch-all ELSE 'llm-generated' would file all of them under a tier that currently has zero rows in the directory, which is the kind of mislabelled dashboard number that survives for months.

The empty tiers are the point of running this. fallback-template is at zero only because the humanization pass just rewrote the remaining 251 templated rows into the metadata-derived tier — that's exactly where the 251 comes from. It won't stay at zero: newly fetched HuggingFace models land as fallback-template every day until either the deterministic hash pool upgrade or the polish run picks them up. no-content is zero because every row in models currently has a matching model_content row. The numbers shift as the daily upgrade cron runs; metadata-derived should stay put unless the humanization script runs again or the lint check finds new patterns.

If no-content starts growing, a fetch step in the ETL is writing to models without triggering the content generation step. If fallback-template stops shrinking, the upgrade queue has stalled.

How I actually run them (and what isn't automated yet)

Today these are hand-run. I open a scratch Node script that talks to the same @libsql/client the ETL uses, point it at the local database file, and read the numbers myself:

import { createClient } from "@libsql/client";

const db = createClient({
  url: process.env.TURSO_DATABASE_URL ?? "file:./data/local.db",
  authToken: process.env.TURSO_AUTH_TOKEN,
});

const missing = await db.execute(`
  SELECT COUNT(*) AS n FROM models m
  LEFT JOIN model_content c ON c.model_id = m.id
  WHERE c.model_id IS NULL
`);
console.log(`Missing content: ${missing.rows[0].n}`);

There is no CI gate on these numbers yet, and I'd rather say so than imply a safety net I don't have. The scheduled watchdog I do run — pipeline-health.yml, on a 30 23 * * * UTC cron — looks at GitHub Actions run history, not at rows: it opens a deduplicated issue when a content workflow fails or when nothing has published for a day and a half. A silent gap of a few hundred content-less rows would sail straight past it.

Wiring one of these queries into that job is the obvious next step: run the missing-content count after the daily refresh and fail the step above some loose threshold, high enough not to fire on the handful of entries legitimately in flight on a normal day. That's on the list, not in the repo.

---

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