Multi-Tenant SaaS Architecture in 2026: Next.js, Supabase RLS & Clerk Organizations

-- ══════════════════════════════════════════════
-- MULTI-TENANT SCHEMA — Production Ready 2026
-- ══════════════════════════════════════════════ 

 -- ORGANIZATIONS table — the central tenant entity 
 create table   organizations  (
  id uuid  primary key default  gen_random_uuid(),
  name text  not null ,
  slug text  unique not null ,          -- used for subdomain: slug.yourapp.com 
  plan text  not null default   'free' ,     -- 'free' | 'pro' | 'enterprise' 
  stripe_customer_id text,
  stripe_subscription_id text,
  max_members integer  default   5 ,
  created_at timestamptz  default  now()
);

 -- ORGANIZATION MEMBERS — who belongs to which org + their role 
 create table   organization_members  (
  id uuid  primary key default  gen_random_uuid(),
  organization_id uuid  references  organizations(id)  on delete cascade ,
  user_id uuid  references  auth.users(id)  on delete cascade ,
  role text  not null default   'member' ,   -- 'owner' | 'admin' | 'member' 
  invited_by uuid  references  auth.users(id),
  joined_at timestamptz  default  now(),
   unique (organization_id, user_id)
);

 -- CORE DATA TABLE — every tenant's data in one table 
 create table   projects  (
  id uuid  primary key default  gen_random_uuid(),
  organization_id uuid  not null references  organizations(id)  on delete cascade ,
  created_by uuid  references  auth.users(id),
  name text  not null ,
  description text,
  status text  default   'active' ,
  created_at timestamptz  default  now()
);

 -- Helper function: get current user's organization memberships
-- Called by RLS policies — runs with SECURITY DEFINER to bypass RLS 
 create or replace function   get_user_org_ids ()
 returns  uuid[]
 language sql security definer stable as 
$$
   select   array_agg (organization_id)
   from  organization_members
   where  user_id = auth. uid ();
$$;

 -- ══ ENABLE ROW LEVEL SECURITY ══ 
 alter table  organizations  enable row level security ;
 alter table  organization_members  enable row level security ;
 alter table  projects  enable row level security ;

 -- ══ RLS POLICIES ══ 

 -- Organizations: users can only see orgs they belong to 
 create policy   "Members can view their organizations" 
   on  organizations  for select using  (
    id =  any ( get_user_org_ids ())
  );

 -- Only owners can update organization settings 
 create policy   "Owners can update organization" 
   on  organizations  for update using  (
    id  in  (
       select  organization_id  from  organization_members
       where  user_id = auth. uid ()  and  role =  'owner' 
    )
  );

 -- Projects: only visible to org members 
 create policy   "Org members can view projects" 
   on  projects  for select using  (
    organization_id =  any ( get_user_org_ids ())
  );

 create policy   "Org members can create projects" 
   on  projects  for insert with check  (
    organization_id =  any ( get_user_org_ids ())
  );

 -- Admins and owners can delete; members cannot 
 create policy   "Admins can delete projects" 
   on  projects  for delete using  (
    organization_id  in  (
       select  organization_id  from  organization_members
       where  user_id = auth. uid ()  and  role  in  ( 'owner' ,  'admin' )
    )
  );

 -- Indexes for performance — critical when filtering by org_id 
 create index   org_members_user_idx   on  organization_members (user_id);
 create index   org_members_org_idx   on  organization_members (organization_id);
 create index   projects_org_idx   on  projects (organization_id);

'use server' 

 import  {  createClient  }  from   '@/lib/supabase/server' 
 import  {  auth ,  currentUser  }  from   '@clerk/nextjs/server' 
 import  { z }  from   'zod' 

 // Get the active organization from Clerk session 
 async function   getActiveOrgId ():  Promise < string > {
   const  { orgId } =  await   auth ()
   if  (!orgId)  throw new   Error ( 'No active organization' )
   return  orgId
}

 // List projects — RLS ensures only this org's projects are returned
