source: .forge/discovery/source-schemas/weekly-price-workbook-schema.md · generated — edit the markdown, not this file

Weekly price workbook — schema (F26 importer reference)

What this is: the structure of Empire’s real weekly bulk-price workbook (Weekly Auction Prices 35.xlsx, week 35, received 2026-09-13). It is the design input for F26 (weekly bulk pricing + Excel importer), F7 (quote gating on an unpriced standard) and F23, and for the forge-et-be data-model §2.2 revision (owner: Tharinda, AD #31). Only the structure is recorded here. The workbook holds Empire’s live per-customer prices (commercial data) and lives outside the repo — never committed. It backs OQ-59 (project-prd-signals.md) and PRD Rev 23.

The pricing model (confirmed by Empire, 2026-09)

  • Price is per customer-standard, per week. A “standard” is a customer-specific buying standard (recipe/spec).
  • Standard numbers are unique to one customer — another customer cannot hold the same standard number, even buying the same product. One customer can hold many standard numbers.
  • No per-region / per-market price. The workbook’s tabs group standards by market/customer for navigation only; region is a customer/quote attribute, not a price key. (Confirms PRD Rev 23; the market price axis is dropped from data-model §2.2.)
  • Derivation — FOUR stored figures (team decision — Tharinda, 2026-09-17; PRD Rev 31). The weekly price is set in LKR; an overhead is added in LKR; the total is converted to USD at a rate (≈ 327.5 LKR/USD in this workbook); a profit margin is added to reach the final USD price shared with the customer. All four are kept on the price row — not just the last one — so a past week’s price stays reconstructable. Only the final USD figure is required; the three working figures are recorded when the Tea Room has them and left empty when a price is set directly. The rate and the overhead are kept per price row (team decision — Tharinda, 2026-10-06; PRD Rev 53): the rate is a column on the price row, and the overhead is the difference between the two LKR figures — this workbook already uses two rates in one week (327.5 and 327).
  • Sharing: each customer receives their own standards’ prices by email — each recipient sees only their own standards, so this is a per-customer email whatever triggers it. The committed V1 path is the per-account send this week’s prices action; sending the whole week’s round in one action (a batch of these same per-account emails) is F37-a, Phase 2 — a stretch candidate from PRD Rev 28, committed at Rev 57 (2026-10-09). The per-recipient content rule above holds in both: a broadcast is a mail-merge run, never one message to a list.
  • History: previous weeks’ prices are kept (effective-dated rows, AD #14) for trend/history.
  • Editing: the Tea Room can manually edit a weekly price per customer-standard, and can upload the workbook for the system to extract and update prices (draft prices for Tea Room review + publish — PRD Rev 23). Automatic mailbox pick-up is post-V1; default is upload.

Structure as received (week 35)

  • 10 tabs, each a market or customer grouping — mostly destination markets (IRAQ, TURKEY, SYRIA, ERBIL, IRAN, HONG KONG, RUSSIA) plus a few client/brand-specific tabs.
  • Typical columns: CLIENT · STD · TYPE · GRADE · one column per auction week (dated headers, e.g. 18th Aug, 25th Aug, 02nd Sep, 09th Sep) · sometimes LKR and USD (the derivation), sometimes a bare rate literal header (327.5, 327).
  • Row counts vary widely per tab (e.g. TURKEY ≈ 495, MD ≈ 450, RUSSIA ≈ 277, IRAN ≈ 209, SYRIA ≈ 161; smaller for others). OQ-59 notes ≈ 1,450 standards priced in a current week.

Real-world quirks the importer MUST tolerate (all observed in week 35)

  • Column order is not fixed — TYPE/GRADE are swapped on some tabs; a couple of client-grouping tabs use CODE instead of CLIENT and reorder to CODE · STD · GRADE · TYPE.
  • Stale tabs — IRAQ carries old week columns (06th Mar…27th Mar) and has no CLIENT column.
  • Tabs with no price columns — IRAN has only STD · TYPE · GRADE (no week/price columns).
  • Rate embedded as a header — SYRIA/ERBIL carry the LKR/USD rate (327.5/327) as a column header rather than a labelled USD column.
  • Sparse CLIENT column carrying working notes; occasional shifted header row; a few #REF! and text in price cells (hand-maintained sheet).
  • Same standard number in more than one tab (~30 cases) — this is data drift / mis-keying, not a pricing rule (standard numbers are unique per customer). The importer must flag duplicates and never silently pick one.

Design implication: V1 ingests the workbook as draft prices for the Tea Room to review and publish, with a validation/exception report for the quirks above — not a silent overwrite. No fixed template is required (team decision — Tharinda, 2026-10-06; PRD Rev 53): the upload accepts today’s shape, handles the quirks above, and reports anything else instead of guessing (F26-c).

Still open (OQ-59)

Settled since this file was written: the derivation (PRD Rev 31), upload rather than mailbox pick-up, the duplicate-number rule (flag, never pick), and the rate/overhead grain and the template question (PRD Rev 53). Still to confirm with the Tea Room: the unit of the base numbers — F26-c builds on LKR per kg and shows the unit on screen — and who produces the sheet. Owner: Tharinda.

Generated from .forge/discovery/source-schemas/weekly-price-workbook-schema.md by .claude/lib/render-docs-html.js. Markdown is canonical.