➜~cpchander@dev:~$
cpchander@dev:~/blog$ cat building-multi-tenant-hrms.mdx
➜ ~ cat building-multi-tenant-hrms.mdx
# published May 15, 2026

Three generations of HRMS: from OpenERP to a multi-tenant SaaS

What I learned across 20 years and three generations of HRMS — OpenERP (CRM + telephony + document library), a custom in-house HRMS at scale for 1,500 employees, and now a modern multi-tenant SaaS on NestJS + Prisma + Postgres with row-level security.

I've built or operated HRMS systems across three generations. Each one taught me something different. Each one was the right answer for its moment. And the current one — a multi-tenant SaaS for our ventures — only makes sense in the context of the previous two.

This is the journey, then the technical playbook for the latest generation.

Generation 1 — OpenERP (Virtual Employee, 2006–2012)

When I joined Virtual Employee in 2006 we were 60 people. We needed something to run the basics — employee records, attendance, payroll, hiring, plus operations. Commercial HRMS was way out of budget. The right answer in that era was OpenERP (which later became Odoo).

What we ran in OpenERP:

  • HR modules — employee master, recruitment, attendance, leaves, payroll
  • CRM — accounts, contacts, opportunities, sales pipeline (the same instance that handled HR)
  • Telephony integration — incoming/outgoing calls logged against contacts via an AGI bridge to Asterisk. Click-to-call from the CRM record opened an SIP session
  • Document library — onboarding documents, signed contracts, internal SOPs, all attached to the relevant employee or client record
  • Project management — task tracking against billable client work
  • Accounting (later) — basic financials before we moved to Navision

The thing OpenERP did right was bundling. The CRM record knew about the salesperson (HR), the calls (telephony), the contracts (document library), the projects (operations), and the invoices (accounting). Everything was one model graph. That single-source-of-truth design saved us hundreds of hours of reconciliation that companies running siloed tools were spending every month.

What OpenERP broke around 500 employees

Three things, in order of pain:

  1. Performance. OpenERP's ORM made every list-view slow at scale. Lists with 5,000 rows took seconds; lists with 50,000 timed out. We sharded by year for some tables; we caching others; eventually it became a constant tuning exercise.

  2. Customization tax. Every Indian payroll quirk (TDS, ESI, PF, leave encashment, bonus payable, gratuity) required custom modules. Each custom module made the OpenERP version-upgrade harder. By the time we were on OpenERP 6, the upgrade to OpenERP 7 was a multi-quarter project we couldn't afford.

  3. HR specifics weren't strong. The HR modules were generic. We needed deep capability around attendance/CDR integration, attrition analytics, client-billable-utilization tracking, and IT-ticketing-integrated-with-employee — and OpenERP couldn't do any of those without heavy customization.

We made a call in 2012: the HR side of OpenERP would graduate to its own in-house HRMS. The CRM stayed on OpenERP, then later migrated to Microsoft Dynamics CRM. The accounting moved to Microsoft Dynamics NAV (Navision). The document library moved to SharePoint.

Generation 2 — In-house custom HRMS (Virtual Employee, 2012–2023)