// No manual WHERE clause needed — Supabase enforces it via RLS 
 export async function   getProjects () {
   const  supabase =  createClient ()
   const  orgId =  await   getActiveOrgId ()

   const  { data, error } =  await  supabase
    .from( 'projects' )
    .select( '*, created_by_user:profiles(full_name, avatar_url)' )
    .eq( 'organization_id' , orgId)   // Extra safety — RLS also enforces this 
    .order( 'created_at' , { ascending:  false  })

   if  (error)  throw  error
   return  data
}

 // Create a project — RLS checks that user belongs to this org 
 const  createProjectSchema = z. object ({
  name: z. string (). min ( 1 ). max ( 100 ),
  description: z. string (). optional (),
})

 export async function   createProject (formData: FormData) {
   const  supabase =  createClient ()
   const  orgId =  await   getActiveOrgId ()

   // Check org plan limits before creating 
   const  { count } =  await  supabase
    .from( 'projects' )
    .select( '*' , { count:  'exact' , head:  true  })
    .eq( 'organization_id' , orgId)

   const  { data: org } =  await  supabase
    .from( 'organizations' )
    .select( 'plan' )
    .eq( 'id' , orgId)
    .single()

   const  projectLimits = { free:  3 , pro:  50 , enterprise:  Infinity  }
   const  limit = projectLimits[org?.plan  as   keyof typeof  projectLimits] ??  3 

   if  (count !==  null  && count >= limit) {
     throw new   Error ( `Project limit reached for your plan. Upgrade to create more.` )
  }

   const  input = createProjectSchema. parse ({
    name: formData. get ( 'name' ),
    description: formData. get ( 'description' ) ||  undefined ,
  })

   const  user =  await   currentUser ()
   const  { data, error } =  await  supabase
    .from( 'projects' )
    .insert({ ...input, organization_id: orgId, created_by: user?.id })
    .select()
    .single()

   if  (error)  throw  error
   return  data
}

import  {  clerkMiddleware ,  createRouteMatcher  }  from   '@clerk/nextjs/server' 
 import  {  NextResponse  }  from   'next/server' 

 const  isPublicRoute =  createRouteMatcher ([
   '/' ,
   '/sign-in(.*)' ,
   '/sign-up(.*)' ,
   '/accept-invitation(.*)' ,   // Public — user clicks invite link before auth 
])

 export default   clerkMiddleware ( async  (auth, req) => {
   const  { userId, orgId, orgRole } =  await  auth()
   const  { pathname, hostname } = req.nextUrl

   // Protect non-public routes 
   if  (! isPublicRoute (req)) {
     await  auth. protect ()
  }

   // If authenticated but no org selected, redirect to org selector 
   if  (userId && !orgId && pathname. startsWith ( '/dashboard' )) {
     return  NextResponse. redirect ( new   URL ( '/select-org' , req.url))
  }

   // SUBDOMAIN ROUTING: org.yourapp.com → /dashboard/[org]
  // Extract org slug from subdomain in production 
   const  subdomain = hostname. split ( '.' )[ 0 ]
   if  (subdomain && subdomain !==  'www'  && subdomain !==  'app' ) {
     // Rewrite to org-specific routes while keeping URL clean 
     const  rewriteUrl = req.nextUrl. clone ()
    rewriteUrl.pathname =  `/org/${subdomain}${pathname}` 
     return  NextResponse. rewrite (rewriteUrl)
  }
})

 export const  config = {
  matcher: [ '/((?!_next|[^?]*\\.(?:html?|css|js(?!on)|jpe?g|webp|png|gif|svg|ttf|woff2?|ico|csv|docx?|xlsx?|zip|webmanifest)).*)','/(api|trpc)(.*)' ]
}

'use client' 

 import  {
   useOrganization ,
   useOrganizationList ,
   OrganizationProfile ,
   CreateOrganization ,
}  from   '@clerk/nextjs' 

 // Display org members and invite form — entirely from Clerk 
 export default function   TeamSettings () {
   const  { organization, membership, memberships } =  useOrganization ({
    memberships: { pageSize:  20  }
  })

   const  isAdmin = [ 'admin' ,  'org:admin' ]. includes (membership?.role ??  '' )
   const  isOwner = membership?.role ===  'org:owner' 

   return  (
    <div>
      <h1>{organization?.name}</h1>
      <p>{memberships?.data?.length} members</p>

      { /* Clerk's built-in org management UI — handles invites, roles, removal */ }
      < OrganizationProfile 
        appearance={{
          elements: {
            card:  'bg-transparent shadow-none border border-border rounded-xl' ,
            navbar:  'hidden' ,   // Hide Clerk's nav — use your own 
          }
        }}
      />

      { /* Or build custom invite UI using Clerk's hooks */ }
      {isAdmin && < InviteMemberForm  orgId={organization?.id} />}

      { /* Members list */ }
      <ul>
        {memberships?.data?. map (m => (
          <li key={m.id}>
            {m.publicUserData?.firstName} — {m.role}
            {isOwner && m.role !==  'org:owner'  && (
              <button onClick={() => m. destroy ()}>Remove</button>
            )}
          </li>
        ))}
      </ul>
    </div>
  )
}

 // Custom invite form using Clerk's useOrganization hook 
 function   InviteMemberForm ({ orgId }: { orgId?:  string  }) {
   const  { organization } =  useOrganization ()
   const  [email,  setEmail ] =  useState ( '' )
   const  [role,  setRole ] =  useState ( 'org:member' )

   async function   invite () {
     await  organization?. inviteMember ({ emailAddress: email, role })
     setEmail ( '' )
  }

   return  (
    <div>
      <input value={email} onChange={e =>  setEmail (e.target.value)} placeholder= "[email protected]"  />
      <select value={role} onChange={e =>  setRole (e.target.value)}>
        <option value= "org:member" >Member</option>
        <option value= "org:admin" >Admin</option>
      </select>
      <button onClick={ invite }>Send Invitation</button>
    </div>
  )
}

