Design the PostgreSQL schema

Before touching the dashboard’s table editor, design the schema like the ordinary PostgreSQL schema it is. Put required rules in constraints, connect ownership with foreign keys, and add indexes for real query paths.

Here is the pair of tables this course keeps returning 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 remembers. 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 authenticated identity, and later lessons build every ownership rule on top of it.

Indexes come from queries, not habit. The app lists a user’s notes, newest first, so:

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

Verify it is actually used:

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 will not 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 Supabase-specific decision: tables in exposed schemas (by default, public) can become available through the Data API. Keep internal-only structures 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 requests.

Generated TypeScript types (supabase gen types typescript) describe the schema; they do not secure it. Types keep your code honest. Only constraints and policies keep your data honest.