Building a RAG Pipeline From Scratch: Hybrid Search with pgvector and Supabase

When I set out to build the AI chat assistant on this site, I had one goal: give recruiters and visitors a way to actually ask questions about my work and get accurate, grounded answers — not hallucinated ones. That meant building a real retrieval-augmented generation (RAG) pipeline, not just wiring an API key to a system prompt.

This post walks through the architecture I landed on: a hybrid search system combining full-text and semantic search, fused with Reciprocal Rank Fusion (RRF), running on Postgres via Supabase with pgvector and HNSW indexing.

Why Hybrid Search?

Pure semantic (vector) search is great at understanding intent and meaning, but it can miss exact keyword matches — project names, acronyms, or specific technologies that a recruiter might search for verbatim. Pure full-text search is the opposite: precise on exact terms, blind to paraphrasing.

Hybrid search runs both in parallel and merges the results, so a query like "did he build anything with Django" retrieves relevant chunks whether the source content says "Django," "Python web framework," or something semantically adjacent.

The Database Layer

Everything lives in a single knowledge_chunks table in Supabase, with two extensions doing the heavy lifting: pgvector for embeddings and pg_trgm for fuzzy text matching.

CREATE SCHEMA IF NOT EXISTS extensions;

CREATE EXTENSION IF NOT EXISTS vector  SCHEMA extensions;
CREATE EXTENSION IF NOT EXISTS pg_trgm SCHEMA extensions;

