How to Build a RAG Chatbot for Your Business with OpenAI and pgvector

Why RAG instead of a plain chatbot

A language model on its own only knows what it learned in training. It does not know your courses, policies or product documentation, and when it does not know, it may guess. RAG gives the model the relevant parts of your content at the moment it answers, so answers are grounded in your material and can link to the source.

It is also cheaper and easier to keep current than fine-tuning: when your content changes, you re-embed the changed pages instead of retraining anything.

The architecture

1. Ingest: collect content and split it into chunks of a few hundred words, keeping the title and URL with each chunk.
2. Embed: turn each chunk into a vector with an embedding model.
3. Store: save chunks and vectors in Postgres using the pgvector extension.
4. Retrieve: embed the user's question and find the most similar chunks the user is allowed to read.
5. Generate: send the question and the retrieved chunks to a chat model with instructions to answer only from them.
6. Cite and log: show sources, and log unanswered questions.

Step 1: Store embeddings in Postgres with pgvector

Supabase supports the pgvector extension. The vector size must match your embedding model. OpenAI's text-embedding-3-small returns 1,536 dimensions by default.

create extension if not exists vector;

create table public.doc_chunks (
  id bigint generated always as identity primary key,
  org_id uuid not null,
  source_url text not null,
  title text,
  content text not null,
  embedding vector(1536)
);

alter table public.doc_chunks enable row level security;

create index on public.doc_chunks
  using hnsw (embedding vector_cosine_ops);

The HNSW index keeps similarity search fast as the table grows. The org_id column and RLS keep each customer's knowledge base private.

Chunking: the step that decides answer quality

How you split content matters more than which model you pick. Chunks that are too large dilute the match with unrelated text; chunks that are too small lose the context needed to answer. A few rules that hold up in practice:

• Split on the content's own structure, such as headings, sections or lessons, before falling back to a fixed size.
• Keep a small overlap between neighbouring chunks so sentences that span a boundary are not lost.
• Store the title, URL and section heading with each chunk, and include them in the text you embed.
• Re-run your evaluation questions whenever you change the chunking, because quality can shift in either direction.

Step 2: Create embeddings

Embed each chunk with the OpenAI embeddings API and insert it. Run this on the server or in a background job, never in the browser.

import OpenAI from "openai";

const openai = new OpenAI();

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

Step 3: Retrieve the closest chunks

pgvector's <=> operator returns cosine distance, so smaller means more similar. A SQL function lets your app call the search through Supabase. Because it runs with the caller's permissions (the default, security invoker), RLS still filters the rows.

create or replace function public.match_chunks(
  query_embedding vector(1536),
  match_count int default 5
)
returns table (id bigint, title text, source_url text, content text, similarity float)
language sql stable
as $$
  select id, title, source_url, content,
         1 - (embedding <=> query_embedding) as similarity
  from public.doc_chunks
  order by embedding <=> query_embedding
  limit match_count;
$$;

Step 4: Generate a grounded answer

Send the question with the retrieved chunks, and tell the model to answer only from them and to say when the answer is not there. Ask it to reference which sources it used, then render those as links.

• Keep the system prompt short and specific: role, rules, and the format of the answer.
• Put each chunk in the prompt with its title and URL so the model can cite it.
• Stream the response to the interface so users see the answer as it is written.
• Use OpenAI or Claude for the answer. The retrieval layer does not care which model generates the text.

Making it production-ready

• Permissions: retrieval must respect who the user is. RLS on the chunks table is the simplest reliable way. See Supabase RLS mistakes.
• Freshness: re-embed content when it changes, with a background job.
• Cost: cache embeddings, limit chunks per prompt, and set usage limits per plan.
• Evaluation: keep a list of real questions with expected answers and check them after every change.
• Fallbacks: when similarity is low, say so and offer a human contact instead of guessing.

RAG in a real product

I built course Q&A with RAG inside LeadsMind, so learners get answers grounded in the course material they are enrolled in. If you want a chatbot that answers from your own content, see my AI chatbot development service.

Sources

• Supabase: pgvector extension
• pgvector on GitHub
• OpenAI: Embeddings guide