MedStock IQ
Smarter inventory. Intelligent distribution. An AI copilot that never acts without approval.
A case study on how I designed and built MedStock IQ, a multi-tenant ERP for pharmaceutical wholesalers, stockists and hospital pharmacy chains. It combines a deterministic core (strict FEFO dispatch, cold-chain and credit checks, and a double-entry inventory ledger) with MedStock Assist, a multimodal AI copilot that reads supplier bills, takes voice input and drafts actions a human must approve.
Project Snapshot
The problem, the users and the approach at a glance
Wholesalers run on 3–7% margins while losing stock to expiry, cold-chain failures, bad credit and paper-based compliance.
Owners, CFOs and accountants, warehouse heads, sales executives, dispatch staff and chief pharmacists.
The rules that protect money and patients (expiry, temperature, credit, licences) must be deterministic code, not operator judgement.
An ERP core with four deterministic engines, batch-level genealogy, GST invoicing and an append-only audit trail.
MedStock Assist: bill OCR, voice dictation, 12+ typed ERP tools (no raw SQL) and two-phase action proposals.
Next.js 14, NestJS 10, Prisma 5.22 on Neon PostgreSQL 16, Entra ID, and Azure OpenAI / Gemini / OpenAI.
Why I Built MedStock IQ
Pharmaceutical wholesale looks like ordinary distribution, but every unit has an expiry date, some need 2–8 °C storage, buyers often buy on credit, and regulators expect a paper trail for every movement. Generic ERPs treat medicines like any other SKU, and the gaps show up as write-offs, bad debt and failed audits.
FIFO or manual picking leaves older batches in the corner of the warehouse until they expire and have to be written off.
Vaccines, biologics and insulin must stay between 2 °C and 8 °C. One wrong zone can ruin an entire lot.
Sales teams keep booking orders for pharmacies already over their credit limit, which turns into bad debt.
21 CFR Part 11, Drug Licence Forms 20B/21B, Schedule H/H1 registers and GST all need traceable records.
Supplier bills are typed in line by line at the loading dock, which is slow and error-prone.
An assistant that writes SQL or edits stock on its own is a liability when the data is medical and financial.
“Make it impossible to ship an expired, mis-stored or unpaid-for medicine by accident, and give every operator an AI assistant that proposes rather than acts.”
Understanding the Problem Before Writing Code
I began by mapping the full wholesale lifecycle (procure, inward, store, sell, invoice, collect) and the six roles that touch it. Each role's pain point became a requirement, and each regulatory obligation became a rule the system has to enforce.
Lifecycle Mapping
Traced PO → GRN → batch & bin → FEFO sales order → GST invoice → payment → audit.
Persona Analysis
Defined six personas with their focus, the modules they use and the value they need.
Regulatory Review
Listed 21 CFR Part 11, Form 20B/21B, Schedule H/H1 and GST invoicing obligations.
Risk Scenarios
Credit over-limit orders, near-expiry batches, cold-chain mis-slotting and recalled lots.
Who I Was Building For
| STAKEHOLDER | PAIN POINT | WHAT THEY NEED FROM THE SYSTEM |
|---|---|---|
| CEO / Owner | No clear view of margin by customer, manufacturer or SKU. | Executive KPIs and true batch-cost (COGS) margin reporting. |
| CFO / Accountant | Bad debt and manual GST reconciliation. | Automatic credit-ceiling locks, a payment ledger and GST summaries ready for GSTR-1/3B. |
| Warehouse Head | Expired stock and slow picking. | Strict FEFO, rack/shelf/bin mapping, cold-chain zoning and quarantine. |
| Sales Executive | Can't tell if an order will ship or be blocked. | Instant credit status and a FEFO allocation preview while booking. |
| Dispatch Staff | Picking the wrong batch or bin. | Picklists bound to exact batch and bin coordinates. |
| Chief Pharmacist | Audit and licence risk. | An immutable audit trail, drug licence checks and Schedule H/H1 registers. |
- ✓PO lifecycle (Draft → Received)
- ✓GRN with batch, expiry, MRP & bin
- ✓Sales orders, picklists, dispatch
- ✓GST invoices (CGST/SGST/IGST, HSN)
- ✓Payments: NEFT, RTGS, cheque, cash, UPI
- ✓Batch-level genealogy
- ✓Strict FEFO allocation
- ✓Warehouse → Zone → Rack/Shelf/Bin
- ✓Cold-chain 2–8 °C validation
- ✓Quarantine & recall
- ✓Expiry horizon tiers (<30, 31–60, 61–180, >180 days)
- ✓Financial value-at-risk
- ✓Reorder triggers
- ✓Dead-stock & aging analytics
- ✓Natural-language queries
- ✓Bill / PDF / image / CSV ingestion
- ✓Voice dictation
- ✓Two-phase action proposals
- ✓Executive command centre
- ✓True COGS & gross margin
- ✓GST tax summary
- ✓21 CFR Part 11 audit trail
- ✓Multi-tenant isolation
- ✓Entra ID SSO + RBAC
- ✓JWT access/refresh tokens
- ✓OpenAPI docs
Integrity
Quantities never change in place. Every movement is a signed ledger entry inside an ACID transaction.
Compliance
An append-only audit log with before/after JSON snapshots, user, IP and timestamp.
Tenant isolation
Every operational table is scoped by organizationId on every query.
AI safety
No raw SQL, input guardrails, and human approval before any write.
Vendor independence
AI providers are hot-swappable, and their keys are encrypted with AES-256-GCM.
Type safety
TypeScript end to end, with shared domain types from UI to database.
Translating Requirements Into Product Decisions
The next step was deciding what each persona should actually see and be stopped from doing. Most of MedStock IQ's value comes from what it prevents, so I turned each complaint into a guard or a view.
“We keep finding expired stock at the back of the rack.”
Picking can't be left to the operator.
A strict FEFO engine that allocates from the earliest viable batch and skips anything inside the minimum shelf-life window.
“Sales booked a big order for a pharmacy that already owes us.”
Credit limits must be enforced when the order is booked, not after it ships.
A credit ceiling check: if outstanding balance plus the new order exceeds the limit, the order is locked until Finance overrides it.
“Someone put insulin in the ambient zone.”
Storage rules must be physically impossible to break.
A cold-chain check that rejects any 2–8 °C product movement into a non-refrigerated zone.
“Typing supplier bills takes forever.”
Inwarding needs to be much faster, without trusting unchecked AI.
Multimodal bill parsing that matches SKUs and suppliers, then presents a draft purchase order for approval.
“I don't want the AI touching our books.”
The AI should draft, and a human should decide.
Two-phase action proposals: a dry run with a diff, then “Approve & Execute”, all through typed tools with no SQL.
“What's actually about to expire, in rupees?”
Expiry risk should be shown as money, not just dates.
Expiry horizon tiers plus value-at-risk: Σ (quantity × purchase price) across risky batches.
The User Journey I Designed For
Sign in
Microsoft Entra ID SSO with role-based access.
Inward goods
Receive a purchase order through GRN, capturing batch, expiry and bin, with a barcode printed.
Book an order
Credit status is checked first, then batches are allocated FEFO and shown before submitting.
Pick, pack & invoice
A bin-accurate picklist, then a GST invoice with batch numbers on every line.
Watch the risk
The command centre shows valuation, receivables and expiry value-at-risk.
Ask MedStock Assist
Upload a bill or dictate a request, then review and approve the proposal.
How the Architecture Was Designed, Step by Step
I designed the architecture outward from the data. First the ledger and the rules that can never be broken, then the API that enforces them, and only then the AI layer, which is only allowed to go through that API's own services.
How do we pass an audit of every unit's history?
Every movement (purchase, sale, returns, adjustments, damage/expiry, transfers) writes a signed entry, so the new balance is always the previous balance plus the change.
Quantities are always derivable and replayable, and never silently edited.
Where should the rules that protect money and safety live?
Four service-layer engines: strict FEFO, expiry horizon & VaR, credit ceiling, and cold-chain storage checks.
The same input always gives the same answer, and each engine can be tested on its own.
How do several distributors share one deployment safely?
An indexed organizationId on every operational table, with tenant scoping applied to every Prisma query.
Complete data isolation without separate databases per customer.
Which stack keeps a large domain model consistent?
Next.js 14 and NestJS 10 in TypeScript, with Prisma 5.22 on Neon PostgreSQL 16.
Shared types from UI to database, fewer context switches, and good concurrency on Node.js.
How is every request authenticated and validated?
NestJS guards (JWT + RBAC), class-validator pipes, CORS, transform interceptors and auto-generated OpenAPI docs.
Nothing reaches a domain engine without being authenticated, authorised and validated.
How can an LLM help without corrupting data?
The AI can only call 12+ typed ERP tools that wrap the same services the UI uses, and every input first passes pharma guardrails.
Removes SQL injection and hallucinated queries, and the AI inherits every business rule.
Who has the final say on a write?
Phase 1 runs the tool as a dry run and stores an ActionProposal. Phase 2 executes only after the user clicks “Approve & Execute”.
Human-in-the-loop by construction, with a reviewable diff.
What if a model is down, too expensive, or not allowed?
A provider router across Azure OpenAI (GPT-4o), Google Gemini 1.5 and OpenAI, with keys encrypted in a CryptoVault (AES-256-GCM).
No vendor lock-in, and credentials stay safe even inside database backups.
Layered System Architecture
Client & Presentation
Next.js 14 App Router
API Gateway & Security
NestJS 10 / Express
Deterministic Business Engines
Service layer
MedStock Assist AI
Copilot architecture
Cloud Persistence
Neon Serverless PostgreSQL 16
Wholesale Lifecycle Through the System
The Engines and the AI, Up Close
The four engines that enforce the rules, the ledger underneath them, and the path an AI request takes before anything is written.
Strict FEFO Allocation Engine
The Other Three Engines
Δt = expiry − today
Critical ≤ 30 days · near-expiry 31–60 · medium 61–180 · safe > 180. VaR = Σ quantity × purchase price over risky batches.
Outstanding + new order > limit?
If yes, the order stays SUBMITTED, is marked credit-blocked, and dispatch halts until a Finance Manager overrides it.
2–8 °C product → refrigerated zone only
Any inward receipt or move of cold-chain stock into a non-refrigerated zone is rejected with a clear error.
Form 20B / 21B validity
If a customer's drug licence has expired, invoice dispatch is blocked automatically.
One click, every location
A QUARANTINED batch is removed from all picklists and sales allocation immediately.
COGS from actual batch cost
Gross margin uses each batch's real purchase price, not a moving average.
Double-Entry Inventory Ledger
| Transaction | Effect | Sign | Source document |
|---|---|---|---|
| PURCHASE | Stock increase | + | Goods Receipt (GRN) |
| SALE | Stock decrease | − | Sales order / invoice |
| PURCHASE_RETURN | Stock decrease | − | Debit note |
| SALES_RETURN | Stock increase | + | Credit note |
| STOCK_ADJUSTMENT | Reconciled variance | ± | Physical stock count |
| DAMAGE / EXPIRY | Stock decrease | − | Quarantine record |
| TRANSFER_IN / OUT | Re-location | ± | Inter-warehouse transfer |
MedStock Assist — Request to Approved Action
Typed ERP Tools the AI Can Call
| Tool | What it does |
|---|---|
| queryInventory | Live stock, warehouse coordinates and available quantities. |
| checkNearExpiryExposure | Batches expiring within 30/60/90 days, with value-at-risk. |
| createSalesOrderWithFefo | Draft sales order with automatic FEFO batch allocation. |
| createDraftPurchaseOrder | Purchase order from reorder thresholds and supplier terms. |
| ingestSupplierInvoice | Parses a supplier bill and matches line items to SKUs. |
| liquidateNearExpiryStock | Proposes markdown orders for stock nearing expiry. |
| getCustomerCreditReport | Balance, credit limit and payment history. |
| getGrossMarginReport | Profitability using true batch COGS. |
Data Model — 10 Functional Domains
| Domain | Tables |
|---|---|
| Tenancy & RBAC | organizations, users, roles, permissions, role_permissions |
| Catalog | products, categories, manufacturers |
| Spatial warehouse | warehouses, warehouse_zones, warehouse_locations |
| Inventory | batches, inventory_transactions |
| Purchasing | suppliers, purchase_orders, purchase_order_items, goods_receipts |
| Sales | customers, sales_orders, sales_order_items, batch_allocations |
| Billing | invoices, sales_returns, purchase_returns |
| Approvals | stock_adjustments, approval_requests |
| Audit & telemetry | audit_logs, notifications, payments |
| AI memory | ai_conversations, ai_chat_messages, ai_settings |
Tech Stack & Why Each Piece Was Chosen
Next.js 14 · React 18 · Tailwind
App Router SSR, fast route transitions and an enterprise slate/emerald theme.
TanStack Query · React Hook Form · Zod
Optimistic updates, cached fetching and type-safe forms.
Recharts
Command-centre KPIs and expiry distribution curves.
NestJS 10 · Express
Modular domain design with dependency injection, guards and pipes.
Prisma 5.22 · Neon PostgreSQL 16
Typed models and migrations on serverless Postgres with PgBouncer pooling.
Entra ID (MSAL) · JWT · bcrypt · AES-256-GCM
SSO, rotating access/refresh tokens, hashed passwords and encrypted AI keys.
Azure AI Inference · Gemini SDK · OpenAI SDK
Swappable GPT-4o, Gemini 1.5 Pro/Flash and OpenAI.
Web Speech API
Hands-free dictation on the warehouse floor, straight into the Assist dock.
Playwright
Headless end-to-end regression, plus headed runs for recorded walkthroughs.
Building It Phase by Phase
I built MedStock IQ in the same order the architecture was designed: data first, then the rules, then the workflows, and the AI last. That way the AI could only reuse rules that were already tested.
- 1
Multi-tenant schema & ledger
Goal: A data model that can pass an audit.
- •Prisma schema across 10 domains
- •organizationId scoping on every table
- •inventory_transactions double-entry ledger
Output: A tenant-safe, auditable database foundation.
- 2
Gateway, identity & RBAC
Goal: Every request authenticated and validated.
- •Entra ID (MSAL) SSO + JWT access/refresh
- •RBAC guards and validation pipes
- •OpenAPI docs at /api/docs
Output: A secure, documented API surface.
- 3
Procurement & GRN inwarding
Goal: Controlled entry of stock.
- •PO status lifecycle
- •GRN capture of batch, expiry, MRP and bin
- •ACID transaction: batch + ledger + PO update
Output: Every batch traceable to its PO and GRN.
- 4
Deterministic engines
Goal: Encode the rules that can't be broken.
- •Strict FEFO with shelf-life cutoff
- •Expiry horizons & VaR
- •Credit ceiling, cold-chain and drug licence checks
Output: Four tested engines shared by the UI and the AI.
- 5
Sales, invoicing & payments
Goal: Order to cash.
- •FEFO allocation preview while booking
- •Picklists, packing slips, dispatch
- •GST invoices and the payment ledger
Output: A full commercial cycle with batch numbers on every invoice line.
- 6
Insights & compliance
Goal: Visibility for owners and auditors.
- •Command centre KPIs
- •True-COGS margin & GST summary
- •Append-only 21 CFR Part 11 audit log
Output: Executive and regulatory reporting.
- 7
MedStock Assist
Goal: A safe multimodal copilot.
- •Provider router + CryptoVault
- •Guardrails, zero-SQL tools and bill OCR
- •Two-phase proposals and voice dictation
Output: An AI that drafts and a human who approves.
How I Verified It Works
I tested the deterministic core as rules with exact expected outcomes, and ran the whole product end to end with Playwright on every change.
End-to-end regression (Playwright)
Headless runs (npm run test:headless) cover the critical user journeys.
- ✓Login and role-based navigation
- ✓FEFO simulation and order creation
- ✓Invoicing flow
- ✓GRN inwarding
Business rules
Each engine is checked against the edge cases it exists to catch.
- ✓FEFO skips batches inside the shelf-life cutoff
- ✓Credit-exceeded orders lock at SUBMITTED
- ✓Cold-chain stock into an ambient zone is rejected
- ✓Quarantined and expired-licence cases are blocked
Ledger & tenancy
The data must always stay consistent and isolated.
- ✓Balances equal the sum of ledger entries
- ✓GRN writes batch, ledger and PO in one transaction
- ✓Queries never cross organizationId
- ✓Audit log is append-only
AI safety
The copilot must be unable to cause harm on its own.
- ✓No write happens without Approve & Execute
- ✓Guardrails reject negative stock and impossible dates
- ✓Only typed tools are callable, never raw SQL
- ✓Provider keys are stored encrypted
Success Criteria → How Each Was Verified
| CRITERION | VERIFIED BY |
|---|---|
| Expiry control | FEFO simulation tests across multiple batches and shelf-life cutoffs. |
| Credit discipline | Order-booking scenarios above and below the credit limit. |
| Cold-chain safety | Rejected inward/move attempts into non-refrigerated zones. |
| Traceability | Following a unit from PO → GRN → batch → invoice → audit log. |
| AI human-in-the-loop | Proposal flows that only commit after explicit approval. |
| Regression safety | Playwright headless suite on login, FEFO, orders and invoicing. |
Shipping It
MedStock IQ ships as two Node 20 containers behind a TLS reverse proxy, with Neon PostgreSQL as the database. It can run on Azure, AWS, GCP or on-premise.
Provision Neon PostgreSQL
A pooled connection (PgBouncer) for the app and a direct URL for Prisma migrations.
Run migrations & seed
Apply the Prisma schema and seed the organisation, roles, warehouses and catalog.
Configure secrets
JWT secret and expiry, CORS origin, Entra ID app registration, and AI provider keys in the vault.
Build containers
Next.js webapp (port 3002) and NestJS API (port 4000) on Node 20 Alpine.
Expose via TLS proxy
Cloud ingress with TLS 1.3 routes the web and API hosts.
Verify
Check the OpenAPI docs at /api/docs, then run the Playwright suite against the environment.
Local development
Webapp and API run locally against a Neon branch, with seeded demo data for fast iteration.
Cloud deployment
Containerised web + API behind TLS on Azure, AWS, GCP or on-premise Linux.
# backend/.env PORT=4000 DATABASE_URL="postgresql://<user>:<password>@<pooler-host>/medstock?sslmode=require" DIRECT_URL="postgresql://<user>:<password>@<direct-host>/medstock?sslmode=require" JWT_SECRET="<random-32+-chars>" JWT_EXPIRES_IN="1d" JWT_REFRESH_EXPIRES_IN="7d" CORS_ORIGIN="https://erp.yourdomain.com" # webapp/.env.local NEXT_PUBLIC_API_URL="https://api.yourdomain.com/api" # Quality gates npm run test:headless # Playwright end-to-end regression npm run demo:headed # recorded product walkthrough
What I Learned & What's Next
Put the rules that protect money in code.
FEFO, credit and cold-chain checks work because operators can't bypass them, and they're applied the same way every time.
A ledger is the audit.
Writing every movement as a signed entry made compliance and debugging the same job.
Give AI tools, not access.
Typed tools mean the copilot inherits every business rule and can't write SQL.
Proposals beat autonomy in regulated domains.
A two-phase “draft, then approve” flow made the AI safe enough to use on real stock and money.
Stay provider-agnostic.
A small router and an encrypted vault turned model choice into configuration.
Automate the demo, too.
The same Playwright harness that tests the product also records a repeatable walkthrough.
Roadmap
Phase 1 — Core Foundation
Multi-tenant schema, algorithmic FEFO, credit lock, GST invoices and the audit trail.
Phase 2 — AI Expansion
Voice assistant, multimodal invoice OCR, automated reorder POs and WhatsApp inward bills.
Phase 3 — Connected Ecosystem
IoT cold-chain sensors, manufacturer EDI, a driver PWA and a B2B pharmacy portal.