Search technical documentation

What you will be able to do

Choose a search method for real technical documentation with evidence from your own questions. You compare keyword, vector, hybrid and reranked search on the SQLite documentation. You measure them by kind of question, with their time and cost. And you see that the winner here is not the winner on Larkfield’s policies.

Case studyPortfolio piece

Dataset

SQLite 3.53.4 documentation (195 pages) and 53 labelled questions

Licence

Public domain

Time

About 2 hours

Runs in

Colab, Kaggle or VS Code

Skills used

• Parse with provenance
• Relevance labels and pooling
• BM25, e5 and hosted embeddings
• Hybrid search and reranking
• recall@k, MRR, hit@1 by kind
• Paired comparison
• Cited answers

Open in ColabOpen in KaggleView on GitHub

Colab and Kaggle open your own copy. To use VS Code, download the notebook from GitHub. Stuck? See the solution notebook.

The notebook runs every step of this page with the same code and the same output, plus three tasks with checks. Its setup downloads the documentation snapshot and precomputed vectors (about 20 MB) and the two local models of the course. It ran in about 2 minutes on the test laptop with the models already downloaded.

Before you startWhat to know and to have readyAbout 120 minutes

What you need to know

This case study uses the whole course. These lessons matter most:

• BM25 and hit@5, from A keyword-search baseline.
• Parsing HTML with provenance and structure-aware chunks, from Parse and normalize and Chunk deliberately.
• Embeddings, the model record and embed-small, from Embeddings for search.
• Hybrid search with reciprocal rank fusion, and the honest result on Larkfield’s set, from Lexical, dense and hybrid search.
• The cross-encoder and its time cost, from Reranking and query changes.
• Claims with citations and what code can check, from Answer and cite.

What you need on your computer (only for the local route; Colab and Kaggle need none of it)

• A virtual environment with the course’s packages, as in the project from Embeddings for search. The notebook installs only pandas and matplotlib if they are missing.
• The notebook, downloaded from the link above. Its setup cell creates a folder sqlite-docs next to it. To start again, delete that folder.

No account and no key. Tested on macOS (Apple silicon) with Python 3.12.13, sentence-transformers 6.1.0 and SQLite 3.53.4. Windows, Linux, Colab and Kaggle are not tested yet.

The situation

Dana leads the developer support desk of a small software company. Its product stores its data in SQLite. About 40 questions a week reach her team. Half come from people who paste an exact name from an error or a log: SQLITE_BUSY, PRAGMA journal_size_limit, .once. The other half describe a problem in their own words: “I deleted half the rows and the file did not get smaller.”

Felix, a developer on the team, wants a search box over the official SQLite documentation, and later an assistant that answers with citations. He has read that “hybrid search always wins” and wants to ship it. Dana asks for evidence first: which method finds the right section for both kinds of question, and what does it cost? A wrong first result costs her team a few minutes. A confident answer from the wrong page costs more, because a customer may act on it.

What you will build

You build a fair comparison of six search methods on one fixed snapshot of the documentation, with a labelled question set. You measure each method by kind of question, with its time and cost, and you write a recommendation for Dana. At the end, you run one new question end to end and look at the cited answer that a model gave.

The skills come from every module of the course. Module 2 parses with provenance, and Module 3 compares keyword, vector and hybrid search and reranking. Module 4 writes cited answers and checks them, and Retrieval measures defines the measures. One idea is new: how to label relevant passages when you cannot read the whole collection. It has its own section below.

The data

Source and licence

SQLite 3.53.4 documentation, the official archive sqlite-doc-3530400.zip from sqlite.org (SHA-256 a1d0f5de…bbdc8). SQLite’s code and documentation are in the public domain, so the course may host a copy. The questions and labels were written for this course. The dataset card is in the labs folder, next to the notebook.

The author compared two other candidates first. The pytest 9.1.1 documentation (MIT licence) has a clean version tag, but its Sphinx sources need a build step. The Kubernetes documentation (CC BY 4.0) has 1,721 pages, no tagged versions, and attribution on every reuse. SQLite won for four reasons. It is public domain and versioned, and it has many kinds of exact names. It is the right size after a clear selection. And it is the engine of the course’s own keyword search. You search the documentation of the tool that you use.

