Complete End-to-End System Flow: From Raw Docs to Grounded Answers

An exhaustive, architectural walkthrough of every phase, data structure, SQL query, algorithm, and interface in the RAG Technical Documentation Assistant.

Overview: This document tracks the complete end-to-end lifecycle of technical documentation in the system: from crawling raw DocBook HTML and parsing groff man pages, through recursive token-aware chunking and 384-dimensional BGE embeddings, into dual-indexed PostgreSQL 16 storage, parallel HNSW vector and GIN full-text retrieval, Reciprocal Rank Fusion (RRF), cross-encoder neural reranking, and citation-grounded LLM synthesis with evaluation metrics.


πŸ—ΊοΈ Complete Architecture & Dataflow Map

Phase 1: Ingestion
  β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”       β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
  β”‚ PostgreSQL 16 HTML (DocBook)   β”‚       β”‚ Linux Manual Pages (groff cat)  β”‚
  β”‚ Web Crawler (aiohttp)           β”‚       β”‚ Subprocess (man -P cat)         β”‚
  β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜       β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
                   β”‚                                         β”‚
                   β–Ό                                         β–Ό
  β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
  β”‚                        Document Extraction (extractor.py)                 β”‚
  β”‚  - Strip .navheader, .navfooter, .toc, script, style                      β”‚
  β”‚  - Extract DocBook container divs (.sect2, .sect1, .refentry)             β”‚
  β”‚  - Parse man page all-caps headings (NAME, SYNOPSIS, DESCRIPTION)         β”‚
  β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
                                       β”‚
Phase 2: Chunking & Deduplication      β–Ό
  β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
  β”‚                   Recursive Token Chunker (chunker.py)                    β”‚
  β”‚  - Boundary hierarchy: Paragraph (\n\n+) β†’ Sentence (?<=[.!?])\s+ β†’ Word  β”‚
  β”‚  - Token-level sizing with tiktoken (cl100k_base): target 512 tokens      β”‚
  β”‚  - Sliding context overlap: 64 tokens carried forward                     β”‚
  β”‚  - SHA-256 content hashing for idempotency: ON CONFLICT DO NOTHING        β”‚
  β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
                                       β”‚
Phase 3: Dense Embeddings              β–Ό
  β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
  β”‚                     Embedding Generation (encoder.py)                     β”‚
  β”‚  - Model: BAAI/bge-small-en-v1.5 (384 dimensions, float32)                β”‚
  β”‚  - Asymmetric instruction: Query prefix prepended only during search      β”‚
  β”‚  - Normalized embeddings: Cosine distance <=> is equivalent to dot productβ”‚
  β”‚  - Batched inference (batch_size=32) for fast ingestion throughput        β”‚
  β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
                                       β”‚
Phase 4: Database Storage              β–Ό
  β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
  β”‚                  PostgreSQL 16 Storage (schema.sql, connection.py)        β”‚
  β”‚  - Table 'documents': id, name, source_url, doc_type                      β”‚
  β”‚  - Table 'chunks': chunk_id (UUID), content, token_count, content_hash    β”‚
  β”‚  - Column 'content_tsv': TSVECTOR GENERATED ALWAYS AS (to_tsvector)       β”‚
  β”‚  - Column 'embedding': VECTOR(384)                                        β”‚
  β”‚  - Index 1: HNSW cosine index (m=16, ef_construction=64)                  β”‚
  β”‚  - Index 2: GIN inverted index on content_tsv                             β”‚
  β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
                                       β”‚
Phase 5: Candidate Retrieval           β–Ό (User Query Arrives)
              β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
              β–Ό                                                 β–Ό
  β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”               β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
  β”‚ Channel A: Vector Search      β”‚               β”‚ Channel B: Full-Text (FTS)β”‚
  β”‚ SET hnsw.ef_search = 40       β”‚               β”‚ websearch_to_tsquery(...) β”‚
  β”‚ ORDER BY embedding <=> $1     β”‚               β”‚ content_tsv @@ query      β”‚
  β”‚ Top-20 candidates (Cosine)    β”‚               β”‚ ts_rank_cd cover density  β”‚
  β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜               β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
                  β”‚                                             β”‚
                  β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
                                         β–Ό
