Mastering Supabase pgvector for Semantic Search in Next.js

-- Enable the pgvector extension to work with embedding vectors
CREATE EXTENSION IF NOT EXISTS vector;

-- Create our knowledge base table
CREATE TABLE public.documents (
  id uuid primary key default gen_random_uuid(),
  content text not null,
  metadata jsonb,
  -- 1536 is the correct dimensionality for OpenAI's text-embedding-3-small
  embedding vector(1536) 
);

-- Create an HNSW index for blazing fast vector similarity search
CREATE INDEX ON public.documents USING hnsw (embedding vector_cosine_ops);

CREATE OR REPLACE FUNCTION match_documents (
  query_embedding vector(1536),
  match_threshold float,
  match_count int
)
RETURNS TABLE (
  id uuid,
  content text,
  similarity float
)
LANGUAGE sql STABLE
AS $$
  SELECT
    documents.id,
    documents.content,
    1 - (documents.embedding <=> query_embedding) AS similarity
  FROM documents
  -- Filter by threshold to only return relevant matches
  WHERE 1 - (documents.embedding <=> query_embedding) > match_threshold
  ORDER BY documents.embedding <=> query_embedding
  LIMIT match_count;
$$;

// app/actions/embeddings.ts
'use server'

import OpenAI from 'openai'
import { createClient } from '@/lib/supabase/server'

const openai = new OpenAI()

export async function ingestDocument(content: string) {
  const supabase = createClient()

  // 1. Generate the embedding from OpenAI
  const response = await openai.embeddings.create({
    model: 'text-embedding-3-small',
    input: content.trim(),
  })

  const embedding = response.data[0].embedding

  // 2. Insert into Supabase
  const { error } = await supabase.from('documents').insert({
    content,
    embedding
  })

  if (error) {
    throw new Error('Failed to insert vector into database')
  }

  return { success: true }
}

// app/actions/search.ts
'use server'

import OpenAI from 'openai'
import { createClient } from '@/lib/supabase/server'

export async function performSemanticSearch(query: string) {
  const openai = new OpenAI()
  const supabase = createClient()

  // Turn user text query into an embedding
  const embeddingResponse = await openai.embeddings.create({
    model: 'text-embedding-3-small',
    input: query,
  })

  const queryEmbedding = embeddingResponse.data[0].embedding

  // Call the Postgres RPC function
  const { data: matchedDocs, error } = await supabase.rpc('match_documents', {
    query_embedding: queryEmbedding,
    match_threshold: 0.70, // Adjust depending on strictness needs
    match_count: 5 // Return top 5 results
  })

  if (error) throw new Error(error.message)
    
  return matchedDocs
}

// components/SemanticSearchbar.tsx
'use client'

import { useState, useTransition } from 'react'
import { performSemanticSearch } from '@/app/actions/search'

export function SemanticSearchbar() {
  const [query, setQuery] = useState('')
  const [results, setResults] = useState<any[]>([])
  const [isPending, startTransition] = useTransition()

  const handleSearch = (e: React.FormEvent) => {
    e.preventDefault()
    startTransition(async () => {
      const docs = await performSemanticSearch(query)
      setResults(docs)
    })
  }

  return (
    <div className="max-w-xl mx-auto space-y-4">
      <form onSubmit={handleSearch} className="flex gap-2">
        <input
          type="text"
          value={query}
          onChange={(e) => setQuery(e.target.value)}
          placeholder="Search by meaning or concept..."
          className="w-full p-2 border rounded-md"
        />
        <button 
          type="submit" 
          disabled={isPending}
          className="px-4 py-2 bg-black text-white rounded-md"
        >
          {isPending ? 'Searching...' : 'Search'}
        </button>
      </form>

      <ul className="grid gap-2">
        {results.map((r, i) => (
          <li key={i} className="p-4 border shadow-sm rounded-md">
            <p className="text-sm text-gray-800">{r.content}</p>
            <span className="text-xs text-green-600 block mt-2">
              {(r.similarity * 100).toFixed(1)}% Match
            </span>
          </li>
        ))}
      </ul>
    </div>
  )
}