Real Manufacturer UPCs for Sealed Products — Design

Date: 2026-07-30 Status: Approved Scope decision: Source real UPCs for sealed products from the TCGCSV extUPC column we already download. Sealed products stop receiving synthetic LGSF… barcodes entirely — a real UPC when known, an empty barcode field otherwise. Singles are unchanged: their generated LGSF… barcodes remain correct and stay gated by syncConfig.barcodesEnabled. MTG only; Pokemon and Riftbound are exempt because neither has a sealed catalog source.

Problem

A sealed product — a booster box, a display case — has a manufacturer barcode physically printed on it. A merchant scanning that box at POS expects Shopify to find the product. Today it never will.

resolveSealedBarcode in server/utils/barcodeGenerator.js is written to prefer an official UPC and fall back to a generated LGSF{GAME}{HASH} value. In practice the official branch is unreachable, because nothing in the codebase ever supplies one:

• transformed.barcode — no plugin's transformSealedToProduct returns a barcode key. Verified in all three: server/plugins/mtg/index.js:453, server/plugins/pokemon/index.js:577, server/plugins/riftbound/index.js:417. The MTG transform passes through only tcgplayerProductId, cardmarketProductId, and mcmId.
• existing.barcode — SealedProduct.barcode is declared at server/models/SealedProduct.js:104 but has no writer. The sealed importer has zero barcode references, the Zod schema in server/schemas/sealedProducts.js has no barcode field, and the create path at server/routes/sealedProducts.js:415 deliberately leaves it unset. The only code that ever assigns it is a round-trip test hand-writing '850001234567'.

So every sealed product ships a synthetic LGSF… code. That value then occupies the barcode field, which is worse than leaving it empty: a POS scan of the real UPC silently finds nothing, and the merchant has no signal as to why.

This is CLAUDE.md §5.9 (Dead Scaffold) — the field, the explanatory comment, and the preference logic were all built around a value nothing produces.

Why not a merchant-entered UPC field

The original framing for this work was a merchant-entered UPC field. Two findings killed it:

1. The upstream data already has UPCs. See below.
2. Shopify already handles merchant entry, permanently. Sealed products are only ever written via createProduct (server/routes/sealedProducts.js:373 and :128). There is no update path that re-pushes a sealed barcode — the // recomputed each sync comment at :417 is inaccurate. A merchant editing the barcode in Shopify admin today already sticks forever. An in-app entry flow would duplicate a working Shopify feature.

The data source already carries UPCs

server/scripts/data-loading/updateSealedProductPrices.js already downloads ProductsAndPrices.csv from TCGCSV. That CSV contains an extUPC column that we never read.

Verified against the live CSVs (§5.4 — literal copied from real data, not assumed):

• group 23874 — Aetherdrift: 197 rows, 3 with a non-empty extUPC, all sealed — Play Booster Pack 195166278629, Play Booster Display 195166278636, Play Booster Display Case 195166278643. 195166 is Hasbro's GS1 prefix.
• group 2422: extUPC populated on booster box rows, e.g. 630509777709.

Full column list: productId, name, cleanName, imageUrl, categoryId, groupId, url, modifiedOn, imageCount, extRarity, extNumber, extSubType, extP, extT, extOracleText, lowPrice, midPrice, highPrice, marketPrice, directLowPrice, subTypeName, extUPC, extFlavorText.

Coverage is narrow but well-targeted: roughly 3 sealed SKUs per set (booster pack, display, display case). Bundles, collector boosters, prerelease kits and commander decks are blank. Packs and displays are the highest-volume items a shop scans, so the covered slice is the valuable one. The remaining ~85% get an empty barcode field, not a synthetic one.

Revised 2026-07-31 after the first production import — see "Uniqueness" below. Actual coverage was better than this estimate (849 rows recorded, not ~3/set), but 36% of those rows carry a UPC shared with another product and are suppressed.

Uniqueness — extUPC is not reliably per-SKU

The first production import recorded 849 barcodes across 3,249 matched sealed products, but only 634 distinct barcode values: 89 values are used by more than one product, covering 304 rows (36%).

These are not duplicate listings of one item. Verified against the live catalog:

• 195166121468 is assigned to ten distinct products — all five Streets of New Capenna commander decks (Bedecked Brokers, Cabaretti Cacophony, Maestros Massacre, Obscura Operation, Riveteers Rampage) plus each one's Minimal Packaging variant. Different decks, different contents, one code.
• 630509997053 spans seven products across two set codes (Q05 and Q06), mixing individual Challenger Decks with the Display and the "Set of 4".

