Buylist Sheet Pricing — Design

Date: 2026-08-21 Status: Decided 2026-08-23 (Brent) — implemented Source: Notion, Jonathan Medina 2026-08-20 — "Buylist should give an option to use pricing from the sheet or market priceing"Related: 2026-07-18-buylist-import-connectors.md (the parsers this extends), 2026-07-26-buylist-url-csv-import-design.md

Problem

A customer's uploaded list already carries prices. TCGplayer's collection export has six price columns; ManaBox's has one. The parsers read neither — every matched line is re-priced from our own catalog via resolveBasisPrice, and the sheet's numbers are discarded at parseTcgplayerRow / parseManaBoxRow.

That is the right default. But a merchant who has agreed a price with a customer off a printed sheet, or who trusts TCGplayer's number over ours for a thin market, currently has no way to say so except to retype every line's Market price by hand on the review screen.

Scope

Let the merchant choose, per import, whether matched lines take their basis price from the uploaded sheet or from our catalog (today's behavior).

Non-goals:

• No change to the rate math. Whatever becomes basisPrice still flows through applyBuylistRate unchanged — this decides the input to buylist rates, not the rates.
• No new persisted store setting. This is a per-import choice, not a configuration (see "Why not a config field").
• Nothing for the pasted-text path, which carries no prices at all.
• No change to buylistConfig.priceBasis (market / lowest / lower_of), which selects among our price sources. Sheet pricing sits above it: when the sheet supplies the price, priceBasis is not consulted for that line.

Decisions (2026-08-23, Brent)

All three questions below are answered. Kept in question form because the reasoning is what a future reader needs, not just the outcome.

Question	Decision
Which TCGplayer column?	All four market columns, merchant picks per import. TCG Marketplace Price and My Store Price excluded.
Is ManaBox in scope?	No. ManaBox uploads keep catalog pricing and the control is not shown.
Rows with no usable price?	Fall back to catalog pricing, and mark the line so the fallback is visible on the review screen.

1. Which TCGplayer column is "the price"? — all four market columns

The export carries all of:

Column	What it means
TCG Market Price	TCGplayer's computed market price
TCG Direct Low	Lowest TCGplayer Direct listing
TCG Low w/ Shipping	Lowest listing including shipping
TCG Low Price	Lowest listing
TCG Marketplace Price	The seller's own current listed price
My Store Price	The seller's store price

Column names are verbatim from TCGplayer's official CSV glossary, cited in 2026-07-18-buylist-import-connectors.md. We must not guess. The last two are the uploading seller's prices, which for a customer-supplied list means what that customer wants for the card — not a market figure, and not a sane buylist basis.

Decided: offer the four genuine market figures — TCG Market Price, TCG Low Price, TCG Low w/ Shipping, TCG Direct Low — and never the two seller-asking-price columns. catalog remains the default, so nothing changes for a merchant who ignores the control.

The exclusion is enforced in three places, deliberately: the parser captures only the four (readSheetPrices), the schema's z.enum(PRICE_SOURCES) rejects anything else, and the UI never renders them as options.

2. Is ManaBox in scope at all? — no

ManaBox's only price column is Purchase Price (+ Purchase Price Currency) — what the customer paid for the card, which has no relationship to its current value or to what the shop should pay. Using it as a buylist basis is close to meaningless.

Decided: TCGplayer only. A ManaBox upload keeps catalog pricing and the control is not rendered at all.

Enforced on both sides, because the CSV UI sends format: 'auto' (the layout is detected server-side from the header row) and so the schema alone cannot tell what was actually uploaded: generateQuote throws PRICE_SOURCE_UNSUPPORTED once the real format is known, surfaced as a 400. Without that, a ManaBox upload with a sheet price source would fall back to catalog pricing on every line and say nothing.

3. What happens to a row with no usable price? — fall back, and mark it

Blank, 0, $1.00 with a currency symbol, or a non-numeric cell. Options:

• (a) Fall back to catalog pricing for that line. The order still prices fully. Risk: silent mixing — the merchant believes they are on sheet pricing and some lines aren't. Mitigated by marking which lines fell back.
• (b) Leave the line unpriced (basisPrice: null), which applyBuylistRate already handles as ruleApplied: 'no-price', and let the merchant fill it in on the review screen.

Decided: (a), with a per-line marker. basisPriceSource is stored on each line and the review screen shows it under the Market cell — but only on an order that quoted something from a sheet, since on an ordinary order every row would carry the same label. Fallback rows render in the destructive colour so "which lines did NOT come from the sheet" is answerable at a glance.

4. Currency — moot

ManaBox ships Purchase Price Currency; TCGplayer's export is USD with no currency column. If ManaBox is out of scope (Q2), this question disappears — one more reason to scope it out.

Decided: moot, since ManaBox is out of scope and TCGplayer's export carries no currency column. Revisit only when a non-USD store exists.

Design as built

Data flow

The parsers already produce a ParsedLine; this adds one optional field.

parseTcgplayerRow  ──► ParsedLine { …, sheetPrices: { tcg_market, tcg_low,
                            │                         tcg_low_shipping, tcg_direct_low } }
generateQuote({ …, priceSource })
                            │   ← throws PRICE_SOURCE_UNSUPPORTED if the
                            │     resolved format is not 'tcgplayer'
priceAndRateLine ──► resolveLineBasis({ parsed, priceSource })
                            │     sheet price if usable, else resolveBasisPrice(…)
                            │     returns { basisPrice, basisPriceSource }
                     applyBuylistRate  (unchanged)

Everything downstream of basisPrice is untouched, which is what keeps this small: no per-game branching is added, and the plugin interface does not change.

Changes

File	Change
server/utils/tcgplayerCsv.js	PRICE_SOURCES, SHEET_PRICE_COLUMNS, parseSheetPrice, readSheetPrices — the vocabulary lives with the other TCGplayer CSV literals, so the schema can import it without pulling in the quote service
server/services/buylistQuoteService.js	parseTcgplayerRow captures all four columns; new resolveLineBasis picks and reports; generateQuote threads priceSource and rejects a non-TCGplayer upload
server/schemas/buylistOrder.js	priceSource: z.enum(PRICE_SOURCES).optional().default('catalog'), plus a refine pairing it with a CSV format
server/models/BuylistOrder.js	basisPriceSource: String on the line — declared field-by-field (§5.2), guarded by round-trip tests verified to fail without the schema line
server/routes/buylist.js	Threads priceSource; maps PRICE_SOURCE_UNSUPPORTED to a 400
client/src/utils/buylistPriceSources.js	UX mirror of the vocabulary + looksLikeTcgplayerCsv
client/src/pages/buylist/NewBuyOrderPage.jsx	The choice, rendered only for a TCGplayer upload and reset when the file is swapped
client/src/pages/buylist/BuylistReviewPage.jsx	Per-line provenance under the Market cell, shown only on a sheet-priced order

priceSource defaults to 'catalog' — today's behavior — so every existing caller is unaffected. Note this is a mode selector with a safe default, not an identity default: §5.5 governs game/shop, which are still never defaulted.

Parsing the value

parseSheetPrice strips non-numeric characters and rejects anything that doesn't yield a finite number above zero, falling back per Q3 rather than coercing to 0 — a 0 basis would silently offer the customer nothing, which is §5.4's failure shape with money attached.

Testing

• Parser: a real TCGplayer header row with populated, blank, zero, symbol- prefixed and non-numeric price cells.
• priceAndRateLine: sheet price wins when present; falls back when absent; priceSource: 'catalog' ignores sheetPrice entirely.
• Round-trip test for basisPriceSource (§5.2).
• Parity (§5.1): the change is in game-agnostic quote code, not in any plugin, so mtg / pokemon / riftbound are all affected identically — a test per game asserting a sheet-priced line prices the same way in each.

Risks

• Silent mixing (Q3) is the main one: a merchant believing they bought at sheet prices when some lines used catalog prices. The per-line marker exists for this.
• My Store Price misuse (Q1) — buying at the customer's asking price. The mitigation is not exposing the column.
• Scope creep into a general "price source" abstraction. Resisted deliberately: this is one optional field on ParsedLine, not a new pricing subsystem.