User Invitation and Team Onboarding for Self-Hosted Supabase

Building a multi-user application on self-hosted Supabase inevitably leads to the same question: how do I let users invite their teammates? Unlike some managed platforms that offer built-in team invitation features, Supabase takes a different approach—it provides the primitives, and you build the invitation system that fits your specific needs.

This guide walks through implementing a complete user invitation and team onboarding system for self-hosted Supabase deployments. We'll cover database design, Edge Functions for handling invitations, email integration, and the frontend flows that tie everything together.

Why Supabase Doesn't Include Built-In Team Invitations

Before diving into implementation, it's worth understanding why Supabase Auth doesn't ship with a full team invitation feature. According to the Supabase team, invite systems are highly customizable—each implementation depends on how you structure your tenants, organizations, and permission models.

Some applications need simple flat teams. Others require nested organizations with complex role hierarchies. Some want invitations to expire after 24 hours; others want them valid indefinitely. Building all these options into the core auth system would bloat it beyond reason.

What Supabase does provide is supabase.auth.admin.inviteUserByEmail—a function that sends an invitation email and creates a user record. This is the foundation you'll build upon.

Prerequisites

This guide assumes you have:

• A running self-hosted Supabase instance with SMTP configured
• Edge Functions enabled (see our Edge Functions setup guide)
• Basic familiarity with Row Level Security
• A frontend application ready to integrate

If you're still setting up, Supascale can handle the infrastructure complexity while you focus on building your application logic.

Database Schema Design

First, let's design the tables that will power our invitation system. The schema needs to track teams, team memberships, and pending invitations.

-- Teams table
CREATE TABLE public.teams (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL
);

-- Team memberships
CREATE TABLE public.team_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  team_id UUID REFERENCES public.teams(id) ON DELETE CASCADE NOT NULL,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  role TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member')),
  joined_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(team_id, user_id)
);

-- Pending invitations
CREATE TABLE public.team_invitations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  team_id UUID REFERENCES public.teams(id) ON DELETE CASCADE NOT NULL,
  email TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('admin', 'member')),
  invited_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  expires_at TIMESTAMPTZ DEFAULT (now() + interval '7 days'),
  token TEXT UNIQUE DEFAULT encode(gen_random_bytes(32), 'hex'),
  accepted_at TIMESTAMPTZ,
  UNIQUE(team_id, email)
);

-- Index for faster lookups
CREATE INDEX idx_team_invitations_email ON public.team_invitations(email);
CREATE INDEX idx_team_invitations_token ON public.team_invitations(token);

This schema separates concerns cleanly. The team_invitations table tracks pending invites with expiration timestamps and unique tokens, while team_members records actual team membership after an invitation is accepted.

Setting Up Row Level Security

RLS policies ensure users can only access teams they belong to and invitations they're authorized to manage.

-- Enable RLS
ALTER TABLE public.teams ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.team_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.team_invitations ENABLE ROW LEVEL SECURITY;

-- Teams: Users can see teams they're members of
CREATE POLICY "Users can view their teams"
  ON public.teams FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM public.team_members
      WHERE team_members.team_id = teams.id
      AND team_members.user_id = auth.uid()
    )
  );

-- Team members: Users can view members of their teams
CREATE POLICY "Users can view team members"
  ON public.team_members FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM public.team_members AS tm
      WHERE tm.team_id = team_members.team_id
      AND tm.user_id = auth.uid()
    )
  );

-- Invitations: Only owners and admins can view/create invitations
CREATE POLICY "Admins can manage invitations"
  ON public.team_invitations FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM public.team_members
      WHERE team_members.team_id = team_invitations.team_id
      AND team_members.user_id = auth.uid()
      AND team_members.role IN ('owner', 'admin')
    )
  );

Creating the Invitation Edge Function

The core of our invitation system is an Edge Function that validates permissions, creates the invitation record, and sends the email. Create a file at supabase/functions/invite-to-team/index.ts:

import { serve } from "https://deno.land/[email protected]/http/server.ts";
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";

const corsHeaders = {
  "Access-Control-Allow-Origin": "*",
  "Access-Control-Allow-Headers": "authorization, x-client-info, apikey, content-type",
};

