A deterministic fiscal-enrichment API for a LATAM tire & auto-parts retailer operating in four countries — a 3-stage lookup chain that turns a product code or a raw free-text description into a fully classified record with verified per-country fiscal codes, plus the data-quality sprint that made the catalog trustworthy enough to serve it.
A LATAM tire & auto-parts retailer operating across four countries had a product catalog that told the story of every migration it had ever survived. Tires, rims, and parts imported from different ERPs in different years, through different scripts written by different people. The result: inconsistent taxonomy, malformed and truncated descriptions, duplicate spellings of the same brand, and — most expensive of all — missing or wrong fiscal codes for the countries where each product is invoiced. Every misclassified row was a downstream invoicing problem waiting to happen.
The obvious 2026 answer — "just ask an LLM to classify each product" — fails at this scale, and it fails in four distinct ways. Cost: at 243,000 SKUs plus a steady stream of enrichment requests from ERP and e-commerce integrations, per-request inference becomes a permanent line item. Latency: an invoicing pipeline cannot wait seconds for a model round-trip when a lookup should take milliseconds. Non-determinism: the same product description classified twice must produce the same answer, or reconciliation becomes impossible. And hallucination: a fiscal code is a legal artifact — a model that confidently invents one creates tax exposure, not convenience.
The design constraint I set from day one: LLMs were allowed exactly once — offline, to help build and cross-check the reference data. After that, every runtime answer had to come from a deterministic lookup against verified tables. Same input, same output, forever, at database speed.
Every enrichment request — a product code, structured fields, or a raw description like LT265/70R17 BRIDGESTONE DUELER AT 121/118S TL — falls through the same chain: exact match, structured-field match, normalized-description match. Each stage either answers with a known confidence score or passes the request down. No stage guesses.
Confidence is a contract, not a vibe. A Stage 1 exact-match answers at 0.97–0.99 and can be auto-inserted by the client's ERP. A Stage 2 structured-field match answers at 0.87 with fiscal codes merged from the reference table. A Stage 3 normalized-description match answers at 0.72 and is explicitly flagged for review before it can touch an invoice. Below that, the API says not_found — it never rounds up to a guess. The normalization layer underneath is a regex-driven size parser (tire and rim formats — 205/55R16, LT265/70R17 — plus segment classification) and a brand/model token map, both built offline and versioned with the catalog.
The per-country fiscal-code mapping is the part that had to be boringly reliable. Each classified product resolves to verified fiscal codes for the retailer's four-country footprint through a lookup keyed on product line and destination country. Where LLMs helped assemble candidate mappings offline, every code that shipped was verified against the reference tables before entering the runtime dataset — the API only ever serves codes that exist in a verified table.
Reliability engineering wraps the whole chain. PostgreSQL sits behind an opossum circuit breaker (trips at 50% error rate, 30-second reset) so a database incident degrades to fast failures instead of piled-up timeouts. Rate limiting is enforced per API key and per IP, Redis-backed with an in-memory fallback. Requests carry a structured-log trail (pino, per-request ID) and a hard per-request timeout, so no upstream hiccup can hold a connection hostage.
The API is only as deterministic as the data underneath it. Before the lookup chain could be the source of truth, the catalog itself had to be repaired — systematically, category by category, with every correction auditable.
The sprint started with a full-catalog audit that binned every anomaly into 16 defect categories — inconsistent segment taxonomy, malformed and truncated descriptions, duplicate brand spellings, missing spec fields, broken fiscal-code mappings, and the rest of the sediment a decade of ETL leaves behind. The discipline was strict: one category at a time. For each category — write the detection query, review the affected rows, apply the correction as a scripted, reversible batch, then re-run the detection to prove the count hits zero before moving to the next. No opportunistic fixes, no manual edits outside the pipeline. By the end, 26,420 rows had been corrected, each one traceable to the category and script that changed it.
The second half of the sprint attacked a different trust problem: every SKU's external reference link. At 243,255 SKUs, spot-checking is theater — so I built a 4-stage per-SKU verification system that checks every single one, deterministically, using a free search backend that needs no API key. Runtime LLM cost: $0. Search API cost: $0.
For each SKU, a candidate product page is resolved deterministically — brand-specific URL adapters where the manufacturer's site structure is known, falling back to a pluggable, key-free search backend. Same SKU in, same candidate out, on every run.
The candidate page is fetched (with caching, resume support, and per-domain politeness limits) and mined for structured attributes — JSON-LD product markup, OpenGraph tags, and regex extraction of brand, model, size, and bolt-pattern fields. Standard library only; no headless browser, no external service.
Extracted attributes are scored against the catalog record with hard gates and weighted bonuses: the size must appear on the page, the brand must match, and model and fitment fields add score. A fixed threshold separates verified from suspect — no fuzzy judgment calls, no model in the loop.
Passing SKUs land in a verified set with their exact link. Failing SKUs are flagged with the record, the candidate, and machine-readable reasons — a ready-made review queue. As a side effect, the verifier became a data-quality sensor in its own right: it surfaced truncated model fields in the source data that no schema check had caught.
Every number below is either a row count from the delivered catalog or a measured before/after on the running system. Nothing is projected.
| Segment | Description | SKUs | |
|---|---|---|---|
| PCR | Passenger car tires | 182,262 | |
| LTR | Light truck tires | 20,236 | |
| RINES | Rims & wheels | 18,637 | |
| MOTO | Motorcycle tires | 8,032 | |
| TBR | Truck & bus tires | 7,505 | |
| AG | Agricultural tires | 3,865 | |
| OTR | Off-the-road tires | 2,531 |
The admin catalog browse was doing an expensive sort and a full count on every page load — fine at prototype scale, punishing at 243k rows. A precomputed sort-index column turned the hot ORDER BY into an index walk, and a count cache eliminated the repeated full-table COUNT. Browse latency dropped roughly 5.8×, with no schema-breaking changes and no downtime — the kind of win that only exists because the taxonomy underneath had already been made canonical.
Every SKU resolves to exactly one canonical segment, enforced at the data layer. The segment counts above are now the regression baseline: any future import that shifts a count outside its expected band is caught before it ships, not after it invoices.
Zero LLM calls in the request path means enrichment answers are reproducible, auditable, and cost a database query. The confidence score in every response tells the consuming system exactly how the answer was derived and what to do with it.
Per-country fiscal codes for the four-country footprint are served only from verified reference tables, with provenance columns identifying which source supplied each field. A code the system doesn't know is a not_found — never an invention.
Five Jest + supertest suites — size parsing across every tire and rim format, fiscal mapping, description extraction, health checks, and auth/validation flows — run in GitHub Actions on every push, with a Docker build behind them. The lookup chain cannot regress silently.
I build deterministic data infrastructure for production environments where the same question must always get the same answer.
Let's Work Together