Status (2026-09-22 night): stages 1, 2, 3 AND 4 are LIVE and verified (35/35, 24/24, 24/24, 49/49); stage 5
(retention and purge) later. The import, the visible prices, price-driven ordering, the order's value and the
minimum-order-value advisory all run ontest-client-pharmacy, with the demo data now reaching every freshness state,
a stale-only item so the fallback is demonstrable, and every value state on the build screen at once (priced,
not known, and short of a minimum). Stage 4 is on
supply-orders-pricing-phase4-value — its
margin column (W4) is answered in writing and deliberately not built, because the only reimbursement figures in
the system are synthetic demo prices against unlinked items.
Matthew's walk-through of stages 1–4 is the next human step, and the two stage 3 items he parked were built as
their recorded starting positions (reversible in one line each). The only open item between stages 1 and 2 was the
price basis, and it is settled: the import is not refused for an unconfirmed basis (aNOT_STATEDprice is shown
with its basis beside it and never as comparable). Q1 (who sees prices) is answered: import is the
Business Manager's, the RP / Technician / Business Manager see prices on the stock screens. Q3 (per pack or
per unit) does not block — the mapping records it and example files settle the mechanism. Q2 (retention)
stays open and blocks nothing. He also expects "further modifications post-completion when we start using it"
— see After first real use on the Phase 1 spec.Stage 2 is fully specced too and held — supply-orders-pricing-phase2-visible
— because no stage starts before the one before it is verified live. An external review settled the freshness,
basis and guardrail questions there: stale prices are shown but marked unusable (never hidden), freshness is
measured in working days, prices stay off the order-building screen until stage 3, and the
reimbursement guardrail is postponed until the NHS Drug Tariff and price concessions are licensed and imported.Paused here for the night, 2026-09-22: no further code until the walkthrough pages are done (2026-09-23). The
plan's only remaining code is stage 5 (retention/purge), unnamed and waiting on Matthew's Q2 answer, and it blocks
nothing.Four pages now cover supply ordering, in order:
supply_orders (client research),
supply_orders_part2 (the client-side design
transcript), supply-orders-spec (the v1 build, live),
and this page (the pricing layer on top of it).
Matthew, 2026-09-22: "we need to provide facilities for csv files to be imported for the purpose of pricing data
and assisting the pharmacy in ordering their supplies based on these prices."
The shipped v1 does the opposite by design — the build spec lists no purchase prices under Deliberately not in
v1, and Supplier.minimum_order_value is even documented in the code as "display only — the app holds no purchase
prices and does not check it". That decision is now reversed, so this page plans the reversal.
Part 2 proposed the same feature inside ERPNext
(custom DocTypes, a Wholesaler Price List Upload form, buying Price Lists named Wholesale-<supplier>, then a
scorecard that reads them with frappe.db.get_all per line). The intent is kept; the placement changes — the app
holds the prices, because a per-line frappe read is exactly the cost the stock code already refuses to pay (W4:
"Use the cache, never a live call per item").
| Thing | Decision |
|---|---|
| Who may import a price file | Business Manager |
| Who may see prices | All three stock roles (RP, Technician, Business Manager) on the ordering screens; import and price-file history stay Business-Manager-only |
| Where the prices live | In the app, on the client DB — not pushed into ERPNext buying price lists |
| Retention | Prices will be uploaded daily, so "we may automatically remove them after a few days" — foreseen, to be discussed (Phase 5, not built now) |
| What we build it against | Example data — the mechanism and its working parts go in first, then the procedures are learned from example CSVs (see the staged approach below) |
"I think we need to approach this by assembling everything so that we have the mechanism and working parts in
place — once that is done we can then start learning the procedures using example data and csv files — this is a
very complex operation so will have to do it in stages."
That settles what the unknown quantities were holding up, and it changes nothing about the phases — it changes what
they are built and tested against. No real wholesaler file is needed to start, and nothing waits for one:
seed_pharmacy_stock pattern) and example minimum order values on the seeded suppliers give every phasePharmacyStockManageRequiredMixin admits. Built as one named read gate plus one import gate, so the role listimported_at on every row, and order lines copy the price by value, never by reference to a price row).StockItem.pack_size is already there to reconcile the file's pack againstIt is a field on the supplier record (Supplier.minimum_order_value), captured on the supplier add/edit screen and
defined in the code as "Contractual minimum order value (display only — the app holds no purchase prices and does
not check it)". It is there because wholesalers set a minimum basket per delivery: below it the pharmacy
either cannot place the order or pays a delivery surcharge, which is real money on a small, split order.
Today the field does nothing at all but display — the app holds no prices, so it has nothing to total, and the
help text on the screen says so.
It is not evidenced anywhere in the wiki. No page under pharmacy_app mentions a minimum order value; the
field arrived with the ordering build's supplier profile (W2 / Ticket E in
supply-orders-spec) and nothing confirms its numbers.
So, per Matthew's steer, example values on the seeded suppliers stand in while Phase 4 is built and
demonstrated, and the real thresholds are established from each wholesaler's own terms later — what the
threshold is, and what actually happens below it (refusal, surcharge, or nothing), differs by wholesaler. Both are
data entry, not a build.
Everything below is read from /root/work/pharmacy_booking_app at 38e2271; where the wiki pages and the code
disagree, the code wins.
StockItem (pharmacy_stock/models.py:41) already carries the keys a wholesaler file can match on:item_code (the ERPNext item), item_name, pip_code (CharField, blank, leading zeros preserved), barcodepack_size, form, strength, item_group, preferred_supplier (FK, null, PROTECT),order_up_to_level, is_active.Supplier (models.py:214) carries the per-wholesaler profile: name, contact fields, supplier_codeaccount_reference, export_profile, integration_profile,minimum_order_value, is_active. No price data of any kind.Order / OrderLine (models.py:383, :438): supplier, reference, statusDRAFT → EXPORTED → RECEIVED (or CANCELLED), export count, actor names; a line is stock_item +quantity + position only — no price, no rate, no line value.services.reorder_suggestions() (services.py:191) lists activeStockAlertThreshold.low_stock_level and suggestsorder_up_to_level − on_hand; the build screen groups those lines by preferred_supplier. Cost never enterspharmacy_stock/order_profiles.py isGENERIC entry, and a realpharmacy_stock/urls.py): /stock/, /stock/receipts/, /stock/orders/ (+ new, detail,edit, export, cancel), /stock/suppliers/, /stock/low-stock/, /stock/expiry/, /stock/sync/.core/permissions.py:136-207): PharmacyStockStaffRequiredMixin (all four pharmacy roles, read),PharmacyStockManageRequiredMixin (RP / Technician / Business Manager — the goods-in and ordering gate),PharmacyBusinessManagerRequiredMixin (Business Manager and RP — used by the premises profile).pharmacy/models.py:234-239): PharmacyAuditEvent.Action already has ORDER_CREATED,ORDER_UPDATED, ORDER_EXPORTED, ORDER_CANCELLED, ORDER_RECEIVED. Next pharmacy migration is 0018;pharmacy_stock migration is 0004.pharmacy_stock today. The only request.FILES uses in the whole codebase arecore/views.py:168 and integrations/api/views.py:113 — the pharmacy stock upload is new ground here.pharmacy_stock (dsp_clinic/celery.py; other apps have tasks.py). So apharmacy_accounting.compute_expected_reimbursementprovision_pharmacy_reimbursement seeds it). That is the NHS side, not thetab*module-map). Nothing in this plan adds an ERPNext write.The Business Manager drops the wholesaler's own price file (CSV) into the app; the app shows the columns the file
actually has and the BM confirms which is the product code and which is the price; rows are matched to StockItem
on PIP code, else barcode, and stored against that supplier on the client DB with the upload kept as an audited
record. The order build then shows, per low-stock line, the cheapest supplier that has a price on file, its price
and the spread across suppliers, proposes that supplier, and lets the pharmacist keep or override it. The existing
one-order-per-supplier export is untouched. The price used is snapshotted on the order line by value, so an
order record survives any later purge of the price data.
SupplierPriceFile — one row per upload, the audited record:
| Field | Notes |
|---|---|
supplier |
FK Supplier, PROTECT |
filename, file_sha256, file_size |
as uploaded; the hash is what makes a repeat upload detectable |
price_date |
the date the prices are for — default today, editable at upload |
mapping |
which columns were used (code / price / description / pack), recorded as text so a mapping is auditable |
status |
PARSED / FAILED / SUPERSEDED |
rows_read, rows_matched, rows_unmatched, rows_rejected |
the counts the screen shows and the report quotes |
notes |
why a file failed, or what was rejected and why |
uploaded_by / uploaded_by_name / uploaded_at |
db_constraint=False, the house pattern for user refs |
SupplierPrice — one row per file line:
| Field | Notes |
|---|---|
file |
FK SupplierPriceFile, CASCADE |
supplier |
FK Supplier, PROTECT (denormalised so comparisons need no join) |
stock_item |
FK StockItem, PROTECT, null when the row matched nothing |
raw_code, raw_description |
exactly what the file said — never overwritten |
price |
DecimalField(max_digits=12, decimal_places=4), stored as the file gives it, never rounded by us |
imported_at |
on every row, so a Phase 5 purge is a date filter and nothing else |
unmatched_reason / rejected_reason |
why a row has no item, or was not accepted |
Constraints: unique (file, raw_code) so a duplicated code inside one file is caught rather than silently
doubling; index on (supplier, stock_item) and (stock_item) for the comparison query.
Matching rules. Normalise the file's code (strip spaces and any hyphen, keep leading zeros, case-fold for
alphanumeric barcodes) and try StockItem.pip_code, then StockItem.barcode. No fuzzy matching in v1 — an
unmatched row is reported, never guessed at. Rows that match nothing are kept with stock_item = null so the
BM can see exactly what the file held that the pharmacy does not stock.
Price parsing rules. Strip a currency symbol, thousands separators and spaces; a blank, zero, negative or
non-numeric price is rejected with a reason, never stored as 0. No VAT arithmetic and no pence rounding — the
app records what the wholesaler quoted.
No file retained. The original upload is parsed and discarded; the record keeps the name, size and hash. A
re-import is a new upload, not a replay (the hash then shows it is the same file).
Each phase is independently deployable and independently verifiable, and no phase starts before the previous one
is verified live. Phase 1 changes no existing behaviour; the ordering screens only change in Phase 3.
pharmacy_stock.0004; a mapping profile module besideorder_profiles.py (the same one-dict-entry rule); the import screen (upload → preview the real headers and thePRICE_FILE_IMPORTED, PRICE_FILE_FAILED,SUPERSEDED and the rejection count going into the detail text); the Business-Manager gate; admin registration..csv / .txt / .tsv only, size cap, UTF-8 with BOM tolerated, CRLF or LF, quoted fields,price_date supersedes the earlier fileSUPERSEDED) rather than accumulating a second set of rows for the same day.£1,234.56 price; a blank/zero/negative priceStockPositionCache row is written); then Hermes uploads a synthetictest-client-pharmacy and reconciles the counts against the file by hand.test-client-pharmacy against a synthetic price file with a deliberately unmatched line.Specced 2026-09-22: supply-orders-pricing-phase3-ordering
— the routing rules, the order/line snapshots, the routed build screen and one open decision (create the drafts
supplier by supplier, or all at once). Not authorised to build yet.
preferred_supplier; OrderLine gains the snapshot fields (unit_price,price_recorded_at, and the supplier the price came from, copied by value, not a FK to the price row — atest-client-pharmacy with byte comparison.Specced 2026-09-22: supply-orders-pricing-phase4-value
— the order value from the stage 3 snapshots, the minimum order value made a live advisory, and the margin
column written as an investigation, because there is no per-item reimbursement price we may display today.
Not authorised to build.
minimum_order_value becomes a realcompute_expected_reimbursement is per endorsed token today, sotest-client-pharmacy.SupplierPrice rows older than N days, keeping every SupplierPriceFilepurge_supplier_prices --days N, with a --dry-run) run by celerytest-client-pharmacy before it is scheduled.Price List, no Item Price writes). Revisit only if aWalkthrough pages — DEFERRED 2026-09-22 and WRITTEN 2026-09-27: "we'll need to get some walkthrough pages
done once we are done with the development work." The development work has now stopped moving (stages 1–4 and the
phase-8 comparison are all live and verified), so the click-by-click walkthrough exists:
Pricing & the Reimbursement Comparison — Walkthrough
— five stops in one sitting, from the price-file import through the working screens and the order advisory to the
reimbursement beside what the pharmacy paid, every figure read off the live test client when it was written. The
per-stage user guides remain the documentation that tracks a stage as it ships.
Sequencing decided 2026-09-22 (Matthew asked whether to spec stage 3 or build stages 1–2 first): implement stages 1
and 2 before speccing anything further. Stage 2 is already specced and read-only, so it is the cheapest possible
next increment; stage 3's design (which supplier gets proposed, how the pharmacist overrides, what the columns must
show) is exactly the sort of thing that changes once the pharmacy has used stage 2 — and the parked column-set
question above cannot be answered any other way. Stage 3 stays unwritten until stages 1 and 2 are live and used.
Phases in order, one at a time: audit → propose → confirm → apply → verify. OpenCode builds a phase and reports
back over from-opencode; Hermes verifies it live on test-client-pharmacy
after deploy (with a synthetic price file, never a real one), and the user guides are only updated once the
behaviour is live — the ordering guide C6 is amended when Phase 3 lands, and a Business-Manager guide for the price
import is written when Phase 1 does. Each stage is demonstrated with example data before the next one starts,
which is the point of Matthew's staged approach: the mechanism first, the procedures learned from example CSVs
afterwards. This page is the plan; the Phase 1 build spec is written once Matthew confirms it — Q1 is answered,
Q3 no longer blocks (mapping plus example data), Q2 stays open and harmless, and nothing is built or handed to
OpenCode before he says so.