serve(async (req) => {
  if (req.method === "OPTIONS") {
    return new Response("ok", { headers: corsHeaders });
  }

  try {
    const { email, teamId, role = "member" } = await req.json();

    // Create client with user's auth context
    const supabaseClient = createClient(
      Deno.env.get("SUPABASE_URL") ?? "",
      Deno.env.get("SUPABASE_ANON_KEY") ?? "",
      {
        global: {
          headers: { Authorization: req.headers.get("Authorization")! },
        },
      }
    );

    // Verify the user is an owner or admin of the team
    const { data: membership, error: membershipError } = await supabaseClient
      .from("team_members")
      .select("role")
      .eq("team_id", teamId)
      .eq("user_id", (await supabaseClient.auth.getUser()).data.user?.id)
      .single();

    if (membershipError || !["owner", "admin"].includes(membership?.role)) {
      return new Response(
        JSON.stringify({ error: "Not authorized to invite users" }),
        { status: 403, headers: { ...corsHeaders, "Content-Type": "application/json" } }
      );
    }

    // Create invitation record
    const { data: invitation, error: inviteError } = await supabaseClient
      .from("team_invitations")
      .insert({
        team_id: teamId,
        email: email.toLowerCase().trim(),
        role,
        invited_by: (await supabaseClient.auth.getUser()).data.user?.id,
      })
      .select()
      .single();

    if (inviteError) {
      if (inviteError.code === "23505") {
        return new Response(
          JSON.stringify({ error: "User already invited to this team" }),
          { status: 400, headers: { ...corsHeaders, "Content-Type": "application/json" } }
        );
      }
      throw inviteError;
    }

    // Send invitation email using admin client
    const supabaseAdmin = createClient(
      Deno.env.get("SUPABASE_URL") ?? "",
      Deno.env.get("SUPABASE_SERVICE_ROLE_KEY") ?? ""
    );

    const inviteUrl = `${Deno.env.get("SITE_URL")}/accept-invite?token=${invitation.token}`;

    await supabaseAdmin.auth.admin.inviteUserByEmail(email, {
      redirectTo: inviteUrl,
      data: {
        team_id: teamId,
        invitation_token: invitation.token,
      },
    });

    return new Response(
      JSON.stringify({ success: true, invitation }),
      { headers: { ...corsHeaders, "Content-Type": "application/json" } }
    );
  } catch (error) {
    return new Response(
      JSON.stringify({ error: error.message }),
      { status: 500, headers: { ...corsHeaders, "Content-Type": "application/json" } }
    );
  }
});

Handling Invitation Acceptance

When a user clicks the invitation link, they need to be guided through signup (if new) or directly added to the team (if existing). Create another Edge Function at supabase/functions/accept-invite/index.ts:

import { serve } from "https://deno.land/[email protected]/http/server.ts";
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";

const corsHeaders = {
  "Access-Control-Allow-Origin": "*",
  "Access-Control-Allow-Headers": "authorization, x-client-info, apikey, content-type",
};

serve(async (req) => {
  if (req.method === "OPTIONS") {
    return new Response("ok", { headers: corsHeaders });
  }

  try {
    const { token } = await req.json();

    const supabaseAdmin = createClient(
      Deno.env.get("SUPABASE_URL") ?? "",
      Deno.env.get("SUPABASE_SERVICE_ROLE_KEY") ?? ""
    );

    // Find the invitation
    const { data: invitation, error: inviteError } = await supabaseAdmin
      .from("team_invitations")
      .select("*, teams(name)")
      .eq("token", token)
      .is("accepted_at", null)
      .gt("expires_at", new Date().toISOString())
      .single();

    if (inviteError || !invitation) {
      return new Response(
        JSON.stringify({ error: "Invalid or expired invitation" }),
        { status: 400, headers: { ...corsHeaders, "Content-Type": "application/json" } }
      );
    }

    // Get the authenticated user
    const supabaseClient = createClient(
      Deno.env.get("SUPABASE_URL") ?? "",
      Deno.env.get("SUPABASE_ANON_KEY") ?? "",
      {
        global: {
          headers: { Authorization: req.headers.get("Authorization")! },
        },
      }
    );

    const { data: { user }, error: userError } = await supabaseClient.auth.getUser();
    
    if (userError || !user) {
      return new Response(
        JSON.stringify({ error: "Authentication required" }),
        { status: 401, headers: { ...corsHeaders, "Content-Type": "application/json" } }
      );
    }

    // Verify the email matches
    if (user.email?.toLowerCase() !== invitation.email.toLowerCase()) {
      return new Response(
        JSON.stringify({ error: "Invitation is for a different email address" }),
        { status: 403, headers: { ...corsHeaders, "Content-Type": "application/json" } }
      );
    }

    // Add user to team
    const { error: memberError } = await supabaseAdmin
      .from("team_members")
      .insert({
        team_id: invitation.team_id,
        user_id: user.id,
        role: invitation.role,
      });

    if (memberError) {
      if (memberError.code === "23505") {
        return new Response(
          JSON.stringify({ error: "Already a member of this team" }),
          { status: 400, headers: { ...corsHeaders, "Content-Type": "application/json" } }
        );
      }
      throw memberError;
    }

    // Mark invitation as accepted
    await supabaseAdmin
      .from("team_invitations")
      .update({ accepted_at: new Date().toISOString() })
      .eq("id", invitation.id);

    return new Response(
      JSON.stringify({ 
        success: true, 
        team: { id: invitation.team_id, name: invitation.teams.name }
      }),
      { headers: { ...corsHeaders, "Content-Type": "application/json" } }
    );
  } catch (error) {
    return new Response(
      JSON.stringify({ error: error.message }),
      { status: 500, headers: { ...corsHeaders, "Content-Type": "application/json" } }
    );
  }
});