import  {  notFound  }  from   'next/navigation' 
 import  {  auth  }  from   '@clerk/nextjs/server' 
 import  {  createClient  }  from   '@/lib/supabase/server' 

 interface   OrgLayoutProps  {
  children: React. ReactNode 
  params:  Promise <{ org:  string  }>
}

 export default async function   OrgLayout ({ children, params }:  OrgLayoutProps ) {
   const  { org: orgSlug } =  await  params
   const  { userId, orgId } =  await   auth ()

   if  (!userId)  notFound ()

   const  supabase =  createClient ()

   // Verify user has access to this org slug 
   const  { data: org, error } =  await  supabase
    .from( 'organizations' )
    .select( 'id, name, slug, plan' )
    .eq( 'slug' , orgSlug)
    .single()

   if  (error || !org)  notFound ()

   // Confirm this user is a member of this org
  // (RLS also handles this, but explicit check gives better UX) 
   const  { data: member } =  await  supabase
    .from( 'organization_members' )
    .select( 'role' )
    .eq( 'organization_id' , org.id)
    .single()

   if  (!member)  notFound ()

   return  (
    <div>
       {/* Org context available to all child pages via layout */} 
      <OrgNavbar org={org} userRole={member.role} />
      <main>{children}</main>
    </div>
  )
}

 // Path-based alternative (no subdomain):
// app/dashboard/[org]/page.tsx → yourapp.com/dashboard/acme
// Works identically but uses path instead of subdomain

{
   "rewrites" : [
    {
       "source" :  "/:path*" ,
       "has" : [
        {
           "type" :  "host" ,
           "value" :  "(?<org>[^.]+).yourapp.com" 
        }
      ],
       "destination" :  "/org/:org/:path*" 
    }
  ]
}

 // DNS: Add wildcard CNAME *.yourapp.com → cname.vercel-dns.com
// Vercel project: Add *.yourapp.com as a custom domain
// This routes all *.yourapp.com requests to your app

'use server' 

 import  { stripe }  from   '@/lib/stripe' 
 import  {  createClient  }  from   '@/lib/supabase/server' 
 import  {  auth  }  from   '@clerk/nextjs/server' 
 import  {  redirect  }  from   'next/navigation' 

 export async function   createOrgCheckout (priceId:  string , seats:  number  =  1 ) {
   const  { orgId } =  await   auth ()
   if  (!orgId)  throw new   Error ( 'No organization' )

   const  supabase =  createClient ()
   const  { data: org } =  await  supabase
    .from( 'organizations' )
    .select( 'name, stripe_customer_id' )
    .eq( 'id' , orgId)
    .single()

   // Get or create Stripe Customer for this organization 
   let  customerId = org?.stripe_customer_id
   if  (!customerId) {
     const  customer =  await  stripe.customers. create ({
      name: org?.name,
      metadata: { organization_id: orgId, platform:  'codebitbybit-saas-2026'  }
    })
    customerId = customer.id
     await  supabase
      .from( 'organizations' )
      .update({ stripe_customer_id: customerId })
      .eq( 'id' , orgId)
  }

   // Per-seat subscription checkout 
   const  session =  await  stripe.checkout.sessions. create ({
    customer: customerId,
    mode:  'subscription' ,
    line_items: [{
      price: priceId,
      quantity: seats,    // Number of seats to charge for 
    }],
    subscription_data: {
      metadata: { organization_id: orgId },
    },
     // Allow adjusting seat count during checkout 
    adjustable_quantity: {
      enabled:  true ,
      minimum:  1 ,
      maximum:  100 ,
    },
    success_url:  `${process.env.NEXT_PUBLIC_APP_URL}/dashboard?upgraded=1` ,
    cancel_url:  `${process.env.NEXT_PUBLIC_APP_URL}/pricing` ,
    automatic_tax: { enabled:  true  },
  })

   redirect (session.url!)
}

 // When a member is added, increase seat count 
 export async function   addSeat (orgId:  string ) {
   const  supabase =  createClient ()
   const  { data: org } =  await  supabase
    .from( 'organizations' )
    .select( 'stripe_subscription_id' )
    .eq( 'id' , orgId)
    .single()

   if  (!org?.stripe_subscription_id)  return 

   const  subscription =  await  stripe.subscriptions. retrieve (org.stripe_subscription_id)
   const  item = subscription.items.data[ 0 ]

   // Increase seat quantity by 1 
   await  stripe.subscriptionItems. update (item.id, {
    quantity: (item.quantity ??  1 ) +  1 ,
    proration_behavior:  'create_prorations' ,   // Bill immediately for new seat 
  })
}

