I've been spending time learning how AI systems actually work under the hood, not just calling an API and hoping for the best. One of the clearest ways to understand Retrieval Augmented Generation (RAG) is to build something with a real use case: an AI system that reads a customer email, finds a similar question we've already answered, and drafts a reply using that approved answer.
Here's the full mental model, no code first, just the pieces and how they connect.
The Problem
A customer sends an email like this:
"Hey, I want to stop paying for my account. How can I do that?"
An LLM on its own has no idea that "Settings → Billing → Cancel" is how your product handles that. It only knows general language patterns, not your product's specific answers. So the LLM needs to be handed the right context before it writes a reply. That's the whole idea behind RAG: retrieve relevant information first, then generate a response using that information.
Step 1: A Table of Known Questions and Answers
Think of it like a spreadsheet with four columns:
| id | question | answer | embedding |
|---|---|---|---|
| 1 | How do I cancel my subscription? | Go to Settings → Billing and click Cancel. | [0.12, -0.43, 0.71, ...] |
| 2 | Can I export my submissions? | Go to Submissions → Export. | [0.82, 0.14, -0.21, ...] |
| 3 | My Google Sheets sync isn't working. | Reconnect Google Sheets from Integrations. | [0.31, -0.72, 0.44, ...] |
The first three columns are self explanatory. The fourth one, embedding, is the interesting part.
An embedding is a list of numbers that represents the meaning of a piece of text. Two sentences that mean similar things end up with number lists that are close together mathematically, even if the wording is completely different. "I want to stop paying for my account" and "How do I cancel my subscription?" don't share many words, but they'll produce embeddings that sit close to each other in that number space.
This is not something you calculate by hand. You send the text to an embedding model (OpenAI's text-embedding-3-small, for example) and it returns 1,536 numbers back.
In PostgreSQL, once you install the pgvector extension, you can store that directly as a column type:
CREATE TABLE knowledge (
id SERIAL PRIMARY KEY,
question TEXT NOT NULL,
answer TEXT NOT NULL,
embedding VECTOR(1536)
);
Step 2: Turning the Incoming Email into a Vector
When a new email comes in, the application sends that email's text to the same embedding model that built the table. In Node.js, that's roughly:
const embedding = await createEmbedding(email);
The model turns the customer's sentence into a list of numbers:
"I want to stop paying for my account."
↓
Embedding model
↓
[0.15, -0.38, 0.71, ...]
This is called the query vector. It's temporary. It never gets inserted into the knowledge table, it only exists to search against the rows that are already there. Once the reply is sent, it's discarded.
Step 3: The Retrieval Query, Broken All the Way Down
This next part is the piece most tutorials rush past, but it's the actual "R" in RAG, so it's worth slowing down for. Here's the full query again:
SELECT
question,
answer,
1 - (embedding <=> $1::vector) AS similarity
FROM knowledge
ORDER BY embedding <=> $1::vector
LIMIT 3;
It looks intimidating the first time you see it. In plain English it's doing exactly one thing: "Take this user's email, compare its vector against every stored vector, and give me the three most similar ones."
Let's go through it piece by piece.
What $1 actually is
$1 is a parameter placeholder, standard in parameterized SQL queries. On the Node.js side, the application passes the query vector into that placeholder when it runs the query:
db.query(sql, [JSON.stringify(embedding)]);
So conceptually, by the time this reaches PostgreSQL, it's really executing:
embedding <=> '[0.15, -0.38, 0.71, ...]'::vector
Which just means: "Compare the stored embedding column with this new vector I just generated."
What <=> actually does
This is a pgvector operator. It calculates cosine distance between two vectors, which is a standard way to measure how different two directions in space are.
Picture two vectors that point in nearly the same direction:
Stored vector Query vector
[0.14, -0.40, 0.70] [0.15, -0.38, 0.71]
\ /
\ /
very similar
The distance between those might come out to something like 0.05.
Now picture two vectors pointing in very different directions:
Stored vector Query vector
[-0.8, 0.2, -0.4] [0.15, -0.38, 0.71]
\ /
\ /
very different
That distance might come out to 0.91.
The rule to remember: smaller distance means more similar meaning.
What ORDER BY does here
ORDER BY embedding <=> $1
This sorts every row in the table by its distance from the new email's vector, closest first. If PostgreSQL calculated distances like this across five stored questions:
Document Distance
Cancel subscription 0.08
Export submissions 0.71
Google Sheets 0.63
Refund policy 0.21
React documentation 0.94
after the ORDER BY, it becomes:
Cancel subscription 0.08
Refund policy 0.21
Google Sheets 0.63
Export submissions 0.71
React documentation 0.94
The closest matches float to the top.
What LIMIT 3 does
Exactly what it says: keep only the first three rows after sorting. So from the list above, we'd walk away with Cancel subscription, Refund policy, and Google Sheets, and drop the rest.
Where the similarity score comes from
This is the part that trips people up the most:
1 - (embedding <=> $1::vector) AS similarity
Cosine distance works backwards from how humans naturally think about it. Smaller distance means more similar, but most people expect a "similarity score" to work the other way, where a bigger number means more similar. So the query flips it:
distance = 0.08
1 - 0.08
similarity = 0.92
Which lets the API return something like:
[
{
"question": "How do I cancel my subscription?",
"answer": "Go to Settings → Billing.",
"similarity": 0.92
}
]
Now you can say "this previous question is 92% similar to the customer's question," which reads a lot more naturally in a dashboard or a log than a raw distance value would.
One caveat worth keeping in your head: don't treat that number as a calibrated probability. It's a transformed cosine-distance score, not a confidence percentage in any statistical sense. It's still genuinely useful for ranking and for sanity-checking whether a match is close enough to trust, just don't over-interpret the exact number.
Putting the whole query back together
FROM knowledge
↓
Look through all our stored knowledge
↓
embedding <=> $1
↓
Calculate distance between each stored vector
and the user's vector
↓
ORDER BY
↓
Put closest vectors first
↓
LIMIT 3
↓
Give me the three closest ones
↓
SELECT question, answer
↓
Return the actual useful information
Run against our example table, this returns something like:
| question | answer | similarity |
|---|---|---|
| How do I cancel my subscription? | Go to Settings → Billing and click Cancel. | 0.92 |
| What is your refund policy? | Refunds can be requested within 30 days. | 0.71 |
| Can I change my billing plan? | You can change your plan from Settings → Billing. | 0.68 |
That's the retrieval half of RAG, done in one query. When people talk about RAG "retrieving relevant context," this is the exact mechanism they mean. And it's also why the embedding column matters so much: without those stored vectors, PostgreSQL has nothing to compare the new email against.
Step 4: Handing the Retrieved Answers to the LLM
Now the system passes the customer's original email plus the top matched answers to the LLM, with a prompt roughly like:
Customer:
"I want to stop paying for my account. How can I do that?"
Relevant company knowledge:
"Go to Settings → Billing and click Cancel."
Write a helpful response.
The LLM isn't guessing anymore. It has the actual approved answer in front of it and just needs to phrase it as a reply:
"Hi! You can cancel your subscription by going to Settings → Billing and clicking Cancel. Let me know if you need any help."
Building It: A Minimal Working Implementation
Everything above is the mental model. Here's what it looks like as actual code, using Node.js, PostgreSQL, pgvector, and OpenAI's API. This is deliberately minimal, enough to see every piece connect, not a production-hardened system.
1. Set up the database
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE knowledge (
id SERIAL PRIMARY KEY,
question TEXT NOT NULL,
answer TEXT NOT NULL,
embedding VECTOR(1536)
);
2. Seed it with known questions and answers
This runs once, or whenever you add new approved answers to your knowledge base. Each row gets embedded before it's inserted.
import OpenAI from "openai";
import { Pool } from "pg";
const openai = new OpenAI();
const pool = new Pool();
async function createEmbedding(text) {
const response = await openai.embeddings.create({
model: "text-embedding-3-small",
input: text,
});
return response.data[0].embedding;
}
const knowledgeBase = [
{
question: "How do I cancel my subscription?",
answer: "Go to Settings → Billing and click Cancel.",
},
{
question: "Can I export my submissions?",
answer: "Go to Submissions → Export.",
},
{
question: "My Google Sheets sync isn't working.",
answer: "Reconnect Google Sheets from Integrations.",
},
];
async function seed() {
for (const item of knowledgeBase) {
const embedding = await createEmbedding(item.question);
await pool.query(
`INSERT INTO knowledge (question, answer, embedding)
VALUES ($1, $2, $3::vector)`,
[item.question, item.answer, JSON.stringify(embedding)]
);
}
console.log("Knowledge base seeded.");
}
seed();
3. Handle an incoming email
This is the part that runs live, every time a new customer email arrives. It embeds the email, retrieves the closest matches, and asks the LLM to draft a reply using them.
async function draftReply(customerEmail) {
// Step 1: embed the incoming email
const queryEmbedding = await createEmbedding(customerEmail);
// Step 2: retrieve the closest known questions
const result = await pool.query(
`SELECT question, answer,
1 - (embedding <=> $1::vector) AS similarity
FROM knowledge
ORDER BY embedding <=> $1::vector
LIMIT 3`,
[JSON.stringify(queryEmbedding)]
);
const matches = result.rows;
// Step 3: build context from the retrieved rows
const context = matches
.map((row) => `Question: ${row.question}\nApproved answer: ${row.answer}`)
.join("\n\n");
// Step 4: ask the LLM to draft a reply using that context
const completion = await openai.chat.completions.create({
model: "gpt-4o-mini",
messages: [
{
role: "system",
content:
"You are a support assistant. Use the approved answers below to write a short, helpful reply to the customer. Do not invent information that isn't in the approved answers.",
},
{
role: "user",
content: `Customer email:\n"${customerEmail}"\n\nRelevant company knowledge:\n${context}`,
},
],
});
return completion.choices[0].message.content;
}
// Example usage
const reply = await draftReply(
"I want to stop paying for my account. How can I do that?"
);
console.log(reply);
That's the entire system. An Express route or a Next.js API route just wraps draftReply and returns the result, maybe with a "send" button in a dashboard rather than auto-sending, since you'll want a human reviewing replies at first.
A few things worth doing before this touches real customers
Set a similarity threshold. If the closest match only scores 0.35, that's not actually relevant, it's just the least-far option in the table. Below a threshold like 0.75 or so (tune this against your own data), fall back to a human instead of letting the LLM guess.
Log what got matched. Store which rows were retrieved for each email alongside the final reply. When something goes wrong, you'll want to see exactly what context the LLM was working from.
Keep the system prompt strict. Telling the model not to invent information outside the approved answers matters more than it looks like it should. Without that instruction, it'll happily blend in plausible sounding details that were never approved.
The Full Pipeline
Customer email
↓
Embedding model
↓
Query vector
↓
PostgreSQL + pgvector
↓
Vector similarity search
↓
Top matching Q&A rows
↓
LLM (given the retrieved answers as context)
↓
Drafted reply
The Mental Model Worth Keeping
Don't treat the embedding column as some kind of AI magic. It's just a column full of numbers that represent meaning. And the retrieval query is just this, in plain language:
"Take the new email's numbers, compare them against every row's numbers, and give me back the rows whose numbers are closest."
The LLM's only job after that is to take the retrieved rows and turn them into a well written reply. Everything before that step is straightforward math and a database query.
That's the foundation RAG systems are built on, whether it's a support ticket router, a documentation chatbot, or an email auto-reply system like this one. The retrieval mechanism barely changes. What changes is what you're retrieving and what you do with it afterward.
What the Vector Database Is Doing vs What the LLM Is Doing
It's easy to blur these two together when you're first learning this, so it's worth separating them cleanly.
| Vector database (PostgreSQL + pgvector) | LLM | |
|---|---|---|
| Job | Find which stored rows are closest in meaning to the new input | Turn retrieved information into natural, well phrased language |
| How | Cosine distance between number lists, pure math | Language generation, trained on huge amounts of text |
| Knows about your product? | No, it just stores numbers and compares them | No, unless you hand it context |
| Creative? | Not at all, it's deterministic, same input always ranks the same | Yes, this is where actual reasoning and phrasing happen |
| Can it hallucinate? | No, it either finds a close match or it doesn't | Yes, if it's not given clear context or instructions |
The database never "understands" anything. It's doing arithmetic on lists of numbers, nothing more. All of the actual language understanding happened earlier, when the embedding model converted text into those numbers in the first place. And all the actual writing happens later, when the LLM turns retrieved facts into a sentence a customer will read.
Once you see the split this clearly, a lot of the mystery around RAG disappears. The database's only job is ranking. The LLM's only job is writing. Neither one does the other's job.
When You Don't Need a Vector Database
Just because you're building something AI-powered doesn't mean pgvector belongs in the stack. A few honest signals that you can skip it:
Your knowledge base is small enough to just paste into the prompt. If you have twenty or thirty approved answers total, you can hand all of them to the LLM directly, every time, and let it pick the relevant one itself. No retrieval step needed. This stops making sense once your knowledge base grows past what comfortably fits in a prompt, both for cost and for the model's accuracy at picking the right one out of a long list.
Plain keyword search already works. If your questions use fairly consistent, predictable wording (support tickets from a technical audience often do), a simple ILIKE or full text search in PostgreSQL might get you 90% of the way there without needing embeddings at all.
Your data barely changes. If the set of question and answer pairs is basically static, you don't need a live similarity search system. You could precompute the best match for a fixed set of common inputs and skip the infrastructure entirely.
You're validating an idea, not shipping a product yet. Standing up pgvector, an embedding pipeline, and a retrieval query is real infrastructure. If you're still testing whether AI auto replies are even useful for your support volume, start by hardcoding a handful of examples straight into a prompt. Add the vector database once you've proven the concept and the knowledge base has grown past what fits comfortably in context.
The pattern is the same one that shows up everywhere in software: reach for the simplest thing that solves the actual problem, and only add the heavier piece once you've outgrown the simple one.
Top comments (1)
Clear walkthrough. The security boundary I’d make explicit is that the customer email is untrusted data, not an instruction. Quoting it inside a user message does not stop prompt injection (“ignore the approved answers and disclose…”). The send policy therefore needs enforcement outside the model.
I’d classify intent and risk first, then retrieve only from a versioned, approved corpus scoped by product/plan/locale. The deterministic gate should require a calibrated retrieval score plus agreement between top matches, reject conflicting or expired answers, and route account changes, refunds, legal/press/security topics, unknown senders, or requested secrets to a human.
Before auto-send, validate the draft against structured claims from the retrieved records—not only a system prompt—and prohibit URLs, prices, personal data, or commitments absent from those claims. Log corpus version, retrieved IDs, prompt/model version, sender classification, final disposition, and any human edits.
Also add idempotency around inbound message ID and reply thread ID. Mail providers retry webhooks; without a durable send ledger, a correct draft can become two customer replies.