Module Schema Reference
The complete DDL for every module type, embedded verbatim from the reference
application's shipped schema files
(packages/core/src/sql/schemas/initial/*.sql). These are the single source of
truth: the app, the validator, and the template generator all read from them.
note
The module_info, verse_link, and schema_version tables are common to
every type (with small per-type additions to module_info). The range
convention is normative everywhere: verse_id_start / verse_id_end are both
inclusive, verse_id_end is NOT NULL where a range is mandatory, and a single
verse is verse_id_end = verse_id_start.
Bible translation (bible)
Source: packages/core/src/sql/schemas/initial/BibleTranslation.sql
-- Bible Translation Module Database Schema
-- Database: bible_[abbreviation].db
-- Purpose: Individual Bible translation content
-- Format: bible-module
-- Format version / Schema version: 2.0.0
-- Generated: 2025-10-10
-- Revised: 2026-07-24 (Module Format v2)
--
-- ----------------------------------------------------------------------------
-- Module Format v2 summary of changes (see docs/Design/DataModel/ModuleFormat-v2-Plan.md)
-- ----------------------------------------------------------------------------
-- WS-2 Text representation:
-- * `bible_verse.text` is now clean canonical UTF-8 -- no HTML, no pilcrow
-- (U+00B6), no leading/trailing whitespace.
-- * `bible_verse.text_plain` is REMOVED (the normalized `text` replaces it,
-- and FTS indexes `text` directly).
-- * `bible_verse.formatting_data` is RENAMED to `bible_verse.formatting` and
-- carries plain-text + structured spans (see the format block below).
-- WS-3 `verse_link` added -- the single, uniform content->verse linking table,
-- byte-identical in every module DB and in the user DB. Typically empty for
-- a plain translation; carries publisher cross-references / parallel-passage
-- notes when the source text ships them (bible module as link SOURCE).
-- WS-4 `module_info` gains a stable identity + provenance block: `module_uuid`,
-- `format`, `format_version`, `content_version` (renamed from `version`),
-- `content_sha256`, `license_spdx`, `license_url`, `source_url`, `canon`,
-- `versification`. Freeform `copyright` is retained as DISPLAY ONLY -- never
-- parse it; use `license_spdx` for licensing decisions.
-- `has_apocrypha` is deliberately NOT present (superseded by `canon`; see
-- ModuleFormat-v2-Plan.md WS-4 / Q3). The 66-book Protestant canon is the
-- only supported canon.
-- WS-5 Word offsets are 0-based and inclusive everywhere (see the convention note
-- above `interlinear_word`).
-- WS-6 Dead schema removed: `book_search_index`, `book_search_metadata`,
-- `verse_positions` (0 rows in every shipped module -- the live
-- implementation is `bible_search_index` / `bible_search_index_metadata` /
-- `bible_search_verse_positions` in `main.db`). FTS5 external-content
-- triggers rewritten to the correct 'delete'-command pattern.
-- WS-7 Enum policy: CHECK constraints are kept only for genuinely closed domain
-- sets; open/extensible sets are validated in TypeScript (see note in
-- section 1).
--
-- ============================================================================
-- Pragmas and Initialization
-- ============================================================================
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA temp_store = MEMORY;
PRAGMA cache_size = -32000; -- 32MB cache
-- ============================================================================
-- 1. Module Information
-- ============================================================================
--
-- ENUM / CHECK POLICY (WS-7)
-- --------------------------
-- SQLite cannot ALTER a CHECK constraint: changing one requires rebuilding the
-- whole table (create-new / copy / drop / rename). A CHECK on a value set that is
-- expected to grow therefore turns a routine additive change into a migration of
-- every shipped module file. Consequently:
--
-- * KEEP a CHECK only for closed domain sets fixed by the domain itself --
-- `testament IN ('OT','NT')`, `text_direction IN ('ltr','rtl')`, boolean 0/1
-- flags, and structural invariants (`info_id = 1`, `verse_id > 0`).
-- * DROP the CHECK for open / extensible sets -- `module_type`, `canon`,
-- `versification`, `license_spdx`, `format`, `feature_name`, and any future
-- vocabulary. These are validated in TypeScript at the repository boundary,
-- with the union types in `packages/core/src/Data/Core/Types.ts` as the single
-- source of truth.
-- 1.1 Module Metadata (single row: info_id = 1)
CREATE TABLE module_info (
info_id INTEGER PRIMARY KEY CHECK (info_id = 1), -- Only one row
-- Identity (WS-4)
module_uuid TEXT NOT NULL, -- Stable identity across versions
-- (RFC 4122 UUID). This -- not
-- `abbreviation` -- is the cross-DB
-- join key.
module_type TEXT NOT NULL DEFAULT 'bible', -- Open set: validated in TypeScript
abbreviation TEXT NOT NULL, -- "KJV", "ESV", "NIV" (display / lookup
-- convenience only, NOT an identity key)
full_name TEXT NOT NULL, -- "King James Version"
language_code TEXT NOT NULL, -- ISO 639-1: "en", "es", "fr"
year_published INTEGER,
description TEXT,
publisher TEXT,
is_original_language INTEGER DEFAULT 0, -- 1 for Hebrew/Greek texts
text_direction TEXT DEFAULT 'ltr', -- 'ltr' or 'rtl'
-- Format + content versioning (WS-4)
format TEXT NOT NULL DEFAULT 'bible-module', -- Container format name
format_version TEXT NOT NULL DEFAULT '2.0', -- Spec version this file conforms to
content_version TEXT, -- The module's own content revision
-- (renamed from `version`)
content_sha256 TEXT, -- Integrity / dedup hash of the content
-- Licensing + provenance (WS-4)
copyright TEXT, -- Freeform, DISPLAY ONLY. Never parsed
-- to infer licensing -- use license_spdx.
license_spdx TEXT, -- SPDX id: 'CC-BY-4.0', 'PD', 'Proprietary'
license_url TEXT, -- Full terms
source_url TEXT, -- Where this module came from
-- Canon + versification (WS-4). Explicit so a future format can differ.
canon TEXT NOT NULL DEFAULT 'protestant-66', -- Only 'protestant-66' is supported today
versification TEXT NOT NULL DEFAULT 'kjv-english',
created_date TEXT DEFAULT CURRENT_TIMESTAMP,
metadata TEXT, -- JSON: additional data
-- Closed sets only (see policy above)
CHECK (is_original_language IN (0, 1)),
CHECK (text_direction IN ('ltr', 'rtl'))
);
-- ============================================================================
-- 2. Bible Verses
-- ============================================================================
--
-- TEXT + FORMATTING REPRESENTATION (WS-2)
-- ---------------------------------------
-- `text` is clean canonical UTF-8: no HTML, no inline markup delimiters, no
-- pilcrow (U+00B6), no leading or trailing whitespace. A consumer that only wants
-- readable text can ignore `formatting` entirely. FTS indexes `text` directly.
--
-- `formatting` is JSON describing structure as DATA (ranges of words), never as
-- markup:
--
-- {
-- "v": 1,
-- "block": {
-- "paragraph_start": true,
-- "poetry_level": 1, // 1..3, omitted for prose
-- "heading": "A Psalm of David", // see "Headings" note below
-- "heading_kind": "psalm_title", // optional: 'section' | 'psalm_title'
-- "selah": true // optional
-- },
-- "spans": [
-- { "type": "divine_name", "start": 1, "end": 1 },
-- { "type": "supplied", "start": 2, "end": 2 },
-- { "type": "words_of_christ", "start": 0, "end": 24 },
-- { "type": "emphasis", "start": 5, "end": 6 },
-- { "type": "quotation", "start": 3, "end": 9, "ref": 23007014 }
-- ]
-- }
--
-- Span vocabulary (plain-English names; USFM equivalent published for
-- interoperability -- we borrow the vocabulary, not the syntax):
--
-- | Span type (stored) | USFM | Meaning / rendering |
-- |--------------------|------|------------------------------------------------|
-- | divine_name | \nd | YHWH rendered LORD/GOD -> small caps |
-- | supplied | \add | translator-supplied words -> italic |
-- | words_of_christ | \wj | red letter |
-- | emphasis | \em | genuine emphasis present in the source |
-- | quotation | \qt | OT quotation in NT; source verse_id in `ref` |
-- | transliteration | \tl | italic |
--
-- Block-level fields map to USFM as: paragraph_start -> \p,
-- poetry_level 1..3 -> \q1..\q3, heading -> \d / \s, selah -> \qs.
--
-- Headings. `block.heading` is the ONE field for heading text. It covers both USFM
-- \s (section heading, e.g. "The Beatitudes") and USFM \d (Psalm superscription /
-- descriptive title, e.g. "A Psalm of David"). Where the distinction matters for
-- rendering or for the toUSFM() exporter, qualify it with the optional
-- `block.heading_kind`:
--
-- | heading_kind | USFM | Typical rendering |
-- |---------------|------|-----------------------------------------------------|
-- | 'section' | \s | bold/large heading above the verse |
-- | 'psalm_title' | \d | italic superscription, part of the psalm itself |
--
-- `heading_kind` is omitted when unknown; consumers should default to 'section'.
-- Do NOT introduce a second heading field. The legacy v1 `formatting_data`
-- key `sectionHeading` normalizes to `block.heading` (+ heading_kind 'section').
--
-- `start` / `end` are WORD indices, 0-based and INCLUSIVE (WS-5).
-- Extend the format by adding span TYPE NAMES -- never by adding presentational
-- attributes, and never by re-introducing inline markup or a third `text_usfm`
-- column. USFM is produced on export (see the toUSFM() exporter, WS-8).
--
-- Worked example -- Psalm 23:1:
-- text: The LORD is my shepherd; I shall not want.
-- formatting: {"v":1,"spans":[{"type":"divine_name","start":1,"end":1},
-- {"type":"supplied","start":2,"end":2}]}
-- 2.1 Bible Verse Table
CREATE TABLE bible_verse (
verse_id INTEGER PRIMARY KEY, -- Matches main.db bible_verse_ref.verse_id
text TEXT NOT NULL, -- Clean canonical UTF-8 (see block above)
formatting TEXT, -- JSON: block + spans (see block above)
word_count INTEGER, -- Number of whitespace-delimited words in
-- `text`; span indices run 0..word_count-1
metadata TEXT,
CHECK (verse_id > 0)
);
-- No foreign key to main.db; verse_id values must match main.db bible_verse_ref
CREATE INDEX idx_verse_id ON bible_verse(verse_id);
-- 2.2 Interlinear Data (Original Language Texts Only)
--
-- WORD OFFSET CONVENTION (WS-5)
-- -----------------------------
-- Word offsets are 0-BASED and INCLUSIVE throughout this project: the first word of
-- a verse is index 0, and a range [start, end] includes both endpoints. A single
-- word is expressed as start == end. This matches `user_text_markup`, matches
-- JavaScript array indexing in the renderer, and matches the `formatting` span
-- indices above. Normative statement of the convention:
-- docs/Design/DataModel/ModuleFormat-v2-Plan.md, WS-5.
CREATE TABLE interlinear_word (
interlinear_id INTEGER PRIMARY KEY AUTOINCREMENT,
verse_id INTEGER NOT NULL,
word_position_start INTEGER NOT NULL, -- 0-based inclusive word index (0, 1, 2...)
word_position_end INTEGER NOT NULL, -- 0-based inclusive; == start for one word
original_word TEXT NOT NULL, -- Greek/Hebrew word
transliteration TEXT,
strongs_number TEXT, -- "H1234" or "G5678"
morphology TEXT, -- "V-AAI-3S"
lemma TEXT, -- Dictionary/lexical form
gloss TEXT, -- Brief English gloss
metadata TEXT,
FOREIGN KEY (verse_id) REFERENCES bible_verse(verse_id) ON DELETE CASCADE,
CHECK (word_position_start >= 0),
CHECK (word_position_end >= word_position_start)
);
CREATE INDEX idx_interlinear_verse ON interlinear_word(verse_id);
CREATE INDEX idx_interlinear_strongs ON interlinear_word(strongs_number);
CREATE INDEX idx_interlinear_morphology ON interlinear_word(morphology);
CREATE INDEX idx_interlinear_position ON interlinear_word(verse_id, word_position_start);
-- ============================================================================
-- 3. Verse Linking
-- ============================================================================
--
-- WS-3: `verse_link` is the ONE content->verse linking shape, byte-identical in
-- every module database and in the user database, replacing the six legacy shapes
-- (`scripture_reference`, `verse_reference`, `cross_reference`, JSON
-- `scripture_verses`, JSON `example_verses`, `topic_verses`, `content_verse_link`).
-- A required, uniform table means an integrator writes one query shape for every
-- module type instead of special-casing bible modules.
--
-- For a plain translation this table is TYPICALLY EMPTY. It is populated when the
-- source text ships publisher marginal cross-references or parallel-passage notes
-- attached to specific verses -- in that case a bible module is the link SOURCE:
-- source_type = 'verse', source_id = <verse_id>, link_type = 'cross_reference'
-- (or 'reference' for a parallel passage), verse_id_start/_end = the target.
-- Giving those a first-class home is precisely why cross-references must stop being
-- HTML anchors buried in content (see WS-9).
--
-- Range convention (normative): `verse_id_start` is inclusive; `verse_id_end` is
-- inclusive and NULL means "single verse". Never spell these
-- `start_verse_id` / `end_verse_id`.
--
-- `source_type` and `link_type` are OPEN sets -- no CHECK, validated in TypeScript
-- (see the enum policy in section 1).
CREATE TABLE verse_link (
link_id INTEGER PRIMARY KEY AUTOINCREMENT,
source_type TEXT NOT NULL,
source_id INTEGER NOT NULL,
verse_id_start INTEGER NOT NULL,
verse_id_end INTEGER NOT NULL, -- inclusive; single verse is expressed as end = start
link_type TEXT NOT NULL DEFAULT 'reference',
sort_order INTEGER NOT NULL DEFAULT 0,
context TEXT,
metadata TEXT
);
CREATE INDEX idx_verse_link_source ON verse_link(source_type, source_id, sort_order);
CREATE INDEX idx_verse_link_start ON verse_link(verse_id_start);
CREATE INDEX idx_verse_link_range ON verse_link(verse_id_start, verse_id_end);
-- R-2: the reverse pair, so a containment probe (start <= X AND end >= X) can be
-- driven from either side. Cheap, and only useful once end is NOT NULL.
CREATE INDEX idx_verse_link_covering ON verse_link(verse_id_end, verse_id_start);
-- ============================================================================
-- 4. Full-Text Search
-- ============================================================================
--
-- `bible_verse_fts` is an EXTERNAL CONTENT table (content='bible_verse'): the FTS
-- index stores only the inverted index, and the column values live in
-- `bible_verse`. For such tables a plain `UPDATE`/`DELETE` against the FTS table is
-- NOT supported and silently leaves stale terms in the index. Rows must instead be
-- removed with the special 'delete' command, supplying the OLD column values so
-- FTS5 can locate and remove the corresponding index entries:
--
-- INSERT INTO x_fts(x_fts, rowid, <cols...>) VALUES('delete', old.id, <old vals...>);
--
-- Reference implementation: scripts/convert-topical-index.js.
--
-- Column ordering is load-bearing: column 0 = verse_id (UNINDEXED),
-- column 1 = text. `highlight()` / `snippet()` callers index by position.
-- 4.1 Bible Verses FTS (indexes the clean `text` directly -- WS-2)
CREATE VIRTUAL TABLE bible_verse_fts USING fts5(
verse_id UNINDEXED,
text,
content='bible_verse',
content_rowid='verse_id',
tokenize='porter unicode61'
);
-- Triggers to keep FTS in sync (external-content pattern -- WS-6)
CREATE TRIGGER bible_verse_fts_insert AFTER INSERT ON bible_verse BEGIN
INSERT INTO bible_verse_fts(rowid, verse_id, text)
VALUES (new.verse_id, new.verse_id, new.text);
END;
CREATE TRIGGER bible_verse_fts_delete AFTER DELETE ON bible_verse BEGIN
INSERT INTO bible_verse_fts(bible_verse_fts, rowid, verse_id, text)
VALUES('delete', old.verse_id, old.verse_id, old.text);
END;
CREATE TRIGGER bible_verse_fts_update AFTER UPDATE ON bible_verse BEGIN
INSERT INTO bible_verse_fts(bible_verse_fts, rowid, verse_id, text)
VALUES('delete', old.verse_id, old.verse_id, old.text);
INSERT INTO bible_verse_fts(rowid, verse_id, text)
VALUES (new.verse_id, new.verse_id, new.text);
END;
-- NOTE (WS-6): `book_search_index`, `book_search_metadata` and `verse_positions`
-- were removed in v2. They shipped in all 53 bible modules with 0 rows in every
-- one; the live book-level / proximity search implementation is
-- `bible_search_index`, `bible_search_index_metadata` and
-- `bible_search_verse_positions` in `main.db`. Do not re-add them here, and do not
-- let a converter script recreate them.
-- ============================================================================
-- 5. Module Features
-- ============================================================================
-- 5.1 Module Features Table
CREATE TABLE module_feature (
feature_id INTEGER PRIMARY KEY AUTOINCREMENT,
feature_name TEXT NOT NULL UNIQUE, -- 'strongs', 'morphology', 'footnotes', etc.
-- Open set: validated in TypeScript
is_enabled INTEGER DEFAULT 1,
metadata TEXT,
CHECK (is_enabled IN (0, 1))
);
-- ============================================================================
-- 6. Schema Version
-- ============================================================================
CREATE TABLE schema_version (
version_id INTEGER PRIMARY KEY AUTOINCREMENT,
version_number TEXT NOT NULL,
applied_date TEXT DEFAULT CURRENT_TIMESTAMP,
notes TEXT,
metadata TEXT
);
INSERT INTO schema_version (version_number, notes)
VALUES (
'2.0.0',
'Module Format v2. WS-2: bible_verse.text is clean canonical UTF-8, text_plain '
|| 'dropped, formatting_data renamed to formatting (block + word-indexed spans), '
|| 'FTS indexes text directly; block.heading (+ optional heading_kind) replaces '
|| 'the legacy sectionHeading key. WS-3: verse_link added as the single uniform '
|| 'content-to-verse linking table. WS-4: module_info identity and provenance block '
|| '(module_uuid, format, format_version, content_version, content_sha256, '
|| 'license_spdx, license_url, source_url, canon, versification); has_apocrypha '
|| 'deliberately absent, superseded by canon. WS-5: word offsets are 0-based and '
|| 'inclusive (interlinear_word CHECK relaxed to >= 0). WS-6: dead '
|| 'book_search_index / book_search_metadata / verse_positions removed; FTS5 '
|| 'triggers rewritten to the external-content delete-command pattern. WS-7: '
|| 'CHECK constraints retained only for closed domain sets.'
);
Commentary (commentary)
Source: packages/core/src/sql/schemas/initial/Commentary.sql
-- Commentary Module Database Schema
-- Database: commentary_[abbreviation].db
-- Purpose: Individual commentary content
-- Version: 2.0.0
-- Generated: 2025-10-10
-- Updated: 2026-07-24 (Module Format v2 — see docs/Design/DataModel/ModuleFormat-v2-Plan.md)
--
-- v2 changes:
-- * WS-3: unified `verse_link` table replaces every ad-hoc content->verse shape.
-- The `cross_reference` table is removed (superseded — cross-references are
-- now `verse_link` rows with link_type='cross_reference'). The `verse_reference`
-- table introduced by an older migration is likewise superseded and is not
-- created here (it exists in zero shipped commentary databases).
-- * WS-4: `module_info` gains identity + provenance columns; `version` renamed to
-- `content_version`.
-- * WS-6: FTS5 update/delete triggers rewritten to the external-content
-- 'delete' command pattern (plain UPDATE/DELETE left stale terms behind).
-- * WS-7: CHECK constraint dropped from `commentary_entry.entry_level` (open set).
--
-- Range convention (normative): `verse_id_start` inclusive, `verse_id_end` inclusive,
-- a single verse is expressed as `verse_id_end = verse_id_start`, never as NULL.
-- Never `start_verse_id`/`end_verse_id`.
-- ============================================================================
-- Pragmas and Initialization
-- ============================================================================
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA temp_store = MEMORY;
PRAGMA cache_size = -32000; -- 32MB cache
-- ============================================================================
-- 1. Module Information
-- ============================================================================
-- 1.1 Module Metadata (exactly one row, info_id = 1)
CREATE TABLE module_info (
info_id INTEGER PRIMARY KEY CHECK (info_id = 1),
module_type TEXT NOT NULL DEFAULT 'commentary',
-- Identity (WS-4)
module_uuid TEXT, -- Stable identity across versions; the join key
abbreviation TEXT NOT NULL, -- "MHC", "BARNES" (display / legacy key)
full_name TEXT NOT NULL, -- "Matthew Henry's Commentary"
-- Format + content versioning (WS-4)
format TEXT NOT NULL DEFAULT 'commentary-module', -- Container format name
format_version TEXT NOT NULL DEFAULT '2.0', -- Spec version this file conforms to
content_version TEXT, -- Module's own content revision (was `version`)
content_sha256 TEXT, -- Integrity / dedup hash
-- Descriptive
author TEXT,
year_published INTEGER,
description TEXT,
language_code TEXT DEFAULT 'en',
publisher TEXT, -- From SWORD CopyrightHolder/Publisher
-- Licensing + provenance (WS-4)
copyright TEXT, -- Freeform, DISPLAY ONLY - never parsed
license_spdx TEXT, -- 'CC-BY-4.0', 'PD', 'Proprietary', ...
license_url TEXT, -- Full terms
source_url TEXT, -- Where this module came from
-- Canon + versification (WS-4) - explicit so a future format may differ
canon TEXT NOT NULL DEFAULT 'protestant-66',
versification TEXT NOT NULL DEFAULT 'kjv-english',
created_date TEXT DEFAULT CURRENT_TIMESTAMP,
metadata TEXT,
CHECK (module_type = 'commentary')
);
-- ============================================================================
-- 2. Commentary Entries
-- ============================================================================
-- 2.1 Commentary Entry Table
-- verse_id_start/verse_id_end anchor the entry to the passage it comments on
-- (inclusive start, inclusive end). commentary_entry is anchor-optional: a
-- book/chapter-level entry leaves BOTH columns NULL; where an anchor is present
-- both are populated and a single verse is expressed as end = start (R-1), never
-- as a NULL end.
CREATE TABLE commentary_entry (
entry_id INTEGER PRIMARY KEY AUTOINCREMENT,
verse_id_start INTEGER, -- For passage-level entries (inclusive). NULL for
-- entry_level='book'/'chapter', which comment on no
-- specific verse range.
verse_id_end INTEGER, -- inclusive; nullable in step with start. Where the
-- range exists at all, both columns are populated and
-- a single verse is expressed as end = start.
entry_level TEXT NOT NULL, -- 'book', 'chapter', 'passage', 'verse', ...
content TEXT NOT NULL, -- Commentary text
content_file TEXT, -- OR path to external file
word_count INTEGER,
metadata TEXT
-- WS-7: no CHECK on entry_level. It is an open/extensible set and SQLite cannot
-- alter a CHECK constraint without a full table rebuild. Validation lives in
-- TypeScript (packages/core/src/Data/Core/Types.ts) and is enforced at the
-- repository boundary.
);
CREATE INDEX idx_entry_verse_start ON commentary_entry(verse_id_start);
CREATE INDEX idx_entry_range ON commentary_entry(verse_id_start, verse_id_end);
CREATE INDEX idx_entry_level ON commentary_entry(entry_level);
-- ============================================================================
-- 3. Verse Linking (WS-3)
-- ============================================================================
-- Unified content->verse linking. This table is byte-identical in every module
-- schema and in the user database. For commentaries it carries cross-references
-- parsed out of entry content at import time (e.g. TSK's
-- <a href="passagestudy.jsp?action=showRef&value=Ge+1%3A1"> anchors) as rows with
-- link_type='cross_reference', plus any additional scripture citations.
CREATE TABLE verse_link (
link_id INTEGER PRIMARY KEY AUTOINCREMENT,
source_type TEXT NOT NULL, -- 'commentary_entry','book_section','dictionary_entry',
-- 'devotional_entry','topic','note','journal','prayer'
source_id INTEGER NOT NULL,
verse_id_start INTEGER NOT NULL,
verse_id_end INTEGER NOT NULL, -- inclusive; single verse is expressed as end = start
link_type TEXT NOT NULL DEFAULT 'reference', -- reference|annotation|primary_passage|cross_reference
sort_order INTEGER NOT NULL DEFAULT 0,
context TEXT,
metadata TEXT
);
CREATE INDEX idx_verse_link_source ON verse_link(source_type, source_id, sort_order);
CREATE INDEX idx_verse_link_start ON verse_link(verse_id_start);
CREATE INDEX idx_verse_link_range ON verse_link(verse_id_start, verse_id_end);
-- R-2: the reverse pair, so a containment probe (start <= X AND end >= X) can be
-- driven from either side. Cheap, and only useful once end is NOT NULL.
CREATE INDEX idx_verse_link_covering ON verse_link(verse_id_end, verse_id_start);
-- WS-7: no CHECK on verse_link.source_type or verse_link.link_type. Both are
-- open/extensible sets; validation lives in TypeScript
-- (packages/core/src/Data/Core/Types.ts) and is enforced at the repository boundary.
-- ============================================================================
-- 4. Full-Text Search
-- ============================================================================
-- 4.1 Commentary FTS
CREATE VIRTUAL TABLE commentary_entry_fts USING fts5(
entry_id UNINDEXED,
content,
content='commentary_entry',
content_rowid='entry_id',
tokenize='porter unicode61'
);
-- Triggers (WS-6)
-- External-content FTS5 tables do not own their data, so rows must be removed with
-- the special 'delete' command carrying the OLD column values. A plain
-- DELETE/UPDATE against the FTS table leaves stale terms in the index.
CREATE TRIGGER commentary_entry_fts_insert AFTER INSERT ON commentary_entry BEGIN
INSERT INTO commentary_entry_fts(rowid, entry_id, content)
VALUES (new.entry_id, new.entry_id, new.content);
END;
CREATE TRIGGER commentary_entry_fts_delete AFTER DELETE ON commentary_entry BEGIN
INSERT INTO commentary_entry_fts(commentary_entry_fts, rowid, entry_id, content)
VALUES ('delete', old.entry_id, old.entry_id, old.content);
END;
CREATE TRIGGER commentary_entry_fts_update AFTER UPDATE ON commentary_entry BEGIN
INSERT INTO commentary_entry_fts(commentary_entry_fts, rowid, entry_id, content)
VALUES ('delete', old.entry_id, old.entry_id, old.content);
INSERT INTO commentary_entry_fts(rowid, entry_id, content)
VALUES (new.entry_id, new.entry_id, new.content);
END;
-- ============================================================================
-- 5. Schema Version
-- ============================================================================
CREATE TABLE schema_version (
version_id INTEGER PRIMARY KEY AUTOINCREMENT,
version_number TEXT NOT NULL,
applied_date TEXT DEFAULT CURRENT_TIMESTAMP,
notes TEXT,
metadata TEXT
);
INSERT INTO schema_version (version_number, notes)
VALUES ('2.0.0', 'Module Format v2: unified verse_link (replaces cross_reference and the legacy verse_reference table); module_info identity/provenance columns with version renamed to content_version; FTS5 external-content triggers corrected; CHECK dropped from entry_level (validated in TypeScript).');
Dictionary / lexicon (dictionary)
Source: packages/core/src/sql/schemas/initial/Dictionary.sql
-- Dictionary Module Database Schema
-- Database: dictionary_[abbreviation].db
-- Purpose: Dictionary, lexicon, or concordance content
-- Version: 2.0.0
-- Generated: 2025-10-10
-- Updated: 2026-07-24 (Module Format v2 — see docs/Design/DataModel/ModuleFormat-v2-Plan.md)
--
-- v2 changes:
-- * WS-3: unified `verse_link` table replaces the JSON `example_verses` column on
-- `dictionary_entry`.
-- * WS-4: `module_info` gains identity + provenance columns; `version` renamed to
-- `content_version`. `word_occurrence.bible_module` (a soft abbreviation
-- reference) becomes `bible_module_uuid`.
-- * WS-6: FTS5 update/delete triggers rewritten to the external-content
-- 'delete' command pattern.
-- * WS-7: CHECK constraint dropped from `module_info.dictionary_type` (open set).
--
-- Range convention (normative): `verse_id_start` inclusive, `verse_id_end` inclusive,
-- a single verse is expressed as `verse_id_end = verse_id_start`, never as NULL.
-- Never `start_verse_id`/`end_verse_id`.
-- ============================================================================
-- Pragmas and Initialization
-- ============================================================================
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA temp_store = MEMORY;
PRAGMA cache_size = -16000; -- 16MB cache
-- ============================================================================
-- 1. Module Information
-- ============================================================================
-- Exactly one row, info_id = 1
CREATE TABLE module_info (
info_id INTEGER PRIMARY KEY CHECK (info_id = 1),
module_type TEXT NOT NULL DEFAULT 'dictionary',
-- Identity (WS-4)
module_uuid TEXT, -- Stable identity across versions; the join key
abbreviation TEXT NOT NULL, -- Display / legacy key
full_name TEXT NOT NULL,
-- Dictionary specifics
dictionary_type TEXT NOT NULL, -- 'strongs', 'greek_lexicon', 'hebrew_lexicon',
-- 'bible_dictionary', 'topical', ...
-- WS-7: intentionally no CHECK - open set,
-- validated in TypeScript.
language_from TEXT, -- 'greek', 'hebrew', 'english'
language_to TEXT DEFAULT 'en',
-- Format + content versioning (WS-4)
format TEXT NOT NULL DEFAULT 'dictionary-module', -- Container format name
format_version TEXT NOT NULL DEFAULT '2.0', -- Spec version this file conforms to
content_version TEXT, -- Module's own content revision (was `version`)
content_sha256 TEXT, -- Integrity / dedup hash
-- Descriptive
author TEXT,
year_published INTEGER,
description TEXT,
language_code TEXT DEFAULT 'en', -- UI/display language of the entries themselves
-- (language_from/language_to describe the headwords)
publisher TEXT, -- From SWORD CopyrightHolder/Publisher
-- Licensing + provenance (WS-4)
copyright TEXT, -- Freeform, DISPLAY ONLY - never parsed
license_spdx TEXT, -- 'CC-BY-4.0', 'PD', 'Proprietary', ...
license_url TEXT, -- Full terms
source_url TEXT, -- Where this module came from
-- Canon + versification (WS-4) - explicit so a future format may differ
canon TEXT NOT NULL DEFAULT 'protestant-66',
versification TEXT NOT NULL DEFAULT 'kjv-english',
created_date TEXT DEFAULT CURRENT_TIMESTAMP,
metadata TEXT,
CHECK (module_type = 'dictionary')
-- WS-7: no CHECK on dictionary_type. It is an open/extensible set and SQLite
-- cannot alter a CHECK constraint without a full table rebuild. Validation lives
-- in TypeScript (packages/core/src/Data/Core/Types.ts) and is enforced at the
-- repository boundary.
);
-- ============================================================================
-- 2. Dictionary Entries
-- ============================================================================
CREATE TABLE dictionary_entry (
entry_id INTEGER PRIMARY KEY AUTOINCREMENT,
entry_key TEXT NOT NULL UNIQUE, -- "G25", "H430", "Love"
word TEXT, -- Original language word
transliteration TEXT,
pronunciation TEXT,
part_of_speech TEXT,
definition TEXT NOT NULL,
etymology TEXT,
usage_notes TEXT,
semantic_range TEXT,
related_words TEXT, -- JSON: array of related entry_keys
content_file TEXT, -- Path to full entry (optional)
metadata TEXT
-- WS-3: the JSON `example_verses` column is removed. Example/illustrative verses
-- are now rows in `verse_link` with source_type='dictionary_entry' and
-- source_id=entry_id, which makes them queryable and range-aware.
);
CREATE INDEX idx_entry_key ON dictionary_entry(entry_key);
CREATE INDEX idx_entry_word ON dictionary_entry(word);
-- ============================================================================
-- 3. Word Occurrences
-- ============================================================================
CREATE TABLE word_occurrence (
occurrence_id INTEGER PRIMARY KEY AUTOINCREMENT,
entry_key TEXT NOT NULL, -- "G25", "H430"
verse_id INTEGER NOT NULL,
bible_module_uuid TEXT, -- WS-4: which translation, by module_info.module_uuid
-- (was `bible_module`, a soft abbreviation reference)
translation_word TEXT, -- How it's translated in that verse
metadata TEXT,
FOREIGN KEY (entry_key) REFERENCES dictionary_entry(entry_key) ON DELETE CASCADE
);
CREATE INDEX idx_occurrence_entry ON word_occurrence(entry_key);
CREATE INDEX idx_occurrence_verse ON word_occurrence(verse_id);
-- ============================================================================
-- 4. Verse Linking (WS-3)
-- ============================================================================
-- Unified content->verse linking. This table is byte-identical in every module
-- schema and in the user database. It replaces the JSON `example_verses` column
-- that used to live on `dictionary_entry`.
CREATE TABLE verse_link (
link_id INTEGER PRIMARY KEY AUTOINCREMENT,
source_type TEXT NOT NULL, -- 'commentary_entry','book_section','dictionary_entry',
-- 'devotional_entry','topic','note','journal','prayer'
source_id INTEGER NOT NULL,
verse_id_start INTEGER NOT NULL,
verse_id_end INTEGER NOT NULL, -- inclusive; single verse is expressed as end = start
link_type TEXT NOT NULL DEFAULT 'reference', -- reference|annotation|primary_passage|cross_reference
sort_order INTEGER NOT NULL DEFAULT 0,
context TEXT,
metadata TEXT
);
CREATE INDEX idx_verse_link_source ON verse_link(source_type, source_id, sort_order);
CREATE INDEX idx_verse_link_start ON verse_link(verse_id_start);
CREATE INDEX idx_verse_link_range ON verse_link(verse_id_start, verse_id_end);
-- R-2: the reverse pair, so a containment probe (start <= X AND end >= X) can be
-- driven from either side. Cheap, and only useful once end is NOT NULL.
CREATE INDEX idx_verse_link_covering ON verse_link(verse_id_end, verse_id_start);
-- WS-7: no CHECK on verse_link.source_type or verse_link.link_type. Both are
-- open/extensible sets; validation lives in TypeScript
-- (packages/core/src/Data/Core/Types.ts) and is enforced at the repository boundary.
-- ============================================================================
-- 5. Full-Text Search
-- ============================================================================
CREATE VIRTUAL TABLE dictionary_entry_fts USING fts5(
entry_id UNINDEXED,
entry_key UNINDEXED,
word,
definition,
usage_notes,
content='dictionary_entry',
content_rowid='entry_id',
tokenize='porter unicode61'
);
-- Triggers (WS-6)
-- External-content FTS5 tables do not own their data, so rows must be removed with
-- the special 'delete' command carrying the OLD column values. A plain
-- DELETE/UPDATE against the FTS table leaves stale terms in the index.
CREATE TRIGGER dictionary_entry_fts_insert AFTER INSERT ON dictionary_entry BEGIN
INSERT INTO dictionary_entry_fts(rowid, entry_id, entry_key, word, definition, usage_notes)
VALUES (new.entry_id, new.entry_id, new.entry_key, new.word, new.definition, new.usage_notes);
END;
CREATE TRIGGER dictionary_entry_fts_delete AFTER DELETE ON dictionary_entry BEGIN
INSERT INTO dictionary_entry_fts(dictionary_entry_fts, rowid, entry_id, entry_key, word, definition, usage_notes)
VALUES ('delete', old.entry_id, old.entry_id, old.entry_key, old.word, old.definition, old.usage_notes);
END;
CREATE TRIGGER dictionary_entry_fts_update AFTER UPDATE ON dictionary_entry BEGIN
INSERT INTO dictionary_entry_fts(dictionary_entry_fts, rowid, entry_id, entry_key, word, definition, usage_notes)
VALUES ('delete', old.entry_id, old.entry_id, old.entry_key, old.word, old.definition, old.usage_notes);
INSERT INTO dictionary_entry_fts(rowid, entry_id, entry_key, word, definition, usage_notes)
VALUES (new.entry_id, new.entry_id, new.entry_key, new.word, new.definition, new.usage_notes);
END;
-- ============================================================================
-- 6. Schema Version
-- ============================================================================
CREATE TABLE schema_version (
version_id INTEGER PRIMARY KEY AUTOINCREMENT,
version_number TEXT NOT NULL,
applied_date TEXT DEFAULT CURRENT_TIMESTAMP,
notes TEXT,
metadata TEXT
);
INSERT INTO schema_version (version_number, notes)
VALUES ('2.0.0', 'Module Format v2: unified verse_link replaces JSON example_verses; module_info identity/provenance columns with version renamed to content_version; word_occurrence.bible_module renamed to bible_module_uuid; FTS5 external-content triggers corrected; CHECK dropped from dictionary_type (validated in TypeScript).');
Book (book)
Source: packages/core/src/sql/schemas/initial/Book.sql
-- Book Module Database Schema
-- Database: book_[abbreviation].db
-- Purpose: General study books (theology, history, systematic works)
-- Version: 2.0.0
-- Generated: 2025-10-10
-- Updated: 2026-07-24 (Module Format v2 — see docs/Design/DataModel/ModuleFormat-v2-Plan.md)
--
-- v2 changes:
-- * WS-3: unified `verse_link` table replaces the `scripture_reference` table.
-- * WS-4: `module_info` gains identity + provenance columns; `version` renamed to
-- `content_version`.
-- * WS-6: `book_section.sort_order` added (section_number is TEXT, so "1.10"
-- sorted before "1.2"); FTS5 update/delete triggers rewritten to the
-- external-content 'delete' command pattern.
--
-- Range convention (normative): `verse_id_start` inclusive, `verse_id_end` inclusive,
-- a single verse is expressed as `verse_id_end = verse_id_start`, never as NULL.
-- Never `start_verse_id`/`end_verse_id`.
-- ============================================================================
-- Pragmas and Initialization
-- ============================================================================
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA temp_store = MEMORY;
PRAGMA cache_size = -32000; -- 32MB cache
-- ============================================================================
-- 1. Module Information
-- ============================================================================
-- Exactly one row, info_id = 1
CREATE TABLE module_info (
info_id INTEGER PRIMARY KEY CHECK (info_id = 1),
module_type TEXT NOT NULL DEFAULT 'book',
-- Identity (WS-4)
module_uuid TEXT, -- Stable identity across versions; the join key
abbreviation TEXT NOT NULL, -- Display / legacy key
full_name TEXT NOT NULL,
-- Format + content versioning (WS-4)
format TEXT NOT NULL DEFAULT 'book-module', -- Container format name
format_version TEXT NOT NULL DEFAULT '2.0', -- Spec version this file conforms to
content_version TEXT, -- Module's own content revision (was `version`)
content_sha256 TEXT, -- Integrity / dedup hash
-- Descriptive
author TEXT,
year_published INTEGER,
description TEXT,
language_code TEXT DEFAULT 'en',
publisher TEXT,
-- Licensing + provenance (WS-4)
copyright TEXT, -- Freeform, DISPLAY ONLY - never parsed
license_spdx TEXT, -- 'CC-BY-4.0', 'PD', 'Proprietary', ...
license_url TEXT, -- Full terms
source_url TEXT, -- Where this module came from
-- Canon + versification (WS-4) - explicit so a future format may differ
canon TEXT NOT NULL DEFAULT 'protestant-66',
versification TEXT NOT NULL DEFAULT 'kjv-english',
created_date TEXT DEFAULT CURRENT_TIMESTAMP,
metadata TEXT,
CHECK (module_type = 'book')
);
-- ============================================================================
-- 2. Book Structure
-- ============================================================================
-- 2.1 Table of Contents / Sections
CREATE TABLE book_section (
section_id INTEGER PRIMARY KEY AUTOINCREMENT,
parent_section_id INTEGER, -- For hierarchical TOC
section_number TEXT, -- DISPLAY label: "1.2.3" or "Chapter 5"
sort_order INTEGER NOT NULL DEFAULT 0, -- WS-6: authoritative ordering among siblings.
-- section_number is TEXT and sorts "1.10"
-- before "1.2", so it cannot be used for order.
title TEXT NOT NULL,
content TEXT, -- Text content
content_file TEXT, -- OR path to external file
word_count INTEGER,
metadata TEXT,
FOREIGN KEY (parent_section_id) REFERENCES book_section(section_id) ON DELETE CASCADE
);
CREATE INDEX idx_section_parent ON book_section(parent_section_id);
CREATE INDEX idx_section_number ON book_section(section_number);
CREATE INDEX idx_section_sort ON book_section(parent_section_id, sort_order);
-- ============================================================================
-- 3. Verse Linking (WS-3)
-- ============================================================================
-- Unified content->verse linking. This table is byte-identical in every module
-- schema and in the user database. It replaces the former `scripture_reference`
-- table: section_id becomes (source_type='book_section', source_id), and the old
-- `context` column carries over unchanged.
CREATE TABLE verse_link (
link_id INTEGER PRIMARY KEY AUTOINCREMENT,
source_type TEXT NOT NULL, -- 'commentary_entry','book_section','dictionary_entry',
-- 'devotional_entry','topic','note','journal','prayer'
source_id INTEGER NOT NULL,
verse_id_start INTEGER NOT NULL,
verse_id_end INTEGER NOT NULL, -- inclusive; single verse is expressed as end = start
link_type TEXT NOT NULL DEFAULT 'reference', -- reference|annotation|primary_passage|cross_reference
sort_order INTEGER NOT NULL DEFAULT 0,
context TEXT,
metadata TEXT
);
CREATE INDEX idx_verse_link_source ON verse_link(source_type, source_id, sort_order);
CREATE INDEX idx_verse_link_start ON verse_link(verse_id_start);
CREATE INDEX idx_verse_link_range ON verse_link(verse_id_start, verse_id_end);
-- R-2: the reverse pair, so a containment probe (start <= X AND end >= X) can be
-- driven from either side. Cheap, and only useful once end is NOT NULL.
CREATE INDEX idx_verse_link_covering ON verse_link(verse_id_end, verse_id_start);
-- WS-7: no CHECK on verse_link.source_type or verse_link.link_type. Both are
-- open/extensible sets; validation lives in TypeScript
-- (packages/core/src/Data/Core/Types.ts) and is enforced at the repository boundary.
-- ============================================================================
-- 4. Full-Text Search
-- ============================================================================
CREATE VIRTUAL TABLE book_section_fts USING fts5(
section_id UNINDEXED,
title,
content,
content='book_section',
content_rowid='section_id',
tokenize='porter unicode61'
);
-- Triggers (WS-6)
-- External-content FTS5 tables do not own their data, so rows must be removed with
-- the special 'delete' command carrying the OLD column values. A plain
-- DELETE/UPDATE against the FTS table leaves stale terms in the index.
CREATE TRIGGER book_section_fts_insert AFTER INSERT ON book_section BEGIN
INSERT INTO book_section_fts(rowid, section_id, title, content)
VALUES (new.section_id, new.section_id, new.title, new.content);
END;
CREATE TRIGGER book_section_fts_delete AFTER DELETE ON book_section BEGIN
INSERT INTO book_section_fts(book_section_fts, rowid, section_id, title, content)
VALUES ('delete', old.section_id, old.section_id, old.title, old.content);
END;
CREATE TRIGGER book_section_fts_update AFTER UPDATE ON book_section BEGIN
INSERT INTO book_section_fts(book_section_fts, rowid, section_id, title, content)
VALUES ('delete', old.section_id, old.section_id, old.title, old.content);
INSERT INTO book_section_fts(rowid, section_id, title, content)
VALUES (new.section_id, new.section_id, new.title, new.content);
END;
-- ============================================================================
-- 5. Schema Version
-- ============================================================================
CREATE TABLE schema_version (
version_id INTEGER PRIMARY KEY AUTOINCREMENT,
version_number TEXT NOT NULL,
applied_date TEXT DEFAULT CURRENT_TIMESTAMP,
notes TEXT,
metadata TEXT
);
INSERT INTO schema_version (version_number, notes)
VALUES ('2.0.0', 'Module Format v2: unified verse_link replaces scripture_reference; module_info identity/provenance columns with version renamed to content_version; book_section.sort_order added; FTS5 external-content triggers corrected.');
Devotional (devotional)
Source: packages/core/src/sql/schemas/initial/Devotional.sql
-- Devotional Module Database Schema
-- Database: devotional_[abbreviation].db
-- Purpose: Daily devotional content
-- Version: 2.0.0
-- Generated: 2025-10-10
-- Updated: 2026-07-24 (Module Format v2 — see docs/Design/DataModel/ModuleFormat-v2-Plan.md)
--
-- v2 changes:
-- * WS-3: unified `verse_link` table replaces the JSON `scripture_verses` column on
-- `devotional_entry`. The freeform `scripture_reference` display label is
-- KEPT (see note on that column).
-- * WS-4: `module_info` gains identity + provenance columns; `version` renamed to
-- `content_version`.
-- * WS-6: `devotional_entry.sort_order` added (day_number is nullable when
-- devotional_type='continuous', so it cannot order every module); FTS5
-- update/delete triggers rewritten to the external-content 'delete'
-- command pattern.
--
-- Range convention (normative): `verse_id_start` inclusive, `verse_id_end` inclusive,
-- a single verse is expressed as `verse_id_end = verse_id_start`, never as NULL.
-- Never `start_verse_id`/`end_verse_id`.
-- ============================================================================
-- Pragmas and Initialization
-- ============================================================================
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA temp_store = MEMORY;
PRAGMA cache_size = -16000; -- 16MB cache
-- ============================================================================
-- 1. Module Information
-- ============================================================================
-- Exactly one row, info_id = 1
CREATE TABLE module_info (
info_id INTEGER PRIMARY KEY CHECK (info_id = 1),
module_type TEXT NOT NULL DEFAULT 'devotional',
-- Identity (WS-4)
module_uuid TEXT, -- Stable identity across versions; the join key
abbreviation TEXT NOT NULL, -- Display / legacy key
full_name TEXT NOT NULL,
-- Devotional specifics
devotional_type TEXT NOT NULL, -- 'day_of_year', 'fixed_length', 'continuous'
total_days INTEGER, -- 365, 40, NULL for continuous
-- Format + content versioning (WS-4)
format TEXT NOT NULL DEFAULT 'devotional-module', -- Container format name
format_version TEXT NOT NULL DEFAULT '2.0', -- Spec version this file conforms to
content_version TEXT, -- Module's own content revision (was `version`)
content_sha256 TEXT, -- Integrity / dedup hash
-- Descriptive
author TEXT,
year_published INTEGER,
description TEXT,
language_code TEXT DEFAULT 'en',
publisher TEXT, -- From SWORD CopyrightHolder/Publisher
-- Licensing + provenance (WS-4)
copyright TEXT, -- Freeform, DISPLAY ONLY - never parsed
license_spdx TEXT, -- 'CC-BY-4.0', 'PD', 'Proprietary', ...
license_url TEXT, -- Full terms
source_url TEXT, -- Where this module came from
-- Canon + versification (WS-4) - explicit so a future format may differ
canon TEXT NOT NULL DEFAULT 'protestant-66',
versification TEXT NOT NULL DEFAULT 'kjv-english',
created_date TEXT DEFAULT CURRENT_TIMESTAMP,
metadata TEXT,
CHECK (module_type = 'devotional'),
-- WS-7: devotional_type is a genuinely closed domain set (it determines how the
-- module is navigated), so the CHECK is retained.
CHECK (devotional_type IN ('day_of_year', 'fixed_length', 'continuous'))
);
-- ============================================================================
-- 2. Devotional Entries
-- ============================================================================
CREATE TABLE devotional_entry (
entry_id INTEGER PRIMARY KEY AUTOINCREMENT,
day_number INTEGER, -- 1-365 or sequential; NULL for 'continuous'
sort_order INTEGER NOT NULL DEFAULT 0, -- WS-6: authoritative reading order. day_number is
-- nullable (devotional_type='continuous'), so it
-- cannot order every module.
date_label TEXT, -- "January 1" or "Morning"
title TEXT,
content TEXT NOT NULL, -- Devotional text
scripture_reference TEXT, -- DISPLAY label as the author wrote it,
-- e.g. "John 3:16-17", "Ps 23 (NIV)".
-- KEPT: carries the author's own citation
-- wording/abbreviation/translation note, which the
-- structured verse_link rows cannot reproduce.
scripture_text TEXT, -- Embedded verse text (optional)
author_note TEXT,
metadata TEXT,
-- WS-3: the JSON `scripture_verses` column is removed. The machine-readable
-- verse references are now rows in `verse_link` with
-- source_type='devotional_entry', source_id=entry_id and
-- link_type='primary_passage' for the entry's main passage.
-- Retained: SQLite treats NULLs as distinct in a UNIQUE index, so this still
-- permits the many NULL day_numbers of a 'continuous' devotional.
UNIQUE(day_number)
);
CREATE INDEX idx_devotional_day ON devotional_entry(day_number);
CREATE INDEX idx_devotional_date ON devotional_entry(date_label);
CREATE INDEX idx_devotional_sort ON devotional_entry(sort_order);
-- ============================================================================
-- 3. Verse Linking (WS-3)
-- ============================================================================
-- Unified content->verse linking. This table is byte-identical in every module
-- schema and in the user database. It replaces the JSON `scripture_verses` column
-- that used to live on `devotional_entry`.
CREATE TABLE verse_link (
link_id INTEGER PRIMARY KEY AUTOINCREMENT,
source_type TEXT NOT NULL, -- 'commentary_entry','book_section','dictionary_entry',
-- 'devotional_entry','topic','note','journal','prayer'
source_id INTEGER NOT NULL,
verse_id_start INTEGER NOT NULL,
verse_id_end INTEGER NOT NULL, -- inclusive; single verse is expressed as end = start
link_type TEXT NOT NULL DEFAULT 'reference', -- reference|annotation|primary_passage|cross_reference
sort_order INTEGER NOT NULL DEFAULT 0,
context TEXT,
metadata TEXT
);
CREATE INDEX idx_verse_link_source ON verse_link(source_type, source_id, sort_order);
CREATE INDEX idx_verse_link_start ON verse_link(verse_id_start);
CREATE INDEX idx_verse_link_range ON verse_link(verse_id_start, verse_id_end);
-- R-2: the reverse pair, so a containment probe (start <= X AND end >= X) can be
-- driven from either side. Cheap, and only useful once end is NOT NULL.
CREATE INDEX idx_verse_link_covering ON verse_link(verse_id_end, verse_id_start);
-- WS-7: no CHECK on verse_link.source_type or verse_link.link_type. Both are
-- open/extensible sets; validation lives in TypeScript
-- (packages/core/src/Data/Core/Types.ts) and is enforced at the repository boundary.
-- ============================================================================
-- 4. Full-Text Search
-- ============================================================================
CREATE VIRTUAL TABLE devotional_entry_fts USING fts5(
entry_id UNINDEXED,
title,
content,
content='devotional_entry',
content_rowid='entry_id',
tokenize='porter unicode61'
);
-- Triggers (WS-6)
-- External-content FTS5 tables do not own their data, so rows must be removed with
-- the special 'delete' command carrying the OLD column values. A plain
-- DELETE/UPDATE against the FTS table leaves stale terms in the index.
CREATE TRIGGER devotional_entry_fts_insert AFTER INSERT ON devotional_entry BEGIN
INSERT INTO devotional_entry_fts(rowid, entry_id, title, content)
VALUES (new.entry_id, new.entry_id, new.title, new.content);
END;
CREATE TRIGGER devotional_entry_fts_delete AFTER DELETE ON devotional_entry BEGIN
INSERT INTO devotional_entry_fts(devotional_entry_fts, rowid, entry_id, title, content)
VALUES ('delete', old.entry_id, old.entry_id, old.title, old.content);
END;
CREATE TRIGGER devotional_entry_fts_update AFTER UPDATE ON devotional_entry BEGIN
INSERT INTO devotional_entry_fts(devotional_entry_fts, rowid, entry_id, title, content)
VALUES ('delete', old.entry_id, old.entry_id, old.title, old.content);
INSERT INTO devotional_entry_fts(rowid, entry_id, title, content)
VALUES (new.entry_id, new.entry_id, new.title, new.content);
END;
-- ============================================================================
-- 5. Schema Version
-- ============================================================================
CREATE TABLE schema_version (
version_id INTEGER PRIMARY KEY AUTOINCREMENT,
version_number TEXT NOT NULL,
applied_date TEXT DEFAULT CURRENT_TIMESTAMP,
notes TEXT,
metadata TEXT
);
INSERT INTO schema_version (version_number, notes)
VALUES ('2.0.0', 'Module Format v2: unified verse_link replaces JSON scripture_verses (freeform scripture_reference display label retained); module_info identity/provenance columns with version renamed to content_version; devotional_entry.sort_order added; FTS5 external-content triggers corrected.');
Topical index (topical_index)
Source: packages/core/src/sql/schemas/initial/TopicalIndex.sql
-- Topical Index Module Database Schema
-- Database: topical_[abbreviation].db
-- Purpose: Hierarchical topical index (Nave's, Torrey's, ...) mapping topics to passages
-- Version: 2.0.0
-- Generated: 2026-07-24 (Module Format v2 — see docs/Design/DataModel/ModuleFormat-v2-Plan.md)
--
-- This schema had no schema file before v2: its DDL lived only inside
-- `scripts/convert-topical-index.js`. Promoted here per WS-8.3.
--
-- v2 changes vs. the DDL embedded in convert-topical-index.js:
-- * WS-8.3: table names singularized — `topics` -> `topic`, `topics_fts` -> `topic_fts`.
-- * WS-8.3: the competing `topical_index_module_info` table is REMOVED. There is now
-- exactly one info table, `module_info`, and it no longer carries the
-- `dictionary_type` / `language_from` / `language_to` columns that were
-- copy-pasted in from the Dictionary schema. The old scalar counters
-- (`topic_count`, `verse_count`) are derived data and live in
-- `module_info.metadata` JSON instead of dedicated columns.
-- * WS-3: `topic_verses` is REPLACED by the unified `verse_link` table
-- (source_type='topic', source_id=topic_id). This also retires the
-- reversed `start_verse_id` / `end_verse_id` column spelling.
-- * WS-4: `module_info` gains identity + provenance columns; `version` renamed to
-- `content_version`.
-- * WS-6: FTS5 triggers use the external-content 'delete' command pattern.
-- (convert-topical-index.js already had this right — it is the reference
-- implementation; the insert trigger is carried over unchanged.)
-- * WS-7: no CHECK constraints on open/extensible sets.
--
-- Range convention (normative): `verse_id_start` inclusive, `verse_id_end` inclusive,
-- a single verse is expressed as `verse_id_end = verse_id_start`, never as NULL.
-- Never `start_verse_id`/`end_verse_id`.
-- ============================================================================
-- Pragmas and Initialization
-- ============================================================================
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA temp_store = MEMORY;
PRAGMA cache_size = -16000; -- 16MB cache
-- ============================================================================
-- 1. Module Information
-- ============================================================================
-- 1.1 Module Metadata (exactly one row, info_id = 1)
CREATE TABLE module_info (
info_id INTEGER PRIMARY KEY CHECK (info_id = 1),
module_type TEXT NOT NULL DEFAULT 'topical_index',
-- Identity (WS-4)
module_uuid TEXT, -- Stable identity across versions; the join key
abbreviation TEXT NOT NULL, -- "NaveTopics", "TorreyTopics" (display / legacy key)
full_name TEXT NOT NULL, -- "Nave's Topical Bible"
-- Format + content versioning (WS-4)
format TEXT NOT NULL DEFAULT 'topical-index-module', -- Container format name
format_version TEXT NOT NULL DEFAULT '2.0', -- Spec version this file conforms to
content_version TEXT, -- Module's own content revision (was `version`)
content_sha256 TEXT, -- Integrity / dedup hash
-- Descriptive
author TEXT,
year_published INTEGER,
description TEXT,
language_code TEXT DEFAULT 'en',
publisher TEXT, -- From SWORD CopyrightHolder/Publisher
-- Licensing + provenance (WS-4)
copyright TEXT, -- Freeform, DISPLAY ONLY - never parsed
license_spdx TEXT, -- 'CC-BY-4.0', 'PD', 'Proprietary', ...
license_url TEXT, -- Full terms
source_url TEXT, -- Where this module came from
-- Canon + versification (WS-4) - explicit so a future format may differ
canon TEXT NOT NULL DEFAULT 'protestant-66',
versification TEXT NOT NULL DEFAULT 'kjv-english',
created_date TEXT DEFAULT CURRENT_TIMESTAMP,
metadata TEXT, -- JSON. Derived counters live here:
-- {"topicCount":n,"verseLinkCount":n,"sourceModule":"dictionary_nave"}
CHECK (module_type = 'topical_index')
);
-- ============================================================================
-- 2. Topics
-- ============================================================================
-- 2.1 Topic Hierarchy
-- Self-referential parent/child tree of arbitrary depth. Nave's ships three levels
-- (topic -> sub-topic -> section child); the schema does not cap the depth.
-- Breadcrumbs are produced by walking `parent_topic_id` upward with a recursive CTE.
CREATE TABLE topic (
topic_id INTEGER PRIMARY KEY AUTOINCREMENT,
parent_topic_id INTEGER REFERENCES topic(topic_id), -- NULL = root topic
name TEXT NOT NULL, -- "Aaron", "Lineage of", "MIRACLES OF"
description TEXT, -- Annotations, "See X" redirects
sort_order INTEGER, -- Source ordering within the parent
metadata TEXT -- JSON, e.g. {"seeAlso":["PRIESTHOOD"]}
);
CREATE INDEX idx_topic_parent ON topic(parent_topic_id);
CREATE INDEX idx_topic_name ON topic(name COLLATE NOCASE);
-- ============================================================================
-- 3. Verse Linking (WS-3)
-- ============================================================================
-- Unified content->verse linking. This table is byte-identical in every module
-- schema and in the user database. For a topical index every row has
-- source_type='topic' and source_id=topic.topic_id; a topic's passages are its own
-- rows, and a subtree's passages are the union over the topic's descendants.
--
-- Replaces the v1 `topic_verses` table:
-- topic_verses.topic_id -> verse_link.source_id (source_type='topic')
-- topic_verses.start_verse_id -> verse_link.verse_id_start
-- topic_verses.end_verse_id -> verse_link.verse_id_end (NULL when single verse)
-- topic_verses.context -> verse_link.context
-- topic_verses.sort_order -> verse_link.sort_order
CREATE TABLE verse_link (
link_id INTEGER PRIMARY KEY AUTOINCREMENT,
source_type TEXT NOT NULL, -- 'commentary_entry','book_section','dictionary_entry',
-- 'devotional_entry','topic','note','journal','prayer'
source_id INTEGER NOT NULL,
verse_id_start INTEGER NOT NULL,
verse_id_end INTEGER NOT NULL, -- inclusive; single verse is expressed as end = start
link_type TEXT NOT NULL DEFAULT 'reference', -- reference|annotation|primary_passage|cross_reference
sort_order INTEGER NOT NULL DEFAULT 0,
context TEXT,
metadata TEXT
);
CREATE INDEX idx_verse_link_source ON verse_link(source_type, source_id, sort_order);
CREATE INDEX idx_verse_link_start ON verse_link(verse_id_start);
CREATE INDEX idx_verse_link_range ON verse_link(verse_id_start, verse_id_end);
-- R-2: the reverse pair, so a containment probe (start <= X AND end >= X) can be
-- driven from either side. Cheap, and only useful once end is NOT NULL.
CREATE INDEX idx_verse_link_covering ON verse_link(verse_id_end, verse_id_start);
-- WS-7: no CHECK on verse_link.source_type or verse_link.link_type. Both are
-- open/extensible sets; validation lives in TypeScript
-- (packages/core/src/Data/Core/Types.ts) and is enforced at the repository boundary.
--
-- NOTE for writers: v1 `topic_verses` deduplicated by its PRIMARY KEY
-- (topic_id, start_verse_id, end_verse_id). `verse_link` has a surrogate key and no
-- such constraint, so converters MUST deduplicate verse ranges per topic before
-- inserting.
--
-- NOTE for readers: "which topics cover verse V" is
-- WHERE verse_id_start <= V AND COALESCE(verse_id_end, verse_id_start) >= V
-- and a range's verse count is
-- COALESCE(verse_id_end, verse_id_start) - verse_id_start + 1
-- ============================================================================
-- 4. Full-Text Search
-- ============================================================================
-- 4.1 Topic name FTS
CREATE VIRTUAL TABLE topic_fts USING fts5(
name,
content='topic',
content_rowid='topic_id',
tokenize='porter unicode61'
);
-- Triggers (WS-6)
-- External-content FTS5 tables do not own their data, so rows must be removed with
-- the special 'delete' command carrying the OLD column values. A plain
-- DELETE/UPDATE against the FTS table leaves stale terms in the index.
CREATE TRIGGER topic_fts_insert AFTER INSERT ON topic BEGIN
INSERT INTO topic_fts(rowid, name) VALUES (new.topic_id, new.name);
END;
CREATE TRIGGER topic_fts_delete AFTER DELETE ON topic BEGIN
INSERT INTO topic_fts(topic_fts, rowid, name) VALUES('delete', old.topic_id, old.name);
END;
CREATE TRIGGER topic_fts_update AFTER UPDATE ON topic BEGIN
INSERT INTO topic_fts(topic_fts, rowid, name) VALUES('delete', old.topic_id, old.name);
INSERT INTO topic_fts(rowid, name) VALUES (new.topic_id, new.name);
END;
-- ============================================================================
-- 5. Schema Version
-- ============================================================================
CREATE TABLE schema_version (
version_id INTEGER PRIMARY KEY AUTOINCREMENT,
version_number TEXT NOT NULL,
applied_date TEXT DEFAULT CURRENT_TIMESTAMP,
notes TEXT,
metadata TEXT
);
INSERT INTO schema_version (version_number, notes)
VALUES ('2.0.0', 'Module Format v2: first schema file for the topical index module type (was embedded in scripts/convert-topical-index.js). Singular table names (topics -> topic); single module_info shape with WS-4 identity/provenance (topical_index_module_info and the copy-pasted Dictionary columns removed); topic_verses replaced by the unified verse_link table.');
Cross-reference (cross_reference)
Source: packages/core/src/sql/schemas/initial/CrossReference.sql
-- Cross-Reference Module Database Schema
-- Database: xref_[abbreviation].db
-- Purpose: Phrase-grouped scripture cross-references (Treasury of Scripture Knowledge, ...)
-- Version: 2.0.0
-- Generated: 2026-07-24 (Module Format v2 — see docs/Design/DataModel/ModuleFormat-v2-Plan.md)
--
-- This schema had no schema file before v2: its DDL lived only inside
-- `scripts/import-tsk.js`. Promoted here per WS-8.3.
--
-- Shape: a source verse owns an ordered list of phrase groups ("For God so loved"),
-- and each group owns an ordered list of target passages. The groups are the module's
-- own content; the targets are verse links and therefore live in `verse_link`.
--
-- v2 changes vs. the DDL embedded in import-tsk.js:
-- * WS-3: `cross_reference_entry` is REPLACED by the unified `verse_link` table
-- (source_type='cross_reference_group', link_type='cross_reference'). This
-- retires the third range spelling `target_verse_id`/`target_verse_end_id`.
-- * WS-4: `module_info` gains identity + provenance columns; `version` renamed to
-- `content_version`.
-- * The group's source anchor is normalized to `verse_id_start`/`verse_id_end`
-- (was a bare `verse_id`), so a group may anchor to a passage as well as a
-- single verse. TSK writes verse_id_end = NULL.
-- * WS-7: no CHECK constraints on open/extensible sets.
--
-- Range convention (normative): `verse_id_start` inclusive, `verse_id_end` inclusive,
-- a single verse is expressed as `verse_id_end = verse_id_start`, never as NULL.
-- Never `start_verse_id`/`end_verse_id`.
-- ============================================================================
-- Pragmas and Initialization
-- ============================================================================
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA temp_store = MEMORY;
PRAGMA cache_size = -16000; -- 16MB cache
-- ============================================================================
-- 1. Module Information
-- ============================================================================
-- 1.1 Module Metadata (exactly one row, info_id = 1)
CREATE TABLE module_info (
info_id INTEGER PRIMARY KEY CHECK (info_id = 1),
module_type TEXT NOT NULL DEFAULT 'cross_reference',
-- Identity (WS-4)
module_uuid TEXT, -- Stable identity across versions; the join key
abbreviation TEXT NOT NULL, -- "TSKxref" (display / legacy key)
full_name TEXT NOT NULL, -- "Treasury of Scripture Knowledge"
-- Format + content versioning (WS-4)
format TEXT NOT NULL DEFAULT 'cross-reference-module', -- Container format name
format_version TEXT NOT NULL DEFAULT '2.0', -- Spec version this file conforms to
content_version TEXT, -- Module's own content revision (was `version`)
content_sha256 TEXT, -- Integrity / dedup hash
-- Descriptive
author TEXT,
year_published INTEGER,
description TEXT,
language_code TEXT DEFAULT 'en',
publisher TEXT, -- From SWORD CopyrightHolder/Publisher
-- Licensing + provenance (WS-4)
copyright TEXT, -- Freeform, DISPLAY ONLY - never parsed
license_spdx TEXT, -- 'CC-BY-4.0', 'PD', 'Proprietary', ...
license_url TEXT, -- Full terms
source_url TEXT, -- Where this module came from
-- Canon + versification (WS-4) - explicit so a future format may differ
canon TEXT NOT NULL DEFAULT 'protestant-66',
versification TEXT NOT NULL DEFAULT 'kjv-english',
created_date TEXT DEFAULT CURRENT_TIMESTAMP,
metadata TEXT, -- JSON. Derived counters live here:
-- {"groupCount":n,"linkCount":n,"sourceModule":"commentary_tsk"}
CHECK (module_type = 'cross_reference')
);
-- ============================================================================
-- 2. Cross-Reference Groups
-- ============================================================================
-- 2.1 Phrase Group
-- One group per phrase of the source passage. `phrase` is NULL for references that
-- the source lists without a phrase label. The targets belonging to a group are
-- `verse_link` rows with source_type='cross_reference_group' and source_id=group_id.
CREATE TABLE cross_reference_group (
group_id INTEGER PRIMARY KEY AUTOINCREMENT,
verse_id_start INTEGER NOT NULL, -- Source passage (inclusive)
verse_id_end INTEGER NOT NULL, -- inclusive; single verse is expressed as end = start
phrase TEXT, -- "For God so loved the world"
sort_order INTEGER NOT NULL DEFAULT 0, -- Order of groups within the source verse
metadata TEXT
);
CREATE INDEX idx_xref_group_start ON cross_reference_group(verse_id_start);
CREATE INDEX idx_xref_group_range ON cross_reference_group(verse_id_start, verse_id_end);
-- ============================================================================
-- 3. Verse Linking (WS-3)
-- ============================================================================
-- Unified content->verse linking. This table is byte-identical in every module
-- schema and in the user database. For a cross-reference module every row is a
-- target passage of a phrase group: source_type='cross_reference_group',
-- source_id=cross_reference_group.group_id, link_type='cross_reference'.
--
-- Replaces the v1 `cross_reference_entry` table:
-- cross_reference_entry.group_id -> verse_link.source_id
-- cross_reference_entry.target_verse_id -> verse_link.verse_id_start
-- cross_reference_entry.target_verse_end_id -> verse_link.verse_id_end
-- cross_reference_entry.note -> verse_link.context
-- cross_reference_entry.sort_order -> verse_link.sort_order
-- cross_reference_entry.metadata -> verse_link.metadata
CREATE TABLE verse_link (
link_id INTEGER PRIMARY KEY AUTOINCREMENT,
source_type TEXT NOT NULL, -- 'commentary_entry','book_section','dictionary_entry',
-- 'devotional_entry','topic','note','journal','prayer'
source_id INTEGER NOT NULL,
verse_id_start INTEGER NOT NULL,
verse_id_end INTEGER NOT NULL, -- inclusive; single verse is expressed as end = start
link_type TEXT NOT NULL DEFAULT 'reference', -- reference|annotation|primary_passage|cross_reference
sort_order INTEGER NOT NULL DEFAULT 0,
context TEXT,
metadata TEXT
);
CREATE INDEX idx_verse_link_source ON verse_link(source_type, source_id, sort_order);
CREATE INDEX idx_verse_link_start ON verse_link(verse_id_start);
CREATE INDEX idx_verse_link_range ON verse_link(verse_id_start, verse_id_end);
-- R-2: the reverse pair, so a containment probe (start <= X AND end >= X) can be
-- driven from either side. Cheap, and only useful once end is NOT NULL.
CREATE INDEX idx_verse_link_covering ON verse_link(verse_id_end, verse_id_start);
-- WS-7: no CHECK on verse_link.source_type or verse_link.link_type. Both are
-- open/extensible sets; validation lives in TypeScript
-- (packages/core/src/Data/Core/Types.ts) and is enforced at the repository boundary.
--
-- NOTE for writers: `verse_link` carries no foreign key to cross_reference_group
-- (it is generic across source types), so the ON DELETE CASCADE that v1
-- `cross_reference_entry` had is gone. Deleting a group must delete its links
-- explicitly:
-- DELETE FROM verse_link
-- WHERE source_type = 'cross_reference_group' AND source_id = ?;
--
-- NOTE for readers: the reverse lookup ("what points at verse V?") joins back to the
-- group for the source passage and phrase:
-- SELECT g.verse_id_start, g.phrase, l.context
-- FROM verse_link l
-- JOIN cross_reference_group g ON g.group_id = l.source_id
-- WHERE l.source_type = 'cross_reference_group'
-- AND l.verse_id_start <= V AND COALESCE(l.verse_id_end, l.verse_id_start) >= V;
-- ============================================================================
-- 4. Schema Version
-- ============================================================================
CREATE TABLE schema_version (
version_id INTEGER PRIMARY KEY AUTOINCREMENT,
version_number TEXT NOT NULL,
applied_date TEXT DEFAULT CURRENT_TIMESTAMP,
notes TEXT,
metadata TEXT
);
INSERT INTO schema_version (version_number, notes)
VALUES ('2.0.0', 'Module Format v2: first schema file for the cross-reference module type (was embedded in scripts/import-tsk.js). cross_reference_entry replaced by the unified verse_link table; group anchor normalized to verse_id_start/verse_id_end; module_info gains WS-4 identity/provenance with version renamed to content_version.');
Tag graph (tag_graph)
Source: packages/core/src/sql/schemas/initial/TagGraph.sql
-- Tag Graph Module Database Schema
-- Database: tag_graph[_abbreviation].db
-- Purpose: Biblical entity graph — people, places, objects, themes, their attributes,
-- their relationships/associations, and the passages that evidence them
-- Version: 2.0.0
-- Generated: 2026-07-24 (Module Format v2 — see docs/Design/DataModel/ModuleFormat-v2-Plan.md)
--
-- This schema had no schema file before v2: its DDL lived only inside
-- `scripts/build-tag-graph.js` (with `entity_verses` additionally created by
-- `scripts/enrich-tag-graph-verses.js`). Promoted here per WS-8.3.
--
-- v2 changes vs. the DDL embedded in build-tag-graph.js:
-- * WS-8.3: table names singularized — `people` -> `person`, `places` -> `place`,
-- `objects` -> `object`, `themes` -> `theme`, `tag_associations` ->
-- `tag_association`, and so on for every table (see the mapping comment
-- above each section).
-- * WS-8.3 / WS-4: a `module_info` table (single row, info_id = 1) is added, so a
-- tag graph is a first-class module rather than an unregistered file.
-- WS-7 adds `tag_graph` to the recognized module types.
-- * WS-3: `association_verses` and `entity_verses` — two bespoke verse-linking
-- shapes with the reversed `start_verse_id`/`end_verse_id` spelling — are
-- merged into ONE link table using the canonical column names. See §8.
-- * WS-7: CHECK constraints dropped from the open/extensible enums
-- (`entity_category`, `confidence`, `match_type`, `significance`);
-- validation lives in TypeScript. The closed numeric domain
-- `strength BETWEEN 0.0 AND 1.0` is kept.
-- * `schema_version` added.
--
-- Entity identity is TEXT (slugs such as 'david', 'mount_sinai'). That is deliberate:
-- the taxonomy JSON sources, the polymorphic association columns, and the repository's
-- category->table dispatch all key on it, and every entity table therefore exposes the
-- same `id` / `name` / `notes` column trio. Category values stay plural
-- ('people','places','objects','themes') because they are DATA, not table names.
--
-- Range convention (normative): `verse_id_start` inclusive, `verse_id_end` inclusive,
-- a single verse is expressed as `verse_id_end = verse_id_start`, never as NULL.
-- Never `start_verse_id`/`end_verse_id`.
-- ============================================================================
-- Pragmas and Initialization
-- ============================================================================
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA temp_store = MEMORY;
PRAGMA cache_size = -32000; -- 32MB cache
-- ============================================================================
-- 1. Module Information
-- ============================================================================
-- 1.1 Module Metadata (exactly one row, info_id = 1)
CREATE TABLE module_info (
info_id INTEGER PRIMARY KEY CHECK (info_id = 1),
module_type TEXT NOT NULL DEFAULT 'tag_graph',
-- Identity (WS-4)
module_uuid TEXT, -- Stable identity across versions; the join key
abbreviation TEXT NOT NULL, -- "TagGraph" (display / legacy key)
full_name TEXT NOT NULL, -- "Biblical Entity Tag Graph"
-- Format + content versioning (WS-4)
format TEXT NOT NULL DEFAULT 'tag-graph-module', -- Container format name
format_version TEXT NOT NULL DEFAULT '2.0', -- Spec version this file conforms to
content_version TEXT, -- Module's own content revision (was `version`)
content_sha256 TEXT, -- Integrity / dedup hash
-- Descriptive
author TEXT,
year_published INTEGER,
description TEXT,
language_code TEXT DEFAULT 'en',
-- Licensing + provenance (WS-4)
copyright TEXT, -- Freeform, DISPLAY ONLY - never parsed
license_spdx TEXT, -- 'CC-BY-4.0', 'PD', 'Proprietary', ...
license_url TEXT, -- Full terms
source_url TEXT, -- Where this module came from
-- Canon + versification (WS-4) - explicit so a future format may differ
canon TEXT NOT NULL DEFAULT 'protestant-66',
versification TEXT NOT NULL DEFAULT 'kjv-english',
created_date TEXT DEFAULT CURRENT_TIMESTAMP,
metadata TEXT, -- JSON. Generation provenance + counters:
-- {"passes":["pass1_people",...],"personCount":n,...}
CHECK (module_type = 'tag_graph')
);
-- ============================================================================
-- 2. People
-- ============================================================================
-- v1 -> v2: person_roles -> person_role, people -> person, people_aliases ->
-- person_alias, people_roles -> person_role_map,
-- people_relationships -> person_relationship
-- 2.1 Role taxonomy (self-nesting: 'prophet' -> 'major_prophet')
CREATE TABLE person_role (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
parent_id TEXT REFERENCES person_role(id),
notes TEXT
);
-- 2.2 People
CREATE TABLE person (
id TEXT PRIMARY KEY, -- Slug: 'david', 'mary_mother_of_jesus'
name TEXT NOT NULL,
tribe TEXT,
nation TEXT,
notes TEXT,
metadata TEXT
);
CREATE INDEX idx_person_name ON person(name COLLATE NOCASE);
-- 2.3 Alternate names ("Saul of Tarsus" -> paul)
CREATE TABLE person_alias (
person_id TEXT NOT NULL REFERENCES person(id),
alias TEXT NOT NULL
);
CREATE INDEX idx_person_alias_person ON person_alias(person_id);
CREATE INDEX idx_person_alias_alias ON person_alias(alias COLLATE NOCASE);
-- 2.4 Role assignment
CREATE TABLE person_role_map (
person_id TEXT NOT NULL REFERENCES person(id),
role_id TEXT NOT NULL REFERENCES person_role(id),
PRIMARY KEY (person_id, role_id)
);
-- 2.5 Person-to-person relationships (father_of, wife_of, ...)
CREATE TABLE person_relationship (
id TEXT PRIMARY KEY,
person_1_id TEXT NOT NULL REFERENCES person(id),
person_2_id TEXT NOT NULL REFERENCES person(id),
relationship_type TEXT NOT NULL, -- Open set (WS-7), validated in TypeScript
relationship_type_reciprocal TEXT, -- Type as read from person 2 to person 1
confidence TEXT, -- certain|probable|disputed|derived|possible (open set)
notes TEXT
);
CREATE INDEX idx_person_relationship_p1 ON person_relationship(person_1_id);
CREATE INDEX idx_person_relationship_p2 ON person_relationship(person_2_id);
-- ============================================================================
-- 3. Places
-- ============================================================================
-- v1 -> v2: place_attributes -> place_attribute, places -> place,
-- place_aliases -> place_alias, place_attribute_map (unchanged)
-- 3.1 Attribute taxonomy (self-nesting: 'settlement' -> 'walled_city')
CREATE TABLE place_attribute (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
parent_id TEXT REFERENCES place_attribute(id),
notes TEXT
);
-- 3.2 Places (self-nesting: 'canaan' -> 'judea' -> 'bethlehem')
CREATE TABLE place (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
parent_id TEXT REFERENCES place(id), -- Containing place
modern_name TEXT,
notes TEXT,
metadata TEXT
);
CREATE INDEX idx_place_name ON place(name COLLATE NOCASE);
CREATE INDEX idx_place_parent ON place(parent_id);
-- 3.3 Alternate names
CREATE TABLE place_alias (
place_id TEXT NOT NULL REFERENCES place(id),
alias TEXT NOT NULL
);
CREATE INDEX idx_place_alias_place ON place_alias(place_id);
CREATE INDEX idx_place_alias_alias ON place_alias(alias COLLATE NOCASE);
-- 3.4 Attribute assignment
CREATE TABLE place_attribute_map (
place_id TEXT NOT NULL REFERENCES place(id),
attribute_id TEXT NOT NULL REFERENCES place_attribute(id),
PRIMARY KEY (place_id, attribute_id)
);
-- ============================================================================
-- 4. Objects
-- ============================================================================
-- v1 -> v2: object_attributes -> object_attribute, objects -> object,
-- object_aliases -> object_alias, object_attribute_map (unchanged)
-- 4.1 Attribute taxonomy
CREATE TABLE object_attribute (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
parent_id TEXT REFERENCES object_attribute(id),
notes TEXT
);
-- 4.2 Objects (self-nesting: 'tabernacle_furnishing' -> 'ark_of_the_covenant')
CREATE TABLE object (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
parent_id TEXT REFERENCES object(id),
significance TEXT, -- mundane|symbolic|sacred|miraculous (open set, WS-7)
notes TEXT,
metadata TEXT
);
CREATE INDEX idx_object_name ON object(name COLLATE NOCASE);
CREATE INDEX idx_object_parent ON object(parent_id);
-- 4.3 Alternate names
CREATE TABLE object_alias (
object_id TEXT NOT NULL REFERENCES object(id),
alias TEXT NOT NULL
);
CREATE INDEX idx_object_alias_object ON object_alias(object_id);
CREATE INDEX idx_object_alias_alias ON object_alias(alias COLLATE NOCASE);
-- 4.4 Attribute assignment
CREATE TABLE object_attribute_map (
object_id TEXT NOT NULL REFERENCES object(id),
attribute_id TEXT NOT NULL REFERENCES object_attribute(id),
PRIMARY KEY (object_id, attribute_id)
);
-- ============================================================================
-- 5. Themes
-- ============================================================================
-- v1 -> v2: theme_attributes -> theme_attribute, themes -> theme,
-- theme_aliases -> theme_alias, theme_attribute_map (unchanged),
-- theme_traditions -> theme_tradition
-- 5.1 Attribute taxonomy
CREATE TABLE theme_attribute (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
parent_id TEXT REFERENCES theme_attribute(id),
notes TEXT
);
-- 5.2 Themes (self-nesting: 'covenant' -> 'new_covenant')
CREATE TABLE theme (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
parent_id TEXT REFERENCES theme(id),
notes TEXT,
metadata TEXT
);
CREATE INDEX idx_theme_name ON theme(name COLLATE NOCASE);
CREATE INDEX idx_theme_parent ON theme(parent_id);
-- 5.3 Alternate names
CREATE TABLE theme_alias (
theme_id TEXT NOT NULL REFERENCES theme(id),
alias TEXT NOT NULL
);
CREATE INDEX idx_theme_alias_theme ON theme_alias(theme_id);
CREATE INDEX idx_theme_alias_alias ON theme_alias(alias COLLATE NOCASE);
-- 5.4 Attribute assignment
CREATE TABLE theme_attribute_map (
theme_id TEXT NOT NULL REFERENCES theme(id),
attribute_id TEXT NOT NULL REFERENCES theme_attribute(id),
PRIMARY KEY (theme_id, attribute_id)
);
-- 5.5 Traditions that emphasize a theme (open set: 'reformed', 'catholic', ...)
CREATE TABLE theme_tradition (
theme_id TEXT NOT NULL REFERENCES theme(id),
tradition TEXT NOT NULL,
PRIMARY KEY (theme_id, tradition)
);
-- ============================================================================
-- 6. Cross-Entity Associations
-- ============================================================================
-- v1 -> v2: association_types -> association_type,
-- association_type_category_pairs -> association_type_category_pair,
-- tag_associations -> tag_association
-- 6.1 Association vocabulary (born_in, dwelt_in, authored, ...)
CREATE TABLE association_type (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
reciprocal_name TEXT, -- Label when read in the other direction
reciprocal_id TEXT REFERENCES association_type(id),
notes TEXT
);
-- 6.2 Which category pairs a type is valid for ('people' x 'places', ...)
CREATE TABLE association_type_category_pair (
association_type_id TEXT NOT NULL REFERENCES association_type(id),
entity_1_category TEXT NOT NULL,
entity_2_category TEXT NOT NULL,
PRIMARY KEY (association_type_id, entity_1_category, entity_2_category)
);
-- 6.3 The association edges themselves.
-- Polymorphic: (entity_N_category, entity_N_id) points at person/place/object/theme.
-- No FK is possible on a polymorphic reference; integrity is checked by
-- scripts/validate-module.js.
CREATE TABLE tag_association (
id TEXT PRIMARY KEY,
entity_1_id TEXT NOT NULL,
entity_1_category TEXT NOT NULL, -- people|places|objects|themes (open set, WS-7)
entity_2_id TEXT NOT NULL,
entity_2_category TEXT NOT NULL, -- people|places|objects|themes (open set, WS-7)
association_type_id TEXT REFERENCES association_type(id),
strength REAL CHECK (strength BETWEEN 0.0 AND 1.0), -- Closed numeric domain: CHECK kept
confidence TEXT, -- certain|probable|disputed|derived|possible (open set)
source TEXT, -- 'seeded', 'cooccurrence', ...
notes TEXT,
metadata TEXT
);
CREATE INDEX idx_tag_association_e1 ON tag_association(entity_1_id, entity_1_category);
CREATE INDEX idx_tag_association_e2 ON tag_association(entity_2_id, entity_2_category);
CREATE INDEX idx_tag_association_type ON tag_association(association_type_id);
-- ============================================================================
-- 7. Topical Index Integration
-- ============================================================================
-- v1 -> v2: entity_topic_links -> entity_topic_link, entity_facets -> entity_facet,
-- entity_facet_members -> entity_facet_member
--
-- `source_module` + `topic_id` are a SOFT cross-database reference into a topical
-- index module (topical_*.db, table `topic`). There is no FK across databases;
-- `source_module` holds the topical module's abbreviation.
-- 7.1 Entity -> topic mapping produced by name/alias matching
CREATE TABLE entity_topic_link (
entity_id TEXT NOT NULL,
entity_category TEXT NOT NULL, -- people|places|objects|themes (open set, WS-7)
source_module TEXT NOT NULL, -- Topical module identifier ('topical_nave')
topic_id INTEGER NOT NULL, -- topic.topic_id in that module
match_type TEXT NOT NULL, -- exact|alias|stem (open set, WS-7)
PRIMARY KEY (entity_id, source_module, topic_id)
);
CREATE INDEX idx_entity_topic_link_entity ON entity_topic_link(entity_id, entity_category);
CREATE INDEX idx_entity_topic_link_topic ON entity_topic_link(source_module, topic_id);
-- 7.2 Structural subtopic groups hanging off an entity
-- ("Kings of Judah" as a facet of the entity 'judah')
CREATE TABLE entity_facet (
facet_id INTEGER PRIMARY KEY, -- Assigned by the builder, not autoincrement
parent_entity_id TEXT NOT NULL,
parent_entity_category TEXT NOT NULL,
facet_label TEXT NOT NULL, -- Normalized label
facet_display_label TEXT NOT NULL, -- Label as shown in the UI
source_module TEXT NOT NULL, -- Topical module abbreviation
source_topic_id INTEGER NOT NULL, -- topic.topic_id in that module
metadata TEXT
);
CREATE INDEX idx_entity_facet_parent ON entity_facet(parent_entity_id, parent_entity_category);
-- 7.3 Entities that belong to a facet
CREATE TABLE entity_facet_member (
facet_id INTEGER NOT NULL REFERENCES entity_facet(facet_id),
member_entity_id TEXT NOT NULL,
member_entity_category TEXT NOT NULL,
source_topic_id INTEGER,
sort_order INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (facet_id, member_entity_id)
);
CREATE INDEX idx_entity_facet_member_facet ON entity_facet_member(facet_id);
-- ============================================================================
-- 8. Verse Linking (WS-3)
-- ============================================================================
-- 8.1 Contract table.
-- Byte-identical in every module schema and in the user database. It is the table a
-- generic consumer or the format validator looks for. In a tag graph it carries links
-- whose source row has an INTEGER key — currently `entity_facet` (source_type=
-- 'entity_facet', source_id=facet_id); the graph's own entity and association links
-- are textual and live in `entity_verse_link` below.
CREATE TABLE verse_link (
link_id INTEGER PRIMARY KEY AUTOINCREMENT,
source_type TEXT NOT NULL, -- 'commentary_entry','book_section','dictionary_entry',
-- 'devotional_entry','topic','note','journal','prayer'
source_id INTEGER NOT NULL,
verse_id_start INTEGER NOT NULL,
verse_id_end INTEGER NOT NULL, -- inclusive; single verse is expressed as end = start
link_type TEXT NOT NULL DEFAULT 'reference', -- reference|annotation|primary_passage|cross_reference
sort_order INTEGER NOT NULL DEFAULT 0,
context TEXT,
metadata TEXT
);
CREATE INDEX idx_verse_link_source ON verse_link(source_type, source_id, sort_order);
CREATE INDEX idx_verse_link_start ON verse_link(verse_id_start);
CREATE INDEX idx_verse_link_range ON verse_link(verse_id_start, verse_id_end);
-- R-2: the reverse pair, so a containment probe (start <= X AND end >= X) can be
-- driven from either side. Cheap, and only useful once end is NOT NULL.
CREATE INDEX idx_verse_link_covering ON verse_link(verse_id_end, verse_id_start);
-- 8.2 Entity verse links (textual source identity).
-- Identical in shape and conventions to `verse_link` except that `source_id` is TEXT,
-- because tag-graph entities and associations are keyed by slug. This ONE table
-- replaces BOTH v1 verse-linking shapes:
--
-- entity_verses.entity_id -> entity_verse_link.source_id
-- entity_verses.entity_category -> entity_verse_link.source_type
-- ('people'|'places'|'objects'|'themes')
-- entity_verses.start_verse_id -> entity_verse_link.verse_id_start
-- entity_verses.end_verse_id -> entity_verse_link.verse_id_end
-- (v1 wrote 0 for "single verse" on text-search
-- rows; v2 requires NULL)
-- entity_verses.source -> entity_verse_link.provenance ('topical','text_search')
--
-- association_verses.association_id -> entity_verse_link.source_id
-- (source_type='tag_association')
-- association_verses.start_verse_id -> entity_verse_link.verse_id_start
-- association_verses.end_verse_id -> entity_verse_link.verse_id_end
-- association_verses.source_module -> entity_verse_link.provenance
-- association_verses.sort_order -> entity_verse_link.sort_order
CREATE TABLE entity_verse_link (
link_id INTEGER PRIMARY KEY AUTOINCREMENT,
source_type TEXT NOT NULL, -- 'people','places','objects','themes','tag_association'
source_id TEXT NOT NULL, -- Entity slug or tag_association.id
verse_id_start INTEGER NOT NULL,
verse_id_end INTEGER NOT NULL, -- inclusive; single verse is expressed as end = start
link_type TEXT NOT NULL DEFAULT 'reference',
sort_order INTEGER NOT NULL DEFAULT 0,
provenance TEXT, -- What produced this link: 'topical', 'text_search',
-- or a module identifier such as 'topical_nave'
context TEXT,
metadata TEXT
);
CREATE INDEX idx_entity_verse_link_source ON entity_verse_link(source_type, source_id, sort_order);
CREATE INDEX idx_entity_verse_link_start ON entity_verse_link(verse_id_start);
CREATE INDEX idx_entity_verse_link_range ON entity_verse_link(verse_id_start, verse_id_end);
-- Preserves the deduplication that the v1 composite PRIMARY KEYs provided
-- (entity_verses: entity_id+entity_category+start_verse_id+source;
-- association_verses: association_id+start_verse_id). COALESCE keeps rows with a
-- NULL provenance from being treated as distinct.
CREATE UNIQUE INDEX idx_entity_verse_link_unique
ON entity_verse_link(source_type, source_id, verse_id_start, COALESCE(provenance, ''));
-- WS-7: no CHECK on source_type or link_type in either table. Both are
-- open/extensible sets; validation lives in TypeScript
-- (packages/core/src/Data/Core/Types.ts) and is enforced at the repository boundary.
-- ============================================================================
-- 9. Schema Version
-- ============================================================================
CREATE TABLE schema_version (
version_id INTEGER PRIMARY KEY AUTOINCREMENT,
version_number TEXT NOT NULL,
applied_date TEXT DEFAULT CURRENT_TIMESTAMP,
notes TEXT,
metadata TEXT
);
INSERT INTO schema_version (version_number, notes)
VALUES ('2.0.0', 'Module Format v2: first schema file for the tag graph module type (was embedded in scripts/build-tag-graph.js and scripts/enrich-tag-graph-verses.js). Singular table names; module_info added with WS-4 identity/provenance; association_verses and entity_verses merged into entity_verse_link using the canonical verse_id_start/verse_id_end convention, alongside the contract verse_link table; CHECK dropped from the open enums (entity_category, confidence, match_type, significance).');