Supabase AI: Vector Search, Hybrid Search, and the $5B Valuation

1-- From the Supabase Dashboard or SQL Editor
2CREATE EXTENSION IF NOT EXISTS vector;
3 
4-- Create a table with a vector column
5CREATE TABLE documents (
6  id BIGSERIAL PRIMARY KEY,
7  content TEXT NOT NULL,
8  embedding VECTOR(1536), -- OpenAI text-embedding-3-small dimension
9  metadata JSONB DEFAULT '{}',
10  created_at TIMESTAMPTZ DEFAULT NOW()
11);
12 
13-- HNSW index for performance
14CREATE INDEX ON documents
15  USING hnsw (embedding vector_cosine_ops)
16  WITH (m = 16, ef_construction = 200);

1import { createClient } from '@supabase/supabase-js';
2import OpenAI from 'openai';
3 
4const supabase = createClient(
5  process.env.SUPABASE_URL!,
6  process.env.SUPABASE_ANON_KEY!
7);
8 
9const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY });
10 
11interface Document {
12  id: number;
13  content: string;
14  embedding: number[];
15  metadata: Record<string, unknown>;
16  similarity?: number;
17}
18 
19// Create and store the embedding
20async function embedAndStore(content: string, metadata: Record<string, unknown>) {
21  const embeddingResponse = await openai.embeddings.create({
22    model: 'text-embedding-3-small',
23    input: content,
24  });
25 
26  const embedding = embeddingResponse.data[0].embedding;
27 
28  const { data, error } = await supabase
29    .from('documents')
30    .insert({
31      content,
32      embedding,
33      metadata,
34    })
35    .select()
36    .single();
37 
38  if (error) throw new Error(`Save error: ${error.message}`);
39  return data as Document;
40}
41 
42// Semantic search
43async function semanticSearch(query: string, limit = 10): Promise<Document[]> {
44  const embeddingResponse = await openai.embeddings.create({
45    model: 'text-embedding-3-small',
46    input: query,
47  });
48 
49  const queryEmbedding = embeddingResponse.data[0].embedding;
50 
51  const { data, error } = await supabase.rpc('match_documents', {
52    query_embedding: queryEmbedding,
53    match_threshold: 0.7,
54    match_count: limit,
55  });
56 
57  if (error) throw new Error(`Search error: ${error.message}`);
58  return data as Document[];
59}

1CREATE OR REPLACE FUNCTION match_documents(
2  query_embedding VECTOR(1536),
3  match_threshold FLOAT DEFAULT 0.7,
4  match_count INT DEFAULT 10
5)
6RETURNS TABLE (
7  id BIGINT,
8  content TEXT,
9  metadata JSONB,
10  similarity FLOAT
11)
12LANGUAGE plpgsql
13AS $$
14BEGIN
15  RETURN QUERY
16  SELECT
17    d.id,
18    d.content,
19    d.metadata,
20    1 - (d.embedding <=> query_embedding) AS similarity
21  FROM documents d
22  WHERE 1 - (d.embedding <=> query_embedding) > match_threshold
23  ORDER BY d.embedding <=> query_embedding
24  LIMIT match_count;
25END;
26$$;

1-- BM25 extension
2CREATE EXTENSION IF NOT EXISTS pg_bm25;
3 
4-- Full-text search index
5CREATE INDEX idx_documents_fts ON documents
6  USING bm25 (content)
7  WITH (text_fields = '{"content": {}}');
8 
9-- Hybrid search: combining BM25 + Vector
10CREATE OR REPLACE FUNCTION hybrid_search(
11  query_text TEXT,
12  query_embedding VECTOR(1536),
13  bm25_weight FLOAT DEFAULT 0.3,
14  vector_weight FLOAT DEFAULT 0.7,
15  match_count INT DEFAULT 10
16)
17RETURNS TABLE (
18  id BIGINT,
19  content TEXT,
20  metadata JSONB,
21  bm25_score FLOAT,
22  vector_score FLOAT,
23  combined_score FLOAT
24)
25LANGUAGE plpgsql
26AS $$
27BEGIN
28  RETURN QUERY
29  WITH bm25_results AS (
30    SELECT d.id, d.content, d.metadata,
31           paradedb.score(d.id) AS score
32    FROM documents d
33    WHERE d.content @@@ query_text
34    ORDER BY score DESC
35    LIMIT match_count * 3
36  ),
37  vector_results AS (
38    SELECT d.id, d.content, d.metadata,
39           1 - (d.embedding <=> query_embedding) AS score
40    FROM documents d
41    ORDER BY d.embedding <=> query_embedding
42    LIMIT match_count * 3
43  ),
44  combined AS (
45    SELECT
46      COALESCE(b.id, v.id) AS id,
47      COALESCE(b.content, v.content) AS content,
48      COALESCE(b.metadata, v.metadata) AS metadata,
49      COALESCE(b.score, 0) AS bm25_score,
50      COALESCE(v.score, 0) AS vector_score,
51      (COALESCE(b.score, 0) * bm25_weight +
52       COALESCE(v.score, 0) * vector_weight) AS combined_score
53    FROM bm25_results b
54    FULL OUTER JOIN vector_results v ON b.id = v.id
55  )
56  SELECT c.id, c.content, c.metadata,
57         c.bm25_score, c.vector_score, c.combined_score
58  FROM combined c
59  ORDER BY c.combined_score DESC
60  LIMIT match_count;
61END;
62$$;

