Build a RAG Chatbot Without Pinecone: pgvector + Next.js in Under 100 Lines

create extension if not exists vector;

create table documents (
  id uuid primary key default gen_random_uuid(),
  content text not null,
  embedding vector(1536),
  metadata jsonb default '{}',
  created_at timestamptz default now()
);

-- HNSW index for fast approximate search
create index on documents using hnsw (embedding vector_cosine_ops)
  with (m = 16, ef_construction = 64);

// lib/rag/ingest.ts
import Anthropic from '@anthropic-ai/sdk'
import { db } from '@/db'
import { documents } from '@/db/schema'

async function embed(text: string): Promise<number[]> {
  const response = await fetch('https://api.voyageai.com/v1/embeddings', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${process.env.VOYAGE_API_KEY}`,
      'Content-Type': 'application/json',
    },
    body: JSON.stringify({ input: text, model: 'voyage-3' }),
  })
  const data = await response.json()
  return data.data[0].embedding
}

export async function ingestDocument(content: string, metadata = {}) {
  const embedding = await embed(content)
  await db.insert(documents).values({ content, embedding, metadata })
}

export async function ingestBatch(docs: { content: string; metadata?: object }[]) {
  for (const doc of docs) {
    await ingestDocument(doc.content, doc.metadata)
    await new Promise(r => setTimeout(r, 100))
  }
}

// lib/rag/retrieve.ts
import { sql } from 'drizzle-orm'
import { db } from '@/db'

export async function retrieveContext(
  query: string,
  limit = 5,
  threshold = 0.7
): Promise<string[]> {
  const queryEmbedding = await embed(query)
  const embeddingStr = `[${queryEmbedding.join(',')}]`

  const results = await db.execute(sql`
    select content, 1 - (embedding <=> ${embeddingStr}::vector) as similarity
    from documents
    where 1 - (embedding <=> ${embeddingStr}::vector) > ${threshold}
    order by embedding <=> ${embeddingStr}::vector
    limit ${limit}
  `)

  return results.rows.map((r: any) => r.content)
}

// app/api/chat/route.ts
import Anthropic from '@anthropic-ai/sdk'
import { retrieveContext } from '@/lib/rag/retrieve'

const client = new Anthropic()

export async function POST(req: Request) {
  const { messages } = await req.json()
  const lastMessage = messages[messages.length - 1]

  const context = await retrieveContext(lastMessage.content)

  const systemPrompt = context.length > 0
    ? `You are a helpful assistant. Use the following context to answer questions accurately.\n\n<context>\n${context.join('\n\n---\n\n')}\n</context>\n\nIf the context doesn't contain the answer, say so clearly.`
    : 'You are a helpful assistant.'

  const stream = client.messages.stream({
    model: 'claude-sonnet-4-6',
    max_tokens: 1024,
    system: systemPrompt,
    messages,
  })

  return new Response(
    new ReadableStream({
      async start(controller) {
        for await (const chunk of stream) {
          if (chunk.type === 'content_block_delta' && chunk.delta.type === 'text_delta') {
            controller.enqueue(new TextEncoder().encode(chunk.delta.text))
          }
        }
        controller.close()
      },
    }),
    { headers: { 'Content-Type': 'text/plain; charset=utf-8' } }
  )
}