RoadsideCoderNotes

RAG & Vector Databases with Postgres (Full Notes)

Complete written notes for the RAG and vector database tutorial: embeddings, cosine distance with pgvector, chunking, semantic search, a chat-with-your-docs app with citations and hybrid search, plus multi-tenant RLS, HNSW indexes, auto-updating embeddings and agentic RAG, all on Supabase's free tier with Gemini.

Video coming soon

These notes are ready, the companion video is still on the way.

Full written notes to follow along with the video, or come back to as a reference later.

Note

This builds on the Postgres tutorial notes. Tables, EXPLAIN ANALYZE and Row Level Security are used here without being re-taught, so skim that first if any of them are new.

The sections are numbered the same as the chapters in the video and the interactive lab. The video covers 01–09 and 16. Sections 10–15 aren't in the video; they're collapsed, so open them when you need them.

What we're building: chat with your docs

We build a "chat with your company docs" app: upload documents, ask questions, and get answers that come only from your documents, with citations. It's also one of the most requested AI freelance projects right now, so by the end you'll have a real portfolio piece.

Everything runs on free tiers: a free Supabase project for the database and backend, and Google's Gemini API free tier for embeddings and answers. No card needed for either.

Environment setup

Two ways to get a Postgres database with pgvector. These notes use Supabase from here on, so Option A is the one to pick if you want to follow every step.

Option A, Supabase (hosted). No local install, and it runs the whole app. Sign up for Supabase, click New project, name it rag-tutorial, set a database password (save it somewhere), pick the region closest to you, and wait a minute for it to start. Then open the SQL Editor and turn on pgvector:

create extension if not exists vector with schema extensions;

You can also switch it on under Database → Extensions (search vector).

Option B, local Postgres via Docker. The pgvector/pgvector image is plain Postgres with pgvector already installed:

docker run --name learn-vectors \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  -d pgvector/pgvector:pg17
docker exec -it learn-vectors psql -U postgres

Then turn on pgvector inside psql:

create extension if not exists vector;
Watch out

Local Postgres only gets you the database. The app also needs Supabase's Edge Functions and Table Editor, so every step after this assumes a Supabase project.

What is RAG?

Large language models have three problems when you ask them about your data:

  • They're frozen at a training cutoff.
  • They've never seen your company's handbook, contracts or docs.
  • When they don't know, they often answer confidently anyway.

The naive fix is pasting every document into the prompt. That breaks quickly: you pay for every token on every question, you hit context limits, and the model gets worse when buried in irrelevant text.

RAG (Retrieval-Augmented Generation) fixes this in three steps:

  1. Retrieve the few pieces of your documents most relevant to the question.
  2. Augment the prompt with just those pieces.
  3. Generate the answer from them.

Think of it as an open-book exam. The LLM is a brilliant student who never read your handbook. Without RAG, they guess. With RAG, before each question you slide the 3 right pages onto their desk. They still write the answer themselves, but now from the pages.

Closed book (plain LLM)Question“Can I fly business?”nothing about your company in hereLLMnever read your docsConfident guess✗ maybe made upOpen book (RAG)Question“Can I fly business?”1. Retrievesearch your docs2. Augmenttop 3 chunks → prompt3. GenerateLLM reads the pagesGrounded answer✓ “No, economy [1]”

Same model, same question. The only change is handing it the right pages from your docs before it answers.

Note

RAG vs fine-tuning, a common interview question: fine-tuning changes how a model writes (tone, format, style). RAG changes what it knows right now. For company documents that change every week, you want RAG.

Embeddings: turning text into numbers

To "retrieve the relevant pages", we need a way to measure how similar a question is to each piece of a document by meaning, not by matching words. That's what an embedding does.

An embedding model turns any text into a fixed-length list of numbers, a vector. Think of it as GPS coordinates for meaning. Cities close together on a map have similar coordinates; sentences close together in meaning get similar vectors. "Puppy" and "dog" live in the same neighborhood. "Invoice" lives in a different town.

petsdogpuppycatmoneyinvoicereceipttaxtravelcheap flightsbudget airfarehotel← zero shared words,almost the same spot

An embedding is a coordinate on a map of meaning. Nearby points = similar meaning, even when the words share nothing.

Two facts to remember:

  • Dimensions are fixed per model. We use Google's gemini-embedding-2, asking it for 768 numbers per text.
  • Documents and questions must use the same model. Vectors from different models live on different maps and can't be compared.
▸King − man + woman, and why 768 instead of the default 3,072Go deeper

The classic demonstration: take the vector for king, subtract man, add woman, and the nearest vector is queen. Meaning has become geometry, and once text is geometry, "find similar text" is just "find nearby points".

gemini-embedding-2 returns 3,072 numbers by default. We ask for 768 because pgvector's HNSW index (see Vector indexes) supports at most 2,000 dimensions for the vector type. Think of it like photo resolution: 768 is a smaller photo where every face is still clearly recognizable. It's smaller to store, faster to compare, and it fits the index.

Cosine distance: measuring similarity

Open the SQL Editor in your Supabase project. Real embeddings have 768 numbers, which is unreadable. So we start with 3-number vectors where you can see why things are close. Imagine the three numbers mean [fruitiness, sweetness, techiness]:

create table items (
  id bigserial primary key,
  name text not null,
  embedding vector(3)   -- the new column type pgvector adds
);
 
insert into items (name, embedding) values
  ('apple',  '[1, 0.9, 0]'),
  ('banana', '[0.9, 1, 0.1]'),
  ('laptop', '[0, 0.1, 1]');
Output
CREATE TABLE
INSERT 0 3

Our query vector is "something fruity and sweet", [1, 1, 0]. The <=> operator measures the cosine distance between two vectors: small means similar, large means different.

select name, embedding <=> '[1, 1, 0]' as distance
from items order by embedding <=> '[1, 1, 0]';
Output (values rounded)
namedistance
apple0.001
banana0.004
laptop0.930
(3 rows)

The fruit comes first and the laptop last. Picture each vector as an arrow from the origin. Cosine asks "are the arrows pointing the same way?" and ignores how long they are. A one-line tweet and a three-page essay about refunds point the same direction, so cosine calls them similar, which is exactly what we want for text.

tweet about refunds3-page essay about refundsrecipe for pastatweet ↔ essaycosine ≈ 0 (same way)euclidean: far aparttweet ↔ recipecosine: large(different topic)

Cosine distance only cares about direction, not length, which is why it's the default for text.

One handy fact: similarity = 1 - cosine_distance. So a distance of 0.001 is a similarity of 0.999, and we'll use that to score search results.

pgvector has two more operators, <-> (straight-line distance) and <#> (inner product). For text embeddings, stick with <=>; the other two operators are covered at the end.

Honesty checkpoint

pgvector is an open-source Postgres extension, not a Supabase product. Supabase ships it pre-installed, and so do most managed Postgres hosts.

Chunking: why it makes or breaks RAG

If you embed a whole 40-page handbook as one vector, that vector is an average of everything in it, too blurry to match any specific question. Think of a book's index: a good one says "Refunds → page 42". An index with a single entry, "Handbook → pages 1–300", is technically correct and completely useless. Embedding a whole document is that one-entry index. Chunking writes a proper index, one entry per topic.

No chunkingIndexHandbook ...... p.1–300✗ matches everything, answers nothingChunked by headingIndexTravel Policy ...... p.4Approvals .......... p.9IT Troubleshooting . p.14Remote Work ........ p.21✓ lands on the one section that answers

Question: “Who approves travel over $2,000?” One blurry entry can't point anywhere useful; a per-topic index points straight at the answer.

Too small is also bad: a single sentence like "It must be approved by your department head" has lost what must be approved. A practical recipe for docs:

  • Split on markdown headings, so each chunk is one topic.
  • Cap chunks at about 1,000 characters.
  • Overlap neighboring windows by about 150 characters so a sentence cut at a boundary still appears whole in one chunk.

The chunkMarkdown function in the next section is this recipe in code.

Knowledge base: from files to searchable rows

Time to turn real documents into searchable chunks. We'll build it one step at a time, and every step ends with something you can see working:

  1. Run the starter and look at what's inside.
  2. Connect the app to Supabase, so it lists documents from your database.
  3. Write your first Edge Function, embed, and see a real embedding.
  4. Upload documents with the ingest function, and watch them turn into chunks.

From here on, code goes in three places. Every block in these notes is labelled with where it belongs:

