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 test set (supabase test db --tag scale, nightly on main) seeds a 100k-task workspace with 1k members and asserts:

  • select * from tasks where workspace_id = $1 limit 50 returns in < 50 ms p95.
  • The query plan uses the partial index on workspace_members and the (workspace_id, status) index on tasks — no seq scan on either.