auth/role_grant_queries.ts

Like query_account_has_global_role, but only counts the grant when the account and the granting actor are both active (deleted_at IS NULL). Used by the last-admin branch of the removability guard: a soft-deleted admin can't log in and is excluded from query_count_active_accounts_with_global_role, so the guard must use the same active predicate when testing whether the *target* is an admin — otherwise removing an already-tombstoned admin is falsely blocked as cannot_delete_last_admin. Soft-delete does not revoke role_grant rows (it's reversible), so the actor join filters `deleted_at IS NULL` too: a grant held only by a tombstoned actor must not keep the account reading as an admin (the same tombstone-exclusion the acting-actor resolution applies). The keeper branch deliberately uses the unconditional query_account_has_global_role (a keeper is never removable regardless of tombstone state).

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.

Deliberately unconditional — no deleted_at filter on the account or the granting actor. This backs the keeper removability guard, where the keeper must read as present *even if* its account or keeper-actor is already tombstoned (deleting it would brick keeper/daemon auth with no in-band recovery). That is the opposite posture from resolve_keeper_account_id, which *resolves* the daemon identity and so excludes a tombstoned keeper actor.

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.

Count active accounts (deleted_at IS NULL) holding an active global role_grant for role, counting only grants on active actors (act.deleted_at IS NULL). Used by the last-admin guard on account_delete / account_purge: soft-delete revokes neither a tombstoned account's nor a tombstoned actor's admin grant, so an unfiltered count would include unusable admins — this joins account *and* filters the actor, so an account whose only admin grant sits on a tombstoned actor no longer counts (otherwise the guard could fail-open and let the last usable admin be deleted).

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, counting only an active account + active actor (deleted_at IS NULL on both). This *resolves* an identity (the daemon-token keeper via resolve_keeper_account_id), so a tombstoned actor must not grant the role — the opposite posture from the removability guard query_account_has_global_role, which stays unconditional. Returns the first match by ascending account.id (deterministic under multiple holders), 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.