Skip to main content

​
1. Core Principles

​
The Problem

Supabase Auth does not natively support domestic OAuth platforms like Feishu or WeChat. We need to use these third-party platforms for identity verification while reusing Supabase’s session management capabilities (JWT, refresh token, RLS).

​
The Solution

Treat third-party OAuth as an “access control system” — responsible only for verifying identity, not for managing session tokens. Once identity is verified, Supabase Auth issues the real session token. Data flow: 
User scans QR code / authorizes
    │
    ▼
Third-party platform returns authorization code (code)
    │
    ▼
Edge Function exchanges code for user info
    │
    ▼
Query profiles table: Is this OAuth user already registered?
    │
    ├── Exists → Update user info
    │
    └── Not exists → auth.admin.createUser() creates Supabase user
                      ↓
                      Trigger automatically inserts record into profiles table
    │
    ▼
auth.admin.generateLink({ type: 'magiclink', email })
    │
    ▼
Return hashed_token to frontend
    │
    ▼
Frontend calls supabase.auth.verifyOtp({ token_hash, type: 'magiclink' })
    │
    ▼
Supabase session established (access_token + refresh_token auto-persisted)

​
2. user_identities Table Design

​
Table Structure

CREATE TABLE public.user_identities (
  id              UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  oauth_provider  TEXT,            -- 'feishu', 'wechat', 'google', etc.; NULL for non-OAuth users
  oauth_open_id   TEXT,            -- Unique user identifier on that platform; NULL for non-OAuth users
  raw_metadata    JSONB DEFAULT '{}'::jsonb,  -- Preserve raw fields from each platform
  created_at      TIMESTAMPTZ DEFAULT now(),
  updated_at      TIMESTAMPTZ DEFAULT now()
);

​
Key Design Decisions

1. oauth_provider and oauth_open_id are nullable fields This is the most important design decision. The reason:
The trigger executes for all auth.users inserts. If a user registers via email/password, phone number, or other non-OAuth methods, user_metadata will not contain oauth_provider or oauth_open_id. If these two fields are NOT NULL, the trigger would fail with a NOT NULL constraint violation, causing user registration to fail. Therefore, these fields must be nullable, allowing the trigger to create identity records for all user types — OAuth users get specific values, non-OAuth users get NULL.
2. Use a partial unique index instead of a composite unique constraint 
CREATE UNIQUE INDEX unique_oauth_identity
  ON public.user_identities(oauth_provider, oauth_open_id)
  WHERE oauth_provider IS NOT NULL AND oauth_open_id IS NOT NULL;
Why not use UNIQUE(oauth_provider, oauth_open_id)?
  • In PostgreSQL’s UNIQUE constraint, NULL does not equal NULL, so multiple rows with (NULL, NULL) won’t conflict
  • However, the intent is not semantically clear; a partial unique index more explicitly expresses the intent: enforce uniqueness only when OAuth fields are non-null
  • This ensures the same user on the same platform is never created twice, while allowing any number of non-OAuth users
3. id directly references auth.users(id) with ON DELETE CASCADE
  • user_identities.id and auth.users.id are the same UUID
  • When a user is deleted from the Supabase dashboard, the profile record is automatically cleaned up
  • RLS policies can directly use auth.uid() = id for access control
4. raw_metadata stores platform-specific fields Different OAuth platforms return different user info fields (Feishu has tenant_key, WeChat has unionid). Rather than adding dedicated columns per platform, storing them all in JSONB means the table structure never needs to change for new platforms.

​
RLS Policies

ALTER TABLE public.user_identities ENABLE ROW LEVEL SECURITY;

-- Users can only read their own identity
CREATE POLICY "Users can view own identity"
  ON public.user_identities FOR SELECT
  USING (auth.uid() = id);

-- Users can only update their own identity
CREATE POLICY "Users can update own identity"
  ON public.user_identities FOR UPDATE
  USING (auth.uid() = id);

​
3. Trigger

​
Trigger Function

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  provider TEXT := NEW.raw_user_meta_data->>'oauth_provider';
  open_id  TEXT := NEW.raw_user_meta_data->>'oauth_open_id';
BEGIN
  INSERT INTO public.user_identities (id, oauth_provider, oauth_open_id, raw_metadata)
  VALUES (
    NEW.id,
    provider,    -- OAuth users have a value; non-OAuth users get NULL
    open_id,     -- OAuth users have a value; non-OAuth users get NULL
    COALESCE(NEW.raw_user_meta_data, '{}'::jsonb)
  );
  RETURN NEW;
END;
$$;

​
Bind the Trigger

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION public.handle_new_user();

​
Trigger Behavior Summary

Registration Methodoauth_provideroauth_open_idraw_metadata
Feishu OAuth'feishu''ou_xxxxx'Contains name, avatar_url, tenant_key, etc.
WeChat OAuth'wechat''oXXXX'Contains nickname, headimgurl, unionid, etc.
Email/passwordNULLNULL{} or metadata passed during registration
Phone numberNULLNULL{}
SECURITY DEFINER ensures the trigger executes with the creator’s permissions, bypassing RLS to write to the profiles table. NEW.raw_user_meta_data is the user_metadata object passed to Supabase Auth during createUser.

​
4. Edge Function Key Code (Feishu Example)

​
Find or Create User

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

// Create admin client with Service Role Key to bypass RLS
const supabaseAdmin = createClient(
  Deno.env.get('SUPABASE_URL')!,
  Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!,
  { auth: { autoRefreshToken: false, persistSession: false } }
);

