Building a RAG Knowledge Base with Supabase pgvector and OpenAI Embeddings

By Elhassane Mehdioui, AI Consultant — June 15, 2026

Retrieval-Augmented Generation (RAG) has become the go-to architecture for grounding large language models in private, up-to-date knowledge. Instead of fine-tuning an expensive model, you store your documents as vector embeddings, retrieve the most relevant chunks at query time, and pass them as context to the LLM. The result is accurate, citable answers without hallucination.

In this tutorial you will build a complete RAG knowledge base using Supabase pgvector, OpenAI text-embedding-3-small, and a Node.js/TypeScript backend. By the end you will have a working semantic search pipeline, a context construction layer, and a clear cost optimisation strategy.

---

What Are Embeddings?

An embedding is a fixed-length numerical vector that represents the meaning of a piece of text. Two sentences that are semantically similar — even if they share no keywords — will have vectors that are close together in high-dimensional space.

OpenAI's text-embedding-3-small model converts any string into a 1 536-dimension vector. You can compare vectors using cosine similarity: a score of 1 means identical meaning, 0 means unrelated. This is the foundation of semantic search.

// Embedding a single sentence
import OpenAI from "openai";

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

async function embed(text: string): Promise<number[]> {
  const response = await openai.embeddings.create({
    model: "text-embedding-3-small",
    input: text,
  });
  return response.data[0].embedding;
}

The cost of text-embedding-3-small is $0.02 per 1 million tokens — roughly 750 000 words for two cents. This makes it the most cost-effective choice for a RAG knowledge base at almost any scale.

---

Setting Up Supabase pgvector

Supabase ships with the pgvector PostgreSQL extension pre-installed. Enable it and create your documents table in a single migration.

-- Enable the pgvector extension
create extension if not exists vector;

-- Documents table
create table documents (
  id          bigserial primary key,
  content     text        not null,
  metadata    jsonb       default '{}',
  embedding   vector(1536),
  created_at  timestamptz default now()
);

-- IVFFlat index for fast approximate nearest-neighbour search
create index on documents
  using ivfflat (embedding vector_cosine_ops)
  with (lists = 100);

The ivfflat index partitions the vector space into lists clusters. For a knowledge base under 500 000 rows, lists = 100 is a solid starting point. As your corpus grows, increase lists proportionally (roughly sqrt(row_count)).

Install the Supabase JavaScript client and the OpenAI SDK:

npm install @supabase/supabase-js openai

Initialise both clients:

import { createClient } from "@supabase/supabase-js";
import OpenAI from "openai";

export const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
);

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

---

Document Chunking Strategy

Before embedding, you must split your source documents into chunks. The chunk size is the single most important tuning parameter in a RAG system:

• Too large — the chunk carries irrelevant context, diluting the embedding signal and wasting LLM tokens.
• Too small — the chunk lacks enough context for the embedding to be meaningful.

A practical default is 512 tokens with a 64-token overlap. The overlap ensures that a sentence split across a boundary is still retrievable from either chunk.

function chunkText(text: string, maxTokens = 512, overlapTokens = 64): string[] {
  // Rough approximation: 1 token ≈ 4 characters
  const maxChars = maxTokens * 4;
  const overlapChars = overlapTokens * 4;
  const chunks: string[] = [];
  let start = 0;

  while (start < text.length) {
    const end = Math.min(start + maxChars, text.length);
    // Try to break on a sentence boundary
    const slice = text.slice(start, end);
    const lastPeriod = slice.lastIndexOf(". ");
    const breakPoint = lastPeriod > maxChars * 0.6 ? lastPeriod + 1 : slice.length;
    chunks.push(slice.slice(0, breakPoint).trim());
    start += breakPoint - overlapChars;
  }

  return chunks.filter(Boolean);
}

For production, replace the character heuristic with a proper tokeniser such as tiktoken to count tokens exactly.

---

Ingesting Documents into the Knowledge Base

With chunking in place, the ingestion pipeline is straightforward: chunk the document, embed each chunk in a batch call, then upsert into Supabase.

interface DocumentMetadata {
  source: string;
  title?: string;
  [key: string]: unknown;
}

async function ingestDocument(
  content: string,
  metadata: DocumentMetadata
): Promise<void> {
  const chunks = chunkText(content);

  // Batch embedding — one API call for all chunks
  const embeddingResponse = await openai.embeddings.create({
    model: "text-embedding-3-small",
    input: chunks,
  });

  const rows = chunks.map((chunk, i) => ({
    content: chunk,
    metadata,
    embedding: embeddingResponse.data[i].embedding,
  }));

  const { error } = await supabase.from("documents").insert(rows);
  if (error) throw new Error(`Supabase insert failed: ${error.message}`);

  console.log(`Ingested ${rows.length} chunks from "${metadata.source}"`);
}

Batching all chunks into a single openai.embeddings.create call is critical for performance and cost. The API accepts up to 2 048 inputs per request.

---

Semantic Search Query

