A RAG-Powered Product Q&A System -- a Clean Prisma + PostgreSQL Setup

Imagine you have a massive product catalog. You want users to ask natural language questions like "Which electronics are under $50 and have good reviews?" and get accurate, context-rich answers.

I started with a visual drag-and-drop tool (n8n) to test the water. It worked but I felt a disconnect. So I wanted to get a feeling on what how it would look like in Prisma and PostgreSQL.

The Database Setup: pgVector + Prisma

First, ensure your PostgreSQL has the vector extension enabled. This allows us to store embeddings and run lightning-fast similarity searches.

CREATE EXTENSION IF NOT EXISTS vector;

And our table looks like this:

CREATE TABLE IF NOT EXISTS products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL CHECK (LENGTH(name) >= 1),
    description VARCHAR(2000) NOT NULL CHECK (LENGTH(description) BETWEEN 100 AND 2000),
    sku VARCHAR(255) NOT NULL CHECK (LENGTH(sku) >= 1),
    price DOUBLE PRECISION NOT NULL,
    price_unit VARCHAR(50) NOT NULL,
    category VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

As for having a knowledge base for products we will go with a separate table as I explained here: https://dev.to/kasir-barati/rags-and-embedded-models-simplified-29ob

CREATE TABLE IF NOT EXISTS products_knowledge_base (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    content TEXT,
    metadata JSONB,
    embedding VECTOR(1536)
);
CREATE INDEX IF NOT EXISTS idx_products_kb_embedding_hnsw 
ON products_knowledge_base 
USING hnsw (embedding vector_cosine_ops);

Keep in mind that metadata is a JSONB field and NOT indexed. So this might bite you in production. You can index it although it is still risky since it is a JSON field.

CREATE INDEX idx_kb_metadata_gin 
ON products_knowledge_base 
USING GIN (metadata jsonb_path_ops);

It is crucial to understand one thing: Postgres won't combine these two indexes into one scan for match_documents query. So imagining we have the index on both fields the planner will pick one of these two paths:

• Index Scan using the HNSW index, walking the graph in similarity order, with metadata @> filter applied as a row-by-row Filter on whatever candidates come out. The GIN index sits unused.
• Bitmap Index Scan using the GIN index to find filter-matching rows, then a Sort on distance, then Limit. The HNSW index sits unused.

It's one or the other per query, decided by cost estimates. Never both at once. But I believe it should NOT be a problem to not index the metadata field because we already should be getting a few records. But I am not sure. Feel free to drop a comment about this if you know something about it.

Important part is a pure SQL function that does all the heavy lifting. It takes a query embedding, an optional metadata filter, and returns the top-k most similar documents along with a similarity score.

CREATE OR REPLACE FUNCTION match_documents (
    query_embedding VECTOR(1536), 
    match_count INT DEFAULT 5,
    filter JSONB DEFAULT '{}' 
)
RETURNS TABLE (
    id UUID,
    content TEXT,
    metadata JSONB,
    similarity FLOAT
)
LANGUAGE SQL STABLE
AS $$
    SELECT
        id,
        content,
        metadata,
        1 - (embedding <=> query_embedding) AS similarity
    FROM products_knowledge_base
    WHERE metadata @> filter
    ORDER BY embedding <=> query_embedding
    LIMIT match_count;
$$;

• embedding <=> query_embedding calculates the cosine distance (lower = more similar).
• 1 - distance converts it to a similarity score (0 to 1, higher = better).
• metadata @> filter applies a JSONB filter (e.g., {"category": "Electronics"}).
• The HNSW index ensures sub-100ms queries even with 100,000+ rows.

Prisma

Prisma doesn't natively support the vector type, but we can map it as Unsupported and use raw SQL to call our function.

