rag-agent-pgvector/
│
├── data/
│ └── docs.json # seed KB
│
├── src/
│ ├── db.js # Postgres connection + table setup
│ ├── embed.js # create + store embeddings in pgvector
│ ├── retrieve.js # query pgvector for top-k docs
│ ├── agent.js # reasoning + reflection loop
│ └── utils.js # small helpers
│
├── .env # API + DB creds
├── package.json
└── index.js # entry point
OPENAI_API_KEY=sk-your-key
DATABASE_URL=postgresql://postgres:password@localhost:5432/ragdb
You need Postgres 15+ with pgvector installed:
psql -d ragdb -c "CREATE EXTENSION IF NOT EXISTS vector;"{
"name": "rag-agent-pgvector",
"version": "1.0.0",
"type": "module",
"scripts": {
"start": "node index.js",
"embed": "node src/embed.js"
},
"dependencies": {
"dotenv": "^16.4.0",
"openai": "^4.0.0",
"pg": "^8.11.0"
}
}Install:
npm installSame toy corpus:
[
{
"id": "D1",
"title": "Billing Policy",
"text": "You can pause your subscription once per year for up to 4 weeks. Pauses longer than 4 weeks require program lead approval."
},
{
"id": "D2",
"title": "Refunds",
"text": "Refunds are available within 7 days of first payment only."
},
{
"id": "D3",
"title": "Cohort Rules",
"text": "Attendance below 60% requires remedial tasks; pauses don’t reset attendance."
},
{
"id": "D4",
"title": "Contact",
"text": "Email support@neog.camp for approval cases; include start and end dates."
}
]// src/db.js
import pg from "pg";
import dotenv from "dotenv";
dotenv.config();
export const pool = new pg.Pool({
connectionString: process.env.DATABASE_URL,
});
export async function setupDB() {
await pool.query(`
CREATE TABLE IF NOT EXISTS documents (
id TEXT PRIMARY KEY,
title TEXT,
content TEXT,
embedding vector(1536)
);
`);
console.log("✅ documents table ready");
}// src/embed.js
import fs from "fs";
import path from "path";
import OpenAI from "openai";
import { pool, setupDB } from "./db.js";
import dotenv from "dotenv";
dotenv.config();
const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY });
const docs = JSON.parse(fs.readFileSync(path.resolve("data/docs.json"), "utf-8"));
async function createEmbeddings() {
await setupDB();
console.log("🧠 Creating embeddings and inserting into Postgres...");
for (const doc of docs) {
const input = `${doc.title}\n${doc.text}`;
const emb = await openai.embeddings.create({
model: "text-embedding-3-small",
input,
});
const vec = emb.data[0].embedding;
await pool.query(
`INSERT INTO documents (id, title, content, embedding)
VALUES ($1,$2,$3,$4)
ON CONFLICT (id) DO UPDATE SET
title = EXCLUDED.title,
content = EXCLUDED.content,
embedding = EXCLUDED.embedding;`,
[doc.id, doc.title, doc.text, vec]
);
console.log(`→ stored ${doc.id}`);
}
console.log("✅ All documents embedded.");
await pool.end();
}
createEmbeddings();Run once:
npm run embed// src/retrieve.js
import OpenAI from "openai";
import { pool } from "./db.js";
import dotenv from "dotenv";
dotenv.config();
const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY });
export async function retrieveRelevantDocs(query, k = 2) {
// 1. get embedding for query
const emb = await openai.embeddings.create({
model: "text-embedding-3-small",
input: query,
});
const qVec = emb.data[0].embedding;
// 2. similarity search using pgvector's <-> operator (L2 distance)
const res = await pool.query(
`SELECT id, title, content, 1 - (embedding <=> $1) AS similarity
FROM documents
ORDER BY embedding <-> $1
LIMIT $2;`,
[qVec, k]
);
return res.rows;
}Note: <-> is the pgvector distance operator; smaller = closer.
1 - distance gives a pseudo-similarity score.
// src/agent.js
import OpenAI from "openai";
import { retrieveRelevantDocs } from "./retrieve.js";
import dotenv from "dotenv";
dotenv.config();
const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY });
export async function agenticAnswer(question) {
console.log("🤔 Question:", question);
// Step 1 — retrieve
const docs = await retrieveRelevantDocs(question, 2);
console.log("📚 Retrieved:", docs.map(d => d.title).join(", "));
const context = docs.map(d => `${d.title}: ${d.content}`).join("\n\n");
// Step 2 — generate grounded answer
const completion = await openai.chat.completions.create({
model: "gpt-4o-mini",
messages: [
{
role: "system",
content:
"Answer truthfully using only the context. Cite the doc titles you used."
},
{
role: "user",
content: `Context:\n${context}\n\nQuestion: ${question}\nAnswer:`
}
]
});
const answer = completion.choices[0].message.content;
return { answer, sources: docs.map(d => d.title) };
}// index.js
import { agenticAnswer } from "./src/agent.js";
import { pool } from "./src/db.js";
const question = "Can I pause my neoG Bootcamp subscription for 2 months?";
const { answer, sources } = await agenticAnswer(question);
console.log("\n💬 Final Answer:\n", answer);
console.log("\n📎 Sources:", sources);
await pool.end();npm run embed
npm startOutput:
🤔 Question: Can I pause my neoG Bootcamp subscription for 2 months?
📚 Retrieved: Billing Policy, Contact
💬 Final Answer:
You can pause for up to 4 weeks per year. Two months exceeds this limit, so you
must get program-lead approval. Email support@neog.camp with your dates.
(Sources: Billing Policy, Contact)
| Aspect | JSON + cosine | pgvector |
|---|---|---|
| Scale | few hundred docs | millions easily |
| Speed | linear scan | index-accelerated (ivfflat, hnsw) |
| Query | manual cosine math | SQL (ORDER BY embedding <-> $1) |
| Maintenance | static file | updatable, versionable |
| Integration | none | full SQL joins, filters, metadata |