Hybrid retrieval

By the end of this stage a plain-English question goes in and a ranked list of relevant filing passages comes out, with document metadata and surrounding context attached. There is no LLM answering anything yet. This is pure search, and it is the foundation everything after it stands on.

The design is deliberately familiar. Dave models it on his public hybrid search tutorial repository, with one substitution. That project used BM25 for keyword search, and here Postgres full-text search takes its place because the chunks already live in Supabase Postgres.

Why hybrid search

Semantic search alone is not enough for SEC filings. Embeddings catch meaning ("demand drivers" matches "factors affecting revenue growth"), but they are unreliable for exact terms like ticker symbols, product names like iPhone or Azure, and financial line items. Keyword search has the opposite profile. Exact matches are its whole job, but it has no idea that two differently worded sentences mean the same thing.

So the retriever runs both and merges the results. As Dave puts it in the video, Postgres "doesn't really have a native implementation" of BM25, "but it does have a full text search. It's a little bit different, but it can still search and match keywords."

The plan for this phase also suggested adding Cohere as a cross-encoder re-ranker, as in the original cookbook. Dave declines: "for now I want to keep things simple because otherwise you also would need to get an additional API key." Re-ranking is a later optimization, not a requirement for a working pipeline.

Two searches, run concurrently

The whole pipeline fits in one picture. A question fans out into an embedding and a keyword set, both searches run at the same time, and rank fusion merges the results before hydration attaches metadata and neighbors.

Both search paths live in backend/app/retrieval/queries.py as raw SQL. Semantic search orders by pgvector cosine distance (the <=> operator) and reports 1 - distance as the score:

# backend/app/retrieval/queries.py
sql = f"""
    SELECT dc.id,
           1 - (dc.embedding <=> CAST(:query_vec AS vector)) AS score
    FROM document_chunks dc
    JOIN source_documents sd ON sd.id = dc.document_id
    WHERE dc.embedding IS NOT NULL
    {filter_clause.sql}
    ORDER BY dc.embedding <=> CAST(:query_vec AS vector)
    LIMIT :limit
"""

Full-text search runs plainto_tsquery against the search_vector column that ingestion generated, ranked with ts_rank_cd:

# backend/app/retrieval/queries.py
sql = f"""
    SELECT dc.id,
           ts_rank_cd(dc.search_vector, query) AS score
    FROM document_chunks dc
    JOIN source_documents sd ON sd.id = dc.document_id,
         plainto_tsquery('{fts_config}', :query_text) query
    WHERE dc.search_vector @@ query
    {filter_clause.sql}
    ORDER BY score DESC
    LIMIT :limit
"""

Each path fetches up to retrieval_candidate_k hits (default 50), far more than the final result count, so fusion has something to work with. The orchestrator in backend/app/retrieval/retriever.py runs them at the same time, each on its own database session:

# backend/app/retrieval/retriever.py
def _dual_search(query_vec, fts_query, *, candidate_k, filters):
    """Run semantic and full-text search in parallel (separate DB sessions)."""

    def semantic() -> list[RankedChunkHit]:
        with get_session() as search_session:
            return semantic_search(search_session, query_vec, limit=candidate_k, filters=filters)

    def fts() -> list[RankedChunkHit]:
        with get_session() as search_session:
            return full_text_search(search_session, fts_query, limit=candidate_k, filters=filters)

    with ThreadPoolExecutor(max_workers=2) as executor:
        semantic_future = executor.submit(semantic)
        fts_future = executor.submit(fts)
        return semantic_future.result(), fts_future.result()

The query embedding and the keyword extraction (below) also run in parallel before the searches start, so the two network calls to OpenAI do not stack.

Reciprocal rank fusion

Now you have two ranked ID lists with incomparable scores, a cosine similarity and a ts_rank_cd value. Reciprocal rank fusion sidesteps the problem by ignoring scores entirely and using only positions. The whole algorithm is a few lines in backend/app/retrieval/fusion.py:

# backend/app/retrieval/fusion.py
def reciprocal_rank_fusion(
    rankings: list[list[UUID]],
    *,
    k: int = 60,
) -> list[tuple[UUID, float]]:
    scores: dict[UUID, float] = defaultdict(float)
    for ranking in rankings:
        for rank, chunk_id in enumerate(ranking, start=1):
            scores[chunk_id] += 1.0 / (k + rank)
    return sorted(scores.items(), key=lambda item: -item[1])

