libre-study/docs/storage-search-foundation.md

Storage And Search Foundation

Libre Study should query study resources through stable app-level search and resource interfaces. The UI should not care whether a result came from local JSON packages, browser indexes, Tauri SQLite, hosted SQLite, or a future server database.

Current Phase 5 Result

• Browser/local preview still loads LibreBible JSON/JSONL packages.
• Visible page hydration now goes through a shared page-query method instead of the reader assuming every resource is already attached to the whole-library array.
• Verse search now goes through a cached verse search index instead of open-coded filtering.
• KJV Strong's-number search uses a compact package index where available.
• KJV page Strong's links load from strongs-by-chapter shards where available, with whole-file loading kept as a fallback for older packages.
• The native SQLite path has explicit reference/attachment indexes and an FTS5 verse-search table.
• Browser indexes are disposable and rebuildable from loaded package surfaces, which keeps them compatible with later SQLite/server-backed search.

Resource Data vs User Data

Public resource data belongs to LibreBible:

• translations
• Strong's links
• lexicons and dictionaries
• footnotes and study notes
• commentaries
• maps and place metadata
• audio Bible metadata
• pronunciation metadata

User workspace data belongs to Libre Study:

• sessions
• notes
• visual settings
• timeline events
• tags
• highlights
• saved searches
• future collaboration state

Query Interface Goals

The app should ask stable questions:

• search this translation for text
• find verses by Strong's number
• get notes for visible verses
• get commentary for a verse or range
• get dictionary/lexicon entries for terms, lemmas, or Strong's numbers
• get maps for places connected to this verse/page
• get audio for this chapter, verse, or lemma

The backing implementation can change without rewriting the page UI.

SQLite Index Plan

SQLite remains appropriate for desktop, local, and private alpha use. It needs explicit tables and indexes instead of ad hoc file reads.

Core resource tables:

• resources: resource id, type, title, version, license, package version.
• verses: translation id, source verse id, book, chapter, verse, text.
• verse_strongs: translation id, source verse id, position, surface, Strong's number.
• lexicon_entries: Strong's number, language, lemma, transliteration, pronunciation, definitions.
• verse_notes: resource id, translation id, source verse id, note type, caller, text.
• commentary_entries: resource id, book, chapter, verse start, verse end, title, text.
• dictionary_entries: resource id, term, slug, definitions, scripture refs.
• places: place id, canonical name, aliases, coordinates, bounds.
• verse_places: source verse id or range, place id, confidence/source.
• audio_segments: resource id, translation/language, book, chapter, verse range, file path/url, timestamps.
• pronunciations: resource id, language, term, lemma, Strong's number, file path/url, source.

Indexes:

• verses(translation_id, book, chapter, verse)
• verses(source_verse_id)
• verse_strongs(strong)
• verse_strongs(translation_id, source_verse_id)
• verse_notes(translation_id, source_verse_id, note_type)
• commentary_entries(book, chapter, verse_start, verse_end)
• dictionary_entries(term)
• places(canonical_name)
• verse_places(place_id)
• audio_segments(book, chapter, verse_start, verse_end)
• pronunciations(strong_number)
• pronunciations(lemma)

Full-text indexes:

• Bible text through SQLite FTS5.
• Notes and commentaries through SQLite FTS5.
• Dictionary/lexicon definitions through SQLite FTS5.

Hosted Web Path

For the private alpha, server-side SQLite is acceptable. Before public multi-user accounts and collaboration, user/account/session data should move to a server database such as Postgres while public resource packages can remain static, indexed, or separately cached.