Files

23 lines
1.7 KiB
Markdown

---
paths: ["supabase/migrations/**", "supabase/tests/**", "packages/supabase/**", "apps/app/src/state/authStore.ts"]
---
# Database (Supabase)
- The auth session lives in the Supabase client (localStorage, PKCE); the app reads it through `state/authStore.ts` only. Profile columns the user may edit are the column grants in the profiles migrations; never widen them from the client.
- Platform roles (`platform_role`: owner/admin/moderator/support) are NOT professional
statuses (practitioner/student/professor come from verification, cabinet roles from
organization_members). Never add a professional status to `platform_role`.
- Every `public` table: RLS enabled, `revoke all` from API roles then exact grants,
separate policies per command, no `using (true)` for anon/authenticated (test-enforced).
- Writes that change privileges or sensitive state go through a security-definer RPC
that checks `auth.uid()` and calls `private.log_event()`. Never a direct table policy.
- Helpers in `private` are `security definer`, `set search_path = ''`, EXECUTE revoked
from public and granted only to the roles that evaluate them.
- A migration is never edited once applied. Each migration ships its pgTAP file with
allowed AND denied cases, and a regenerated `database.types.ts`.
- Every migration step runs first on the throwaway sandbox (`pnpm db:sandbox`,
`scripts/db-sandbox.sh`), never against DEV; changes to DEV stay owner actions (LRN-080).
- The browser holds the anon key only. The `service_role` key value never appears in env.js, .env.example or code;
operator scripts under `packages/*/scripts/` read it from the environment and are never bundled.
- pgTAP files run on PGlite and on the real image: scope counts to fixture ids, pin `collate "C"` on text ordering.