Building a Multi-Tenant SaaS with Database-per-Tenant Architecture

When I started building the HRMS SaaS, the first real architectural decision was how to isolate tenants. The choice between shared database with tenant columns, schema-per-tenant, and database-per-tenant shapes everything that comes after. I went with database-per-tenant, and it paid off in ways I did not fully anticipate at the start.

Why database-per-tenant

HRMS data is sensitive. Payroll, attendance, performance reviews, biometric IDs. A leak across tenants is not a bug, it is a company-ending event. With a shared database, every query carries a WHERE tenant_id = ? clause, and one missed join leaks data. With database-per-tenant, isolation is enforced by Postgres itself. There is no cross-tenant query path unless you explicitly open one.

The trade-off is operational complexity. Provisioning a new tenant means creating a database, running migrations, and wiring connection pooling. But that complexity is finite and automatable. The risk of a missed tenant_id filter is not.

Branch isolation within a tenant

Midway through, tenants started asking for branch-level isolation. A company with offices in Phnom Penh and Siem Reap wanted payroll and inventory separated per branch, but still under one tenant. Rather than introduce a second isolation layer at the database level, I modeled branches as a soft boundary inside each tenant database. Every row carries a branch_id, and the ORM enforces it at the repository layer.

Database-per-tenant for hard isolation between companies. branch_id for soft isolation within a company. Two different problems, two different mechanisms.

Connection management

The hardest part was connection pooling. With 10+ companies each on their own database, a naive pool per database exhausts Postgres max_connections fast. The solution was a dynamic pool registry: one pool per tenant database, lazily created on first request, evicted after idle timeout. Redis tracks which tenant maps to which database, so the lookup is fast.

async function getTenantPool(tenantId: string): Promise<Pool> {
  if (pools.has(tenantId)) return pools.get(tenantId)!;
  const dbName = await redis.get(`tenant:${tenantId}:db`);
  const pool = new Pool({ ...baseConfig, database: dbName, max: 5 });
  pools.set(tenantId, pool);
  scheduleIdleEviction(tenantId, pool, 10 * 60_000);
  return pool;
}

Migrations across all tenants

Schema changes are the part most teams underestimate. A single migration has to run against every tenant database. I built a migration runner that iterates the tenant registry, applies pending migrations in sequence, and reports per-tenant status. Failed migrations on one tenant do not block others, but they do page me.

• Iterate tenant registry from Redis.
• For each tenant, resolve its database and acquire the pool.
• Run pending migrations inside a per-tenant transaction.
• Record applied version in a shared migrations ledger.
• On failure, log, alert, and continue to the next tenant.

What I would do differently

If I started over, I would introduce the migration ledger on day one instead of retrofitting it. I also underestimated how often tenants would request custom fields. A JSONB column for tenant-defined attributes on core tables would have saved me from several schema migrations. The core decision, database-per-tenant, I would make again without hesitation.