WhatWhere you put it
SQL (tables, grants, search functions)Supabase dashboard → SQL Editor → paste → Run
Edge Functions (embed, ingest, chat)Supabase dashboard → Edge Functions (no files on your computer)
The app (.env.local, src/api.ts, try.js)The starter folder on your computer

Step 1: Run the starter

The starter is the app's frontend: a React page with your documents on the left and a chat on the right, already styled, with no backend yet. It also has the sample documents we'll upload, in demo-docs/. If you get stuck at any point, the finished code is on the repo's final branch.

git clone https://github.com/piyush-eon/rag-tutorial.git
cd rag-tutorial
npm install
npm run dev

Open http://localhost:5173. The whole app is there, but nothing works yet: try uploading a file and it says "the app isn't connected to Supabase". Before connecting anything, here's how the starter is put together.

src/
  main.tsx          starts React and shows <App />
  App.tsx           the page: a heading and the two panels side by side
  components/
    Documents.tsx   left panel: the upload box and the list of documents
    Chat.tsx        right panel: the messages, the question box, the citation chips
  api.ts            every call to the backend, as three TODO functions
  index.css         Tailwind and the app's colours
demo-docs/          sample company handbooks to upload

App.tsx only lays out the page: one column for documents, two for the chat on wide screens.

// src/App.tsx (the part that matters)
<main className="grid gap-4 md:grid-cols-3">
  <Documents />
  <div className="md:col-span-2">
    <Chat />
  </div>
</main>

The Documents panel keeps the list of documents in React state. When the page loads, it fills that list from listDocuments(). When you pick files, it sends them to uploadDocument() one at a time and reloads the list after each one:

// src/components/Documents.tsx (simplified: the parts that talk to the backend)
async function refresh() {
  setDocs(await listDocuments())   // ask the backend for the list
}
 
useEffect(() => {
  refresh()                        // runs once, when the page loads
}, [])
 
async function onFiles(e: ChangeEvent<HTMLInputElement>) {
  const files = [...(e.target.files ?? [])]
  for (const [i, file] of files.entries()) {
    setStatus(`Uploading ${i + 1} of ${files.length}: ${file.name}…`)
    await uploadDocument(file)     // send one file
    await refresh()                // then reload the list
  }
}

The rest of the file draws the upload box and one row per document, with its title and chunk count.

The Chat panel keeps the conversation in state. When you press Send, it adds your question to the conversation, calls askQuestion(), and adds the reply:

// src/components/Chat.tsx (simplified)
async function send(e: FormEvent) {
  e.preventDefault()
  setMessages((m) => [...m, { role: 'user', text: question }])
  const reply = await askQuestion(question)   // ask the backend
  setMessages((m) => [...m, { role: 'bot', reply }])
}

Under it, a small Answer component turns markers like [1] in a reply into clickable chips. We'll look at it in the chat section.

api.ts is the only file we edit. The components never talk to Supabase themselves: they call the three functions in api.ts, which for now return nothing or say "not connected":

// Everything the browser sends to the backend lives in this file.
// In the video we connect each function to Supabase: look for TODO(06) and TODO(08).
 
export type Doc = { id: number; title: string; chunks: number }
export type Source = { title: string; content: string; similarity: number }
export type ChatReply = { answer: string; sources: Source[] }
 
// TODO(06): read the documents, with how many chunks each one has, from the `documents` table
export async function listDocuments(): Promise<Doc[]> {
  return []
}
 
// TODO(06): send the file's name and text to the `ingest` Edge Function
export async function uploadDocument(file: File): Promise<void> {
  throw new Error(`Can't upload ${file.name} yet: the app isn't connected to Supabase`)
}
 
// TODO(08): send the question to the `chat` Edge Function. It returns { answer, sources }.
export async function askQuestion(question: string): Promise<ChatReply> {
  throw new Error(`Can't answer "${question}" yet: the app isn't connected to Supabase`)
}

So connecting the backend means filling in api.ts, one function at a time:

FunctionCalled byWhenFilled in
listDocumentsDocuments panelthe page loads, and after each uploadStep 2
uploadDocumentDocuments panelyou pick filesStep 4
askQuestionChat panelyou press SendSection 08

Step 2: Connect the app to Supabase

First goal: the Documents panel shows rows from your database.

How the pieces talk to each other. Every Supabase project comes with a built-in API: a web address your code sends requests to, which Supabase turns into database queries. There's nothing to install or run; it's already part of the project. Two values let code use it:

  • the project URL: where to send requests
  • the publishable key: like a visitor badge, it says which project the request is for

In a minute, these two values go into a .env.local file in the starter. They aren't a separate route to the database; they're the address and the badge for this API. The app talks to the database this way, and so will the Edge Functions we write in Step 3. That's also why the tables will need a grant in a minute: the API only lets the badge do what you've allowed.

Create the tables. Two tables: documents, and the searchable chunks each document is split into. We only list documents in this step, but listDocuments also counts each document's chunks, so both tables need to exist. Run this in the SQL Editor:

create table documents (
  id bigserial primary key,
  title text not null,
  created_at timestamptz default now()
);
 
create table document_chunks (
  id bigserial primary key,
  document_id bigint not null references documents on delete cascade,
  chunk_index int not null,
  content text not null,
  embedding vector(768)   -- must match the embedding model's output size
);

Who may use these tables? Creating a table doesn't let the app reach it yet. The app (and, later, our Edge Functions) read and write through Supabase's API, and every API request runs as a Postgres role: a named user with its own permissions. Requests made with the publishable key run as a role called anon, short for anonymous, meaning someone who hasn't signed in. Since 2026, Supabase no longer opens new tables to the API automatically, so a new table gives anon no access at all.

Think of a new room in an office building: it starts locked for visitors. grant gives the visitor badge a key that opens only what you list:

grant select, insert on documents, document_chunks to anon;
grant usage on sequence documents_id_seq, document_chunks_id_seq to anon;  -- for the auto-numbered ids
  • select lets anon read rows: the app lists documents, and search reads chunks.
  • insert lets anon add rows: ingest saves each document and its chunks.
  • No update or delete: visitors can't change or remove anything.
  • The second line covers the counter Postgres keeps behind every auto-numbered id column (named <table>_id_seq), so new rows can get their next number.

Skip the grants and the app sees nothing: the test row in a minute won't show up, and uploads fail with permission denied for table documents.

There are no users yet: anyone can upload and everyone sees everything. That's fine for one person learning; Multi-tenant RLS, one of the extras at the end, adds owners and sign-in. The embedding column is nullable on purpose; auto-updating embeddings uses that.

Watch out

Supabase will warn that these tables have no Row Level Security yet: anyone with your publishable key can read every document. That's fine for learning, and Multi-tenant RLS shows the leak it causes and the fix. Never ship a real app in this state.

Install the Supabase client in the starter folder:

npm install @supabase/supabase-js

Create a file named .env.local in the starter folder, next to package.json. Click Connect at the top of the Supabase dashboard and copy two values into it: the project URL and the publishable key (it starts with sb_publishable_):

# .env.local
VITE_SUPABASE_URL=https://<project-ref>.supabase.co
VITE_SUPABASE_PUBLISHABLE_KEY=sb_publishable_...

The VITE_ prefix is how Vite knows these values may be used in the browser. .env.local is already in the starter's .gitignore, so it never gets committed. Stop npm run dev and start it again: Vite only reads env files when it starts.

Fill in listDocuments. In src/api.ts, add the import and the client at the top of the file, then replace the TODO version of listDocuments with this one:

// src/api.ts
import { createClient } from '@supabase/supabase-js'
 
const supabase = createClient(
  import.meta.env.VITE_SUPABASE_URL,
  import.meta.env.VITE_SUPABASE_PUBLISHABLE_KEY,
)
 
// ...the types stay as they are...
 
export async function listDocuments(): Promise<Doc[]> {
  const { data } = await supabase.from('documents').select('id, title, document_chunks(count)').order('id')
  return (data ?? []).map((d) => ({ id: d.id, title: d.title, chunks: d.document_chunks[0]?.count ?? 0 }))
}
  • createClient connects to your project, using the two values from .env.local. Every function in api.ts uses this one supabase client.
  • The query is SQL written in JavaScript: from('documents') picks the table, and select(...) asks for each row's id and title, plus document_chunks(count), how many chunks it has. The count follows the document_id foreign key, so it's one query, not two.
  • The last line reshapes each row into what the Documents panel expects: { id, title, chunks }.

