Add a table with RLS

When to use it

The product needs its own data owned by a user, for example notes, projects or saved items.

Rules to keep

• Migrations are append-only: create a new file in supabase/migrations/, never edit one that has already been applied.
• RLS is the authorization boundary. Every new table in public enables row level security in the same migration that creates it.
• The owner comes from the session (auth.uid()), never from the request body.
• Server code uses the per-request client in src/lib/supabase/server.ts, which runs as the signed-in user. SUPABASE_SERVICE_ROLE_KEY bypasses RLS: do not use it for user data and never import it in client code.

Steps

1. Create supabase/migrations/<timestamp>_<name>.sql with a timestamp later than the latest migration. For a notes table:

   create table public.notes (
     id uuid primary key default gen_random_uuid(),
     owner_id uuid not null default auth.uid() references auth.users(id) on delete cascade,
     body text not null check (char_length(body) between 1 and 2000),
     created_at timestamptz not null default now()
   );
   
   create index notes_owner_id_idx on public.notes (owner_id);
   
   alter table public.notes enable row level security;
   
   create policy "notes_select_own" on public.notes for select
     to authenticated using ((select auth.uid()) = owner_id);
   create policy "notes_insert_own" on public.notes for insert
     to authenticated with check ((select auth.uid()) = owner_id);
   create policy "notes_update_own" on public.notes for update
     to authenticated using ((select auth.uid()) = owner_id)
     with check ((select auth.uid()) = owner_id);
   create policy "notes_delete_own" on public.notes for delete
     to authenticated using ((select auth.uid()) = owner_id);
   
   revoke all on public.notes from anon;
   

2. Write only the policies the product needs: a table that users read but never change has only a select policy. Write (select auth.uid()) as in profiles, so Postgres evaluates it once per query instead of once per row.

3. If users must not change some columns, follow profiles: revoke update on the table, then grant update (<columns>) only on the editable ones.

4. Read and write from a Server Component or a Route Handler with createClient() from src/lib/supabase/server.ts. As in src/app/api/me/route.ts, call auth.getUser() first and answer 401 without a user. Do not filter on an owner_id taken from the request: RLS already limits the rows.

5. Apply the migration (supabase db push, or the SQL editor of the project) and check isolation with two accounts: user A creates a row, user B must not see, update or delete it, and an anonymous request gets no rows.

6. Put validation of the input in a pure module under src/lib/ and test it in test/ with Vitest; the tests do not use the network.

7. Describe the table and its rules in DATABASE.md.

Done when

• the verification commands pass;
• the new table has RLS enabled and one policy per allowed operation;
• the two-account check in step 5 passed;
• DATABASE.md describes the table.