Supabase + AI: pgvector, Embeddings and Semantic Search | Codea Bien

Semantic search used to be a weeks-long project: provisioning a Pinecone or Weaviate cluster, keeping it in sync with your database, and adding another bill to the stack. Now it's a column and a function. In the same PostgreSQL database.

pgvector in Supabase gives you embeddings, similarity search, and RAG without leaving SQL. In this post you'll enable the extension, generate your first embeddings, and build semantic search and recommendations that live next to the rest of your data.

What is pgvector

pgvector is a PostgreSQL extension that adds vector (embedding) support. This means you can:

Store embeddings of text, images, etc.
Do similarity search (cosine, L2, inner product)
Build RAG (Retrieval Augmented Generation) pipelines
All within your same PostgreSQL database

Enable pgvector

From the dashboard

Go to Database > Extensions
Search for vector
Click Enable

From SQL

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS vector;

Create a table with vectors

-- Table for documents with embeddings
CREATE TABLE documents (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  content TEXT NOT NULL,
  metadata JSONB DEFAULT '{}',
  embedding VECTOR(1536), -- Embedding dimension (1536 for OpenAI ada-002)
  created_at TIMESTAMPTZ DEFAULT now()
);

Index for fast search

-- HNSW index for approximate search (faster)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- Or IVFFlat index (alternative)
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

Generate embeddings

With OpenAI

// src/embeddings/openai.ts
import OpenAI from 'openai'

const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY })

export async function generateEmbedding(text: string): Promise<number[]> {
  const response = await openai.embeddings.create({
    model: 'text-embedding-ada-002',
    input: text
  })
  return response.data[0].embedding
}

With Supabase Edge Function

// supabase/functions/generate-embedding/index.ts
import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

serve(async (req) => {
  const { text } = await req.json()

  // Call embeddings API (OpenAI, Gemini, etc.)
  const embeddingResponse = await fetch('https://api.openai.com/v1/embeddings', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${Deno.env.get('OPENAI_API_KEY')}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      model: 'text-embedding-ada-002',
      input: text
    })
  })

  const { data } = await embeddingResponse.json()
  const embedding = data[0].embedding

  // Save to Supabase
  const supabase = createClient(
    Deno.env.get('SUPABASE_URL') ?? '',
    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') ?? ''
  )

  const { error } = await supabase
    .from('documents')
    .insert({
      content: text,
      embedding: JSON.stringify(embedding)
    })

  if (error) {
    return new Response(JSON.stringify({ error: error.message }), { status: 500 })
  }

  return new Response(JSON.stringify({ success: true }))
})

Semantic search

Search similar documents

-- Function for similarity search
CREATE OR REPLACE FUNCTION search_documents(
  query_embedding VECTOR(1536),
  match_count INT DEFAULT 5,
  match_threshold FLOAT DEFAULT 0.8
)
RETURNS TABLE (
  id UUID,
  content TEXT,
  metadata JSONB,
  similarity FLOAT
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    documents.id,
    documents.content,
    documents.metadata,
    1 - (documents.embedding <=> query_embedding) AS similarity
  FROM documents
  WHERE 1 - (documents.embedding <=> query_embedding) > match_threshold
  ORDER BY documents.embedding <=> query_embedding
  LIMIT match_count;
END;
$$;

Use from client

// src/search.ts
import { supabase } from './lib/supabase'
import { generateEmbedding } from './embeddings/openai'

export async function semanticSearch(query: string) {
  // Generate query embedding
  const queryEmbedding = await generateEmbedding(query)

  // Search similar documents
  const { data, error } = await supabase.rpc('search_documents', {
    query_embedding: JSON.stringify(queryEmbedding),
    match_count: 5,
    match_threshold: 0.7
  })

  if (error) throw error
  return data
}

// Example usage
const results = await semanticSearch('How do I configure authentication?')
console.log(results)
// [{ content: "Supabase Auth allows...", similarity: 0.92 }, ...]

RAG (Retrieval Augmented Generation)

RAG combines semantic search with text generation. The flow is:

Search relevant documents (pgvector)
Add them as context to the prompt
Generate response with LLM

Complete implementation

// src/rag.ts
import { supabase } from './lib/supabase'
import { generateEmbedding } from './embeddings/openai'
import OpenAI from 'openai'

const openai = new OpenAI()

export async function ragQuery(question: string) {
  // 1. Search relevant documents
  const queryEmbedding = await generateEmbedding(question)
  
  const { data: documents } = await supabase.rpc('search_documents', {
    query_embedding: JSON.stringify(queryEmbedding),
    match_count: 3,
    match_threshold: 0.7
  })

  if (!documents?.length) {
    return 'I did not find relevant information for your question.'
  }

  // 2. Build context
  const context = documents
    .map(doc => doc.content)
    .join('\n\n')

  // 3. Generate response with context
  const completion = await openai.chat.completions.create({
    model: 'gpt-4',
    messages: [
      {
        role: 'system',
        content: `Answer the question based ONLY on the following context. If the context does not contain the information, say you don't have enough information.\n\nContext:\n${context}`
      },
      { role: 'user', content: question }
    ]
  })

  return completion.choices[0].message.content
}

// Example
const answer = await ragQuery('How do I implement RLS in Supabase?')
console.log(answer)

Complete example: Chatbot with memory

// src/chatbot.ts
import { supabase } from './lib/supabase'
import { generateEmbedding } from './embeddings/openai'
import OpenAI from 'openai'

const openai = new OpenAI()

export class RAGChatbot {
  private conversationHistory: Array<{ role: string; content: string }> = []

  async ask(question: string) {
    // Search relevant documents
    const queryEmbedding = await generateEmbedding(question)
    
    const { data: documents } = await supabase.rpc('search_documents', {
      query_embedding: JSON.stringify(queryEmbedding),
      match_count: 3
    })

    const context = documents?.map(d => d.content).join('\n') || ''

    // Add to history
    this.conversationHistory.push({
      role: 'user',
      content: question
    })

    // Generate response
    const completion = await openai.chat.completions.create({
      model: 'gpt-4',
      messages: [
        {
          role: 'system',
          content: `You are an expert assistant on Supabase. Use the following context to respond:\n\n${context}`
        },
        ...this.conversationHistory
      ]
    })

    const answer = completion.choices[0].message.content || ''
    
    this.conversationHistory.push({
      role: 'assistant',
      content: answer
    })

    return answer
  }
}

// Usage
const chatbot = new RAGChatbot()
console.log(await chatbot.ask('What is RLS?'))
console.log(await chatbot.ask('How do I implement it?')) // Remembers previous context

Multimodal search

pgvector can store vectors from any source - text, images, audio:

// src/multimodal.ts
// Image embedding (with CLIP, for example)
const imageEmbedding = await generateImageEmbedding(imageUrl)

await supabase.from('documents').insert({
  content: 'Image description',
  embedding: JSON.stringify(imageEmbedding),
  metadata: { type: 'image', url: imageUrl }
})

Codea Bien reference

For more on embeddings and RAG in Angular, check: RAG en Angular: Búsqueda inteligente con embeddings y Gemini

What's next

In the next article we cover Supabase + AI Agents: how to use the MCP Server to connect AI agents to your database, and build intelligent automations.