How do you implement RAG with Supabase and pgvector?
Supabase with pgvector provides a complete RAG infrastructure using PostgreSQL. Setup: enable the vector extension (CREATE EXTENSION IF NOT EXISTS vector), create a documents table with an embedding column (embedding vector(1536) for OpenAI embeddings or vector(384) for smaller models), and create an index for fast similarity search. Schema: documents table with id, content (text chunk), metadata (JSONB for source, page, section), embedding (vector), and createdat. Create an HNSW index: CREATE INDEX ON documents USING hnsw (embedding vectorcosineops). Ingestion pipeline: chunk documents (recursive text splitting, 512-1024 tokens per chunk with overlap), generate embeddings (OpenAI API or local model via Ollama), and insert into Supabase using the JavaScript client. Retrieval: create a PostgreSQL function for similarity search that accepts a query embedding and returns the most similar documents with their similarity scores. Use Supabase RPC to call this function from the client. Enhanced retrieval: combine vector search with full-text search using tsvector for hybrid search, implement metadata filtering (filter by source, date, category before vector search), and use RLS to enforce document access permissions during retrieval. Generation: pass retrieved chunks as context to an LLM (via edge function calling OpenAI/Anthropic API), include source attribution in the prompt, and return the generated answer with source references. Edge function implementation: create a Supabase Edge Function that accepts a user question, generates the query embedding, calls the similarity search function, assembles the prompt with retrieved context, calls the LLM API, and returns the formatted response. Performance: HNSW indexes provide sub-millisecond search on millions of vectors, implement connection pooling for concurrent requests, and cache frequent query embeddings for reduced embedding API costs.