Stop the Hallucinations Hybrid Retrieval with BM25

ALTER TABLE docs
ADD COLUMN body_tsv tsvector
    GENERATED ALWAYS AS (to_tsvector('english', body)) STORED;

CREATE INDEX idx_docs_fts ON docs USING GIN (body_tsv);

import psycopg2, numpy as np, openai, os
openai.api_key = os.getenv("OPENAI_API_KEY")

DB_DSN = os.getenv("DATABASE_URL")
EMBED_MODEL = "text-embedding-ada-002"
ALPHA = 0.7          # weight on vector vs BM25

def embed(text):                 # ① get embedding
    emb = openai.Embedding.create(model=EMBED_MODEL,
                                  input=text)["data"][0]["embedding"]
    return np.array(emb, dtype=np.float32)

def vector_hits(conn, emb, k=10):  # ② top-k vector
    cur = conn.cursor()
    cur.execute("""
      SELECT id, content,
             1 - (embedding <=> %s) AS vec_sim  -- cosine → similarity
      FROM docs ORDER BY vec_sim DESC LIMIT %s""",
      (emb.tolist(), k))
    return cur.fetchall()

def bm25_hits(conn, text, k=10):    # ③ top-k BM25
    cur = conn.cursor()
    cur.execute("""
      SELECT id, content,
             ts_rank_cd(body_tsv, plainto_tsquery('english', %s)) AS bm25
      FROM docs
      WHERE body_tsv @@ plainto_tsquery('english', %s)
      ORDER BY bm25 DESC LIMIT %s""",
      (text, text, k))
    return cur.fetchall()

def hybrid(section_draft:str):
    conn = psycopg2.connect(DB_DSN)
    v_hits = vector_hits(conn, embed(section_draft))
    b_hits = bm25_hits(conn, section_draft)
    conn.close()

    # ④ normalise each score to 0-1
    def norm(scores):
        a=np.array(scores); return (a-a.min())/(a.ptp() or 1)
    v_norm = norm([h[2] for h in v_hits]); b_norm = norm([h[2] for h in b_hits])

    # ⑤ merge by document id
    combined = {}
    for (i,c,_), s in zip(v_hits, v_norm):
        combined[i] = [c, s, 0.0]
    for (i,c,_), s in zip(b_hits, b_norm):
        combined.setdefault(i, [c, 0.0, 0.0])[2] = s

    # ⑥ weighted rerank
    scored = [(ALPHA*v + (1-ALPHA)*b, c) for c,(v,b) in combined.items()]
    return sorted(scored, reverse=True)[:5]

demo = hybrid("Combining BM25 lexical ranking with pgvector semantic search")
for score, snippet in demo:
    print(f"{score:.3f} → {snippet[:120]}…")

