Case study · PHP · MySQL · Flutter · Multi-tenant

Turning one hospital's software into a business that can sell to many.

The clinical system already worked. The hard part was making it sellable: one codebase, many hospitals, each paying for the modules it uses and none able to see another's data. This is how it was built, the role mistake I made, and what hasn't been proven yet.

Tenancy: Database per hospital
Web: PHP 8, MySQL
Desktop: Flutter, SQLite
Billing: Razorpay, Mailgun

The problem

A working hospital system doesn't automatically become a product.

The starting point was a complete system for one hospital, in a small PHP codebase of my own with no framework: about 35 tables covering patients, OPD, IPD, pharmacy, lab and billing. It worked, and patients and bills were off paper.

The business problem appeared when a second hospital wanted it. The system had no idea of a "tenant". The choices were to set up a second copy by hand for every hospital, or to let two hospitals' patients share tables. The first doesn't scale and the second isn't acceptable for medical and billing data. The real work was to turn one hospital's software into something one person could sell to many.

The decision everything follows from

A database per hospital, not a shared table with a hospital column.

The common way to do this is one database and a tenant_id on every row. It's cheaper to run, and it puts every hospital's patients one forgotten WHERE clause away from each other. I chose a separate MySQL database per hospital instead, built from one schema when the hospital signs up.

That buys three things for medical data: a hospital can never read another's rows by accident, one hospital can be backed up, restored or deleted alone, and the desktop app has a clean unit to sync, because one database is one hospital's whole world. It costs two things I accepted on purpose. Every change to the table layout must be rolled out to every hospital's database, so migrations became scripted. And the app connects to a different database on each request, chosen from the address: cityhospital. in front of the domain picks that hospital.

The Master Panel

The business layer lives apart from every hospital.

Plans, invoices, referrals, gateway settings and the list of hospitals live in a separate Master database and are managed from a separate Master Panel, on its own host. A hospital's admin never sees it and the platform never reads a hospital's patients. A plan is a set of modules each priced per day; a month is a flat 30 days so the price never changes with the calendar; a year is twelve months less a discount.

Billing follows a simple lifecycle: an invoice is due after 7 days, a further 3 days of grace follow, then the hospital is suspended automatically and reactivated automatically when payment clears. Hospitals can refer other hospitals for account credit (Rs 500 to the referrer, Rs 200 to the new hospital). Gateway secrets are stored encrypted with AES-256-GCM.

The Master Panel started as a subdomain of the app's domain. When I planned the real deployment I wanted it on its own address, so its host is now a separate setting and tenants and the panel no longer have to share a parent domain.

A mistake I made, and what it taught me

I merged two roles that were meant to be different, and every hospital became over-privileged.

The single-hospital system had two roles on purpose: a super_admin for me, who could define brand-new permission types, and a deliberately restricted admin for the paying customer, who could create any roles they liked from existing permissions but couldn't invent permissions or edit the protected admin role. In an earlier pass I read the name super_admin as just a confusing label, deleted the restricted admin and renamed the other. That collapsed two different roles into one and quietly removed the guard that stopped a hospital editing its own top role.

Looking deeper made it worse: new hospitals were being provisioned with the all-powerful role from the very beginning, so every one of them had been over-privileged from day one. The fix had two parts. A hospital has no super-admin at all any more, so its admin role is locked from being viewed, edited or renamed by anyone inside the hospital. And the permission list and the six default roles moved to the Master database as a single catalog that is synced into each hospital, with room for per-hospital changes. The lesson I took from it is to find out what a thing is for before renaming it.

Support access without a password

Opening a hospital's screen for support, safely.

Support needs a way into a hospital's admin screen, and the worst way to get it is to know the admin's password. The Master Panel issues a random single-use token that lasts two minutes and is tied to one hospital and one user. The hospital side redeems it in a locked transaction, so a double-click can't use it twice, and a token minted for one hospital can't be replayed on another. Every use goes to an audit log, and the master never stores or learns a hospital's passwords.

Deleting a hospital got the same care. The panel asks you to type the hospital's exact subdomain, the server checks it again, and the platform's record is removed before the database is dropped, so a failure leaves a harmless orphan database rather than a record pointing at nothing.

The Windows app and the offline problem

The scary problem turned out not to be one.

