Build production-ready full-stack applications with Supabase.
Supabase is an open-source Firebase alternative providing PostgreSQL database, authentication, storage, real-time subscriptions, and edge functions. This skill guides you through building secure, scalable applications using Supabase's full feature set.
When to Use This Skill
Authentication: Implementing user signup/login with email, OAuth, magic links, or phone auth
Database: Designing PostgreSQL schemas with Row Level Security (RLS)
Storage: Managing file uploads, downloads, and access control
Real-time: Building live features with subscriptions and broadcasts
Edge Functions: Serverless TypeScript functions at the edge
Migrations: Managing database schema changes
Integration: Connecting Next.js, React, Vue, or other frameworks
Core Supabase Concepts
1. Database (PostgreSQL)
Supabase uses PostgreSQL with extensions:
PostgREST: Auto-generates REST API from schema
pg_graphql: Optional GraphQL support
Extensions: pgvector for embeddings, pg_cron for scheduled jobs
2. Authentication
Built-in auth with multiple providers:
Email/password with confirmation
Magic links (passwordless)
OAuth (Google, GitHub, etc.)
Phone/SMS authentication
SAML SSO (enterprise)
3. Row Level Security (RLS)
PostgreSQL policies that enforce data access at the database level:
User can only read their own data
Admin can read all data
Public read, authenticated write
4. Storage
S3-compatible object storage with RLS:
Public and private buckets
File size and type restrictions
Image transformations on the fly
CDN integration
5. Real-time
WebSocket-based subscriptions:
Database changes (INSERT, UPDATE, DELETE)
Broadcast messages to channels
Presence tracking (who's online)
6. Edge Functions
Deno-based serverless functions:
Deploy globally at the edge
TypeScript/JavaScript runtime
Background jobs and webhooks
Custom API endpoints
6-Phase Supabase Implementation
Phase 1: Project Setup & Configuration
Goal: Initialize Supabase project and connect to your application
1.1 Create Supabase Project
# Option A: Web Dashboard# 1. Go to https://supabase.com# 2. Create new project# 3. Save database password securely# Option B: CLI (recommended for production)
npx supabase init
npx supabase start
// Get current sessionasyncfunctiongetSession() {
const {
data: { session },
error
} = await supabase.auth.getSession()
return session
}
// Get current userasyncfunctiongetUser() {
const {
data: { user },
error
} = await supabase.auth.getUser()
return user
}
// Listen to auth changes
supabase.auth.onAuthStateChange((event, session) => {
console.log(event, session)
if (event === 'SIGNED_IN') {
// User signed in
}
if (event === 'SIGNED_OUT') {
// User signed out
}
if (event === 'TOKEN_REFRESHED') {
// Token refreshed
}
})
Goal: Design secure database schema with Row Level Security
3.1 Schema Design
-- Example: Blog application schema-- Enable UUID extensionCREATE EXTENSION IF NOTEXISTS "uuid-ossp";
-- Profiles table (extends auth.users)CREATE TABLE profiles (
id UUID REFERENCES auth.users(id) PRIMARY KEY,
username TEXT UNIQUENOT NULL,
full_name TEXT,
avatar_url TEXT,
bio TEXT,
created_at TIMESTAMPWITHTIME ZONE DEFAULT NOW(),
updated_at TIMESTAMPWITHTIME ZONE DEFAULT NOW()
);
-- Posts tableCREATE TABLE posts (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
user_id UUID REFERENCES profiles(id) ONDELETE CASCADE NOT NULL,
title TEXT NOT NULL,
content TEXT NOT NULL,
published BOOLEANDEFAULTFALSE,
created_at TIMESTAMPWITHTIME ZONE DEFAULT NOW(),
updated_at TIMESTAMPWITHTIME ZONE DEFAULT NOW()
);
-- Comments tableCREATE TABLE comments (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
post_id UUID REFERENCES posts(id) ONDELETE CASCADE NOT NULL,
user_id UUID REFERENCES profiles(id) ONDELETE CASCADE NOT NULL,
content TEXT NOT NULL,
created_at TIMESTAMPWITHTIME ZONE DEFAULT NOW()
);
-- Indexes for performanceCREATE INDEX posts_user_id_idx ON posts(user_id);
CREATE INDEX posts_created_at_idx ON posts(created_at DESC);
CREATE INDEX comments_post_id_idx ON comments(post_id);
3.2 Row Level Security (RLS) Policies
-- Enable RLS on all tablesALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE comments ENABLE ROW LEVEL SECURITY;
-- Profiles: Users can read all, update only their ownCREATE POLICY "Public profiles are viewable by everyone"
ON profiles FORSELECTUSING (true);
CREATE POLICY "Users can insert their own profile"
ON profiles FORINSERTWITHCHECK (auth.uid() = id);
CREATE POLICY "Users can update their own profile"
ON profiles FORUPDATEUSING (auth.uid() = id);
-- Posts: Public can read published, users can manage their ownCREATE POLICY "Published posts are viewable by everyone"
ON posts FORSELECTUSING (published =trueOR auth.uid() = user_id);
CREATE POLICY "Users can create their own posts"
ON posts FORINSERTWITHCHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own posts"
ON posts FORUPDATEUSING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own posts"
ON posts FORDELETEUSING (auth.uid() = user_id);
-- Comments: Public can read, users can manage their ownCREATE POLICY "Comments are viewable by everyone"
ON comments FORSELECTUSING (true);
CREATE POLICY "Authenticated users can create comments"
ON comments FORINSERTWITHCHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own comments"
ON comments FORUPDATEUSING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own comments"
ON comments FORDELETEUSING (auth.uid() = user_id);
3.3 Database Functions
-- Automatic updated_at timestampCREATEOR REPLACE FUNCTION handle_updated_at()
RETURNSTRIGGERAS $$
BEGIN
NEW.updated_at = NOW();
RETURNNEW;
END;
$$ LANGUAGE plpgsql;
-- Apply to tablesCREATETRIGGER handle_profiles_updated_at
BEFORE UPDATEON profiles
FOREACHROWEXECUTEFUNCTION handle_updated_at();
CREATETRIGGER handle_posts_updated_at
BEFORE UPDATEON posts
FOREACHROWEXECUTEFUNCTION handle_updated_at();
-- Automatic profile creation on signupCREATEOR REPLACE FUNCTION handle_new_user()
RETURNSTRIGGERAS $$
BEGININSERT INTO public.profiles (id, username, full_name, avatar_url)
VALUES (
NEW.id,
NEW.raw_user_meta_data->>'username',
NEW.raw_user_meta_data->>'full_name',
NEW.raw_user_meta_data->>'avatar_url'
);
RETURNNEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATETRIGGER on_auth_user_created
AFTER INSERTON auth.users
FOREACHROWEXECUTEFUNCTION handle_new_user();
-- Avatars: Anyone can read, users can upload their ownCREATE POLICY "Avatar images are publicly accessible"
ON storage.objects FORSELECTUSING (bucket_id ='avatars');
CREATE POLICY "Users can upload their own avatar"
ON storage.objects FORINSERTWITHCHECK (
bucket_id ='avatars'AND
auth.uid()::text = (storage.foldername(name))[1]
);
CREATE POLICY "Users can update their own avatar"
ON storage.objects FORUPDATEUSING (
bucket_id ='avatars'AND
auth.uid()::text = (storage.foldername(name))[1]
);
-- Private docs: Only owner can accessCREATE POLICY "Users can access their own documents"
ON storage.objects FORSELECTUSING (
bucket_id ='private-docs'AND
auth.uid()::text = (storage.foldername(name))[1]
);
CREATE POLICY "Users can upload their own documents"
ON storage.objects FORINSERTWITHCHECK (
bucket_id ='private-docs'AND
auth.uid()::text = (storage.foldername(name))[1]
);
# Initialize Supabase locally
supabase init
supabase start
# Create new migration
supabase migration new add_posts_table
# Edit migration file in supabase/migrations/# Apply migrations
supabase db reset
# Generate TypeScript types
supabase gen types typescript --local > types/supabase.ts
Production Deployment
# Link to remote project
supabase link --project-ref your-project-ref
# Push migrations to production
supabase db push
# Or apply specific migration
supabase db remote commit
Security Best Practices
1. Never Expose Service Role Key
// ❌ WRONG - Never on client sideconst supabase = createClient(url, SERVICE_ROLE_KEY)
// ✅ CORRECT - Use anon key on clientconst supabase = createClient(url, ANON_KEY)
// ✅ Service role only on server// app/api/admin/route.tsconst supabase = createClient(url, SERVICE_ROLE_KEY)
2. Always Use RLS
-- ❌ WRONG - Table without RLSCREATE TABLE sensitive_data (
id UUID PRIMARY KEY,
secret TEXT
);
-- ✅ CORRECT - RLS enabledCREATE TABLE sensitive_data (
id UUID PRIMARY KEY,
user_id UUID REFERENCES auth.users(id),
secret TEXT
);
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can only access their data"
ON sensitive_data FORALLUSING (auth.uid() = user_id);
-- Add indexes on frequently queried columnsCREATE INDEX posts_user_id_idx ON posts(user_id);
CREATE INDEX posts_created_at_idx ON posts(created_at DESC);
-- Composite indexes for multi-column queriesCREATE INDEX posts_user_published_idx ON posts(user_id, published);
-- Full-text search indexesCREATE INDEX posts_content_fts_idx ON posts USING gin(to_tsvector('english', content));
2. Select Only What You Need
// ❌ WRONG - Select everythingconst { data } = await supabase.from('posts').select('*')
// ✅ CORRECT - Select specific columnsconst { data } = await supabase.from('posts').select('id, title, created_at')
3. Use Pagination
// Offset paginationconst { data } = await supabase.from('posts').select('*').range(0, 9)
// Cursor pagination (better for large datasets)const { data } = await supabase
.from('posts')
.select('*')
.gt('created_at', lastCreatedAt)
.order('created_at', { ascending: false })
.limit(10)
4. Cache Static Data
// Use React Query or SWRimport { useQuery } from'@tanstack/react-query'functionusePosts() {
returnuseQuery({
queryKey: ['posts'],
queryFn: async () => {
const { data } = await supabase.from('posts').select('*')
return data
},
staleTime: 5 * 60 * 1000// 5 minutes
})
}
-- Add deleted_at columnALTER TABLE posts ADDCOLUMN deleted_at TIMESTAMPWITHTIME ZONE;
-- Update RLS to exclude deletedCREATE POLICY "Only show non-deleted posts"
ON posts FORSELECTUSING (deleted_at ISNULL);
-- Soft delete functionCREATEOR REPLACE FUNCTION soft_delete_post(post_id UUID)
RETURNS void AS $$
BEGINUPDATE posts
SET deleted_at = NOW()
WHERE id = post_id AND user_id = auth.uid();
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
3. Audit Logs
-- Create audit log tableCREATE TABLE audit_logs (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
table_name TEXT NOT NULL,
record_id UUID NOT NULL,
action TEXT NOT NULL,
old_data JSONB,
new_data JSONB,
user_id UUID REFERENCES auth.users(id),
created_at TIMESTAMPWITHTIME ZONE DEFAULT NOW()
);
-- Audit trigger functionCREATEOR REPLACE FUNCTION audit_trigger()
RETURNSTRIGGERAS $$
BEGININSERT INTO audit_logs (table_name, record_id, action, old_data, new_data, user_id)
VALUES (
TG_TABLE_NAME,
COALESCE(NEW.id, OLD.id),
TG_OP,
CASEWHEN TG_OP ='DELETE'THEN row_to_json(OLD) ELSENULLEND,
CASEWHEN TG_OP IN ('INSERT', 'UPDATE') THEN row_to_json(NEW) ELSENULLEND,
auth.uid()
);
RETURNNEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Apply to tablesCREATETRIGGER audit_posts
AFTER INSERTORUPDATEORDELETEON posts
FOREACHROWEXECUTEFUNCTION audit_trigger();
Troubleshooting
Issue: RLS Policies Not Working
Symptom: Can't query data even with correct policies
Solution:
-- Check if RLS is enabledSELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname ='public';
-- Check policiesSELECT*FROM pg_policies WHERE tablename ='your_table';
-- Test policy as userSETLOCAL ROLE authenticated;
SETLOCAL request.jwt.claims.sub TO'user-uuid';
SELECT*FROM your_table;
-- Enable replication for tableALTER PUBLICATION supabase_realtime ADDTABLE posts;
-- Check if table is in publicationSELECT*FROM pg_publication_tables WHERE pubname ='supabase_realtime';
Quick Reference
Essential Commands
# Local development
supabase init
supabase start
supabase stop
supabase status
# Migrations
supabase migration new migration_name
supabase db reset
supabase db push
# Type generation
supabase gen types typescript --local > types/supabase.ts
# Edge Functions
supabase functions new function_name
supabase functions serve
supabase functions deploy function_name
# Link to remote
supabase link --project-ref your-ref
This skill covers the complete Supabase development lifecycle:
✅ Setup: Project initialization and client configuration
✅ Auth: Multiple authentication strategies with session management
✅ Database: PostgreSQL schema design with Row Level Security
✅ Storage: File management with access control
✅ Real-time: Live subscriptions, broadcasts, and presence
✅ Edge Functions: Serverless TypeScript functions
✅ Security: Best practices for production applications
✅ Performance: Optimization strategies for scale
✅ Testing: Unit and integration testing patterns
✅ Migration: Database change management
Key Takeaway: Supabase provides a complete backend platform with PostgreSQL at its core. Row Level Security is your primary security layer—design RLS policies carefully to ensure data is secure by default.
For complex scenarios, combine this skill with:
api-designer for custom API endpoints
security-engineer for advanced security reviews
performance-optimizer for scaling large applications