```**What just happened?**1.**HyDE draft**: you embed a short hypothetical answer.
2.**Vector retrieval**: `embedding <=> query` uses pgvector’s cosine distance.
3.**BM25 retrieval**: `ts_rank_cd()` scores keyword overlap.
4.**Normalization**: each list rescales to `[0,1]` so apples ≈ oranges.
5.**Merge & rerank**: weight the scores (`α` for semantic, `1-α` for lexical) and sort.

Adjust `α` on a validation set (or A/B test) until your engineers nod in approval.

---


## 5. Turbo-charging relevance: five pragmatic add-ons

| Technique | Why it helps | One-liner hint |
| --- | --- | --- |
|**Query expansion**| Inject synonyms, abbreviations, or ontology terms so BM25 matches more variants. | `ALTER TEXT SEARCH DICTIONARY…` or `nltk WordNet` in Python |
|**Maximal Marginal Relevance (MMR)**| Promotes diversity—avoids ten nearly identical hits. | Rerank with `λ * similarity – (1-λ) * redundancy` |
|**Metadata boosts**| Favor “chapter:intro” or recent docs via a simple WHERE clause. | `AND doc_type = 'api'` or multiply by `(1 + log(days_recent))` |
|**Cross-encoder rerank**| After top-k, ask a tiny transformer to score `{query, doc}` pairs. | Sentence-Transformers `ms-marco-MiniLM-L-6-v2` in 20 ms |
|**Adaptive chunking**| Code snippets want smaller chunks than prose. | At ingest time: `if is_code: chunk=20 lines else chunk=200 words` |

Mix-and-match; each layer is orthogonal and incremental.

---


## 6. Why Postgres as the single store?

-**Operational simplicity**: one connection pool, one backup story.
-**Transactional ingest**: embed and index in the same `INSERT` statement.
-**Extensions galore**: `pg_trgm` for fuzzy match, `unaccent` for diacritics, `hnsw` for faster vector ANN—all sitting beside your tables.
-**Cost**: if you’re already paying for a managed Postgres (RDS, Cloud SQL, Timescale), pgvector is literally `CREATE EXTENSION pgvector;` away.

Elasticsearch is fantastic for planet-scale log analytics, but many engineering orgs don’t need a second cluster just to serve 20 ms autocomplete suggestions.

If you got this far, congratulations. Smoke if you got ‘em, but don’t smoke. You know take a break. Get some coffee. Chill by the water cooler. Breathe. Ok… Let’s dive further. 


### Hands-On Part 2: Text Embedding-Rerank (a.k.a. “Let the HyDE judge”)

The first snippet blended**vector scores**and**BM25 ranks**with a weighted sum.

Sometimes you’d rather let the *embedding of the [HyDE](https://www.linkedin.com/pulse/using-chatgpt-embeddings-hyde-improve-search-results-rick-hightower/?trackingId=%2Fhdzp4ZNT3aCh9Bg5mQoEA%3D%3D) draft itself* decide which passages are closest—regardless of whether they arrived via vector search or keyword search. 

Or perhaps you have multiple ways to search. Let’s say you are trying to match a resume against job postings. You can match against the job posting itself. You can match it against hypothetical resumes that you already generated from job postings. You can extract the skills section of the resume to the required skills of the job posting, but at some point you need a final judge or at least a semi-final judge.  This is where rerank comes into. Pull the data back using many techniques but then prioritize the snippet for content based on your rerank. 

Below is a *drop-in* extension that:

1.**Runs BM25 twice**-**Mode A – Question only**(what the user actually typed)
    -**Mode B – HyDE draft**(the LLM’s hypothetical answer)
2.**Pulls the three candidate pools**- Vector top-*k* (semantic)
    - BM25-Q top-*k* (lexical)
    - BM25-HyDE top-*k* (lexical, paraphrased)
3.**Embeds every candidate passage**(once—memoize in production)
4.**Reranks all candidates**purely by *cosine similarity to the HyDE embedding*
    
    – drop-in flag lets you *blend* similarity with the earlier hybrid score if you prefer.
    

> Cost note: Embedding every passage is cheap at small k (e.g., 30 items × Ada ≈ $0.0006). For large corpora, cache passage embeddings at ingest time. You can also get embedding models libraries that are free and that are quite good, but let’s save that for another because I don’t get paid by the word or at all.
> 

```python

# ─── Imports & config (same as before) ─────────────────────────────────────────
import os, psycopg2, openai, numpy as np
openai.api_key = os.getenv("OPENAI_API_KEY")
DB_DSN = os.getenv("DATABASE_URL")
EMBED_MODEL = "text-embedding-ada-002"
TOP_K_VEC = 10          # vector pool
TOP_K_BM25 = 10         # each BM25 pool


# ─── Utility: embed & cosine sim ──────────────────────────────────────────────
def embed(text: str) -> np.ndarray:
    em = openai.Embedding.create(model=EMBED_MODEL, input=text)["data"][0]["embedding"]
    return np.asarray(em, dtype=np.float32)

def cos_sim(a: np.ndarray, b: np.ndarray) -> float:
    return float(np.dot(a, b) / (np.linalg.norm(a) * np.linalg.norm(b) + 1e-9))


# ─── Retrieval helpers (vector + generic BM25) ────────────────────────────────
def vec_hits(conn, q_emb, k=TOP_K_VEC):
    cur = conn.cursor()
    cur.execute("""
        SELECT id, content, 1 - (embedding <=> %s) AS vec_sim
        FROM docs ORDER BY vec_sim DESC LIMIT %s
    """, (q_emb.tolist(), k))
    return cur.fetchall()                      # (id, passage, vec_sim)

def bm25_hits(conn, query_text:str, k=TOP_K_BM25):
    cur = conn.cursor()
    cur.execute("""
        SELECT id, content,
               ts_rank_cd(body_tsv, plainto_tsquery('english', %s)) AS bm25
        FROM docs
        WHERE body_tsv @@ plainto_tsquery('english', %s)
        ORDER BY bm25 DESC LIMIT %s
    """, (query_text, query_text, k))
    return cur.fetchall()                      # (id, passage, bm25_rank)