TCGCSV appears to carry a family- or case-level UPC on multi-deck products rather than a per-SKU one.

Confirmed 2026-07-31: shared values are the wrong product's barcode

A merchant question ("I question that is the correct UPC") prompted verification against the raw feed and against external sources. Both confirmed the shared values are not merely ambiguous — they are incorrect.

Raw TCGCSV for group 24554 (Commander: Marvel Super Heroes) publishes exactly two non-empty extUPC values across the whole set:

195166313085  -> all four Collector's Edition decks (tcgIds 675627-675630)
195166313009  -> all four standard decks

195166313085 is the UPC of the four-deck Collector's Edition bundle — WotC SKU D53620000, MSRP $159.99, "includes all 4 decks". That code is printed on the outer bundle packaging. It is not on the single deck a merchant stocks, so scanning that deck can never produce it.

By contrast, a value held by exactly one product checks out: 195166326399 is genuinely The Hobbit Play Booster Box.

Therefore uniqueness is a usable proxy for correctness in this feed, and the guard does more than prevent POS ambiguity — it filters out categorically wrong data. Restated rule:

A shared extUPC indicates TCGCSV recorded a bundle or case barcode on its component items. Suppress it.

Known limitation: this only catches wrong-granularity values when they collide. A wrong-but-unique UPC would pass the guard undetected. No instance is known, and two independent spot-checks came back correct, but nothing in the design would surface one.

Merchant-visible consequence: multi-deck product lines — commander decks, Challenger decks — are disproportionately affected, so for those a blank barcode is the correct and permanent outcome unless a per-SKU source is found. Nothing currently explains this to the merchant; see the follow-on spec for surfacing it.

An ambiguous barcode is worse than none, which inverts this design's own premise. An empty field makes a POS scan miss — annoying but obvious. A shared code makes the scan match several products, so the merchant rings up the wrong item and the error is silent. That is the failure mode this whole effort exists to remove.

Rule: SealedProductBarcode.getBarcode returns null when the stored barcode is held by more than one product in that game. Both consumers — the sync payload and the repair script — inherit the suppression from that one choke point.

Checked at read time, not stamped at ingest. A stored ambiguous flag would need a migration and would go stale whenever upstream data shifts. Read-time checking is one extra countDocuments with limit: 2 (short-circuiting on the first other holder), backed by a non-unique {game, barcode} index. It costs a second query per lookup on a per-store handful of sealed products, and it self-corrects — which matters because nothing re-pushes a sealed barcode after product creation, so a stale flag could never be repaired.

What this was: the column name was verified against real data (§5.4), but its values were not verified to be per-product unique. Citing a field's existence is not the same as trusting its cardinality.

The join already exists. processPriceRows resolves each CSV row to a sealed product via tcgplayerProductId → productMap → product.uuid (built in buildSealedProductMap, server/scripts/data-loading/updateSealedProductPrices.js:168). That uuid is exactly the key resolveSealedBarcode uses. Nothing new needs joining — the column is sitting unread inside a loop that has already done the work.

Approach (chosen: A — read extUPC into a global reference collection)

Considered:

• A. Read extUPC in the existing importer, persist to a global reference collection ✅ — no new network calls, no new joins, no merchant workflow. Data is upstream and authoritative.
• B. Merchant-entered UPC field — rejected: duplicates a Shopify feature that already works permanently (see above), and asks every store to re-enter data we can get for free.
• C. Stash UPCs on SetModel.data.sealedProduct[] — rejected: SetModel is rebuilt daily from MTGJSON by updateMTGSets.js (3:00 UTC), so any value written there is clobbered. Depending on the 3:00/5:00 workflow ordering to survive is fragile.
• D. License a third-party UPC database — rejected: real cost, questionable TCG coverage, and unnecessary given TCGCSV covers the SKUs that matter.

MTGJSON is not a viable source: its Identifiers data model has 27 fields (abuId, cardKingdomId, scgId, tcgplayerProductId, tntId, …) and no UPC, EAN, or GTIN.

Section 1 — Data model

New model server/models/SealedProductBarcode.js, mirroring SealedProductMSRP as global reference data:

