Sqlite Index Builder

SQLite Index Builder

Build or refresh .knowledge/knowledge.sqlite from portable knowledge artifacts. Markdown and JSONL remain canonical; SQLite is derived local state for FTS5, embeddings, and graph traversal.

Input Contract

Discover the knowledge root from the user-provided path, the current directory, or the conventional knowledge-system/, knowledge-base/, and knowledge-artifacts/ directories. The indexer consumes these files when present:

_knowledge/catalog.jsonl
_rag/chunks.jsonl              # or rag/chunks.jsonl
_graph/nodes.jsonl             # or graph/nodes.jsonl
_graph/edges.jsonl             # or graph/edges.jsonl

Markdown is indexed through each catalog record's path; the indexer does not crawl arbitrary *.md files. If Markdown exists without _knowledge/catalog.jsonl, use knowledge:knowledge-base-builder to create the catalog before indexing. If graph or RAG JSONL must be produced or repaired, use knowledge:knowledge-graph-builder or knowledge:rag-corpus-builder first.

Build

1. Inspect the discovered inputs and state which artifact types are present or missing.

2. Choose the embedding provider:

   • hash is the dependency-free default. It provides deterministic lexical feature vectors, not semantic-model embeddings, and search ranks full-text matches ahead of its vector similarity. Report this when the user expects semantic recall; the build result carries embedding_quality: lexical-baseline and a matching notice.
   • ollama provides semantic embeddings when a local Ollama endpoint and model already exist. Do not silently install Ollama or pull a model.

   The provider is the largest single retrieval lever, and it is usually the wrong one to leave for last. Measured on a 94-question vault with the engine and corpus held fixed, swapping hash for embeddinggemma moved hits 43 → 62, recall@10 0.631 → 0.803, and MRR 0.558 → 0.791 — more than the previous six engine versions combined. Paraphrased and operator-phrased questions are the ones hash cannot reach at all, so when eval misses cluster there, raise the provider before proposing vocabulary edits.

   Report embedding_prompt from the build result. embeddinggemma is trained for asymmetric retrieval and the indexer applies its query and document instructions; an index that reports none under that model predates the prompts and must be rebuilt to get them.

   Report documents_windowed as well. It counts notes too long for the model's context window, which are embedded in overlapping windows and pooled rather than truncated. A high count is not a failure, but it means the corpus is chunked more coarsely than the model can read in one pass; knowledge:rag-corpus-builder is where that is fixed, not --embed-chars.

3. When the knowledge-local MCP server is available, call knowledge_status and compare its reported root with the desired knowledge root. If they resolve to the same path, call knowledge_index with the selected provider and model; the MCP tool does not accept a per-call root. If the server is unavailable or points at a different root, run the bundled CLI with an explicit --root relative to the plugin root:

node --no-warnings "${CLAUDE_PLUGIN_ROOT}/scripts/sqlite-knowledge.mjs" index \
  --root /path/to/vault \
  --provider hash

For an existing local Ollama model:

node --no-warnings "${CLAUDE_PLUGIN_ROOT}/scripts/sqlite-knowledge.mjs" index \
  --root /path/to/vault \
  --provider ollama \
  --model embeddinggemma

If CLAUDE_PLUGIN_ROOT is unavailable, resolve ../../scripts/sqlite-knowledge.mjs from this SKILL.md.

The indexer requires Node 24+, or Node 22.5-23 with --experimental-sqlite and an FTS5-enabled SQLite build. On No such built-in module: node:sqlite or no such module: fts5, switch to the Docker path in the local SQLite reference instead of reporting the vault as unindexable.

The index operation rebuilds the SQLite schema and contents from the current source artifacts; it is not a live file sync. It is, however, incremental where it costs: a document whose embedded text is unchanged reuses its stored vector, so a rebuild after editing three notes embeds three documents, not the corpus. Everything else is rebuilt from scratch, so a deleted or renamed note cannot leave a stale row behind — the failure mode a partial reindex has and this does not.

Report embeddings_reused and embeddings_computed. A rebuild that recomputes everything when little changed means the cache was rejected: the provider, model, or prompt template differs from the existing index, or the schema version moved. That is correct behavior, not a fault — but say which it was rather than reporting a slow build as normal. Pass --no-reuse-embeddings to force a cold rebuild when a measurement needs one.

Verify

After indexing:

1. Run knowledge_status, or the CLI status --root /path/to/vault.
2. Require the database to exist and report stale: false.
3. Compare the returned note, chunk, node, and edge counts with the discovered inputs. Explain legitimately absent artifact types instead of treating every zero as success.
4. When notes exist, run one bounded search smoke test using a term that appears verbatim in a known note title or alias, and confirm that note is returned with lexical_match: true. A smoke test whose results are all lexical_match: false indicates a ranking or tokenization problem, not a passing build. When graph nodes and edges exist, run one neighbor lookup for a known node.
5. When _knowledge/questions.jsonl exists, run eval --root /path/to/vault --k 10 and report mrr, recall_at_k, and mean_distinct_notes. A mean_distinct_notes well below k means chunks of one note are crowding the top slots; results are grouped per note by default, so this should only happen with --group none. Any competency question whose required notes are missing from the top k is a retrieval defect: name the question rather than reporting the build as clean, and follow the retrieval repair loop below instead of accepting the score.
6. Report the database path, source root, indexed counts, embedding provider/model/dimensions, fingerprint, and freshness.

Retrieval Repair Loop

Run this when eval reports missed questions. It is a measurement loop, not an editing loop: the danger is not a failed question, it is a fix that scores well because it was tuned against the same questions used to judge it.

1. Split before touching anything. eval --split holdout scores the reserved third of the question set; --split dev scores the rest. Buckets are derived from each question id, so they are stable across runs and cannot drift while vocabulary is being edited. Repair against dev only, and record the holdout number first — a holdout measured after the repair proves nothing.
2. Read repair_targets before proposing an edit. Each entry names an unretrieved required note, how many questions it blocks, and its gap: missing-note means the note is absent from the catalog and this is an extraction job for knowledge:knowledge-base-builder; no-lookup-vocabulary means the note carries no aliases, user terms, or source symbols; ranking means the vocabulary exists and something else is outranking it. Start with the note blocking the most questions, not the note that is easiest to edit. When a comparison question retrieves its relation note but none of the sides, check the relation record's participants before touching vocabulary: search pulls declared participants in on its own, so an undeclared side is a catalog defect, not a lookup one.
3. Ground each added term in the repo, not in the question set. See the vocabulary-bridge repair rules in answerability-contract.md.
4. Re-index and re-score after each change, against the saved run. One edit per measurement; a batch of edits cannot be attributed or reverted.
5. Sweep the fusion split before accepting a semantic provider's losses. With a real embedding the default is semantic 0.7 / lexical 0.3, which is a guess. Questions that quote an exact screen label back at the index are the ones it loses: meaning similarity dilutes an exact term match. eval --sweep 0.3,0.4,0.5 scores every weight in one pass without re-indexing, comparing each against the first weight per question. Judge a sweep on the whole question set, not on the label questions that motivated it, and treat decisive: false as a tie — adopting a winner that cannot be separated from the reference is how a guess becomes a default.

node --no-warnings "${CLAUDE_PLUGIN_ROOT}/scripts/sqlite-knowledge.mjs" eval \
  --root /path/to/vault --split dev --k 10 > /tmp/before.json
# edit vocabulary, then index again
node --no-warnings "${CLAUDE_PLUGIN_ROOT}/scripts/sqlite-knowledge.mjs" eval \
  --root /path/to/vault --split dev --k 10 --baseline /tmp/before.json
# fusion sweep — one pass, no re-index, shared query embeddings
node --no-warnings "${CLAUDE_PLUGIN_ROOT}/scripts/sqlite-knowledge.mjs" eval \
  --root /path/to/vault --split dev --k 10 --sweep 0.3,0.4,0.5

The baseline block reports improvements, regressions, and a verdict. Revert on any regression, even when recall_at_k rose. Competency sets are small enough that one question moves recall by several points, so an aggregate gain routinely hides a question that stopped working; regressions names it. This rule scopes to vocabulary and catalog edits at a fixed engine and provider, where a regression means the edit broke something. A provider or engine change is judged differently: it moves every question at once, so weigh net movement and holdout together and name the regressions rather than reverting on their existence. Only after dev is stable, score --split holdout once and report both numbers. A holdout that did not move means the repair generalized to nothing — report that plainly instead of citing the dev gain.

6. Measure a reranker's ceiling before attaching one. A cross-encoder can only reorder what retrieval already returned, so its maximum gain is recall@50 − recall@10. Score eval --k 10 and eval --k 50 on the same index: required notes missing at both depths are a retrieval or catalog problem that no reranker can fix. When the headroom is real and the user has an endpoint, pass --reranker-url and re-score — and record in the report that the run had a reranker attached, because it is not comparable to one without.

Do not cite the SQLite file as source evidence and do not commit it merely to share knowledge. Commit or synchronize the canonical Markdown and JSONL instead. Do not modify source artifacts during an index-only request.

Read the local SQLite reference when Docker operation, MCP routing, Ollama configuration, or failure recovery is needed.

Related Skills

• knowledge:knowledge-base-builder - create the Markdown catalog that controls note inclusion.
• knowledge:knowledge-graph-builder - create or repair graph node and edge JSONL.
• knowledge:rag-corpus-builder - create or repair retrieval chunk JSONL.
• knowledge:knowledge-query - query and cite evidence from an existing index after the build.