The archive holds 224 pages at its top level. The snapshot keeps 195. It leaves out 29 pages that are not technical documentation: lists of links, cross-reference indexes, news, release history, and organisation and legal pages. A file pages.csv gives each page and the reason. The parser follows the course’s rules. It skips what SQLite itself marks as navigation (<div class=nosearch>), and it keeps each section with its heading and its anchor. So every chunk links back to its exact place, for example https://sqlite.org/pragma.html#pragma_busy_timeout.

195 pages, 2231 sections, 4300 chunks
Your chunks are identical to the shipped ones.
597,305 words; median chunk 151 words; longest 571

What this tells us. The snapshot is about 90 times larger than Larkfield’s 36 policies (6,520 words). Each chunk is one section, cut at 200 words between paragraphs. Some tables are much longer: the opcode table of the bytecode engine has about 16,000 words. Those tables are split between rows, and each part repeats the table’s header row. The course’s chunker never splits a table, but at this size a table must be split.

The notebook also finds two traps that matter later:

fileformat: 106 chunks; identical text in fileformat2: 106 (100%)
The C language interface to SQLite Version 2

What this tells us. Two pages, fileformat.html and fileformat2.html, have exactly the same text, as on the website. So a search can return the same answer twice and fill two of the five places. And c_interface.html describes the C interface of SQLite version 2, more than 20 years old, inside the 3.53.4 documentation. A search for “database is locked” can find it, and an answer can cite it. Real documentation carries its own history.

Frame the problem

The unit is one question. A search method returns a ranked list of chunks, and the top 5 go to the reader or to the model, as in the course project. One version is in force (3.53.4) and every page is public, so there is no date filter and no access filter here.

The main measure is hit@5: is at least one relevant chunk in the top 5? Next to it are recall@5 and recall@10: the share of a question’s relevant passages in the top 5 or 10. MRR@10 is the mean of 1 ÷ the rank of the first relevant chunk, or 0 if it is not in the top 10. hit@1 asks whether the first chunk is relevant. A reader of a search box looks at the first result first, so hit@1 and MRR matter as much as hit@5. The tables give every measure by kind of question, because “on average” hides where a method fails.

Unanswerable questions have no relevant passage, so they are left out of these measures. Abstaining on them is the answer step’s job (Handle difficult evidence).

The questions and their labels

The case study has 53 questions, written by the course author in the style of Dana’s queue. 18 contain an exact identifier; 5 of them are only the name, such as “What is SQLITE_BUSY_SNAPSHOT?”. 15 are paraphrases with no identifier, and 15 are how-to tasks. The snapshot cannot answer the last 5. They ask about another product (a PostgreSQL error code, a Django setting), a page outside the snapshot, or a made-up name such as PRAGMA wal_buffer_size.

Each question needs its relevant passages: the sections that answer it. On Larkfield’s 36 documents, Grace could read everything. Here there are 2,231 sections, and nobody reads them all for each of 53 questions.

Think of a teacher who marks a quiz with an answer key. A student gives a correct answer that the key does not list. A careful teacher reads it and adds it to the key. Search has the same problem. The author wrote each question from one section and labelled the sections he already knew. But the documentation often says the same thing in two places. The FAQ says “You can use PRAGMA integrity_check to do a thorough … test”, and the pragma’s own page says it too. The key would count a method that finds the FAQ as wrong.

The usual fix has a name: pooling. Run every method, collect the top results of all of them into one pool, and judge each pooled chunk by reading it. A chunk is relevant if a reader with that chunk alone could answer the question. The pool is small enough to read, and every method gets the same chance to have its finds counted.

How the 53 questions were labelled. The second reading came from evidence: a recorded answer cited a chunk that the first reading had judged not relevant.

Text description of the diagram

Six steps from left to right. 1: 53 questions, each written from one section. 2: the initial labels, 82 sections that the author knew. 3: all six methods run. 4: the pool, the top 5 of every method for each question. It holds 637 chunks that no initial label covered. 5: the author reads and judges each one. Two arrows leave step 5. One gives 31 more relevant passages from the first reading. The other, drawn dashed, gives 9 more from a second reading of the full text. 6: the frozen labels, 122 passages.

