Building Production-Ready RAG Web Apps: Vector Search with PGVector and Next.js

AI integration has advanced far beyond simple completions and generic chatbots. Modern businesses are building intelligent tools that search internal knowledge bases, analyze private code repositories, and answer customer queries based on dynamic documentation.

To deliver these capabilities, Retrieval-Augmented Generation (RAG) has emerged as the standard architecture. RAG retrieves context from a database containing your company's documents, joins that context with the user's prompt, and passes it to a Large Language Model (LLM) like OpenAI's GPT-4 or Anthropic's Claude.

In this guide, we'll build a production-ready RAG application using Next.js and pgvector—a powerful extension that enables vector search directly inside PostgreSQL.

---

Why pgvector?

Historically, developers used specialized standalone vector databases (like Pinecone or Milvus) to store and query document embeddings. While performant, this introduced architecture fragmentation: you had to maintain separate sync scripts to keep your main database in line with your vector database.

With pgvector, your vector search functionality is built directly into PostgreSQL. This means: * Your application data and vectors live in the same database. * You can join vector search queries with traditional relational filters (e.g. searching only documents owned by a specific workspace or created after a certain date). * You can utilize standard database transaction safety, backups, and replication.

+------------------+     Query     +-------------------+
|   User Prompt    | ------------> | OpenAI Embeddings |
+------------------+               +-------------------+
                                             |
                                             v (Vector Embedding)
+------------------+  Top Matching +-------------------+
|  LLM (GPT/Claude)| <------------ | PostgreSQL Vector |
|                  |   Documents   |  (via pgvector)   |
+------------------+               +-------------------+
        |
        v
+------------------+
| Context-Aware    |
|   AI Response    |
+------------------+

---

Step 1: Initialize pgvector

In Supabase or any PostgreSQL instance, enable the `vector` extension:

-- Enable the pgvector extension
create extension if not exists vector;

---

Step 2: Define the Document Schema

We'll define a table to store document sections. We generate vector embeddings using OpenAI's `text-embedding-3-small` model, which yields 1536 dimensions.

create table document_sections (
  id uuid default gen_random_uuid() primary key,
  document_title text not null,
  content text not null,
  embedding vector(1536), -- Vector column for embeddings
  created_at timestamp with time zone default timezone('utc'::text, now()) not null

-- Build a vector index to accelerate query execution speed create index on document_sections using ivfflat (embedding vector_cosine_ops) with (lists = 100); ```

---

Step 3: Implement Context Search Function

Next, we write a database function that retrieves the most semantically similar sections based on cosine similarity:

create or replace function match_document_sections (
  query_embedding vector(1536),
  match_threshold float,
  match_count int
)
returns table (
  id uuid,
  document_title text,
  content text,
  similarity float
)
language sql stable
as $$
  select
    document_sections.id,
    document_sections.document_title,
    document_sections.content,
    1 - (document_sections.embedding <=> query_embedding) as similarity
  from document_sections
  where 1 - (document_sections.embedding <=> query_embedding) > match_threshold
  order by document_sections.embedding <=> query_embedding
  limit match_count;
$$;

---

Step 4: Building the Next.js API Router

In our Next.js App Router, we fetch embeddings from OpenAI, execute the database similarity search, construct the context payload, and stream the LLM response.

import { NextRequest } from 'next/server';
import { createClient } from '@/lib/supabase/server';

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

export async function POST(req: NextRequest) { try { const { prompt } = await req.json(); if (!prompt) return new Response("Missing prompt", { status: 400 });

// 1. Generate vector embedding for user query const embeddingResponse = await openai.embeddings.create({ model: 'text-embedding-3-small', input: prompt, }); const [{ embedding }] = embeddingResponse.data;

// 2. Fetch context from PostgreSQL using Supabase RPC const supabase = await createClient(); const { data: matchedSections, error } = await supabase.rpc( 'match_document_sections', { query_embedding: embedding, match_threshold: 0.65, match_count: 5, } );

if (error) throw error;

// 3. Assemble prompt context const contextText = matchedSections ? matchedSections.map((sec: any) => `[Source: ${sec.document_title}]\n${sec.content}`).join('\n\n') : "No matching context found.";

const systemPrompt = `You are a helpful software assistant. Answer the user's question using ONLY the context provided below. If you do not know the answer or if the context does not cover it, say 'I cannot find the answer in the provided documents.' Context: ${contextText}`;

// 4. Stream response from OpenAI Chat Completion const chatResponse = await openai.chat.completions.create({ model: 'gpt-4o-mini', messages: [ { role: 'system', content: systemPrompt }, { role: 'user', content: prompt } ], stream: true, });

// Translate chatResponse stream into a Response const encoder = new TextEncoder(); const stream = new ReadableStream({ async start(controller) { for await (const chunk of chatResponse) { const text = chunk.choices[0]?.delta?.content || ""; controller.enqueue(encoder.encode(text)); } controller.close(); }, });

return new Response(stream, { headers: { 'Content-Type': 'text/plain; charset=utf-8' }, });

} catch (error: any) { return new Response(error.message, { status: 500 }); } } ```

---

Best Practices for RAG Systems

1. Document Chunking: Do not pass entire 100-page files as a single block. Break files down into manageable paragraphs or markdown blocks (e.g. 500-1000 characters) with a slight overlap (e.g. 100 characters) to ensure context continuity.
2. Metadata Tagging: Attach tenant/workspace IDs to your document indexes. This allows you to filter database queries BEFORE running vector comparison, preventing cross-tenant data leaks.
3. Hybrid Search: Combine vector search (semantic similarity) with keyword search (Full-Text Search) using PostgreSQL's native search features to catch precise vocabulary matches.
4. Prompt Engineering: Explicitly instruct models to stick to the provided context to prevent hallucinations.