The worry going in was two offline front-desk PCs both making the same bill number. It disappears once offline creation is designed properly: a device never assigns a patient number or a receipt number. It saves the record with its own unique id, marks it waiting, and sends it later. The server hands out the number when the record arrives, and checks the id first, so a retry after a lost response returns the existing record instead of making a second one.

Offline writes are therefore limited to creating things (patients, OPD consultations and miscellaneous bills), and the modules that touch a server-held resource such as stock or a bed are online-only by design. A server-side change log lets the app pull down patients added elsewhere, so the front desk's local copy is a mirror and not a separate world. The app also enforces its licence: the plan decides how many devices, whether offline use is allowed, and how many days it may go without checking in.

Never write the money logic twice

One function, two clients.

The desktop app talks to the same server through a token-based API. The rule I held to is that any logic involving money or stock, such as IPD charges, pharmacy purchases with weighted-average cost, stock adjustments and payments, lives in one shared helper file used by both the web screens and the API, and is never copied. When I extracted each one, the web page was re-checked to confirm it still loaded and behaved the same.

Two real bugs were caught this way before anything shipped. The desktop API was missing the plan's module check, which I noticed while building miscellaneous billing. And a purchase screen was wired to the sales screen's medicine search, which deliberately hides medicines with no stock, which is exactly backwards for a screen whose job is to restock them. I added a proper endpoint instead and tested both side by side.

How it was checked

Against a real database, not just on paper.

There is no PHP test suite. Only the lab formula builder has automated tests. Instead each feature was exercised against a real database through the server: for example, a purchase of 100 units at Rs 10 and then 100 more at Rs 14 had to give a weighted cost of exactly Rs 12, and a stock adjustment in the wrong direction had to be refused. Test data was cleaned up afterwards, including working around a circular foreign key that made the obvious delete order fail.

That's decent evidence for the business logic and much weaker evidence for the screens. Several desktop modules were verified through the server's API and not by clicking through the window, and I'm saying so rather than rounding it up.

What isn't proven

Four things I won't claim yet.

Razorpay and Mailgun have only been exercised against the platform's own rules, with no real accounts behind them. Certificate pinning in the desktop app is written but unverified because there is no production host to pin to. The network feature that lets two offline PCs see each other's unsent patients is built but hasn't been run on two separate machines. And there is no passwordless login, OTP or SMS: sign-in is by password. The Master Panel also doesn't stop you building a plan that leaves out a module another one relies on, even though I planned that check.

The architecture

What's actually running under it.

Core: A small PHP core of my own, with routing, sessions, role-based permissions, CSRF protection, security headers, a database-backed rate limiter on sign-in and an idempotency helper that stops a double-submitted form. No Composer packages. About 32,000 lines of PHP.
Tenancy: One MySQL database per hospital, 48 tables each, built from a schema at signup. The hospital is resolved from the request's host name before any connection is made.
Platform: A separate Master database of 19 tables: tenants, plans and modules, invoices, gateways, referrals, role templates, impersonation tokens, desktop licences, announcements and an audit log. Scripted migrations roll changes out to every hospital.
Modules: Patient registry, Reception (OPD and IPD), Pharmacy, Laboratory, Miscellaneous billing and Masters, switched on per plan, with the plan's module list checked before the usual permission check.
Billing: Razorpay checkout and manual payment records, an automated suspend and reactivate lifecycle, and Mailgun for billing email.
Desktop app: Flutter for Windows, about 7,500 lines of Dart in 34 files, with a local SQLite copy for offline work, the token in Windows' secure storage and a compiled .exe built and run.
Security: Gateway secrets encrypted with AES-256-GCM, single-use support tokens, per-plan device limits re-checked by the server, anomaly logging for tampered desktop installs and release-build code obfuscation.
Verification: Exercised against a real database through the server. Automated tests only for the lab formula builder.

The outcome

Honest about where it actually is.

One person can now run this as a business: create a hospital, give it exactly the modules it pays for, bill it, suspend it when it stops paying, and support it without its password. The hospital's staff get a single system for reception, wards, pharmacy, lab and the cashier, in a browser or in a Windows app that keeps working when the connection drops.

What it isn't yet: a product with customers. Live payment and email accounts, a production host with certificates, and a real two-machine test of the offline sync are the gaps between "built and tested" and "launched", and they are listed above instead of left for a buyer to find.

Running a hospital or a clinic, or want a multi-tenant product built properly?

Get in touch