Md Nayeem Hossain | Software Engineer

Designing a Multi-Tenant SaaS Architecture#

When we built AffsFlow, every customer needed their own fully branded affiliate network: their own subdomain, their own users, their own settings, and absolutely no way to see another customer's data. That is multi-tenancy, and the decisions you make about it in the first month shape the product for years.

This post covers the trade-offs I weighed and the patterns that held up in production.

Three ways to isolate tenants#

1. Database per tenant#

Each tenant gets a separate database. Isolation is as strong as it gets, noisy neighbours are contained, and per-tenant backup and restore is easy.

The cost is operational: thousands of databases to migrate, monitor and connect to. Connection pooling becomes hard, and cross-tenant analytics require a separate pipeline. This model suits a small number of large, high-value customers, often with compliance requirements.

2. Schema per tenant#

One database, one schema per tenant. It is a middle ground, but migrations still run once per tenant and schema counts grow without bound. In my experience it combines many of the costs of both other models.

3. Shared schema with a tenant ID#

Every table has a tenant_id column and every query filters by it. It is the cheapest to operate and scales to a very large number of small tenants. The risk is obvious: one missing WHERE tenant_id = ... leaks data across customers.

For AffsFlow we chose the shared schema, with layered defenses against that risk.

Make the tenant impossible to forget#

Relying on every developer to remember a filter in every query is not a strategy. Push tenant scoping into the infrastructure so it happens by default.

Resolve the tenant once, at the edge of the request:

1@Injectable()2export class TenantMiddleware implements NestMiddleware {3  constructor(private readonly tenants: TenantService) {}4 5  async use(req: Request, _res: Response, next: NextFunction) {6    const host = req.hostname; // acme.affsflow.com or a custom domain7    const tenant = await this.tenants.resolveByHost(host);8    if (!tenant) throw new NotFoundException("Unknown network");9    tenantContext.run({ tenantId: tenant.id }, next);10  }11}

Scope data access through a repository that reads the context:

1export class TenantRepository<T extends { tenantId: string }> {2  constructor(private readonly repo: Repository<T>) {}3 4  find(where: FindOptionsWhere<T> = {}) {5    return this.repo.find({ where: { ...where, tenantId: currentTenantId() } as FindOptionsWhere<T> });6  }7}

Application code then cannot query without a tenant, because the raw repository is not exposed.

Defense in depth with row-level security#

PostgreSQL row-level security adds a guarantee at the database itself. Even a buggy raw query cannot read another tenant's rows:

sql

1ALTER TABLE campaigns ENABLE ROW LEVEL SECURITY;2 3CREATE POLICY tenant_isolation ON campaigns4  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Set app.tenant_id at the start of each transaction from the request context. Combine it with a database role that is not the table owner, since owners bypass RLS by default.

Indexes and keys#

With a shared schema, tenant_id should lead most indexes, because nearly every query filters on it:

sql

1CREATE INDEX campaigns_tenant_created ON campaigns (tenant_id, created_at DESC);

Unique constraints must also include the tenant. Two networks can each have a campaign with the slug summer-sale:

sql

1ALTER TABLE campaigns ADD CONSTRAINT campaigns_tenant_slug UNIQUE (tenant_id, slug);

Noisy neighbours#

In a shared system, one tenant running a huge export can slow everyone down. Protections we added:

Per-tenant rate limits on the API and on background job enqueueing.
Fair job queues that round-robin between tenants instead of processing strictly first-in, first-out.
Per-tenant metrics, so you can see which customer is driving load before they cause an incident.
An escape hatch: the ability to move a very large tenant to its own database later, which is far easier if every table already has a clean tenant_id.

Custom domains and branding#

Branded networks meant tenants could bring their own domain. That needs automated certificate provisioning, a domain verification step (a DNS TXT record), and tenant resolution by host. Keep a cache of host-to-tenant mappings, because this lookup happens on every request.

Testing isolation#

Write tests that specifically try to break isolation: create two tenants, log in as one, and attempt to read, update and delete the other's resources through every endpoint. Run them in CI. Isolation bugs are the most damaging bugs a SaaS can ship, so they deserve dedicated tests.

Conclusion#

For most SaaS products, a shared schema with a tenant ID is the right starting point — as long as tenant scoping is automatic, enforced again by row-level security, reflected in every index and unique key, and protected by tests. Start shared, design so a large tenant can be moved out later, and make the safe path the default path.