Supabase Full-Text Search: Implementing Search Functionality

-- Add tsvector column for search
alter table posts
  add column if not exists search_vector tsvector;

-- Create GIN index for fast searches
create index posts_search_vector_idx on posts using gin(search_vector);

-- Generate search vectors from existing data
update posts
set search_vector = 
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(content, '')), 'B');

-- Breakdown:
-- to_tsvector('english', text) - Converts text to searchable format
-- setweight(..., 'A') - Title has higher weight (importance)
-- setweight(..., 'B') - Content has lower weight
-- || - Concatenates the vectors
-- coalesce - Handles NULL values

-- View the search vector
select title, search_vector from posts limit 1;
-- Result: 'guid':1A 'complet':3A 'supabas':2A 'blog':5B 'post':6B ...

-- Simple search query
select
  id,
  title,
  content
from posts
where search_vector @@ to_tsquery('english', 'supabase');

-- The @@ operator matches tsvector against tsquery

-- Search with OR (multiple terms)
select * from posts
where search_vector @@ to_tsquery('english', 'supabase | database');
-- Matches posts containing 'supabase' OR 'database'

-- Search with AND (all terms required)
select * from posts
where search_vector @@ to_tsquery('english', 'supabase & tutorial');
-- Matches posts containing both 'supabase' AND 'tutorial'

-- Search with NOT (exclude terms)
select * from posts
where search_vector @@ to_tsquery('english', 'supabase & !firebase');
-- Matches 'supabase' but NOT 'firebase'

-- Phrase search (adjacent words)
select * from posts
where search_vector @@ phraseto_tsquery('english', 'getting started');
-- Matches 'getting started' as a phrase

-- Using JavaScript client
const { data, error } = await supabase
  .from('posts')
  .select('*')
  .textSearch('search_vector', 'supabase tutorial');

-- Search with relevance ranking
select
  id,
  title,
  ts_rank(search_vector, query) as rank
from posts,
  to_tsquery('english', 'supabase & tutorial') query
where search_vector @@ query
order by rank desc;

-- ts_rank calculates relevance score based on:
-- - Term frequency
-- - Term positions
-- - Document length

-- Enhanced ranking with normalization
select
  id,
  title,
  ts_rank_cd(search_vector, query, 32) as rank
from posts,
  to_tsquery('english', 'supabase') query
where search_vector @@ query
order by rank desc;

-- ts_rank_cd considers:
-- - Cover density (how close terms are)
-- - 32 flag normalizes by document length

-- Custom weighted ranking
select
  id,
  title,
  ts_rank(
    search_vector,
    query,
    1  -- Normalization: divides rank by document length
  ) as rank
from posts,
  plainto_tsquery('english', 'supabase tutorial') query
where search_vector @@ query
order by rank desc
limit 10;

-- JavaScript with ranking
const { data } = await supabase
  .rpc('search_posts', { search_query: 'supabase tutorial' });

-- Requires creating a database function (see next section)

-- Create reusable search function
create or replace function search_posts(search_query text)
returns table (
  id uuid,
  title text,
  content text,
  rank real
)
language plpgsql
as $$
begin
  return query
  select
    posts.id,
    posts.title,
    posts.content,
    ts_rank(posts.search_vector, query) as rank
  from posts,
    plainto_tsquery('english', search_query) query
  where posts.search_vector @@ query
  order by rank desc;
end;
$$;

-- Usage in JavaScript
const { data, error } = await supabase
  .rpc('search_posts', {
    search_query: 'supabase authentication'
  });

console.log(data);
// Returns ranked results

-- Advanced search with filters
create or replace function advanced_search(
  search_query text,
  category_filter text default null,
  published_only boolean default true
)
returns table (
  id uuid,
  title text,
  slug text,
  category text,
  created_at timestamptz,
  rank real
)
language plpgsql
as $$
begin
  return query
  select
    p.id,
    p.title,
    p.slug,
    c.name as category,
    p.created_at,
    ts_rank(p.search_vector, query) as rank
  from posts p
  left join categories c on p.category_id = c.id,
    plainto_tsquery('english', search_query) query
  where
    p.search_vector @@ query
    and (not published_only or p.published = true)
    and (category_filter is null or c.slug = category_filter)
  order by rank desc
  limit 50;
end;
$$;

-- Trigger to automatically update search_vector
create or replace function posts_search_vector_update()
returns trigger
language plpgsql
as $$
begin
  new.search_vector :=
    setweight(to_tsvector('english', coalesce(new.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(new.content, '')), 'B');
  return new;
end;
$$;

create trigger posts_search_vector_update_trigger
  before insert or update on posts
  for each row
  execute function posts_search_vector_update();

-- Now search_vector updates automatically:
insert into posts (title, content, user_id)
values ('New Post', 'Content here...', 'user-123');
-- search_vector automatically generated

update posts set title = 'Updated Title' where id = 'post-123';
-- search_vector automatically updated

-- Search across posts and comments
create or replace function global_search(search_query text)
returns table (
  result_type text,
  id uuid,
  title text,
  snippet text,
  rank real
)
language plpgsql
as $$
begin
  return query
  -- Search posts
  select
    'post'::text as result_type,
    p.id,
    p.title,
    left(p.content, 200) as snippet,
    ts_rank(p.search_vector, query) as rank
  from posts p,
    plainto_tsquery('english', search_query) query
  where p.search_vector @@ query and p.published = true
  
  union all
  
  -- Search comments
  select
    'comment'::text as result_type,
    c.id,
    p.title,  -- Post title for context
    left(c.content, 200) as snippet,
    ts_rank(to_tsvector('english', c.content), query) as rank
  from comments c
  join posts p on c.post_id = p.id,
    plainto_tsquery('english', search_query) query
  where to_tsvector('english', c.content) @@ query
  
  order by rank desc
  limit 50;
end;
$$;

-- Usage
const { data } = await supabase
  .rpc('global_search', { search_query: 'authentication' });

-- Enable pg_trgm extension for fuzzy matching
create extension if not exists pg_trgm;

-- Create autocomplete function
create or replace function autocomplete_posts(search_term text)
returns table (suggestion text)
language plpgsql
as $$
begin
  return query
  select distinct title as suggestion
  from posts
  where
    title ilike '%' || search_term || '%'
    and published = true
  order by similarity(title, search_term) desc
  limit 10;
end;
$$;

-- Trigram index for fast autocomplete
create index posts_title_trgm_idx on posts using gin(title gin_trgm_ops);

-- React autocomplete component
import { useState, useEffect } from 'react'
import { supabase } from './supabaseClient'

function SearchAutocomplete() {
  const [query, setQuery] = useState('')
  const [suggestions, setSuggestions] = useState([])

  useEffect(() => {
    if (query.length < 2) {
      setSuggestions([])
      return
    }

    const fetchSuggestions = async () => {
      const { data } = await supabase
        .rpc('autocomplete_posts', { search_term: query })
      
      setSuggestions(data?.map(d => d.suggestion) || [])
    }

    const debounce = setTimeout(fetchSuggestions, 300)
    return () => clearTimeout(debounce)
  }, [query])

  return (
    <div className="autocomplete">
      <input
        value={query}
        onChange={(e) => setQuery(e.target.value)}
        placeholder="Search..."
      />
      {suggestions.length > 0 && (
        <ul className="suggestions">
          {suggestions.map((s, i) => (
            <li key={i} onClick={() => setQuery(s)}>{s}</li>
          ))}
        </ul>
      )}
    </div>
  )
}