
Adding Semantic Search to Your AI Agent with Supabase Vector Database (pgvector)
In our previous tutorial on turning your Python AI agent into an asynchronous FastAPI backend with SSE streaming, we exposed our multi-step reasoning loop over HTTP so web and mobile clients can stream tool executions in real time. However, if you look inside both our Python FastAPI service (agent/tools.py) and our Next.js backend (src/lib/agent/tools.ts), one critical piece of our DevPulse agent is still faking its job: searchKnowledgeBase is returning a hardcoded string about docs/api/rate-limits.md instead of searching real documentation.
To add production-grade semantic search to an AI agent using Supabase, you enable PostgreSQL's pgvector extension, store chunked documentation alongside 1536-dimensional embeddings (text-embedding-3-small) indexed with an HNSW (vector_cosine_ops) index, and expose a PostgreSQL RPC function (match_documentation_chunks) that filters results by a minimum cosine similarity threshold. Wiring this single RPC function into your Next.js and Python FastAPI tools lets your agent retrieve conceptually matching technical docs in under 25 milliseconds—even when the user's phrasing shares zero exact keywords with the documentation.
Back in Post 5, we covered the foundational theory of Retrieval Augmented Generation (RAG). Today, in Post 29, we replace our last remaining mock function with a live, shared Supabase vector search pipeline that serves both our Next.js TypeScript stack and our Python FastAPI stack.
Why SQL ILIKE Keyword Search Fails for Technical Agents
When developers first connect an agent tool to a PostgreSQL documentation table, they are often tempted to run a standard full-text or ILIKE query:
sql SELECT doc_path, content FROM docs WHERE content ILIKE '%' || query || '%' LIMIT 3;
That approach breaks down immediately in real developer support scenarios:
- Vocabulary Mismatch: A developer asks, 'Why are our client requests getting throttled when refreshing tokens?' The user wrote 'throttled', while your official markdown guide only uses the phrase 'HTTP 429 rate-limit burst policy'. A keyword search for
'throttled'returns zero rows. - No Relevance Ranking by Meaning: If a user searches for
'authentication timeout', a keyword query gives equal weight to a passing mention in a changelog and the actual authentication architecture guide. - Context Window Pollution: Returning entire unchunked markdown files dumps 3,000+ tokens into the agent's scratchpad when the model only needed the 180-token paragraph explaining the
Retry-Afterheader.
Semantic vector search solves all three problems by converting both your documentation paragraphs and the agent's search query into high-dimensional numerical vectors (1536 floating-point numbers) where geometric closeness (cosine similarity) directly represents conceptual similarity.

