Prisma + PostgreSQL Schema Design for a Multi-Tenant SaaS: Patterns From Alemmi and SouqSoft

Short answer: for almost every SaaS built on Prisma and PostgreSQL, the right design is one database, one schema, and an organizationId column on every tenant-owned table, enforced twice: once in a Prisma client extension that scopes every query, and once in PostgreSQL row-level security so a missed scope cannot leak data. Around that core sit a Membership table with a role enum, an append-only AuditLog, soft deletes, composite indexes that start with the tenant column, cursor pagination, and migrations that run in CI against a direct connection while the app uses a pooled one.

These are the patterns behind the Alemmi Network platform (four roles, trade lifecycle states, exception handling and a full audit trail on Prisma and PostgreSQL) and the SouqSoft marketplace (role-based access, optional two-factor authentication, feature flags, real-time notifications, Next.js 16 with Prisma). The code is trimmed to what matters.

Which tenancy model should you choose?

Short answer: shared database, shared schema, tenant column. Move to schema-per-tenant or database-per-tenant only when a paying enterprise customer demands physical isolation, and price that as a separate tier.

What does the core schema look like?

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")   // pooled (PgBouncer / Neon pooler)
  directUrl = env("DIRECT_URL")     // direct, for migrations
}

model Organization {
  id          String       @id @default(cuid())
  slug        String       @unique
  name        String
  plan        Plan         @default(FREE)
  createdAt   DateTime     @default(now())
  memberships Membership[]
  projects    Project[]
  auditLogs   AuditLog[]
}

model User {
  id            String       @id @default(cuid())
  email         String       @unique
  name          String?
  twoFactorSecret String?    // optional 2FA, as in SouqSoft
  memberships   Membership[]
}

model Membership {
  userId         String
  organizationId String
  role           Role         @default(MEMBER)
  user           User         @relation(fields: [userId], references: [id], onDelete: Cascade)
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  createdAt      DateTime     @default(now())

  @@id([userId, organizationId])
  @@index([organizationId, role])
}

model Project {
  id             String        @id @default(cuid())
  organizationId String
  slug           String
  name           String
  status         ProjectStatus @default(ACTIVE)
  createdAt      DateTime      @default(now())
  updatedAt      DateTime      @updatedAt
  deletedAt      DateTime?     // soft delete
  organization   Organization  @relation(fields: [organizationId], references: [id], onDelete: Cascade)

  @@unique([organizationId, slug])
  @@index([organizationId, status, createdAt])
}

model AuditLog {
  id             String       @id @default(cuid())
  organizationId String
  actorId        String?
  action         String       // "project.update"
  entity         String       // "Project"
  entityId       String
  before         Json?
  after          Json?
  createdAt      DateTime     @default(now())
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)

  @@index([organizationId, entity, entityId])
  @@index([organizationId, createdAt])
}

enum Role          { OWNER ADMIN MEMBER VIEWER }
enum Plan          { FREE PRO ENTERPRISE }
enum ProjectStatus { ACTIVE ARCHIVED }

Three deliberate choices: roles live on the membership, not the user, so one person can be an owner in one organisation and a viewer in another; every tenant-owned table gets onDelete: Cascade from the organisation so deleting a tenant is one statement; and status fields are enums, which keeps invalid states out of the database and makes indexes selective.

How do you scope every query to the tenant?

Short answer: resolve the tenant once per request from the session and the URL, keep it in AsyncLocalStorage, and use a Prisma client extension that injects organizationId into every read's where and every create's data for tenant-owned models. Developers then cannot forget the scope, because they never write it.

// lib/tenant.ts
import { AsyncLocalStorage } from 'node:async_hooks';
export const tenantStore = new AsyncLocalStorage<{ organizationId: string; userId: string }>();
export const currentTenant = () => {
  const t = tenantStore.getStore();
  if (!t) throw new Error('No tenant in context');
  return t;
};

// lib/db.ts
import { PrismaClient } from '@prisma/client';
const TENANT_MODELS = new Set(['Project', 'AuditLog', 'Invoice']);

const base = new PrismaClient();
export const db = base.$extends({
  query: {
    $allModels: {
      async $allOperations({ model, operation, args, query }) {
        if (!model || !TENANT_MODELS.has(model)) return query(args);
        const { organizationId } = currentTenant();
        const a = args as any;
        if (['findMany', 'findFirst', 'findUnique', 'update', 'updateMany', 'delete', 'deleteMany', 'count', 'aggregate'].includes(operation)) {
          a.where = { ...(a.where ?? {}), organizationId };
        }
        if (operation === 'create') a.data = { ...a.data, organizationId };
        if (operation === 'createMany') a.data = a.data.map((d: any) => ({ ...d, organizationId }));
        return query(a);
      },
    },
  },
});

// In a Server Action or Route Handler: establish context once
export async function withTenant<T>(orgSlug: string, fn: () => Promise<T>) {
  const session = await auth();
  const membership = await base.membership.findFirst({
    where: { userId: session.user.id, organization: { slug: orgSlug } },
    select: { organizationId: true, role: true },
  });
  if (!membership) notFound();  // a 404, never a 403, so tenants cannot probe each other
  return tenantStore.run({ organizationId: membership.organizationId, userId: session.user.id }, fn);
}

