Add matter_participants table and RLS policies for portal access control
MuseLegal replaces implicit matter ownership with an explicit participants model: a new join table, two SQL helper functions, and Supabase RLS policies covering six portal tables. TypeScript helpers in `access.ts` mirror the database check at the application tier.
The migration (001_matter_participants_access.sql) creates public.matter_participants with a constrained participant_role column (client, attorney, reviewer, paralegal) and a composite unique on (matter_id, user_id, participant_role). Two SQL functions handle the evaluation logic: current_portal_role() reads the JWT claim, and can_access_matter(p_matter_id) gates access by role - partners and admins bypass, everyone else needs a matching row in the join table.
A DO block then iterates over six tables (matters, documents, portal_messages, tasks, intake_submissions, downloadable_files), enables RLS on each, and attaches both select and modify policies. It uses to_regclass() before touching any table, so the migration tolerates partial schemas across environments.
backend/src/lib/access.ts gets two new functions. canAccessMatterByRole queries matter_participants directly. ensureMatterScopedResourceAccess wraps it: given a table name and resource ID, it fetches the matter_id first, then delegates. Route handlers get a single chokepoint rather than re-implementing the two-step lookup per endpoint.
The design is defense in depth: the server rejects unauthorized requests, and RLS rejects them again even if something slips past.
Two things to verify before adopting: the current_portal_role() function reads auth.jwt() ->> 'role', which requires a custom JWT claim to be injected - that configuration is not included here. The matters table is assumed to pre-exist; this commit doesn't create it. No tests were shipped, and the PR was merged within a minute of opening.
Spotted something wrong? Or know the PR text has fresher detail than the writeup above?