Chapter 20: Building a RAG System From Scratch With Next.js and Supabase

What RAG Solves (And What It Does Not)

Every AI model has a knowledge cutoff. It knows nothing about your specific documents, your company's policies, your product's documentation, or any data created after its training date.

Retrieval-Augmented Generation (RAG) solves this by giving the model access to a dynamic knowledge base at query time. Instead of storing information in the model's weights (which is expensive, requires fine-tuning, and goes stale), RAG stores information in a searchable vector database and retrieves relevant chunks at query time.

What RAG is good for:

• Q&A over custom documents (PDFs, docs, wikis)
• Customer support bots that know your product
• Codebase Q&A tools
• Knowledge bases that update frequently

• Tasks that require reasoning across the entire document corpus simultaneously (retrieval inherently samples)
• Tasks where the answer requires synthesizing hundreds of documents (use long-context models instead)

The RAG Architecture

INDEXING PHASE (done once, or on document update):
Document → Text Extraction → Chunking → Embedding Model → Vector Store

QUERY PHASE (done on every user question):
User Question → Embedding Model → Vector Similarity Search → Relevant Chunks → LLM + Context → Answer

---

Step 1: Set Up pgvector in Supabase

Supabase supports pgvector, a PostgreSQL extension for vector similarity search. Enable it in the Supabase SQL Editor:

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

-- Create a table for document chunks
CREATE TABLE document_chunks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id UUID NOT NULL,
  content TEXT NOT NULL,           -- The raw text chunk
  embedding VECTOR(1536),          -- 1536 dimensions for OpenAI text-embedding-3-small
  metadata JSONB,                  -- source file, page number, section, etc.
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create an index for fast similarity search
CREATE INDEX ON document_chunks USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100); -- Adjust lists based on dataset size

---

Step 2: Document Ingestion Pipeline

```javascript // lib/rag/ingest.js import OpenAI from 'openai'; import { createClient } from '@supabase/supabase-js';

const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY }); const supabase = createClient(process.env.NEXT_PUBLIC_SUPABASE_URL, process.env.SUPABASE_SERVICE_ROLE_KEY);

// Split text into overlapping chunks function chunkText(text, chunkSize = 500, overlap = 50) { const chunks = []; const words = text.split(' '); for (let i = 0; i < words.length; i += chunkSize - overlap) { const chunk = words.slice(i, i + chunkSize).join(' '); if (chunk.trim()) chunks.push(chunk); } return chunks; }

// Generate embeddings for a batch of text chunks async function embedChunks(chunks) { const response = await openai.embeddings.create({ model: 'text-embedding-3-small', // 1536 dimensions, very cheap input: chunks, }); return response.data.map(d => d.embedding); }

// Full ingestion pipeline

export async function ingestDocument(documentId, text, metadata = {}) { const chunks = chunkText(text); console.log(Ingesting ${chunks.length} chunks...);

// Process in batches of 20 to stay within rate limits const batchSize = 20; for (let i = 0; i < chunks.length; i += batchSize) { const batch = chunks.slice(i, i + batchSize); const embeddings = await embedChunks(batch);

const rows = batch.map((chunk, j) => ({ document_id: documentId, content: chunk, embedding: embeddings[j], metadata: { ...metadata, chunk_index: i + j }, }));

await supabase.from('document_chunks').insert(rows); console.log(Inserted batch ${Math.floor(i/batchSize) + 1}/${Math.ceil(chunks.length/batchSize)}); } }

---

## Step 3: The Query Pipeline

const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY }); const supabase = createClient(/ ... /);

export async function queryRAG(question, options = {}) { const { limit = 5, threshold = 0.7 } = options;

// Step 1: Embed the user's question const embeddingResponse = await openai.embeddings.create({ model: 'text-embedding-3-small', input: question, }); const questionEmbedding = embeddingResponse.data[0].embedding;

// Step 2: Find similar chunks using pgvector cosine similarity const { data: chunks } = await supabase.rpc('match_chunks', { query_embedding: questionEmbedding, match_threshold: threshold, match_count: limit, });

if (!chunks || chunks.length === 0) { return { answer: "I don't have information about that in my knowledge base.", sources: [] }; }

// Step 3: Build the context from retrieved chunks const context = chunks.map((chunk, i) => [Source ${i+1}]: ${chunk.content}).join('

');

// Step 4: Call the LLM with the retrieved context const response = await openai.chat.completions.create({ model: 'gpt-4o', messages: [ { role: 'system', content: You are a helpful assistant. Answer the user's question based only on the provided context. If the context does not contain the answer, say so clearly. Do not make up information.

Context: ${context} }, { role: 'user', content: question } ], });

return { answer: response.choices[0].message.content, sources: chunks.map(c => ({ content: c.content, metadata: c.metadata, similarity: c.similarity })), }; }

---

## Step 4: The API Route

export async function POST(request) { const { question } = await request.json(); if (!question?.trim()) { return Response.json({ error: 'Question is required' }, { status: 400 }); }

const result = await queryRAG(question, { limit: 5, threshold: 0.7 }); return Response.json(result); } ```

---

Cost Estimation

For a typical knowledge base with 1000 documents (~500 pages total):

• Ingestion: ~1M tokens at $0.02/1M = $0.02 one-time
• Per query: 1 embedding ($0.00002) + ~2000 tokens to GPT-4o (~$0.012) = ~$0.012 per query
• 1000 queries/month = ~$12/month