Select SQLite FTS5 for offline lexical search #68

Merged

JackFrostbyte merged 1 commit from feature/issue-20-search-backend-adr into develop

2026-09-04 01:48:32 -04:00

JackFrostbyte commented

2026-09-04 01:44:47 -04:00

Owner

Summary

Evaluate SQLite FTS5 3.51.2 and Tantivy 0.26.0 against Arkive’s completed five-document, 36-chunk pilot corpus.
Run both backends against the unchanged Issue #19 M2-01 retrieval fixture containing 10 cases.
Record reproducible measurements covering determinism, CPU and memory use, index size, query latency, Unicode behavior, filters, identifier preservation, offline operation, licensing, portability, and maintenance.
Add ADR-0004 selecting SQLite FTS5 for Arkive’s offline lexical search core.
Retain the disposable evaluation harness under docs/evaluations/ for reproducibility without adding candidate dependencies to Arkive.

Evaluation results

Both evaluated backends:

passed all 10 Issue #19 fixture cases;
respected source, document, and page filters;
returned zero results for required no-result cases;
preserved Arkive source, document, chunk, location, and citation identity;
operated successfully with networking disabled; and
produced logically deterministic results.

SQLite FTS5 was selected because the pilot evaluation showed:

byte-identical repeated index builds;
lower peak process memory;
faster index construction;
successful tested accent-insensitive Latin matching;
no additional Python or native package dependency;
public-domain SQLite licensing; and
a simple, portable SQL boundary between the search core and presentation layers.

Tantivy remains a credible alternative. It produced a smaller index and materially lower query latency, but those advantages did not satisfy an unmet requirement on this 36-chunk corpus. Its indexes were not byte-identical across repeated builds, it used more peak memory, and adoption would add a compiled MIT-licensed dependency.

The ADR explicitly requires reevaluation with representative larger-corpus evidence if Arkive’s scale or performance requirements change.

Validation

Canonical Arkive validator: passed
Complete test suite: 312 passed
Issue #19 retrieval fixture: 10/10 for SQLite and 10/10 for Tantivy
Reproducibility tests: 15 passed
Offline/network-disabled evaluations: passed for both backends
Ruff check and format check: passed
Coverage: 92%
pip check: passed
Markdown link and heading checks: passed
git diff --check: passed
Repeated evidence checksum verification: passed
Project dependencies: unchanged

Scope

This PR does not implement:

the production index builder;
a production retrieval API;
vector, hybrid, semantic, embedding, or LLM retrieval;
generated answers or UI;
a network search service; or
the Issue #21 index manifest.

Closes #20

## Summary * Evaluate SQLite FTS5 3.51.2 and Tantivy 0.26.0 against Arkive’s completed five-document, 36-chunk pilot corpus. * Run both backends against the unchanged Issue #19 M2-01 retrieval fixture containing 10 cases. * Record reproducible measurements covering determinism, CPU and memory use, index size, query latency, Unicode behavior, filters, identifier preservation, offline operation, licensing, portability, and maintenance. * Add ADR-0004 selecting SQLite FTS5 for Arkive’s offline lexical search core. * Retain the disposable evaluation harness under `docs/evaluations/` for reproducibility without adding candidate dependencies to Arkive. ## Evaluation results Both evaluated backends: * passed all 10 Issue #19 fixture cases; * respected source, document, and page filters; * returned zero results for required no-result cases; * preserved Arkive source, document, chunk, location, and citation identity; * operated successfully with networking disabled; and * produced logically deterministic results. SQLite FTS5 was selected because the pilot evaluation showed: * byte-identical repeated index builds; * lower peak process memory; * faster index construction; * successful tested accent-insensitive Latin matching; * no additional Python or native package dependency; * public-domain SQLite licensing; and * a simple, portable SQL boundary between the search core and presentation layers. Tantivy remains a credible alternative. It produced a smaller index and materially lower query latency, but those advantages did not satisfy an unmet requirement on this 36-chunk corpus. Its indexes were not byte-identical across repeated builds, it used more peak memory, and adoption would add a compiled MIT-licensed dependency. The ADR explicitly requires reevaluation with representative larger-corpus evidence if Arkive’s scale or performance requirements change. ## Validation * Canonical Arkive validator: passed * Complete test suite: 312 passed * Issue #19 retrieval fixture: 10/10 for SQLite and 10/10 for Tantivy * Reproducibility tests: 15 passed * Offline/network-disabled evaluations: passed for both backends * Ruff check and format check: passed * Coverage: 92% * `pip check`: passed * Markdown link and heading checks: passed * `git diff --check`: passed * Repeated evidence checksum verification: passed * Project dependencies: unchanged ## Scope This PR does not implement: * the production index builder; * a production retrieval API; * vector, hybrid, semantic, embedding, or LLM retrieval; * generated answers or UI; * a network search service; or * the Issue #21 index manifest. Closes #20

JackFrostbyte added 1 commit

2026-09-04 01:44:48 -04:00