Supabase Multi-Tenancy: Building SaaS Applications

-- Organizations table (tenants)
create table organizations (
  id uuid default gen_random_uuid() primary key,
  name text not null,
  slug text unique not null,
  plan text not null default 'free', -- free, pro, enterprise
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- Organization members (team membership)
create table organization_members (
  id uuid default gen_random_uuid() primary key,
  organization_id uuid references organizations(id) on delete cascade not null,
  user_id uuid references auth.users(id) on delete cascade not null,
  role text not null default 'member', -- owner, admin, member
  created_at timestamp with time zone default now(),
  unique(organization_id, user_id)
);

create index org_members_org_id_idx on organization_members(organization_id);
create index org_members_user_id_idx on organization_members(user_id);

-- Multi-tenant data tables (with tenant_id)
create table projects (
  id uuid default gen_random_uuid() primary key,
  organization_id uuid references organizations(id) on delete cascade not null,
  name text not null,
  description text,
  created_by uuid references auth.users(id),
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

create index projects_org_id_idx on projects(organization_id);

create table tasks (
  id uuid default gen_random_uuid() primary key,
  organization_id uuid references organizations(id) on delete cascade not null,
  project_id uuid references projects(id) on delete cascade not null,
  title text not null,
  description text,
  assigned_to uuid references auth.users(id),
  status text not null default 'todo',
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

create index tasks_org_id_idx on tasks(organization_id);
create index tasks_project_id_idx on tasks(project_id);

-- Enable RLS on all tenant tables
alter table organizations enable row level security;
alter table organization_members enable row level security;
alter table projects enable row level security;
alter table tasks enable row level security;

-- Helper function: Get user's organizations
create or replace function get_user_organizations()
returns setof uuid
language sql
security definer
as $$
  select organization_id
  from organization_members
  where user_id = auth.uid();
$$;

-- Organizations policies
create policy "Users can view their organizations"
  on organizations for select
  using ( id in (select get_user_organizations()) );

create policy "Users can update organizations they own"
  on organizations for update
  using (
    exists (
      select 1 from organization_members
      where organization_id = id
      and user_id = auth.uid()
      and role = 'owner'
    )
  );

-- Organization members policies
create policy "Users can view members of their organizations"
  on organization_members for select
  using ( organization_id in (select get_user_organizations()) );

create policy "Admins can invite members"
  on organization_members for insert
  with check (
    exists (
      select 1 from organization_members
      where organization_id = organization_members.organization_id
      and user_id = auth.uid()
      and role in ('owner', 'admin')
    )
  );

-- Projects policies
create policy "Users can view projects in their organizations"
  on projects for select
  using ( organization_id in (select get_user_organizations()) );

create policy "Users can create projects in their organizations"
  on projects for insert
  with check ( organization_id in (select get_user_organizations()) );

create policy "Users can update projects in their organizations"
  on projects for update
  using ( organization_id in (select get_user_organizations()) );

create policy "Users can delete projects in their organizations"
  on projects for delete
  using ( organization_id in (select get_user_organizations()) );

-- Tasks policies (same pattern)
create policy "Users can view tasks in their organizations"
  on tasks for select
  using ( organization_id in (select get_user_organizations()) );

create policy "Users can create tasks in their organizations"
  on tasks for insert
  with check ( organization_id in (select get_user_organizations()) );

create policy "Users can update tasks in their organizations"
  on tasks for update
  using ( organization_id in (select get_user_organizations()) );

create policy "Users can delete tasks in their organizations"
  on tasks for delete
  using ( organization_id in (select get_user_organizations()) );

// contexts/OrganizationContext.tsx
import { createContext, useContext, useState, useEffect, ReactNode } from 'react'
import { supabase } from '../lib/supabaseClient'

interface Organization {
  id: string
  name: string
  slug: string
  plan: string
}

interface OrganizationContextType {
  currentOrg: Organization | null
  organizations: Organization[]
  switchOrganization: (orgId: string) => void
  loading: boolean
}

const OrganizationContext = createContext<OrganizationContextType | undefined>(undefined)

export function OrganizationProvider({ children }: { children: ReactNode }) {
  const [currentOrg, setCurrentOrg] = useState<Organization | null>(null)
  const [organizations, setOrganizations] = useState<Organization[]>([])
  const [loading, setLoading] = useState(true)

  useEffect(() => {
    fetchUserOrganizations()
  }, [])

  async function fetchUserOrganizations() {
    const { data: memberships } = await supabase
      .from('organization_members')
      .select(`
        organization_id,
        organizations (
          id,
          name,
          slug,
          plan
        )
      `)

    const orgs = memberships?.map(m => m.organizations).filter(Boolean) || []
    setOrganizations(orgs)

    // Set current org from localStorage or first org
    const savedOrgId = localStorage.getItem('currentOrgId')
    const org = orgs.find(o => o.id === savedOrgId) || orgs[0]
    setCurrentOrg(org || null)
    setLoading(false)
  }

  function switchOrganization(orgId: string) {
    const org = organizations.find(o => o.id === orgId)
    if (org) {
      setCurrentOrg(org)
      localStorage.setItem('currentOrgId', orgId)
    }
  }

  return (
    <OrganizationContext.Provider value={{
      currentOrg,
      organizations,
      switchOrganization,
      loading
    }}>
      {children}
    </OrganizationContext.Provider>
  )
}

export function useOrganization() {
  const context = useContext(OrganizationContext)
  if (!context) {
    throw new Error('useOrganization must be used within OrganizationProvider')
  }
  return context
}

// components/OrganizationSwitcher.tsx
import { useOrganization } from '../contexts/OrganizationContext'

export function OrganizationSwitcher() {
  const { currentOrg, organizations, switchOrganization } = useOrganization()

  return (
    <div className="org-switcher">
      <select
        value={currentOrg?.id || ''}
        onChange={(e) => switchOrganization(e.target.value)}
        className="org-select"
      >
        {organizations.map(org => (
          <option key={org.id} value={org.id}>
            {org.name} ({org.plan})
          </option>
        ))}
      </select>
    </div>
  )
}

// Usage in layout
import { OrganizationProvider } from './contexts/OrganizationContext'
import { OrganizationSwitcher } from './components/OrganizationSwitcher'

function App() {
  return (
    <OrganizationProvider>
      <nav>
        <OrganizationSwitcher />
      </nav>
      {/* Rest of app */}
    </OrganizationProvider>
  )
}

// hooks/useProjects.ts
import { useState, useEffect } from 'react'
import { supabase } from '../lib/supabaseClient'
import { useOrganization } from '../contexts/OrganizationContext'

export function useProjects() {
  const { currentOrg } = useOrganization()
  const [projects, setProjects] = useState([])
  const [loading, setLoading] = useState(true)

  useEffect(() => {
    if (currentOrg) {
      fetchProjects()
    }
  }, [currentOrg])

  async function fetchProjects() {
    if (!currentOrg) return

    setLoading(true)
    
    const { data, error } = await supabase
      .from('projects')
      .select('*')
      .eq('organization_id', currentOrg.id)
      .order('created_at', { ascending: false })

    if (!error) {
      setProjects(data || [])
    }
    setLoading(false)
  }

  async function createProject(name: string, description: string) {
    if (!currentOrg) return

    const { data, error } = await supabase
      .from('projects')
      .insert({
        organization_id: currentOrg.id,
        name,
        description
      })
      .select()
      .single()

    if (!error) {
      setProjects(prev => [data, ...prev])
    }

    return { data, error }
  }

  return { projects, loading, createProject, refetch: fetchProjects }
}

// Usage in component
function ProjectsList() {
  const { projects, loading, createProject } = useProjects()
  const { currentOrg } = useOrganization()

  if (!currentOrg) {
    return <div>Please select an organization</div>
  }

  if (loading) return <div>Loading...</div>

  return (
    <div>
      <h2>Projects for {currentOrg.name}</h2>
      {projects.map(project => (
        <div key={project.id}>
          <h3>{project.name}</h3>
          <p>{project.description}</p>
        </div>
      ))}
    </div>
  )
}

// Team invitation function
create or replace function invite_team_member(
  org_id uuid,
  invitee_email text,
  member_role text default 'member'
)
returns json
language plpgsql
security definer
as $$
declare
  inviter_role text;
  invitee_user_id uuid;
begin
  -- Check if inviter has permission
  select role into inviter_role
  from organization_members
  where organization_id = org_id
  and user_id = auth.uid();

  if inviter_role not in ('owner', 'admin') then
    raise exception 'Only owners and admins can invite members';
  end if;

  -- Check if user exists
  select id into invitee_user_id
  from auth.users
  where email = invitee_email;

  if invitee_user_id is null then
    -- Send invitation email (implement with Edge Function)
    return json_build_object(
      'status', 'invitation_sent',
      'email', invitee_email
    );
  end if;

  -- Add user to organization
  insert into organization_members (organization_id, user_id, role)
  values (org_id, invitee_user_id, member_role)
  on conflict (organization_id, user_id) do nothing;

  return json_build_object(
    'status', 'member_added',
    'user_id', invitee_user_id
  );
end;
$$;

// React component for team management
import { useState } from 'react'
import { supabase } from '../lib/supabaseClient'
import { useOrganization } from '../contexts/OrganizationContext'

function TeamManagement() {
  const { currentOrg } = useOrganization()
  const [members, setMembers] = useState([])
  const [email, setEmail] = useState('')
  const [role, setRole] = useState('member')

  async function inviteMember() {
    const { data, error } = await supabase
      .rpc('invite_team_member', {
        org_id: currentOrg.id,
        invitee_email: email,
        member_role: role
      })

    if (!error) {
      alert('Invitation sent!')
      setEmail('')
    }
  }

  return (
    <div>
      <h2>Team Members</h2>
      
      <div className="invite-form">
        <input
          type="email"
          value={email}
          onChange={(e) => setEmail(e.target.value)}
          placeholder="Email address"
        />
        <select value={role} onChange={(e) => setRole(e.target.value)}>
          <option value="member">Member</option>
          <option value="admin">Admin</option>
        </select>
        <button onClick={inviteMember}>Invite</button>
      </div>
    </div>
  )
}