Search

ALTER TABLE prompts ADD COLUMN search_tokens tsvector;
CREATE INDEX idx_prompts_search_tokens ON prompts USING GIN(search_tokens);

CREATE FUNCTION update_prompt_search_tokens_trigger()
RETURNS TRIGGER AS $$
DECLARE
  p_title TEXT;
  p_description TEXT;
  p_content TEXT;
  p_id UUID;
BEGIN
  IF TG_TABLE_NAME = 'prompts' THEN
    p_id := NEW.id;
    p_title := NEW.title;
    p_description := NEW.description;
    p_content := get_latest_prompt_content(p_id);
    
    NEW.search_tokens := (
      setweight(to_tsvector('english', COALESCE(p_title, '')), 'A') ||
      setweight(to_tsvector('english', COALESCE(p_description, '')), 'B') ||
      setweight(to_tsvector('english', COALESCE(p_content, '')), 'C')
    );
    RETURN NEW;
  ELSIF TG_TABLE_NAME = 'prompt_versions' THEN
    -- Update parent prompt's search_tokens when new version is created
    p_id := NEW.prompt_id;
    SELECT title, description INTO p_title, p_description 
    FROM prompts WHERE id = p_id;
    p_content := NEW.content;
    
    UPDATE prompts
    SET search_tokens = (
      setweight(to_tsvector('english', COALESCE(p_title, '')), 'A') ||
      setweight(to_tsvector('english', COALESCE(p_description, '')), 'B') ||
      setweight(to_tsvector('english', COALESCE(p_content, '')), 'C')
    )
    WHERE id = p_id;
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

import { searchPrompts } from '@/features/search/actions';

const { data, error } = await searchPrompts('customer email template');

export async function searchPrompts(
  query: string,
  options?: { userId?: string; collectionId?: string; archived?: boolean },
) {
  const supabase = createClient(cookieStore);

  if (!query || query.trim() === '') {
    return { data: [], error: null };
  }

  const validation = searchSchema.safeParse({ query, ...options });
  if (!validation.success) {
    return { data: null, error: 'Invalid search query or parameters' };
  }

  const { data, error } = await supabase.rpc('search_prompts', {
    query_text: query,
    filter_user_id: options?.userId || null,
    filter_collection_id: options?.collectionId || null,
    filter_archived: typeof options?.archived === 'boolean' ? options.archived : null,
  });

  if (error) {
    console.error('Search error:', error);
    return { data: null, error: `Search failed: ${error.message}` };
  }

  return { data: data as SearchResult[], error: null };
}

const searchSchema = z.object({
  query: z.string().min(1).max(500),
  userId: z.string().uuid().optional(),
  collectionId: z.string().uuid().optional(),
  archived: z.boolean().optional(),
});

interface SearchResult {
  id: string;
  user_id: string;
  title: string;
  description: string | null;
  archived_at: string | null;
  is_public: boolean;
  created_at: string;
  updated_at: string;
  latest_content: string;
  latest_version_id: string;
  collection_ids: string[];
  rank: number;  // Relevance score (higher = better match)
}

const { data } = await searchPrompts('api', {
  userId: 'user-uuid',
});

const { data } = await searchPrompts('template', {
  collectionId: 'collection-uuid',
});

AND (filter_collection_id IS NULL OR EXISTS (
  SELECT 1 FROM collection_prompts cp 
  WHERE cp.prompt_id = p.id AND cp.collection_id = filter_collection_id
))

// Only archived prompts
const { data } = await searchPrompts('old', {
  archived: true,
});

// Only active prompts (default behavior)
const { data } = await searchPrompts('active', {
  archived: false,
});

// All prompts (omit the filter)
const { data } = await searchPrompts('all');

CREATE FUNCTION search_prompts(
  query_text TEXT,
  filter_user_id UUID DEFAULT NULL,
  filter_collection_id UUID DEFAULT NULL,
  filter_archived BOOLEAN DEFAULT NULL
)
RETURNS TABLE (
  id UUID,
  user_id UUID,
  title TEXT,
  description TEXT,
  created_at TIMESTAMP WITH TIME ZONE,
  updated_at TIMESTAMP WITH TIME ZONE,
  latest_content TEXT,
  latest_version_id UUID,
  rank REAL
) AS $$
DECLARE
  search_query tsquery;
BEGIN
  -- Handle empty query
  IF query_text IS NULL OR trim(query_text) = '' THEN
    RETURN;
  END IF;

  -- Create prefix-matching query
  SELECT string_agg(lexeme || ':*', ' & ')::tsquery INTO search_query
  FROM unnest(to_tsvector('english', query_text));

  RETURN QUERY
  SELECT
    p.id,
    p.user_id,
    p.title,
    p.description,
    p.created_at,
    p.updated_at,
    get_latest_prompt_content(p.id) as latest_content,
    get_latest_prompt_version_id(p.id) as latest_version_id,
    ts_rank(p.search_tokens, search_query) as rank
  FROM prompts p
  WHERE p.search_tokens @@ search_query
  AND (filter_user_id IS NULL OR p.user_id = filter_user_id)
  AND (filter_collection_id IS NULL OR EXISTS (
    SELECT 1 FROM collection_prompts cp 
    WHERE cp.prompt_id = p.id AND cp.collection_id = filter_collection_id
  ))
  AND (filter_archived IS NULL OR 
       (filter_archived = true AND p.archived_at IS NOT NULL) OR
       (filter_archived = false AND p.archived_at IS NULL))
  ORDER BY rank DESC;
END;
$$ LANGUAGE plpgsql STABLE;

SELECT string_agg(lexeme || ':*', ' & ')::tsquery INTO search_query
FROM unnest(to_tsvector('english', query_text));

CREATE INDEX idx_prompts_search_tokens 
  ON prompts USING GIN(search_tokens);

{
  "jsonrpc": "2.0",
  "method": "tools/call",
  "params": {
    "name": "search_prompts",
    "arguments": {
      "query": "customer support"
    }
  }
}

// Search with multiple filters
const results = await searchPrompts('api documentation', {
  collectionId: engineeringCollectionId,
  archived: false,
});

// Display results with custom UI
results.data?.forEach(result => {
  console.log(`${result.title} (rank: ${result.rank})`);
});

// Log searches for analytics
async function logSearch(userId: string, query: string) {
  await supabase.from('search_logs').insert({
    user_id: userId,
    query,
    timestamp: new Date().toISOString(),
  });
}