How I fixed the slug collision that silenced aiappdex.com for 36 hours

The nightly HuggingFace ETL for aiappdex.com ran successfully on July 20, then crashed on July 21 — and again on July 22. The runs went red and the failure alert fired; the site itself just kept serving stale model listings while the refresh stayed broken. I didn't get to the logs until the second failure.

Reading the logs, I found the bug: a slug collision the upsert loop had no handling for.

What the outage looked like from the outside

The ETL workflow runs on a nightly cron, fetches up to 2,000 HuggingFace models by downloads (ETL_LIMIT: "2000" in the workflow), and upserts them into a Turso libSQL database. Astro builds the directory pages statically at deploy time, so a failed upsert doesn't immediately produce 500 errors — it produces pages that never update. The site keeps serving whatever was last built. Models launched after the last successful refresh simply don't appear.

The workflow itself failed loudly: the UNIQUE violation crashed the Node process with exit code 1, the run went red, and the Discord failure alert fired. There was no error handling around the upsert loop at all — no try/catch, no retry — so the first collision terminated the batch mid-loop, and every model after it in the sorted list went unstored.

What made the outage last two nights wasn't silence — it was me not reading the logs until the second failure. The boring part of every postmortem is the response gap.

The bug: slugify() collapses distinct HuggingFace model IDs

The slugify function I wrote reduces model IDs to URL-safe strings:

