search_events Table

Overview

search_events is a Supabase table that records one row per user search on Rangefinder. It tracks true search frequency and enables no-result rate analysis — two metrics that cannot be derived from the existing resource_events table.

Date created: March 2026 Migration: rangefinder/supabase/migrations/20260318120000_search_events.sql

Why a Separate Table

The existing resource_events table logs search_impression events — one row per resource returned in search results. This means:

• A search returning 12 results produces 12 rows, inflating the count.
• A search returning 0 results produces 0 rows, making it invisible.
• Counting rows by query gives impression count, not search frequency.

search_events solves both problems with a single row per search, including a result_count field that captures zero-result searches.

Schema

CREATE TABLE IF NOT EXISTS public.search_events (
  id            uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  query         text        NOT NULL,
  result_count  integer     NOT NULL DEFAULT 0,
  source        text,
  referrer      text,
  created_at    timestamptz NOT NULL DEFAULT now()
);

Column	Type	Description
id	uuid	Primary key
query	text	Raw search text (required, max 500 chars enforced at API)
result_count	integer	Number of resources returned (0 = no-result search)
source	text	Where the search originated (e.g. search)
referrer	text	HTTP referrer
created_at	timestamptz	When the search occurred

No resource_id foreign key — this is a search-level event, not resource-level.

Indexes

Index	Columns	Purpose
idx_search_events_created	created_at DESC	Recent searches, time-range queries
idx_search_events_query	query, created_at DESC	Per-query frequency lookups

RLS Policies

Matches the resource_events pattern:

Policy	Operation	Rule
Anyone can insert search events	INSERT	WITH CHECK (true)
Admins can read search events	SELECT	USING (public.is_admin() = true)
Admins can delete search events	DELETE	USING (public.is_admin() = true)

Write Path (Rangefinder)

1. User performs a search on the Rangefinder search page.
2. After loading completes (regardless of result count), trackSearchEvent(query, totalResults) fires from rangefinder/app/search/page.tsx.
3. The event is sent to POST /api/events via navigator.sendBeacon (fire-and-forget).
4. The API route (rangefinder/app/api/events/route.ts) detects event_type === 'search' and inserts into search_events instead of resource_events.

Deduplication: A ref-based key (query::totalResults) prevents duplicate fires from React re-renders within the same search.

Read Path (Pathfinder)

Two functions in pathfinder/lib/db/resourceEvents.ts:

• getTopSearchQueries(limit) — Reads search_events, groups by lowercased query, counts rows. Falls back to impression-based counting from resource_events if search_events is empty.
• getSearchNoResultRate() — Returns overall no-result rate and per-query no-result percentages.

These are consumed by:

• Insights page (/insights) — Top Searched Needs panel, No-Result Rate KPI.
• Admin Analytics page (/admin/analytics) — Top Search Queries list.

Relationship to resource_events

Table	What it tracks	Granularity	Used for
resource_events (search_impression)	Which resources appeared in results	1 row per resource per search	Resource-level visibility analytics
search_events	What users searched for	1 row per search	Search frequency, no-result rate

Both are written in parallel — neither replaces the other.

Materialized View

search_events has no materialized view. Queries run directly against the table. At current volume this is fast; a materialized view can be added later if needed.