The first reading judged each pooled chunk from its first 420 characters and found 31 more relevant passages. Then a recorded answer (below) cited a chunk of fileformat2.html that the first reading had judged “no”. Its full text states the answer, “about 281 terabytes”, after the first 420 characters. So every “no” chunk whose full text contains a key term of the reference answer was read again in full, and 9 more became relevant. Then the labels were frozen.

Say the limits plainly. It is one person’s reading, with no second judge. A relevant section that no method put in its top 5 is still missing, for every method alike. And the labels changed after the first measurements. The order of close methods changed with them: with the initial labels, hybrid search with e5 was slightly below e5 alone (hit@5 0.667 against 0.688); with the final labels it is above (0.792 against 0.729). A difference of one or two questions depends on the labels. The procedure and every judgment are in the reference files of the case study.

A baseline: keyword search

Start with the simplest method, as in Module 1: SQLite FTS5 with BM25 over the 4,300 chunks. The query is the course’s: every word quoted, joined by OR.

index built in 0.04 s
bm25 {'hit@1': 0.458, 'hit@5': 0.708, 'recall@5': 0.555, 'recall@10': 0.604, 'MRR@10': 0.553}
hit@5 by kind: {'identifier': 0.944, 'paraphrase': 0.533, 'howto': 0.6}

What this tells us. Keyword search finds 17 of the 18 identifier questions, but only about half of the paraphrases and how-to tasks. “How do I see whether my query uses an index?” shares almost no words with “EXPLAIN QUERY PLAN”, so BM25 ranks the right section 18th. This is the number to beat: 0.708.

The models

Vector search with the local model, e5. The notebook loads the e5 vectors of the 4,300 chunks, made with the course’s model at its pinned revision. It checks the model and the revision before it compares any vector. Embedding all chunks took 108 seconds on the test laptop (2 threads), so the vectors are shipped with their checksum. Set RECOMPUTE = True to make them yourself.

e5 vectors: 4300 x 384
dense-e5 {'hit@1': 0.542, 'hit@5': 0.729, 'recall@5': 0.547, 'recall@10': 0.629, 'MRR@10': 0.621}
hit@5 by kind: {'identifier': 0.889, 'paraphrase': 0.6, 'howto': 0.667}

What this tells us. On Larkfield’s short policies, e5 beat BM25 clearly (0.915 against 0.831). Here it is only one question better on hit@5, although its first result is right more often (hit@1 0.542 against 0.458). e5 is a small multilingual model, and this is long, technical English. It misses -bail, a flag inside a long --help listing that BM25 ranks 2nd.

Vector search with the hosted model, embed-small. Its vectors were recorded once: 828,297 tokens for the chunks, US$0.0166, about 10 minutes with the shared deployment’s rate limits. The notebook replays them, so you need no key.

dense-embed-small {'hit@1': 0.688, 'hit@5': 0.917, 'recall@5': 0.691, 'recall@10': 0.75, 'MRR@10': 0.789}
hit@5 by kind: {'identifier': 1.0, 'paraphrase': 0.867, 'howto': 0.867}

What this tells us. The hosted model is the best single method on every kind of question. On Larkfield’s set, e5 matched it. A model that is “as good” on one collection can be clearly weaker on another.

Hybrid search and reranking. Hybrid search fuses the BM25 top 20 and a vector top 20 with reciprocal rank fusion. Rerank is the course default: the hybrid e5 top 20, reread by the cross-encoder.

hybrid-e5 {'hit@1': 0.604, 'hit@5': 0.792, 'recall@5': 0.58, 'recall@10': 0.724, 'MRR@10': 0.695}
hybrid-embed-small {'hit@1': 0.667, 'hit@5': 0.833, 'recall@5': 0.625, 'recall@10': 0.789, 'MRR@10': 0.752}
rerank {'hit@1': 0.625, 'hit@5': 0.875, 'recall@5': 0.69, 'recall@10': 0.751, 'MRR@10': 0.723}
rerank: 789 ms per question on this computer

What this tells us. Here, hybrid search with e5 helps: 0.729 → 0.792. BM25 and e5 fail on different questions, so their votes add up. With embed-small, hybrid search hurts: 0.917 → 0.833, because BM25’s weaker list pulls good results down. Larkfield showed this effect with e5; here it appears with the other model. Reranking adds four questions to hybrid e5 (six gained, two lost), for about 0.8 seconds per question.

The fair comparison