Phase 6: Rank Fusion & Rerank            β”‚
  β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
  β”‚                 Reciprocal Rank Fusion (RRF, hybrid.py)                   β”‚
  β”‚      RRF_Score(chunk) = Ξ£_retriever 1 / (60 + rank_in_retriever)          β”‚
  β”‚      Merges candidates without cosine vs ts_rank scale distortion         β”‚
  β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
                                         β”‚ Top-20 Candidates
                                         β–Ό
  β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
  β”‚              Cross-Encoder Neural Reranking (cross_encoder.py)            β”‚
  β”‚  - Model: cross-encoder/ms-marco-MiniLM-L-6-v2 (22M params, 80 MB)        β”‚
  β”‚  - Full joint self-attention over [CLS] Query [SEP] Passage [SEP]         β”‚
  β”‚  - Prunes Top-20 candidate pool into Top-5 context chunks                 β”‚
  β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
                                         β”‚ Top-5 Reranked Chunks
Phase 7: Grounded Generation             β–Ό
  β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
  β”‚              Citation-Grounded LLM Synthesis (prompt.py, llm.py)          β”‚
  β”‚  - Formats numbered context blocks: [1]...[5] with source URLs            β”‚
  β”‚  - System Prompt: Answer ONLY from context; cite [Source N] on all claims β”‚
  β”‚  - Fallback guardrail: Output "insufficient context" if evidence is absentβ”‚
  β”‚  - Google GenAI SDK (Gemini 1.5 Flash / Gemma fallback) or OpenAI         β”‚
  β”‚  - Regex parser extracts cited sources and builds metadata citations      β”‚
  β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
                                         β”‚
Phase 8: Serving & Interfaces            β–Ό
  β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
  β”‚  FastAPI REST Server (POST /query)  β”‚  Interactive Terminal CLI (cli.py)  β”‚
  β”‚  Asynchronous asyncpg Pool Lifespan β”‚  REPL with /mode, /topk, ANSI Color β”‚
  β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
                                         β”‚
Phase 9: Evaluation Suite                β–Ό
  β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
  β”‚  41-Question Technical Dataset (dataset.py)                               β”‚
  β”‚  Recall@K, Hit@K, MRR, Citation Accuracy, LLM-Judge Hallucination Check   β”‚
  β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Phase 1: Ingestion Pipeline β€” Download & Structural Extraction

The ingestion pipeline starts in ingestion/downloader.py and ingestion/extractor.py. It ingests two very different document formats into a common normalized structure:

1.1 PostgreSQL 16 Official HTML Documentation

1.2 Linux System Manual Pages


Phase 2: Recursive Token-Aware Chunking & Deduplication

Once raw sections are extracted, they are passed to RecursiveChunker in ingestion/chunker.py. Slicing technical prose arbitrarily creates fragmented sentences and broken code blocks.

2.1 Recursive Boundary Splitting Hierarchy

The chunker implements a recursive divide-and-conquer strategy:

  1. Paragraph Split: Checks if the section fits within the token budget. If not, it splits on double newlines: re.split(r"\n\n+", text).
  2. Sentence Split: If an individual paragraph still exceeds the token budget, it splits on sentence boundaries: re.split(r"(?<=[.!?])\s+", text).
  3. Word Split: If a single sentence (such as a massive command string or configuration dump) exceeds the budget, it falls back to word splitting: text.split(" ").

2.2 Exact Token Sizing via tiktoken

Unlike naive chunkers that count characters (e.g., 1000 characters $\approx$ unpredictable token counts depending on whitespace and punctuation), RecursiveChunker measures exact token counts using OpenAI's fast BPE tokenizer:

self._enc = tiktoken.get_encoding("cl100k_base")

def _token_count(self, text: str) -> int:
    return len(self._enc.encode(text))

2.3 Idempotency via SHA-256 Hashing

To support incremental and resumable ingestion, each chunk calculates a SHA-256 hash of its content:

content_hash = hashlib.sha256(chunk.content.encode("utf-8")).hexdigest()

In PostgreSQL, the content_hash column is marked UNIQUE. The insertion query uses: ON CONFLICT (content_hash) DO NOTHING. Re-running the ingestion pipeline on updated documentation skips already-processed chunks with zero overhead.


Phase 3: Dense Vector Embeddings

Dense semantic representations are generated by Encoder in embeddings/encoder.py:

