-- VerseLive Database Schema -- MySQL 8+ -- Charset chosen for full Unicode (accented translations, future languages) SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- --------------------------------------------------------------- -- CHURCHES (tenant table) -- Present now so every other table can carry a nullable church_id -- from day one. Phase 1 runs everything under church_id = 1 -- (a "Default Church"). The Super Admin phase later activates -- real multi-tenancy without any schema migration. -- --------------------------------------------------------------- CREATE TABLE churches ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, slug VARCHAR(150) NOT NULL UNIQUE, status ENUM('active','suspended') NOT NULL DEFAULT 'active', plan ENUM('free','basic','standard','premium','enterprise') NOT NULL DEFAULT 'free', max_users INT UNSIGNED NOT NULL DEFAULT 5, max_admins INT UNSIGNED NOT NULL DEFAULT 1, storage_limit_mb INT UNSIGNED NOT NULL DEFAULT 100, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------- -- BOOKS -- --------------------------------------------------------------- CREATE TABLE books ( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, abbreviation VARCHAR(10) NOT NULL, testament ENUM('OT','NT') NOT NULL, order_no SMALLINT UNSIGNED NOT NULL, UNIQUE KEY uq_books_order (order_no), KEY idx_books_abbreviation (abbreviation) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------- -- TRANSLATIONS -- --------------------------------------------------------------- CREATE TABLE translations ( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, abbreviation VARCHAR(10) NOT NULL UNIQUE, language VARCHAR(50) NOT NULL DEFAULT 'English', is_public_domain TINYINT(1) NOT NULL DEFAULT 1, is_active TINYINT(1) NOT NULL DEFAULT 1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------- -- VERSES -- verse_text_normalized: lowercase, punctuation-stripped copy used -- for fast LIKE-based phrase search and fuzzy fallback matching. -- FULLTEXT index powers relevance-ranked natural language search. -- --------------------------------------------------------------- CREATE TABLE verses ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, book_id SMALLINT UNSIGNED NOT NULL, chapter SMALLINT UNSIGNED NOT NULL, verse SMALLINT UNSIGNED NOT NULL, translation_id SMALLINT UNSIGNED NOT NULL, verse_text TEXT NOT NULL, verse_text_normalized TEXT NOT NULL, popularity INT UNSIGNED NOT NULL DEFAULT 0, CONSTRAINT fk_verses_book FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE, CONSTRAINT fk_verses_translation FOREIGN KEY (translation_id) REFERENCES translations(id) ON DELETE CASCADE, UNIQUE KEY uq_verse_ref (book_id, chapter, verse, translation_id), KEY idx_verse_lookup (book_id, chapter, verse), FULLTEXT KEY ft_verse_text (verse_text) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------- -- VERSE WORDS (dictionary used for fuzzy / misspelling correction) -- Built once from the corpus; lets us SOUNDEX-match a mistyped -- query word against real Bible vocabulary quickly. -- --------------------------------------------------------------- CREATE TABLE verse_words ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, word VARCHAR(50) NOT NULL, soundex_code VARCHAR(10) NOT NULL, frequency INT UNSIGNED NOT NULL DEFAULT 1, UNIQUE KEY uq_word (word), KEY idx_soundex (soundex_code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------- -- SEARCH HISTORY -- --------------------------------------------------------------- CREATE TABLE search_history ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, church_id INT UNSIGNED NULL, keyword VARCHAR(255) NOT NULL, detected_reference VARCHAR(100) NULL, ip_address VARCHAR(45) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_search_created (created_at), KEY idx_search_church (church_id), CONSTRAINT fk_search_church FOREIGN KEY (church_id) REFERENCES churches(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------- -- USERS -- --------------------------------------------------------------- CREATE TABLE users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, church_id INT UNSIGNED NULL, fullname VARCHAR(150) NOT NULL, email VARCHAR(150) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, role ENUM('super_admin','church_admin','operator','viewer') NOT NULL DEFAULT 'viewer', status ENUM('active','suspended') NOT NULL DEFAULT 'active', last_login_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_users_church (church_id), CONSTRAINT fk_users_church FOREIGN KEY (church_id) REFERENCES churches(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------- -- SETTINGS (key/value, scoped per church; church_id NULL = global) -- --------------------------------------------------------------- CREATE TABLE settings ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, church_id INT UNSIGNED NULL, setting_key VARCHAR(100) NOT NULL, setting_value TEXT NULL, UNIQUE KEY uq_setting (church_id, setting_key), CONSTRAINT fk_settings_church FOREIGN KEY (church_id) REFERENCES churches(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------- -- FAVORITES / SERMON HISTORY (foundations for later phases) -- --------------------------------------------------------------- CREATE TABLE favorites ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, verse_id INT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_favorite (user_id, verse_id), CONSTRAINT fk_fav_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_fav_verse FOREIGN KEY (verse_id) REFERENCES verses(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE sermon_sessions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, church_id INT UNSIGNED NULL, title VARCHAR(150) NULL, started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, ended_at DATETIME NULL, CONSTRAINT fk_session_church FOREIGN KEY (church_id) REFERENCES churches(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE sermon_verses ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, session_id INT UNSIGNED NOT NULL, verse_id INT UNSIGNED NOT NULL, detected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_sv_session FOREIGN KEY (session_id) REFERENCES sermon_sessions(id) ON DELETE CASCADE, CONSTRAINT fk_sv_verse FOREIGN KEY (verse_id) REFERENCES verses(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------- -- AUDIT LOGS (foundation for Super Admin phase) -- --------------------------------------------------------------- CREATE TABLE audit_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NULL, church_id INT UNSIGNED NULL, action VARCHAR(100) NOT NULL, details TEXT NULL, ip_address VARCHAR(45) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_audit_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET FOREIGN_KEY_CHECKS = 1;