We rebuilt from scratch. PHP + MySQL initially (because that's what we had in-house skills for), with a clean module structure:

  • Employee module — full employee record, organization chart, manager assignment, role, location
  • Recruitment — applicant tracking, interview workflow, offer letters, joining formalities
  • Attendance — punch-in/out (later integrated with Asterisk CDR for call agents; with VPN logs for back-office; with employee monitoring for remote staff)
  • Leaves — leave types, accrual rules, approvals, calendar integration
  • Payroll — salary structures, tax computation, statutory deductions, bank file generation
  • IT ticketing — internal IT tickets routed via the same identity
  • Performance reviews — quarterly + annual cycles, 360 feedback
  • Client billing integration — agent hours → client invoices via Navision
  • Documents — contracts, ID proofs, education proofs, performance docs

Three things made this generation work that we'd missed in the OpenERP era:

1. Direct integration with operational systems. Asterisk CDR → attendance. Active Directory → identity. Navision → payroll bank file. Employee monitoring → productivity dashboards. The HRMS wasn't a silo; it was the orchestrator of every operational system around it.

2. Real-time visibility for managers. Every team lead could see their team's attendance, leave, utilization, billable hours, and IT tickets in one dashboard. That single feature — boring to describe but transformative to operate — eliminated dozens of weekly status meetings.

3. The COVID-day-one moment. When 1,200 employees moved home in March 2020 (the full transition story), the HRMS was already web-based. Attendance kept flowing in from Asterisk CDR and VPN logs. Payroll ran from finance head's home on Day 3 against attendance data the HRMS had already collected. The HRMS investment from 2012–2018 became the operational lifeline of March 2020.

What I'd change about Generation 2 if I could

  • Build it on Postgres, not MySQL. Better JSON support, better extensibility, better RLS for future multi-tenanting.
  • Build it as multi-tenant from day one. We built it for one company. When we wanted to sell it to other staffing firms as a SaaS product (we considered this multiple times), the single-tenant assumptions were everywhere and the refactor cost was too high.
  • API-first. We built UI-first. Every integration with operational systems was retrofit. API-first design would have saved months of integration work over a decade.

Generation 3 — Modern multi-tenant SaaS HRMS (current ventures, 2024+)

For Zedtreeo and our broader ventures, I'm building a third-generation HRMS — this time multi-tenant from day one, API-first, Postgres-backed. This is the technical detail people came here looking for. Now you have the context.

The stack

  • Backend: NestJS + Prisma + Postgres
  • Frontend: Next.js 14+ App Router + shadcn/ui + Tailwind
  • Auth: Clerk (multi-tenant orgs) → Postgres tenant_id
  • Hosted: Vercel for web; AWS RDS for Postgres; AWS for S3 document storage
  • Telephony integration: Twilio API (modern replacement for our Asterisk + AGI bridge)
  • Document library: S3 buckets per tenant with KMS encryption + signed-URL access

Why Postgres + RLS is the right answer in 2026

Most multi-tenant SaaS tutorials show you WHERE tenant_id = $1 and call it a day. That works until your first customer audit. Here's what production-grade tenant isolation actually looks like.

The three isolation models

Every multi-tenant system picks one — or hybrids them:

  1. Database-per-tenant — strongest isolation, brutal ops cost.
  2. Schema-per-tenant — middle ground, hits Postgres limits around 1k tenants.
  3. Shared schema + row-level — cheapest, requires discipline.

For SMB-targeted HRMS, shared schema with Postgres RLS is the right default. The trick is doing it without trusting your application code.

Defense in depth

The mistake junior teams make: enforcing tenant scoping in the ORM layer. One bad query, one missing WHERE clause, and you've leaked data across tenants. The fix is layered:

ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON employees
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Then every connection sets the tenant context before queries. Even if a developer writes prisma.employee.findMany() with no filter, Postgres enforces isolation.

The Prisma + RLS dance

Prisma doesn't natively understand RLS. The pattern:

  1. Wrap each request in a transaction.
  2. SET LOCAL app.tenant_id = '...' as the first statement.
  3. Run the user's queries.
  4. Commit.

This guarantees the session variable lives only for that request and dies on commit.

What breaks

Three things will bite you:

  • Background jobs. They have no request context. Pass tenant_id explicitly through the job payload.
  • Cross-tenant operations. Some admin actions legitimately span tenants. Use a separate "super-admin" connection with RLS bypassed — and log every query.
  • Connection pooling. PgBouncer in transaction mode is fine. Session mode breaks RLS context. Use transaction mode.

What we tested

For our HRMS, we ran a 50-tenant fuzz test: random tenants, random API calls, random failure injection. Goal: cross-tenant reads = 0. After three iterations, we got there. The change that mattered most was moving tenant context setup from middleware (which can be skipped) to a Prisma extension that runs on every query.

Use cases — what tenant isolation actually prevents

Use case 1 — A misconfigured query that would have leaked across tenants

In dev we wrote a "list all employees with manager flag" query that omitted the tenant filter. App-layer code review caught it, but RLS would have caught it too — and that's the point. Defense in depth is the only honest answer for multi-tenant data. One layer fails; the other catches it.

Use case 2 — An audit log review that found a near-miss

During a quarterly access review we found a developer service-account had been incorrectly granted the superuser role for a routine migration and the role hadn't been revoked. RLS doesn't apply to superuser. We caught it from the audit log before any real data crossed tenants. The fix: a new policy — all migration accounts are time-bound (24 hours), automatically revoked.

Use case 3 — Migrating from Postgres logical replication to physical

When we needed to migrate the HRMS database to a new host, the replication slot had to be tenant-aware. We used logical replication with publications scoped per tenant — the replica was a hot standby for failover, and tenant data crossed shards properly. RLS works during replication too, as long as you understand which session role is doing the replication.

Use case 4 — Scaling to 200 tenants

The original architecture was sized for 50 tenants. By the time we hit 200, the policy evaluation overhead was noticeable on hot paths. Optimizations:

  • Indexed every tenant_id column
  • Used CREATE POLICY ... USING (...) rather than computed expressions where possible
  • Cached current_setting('app.tenant_id') in PL/pgSQL functions for repeated reads

Cumulative effect: ~12% query-time reduction on dashboard endpoints.

Use case 5 — Onboarding a Fortune 500 client (single-tenant in shared db)

A Fortune 500 client onboarded with strict compliance requirements but didn't need a separate database — they needed proof of isolation. Postgres RLS + policy documentation + a one-page architectural diagram + the dev test suite outputs satisfied their security review. You don't always need separate databases for isolation. Sometimes the right answer is RLS + paperwork.

What I'm carrying forward into Generation 3

Across the three generations, certain principles have only become more right with time:

  1. Bundling beats sprawl. OpenERP got this right in the small. We lost it when we split everything across many tools (HRMS + Dynamics CRM + Navision + SharePoint + Asterisk + Active Directory + monitoring). The current generation re-bundles: the HRMS, document library, telephony integration, and identity are one product. Other tools (CRM, accounting) integrate via API, not by data duplication.

  2. Operational integrations are the moat. Anyone can build an HRMS with a clean UI. Few HRMS products integrate cleanly with telephony for call-agent attendance, with VPN logs for back-office, with productivity monitoring, with bank-payment files. Integration depth is the defensible difference for a B2B HRMS targeting staffing companies.

  3. Build it multi-tenant once, not retrofit it later. The cost of multi-tenanting a single-tenant product is brutal. RLS + tenant_id everywhere + per-tenant document storage + per-tenant identity is the from-day-one tax that's far cheaper than the retrofit.

  4. API-first means your future integrations cost nothing. Generation 2 took months per integration. Generation 3 takes hours because every domain operation has a clean API surface.

  5. The telephony + document library + HRMS triangle still matters. Two decades after OpenERP demonstrated the value of bundling, the modern multi-tenant SaaS HRMS I'm building puts them back together — just with cloud-native components (Twilio, S3, Postgres) instead of self-hosted (Asterisk, filesystem, MySQL).

Takeaway

If you're building B2B SaaS in 2026 and you're not using Postgres RLS, you're either over-engineering with separate databases or under-engineering with app-layer scoping. RLS is the boring, correct answer.

And if you're building a vertical HRMS specifically — for staffing, BPO, or remote-workforce companies — the integration depth (telephony, attendance, payroll, document library, monitoring) is what wins. The UI is the cost of entry; the operational integrations are the product.

If this resonates with how you're thinking about HR/operational software for a staffing or BPO business, the COVID transition piece is a deep read on what these systems actually do under pressure: 1,200 employees, 5 days: the COVID remote-work transition playbook.

← back to /blog