Status (Hermes, 2026-09-21) — research, not a specification; implementation deferred. Raised by Matthew after
reading guide C3 — Run the Expiry Check, which states plainly
that the app cannot take expired stock out of the ledger except by a general adjustment. His direction: this work
is done after supply ordering.Read the models below as a design discussion, not as the schema to build: the settled Phase 3 decision D3.1 is
that ERPNext owns the stock ledger and Django holds only operating state (the SNOMED↔item mapping, alert
thresholds, a read-cache), so a local quantity-holding batch table must not be introduced — the same outcome is
reached with ERPNext batches through the existing receipt flow, which already requires a batch and an expiry on
every line. Two things here are genuinely new and queued, not ruled: (a) importing the wholesaler's
delivery/invoice file to fill the receipt lines (batch and expiry) instead of typing each one, which pairs with the
outbound export on supply_orders; (b) a proper route for
expired and withdrawn stock — quarantine, disposal and a reason of its own — rather than a free-text adjustment.
Recorded as Finding 8 on the regulatory content audit.Also worth holding against this page: the expiry bands (90 / 30 days, held in
StockSettings) are already built,
FEFO already refuses an expired batch, and the cached positions already power the expiry report — so the "alerts"
described here are largely built. See
phase-3-stock.
When stock is delivered to a pharmacy, since it has an expiry date the pharmacy needs to record this so if it surpasses the expiry date while still in stock, it becomes highlighted - my question is how do pharmacies record this information with incoming stock? Do they scan each item?
Pharmacies absolutely do not scan every individual packet upon arrival. Doing so would cause a logistical gridlock at the dispensary counter, especially when a delivery contains hundreds of items across multiple totes. [1]
Instead, pharmacies rely on two primary methods to log expiry dates and batch data when stock is delivered: Electronic Delivery Notes (EDNs) or "First Expired, First Out" (FEFO) manual visual checks.
Here is exactly how the process plays out on the pharmacy counter and how your SIaaS app can easily automate it.
Instead of the physical box, the data entry is taken care of by the Supplier Portal File.
When a pharmacist goes to their portal (AAH, Alliance, Phoenix) to checkout using your app's exported CSV order sheet, the wholesaler generates an electronic packing file alongside the physical delivery truck.
.csv or .txt file download).client-vps splits open the file. Wholesaler delivery files natively contain the item code, exact Batch/Lot Numbers, and Expiry Dates allocated to that specific crate shipment. [1, 2]If a short-liner or specialist distributor doesn't provide an electronic delivery file, the pharmacy resorts to a traditional operational routine called Date Checking & Stock Rotation. [4]
[ Crate of Stock Arrives at Pharmacy ]
│
▼
[ User matches items against your App's Invoice checklist ]
│
┌────────────────┴────────────────┐
▼ ▼
[ Normal Expiry Date ] [ Short-Dated Line (< 6 months) ]
│ │
Pushed straight to back Manually input date into your App
of physical shelf (FEFO) UI box to trigger the red alert system
You might wonder why they don't just use a barcode gun to scan the box.
Every modern prescription medicine pack in the UK features a 2D DataMatrix barcode containing the item's unique serial number, lot number, and expiry date. However, the industry workflow handles this at the point of dispensing, not the point of receipt: [2, 9, 10, 11, 12]
To make your app's stock control module highly robust, you should build an Inbound Delivery File Uploader alongside your export tool:
[ Import Supplier Delivery File ] on your dashboard.[ Mark as Short-Dated ]. Clicking it opens a simple date selector so the staff can quickly flag anomaly items during their physical shelf rotation. [1, 3, 16]Would you like to examine how to layout the Python script parser to read a standard wholesaler delivery invoice file, or should we design the Django database tables required to store these individual batch tracks and expiry dates?
If you're interested, let me know:
[2] https://www.pharma-scan.co.uk
[3] https://www.bluelinkerp.com
[6] https://www.blackcountryformulary.nhs.uk
[10] https://pmc.ncbi.nlm.nih.gov
[12] https://pharmaceutical-journal.com
[13] https://mhrainspectorate.blog.gov.uk
[14] https://northyorkshireccg.nhs.uk
[16] https://retailwings.africa
Yes can you show how to layout the Python script parser to read a standard wholesaler delivery invoice file
To automate the ingestion of expiry dates and batch codes without forcing the pharmacy staff to scan individual packets, your app can parse the electronic Invoicing / Delivery Files that wholesalers provide on their portals.
In the UK, when you download a digital delivery summary from AAH or Alliance Healthcare, the files are typically structured as comma-separated (CSV) or tab-delimited (TXT) files.
Here is how you can layout a clean, high-performance Python parser utilizing a Strategy Pattern to read these custom wholesaler formats directly within your client space, updating the local stock control database natively.
You can structure this parsing utility inside your codebase (e.g., clinical_data/integrations/delivery_parsers.py). Wholesalers often use different date formats (like DDMMYYYY or YYYY-MM-DD), so the mappers need to normalize these strings into Python datetime.date objects before committing them to PostgreSQL.
# clinical_data/integrations/delivery_parsers.py
import csv
from datetime import datetime
from django.core.exceptions import ValidationError
def parse_aah_date(date_str):
"""AAH often formats dates as DDMMYYYY (e.g., 31122027)."""
clean_date = str(date_str).strip()
try:
return datetime.strptime(clean_date, "%d%m%Y").date()
except ValueError:
return None
def parse_alliance_date(date_str):
"""Alliance commonly uses standard ISO date loops YYYY-MM-DD."""
clean_date = str(date_str).strip()
try:
return datetime.strptime(clean_date, "%Y-%m-%d").date()
except ValueError:
return None
# Master Inbound Layout Schema Dictionary
DELIVERY_PARSERS = {
"AAH": {
"delimiter": ",",
"mappings": {
"item_identifier": "pip_code",
"batch_column": "batch_number",
"expiry_column": "expiry_date",
"qty_column": "quantity_delivered"
},
"date_parser": parse_aah_date
},
"ALLIANCE": {
"delimiter": ",", # Can switch to '\t' if parsing tab-separated text variants
"mappings": {
"item_identifier": "ProductCode",
"batch_column": "LotNumber",
"expiry_column": "ExpiryDate",
"qty_column": "QtyShipped"
},
"date_parser": parse_alliance_date
}
}
Next, we write the processing core that handles the file upload loop. This service opens the file memory buffer, maps the columns dynamically based on the supplier key, and pushes the stock counts straight into your Django models.
# clinical_data/services.py
import io
import csv
from django.db import transaction
from .models import InventoryItem, StockBatch # Custom multi-tenant models
from .integrations.delivery_parsers import DELIVERY_PARSERS
class InboundDeliveryProcessor:
"""
Sovereign background processor that ingests supplier delivery notes
and updates batch tracking parameters locally. Cost: £0.00.
"""
def __init__(self, wholesaler_key, file_object):
self.supplier_key = str(wholesaler_key).upper().strip()
if self.supplier_key not in DELIVERY_PARSERS:
raise ValueError(f"No configured delivery parser for: {wholesaler_key}")
self.parser_config = DELIVERY_PARSERS[self.supplier_key]
self.file_data = file_object
def execute_ingestion(self, practice_context):
"""Processes rows in a single atomic transaction block for data safety."""
# Read file string stream seamlessly out of memory
csv_file = io.StringIO(self.file_data.read().decode('utf-8'))
reader = csv.DictReader(csv_file, delimiter=self.parser_config["delimiter"])
cols = self.parser_config["mappings"]
date_formatter = self.parser_config["date_parser"]
parsed_count = 0
with transaction.atomic():
for row in reader:
# 1. Extract raw dictionary elements based on wholesaler column names
raw_id = row.get(cols["item_identifier"], "").strip()
raw_batch = row.get(cols["batch_column"], "").strip()
raw_expiry = row.get(cols["expiry_column"], "").strip()
raw_qty = row.get(cols["qty_column"], "0").strip()
if not raw_id or not raw_qty:
continue # Skip corrupt row frames cleanly
# 2. Normalize and resolve the specific product inside this tenant practice database
# (Queries by either PIP code or GTIN barcode based on format type)
try:
inventory_item = InventoryItem.objects.get(
practice=practice_context,
pip_code=raw_id
)
except InventoryItem.DoesNotExist:
# Fallback string lookups for barcode structures (e.g. Alliance)
try:
inventory_item = InventoryItem.objects.get(
practice=practice_context,
gtin_barcode=raw_id
)
except InventoryItem.DoesNotExist:
continue # Log unknown item warning and skip row
# 3. Format structural fields
parsed_date = date_formatter(raw_expiry)
qty_delivered = int(raw_qty)
# 4. Update or Create the distinct batch log record
# If the batch exists, we add the new quantity; otherwise, log a new timeline tracking segment
batch_record, created = StockBatch.objects.get_or_create(
inventory_item=inventory_item,
batch_number=raw_batch,
defaults={'expiry_date': parsed_date, 'quantity': qty_delivered}
)
if not created:
batch_record.quantity += qty_delivered
batch_record.save()
# 5. Increments total local master unit counts cleanly
inventory_item.total_stock_count += qty_delivered
inventory_item.requires_reorder = False # Reset reorder safety flag
inventory_item.save()
parsed_count += 1
return parsed_count
To tie this straight into your frontend dashboard template where the user uploads the text file, you build a basic Django upload view wrapper:
# clinical_data/views.py
from django.shortcuts import render, redirect
from django.contrib import messages
from django.views.generic import View
from django.contrib.auth.mixins import LoginRequiredMixin
from .services import InboundDeliveryProcessor
class UploadDeliveryFileView(LoginRequiredMixin, View):
def post(self, request, wholesaler_key, *args, **kwargs):
uploaded_file = request.FILES.get('delivery_file')
if not uploaded_file:
messages.error(request, "No file attachment detected. Please select a valid document.")
return redirect('inventory_dashboard')
try:
# Instantiate engine and process entirely inside memory bounds
processor = InboundDeliveryProcessor(wholesaler_key, uploaded_file)
current_practice = request.user.current_practice # Enforces local context scoping
records_updated = processor.execute_ingestion(current_practice)
messages.success(request, f"Successfully processed delivery. Ingested {records_updated} unique stock lines.")
except Exception as e:
messages.error(request, f"Fulfillment ingestion failed: {str(e)}")
return redirect('inventory_dashboard')
.csv file data is read in-memory via io.StringIO and processed straight down to SQL logs. It is never saved to a temporary folder or file vault on VPS1, ensuring the data remains entirely within the client's execution container.DATE field, your local Django dashboard can query the StockBatch table every morning using a clean Django look-up (expiry_date__lte=today + timedelta(days=90)) to instantly highlight short-dated stocks with high-visibility warning flags automatically.Would you like to review how to lay out the matching Django database schemas (InventoryItem and StockBatch) to ensure these multi-supplier relational tables handle stock allocations and expiry calculations cleanly?
Which part of this stock tracking architecture should we implement next?
Let's review how to lay out the matching Django database schemas (InventoryItem and StockBatch) to ensure these multi-supplier relational tables handle stock allocations and expiry calculations cleanly
To support this seamless file-import workflow, your sovereign client database instance needs a clean, normalized structure.
Storing stock count data as a single raw integer on a product table is not enough for modern pharmacy inventory control. To handle automated expiration tracking and supplier auditing, your database must use a One-to-Many relationship between a core InventoryItem template catalog and a granular StockBatch tracking layer.
Here is how you can lay out these Django models (clinical_data/models.py), utilizing your stack's native PostgreSQL capabilities to enforce data integrity while automating "short-date" expiry calculations.
# clinical_data/models.py
from django.db import models
from datetime import date, timedelta
from django.utils.timezone import localdate
class InventoryItem(models.Model):
"""
Core master product catalog entry inside a single pharmacy practice tenant database.
Tracks global stock targets, barcodes, and ordering flags.
"""
practice = models.ForeignKey(
'onboarding.Practice',
on_delete=models.CASCADE,
related_name='inventory_items'
)
product_name = models.CharField(max_length=255)
# Crucial UK pharmaceutical index keys
pip_code = models.CharField(max_length=20, db_index=True, blank=True, null=True)
gtin_barcode = models.CharField(max_length=20, db_index=True, blank=True, null=True)
# Ordering Matrix rules
preferred_supplier = models.CharField(
max_length=50,
choices=[('AAH', 'AAH'), ('ALLIANCE', 'Alliance'), ('PHOENIX', 'Phoenix')],
db_index=True
)
min_stock_threshold = models.PositiveIntegerField(default=10)
ideal_stock_level = models.PositiveIntegerField(default=50)
# Aggregated fields for quick dashboard metrics
total_stock_count = models.PositiveIntegerField(default=0)
requires_reorder = models.BooleanField(default=False, db_index=True)
class Meta:
# Enforces that a PIP code or barcode is unique per physical shop practice tenant
unique_together = ('practice', 'pip_code')
verbose_name = "Inventory Item"
def __str__(self):
return f"{self.product_name} ({self.total_stock_count} units)"
def recalculate_stock_metrics(self):
"""
Aggregates total active batch stocks, checks min limits,
and flags if an automated order CSV export is required.
"""
# Sums non-zero quantities cleanly out of our PostgreSQL database instance
total = self.batches.filter(quantity__gt=0).aggregate(
models.Sum('quantity')
)['quantity__sum'] or 0
self.total_stock_count = total
self.requires_reorder = total <= self.min_stock_threshold
self.save()
class StockBatch(models.Model):
"""
Granular logistical tracking records. Every inbound CSV/TXT delivery note file
populates entries here to isolate distinct lot numbers and expiration lifespans.
"""
inventory_item = models.ForeignKey(
InventoryItem,
on_delete=models.CASCADE,
related_name='batches'
)
batch_number = models.CharField(max_length=100, db_index=True)
expiry_date = models.DateField(db_index=True)
# Current active physical shelf count for this specific lot segment
quantity = models.PositiveIntegerField(default=0)
# Manual overriding toggle flag for shelf checks
is_manually_flagged_short = models.BooleanField(default=False)
date_received = models.DateField(auto_now_add=True)
class Meta:
unique_together = ('inventory_item', 'batch_number')
ordering = ['expiry_date'] # Enforces natural "First Expired, First Out" (FEFO) ordering
verbose_name = "Stock Batch"
def __str__(self):
return f"Batch {self.batch_number} - Exp: {self.expiry_date} ({self.quantity} units)"
@property
def days_until_expiry(self):
"""Returns integers tracking operational deadlines relative to current time."""
if not self.expiry_date:
return 999
delta = self.expiry_date - localdate()
return delta.days
@property
def alert_status(self):
"""
Evaluates strict pharmaceutical expiration status profiles:
- EXPIRED (<= 0 days)
- CRITICAL (< 90 days / 3 months)
- WARNING (< 180 days / 6 months)
- GOOD (Safe)
"""
if self.quantity == 0:
return "DEPLETED"
days_left = self.days_until_expiry
if days_left <= 0:
return "EXPIRED"
if days_left <= 90 or self.is_manually_flagged_short:
return "CRITICAL"
if days_left <= 180:
return "WARNING"
return "GOOD"
Because this tracking database lives locally on the client's own virtual server (VPS2), you want queries that generate metrics instantly without table scan lag.
By structuring the tables with proper indices (db_index=True), you can write incredibly responsive lookups to populate the pharmacy's morning alerts section.
To populate an inventory grid displaying only medications expiring within the next 6 months, your Django dashboard view uses this fast query:
from datetime import timedelta
from django.utils.timezone import localdate
from .models import StockBatch
six_months_threshold = localdate() + timedelta(days=180)
# Instantly returns active short-dated stock records for checking lists
short_dated_stock = StockBatch.objects.filter(
inventory_item__practice=request.user.current_practice,
quantity__gt=0,
expiry_date__lte=six_months_threshold
).select_related('inventory_item')
When a dispenser hands out a box of medicine to a patient, the system updates the batch metrics backwards using the FEFO rule. Your backend script iterates through the active batches, draining the oldest expiry date segments first.
def dispense_medication(inventory_item_instance, units_dispensed):
"""
Drains stock automatically from active lots following strict
First Expired, First Out parameters. Atomic data flow execution.
"""
remaining_to_dispense = units_dispensed
# Get all active, unexpired batches ordered by soonest expiry
active_batches = inventory_item_instance.batches.filter(
quantity__gt=0,
expiry_date__gt=localdate()
).order_file_list() # Inherits meta ordering: ['expiry_date']
for batch in active_batches:
if remaining_to_dispense <= 0:
break
if batch.quantity >= remaining_to_dispense:
batch.quantity -= remaining_to_dispense
remaining_to_dispense = 0
batch.save()
else:
remaining_to_dispense -= batch.quantity
batch.quantity = 0
batch.save()
# Always trigger recalculation metrics on completion
inventory_item_instance.recalculate_stock_metrics()
Would you like to explore how to write the daily automated Celery task that runs every morning to compile the short-dated status report and flag alerts on the practice dashboard?
Which part of this local reporting architecture should we configure next?