1// Namespace-based vector grouping
2interface VectorBucket {
3  namespace: string;  // 'blog', 'docs', 'support'
4  partition: string;  // 'tr', 'en', 'de'
5}
6 
7async function searchInBucket(
8  query: string,
9  bucket: VectorBucket,
10  limit = 10
11) {
12  const embedding = await generateEmbedding(query);
13 
14  const { data } = await supabase.rpc('match_documents_in_bucket', {
15    query_embedding: embedding,
16    target_namespace: bucket.namespace,
17    target_partition: bucket.partition,
18    match_count: limit,
19  });
20 
21  return data;
22}
23 
24// Usage
25const blogResults = await searchInBucket(
26  'How do React Server Components work?',
27  { namespace: 'blog', partition: 'tr' }
28);

1// supabase/functions/rag-chat/index.ts
2import { serve } from 'https://deno.land/[email protected]/http/server.ts';
3import { createClient } from 'https://esm.sh/@supabase/supabase-js@2';
4import OpenAI from 'https://esm.sh/openai@4';
5 
6const supabase = createClient(
7  Deno.env.get('SUPABASE_URL')!,
8  Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
9);
10 
11const openai = new OpenAI({
12  apiKey: Deno.env.get('OPENAI_API_KEY')!,
13});
14 
15serve(async (req: Request) => {
16  const { query, history = [] } = await req.json();
17 
18  // 1. Create the query embedding
19  const embeddingRes = await openai.embeddings.create({
20    model: 'text-embedding-3-small',
21    input: query,
22  });
23 
24  // 2. Find relevant documents with hybrid search
25  const { data: documents } = await supabase.rpc('hybrid_search', {
26    query_text: query,
27    query_embedding: embeddingRes.data[0].embedding,
28    bm25_weight: 0.3,
29    vector_weight: 0.7,
30    match_count: 5,
31  });
32 
33  // 3. Build the context
34  const context = documents
35    ?.map((d: { content: string }) => d.content)
36    .join('\n\n---\n\n');
37 
38  // 4. Generate the answer with the LLM
39  const completion = await openai.chat.completions.create({
40    model: 'gpt-4o',
41    messages: [
42      {
43        role: 'system',
44        content: `You are a helpful assistant. Answer questions based on the context below.
45If the context doesn't contain the answer, say "I don't have information about this."
46 
47Context:
48${context}`,
49      },
50      ...history,
51      { role: 'user', content: query },
52    ],
53    temperature: 0.2,
54    max_tokens: 1000,
55  });
56 
57  return new Response(
58    JSON.stringify({
59      answer: completion.choices[0].message.content,
60      sources: documents?.map((d: { id: number; metadata: Record<string, unknown> }) => ({
61        id: d.id,
62        metadata: d.metadata,
63      })),
64    }),
65    { headers: { 'Content-Type': 'application/json' } }
66  );
67});

1// Supabase Vector Search — the most common usage
2const { data } = await supabase.rpc('match_documents', {
3  query_embedding: embedding,
4  match_threshold: 0.78,
5  match_count: 10
6});

1// Supabase provides automatic connection pooling (PgBouncer)
2// But in Edge Functions every request opens a new connection
3// Solution: define the Supabase client in global scope
4const supabase = createClient(url, key, {
5  db: { schema: 'public' },
6  auth: { persistSession: false },
7  global: {
8    headers: { 'x-connection-pool': 'true' },
9  },
10});

1// A cache table in Redis or Supabase
2const CACHE_TTL = 3600; // 1 hour
3 
4async function getCachedEmbedding(query: string): Promise<number[] | null> {
5  const cacheKey = createHash('sha256').update(query).digest('hex');
6 
7  const { data } = await supabase
8    .from('embedding_cache')
9    .select('embedding')
10    .eq('cache_key', cacheKey)
11    .gt('expires_at', new Date().toISOString())
12    .single();
13 
14  return data?.embedding ?? null;
15}

1-- Users should only see their own documents
2ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
3 
4CREATE POLICY "Users see own documents"
5  ON documents FOR SELECT
6  USING (auth.uid()::text = metadata->>'user_id');
7 
8CREATE POLICY "Users insert own documents"
9  ON documents FOR INSERT
10  WITH CHECK (auth.uid()::text = metadata->>'user_id');