The Library › Vol. I · Case study · In production since 2026
Pharma ERP
The operating system of a pharma distribution business, where every permission and every status change is enforced in Postgres, not just hidden in the UI.
By Youbal Lakandri · Interactive edition
Most business software draws a line between what the screen shows and what the server allows, and then quietly trusts the screen. Pharma ERP does not. Each of its sixteen roles, from a lead operator to the managing director, logs in as a real database user, and Row Level Security decides row by row what that person may read or write.
Work moves through five lanes: leads, customer orders, third-party manufacturing, restock purchase orders, and an in-house factory line. Every move from one status to the next passes a database trigger that checks the role, validates the transition and stamps who did it and when. Nothing the browser sends is taken on faith.
The rest of this edition is interactive. The system map below is drawn from the real statuses and rules: pick a role to see what it may do, press play to watch an order travel, and step through the QR sign-in handshake. The code shown along the way is sketched for this page, simplified to show the idea rather than copied from the system.
§ Try the app
The real interface, with made-up data
A working replica of the ERP's own screens: pick any of its 16 roles and click through that role's menu. Approve an order sheet, bill it from stock (oldest expiry first), move a lead, confirm a factory stage, pass a batch in QA. Parties, products and numbers are invented; the layout, flows and rules are the app's.
Dashboard
Orders this month
22
17 still moving
Order value
₹10,57,694
all order types
Awaiting approval
2
Delayed
3
past expected delivery
Monthly target
₹10,57,694 / ₹15,00,000The truck drives across as the month's billed value grows. 71% of target.
Latest orders
| Order | Party | Product | Value | Stage | Progress |
|---|---|---|---|---|---|
| CT-2400 Contract · A. Verma | Shree Medicos Jaipur | Paracetamol syrup 60 ml 200 units | ₹8,000 | Draft sheet | |
| FR-2403 Franchise · N. Khan | City Pharma Agencies Lucknow | Omeprazole capsules 20 mg 700 units | ₹15,919 | Sheet approved | |
| FR-2406 Franchise · S. Iyer | Green Cross Distributors Indore | Calcium tablets 500 mg 1,200 units | ₹23,838 | Billing | |
| CT-2409 Contract · P. Das | Nova Health Patna | Antacid suspension 170 ml 1,700 units | ₹31,757 | Billing | |
| FR-2412 Franchise · A. Verma | Sunrise Medical Hall Nagpur | Vitamin D3 sachets 400 units | ₹39,676 | Packing late | |
| FR-2415 Franchise · N. Khan | Apex Pharma Guwahati | Cough syrup 100 ml 900 units | ₹47,595 | Cancelled |
Best on a laptop screen. Menu wording is simplified for a public page; nothing here connects to the real system.
§ 01 / Architecture
The database is the rulebook
The UI decides what to show you. Postgres decides what you're allowed to do. Every piece is real: no demo mode and no mock data.
Click a node to see its role.
§ 02 / The whole system
16 roles, five lanes, one rulebook
Leads, PCD orders, 3rd Party orders, restock POs and the factory floor, drawn from the real status enums and database triggers. Pick a role to see exactly what it may do, click any status for its rules, or press play.
-- sketch: who may move an order from one status to the next
if status_changed then
if role in ('officer') and old = 'review' and new in ('billing', 'rejected') then ok;
elsif role in ('stock') and old = 'billing' and new = 'billed' then ok;
elsif role in ('warehouse') and old = 'billed' and new = 'shipped' then stamp_who_and_when();
elsif role in ('rep') and old = 'shipped' and new = 'done' then require(tracking_no);
else raise 'not allowed for this role';
end if;
end if;§ 03 / Leads, up close
LRM: from a buy-lead to a phone call
The newest module. On the phone companion a rep calls straight through IndiaMart's tracked link and opens WhatsApp in one tap; every call outcome feeds the redial rules and the KPI desk, which breaks results down by portal and by rep. Make a call and see how the lead moves.
LRM · My leads
R. Sharma
Jaipur · IndiaMart · Portal A
Paracetamol syrup 60 ml, ~500 units
call attempts: 0
phone companion · locked to LRM · works off office Wi-Fi
63%
pick rate
68%
details rate
43%
rate → conv.
weekly lead target · IndiaMart · A
54 / 60 leads · 10 junk
§ 04 / Factory floor
The production board
On the factory floor a product's stages come from its dosage form: tablets are compressed, coated and stripped; liquids are filled, sealed and labelled. This is a working model of the ERP's own board, with simulated batches. Add batches, run a shift, and watch the middle stages wait for a Start and a Stop.
Production pipeline
simulated batches · shift 0◖◗tablet
⬭capsule
💧liquid
▭sachet
Dispatch
Click a stage to see its batches. ⚙ = started and in process (middle stages need Start, then Stop).
§ 05 / Inventory
FIFO stock that can't double-spend
Billing consumes real batches, oldest expiry first, inside one locked transaction. Each allocation snapshots the batch number and expiry, so undoing a bill restores exactly what was taken.
// sketch: take stock oldest-expiry-first, inside one locked transaction
function billFromBatches(order, batches) {
for (const line of order.lines) {
let needed = line.qty;
for (const batch of oldestFirst(usable(batches, line.product))) {
const take = Math.min(batch.qty, needed);
batch.qty -= take;
recordAllocation(order, batch, take); // snapshot, so an undo is exact
needed -= take;
if (needed === 0) break;
}
if (needed > 0) throw new Error("not enough stock"); // whole bill rolls back
}
}The edge case that mattered
Pharma stock stays good through its expiry month, and the accounting system sends “no expiry” as the date 1900-01-01. A naive expiry < today check would write off this month's stock and hide every batch that had no date.
// sketch: medicine is good THROUGH its expiry month, and
// "no date" arrives as a far-past placeholder, not a real date
function isExpired(expiry) {
if (!expiry || isPlaceholder(expiry)) return false; // unknown isn't expired
return expiry < firstDayOfThisMonth(); // local time, not UTC
}Knowing when to reorder
The Stock Calculator shows live usage from the accounting system and days of stock left. The simple rule can't see seasons, so a forecast reads last year's shape: January looks calm, March triples. Drag the lead time and watch the two answers split.
Stock calculator
illustrative product · it's JanuaryFlat rule (days remaining)
200 days
looks fine
Seasonal forecast
80 days
Order now: the spring spike empties the shelf before new stock would land
Last financial year, by month. The forecast uses its shape, not its volume, so a growing business doesn't look seasonal.
// sketch: drain stock month by month by seasonal demand
const shape = lastYear.map((m) => m / average(lastYear)); // shape, not volume
let stock = onHand;
for (const month of monthsAfter(today + leadTime)) {
stock -= currentPace * shape[month];
if (stock <= 0) return `reorder now: runs out in ${month}`;
}
return "covered";§ 06 / Pricing
A rate finder reps can't reverse-engineer
Reps used to message Purchase for a price on every lead. Now they get an opening rate, a floor, and a second-round rate that has to be earned by order size or by the product's real history. Type what the lead offers and it stamps a verdict. Switch to the rep's view to see what gets stripped: any margin next to a rate would reveal the cost.
Rate Finder
Example Syrup 100 ml · illustrative numbers- Open at
- ₹77.00
- Floor (1st quote)
- ₹69.44
- 2nd round
- not available
- Past sales used
- 22
privileged fields
████████████ stripped by stripForRep(): cost and every margin %
near miss
68
floor
69
ask
77
offer ₹72
You can accept this
Inside your range. No approval needed.
// sketch: suggest rates from real sales, then strip the reasoning
const history = pastSales(product).filter(isRealSale);
if (history.length < MIN_SALES) return { askPurchase: true };
const size = qty / median(history.map((s) => s.qty)); // "large" is per product
const guidance = {
ask: rateFor(median(margins(history)) - discountFor(size)),
floor: rateFor(POLICY_FLOOR),
secondRound: earned(size, history) ? rateFor(LOWER_FLOOR) : null,
};
return forRep(guidance); // rates only: any margin % would reveal the cost§ 07 / Ops
An office that reports its own address
Some accounts may only sign in from the office, but the office line has a dynamic IP. Instead of an admin fixing the allowlist every time it changes, a PowerShell task on an always-on office PC reports in every five minutes, and the server records the address it sees. If the reports stop, the restriction switches itself off rather than enforcing a stranger's address. Try breaking it.
Office beacon
simulated · RFC 5737 addresses09:00office line right now
203.0.113.24
office_networks (source = 'auto')
203.0.113.24/32
last heartbeat 0 min ago
office-only restriction
ENFORCED
lapses if no heartbeat for 120 more min
who can sign in
✓ staff in the office
✓ outsiders kept out
PS> Get-Content $env:ProgramData\OfficeBeacon\beacon.log -Tail 7
2026-09-24 09:00:00 OK office address unchanged (203.0.113.24)# sketch: every few minutes, tell the server "the office is here"
# the body is empty on purpose: the server records the address it SEES
Invoke-RestMethod -Method Post -Uri $BeaconUrl `
-Headers @{ 'x-beacon-key' = $BeaconKey } -Body '{}'
# registered with two triggers: once-now-and-repeat, AND at startup,
# so a Windows-update reboot doesn't quietly stop the loop§ 08 / Auth UX
Scan to sign in, WhatsApp-Web style
Staff sign in to shared desktops by scanning a QR code with their already signed-in phone. Step through the handshake.
The desktop asks for a one-time token, stored hashed with a short TTL.
// sketch: pull a token out of the scan, never follow the scanned link
function tokenFromScan(scanned) {
const url = tryParseUrl(scanned);
if (!url || url.origin !== thisSite) return null;
return url.pathname.match(TOKEN_PATH)?.[1] ?? null;
}§ 09 / Latest edition
Just shipped
From the latest weeks of commits, roughly fifty since the end of September: the app keeps changing as the business does.
A "What's new" log
After an update, a pop-up tells each person what changed for them, with a bell and a history of past updates.
Near-instant hand-overs
An order passed between accounts now shows up on the other side almost at once, and the factory tables refresh live too.
Packing with a timer
QA packing runs Start, then Hold or Stop, then Confirm, and logs the packing time. Orders can be packed in parts, with a packed and remaining quantity beside each batch.
Third-party orders, deeper
Expected delivery can be a month only, a party's product history can be exported, and POs to one manufacturer can be printed together.
Rate checks, round two
A rate request carries quantity, packing and remarks, collects offers from several manufacturers, and has its own My Rate Checks page.
Credit parties
A new CRM view shows each party's live outstanding balance from the accounting system and its payment terms.
§ 10 / In the next edition
What's being built next
A CRM for customer planning
Weekly planning already runs in the ERP today: reps plan each party week by week, set targets, and track what's achieved against them, with a monthly roster on top. Next, that planning moves out of the order screens into a dedicated CRM, built around the customer rather than the order.
Pre-written WhatsApp messages
WhatsApp already opens a chat from any lead in one tap. Next, the message arrives pre-filled from the lead itself (name, product and quantity) so a rep's first reply is consistent and takes seconds.
§ 11 / Takeaways
What building it taught me
01
Enforce in the database
Hidden buttons are UX. RLS and triggers are security. Every rule exists in both places, and the database wins when they disagree.
02
Model the real world
The warehouse checkbox, the write-once docket number, expiry by month: each one came from watching how the business actually runs.
03
Docs are a feature
A 'read this first' file, a 180 KB changelog and hundreds of commented migrations keep an app this size workable.