pgTAP tests schema and policies in SQL, rolled back afterwards

The answer. pgTAP is a unit-test framework that lives in Postgres: assertions are SQL functions, a test file is begin; select plan(n); …assertions…; select * from finish(); rollback;, and the rollback discards every side effect, so tests can insert and delete freely. The Supabase CLI runs them (supabase test new, supabase test db) against the local stack, and the basejump-supabase_test_helpers package (installed through dbdev) adds what RLS tests need: create a fake user, authenticate_as it (which sets the role and the JWT claims the way a request would), and assert what that user can see. It is the same trick our vitest suite plays with set local role and request.jwt.claims in a transaction; pgTAP keeps the test next to the migration in SQL, our runner keeps it next to the application code in TypeScript. Either way the rule holds: one positive and one negative case per tenant table, or the PR fails.

The pattern.

-- supabase/tests/database/tickets_rls.test.sql
begin;
select plan(4);
select tests.create_supabase_user('alice'); select tests.create_supabase_user('bob');
insert into public.memberships (profile_id, tenant) values ((select id from public.profiles where user_id = tests.get_supabase_uid('alice')), 'packt');
select tests.authenticate_as('alice');
select results_eq($$ select count(*) from public.tickets where tenant = 'packt' $$, array[2::bigint], 'alice sees packt tickets');
select tests.authenticate_as('bob');
select is_empty($$ select id from public.tickets where tenant = 'packt' $$, 'bob sees nothing');
select throws_ok($$ insert into public.tickets (tenant, title, description) values ('packt', 'x', 'y') $$, '42501', null, 'bob cannot insert');
select tests.rls_enabled('public');
select * from finish();
rollback;

npx supabase test db     # runs every *.sql under supabase/tests

Watch out.

authenticate_as sets claims on the transaction; forgetting tests.clear_authentication() between cases makes a later assertion pass for the wrong reason.
pgTAP runs as the owner between authenticate_as calls; a setup insert that "works" there may be impossible for real users. Test the write path as the user too.
Schema assertions (has_table, has_column, rls_enabled) are cheap regression guards for migrations; add them the day a table is created.