CREATE TABLE IF NOT EXISTS knowledge_chunks (
    id              BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,

    section         TEXT NOT NULL,
    title           TEXT NOT NULL,
    content         TEXT NOT NULL,

    -- Stable deterministic hash for idempotent upserts
    content_hash    TEXT NOT NULL,

    fts             TSVECTOR GENERATED ALWAYS AS (
                        to_tsvector(
                            'english',
                            COALESCE(title, '') || ' ' || COALESCE(content, '')
                        )
                    ) STORED,

    embedding       VECTOR(1536) NOT NULL,

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

A few deliberate design choices here:

• content_hash is a SHA-256 hash of the section, title, and content. It's the key to making ingestion idempotent — re-running the pipeline on unchanged content is a no-op, and changed content naturally produces a new hash, triggering a clean upsert instead of a duplicate row.
• fts is a generated column, not something I populate manually. Postgres keeps it in sync automatically every time a row is inserted or updated, which removes an entire category of bugs where the search index quietly drifts from the source content.
• embedding is VECTOR(1536), sized to match OpenAI's text-embedding-3-small output dimensions.

On top of that, three indexes do the actual performance work:

-- Unique hash index for idempotent ingestion
CREATE UNIQUE INDEX IF NOT EXISTS knowledge_chunks_content_hash_idx
    ON knowledge_chunks (content_hash);

-- Full-text Search
CREATE INDEX IF NOT EXISTS knowledge_chunks_fts_idx
    ON knowledge_chunks
    USING GIN(fts);

-- Semantic vector search
CREATE INDEX IF NOT EXISTS knowledge_chunks_embedding_idx
    ON knowledge_chunks
    USING HNSW(embedding vector_cosine_ops);

-- Fuzzy keyword matching
CREATE INDEX IF NOT EXISTS knowledge_chunks_content_trgm_idx
    ON knowledge_chunks
    USING GIN(content gin_trgm_ops);

The HNSW (Hierarchical Navigable Small World) index is what makes vector search fast at scale — it trades a small amount of recall for a large gain in query speed compared to a brute-force nearest-neighbor scan, which matters even at modest row counts once you're running searches on every chat message.

Row Level Security is enabled with a simple public-read policy, since this data powers a public-facing chat feature and doesn't need per-user scoping.

The Ingestion Pipeline

Content lives as markdown files in content/knowledge-base/, one file per section (projects, experience, skills, etc.). The ingestion script reads them all, chunks them, embeds them, and upserts:

async function main() {
    console.log("🔄 Starting knowledge base ingestion...
")
    const supabase = createServerSupabaseClient()

    const files = loadMarkdownFiles()

    let totalChunks = 0
    const activeHashes: string[] = []

    for (const { section, content } of files) {
        const { inserted, hashes } = await ingestFile(section, content)
        totalChunks += inserted
        activeHashes.push(...hashes)
    }

    if (activeHashes.length === 0) {
        console.warn("⚠️ No active hashes generated. Skipping cleanup.")
    } else {
        const { error } = await supabase.rpc("delete_stale_chunks", {
            active_hashes: activeHashes,
        })

        if (error) {
            console.error("❌ Cleanup error:", error.message)
        } else {
            console.log("🧹 Removed stale chunks")
        }
    }

    console.log(`
✅ Done! Upserted ${totalChunks} chunks total.`)
}

The cleanup step matters more than it looks. Every ingestion run collects the hashes of everything that's currently in the source markdown, then calls a Postgres function to delete anything in the table whose hash isn't in that active set:

CREATE OR REPLACE FUNCTION delete_stale_chunks(
    active_hashes TEXT[]
)
RETURNS VOID
LANGUAGE SQL
SECURITY DEFINER
SET search_path = public
AS $$
    DELETE FROM knowledge_chunks
    WHERE content_hash != ALL(active_hashes);
$$;

Without this, deleting a paragraph from a source file would leave its embedding orphaned in the database forever, quietly degrading search quality over time as stale chunks keep surfacing in results.

Chunking and Embedding

Each markdown file is split into chunks, and before embedding, I prepend the chunk's title to its content:

const texts = batch.map(
    chunk => `${chunk.title}

${chunk.content}`
)

const embeddings = await generateEmbeddings(texts)

This small detail — feeding the model title + content instead of content alone — gives the embedding model extra context about what the chunk is about, which noticeably improves retrieval quality for short chunks that might otherwise read as ambiguous on their own.

Chunks are processed in batches of 20 to stay within embedding API rate limits, and each batch is upserted with onConflict: "content_hash", so re-ingesting unchanged content is cheap and safe:

const { error } = await supabase
    .from("knowledge_chunks")
    .upsert(rows, {
        onConflict: "content_hash",
        ignoreDuplicates: false,
    })

The Hybrid Search Function: RRF in Practice

This is the core of the system — a single Postgres function that runs full-text and semantic search in parallel, then merges the two ranked lists using Reciprocal Rank Fusion:

CREATE OR REPLACE FUNCTION hybrid_search(
    query_text          TEXT,
    query_embedding     VECTOR(1536),
    match_count         INT     DEFAULT 5,
    full_text_weight    FLOAT   DEFAULT 1.0,
    semantic_weight     FLOAT   DEFAULT 1.0,
    rrf_k               INT     DEFAULT 50
)
RETURNS TABLE (
    id          BIGINT,
    section     TEXT,
    title       TEXT,
    content     TEXT,
    similarity  FLOAT
)
LANGUAGE SQL
STABLE
SECURITY INVOKER
SET search_path = public, extensions
AS $$
WITH
query AS (
    SELECT websearch_to_tsquery('english', query_text) AS ts_query
),
full_text AS (
    SELECT
        kc.id,
        ROW_NUMBER() OVER(
            ORDER BY ts_rank_cd(kc.fts, q.ts_query) DESC
        ) AS rank_ix
    FROM knowledge_chunks kc
    CROSS JOIN query q
    WHERE kc.fts @@ q.ts_query
    LIMIT LEAST(match_count, 30) * 2
),
semantic AS (
    SELECT
        kc.id,
        ROW_NUMBER() OVER(
            ORDER BY kc.embedding <=> query_embedding
        ) AS rank_ix
    FROM knowledge_chunks kc
    LIMIT LEAST(match_count, 30) * 2
),
fused AS (
    SELECT
        COALESCE(ft.id, sem.id) AS id,
        (
            COALESCE(1.0 / (rrf_k + ft.rank_ix), 0.0) * full_text_weight
            +
            COALESCE(1.0 / (rrf_k + sem.rank_ix), 0.0) * semantic_weight
        ) AS similarity
    FROM full_text ft
    FULL OUTER JOIN semantic sem
        ON ft.id = sem.id
)
SELECT
    kc.id, kc.section, kc.title, kc.content, fused.similarity
FROM fused
JOIN knowledge_chunks kc ON fused.id = kc.id
ORDER BY fused.similarity DESC
LIMIT LEAST(match_count, 30)
$$;

Breaking down what's actually happening:

1. full_text ranks results by ts_rank_cd against a websearch_to_tsquery-parsed query — this gives Google-style query parsing (quotes, OR, exclusions) for free.
2. semantic ranks results by cosine distance (<=>) between the query embedding and stored embeddings.
3. fused combines both ranked lists with RRF: instead of trying to normalize and compare raw scores from two completely different scales (a tsvector rank and a cosine distance aren't comparable), RRF scores each result purely by rank position — 1 / (k + rank). A result ranked #1 in either list contributes far more than one ranked #20, and results appearing in both lists get their scores summed, naturally boosting documents both methods agree on.
4. The rrf_k constant (50 here) controls how much the fusion favors top-ranked results versus flattening the curve — a smaller k sharply rewards rank #1, a larger k spreads weight more evenly.
5. full_text_weight and semantic_weight are exposed as parameters, so the balance between exact-match and meaning-match can be tuned per query type without touching the function itself.

Using a FULL OUTER JOIN between the two CTEs is what makes a result surfaced by only one method — say, a strong keyword match with no semantic overlap — still eligible to appear in the final results, rather than requiring agreement from both.

What This Actually Buys You

The end result is a /chat endpoint on this site that retrieves grounded context before generating a response, rather than relying on the model's training data or a static system prompt. A few practical wins from this architecture:

• Idempotent by design — re-running ingestion after editing a single markdown file only touches the chunks that actually changed.
• No orphaned data — deleted content is automatically cleaned from the vector store on the next ingestion run.
• Tunable retrieval — full-text and semantic weights can be adjusted without re-architecting anything, useful for A/B testing retrieval quality.
• Single round-trip — the entire hybrid search runs as one Postgres function call, avoiding the latency of fetching two result sets in application code and merging them client-side.

What's Next

The obvious extension here is bringing blog content into this same knowledge base — once posts are being published through Sanity, each one becomes another markdown-equivalent source that the ingestion pipeline can chunk and embed, so the chat assistant's knowledge grows automatically as this blog does.

If you're building something similar, the short version is: don't pick vector search or full-text search — Postgres with pgvector makes it cheap enough to run both and let RRF sort out the ranking.