auth/role_grant_queries.ts

Account-grain check: does any actor on account_id hold an active global role_grant for role?

Symmetric with query_role_grant_has_role but keyed on the account instead of a single actor — for surfaces with auth: actor: 'none' that don't load auth.role_grants and can't use the in-memory has_scoped_role predicate. Joins role_grant → actor; matches only global role_grants (scope_id IS NULL) since the use case is "is the caller's account broadly admin", not scope-aware.

Fast under the existing idx_role_grant_actor index — the inner actor_id IN (...) subquery is index-scan, and the outer EXISTS stops at the first match.

Grant a role_grant to an actor. Idempotent — if an active role_grant already exists for this actor, role, and scope, returns the existing role_grant instead of creating a duplicate.

The ON CONFLICT target and the fallback SELECT both collapse NULL scopes via the same sentinel + index-side 'GLOBAL' token used by the partial unique index (role_grant_actor_role_scope_active_unique). The IS NOT DISTINCT FROM form on the fallback is deliberate — plain = would miss the NULL-scope case where the conflict fired.

scope_kind is paired-null with scope_id per the role_grant_scope_kind_paired CHECK; mismatched pairs raise at the DB layer rather than producing silent rows.

Revoke a role_grant by id, constrained to a specific actor.

Requires actor_id to prevent cross-account revocation (IDOR guard). Returns null if the role_grant is not found, already revoked, or belongs to a different actor.

Supersedes any pending offers for the revoked role_grant's (to_account, role, scope) in the same transaction. Prevents the "accept a pre-revoke offer to bypass the revoke" path — any stale offer becomes terminal at revoke time. A fresh post-revoke grant requires the grantor to call query_role_grant_offer_create again.

Find the account ID of an account that holds an active role_grant for a given role.

Joins role_grant → actor → account. Returns the first match, or null if none.

Find all active (non-revoked, non-expired) role_grants for an actor.

Look up the role of an active role_grant (constrained to a specific actor) plus the actor's account_id.

Used by admin routes to inspect the role_grant's role before acting (e.g., enforcing the admin-grant-path gate on revoke). The actor constraint mirrors query_revoke_role_grant so IDOR protection is consistent: a caller can only see role_grants belonging to the target actor.

The JOIN to actor collapses what used to be a second query_actor_by_id round-trip in the revoke handler into one read, which closes the small TOCTOU window where the actor row could be deleted between the IDOR check and the actor lookup. The account_id is needed by the audit envelope's target_account_id field and the SSE/WS socket-close fan-out targeting.

Returns null if the role_grant is not found, already revoked, or belongs to a different actor.

Check if an actor has an active role_grant for a given role.

The scope_id parameter selects between global and scoped checks: - Omitted or null — matches a global role_grant (scope_id IS NULL). Pre-scope callers keep their existing semantics. - A scope uuid — matches a role_grant bound to that exact scope.

The IS NOT DISTINCT FROM comparison handles the NULL case uniformly.

List all role_grants for an actor (including revoked/expired).

Revoke every active role_grant bound to a scope and supersede every pending offer at the scope, in one cascade.

Use this from a consumer's parent-scope delete handler (e.g., classroom deletion) — role_grant.scope_id and role_grant_offer.scope_id are polymorphic with no FK constraint by design, so a parent row deletion would otherwise orphan role_grants and offers. The cascade is role-agnostic: anything attached to the destroyed scope is cleaned up.

Both updates run as separate statements inside the caller's transaction (mirrors query_role_grant_revoke_role's shape). The two halves are independent — orphan pending offers can exist at a scope with no active role_grants, so the supersede half always runs even when no role_grant was revoked.

Revoke every active role_grant an actor holds for a given role.

With scoped role_grants a single actor+role tuple can hold several active role_grants (one per scope), so this revokes all of them. Pass query_revoke_role_grant(role_grant_id, ...) when a single scoped role_grant is the target.

Also supersedes pending offers for the actor's account across every scope of this role (the actor can no longer hold the role, so any pending offer of the same role is a bypass vector).

Result of query_role_grant_revoke_for_scope — every role_grant revoked plus every pending offer superseded by the scope-wide cascade.

Result of query_revoke_role_grant — the revoked role_grant plus any pending offers superseded by the revoke.

Result of query_role_grant_revoke_role — every role_grant revoked plus the pending offers superseded by the bulk revoke.