Customizing the Invitation Email Template

Self-hosted Supabase lets you customize email templates. For invitations, update your GoTrue environment variables or use a custom template server. Add these to your .env:

GOTRUE_MAILER_TEMPLATES_INVITE=http://templates-server/invite.html
GOTRUE_MAILER_SUBJECTS_INVITE="You've been invited to join {{.Data.team_name}}"

Your invitation template should include the invitation link and any relevant context about the team they're joining.

Frontend Integration

Here's how to integrate the invitation system into a React application:

// Sending an invitation
async function inviteUser(email: string, teamId: string, role: string) {
  const { data, error } = await supabase.functions.invoke('invite-to-team', {
    body: { email, teamId, role }
  });
  
  if (error) throw error;
  return data;
}

// Accepting an invitation
async function acceptInvitation(token: string) {
  const { data, error } = await supabase.functions.invoke('accept-invite', {
    body: { token }
  });
  
  if (error) throw error;
  return data;
}

Handling Edge Cases

A production-ready invitation system needs to handle several edge cases:

Resending invitations: Add an endpoint to regenerate the token and resend the email for expired or lost invitations.

Revoking invitations: Allow admins to delete pending invitations before they're accepted.

Existing users: Check if the invited email already has an account and customize the flow accordingly—they might just need to log in, not sign up.

Rate limiting: Implement rate limits on invitation sending to prevent abuse. The API rate limiting guide covers this in detail.

Cleanup and Maintenance

Set up a scheduled job to clean up expired invitations using pg_cron:

SELECT cron.schedule(
  'cleanup-expired-invitations',
  '0 2 * * *',  -- Run daily at 2 AM
  $$DELETE FROM public.team_invitations 
    WHERE expires_at < now() 
    AND accepted_at IS NULL$$
);

Testing Your Invitation Flow

Before deploying to production, test these scenarios:

1. New user flow: Invite an email that doesn't have an account, verify they can sign up and join the team
2. Existing user flow: Invite a user who already has an account, verify they can log in and accept
3. Expired invitation: Wait for an invitation to expire and verify it can't be accepted
4. Duplicate invitation: Try inviting the same email twice and verify proper error handling
5. Permission checks: Verify that members can't invite others, only owners and admins

Self-Hosted Considerations

When running this on self-hosted Supabase, keep these points in mind:

SMTP reliability: Your invitation emails depend on your SMTP configuration. Test email delivery thoroughly before launching.

Token security: The invitation tokens in this implementation are long random strings. For additional security, consider encrypting them using Supabase Vault.

Backup strategy: Invitation records should be included in your regular database backups.

Scaling Considerations

For applications with high invitation volume, consider:

• Adding database indexes on frequently queried columns
• Implementing background job processing for email sending
• Using a dedicated email queue to handle failures and retries

Conclusion

Building a custom invitation system on self-hosted Supabase gives you complete control over the user onboarding experience. While it requires more upfront work than using a built-in feature, the flexibility to customize every aspect—from email templates to role assignment logic—makes it worthwhile for production applications.

The combination of database triggers, Edge Functions, and Row Level Security provides all the primitives you need to build secure, scalable team collaboration features.

If managing Edge Functions, SMTP configuration, and database maintenance feels like too much operational overhead, Supascale provides a managed platform for self-hosted Supabase that handles the infrastructure while you focus on building your application. Check out our pricing or get started with a one-time purchase that includes unlimited projects.

Further Reading

• Edge Functions for Self-Hosted Supabase
• Row Level Security Guide
• Auth Hooks and Custom Claims
• Configuring SMTP and Email Templates