The User Profiles Pattern

---

Extending auth.users

Supabase Auth stores user data in auth.users, but you often need additional fields: display names, avatars, preferences, roles. The solution is a linked profiles table.

---

Why Not Modify auth.users?

The auth schema is managed by Supabase:

-- Don't do this:
ALTER TABLE auth.users ADD COLUMN display_name text;

-- Problems:
-- 1. Migrations may conflict with Supabase updates
-- 2. auth.users has specific RLS that you can't easily modify
-- 3. Separation of concerns: auth data vs. application data

---

The Profiles Table

CREATE TABLE public.profiles (
  -- Primary key is also foreign key to auth.users
  id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,

  -- Profile fields
  username text UNIQUE,
  full_name text,
  avatar_url text,
  bio text,
  website text,

  -- Timestamps
  created_at timestamptz DEFAULT now() NOT NULL,
  updated_at timestamptz DEFAULT now() NOT NULL
);

-- Index for username lookups
CREATE UNIQUE INDEX idx_profiles_username ON profiles(username);

-- Enable RLS
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

---

RLS Policies for Profiles

-- Everyone can view profiles
CREATE POLICY "Profiles are publicly viewable"
  ON profiles FOR SELECT
  USING (true);

-- Users can update their own profile
CREATE POLICY "Users can update own profile"
  ON profiles FOR UPDATE
  USING (id = auth.uid())
  WITH CHECK (id = auth.uid());

-- Note: No INSERT policy for regular users
-- Profiles are created via trigger (see below)

---

Automatic Profile Creation

Create a profile when a user signs up:

-- Function to create profile
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.profiles (id, full_name, avatar_url)
  VALUES (
    NEW.id,
    NEW.raw_user_meta_data->>'full_name',
    NEW.raw_user_meta_data->>'avatar_url'
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Trigger on auth.users insert
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION handle_new_user();

Why SECURITY DEFINER?

The trigger runs when GoTrue creates a user, not when a user queries. SECURITY DEFINER allows the function to insert into profiles even though no user is "logged in" during the trigger.

---

Accessing Profile Data

From the Client

// Get current user's profile
const { data: profile } = await supabase
  .from('profiles')
  .select('*')
  .eq('id', user.id)
  .single()

// Get another user's profile
const { data: profile } = await supabase
  .from('profiles')
  .select('*')
  .eq('username', 'alice')
  .single()

// Update your profile
const { error } = await supabase
  .from('profiles')
  .update({ full_name: 'Alice Smith', bio: 'Developer' })
  .eq('id', user.id)

Joining with Other Tables

// Get posts with author profile
const { data: posts } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    content,
    author:profiles(
      username,
      full_name,
      avatar_url
    )
  `)

---

Username Handling

Usernames need special consideration:

-- Constraints
username text UNIQUE
  CHECK (
    -- Length limits
    length(username) >= 3 AND
    length(username) <= 20 AND
    -- Only lowercase letters, numbers, underscores
    username ~ '^[a-z0-9_]+$'
  )

Reserving Usernames

-- Reserved usernames table
CREATE TABLE reserved_usernames (
  username text PRIMARY KEY
);

INSERT INTO reserved_usernames VALUES
  ('admin'), ('api'), ('www'), ('help'), ('support'),
  ('billing'), ('settings'), ('profile'), ('null'), ('undefined');

-- Check in profile update
CREATE OR REPLACE FUNCTION check_username_available()
RETURNS trigger AS $$
BEGIN
  IF EXISTS (SELECT 1 FROM reserved_usernames WHERE username = NEW.username) THEN
    RAISE EXCEPTION 'Username is reserved';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_username
  BEFORE INSERT OR UPDATE ON profiles
  FOR EACH ROW
  WHEN (NEW.username IS NOT NULL)
  EXECUTE FUNCTION check_username_available();

---

Avatar Handling

Option 1: URL from OAuth Provider

-- Stored during signup from provider metadata
avatar_url text  -- 'https://lh3.googleusercontent.com/...'

Option 2: Supabase Storage

-- Reference to storage bucket
avatar_path text  -- 'user-uuid/avatar.jpg'

-- Get URL in application
const avatarUrl = supabase.storage
  .from('avatars')
  .getPublicUrl(profile.avatar_path).data.publicUrl

Storage Policy for Avatars

-- Users can upload their own avatar
CREATE POLICY "Users can upload own avatar"
  ON storage.objects FOR INSERT
  WITH CHECK (
    bucket_id = 'avatars' AND
    auth.uid()::text = (storage.foldername(name))[1]
  );

-- Anyone can view avatars
CREATE POLICY "Avatars are publicly viewable"
  ON storage.objects FOR SELECT
  USING (bucket_id = 'avatars');

---

Extended Profile Patterns

Role-Based Profiles

ALTER TABLE profiles ADD COLUMN role text DEFAULT 'user'
  CHECK (role IN ('user', 'moderator', 'admin'));

-- Helper function
CREATE FUNCTION is_admin() RETURNS boolean AS $$
  SELECT role = 'admin' FROM profiles WHERE id = auth.uid()
$$ LANGUAGE sql SECURITY DEFINER STABLE;

Profile with Preferences

ALTER TABLE profiles ADD COLUMN preferences jsonb DEFAULT '{
  "theme": "system",
  "email_notifications": true,
  "language": "en"
}';

-- Update preferences
await supabase
  .from('profiles')
  .update({
    preferences: { ...currentPrefs, theme: 'dark' }
  })
  .eq('id', user.id)

Profile Completeness

-- Track onboarding progress
ALTER TABLE profiles ADD COLUMN onboarding_completed boolean DEFAULT false;

-- Or more detailed
ALTER TABLE profiles ADD COLUMN onboarding_step integer DEFAULT 0;

---

Common Queries

Get Profile with Stats

CREATE VIEW profile_stats AS
SELECT
  p.*,
  (SELECT count(*) FROM posts WHERE user_id = p.id) as post_count,
  (SELECT count(*) FROM followers WHERE following_id = p.id) as follower_count
FROM profiles p;

Search Profiles

-- By username or name
const { data } = await supabase
  .from('profiles')
  .select('*')
  .or(`username.ilike.%${query}%,full_name.ilike.%${query}%`)
  .limit(10)

---

Key Takeaways

1. Separate table for profiles: Don't modify auth.users
2. One-to-one with auth.users: Primary key is also foreign key
3. Trigger for auto-creation: Profile created on signup
4. SECURITY DEFINER for trigger: Runs without user context
5. Public read, owner write: Common RLS pattern
6. Username validation: Check constraints and reserved words

---

Next Steps

The profiles pattern is the foundation. Next, we'll explore multi-tenancy patterns for SaaS applications.

---

The user profile pattern demonstrates a core Supabase principle: extend the platform with your own tables rather than fighting the managed schemas. This separation keeps your data yours while leveraging Supabase's infrastructure.