Designing Multi-Tenant SaaS Architecture: 100 to 10,000 Tenants Without Data Leaks

+----------------------------------------------------------------------+
|                     3 Multi-Tenancy Database Models                  |
+----------------------------------------------------------------------+
| 1. Database-per-Tenant  | Separate DB for every customer (High cost) |
| 2. Schema-per-Tenant    | Shared DB, isolated Postgres schemas       |
| 3. Shared Database      | Shared tables with TenantId column (Low cost|
+----------------------------------------------------------------------+

+--------------------+----------------+------------------+-------------------+
| Feature            | DB-per-Tenant  | Schema-per-Tenant| Shared Table      |
+--------------------+----------------+------------------+-------------------+
| Data Isolation     | Maximum (Hard) | High (Logical)   | Logical (Software)|
| Cost Efficiency    | Low            | Medium           | Maximum           |
| Scale Boundary     | ~ 100 tenants  | ~ 1,000 tenants  | 10,000+ tenants   |
| DB Migrations      | Very Slow      | Slow             | Fast (Single Run) |
+--------------------+----------------+------------------+-------------------+

import { PrismaClient } from '@prisma/client';

export function createTenantPrismaClient(tenantId: string) {
  const prisma = new PrismaClient();

  return prisma.$extends({
    query: {
      $allModels: {
        async $allOperations({ model, operation, args, query }) {
          // Automatically inject tenantId into all queries
          if (['findMany', 'findFirst', 'count'].includes(operation)) {
            args.where = { ...args.where, tenantId };
          }
          if (['create'].includes(operation)) {
            args.data = { ...args.data, tenantId };
          }
          return query(args);
        },
      },
    },
  });
}