Projects › BibleWeb › Translations › Multiple external translations infrastructure
In Progress

Multiple external translations infrastructure

Area: Translations

Context

Problem: BSB and Statenvertaling are hardcoded as verses.text and verses.text_nl columns. Adding a third, fourth, or fifth translation by adding more columns to the verses table would be unsustainable. BibleWeb needs a scalable system for adding new Bible translations without schema changes.

Solution: A normalized verse_texts table that stores translation text keyed by (translation_id, book_id, chapter, verse), paired with a bible_translations registry. Any new translation can be added by inserting a row into the registry and populating verse_texts — no code changes needed.

Not included: A UI for importing or managing translations (admin panel). Currently, adding a translation requires running a script and rebuilding the FTS5 index.

Functional

The system allows multiple Bible translations to coexist. Users can select their preferred translation from a dropdown in the Settings page. The reader, search, and all text-displaying features automatically use the selected translation.

User flow:

  1. User opens Settings → Bible Translation section
  2. Dropdown shows available translations (currently: "Berean Standard Bible (EN)" and "Statenvertaling (NL)")
  3. User selects a translation → reader immediately shows text in that translation
  4. The selection persists across sessions via cookie and localStorage

How a new translation would be added (developer flow):

  1. Insert a row into bible_translations (code, name, language)
  2. Populate verse_texts with all verse texts for that translation
  3. Rebuild the FTS5 index to enable full-text search
  4. The translation automatically appears in the Settings dropdown

Edge cases:

  • If a verse has no text in the selected translation, the BSB text is used as fallback
  • BSB and SV bypass the verse_texts table for performance — they read directly from verses.text / verses.text_nl
  • Translation selection priority: URL param ?translation=X > localStorage > translation cookie > default 'BSB'

UX & Design

Settings dropdown:

  • Section: "Bible Translation"
  • Component: HTML <select> element
  • Options: fetched from /api/translations on page load
  • Currently shows 2 options: BSB and SV

No other UI currently — the infrastructure is in place but there's no admin interface for adding translations.

Technical

Database schema (packages/db/src/schema/bible.ts):

  • bible_translations table: id (PK), code (UNIQUE, e.g., 'BSB'), name, language, isDefault (boolean)
  • verse_texts table: composite PK (translation_id, book_id, chapter, verse), text (NOT NULL)
  • verse_texts_fts: FTS5 virtual table with porter and unicode61 tokenizers — indexes text from verse_texts for full-text search across all translations

Query layer (apps/web/src/lib/server/queries/verses.ts):

  • getTranslations() — fetches all rows from bible_translations (cached in memory)
  • getVersesByChapter(bookId, chapter, translationCode):
    • BSB/SV: returns verses rows directly (performance short-circuit)
    • Other codes: looks up translation ID, fetches from verse_texts, builds a Map, overlays text onto verses rows (preserving IDs and metadata)

API (apps/web/src/routes/api/translations/+server.ts):

  • GET /api/translations — returns translation list; falls back to hardcoded [BSB, SV] if table is empty
  • Cached: Cache-Control: public, max-age=86400

Settings persistence (apps/web/src/lib/stores/settings.svelte.ts):

  • setTranslation(code) writes to settings state, localStorage, and a translation cookie (1-year expiry, SameSite=Lax)
  • The server-side load function reads the cookie to determine which translation to fetch

Migration history:

  • migrate-phase2-6.ts created the initial tables
  • migrate-verse-texts.ts rebuilt with coordinate-based schema (replacing earlier verse_id FK design) and rebuilt FTS5 index

Current data: BSB (id=1) and SV (id=2) are seeded. Adding a 3rd translation requires: INSERT INTO bible_translations, populate verse_texts, rebuild FTS5.

Files:

  • packages/db/src/schema/bible.ts — bibleTranslations, verseTexts table definitions
  • apps/web/src/lib/server/queries/verses.ts — getTranslations(), external translation lookup
  • apps/web/src/routes/api/translations/+server.ts — GET endpoint
  • apps/web/src/lib/stores/settings.svelte.ts — setTranslation(), cookie/localStorage
  • apps/web/src/routes/(app)/settings/+page.svelte — translation dropdown UI
  • scripts/migrate-verse-texts.ts — migration that built current schema
  • scripts/migrate-phase2-6.ts — initial tables migration

Status

Current: IN_PROGRESS Milestone: Foundation (pre-v1) Priority: Medium — infrastructure is complete, but no 3rd translation has been added yet

What's done:

  • Database schema (bible_translations + verse_texts + FTS5 index)
  • Query layer with translation lookup and BSB/SV performance bypass
  • API endpoint for translation list
  • Settings UI with translation dropdown
  • Translation persistence (cookie + localStorage + URL param)
  • Search integration (FTS5 indexes across all translations)

What remains:

  • No admin UI for adding translations
  • No import script template for new translations
  • Only 2 translations active (BSB, SV)
  • Would benefit from a "Bible translation browser" showing available translations with metadata

History:

  • Early design used a verse_id foreign key in verse_texts — replaced with coordinate-based PK (translation_id, book_id, chapter, verse) for simpler imports
  • BSB and SV were initially stored only in verses.text / verses.text_nl — the verse_texts table was added later for scalability and FTS5 support

Dependencies:

  • Requires: verse database (DONE)
  • Used by: search (DONE), settings page (DONE)
  • Enables: adding future translations (KJV, HSV, NBV, etc.)
← Back to Translations features