The publishable key is safe in the browser: it can only do what you granted to anon. Never put the secret key in a frontend.

Try it. The list is still empty, because the table is. Add a row by hand: open Table Editor → documents, click Insert → Insert row, type test.md as the title, and save. Reload the app, and test.md · 0 chunks appears on the left: your React app is reading from Postgres. (If it doesn't, check that the grants ran.) Delete the test row in the Table Editor before moving on.

Step 3: Your first Edge Function, embed

Uploading a document means turning its text into embeddings, and that means calling Gemini with a secret key.

What's an Edge Function? Some of our code can't run in the browser. Calling Gemini needs your Gemini key, and anything inside a web page can be read by anyone who opens it. So that code runs on Supabase's servers instead, as Edge Functions: small TypeScript functions that Supabase hosts for you. Each one gets its own web address, https://<project-ref>.supabase.co/functions/v1/<name>, and the app calls it by name. "Edge" just means Supabase runs them on servers close to your users.

Think of a bank teller's window. You pass your request through the window, the teller uses the vault key on their side, and hands back the result. The key never leaves the counter. Edge Functions are the teller, and the Gemini key is the vault key.

We'll write three of them:

FunctionYou send itIt doesBuilt in
embeda piece of textturns it into 768 numbers and sends them backStep 3
ingestan uploaded filesplits it into chunks, embeds them, saves them in the databaseStep 4
chata questionfinds the best chunks, asks Gemini to answer from themSection 08

Add your Gemini API key. Create a free key in Google AI Studio (Get API key, no card required). In the Supabase dashboard, open Edge Functions → Secrets, add a secret named GEMINI_API_KEY with your key as the value, and click Save. Our functions read it with Deno.env.get('GEMINI_API_KEY'), so the key never reaches the browser.

How we'll write them. Right in the dashboard: Edge Functions → Deploy a new function → Via Editor, give it a name, paste the code, and click Deploy function.

Each of our functions needs one setting changed. The app has no sign-in, so it calls functions with your project's publishable key instead of a user's login token, and Supabase's built-in JWT check only accepts login tokens. So after deploying a function, open its Settings tab, turn off Verify JWT with legacy secret and click Save changes. (Supabase recommends this setting off anyway.) The functions check the key themselves, with withSupabase (you'll see it in a minute).

Watch out

Free-tier rate limits depend on your project. Check yours in AI Studio before building, especially if you try agentic RAG, which makes several calls per question.

