Add a Supabase table with RLS

When to use it

The app stores new user data in Supabase, for example notes, favourites or log entries, and shows it on a screen.

Rules to keep

• The app ships the publishable key, so RLS is the only thing that keeps one user's rows away from another. Every new table enables RLS in the migration that creates it.
• Migrations are append-only: never edit one that has already been applied.
• Every new policy has a pgTAP test that proves allowed access and rejects the rest.
• Supabase is called only from a repository in data, behind an interface in domain.

Steps

1. Create supabase/migrations/<timestamp>_<name>.sql with a timestamp after the latest one, shaped like push_devices:

   create table public.notes (
     id uuid primary key default gen_random_uuid(),
     user_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_user_id_idx on public.notes (user_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()) = user_id);
   create policy "notes_insert_own" on public.notes for insert
     to authenticated with check ((select auth.uid()) = user_id);
   create policy "notes_delete_own" on public.notes for delete
     to authenticated using ((select auth.uid()) = user_id);
   
   revoke all on public.notes from anon;
   

   Add only the operations the app needs: here users cannot edit a note.

2. If a write needs rules that a policy cannot express, such as moving ownership or checking other tables, revoke direct writes and expose a security definer function with set search_path = '' and a check on auth.uid(), as register_push_device does.

3. Add supabase/tests/database/<name>_rls.test.sql following push_devices_rls.test.sql: two users in auth.users, set local role authenticated, request.jwt.claim.sub set to each user in turn. Check that a user sees and changes only their own rows, that writing a row for someone else fails with 42501, and that anon gets nothing.

4. Run supabase db reset and supabase test db locally.

5. In lib/features/<feature>/domain define the model with fromJson and an abstract interface class for the repository, like ProfileRepository. In data, implement it with SupabaseClient, like SupabaseProfileRepository: without a session, throw instead of querying.

6. In application, expose the repository with a Provider that reads supabaseClientProvider, like profileRepositoryProvider, and add the providers the screen watches. To build the screen, follow the playbook "Add a screen with a route and state".

7. Add Dart tests for the model mapping and for the providers with a fake repository; the tests do not use the network.

8. Add the table, its owner and its rule to the table in DATABASE.md.

Done when

• the verification commands pass;
• supabase test db passes, including the new test;
• the table has RLS enabled and only the policies the app needs;
• DATABASE.md lists the table.