Schema Design Principles for Supabase
Designing for Supabase
Schema design for Supabase follows PostgreSQL best practices, with additional considerations for RLS, the API layer, and client-side access patterns.
Core Principles
1. Design for RLS First
Every table needs a security model. Consider access control during design, not after.
-- Design questions for each table:
-- Who can SELECT?
-- Who can INSERT?
-- Who can UPDATE?
-- Who can DELETE?
CREATE TABLE posts (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid REFERENCES auth.users(id) NOT NULL, -- ← Ownership column
title text NOT NULL,
content text,
published boolean DEFAULT false -- ← Visibility column
);
-- Security is built into the schema
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users manage own posts"
ON posts FOR ALL
USING (user_id = auth.uid());
CREATE POLICY "Anyone can view published posts"
ON posts FOR SELECT
USING (published = true);
2. Use UUIDs as Primary Keys
-- Standard Supabase pattern
id uuid PRIMARY KEY DEFAULT gen_random_uuid()
-- Benefits:
-- - Matches auth.users.id type
-- - Safe to expose in URLs
-- - Globally unique
-- - Works across distributed systems
3. Embrace Foreign Keys
-- Always use explicit foreign keys
CREATE TABLE comments (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
post_id uuid REFERENCES posts(id) ON DELETE CASCADE NOT NULL,
user_id uuid REFERENCES auth.users(id) ON DELETE SET NULL,
content text NOT NULL
);
-- Foreign keys provide:
-- - Data integrity
-- - Automatic cleanup (CASCADE)
-- - Clear relationship documentation
-- - Better PostgREST query generation
4. Include Timestamps
CREATE TABLE posts (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
-- ... other columns ...
created_at timestamptz DEFAULT now() NOT NULL,
updated_at timestamptz DEFAULT now() NOT NULL
);
-- Auto-update updated_at
CREATE TRIGGER posts_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
Schema Organization
Use Schemas Appropriately
-- public: Your application tables
CREATE TABLE public.profiles (...);
CREATE TABLE public.posts (...);
-- Don't modify:
-- auth: Supabase authentication
-- storage: File metadata
-- extensions: PostgreSQL extensions
Naming Conventions
-- Tables: plural, snake_case
users, posts, team_members, order_items
-- Columns: singular, snake_case
user_id, created_at, is_active, full_name
-- Foreign keys: singular_id
user_id (references users.id)
post_id (references posts.id)
-- Junction tables: both entities
team_members (teams ↔ users)
post_tags (posts ↔ tags)
API-Friendly Design
Design for PostgREST Queries
-- Tables become API endpoints
-- /rest/v1/posts → posts table
-- /rest/v1/comments → comments table
-- Relationships work automatically
CREATE TABLE posts (
id uuid PRIMARY KEY,
user_id uuid REFERENCES auth.users(id)
);
-- This query works automatically:
-- supabase.from('posts').select('*, user:auth.users(email)')
Avoid Composite Primary Keys When Possible
-- Harder for API
CREATE TABLE post_tags (
post_id uuid,
tag_id uuid,
PRIMARY KEY (post_id, tag_id) -- Composite
);
-- Easier for API
CREATE TABLE post_tags (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
post_id uuid REFERENCES posts(id),
tag_id uuid REFERENCES tags(id),
UNIQUE (post_id, tag_id)
);
Normalization Guidelines
When to Normalize
Separate data that:
- Has independent lifecycle
- Is referenced from multiple places
- Needs independent access control
- Changes at different rates
-- Normalized: User and profile separate
CREATE TABLE profiles (
id uuid PRIMARY KEY REFERENCES auth.users(id),
display_name text,
bio text
);
-- Profile can have different RLS than user data
-- Profile can be public while auth data is private
When to Denormalize
Combine data that:
- Is always accessed together
- Would require many joins
- Needs atomic updates
-- Denormalized: Address in order
CREATE TABLE orders (
id uuid PRIMARY KEY,
-- Snapshot of address at time of order
shipping_address jsonb NOT NULL
-- Even if user updates address, order keeps original
);
JSONB: When and How
Good Uses for JSONB
-- User preferences (rarely queried, flexible structure)
preferences jsonb DEFAULT '{}'
-- External API response caching
api_response jsonb
-- Flexible metadata
metadata jsonb DEFAULT '{}'
-- Denormalized read-only data
author_snapshot jsonb -- {name, avatar_url at time of post}
Avoid JSONB When
-- Bad: Core business data
CREATE TABLE orders (
id uuid PRIMARY KEY,
data jsonb -- Don't put order items, status, etc. here
);
-- Good: Proper columns with types and constraints
CREATE TABLE orders (
id uuid PRIMARY KEY,
status text CHECK (status IN ('pending', 'paid', 'shipped')),
total_cents integer NOT NULL CHECK (total_cents >= 0)
);
Index Strategy
Index Foreign Keys
-- PostgreSQL doesn't auto-index foreign keys!
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);
Index RLS Columns
-- If RLS checks this column, index it
CREATE POLICY "..." USING (organization_id = get_org_id());
CREATE INDEX idx_posts_org_id ON posts(organization_id);
Index Query Patterns
-- Common query: Recent posts by status
SELECT * FROM posts
WHERE status = 'published'
ORDER BY created_at DESC;
-- Matching index
CREATE INDEX idx_posts_published_recent
ON posts(created_at DESC)
WHERE status = 'published';
Constraints as Documentation
CREATE TABLE orders (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
-- Status must be valid
status text NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'canceled')),
-- Price must be positive
total_cents integer NOT NULL
CHECK (total_cents >= 0),
-- Quantity must be at least 1
quantity integer NOT NULL DEFAULT 1
CHECK (quantity >= 1),
-- Shipping date after order date
ordered_at timestamptz NOT NULL DEFAULT now(),
shipped_at timestamptz
CHECK (shipped_at IS NULL OR shipped_at >= ordered_at)
);
Key Takeaways
- RLS-first design: Build security into the schema
- UUIDs everywhere: Match Supabase patterns
- Foreign keys always: Integrity and documentation
- Timestamps on everything: Track changes
- Index for RLS and queries: Performance matters
- Constraints document rules: Database enforces business logic
Looking Ahead
With principles understood, we'll explore specific patterns like user profiles and multi-tenancy.
A well-designed schema is self-documenting. When constraints, relationships, and naming are clear, the schema tells the story of your application.
التالي · الدرس 30 من 40
The User Profiles Pattern
قراءة 4 دقيقة
أنهيت 29 من 40 درسًا

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