One more small table. Run this in the SQL Editor too. It keeps every test question we embed, so we can search with them from the SQL Editor later (typing 768 numbers by hand isn't an option). It gets update too, because embedding the same question twice overwrites the saved row:

create table demo_queries (q text primary key, embedding vector(768));
grant select, insert, update on demo_queries to anon;

Our first Edge Function, embed, does one small job: you send it a piece of text, it asks Gemini for that text's 768 numbers, and sends them back. The app itself never calls it. It's how we see a real embedding, and later how we turn test questions into numbers for the SQL demos. (ingest and chat make their own embeddings inside, with the same code.)

Create a new function named embed and paste this:

// Edge Function: embed
import { withSupabase } from 'npm:@supabase/server@1'
import { GoogleGenAI } from 'npm:@google/genai'
 
const ai = new GoogleGenAI({ apiKey: Deno.env.get('GEMINI_API_KEY') })
 
// Turns texts into 768-number vectors. Many texts in one call = one API request,
// which matters on free-tier rate limits.
async function embed(texts: string[]): Promise<number[][]> {
  const res = await ai.models.embedContent({
    model: 'gemini-embedding-2',
    // one { parts } per text: a plain list of strings gets merged into a single vector
    contents: texts.map((text) => ({ parts: [{ text }] })),
    config: { outputDimensionality: 768 },  // must match vector(768) in the table
  })
  return res.embeddings!.map((e) => e.values!)
}
 
export default {
  fetch: withSupabase({ auth: 'publishable' }, async (req, ctx) => {
    const { input } = await req.json()
    const [embedding] = await embed([input])
    // keep it in demo_queries, so we can search with it from SQL later
    await ctx.supabase.from('demo_queries').upsert({ q: input, embedding })
    return Response.json({ embedding })
  }),
}
  • The two import lines. Edge Functions run on Deno, which can load npm packages straight from the import, so there's nothing to install. npm:@google/genai is Google's official Gemini library. npm:@supabase/server@1 is Supabase's helper library for Edge Functions; @1 means "version 1", so a future version 2 can't break this code.
  • embed(), the helper inside (same name as the function, different thing), sends texts to Gemini and gets back one list of 768 numbers per text. Every function that needs embeddings uses it. The dashboard editor keeps each function in its own file, so ingest and chat each get a copy.
  • withSupabase wraps our function and does three jobs for us:
    • Checks the key. auth: 'publishable' means only requests carrying this project's publishable key get in.
    • Answers the browser's CORS check. Before calling a server on another domain, the browser first asks "may this website call you?". Without an answer, the browser blocks the request.
    • Hands us ctx.supabase, a database client that's already set up with the project's URL and key, so the function never handles them itself.
  • gemini-embedding-2 automatically normalizes vectors when you ask for fewer dimensions, which is why cosine distance is safe to use.

Click Deploy function, then turn off Verify JWT with legacy secret in its Settings tab.

Call it from your own code. The app never calls embed, so let's call it ourselves. Create a file named try.js in the starter folder, next to .env.local. It's a small Node script, not part of the React app, and it grows by one block with each function we write:

// try.js (in the starter folder): call our Edge Functions without the app
import { createClient } from '@supabase/supabase-js'
 
const supabase = createClient(process.env.VITE_SUPABASE_URL, process.env.VITE_SUPABASE_PUBLISHABLE_KEY)
 
// Calls an Edge Function by name, at <project URL>/functions/v1/<name>
async function call(name, body) {
  const { data, error } = await supabase.functions.invoke(name, { body })
  // error.context is the HTTP response; a 401 usually means "Verify JWT" is still on
  if (error) throw new Error(`${name} failed: ${error.context?.status ?? error.message}`)
  return data
}
 
const [command, arg] = process.argv.slice(2)
 
// text → 768 numbers
if (command === 'embed') {
  const { embedding } = await call('embed', { input: arg })
  console.log(`${embedding.length} numbers: [${embedding.slice(0, 5).join(', ')}, ...]`)
}

Run it from the starter folder:

node --env-file=.env.local try.js embed "budget airfare"
Output (your numbers will differ)
768 numbers: [-0.015586126, 0.008970454, -0.016507592, -0.033303257, -0.0000681844, ...]

That's an embedding: 768 numbers that mean "budget airfare".

  • --env-file=.env.local makes Node read the same two values the app uses, so there's nothing new to paste.
  • createClient is the same client as in api.ts. supabase.functions.invoke(name, { body }) sends the body to <project URL>/functions/v1/<name>, with your publishable key. Every Edge Function has its own address like this, and the app will call ingest and chat the very same way.
  • process.argv is everything you typed. .slice(2) skips node and the file name, leaving the command (embed) and its input ("budget airfare").
  • embed also saved "budget airfare" in demo_queries. We'll search with that table in Semantic search.

Prefer clicking? The function's Test button (top right) sends a request too: set the body to {"input": "budget airfare"} and click Send Request.

Watch out

On Gemini's free tier, Google may use what you send to improve its products, and for embeddings that's your document text. Fine for tutorials and demo data. For a client's real documents, use the paid tier, or Supabase's built-in gte-small model, which runs inside the Edge Function so the text never leaves Supabase (English only, 384 dimensions).

▸Keeping functions in git with the Supabase CLIGo deeper

The dashboard editor is the quickest way to learn, but it has no version history. In real projects, keep each function in your repo under supabase/functions/<name>/index.ts and deploy with the Supabase CLI: supabase functions deploy <name>. The code is exactly the same.

Step 4: Upload documents with ingest

Upload text → split into chunks → embed them all in one batched call → one bulk insert. Create a new function named ingest:

// Edge Function: ingest
import { withSupabase } from 'npm:@supabase/server@1'
import { GoogleGenAI } from 'npm:@google/genai'
 
const ai = new GoogleGenAI({ apiKey: Deno.env.get('GEMINI_API_KEY') })
 
// Turns texts into 768-number vectors. Many texts in one call = one API request,
// which matters on free-tier rate limits.
async function embed(texts: string[]): Promise<number[][]> {
  const res = await ai.models.embedContent({
    model: 'gemini-embedding-2',
    // one { parts } per text: a plain list of strings gets merged into a single vector
    contents: texts.map((text) => ({ parts: [{ text }] })),
    config: { outputDimensionality: 768 },  // must match vector(768) in the table
  })
  return res.embeddings!.map((e) => e.values!)
}
 
function chunkMarkdown(text: string, maxLen = 1000, overlap = 150): string[] {
  // 1. split on headings, keeping the heading with its section
  const sections = text.split(/\n(?=#{1,3} )/)
  const chunks: string[] = []
  for (const section of sections) {
    // 2. long sections get cut into overlapping windows
    for (let start = 0; start < section.length; start += maxLen - overlap) {
      chunks.push(section.slice(start, start + maxLen).trim())
      if (start + maxLen >= section.length) break
    }
  }
  return chunks.filter((c) => c.length > 0)
}
 
export default {
  // ctx.supabase is a database client that runs as the caller (here: anon)
  fetch: withSupabase({ auth: 'publishable' }, async (req, ctx) => {
    const { title, content } = await req.json()
 
    const { data: doc, error } = await ctx.supabase
      .from('documents').insert({ title }).select().single()
    if (error) return Response.json({ error }, { status: 400 })
 
    const chunks = chunkMarkdown(content)
    const embeddings = await embed(chunks)  // one batched API call for the whole document
    const rows = chunks.map((chunk, i) => ({
      document_id: doc.id, chunk_index: i, content: chunk, embedding: embeddings[i],
    }))
    const { error: chunkError } = await ctx.supabase.from('document_chunks').insert(rows)
    if (chunkError) return Response.json({ error: chunkError }, { status: 400 })
 
    return Response.json({ document_id: doc.id, chunks: rows.length })
  }),
}

Click Deploy function, and turn off Verify JWT with legacy secret, like you did for embed.

Try it from try.js first. Add two imports at the top of try.js:

// try.js: add at the top
import { readFile } from 'node:fs/promises'
import { basename } from 'node:path'

And this block at the bottom:

// try.js: add at the bottom
// a file → chunks saved in the database
if (command === 'ingest') {
  const result = await call('ingest', { title: basename(arg), content: await readFile(arg, 'utf8') })
  console.log(result)
}

readFile reads the file from disk, and basename turns its path into just the file name, which becomes the title. Send one document:

node --env-file=.env.local try.js ingest demo-docs/northwind/travel-policy.md
Output (your id may differ)
{ document_id: 2, chunks: 8 }

Reload the app: travel-policy.md · 8 chunks is on the left. The function works; now the app just has to call it.

Before moving on, delete travel-policy.md in Table Editor → documents. Its chunks go with it, thanks to on delete cascade. In a moment you'll upload every file from the app, and without this the travel policy would be stored twice, so every search would return each of its chunks twice.

Fill in uploadDocument. In src/api.ts, replace the TODO version with this one:

// src/api.ts
export async function uploadDocument(file: File): Promise<void> {
  const { error } = await supabase.functions.invoke('ingest', {
    body: { title: file.name, content: await file.text() },
  })
  if (error) throw error
}
  • supabase.functions.invoke('ingest', ...) is the same call try.js just made: it calls the Edge Function by name and sends it the file's name and text. The client adds your publishable key for you.
  • if (error) throw error passes any failure back to the Documents panel, which shows it under the upload box.
  • You don't need a loop here: the Documents panel already sends files one at a time and refreshes the list after each, which also keeps you inside Gemini's free-tier rate limits.

Save the file; the page reloads by itself. Upload a few documents. The examples in these notes use the sample handbook in the starter's demo-docs/northwind/ folder, for a made-up company called Northwind, so select all of its files. Any .md or .txt files work too. Then open Table Editor → document_chunks: every row is one searchable piece of knowledge, with its 768 numbers in the embedding column.

Recap: every environment variable. Only three are yours to set, in two places:

  • GEMINI_API_KEY: in the dashboard under Edge Functions → Secrets. You add it in Step 3. The Edge Functions use it to call Gemini.
  • VITE_SUPABASE_URL and VITE_SUPABASE_PUBLISHABLE_KEY: in a .env.local file in the starter folder. You add them when connecting the app. The app and try.js use them to reach your project.

Edge Functions also get your project's URL and keys automatically (SUPABASE_URL, SUPABASE_PUBLISHABLE_KEYS and a few more). You never add those; withSupabase reads them for you.

Watch out

If something doesn't work, it's almost always one of these:

  • Blank page, supabaseUrl is required in the browser console: .env.local is missing, misnamed, or not next to package.json, or you didn't restart npm run dev.
  • The document list stays empty, or permission denied for table documents: the grant lines from Step 2 didn't run.
  • A 401 error from a function: Verify JWT with legacy secret is still on in that function's Settings tab.
  • A function fails with an error about the API key: the secret isn't named exactly GEMINI_API_KEY.
  • Not sure if it's the function or the app? Call the function with try.js. If that works, the problem is in src/api.ts.
  • Anything else: open the function in the dashboard and check its Logs tab; the error is there.

Preparing for frontend interviews?

My Frontend Interview Preparation course is the only resource you'll need: in-depth JavaScript, React, system design, machine coding, and more, all in one place.

Check out the course

Semantic search: finding text by meaning

Normal search looks for the exact words you typed. Search the Northwind handbook for "cheap flights" and you get nothing, because its travel policy says "budget airfare" instead. Semantic search looks for text that means the same thing, even when the words are completely different.

It works in three steps, using the embeddings from earlier:

  1. Turn the question into 768 numbers, with the same model used for the chunks.
  2. Find the chunks whose numbers are closest, because closest numbers means most similar meaning.
  3. Return the best few. These become the context the AI answers from.

Step 1 happens in the Edge Function, where Gemini creates the embedding. Steps 2 and 3 happen inside Postgres, in one SQL function, so the chat function calls it in a single line with ctx.supabase.rpc('match_chunks', ...):

create or replace function match_chunks(
  query_embedding vector(768),
  match_threshold float default 0.6,  -- tune for your data; every embedding model scores differently
  match_count int default 5
)
returns table (id bigint, document_id bigint, title text, content text, similarity float)
language sql stable
as $$
  select c.id, c.document_id, d.title, c.content,
         1 - (c.embedding <=> query_embedding) as similarity
  from document_chunks c
  join documents d on d.id = c.document_id
  where 1 - (c.embedding <=> query_embedding) > match_threshold
  order by c.embedding <=> query_embedding   -- order by the raw operator so an index can be used
  limit match_count;
$$;
 
grant execute on function match_chunks to anon;  -- same rule as tables: say who may call it

To try it from the SQL Editor you need the question as a vector. That's what demo_queries is for. In the starter folder, embed the question:

node --env-file=.env.local try.js embed "cheap flights"

embed saved it with its 768 numbers, ready to search with.

Now compare keyword search with semantic search on the same question. The handbook says "budget airfare"; the question says "cheap flights":

-- Keyword search: the words "cheap flights" appear nowhere in the handbook
select content from document_chunks where content ilike '%cheap flights%';
Output
 content
---------
(0 rows)
-- Semantic search: same question, matched by meaning
select title, left(content, 60) as preview, round(similarity::numeric, 3) as similarity
from match_chunks((select embedding from demo_queries where q = 'cheap flights'), 0.5, 3);
Output (your scores may differ slightly)
titlepreviewsimilarity
travel-policy.md## 2. Booking - Book through the Northwind travel portal at0.664
travel-policy.md## 5. Changes and cancellations - Cancel unused bookings in0.634
approval-matrix.md## 2. Trips - All travel over $2,000 must be approved by you0.627
(3 rows)

Zero shared words, and the booking rules come out on top. That's the whole point of vectors. Notice the scores: with gemini-embedding-2, a good match lands around 0.6–0.75, not near 1. That's why match_threshold needs tuning for each embedding model.

With a few hundred chunks this search is instant. Once a table grows past roughly 10,000 rows, add a vector index; see Vector indexes.

Chat with citations: the full RAG loop

Now the three RAG steps become code. The chat Edge Function embeds the question, retrieves the closest chunks, numbers them, and asks Gemini to answer from them. Create a new function named chat:

// Edge Function: chat
import { withSupabase } from 'npm:@supabase/server@1'
import { GoogleGenAI } from 'npm:@google/genai'
 
const ai = new GoogleGenAI({ apiKey: Deno.env.get('GEMINI_API_KEY') })
 
// Turns texts into 768-number vectors. Many texts in one call = one API request,
// which matters on free-tier rate limits.
async function embed(texts: string[]): Promise<number[][]> {
  const res = await ai.models.embedContent({
    model: 'gemini-embedding-2',
    // one { parts } per text: a plain list of strings gets merged into a single vector
    contents: texts.map((text) => ({ parts: [{ text }] })),
    config: { outputDimensionality: 768 },  // must match vector(768) in the table
  })
  return res.embeddings!.map((e) => e.values!)
}
 
const SYSTEM_PROMPT = `You answer questions about the user's documents.
Use ONLY the numbered context below. Cite sources inline like [1] or [2].
If the answer is not in the context, say "I don't know based on your documents." Never guess.`
 
export default {
  fetch: withSupabase({ auth: 'publishable' }, async (req, ctx) => {
    const { question } = await req.json()
 
    // 1. RETRIEVE — embed the question, find the closest chunks
    const [query_embedding] = await embed([question])
    const { data: chunks } = await ctx.supabase.rpc('match_chunks', {
      query_embedding, match_threshold: 0.6, match_count: 5,
    })
 
    // 2. AUGMENT — number the chunks so the model can cite them
    const context = (chunks ?? [])
      .map((c, i) => `[${i + 1}] (${c.title})\n${c.content}`)
      .join('\n\n')
 
    // 3. GENERATE
    const interaction = await ai.interactions.create({
      model: 'gemini-3.5-flash-lite',
      system_instruction: SYSTEM_PROMPT,
      generation_config: { max_output_tokens: 1024 },
      input: `Context:\n${context}\n\nQuestion: ${question}`,
    })
    const answer = interaction.output_text
 
    return Response.json({ answer, sources: chunks })
  }),
}

Click Deploy function and turn off Verify JWT with legacy secret. Then add chat to the bottom of try.js:

// try.js: add at the bottom
// a question → an answer, plus the chunks it used
if (command === 'chat') {
  const { answer, sources } = await call('chat', { question: arg })
  console.log(answer)
  sources.forEach((s, i) => console.log(`[${i + 1}] ${s.title}`))
}
node --env-file=.env.local try.js chat "Can I book a business class flight?"

It prints the answer, with markers like [1], and then one numbered line per chunk the function used, showing which document it came from. [1] in the answer means the first chunk.

A few things worth noticing:

  • The three comments map exactly onto the diagram at the top. The LLM call is the only swappable part; Claude, OpenAI or a local model would only change step 3.
  • ctx.supabase runs as the caller. Today that's the anonymous anon role. If you add sign-in (Multi-tenant RLS), changing 'publishable' to 'user' makes the search run as that user, which is what makes RLS work.
  • We use Gemini's Interactions API, which Google recommends for new projects. The older generateContent API still works too.

The prompt that stops hallucinations

The system prompt is the rules of an open-book exam: answer only from the pages you were given, write the page number next to every answer, and if it isn't on the pages, say "not in the book". A student with those rules can't bluff. Citations also let the user verify each claim, which is what clients actually pay for.

Ask it two kinds of questions:

  • "Can I book a business class flight?" Answered from the travel policy, with a [1] citation.
  • "Who won the 2022 World Cup?" "I don't know based on your documents." This refusal is the most important feature; a support bot that invents answers is a liability.

The app: the chat panel

The chat panel's TODO(08) is the last function in src/api.ts:

// src/api.ts
export async function askQuestion(question: string): Promise<ChatReply> {
  const { data, error } = await supabase.functions.invoke<ChatReply>('chat', { body: { question } })
  if (error) throw error
  return data!
}

That's the whole RAG loop from the browser's side: the same call try.js makes, and back comes { answer, sources }. Save, and ask the two questions above in the app's chat panel. Click the [1] chip under an answer to see the chunk it came from.

The citations are already handled by the starter's Answer component in src/components/Chat.tsx. The answer arrives as text like "…is not reimbursable [1].", and reply.answer.split(/(\[\d+\])/) splits it around every [1], [2]… The brackets in the pattern keep the markers in the result, so each one can be swapped for a small button. [1] points at sources[0], the first chunk the function retrieved, and clicking it opens that chunk under the answer.

Honesty checkpoint

Everything so far (pgvector, <=>, match_chunks) is stock Postgres plus an open-source extension, portable to any Postgres host. The RAG loop itself is about 40 lines of TypeScript.

Watch out

The app has one shared pile of documents, so every user sees everything. If several companies share the app, filter by owner inside the vector search, not after it. Multi-tenant RLS shows how.

Hybrid search: when vectors aren't enough

The IT helpdesk guide has a family of look-alike error codes: ERR_4012 for the VPN, ERR_4021 for printing, ERR_4102 for Wi-Fi, and a few more. To an embedding model they look almost the same. Embeddings capture meaning, and an error code has almost none; it's an exact token. The same goes for product SKUs, invoice numbers, function names and people's names.

See it for yourself. Embed the question:

node --env-file=.env.local try.js embed "What does ERR_4021 mean?"

Then search with it:

select title, left(content, 60) as preview, round(similarity::numeric, 3) as similarity
from match_chunks((select embedding from demo_queries where q = 'What does ERR_4021 mean?'), 0, 3);
Output (your scores may differ slightly)
titlepreviewsimilarity
it-helpdesk-guide.md## 2. VPN problems The company VPN is needed to reach intern0.729
it-helpdesk-guide.md## 3. Passwords Passwords expire every 180 days. Reset yours0.721
it-helpdesk-guide.md## 6. Printing and meeting-room screens Printers are on ever0.706
(3 rows)

The VPN section (ERR_4012) comes first, confidently. The right answer, the printing section, is third. In this small handbook the chat still gets it right, because it reads the top 5 chunks. With 10,000 chunks, third place becomes thirtieth, and the right chunk never reaches the model.

Picture two kinds of librarian. The first understands what you mean (vector search), but mixes up codes that look alike. The second only matches exact words (keyword search): they find "ERR_4021" instantly but have no idea what "cheap flights" means. Hybrid search asks both.

Postgres has keyword search built in. Add a full-text column that Postgres keeps in sync automatically, plus an index for it:

alter table document_chunks
add column fts tsvector generated always as (to_tsvector('english', content)) stored;
 
create index on document_chunks using gin (fts);

To merge the two lists, we only look at positions, because their scores can't be compared. Each chunk earns points from every list it appears in (1 / (50 + position)), and the points are added up. Ranked high by both librarians beats ranked first by one. This is called Reciprocal Rank Fusion (RRF).

chunkkeyword #vector #RRF scorefinalVPN setup guide220.0385#1IT Troubleshooting140.0381#2Remote Work—10.0196#3Travel Policy3—0.0189#4e.g. VPN setup guide: 1/(50+2) + 1/(50+2) = 0.0385 · Remote Work: 1/(50+1) + 0 = 0.0196

RRF: score = 1/(k + rank) from each list, summed (k = 50). Good in both lists beats great in one.

The whole thing is one SQL function. Paste it into the SQL Editor:

create or replace function hybrid_search(
  query_text text,
  query_embedding vector(768),
  match_count int default 5,
  full_text_weight float default 1,
  semantic_weight float default 1,
  rrf_k int default 50
)
returns table (id bigint, document_id bigint, title text, content text, score float)
language sql stable
as $$
  with full_text as (           -- librarian 1: exact words, numbered 1, 2, 3…
    select id,
           row_number() over (order by ts_rank_cd(fts, keywords) desc) as rank_ix
    from document_chunks,
         -- any word of the question may match, not all ("what does … mean" isn't in the docs)
         to_tsquery(replace(websearch_to_tsquery(query_text)::text, ' & ', ' | ')) as keywords
    where fts @@ keywords
    order by rank_ix
    limit match_count * 2
  ),
  semantic as (                 -- librarian 2: meaning, numbered 1, 2, 3…
    select id,
           row_number() over (order by embedding <=> query_embedding) as rank_ix
    from document_chunks
    order by rank_ix
    limit match_count * 2
  )
  select c.id, c.document_id, d.title, c.content,
         coalesce(1.0 / (rrf_k + full_text.rank_ix), 0.0) * full_text_weight +
         coalesce(1.0 / (rrf_k + semantic.rank_ix), 0.0) * semantic_weight as score
  from full_text
  full outer join semantic on full_text.id = semantic.id   -- keep chunks found by either list
  join document_chunks c on c.id = coalesce(full_text.id, semantic.id)
  join documents d on d.id = c.document_id
  order by score desc
  limit match_count;
$$;
 
grant execute on function hybrid_search to anon;

One detail in the keyword part: websearch_to_tsquery normally requires every word of the question to appear. Nobody's handbook contains the word "mean", so "What does ERR_4021 mean?" would find nothing. Swapping its & (and) for | (or) lets any word match, and chunks with more of the words still rank higher.

Try the same question:

select title, left(content, 60) as preview, round(score::numeric, 4) as score
from hybrid_search('What does ERR_4021 mean?',
                   (select embedding from demo_queries where q = 'What does ERR_4021 mean?'), 3);
Output
           title            |                           preview                            | score
----------------------------+--------------------------------------------------------------+--------
it-helpdesk-guide.md       | ## 6. Printing and meeting-room screens Printers are on ever | 0.0377
it-helpdesk-guide.md       | ## 2. VPN problems The company VPN is needed to reach intern | 0.0196
performance-review-guide.md| ## 3. Ratings | Rating | Meaning | |---|---| | 5 | Outstandi | 0.0196
(3 rows)

The printing section is now first, with about double the score of everything else, because it's the only chunk both librarians picked. (The ratings table sneaks in third because it contains "Meaning"; ranking by both lists keeps strays like that below the real match.)

It's security invoker too, so if you add Multi-tenant RLS later, it filters this search as well. Open the chat function in the dashboard and replace the ctx.supabase.rpc('match_chunks', …) call under // 1. RETRIEVE with this one. It no longer needs match_threshold, because hybrid search ranks instead of cutting off:

const { data: chunks } = await ctx.supabase.rpc('hybrid_search', {
  query_text: question, query_embedding, match_count: 5,
})

Click Deploy updates. The app doesn't change at all. Ask "What does ERR_4021 mean?" in the app and [1] is now the printing section, while "cheap flights" still finds "budget airfare".

hybrid_search has no similarity cutoff, so it always returns 5 chunks, even for "Who won the 2022 World Cup?". That's fine: the system prompt's "say I don't know" rule is what turns away off-topic questions, and it still does.

Honesty checkpoint

tsvector, GIN indexes and ts_rank_cd are built into Postgres. RRF is a published ranking algorithm; Supabase documents this exact hybrid pattern but doesn't own it.

Ready to build something real with this?

See how Postgres and Supabase power a full-stack Next.js app: auth, real-time data, file storage, and more.

Watch the Next.js + Supabase playlist
Note

The sections below aren't in the video. They take the app from a working demo to production. Click a heading to open it.

▸

Multi-tenant RLS: keeping each company's data private

So far the app has no users: every upload lands in one shared pile. Upload Globex's travel policy next to Northwind's and ask "Can I book a business class flight?": the answer cites both companies (Northwind says never, Globex says yes on long flights). For an app that several companies use, that means Globex is reading Northwind's internal policy. Swap "travel policy" for "contracts" or "salary bands" and it's a lawsuit.

The fix has three parts: users, an owner on every document, and a lock on every row.

Sign-in

Supabase Auth handles email and password sign-in. For the demo, turn off Confirm email (Authentication → Sign In / Providers → Email), because alice@northwind.test and bob@globex.test can't receive mail. Turn it back on before a real launch.

In the app, add a small sign-in form (email, password, Sign in and Sign up buttons) that calls supabase.auth.signUp, signInWithPassword and signOut, and show it until someone is signed in. supabase.functions.invoke sends the signed-in user's token on its own.

The functions now get a login token instead of just the publishable key. In ingest and chat, change withSupabase({ auth: 'publishable' }, ...) to withSupabase({ auth: 'user' }, ...): only signed-in users get in, and ctx.supabase runs as that user. Click Deploy updates for both. Leave Verify JWT with legacy secret off: auth: 'user' already checks the login token in your code.

An owner on every document

-- old uploads have no owner, so clear them out first (their chunks go too: on delete cascade)
delete from documents;
 
alter table documents
add column owner_id uuid not null
  references auth.users on delete cascade
  default auth.uid();
Output (your DELETE count will differ)
DELETE 2
ALTER TABLE

Signed-in users run as the authenticated role, so they need the permissions anon had, and anon loses them:

grant select, insert on documents, document_chunks to authenticated;
grant usage on sequence documents_id_seq, document_chunks_id_seq to authenticated;
grant execute on function match_chunks, hybrid_search to authenticated;
revoke select, insert on documents, document_chunks from anon;

default auth.uid() means whoever is signed in owns what they upload, so ingest doesn't change either. Sign up as Alice and upload Northwind's travel policy, then as Bob and upload Globex's.

Now ask Bob's question again: Northwind's policy still shows up. owner_id is only a label. Nothing checks it yet.

A lock on every row

Think of hotel keycards. Every guest's keycard opens only their own rooms. Row Level Security (RLS) gives every row a lock: before Postgres returns a row, it checks it against a rule, and rows that fail stay invisible.

Bob asks“travel policy?”JWT 🔑match_chunks()uses Bob's keycardGlobex travelowner: Bob✓ opensGlobex remoteowner: Bob✓ opensAcme travelowner: Alice✗ lockedAcme ITowner: Alice✗ locked

The search function borrows your keycard (security invoker). No matter what it searches for, it can only open your rooms.

The fix is three policies, one rule each: you manage your own documents, you read chunks of your own documents, and you add chunks only to your own documents.

alter table documents enable row level security;
alter table document_chunks enable row level security;
 
-- "to authenticated": anonymous requests get nothing at all.
-- "(select auth.uid())": evaluated once per query instead of once per row.
create policy "Users manage their own documents"
on documents for all to authenticated
using (owner_id = (select auth.uid()))
with check (owner_id = (select auth.uid()));
 
create policy "Users read chunks of their own documents"
on document_chunks for select to authenticated
using (document_id in (select id from documents where owner_id = (select auth.uid())));
 
create policy "Users insert chunks into their own documents"
on document_chunks for insert to authenticated
with check (document_id in (select id from documents where owner_id = (select auth.uid())));
Output
ALTER TABLE
ALTER TABLE
CREATE POLICY
CREATE POLICY
CREATE POLICY

match_chunks and hybrid_search didn't change at all. SQL functions run with the caller's permissions by default (security invoker), and the chat function calls them as the signed-in user, so RLS filters rows inside the vector search itself. Ask Bob's question again and only Globex's policy comes back. Switch to Alice and only Northwind's does. Same question, same code, different answers.

Watch out

Two things bring the leak back: marking a search function security definer, or calling it with the secret key (ctx.supabaseAdmin). Both skip RLS. enable row level security and create policy are plain Postgres; Supabase's part is the auth service that issues the JWT and the auth.uid() helper that reads it.

▸

Other distance operators: Euclidean and inner product

pgvector adds three operators. Each measures "how far apart" two vectors are, in a different way. With the items table and the query vector [1, 1, 0] from earlier:

-- <-> Euclidean distance: straight-line distance between the tips
select name, embedding <-> '[1, 1, 0]' as distance
from items order by embedding <-> '[1, 1, 0]';
 
-- <=> Cosine distance: the angle between the vectors (what our app uses)
select name, embedding <=> '[1, 1, 0]' as distance
from items order by embedding <=> '[1, 1, 0]';
 
-- <#> Negative inner product (negated so "order by ... asc" still means most similar first)
select name, embedding <#> '[1, 1, 0]' as distance
from items order by embedding <#> '[1, 1, 0]';

All three put the fruit first and the laptop last. When vectors are normalized (Gemini's are), all three produce the same ranking. Cosine is still the safe default for text, because it ignores length entirely. Whichever you pick, the index must match it; see the silent bug below.

▸

Vector indexes: HNSW vs IVFFlat

Without an index, every search compares the query against every row. It's like finding your friend in a 50,000-seat stadium by walking every row and checking every face. Fine at a house party of 50 people, hopeless in a stadium.

A handful of handbook chunks won't show the difference (just like the tiny tables in the Postgres notes), so build a separate benchmark table with 20,000 random vectors, about 60 MB, well within the free plan's 500 MB:

create table bench_vectors (id bigserial primary key, embedding vector(768));
 
-- "where g > 0" references the outer row, forcing a fresh random vector per row
insert into bench_vectors (embedding)
select (select array_agg(random()) from generate_series(1, 768) where g > 0)::vector(768)
from generate_series(1, 20000) g;
explain analyze
select id from bench_vectors
order by embedding <=> (select embedding from bench_vectors where id = 1)
limit 5;
Before the index (shape; times vary by machine)
 Limit  (actual time=95.1..95.2 rows=5 loops=1)
 ->  Sort  (actual time=95.1..95.1 rows=5 loops=1)
       Sort Key: ((bench_vectors.embedding <=> $0))
       ->  Seq Scan on bench_vectors  (actual time=0.02..84.7 rows=20000 loops=1)
Execution Time: 95.4 ms
-- Can take a minute on the free tier. If it runs out of memory: set maintenance_work_mem = '256MB';
create index on bench_vectors using hnsw (embedding vector_cosine_ops);
 
explain analyze
select id from bench_vectors
order by embedding <=> (select embedding from bench_vectors where id = 1)
limit 5;
After the index (shape)
 Limit  (actual time=1.8..2.0 rows=5 loops=1)
 ->  Index Scan using bench_vectors_embedding_idx on bench_vectors  (actual time=1.8..2.0 rows=5 loops=1)
       Order By: (embedding <=> $0)
Execution Time: 2.2 ms

Sort + Seq Scan over 20,000 rows became a single Index Scan touching a handful. pgvector has two index types, and each has a good real-world picture.

HNSW is asking for directions. You take the highway to the right city, then main roads to the right area, then local streets to the exact house. Each layer gets you closer, and you never visit most of the map.

Layer 2 · highwayLayer 1 · main roadsLayer 0 · every streetnearest matchStart on the sparse top layer, hop toward the target, drop down, repeat. Most points are never visited.

HNSW is like asking for directions: highway → main road → your street.

IVFFlat is sorted neighborhoods. Every vector is pre-sorted into neighborhoods (called lists). A search goes to the few neighborhoods nearest the target and knocks on only those doors.

skippedsearchedskippedskippedsearchedqueryVectors are pre-sorted into neighborhoods (“lists”). Only the few nearest neighborhoods get checked.

IVFFlat is like sorted neighborhoods: go to the nearest few, knock only on those doors.

HNSWIVFFlat
Search quality (recall)BetterGood, depends on tuning
Build speed / memorySlower, more memoryFaster, less memory
Build on an empty table?YesNo, needs data first to form neighborhoods
TuningMostly works out of the boxMust choose lists
Default choice?YesWhen build time/memory matters most

Both are approximate (ANN, approximate nearest neighbor). Asking for directions gets you to the right house almost every time, dramatically faster than knocking on every door, but very occasionally you end up one house off. For search results that trade is almost always worth it. To favor accuracy over speed per query:

set hnsw.ef_search = 100;  -- default is 40; higher = more accurate, slower

Add the real index to document_chunks too. It's tiny today, but correct for when it grows:

create index on document_chunks using hnsw (embedding vector_cosine_ops);

The silent pgvector bug

-- Index was built with vector_cosine_ops (for <=>). Now query with <-> instead:
explain
select id from bench_vectors
order by embedding <-> (select embedding from bench_vectors where id = 1)
limit 5;
Output (shape)
 Limit
 ->  Sort
       ->  Seq Scan on bench_vectors

No error, no warning, just silently slow. It's like a phone book sorted by last name: brilliant for last-name lookups, useless if you search by first name. The index's operator class must match the operator you query with:

Index operator classQuery operator
vector_cosine_ops<=>
vector_l2_ops<->
vector_ip_ops<#>
▸

Auto-embeddings: keeping vectors up to date

When someone edits a chunk's text, its embedding still describes the old text, and search quietly returns stale results. Re-embedding in app code everywhere text changes works until you forget one place. Instead, let the database keep its own search index honest.

It works like a restaurant kitchen. The waiter doesn't cook; they pin a ticket on the rail. Every few seconds the chef checks the rail, cooks what's there, and sends the plate out. If the chef drops a dish, the ticket is still on the rail and gets cooked again.

Row editedcustomer ordersUPDATE contentWaiterpins a tickettriggerTicket railorders wait herepgmq queueChefchecks every 10spg_cron → workerPlate outdish servedembedding savedfailed? ticket reappears → retried

The kitchen never forgets an order: if the chef drops a dish, the ticket is still on the rail and gets cooked again.

This is a trimmed-down, single-table version of Supabase's official automatic embeddings guide, which adds reusable helpers, batching and multi-table support. Queues, Cron and Vault are all available on the free plan, under Integrations in the dashboard.

1. The waiter: queue a job whenever content changes.

create extension if not exists pgmq;
create extension if not exists pg_net with schema extensions;
create extension if not exists pg_cron;
 
select pgmq.create('embedding_jobs');
 
-- Clear the stale embedding when content changes
create or replace function clear_embedding() returns trigger
language plpgsql as $$
begin
  new.embedding := null;
  return new;
end $$;
 
create trigger chunk_content_changed
before update of content on document_chunks
for each row execute function clear_embedding();
 
-- Pin a ticket for any chunk that needs an embedding
create or replace function queue_embedding() returns trigger
language plpgsql security definer set search_path = '' as $$
begin
  perform pgmq.send('embedding_jobs', jsonb_build_object('chunk_id', new.id));
  return new;
end $$;
 
create trigger chunk_needs_embedding
after insert or update of content on document_chunks
for each row when (new.embedding is null)
execute function queue_embedding();

The ingest function still embeds on insert, so the when (new.embedding is null) condition skips those rows. Rows added without an embedding, like bulk imports or edits in the Table Editor, get queued automatically.

2. The ticket rail: small wrappers only the worker can call.

create or replace function read_embedding_jobs(batch int default 10)
returns table (msg_id bigint, chunk_id bigint)
language sql security definer set search_path = '' as $$
  select msg_id, (message->>'chunk_id')::bigint
  from pgmq.read('embedding_jobs', 30, batch);  -- hidden for 30s; reappears if not deleted
$$;
 
create or replace function delete_embedding_job(id bigint)
returns boolean
language sql security definer set search_path = '' as $$
  select pgmq.delete('embedding_jobs', id);
$$;
 
revoke execute on function read_embedding_jobs, delete_embedding_job from public, anon, authenticated;
 
-- the worker runs as service_role, which needs its own permissions
grant execute on function read_embedding_jobs, delete_embedding_job to service_role;
grant select, update on document_chunks to service_role;

The 30-second visibility timeout is the "dropped dish" safety net: a job that's read but never deleted reappears on the rail and gets retried.

3. The chef: a worker Edge Function.

// Edge Function: embed-worker
import { withSupabase } from 'npm:@supabase/server@1'
import { GoogleGenAI } from 'npm:@google/genai'
 
const ai = new GoogleGenAI({ apiKey: Deno.env.get('GEMINI_API_KEY') })
 
// ...the same embed() helper as in ingest...
 
export default {
  // 'secret': only callers with the secret key. A background job, never the browser.
  fetch: withSupabase({ auth: 'secret' }, async (_req, ctx) => {
    const db = ctx.supabaseAdmin  // runs as service_role, which skips RLS
 
    const { data: jobs } = await db.rpc('read_embedding_jobs', { batch: 10 })
 
    for (const job of jobs ?? []) {
      const { data: chunk } = await db
        .from('document_chunks').select('content').eq('id', job.chunk_id).single()
      if (chunk) {
        const [embedding] = await embed([chunk.content])
        await db.from('document_chunks').update({ embedding }).eq('id', job.chunk_id)
      }
      await db.rpc('delete_embedding_job', { id: job.msg_id })
    }
    return Response.json({ processed: jobs?.length ?? 0 })
  }),
}

Deploy it as a new function named embed-worker, and turn off Verify JWT with legacy secret like the others (the secret key isn't a login token either).

4. The chef's timer: run it every 10 seconds.

-- Keep secrets in Vault instead of hardcoding them in the cron job
select vault.create_secret('https://<project-ref>.supabase.co', 'project_url');
select vault.create_secret('<secret-key>', 'secret_key');  -- sb_secret_..., under Connect / API keys
 
select cron.schedule('process-embeddings', '10 seconds', $$
  select net.http_post(
    url := (select decrypted_secret from vault.decrypted_secrets where name = 'project_url')
           || '/functions/v1/embed-worker',
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'apikey',
        (select decrypted_secret from vault.decrypted_secrets where name = 'secret_key')
    ),
    body := '{}'::jsonb
  );
$$);

Try it: edit a chunk's content in the Table Editor. Its embedding turns NULL immediately, and about 10 seconds later it's filled again. Integrations → Cron shows the job and its run history. No app code changed.

Honesty checkpoint

pgmq, pg_net, pg_cron and Vault are open-source Postgres extensions. Supabase pre-installs them and adds the managed Edge Functions plus the Queues and Cron dashboards.

▸

Everything so far is classic RAG: every question triggers exactly one search, using the user's exact words. Real questions often need more than one lookup, or a better query than the one the user typed.

Try "I want to fly to our Berlin offsite for $2,500. What class can I book, and who has to sign off?" One search with that whole sentence finds the travel policy but misses the approvals section, which is worded differently and sits elsewhere in the handbook. You get half an answer.

Classic RAG is a vending machine: press one button, get one thing. Agentic RAG is a research assistant with a library card: they read your question, decide what to look up, read what they found, notice something is missing, go back for another book, and then write the answer.

Classic RAG · vending machine (one button, one result)Questionthe whole sentence1 searchexact user wordingLLM answers½ answerclass ✓ approver ✗Agentic RAG · research assistant (decides what to look up)LLM plans2 parts → 2 searchessearch_docs“flight class policy”search_docs“approval over $2,000”Enough info?no → search againFull answerboth parts, citedloop until satisfied (capped at 6 steps)

“What class can I book, and who signs off?” Classic RAG searches once and gets half the answer. The agent splits the question and searches for each part.

In code, "giving the AI a library card" means giving the LLM tools. It can call search_docs (our hybrid_search) as many times as it needs, with queries it writes itself:

// Edge Function: agent
import { withSupabase } from 'npm:@supabase/server@1'
import { GoogleGenAI } from 'npm:@google/genai'
 
const ai = new GoogleGenAI({ apiKey: Deno.env.get('GEMINI_API_KEY') })
 
// ...the same embed() helper as in ingest...
 
const tools = [
  {
    type: 'function',
    name: 'search_docs',
    description:
      "Hybrid keyword + semantic search over the user's documents. Returns numbered chunks. " +
      'Use short, specific queries. Call it multiple times for multi-part questions.',
    parameters: {
      type: 'object',
      properties: { query: { type: 'string', description: 'A focused search query' } },
      required: ['query'],
    },
  },
  {
    type: 'function',
    name: 'list_documents',
    description: "List the titles of all the user's documents.",
    parameters: { type: 'object', properties: {} },
  },
]
 
const SYSTEM_PROMPT = `You are an assistant for the user's own documents.
Break the question into parts and search for each part separately.
If results don't fully answer a part, rephrase and search again.
Answer ONLY from search results, cite sources like [3], and say "I don't know" for anything not found.`
 
export default {
  fetch: withSupabase({ auth: 'publishable' }, async (req, ctx) => {
    const supabase = ctx.supabase
    const { question } = await req.json()
 
    const sources: any[] = []   // every chunk seen, numbered globally for citations
    const trace: string[] = []  // tool calls, shown in the UI
 
    async function runTool(name: string, args: any): Promise<string> {
      if (name === 'list_documents') {
        const { data } = await supabase.from('documents').select('title')
        return (data ?? []).map((d) => d.title).join('\n')
      }
      // search_docs — the same hybrid_search (filtered by RLS, if you added it)
      const [query_embedding] = await embed([args.query])
      const { data } = await supabase.rpc('hybrid_search', {
        query_text: args.query, query_embedding, match_count: 3,
      })
      return (data ?? [])
        .map((c) => {
          sources.push(c)
          return `[${sources.length}] (${c.title})\n${c.content}`
        })
        .join('\n\n')
    }
 
    let input: any = question
    let previous_interaction_id: string | undefined
 
    // The agent loop: keep going while the model asks for tools (capped for cost/safety)
    for (let step = 0; step < 6; step++) {
      const interaction = await ai.interactions.create({
        model: 'gemini-3.5-flash-lite',
        system_instruction: SYSTEM_PROMPT,  // system prompt + tools are re-sent every turn
        tools,
        input,
        previous_interaction_id,            // the server remembers the conversation so far
      })
 
      const calls = interaction.steps.filter((s) => s.type === 'function_call')
      if (calls.length === 0) {
        return Response.json({ answer: interaction.output_text, sources, trace })
      }
 
      // Run every tool the model asked for, then send all results back in one turn
      input = []
      for (const call of calls) {
        trace.push(`${call.name}(${JSON.stringify(call.arguments)})`)
        input.push({
          type: 'function_result',
          name: call.name,
          call_id: call.id,
          result: [{ type: 'text', text: await runTool(call.name, call.arguments) }],
        })
      }
      previous_interaction_id = interaction.id
    }
 
    return Response.json({ answer: "I couldn't finish researching that.", sources, trace })
  }),
}

Deploy it as a new function named agent, and turn off Verify JWT with legacy secret, like chat.

Three ideas carry the whole thing:

  1. Tools. search_docs is just the hybrid_search SQL function from earlier. The agent's "powers" are functions you already wrote.
  2. The loop. Call the model; if it asks for tools, run them and send the results back; repeat until it answers. That loop is an agent.
  3. The step cap. Six steps maximum. Agents can loop, so always bound the cost.

If you added RLS (and switched this function to auth: 'user' too), it still protects everything: the agent searches as the signed-in user, so it can never find another tenant's data whatever it searches for.

Ask the Berlin question again and the trace shows the agent splitting it on its own:

Research steps (shape)
search_docs({"query":"flight class travel policy"})
search_docs({"query":"travel approval over $2,000"})

The answer now covers both parts, each with its own citation.

Note

Agentic isn't always better. It's slower and costs more (several LLM calls per question), so classic RAG remains the right default for simple lookups. A toggle between the two is realistic product design.

Where MCP fits: RAG gives an AI searchable memory; MCP (Model Context Protocol) is a standard way to give any AI client tools. Here the tools live inside our own function; exposing them over MCP would let other AI apps use them too.

▸

Interview drill: RAG questions these notes answer

Who did what: Postgres vs pgvector vs Supabase

Postgres, pgvector, Supabase and Gemini each did a different part of the app. Here's the honest breakdown.

Stock Postgres, works anywhere:

  • SQL functions (match_chunks, hybrid_search) and security invoker behavior
  • Row Level Security
  • Full-text search: tsvector, generated columns, GIN indexes, ts_rank_cd

pgvector, open source, works on any Postgres host:

  • The vector type and the <=> operator (plus <->, <#>, and HNSW and IVFFlat indexes)

What Supabase itself adds:

  • Auth and auth.uid(), which turn RLS into per-tenant retrieval with zero app code
  • Edge Functions as the home for the ingest and chat code
  • .rpc() client calls, plus the dashboard's SQL Editor and Table Editor
  • A built-in gte-small embedding model, as a private, no-key alternative to Gemini
  • Pre-installed extensions for the production extras: pgmq (queues), pg_cron (scheduling), pg_net (HTTP from SQL) and Vault (secrets)

Not a database thing at all: the AI models. We used Gemini's free tier for both embeddings and answers. You can swap either one, but changing the embedding model means re-embedding every chunk and updating vector(768) to the new size.

pgvector vs a dedicated vector database

Think family car vs freight truck. pgvector, inside the Postgres you already have, handles up to low millions of vectors comfortably, with your data, auth and search in one place and one transaction. Most apps, and nearly every freelance project, never outgrow it. Dedicated vector databases (Pinecone, Qdrant, Weaviate) make sense at hundreds of millions of vectors, very high query volume, or when search has to scale separately from your app. Buy the truck when you're actually hauling freight.

What to learn next: the collapsed sections above (vector indexes, auto-updating embeddings, agentic RAG), then reranking retrieved chunks with a cross-encoder, measuring RAG quality (faithfulness, context precision and recall, with tools like Ragas), parsing PDFs, and GraphRAG.

Want to build a full-stack app on this same database?

See how Postgres + Supabase power a real Next.js project: auth, RLS, file uploads, and more, end to end.

Watch the Next.js + Supabase playlist