All six methods ran on the same chunks and the same 48 scored questions. They share the frozen labels and the measures.

• Keyword
• Local models (e5, cross-encoder)
• Hosted embed-small (recorded)

The share of questions with a relevant chunk in the top 5 (Hyb = hybrid, e-s = embed-small). The hosted model alone is highest; among the local methods, hybrid search with reranking is highest.

Text description of the chart

A column chart of hit@5 for six methods, on 48 questions. Keyword search, BM25: 0.708. Local models: e5 alone 0.729, hybrid with e5 0.792, hybrid e5 with reranking 0.875. Hosted model: embed-small alone 0.917, hybrid with embed-small (e-s) 0.833. The axis goes from 0 to 1.

By kind of question, the picture is clearer:

hit@5	Identifier (18)	Paraphrase (15)	How-to (15)	MRR@10, all
Keyword (BM25)	0.944	0.533	0.600	0.553
Vector, e5	0.889	0.600	0.667	0.621
Hybrid, e5	0.889	0.733	0.733	0.695
Hybrid e5 + rerank	1.000	0.800	0.800	0.723
Vector, embed-small	1.000	0.867	0.867	0.789
Hybrid, embed-small	0.944	0.800	0.733	0.752

Every method finds the 5 bare identifiers (“What is SQLITE_BUSY_SNAPSHOT?”) at rank 1. On Larkfield’s set, “What is LB-1?” defeated vector search. Why the difference? SQLite’s names are long and made of words (BUSY, SNAPSHOT, cacheflush), and each section names its subject in its heading. A short code like LB-1 carries no meaning for an embedding model. Bare identifiers are not hard in general; short, meaningless codes are.

The real differences are in paraphrases and how-to tasks. There, keyword search finds about half. The hosted model finds 26 of 30.

• embed-small (hosted, dashed)
• Hybrid e5 + rerank
• e5 alone (dashed)
• BM25

The share of all relevant passages found in the top k. Reranking and embed-small are close from k = 3, and from k = 5 their lines lie on top of each other. No method finds 80% of the passages, even at k = 10.

Text description of the chart

A line chart of recall@k at k = 1, 3, 5 and 10. embed-small: 0.408, 0.628, 0.691, 0.750. Hybrid e5 with reranking: 0.370, 0.603, 0.690, 0.751. e5 alone: 0.333, 0.503, 0.547, 0.629. BM25: 0.312, 0.465, 0.555, 0.604.

Recall stays below 0.8 even at k = 10 for every method. Many questions have three or four relevant sections on different pages. A user needs one good section, so hit@5 is the decision measure here. An assistant that must show both the FAQ and the pragma’s own page would need more than 5 chunks.

Is the difference real?

With 48 questions, one question is 0.021 of hit@5. So count the questions that only one of two methods finds. Then draw 10,000 bootstrap samples of the questions for a 95% interval of the difference:

dense-embed-small vs rerank: only dense-embed-small ['D26', 'D27', 'D39', 'D41', 'D44']; only rerank ['D30', 'D40', 'D45']; 95% interval of the hit@5 difference [-0.062, +0.146]
rerank vs hybrid-e5: only rerank ['D04', 'D18', 'D29', 'D32', 'D40', 'D43']; only hybrid-e5 ['D19', 'D41']; 95% interval of the hit@5 difference [-0.021, +0.208]
rerank vs bm25: only rerank ['D18', 'D22', 'D23', 'D32', 'D33', 'D35', 'D43', 'D45', 'D48']; only bm25 ['D41']; 95% interval of the hit@5 difference [+0.042, +0.292]

What this tells us. The hosted model beats the reranker on 5 questions and loses on 3. That is not proof: the interval goes from −0.062 to +0.146. Only one comparison here is clear: rerank against keyword search alone, 9 questions against 1. The data does not support Felix’s “hybrid always wins”. And “embed-small is better” is likely, but 48 questions do not prove it.

Where each method fails

The question “How do I check a database file for corruption?” is easy for a person: PRAGMA integrity_check. Look at the reranked top 5:

1   5.589 howtocorrupt | Mispairing database files and hot journals
2   4.241 howtocorrupt | Carrying an open database connection across a fork()
3   4.074 howtocorrupt | False corruption reports due to database shrinkage
4   3.611 howtocorrupt | File locking problems
5   3.479 howtocorrupt | Memory corruption
first relevant rank: {'bm25': 5, 'dense-e5': 7, 'dense-embed-small': 1, 'hybrid-e5': 1, 'hybrid-embed-small': 1, 'rerank': 8}

