git:20260930.1462bf9 to git:20261021.a22139a
4 added, 2 removed. Audit A to A.
# supabase — the shared backend (schema, RLS, tests, functions)
ONE Postgres database that the Next web host and the Expo mobile client both reach through the
SAME RLS policies. Schema truth is SQL-first; there is no ORM.
## Layout
- `schemas/*.sql` — the DECLARATIVE source of truth (`00_shared`, `10_account`, `20_notes`, …),
the desired end-state a reviewer reads.
- `migrations/<timestamp>_<slice>.sql` — the APPLIED, append-only history. `supabase db push`
records a migration by FILENAME, so editing an already-applied file changes nothing on a
database that ran it and silently diverges deployed schema from committed history. Never edit
or delete a committed migration — add a new timestamped one (`supabase migration new`).
- `tests/*.sql` — pgTAP. `rls_structure.test.sql` reads back what the database compiled;
`rls_isolation.test.sql` proves the empty-set principle through raw role-switch. Both run on
`pnpm db:test` and are the structural twin of `tests/rls/` (the supabase-js client suite).
`rls_grants.generated.test.sql` is GENERATED by `node tools/gen-grant-assertions.mjs`.
- `functions/<name>/index.ts` — Edge Functions: the ONLY sanctioned home for service-role code,
each with a merged `docs/adr/NNNN-<slug>.md`.
- `seed.sql` — fixtures (DML belongs here, not in a migration). `config.toml` — local ports
(API 54321, Postgres 54322, Studio 54323).
## Every user-scoped table, in the migration that CREATES it
- `ENABLE` + `FORCE ROW LEVEL SECURITY` (ENABLE alone still lets the table owner — the role that
runs migrations — read every row; FORCE closes that, and only the REVOKE below constrains a
`BYPASSRLS` role).
- Four per-operation policies (SELECT/INSERT/UPDATE/DELETE, never `FOR ALL`), each `TO
- authenticated`, each predicate REAL (no `USING (true)`), identity via the initPlan sub-select
- `(select auth.uid())` — hoisted once per statement, not run per row.
+ authenticated`, each predicate REAL (no `USING (true)`), scoped by the NOT NULL `org_id`
+ through the initPlan helpers `org_id = ANY((SELECT private.member_org_ids())::uuid[])` and
+ the `private.member_ranks()` rank floor — hoisted once per statement, not run per row. Never a
+ per-user `owner_id = (select auth.uid())` scope; `schemas/20_notes.sql` is the pattern.
- `WITH CHECK` on every INSERT and UPDATE (the only defence against writing under, or handing
away, another user's owner column).
- `REVOKE ALL … FROM anon`, `FROM service_role` AND `FROM authenticated`, then `GRANT` to
`authenticated` exactly the operations the policies admit (`SELECT` alone on a read-only
table). The platform default grants ALL to all three, and a GRANT removes nothing — skip the
third revoke and `authenticated` keeps TRUNCATE, REFERENCES and TRIGGER. That revoke needs an
`-- adr:` marker (the `migrations` gate). Then `pnpm gen` (or `node
tools/gen-grant-assertions.mjs`) regenerates `tests/rls_grants.generated.test.sql`, the exact
privileges of every table; never edit its rows or its `plan()` by hand. A table stays
unreachable by an Edge Function until a later, ADR'd migration grants it explicitly, per table.
- A leading-column index on the owner column (an index whose LEADING column is the owner turns
the policy qual into an Index Cond; when keyset-paginated the same index carries the ORDER BY
tail). A two-row test database never reveals a missing one.
## Rules
- Destructive DDL (DROP/TRUNCATE) needs a resolvable `-- adr: docs/adr/<file>`; non-fixture DML
in a migration needs `-- harness-allow-dml: <reason>`. The `migrations` gate enforces both.
- `pnpm db:types` regenerates `packages/platform/supabase/src/database.types.ts` (byte-diffed by
the `types-drift` gate). Exemptions from FORCE RLS are a reviewed `tools/rls-exempt.json` entry.
- Reviewer: `security-reviewer` (MUST) on any migration, schema, policy, or GRANT change.