Pharmacy Stock Ledger with Batch Expiry and FEFO Dispensing

The problem independent pharmacies have with batches and expiry

A neighbourhood pharmacy holds a few thousand products, many of them in several batches with different expiry dates. Stock arrives in boxes, is shelved as strips, and is often sold as a handful of loose tablets. Most small pharmacies track this in a paper register or in a general POS that only knows "quantity". The result is familiar to anyone who has worked a counter: an older batch expires at the back of the shelf while a newer one is sold, the system says there are 40 strips when there are really 37 and a half, and the monthly count never matches.

Expired stock is money lost, and dispensing it is a patient safety issue. Owners also struggle to return near-expiry stock to distributors in time because nobody knows what is expiring until it already has.

What already exists and the gap

• Sadahisab and OneClick POS are cloud POS products aimed at Pakistani medical stores, with batch tracking and expiry alerts.
• MoneyPex offers pharmacy POS with expiry alerts at 30, 60 and 90 days.
• Marg ERP is widely used by Indian chemists and distributors, but it is a large desktop ERP with a learning curve to match.

The commercial tools exist, so the gap is not "nobody does batches". The gap is transparency and correctness: most small products store a single mutable quantity per batch, so when numbers drift there is no way to explain why. Loose-tablet sales are frequently handled as a workaround. This project builds the inventory core the right way, as an append-only ledger with explicit units, and proves it with tests. That is also exactly what makes it a strong engineering portfolio piece.

What you build

A web app with three roles. The owner sets up the catalogue, suppliers and prices and reads the reports. The pharmacist receives purchases, handles returns and write-offs. Counter staff use a fast billing screen designed for a barcode scanner and keyboard.

Main flows:

1. Receive a supplier invoice: each line creates or tops up a batch, stored in base units (tablets, millilitres).
2. Sell: the cashier scans or searches, picks box, strip or tablet, and the system allocates from the earliest-expiring sellable batch, splitting across batches when needed.
3. Return and adjust: customer returns go back to the original batch; damaged or expired stock is written off with a reason.
4. Close the day: expected cash versus counted cash, with the difference recorded.

The technically interesting part

The heart of the project is the stock ledger. Instead of updating a quantity column, every change is an immutable movement row. On-hand stock per batch is a sum over movements, cached in a projection table that is rebuilt and checked nightly. This is the same double-entry thinking Stripe and Shopify describe for money, applied to tablets.

Two hard parts to show your evaluators:

• Unit conversion without rounding errors. A box of 10 strips of 14 tablets is 140 base units. Selling one strip is 14. Prices must be derived consistently so that selling loose never earns more or less than intended.
• Concurrent FEFO allocation. Two counters selling the last strips of a batch at the same time must not oversell. Use a database transaction with row locks on the batch projection, or a serialisable isolation level, and write a test that fires parallel sales to prove it.

How to evaluate it

• Correctness: a replay test of at least 5,000 synthetic transactions across 200 items where ledger-derived stock equals the projection for every batch. Report zero discrepancies or explain each one.
• Concurrency: run 50 parallel sale requests against a batch with 20 units and show exactly 20 succeed.
• Speed at the counter: time to bill a five-item sale with a scanner, measured with 5 real or role-play cashiers; aim for under 30 seconds.
• Usefulness: run the near-expiry report on a real pharmacy's anonymised stock list if one will share it, and count the items flagged in time for supplier return.

Suggested plan

1. Weeks 1 to 3: visit two pharmacies, map the purchase, sale and return flows, and design the schema with units and movements.
2. Weeks 4 to 8: catalogue, purchases, ledger and projections with tests first.
3. Weeks 9 to 12: billing counter with FEFO allocation and receipts.
4. Semester 2, weeks 1 to 5: returns, write-offs, near-expiry and closing reports.
5. Weeks 6 to 9: concurrency and replay tests, role permissions, audit trail.
6. Weeks 10 to 14: pilot at one pharmacy, fix what the pilot finds, write the report.

Path to a product

The first customer is a single independent pharmacy near your campus, ideally one owned by someone you know, where you can observe the counter for a week. Charge a flat monthly subscription per branch once it runs a full month without stock disputes. Distributors are a second market: they care about near-expiry returns too. Scale by adding branches and a distributor portal before adding more features.

Scope warning

Do not build prescription e-records, insurance claims, doctor portals or delivery. Do not integrate a payment gateway at the counter; record the payment method only. Do not attempt a drug interaction database. The value is in the ledger being right.

Learn what you need on Sefism

• Backend roadmap : transactions, schema design and API structure for the ledger.
• Full-stack cheat sheet : quick reference while wiring the billing screen to the API.
• PostgreSQL Documentation : read the chapters on transaction isolation and row locking before writing FEFO.
• Use The Index, Luke : the ledger grows fast, so index the movement table properly.
• Shopify Engineering : posts on inventory and money correctness at scale.

Get help with this idea

Most teams underestimate the ledger and overbuild the screens. Tauseef can help you tighten the scope, choose a stack your team can finish with, and design an evaluation your examiners will respect. If you are new here, create a free account first.

• Discuss this idea with a mentor
• Send a query to Tauseef
• Book a 1:1 session