So our schema would look like this roughly:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Product {
  id          String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  name        String   @db.VarChar(255)
  description String   @db.VarChar(2000)
  sku         String   @db.VarChar(255)
  price       Float
  price_unit  String   @db.VarChar(50)
  category    String   @db.VarChar(255)
  created_at  DateTime @default(now()) @db.Timestamptz(6)
  updated_at  DateTime @default(now()) @db.Timestamptz(6)

  @@map("products")
}

model ProductKnowledgeBase {
  id        String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  content   String?  @db.Text
  metadata  Json?
  embedding Unsupported("vector(1536)")? 

  @@map("products_knowledge_base")
}

Service Layer

Here is the complete service file. It:

1. Generates an OpenAI embedding for the user's question.
2. Calls the match_documents function via Prisma raw SQL.
3. Enriches the results with structured data from the products table.
4. Builds a rich context prompt.
5. Returns a final answer from GPT-4o-mini or any other model.

import { PrismaClient } from '@prisma/client';
import OpenAI from 'openai';

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

type MatchedDocument = {
  id: string;
  content: string;
  metadata: any;
  similarity: number;
};

export async function askProductQuestion(
  userQuestion: string,
  topK: number = 5,
  categoryFilter?: string
): Promise<string> {
  // 1. Generate the embedding
  const embeddingResponse = await openai.embeddings.create({
    model: 'text-embedding-3-small',
    input: userQuestion,
  });
  const queryEmbedding = embeddingResponse.data[0].embedding;
  const vectorString = `[${queryEmbedding.join(',')}]`;

  // 2. Build metadata filter
  const filter: Record<string, any> = {};
  if (categoryFilter) {
    filter.category = categoryFilter;
  };

  // 3. Call the match_documents function
  const matchedDocs = await prisma.$queryRaw<MatchedDocument[]>`
    SELECT * FROM match_documents(
      ${vectorString}::vector(1536),
      ${topK}::int,
      ${filter}::jsonb
    );
  `;

  if (matchedDocs.length === 0) {
    return "I couldn't find any relevant products matching your question.";
  }

  // 4. Build context directly from the content
  const contextSections = matchedDocs
    .map((doc, index) => 
      `--- Document ${index + 1} (Relevance: ${(doc.similarity * 100).toFixed(1)}%) ---\n${doc.content}`
    )
    .join('\n\n');

  const systemPrompt = `You are a helpful assistant for a shopping application.
Use the following product information to answer the user's question. 
If the information is insufficient, politely say so.

Context from our knowledge base:
${contextSections}`;

  // 5. Get the final answer
  const completion = await openai.chat.completions.create({
    model: 'gpt-4o-mini',
    messages: [
      { role: 'system', content: systemPrompt },
      { role: 'user', content: userQuestion },
    ],
    temperature: 0.3,
  });

  return completion.choices[0].message.content || 'No response generated.';
}

Important

• We are using OpenAI Text Embedding Small and that is why we are using 1536 dimensions.
• We have to maintain and keep the separate table's content up to date with the main products table. Otherwise LLM will reply wrong answers.

Exposing It

I am not gonna use NestJS here since I am more focusing on the main RAG and how can we have it in our NodeJS apps:

import express from 'express';
import { askProductQuestion } from './product-qa.services';

const router = express.Router();

router.post('/api/products/ask', async (req, res) => {
  try {
    const { question, category, topK } = req.body;

    if (!question) {
      return res.status(400).json({ error: 'Question required.' });
    }

    const answer = await askProductQuestion(question, topK || 5, category);

    res.json({ answer });
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Internal server error.' });
  }
});

export default router;

Then you can simply test it like this:

curl -X POST http://localhost:3000/api/products/ask \
  -H "Content-Type: application/json" \
  -d '{
    "question": "Do you have any wireless headphones with noise cancellation?",
    "category": "Electronics",
    "topK": 3
  }'

Comments

[Mohammad Jawad (Kasir) Barati]

BTW I was not 100% sure if my understanding of indexes in Postgres and query planner is correct. So I asked this question from Claude: claude.ai/share/f9908db5-0115-46c1...