How to Build an AI Email Auto-Reply System with RAG and PostgreSQL

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:

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:

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.

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.