One ON CONFLICT Clause Failed 86.7% of My AI Batches. I Killed the Pipeline.

# One ON CONFLICT Clause Failed 86.7% of My AI Batches. I Killed the Pipeline.

Products: 311,447  |  FAILED rows: 94,107 (86.7%)  |  Descriptions at peak: 55.7%
Pipeline age: ~3 months  |  Date: 2026-10  |  Status: generate endpoints return 410

Last week I deleted every LLM prose generator from my product-aggregation backend. Description writer, category guides, comparisons, meta tags, the auto-scheduler, the gap analyzer — gone. The generate endpoints return 410 now. It ran unattended for about three months, and the state of its queue when I finally looked:

FAILED     94,107   (86.7%)
PENDING    11,686
PROCESSED   2,697

The root cause was one ON CONFLICT clause pointing at the wrong unique constraint. That bug is worth knowing. The decision that followed is worth more.

What the pipeline did

One Node worker pulling products from 14 vendor feeds — 311k products — and calling an LLM through a shared OpenRouter key to write descriptions, guides, comparisons, and meta tags. Consumer sites read the output over an API. Nobody watches the terminal; the health signal is the dashboard and a status count.

Failure 1: two unique constraints, one conflict target

The tags table has two unique indexes. Postgres names them honestly:

tags_name_key    -- UNIQUE(name)
tags_slug_key    -- UNIQUE(slug)

The insert path for on-the-fly tags handled exactly one of them:

await db.query(
  `INSERT INTO tags (name, slug) VALUES ($1, $2)
   ON CONFLICT (name) DO NOTHING`,   // does NOT catch a slug collision
  [tag.name, slugify(tag.name)]
)

"iPhone 6+" and "iPhone 6 Plus" are different names with the same slug. The first insert lands. The second one has a novel name — so ON CONFLICT (name) never fires — and throws 23505 on tags_slug_key. A duplicate-check written against the wrong key is worse than none: it passes tests, because your fixtures never contain a slug homonym.

Failure 2: the batch is the transaction

The worker wrapped 10 products per batch in a single try/catch. Any throw — including the one above — reset all 10 rows to PENDING with retry_count++. Three strikes and a row goes to FAILED, which the batch retriever never selects again (WHERE status='PENDING' AND retry_count < 3).

Two properties made this lethal:

• Collateral damage. One poison tag reset nine healthy products that had already been paid for. 97% of the 94k FAILED rows trace to this loop, accumulating 3k–12k rows per day.
• Zero forensics. The table has no error column. 94,107 rows sat in a terminal state with nothing recorded about why. The only way I diagnosed it was the worker's stdout log — Key (slug)=(iphone-6-plus) already exists → Batch failed, resetting records — at 23:03 on a Tuesday. From the database alone, it was undiagnosable.

Failure 3: the zombie leak

The recovery sweep that reclaims stale PROCESSING rows increments retry_count unconditionally on the way back to PENDING. So a row stranded at retry_count = 2 comes back as PENDING with retry_count = 3 — and 3 < 3 is false forever. Nothing ever re-marks it FAILED; the invariant "PENDING means reachable" is enforced nowhere.

I found 2,266 of these zombies. Seven weeks later, 1,401 were still there, because a second feeder existed: an EAN fast path that committed its write without marking the row PROCESSED, so rows cycled through paid LLM calls up to three times before falling into the same hole. The worker logged processedCount: 0 every five minutes while the dashboard showed a full queue.

Failure 4: pay twice

Every reset happened after the LLM call. Batch fails on the DB write → rows return to the queue → the next round pays for generation again. Multiply by a shared key that hit its monthly ceiling mid-August: the quota breaker correctly paused raw imports, but the description queue kept hammering the dead key with 403s regardless. A retry loop that bills on entry and validates on exit is a metering bug, not just a correctness bug.

Why I didn't fix it

Every failure above is a one-day fix. Isolate writes per product. Sweep PENDING AND retry_count >= max into FAILED. Persist the error. Look up tags by slug before insert. That was the trap — a seductive engineering afternoon waiting to happen.

The product was the problem. Central prose generation for consumer sites is inventory nobody ordered:

• Every consumer site needs its own tone, structure, and internal linking; one shared description serves none of them well.
• The same description served to multiple properties is a duplicate-content risk, not a feature.
• The target list was 286k products. The pipeline's best day ever was 159k descriptions — 55.7% — and it never moved again.

The split that matters is data versus prose. Facts — normalized attributes, specifications, entity FAQs — are objective: compute once, amortize across every consumer, store forever. Prose is per-site voice. I let one worker, one queue, and one budget own both, so every prose failure put the facts pipeline at risk too.

What survived

The queue table stays, frozen behind a kill flag. If a consumer site wants prose, it generates its own, in its own stack, against its own budget — where a failed batch costs one site, not the whole catalog.

What I'd change

1. Per-product write isolation and an error column, from day one. The two non-negotiables for any unattended write loop. A batch that can fail together will.
2. Split the contract: enrichment is data, prose is editorial. Different consumers, different budgets, different failure tolerances. One queue for both means the cheapest component's bug starves the most valuable one.
3. Check state-machine invariants, not status counts. My dashboard said "pending work" while 94k rows sat unreachable in a terminal state. SELECT count(*) GROUP BY status is not observability.