The real question D41. Fusion puts the right chunk first; the reranker, a second model, pushes it out of the top 5.

Text description of the diagram

Keyword search ranks the FAQ chunk with the answer 5th, and vector search 7th. Hybrid search adds 1/65 + 1/67 = 0.0303 and ranks it first, because both lists liked it. The cross-encoder then rereads the 20 candidates. Five sections of the page How To Corrupt An SQLite Database File get scores from 5.59 to 3.48. The FAQ answer gets 2.55. The answer falls to rank 8, out of the top 5.

The page How To Corrupt An SQLite Database File has the most words about corruption. So it looks most relevant to every model that reads words. The pragma’s own section says “consistency check”, not “corruption”. Here, fusion found the right answer and the reranker took it away. With the rerank top 5, the recorded answer abstained. It said: “The documentation passages do not say how to check a database file for corruption.” That is safe, but the documentation does say it.

The other failures follow the same pattern. When a user’s words differ from the documentation’s words, keyword search misses, and the small e5 model often misses too. “Can a column fill itself with the time the row was added?” means DEFAULT CURRENT_TIMESTAMP, and only the hosted model has it in its top 5.

Change one thing. Reranking can only reorder what the first stage found. So give it more: 40 candidates instead of 20, on the 30 paraphrase and how-to questions (notebook task 3):

40 candidates: 0.733 (1367 ms per question)
20 candidates: 0.8 (789 ms per question)

More candidates made it worse, at almost twice the time. The extra candidates gave the cross-encoder more wrong chunks to promote. “More” is a setting to measure, not a free gain.

Time and cost

Method	Index the 4,300 chunks	Per question (median, laptop)	Money	Questions leave the computer
BM25	32.7 ms	3.93 ms	0	No
e5	108 s (2 CPU threads)	6.65 ms, plus 1.06 ms to search	0 (a 470 MB download)	No
Hybrid e5	both of the above	about 10 ms	0	No
Hybrid e5 + rerank	both of the above	790 ms	0 (a second 470 MB model)	No
embed-small	US$0.0166, about 10 min	180–770 ms per call (8 calls, recorded)	about US$0.0000003 per question	Yes, every question goes to the provider

Searching 4,300 vectors with NumPy takes about 1 ms, so this collection still needs no vector database. The slow step is the reranker, about 0.7 to 0.8 seconds per question on a laptop CPU. The hosted model costs almost nothing in money here. Its real costs are a network call per question, an account and a key, and every user’s question leaving the company.

The recommendation

Felix’s slogan does not hold. On this collection, hybrid search helps one embedding model and hurts the other. The recommendation for Dana’s team:

1. If the team may send its users’ questions to an external provider, use embed-small alone. It has the best hit@5 (0.917), the best first result (hit@1 0.688) and the best MRR (0.789), with no reranker. Check the provider’s terms first: the documentation is public, but the questions may contain customer names or error logs.
2. If questions must stay on the company’s computers, use hybrid e5 with reranking (0.875), and accept about 0.8 seconds per question. If that is too slow for a search box, hybrid e5 without the reranker (0.792) answers in about 10 ms.
3. Do not choose between options 1 and 2 on these numbers alone. The difference is 5 questions against 3. Add the team’s next 50 real questions, label them the same way, and measure again.

Whatever the method, keep keyword search available for exact names. It is fast and free, and it finds 17 of 18 identifier questions.

End to end: a new question with a cited answer

Here is a new support question, not in the set: “A nightly cron job and my web app both write to the same SQLite file, and the app sometimes fails with ‘database is locked’. What can I change?” The notebook runs the local rerank pipeline on it. Then it replays the answer that chat-small (gpt-6-luna on Azure) gave to the same five passages. The prompt follows Answer and cite, with “SQLite documentation, version 3.53.4” in place of Larkfield’s policies.

  65ef89eb3990 https://sqlite.org/rtree.html#reading_and_writing_at_the_same_time | The SQLite R*Tree Module
  398ca44b61b8 https://sqlite.org/c_interface.html#4_error_codes | The C language interface to SQLite Version 2
  ed0c02ce8939 https://sqlite.org/lockingv3.html#writing | File Locking And Concurrency In SQLite Version 3
  db447e805ee1 https://sqlite.org/tclsqlite.html#the_timeout_method | The Tcl interface to the SQLite library
  9c8a6b3b7716 https://sqlite.org/c_interface.html#4_error_codes | The C language interface to SQLite Version 2