3.1 Model Architecture: BAAI/bge-small-en-v1.5

3.2 Asymmetric Query Instruction Prefix

The BGE model family was pre-trained with instruction tuning to differentiate between document indexing and retrieval queries. At query time, a task-specific prefix must be prepended:

_BGE_QUERY_PREFIX = "Represent this sentence for searching relevant passages: "

def encode_query(self, query: str) -> np.ndarray:
    prefixed = _BGE_QUERY_PREFIX + query
    return self._model.encode(prefixed, normalize_embeddings=True, convert_to_numpy=True).astype(np.float32)

Document passages are encoded without the prefix during ingestion. This asymmetry teaches the embedding model to bridge the semantic gap between a user question and an expository technical manual.

3.3 Vector Normalization

Embeddings are normalized to unit length ($\|v\|_2 = 1.0$) upon output. When vectors are unit-normalized, cosine distance is mathematically directly proportional to Euclidean distance and dot product:

$$\text{Cosine Distance}(u, v) = 1 - \frac{u \cdot v}{\|u\|_2 \|v\|_2} = 1 - u \cdot v$$

This allows pgvector to compute cosine distance using fast SIMD dot product instructions on modern CPU and GPU hardware.

3.4 Why BAAI/bge-small-en-v1.5? Systems Justification & Alternatives Analysis

Selecting an embedding model for a production RAG system is a multi-dimensional systems trade-off between retrieval accuracy, vector memory footprint, HNSW RAM cache residency, and query latency.

Embedding Model Dimensions Parameters / Disk Vector Size (FP32) HNSW 100K Chunks RAM Evaluation & Trade-off Analysis
BAAI/bge-small-en-v1.5 (Selected) 384 33.4M (~130 MB) 1.536 KB ~220 MB Optimal. Top-tier MTEB retrieval score; 512-token context window matches chunk size; small vector size keeps HNSW graph in RAM; asymmetric query prefix.
all-MiniLM-L6-v2 384 22.7M (~90 MB) 1.536 KB ~220 MB Fast and lightweight, but lacks modern contrastive hard-negative mining; delivers noticeably lower Recall@K on technical documentation and code.
BAAI/bge-base-en-v1.5 768 109M (~440 MB) 3.072 KB ~440 MB Only +1.2% higher MTEB retrieval score, but doubles HNSW RAM usage and slows dot-product vector math by 2x. Downstream cross-encoder makes this redundant.
BAAI/bge-large-en-v1.5 1024 335M (~1.3 GB) 4.096 KB ~580 MB Triples RAM consumption; 4x slower vector distance calculation. Unsuitable for fast local CPU inference.
intfloat/e5-small-v2 384 33M (~130 MB) 1.536 KB ~220 MB Strong alternative, but requires prefixes on both queries ("query: ") and documents ("passage: "). BGE stores document chunks clean without wasted prefix tokens.
OpenAI text-embedding-3-small 1536 (or 512 MRL) Cloud API 6.144 KB (or 2.048 KB) ~880 MB (or ~290 MB) High quality, but introduces 100–300 ms network HTTP latency, recurring API costs, vendor lock-in, and prevents offline terminal CLI usage.
πŸ’‘ Microarchitectural RAM Implications of 384 vs. 768 / 1536 Dimensions in PostgreSQL:
In pgvector, an HNSW index must ideally reside entirely in RAM (PostgreSQL shared_buffers + OS page cache) to prevent random NVMe disk seeks during multi-layer graph navigation. Every HNSW node stores the raw vector ($D \times 4\text{ bytes}$) plus an array of bidirectional neighbor pointers ($m \times 8\text{ bytes}$ on 64-bit systems).
Choosing 384 dimensions reduces graph memory traffic over the memory bus and guarantees low-latency search even on resource-constrained servers.

Phase 4: Unified PostgreSQL 16 Storage & Dual-Index Engine

All relational metadata, document chunks, full-text inverted indexes, and vector embeddings reside in a single PostgreSQL 16 database initialized by database/schema.sql:

