# Sqlite Index Builder > Use when building, rebuilding, refreshing, or syncing a local SQLite knowledge index from canonical Markdown plus catalog, RAG, and graph JSONL; not for querying an existing index or creating source artifacts. - Skill: `newkayak12/sqlite-index-builder` (Agent Skill) - Install (CLI): `npx skillmds@latest add newkayak12/sqlite-index-builder` - Raw SKILL.md: https://api.skillmd.com/api/skills/newkayak12/sqlite-index-builder/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: AI & ML - Author: newkayak12 (https://skillmd.com/u/newkayak12) - Updated: 2026-09-21 - Page: https://skillmd.com/skills/newkayak12/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: ```text _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: ```bash node --no-warnings "${CLAUDE_PLUGIN_ROOT}/scripts/sqlite-knowledge.mjs" index \ --root /path/to/vault \ --provider hash ``` For an existing local Ollama model: ```bash 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](../knowledge-base-builder/references/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. ```bash 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](../knowledge-query/references/local-sqlite.md) 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.