// ... After Feishu OAuth verification + whitelist validation ...

const oauthProvider = 'feishu';
const oauthOpenId = feishuUser.open_id;
// Always use a virtual email, not the real email returned by the OAuth platform
// Reason: the user may have already registered with the same email as a regular account,
// using the real email would cause a createUser conflict
const userEmail = `feishu_${oauthOpenId}@oauth.local`;

const userMetadata = {
  oauth_provider: oauthProvider,
  oauth_open_id: oauthOpenId,
  name: feishuUser.name,
  avatar_url: feishuUser.avatar_url,
  tenant_key: feishuUser.tenant_key,
  ...feishuUser,  // Preserve all original fields
};

// Find user via user_identities table (Service Role ignores RLS)
const { data: existingIdentity } = await supabaseAdmin
  .from('user_identities')
  .select('id')
  .eq('oauth_provider', oauthProvider)
  .eq('oauth_open_id', oauthOpenId)
  .single();

if (existingIdentity) {
  // Existing user: update auth user metadata + sync user_identities table
  await supabaseAdmin.auth.admin.updateUserById(existingIdentity.id, {
    user_metadata: userMetadata,
  });
  await supabaseAdmin.from('user_identities').update({
    raw_metadata: userMetadata,
    updated_at: new Date().toISOString(),
  }).eq('id', existingIdentity.id);
} else {
  // New user: createUser triggers automatic profile record creation
  const { error } = await supabaseAdmin.auth.admin.createUser({
    email: userEmail,         // Use virtual email, not the real email from OAuth platform
    email_confirm: true,      // Skip email verification
    user_metadata: userMetadata,
  });
  if (error) throw error;
}

​
Issue Session Token

// generateLink generates a one-time token
const { data: linkData, error: linkError } =
  await supabaseAdmin.auth.admin.generateLink({
    type: 'magiclink',
    email: userEmail,
  });

if (linkError || !linkData) throw linkError;

const hashedToken = linkData.properties?.hashed_token;

// Return to frontend
return new Response(JSON.stringify({
  token_hash: hashedToken,
  user: feishuUser,
}));

​
Frontend Session Establishment

// After callback page receives token_hash
const { data, error } = await supabase.auth.verifyOtp({
  token_hash: tokenHash,
  type: 'magiclink',
});

// data.session contains access_token + refresh_token
// Supabase client auto-persists; subsequent requests automatically include JWT

​
5. Frontend Auth State

No custom Context or localStorage needed — use Supabase Auth’s built-in session management directly: 
import { supabase } from '@/integrations/supabase/client';

export function useAuth() {
  const [session, setSession] = useState(null);
  const [user, setUser] = useState(null);
  const [isLoading, setIsLoading] = useState(true);

  useEffect(() => {
    // Read current session
    supabase.auth.getSession().then(({ data: { session } }) => {
      setSession(session);
      setUser(session?.user ? extractUser(session.user) : null);
      setIsLoading(false);
    });

    // Listen for session changes (login, logout, token refresh)
    const { data: { subscription } } =
      supabase.auth.onAuthStateChange((_event, session) => {
        setSession(session);
        setUser(session?.user ? extractUser(session.user) : null);
        setIsLoading(false);
      });

    return () => subscription.unsubscribe();
  }, []);

  const logout = useCallback(async () => {
    await supabase.auth.signOut();
  }, []);

  return { session, user, isLoading, logout };
}

// Extract business fields from Supabase user.user_metadata
function extractUser(user) {
  const metadata = user.user_metadata || {};
  return {
    id: user.id,
    email: user.email,
    name: metadata.name || '',
    avatarUrl: metadata.avatar_url || '',
    oauthProvider: metadata.oauth_provider || '',
    oauthOpenId: metadata.oauth_open_id || '',
    tenantKey: metadata.tenant_key || '',
  };
}
User information is stored in session.user.user_metadata — the same userMetadata object passed during createUser.

​
6. Extending to New OAuth Platforms

To add WeChat as an example, you only need to:
  1. Create a new Edge Function wechat-auth: implement WeChat OAuth authorization code exchange and user info retrieval
  2. Same Supabase logic: query user_identities → create/update user → generateLink → return token_hash
  3. Change the provider on lookup: .eq('oauth_provider', 'wechat').eq('oauth_open_id', wechatOpenId)
What you don’t need to change: user_identities table structure, trigger, frontend useAuth, verifyOtp logic, RLS policies — all reused.

​
7. Common Pitfalls

IssueCauseSolution
Non-OAuth user registration fails with trigger erroroauth_provider and oauth_open_id set as NOT NULL, but non-OAuth users don’t have these fields in metadataMake them nullable; trigger handles all cases: fills in OAuth info when present, leaves NULL otherwise
Partial unique index vs. composite unique constraintUNIQUE(a, b) in PostgreSQL allows multiple (NULL, NULL) rows (because NULL != NULL), which is semantically unclearUse CREATE UNIQUE INDEX ... WHERE ... IS NOT NULL partial index to explicitly constrain only non-null values
generateLink returns a one-time hashed_tokenAfter verifyOtp succeeds, the token is invalidatedFrontend must call verifyOtp immediately on the callback page; cannot delay or retry
createUser in Edge Function requires emailSupabase Auth requires each user to have an emailUse virtual email {provider}_{openid}@oauth.local; do not use the real email from the OAuth platform to avoid conflicts with regular accounts