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
- Separate table for profiles: Don't modify auth.users
- One-to-one with auth.users: Primary key is also foreign key
- Trigger for auto-creation: Profile created on signup
- SECURITY DEFINER for trigger: Runs without user context
- Public read, owner write: Common RLS pattern
- 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.
التالي · الدرس 31 من 40
Multi-Tenancy Patterns
قراءة 5 دقيقة
أنهيت 30 من 40 درسًا

اسأل Byte عن هذا الدرس
محادثة خاصة حول هذا الدرس. قرأ Byte الدرس كاملًا، ويجيب خلال ثوانٍ، ويُظهر القسم الذي تأتي منه كل إجابة. يمكنك السؤال بأي لغة.
الأسئلة والأجوبة
أسئلة عامة حول هذا الدرس. يجيب AI Tutor فورًا بلغتك، ويراجع فريق FreeAcademy الإجابات، ويمكن لكل المتعلمين قراءتها.