{
  game: String,                  // required — 'mtg' today
  uuid: String,                  // required — MTGJSON sealed product uuid (the join key)
  barcode: String,               // required — digits only, 12 (UPC-A) or 13 (EAN-13)
  source: String,                // 'tcgcsv' — provenance
  tcgplayerProductId: Number,    // traceability back to the CSV row
  updatedAt: Date
}
unique index: { game: 1, uuid: 1 }
static getBarcode(game, uuid)

game is on the key from day one so Pokemon and Riftbound need no migration when sealed lands for them — they pull the same TCGCSV columns from the same endpoint.

Why a separate collection rather than extending SealedProductMSRP: MSRP is keyed on (type, setCode, game) — a class of product. A UPC is per-uuid. Different grain; folding them would force one of the two into a key shape that doesn't fit.

Why global is safe here (CLAUDE.md §2 warns a change to a shared collection affects every store): the source is upstream and authoritative, not merchant-contributed. No store can poison it with a bad scan. This is the same trust model as SealedProductMSRP and mtg_sets.

Size: ~3 rows × ~900 MTG sets ≈ 2–3k small documents. Negligible against the Atlas budget.

Round-trip test required (§5.2): write via the model → read back → assert every field present, including source and tcgplayerProductId. The test must fail if any schema line is deleted.

Section 2 — Importer change

In server/scripts/data-loading/updateSealedProductPrices.js, inside the existing processPriceRows loop:

1. Read row.extUPC.
2. Validate: strip whitespace; accept only all-digit values of length 12 or 13. Reject anything else and count it in the run summary rather than throwing — a malformed upstream value must not fail the price import.
3. Buffer an upsert keyed on { game, uuid: product.uuid }.
4. Flush with the existing batching, via bulkWrite.

Source game from getPlugin('mtg').gameId rather than writing a bare 'mtg' literal. This importer is MTG-only by construction — it reads SetModel.data.sealedProduct, which is MTGJSON — so the value is an explicit constant, not a §5.5 phantom default. Sourcing it from the plugin makes that unambiguous to a reviewer grepping for 'mtg' literals, and matches the existing precedent at server/scripts/data-loading/transformSetsToProducts.js:561, which pulls mtgGameCode from getPlugin('mtg').gameCode.

The ordering trap

The loop hits if (!finalPrice) continue; at :304. The UPC upsert must happen before that early-continue, or any sealed product without a usable market price or MSRP fallback silently never records its UPC. This gets an explicit test: a row with a valid extUPC and no price must still produce a barcode upsert.

Absence is not deletion

If extUPC is blank on a later run, keep the last known value — do not delete. A transient upstream blank must not wipe a good UPC, because (per Section 5) nothing would ever re-push it to an already-synced product.

Duplicate UPCs

Two uuids can legitimately share a UPC (reprints, repackaging). Shopify permits duplicate barcodes. Log at warn for visibility; do not block the write.

Section 3 — Sync payload change

resolveSealedBarcode in server/utils/barcodeGenerator.js drops the generated fallback. It becomes "prefer official, else nothing"; the gameCode and uuid parameters go away. The function is kept rather than inlined so the sealed-is-official-only policy lives in one named, commented place.

buildSealedSyncPayload in server/routes/sealedProducts.js resolves officialBarcode from SealedProductBarcode.getBarcode(plugin.gameId, transformed.uuid).

When the UPC is unknown, syncPayload.barcode is undefined, and server/services/shopifyAPI.js:1837 already omits the field via ...(mongoProduct.variantbarcode ? { barcode: … } : {}). No change is needed in shopifyAPI.js.

Also corrects the inaccurate // recomputed each sync comment at server/routes/sealedProducts.js:417.

Interaction with syncConfig.barcodesEnabled

After this change the toggle governs one thing only:

	Source	Gated by barcodesEnabled?
Singles	Generated LGSF…	Yes — our synthetic label, merchant opts in
Sealed	Real manufacturer UPC	No — the product's factual identity

A UPC is not a label the merchant chose to adopt; it is what is physically on the box. Suppressing it would make a POS scan fail for a store with no reason to think the toggle reached that far. The toggle's meaning becomes exactly what the existing UI copy in client/src/components/BarcodeSyncSettings.jsx already describes: generated barcodes for single cards.

