Zoth's RAG retrieval — pgvector, HNSW, and the chunking gap
The vector half of your advisory layer, at code level — including what's missing.
Know your own system
The storage
policy_documents(id, tenant_id, title, content, embedding vector(768), ...) with CREATE INDEX ... USING hnsw (embedding vector_cosine_ops) (migration 007). Embeddings come from Gemini text-embedding-004 (768 dims — hence the dimension migration Day 36 discussed). Queries: SearchByEmbedding — ORDER BY embedding <=> $1::vector LIMIT n, tenant-scoped in the WHERE.
Mechanics to know: <=> is cosine distance (1 − cosine similarity); HNSW is a multi-layer navigable small-world graph — greedy descent from the top layer finds approximate nearest neighbors in O(log n)-ish hops; recall is tuned via hnsw.ef_search (higher = better recall, slower); the index costs memory (graph edges) and build time. At your scale (hundreds-to-thousands of docs) it's milliseconds.
The tenant-filter interaction (Day 30's senior point, now with the fix): HNSW traversal happens before the WHERE filter, so a selective tenant filter can yield fewer than k results (candidates filtered out post-retrieval). At your scale, irrelevant. At real scale: partition the table by tenant (each partition its own index — filter becomes partition pruning) or over-fetch and re-rank. Naming this interaction unprompted is a strong signal.
The ingestion path
Documents are embedded on upload (policy.Service.AddDocument) — one embedding per document, full content, no chunking. That's the gap to own: long documents (security policy PDFs, runbooks) exceed the embedding model's useful context (~2k tokens for text-embedding-004) and get truncated, so recall degrades for content past the cutoff. The fix has a standard shape: chunk (400–800 token windows with 10–20% overlap, respecting headings), embed each chunk with metadata (doc id, chunk index, section title), store chunks as rows, retrieve chunks → group by doc → rerank by max/mean score. Chunking also improves precision: the retrieved unit matches the query's granularity. Additional gaps worth naming: no reranking stage (a cross-encoder or LLM rerank on top-20 → top-5 would sharpen results), no embedding version column (model changes = full re-embed — the dimension migration proved that pain), and no freshness check (a policy edited after embedding has a stale vector until re-ingest).
What the retrieval feeds (the advisory layer's diet)
Day 68 covers the generation side; here, note the retrieval uses: historical access decisions + audit rows + policy docs are the corpus; queries embed the current request (resource, justification, requester context) → nearest neighbors become few-shot evidence for risk scoring and policy suggestions. That's textbook RAG as grounding: the LLM's output is anchored to retrieved precedents rather than priors — the reason your risk scores are explainable ("similar requests were approved 87% of the time, median duration 45 min" is a retrieval statistic, not an LLM hallucination — say that distinction; it's the strongest part of the design).
Bulletproof your resume
Say these answers aloud before moving on
These are the follow-ups an interviewer will actually ask about your projects.
Why pgvector over a dedicated vector database?
The corpus is small (policy docs + decision history), the store is already the system of record, and the access path (ANN cosine search) is an extension of a relational query — I filter by tenant, join nothing, but I keep transactional consistency and one backup story. Pinecone/Qdrant would add a second stateful service and a sync problem for a few thousand 768-dim vectors. The upgrade triggers: index exceeding RAM, recall requirements needing IVF-PQ/DiskANN, or QPS competing with OLTP — then it's a projection I can rebuild from Postgres, not a rewrite.
Your documents aren't chunked. What breaks and what's the fix?
Long documents get truncated at the embedding model's context — content past the cutoff is invisible to retrieval, and even within the window, one vector per doc dilutes the signal. Fix: chunk into 400–800 token windows with overlap, embed per chunk with doc/section metadata, retrieve chunks and aggregate to documents, optionally rerank. I'd also store the embedding model version per row so model changes trigger targeted re-embedding rather than the full reset migration 007 had to do.
Is your risk score an LLM output or a retrieval statistic?
Both, and the layering matters: retrieval produces statistics over historical decisions (approval rate, median duration for similar requests — facts from the corpus), then the LLM synthesizes a narrative and a score given those facts. So the numbers are auditable against the corpus, and the LLM contributes interpretation, not invention. That's the difference between RAG-as-grounding and an LLM guessing — and it's why the advisory layer can be wrong without being dangerous: it never decides.