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

1. RLS-first design: Build security into the schema
2. UUIDs everywhere: Match Supabase patterns
3. Foreign keys always: Integrity and documentation
4. Timestamps on everything: Track changes
5. Index for RLS and queries: Performance matters
6. 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.