Client work · Under NDA

Coaching Management Platform

Multi-tenant, 1,000+ Students

One codebase for many coaching institutes, where row-level security in PostgreSQL, not developer discipline, keeps each institute's data apart.

Solo build: architecture, code, tests and deployment.

students across live institutes: 1,000+
tenant tables under row-level security: 33
automated tests, 54 suites on real Postgres: ~1,400
typed API operations: 158

Platform console institutes table with plan, billing and website status

The problem

A coaching institute was running on a prototype that stored money as floating-point numbers, recomputed dues on every request and had no tests, so fee balances were a standing bug. Once it was rebuilt properly, other institutes needed the same software, and a separate server, database and certificate per client would not scale on one VPS.

What I built

Institute app

Students
Profiles, import, and per-student attendance, documents, enrolments, exams and fees.
Courses and batches
Courses, batches with a teacher and schedule, and the enrolments that fees and attendance hang off.
Attendance
Per-batch session sheet, history and summary; teachers mark only the batches they teach.
Fees
Fee plans, monthly invoices, payments split across invoices, reversals and receipts.
Exams
Question bank (MCQ, fill-blank, order, match), exam builder, public attempts by share code, auto-grading and retakes.
Certificates
Versioned templates, certificates and ID cards with frozen data snapshots, verifiable publicly by serial.
Leads
Public inquiries with notes, follow-ups and conversion to a student.
Notifications and website
Email templates delivered through a queue and worker, and a bilingual public website per institute.

Platform console

Onboarding and domains
Create an institute with its first admin on a platform subdomain or its own domain.
Website editor
Edit and publish each institute's site with a live preview.
Billing and features
Plans, subscriptions, payments with gapless receipts and per-institute feature toggles.
Support and data
Consent-based read-only support sessions, usage metering, streamed export and deletion.

Screenshots

Staff dashboard with today's classes, pending attendance, outstanding fees and new inquiries — The staff app's Today screen: today's classes and their attendance status, outstanding fees and new inquiries.

Record payment drawer splitting one amount across four open invoices — One payment split across several open invoices, with a running allocated and unallocated total before it is saved.

Attendance sheet with students marked present, absent or late — A batch's attendance sheet, marking each student present, absent or late with live totals.

System architecture

Every institute hostname, a platform subdomain or the institute's own domain, reaches one Docker Compose stack behind Caddy, which issues a certificate on demand only after asking the API whether that hostname belongs to an institute. The Next.js app routes by Host header to the institute app, the platform console or the platform home, and calls the Fastify API server-side. The API resolves the hostname to an institute through a bounded cache, then runs the whole request inside one PostgreSQL transaction with the institute set as a transaction-local setting, so row-level security scopes every query. Email is queued in Redis only after the outermost commit and sent by a BullMQ worker, the one container with outbound network access, and migrations run as a one-shot container before each deploy.

Data model

Entity relationship diagram of the coaching platform: platform tables for billing plans, subscriptions, institutes, institute domains, platform admins and support sessions; tenant tables for staff users, support grants, audit log, inquiries, courses, batches, attendance sessions and records, exams, questions, students, enrolments, exam attempts, payments, payment allocations, invoices, fee plans and certificates. — Scroll sideways to see the whole diagram.Open full size

46 tables: 13 platform tables and 33 tenant tables. Every tenant table has an institute_id that defaults to the current institute setting, a row-level security policy on it, and a unique (institute_id, id), so every foreign key is composite and a row physically cannot point at another institute's row. Money is integer paise, invoices are one row per enrolment per month, payments are never deleted (a reversal is a new row pointing at the original), and a trigger stops allocations from exceeding a payment. Registration, receipt and certificate numbers come from per-institute gapless counters that hold their lock until commit, so a rolled-back insert gives its number back.

Key problems and how I solved them

01Many institutes, one database, no leaks

Problem: The software had to serve many institutes, but a server per client multiplies RAM, migrations, backups and certificates.
Approach: A shared schema with institute_id on 33 tables and a row-level security policy on each, two least-privilege database roles (tenant and platform), composite (institute_id, id) foreign keys, and a runtime that sets the institute with set_config(..., true) inside each transaction and binds it to AsyncLocalStorage. The tenant database handle is a Proxy that throws if a query runs outside an institute transaction.
Why it was hard: An application-level filter can be forgotten on one route, a session-level setting leaks across pooled connections, a nested transaction would take a second connection, and a promise that resolves after commit could run inside the next request's transaction. Each case has its own guard.
Result: An automated sweep reads the generated OpenAPI document, seeds two institutes and calls every operation as one institute with the other's IDs, asserting a refusal and unchanged data. A new route is covered without anyone remembering to add a test.