chat-small (gpt-6-luna on Azure, recorded): SQLite allows only one writer at a time, so coordinate the cron job and app to avoid overlapping writes. If your app uses the Tcl interface, increase its lock wait with the `timeout` method; the default is 0 milliseconds. [398ca44b61b8] [db447e805ee1]
 - SQLite permits only one process or thread to have the database open for writing at a time, and the lock applies to the entire database. ['398ca44b61b8'] problems: none
 - The Tcl `timeout` method controls how long SQLite waits for locks; its default is 0 milliseconds, and it can be set to a positive value. ['db447e805ee1'] problems: none

A real recorded answer. Every claim cites a passage that supports it, and the answer is still not the one a developer needs.

Text description of the diagram

Two claims, each linked to its passage. Claim 1, that only one process or thread can write at a time, cites the page about the C interface of SQLite version 2. Claim 2, about the Tcl timeout method, cites the Tcl interface page. The code checks pass for both. Apart, in a dashed box: the answer that a developer needs, PRAGMA busy_timeout or sqlite3_busy_timeout(), was not in the top 5.

Every claim passes the code checks, and the answer is poor. One source is a page about version 2, and the other is about the Tcl interface, which the user never mentioned. The right advice, a busy timeout, was not in the top 5, so the model could not give it. This is the lesson of Answer and cite on new data: a citation shows where a claim comes from. It does not show that the source is right for this version and this user.

The other recorded answers behave as expected. With a relevant chunk in the top 5, chat-small answered correctly. Examples are “The -bail option makes the shell stop after hitting an error” and the 281-terabyte limit. Without one, it abstained, on three answerable questions and on the two unanswerable ones in the recording. Better retrieval, again, is what would improve these answers most. The 9 recorded answers cost US$0.0020 in all.

Common mistake

A score threshold to detect unanswerable questions

Symptom: Felix proposes a shortcut: “If the reranker’s best score is low, say that the documentation does not cover it.” For “What is the default value of PRAGMA wal_buffer_size?”, a pragma that does not exist, the reranker’s best score is 6.12.

Cause: the made-up name is close to real pragmas (wal_autocheckpoint, journal_size_limit), so the cross-encoder scores them high. Half of the real, answerable questions (24 of 48) have a best score below 6.12. No threshold separates the two groups.

Fix: leave abstention to the answer step. It reads the passages and must say “the documentation passages do not say” when they do not answer (Handle difficult evidence). chat-small abstained on this question.

For a real AI service: test abstention with questions about made-up names and other products, as this set does. Scores rank the chunks for one question; they do not compare across questions.

Insights and limits

• The best method depends on the collection and on the embedding model. On Larkfield’s policies, hybrid search with e5 hurt. Here it helps, and hybrid search with embed-small hurts. Measure on your own questions.
• Exact names are easy when they are long and made of words, and when each section names its subject in a heading. Paraphrases and how-to questions decide the comparison.
• A second model is not always a better judge: the reranker removed the right chunk for D41, and 40 candidates were worse than 20.
• The documentation itself carries traps: identical pages, a version 2 page, and sections whose headings are bold text that the parser cannot see (limits.html).

The limits: 48 scored questions, written and labelled by one person; one chunker and one chunk size; one snapshot (3.53.4). The hosted vectors were recorded once, and the provider can change the model behind the name. Times come from one laptop with 2 threads.

Responsible use

• Cite the version. Every answer should name SQLite 3.53.4 and link the exact section. Documentation changes with releases: a default or a limit can change, and old pages (version 2) stay in the archive.
• Do not present the answers as the project’s official support. The SQLite developers did not write or review these answers. Say that the assistant searches a public-domain copy of the documentation, and link sqlite.org.
• Keep users’ questions private. The documentation is public, but questions can contain customer data and error logs. A hosted embedding model sends every question to the provider.
• Keep a person in the loop for actions. A wrong answer about recovery, corruption or deleting data can cause harm. The assistant shows evidence; a developer decides.

