AGENTS.md@template/stack/supabase git:20261021.a22139aA

Immutable. This exact content never changes and is served at /api/v1/blob/f9f949920194d7e2.

# 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)`), 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.