Add anon-granted hivemind_public_search RPC (snippet-only wrapper over hivemind_lexical_candidates)

[peteromallet]

Request: grant anon a narrow hivemind_public_search RPC

Context (verified live 2026-08-19)

The public search path is broken for real queries. The pack/CLI search used to
send PostgREST ilike predicates against the unified_feed view with the
anon key. That path no longer works:

• A single multi-word phrase (or=(title.ilike.*<query>*,body.ilike.*<query>*))
  returns zero rows for any multi-word query — the literal substring never
  occurs in the corpus. Verified: "Minimax distillation LoRA" → 0 results.
• A per-token OR over unified_feed (the union + jsonb_build_object +
  lateral joins) blows the anon role's 3s statement_timeout
  (HTTP 500 / SQLSTATE 57014) — a LIMIT can't help because the 1.28M-row
  derived-view scan exceeds the budget.

The repo's own fast path — hivemind_lexical_candidates (schema/008–013) —
queries the raw tables and is exactly what public search needs, but it is
SECURITY DEFINER and revoked from anon (schema/011), so it's unusable
with the publishable key.

The ask

One new function: a snippet-only, anon-granted, 2s-backstopped, limit≤20
wrapper around the existing hivemind_lexical_candidates engine. Please do
not grant hivemind_lexical_search as-is — it hydrates full
unified_feed-shaped rows (limit 100) and was revoked from anon by design
(011). The wrapper below addresses that: hydrate after the limit, emit a
280-char snippet only, never payload, never a full row.

Proposed SQL

create or replace function public.hivemind_public_search(
  p_query    text,
  p_limit    int     default 10,
  p_kinds    text[]  default '{}',     -- message | workflow | distillation | resource
  p_channels text[]  default '{}',
  p_since    timestamptz default null
)
returns jsonb
language plpgsql
security definer
set search_path = public, pg_temp
as $$
declare
  v_query text := btrim(coalesce(p_query, ''));
  v_limit int;
  v_cand  int;
  v_kinds text[] := coalesce(p_kinds, '{}');
  v_out   jsonb;
begin
  set local statement_timeout = '2000ms';

  if v_query = '' or char_length(v_query) > 200 then
    raise exception 'query must be 1..200 chars';
  end if;
  v_limit := least(greatest(coalesce(p_limit, 10), 1), 20);
  v_cand  := least(5 * v_limit, 80);
  if coalesce(array_length(v_kinds, 1), 0) > 8
     or coalesce(array_length(p_channels, 1), 0) > 16 then
    raise exception 'filter too wide';
  end if;

  with cand as (
    select entity_type, item_id, representation_type, matched_snippet,
           lexical_rank, lexical_source, created_at
      from public.hivemind_lexical_candidates(
             p_query := v_query,
             p_candidate_limit := v_cand,
             p_kinds := v_kinds,
             p_sources := '{}',
             p_item_ids := '{}',
             p_since := p_since,
             p_channels := coalesce(p_channels, '{}'),
             p_authors := '{}'
           )
     limit v_limit
  ),
  hydrated as (
    -- snippet only: left(body/content/answer, 280). no payload. no unified_feed.
    select c.entity_type,
           c.item_id,
           c.lexical_rank,
           c.created_at,
           c.matched_snippet,
           case c.entity_type
             when 'message' then left(m.content, 280)
             when 'distillation' then left(d.answer, 280)
             else left(r.body, 280)
           end as snippet,
           case c.entity_type
             when 'message' then null
             when 'distillation' then d.question
             else r.title
           end as title,
           case c.entity_type
             when 'message' then 'message'
             when 'distillation' then 'distillation'
             else r.kind
           end as kind
      from cand c
      left join public.message_feed m
        on c.entity_type = 'message' and m.message_id::text = c.item_id
      left join public.distillations d
        on c.entity_type = 'distillation' and d.id::text = c.item_id
       and d.status in ('pending', 'approved')
      left join public.external_resources r
        on c.entity_type not in ('message', 'distillation')
       and r.id::text = c.item_id
  )
  select jsonb_build_object(
           'results', coalesce(jsonb_agg(
              jsonb_build_object(
                'evidence_id', 'hivemind:' ||
                  case entity_type
                    when 'message' then 'message_feed:'
                    when 'distillation' then 'distillations:'
                    else 'external_resources:'
                  end || item_id,
                'kind', kind,
                'title', title,
                'snippet', snippet,
                'matched_snippet', matched_snippet,
                'score', lexical_rank,
                'created_at', created_at
              ) order by lexical_rank desc nulls last, created_at desc, item_id
           ), '[]'::jsonb),
           'count', (select count(*) from hydrated),
           'meta', jsonb_build_object('limit', v_limit, 'timeout_ms', 2000)
         )
    into v_out
    from hydrated;

  return v_out;
end;
$$;

revoke execute on function public.hivemind_public_search(text,int,text[],text[],timestamptz)
  from public;
grant execute on function public.hivemind_public_search(text,int,text[],text[],timestamptz)
  to anon, authenticated, service_role;

Why this shape

• Reuses hivemind_lexical_candidates (FTS + trigram + identifier-normalized
  hybrid, already built in 008–013) — no new engine.
• Snippet-only + post-limit hydration addresses the reason 011 locked anon
  out (full identity stream / fat rows).
• 2s backstop fits under the 3s anon statement_timeout.
• Clients get one call: POST /rest/v1/rpc/hivemind_public_search.

Until this exists, public clients fall back to a parallel 3-scope merge over
the raw tables (message_feed / external_resources / distillations) with
per-token ILIKE predicates and client-side ranking — which is what the pack
executor ships now, and what VibeComfy's client already does.

Acceptance

• POST /rest/v1/rpc/hivemind_public_search with anon key + {"query": "wan animate workflow", "limit": 10} returns ≥1 snippet result in <2s.
• Multi-word queries return real rows (not the zero-row phrase miss).
• No payload / full-row fields in the response.