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 theforge-et-bedata-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
marketprice 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) · sometimesLKRandUSD(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/GRADEare swapped on some tabs; a couple of client-grouping tabs useCODEinstead ofCLIENTand reorder toCODE · STD · GRADE · TYPE. - Stale tabs —
IRAQcarries old week columns (06th Mar…27th Mar) and has noCLIENTcolumn. - Tabs with no price columns —
IRANhas onlySTD · TYPE · GRADE(no week/price columns). - Rate embedded as a header —
SYRIA/ERBILcarry the LKR/USD rate (327.5/327) as a column header rather than a labelledUSDcolumn. - Sparse
CLIENTcolumn 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.