At query time, embed the user's question and ask Postgres for the k nearest neighbours using cosine distance.

async function semanticSearch(
  query: string,
  topK = 5,
  threshold = 0.75
): Promise<Array<{ content: string; metadata: unknown; similarity: number }>> {
  const [queryEmbedding] = (
    await openai.embeddings.create({
      model: "text-embedding-3-small",
      input: query,
    })
  ).data;

  // pgvector cosine similarity: 1 - (embedding <=> query_vector)
  const { data, error } = await supabase.rpc("match_documents", {
    query_embedding: queryEmbedding.embedding,
    match_threshold: threshold,
    match_count: topK,
  });

  if (error) throw new Error(`Search failed: ${error.message}`);
  return data;
}

Create the Postgres function that powers the RPC call:

create or replace function match_documents(
  query_embedding  vector(1536),
  match_threshold  float,
  match_count      int
)
returns table (
  id         bigint,
  content    text,
  metadata   jsonb,
  similarity float
)
language sql stable
as $$
  select
    id,
    content,
    metadata,
    1 - (embedding <=> query_embedding) as similarity
  from documents
  where 1 - (embedding <=> query_embedding) > match_threshold
  order by embedding <=> query_embedding
  limit match_count;
$$;

The <=> operator is pgvector's cosine distance operator. Subtracting from 1 converts distance to similarity.

---

Context Construction and Passing to the LLM

Retrieved chunks must be assembled into a coherent context block before being sent to the LLM. Keep the context tight — every token costs money and consumes context window space.

async function answerQuestion(userQuestion: string): Promise<string> {
  const results = await semanticSearch(userQuestion, 5, 0.75);

  if (results.length === 0) {
    return "I could not find relevant information in the knowledge base.";
  }

  // Build the context block
  const context = results
    .map((r, i) => `[Source ${i + 1}: ${(r.metadata as any).source}]\n${r.content}`)
    .join("\n\n---\n\n");

  const systemPrompt = `You are a helpful assistant. Answer the user's question using ONLY the context provided below. If the answer is not in the context, say so clearly. Always cite the source number.

CONTEXT:
${context}`;

  const response = await openai.chat.completions.create({
    model: "gpt-4o-mini",
    messages: [
      { role: "system", content: systemPrompt },
      { role: "user", content: userQuestion },
    ],
    temperature: 0.2,
  });

  return response.choices[0].message.content ?? "";
}

Setting temperature: 0.2 keeps answers factual and deterministic. Using gpt-4o-mini instead of gpt-4o reduces cost by roughly 15x for most RAG workloads where the answer is already in the context.

---

Updating the Knowledge Base

Documents change. When a source is updated, delete the old chunks and re-ingest:

async function updateDocument(
  source: string,
  newContent: string,
  metadata: DocumentMetadata
): Promise<void> {
  // Delete all existing chunks for this source
  const { error: deleteError } = await supabase
    .from("documents")
    .delete()
    .eq("metadata->>source", source);

  if (deleteError) throw new Error(`Delete failed: ${deleteError.message}`);

  // Re-ingest with fresh embeddings
  await ingestDocument(newContent, { ...metadata, source });
}

For large corpora, consider a version field in metadata and a background job that compares document hashes before triggering re-embedding.

---

Using pgvector in a Next.js Project

If you are building a pgvector Next.js application, you can expose the RAG pipeline as an API route:

// app/api/chat/route.ts (Next.js App Router)
import { NextRequest, NextResponse } from "next/server";
import { answerQuestion } from "@/lib/rag";

export async function POST(req: NextRequest) {
  const { question } = await req.json();
  if (!question) return NextResponse.json({ error: "Missing question" }, { status: 400 });

  const answer = await answerQuestion(question);
  return NextResponse.json({ answer });
}

Keep SUPABASE_SERVICE_ROLE_KEY and OPENAI_API_KEY in .env.local and never expose them to the client.

---

Cost Optimisation

A typical knowledge base with 50 000 document chunks costs approximately $1 to embed in full using text-embedding-3-small. Query-time costs are negligible — one semantic search costs a fraction of a cent.

---

Summary

You now have a complete RAG Supabase pgvector pipeline:

1. Chunk source documents at 512 tokens with overlap.
2. Embed chunks in batches using text-embedding-3-small.
3. Store embeddings in a Supabase vector(1536) column with an IVFFlat index.
4. Search at query time with a single match_documents RPC call.
5. Construct a cited context block and pass it to gpt-4o-mini.
6. Update by deleting stale chunks and re-ingesting.

This architecture scales from a personal knowledge base to an enterprise documentation system without changing a line of code — only the number of rows in Postgres. The combination of Supabase's managed infrastructure, pgvector's native SQL integration, and OpenAI's cost-effective embedding models makes it the most pragmatic RAG stack available today.

---

Elhassane Mehdioui is an AI consultant specialising in RAG systems, LLM integration, and production AI pipelines. For inquiries, reach out via intelligentb2b.com.