**Quick Start:** Build a working RAG pipeline with ten documentation chunks, one SQLite table, and two queries in about 30 minutes. It runs locally without a vector database, embedding model, or API key. Then send the prompt to your chosen chat assistant, hosted model, or local Ollama model. To test the queries first, visit [Practice RAG][practice-rag], which runs real SQLite in your browser. You ask your AI assistant how to roll back a deploy. It confidently suggests `git revert` and `kubectl rollout undo`, but your team does neither. The real answer is in `runbook/deploy.md`, which the model hasn’t seen. It also warns not to touch migrations, a critical detail the generic answer misses. RAG fixes that gap with a step you can build in half an hour: find the few paragraphs of your own docs that answer the question, and put them in the prompt. ## What You'll Learn * What is retrieval augmented generation, and what problem does it solve? * When is RAG the right tool, and when should you paste everything into the prompt instead? * How do you split documents into chunks a model can use? * How do you rank chunks for a question with SQLite's full-text search (FTS5) and BM25 scoring? * How do you build a prompt that makes the model answer from your docs and cite them? * How do you test retrieval before you blame the model? * How do you point the same pipeline at your own repository? * Where can you go to learn more about RAG? ## The Basics Retrieval-augmented generation (RAG) adds relevant text from a controlled source to a language model's prompt before it answers. The term comes from a 2020 paper by [Patrick Lewis and eleven coauthors][lewis-rag], describing models that "combine pre-trained parametric and non-parametric memory for language generation." In plain terms, parametric memory is learned during training; non-parametric memory is documents provided at question time. Every RAG system, from a weekend script to a managed cloud product, runs the same three steps: ```mermaid flowchart TB Q[Question] --> R[Retrieve: rank your chunks, keep the top few] R --> A[Augment: put those chunks and the question in one prompt] A --> G[Generate: the model answers from the chunks and cites them] ``` This guide covers the 20% of RAG that delivers 80% of its value: chunking, BM25 keyword retrieval (a search-engine ranking function), a grounded prompt, and retrieval checks. It skips embeddings, vector databases, rerankers, and agent frameworks, which are useful for large or messy corpora, with resources provided at the end. Keyword search teaches the pattern; [Simon Willison built a working RAG][willison-search-rag] similarly, turning questions into SQLite full-text queries. ### Primary Use Cases * Answer questions about your project's runbooks, architecture decision records (ADRs), and READMEs. * Find past incidents and their fixes when a similar error shows up. * Ground an assistant in internal APIs, conventions, and decisions it was never trained on. * Give new teammates cited answers instead of a link to a wiki search box. ### Less Suitable Use Cases * A small set of docs that fits in the prompt. Anthropic's [Contextual Retrieval write-up][contextual-retrieval] says a knowledge base under 200,000 tokens, about 500 pages, can go into the prompt whole, with no need for RAG. * Teaching the model a new skill or output style. Retrieval adds facts, and examples in the prompt or fine-tuning change behavior. * Questions that need every document at once, such as "count the ADRs that mention Postgres." Run SQL or `grep` for those. * Code navigation inside an editor. Coding assistants already search your files with their own tools. ### When to Use RAG Use RAG when your docs are larger than the context window, or when sending all of them on every question costs too much or takes too long. Start with keyword search like the one below, and measure whether the right chunk comes back. Add embeddings only after the misses turn out to be vocabulary gaps, such as `revert` in the question and `roll back` in the docs. ## The 20% That Does 80% All examples use the same small set of docs for Parcel, a fictional shipping-label service. Set it up first. ### Set up the practice docs **You'll need:** * `sqlite3` 3.44 or later, the SQLite command-line shell. Version 3.44 [added `ORDER BY` inside aggregates][sqlite-3-44], which the prompt step uses. * A terminal and a text editor. * Basic SQL. [Learn SQL][learn-sql] covers `SELECT`, `WHERE`, and CTEs if you need a refresher. * Optionally, for the generate step, Simon Willison's [LLM][llm-setup] command-line tool, `llm`, with a model it can reach, or [Ollama][what-is-ollama] for a local model. Check your version from your terminal: ```bash {practice="machine"} sqlite3 --version ``` Any version from 3.44 up works. Every output in this guide comes from SQLite 3.54.1. Save this as `parcel-docs.sql`. Each row is one chunk: one section of one document. ```sql CREATE VIRTUAL TABLE chunks USING fts5( source UNINDEXED, heading, body, tokenize = 'porter unicode61' ); INSERT INTO chunks (source, heading, body) VALUES ('README.md', 'Run locally', 'Start the stack with make up. The API listens on port 8080, and the worker reads jobs from the labels queue.'), ('README.md', 'Run the tests', 'Run make test for unit tests. Integration tests need the stack running, so run make up first.'), ('adr/0003-use-postgres.md', 'Decision', 'Store labels in PostgreSQL 16 instead of DynamoDB. Labels join to carrier rates, and the team already runs Postgres for billing.'), ('adr/0003-use-postgres.md', 'Consequences', 'Schema changes go through migrations in db/migrations. Never edit a migration that has shipped; add a new one.'), ('adr/0007-use-sqs.md', 'Decision', 'Send render jobs through SQS instead of a Postgres table, because label rendering spikes before carrier cutoffs.'), ('incidents/2026-03-12.md', 'Summary', 'Label rendering stopped for 40 minutes after a carrier rotated its API certificate. The worker retried forever and filled the queue.'), ('incidents/2026-03-12.md', 'Fix', 'Pin the carrier certificate chain in config/carriers.yml, and cap worker retries at five with exponential backoff.'), ('runbook/deploy.md', 'Deploy', 'Deploys run from the main branch through the deploy workflow. Each deploy tags the image with the commit SHA.'), ('runbook/deploy.md', 'Roll back', 'To roll back, rerun the deploy workflow with the previous image tag. Leave migrations alone during a rollback; the old code tolerates the new columns.'), ('runbook/on-call.md', 'When to page', 'Page the on-call engineer in #parcel-oncall when label failures stay above 2 percent for 10 minutes.'); ``` Load it and open the shell: ```bash {practice="machine"} sqlite3 parcel.db < parcel-docs.sql sqlite3 parcel.db ``` To start over, leave the shell with `.quit`, then delete the database and load it again from your terminal. Loading the file twice into the same database fails on `CREATE VIRTUAL TABLE` and then inserts every chunk a second time. ```bash {practice="machine"} rm parcel.db && sqlite3 parcel.db < parcel-docs.sql ``` Turn on readable output inside the shell, and confirm the load: ```sql .headers on .mode column SELECT count(*) AS chunks FROM chunks; ``` ```text chunks ------ 10 ``` **What you get:** a `parcel.db` file holding a full-text index over ten chunks, and the SQLite shell's prompt (`SQLite-3.54 parcel.db->` in 3.54, `sqlite>` in older versions). ### Chunk by heading Chunks are the units retrieval returns, determining what the model sees. Split documents by headings; each section covers one idea, has a label, and is short enough to fit three or four in a prompt. Keep each chunk’s file path so the model can cite its source and you can verify the answer. The `source` column is `UNINDEXED`: it [stores the path without adding it to the full-text index][fts5-unindexed], so questions never match on file-path words. **What you get:** chunks small enough to rank precisely and labeled well enough to cite. ### Index with FTS5 [FTS5][fts5] is SQLite's built-in full-text search engine. `CREATE VIRTUAL TABLE ... USING fts5` builds an inverted index: for every word, the rows that contain it. The `porter` tokenizer [applies the Porter stemming algorithm][fts5-porter] to each word, so `retrying` matches `retried` and `certificates` matches `certificate`: ```sql SELECT source, heading, round(bm25(chunks), 2) AS score FROM chunks WHERE chunks MATCH 'certificates OR retrying' ORDER BY score LIMIT 3; ``` ```text source heading score ----------------------- ------- ----- incidents/2026-03-12.md Fix -2.56 incidents/2026-03-12.md Summary -2.41 ``` Neither chunk contains the word `certificates` or `retrying`, and stemming found both anyway. **What you get:** an index that finds `retried` when you search for `retrying`. ### Retrieve the top chunks with BM25 `MATCH` finds rows; `bm25()` ranks them. BM25, a [search-engine ranking function][bm25-wiki], stands for "best matching". It scores chunks higher when they contain more query words, those words are rarer across chunks, and the chunk is shorter. FTS5 returns negative scores, with [better matches numerically lower][fts5-bm25], so sort ascending. Plain words in a `MATCH` query must all appear, which is precise and brittle: ```sql SELECT source, heading FROM chunks WHERE chunks MATCH 'roll back' ORDER BY bm25(chunks); ``` ```text source heading ----------------- --------- runbook/deploy.md Roll back ``` Join words with `OR` to match any; BM25 ranks the best chunks first. `LIMIT` sets how many reach the prompt (top-k): ```sql SELECT source, heading, round(bm25(chunks), 2) AS score FROM chunks WHERE chunks MATCH 'roll OR back OR deploy' ORDER BY score LIMIT 3; ``` ```text source heading score ----------------- --------- ----- runbook/deploy.md Roll back -5.81 runbook/deploy.md Deploy -2.09 ``` Only two chunks contain these words, so `LIMIT 3` returns two. The rollback section wins because it contains all three. **What you get:** a ranked shortlist of the chunks most likely to answer the question. ### Turn a question into a search query A raw question isn’t a valid FTS5 query; punctuation outside double quotes causes a syntax error. ```sql SELECT source, heading FROM chunks WHERE chunks MATCH 'how do I roll back a deploy?'; ``` ```text Error near line 1: fts5: syntax error near "?" ``` Convert the question in three steps: lowercase it, remove filler words like `how`, `do`, `I`, and `a`, then join the rest with `OR`. Quote each word (`"roll" OR "back"`) to prevent terms like `NOT` or hyphens in user input from becoming operators. The Python script in [Build Something][build-something] does this; Simon Willison's version also asks the model to write the search query. The practice's Retrieve stage runs the same conversion in SQL: it loads the question into a scratch FTS5 table, reads its words back with `fts5vocab`, drops the stop words, and joins the rest with `OR`. **What you get:** any question, safely turned into a query FTS5 accepts. ### Augment: build the prompt The prompt has three parts: context-setting instructions, retrieved chunks with sources, and the question. This query builds all three. `group_concat(... ORDER BY score)` puts the best chunk first: ```sql .headers off .mode list --limits off WITH hits AS ( SELECT source, heading, body, bm25(chunks) AS score FROM chunks WHERE chunks MATCH 'roll OR back OR deploy' ORDER BY score LIMIT 3 ) SELECT 'Answer the question using only the context below. ' || 'Cite the source file for each claim. ' || 'If the context does not answer it, say so.' || char(10) || char(10) || group_concat( '[' || source || ' > ' || heading || ']' || char(10) || body, char(10) || char(10) ORDER BY score ) || char(10) || char(10) || 'Question: How do I roll back a deploy?' FROM hits; ``` ```text Answer the question using only the context below. Cite the source file for each claim. If the context does not answer it, say so. [runbook/deploy.md > Roll back] To roll back, rerun the deploy workflow with the previous image tag. Leave migrations alone during a rollback; the old code tolerates the new columns. [runbook/deploy.md > Deploy] Deploys run from the main branch through the deploy workflow. Each deploy tags the image with the commit SHA. Question: How do I roll back a deploy? ``` `--limits off` keeps an interactive SQLite 3.54 shell from cutting the prompt at 300 characters; a 3.44 or later shell that predates the option prints `extra argument: "off"` and runs the query anyway. Each instruction serves a purpose: "Only the context below” prevents generic advice, "cite the source file” makes claims verifiable, and "say so" discourages guessing. **What you get:** a prompt that turns a generic assistant into one that answers from your runbook. ### Generate: hand the prompt to a model Paste the prompt into a chat assistant or pipe it to a command-line client. If you haven't used LLM before, install it and set an API key for its default hosted model; see the [setup guide][llm-setup]. Save the query as `ask.sql`, then pipe its output to [LLM][llm-usage], Simon Willison's command-line tool, which reads prompts from standard input: ```bash {practice="machine"} sqlite3 parcel.db < ask.sql | llm ``` To keep your docs on your machine, use a model running in [Ollama][what-is-ollama]. Install the [llm-ollama plugin][llm-ollama], which registers each Ollama model under its name prefixed with `ollama/`, then pass that name with `-m`. This needs Ollama installed and running: ```bash {practice="machine"} llm install llm-ollama ollama pull gemma3 sqlite3 parcel.db < ask.sql | llm -m ollama/gemma3 ``` The answer's wording changes from model to model and run to run, so judge it by its claims. It should say to rerun the deploy workflow with the previous image tag, warn against reverting migrations, and cite `runbook/deploy.md`. **What you get:** an answer grounded in your docs, with citations you can check. ### Check retrieval before you blame the model Wrong RAG answers usually stem from retrieval: the model can only use what it receives. Keep a few questions with known answers and verify the correct file appears in the top three: ```sql .headers on .mode column WITH checks(query, expected) AS (VALUES ('roll OR back OR deploy', 'runbook/deploy.md'), ('postgres OR dynamodb', 'adr/0003-use-postgres.md'), ('retry OR certificate', 'incidents/2026-03-12.md'), ('revert OR release', 'runbook/deploy.md') ) SELECT query, expected, CASE WHEN expected IN ( SELECT source FROM chunks WHERE chunks MATCH checks.query ORDER BY bm25(chunks) LIMIT 3 ) THEN 'hit' ELSE 'MISS' END AS result FROM checks; ``` ```text query expected result ---------------------- ------------------------ ------ roll OR back OR deploy runbook/deploy.md hit postgres OR dynamodb adr/0003-use-postgres.md hit retry OR certificate incidents/2026-03-12.md hit revert OR release runbook/deploy.md MISS ``` The last check fails because the runbook says "roll back" and "deploy," while the question says "revert" and "release." Keyword search misses the match. Add the docs’ wording to the query, and it hits: ```sql SELECT source, heading FROM chunks WHERE chunks MATCH 'revert OR release OR roll OR deploy' ORDER BY bm25(chunks) LIMIT 3; ``` ```text source heading ----------------- --------- runbook/deploy.md Roll back runbook/deploy.md Deploy ``` The other fix edits the doc instead: when readers keep asking in words the docs never use, such as `revert`, add those words to the section that answers them. In production, a synonym list or model-written query can rewrite searches. Frequent vocabulary misses signal it’s time to add embeddings, which match by meaning. Anthropic's [Contextual Retrieval tests][contextual-retrieval] added model-written context to each chunk and paired embeddings with BM25, reducing failed top-20 retrievals by 49%. Add embeddings alongside keyword search, but keep BM25. **What you get:** a repeatable test that tells you whether to fix retrieval or the prompt. ## Practice RAG Try the pipeline in your browser: this practice runs real SQLite 3.54.1 on six Parcel docs, with no model call. Press Start practice, and every stage fills in with the default run: docs become chunks, chunks become an index, and the question becomes a ranked chunk list and a prompt. Each stage shows the shell's output, with the SQL it ran folded under a summary such as **Rank SQL**, and every stage your last change altered is marked **changed**. Top-k sets both how many chunks reach the prompt and the cutoff the checks use. The Prompt stage's **Instructions** box starts with `rag.py`'s instructions: edit them to see what the prompt carries, and press Reset to restore them. Work through these tasks in order, in about 20 minutes. Once the practice starts, it shows them one at a time at its top, with Previous task and Next task, so they stay beside the stages. Most ask for a prediction: make it, run the pipeline with Run pipeline or Ctrl+Enter, then open Check your prediction. 1. **Docs.** The ready line counts 6 docs and 10 chunks, and `runbook/deploy.md` is open in the editor. Each `##` heading in it starts a section. 2. **Chunks.** At the end of `runbook/deploy.md`, add a `## Notes` heading with nothing under it. How many chunks will Index count? Run it, then add one sentence under Notes and predict again. See [Chunk by heading][chunk-by-heading].
Check your prediction Still 10 after the empty heading, because a heading with no text beneath it makes no chunk. Press Show chunker code and find the `if (b)` test in `flush`: that is the line that drops it. With a sentence under Notes, Index counts 11.
Now replace that sentence with a fenced `bash` block holding the lines `# restart the worker` and `make restart`. Does the `#` line start a new chunk?
Check your prediction No. Index still counts 11: the chunker ignores `#` lines inside a fence, so the comment stays in the Notes chunk.
3. **Index.** Press Reset, then ask `certificates retrying`. Neither word appears in any doc. Will Rank list anything? See [Index with FTS5][index-fts5].
Check your prediction Both incident chunks, Fix first, then Summary. The `porter` tokenizer indexed `retried` and `certificate` by their stems, and it stems the question's words the same way.
4. **Retrieve.** Ask `How do I revert a release?`. What will Retrieve say? Then rewrite the question until Rank lists `runbook/deploy.md` and `Roll back` first. See [Turn a question into a search query][question-to-query].
Check your prediction `No chunks match: "revert" OR "release"`, and Copy prompt stays off: no doc uses either word. The runbook's own words work, as in `How do I roll back a release?`.
5. **Prompt.** Ask `How do I roll back a deploy?` with top-k 1, then with top-k 3. Which chunks will each prompt hold? See [Augment: build the prompt][augment].
Check your prediction Top-k 1 holds the Roll back chunk alone. Top-k 3 holds Roll back, then Deploy: only two chunks match, so raising the limit adds just one.
6. **Answer.** Press Copy prompt, paste it into your assistant, and check that every citation names a file Retrieve listed. A grounded answer says to rerun the deploy workflow with the previous image tag, says to leave migrations alone, and cites `runbook/deploy.md`. See [Generate: hand the prompt to a model][generate]. 7. **Check.** The fourth check reads `MISS`. Change its query, not its expected file, until it finds `runbook/deploy.md`, then run. Ctrl+Enter works in the check fields too. See [Check retrieval before you blame the model][check-retrieval].
Check your prediction Add the runbook's words, as in `revert OR release OR roll OR deploy`. `MISS` becomes `hit`, and only Check is marked changed, because nothing before it changed.
Press Reset and fix the same miss the other way: keep the query `revert OR release`, and add the sentence `To revert a release, do the same.` at the end of the Roll back section of `runbook/deploy.md`. Will check 4 hit?
Check your prediction Yes. The doc now holds the words readers ask with, so the original query finds it.
Press Reset, then add a fifth check: query `migrations`, expected file `runbook/deploy.md`. Will it hit at top-k 3? At top-k 1?
Check your prediction It hits at top-k 3 and misses at top-k 1. ADR 0003's Consequences section says `migration` three times in fewer words, so it ranks first and pushes the runbook past a one-chunk cutoff.
8. **Your turn.** Press Reset. A teammate asks `The worker retries forever after a carrier changes its certificate. What was the fix?` Which chunk will rank first? Get a prompt whose first chunk is the incident's Fix, add a check that guards it, and confirm the check hits at top-k 1.
Check your prediction Summary ranks first, because the question repeats its words (`forever`, `after`), and at top-k 3 ADR 0003's Consequences chunk rides in on `changes`. Asking `How do I fix carrier certificate retries?` puts Fix first: `fix` matches only that chunk's heading. A check such as `retries OR carrier OR certificate`, expecting `incidents/2026-03-12.md`, hits at top-k 1.
9. **Your doc.** Press Add doc with a path such as `notes/my-runbook.md`, paste one of your own Markdown docs into it, ask a question only that doc answers, and add a check for it. 10. **Not in the docs.** Ask `How do I deploy to staging?`. Will Rank find anything? Copy the prompt and see whether your assistant says the context does not cover staging.
Check your prediction Rank lists the Deploy and Roll back chunks, because both say `deploy`, but neither mentions staging. Retrieval can find the right file and still miss the answer, which is why the prompt says "If the context does not answer it, say so."
These six docs fit in any prompt with room to spare, so by the rule in [Less Suitable Use Cases][less-suitable] you would skip RAG and paste them whole. What would have to change about your own docs for retrieval to pay off? Each stage's SQL ends its `.mode` line with `--limits off`: without it, SQLite 3.54's shell limits each value to five lines or 300 characters and cuts chunks and the prompt short. The practice runs in a browser tab, so it differs from a pipeline on your machine in six ways: * **No model call.** The Answer stage displays the prompt and a Copy prompt button. It requires no API key and sends no request. * **No files, terminal, or Python.** A question that starts with `python3`, `sqlite3`, `rag.py`, or `llm` runs as plain text, and the Retrieve stage says where those tools run. * **An in-memory database.** Your edits to the docs persist until you leave or reload the page. Reset restores the practice docs, the default question, top-k 3, and the default checks without downloading anything again. * **Runs stopped after 10 seconds.** A longer run is stopped, the practice docs are reloaded, and the next run starts from Chunks. * **Output past 2,000 lines is cut.** A stage shows the first 2,000 lines or 256 KB, noting the rest is hidden. SQLite stores every chunk, keeping the Index count accurate. * **Editable prompt instructions.** The Prompt stage's instructions start as `rag.py`'s. Once you edit them, the prompt is one `rag.py` never prints, because its instructions are fixed in the script. To run the pipeline on your own machine, continue to [Build Something][build-something], which wraps these stages in one Python script. For a hosted version of the same keyword-first idea, read Simon Willison's [Building search-based RAG using Claude, Datasette and Val Town][willison-search-rag]. ## Build Something: RAG Over Your Own Docs Point the pipeline at a repository. A 65-line Python script indexes Markdown files by heading and prints a ready prompt for any question. ### Prerequisites **You'll need:** * Python 3 whose `sqlite3` module includes FTS5. Check it in Step 1. * A repository with Markdown docs, such as a `docs/` folder or a set of ADRs. * Optionally, the `llm` command-line tool for the last step. * About 15 minutes. ### Step 1: Check that your Python's SQLite has FTS5 ```bash {practice="machine"} python3 -c "import sqlite3; sqlite3.connect(':memory:').execute('CREATE VIRTUAL TABLE t USING fts5(x)'); print(sqlite3.sqlite_version)" ``` **Expected output:** a version number, such as `3.54.1`. An error ending in `no such module: fts5` means that Python build lacks FTS5; see Troubleshooting. ### Step 2: Save the script Save this as `rag.py` in your repository's root: ```python {practice="machine"} """The simplest RAG retriever: Markdown chunks in SQLite FTS5, ranked by BM25.""" import re import sqlite3 import sys from pathlib import Path DB = "rag.db" STOP = {"a", "an", "and", "are", "can", "do", "does", "for", "how", "i", "in", "is", "it", "my", "of", "on", "or", "the", "to", "we", "what", "when", "where", "which", "why", "with", "you"} INSTRUCTIONS = ("Answer the question using only the context below. " "Cite the source file for each claim. " "If the context does not answer it, say so.") def chunks(path): """Yield (heading, body) pairs, one per Markdown section.""" heading, lines, fenced = path.stem, [], False for line in path.read_text(encoding="utf-8").splitlines(): if line.startswith("```"): fenced = not fenced if not fenced and re.match(r"#{1,6} ", line): yield heading, "\n".join(lines).strip() heading, lines = line.lstrip("#").strip(), [] else: lines.append(line) yield heading, "\n".join(lines).strip() def index(folder): db = sqlite3.connect(DB) db.execute("DROP TABLE IF EXISTS chunks") db.execute("CREATE VIRTUAL TABLE chunks USING fts5(" "source UNINDEXED, heading, body, tokenize = 'porter unicode61')") for path in sorted(Path(folder).rglob("*.md")): for heading, body in chunks(path): if body: db.execute("INSERT INTO chunks VALUES (?, ?, ?)", (str(path), heading, body)) db.commit() count = db.execute("SELECT count(*) FROM chunks").fetchone()[0] print(f"Indexed {count} chunks into {DB}") def ask(question, k=3): terms = [t for t in re.findall(r"\w+", question.lower()) if t not in STOP] if not terms: sys.exit("Ask with at least one keyword.") query = " OR ".join(f'"{t}"' for t in terms) rows = sqlite3.connect(DB).execute( "SELECT source, heading, body FROM chunks WHERE chunks MATCH ? " "ORDER BY bm25(chunks) LIMIT ?", (query, k)).fetchall() if not rows: sys.exit(f"No chunks match: {query}") context = "\n\n".join(f"[{s} > {h}]\n{b}" for s, h, b in rows) print(f"{INSTRUCTIONS}\n\n{context}\n\nQuestion: {question}") if __name__ == "__main__": if len(sys.argv) == 3 and sys.argv[1] == "index": index(sys.argv[2]) elif len(sys.argv) == 3 and sys.argv[1] == "ask": ask(sys.argv[2]) else: sys.exit('Usage: python3 rag.py index DOCS_DIR | ask "QUESTION"') ``` The `fenced` flag ignores `#` lines in code blocks, preventing shell comments from starting new chunks. The question is passed to `MATCH` as a parameter, with each word quoted, preventing user input from breaking the query. **What you learned:** retrieval and augmentation fit in one function, `ask()`, and indexing in another. ### Step 3: Add two practice docs (optional) To check your output, create two Parcel docs as Markdown files in the `rag-practice` scratch folder to avoid overwriting repository files. ```bash {practice="machine"} mkdir -p rag-practice/runbook rag-practice/adr cat > rag-practice/runbook/deploy.md <<'EOF' # Deploy runbook ## Deploy Deploys run from the main branch through the deploy workflow. Each deploy tags the image with the commit SHA. ## Roll back To roll back, rerun the deploy workflow with the previous image tag. Leave migrations alone during a rollback; the old code tolerates the new columns. EOF cat > rag-practice/adr/0003-use-postgres.md <<'EOF' # ADR 0003: Use PostgreSQL ## Decision Store labels in PostgreSQL 16 instead of DynamoDB. Labels join to carrier rates, and the team already runs Postgres for billing. ## Consequences Schema changes go through migrations in db/migrations. Never edit a migration that has shipped; add a new one. EOF ``` ### Step 4: Index your docs If you skipped Step 3, pass your own docs folder in place of `rag-practice`. ```bash {practice="machine"} python3 rag.py index rag-practice ``` **Expected output,** with the two practice docs from Step 3: ```text Indexed 4 chunks into rag.db ``` A heading without text beneath it, such as a page title followed by a subheading, creates no chunk. Front matter becomes part of a Markdown file’s first chunk, so remove it first if your docs use it. Rerun `index` after doc changes; it rebuilds the table from scratch. Ignore `rag.db` in `.gitignore` as a build artifact. The chunker splits only on `#` headings and tracks backtick fences, so underlined headings and `~~~` fences remain in their surrounding chunks. ### Step 5: Ask a question ```bash {practice="machine"} python3 rag.py ask "How do I roll back a deploy?" ``` **Expected output:** ```text Answer the question using only the context below. Cite the source file for each claim. If the context does not answer it, say so. [rag-practice/runbook/deploy.md > Roll back] To roll back, rerun the deploy workflow with the previous image tag. Leave migrations alone during a rollback; the old code tolerates the new columns. [rag-practice/runbook/deploy.md > Deploy] Deploys run from the main branch through the deploy workflow. Each deploy tags the image with the commit SHA. Question: How do I roll back a deploy? ``` ### Step 6: Send it to a model ```bash {practice="machine"} python3 rag.py ask "How do I roll back a deploy?" | llm ``` Or copy the printed prompt into your assistant. Either way, check the answer's citations against the files. **What you learned:** a working RAG loop over your own repository, with no new infrastructure. ### Step 7: Make it yours If you created the practice docs in Step 3, delete them and index your own: ```bash {practice="machine"} rm -r rag-practice python3 rag.py index docs ``` Replace `docs` with your Markdown folder (e.g., `adr` or `.`). Make three changes one at a time, rerunning retrieval checks after each. * **Add your vocabulary.** Put your team's filler words in `STOP`, or map synonyms such as `revert` to `rollback` before building the query. * **Index more than docs.** Write `git log --format='## %h %as%n%n%s%n%n%b' > docs/commits.md` to turn commit messages into chunks, one per commit. The subject goes in the body, so a commit with no message body still gets indexed. Add `docs/commits.md` to `.gitignore` so the generated file stays out of your commits. * **Tune top-k.** Raise `k` when answers miss context, and lower it when the model gets distracted by loosely related chunks. ## Troubleshooting ### `fts5: syntax error near "?"` The question went into `MATCH` as raw text. Strip punctuation and quote each word, as `ask()` does, or write the query by hand with `OR` between words. ### `no such module: fts5` That Python’s SQLite lacks FTS5. Install Python with FTS5 support, or use sqlite.org’s `sqlite3` shell for the SQL steps, then rerun Step 1 to confirm. ### `Parse error near line 1: table chunks already exists` You loaded `parcel-docs.sql` into a database where the table already existed, so its `INSERT` duplicated every chunk. Delete `parcel.db` and reload it. In the practice shell, press Reset first. ### `llm` fails before it answers LLM needs an accessible model. Set a default model key or pass `-m` with a local model like `ollama/gemma3`; ensure Ollama is running. ### `sqlite3.OperationalError: no such table: chunks` You ran `ask` before `index` or from another folder, so `rag.py` opened an empty `rag.db`. First run `python3 rag.py index docs` from the same folder. If it reports `Indexed 0 chunks`, check the path; the folder has no `.md` files. ### The query returns no rows Every plain word in a `MATCH` query must appear in the chunk. Join words with `OR`; check typos. Stemming handles word endings, not synonyms. ### The right chunk exists but never makes the top three Run the retrieval check. If it misses because query and document wording differ, add the document’s terms to the query, map synonyms, or raise `k`. If vocabulary-related misses persist, add embeddings alongside BM25. ### The model ignores the context or makes things up Print the prompt before sending to confirm it has the right chunks. Keep the "only the context below" and "say so" lines, and put the question last. ### A syntax error near `ORDER` in the augment query Your `sqlite3` is pre-3.44 and can't sort within `group_concat`. Upgrade or remove `ORDER BY score` and accept SQLite’s chosen chunk order. ## Learn RAG: Beyond the Basics You've built the core loop. Take a course on embeddings and evaluation, and keep a search-relevance book handy for trickier retrieval. 📹 **Video** * [Retrieval Augmented Generation (RAG)][coursera-rag]. DeepLearning.AI's intermediate course on Coursera, taught by Zain Hasan: retrievers, vector databases, prompts, and evaluation, with hands-on labs. * [Embeddings: What they are and why they matter][willison-embeddings]. Simon Willison's PyBay 2023 talk, with a full write-up, covering the step this guide skipped. 🔊 **Audio** * [SE Radio 673: Abhinav Kimothi on Retrieval-Augmented Generation][se-radio-673]. A Software Engineering Radio episode on retrievers, indexing pipelines, and generation, from June 2025. 📚 **Books** * [A Simple Guide to Retrieval Augmented Generation][kimothi-book] by Abhinav Kimothi. A short, practical introduction from the guest on the episode above. * [AI Engineering][aie-book] by Chip Huyen. Its RAG and Agents chapter places retrieval inside the rest of an AI application: evaluation, prompts, and fine-tuning. * [Relevant Search][relevant-search] by Doug Turnbull and John Berryman. Search relevance from first principles. It predates RAG, and it teaches the ranking skills retrieval depends on. 🌐 **Online** * [SQLite FTS5 Extension][fts5]. The reference for every query in this guide: tokenizers, query syntax, and `bm25()`. * [Building search-based RAG using Claude, Datasette and Val Town][willison-search-rag]. Simon Willison's walkthrough of the same keyword-first approach, with the model writing the search queries. * [Introducing Contextual Retrieval][contextual-retrieval]. Anthropic's measured comparison of BM25, embeddings, and reranking, and the 200,000-token rule for skipping RAG. * [Retrieval-Augmented Generation for Knowledge-Intensive NLP Tasks][lewis-rag]. The 2020 paper that named the pattern. * [sqlite-vec][sqlite-vec]. A SQLite extension for vector search, the natural next step once keyword retrieval runs out. ## Related Content * [Learn SQL][learn-sql], for the queries, CTEs, and practice shell this guide builds on. * [How to Use sqlite-utils][sqlite-utils], for loading files into SQLite from the command line. * [What Is Ollama?][what-is-ollama], for running the generation step on a local model. * [A List of Open Source LLMs][open-source-llms], for choosing that local model. * [What Is Claude Code?][what-is-claude-code], for an assistant that searches your code with its own tools. * [Fundamentals of Technical Writing][technical-writing], because retrieval can only return what someone wrote down clearly. [practice-rag]: #practice-rag [build-something]: #build-something-rag-over-your-own-docs [chunk-by-heading]: #chunk-by-heading [index-fts5]: #index-with-fts5 [question-to-query]: #turn-a-question-into-a-search-query [augment]: #augment-build-the-prompt [generate]: #generate-hand-the-prompt-to-a-model [check-retrieval]: #check-retrieval-before-you-blame-the-model [less-suitable]: #less-suitable-use-cases [lewis-rag]: https://arxiv.org/abs/2005.11401 [willison-search-rag]: https://simonwillison.net/2024/Jun/21/search-based-rag/ [willison-embeddings]: https://simonwillison.net/2023/Oct/23/embeddings/ [contextual-retrieval]: https://www.anthropic.com/engineering/contextual-retrieval [fts5]: https://www.sqlite.org/fts5.html [fts5-unindexed]: https://www.sqlite.org/fts5.html#the_unindexed_column_option [fts5-porter]: https://www.sqlite.org/fts5.html#porter_tokenizer [fts5-bm25]: https://www.sqlite.org/fts5.html#the_bm25_function [llm-usage]: https://llm.datasette.io/en/stable/usage.html [llm-ollama]: https://github.com/taketwo/llm-ollama [llm-setup]: https://llm.datasette.io/en/stable/setup.html [sqlite-3-44]: https://www.sqlite.org/releaselog/3_44_0.html [bm25-wiki]: https://en.wikipedia.org/wiki/Okapi_BM25 [coursera-rag]: https://www.coursera.org/learn/retrieval-augmented-generation-rag [se-radio-673]: https://se-radio.net/2025/06/se-radio-673-abhinav-kimothi-on-retrieval-augmented-generation/ [kimothi-book]: https://www.manning.com/books/a-simple-guide-to-retrieval-augmented-generation [aie-book]: https://github.com/chiphuyen/aie-book [relevant-search]: https://www.manning.com/books/relevant-search [sqlite-vec]: https://github.com/asg017/sqlite-vec [learn-sql]: https://jeffbailey.us/blog/2026/10/22/learn-sql/ [sqlite-utils]: https://jeffbailey.us/blog/2022/05/12/how-do-i-use-sqlite-utils/ [what-is-ollama]: https://jeffbailey.us/blog/2026/04/27/what-is-ollama/ [open-source-llms]: https://jeffbailey.us/blog/2026/01/25/a-list-of-open-source-llms/ [what-is-claude-code]: https://jeffbailey.us/blog/2026/04/21/what-is-claude-code/ [technical-writing]: https://jeffbailey.us/blog/2025/10/12/fundamentals-of-technical-writing/