← ClaudeAtlas

effect-sql-rlslisted

Use when writing any query, migration, or test that touches tenant data in this repository. Covers withOrgScope and withWorkerScope, the two Postgres roles and why the boundary is in the database rather than in a lint rule, the shape a row-level security policy has to take, when Effect.orDie is right, and why a fixture seeds through the product's own write path.
vantionlabs/saas-starter · ★ 6 · API & Backend · score 73
Install: claude install-skill vantionlabs/saas-starter
# Tenant data, and the two things that keep it apart Every tenant-owned table is protected **twice**: a row-level security policy in Postgres, and `withOrgScope` around the query. Neither is trusted alone. Unit tests cover each; only `e2e/tests/tenancy.spec.ts` shows two real users, session to SQL, failing to see each other's rows. ## The connection is the boundary `packages/database/src/roles/init.sql` makes two roles: - **`vantion`** — what every application process connects as, and `NOBYPASSRLS`. A handler that forgets `withOrgScope` therefore reads **nothing** rather than reading everybody. - **`admin`** — holds `BYPASSRLS`, for the cross-tenant surface only. Which process may cross tenants is decided by Postgres, not by a reviewer. This was not true until recently. `docker compose` set `POSTGRES_USER: vantion`, making it the bootstrap **superuser** — so every policy in this schema was inert while `pg_class.relrowsecurity` read true. `packages/database/test/Role.test.ts` asserts the connection cannot bypass, because a suite pointed at a superuser passes every tenancy test while proving nothing. Switching the role found two production bugs, both the same shape: a worker escape on `using` but not on `with check`, and an UPDATE is checked by both. ## Which scope | Function | Who uses it | What it sets | | --------------------------- | ------------------------------------------------