How to build a company brain, part 10: a SQLite index and knowledge graph your agent maintains

Each document gets one row. The row holds its path, size and modified time, a fingerprint of the content (SHA-256), which company it belongs to, the folder, the kind of document, the month, the source, how certain the content is, a one-line purpose and the summary text.

On top of that sits full-text search. SQLite has an extension for it called FTS5, and it ranks results with BM25, the standard keyword ranking, out of the box. Next to the catalog, a links table stores every Markdown link as one row: from this file, to that file.

Python's standard library covers all of it, because Python ships with a SQLite module. There is no server to run. One caveat: FTS5 has to be compiled into the SQLite your Python uses. It is in most builds, and your agent can check in one line. If it is missing, you'll see the error "no such module: fts5", and the fix is a Python build that includes it.

Extraction runs in parallel, one worker per top-level folder. Each worker writes its results as a chunk. A builder then merges the chunks, which takes about a second in my setup, and swaps the finished database in with a single rename. Nobody ever reads a half-built index.

A refresh doesn't start over. It re-reads only the files whose size or modified time changed. That is why one of my brains could grow from about 2,600 to about 3,900 files in a month without the index getting slower.

There is a trade-off in that shortcut. Checking size and modified time is fast, but a file that changes without either of them changing would be skipped. That's rare, and a full rebuild re-reads everything, so it catches it. I'd run one now and then for that reason.

Goal: add a local search index to this brain.
Constraints: one SQLite file in .index/, Python standard library only. First check that this Python's SQLite has FTS5 and tell me if it does not. One row per file with path, size, modified time, SHA-256, folder, kind, month, source, a one-line purpose and the first lines of its SUMMARY or header. Full-text search with FTS5 over path, name, purpose and summary text. A links table with one row per Markdown link (from_path, to_path, link text). Extract in parallel, one worker per top-level folder. Build into a temporary file in the same folder and rename it over the old one when done. Add a refresh that only re-reads files whose size or modified time changed, and a search command.
Definition of done: show me the file count, run three test searches I give you, and explain in plain words how to run the refresh.

Every link is a row, so the agent can answer graph questions with plain SQL. What links to this page? What does this page depend on? Which pages are two steps away? Which notes does nobody link to, and which pages does everyone cite? It can also export the table as a graph file for visual tools, or to sit next to the Graphify graph from part 9.

You won't write these queries yourself. The one below is here so you can see how little there is to it. It asks which pages link to Kranich Studio's waitlist knowledge page, newest first.

SELECT DISTINCT f.path, f.purpose, f.mtime
FROM links AS l
JOIN files AS f ON f.path = l.from_path
WHERE l.to_path = 'knowledge/product/features/waitlist.md'
ORDER BY f.mtime DESC;

Goal: add graph commands to our index tool, using the links table.
Commands: "backlinks <file>" (who links here), "deps <file>" (what this file links to), "near <file> 2" (everything within two links), "orphans" (files with no incoming links), and "hubs" (the 20 most-linked files). Also export the links as a JSON graph file for visual tools.
Definition of done: show me the output of each command for knowledge/product/overview.md, and tell me how many links point at files that do not exist.

An index only helps if agents use it, and left alone they'll happily grep the whole folder. In Claude Code, two hooks stop that. The first blocks a folder-wide text search unless the index has been queried in the last few minutes. The second prints the rule at the start of every session. Agents still read files. They just have to find them through the index first.

The prompt below is written for Claude Code, where both hook types exist. Other assistants have their own mechanisms, and I don't cover them here.

Semantic search can come later. In the product brain, Markdown is split at headings into chunks of about 350 tokens. Those chunks are ranked twice: once by FTS5's BM25, and once by a small multilingual embedding model that runs on a normal CPU (Model2Vec is the family I mean). The two rankings are merged with reciprocal rank fusion. The hybrid search guide walks through that with code.

Goal: make every agent in this brain search the index before it scans folders.
Add two Claude Code hooks: (1) a PreToolUse hook that runs before any folder-wide Grep or Glob, and before grep or rg in Bash. It checks whether our index search ran in the last 20 minutes. If not, it blocks the call and tells the agent to run the index search first. (2) a SessionStart hook that prints one line: "search the brain index before scanning folders".
Constraints: show me the settings change before you apply it, and do not touch any other hook.
Definition of done: a folder-wide Grep is blocked in a fresh session, and allowed right after an index search.