# ─── Master routine: embed-based rerank across all pools ──────────────────────
def retrieve_and_embed_rerank(user_question:str):
    # Step-1  Generate HyDE draft (pretend; swap in your own LLM call)
    hyde_draft = f"A concise answer: {user_question}"  # <-- placeholder
    hyde_emb   = embed(hyde_draft)

    # Step-2  Pull pools
    conn       = psycopg2.connect(DB_DSN)
    vec_pool   = vec_hits(conn, hyde_emb)
    bm25_q     = bm25_hits(conn, user_question)
    bm25_h     = bm25_hits(conn, hyde_draft)
    conn.close()

    # Step-3  Merge pools into {id: passage}
    merged = {}
    for pool in (vec_pool, bm25_q, bm25_h):
        for pid, passage, _ in pool:
            merged.setdefault(pid, passage)

    # Step-4  Embed every candidate passage (cache in prod)
    cand_embs = {pid: embed(txt) for pid, txt in merged.items()}

    # Step-5  Rerank by cosine(query_emb, passage_emb)
    scored = [(cos_sim(hyde_emb, emb), txt) for txt, emb in cand_embs.values()]
    scored.sort(reverse=True)                 # highest similarity first
    return scored[:10]                        # top-10 passages


# ─── Demo ─────────────────────────────────────────────────────────────────────
if __name__ == "__main__":
    q = "How do I combine BM25 and vector search in Postgres?"
    for sim, passage in retrieve_and_embed_rerank(q):
        print(f"{sim:.3f} → {passage[:120]}…")

import os, openai, psycopg2, json
from typing import List, Tuple

openai.api_key = os.getenv("OPENAI_API_KEY")
DB_DSN   = os.getenv("DATABASE_URL")
EMBED    = "text-embedding-ada-002"
MODEL    = "gpt-4o-mini-2025-01-31"   # or o3-mini etc.


# ── STEP 1: recall & first-pass rank (reuse previous helpers) ─────────────
def top_candidates(job_posting:str, K1=1000, K2=100) -> List[Tuple[int,str]]:
    vec = embed(job_posting)                      # HyDE or posting itself
    conn = psycopg2.connect(DB_DSN)
    cur  = conn.cursor()
    cur.execute("""
        SELECT id, resume_text,
               1 - (embedding <=> %s) AS score
        FROM resumes ORDER BY score DESC LIMIT %s
    """, (vec.tolist(), K1))
    hits = cur.fetchall()[:K2]                   # keep best 100
    conn.close()
    return hits                                  # [(id, text, score)]


# ── STEP 2: LLM rubric scorer ────────────────────────────────────────────
RUBRIC = """
You are an expert technical recruiter.
Rate this RESUME against the JOB DESCRIPTION on three axes:
1. Required years of devops/cloud experience (score 1-10)
2. Location proximity to "{city}" (1-10)
3. Skill overlap with these keywords: {skills} (1-10)

Respond strictly as JSON:
{{
  "years": <int>,
  "location": <int>,
  "skills": <int>,
  "explanation": "<optional 1-sentence reason>"
}}
"""

def judge(job_desc:str, resume:str, city:str, skills:str)->dict:
    prompt = RUBRIC.format(city=city, skills=", ".join(skills))
    messages = [
        {"role":"system", "content":prompt},
        {"role":"user", "content":f"JOB DESCRIPTION:\n{job_desc}"},
        {"role":"assistant","content":"Understood."},
        {"role":"user", "content":f"RESUME:\n{resume[:4000]}"}  # trim token risk
    ]
    resp = openai.ChatCompletion.create(model=MODEL,
                                        messages=messages,
                                        temperature=0)
    return json.loads(resp["choices"][0]["message"]["content"])


# ── STEP 3: Combine scores & serve ────────────────────────────────────────
WEIGHTS = {"years":0.4, "location":0.2, "skills":0.4}

def rerank_llm(job_posting:str, city="Austin, TX", skills=("kafka","aws","k8s")):
    cand = top_candidates(job_posting)
    judged = []
    for pid, text, _ in cand:
        j = judge(job_posting, text, city, skills)
        final = sum(j[k]*w for k,w in WEIGHTS.items())
        judged.append((final, pid, text, j))
    judged.sort(reverse=True)          # highest composite first
    return judged[:10]                 # top 10 to caller, rest async


# ── Demo ─────────────────────────────────────────────────────────────────
if __name__ == "__main__":
    JD = "Senior DevOps Engineer – 5+ yrs AWS, Kafka, Kubernetes. Must be in Austin."
    for score, pid, _, detail in rerank_llm(JD):
        print(f"{pid}  →  {score:.1f} pts  |  rubric: {detail}")