Trusted Tech Leader/ Case studies

Case study · Business systems

Multi-tenant pharmacy ERP and POS

A multi-tenant pharmacy ERP with double-entry accounting, batch-level FEFO (first expired, first out) inventory and an offline-capable retail point of sale.

Sector
Pharmacy retail and distribution
Category
Business systems
Maturity
Phases 1-3 complete (accounting, inventory and invoicing, point of sale), plus PDFs, aging reports, a dashboard, fiscal periods and brand/branch management. Tax-authority integration and notifications are not built.

At a glance

Who it's for
Pharmacy owners, distributors, accountants, cashiers and inventory staff.
Core stack
Next.js, FastAPI, PostgreSQL, Celery
What sets it apart
Pharmacy-specific stock and expiry mechanics sitting on a tamper-resistant ledger, with tenant isolation enforced in the database itself.
On this page

What it looks like in use

The till screen a cashier works from, including what it shows when the shop loses its internet connection.

Pharmacy till: Riverside branch
Riverside branchShift open Offline, sales are being saved
Tap a product to add it
Paracetamol 500mg
Rs 3.50
Cough Syrup 120ml
Rs 245
Multivitamin Adult
Rs 22
Domperidone 10mg
Rs 6
Ibuprofen 400mg
Rs 5
Metformin 500mg
Rs 4
Cart
Paracetamol 500mg
Rs 3.50 each, quantity - 2 +Rs 7.00
Cough Syrup 120ml
Rs 245 each, quantity - 1 +Rs 245.00
SubtotalRs 252.00
TaxRs 3.50
TotalRs 255.50
Charge Rs 255.50
Waiting to send
Sale saved on this tillQueued
Sale saved on this tillQueued
Sale saved on this tillQueued
They are sent when the connection returnsAutomatic
Behind the scenes
Stock close to its expiry date is picked up by a daily checkDaily check
When a sale is posted, the soonest-expiring batch is used firstAutomatic
Illustrative screen sample products and amounts, not client data

Real screens from the working product (sample data)

A till screen with product buttons, a sale in progress and a running total.
Real screen, sample data The till screen: tap products to build a sale and see the total. Sample products and prices.
A table of stock batches sorted by soonest expiry, filtered to items expiring within 90 days.
Real screen, sample data Stock batches listed with the soonest expiry first, filtered to items expiring within 90 days. Sample stock.

A day at the till

  1. Maria rings up a sale

    She taps the products on the till and takes payment, the same as on any modern counter.

  2. The internet drops, selling carries on

    The till saves each sale on its own and keeps a queue, so customers are never turned away.

  3. Stock with the nearest expiry goes first

    When a box is sold, the stock that expires soonest is taken first, so medicines are used before they expire.

  4. The books balance on their own

    Every sale updates the accounts automatically, and the system refuses any entry that does not add up.

  5. Each pharmacy sees only its own data

    Each pharmacy's data is kept separate, enforced in the database itself.

The challenge

Pharmacies and distributors need stock tracked by batch and expiry, packs sold loose or by box, trade schemes applied at invoicing, and books that reconcile to the ledger.

Generic ERPs rarely handle expiry-driven picking or recoverable manufacturer claims, and retail counters cannot stop selling when the connection drops. The brief also required one login to span several businesses, such as an accountant serving several pharmacies. Roles are strict: cashiers must not see profit and managers must not see equity. The design must also be able to serve other variable-unit industries later.

The solution

A Next.js frontend talks to a FastAPI service over PostgreSQL. Every tenant table carries a tenant id and is protected by row-level security set per request from the login token, so isolation does not rely on application filters alone. Database triggers protect financial postings.

Inventory, sales and POS flows all post balanced journal entries atomically. The retail terminal caches the catalogue in the browser and queues sales locally for later sync. Billing is provider-agnostic, with manual plan activation for the first release.

For your technical teamhow it works under the hood

Mechanism: an offline sale reaching the ledger

Offline POS sale reaching the ledgerSequence diagram with four participants: cashier terminal, browser store, FastAPI service and PostgreSQL. Step 1: the terminal looks up a product in the cached catalogue. Step 2: it saves the sale to the offline queue, which works without a network. Step 3: when the connection returns, the queued sale is sent to the API. Step 4: the API sets the tenant from the login token and opens one transaction. Step 5: the API allocates stock oldest-expiry first and posts the invoice and journal entry. Step 6: database triggers reject unbalanced or edited postings.Cashier terminalBrowser storeFastAPI servicePostgreSQLLook up product incached catalogue1Save sale to offline queue(works without network)2Connection returns:send queued sale3Set tenant from the token,open one transaction4Allocate oldest-expiry stock;post invoice and journal entry5Triggers reject unbalancedor edited postings6
Read top to bottom. The counter keeps selling from its local copy of the catalogue, then the queued sale is posted in one transaction once the connection is back, with the database as the final check on the books.

Key features