How the Supabase pgvector Semantic Search Pipeline Works
Because we already set up Supabase PostgreSQL in Post 24 to store agent_sessions and agent_messages, we do not need to pay for or sync a separate vector database like Pinecone or Weaviate. Supabase includes pgvector natively inside the same PostgreSQL instance.
Here is how our semantic search tool executes a query from either our Next.js agent or our Python FastAPI agent:
mermaid graph TD A[Agent Tool Call: searchKnowledgeBase] --> B[OpenAI Embeddings API: text-embedding-3-small] B -->|1536-Dim Float Vector| C[Supabase PostgreSQL RPC: match_documentation_chunks] C --> D[HNSW Index Scan: vector_cosine_ops] D --> E{Cosine Similarity >= 0.72?} E -->|No: Below Threshold| F[Discard Low-Relevance Noise] E -->|Yes: Above Threshold| G[Return Top-K Ranked DTO Chunks to Agent]
Architecture Flow Summary: Agent Tool Call → OpenAI text-embedding-3-small API → Supabase RPC (match_documentation_chunks) → HNSW Cosine Similarity Index Scan → Top-K Ranked DTO Chunks.
Zahid's Engineering Perspective: Why My Early ivfflat Index Missed Obvious Docs and Why Thresholds Matter
When I first replaced a mocked search function with Supabase pgvector in an early RAG prototype, I hit two bugs that made me question whether semantic search even worked.
First, I copied an older SQL snippet that created an USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100) index on a table that only had 65 documentation chunks. When I tested the agent with a query that matched a paragraph almost word-for-word, the RPC returned only 1 chunk and completely missed the second relevant paragraph! Why? Because ivfflat divides your vector space into lists (100 clusters) and, by default, only searches probes = 1 cluster at query time. When you have fewer rows than lists—or when you insert new rows after creating an ivfflat index—centroids become skewed and recall drops sharply. Switching the index to USING hnsw (embedding vector_cosine_ops) immediately restored 100% recall on my dataset with zero manual probe tuning.
Second, my initial SQL function did not have a WHERE 1 - (dc.embedding <=> query_embedding) >= match_threshold filter—it simply ran ORDER BY embedding <=> query_embedding LIMIT 3. When I asked the agent a question about CSS styling that was not in our backend documentation at all, the database dutifully returned the 3 closest backend docs with similarity scores of 0.31, wasting 600 input tokens and confusing the LLM. Adding match_threshold = 0.72 ensured that irrelevant queries return [] immediately, letting the agent know that no documentation matched.
Step 1: Enabling pgvector, Creating the HNSW Index, and Writing the RPC in Supabase
Open the SQL Editor in your Supabase dashboard and run the following migration. We enable the vector extension, create the documentation_chunks table, build an HNSW (Hierarchical Navigable Small World) index for fast approximate nearest-neighbor search, and create the match_documentation_chunks RPC function:
`sql -- supabase/migrations/20261117_pgvector_semantic_search.sql create extension if not exists vector;
create table if not exists public.documentation_chunks ( id uuid primary key default gen_random_uuid(), doc_path text not null, heading text not null, content text not null, token_count int not null default 0, embedding vector(1536) not null, updated_at timestamptz not null default now(), unique (doc_path, heading) );
create index if not exists idx_docs_embedding_hnsw on public.documentation_chunks using hnsw (embedding vector_cosine_ops) with (m = 16, ef_construction = 64);
create or replace function public.match_documentation_chunks( query_embedding vector(1536), match_threshold float default 0.72, match_count int default 3 ) returns table ( id uuid, doc_path text, heading text, content text, similarity float ) language sql stable as $$ select dc.id, dc.doc_path, dc.heading, dc.content, 1 - (dc.embedding <=> query_embedding) as similarity from public.documentation_chunks dc where 1 - (dc.embedding <=> query_embedding) >= match_threshold order by dc.embedding <=> query_embedding asc limit greatest(least(match_count, 5), 1); $$; `
Examine three critical engineering details in this SQL migration:
- Why
<=>(Cosine Distance) and1 - (dc.embedding <=> query_embedding): Inpgvector, the<=>operator calculates cosine distance (where0means identical vectors and2means opposite vectors). Subtracting the distance from1converts it into a human-readable cosine similarity score between0.0and1.0. Notice that theORDER BY dc.embedding <=> query_embedding ASCclause uses the raw<=>operator directly so PostgreSQL utilizes ouridx_docs_embedding_hnswindex. - Why HNSW Instead of IVFFlat: Unlike legacy
ivfflatindexes—which require thousands of existing rows to calculate cluster centroids and degrade whenever you insert new documents without rebuilding—anhnswindex works accurately even on day one with 20 rows and updates dynamically as you insert new documentation. - Omitting the
embeddingColumn fromreturns table: A 1536-float vector takes roughly 6KB to 12KB of JSON text when serialized over HTTP. Our RPC function explicitly selects onlyid,doc_path,heading,content, andsimilarityso the raw float arrays never leave the database server.
Step 2: Wiring Semantic Vector Search into Next.js (vector-search.ts)
Let us create src/lib/agent/vector-search.ts in our Next.js project so our searchKnowledgeBase tool can embed incoming queries using OpenAI's text-embedding-3-small model and invoke match_documentation_chunks:
`typescript // src/lib/agent/vector-search.ts import 'server-only'; import OpenAI from 'openai'; import { createClient } from '@supabase/supabase-js'; import { type SearchDocsOutputDTO } from './type-contracts';
function getOpenAIClient(): OpenAI { const apiKey = process.env.OPENAI_API_KEY; if (!apiKey) { throw new Error('Missing OPENAI_API_KEY for vector embeddings.'); } return new OpenAI({ apiKey }); }
function getSupabaseAdmin() { const url = process.env.SUPABASE_URL; const key = process.env.SUPABASE_SERVICE_ROLE_KEY; if (!url || !key) { throw new Error('Missing SUPABASE_URL or SUPABASE_SERVICE_ROLE_KEY.'); } return createClient(url, key, { auth: { persistSession: false, autoRefreshToken: false }, }); }
export async function createQueryEmbedding(text: string): Promise<number[]> { const openai = getOpenAIClient(); const cleanInput = text.replace(/\n+/g, ' ').trim().slice(0, 2000);
const response = await openai.embeddings.create({ model: 'text-embedding-3-small', input: cleanInput, });
const vector = response.data[0]?.embedding; if (!vector) { throw new Error('Failed to generate embedding vector from OpenAI.'); } return vector; }
interface MatchedChunkRow { id: string; doc_path: string; heading: string; content: string; similarity: number; }
export async function semanticSearchDocumentation( query: string, topK = 3, minSimilarity = 0.72 ): Promise { const queryEmbedding = await createQueryEmbedding(query); const supabase = getSupabaseAdmin();
const { data, error } = await supabase.rpc('match_documentation_chunks', { query_embedding: queryEmbedding, match_threshold: minSimilarity, match_count: topK, });
if (error) {
throw new Error(Supabase pgvector RPC error: ${error.message});
}
const rows = (data ?? []) as MatchedChunkRow[];
return {
queryUsed: query,
topK,
matchedChunks: rows.map((row) => ({
docPath: ${row.doc_path}#${row.heading},
score: Number(row.similarity.toFixed(3)),
excerpt: row.content.slice(0, 500),
})),
};
}
`
Now inside src/lib/agent/tools.ts, replace the mocked searchKnowledgeBase return object with await semanticSearchDocumentation(args.query, args.topK).
Step 3: Connecting Our Python FastAPI Agent to the Same Supabase Vector Store
Because our vector store lives in PostgreSQL, our Python FastAPI backend from Post 28 can call the exact same match_documentation_chunks RPC! Install the supabase and openai packages in your Python virtual environment (pip install supabase openai) and update execute_search_docs inside agent/tools.py:
`python
agent/tools.py
import os from typing import Any, Dict from openai import AsyncOpenAI from supabase import create_client, Client from agent.tools import SearchDocsInput
def get_supabase_client() -> Client: url = os.environ.get('SUPABASE_URL') key = os.environ.get('SUPABASE_SERVICE_ROLE_KEY') if not url or not key: raise RuntimeError('Missing SUPABASE_URL or SUPABASE_SERVICE_ROLE_KEY.') return create_client(url, key)
async def execute_search_docs(args: SearchDocsInput) -> Dict[str, Any]: openai_key = os.environ.get('OPENAI_API_KEY') if not openai_key: raise RuntimeError('Missing OPENAI_API_KEY for embedding generation.')
async_openai = AsyncOpenAI(api_key=openai_key)
clean_query = args.query.replace('\n', ' ').strip()[:1000]
emb_response = await async_openai.embeddings.create(
model='text-embedding-3-small',
input=clean_query,
)
query_vector = emb_response.data[0].embedding
supabase = get_supabase_client()
rpc_response = supabase.rpc(
'match_documentation_chunks',
{
'query_embedding': query_vector,
'match_threshold': 0.72,
'match_count': args.top_k,
},
).execute()
rows = rpc_response.data or []
matched_chunks = [
{
'doc_path': f"{row['doc_path']}#{row['heading']}",
'score': round(float(row['similarity']), 3),
'excerpt': str(row['content'])[:500],
}
for row in rows
]
return {
'query_used': args.query,
'top_k': args.top_k,
'matched_chunks': matched_chunks,
}
`
Step 4: Seeding Real Documentation Chunks into Supabase (scripts/seed-docs.py)
To populate documentation_chunks with real project docs, run this concise seeding script in your Python environment:
`python
scripts/seed-docs.py
import os from dotenv import load_dotenv from openai import OpenAI from supabase import create_client
load_dotenv()
DOCS_TO_SEED = [ { 'doc_path': 'docs/api/rate-limits.md', 'heading': 'sliding-window-burst-policy', 'content': ( 'The API gateway enforces a sliding-window rate limit of 5 agent turns per 60 seconds ' 'and a daily quota of 40 turns per 24 hours. Exceeding either limit returns HTTP 429 ' 'Too Many Requests with a standard Retry-After header indicating cooldown seconds.' ), }, { 'doc_path': 'docs/auth/token-refresh.md', 'heading': 'concurrent-refresh-locking', 'content': ( 'When an access token expires, clients must use a mutex lock so parallel requests ' 'do not fire concurrent token refresh calls. Firing more than 3 refresh calls within ' '2 seconds triggers the auth gateway burst limiter (Issue ISS-402).' ), }, { 'doc_path': 'docs/streaming/sse-keepalive.md', 'heading': 'proxy-buffering-and-heartbeats', 'content': ( 'All Server-Sent Events (SSE) endpoints emit a heartbeat frame every 3000ms and include ' 'X-Accel-Buffering: no plus Cache-Control: no-cache, no-transform headers to prevent ' 'Nginx and CDN response buffering during multi-step tool executions.' ), }, ]
def seed_documentation() -> None: openai_client = OpenAI(api_key=os.environ['OPENAI_API_KEY']) supabase = create_client( os.environ['SUPABASE_URL'], os.environ['SUPABASE_SERVICE_ROLE_KEY'], )
for item in DOCS_TO_SEED:
combined_text = f"{item['doc_path']} ({item['heading']}): {item['content']}"
emb = openai_client.embeddings.create(
model='text-embedding-3-small',
input=combined_text,
)
vector = emb.data[0].embedding
supabase.table('documentation_chunks').upsert(
{
'doc_path': item['doc_path'],
'heading': item['heading'],
'content': item['content'],
'token_count': len(combined_text.split()),
'embedding': vector,
},
on_conflict='doc_path,heading',
).execute()
print(f"Seeded vector chunk: {item['doc_path']}#{item['heading']}")
if name == 'main': seed_documentation() `

