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

A red badge in Supabase Dashboard: “RLS not enabled.” Could user posts leak? Are foreign keys right? How do many-to-many joins actually work?

Early on I forgot RLS, mis-modeled keys, and once stored M:N in arrays—a disaster.

Months later the patterns clicked. This article collects that database design experience.

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.

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

Get 15% Discount →Ad

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.

KiloClaw - Managed OpenClaw for enterprise, enabling AI Agent orchestration and smart model routing

Get Started →Ad

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.

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.

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

View Pricing →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