4.1 Relational Schema

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Documents registry table
CREATE TABLE IF NOT EXISTS documents (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    source_url  TEXT,
    doc_type    TEXT CHECK (doc_type IN ('postgresql_docs', 'man_page')),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Chunks table with dual representations
CREATE TABLE IF NOT EXISTS chunks (
    chunk_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id   INTEGER NOT NULL REFERENCES documents (id) ON DELETE CASCADE,
    document_name TEXT NOT NULL,
    section       TEXT,
    content       TEXT NOT NULL,
    source_url    TEXT,
    token_count   INTEGER,
    chunk_index   INTEGER,
    content_hash  TEXT UNIQUE,
    -- Full-text search vector (automatically maintained)
    content_tsv   TSVECTOR GENERATED ALWAYS AS
                      (to_tsvector('english', content)) STORED,
    -- Dense embedding (384 dimensions)
    embedding     VECTOR(384),
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

4.2 Index 1: HNSW Cosine Index

CREATE INDEX IF NOT EXISTS chunks_embedding_hnsw_idx
    ON chunks
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

4.3 Index 2: GIN Inverted Index

CREATE INDEX IF NOT EXISTS chunks_content_tsv_idx
    ON chunks
    USING gin (content_tsv);

Phase 5: Candidate Retrieval Engine

When a query arrives, retrieval executes across two distinct channels in retrieval/:

5.1 Channel A: Semantic Vector Search (retrieval/vector_search.py)

The user query is encoded to a 384-dimensional float vector and queried using pgvector's cosine distance operator (<=>):

SET hnsw.ef_search = 40;

SELECT
    chunk_id::text,
    document_name,
    section,
    content,
    source_url,
    1 - (embedding <=> $1::vector) AS score
FROM chunks
ORDER BY embedding <=> $1::vector
LIMIT 20;

5.2 Channel B: Lexical Full-Text Search (retrieval/fts_search.py)

Parallel to vector search, the raw query string is evaluated by PostgreSQL's full-text engine:

SELECT
    chunk_id::text,
    document_name,
    section,
    content,
    source_url,
    ts_rank_cd(content_tsv, query) AS score
FROM
    chunks,
    websearch_to_tsquery('english', $1) AS query
WHERE
    content_tsv @@ query
ORDER BY score DESC
LIMIT 20;

5.3 Channel C: Hybrid Fusion via Reciprocal Rank Fusion (retrieval/hybrid.py)

Now we have two ranked lists of 20 candidates each. How do we merge them?

⚠️ Why Raw Score Normalization Fails:
Vector similarity produces values bounded in $[0, 1]$, typically clustered tightly between $0.60$ and $0.85$. FTS ts_rank_cd produces unbounded floating-point scores in $[0, \infty)$ where a long chunk with dense term repetitions can achieve a score of $12.4$ while a concise chunk achieves $0.3$. Linearly summing these scores ($w_1 S_{vec} + w_2 S_{fts}$) allows FTS outliers to completely overpower vector scores.

We solve this using Reciprocal Rank Fusion (RRF) (Cormack, Clarke, & Buettcher, SIGIR 2009):

$$RRF\_Score(d) = \sum_{r \in \{\text{vector}, \text{fts}\}} \frac{1}{k + \text{rank}_r(d)}$$

Phase 6: Cross-Encoder Neural Reranking

The top-20 RRF candidates are forwarded to Reranker in reranking/cross_encoder.py:

6.1 Cross-Encoder vs. Bi-Encoder Architecture

Dimension Bi-Encoder (BGE-Small) Cross-Encoder (MS-Marco-MiniLM-L-6-v2)
Input Representation Encodes query $q$ and passage $p$ separately: $E(q)$ and $E(p)$ Concatenates query and passage together: $[CLS] \ q \ [SEP] \ p \ [SEP]$
Attention Mechanism Self-attention restricted within query tokens and within passage tokens Full cross-attention between all query tokens and all passage tokens
Similarity Computation Inner dot product / cosine distance in vector space Single classification head on $[CLS]$ embedding output
Computational Cost $\mathcal{O}(1)$ query encoding + $\mathcal{O}(\log N)$ index lookup $\mathcal{O}(N)$ full transformer forward passes per query
Optimal Role in Pipeline Fast candidate retrieval across 50,000+ chunks High-precision reranking across the top-20 candidate pool

6.2 Reranking Execution

The reranker constructs 20 string pairs (query, chunk["content"]) and executes a single batched forward pass on CPU:

pairs = [(query, chunk["content"]) for chunk in chunks]
scores = self._model.predict(pairs)  # returns numpy array of logits
scored = sorted(zip(scores, chunks), key=lambda x: float(x[0]), reverse=True)
top_5 = scored[:5]

The top 5 chunks with the highest cross-encoder relevance scores are selected as the final context passed to the generation stage.


Phase 7: Citation-Grounded LLM Generation

The top-5 chunks are passed into the generation stage (generation/prompt.py and generation/llm.py):

7.1 Numbered Context Construction

In prompt.py, chunks are formatted into clearly delineated, numbered context blocks:

[1] Document: postgresql_16_docs
    Section:  MVCC Concurrency Control
    URL:      https://www.postgresql.org/docs/16/mvcc-intro.html
    ---
    PostgreSQL maintains database consistency by using a multiversion model...

[2] Document: man_pages
    Section:  KILL(1)
    URL:      https://man7.org/linux/man-pages/man1/kill.1.html
    ---
    The command kill sends the specified signal to the specified process...

7.2 Strict Anti-Hallucination System Prompt

You are a precise technical documentation assistant. Your sole knowledge source
is the documentation excerpts provided below. Follow these rules strictly:

1. Answer using ONLY the information present in the provided excerpts.
2. For every factual claim, add an inline citation: [Source N] where N is the
   excerpt number.
3. If multiple excerpts support a claim, cite all relevant ones: [Source 1][Source 3].
4. If the provided context is insufficient to answer the question, say explicitly:
   "The provided documentation does not contain enough information to answer this question."
   Do not guess or use external knowledge.
5. Be concise and technically accurate. Use bullet points or numbered lists where
   appropriate for clarity.

7.3 Asynchronous LLM Client & Model Fallback Cascade

In generation/llm.py, the client utilizes the official google.genai SDK with temperature set to $0.2$ for deterministic factual extraction. If an API quota or model error occurs, the client automatically executes an in-memory fallback cascade:

$$\text{gemini-flash-latest} \longrightarrow \text{gemma-4-26b-a4b-it} \longrightarrow \text{gemini-pro-latest}$$

Alternatively, the client supports OpenAI's chat completions API if LLM_PROVIDER=openai.

7.4 Citation Extraction & Metadata Verification

Once the model returns its raw generated answer, extract_citations parses the text using regular expressions:

_CITATION_RE = re.compile(r"\[Source\s+(\d+)\]", re.IGNORECASE)

def extract_citations(answer: str, chunks: list[dict]) -> list[dict]:
    cited_numbers = sorted({int(m) for m in _CITATION_RE.findall(answer)})
    citations = []
    for n in cited_numbers:
        idx = n - 1
        if 0 <= idx < len(chunks):
            citations.append({
                "source_number": n,
                "document_name": chunks[idx].get("document_name"),
                "section": chunks[idx].get("section"),
                "source_url": chunks[idx].get("source_url"),
            })
    return citations

This extracts exact document names, sections, and URLs, allowing client interfaces to display interactive link chips for each citation.


Phase 8: Serving & User Interfaces

8.1 FastAPI High-Throughput REST Service (api/)

8.2 Rich Interactive Terminal CLI (cli.py)


Phase 9: Evaluation Suite & Benchmark Results

In evaluation/, the system was subjected to an automated evaluation suite against a 41-question technical dataset (dataset.py) with ground-truth document and section annotations:

9.1 Quantitative Evaluation Metrics (metrics.py)

9.2 Benchmark Comparison Table

Retrieval Mode Recall@5 Recall@10 Hit@5 Hit@10 MRR Avg Latency
Vector Search (HNSW) 0.2927 0.3049 0.3171 0.3171 0.2988 ~18 ms
Hybrid Search (Vector + FTS RRF) 0.2927 0.3049 0.3171 0.3171 0.2988 ~26 ms
Hybrid + Cross-Encoder Rerank 0.2927 0.3171 0.3171 0.3415 0.2791 ~112 ms
πŸ“Š Takeaway from Empirical Benchmarks:
Adding the Cross-Encoder reranker increased Hit@10 from 31.71% to 34.15% and Recall@10 from 30.49% to 31.71%. While the reranker adds ~80 ms of CPU compute, it filters out false-positive lexical matches, ensuring that the LLM context window receives dense, highly relevant technical context.

← 4–5 Minute Speech Next: Master Interview Q&A (A–J) β†’