Tables, Relationships and Migrations

Plain Postgres schema

Tables can be created with the dashboard Table Editor, the SQL Editor or migration files. Use foreign keys for relationships: PostgREST reads them to allow nested selects across tables. User-owned rows usually reference auth.users(id), the table Supabase Auth maintains. Prefer keeping schema changes in migration files in version control, even if you sketch in the dashboard first, so every environment can be rebuilt the same way.

Tables, queries and functions

Model data in SQL, query it from the client and push logic into Postgres functions.

Projects and tasks

A migration with a foreign key to auth.users.

create table public.projects (
  id bigint generated always as identity primary key,
  owner_id uuid not null references auth.users (id) on delete cascade,
  name text not null,
  created_at timestamptz not null default now()
);

create table public.tasks (
  id bigint generated always as identity primary key,
  project_id bigint not null references public.projects (id) on delete cascade,
  title text not null,
  done boolean not null default false
);

alter table public.projects enable row level security;
alter table public.tasks enable row level security;

Blueprints, not sketches on a napkin

Clicking in the dashboard is a sketch; a migration file is the blueprint every builder (environment) follows.

Quick check: Why define foreign keys between tables in Supabase?

• They are required for every column
• They enforce integrity and let the API do nested selects
• They disable RLS automatically
• They make tables public