export function slugify(id: string): string {
  return id
    .toLowerCase()
    .replace(/[^a-z0-9/-]/g, "-")
    .replace(/\//g, "-")
    .replace(/-+/g, "-")
    .replace(/^-|-$/g, "")
    .slice(0, 100);
}

Punctuation collapse means org/model-v1.0 and org/model_v1_0 both become org-model-v1-0. These are two different HuggingFace model IDs with different id primary keys in the models table. But after slugification they resolve to the same string, and slug has a separate UNIQUE constraint.

The INSERT ... ON CONFLICT(id) DO UPDATE clause handles the common rerun case cleanly: the same model ID appears in the nightly batch again, and the row gets refreshed in place. I'd written earlier about where ON CONFLICT DO NOTHING beats DO UPDATE in ETL pipelines — but neither pattern covers the cross-ID slug collision case, because the collision is on slug, not id. The ON CONFLICT(id) clause isn't watching the slug column at all.

What changed around July 21: two HuggingFace model IDs that slugify identically appeared in the same nightly batch for the first time. The first model upserted correctly. The second threw a UNIQUE violation — and there was no handler for it anywhere in the loop, so the run crashed.

The trap I dodged: @libsql/client changed its error shape between 0.14 and 0.17

To be clear about what happened: before this outage, the ETL had no UNIQUE-violation check at all — the fix commit is where isUniqueViolation() first appeared. But while writing that fix, I found a trap I would otherwise have walked straight into.

The naive check I would have written first:

function isUniqueViolation(err: unknown): boolean {
  if (typeof err !== "object" || err === null) return false;
  const e = err as { code?: string };
  return e.code === "SQLITE_CONSTRAINT_UNIQUE";
}

In @libsql/client 0.14 — the version this repo pins and has run in production the whole time — that works. The code field on a constraint error is "SQLITE_CONSTRAINT_UNIQUE".

Between 0.14 and 0.17, the shape changed. The code field became "SQLITE_CONSTRAINT" — the broader category. The specific constraint type moved to a new field: extendedCode: "SQLITE_CONSTRAINT_UNIQUE". A check reading e.code === "SQLITE_CONSTRAINT_UNIQUE" would silently return false for every constraint violation after such an upgrade — the collision handling would stop handling anything, and the failure mode would look exactly like the outage I'd just debugged.

My lockfile pins @libsql/client at 0.14.0 and CI installs with --frozen-lockfile, so versions don't drift on their own. But a future dependency bump would be exactly the kind of change that slips through: the shape change wasn't flagged as breaking in the release notes — it showed up in a GitHub issue, not the CHANGELOG. The credential-isolation setup I'd done earlier gives me confidence that auth isn't the issue when the ETL fails, but it doesn't protect against API surface changes in the client library itself.

The fix: stable rawCode detection and hash-suffix fallback

For detection, I added rawCode: 2067 as a cross-version anchor:

function isUniqueViolation(err: unknown): boolean {
  if (typeof err !== "object" || err === null) return false;
  const e = err as { code?: string; extendedCode?: string; rawCode?: number };
  return (
    e.code === "SQLITE_CONSTRAINT_UNIQUE" ||
    e.extendedCode === "SQLITE_CONSTRAINT_UNIQUE" ||
    e.rawCode === 2067
  );
}

SQLite's extended result code for UNIQUE constraint violations is 2067 — that's SQLITE_CONSTRAINT_UNIQUE in the SQLite extended result codes spec. It's part of the SQLite specification, not a libsql convention, and it hasn't changed since SQLite 3.7.16. The string checks stay in the function for readability and work in any version where the strings are correct. The rawCode === 2067 check is the thing that actually holds across client library updates.

For the slug collision itself, I added a deterministic hash-suffix fallback:

export function collisionSlug(id: string): string {
  const hash = createHash("sha1").update(id).digest("hex").slice(0, 6);
  return `${slugify(id).slice(0, 93)}-${hash}`;
}

And the upsert now retries once before giving up:

try {
  await upsert(slugify(id));
} catch (err) {
  if (!isUniqueViolation(err)) throw err;
  // Slug is taken by a DIFFERENT model. Retry with hash suffix.
  try {
    await upsert(collisionSlug(id));
    console.warn(`[etl] slug collision for ${id} — stored as ${collisionSlug(id)}`);
  } catch (err2) {
    if (!isUniqueViolation(err2)) throw err2;
    console.warn(`[etl] skipping ${id}: slug collision even after hash suffix`);
    continue;
  }
}

First attempt uses the plain slug. If that hits a UNIQUE violation on slug — meaning a different model ID already claims that slug — we retry with the 6-character hex suffix appended. If even that collides (6 hex chars = ~16 million values; against a directory with around 1,600 slugs this is near-impossible), we log and skip rather than crashing the entire run.

SHA-1 is fine here. I'm not using it for security — I'm using it for determinism and brevity. The same model ID produces the same 6-character suffix across every nightly run, so the URL doesn't change between refreshes. The cryptographic weakness of SHA-1 against adversarial preimage attacks is irrelevant when the input space is HuggingFace model ID strings.

Why ON CONFLICT(id) DO UPDATE wasn't enough

When I designed the upsert, I assumed ON CONFLICT(id) covered the conflict surface. It does cover the most common case: the same model appears in the nightly batch on consecutive nights, and the row gets refreshed. This works correctly and I've had no issues with it.

What it doesn't cover is two different IDs competing for the same slug value. They have different primary keys, so ON CONFLICT(id) never fires. The slug TEXT NOT NULL UNIQUE constraint is a separate guard, and there's no ON CONFLICT(slug) clause — I hadn't thought to write one.

I briefly considered adding ON CONFLICT(slug) DO NOTHING to the insert. I decided against it: DO NOTHING silently drops the second model with no record of what happened. The exception-first retry approach at least produces a warning log and stores the model under a differentiated slug. Looking at my ETL health queries confirmed that a few collision-slug entries would be visible and auditable, rather than invisible in a silent skip.

The trade-off: the hash-suffix slug for the colliding model differs from what slugify(id) would produce. If that model later becomes more popular and someone has linked to it, the URL is stable as long as the hash is deterministic — which it is. If the first claimant of the slug later drops out of the fetched batch and its row is cleaned up, the collision model retains its hash-suffixed URL permanently. That's an edge case I'm comfortable living with.

What I'd do differently

Three things, in priority order:

Route console.warn to a notification sink. Slug collisions now log to stdout, which GitHub Actions captures. But I only see it when I go looking. The pipeline health watchdog I built intercepts certain ETL failure signals and opens a GitHub Issue — I should extend it to treat [etl] slug collision warnings as notification-worthy. A collision isn't a crisis with the fix in place, but I want to know within hours, not when the directory goes dark.

Write a version-pinned test for isUniqueViolation(). The test doesn't need a live database. It needs a mock error object shaped like what @libsql/client@0.14 throws and another shaped like what 0.17 throws, and it should assert that isUniqueViolation() returns true for both. The lockfile pin and --frozen-lockfile in CI mean there's no drift today, but that test is what makes a future upgrade past 0.14 safe: a version bump that changes error shape is not behavior I want to merge without a test run that exercises the error path.

Consider a pre-flight slug lookup before insert. Before inserting a model, I could query SELECT id FROM models WHERE slug = ? and check whether that slug is taken by a different ID. If it is, I skip straight to collisionSlug() without going through the exception path. The downside is two DB round-trips per model instead of the common case of one — up to 2,000 extra queries in a full 2,000-model batch, which is a real cost against Turso's edge-deployed replicas even with low per-query latency. But the exception-first pattern is still correct for the no-collision case, which is 99% of runs — pre-flight optimizes for the rare case at the cost of extra queries in every run. I'll revisit if the collision rate climbs.

What actually failed, and what only might have

One bug caused this outage, not two. The slug collision hit an upsert loop that had no error handling of any kind — no isUniqueViolation(), no try/catch, no retry. Those all arrived in the fix commit. The first UNIQUE violation crashed the process, and everything after the colliding model in that batch went unstored, two nights running.

The error-shape difference between @libsql/client 0.14 and 0.17 is a bug I avoided rather than one I hit: production has only ever run 0.14. But if I'd shipped the naive e.code check and later bumped the client, the handler would have silently stopped recognizing UNIQUE violations, and the failure mode would have looked exactly like this outage all over again. Writing the fix with all three checks up front closes that door before it opens.

The pattern I'd flag in code review is the one I originally missed: a UNIQUE constraint on a derived column (slug) that the upsert's ON CONFLICT(id) clause wasn't watching. The conflict surface of an insert is every unique constraint on the table, not just the one you wrote a clause for. At least this failure was loud — exit code 1, red run, alert fired. The four GitHub Actions ETL patterns I use surfaced it; the slow part was me getting to the logs.

---

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