02Fees that cannot double-charge

Problem: The prototype stored money as floats, wrote payments outside transactions and recomputed dues on every request.
Approach: Integer paise, invoices as rows, payments split across invoices through allocations, the idempotency key enforced by a unique index instead of a check-then-insert, invoices locked FOR UPDATE before balances are read, reversals as compensating rows, and a trigger for the cross-row allocation ceiling. The payment form sends a stable idempotency-key header for its whole lifetime.
Why it was hard: A "have I seen this key" read races under a double click, a multi-row invariant cannot be a column CHECK, and a reversal must not itself be reversible or collide on the key.
Result: A replayed request hits the unique index, its savepoint rolls back and the original payment comes back. Allocation, idempotency and reversal are covered end to end by integration tests on real Postgres.

03Retrofitting tenancy into a working app

Problem: The app was built for one institute with no tenant column, and its registration, receipt and certificate numbers were already printed on paper.
Approach: institute_id defaults to current_institute_id(), so existing inserts did not change. Global sequences became per-institute gapless counters that continue from the old values, the settings singleton was dropped, and every route is wrapped in an institute transaction by one onRoute hook instead of touching each module.
Why it was hard: Migrations run in one transaction and a rollback only restores the previous image, so every migration had to be backward compatible, and a number already printed on a receipt or certificate could never be issued again.
Result: The original institute became tenant one without rewriting its services and without reissuing a single number.

04Support access that cannot change data

Problem: The platform owner needs to debug an institute's account, but the institute owns its student data and the platform should not read it freely.
Approach: The platform database role has no grant on tenant tables. An institute admin grants a time-boxed window (one live grant per institute); opening it creates a support session acting as that admin, with non-GET routes refused and every transaction opened READ ONLY, so Postgres refuses writes as well.
Why it was hard: A route that opens its own transaction could slip past an HTTP-level check, which is why the second layer sits in Postgres, and suspending an institute while a session was opening needed a status re-check under a lock.
Result: Read-only support with consent, enforced at two layers and covered by integration tests, and sessions end when an institute is suspended.

05A domain and a certificate per institute

Problem: Each institute needs its own subdomain or custom domain over HTTPS, without manual certificate work.
Approach: A domains table maps hostnames to institutes. Caddy's on-demand TLS asks an internal API route before issuing a certificate; that route only answers for known hostnames and is not reachable from the internet. The resolver caches hits and misses with a 30-second TTL and a 10,000-entry bound.
Why it was hard: Open on-demand TLS lets anyone point a domain at the server and trigger certificate requests, and an uncached lookup per request turns a flood of random hostnames into a flood of queries.
Result: A new institute gets HTTPS on its own domain with no manual step, covered by resolver and proxy tests.

06Side effects that never act on rolled-back data

Problem: An email queued inside a transaction that later rolls back would describe data that does not exist, and two workers could send the same message.
Approach: afterCommit() registers side effects that only run after the outermost commit and are dropped if their savepoint rolls back. The worker claims each message from QUEUED to SENDING atomically and returns it to QUEUED on a transient failure.
Why it was hard: Requests commit even when they deliberately reject, so lockout records persist, which meant commit order and side-effect order had to be explicit.
Result: Tests prove no email leaves before commit and that concurrent workers never send the same message twice.

A request, end to end

Record a fee payment that settles one month and part of the next

1On a student's fees tab the admin enters an amount; it is parsed to integer paise and the form previews an oldest-first allocation, disabling Save if it over-allocates.
2The generated client sends POST /api/payments with an idempotency-key header held for the form's lifetime and a body typed by the shared Zod contract.
3Caddy matches /api/*, strips the prefix and proxies to the Fastify API.
4Hooks run in order: correlation ID, hostname normalisation, rate limit, hostname-to-institute resolution (refusing suspended or unknown institutes), the fees feature gate, the session actor, the read-only support check, then Zod validation.
5The route wrapper opens the institute transaction and sets the institute with set_config(..., true).
6The service checks the actor is an admin, then in a savepoint inserts the payment with a receipt number from the institute's counter, locks the invoices FOR UPDATE, inserts allocations (the trigger checks the ceiling) and marks invoices PAID or PARTIAL.
7Every statement runs as the tenant role under row-level security, and composite foreign keys keep the payment, invoices and student in the same institute.
8A replay with the same key raises a unique violation, the savepoint rolls back and the original payment is returned.
9An audit row with the payment, IP and correlation ID is written in the same transaction; the wrapper commits, runs after-commit callbacks and returns 201.
10The UI moves to the payment page with its allocations and a printable receipt; a later correction goes through the reverse endpoint.