Note the findUnique case: Prisma requires unique fields in where, so with an injected organizationId you should query by the composite unique (organizationId + slug) or use findFirst. Returning 404 rather than 403 for other tenants' records is a small but real security improvement.

How does PostgreSQL row-level security add a second lock?

Short answer: policies on every tenant table compare organizationId to a session variable, the application sets that variable at the start of each transaction, and the app connects as a role that is not the table owner so the policies apply. If a query ever escapes the extension, the database returns zero rows instead of another tenant's data.

-- migration: enable RLS (run once per tenant table)
ALTER TABLE "Project" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Project" FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON "Project"
  USING ("organizationId" = current_setting('app.tenant_id', true))
  WITH CHECK ("organizationId" = current_setting('app.tenant_id', true));

-- the app role must not own the tables
CREATE ROLE app_user LOGIN PASSWORD '...';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

// Set the tenant for the duration of one transaction
export function tenantTx<T>(fn: (tx: Prisma.TransactionClient) => Promise<T>) {
  const { organizationId } = currentTenant();
  return db.$transaction(async (tx) => {
    await tx.$executeRawUnsafe(`SET LOCAL app.tenant_id = '${organizationId.replace(/'/g, "''")}'`);
    return fn(tx);
  });
}

Interactive transactions hold a connection for their duration, so keep them short and use them for the writes and sensitive reads; plain reads can go through the extension alone. With pooled connections, SET LOCAL (transaction-scoped) is essential; SET would leak the tenant to the next borrower of the connection.

What belongs in the audit log and how do you write it?

Write from the data-access layer, never from UI code: who acted (actorId), what (action), on which entity, and the before and after as JSON. In the Alemmi platform the audit trail is what lets compliance officers override an automated screening decision and still prove afterwards exactly what changed and when. Keep it append-only (no update or delete grants for the app role), index by tenant and entity, and partition or archive by month once it passes tens of millions of rows.

Which indexes, pagination and soft-delete rules keep queries fast?

• Composite indexes lead with the tenant column and follow the access pattern: [organizationId, status, createdAt] for list pages, [organizationId, entity, entityId] for audit lookups.
• Composite uniques replace global ones: [organizationId, slug], [organizationId, email] for tenant-level invitations.
• Cursor pagination (cursor + take on a unique, monotonic column such as id or createdAt + id) instead of offset, which degrades on large tenants.
• Soft deletes via deletedAt, with the same client extension adding deletedAt: null to reads and a nightly job that hard-deletes after the retention period.
• Avoid N+1 with include or a second batched query; log slow queries in development with Prisma's log: ['query'] and check EXPLAIN ANALYZE on anything over 50 ms.
• JSON columns for genuinely flexible data (feature flags, settings) and never for anything you filter or join on.

How should pooling and migrations be configured?

1. Application traffic through the pooler: Neon's pooled endpoint, Supabase's transaction-mode pooler or PgBouncer, with ?pgbouncer=true in the URL where the provider requires it.
2. Migrations through directUrl, because migrations need session-level features the transaction pooler does not support.
3. prisma migrate dev locally, committed migration files, prisma migrate deploy as a CI step that runs before the deployment goes live. Never migrate from application startup on a serverless platform.
4. Backward-compatible migrations for zero-downtime deploys: add the column, deploy code that writes both, backfill, then drop the old column in a later release.
5. One PrismaClient per process, cached on globalThis in development to survive hot reloads.

What is the pre-launch checklist?

• Every tenant-owned model is in the extension's list and has a composite index starting with organizationId
• RLS enabled and forced on those tables; the app role does not own them
• A test that logs in as tenant A and requests tenant B's record by ID, expecting 404
• Audit log written for every mutation on money, permissions and status fields
• Backups verified by restoring one; point-in-time recovery enabled where the provider offers it
• Slow-query logging on; the ten most common queries checked with EXPLAIN ANALYZE
• Migrations run in CI against a staging database identical to production

Frequently asked questions

Which multi-tenancy model should a SaaS MVP use with Prisma?

A shared database and shared schema with an organizationId column on every tenant-owned table. It is the cheapest to run, the simplest to migrate, and Prisma supports it naturally. Schema-per-tenant or database-per-tenant only pays off for regulated enterprise customers who demand physical isolation.

How do you make sure every Prisma query is scoped to the tenant?

Resolve the tenant once per request, store it in AsyncLocalStorage, and use a Prisma client extension that injects organizationId into the where clause of every read and the data of every create for tenant-owned models. Then add PostgreSQL row-level security so a missed scope still cannot leak data.

Does Prisma support PostgreSQL row-level security?

Yes, with a small pattern: run the query inside a transaction that first executes SET LOCAL app.tenant_id, and define policies that compare each row's organizationId to current_setting('app.tenant_id'). The application connects with a role that is not the table owner so the policies apply.

What indexes does a multi-tenant table need?

A composite index that starts with organizationId for every access pattern, and composite unique constraints such as organizationId plus slug. Single-column indexes on organizationId alone are rarely enough.

How should migrations and pooling be set up for Prisma on Neon or Supabase?

Use the pooled connection string for the application and a direct connection string for migrations via the directUrl field. Run prisma migrate dev locally and prisma migrate deploy in CI before the app deploys, never from application startup.

Related: hiring a Prisma and PostgreSQL database architect and backend and API development.