03 — UPC Data Pipeline

May contain stale references

This document was audited on 2026-06-22 and contains references that need updating: DB table upc_overrides and admin UI not yet shipped. Cross-check critical details against the current codebase before relying on them.

UPC is the strongest matching signal for the ~56% of lines that carry one, so the UPC data pipeline’s reliability matters. This documents where the data comes from (verified against prod WhereFour), what’s recoverable algorithmically, and the maintainable system that replaces the static CSV.

Provenance (prod-verified)

• Catalog side: WhereFour /inventory API → DLT → dbt (bronze→silver→gold) → Typesense. WhereFour’s stored upc is corrupted (Excel sci-notation, dropped/ wrong check digits) and is passed through uncleaned. The clean upc_case field is created only at Typesense sync time from a static, git-committed CSV (apps/dagster/erp_pipeline/data/ck_finished_goods_upc.csv, ~92 rows from CK’s “Finished Goods – UPC Mapping” master spreadsheet), applied CK-connection-scoped (_ck_upc_override_connection_ids) in assets/typesense.py.
• Document side: extraction dumps all identifiers into item_ids[] (no typed UPC field). ~56% of CK lines carry a UPC; ~43% don’t (bimodal by customer).

Prod re-check (settling the “is it a sandbox artifact?” question)

The env WHEREFOUR_SCRAPE_USER/API_KEY == 1Password “Production WhereFour” (mchung@clevelandkitchen.com @ my.wherefour.com), so the probes were against prod CK WhereFour, not a sandbox import. On a broad 90-SKU prod sample:

• clean each stored verbatim in api.upc: 17/90 (19%)
• clean each GS1-recoverable from api.upc: 67/90 (74%)
• api.upc is the case GTIN-14: 17/90
• clean each in no other field (custom_field_values = “Vendor SKU”=SKU; conversions empty)
• all alternate endpoints 404 (/products, /items, /barcodes, /reports, /inventory/export) — /inventory is the only product endpoint.

Conclusion: the clean canonical each-level UPC does not live in the WhereFour API in usable form. ~74% is algorithmically recoverable; the rest (sci-notation truncation, genuine wrong digits) exists clean only in CK’s master file (their own GS1 file / a web-UI export the API doesn’t expose). So the API cannot be the sole source of truth — a maintained master is required.

What was built

A shared GS1 normalizer, gs1Upc12Candidates (+ upcCheckDigit, isValidUpc12) in apps/webapp/src/utils/matching-helpers.ts, and a Python port in apps/dagster/erp_pipeline/assets/typesense.py. It generates GS1-check-digit-VALID UPC-12 candidates from a corrupted value (leading-zero pad/drop, GTIN-14 indicator reduction, check-digit recompute), returning [] when unrecoverable.

• Match side (shipped, gated, A/B-verified): added to the matcher’s UPC bucket behind MatchGateOptions.gs1UpcRecovery (default off, additive/exact-match-only). Real-matcher A/B: recall 60.4%→61.6% (+7 correct), wrong-match 26.1%→25.5% (zero new wrong), unmatched 18.3%→17.4%. Modest, real, non-regressive.
• Catalog side (built, conservative gap-fill): derive_each_case recovers an each from the live WhereFour upc when unambiguous (single valid candidate); override stays primary; the case GTIN-14 is never derived (only ~30% derivable — CK uses varying pack indicators). Verified on 90 pairs: each recoverable-in-set 77%, conservative single-pick precision 86%, gap-fill 3–7 SKUs the CSV misses, 10 API↔master disagreements (override correctly wins).

The maintainable system (replaces the frozen CSV)

The static CSV goes stale silently. Replace it with:

1. DB table erp_upc_overrides (connection_id, sku, upc_each, upc_case, source, updated_at).
2. Internal-app CSV upload UI (apps/internal): drag-drop the updated master, GS1- validate every row on upload (reject bad check digits), show a coverage diff before commit. No redeploy, no engineer.
3. Dagster sync reads the DB table (not the file) + applies the GS1 gap-fill, and writes a per-SKU audit (source = master / GS1-recovered / UNRESOLVED + flags API↔master disagreements). The UNRESOLVED list is the worklist CK fills via the UI.

Result: self-service, validated, auditable, self-refreshing; ~74% auto-covered from the API even before any upload. See 07-dagster-pipeline-work.md.

Prereq / known issue

api/erp/connections/[id].ts PUT handler does not persist extra_config, so any connection-scoped flag (the override gate, matchingConfig) can’t be toggled in prod until fixed.