Two products, one tenant — why the contractor system lives inside the HRMS database

1. The decision with the longest reach

In October we were hardening a contractor workforce platform for one of India's largest infrastructure and energy conglomerates: contract labour across several hundred sites, gate events, attendance, wage runs, statutory registers. The pass itself is the subject of The hardening pass. This piece is about the one decision in it that reached further than any rule about endpoints.

The client already runs its employees on our HRMS. The prototype, like every new product, had its own login, its own users table, its own role model and its own database. For this client that meant a second identity for every plant HR, every industrial-relations head and every site manager; copies of the employee master, the organisation tree, holidays and shifts, kept in step by a synchronisation service nobody had asked for; and two role systems for the same administrators to maintain. Every one of those is a seam, and seams are where enterprise software fails in the second year, not the first.

The decision, made on 21 October, had two parts. The contractor system lives inside the client's existing tenant database, in its own schema. And the HRMS role management is the only role system for internal users; the contractor system adds the mapping of what each role may do inside it, and nothing else. Six build waves followed, each proven against SQL Server before the next began.

A second product should never mean a second identity.

2. Reading our own HRMS like a stranger

Before deciding anything we audited the HRMS the way we would audit a system we had bought: the Node API with Sequelize over SQL Server, GraphQL and REST, and the React application, read end to end for everything about users, roles, menus and tenancy. The shape that came back was simpler than the folklore around it.

One SQL Server database per tenant, resolved from a central catalogue by subdomain, roughly thirteen hundred tables in the default schema. A role master whose entire rights model is a JSON array of menu identifiers: rights in the HRMS are screen visibility, with no per-screen view, add, edit or delete flags in the live flow. Data scope comes from an organisation-unit filter and a level-access setting on the role. The login token carries no role at all; the role is picked after login and the token re-minted with it.

Two things fell out of that audit. The first is that the design could be additive: the HRMS has no action-level rights for a contractor system to conflict with, so “who holds which role” and “what the role may do here” split cleanly between the two products. The second is a rule about quirks. The employee status codes, the string cast on a join, the identifier that is not what its name suggests: every one of them is honoured as found and never reimplemented. The system of record gets to be strange. The product joining it does not get to fix that on the way in.

3. One database, two schemas

The first problem with sharing a database is names. The contractor system creates 230 tables with ordinary lowercase names: users, roles, holidays, documents, invoices, incidents. The HRMS tenant database already holds Users, user, AuditTrail, LoginSettings and twelve hundred more, on SQL Server's case-insensitive collation. Collisions were not a risk; they were a certainty.

The resolution is older than either product. Everything the contractor system owns lives in a dedicated schema. It connects as its own login whose default schema is that one, so every unqualified table name in 230 tables, 82 migrations and the whole query layer resolves to the right place with zero changes to code. The HRMS stays in its default schema, untouched. The grants are the boundary: full rights on the contractor schema, SELECT only on a named list of HRMS tables the contractor system reads, never database owner. In the other direction the HRMS login can be granted SELECT on a handful of projection views, which is how “the HRMS reads the contractor system” happens: as views, not as table coupling.

Then the guard. At boot, and before every migration or reset command, the service compares the schema the connection actually resolved to with the one it expects, and refuses on mismatch. An administrator connection can therefore never migrate 230 contractor tables into the HRMS schema by accident. The reset command is scoped to the connection's schema, because unscoped it would have dropped the HRMS tables, and that is a sentence I prefer to write in a plan rather than an incident report.

It was proven on SQL Server 2022 against a simulated shared tenant database seeded with decoy HRMS tables carrying sentinel rows: 82 migrations and 25 seeds as the least-privilege login, 232 tables and three views all in the contractor schema, the HRMS schema untouched, an INSERT into it denied by the engine, the administrator connection refused by the guard, and every sentinel row intact after a scoped reset.

A schema is a boundary the database enforces when the code forgets.

4. The singleton that 102 files imported