Make it a portfolio piece

Put the notebook in a public repository with a short README. A template:

# Searching the SQLite documentation: keyword, vector, hybrid and reranking

## Problem
A support team needs the right section of the SQLite 3.53.4 documentation,
both for exact names (SQLITE_BUSY, PRAGMA journal_size_limit)
and for questions in plain words.

## Data and licence
195 pages of the official SQLite 3.53.4 documentation (public domain),
4,300 chunks with page, section and URL. 53 questions (identifier,
paraphrase, how-to, unanswerable), labelled by pooling the top 5 of
six methods and reading 637 chunks.

## Approach
BM25 (SQLite FTS5), e5 (local), embed-small (hosted, recorded),
hybrid with RRF, cross-encoder reranking. hit@5, recall@k, MRR@10 and
hit@1 by kind of question; paired counts and a bootstrap interval.

## Results
hit@5: BM25 0.708, e5 0.729, hybrid e5 0.792, rerank 0.875,
embed-small 0.917. Hybrid helps e5 and hurts embed-small.
Only rerank vs BM25 is a clear difference on 48 questions.

## Limits
One labeller, 48 scored questions, one snapshot. Not SQLite's official support.

In an interview, say it in a few sentences:

I compared six search methods on the SQLite documentation, with 53 questions that I labelled by pooling. The hosted embedding model found the most. But on 48 questions only one difference was clear, so I recommended measuring again with real user questions.

Go further: add 50 questions from a real forum and label them the same way. Try a second chunk size at an equal context size, as in Chunk deliberately. Remove the duplicate fileformat2 pages and measure the change. Label each page with the version that it describes, and keep pages about version 2 out of the results.

Knowledge check

Check your understanding

On Larkfield's policies, hybrid search with e5 scored below e5 alone. Here it scores above. Explain why both results can be true, and what Felix should conclude.

Show the answer

Hybrid search adds votes from two lists. It helps when the two methods fail on different questions and both lists are reasonable. That is the case here: BM25 finds exact names that e5 misses (-bail), and e5 finds paraphrases. It hurts when one list is weak for many questions, and its low ranks pull good results down. That happened with the French questions on Larkfield’s set, and with embed-small here (0.917 → 0.833). Felix should conclude that no method wins everywhere, and measure on his own questions before he ships.

Check your understanding

The hosted model has hit@5 0.917 and the reranker 0.875. Dana asks whether that proves the hosted model is better. What do you answer?

Show the answer

Not on 48 questions. The net difference is 2 questions. The hosted model finds 5 questions that the reranker misses, and the reranker finds 3 that the hosted model misses. A bootstrap interval of the difference goes from −0.062 to +0.146, so it includes 0. The decision should also weigh the costs. The hosted model sends every question to a provider, and the reranker takes about 0.8 s per question. Add more real questions, label them the same way, and measure again.

Check your understanding

The recorded answer to the 'database is locked' question passes every citation check. Name two reasons why it is still a poor answer, and one change that would fix the cause.

Show the answer

First, one of its sources describes SQLite version 2, not 3.53.4. Second, its advice is for the Tcl interface, which the user never mentioned. The cause is retrieval. The sections about PRAGMA busy_timeout and sqlite3_busy_timeout() were not in the top 5, so the model could not use them. One fix: label each page with the version it describes and keep version 2 pages out of the results. Another: compare methods on this kind of question. For the similar question D19 in the set, hybrid e5 ranks a relevant section 2nd, and the reranker 14th. In every case, show the page and version with each claim, so that a person can judge it.

Recap

• On 195 pages of SQLite documentation and 48 scored questions, the hosted embedding model alone found the most (hit@5 0.917). The best local method was hybrid e5 with reranking (0.875), and keyword search alone found 0.708.
• Hybrid search helped e5 and hurt embed-small. Most differences between the strong methods were 1 to 3 questions: not proof.
• Pooling the top results of every method, and reading them, found 40 relevant passages that the first labels missed.
• A cited answer can pass every check and still come from the wrong version or interface. Cite the version, and do not present answers as official support.

Next, the last case study adds time: answer from regulations that change, and cite the version in force on a date.

Next: Answer from versioned regulations