HMS — Hospital Management SaaS

Problem

Bangladesh has roughly 5,000 private hospitals and clinics, and many of them still run on paper registers, Excel sheets and a billing program that has no idea what the pharmacy sold. The ones that do buy software usually get a single install that someone has to host, back up and patch for them. I wanted to find out whether this could work as SaaS instead: a hospital signs up, gets its own space on shared infrastructure, and adding the next hospital costs close to nothing. I started from a design prototype with 23 screens and a data architecture spec. The spec was good. It had real database rules, like a constraint that stops a doctor being double-booked. But both documents assumed one hospital with branches. There was no tenant anywhere, no signup, no subscription, and no way to keep two hospitals apart.

Approach

Before writing code I wrote a plan that turned the single-hospital design into a multi-tenant one, then built it in milestones, each ending with tests. The code lives in a pnpm monorepo. A NestJS API is the only API every client talks to. There's a Next.js app for hospital staff, a separate Next.js console for the platform team, and a BullMQ worker that owns every scheduled job: provisioning, imports, backups, billing and retention. A small control-plane database knows who the customers are. Hospital data lives in 'cells', where each cell is a Postgres cluster behind PgBouncer. Isolation is layered: row-level security on every table, an API role that cannot bypass it, and one withTenant() helper as the only way to reach a cell. A lot of the rules live in the database itself. A bed can hold one patient, money and stock are ledgers you add to and never edit, and a lab result needs a second person to verify it before it's released. The MVP covers the whole hospital, and a hospital can set up its own staff accounts, fees, doctors' hours, wards, beds and medicines without us.

Key decisions

• Cells instead of one big database, or Citus

  There are about 165,000 hospitals in the world. I don't expect to serve them all, but I didn't want a design that stops working at a few thousand. The options were one shared database, Citus, or 'cells' (the pattern Shopify and Slack describe): many identical small stacks, with each hospital assigned to one. Citus is built for this, but it ties you to one Postgres distribution and gives you one big failure domain. Cells run on any managed Postgres, and a failure only hits one cell's hospitals. The MVP runs on a single cell. What I built from the start is the part that's painful to add later: the tenant registry, routing, tenant_id on every row, and a migration runner that updates every cell. Moving a hospital between cells is already tested.

• Transaction-local tenant context, because of PgBouncer

  PgBouncer in transaction mode hands the same database connection to different requests. If you set the tenant with a normal SET, it stays on that connection and the next hospital to borrow it inherits it. So withTenant() sets the tenant with a transaction-local setting that disappears at commit or rollback. It's also parameterised, because SET LOCAL can't take a bind parameter and I didn't want string interpolation in the statement that decides who sees what.

• Plain SQL instead of an ORM

  The rules that matter most are Postgres features most ORMs can't express: exclusion constraints, partial unique indexes, generated columns, RLS policies. I wrote the migrations and queries by hand so those rules stay exactly as designed. Migrations are checksummed once applied, and fixes go forward in a new file.

• Asking the hospital instead of guessing

  When the day's appointments ran past the doctor's hours, the system kept booking walk-ins into the night, including one at five past midnight. The fix wasn't technical. Should it refuse, warn, or extend the session? That's a question for the people running the clinic, so I wrote it into the pilot acceptance script rather than quietly picking one. The behaviour we settled on is that the session extends: the doctor stays late, capped by a hospital setting and never past midnight, and the queue marks those patients as after hours.

A hard problem I solved

The worst bug I found was one row-level security couldn't catch on its own. I wrote a cross-tenant test that tries every door into a neighbouring hospital's data and hashes every table before and after. It found that Postgres doesn't apply RLS when it checks a foreign key. So hospital A could save a prescription, an invoice or a bed assignment that pointed at hospital B's patient, and the database would accept it. Nothing leaked on read, but the data would be quietly wrong in a way that's very hard to clean up later. Adding checks to each endpoint would have been easy, and eventually I'd have missed one. So I rebuilt all 235 foreign keys between tenant tables as composite keys, (tenant_id, patient_id) pointing at (tenant_id, id), which means the database itself refuses a reference across hospitals. A test asserts that no plain foreign key is left. A later run of the same test turned out to be weaker than it looked: a typo in a field name and empty request bodies meant a third of the routes were rejected by validation before they ever reached the tenant check. I rewrote it so every route gets a request that would succeed for the hospital it belongs to.

How I worked with AI

I built this with Claude Code as a pair programmer, and it wrote a large share of the code. That changed what my job was more than how much of it there was. I wrote the architecture plan and decided what the database had to guarantee. I set the rules the AI had to follow, like 'every query goes through withTenant()' and 'migrations are never edited'. Then I kept a progress file it read at the start of every session, so work carried over between sessions instead of starting from scratch. The part I spent the most care on was checking the output. AI-written code tends to look finished and pass the tests it was written alongside, so I leaned on tests the code couldn't fake: real Postgres instead of mocks, a fresh hospital provisioned for every test suite, and end-to-end runs that reconcile a day's money by hand. Several of the bugs on this page were found that way in code that had already been called done. One example: pharmacy returns were being reported as charity write-offs, and the 16 existing report tests passed both before and after the fix because none of them had ever told the two apart.

Status

An MVP, not in production yet. It runs from one Docker Compose file on a single server with TLS, and has been rehearsed end to end on a staging setup. There are 71 database tests, 93 validation tests, 768 API tests that run on freshly provisioned hospitals, and 139 Playwright browser tests. A load test at 3× a busy hospital's peak hour ran with zero failures, and a restore drill brings the whole system back on a fresh machine from the off-site backup in 42 seconds. Next is a pilot with a real hospital, using a bilingual (Bangla/English) acceptance script their own staff run on their own data and sign off.