One tenant per database means one API process serving many databases. The prototype had a single database handle, imported directly by 102 files. The honest options were to rewrite 102 files or to make the handle lie usefully. We chose the second. The handle is now a Proxy that resolves the current tenant's connection pool from the request's AsyncLocalStorage context, so a query call, a raw statement or a transaction all forward to the right pool and none of the 102 files changed.

Request middleware does the resolution the same way the HRMS does: subdomain to catalogue row, cached for five minutes, to a pooled connection per database, with concurrent initialisation de-duplicated and a quiet period for a tenant whose database is unavailable. Background sweeps iterate tenants explicitly and run inside each one's context. The contractor system's credentials come from the vault, never from the catalogue's HRMS credential columns. And the rule that matters most: with multi-tenant mode on and no tenant in context, the Proxy throws. It never falls back to another tenant's database. An unknown domain, or a tenant on an engine we do not support, answers 404.

The find in this wave was quieter. Ten in-memory caches, permissions, scopes, PII tiers, reference data, the notification catalogue, document templates, country packs and the rest, were keyed by tenant identifier. In a one-database- per-tenant world the tenant identifier is the same small number in every database. A bare key would have served tenant A's permission set to tenant B the first time two tenants shared a process. Every cache is now keyed by database as well. The proof was one process serving two tenant databases on one port, with a rename made through the second domain visible only in the second database.

5. Roles in front, personas behind

The prototype shipped sixteen built-in personas: plant HR, industrial-relations head, site manager, compliance consultant, contractor administrator, contractor supervisor, worker, auditor and so on. They were wired into the permission matrix, the approval chains and the personal-data tiers. Replacing them with HRMS roles outright would have been weeks of work and a regression surface across every screen.

So they stopped being roles and became templates. The HRMS governs who holds which role. The contractor system holds a tenant-scoped map from HRMS role code to persona template, and an administration screen that lists the tenant's HRMS roles by their live names, each with its template and the module-by-action grid on top: what this HRMS role will be allowed to see and do here. Resolution runs per-role override, then template override, then shipped default, so two HRMS roles sharing a template can still be granted differently. An HRMS role with no mapping has no access, and gets a clean 403 that names the screen where an administrator can map it, rather than an empty shell.

The people who are not employees keep their own door. Contractor administrators, supervisors, workers, auditors and consultants have no identity in the HRMS to borrow, so they sign in locally with a password and a second factor, and for them the persona is the role, exactly as before. One middleware accepts both issuers. The proof for this wave was denying a single action for a single HRMS role: it vanished from that session's capability set and the gated endpoint answered 403 naming the role, while a native user with the same template kept the untouched default.

6. Signing in with someone else's session

Internal users never see a contractor-system login. They arrive with the HRMS token and exchange it. The exchange verifies the signature against a secret held in the vault, with a previous-secret window so rotation never logs anyone out. It verifies the tenant binding: the token's tenant must match the domain it arrived on, and a token without that claim is refused. It checks the employee is still active in the master, so a separated employee's still-valid HRMS token is refused here even if it is accepted there. It reads the live roles, intersects them with the map, and issues a session that expires no later than the HRMS session it was exchanged from. There is no second multi-factor prompt on this path; the HRMS session is the factor.

The user row is created just in time, a shadow of the HRMS employee, so that the hundred-odd foreign keys that point at users, approvers, audit entries, locks, all keep working. If a native account with the same e-mail already exists, the shadow adopts it, so one person keeps one identity and every record attached to it.

That adoption path produced the bug of the week. The first version, on adopting the demo administrator's account, stripped the eleven roles that administrator had been granted natively, because the mirror reasoned that the HRMS was now the source of truth for roles. It was right about HRMS roles and wrong about everything else. The fix is a source column on every assignment the mirror touches: the sign-in mirror manages only the rows it created, and never deletes or re-defaults an assignment an administrator granted by hand. The same rule was applied to scopes two waves later, before it could bite twice.

