Temporary page — Matthew's copy of the question set he is putting to Gemini for the prices visible stage
(stage 2) of supply-orders-part3-pricing.
Delete this page once the answers are in and folded into the stage 2 spec. It is a prompt, not a record: no
decisions live here, and nothing here is evidence of how the app behaves.
¶ Summary — where this stands (read this first)
Bottom line: five of Gemini's six answers are usable as they stand; the sixth is its headline feature and rests on
data we do not have. Three questions are back with it (round 2, below).
| # |
Question |
Gemini said |
Where it lands |
| 1 |
Item price comparison |
Don't build an item page — add wholesaler price columns to the low-stock report |
Adopt (keeps stage 2 to zero new screens for items) |
| 2 |
Supplier history |
A new supplier detail page, not a panel on the edit form |
Adopt (one new page, and it gives the import an audit home) |
| 3 |
Freshness |
Amber 24h, red 48h, hide the price at 72h |
Challenged — uploads are manual, so a Friday file must serve a Monday; hiding blanks the screens exactly when they are needed |
| 4 |
Unknown price basis |
Withhold the price, show an "awaiting basis confirmation" badge |
Challenged — our import asks for the basis at upload, so "unknown" means the mapping is wrong; I'd block the import rather than badge the price |
| 5 |
No data vs bad data |
Never a blank cell or £0.00: "no supplier data" vs "invalid price row" |
Adopt (the honesty rule, and cheap) |
| 6 |
NHS tariff guardrail |
Show the NHS reimbursement price beside wholesaler prices and flag any line bought above it |
Premise is wrong — dm+d is not the Drug Tariff, and nothing reads dm+d yet; see below |
The three problems with round 1
- The tariff claim — corrected 2026-09-22 after Matthew's clarification and a look at the dataset. Gemini says our
tier "already runs the official NHS TRUD database" with native Drug Tariff access. TRUD is not a database
(Matthew, 2026-09-22) — it is the NHSBSA's licensing and distribution mechanism, and the dm+d XML release he
imported is now staged at /srv/datasets/dmd/2026-09-22 (8 files, 228 MB, retrieved that day, with a
MANIFEST.txt recording provenance, hashes and the licence: internal platform use only, no redistribution). Two
things still stand against Gemini's sentence, and one is new information: nothing in the app reads those files
(there is no dm+d importer or table — the pharmacy app's SNOMED mappings and reimbursement prices are synthetic
demo values), and dm+d is not the Drug Tariff: the AMPP records carry a manufacturer PRICE with a PRICEDT
and a PRICE_BASISCD (a list price, often zero), which is neither the NHS reimbursement rate nor the monthly price
concessions. The Drug Tariff is a separate NHSBSA publication we do not hold. Decision needed from Matthew:
pursue the Drug Tariff (and the concessions) as its own licensed dataset, or build the comparison only against what
we legitimately hold — labelled so it can never be read as an official reimbursement rate?
- Hiding at 72 hours. Manual uploads plus weekends, bank holidays and leave mean gaps are normal, and the next
stage needs something to propose from. Queried: hidden, or visible-but-marked-unusable with its age?
- The stage boundary. It puts price columns on the order-building screen now, in the read-only stage; we had
deliberately held the order build to stage 3. Queried.
What I would do if the only answer is "get on with it": adopt 1, 2 and 5 as written; keep our thresholds but show
stale prices as unusable rather than hiding them (they stay visible, dated and clearly not for ordering); treat an
unknown basis as a reason to refuse the import; and build any price-versus-reimbursement comparison only from data we
legitimately hold — dm+d list prices at most, never labelled as an official reimbursement rate — until the Drug Tariff
is licensed and imported as its own dataset.
REQUEST FOR GEMINI — VeriPath Pharmacy: design decisions for the "prices visible" stage
Context. We run a pharmacy management app for UK independent community pharmacies. Each pharmacy's operational data lives on that pharmacy's own server; our central platform tier holds only the organisation's routing details, never operational or patient data. Stock ordering already works like this: the app lists the items whose stock has run low and produces a CSV of product code plus quantity, which the pharmacy uploads itself into the wholesaler's own ordering portal. The app never holds a wholesaler password and never scrapes or calls a wholesaler system.
We are now adding wholesaler pricing to it. Stage 1 (already specified, not yet built) lets a Business Manager upload a wholesaler's price-list CSV. The app shows the columns the file actually has and the manager confirms which column is the product code and which is the price — and whether that price is per pack or per unit, because we do not know yet and refuse to guess. Rows are matched to our stock items by UK PIP code, or by barcode when there is no PIP code. Every line of the file is accounted for as matched, unmatched (we do not stock it) or rejected (unusable price). Prices are held only in the pharmacy's own database; we never write them into our accounting system, and nothing about them is centralised. Import is Business-Manager-only. Files are expected to arrive daily, and a stale price is considered dangerous — worse than no price at all if it is presented as current.
Stage 2 is only about making those imported prices visible: read-only, nothing exported or ordered from them yet (that is a later stage). Who may see them: the Responsible Pharmacist, the Dispensing Technician and the Business Manager — the three roles who order stock. Counter assistants never see prices.
What exists on screen today:
- A low-stock report showing item, on-hand quantity and alert level.
- A supplier list and a supplier add/edit form (name, wholesaler code, account reference, order-file layout, minimum order value). There is no supplier detail page.
- No item screen at all: stock items are not browsable in the app. They only appear inside the low-stock report, the expiry report and the order-building screen.
- The import itself has a list page and a detail page showing the counts and the unmatched and rejected lines.
Questions — please give a clear recommendation for each, with brief reasoning and the trade-off you are rejecting:
-
Item-level price comparison. Should we build a new screen showing, for one stock item, each wholesaler's price on file, the cheapest, and the spread between them? Or is that over-building until ordering actually uses the prices in the later stage, making price columns on the existing low-stock report enough for now?
-
Supplier price-file history. Should the history of what that wholesaler's files contained — last import, counts, unmatched lines — be a panel on the existing supplier edit form, or a new supplier detail page?
-
Price freshness. How should staleness be presented, and at what thresholds? We assumed amber after 3 days and red after 7, on the basis that files are meant to be uploaded daily. What is a sensible rule, and should a price ever be hidden entirely once it is stale?
-
Unknown price basis. When a file's price basis (per pack or per unit) is unknown, should we show the price with a visible "basis not stated" warning, or withhold those prices from any comparison view until the basis is confirmed?
-
Absence versus bad data. How should we distinguish, on screen, an item that no uploaded file covers at all from an item a file covers but at a rejected or invalid price? We want the pharmacist never to confuse "no data" with "no cost" or "free".
-
Anything else. What would you add to a "prices visible" stage that we have missed, and is anything above a mistake for a UK community pharmacy setting? Please say plainly where you are uncertain rather than guessing.
This is a highly structured and pragmatically designed spec. Your SIAAS (Sovereign Infrastructure as a Service) design philosophy—where data stays completely inside the client-vps database context and the central platform remains purely a routing tier—is perfect for handling wholesaler commercial pricing data safely.
Based on our earlier technical discussions about how the UK pharmacy supply chain operates (specifically the multi-supplier cascade hierarchy, the NHS Drug Tariff guardrails, and data matching logic), here are clear, production-ready recommendations for each of the six questions posed by your agent.
- Recommendation: Add multi-supplier price columns directly to the existing low-stock report and order-building screens for Stage 2. Do not build a standalone, browsable item detail or spread page yet.
- Reasoning: Because your application explicitly hides browsable item catalogs to mirror operational efficiency (items only appear dynamically when actionable), forcing users to navigate to a nested "price comparison page" defeats the purpose. Pharmacists evaluate price velocity at the exact moment an item drops below thresholds on the low-stock pad.
- Rejected Trade-off: We are rejecting a standalone item comparison view. While it offers deeper analytical data, it introduces interface bloat for a read-only phase, whereas an inline table comparison keeps the operational view entirely consolidated.
- Recommendation: Build a dedicated Supplier Detail Page instead of cramming a panel into the existing add/edit modal form.
- Reasoning: Price imports are critical to clinical and commercial audit trails in a UK pharmacy. A simple modal edit form is not structurally equipped to map historical imports, logs of rejected rows, unmatched data sets, or validation statuses cleanly. Giving a supplier a dedicated detail page sets a clean foundational template for future automated integration steps (like configuring automated SFTP hooks or local network nodes).
- Rejected Trade-off: We are rejecting a simple form panel addition. The code complexity of rendering complex database history sets inside an interactive edit state creates a messy user experience and limits auditing potential.
- Recommendation: Enforce stricter time thresholds: Amber at 24 hours, Red at 48 hours, and completely hide (withhold) prices after 72 hours.
- Reasoning: In the UK generic drug market, pricing data changes constantly. A price file that is 3 days old is already commercially unreliable; a file that is 7 days old is financially dangerous. If a pharmacy purchases an item based on a week-old price visibility rule, they run a massive risk of buying a drug that has surged over the NHS Drug Tariff reimbursement cap, causing a net financial loss at the counter.
- Rejected Trade-off: We are rejecting the loose 3/7-day threshold framework. While it requires fewer manual user uploads, exposing stale data compromises the core business value of the pricing tool.
- Recommendation: Completely withhold the price from any comparison view until the Business Manager confirms the pricing basis. Expose a clear structural warning badge saying "Awaiting Price Basis Confirmation".
- Reasoning: Exposing a raw number without knowing if it represents a pack price or a unit price introduces high computational error. If a box of 28 tablets costs £28, but your app reads it as £1 per unit, a comparison calculation engine will report it as the cheapest option by an order of magnitude. If the pharmacist acts on this flawed visualization, the purchase workflow breaks down.
- Rejected Trade-off: We are rejecting the option to show unconfirmed prices with warning text labels. Humans frequently scan order lines quickly under heavy operational workloads, meaning a simple warning tag will eventually be overlooked, leading to material ordering mistakes.
-
Recommendation: Use explicit typographic statuses inside the target table grid cells, mapping them using distinct visual styles:
- No File Coverage: Show a light grey dash text string:
--- (No Supplier Data Available).
- Rejected / Invalid Data: Show a distinct warning layout:
⚠️ Data Error (Invalid Price File Row).
-
Reasoning: The application must never render a blank string, a numeric £0.00, or a generic error string. A distinct visual state proves to the pharmacist that the system is functioning correctly but that the imported supplier file contained formatting errors or corrupt price blocks.
-
Rejected Trade-off: We are rejecting a generic blank space or unified error string format. While simpler to implement programmatically, it increases human clinical friction by forcing the dispenser to guess why data is missing.
Since you are designing explicitly for the UK independent community pharmacy landscape, you should integrate The NHS Drug Tariff Guardrail into this "prices visible" stage.
- The Missing Link: Because your master data tier on VPS1 already runs the official NHS TRUD database, you have native access to the monthly NHS Drug Tariff price book.
- The Execution: When displaying the wholesaler prices side-by-side on the low-stock report, add a permanent reference line displaying the Active NHS Reimbursement Price.
- If a wholesaler's uploaded price exceeds the official NHS reimbursement rate, highlight that specific row instantly. This immediate cost-containment alert tells the pharmacist: "Purchasing this line item from this supplier will result in an immediate financial loss." This is the absolute highest priority feature a UK independent business manager looks for when reviewing stock pricing.
Would you like to map out how the Django views on the client-vps should pull the central TRUD Drug Tariff figures down the tunnel to execute this validation logic, or should we look at designing the exact database tracking columns required for the Supplier Detail Page audit log?
Thanks — the six answers are clear and mostly adopted. Three things need a second pass, because one of your
premises does not match this system, and two of your recommendations land on operational realities you may not
have had in view. Please answer in the same style (recommendation, reasoning, the trade-off rejected) and say plainly
where you are unsure.
A. dm+d and the Drug Tariff are two different datasets, and we hold only the first. Corrected 2026-09-22: an
earlier version of this question said we held no dm+d at all. We do — the release is staged and unread. We hold the
NHSBSA dm+d XML release (VTM / VMP / AMP / VMPP / AMPP names, pack structures and the AMPP-to-GTIN mapping),
under an NHSBSA/TRUD licence that permits internal platform use only and forbids redistributing, publishing
or re-exposing the files or derived dictionaries. We hold no NHS Drug Tariff (the monthly reimbursement price
book) and no price-concession feed. Please answer:
- A1. Under that licence, what may we legitimately do with dm+d inside a multi-tenant platform — can it inform item
matching and on-screen labels per tenant, and what must never be derived into a shared artefact?
- A2. The AMPP records carry a manufacturer
PRICE with a PRICEDT and a PRICE_BASISCD (a list price, often
zero). Is that a sound reference for comparing wholesaler purchase prices at all — and if so, how should it be
labelled so nobody reads it as an NHS reimbursement rate?
- A3. What does
PRICE_BASISCD mean, and can it settle the per-pack versus per-unit question authoritatively for
us? (Our dm+d lookup file carries no price-basis lookup, so we would be reading the specification.)
- A4. Is a buy-above-reimbursement guardrail worth building without the Drug Tariff, or should it wait until
that dataset is separately licensed and imported?
- A5. Please flag any other capability you assumed we have that we may not. We would rather be told a feature needs
a licence than find out after it is built.
B. Hiding prices after 72 hours. The uploads are manual: a person downloads the file from the wholesaler and a
Business Manager uploads it. Realistic gaps are weekends, bank holidays, annual leave, illness, and files simply not
being published. If prices vanish at 72 hours, the screens go blank exactly when the pharmacy is busiest — and the
next stage needs something to propose a supplier from. Please reconsider:
- B1. Should a stale price be hidden or shown as unusable — visible that it exists, clearly marked as not
fit to order from, with its age and the date of the file?
- B2. Is 3 days the right hard stop given a Friday file must serve a Monday, or should the thresholds be expressed
in something other than hours (for example "current / one working day old / older")?
- B3. If prices are withheld, what should the ordering stage propose when a whole file is too old — the item's
preferred supplier with no price, or nothing at all?
C. Stage boundary and the price basis. You put multi-supplier price columns on the low-stock report and the
order-building screen in this read-only stage; we had deliberately held the order build back to the following
stage. You also want any price with an unconfirmed basis withheld entirely. On that:
- C1. Is exposing prices on the order-building screen in the read-only stage sound, or does it invite ordering
decisions before the app can act on them?
- C2. Our import asks the manager for the basis (per pack or per unit) at upload time, so the only way a price
ends up "unconfirmed" is the manager saying they do not know. Should the manager be able to confirm or correct the
basis later on an already-imported file — and if a wholesaler's basis is genuinely unknown, is that not itself a
signal that the mapping is wrong and the file should be re-imported rather than shown with a badge?
Please keep the answers focused on what to build: this is a read-only stage in a staged build, and every feature has
to be justified against what the pharmacy can actually see and act on that day.
¶ Outcome (2026-09-22) — round 2 answered; every open point is settled and folded in
Gemini's Second Response (below) cleared all three challenges, and nothing in it contradicts the plan. Its
answers are now carried in supply-orders-pricing-phase2-visible:
- A — dm+d. Usable only as a central, read-only dictionary (parse once; tenants read it for labels and
matching; never merge tenant commercial data into a central artefact). Its PRICE must not be used as a
purchase benchmark and must not be displayed; PRICE_BASISCD does not settle pack-versus-unit — stage 1's
import-time confirmation does. The guardrail is postponed until the NHS Drug Tariff and the monthly price
concessions are licensed and imported. It also accepted the capability corrections (no Drug Tariff, no concessions,
no wholesaler API).
- B — freshness. Never hide a stale price: show it muted/struck-through with its age and a badge, and mark it
unusable. Thresholds move from hours to working days: current / one working day old / older than two. When a
whole file is stale, the order stage should still propose the item's usual supplier, with a warning.
- C — stage boundary. Remove the price columns from the order-building screen for this read-only stage — which
is what this page asked for — and refuse any import whose basis is unknown, with no post-import edit window.
This page has done its job and can be deleted once the stage 2 spec is signed off.
This Round 2 clarification frames the operational landscape and structural constraints beautifully. Let us correct the previous dataset assumptions directly and establish solid design decisions matching your SIAAS architecture (centralized platform routing tier, locally isolated tenant compute/data on the client-vps).
Here are the concrete recommendations for each specific question across the three problem areas, framed in the requested format.
¶ Part A: dm+d and the Drug Tariff Realities
- Recommendation: Use the centralized dm+d XML reference data as a read-only dictionary schema. Run a parsing task on the master tier to populate structural item records (
VMP/AMP/AMPP hierarchy) that the multi-tenant middleware maps down the tunnels to local tenant databases for text labeling and item matching. Never compile, cache, or merge tenant-uploaded commercial price points or custom matching identifiers back into a central artifact on the master tier.
- Reasoning: The NHSBSA TRUD licence terms explicitly restrict distribution. Keeping the TRUD dictionary as a strict read-only template maps cleanly to your SIAAS model. Isolating custom tenant mappings inside their individual databases avoids creating an unintended shared marketplace index, keeping you within the bounds of your platform licence.
- Rejected Trade-off: We are rejecting a pure, localized per-tenant dm+d parser implementation. Parsing a 228MB XML database across dozens of individual
client-vps instances introduces major storage overhead, whereas a shared read-only dictionary schema optimizes local resources without data leak risks.
- Recommendation: Do not use the dm+d
PRICE field as a baseline comparison for active wholesaler commercial costs. Omit it entirely from the comparison matrix.
- Reasoning: The manufacturer list price in dm+d is structurally disconnected from modern commercial reality; it is frequently zero or an outdated baseline figure that does not reflect actual NHS reimbursement rates or wholesale purchase costs. Exposing this figure beside actual supplier prices will confuse the pharmacy team.
- Rejected Trade-off: We are rejecting the option to display the dm+d price with a custom label (e.g., "Indicative Trade Price"). Displaying an inaccurate benchmark creates visual noise without offering any business value to the pharmacy team.
¶ A3. PRICE_BASISCD Meaning and Pack Constraints
- Recommendation: Do not rely on
PRICE_BASISCD to settle the pack vs. unit problem. Treat this field simply as metadata for your database models.
- Reasoning: In the NHSBSA dm+d schema,
PRICE_BASISCD (Price Basis Code) describes how the manufacturer list price was derived (e.g., whether it is a trade price or a drug tariff equivalent). It does not authoritatively dictate whether a wholesaler's CSV line item is a bulk count or a single pack price.
- Rejected Trade-off: We are rejecting the automation of pack calculations based on
PRICE_BASISCD. It is much safer to rely entirely on your Stage 1 file import validation checks.
- Recommendation: Postpone the cost-containment guardrail feature entirely until the official NHS Drug Tariff and monthly price concession datasets are licensed and imported independently.
- Reasoning: Building a procurement guardrail using dm+d list prices will cause a high volume of false alerts, eroding user trust in your app's insights.
- Rejected Trade-off: We are rejecting a temporary, synthetic guardrail layout. Building a business dashboard feature on incomplete data structures introduces technical debt and frustrates users.
- Correction: We acknowledge the explicit data constraints: The app currently operates without access to live Drug Tariff files, monthly price concessions, or an automated wholesale API connection layer. The platform functions cleanly as an inventory tracker that handles manual CSV file uploads and text matches on isolated customer databases.
- Recommendation: Never hide the price field. If a file passes its age limit, display the price in a visually disabled style (muted grey text struck through) alongside an explicit validation badge:
⚠️ Stale Data (Uploaded 4 days ago).
- Reasoning: Gaps in data uploads are normal due to weekends and holidays. Completely blanking the screens leaves the pharmacy without critical historical benchmarks during their busiest operational windows.
- Rejected Trade-off: We are rejecting the complete structural hiding of aged values. While it prevents purchasing off outdated information, blanking screens removes valuable baseline data that the pharmacy team can use as a reference point.
- Recommendation: Abandon hourly constraints. Transition to an operational framework based on working days: Current (uploaded today), 1 Working Day Old, and Stale (older than 2 working days).
- Reasoning: A calendar-hour metric throws false amber/red warnings over standard weekend shutdowns, whereas a working-day filter accounts for typical business logic patterns.
- Rejected Trade-off: We are rejecting a static hourly calculation model. While simpler to implement via standard Python datetime logic, it requires constant adjustments to account for standard bank holidays and weekend gaps.
- Recommendation: When a supplier's entire data file is marked stale, continue to display the price, but mark it unusable and output the item's default preferred supplier layout with a validation warning.
- Reasoning: The app must still propose a baseline layout for the upcoming ordering stage, even if the pricing file is old. Proposing an item with an explicitly marked old price is much more useful than showing a blank space.
- Rejected Trade-off: We are rejecting the option to hide the price entirely when a file is old. Hiding the price prevents the system from proposing a baseline layout, breaking the workflow efficiency for the next development phase.
¶ Part C: Stage Boundary and Price Basis
- Recommendation: Remove the price columns from the order-building view entirely for Stage 2. Limit pricing visibility strictly to the low-stock and expiry reports.
- Reasoning: Stage 2 is designed strictly as a read-only phase. Introducing multi-supplier pricing columns into the order-building layout implicitly invites procurement decisions before the software has the underlying logic to act on them, leading to workflow friction.
- Rejected Trade-off: We are rejecting an early layout preview in the order builder. Restricting visibility to reporting views keeps Stage 2 focused on information delivery without introducing UI confusion.
- Recommendation: If the Business Manager marks a file's price basis as unknown, block the import file entirely. Do not allow the data rows to commit to the
client-vps database, and do not provide an edit window to correct it later.
- Reasoning: An unknown price basis indicates a fundamental mapping error that requires a clean re-import. Allowing an incorrect layout to hit the database forces your team to maintain complex edit validation views for a scenario that should be blocked at the gateway.
- Rejected Trade-off: We are rejecting post-import data correction tools. While it offers flexbility, it introduces unnecessary database complexity, whereas a strict import gate guarantees data integrity from the start.
If your team is ready, let me know if you would like to map out the Django database structures for tracking the new Working Day Old status fields, or if we should design the exact UI warning labels for the low-stock report view.