Database

The schema is one file, supabase/schema.sql, applied with npm run db:push. It is written to be run repeatedly: every create is if not exists and every policy is dropped before it is created.

The tables#

Deny by default#

Row Level Security is enabled on every table, and the only policies are the ones in the schema. That inverts the usual failure mode. Forget a policy and the query returns nothing, which you notice in ten seconds. Forget an authorisation check in application code and the query returns everything, which you notice when someone tells you.

alter table public.orders enable row level security;

create policy "orders are self readable"
  on public.orders for select using (auth.uid() = user_id);

Note what is absent: there is no insert policy on orders. A user cannot create their own order at any price from a browser console, because the only thing that writes orders is the webhook, using the service key, which bypasses RLS by design.

Policy recursion#

The moment you add teams, you will write this:

create policy "members read the org" on organisations for select
  using (exists (select 1 from memberships where org_id = id and user_id = auth.uid()));

create policy "members read memberships" on memberships for select
  using (exists (select 1 from organisations where id = org_id));

Postgres will refuse both with an infinite recursion error, because each policy queries a table whose policy queries the first one. The way out is a security definer function, which runs with the owner's rights and therefore does not re-enter the policy:

create or replace function public.is_member(target uuid)
returns boolean language sql security definer stable
set search_path = public as $$
  select exists (
    select 1 from memberships
    where org_id = target and user_id = auth.uid()
  );
$$;

Then both policies call is_member(...). Always pin search_path on a security definer function: without it, someone who can create a schema can shadow a table name and your function runs against theirs.

Proving it#

A policy you have not tested is a policy you believe in. Read with the publishable key, which carries no session, and assert you get nothing:

const res = await fetch(`${SUPABASE_URL}/rest/v1/orders?select=id`, {
  headers: { apikey: PUBLISHABLE_KEY },
});
console.assert((await res.json()).length === 0, "orders leak to anonymous");

That check belongs in your test suite, not in your memory. See Testing.

Changing the schema#

Edit supabase/schema.sql, run npm run db:push again. For a column that needs to land on a database that already has the table, use the additive form so the file stays re-runnable:

alter table public.profiles add column if not exists paypal_email text;

There is no migration folder and no migration history. That is a deliberate trade: one readable file that always describes the current shape, rather than forty numbered files you have to replay in your head. If you outgrow it, the Supabase CLI's migrations work fine alongside.