Common Mistakes When Adding pgvector Semantic Search to an Agent
Before we deploy our projects to Vercel in Post 30, make sure your vector search pipeline avoids these 4 common anti-patterns:
- Mixing Embedding Models Between Ingestion and Query Time: If you seed your
documentation_chunkstable usingtext-embedding-3-smalland accidentally query it using a different model, the 1536-dimensional coordinate spaces will not align—and your cosine similarity scores will be meaningless garbage. Always lock the embedding model ID ('text-embedding-3-small') as a shared constant. - Omitting the
match_thresholdFilter in SQL: Never rely onLIMIT 3alone in a RAG tool. Without aWHERE 1 - (dc.embedding <=> query_embedding) >= match_thresholdclause, every query—no matter how off-topic—injects 3 noisy chunks into your agent's scratchpad. - Returning the Raw
embeddingVector Column to the Application: Never runSELECT *inside your vector search RPC. Sending three 1536-float arrays over the network adds ~30KB of useless JSON parsing overhead on every tool call. Only returnid,doc_path,heading,content, andsimilarity. - Splitting Markdown Files by Blind Character Counts: Cutting a technical guide every 500 characters without respecting Markdown headings (
##) often slices a TypeScript or Python code block in half, separating the explanation from the code. Always chunk documentation by semantic section or heading boundaries.
Frequently Asked Questions
Why did we choose vector(1536) with text-embedding-3-small instead of 3072 dimensions?
OpenAI's text-embedding-3-small (1536 dimensions) costs 5x less than text-embedding-3-large, generates embeddings with lower latency, and fits natively within pgvector's standard 2000-dimension HNSW index limit for full-precision vector columns—making it the sweet spot for production agent documentation search.
What is a good match_threshold value for text-embedding-3-small cosine similarity?
In practice with technical documentation and text-embedding-3-small, relevant conceptual matches typically score between 0.70 and 0.88, while unrelated queries score below 0.55. Starting with match_threshold = 0.70 or 0.72 filters out noise while preserving paraphrased developer questions.
Do I need to rebuild the HNSW index in Supabase every time I upsert new documentation chunks?
No. Unlike ivfflat indexes, PostgreSQL hnsw indexes in pgvector update dynamically as you INSERT, UPDATE, or DELETE rows in documentation_chunks, so your seed-docs.py script can upsert updated markdown sections anytime without index downtime.
Need a High-Performance Web App or Custom AI Solution?
I help founders, businesses, and engineering teams build lightning-fast web applications, resilient backend architectures, and intelligent AI workflows. Have an idea in mind? Let’s bring it to life.
Found this article helpful?
Give some claps to support more in-depth engineering logs!
Zahid Hasan Tonmoy
Author & Developer
MERN Full Stack Developer & AI Agent Developer based in Dhaka, Bangladesh. Writing about web development, React, PostgreSQL and my learning journey.