Conflict with a stranded commit: c644924 ("Gate sealed MSRP by game, enrich the import path, and honor barcode policy") adds a barcodesEnabled gate to sealed. It was committed nine minutes after PR #406 merged and is not an ancestor of main — it lives only on origin/claude/issue-396-6aiaxv. If it is cherry-picked later, its sealed barcode gate contradicts this design and must be dropped on the way in. Its other contents (supportsSealedMSRP, the sealedSyncPayload.js extraction, import-path enrichment) are unaffected and still worth landing.

Section 4 — Repair script

server/scripts/migrations/repairSyntheticSealedBarcodes.js, dry-run by default (matching the migratePerGamePricingConfig / :dry-run convention).

For each store with synced sealed products (SealedProduct where shopifyVariantId is set):

1. Read the variant's current barcode from Shopify — through server/services/shopifyAPI.js (CLAUDE.md rule 6, never raw axios/fetch).
2. If it does not start with LGSF, leave it untouched. That value is the merchant's own, and the prefix is what makes this precise: we only ever modify values we wrote.
3. If it does start with LGSF: set the real UPC when SealedProductBarcode has one, otherwise clear the field to empty.
4. Skip stores with revoked or dead tokens without failing the run.

This is not a one-time migration

Because nothing updates a sealed product's barcode after creation (Section 3), an already-synced sealed product will never receive a UPC learned later. This script is therefore the only repair mechanism, not a fire-once migration. It must be idempotent and re-runnable, and is expected to be run again as upstream coverage improves.

A proper sealed re-sync path is the real long-term fix. It is out of scope here and should be tracked separately.

Run ordering

The script must run only after a successful importer run has populated SealedProductBarcode. Against an empty reference collection, "known UPC" is false for everything, so it clears every LGSF… code — and per the point above, nothing would ever fill them back in.

Section 5 — Testing

Per CLAUDE.md §7:

• §5.2 round-trip: server/models/SealedProductBarcode.test.js — write → read back → assert all fields, including source and tcgplayerProductId.
• Importer: extUPC parsed and upserted; a row with a valid extUPC and no usable price still writes the barcode (guards the ordering trap); malformed values rejected and counted, not thrown; a later blank does not delete a stored value.
• resolveSealedBarcode: existing tests in server/utils/barcodeGenerator.test.js updated — official value returned; undefined when unknown; no LGSF… value is ever produced for sealed.
• buildSealedSyncPayload: real UPC preferred; payload omits variantbarcode when unknown; real UPCs ship regardless of syncConfig.barcodesEnabled.
• Repair script: mocked shopifyAPI; asserts non-LGSF values are untouched, dry-run writes nothing, revoked-token stores are skipped, and a second run is a no-op.
• §5.4: the extUPC literal cites the TCGCSV URLs above in a code comment.
• Coverage: every modified file ≥70% on all four metrics, read from the per-file table by hand — the Vitest 4 threshold gate is silently inoperative and exits 0 regardless.

Parity checklist (§5.1)

• mtg — mirrored (the only game with a sealed catalog source).
• pokemon — exempt: no sealed catalog source. transformSealedToProduct exists but nothing can enumerate sealed products for it.
• riftbound — exempt: same reason.

Verified at server/plugins/mtg/index.js:417, which reads SetModel.data.sealedProduct; no equivalent exists in the other two plugins. Both games' catalogs come from TCGCSV, so extUPC will already be present whenever sealed support lands for them — which is why game is on the reference key now.

Section 6 — Rollout

• PR 1 — model + importer + payload change, end-to-end so nothing ships unconsumed (§5.9). New syncs behave correctly immediately; UPCs populate after the next 5:00 UTC update-sealed-product-prices run.
• PR 2 — repair script, run after PR 1's first import completes.

Open item

SealedProduct.barcode becomes fully redundant once the reference collection exists, and nothing has ever written it. The preference is to delete the field and the existing?.barcode precedence rather than leave dead scaffold (§5.9) — contingent on confirming the field is empty in production. If that cannot be confirmed, keeping the field is harmless and costs nothing; it simply stays unread.

Explicitly out of scope

• The c644924 cherry-pick (see Section 3 for the required adjustment).
• Riftbound singles barcodes — server/plugins/riftbound/index.js:189 writes barcode: '' and its buildVariantProducts omits the barcode/uniqueKey/gameCode trio entirely, so Riftbound singles get no barcodes at all. Separate §5.1 gap.
• A sealed re-sync/update path (see Section 4).
• CLAUDE.md §2 incorrectly lists sealedproducts as a global/shared collection; it is per-store (shop field, unique {shop, game, uuid}). Doc fix, tracked separately.