RAG step by step for your SaaS: from PDF to chatbot in 2 hours

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

-- Table for indexed chunks
create table documents (
id bigserial primary key,
content text not null,
metadata jsonb,
embedding vector(1536),       -- text-embedding-3-small produces 1536 dims
tenant_id uuid not null,      -- multi-tenancy from day 1
created_at timestamptz default now()
);

-- Index for fast search (HNSW for up to ~1M vectors)
create index documents_embedding_idx
on documents
using hnsw (embedding vector_cosine_ops);

-- Per-tenant index so the filter is cheap
create index documents_tenant_idx on documents(tenant_id);

-- RLS so each customer sees only their chunks
alter table documents enable row level security;

create policy "tenants can only access their own documents"
on documents
using (tenant_id = auth.uid());

~
pnpm add openai @supabase/supabase-js
pnpm add -D tsx

~
// scripts/index-documents.ts
import { createClient } from "@supabase/supabase-js";
import OpenAI from "openai";
import fs from "node:fs";

const supabase = createClient(
process.env.SUPABASE_URL!,
process.env.SUPABASE_SERVICE_ROLE_KEY! // service_role to bypass RLS while indexing
);
const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY });

const TENANT_ID = process.env.TENANT_ID!;
const CHUNK_SIZE = 600;     // approx tokens. 1 token ~= 4 chars in English
const CHUNK_OVERLAP = 80;   // overlap so context doesn't break

function chunkText(text: string): string[] {
const chunks: string[] = [];
const approxCharsPerChunk = CHUNK_SIZE * 4;
const overlapChars = CHUNK_OVERLAP * 4;
let i = 0;
while (i < text.length) {
  chunks.push(text.slice(i, i + approxCharsPerChunk));
  i += approxCharsPerChunk - overlapChars;
}
return chunks;
}

async function indexFile(path: string, source: string) {
const text = fs.readFileSync(path, "utf-8");
const chunks = chunkText(text);
console.log(`  > ${chunks.length} chunks`);

// Batch embeddings — OpenAI accepts up to 2048 inputs per request
const embedRes = await openai.embeddings.create({
  model: "text-embedding-3-small",
  input: chunks,
});

const rows = chunks.map((content, idx) => ({
  content,
  embedding: embedRes.data[idx].embedding,
  tenant_id: TENANT_ID,
  metadata: { source, chunk_index: idx },
}));

const { error } = await supabase.from("documents").insert(rows);
if (error) throw error;
}

async function main() {
const files = process.argv.slice(2);
for (const f of files) {
  console.log(`Indexing ${f}...`);
  await indexFile(f, f);
}
console.log("Done.");
}

main();

~
TENANT_ID=<uuid> pnpm tsx scripts/index-documents.ts docs/*.txt

~
create or replace function match_documents(
query_embedding vector(1536),
match_threshold float,
match_count int,
filter_tenant_id uuid
)
returns table (
id bigint,
content text,
metadata jsonb,
similarity float
)
language sql stable
as $$
select
  documents.id,
  documents.content,
  documents.metadata,
  1 - (documents.embedding <=> query_embedding) as similarity
from documents
where documents.tenant_id = filter_tenant_id
  and 1 - (documents.embedding <=> query_embedding) > match_threshold
order by documents.embedding <=> query_embedding
limit match_count;
$$;

~
// app/api/ask/route.ts
import { createClient } from "@supabase/supabase-js";
import OpenAI from "openai";
import { NextResponse } from "next/server";

const supabase = createClient(
process.env.SUPABASE_URL!,
process.env.SUPABASE_ANON_KEY! // anon key + RLS so it respects user's tenant_id
);
const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY });

export async function POST(req: Request) {
const { question, tenantId } = await req.json();

// 1. Embed the question
const embedRes = await openai.embeddings.create({
  model: "text-embedding-3-small",
  input: question,
});
const queryEmbedding = embedRes.data[0].embedding;

// 2. Find top-5 chunks
const { data: chunks, error } = await supabase.rpc("match_documents", {
  query_embedding: queryEmbedding,
  match_threshold: 0.75,
  match_count: 5,
  filter_tenant_id: tenantId,
});

if (error || !chunks?.length) {
  return NextResponse.json({
    answer: "I couldn't find relevant information. Could you rephrase the question?",
    sources: [],
  });
}

// 3. Build the augmented prompt
const context = chunks
  .map((c: any, idx: number) => `[Source ${idx + 1}]: ${c.content}`)
  .join("\n\n");

const systemPrompt = `You're an assistant that answers questions based ONLY on the provided context.
If the answer isn't in the context, say you don't know.
Cite sources using [Source N] at the end of each statement.`;

// 4. Call the LLM
const completion = await openai.chat.completions.create({
  model: "gpt-4o-mini",
  messages: [
    { role: "system", content: systemPrompt },
    { role: "user", content: `Context:\n${context}\n\nQuestion: ${question}` },
  ],
  temperature: 0.2, // low for factual answers
});

return NextResponse.json({
  answer: completion.choices[0].message.content,
  sources: chunks.map((c: any) => c.metadata),
  tokens: completion.usage,
});
}

~
const openai = new OpenAI({
apiKey: process.env.OPENAI_API_KEY,
baseURL: "https://oai.helicone.ai/v1",
defaultHeaders: {
  "Helicone-Auth": `Bearer ${process.env.HELICONE_API_KEY}`,
  "Helicone-User-Id": tenantId,
},
});