Adding Semantic Search to Your AI Agent with Supabase Vector Database (pgvector)

Adding Semantic Search to Your AI Agent with Supabase Vector Database (pgvector)

Z
Zahid Hasan Tonmoy
November 17, 2026
12 min read
🇧🇩 বাংলায় পড়ুন
|
0 views
Audio Overview & Podcast
~1 min quick overview
0%

Adding Semantic Search to Your AI Agent with Supabase Vector Database (pgvector)
Adding Semantic Search to Your AI Agent with Supabase Vector Database (pgvector)

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:

  1. 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.
  2. 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.
  3. 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-After header.

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.

3D glassmorphism visualization of a query beam transforming into vector coordinates inside an HNSW geometric lattice to illuminate the three closest emerald documentation slabs
3D glassmorphism visualization of a query beam transforming into vector coordinates inside an HNSW geometric lattice to illuminate the three closest emerald documentation slabs

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:

  1. Why <=> (Cosine Distance) and 1 - (dc.embedding <=> query_embedding): In pgvector, the <=> operator calculates cosine distance (where 0 means identical vectors and 2 means opposite vectors). Subtracting the distance from 1 converts it into a human-readable cosine similarity score between 0.0 and 1.0. Notice that the ORDER BY dc.embedding <=> query_embedding ASC clause uses the raw <=> operator directly so PostgreSQL utilizes our idx_docs_embedding_hnsw index.
  2. Why HNSW Instead of IVFFlat: Unlike legacy ivfflat indexes—which require thousands of existing rows to calculate cluster centroids and degrade whenever you insert new documents without rebuilding—an hnsw index works accurately even on day one with 20 rows and updates dynamically as you insert new documentation.
  3. Omitting the embedding Column from returns table: A 1536-float vector takes roughly 6KB to 12KB of JSON text when serialized over HTTP. Our RPC function explicitly selects only id, doc_path, heading, content, and similarity so 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() `

Dark frosted glass Supabase pgvector vault serving filtered high-similarity documentation chunks to both a Next.js node and a Python FastAPI node
Dark frosted glass Supabase pgvector vault serving filtered high-similarity documentation chunks to both a Next.js node and a Python FastAPI node

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:

  1. Mixing Embedding Models Between Ingestion and Query Time: If you seed your documentation_chunks table using text-embedding-3-small and 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.
  2. Omitting the match_threshold Filter in SQL: Never rely on LIMIT 3 alone in a RAG tool. Without a WHERE 1 - (dc.embedding <=> query_embedding) >= match_threshold clause, every query—no matter how off-topic—injects 3 noisy chunks into your agent's scratchpad.
  3. Returning the Raw embedding Vector Column to the Application: Never run SELECT * 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 return id, doc_path, heading, content, and similarity.
  4. 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.

🟢 Available for Freelance & Contract Work

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.

Fast MVP Launch (2–4 weeks)
AI Agent & LLM API Integration
100/100 Core Web Vitals & SEO
Production-Ready Clean Architecture

Found this article helpful?

Give some claps to support more in-depth engineering logs!

0 views
Z

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.

Related Articles

Stay Updated

Subscribe to get insights on full-stack architecture, AI agent engineering, and web development.