Postgres schema and table design

Introduction

Supabase uses PostgreSQL; all API and RLS depend on schema (tables, views, functions). This post covers table design: naming, choosing keys (UUID vs serial), relations, and a simple example schema (e.g. conversations, messages) that works well with PostgREST and RLS.

Goal: Design a clear, consistent schema that is easy to extend and to protect with RLS later.

Features

Schema and tables in Supabase

• Schema: In Postgres, a schema is a namespace (default public). Supabase PostgREST exposes tables/views in public (and other schemas if configured).
• Tables: Define columns, types, constraints, indexes. PostgREST creates REST resources from table names (e.g. conversations → /rest/v1/conversations).
• RLS: Row Level Security (post 7) is defined on tables; having an “owner” column (e.g. user_id, tenant_id) makes policies simple.

Design principles

• Naming: snake_case for tables and columns; plural table names (e.g. conversations, messages) or whatever your team agrees on.
• Primary key: UUID (gen_random_uuid()) for distributed systems and to avoid exposing order; serial/bigserial is simpler for internal-only tables.
• Timestamps: Always have created_at, updated_at (default now(), trigger or app update) for audit and sorting.
• Owner / tenant: For multi-tenant or per-user data, add user_id (references auth.users(id)) or tenant_id; RLS will use them.

Workflow / process

1. Define bounded contexts: Each group of tables serves one area (e.g. conversations, users, billing); avoid tables that do too much.
2. Sketch a simple ER: Entities, 1-N and N-N relations; add junction tables if needed.
3. Name and type: snake_case; choose UUID or serial; add created_at/updated_at, user_id/tenant_id if you’ll use RLS.
4. Write a migration: Create tables in a migration (post 4), not by hand in production.
5. Review indexes: Index columns used in filters/sorts (e.g. user_id, created_at); unique constraints for business keys.

Convention: same schema across dev/staging/prod; only data and keys differ. Consistent naming makes RLS and API easier to read.

Sample code

Migration: conversations and messages tables

-- supabase/migrations/20260317000001_create_conversations.sql
 
CREATE TABLE public.conversations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  title TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE TABLE public.messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id UUID NOT NULL REFERENCES public.conversations(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE INDEX idx_messages_conversation_id ON public.messages(conversation_id);
CREATE INDEX idx_conversations_user_id ON public.conversations(user_id);
 
-- Optional: trigger to set updated_at
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER conversations_updated_at
  BEFORE UPDATE ON public.conversations
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Notes

• user_id references auth.users(id) so RLS can use auth.uid().
• ON DELETE CASCADE so deleting a conversation deletes its messages; use RESTRICT or SET NULL if your domain requires it.

Apply in your architecture

• Bounded context: Each group of tables (e.g. conversations + messages) lives in one “context”; API and RLS only need to know that boundary and avoid joining across contexts.
• Consistent naming: snake_case and plural/singular convention make PostgREST URLs and code predictable; fewer bugs and easier onboarding.
• RLS-ready: A user_id (or tenant_id) column is the basis for “only read/write my rows”; designing with RLS in mind from the start avoids big refactors later.

Summary

Design schema with clear names, suitable keys (UUID/serial), timestamps and owner columns so API and RLS are straightforward. Next: migrations — writing files, order, applying and rollback.

Next: 04 — Migrations: write and apply

Further reading: Supabase Database — Tables