Prefix-based actor search.
Sibling to actor_lookup_queries.ts — that resolves a batch of ids to
labels; this resolves a partial name to candidate actors. Same row
shape (ActorLookupRow) so the labels arc on the consumer side stays
uniform.
Case-insensitive LIKE-prefix on actor.name backed by the
idx_actor_name_lower functional index. LIKE wildcards (%, _,
\) in the query string are escaped at the JS layer so the
prefix-only contract is enforceable — an unescaped %xyz would
widen the surface to full-LIKE and defeat the per-call cap as a
binding bound.
Auth filtering — scope_ids
When scope_ids is non-empty, the result is filtered to actors
holding an active role_grant on one of the supplied scopes. Active
means revoked_at IS NULL AND (expires_at IS NULL OR expires_at > NOW()).
Stale (revoked / expired) role_grants do not confer membership for
search-visibility purposes — otherwise a removed student would
remain visible to teachers indefinitely.
The DISTINCT on actor.id collapses the case where an actor holds
multiple matching role_grants in the supplied scope set into one row.
When scope_ids is omitted (admin-only global path; the handler
gates), no role_grant join — every actor with a matching prefix is
returned.
Info-leak posture (see actor_search_action_specs.ts §audit)
- Row shape omits account_id — the join is control-plane, not
wire-visible. Identical to actor_lookup_queries.ts.
- Hard-deleted actors (cascade-orphaned via actor.account_id FK)
drop out silently.
- No created_at / updated_at projected (timing-oracle avoidance).
- Scope-membership uses ANY on the supplied scope_ids array but
never surfaces "which scope matched" — the result row carries only
the actor's wire shape. An attacker passing a random scope_id
learns at most "this scope has at least one member matching X"
if a match exists, indistinguishable from a no-match search; the
caller-passes-scope_ids design (handler trusts the array as a
filter, not as authority) means the attacker had to obtain the
scope_id from somewhere else first.
Caller bounds limit (the action-spec layer enforces
ACTOR_SEARCH_LIMIT_MAX); SQL clamps to that cap on the call site
before reaching this query.