Multi-Tenant Applications with Supabase

-- Organizations (tenants)
CREATE TABLE organizations (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  slug text UNIQUE NOT NULL,
  created_at timestamptz DEFAULT NOW()
);

-- Organization memberships
CREATE TABLE memberships (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  org_id uuid REFERENCES organizations(id) ON DELETE CASCADE,
  role text NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member')),
  created_at timestamptz DEFAULT NOW(),
  UNIQUE(user_id, org_id)
);

-- Tenant-scoped data
CREATE TABLE projects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  org_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 timestamptz DEFAULT NOW()
);

-- Enable RLS on all tables
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE memberships ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Helper function: check if user is a member of an org
CREATE OR REPLACE FUNCTION is_org_member(org_id uuid)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
SET search_path = ''
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.memberships
    WHERE memberships.user_id = auth.uid()
      AND memberships.org_id = is_org_member.org_id
  );
$$;

-- Helper: check role within an org
CREATE OR REPLACE FUNCTION get_org_role(org_id uuid)
RETURNS text
LANGUAGE sql
SECURITY DEFINER
SET search_path = ''
AS $$
  SELECT role FROM public.memberships
  WHERE memberships.user_id = auth.uid()
    AND memberships.org_id = get_org_role.org_id;
$$;

-- Organizations: members can view their orgs
CREATE POLICY "Members can view their organizations"
  ON organizations FOR SELECT
  USING (is_org_member(id));

-- Projects: members can view org projects
CREATE POLICY "Members can view org projects"
  ON projects FOR SELECT
  USING (is_org_member(org_id));

-- Projects: only admins/owners can create
CREATE POLICY "Admins can create projects"
  ON projects FOR INSERT
  WITH CHECK (
    get_org_role(org_id) IN ('owner', 'admin')
  );

-- Projects: only admins/owners can delete
CREATE POLICY "Admins can delete projects"
  ON projects FOR DELETE
  USING (
    get_org_role(org_id) IN ('owner', 'admin')
  );

// Create an invitation
async function inviteToOrg(orgId: string, email: string, role: string) {
  const { data, error } = await supabase
    .from('invitations')
    .insert({
      org_id: orgId,
      email,
      role,
      token: crypto.randomUUID(),
      expires_at: new Date(Date.now() + 7 * 24 * 60 * 60 * 1000), // 7 days
    })
    .select()
    .single()

  // Send invitation email via Edge Function or server action
  await sendInvitationEmail(email, data.token)
  return data
}

// Accept an invitation
async function acceptInvitation(token: string) {
  const { data: { user } } = await supabase.auth.getUser()

  const { data: invite } = await supabase
    .from('invitations')
    .select('*')
    .eq('token', token)
    .eq('email', user.email)
    .gt('expires_at', new Date().toISOString())
    .single()

  if (!invite) throw new Error('Invalid or expired invitation')

  // Create the membership
  await supabase.from('memberships').insert({
    user_id: user.id,
    org_id: invite.org_id,
    role: invite.role,
  })

  // Mark invitation as accepted
  await supabase.from('invitations')
    .update({ accepted_at: new Date().toISOString() })
    .eq('id', invite.id)
}

// Fetch all orgs the user belongs to
async function getUserOrgs() {
  const { data } = await supabase
    .from('memberships')
    .select('role, organizations(id, name, slug)')
    .eq('user_id', (await supabase.auth.getUser()).data.user?.id)

  return data
}

// All queries scoped to active org
async function getOrgProjects(orgId: string) {
  const { data } = await supabase
    .from('projects')
    .select('*')
    .eq('org_id', orgId)      // RLS enforces this is allowed
    .order('created_at', { ascending: false })

  return data
}