RLS policies

:::caution Every table in SOMA MUST have RLS enabled. No exceptions. The policies live in packages/db/rls/*.sql and are applied by pnpm db:setup. :::

The contract

Every row has a user_id column. Every policy reads auth.uid() (the Supabase JWT claim) and admits the row only when user_id = auth.uid().

ALTER TABLE entities ENABLE ROW LEVEL SECURITY;

CREATE POLICY entities_select_own ON entities
  FOR SELECT
  USING (user_id = auth.uid());

CREATE POLICY entities_insert_own ON entities
  FOR INSERT
  WITH CHECK (user_id = auth.uid());

CREATE POLICY entities_update_own ON entities
  FOR UPDATE
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

CREATE POLICY entities_delete_own ON entities
  FOR DELETE
  USING (user_id = auth.uid());

All five core tables + oauth_connections follow the same shape. See packages/db/rls/entities.sql etc. for full text.

Bypass path — service role

Background workflows (Inngest functions, Gmail ingestion) run without a user JWT. They connect with DATABASE_URL_SERVICE_ROLE (the postgres superuser), which bypasses RLS by default. This is why @soma/tools takes an explicit resourceId parameter — the tool must scope its queries by WHERE user_id = $resourceId manually when running under service role.

:::note If a tool forgets to scope by resourceId, it will happily read across tenants under service role. Every tool in @soma/tools has this check. Code review: if you see a raw Drizzle query without user_id = resourceId, reject the PR. :::

Testing

Integration tests for RLS live in packages/db/rls/__tests__/rls.test.ts. They require a live Postgres (mock stores don't implement RLS). The current tests are placeholder (expect(true).toBe(true)) — completing them is an open TODO.

Adding a new table

1. Add the Drizzle schema in packages/db/src/schema/<table>.ts.
2. Add an RLS policy file packages/db/rls/<table>.sql with ENABLE ROW LEVEL SECURITY and per-operation policies.
3. Run pnpm db:generate && pnpm db:setup.
4. Review the policy diff in the PR. Every reviewer treats missing RLS as a blocker.