Supabase Database Design: Tables, Relationships & Row Level Security Guide

3 AM. I stared at the red warning on Supabase Dashboard—“RLS not enabled”. Questions raced through my mind: Will user data leak? Is this foreign key relationship correct? How do I build many-to-many relationships?

Honestly, when I started with Supabase, I made so many mistakes. Forgot to enable RLS after creating tables—anyone could read all data. Foreign keys poorly designed—deleted users but posts remained. For many-to-many junction tables, I even tried using arrays—complete disaster.

After months of trial and error, I finally understood Supabase database design patterns. This article summarizes those lessons.

Vultr - High-performance NVMe VPS with 32 global locations, one-click Docker deploy

View Pricing →Ad

1. Table Structure Design: PostgreSQL Naming Conventions

1.1 Naming Convention: snake_case is the Way

PostgreSQL has a quirk: without double quotes, it converts identifiers to lowercase. With double quotes, it respects exactly what you write.

This means using camelCase (UserProfile) requires double quotes everywhere. Too troublesome.

So PostgreSQL community convention: snake_case (underscore-separated), plural table names, singular column names.

-- ✅ Recommended
CREATE TABLE users (
  id UUID PRIMARY KEY,
  email TEXT UNIQUE,
  created_at TIMESTAMPTZ
);

1.2 Column Type Selection: Don’t Be Trapped by MySQL Thinking

Mistake 1: Using VARCHAR instead of TEXT

In PostgreSQL, TEXT and VARCHAR perform identically. Difference is VARCHAR(n) has length limit. Unless you need length restriction, use TEXT.

Mistake 2: Using TIMESTAMP instead of TIMESTAMPTZ

TIMESTAMP doesn’t store timezone. Server in US, user in China—display time gets messy. TIMESTAMPTZ auto-converts timezones.

Mistake 3: Using SERIAL instead of UUID

SERIAL is auto-increment integer. Fine for single-server apps, but conflicts in distributed systems. UUID is globally unique.

2. Three Table Relationships: One-to-One, One-to-Many, Many-to-Many

2.1 One-to-One: Just Add UNIQUE

Most common scenario: users and profiles.

CREATE TABLE profiles (
  id UUID PRIMARY KEY,
  user_id UUID UNIQUE REFERENCES users(id) ON DELETE CASCADE,
  bio TEXT
);

Key point: user_id UUID UNIQUE. UNIQUE constraint ensures each user has only one profile.

2.2 One-to-Many: Ordinary Foreign Key

Authors and books. One author can write many books.

CREATE TABLE books (
  id UUID PRIMARY KEY,
  author_id UUID REFERENCES authors(id) ON DELETE CASCADE,
  title TEXT
);

Supabase JS can fetch nested related data directly.

2.3 Many-to-Many: Junction Table is Key

Students and courses. One student can take many courses, one course can have many students.

Solution: create a junction table.

CREATE TABLE enrollments (
  student_id UUID REFERENCES students(id) ON DELETE CASCADE,
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  PRIMARY KEY (student_id, course_id)
);

3. Row Level Security: Database as Security Guard

3.1 RLS “Default Deny” Philosophy

First time using Supabase, I created a posts table, queried from frontend with anon key. Result—all data returned. Scared me.

Turns out Supabase doesn’t enable Row Level Security (RLS) by default. Without RLS, anyone with anon key can read/write all data.

First rule: Enable RLS immediately after creating tables.

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

But that’s not enough. Enabled without policies equals “deny all access”. Must create at least one policy.

Alibaba Cloud - Top choice for developers, exclusive 15% off for Lighthouse servers

Get 15% Discount →Ad

3.2 Policy Syntax: USING and WITH CHECK

• USING: filter existing rows (SELECT, UPDATE, DELETE)
• WITH CHECK: validate new rows (INSERT, UPDATE)

3.3 Four Common Policy Patterns

Pattern 1: Users Access Own Data

CREATE POLICY "Users manage own data"
ON posts FOR ALL
TO authenticated
USING (user_id = auth.uid());

Pattern 2: Public + Private Data Mixed

Published visible to all, drafts only to author.

Pattern 3: Multi-tenant Isolation

Team members only access their team’s data.

Pattern 4: RBAC Role Control

Admins have special permissions.

Railway - Modern deployment platform, zero-config, go live in minutes

Start Now →Ad

4. RLS Performance Optimization

4.1 Performance Killer: Subquery Executes Per Row

RLS policy subqueries execute on every row. 100K rows with team relationship check—query timeout 3 minutes.

4.2 Optimization 1: Add Indexes

Columns used in RLS policies must have indexes.

CREATE INDEX idx_posts_user_id ON posts(user_id);

Supabase official test: without index 450ms, with index 45ms. 10x improvement.

4.3 Optimization 2: SECURITY DEFINER Functions

Encapsulate subqueries in functions, execute once.

CREATE OR REPLACE FUNCTION user_teams()
RETURNS SETOF UUID
LANGUAGE SQL SECURITY DEFINER STABLE
AS $$ SELECT team_id FROM team_members WHERE user_id = auth.uid(); $$;

5. Real-world Examples

5.1 Blog System: Posts, Categories, Tags

Complete implementation including table structure, RLS policies, and index configuration.

5.2 Multi-tenant SaaS: Team Collaboration

Team data isolation, member access, admin permission control.

Summary

Key points:

• snake_case naming
• UUID primary key, TEXT strings, TIMESTAMPTZ timestamps
• RLS must be enabled
• Indexes + SECURITY DEFINER functions optimization

4 min read · Published on: Apr 4, 2026 · Modified on: Apr 5, 2026

Easton

Technology