StockAlert

Prototype

StockAlert tracks pharmacy inventory by batch and expiry date, dispenses stock first-expiry-first-out, and alerts before a batch expires. It was built for a friend, with clean layering and a real transactional core, and it has not yet been deployed to a live pharmacy, so there are no operational numbers to report on it.

Role: Sole author
Period: 2026
Status note: Built and tested. Not deployed to a live pharmacy.

The problem

A pharmacy tracking stock by hand or by spreadsheet has no reliable way to guarantee older stock is dispensed before newer stock, and no automatic warning before a batch expires unsold.

The constraint

Dispensing has to be correct under partial failure: an oversized request must fail cleanly with nothing deducted, not partially drain several batches and leave the inventory in an inconsistent state.

Architecture

React 19 and Vite on the frontend, with html5-qrcode for camera-based barcode scanning and JsBarcode for label generation. Node and Express 4 on the backend, Prisma 5 over SQLite, JWT auth with a requireRole middleware. The backend follows a strict layering: routes to controllers to services to utils, with one ApiError class and an asyncHandler wrapper replacing scattered try/catch blocks.

Decisions, and what they cost

First-expiry-first-out, checked before any mutation

Dispensing selects non-disposed batches with available quantity, ordered by expiry date ascending, sums availability across them, and throws a 400 if the requested quantity exceeds what's available, before touching any row. Only then does it walk the batches taking min(batch.quantity, remaining) from each, writing a stock-movement row per deduction that records the user, the reason, and the batch. The controller wraps the whole operation in a Prisma transaction and passes the transaction client down through the call, so the decrements and their audit rows commit together or not at all.

SQLite has no native enum

Roles, stock-movement types, and purchase-order statuses are string columns validated in the controller layer rather than constrained by the database itself. That is a documented trade-off of the SQLite choice, not an oversight, and the constraint lives in application code instead of the schema.

What didn't work

A defaulted transaction parameter that should be required

dispenseFefo(..., tx = prisma) takes the transaction client as a parameter with a default value. Every current caller passes a real transaction, so the atomicity guarantee holds today, but a future caller who forgets to pass one gets a silent, non-atomic dispense with no error to catch it. The fix is straightforward, make the parameter required rather than defaulted, and it hasn't been made yet because nothing has hit it in practice.

React StrictMode double-invoking camera effects

Barcode camera scanning broke under React StrictMode, which intentionally double-invokes effects in development to surface exactly this kind of bug: the camera stream was being initialised twice. Fixing it meant making the camera-setup effect properly idempotent rather than working around StrictMode.

Honest limits

No rate limiting and no helmet middleware. Tests cover FEFO dispensing, alert buckets, and purchase orders, but not auth, reports, or CSV handling. There are no frontend tests. It has not been deployed to a live pharmacy, so there are no usage or accuracy numbers to report here, and none are claimed.

Screenshots

StockAlert barcode scanning screen with a manual barcode field beside the camera scanner — The camera scanner, with a manual barcode field beside it. The manual path exists because the application had to stay demonstrable on a machine with no webcam, and it is the same lookup either way.

StockAlert dashboard showing expiry buckets: nine expired, nineteen expiring within thirty days, sixteen within ninety — Expiry is bucketed rather than binary: nine batches already expired, nineteen inside thirty days, sixteen inside ninety. The buckets are computed on read rather than cached, which costs a query on every dashboard load and buys the guarantee that the number is never stale.

StockAlert purchase orders list with one order in PARTIALLY RECEIVED status — A purchase order sits in PARTIALLY RECEIVED. A purchase order can be received more than once, each receipt creating its own batch with its own expiry date, without double-counting what arrived in the earlier delivery.

1 / 3

Stack

React 19
Vite
Tailwind 4
Recharts
html5-qrcode
JsBarcode
Node.js
Express 4
Prisma 5
SQLite
JWT
Vitest
Supertest