What the system does, as built in the codebase.

  • 01

    Double-entry accounting

    Chart of accounts, journal entries with reversal, fiscal period locks, trial balance, profit and loss, balance sheet and general ledger.

  • 02

    Batch-aware inventory with FEFO

    Products, warehouses, goods receipts, expiry-sorted batch suggestions and stock adjustments, including manufacturer claims. At finalize the system picks the nearest-expiry batch automatically (one batch per line; if none covers the quantity, the sale needs the line split by hand) and locks the batch during the sale so two counters cannot oversell it.

  • 03

    Sales invoicing with trade schemes

    A draft-to-finalize flow with cost of goods taken from the specific batch sold (batch-level costing) and bonus lines from a rule engine.

  • 04

    Offline-capable retail POS

    A full-screen terminal with a local catalogue cache, offline queue, shifts, end-of-day report and split payments.

  • 05

    Receivables and payables aging

    Buckets by days past each party's payment terms.

  • 06

    PDF documents

    A4 invoices, thermal receipts and shift reports.

  • 07

    Onboarding and role-based access

    A guided setup wizard, team invitations and a code-defined permission matrix covering eight roles, including owner, admin, accountant, manager and cashier.

  • 08

    Expiry alert job

    A daily scheduled task identifies and logs stock nearing expiry. There is no email or SMS channel yet.

How it fits together

From a back-office or counter request to a balanced ledger entry.

  1. Next.js web appback office and POS terminal
  2. Browser storelocal catalogue cache, offline sale queue
  3. FastAPI serviceJWT auth, RBAC, tenant-scoped sessions
  4. PostgreSQLrow-level security, ledger triggers
  5. Background workersCelery and Redis for alerts
The web app and POS terminal call a FastAPI service. The terminal keeps a local catalogue and sale queue in the browser. The service reads and writes PostgreSQL, where row-level security and ledger triggers enforce isolation and integrity, and background workers handle alerts.
  • Offline POS sales sync to the API when connectivity returns, posting invoices and journal entries.
  • A billing adapter gates plan features.

Implementation notes

Engineering decisions worth knowing about.

  1. Row-level security

    The API uses a non-superuser database role and a transaction-local tenant setting on every request.

  2. Ledger integrity in the database

    A balance-check trigger, immutable posted entries and locked system accounts enforce correctness. A later migration closed a reversal gap.

  3. Permission matrix and account visibility

    Permissions are defined in code, with row-level account visibility for roles that may see only part of a report.

  4. Token handling

    JWT access tokens with rotating refresh tokens, tenant switching, and password reset tokens stored hashed.

  5. Provider-agnostic billing

    A billing adapter with a manual provider supports plan gating.

  6. Deliberate offline oversell handling

    A stock constraint is relaxed only on the sync path, so offline sales are never rejected. Re-sending a queued sale never double-posts (idempotent sync).

  7. End-to-end scripts

    Tests are end-to-end scripts that run against a live API and database, including cross-tenant and paging checks, with no unit-test suite or CI. Tenant isolation is tested adversarially: a second tenant tries to reach every record type and must get "not found".

Technology stack

Taken from the project's dependency and config files.

Frontend
  • Next.js (App Router)
  • React
  • TypeScript
  • Tailwind CSS
  • TanStack Query
  • Zustand
  • Dexie
  • React Hook Form
  • Zod
Backend
  • FastAPI
  • SQLAlchemy 2.0
  • Alembic
  • Pydantic
  • asyncpg
Data
  • PostgreSQL with trigram search and row-level security
  • Redis
Jobs
  • Celery
Documents
  • ReportLab
Auth
  • JWT (python-jose)
  • bcrypt
Infrastructure
  • Docker Compose for local development
For your technical teamtechnology layers

Technology stack by layer

Pharmacy ERP technology stack by layerLayered stack diagram. Client: Next.js, React, TypeScript, Tailwind CSS, TanStack Query, Zustand, Dexie, React Hook Form and Zod. API: FastAPI, SQLAlchemy 2.0, Alembic, Pydantic, asyncpg, JWT and bcrypt. Jobs and documents: Celery and ReportLab. Data: PostgreSQL with trigram search and row-level security, and Redis. Infrastructure: Docker Compose for local development.ClientNext.js (App Router)ReactTypeScriptTailwind CSSTanStack QueryZustandDexieReact Hook FormZodAPIFastAPISQLAlchemy 2.0AlembicPydanticasyncpgJWT (python-jose)bcryptJobs anddocumentsCeleryReportLabDataPostgreSQL with trigram search and row-level securityRedisInfrastructureDocker Compose for local development
Requests move down from the browser to the API and into PostgreSQL. Background jobs and PDF generation sit beside the API, and Docker Compose covers local development.

Business value

What the system enables. No numbers are claimed.

  • Keeps each business's data isolated while letting one user work across several businesses.
  • Allocates stock nearest-expiry-first and costs each sale at the cost of the batch it came from.
  • Lets counters keep selling during outages and reconcile later.
  • Produces books that stay balanced because the database refuses unbalanced or edited postings.
  • Supports different plan tiers (accounts, standard, POS) from one codebase.

Need a system like this built?

Tell us what it needs to do and we will scope it with you on a call.

Discuss a build