A mirror that can delete is not a mirror.

7. Fail closed, cap down

The HRMS already knows which locations a role may see; the level-access setting on the role says so in HRMS location codes. Each contractor-system site now carries the HRMS location code it corresponds to, and at exchange time the active role's level access resolves to site scopes. The rule is fail closed. A restricted level access whose codes match no mapped site scopes the session to nothing, and logs the mapping gap for an administrator to fix. It never resolves to everything because nothing matched.

Personal data follows the same direction. The HRMS carries a per-assignment flag that hides personal data from a role, and a tenant-level switch that turns personal data off entirely. Either one becomes a cap on the session: every full-visibility tier the persona would normally get is masked for that session. The cap rides the token and is applied after the role cache, so it cannot pollute the cache and no masking call site had to change. A cap only ever lowers; a hidden field is never revealed by it.

The proof was an engagement list that returned 23 of 78 rows for a scoped session with zero foreign-site rows; a plant HR session reading a phone number masked that a site HR session read clear; the same mask applied when the tenant switch was off; and, after unmapping a site, the next session scoped to zero rows rather than all of them.

8. The same query on two engines

Development and CI run on SQLite, which has no schemas. Rather than let the shared-database code become something that only runs against SQL Server, the development pool attaches a small simulated HRMS database under the HRMS schema name, so a schema-qualified query is valid syntax on both engines and runs identically. The simulation is built from the audited column shapes with the quirks encoded as CHECK constraints, and seeded with the cases that matter: a separated employee, an inactive role, an assignment whose window has ended, the menu entry that points at the contractor system.

Every HRMS read goes through one typed accessor module, the one reviewed surface, and adding a table to the read set is one accessor plus one GRANT line in the provisioning script. An eleven-check probe over employee lookups, role joins, window exclusion, the PII flag, menu identifiers and session policy passed on SQLite with the attached simulation and on SQL Server against full-shape HRMS fixtures, as the least-privilege login, with byte-identical results. The simulation is development tooling only. It is never shipped and never described as live.

9. What we did not build

The list of deleted work is the real argument. Synchronisation APIs for the employee master, the organisation tree, holiday calendars and shift calendars became joins. The optional payroll push job, which would have copied attendance into payroll-shaped rows, collapsed into projection views the payroll engine can read with a grant. The hand-off when a contract worker is regularised into an employee now happens inside one database, with the outbox events kept as the audit and idempotency spine rather than as transport. What did not change is anything that was external anyway: messaging providers, identity-verification partners, the outbox pattern itself.

One database serving two products has a risk register, and it is short but real. Blast radius: separate logins and schema grants mean a contractor-system bug cannot touch the HRMS beyond SELECT, and its migrations only ever touch its own schema. Operations: a backup now covers both products at once, so maintenance windows are coordinated with the HRMS team. Releases: different schema, no shared DDL, so each product migrates on its own cadence. Collation is inherited; the contractor system's names were already unique among themselves. The heavy writers, gate punches and the outbox, stay in contractor tables so HRMS transactions are not contended beyond server resources. And a secret that two products now both depend on is vaulted and rotated as one item, not two.

10. The honest part

Three things are not settled by code. The first is policy: a client's database team has to agree to a second schema and a second login inside the HRMS database, and to who owns the grants. That is a conversation with a DBA, and the answer decides whether this design ships for that client or the separate-database fallback, which still exists, does. The second is engines: the shared design assumes SQL Server, and a tenant on anything else takes the fallback. The third is the shell: registering the contractor screens in the HRMS menu so the sidebar shows them to permitted roles with no HRMS code change is viable, and it is joint work with the HRMS team, not a wave we could run alone.

And the question I am least sure of. One schema per product is clean at two. I do not yet know whether it stays clean at four, or whether a third product is the point where the shared database stops being the integration layer and starts being the bottleneck. For now the position is simple enough to say in one line: what the client administers is one system, what the code touches is two schemas, and nothing in between synchronises.