Multi-Tenant SaaS Architecture: Lessons From the Trenches

Multi-tenancy is one of those topics that sounds solved in theory and is a constant source of pain in practice. The architecture diagrams are clean: shared database, tenant isolation, row-level security. The reality is messier, especially when you're solo and every architectural decision is a commitment you'll live with for years. Here's what I've learned building Menyo Pro's multi-tenant architecture — the patterns that actually work and the traps I fell into. When you start, everyone tells you there are three multi-tenant patterns. Nobody tells you which one to pick for your specific situation. Let me fix that. 1. Database-per-tenant. Each restaurant gets its own database. Maximum isolation. Easy to reason about. Also: a nightmare to operate at scale. Migrations across 50+ databases. Cross-tenant analytics become a data pipeline problem. Backups are complex. I ruled this out early despite the isolation appeal. 2. Schema-per-tenant. Each tenant gets a schema in a shared database. Better than database-per-tenant but still operationally heavy. Schema migrations are easier than database migrations but still painful at scale. I considered this and rejected it for the same operational reasons. 3. Shared database, shared schema (row-level). All tenants share tables, with a tenant_id column on every row. Maximum operational simplicity. Migrations run once. Analytics are natural. The risk: if you ever leak tenant data across tenants, it's a catastrophic bug, not a config error. I chose option 3, and I'd choose it again — but only because I built the isolation guarantees into the architecture from day one. Row-level multi-tenancy without enforced isolation is a data breach waiting to happen. The core principle: no query should ever be able to access another tenant's data, even by accident. This isn't a convention you document. It's an invariant you enforce in code. Every request goes through middleware that extracts the tenant ID from the authenticated session and makes it available to the query layer.

// tRPC middleware: every procedure gets a verified tenant context
const tenantMiddleware = t.middleware(async ({ ctx, next }) => {
  if (!ctx.session) throw new TRPCError({ code: 'UNAUTHORIZED' });

  const tenantId = await resolveTenantId(ctx.session.userId);
  if (!tenantId) throw new TRPCError({ code: 'FORBIDDEN' });

  return next({
    ctx: {
      ...ctx,
      tenantId, // Every downstream query must use this
    },
  });
});

This is the foundation. Every database query in the system must be scoped to tenantId. Not "should be" — must be. Here's how I enforce it. Middleware can be bypassed by a bug. The database itself should be the last line of defense. With PostgreSQL, I use Row-Level Security (RLS) policies.

-- Enable RLS on every tenant-scoped table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- The application connects as a role that has these policies
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- Every query from the app sets the tenant context first
SET app.current_tenant_id = 'the-tenant-uuid';

Now even if a bug in the application layer forgets to filter by tenant_id, the database itself prevents cross-tenant access. The query returns nothing because the RLS policy blocks it. This defense-in-depth approach — application middleware plus database RLS — is the only pattern I trust for shared-schema multi-tenancy. The most common multi-tenancy bug is forgetting to filter by tenant_id in a query. I solved this by making it impossible to forget — the query layer does it automatically.

// Every Prisma query goes through this extension
const tenantExtension = Prisma.defineExtension((client) => {
  return client.$extends({
    query: {
      $allOperations({ model, operation, args, query }) {
        // Only apply to tenant-scoped models
        if (!tenantScopedModels.has(model)) return query(args);

        // Inject tenant_id into every where clause
        if (operation === 'findMany' || operation === 'findUnique' || operation === 'update') {
          args.where = { ...args.where, tenantId: currentTenantId };
        }

        if (operation === 'create') {
          args.data = { ...args.data, tenantId: currentTenantId };
        }

        return query(args);
      },
    },
  });
});

Now developers can't accidentally write a query that returns another tenant's data. The extension silently injects the filter. This is boring infrastructure code that prevents the most catastrophic class of bug in multi-tenant SaaS. Multi-tenancy creates a specific performance challenge: queries that filter by tenant_id on every operation. Done naively, this means every query scans more rows than necessary. Index everything by tenant first. Every composite index starts with tenant_id.

-- Not this (slow with many tenants)
CREATE INDEX idx_orders_status ON orders(status);

-- This (fast: isolates tenant scope immediately)
CREATE INDEX idx_orders_tenant_status ON orders(tenant_id, status);
CREATE INDEX idx_orders_tenant_created ON orders(tenant_id, created_at);

The difference is dramatic. With 50 restaurants and 100K orders, an index on (tenant_id, status) lets the database find restaurant X's pending orders in milliseconds. An index on (status) alone scans every restaurant's pending orders. Watch the tenant size skew. Most of your tenants will be small. A few will be large. One restaurant might have 10,000 orders; another might have 50. Queries that are fast for small tenants can be slow for large ones. I learned to test performance against the largest tenant, not the average. Schema migrations in multi-tenant systems have a specific risk: a migration that works fine for small tenants can lock the table for large ones. I learned this the hard way. A migration that added a default value to a column took 2 seconds on my test database. In production, against the largest tenant's data, it locked the orders table for 45 seconds during dinner rush. Rules I follow now:

1. Never add a column with a default in a single migration. Add the column nullable, backfill in batches, then add the default.
2. Never create an index concurrently on a large table during peak hours. PostgreSQL's CONCURRENTLY keyword helps but isn't magic.
3. Always test migrations against a copy of the largest tenant's data, not synthetic data.

// Batch backfill pattern for large tables
async function backfillColumn(batchSize = 1000) {
  let offset = 0;
  while (true) {
    const updated = await db.orders.updateMany({
      where: { newColumn: null },
      data: { newColumn: 'default_value' },
      take: batchSize,
    });
    if (updated.count === 0) break;
    offset += batchSize;
    await sleep(100); // Don't overwhelm the database
  }
}

One thing nobody warns you about: cross-tenant analytics are easy in theory and painful in practice. "Show me the average order value across all restaurants" is a simple query in a shared-schema architecture. But "show me the average order value by restaurant, excluding the top and bottom 10% to control for outliers" requires careful SQL. And "let me compare this restaurant's performance against similar restaurants" requires defining similarity — by cuisine, by size, by region. I ended up with a separate analytics database that ingests anonymized, aggregated data nightly. The production database handles operations (fast, tenant-scoped queries). The analytics database handles insights (slow, cross-tenant aggregations). This separation has saved me from accidentally running a heavy analytics query during dinner rush and degrading operations.

1. Choose shared-schema with RLS. The operational simplicity outweighs the isolation risk, as long as you enforce isolation at the database level, not just the application level.
2. Build the tenant-scoping into your query layer from day one. Retrofitting it is painful and error-prone.
3. Index by tenant first, always. Your largest tenant's performance is your performance benchmark.
4. Test migrations against production-scale data. Synthetic data lies about performance.
5. Separate operational and analytical workloads. Mixing them will eventually bite you.

Multi-tenancy isn't hard because the patterns are complex. The patterns are well-documented. It's hard because the failure mode is catastrophic (data leakage) and the performance characteristics are tenant-dependent. Respect both, build the safety nets into the architecture, and it becomes manageable. Ignore either, and it becomes the thing that wakes you up at 2 AM.