Supabase & pgvector for Enterprise AI: Architecture & Integration

-- Step 1: SQL Schema for pgvector table with HNSW index and RLS enabled
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS enterprise_documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  embedding VECTOR(1536),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create HNSW vector index for high-speed similarity search
CREATE INDEX IF NOT EXISTS enterprise_docs_embedding_hnsw_idx 
ON enterprise_documents USING hnsw (embedding vector_cosine_ops);

-- Enable Row-Level Security
ALTER TABLE enterprise_documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can only search their own documents" 
ON enterprise_documents FOR SELECT 
USING (auth.uid() = user_id);

-- Step 2: PostgreSQL RPC vector match function
CREATE OR REPLACE FUNCTION match_enterprise_documents(
  query_embedding VECTOR(1536),
  match_threshold FLOAT,
  match_count INT
)
RETURNS TABLE (id UUID, content TEXT, similarity FLOAT)
LANGUAGE plpgsql STABLE AS $$
BEGIN
  RETURN QUERY
  SELECT
      d.id,
      d.content,
      1 - (d.embedding <=> query_embedding) AS similarity
  FROM enterprise_documents d
  WHERE 1 - (d.embedding <=> query_embedding) > match_threshold
  ORDER BY d.embedding <=> query_embedding
  LIMIT match_count;
END;
$$;
# Python Client calling Supabase pgvector RPC function
from supabase import create_client
import os

SUPABASE_URL = os.environ.get("SUPABASE_URL", "https://xyz.supabase.co")
SUPABASE_KEY = os.environ.get("SUPABASE_SERVICE_ROLE_KEY", "secret-key")

supabase = create_client(SUPABASE_URL, SUPABASE_KEY)

def execute_pgvector_rag_search(query_vector: list[float]):
  response = supabase.rpc("match_enterprise_documents", {
      "query_embedding": query_vector,
      "match_threshold": 0.78,
      "match_count": 5
  }).execute()
  return response.data

if __name__ == "__main__":
  dummy_vec = [0.014] * 1536
  results = execute_pgvector_rag_search(dummy_vec)
  print(f"Retrieved {len(results)} authorized pgvector document matches.")