Your Postgres schema lives in supabase/migrations/ as plain SQL, applied in filename order and checked into git. There is no schema DSL and no ORM — the migration is the source of truth, and lib/database.types.ts is generated from it.

The workflow

pnpmnpmyarnbun
pnpm dlx supabase migration new add_projects
Copy

pnpmnpmyarnbun
pnpm db:push        # to your linked project
npm run db:reset       # or: rebuild the local stack from scratch + seed.sql
Copy

pnpmnpmyarnbun
pnpm db:types
Copy

Typed queries

lib/database.types.ts is passed to createClient as a generic, which is what makes everything downstream typed:

Copyexport const supabase = createClient<Database>(url, key, {/* … */});

From there, column names, filters and return types are all checked:

Copyconst { data } = await supabase
  .from('tasks')
  .select('id, text, is_complete')
  .eq('is_complete', false);
 
// data: { id: string; text: string; is_complete: boolean }[] | null

The starters also export the row types by name, which is usually what you want in a component signature:

Copyexport type Tables<T extends keyof PublicSchema['Tables']> =
  PublicSchema['Tables'][T]['Row'];
 
export type Profile = Tables<'profiles'>;
export type Task = Tables<'tasks'>;

Row level security

RLS is the whole security model. The publishable key is compiled into your app bundle, so anyone with the app has it — your policies are the only thing deciding what that key can do.

Two rules cover most of it:

1. Enable RLS on every table in public. A new table has it off. Adding policies to a table without it does nothing.
2. Never trust a column the client supplies. Verify it with with check.

The anatomy of a policy

Copycreate policy "Users can update their own tasks"
  on public.tasks for update
  to authenticated
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

using without with check on an update lets a user take a row they own and reassign it to somebody else. Both clauses, every time.

auth.uid() reads the id out of the caller's JWT. It is null for an anonymous caller, so every policy above fails closed.

Insert policies only have with check

There is no existing row to test, so this is the one that stops a client claiming ownership it does not have:

Copycreate policy "Users can create their own tasks"
  on public.tasks for insert
  to authenticated
  with check (auth.uid() = user_id);

The client still sends user_id — see hooks/useTasks.ts — but sending somebody else's is rejected by the database.

The no-auth starter is different

Its policies are using (true) for the anon role, which is correct for a public demo and wrong for real data:

Copycreate policy "Anyone can read tasks"
  on public.tasks for select
  to anon, authenticated
  using (true);

Anyone who extracts the publishable key from your app can do exactly what these policies allow, from curl, forever. Tighten them or move to the auth starter before you put anything real behind them.

Indexes follow policies

Every policy that filters on a column makes every query filter on that column. Without an index, each one is a sequential scan:

Copycreate index tasks_user_id_created_at_idx
  on public.tasks (user_id, created_at desc);

This is the most common performance problem in an RLS-heavy schema, and it does not show up until the table is large.

Testing a policy

The honest test is two accounts. Sign in as one, create a row, sign in as the other, and confirm it is not there. Locally you can also ask Postgres directly:

Copyset local role authenticated;
set local request.jwt.claims = '{"sub": "<some-user-id>"}';
select * from public.tasks;   -- only that user's rows
reset role;

Triggers

The auth starter creates a profile row the moment a user exists, rather than from the client:

Copycreate trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

Doing it in the database means it cannot be skipped by a user who closes the app mid-sign-up, and the app never has to handle "signed in but no profile yet" as a permanent state.

Realtime needs two lines

Copyalter publication supabase_realtime add table public.tasks;
alter table public.tasks replica identity full;

The publication is what makes changes broadcast at all — without it a subscription connects and then stays silent. replica identity full is what puts the whole row in UPDATE and DELETE payloads instead of just the primary key. See realtime.

Migrations in production

supabase db push applies anything not yet recorded in the remote migration history. It does not roll back, and Postgres DDL that rewrites a large table takes a lock — so the usual advice applies: add columns as nullable, backfill separately, and drop nothing until nothing reads it.

The shipped CI workflow pushes migrations on merge to main. See deployment.

Next

• Realtime · Storage
• Edge functions
• Supabase RLS documentation