Cinderblock

Policy evaluation cost

Cinderblock's RLS helpers (is_workspace_member, has_workspace_role) evaluate a subquery per row on every SELECT. The cost is bounded by the partial index workspace_members_user_active_idx:

create unique index workspace_members_user_active_idx
  on workspace_members (user_id, workspace_id)
  where removed_at is null;

With this index, each is_workspace_member()call is an index lookup against a tuple whose cardinality is "active memberships per user" — typically 1–3 for a SaaS user.

Cost vs. JWT-embedded roles

The trade-off vs. embedding app_metadata.workspaces[].{id, role} in the JWT is explicit:

  • DB-lookup (Cinderblock default): ~50 ms p95 on a 100k-row workspace read. Role revocation propagates on the next query.
  • JWT-embedded: JSON probe with no DB round trip. Role changes only take effect at next token refresh (up to 60min stale).

Immediate role revocation matters more than the 50 ms tail for the demo's pitch — "you can demote an admin and they can't do admin things on the next request." A forker who wants the other trade-off can swap in the Auth Hook variant documented under Extensions / JWT-embedded roles.

The scale fixture

A separate opt-in suite lives in supabase/tests/scale/ — invoked nightly on main via .github/workflows/scale.yml, or locally as:

npx supabase test db \
  supabase/tests/scale/01_fixture.sql \
  supabase/tests/scale/02_policy_cost.sql

It seeds 50 workspaces × ~20 members × 2,000 tasks each (~100,000 tasks, ~1,000 memberships) and asserts:

  • An outsider with zero memberships still reads zero of the 100k scale tasks — the cross-tenant boundary holds at scale, not just against the 15-task hostile fixture.
  • EXPLAIN of select * from tasks where workspace_id = $1 and status = 'todo' limit 50 shows the tasks node is an index scan, not a Seq Scan — proving the (workspace_id, status) index survives the policy's is_workspace_member() rewrite.

The suite is kept off per-PR CI because the fixture seed adds ~30s and the volume is the point — flaky timings would punish unrelated PRs.