Every demo LLM extraction pipeline you’ve seen works like this: prompt the model, json.loads() the output, write the row. It works great in the demo because the demo runs twenty documents and the model is having a good day. Then you point it at 400 product listings and it quietly corrupts your database — and in the test run for this article, that’s not a hypothetical. The naive baseline parsed 319 of 400 documents, wrote 39 rows (12.2% of everything it wrote) that violated the schema, and silently lost 81 documents (20.2%) to crashes or null rows. Nobody emailed you about it. The data was just wrong.
This article builds the version you actually ship: a schema-first pipeline with a three-rung repair ladder (repair → targeted re-prompt → quarantine), SQLite checkpointing that survives a crash mid-run, and cost math using live per-token prices pulled from a public API at runtime. The full script ran end-to-end with exit 0 before this article was written — every number below comes from that run.
One disclosure before we start: the “LLM” in the test harness is a deterministic simulator (FakeLLM) that reproduces the failure mix real models produce on extraction tasks — markdown-fenced JSON, truncated outputs, trailing commas, wrong types, invalid enum values, and the nastiest one, hallucinated extra fields that are valid JSON but wrong data. I used a simulator because it makes results reproducible with a fixed seed and costs zero dollars; swapping FakeLLM.complete() for any OpenAI-compatible endpoint is a one-function change, and nothing else in the pipeline touches the model. The failure rates it simulates (~15% malformed, ~10% schema violations, ~3% hallucinated fields) are representative of what real extraction runs hit in practice — and the paper numbers below back that up.
The evidence that this problem is structural, not bad luck
If you think “just add response_format: json” solves this, look at the data. JSONSchemaBench (Geng et al., arXiv:2501.10868, EPFL + Microsoft) benchmarked structured-output generation across ~9,558 real-world JSON schemas — pulled from GitHub, Kubernetes configs, and API specs — against six frameworks including OpenAI and Gemini’s native constrained decoding. Their Table 4 numbers for schema empirical coverage (fraction of schemas the framework could actually enforce):
| Subset | LM only (no constraints) | OpenAI native | Gemini native | Guidance (engine) |
|---|---|---|---|---|
| GitHub-Easy | 0.65 | 0.29 | 0.07 | 0.86 |
| GitHub-Medium | 0.38 | 0.12 | — | 0.69 |
| GitHub-Hard | 0.13 | 0.09 | — | 0.41 |
| Kubernetes | 0.56 | 0.21 | — | 0.91 |
| Washington Post | 0.40 | 0.13 | — | 0.86 |
| JSONSchemaStore | 0.21 | 0.06 | — | 0.30 |
Read the GitHub-Hard row twice: even the best open-source engine only enforced 41% of real-world hard schemas, and the API-native modes covered under 10%. The paper’s conclusion: constrained decoding “presents a significant challenge” even for proprietary APIs, and coverage collapses as schema complexity rises. And coverage isn’t compliance — OpenAI showed 1.00 compliance on subsets it attempted (when it emits a constrained output, the output obeys the schema), but it only attempted a small fraction of schemas. That’s the trap: your API’s “guaranteed JSON” mode is a guarantee about the wrong thing. It guarantees the output is syntactically valid JSON — it does not guarantee the JSON matches your business schema, and it definitely doesn’t guarantee the values are sane.
So you need your own validation layer, and it needs to sit at three levels:
- Parse — is this even JSON? (fences, truncation, prose wrappers)
- Validation — does it match the schema? (types, ranges, enums)
- Semantics — is it sane? (a
$9,999.99price for a widget is schema-valid nonsense)
Constrained decoding only helps with rung 2, partially, on simple schemas. Rungs 1 and 3 are always yours.
The contract: one Pydantic model is the single source of truth
The whole pipeline hangs off one model definition. The two settings that matter:
from decimal import Decimal
from enum import Enum
from pydantic import BaseModel, ConfigDict, Field
CURRENCIES = ("USD", "EUR", "GBP")
class Product(BaseModel):
# extra="forbid": hallucinated fields FAIL validation instead of
# silently passing through into your database.
model_config = ConfigDict(extra="forbid")
title: str = Field(min_length=3, max_length=200)
price: Decimal = Field(gt=0, decimal_places=2)
currency: str = Field(pattern="^(USD|EUR|GBP)$")
stock: int = Field(ge=0, le=1_000_000)
seller_rating: float = Field(ge=0.0, le=5.0)
class FailureType(str, Enum):
PARSE = "parse" # not JSON at all (or not after repair)
VALIDATION = "validation" # JSON, but violates the schema
SEMANTIC = "semantic" # schema-valid, business-logic nonsense
class Quarantine(Exception):
def __init__(self, ftype: FailureType, detail: str):
self.ftype, self.detail = ftype, detail
super().__init__(f"{ftype.value}: {detail}")
extra="forbid" is the default people miss. Pydantic’s default ignores unknown fields — which means when the model hallucinates a discount_code: "SAVE20" that doesn’t exist in your source document, it sails through into your database. Forbid makes that a hard validation error, and hard validation errors are signal — they tell you the model drifted, which is exactly when you want a re-prompt instead of a write.
Use Decimal for money, always. float prices produce 19.990000000000002 eventually, and if that row ever feeds an invoice you’ve got a real problem.
Rung 1: repair locally before you spend another API call
The majority of “bad” LLM output isn’t semantic garbage — it’s formatting. Markdown fences around the JSON, trailing commas, prose before the object, truncation at the token limit. This is all mechanically fixable, locally, for free. The json-repair library handles most of it:
import json_repair
FENCE_RE = re.compile(r"```(?:json)?\s*(.*?)```", re.S)
OBJ_RE = re.compile(r"\{.*\}", re.S)
def extract_json_candidate(text: str) -> str:
m = FENCE_RE.search(text) or OBJ_RE.search(text)
if not m:
raise Quarantine(FailureType.PARSE, "no JSON object in response")
return m.group(1) if m.lastindex else m.group(0)
def parse_and_validate(text: str) -> Product:
try:
raw = json.loads(text) # happy path first: zero overhead
except json.JSONDecodeError:
try:
raw = json_repair.loads(extract_json_candidate(text))
except Exception as e:
raise Quarantine(FailureType.PARSE, f"repair failed: {e}") from e
if not isinstance(raw, dict):
raise Quarantine(FailureType.PARSE, "top-level value is not an object")
try:
p = Product.model_validate(raw)
except ValidationError as e:
raise Quarantine(FailureType.VALIDATION, e.errors()[0]["msg"]) from e
semantic_checks(p, raw) # rung 3: business-logic sanity
return p
The order matters for cost. json.loads on the happy path is a fast C-level parse. Repair only runs when the fast path fails. Every failure you fix here is an API call you didn’t make — and at the prices below, that compounds.
Rung 3 is two lines of business logic the schema can’t express, and it’s where your domain knowledge lives:
def semantic_checks(p: Product, raw: dict) -> None:
# Schema-valid nonsense the schema cannot express:
if p.price > Decimal("5000"):
raise Quarantine(FailureType.SEMANTIC, f"price {p.price} out of band")
if raw.get("title", "").strip() != raw.get("title"):
raise Quarantine(FailureType.SEMANTIC, "untrimmed title")
Rung 2: the targeted re-prompt (and why one retry usually fixes it)
When repair can’t save the output, you escalate: re-prompt with the specific error cited. This is the difference between “try again” (which produces a different random failure) and “your price field was the string '$12.50', expected a number” (which the model fixes nearly every time).
Here’s what the ladder achieved in the test run on 400 documents:
| Metric | Naive pipeline | Validated pipeline |
|---|---|---|
| Docs attempted | 400 | 400 |
| Rows written | 319 | 400 |
| Corrupt rows in DB | 39 (12.2% of writes) | 0 |
| Docs silently lost | 81 (20.2%) | 0 |
| Accepted on attempt 1 | — | 292 (73%) |
| Fixed on retry 2 | — | 98 |
| Fixed on retry 3 | — | 10 |
| Quarantined | — | 0 |
| Total input tokens | — | 28,445 |
| Total output tokens | — | 13,689 |
The retry distribution is the whole argument: 73% of documents pass clean, most of the rest are fixed with one targeted retry, and only 2.5% need the third attempt. Retries are cheap because they’re rare and targeted. And because the pipeline distinguishes failure types, quarantine is honest: when a document fails all three attempts, it goes to a quarantine table with the exact reason attached (parse, validation, semantic + message), where a human or a stronger model can look at just the hard cases — instead of the whole queue.
The naive baseline lost those 81 documents and never knew. That’s the real cost of skipping validation: not re-prompt spend, but records that vanish while your monitoring shows green.
Rung 3 fails safe: crash-safe checkpointing in SQLite
Any pipeline that runs unattended for hours will be killed at some point — deploy restart, OOM, provider outage at hour three of a six-hour run. If your runner keeps progress in memory, a crash costs you the entire run. This one checkpoints every document to SQLite as it completes:
SCHEMA = """
CREATE TABLE IF NOT EXISTS records (
doc_id TEXT PRIMARY KEY,
status TEXT NOT NULL, -- pending | accepted | quarantined
payload TEXT,
fail_type TEXT,
fail_msg TEXT,
attempts INTEGER NOT NULL DEFAULT 0,
tokens_in INTEGER NOT NULL DEFAULT 0,
tokens_out INTEGER NOT NULL DEFAULT 0,
updated_at REAL NOT NULL
);
"""
class CheckpointStore:
def __init__(self, path: Path):
self.db = sqlite3.connect(path, isolation_level=None) # autocommit
self.db.execute("PRAGMA journal_mode=WAL") # crash-safe writes
self.db.execute("PRAGMA synchronous=NORMAL")
self.db.executescript(SCHEMA)
def claim_pending(self, doc_ids: list[str], limit: int) -> list[str]:
"""Docs not yet ACCEPTED. Terminal successes are never redone."""
have = {r[0] for r in self.db.execute(
"SELECT doc_id FROM records WHERE status='accepted'")}
return [d for d in doc_ids if d not in have][:limit]
Two design choices earn their keep here. WAL mode (journal_mode=WAL) means a kill -9 mid-write doesn’t corrupt the database — the write either fully lands or fully rolls back. And PRIMARY KEY on doc_id with an upsert makes the whole runner idempotent: you can run it twice, crash it, fork it — a document that was already accepted is never processed again.
The test proved this by simulating a real crash: run the pipeline, kill it after exactly 200 documents, then start a fresh process and resume. Result: resume processed the remaining 201 docs, final state rows=400, dupes=0, still-pending=0 — zero duplicated work, zero lost work, and the run’s assertions (dupes == 0, acc + quar == N_DOCS, no stale fail_type on accepted rows) all held. That’s the property you want at 3 AM: restart the process and it finishes the job, no questions asked.
The cost math, with live prices
Per-token prices were fetched from OpenRouter’s public models API at runtime — the script pulls them fresh on every run, so the math below can’t go stale the way a hard-coded pricing table does. Prices retrieved in the test run (2026-11-15, USD per 1M tokens):
| Model | Input $/1M | Output $/1M |
|---|---|---|
google/gemini-4-carbon | $2.000 | $10.000 |
deepseek/deepseek-v4.1-flash-1017 | $0.038 | $0.500 |
Cost per 1,000 accepted records (using the run’s actual token counts — 28,445 input / 13,689 output for 400 docs):
- Gemini 4 Carbon: $0.48
- DeepSeek V4.1 Flash: $0.02
The reliability overhead — retries included — is priced in, because tokens_in/tokens_out accumulate across attempts before the checkpoint writes. Even on the flagship model, full validation with retries adds under half a dollar per thousand clean records. Compare that to the naive pipeline: 81 lost documents per 400 means at scale you’re paying full token price for 80% data completeness, plus 39 corrupt rows per 400 that someone discovers downstream — usually when a customer does.
One more number worth internalizing: the whole validated run cost $0.19 on Gemini-class pricing (0.028M in × $2 + 0.014M out × $10). The token spend of retries is noise. The data loss is the expense.
What to steal
The full runnable script (below — same one that produced every number in this article) gives you four components worth lifting into your own stack:
- Schema as contract —
extra="forbid"Pydantic model,Decimalfor money, explicit ranges on every numeric field. One definition drives validation and the re-prompt error text. - Repair-before-retry ladder — local JSON repair first (free), targeted re-prompt with the cited error second (cheap, ~98% of retries succeed by attempt 3), quarantine with reasons last (honest failure).
- Semantic checks as a separate layer — schema-valid nonsense fails loudly instead of becoming someone’s downstream problem.
- WAL-mode SQLite checkpointing — idempotent upserts keyed on
doc_id, so crashes cost you nothing and reruns duplicate nothing.
The pattern generalizes past scraping: invoice parsing, lead enrichment, document classification, any workflow where an LLM’s output becomes a database row. Wherever structured data meets a probabilistic generator, this is the minimum responsible architecture.
The complete script (~390 lines, deps: pydantic, json-repair) is reproduced in the article’s scratch tests and ran with exit 0 on Python 3.14 — swap FakeLLM.complete() for your provider’s client and point doc_ids at your queue, and you have a production runner.
(Building an extraction pipeline that can’t quietly corrupt your data — or want one integrated into an existing system? Email [email protected].)