Multi-Tenant Database
Row-level security patterns for multi-tenant applications.
Schema Design#
1// prisma/schema.prisma
2model Organization {
3 id String @id @default(cuid())
4 name String
5 slug String @unique
6 plan Plan @default(FREE)
7
8 members Member[]
9 projects Project[]
10 apiKeys ApiKey[]
11
12 createdAt DateTime @default(now())
13 updatedAt DateTime @updatedAt
14
15 @@index([slug])
16}
17
18model Member {
19 id String @id @default(cuid())
20 userId String
21 organizationId String
22 organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
23 role MemberRole @default(MEMBER)
24
25 user User @relation(fields: [userId], references: [id], onDelete: Cascade)
26
27 createdAt DateTime @default(now())
28
29 @@unique([userId, organizationId])
30 @@index([organizationId])
31 @@index([userId])
32}
33
34model Project {
35 id String @id @default(cuid())
36 name String
37 organizationId String
38 organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
39
40 tasks Task[]
41
42 createdAt DateTime @default(now())
43 updatedAt DateTime @updatedAt
44
45 @@index([organizationId])
46}
47
48model Task {
49 id String @id @default(cuid())
50 title String
51 completed Boolean @default(false)
52 projectId String
53 project Project @relation(fields: [projectId], references: [id], onDelete: Cascade)
54
55 createdAt DateTime @default(now())
56 updatedAt DateTime @updatedAt
57
58 @@index([projectId])
59}
60
61enum Plan {
62 FREE
63 PRO
64 ENTERPRISE
65}
66
67enum MemberRole {
68 OWNER
69 ADMIN
70 MEMBER
71 VIEWER
72}Tenant Context#
1// lib/tenant-context.ts
2import { AsyncLocalStorage } from 'async_hooks';
3
4interface TenantContext {
5 organizationId: string;
6 userId: string;
7 role: string;
8}
9
10const tenantStorage = new AsyncLocalStorage<TenantContext>();
11
12export function getTenantContext(): TenantContext | undefined {
13 return tenantStorage.getStore();
14}
15
16export function runWithTenant<T>(
17 context: TenantContext,
18 fn: () => T
19): T {
20 return tenantStorage.run(context, fn);
21}
22
23export function requireTenantContext(): TenantContext {
24 const context = getTenantContext();
25 if (!context) {
26 throw new Error('Tenant context not set');
27 }
28 return context;
29}Tenant-Aware Prisma Client#
1// lib/prisma-tenant.ts
2import { PrismaClient, Prisma } from '@prisma/client';
3import { getTenantContext } from './tenant-context';
4
5const prisma = new PrismaClient();
6
7// Middleware to enforce tenant isolation
8prisma.$use(async (params, next) => {
9 const context = getTenantContext();
10
11 // Skip for operations without tenant context
12 if (!context) {
13 return next(params);
14 }
15
16 const tenantModels = ['Project', 'Task', 'ApiKey'];
17
18 if (tenantModels.includes(params.model || '')) {
19 // Add organizationId filter to queries
20 if (params.action === 'findMany' || params.action === 'findFirst') {
21 params.args = params.args || {};
22 params.args.where = {
23 ...params.args.where,
24 organization: { id: context.organizationId },
25 };
26 }
27
28 // Ensure creates include organizationId
29 if (params.action === 'create') {
30 params.args.data = {
31 ...params.args.data,
32 organizationId: context.organizationId,
33 };
34 }
35
36 // Ensure updates/deletes are scoped
37 if (
38 params.action === 'update' ||
39 params.action === 'delete' ||
40 params.action === 'findUnique'
41 ) {
42 // Verify ownership before operation
43 const record = await prisma[params.model as any].findFirst({
44 where: {
45 id: params.args.where.id,
46 organization: { id: context.organizationId },
47 },
48 });
49
50 if (!record) {
51 throw new Error('Record not found or access denied');
52 }
53 }
54 }
55
56 return next(params);
57});
58
59export { prisma };Tenant Middleware#
1// middleware.ts
2import { clerkMiddleware, createRouteMatcher } from '@clerk/nextjs/server';
3import { NextResponse } from 'next/server';
4
5const isPublicRoute = createRouteMatcher([
6 '/',
7 '/sign-in(.*)',
8 '/sign-up(.*)',
9]);
10
11export default clerkMiddleware(async (auth, request) => {
12 if (isPublicRoute(request)) {
13 return NextResponse.next();
14 }
15
16 const { userId } = await auth();
17 if (!userId) {
18 return auth.redirectToSignIn();
19 }
20
21 // Extract organization from subdomain or path
22 const host = request.headers.get('host') || '';
23 const subdomain = host.split('.')[0];
24
25 // Or from path: /org/[slug]/...
26 const pathname = request.nextUrl.pathname;
27 const orgSlugMatch = pathname.match(/^\/org\/([^/]+)/);
28 const orgSlug = orgSlugMatch?.[1] || subdomain;
29
30 if (orgSlug && orgSlug !== 'www' && orgSlug !== 'app') {
31 // Set organization context in headers
32 const response = NextResponse.next();
33 response.headers.set('x-organization-slug', orgSlug);
34 return response;
35 }
36
37 return NextResponse.next();
38});Organization Service#
1// lib/services/organization-service.ts
2import { prisma } from '@/lib/prisma';
3import { MemberRole } from '@prisma/client';
4
5export async function createOrganization(
6 userId: string,
7 data: { name: string; slug: string }
8) {
9 return prisma.organization.create({
10 data: {
11 name: data.name,
12 slug: data.slug,
13 members: {
14 create: {
15 userId,
16 role: 'OWNER',
17 },
18 },
19 },
20 include: {
21 members: true,
22 },
23 });
24}
25
26export async function getOrganizationBySlug(slug: string) {
27 return prisma.organization.findUnique({
28 where: { slug },
29 });
30}
31
32export async function getUserOrganizations(userId: string) {
33 return prisma.organization.findMany({
34 where: {
35 members: {
36 some: { userId },
37 },
38 },
39 include: {
40 members: {
41 where: { userId },
42 select: { role: true },
43 },
44 },
45 });
46}
47
48export async function getMemberRole(
49 userId: string,
50 organizationId: string
51): Promise<MemberRole | null> {
52 const member = await prisma.member.findUnique({
53 where: {
54 userId_organizationId: {
55 userId,
56 organizationId,
57 },
58 },
59 select: { role: true },
60 });
61
62 return member?.role || null;
63}
64
65export async function addMember(
66 organizationId: string,
67 userId: string,
68 role: MemberRole = 'MEMBER'
69) {
70 return prisma.member.create({
71 data: {
72 organizationId,
73 userId,
74 role,
75 },
76 });
77}
78
79export async function removeMember(organizationId: string, userId: string) {
80 return prisma.member.delete({
81 where: {
82 userId_organizationId: {
83 userId,
84 organizationId,
85 },
86 },
87 });
88}Tenant-Scoped API Routes#
1// app/api/org/[slug]/projects/route.ts
2import { auth } from '@clerk/nextjs/server';
3import { NextRequest, NextResponse } from 'next/server';
4import { prisma } from '@/lib/prisma';
5import { getMemberRole, getOrganizationBySlug } from '@/lib/services/organization-service';
6import { runWithTenant } from '@/lib/tenant-context';
7
8export async function GET(
9 request: NextRequest,
10 { params }: { params: { slug: string } }
11) {
12 const { userId } = await auth();
13 if (!userId) {
14 return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
15 }
16
17 const organization = await getOrganizationBySlug(params.slug);
18 if (!organization) {
19 return NextResponse.json({ error: 'Organization not found' }, { status: 404 });
20 }
21
22 const role = await getMemberRole(userId, organization.id);
23 if (!role) {
24 return NextResponse.json({ error: 'Access denied' }, { status: 403 });
25 }
26
27 // Run query with tenant context
28 const projects = await runWithTenant(
29 { organizationId: organization.id, userId, role },
30 () =>
31 prisma.project.findMany({
32 where: { organizationId: organization.id },
33 include: { _count: { select: { tasks: true } } },
34 orderBy: { createdAt: 'desc' },
35 })
36 );
37
38 return NextResponse.json(projects);
39}
40
41export async function POST(
42 request: NextRequest,
43 { params }: { params: { slug: string } }
44) {
45 const { userId } = await auth();
46 if (!userId) {
47 return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
48 }
49
50 const organization = await getOrganizationBySlug(params.slug);
51 if (!organization) {
52 return NextResponse.json({ error: 'Organization not found' }, { status: 404 });
53 }
54
55 const role = await getMemberRole(userId, organization.id);
56 if (!role || role === 'VIEWER') {
57 return NextResponse.json({ error: 'Access denied' }, { status: 403 });
58 }
59
60 const body = await request.json();
61
62 const project = await prisma.project.create({
63 data: {
64 name: body.name,
65 organizationId: organization.id,
66 },
67 });
68
69 return NextResponse.json(project);
70}Role-Based Access Control#
1// lib/rbac.ts
2import { MemberRole } from '@prisma/client';
3
4type Permission =
5 | 'read:projects'
6 | 'write:projects'
7 | 'delete:projects'
8 | 'manage:members'
9 | 'manage:billing'
10 | 'manage:settings';
11
12const rolePermissions: Record<MemberRole, Permission[]> = {
13 OWNER: [
14 'read:projects',
15 'write:projects',
16 'delete:projects',
17 'manage:members',
18 'manage:billing',
19 'manage:settings',
20 ],
21 ADMIN: [
22 'read:projects',
23 'write:projects',
24 'delete:projects',
25 'manage:members',
26 'manage:settings',
27 ],
28 MEMBER: ['read:projects', 'write:projects'],
29 VIEWER: ['read:projects'],
30};
31
32export function hasPermission(
33 role: MemberRole,
34 permission: Permission
35): boolean {
36 return rolePermissions[role]?.includes(permission) || false;
37}
38
39export function requirePermission(role: MemberRole, permission: Permission) {
40 if (!hasPermission(role, permission)) {
41 throw new Error(`Permission denied: ${permission}`);
42 }
43}Organization Switcher Component#
1// components/OrganizationSwitcher.tsx
2'use client';
3
4import { useState, useEffect } from 'react';
5import { useRouter } from 'next/navigation';
6import { Building2, ChevronDown, Plus, Check } from 'lucide-react';
7
8interface Organization {
9 id: string;
10 name: string;
11 slug: string;
12 members: { role: string }[];
13}
14
15export function OrganizationSwitcher({
16 currentSlug,
17}: {
18 currentSlug: string;
19}) {
20 const [orgs, setOrgs] = useState<Organization[]>([]);
21 const [isOpen, setIsOpen] = useState(false);
22 const router = useRouter();
23
24 useEffect(() => {
25 fetch('/api/user/organizations')
26 .then((res) => res.json())
27 .then(setOrgs);
28 }, []);
29
30 const currentOrg = orgs.find((o) => o.slug === currentSlug);
31
32 const switchOrg = (slug: string) => {
33 router.push(`/org/${slug}`);
34 setIsOpen(false);
35 };
36
37 return (
38 <div className="relative">
39 <button
40 onClick={() => setIsOpen(!isOpen)}
41 className="flex items-center gap-2 px-3 py-2 rounded-lg hover:bg-gray-100 dark:hover:bg-gray-800"
42 >
43 <Building2 className="w-5 h-5 text-gray-500" />
44 <span className="font-medium">{currentOrg?.name || 'Select Org'}</span>
45 <ChevronDown className="w-4 h-4 text-gray-400" />
46 </button>
47
48 {isOpen && (
49 <>
50 <div
51 className="fixed inset-0 z-40"
52 onClick={() => setIsOpen(false)}
53 />
54 <div className="absolute left-0 top-full mt-1 z-50 w-64 bg-white dark:bg-gray-900 rounded-lg shadow-xl border">
55 <div className="p-2">
56 {orgs.map((org) => (
57 <button
58 key={org.id}
59 onClick={() => switchOrg(org.slug)}
60 className="w-full flex items-center gap-3 px-3 py-2 rounded-lg hover:bg-gray-100 dark:hover:bg-gray-800"
61 >
62 <Building2 className="w-5 h-5 text-gray-400" />
63 <div className="flex-1 text-left">
64 <p className="font-medium">{org.name}</p>
65 <p className="text-xs text-gray-500">{org.members[0]?.role}</p>
66 </div>
67 {org.slug === currentSlug && (
68 <Check className="w-4 h-4 text-green-500" />
69 )}
70 </button>
71 ))}
72 </div>
73 <div className="border-t p-2">
74 <button
75 onClick={() => router.push('/org/new')}
76 className="w-full flex items-center gap-3 px-3 py-2 rounded-lg hover:bg-gray-100 dark:hover:bg-gray-800 text-brand-600"
77 >
78 <Plus className="w-5 h-5" />
79 <span>Create Organization</span>
80 </button>
81 </div>
82 </div>
83 </>
84 )}
85 </div>
86 );
87}