Configuring a Pharmacy POS with Batch Tracking and Expiry Alerts

A pharmacy with 3,200 SKUs and an average of 2.4 active batches per SKU means there are about 7,680 item-batch combinations to monitor every day. Without batch tracking, a POS system only knows "Amoxicillin 500 mg has 180 tablets left" — even though 60 of those tablets expire next month and another 40 tablets come from a batch withdrawn by the distributor due to recall. This is where many pharmacies lose 3–7% margin per year: dead stock, returns that cannot be claimed, to findings during inspections. This article dissects pharmacy POS configuration with batch tracking and expired alert concretely — data schema, FEFO logic during transactions, notification thresholds, through testing. The implementation example uses PostgreSQL 16, Laravel 11, and Redis 7, but the pattern applies to other stacks such as MySQL 8.0 or .NET 8.

Data Foundation: Batch, Lot Number, and FEFO Principles

Aggregate stock is the root of the problem. As long as POS stores one stock number per product, the warehouse team has no basis for determining which units should go out first. Batch tracking forces the system to record every goods receipt as a separate row: batch number, expiration date, quantity, purchase price, and originating distributor. The consequences are positive at many points at once — accurate COGS per batch, returns to distributors can be proven with the same batch number, and when there is a recall from BPOM you withdraw exactly the 12 affected boxes, not the entire shelf contents.

At minimum, four tables need to change. The products table still stores item identity. The stock_batches table stores batch_no, expired_date, qty_on_hand, cost_price, and supplier_id. The stock_movements table records every inbound/outbound movement along with its reference. The one most often forgotten: sale_items must have a batch_id column. Without it, sales history cannot be traced back to a specific batch when there is a complaint or audit.

FEFO query performance depends heavily on the composite index (product_id, expired_date). In a load test with 500 thousand batch rows on PostgreSQL 16, an allocation query without the composite index took 480 ms; after the index was added it dropped to 9 ms. For a pharmacy with 3,200 SKUs the data size is not large, but this query is executed thousands of times per day so the 470 ms difference is felt directly in the cashier queue.

When migrating from an old POS or Excel, do not move aggregate stock just like that. Perform an initial stocktake per batch: match batch numbers on the physical shelf with receipt data, then mark batches without documents as UNVERIFIED so they are not automatically sold before being verified by a pharmacist.

Automatic FEFO Allocation at the Cashier Desk

The cashier flow must remain three seconds per item, which is why batch selection is done automatically on the server. When a barcode is scanned, the system runs a single query: take the batch with the nearest expired_date that still has a balance, lock that row, then reduce qty_on_hand in the same database transaction. If the requested quantity exceeds the nearest batch's stock, the system takes the next batch until the requirement is fulfilled — this is cross-batch allocation.

A classic mistake is reading stock and then reducing it without locking. Two cashiers selling the same item at the same second can cause negative stock or a batch being used twice. On PostgreSQL 14 and above use FOR UPDATE SKIP LOCKED so the second transaction jumps to the next batch without waiting. On MySQL 8.0 use SELECT ... FOR UPDATE with READ COMMITTED isolation; on SQL Server use the UPDLOCK, ROWLOCK hint.

SELECT id, batch_no, expired_date, qty_on_hand
FROM stock_batches
WHERE product_id = 1024
  AND qty_on_hand > 0
  AND expired_date > CURRENT_DATE
ORDER BY expired_date ASC
LIMIT 5
FOR UPDATE SKIP LOCKED;

Take five rows at once so the application can fulfill cross-batch requirements in a single round-trip. The result is processed at the application level: decrement qty_on_hand per batch, write stock_movements of type SALE, and store batch_id in sale_items. For transaction cancellation, do not delete rows — create a reverse RETURN movement that returns stock to the original batch so the audit trail remains intact. Finally, add safeguards at the database level: a check constraint qty_on_hand >= 0 and a trigger that rejects inserts into sale_items if the batch's expired_date has passed. The application can have bugs; constraints do not.

Layered Expired Alerts and Automatic Quarantine

An effective expired alert is not merely a list of items approaching expiration on a dashboard. It must be layered and have operational consequences at every threshold. The pattern we apply in many pharmacies: a 90-day threshold for planning returns to distributors, 60 days for discount or bundling programs, and 30 days for physical quarantine — goods are moved from the sales shelf to a separate shelf and blocked from FEFO allocation.

Scheduled execution once a day is sufficient. In Laravel 11, register the command in routes/console.php with Schedule::command('pharmacy:expiry-scan')->dailyAt('06:00'). If not Laravel, pg_cron on PostgreSQL or Windows Task Scheduler is sufficient. For 3,200 SKUs, scan results are usually 40–120 rows; send it as a single daily summary to the WhatsApp group of the pharmacy manager and the Pharmacist in Charge, not per-item notifications that are actually ignored.

Add two mandatory reports: value of at-risk stock per therapy category, and return status already/not yet claimed to the distributor. The figure to monitor is the percentage of expired stock value against total inventory value — a healthy target is below 1.5%. If it is above 3% for two consecutive months, the root cause is usually not in the system, but in purchasing policy and order lead time.

Best Practices

1. Store batch_id in every sales line. Without this trace, mutation audits and batch recalls cannot be accounted for.
2. Lock batch rows during allocation. Use FOR UPDATE SKIP LOCKED and run stock deduction in a single transaction together with the sale_items insert.
3. Block expired batches at three layers: query filter, check constraint, and database trigger.
4. Schedule scan at 06.00 before the pharmacy opens, with daily summary output and automatic escalation for 30-day batches.
5. Create a physical quarantine SOP. Batches with quarantine status are moved to labeled shelves and must not appear in the cashier selection list.

FAQ

Is batch tracking still necessary for a small pharmacy with 400 SKUs?

It is necessary. The obligation to trace pharmaceutical supplies and the return process to distributors applies regardless of pharmacy size. For 400 SKUs, implementation can be simpler: one batch table, one FEFO query, and weekly scanning without a Redis queue.

How to handle a single batch whose stock is spread across two branches?

Add a location_id column to stock_batches so each row represents a batch at one location. FEFO queries are always filtered by the active cashier's location_id, and inter-branch transfers are recorded as TRANSFER_OUT/TRANSFER_IN mutations with the same batch number.

What is the ideal expired alert threshold?

Follow your distributor's return lead time, generally 30–60 days before ED. A 90/60/30-day threshold is a safe starting point; adjust after three months of historical data have been collected.

Can it be applied to an already running POS without a full migration?

It can be done gradually. Add the batch table and a nullable batch_id column, backfill from receiving data for the last 6–12 months, enable FEFO allocation for high-risk product categories first, then make batch_id mandatory after stocktaking is complete.

Configuring batch tracking and expired alerts is not a one-off project — it touches data schema, transaction logic, and warehouse SOPs at once. Start with indexes and FEFO queries, add safeguards at the database level, then build layered alerts that the team actually reads. If your team in Yogyakarta, DIY, or Magelang is preparing a pharmacy POS, SIM Klinik, or ERP integration and wants to map batch and return flows before coding begins, we offer IT consulting and application development sessions to review the data schema and draft a phased implementation plan.

This article is part of the knowledge base of Nugroho Setiawan, an IT consultant in Jogja (Yogyakarta). Related solutions: PilarPOS Clinic Management System BPJS & SatuSehat Integration. Need a free consultation?