Local database: requirements and technology choice

Research Question

If we decide to use a local database for indexing and state (rather than JSON files), what are the requirements and what technology should we choose?

Findings

Executive Summary

SQLite with FTS5 (Phase 1) and sqlite-vec (Phase 2) is the correct local database technology for this repository when the corpus or query complexity outgrows JSON files. SQLite satisfies all relational query patterns, provides full-text search at zero dependency cost via its built-in FTS5 module, and allows vector search to be added incrementally through the sqlite-vec extension without switching to a different database file or technology stack. DuckDB is poorly matched to the row-oriented access patterns of this workload and lacks built-in vector search; ChromaDB and LanceDB are embeddings-first systems that cannot replace the relational layer and introduce heavy dependency trees. The database should be treated as a derived artefact rebuilt from YAML front-matter, not as the source of truth for research item metadata.

Key Findings

1. SQLite's FTS5 extension is built into Python's standard library sqlite3 module and requires no additional dependencies to provide BM25-ranked full-text search over research item titles, questions, and findings. [high confidence]

2. The sqlite-vec extension (pip install sqlite-vec) enables KNN vector search within the same SQLite database file as FTS5, adding semantic retrieval capability without introducing a second database technology. sqlite-vec is pre-v1 and may have breaking changes before 1.0; this risk is managed by deferring its integration to Phase 2. [medium confidence]

3. DuckDB's columnar-vectorised OLAP engine is optimised for large-table analytical scans — a query pattern that does not match this repository's workload of point lookups by status, tag joins, and URL deduplication. DuckDB also has no built-in vector search extension. [high confidence]

4. ChromaDB and LanceDB are both embeddings-first systems that do not provide a relational query layer; using either would require SQLite alongside them anyway, eliminating any benefit over the proposed SQLite-only approach. ChromaDB's persistent client also pulls in 15+ transitive dependencies including C++ and Rust extensions. [high confidence]

5. The recommended five-table schema (items, tags, sources, processed, transcripts) with an FTS5 virtual table over item content supports all identified query patterns: relational filtering by status/priority/tag, URL deduplication, source tracking, and full-text search. [high confidence]

6. The database file must be treated as a derived artefact excluded from git, rebuildable from YAML front-matter on demand, because all embedded database formats (SQLite, DuckDB, ChromaDB, LanceDB) produce binary files that are not git-diffable. YAML front-matter in Markdown files remains the source of truth for research item metadata. [high confidence]

7. Migration from JSON + YAML to SQLite should be triggered by at least one of four observable signals: state/index.json exceeding 500 entries, a need for cross-item relational queries beyond grep, a semantic search capability being built, or concurrent pipeline runs requiring ACID transactions. The 500-entry threshold is a cognitive cost heuristic (large JSON diffs), not a performance limit. [medium confidence]

8. Zotero's architecture (SQLite with a relational schema + a separate FTS table indexed against item content) is the closest prior art for this use case and validates the proposed schema pattern. [high confidence]

Assumptions

• Assumption: ChromaDB's dependency tree is substantially heavier than SQLite's. Justification: The Chroma getting-started documentation references hnswlib, tokenizers, and other packages visible in the ChromaDB PyPI dependency graph. A precise dependency count was not audited directly; the characterisation "15+ transitive dependencies" is an estimate. If this assumption is wrong, ChromaDB's dependency cost is lower than assessed, but the fundamental objection (not a relational system) remains.
• Assumption: The corpus will not require concurrent write access from multiple processes in Phase 1. Justification: AGENTS.md describes a single-owner workflow with one pipeline running at a time. If parallel pipeline runs are introduced, SQLite WAL mode handles concurrent readers; exclusive write locks must still be serialised.
• Assumption: The embedding model for Phase 2 will produce 1536-dimensional float32 vectors. Justification: 1536 is the dimension of OpenAI's text-embedding-3-small. If a different model is chosen, the vec0 table dimension must be adjusted at schema creation time.

Analysis

The key trade-off in this evaluation is query richness and semantic capability (favouring ChromaDB or LanceDB) versus dependency minimalism and unified relational+FTS access (favouring SQLite). Given the scale (hundreds of items) and query patterns (point lookups, tag filters, FTS), the dependency minimalism argument wins decisively. Both ChromaDB and LanceDB are systems built for embedding-centric workloads at scale; neither provides a relational layer, meaning SQL-expressible queries would still require a separate SQLite instance alongside them.

The DuckDB alternative was evaluated seriously because DuckDB is a legitimate embedded analytical database with Python bindings and FTS support. Its disqualification is not on grounds of quality but on grounds of query pattern mismatch: columnar storage is a disadvantage for single-row lookups and small multi-row joins, and the absence of built-in vector search means two separate stores are still needed for Phase 2.

The phased approach (SQLite alone → SQLite + sqlite-vec) is the correct architecture because it avoids committing to a pre-v1 extension (sqlite-vec) in the core state management path. Phase 1 can be implemented and used in production; Phase 2 is added when the semantic search backlog item is executed.

Risks, Gaps, and Uncertainties

• sqlite-vec pre-v1 stability: The extension may change its vec0 virtual table SQL syntax before reaching 1.0. Mitigation: pin the version in pyproject.toml; isolate vector search behind an abstraction layer in src/db/.
• Embedding model dependency for Phase 2: Generating embeddings requires either a local model (sentence-transformers, ~400MB download) or an API call (OpenAI, Gemini). The choice has cost and privacy implications that are out of scope for this item.
• Database rebuild performance: If the pipeline rebuilds the database from scratch (e.g., in a fresh GitHub Actions environment), parsing all YAML front-matter files and re-inserting into SQLite takes time proportional to corpus size. At hundreds of items this is negligible; at thousands, an incremental build approach would be needed.
• FTS5 content sync: The FTS5 virtual table using content='items' requires triggers or manual INSERT INTO items_fts calls to stay in sync with the items table. If not implemented correctly, the FTS index will be stale. This is a known SQLite FTS5 limitation.

Open Questions

1. What embedding model should be used for Phase 2 vector search? This is the primary unresolved dependency for the semantic search capability. Local models (sentence-transformers) avoid API calls but require a large download in CI; API models (OpenAI, Gemini) require credentials. This is a prerequisite question for the 2026-03-02-semantic-full-text-search.md backlog item.
2. Should the database be committed to git in a compressed or alternative format (e.g., Datasette's .db.gz)? Datasette supports SQLite databases published as static sites; committing the .db file (gzip-compressed) would enable read-only access via GitHub. This is an interface question for 2026-02-27-interface-and-delivery.md.
3. Should the Phase 1 migration include transcripts? Transcripts are large blobs; storing them in SQLite would make the .db file large. An alternative is to store only the URL and a content hash, with the transcript text remaining in Research/transcripts/. This is a scope question for the Phase 1 implementation backlog item.

sources

• [x] SQLite docs
• [x] sqlite-vec
• [x] DuckDB
• [x] ChromaDB
• [x] LanceDB