import  {  Webhook  }  from   'svix' 
 import  { headers }  from   'next/headers' 
 import  {  createAdminClient  }  from   '@/lib/supabase/admin' 

 export async function   POST (req: Request) {
   const  wh =  new   Webhook (process.env.CLERK_WEBHOOK_SECRET!)
   const  hdrs =  await   headers ()
   const  body =  await  req. text ()

   let  event:  any 
   try  {
    event = wh. verify (body, {
       'svix-id' : hdrs. get ( 'svix-id' )!,
       'svix-timestamp' : hdrs. get ( 'svix-timestamp' )!,
       'svix-signature' : hdrs. get ( 'svix-signature' )!,
    })
  }  catch  {
     return   Response . json ({ error:  'Invalid signature'  }, { status:  400  })
  }

   const  supabase =  createAdminClient ()

   switch  (event.type) {
     case   'organizationMembership.created' : {
       const  { organization, public_user_data, role } = event.data
       await  supabase.from( 'organization_members' ). upsert ({
        organization_id: organization.id,
        user_id: public_user_data.user_id,
        role: role. replace ( 'org:' ,  '' ),   // 'org:admin' → 'admin' 
      }, { onConflict:  'organization_id,user_id'  })
       break 
    }

     case   'organizationMembership.deleted' : {
       const  { organization, public_user_data } = event.data
       await  supabase.from( 'organization_members' ). delete ()
        .eq( 'organization_id' , organization.id)
        .eq( 'user_id' , public_user_data.user_id)
       // Also decrease Stripe seat count 
       await   removeSeat (organization.id)
       break 
    }

     case   'organizationMembership.updated' : {
       const  { organization, public_user_data, role } = event.data
       await  supabase.from( 'organization_members' )
        .update({ role: role. replace ( 'org:' ,  '' ) })
        .eq( 'organization_id' , organization.id)
        .eq( 'user_id' , public_user_data.user_id)
       break 
    }

     case   'organization.created' : {
       const  org = event.data
       await  supabase.from( 'organizations' ). upsert ({
        id: org.id,
        name: org.name,
        slug: org.slug,
        plan:  'free' ,
      }, { onConflict:  'id'  })
       break 
    }
  }

   return   Response . json ({ received:  true  })
}

import  {  createClient  }  from   '@/lib/supabase/server' 
 import  {  auth  }  from   '@clerk/nextjs/server' 
 import  {  cache  }  from   'react' 

 type   Plan  =  'free'  |  'pro'  |  'enterprise' 

 const  PLAN_FEATURES: Record< Plan ,  OrgFeatures > = {
  free: {
    maxProjects:  3 ,
    maxMembers:  3 ,
    hasAPIAccess:  false ,
    hasSSOLogin:  false ,
    hasCustomDomain:  false ,
    hasAuditLog:  false ,
    hasPrioritySupport:  false ,
    storageGB:  5 ,
  },
  pro: {
    maxProjects:  50 ,
    maxMembers:  25 ,
    hasAPIAccess:  true ,
    hasSSOLogin:  false ,
    hasCustomDomain:  true ,
    hasAuditLog:  true ,
    hasPrioritySupport:  true ,
    storageGB:  100 ,
  },
  enterprise: {
    maxProjects:  Infinity ,
    maxMembers:  Infinity ,
    hasAPIAccess:  true ,
    hasSSOLogin:  true ,
    hasCustomDomain:  true ,
    hasAuditLog:  true ,
    hasPrioritySupport:  true ,
    storageGB:  Infinity ,
  },
}

 interface   OrgFeatures  {
  maxProjects:  number 
  maxMembers:  number 
  hasAPIAccess:  boolean 
  hasSSOLogin:  boolean 
  hasCustomDomain:  boolean 
  hasAuditLog:  boolean 
  hasPrioritySupport:  boolean 
  storageGB:  number 
}

 // cache() deduplicates within a single request — only hits DB once 
 export const   getOrgFeatures  =  cache ( async  ():  Promise < OrgFeatures > => {
   const  { orgId } =  await   auth ()
   if  (!orgId)  return  PLAN_FEATURES.free

   const  supabase =  createClient ()
   const  { data: org } =  await  supabase
    .from( 'organizations' )
    .select( 'plan' )
    .eq( 'id' , orgId)
    .single()

   return  PLAN_FEATURES[(org?.plan  as   Plan ) ??  'free' ]
})

 // Usage in a Server Component: 
 export default async function   APISettingsPage () {
   const  features =  await   getOrgFeatures ()

   if  (!features.hasAPIAccess) {
     return  < UpgradePrompt  feature= "API access"  requiredPlan= "pro"  />
  }

   return  < APIKeyManager  />
}