Design the PostgreSQL schema

Before you touch the table editor in the dashboard, design the schema like the ordinary PostgreSQL schema it is. Put the rules that must hold in constraints. Connect ownership with foreign keys. Add indexes for the queries you actually run.

Here are the two tables this course keeps coming back to, profiles and notes, with a clear owner relationship:

create table profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  username text unique not null
    check (char_length(username) between 3 and 30)
);

create table notes (
  id bigint generated always as identity primary key,
  user_id uuid not null references profiles (id) on delete cascade,
  title text not null,
  body text not null default '',
  created_at timestamptz not null default now()
);

Every line earns its place. The foreign keys make ownership a fact the database enforces, not a convention the app has to remember. The check constraint rejects garbage usernames at the door. on delete cascade answers “what happens when a user leaves” before it becomes a support ticket.

Notice that profiles.id references auth.users, the table Supabase Auth manages. That link is how a row in your schema gets tied to a real signed-in identity. Every ownership rule we write later in the course builds on it.

Indexes come from queries

Don’t add indexes out of habit. Add them for the queries your app runs. Our app lists a user’s notes, newest first, so:

create index notes_user_id_created_at_idx
  on notes (user_id, created_at desc);

Now check that Postgres uses it:

explain select * from notes
where user_id = 'b7f0c2ae-1d44-4f0a-9c31-58a2d90f5e11'
order by created_at desc limit 20;
-- Index Scan using notes_user_id_created_at_idx on notes ...

If the plan says Seq Scan instead, Postgres is reading the whole table. On a small dev dataset you won’t feel it. At a million rows that same query becomes your slowest endpoint. Read plans early, while they are cheap to fix.

What the Data API exposes

One decision is specific to Supabase. Tables in exposed schemas (by default, public) can become reachable through the Data API. Anything internal belongs in a private schema:

create schema internal;

create table internal.audit_log (
  id bigint generated always as identity primary key,
  event text not null,
  at timestamptz not null default now()
);

Tables in internal are unreachable through the API, no matter what a client asks for. I put audit logs, job queues, and anything else the browser has no business seeing in there.

A last word on types. supabase gen types typescript generates TypeScript types that describe your schema. They keep your code honest: a typo in a column name fails at compile time. But they do not secure anything. Only constraints and policies keep your data honest.