Build a Multi-Tenant SaaS on Supabase in a Day

-- Tenants (organisations)
CREATE TABLE public.tenants (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name       text NOT NULL,
  slug       text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Memberships link auth.users to tenants
CREATE TABLE public.memberships (
  tenant_id  uuid NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  user_id    uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role       text NOT NULL DEFAULT 'member',
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, user_id)
);

-- Helper function: tenant ids the current user belongs to
CREATE OR REPLACE FUNCTION public.my_tenants()
RETURNS SETOF uuid
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public
AS $$
  SELECT tenant_id FROM memberships WHERE user_id = auth.uid()
$$;

-- A business entity: projects
CREATE TABLE public.projects (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id  uuid NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name       text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX projects_tenant_idx ON projects (tenant_id);

-- RLS
ALTER TABLE memberships ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Members read their memberships" ON memberships FOR SELECT TO authenticated
  USING (user_id = auth.uid());

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Members read projects" ON projects FOR SELECT TO authenticated
  USING (tenant_id IN (SELECT public.my_tenants()));
CREATE POLICY "Members write projects" ON projects FOR ALL TO authenticated
  USING (tenant_id IN (SELECT public.my_tenants()))
  WITH CHECK (tenant_id IN (SELECT public.my_tenants()));

"use server";

import { createClient } from "@supabase/supabase-js";

const admin = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!,
);

export async function createTenant(name: string, slug: string, ownerId: string) {
  const { data: tenant, error } = await admin
    .from("tenants")
    .insert({ name, slug })
    .select()
    .single();
  if (error) throw error;

  // Owner becomes the first member, with the owner role
  await admin.from("memberships").insert({
    tenant_id: tenant.id,
    user_id:   ownerId,
    role:      "owner",
  });
  return tenant;
}

export async function inviteToTenant(
  tenantId: string,
  email: string,
  invitedBy: string,
) {
  // Use GoTrue admin invite (sends a magic link)
  const { data, error } = await admin.auth.admin.inviteUserByEmail(email, {
    data: { invited_to_tenant: tenantId, invited_by: invitedBy },
  });
  if (error) throw error;
  return data;
}

import { createServerClient } from "@supabase/ssr";
import { cookies } from "next/headers";

export default async function ProjectsPage({
  params,
}: {
  params: { tenant: string };
}) {
  const c = cookies();
  const sb = createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    { cookies: { get: (n) => c.get(n)?.value } },
  );

  const { data: tenant } = await sb
    .from("tenants")
    .select("id, name")
    .eq("slug", params.tenant)
    .single();

  // RLS already restricts; we don't need to filter on tenant_id again,
  // but doing it explicitly is good defence in depth.
  const { data: projects } = await sb
    .from("projects")
    .select("id, name")
    .eq("tenant_id", tenant!.id)
    .order("created_at", { ascending: false });

  return (
    <ul>
      {projects?.map((p) => (
        <li key={p.id}>{p.name}</li>
      ))}
    </ul>
  );
}