Every appearance at rank r adds 1 / (k + r) to a chunk's score, so a chunk that shows up in both lists beats a chunk that tops only one. The constant k=60 (retrieval_rrf_k) is the standard literature value; it dampens how much a first-place hit dominates a tenth-place one. The fused list is cut to retrieval_top_k (default 10).

Dave is explicit that these numbers are starting points, not gospel, because "there's no perfect set of numbers out of the box... so much depends on your data set, on your chunking strategy, on pretty much all of the components that involve your project."

Neighbors and hydration

The winning chunk IDs are hydrated into full RetrievedPassage objects with document metadata (ticker, form, fiscal year, page, section). Because a matching chunk often starts or ends mid-thought, each hit also pulls its adjacent chunks within retrieval_neighbor_radius (default 1) in the same document. Neighbors are attached with fusion_score=0.0 and deduplicated across hits, so the answer layer later gets context without double-counting.

Filtering happens inside both SQL queries via SearchFilters from backend/app/retrieval/types.py:

# backend/app/retrieval/types.py
class SearchFilters(BaseModel):
    ticker: str | None = None
    fiscal_years: list[int] | None = None
    form: str | None = None

Unset fields apply no filter; set fields are ANDed together (sd.ticker = :ticker, sd.fiscal_year = ANY(:fiscal_years), sd.form = :form). This is how "Apple 10-Ks only" becomes a hard constraint instead of a retrieval suggestion.

The keyword extraction fix

The first generated version of this pipeline sent the raw user question straight into full-text search. Dave catches this by reading the generated README diagram, noting that "if you do a full user query, there will almost never be a match." The reason is plainto_tsquery semantics. Postgres ANDs every word, so a 15-word question only matches chunks that contain all 15 words.

The fix is a small LLM step in backend/app/retrieval/keywords.py that distills the question into 3 to 5 search terms before it hits Postgres:

# backend/app/retrieval/keywords.py
def extract_fts_keywords(query: str, *, filters: SearchFilters | None = None) -> str:
    """Return a space-joined keyword string for plainto_tsquery."""
    stripped = query.strip()
    if not stripped:
        return stripped

    if _token_count(stripped) <= settings.retrieval_fts_keyword_fast_path_tokens:
        return stripped

    try:
        response = _client().chat.completions.parse(
            model=settings.retrieval_fts_keyword_model,
            temperature=0,
            messages=[
                {"role": "system", "content": _SYSTEM_PROMPT},
                {"role": "user", "content": _build_user_message(stripped, filters)},
            ],
            response_format=FtsKeywordExtraction,
        )
        parsed = response.choices[0].message.parsed
        if parsed is None:
            return _deterministic_fallback(stripped, filters=filters)

        words = _merge_fts_words(stripped, _normalize_terms(parsed.terms), filters=filters)
        if len(words) < settings.retrieval_fts_keyword_min:
            return _deterministic_fallback(stripped, filters=filters)
        return " ".join(words)
    except Exception:
        return _deterministic_fallback(stripped, filters=filters)

Three details matter here. Queries of 5 tokens or fewer skip the LLM entirely (they are already keyword-shaped). The system prompt teaches the model domain phrases like "revenue mix" and "customer concentration", and tells it to drop the company name when a ticker filter is already active. And every failure path falls back to a deterministic filler-word stripper, so retrieval never breaks because one small model call did.

Test the pipeline

Run the test script from backend/ to push real client-brief questions through the whole path:

uv run python -m scripts.smoke_retrieval

The script in backend/scripts/smoke_retrieval.py runs ticker-scoped questions like the Apple revenue-mix one:

# backend/scripts/smoke_retrieval.py
SMOKE_QUERIES: list[tuple[str, SearchFilters | None]] = [
    (
        "Across Apple's 10-Ks, how did the revenue mix between iPhone, Services, Mac, iPad, and Wearables change?",
        SearchFilters(ticker="AAPL", form="10-K"),
    ),
    ...
]

passages = retriever.search(query, filters=filters, top_k=5)
print(format_passages_for_agent(passages))

Be clear about what you are checking. Dave: "what I'm interested in is just, does the pipeline work? Can we actually get documents back? I'm not as much interested in the quality just yet, because I know that's an optimization game that we need to play later." Working plumbing first, relevance tuning later.

Checkpoint

• uv run python -m scripts.smoke_retrieval runs without errors from backend/
• The Apple revenue-mix question returns fused passages from AAPL 10-K filings only (filters hold)
• Passages include ticker, form, fiscal year, and page metadata, plus neighbor chunks
• A long natural-language question still returns full-text hits (keyword extraction is working)