Database Schema — IllustrateTheWord & SermonWise

Knowledge > Architecture > Database Schema > ITW & SermonWise Tables

---

Database Schema — IllustrateTheWord & SermonWise

All tables owned or primarily used by sermon-illustrations/ (IllustrateTheWord) and the SermonWise feature in churchwiseai-web/src/app/sermons/. All tables live in the shared Supabase instance (wrwkszmobuhvcfjipasi), public schema.

Content Retrieval Pipeline

CRITICAL Safety Rules

• unified_rag_content has ~326K rows — NEVER bulk delete. To hide content, set is_active = false or curation_status = 'rejected'. Never use DELETE or TRUNCATE.
• sai_scripture has ~31K verses — read-only reference data. Never modify.
• scripture_word_analysis has ~439K rows — word-level morphological data. Read-only in production.
• All writes go to the ONE shared production Supabase instance. There is no staging database.

---

Row Count Summary

Table	Est. Rows	Notes
unified_rag_content	~326,000	CRITICAL — never bulk delete
scripture_word_analysis	~439,000	Read-only morphological data
sai_scripture_access_log	~244,000	Usage telemetry
sai_generated_content	~23,700	AI-generated sermon content
theological_rag_documents	~5,500	Theological commentary
word_study_lexicon	~14,300	Hebrew/Greek lexicon
sai_strongs_concordance	~14,200	Strong's numbers
pericope_lens_perspectives	~18,600	Lens-specific sermon angles
sai_scripture	~31,000	Bible verses (multi-translation)
bible_passages	~6,984	Canonical passage registry
bible_passages_canonical	~3,727	Deduplicated canonical passages
bible_passages_id_mapping	~3,492	Old-to-new passage ID map
theological_lens_positions	~578	Per-lens doctrinal positions
content_passage_links	~9,908	Content-to-passage associations
content_chunks	~1,456	Legacy chunked content
sai_lectionary_calendar	~208	Liturgical calendar entries
sai_lectionary_readings	~712	Lectionary passage assignments
narrative_units	~167	Narrative groupings in scripture
pericope_thematic_links	~107	Cross-pericope thematic links
theme_pericope_connections	~73	Theme-to-pericope mapping
lens_affinity	~68	Lens compatibility scores
bible_book_lens_perspectives	~133	Per-book lens emphases
sai_bible_book_overviews	~132	Book-level overviews
bible_book_metadata	~66	Extended book metadata
theological_contradictions	~124	Per-lens doctrinal guardrails
sai_people_in_story	~237	Biblical characters
sai_sermon_methods	~4	Sermon structure methods
method_lens_guidance	~187	Method × lens adaptations
sai_theological_lenses	~18	The 18 theological lenses
profiles	~6	User profiles (ITW/SermonWise)
user_subscriptions	~1	Stripe subscription records
user_theological_preferences	~11	Per-user lens preferences
illustrations	~93	Legacy illustration table
unified_rag_content_staging	~3,312	Staging before production promotion

---

1. Content Core

unified_rag_content

The primary content store for all ITW illustrations, sermon starters, commentaries, and AI-generated content. ~326K rows. Queried by the ITW frontend, SermonWise RAG pipeline, and chatbot.

Column	Type	Nullable	Notes
id	uuid	NOT NULL	PK, gen_random_uuid()
legacy_table	text	YES	Source table before migration
legacy_id	text	YES	Source row ID before migration
content_category	text	NOT NULL	e.g. illustration, commentary, sermon_starter
content_type	text	NOT NULL	Sub-type within category
content_format	text	YES	Default 'full_text'
title	text	YES	Display title
content	text	NOT NULL	The full content body
summary	text	YES	Short summary for search results
word_count	integer	YES
canonical_pericope_id	integer	YES	FK → bible_passages_canonical.id
pericope	text	YES	Human-readable pericope label
scripture_references	text[]	YES	Array of reference strings
scripture_start	jsonb	YES	{book, chapter, verse}
scripture_end	jsonb	YES	{book, chapter, verse}
scripture_hierarchy	jsonb	YES	Nested book/chapter/verse metadata
theological_lens_id	integer	YES	FK → sai_theological_lenses.lens_id
lens_tags	int[]	YES	Additional lens IDs
lens_variations	jsonb	YES	Per-lens content variants
denominational_focus	text	YES
themes	text[]	YES
topics	text[]	YES
keywords	text[]	YES
emotions	text[]	YES
human_needs	text[]	YES
doctrines	text[]	YES
spiritual_disciplines	text[]	YES
application_points	text[]	YES
source_type	text	YES	original, public_domain, fair_dealing, etc.
source_attribution	text	YES	Display attribution string
primary_source	text	YES	Publication or work title
primary_author	text	YES
copyright_status	text	YES
fair_dealing_compliant	boolean	YES	Default true
quality_score	numeric	YES	0–1 composite score
appropriateness_rating	integer	YES	Default 5 (1–10 scale)
canadian_compliant	boolean	YES	Default true
curation_status	text	YES	pending, approved, rejected — Default 'pending'
auto_approved	boolean	YES	Default false
moderation_notes	text	YES
audience_primary	text	YES
audience_suitability	text[]	YES
app_compatibility	text[]	YES	Which apps can surface this content
usage_types	text[]	YES
sermon_sections	text[]	YES
embedding	vector	YES	pgvector embedding
embedding_model	text	YES	Default 'text-embedding-3-small'
ai_model_used	text	YES
generation_prompt	text	YES
generation_cost	numeric	YES
tokens_used	integer	YES
parent_content_id	uuid	YES	FK → self (versioning)
related_content_ids	uuid[]	YES
organization_id	uuid	YES
created_by	uuid	YES	FK → profiles.id
approved_by	uuid	YES
import_batch_id	text	YES
import_source	text	YES
metadata	jsonb	YES	Default {}
search_vector	tsvector	YES	Full-text search index
view_count	integer	YES	Default 0
use_count	integer	YES	Default 0
last_used_at	timestamptz	YES
created_at	timestamptz	YES	Default now()
updated_at	timestamptz	YES	Default now()
is_universal	boolean	YES	Default false
creative_approach	varchar	YES
pericope_unlinkable	boolean	YES	Default false
contributor_user_id	uuid	YES
rag_contribution_id	uuid	YES
is_active	boolean	YES	Default true — use this to hide content, never DELETE
slug	text	YES	URL slug for public pages
teaser	text	YES	Short preview for paywalled content
visibility_tier	text	YES	free, premium, etc.
image_url	text	YES
tone	text	YES

Safety: Never bulk delete. To unpublish: UPDATE unified_rag_content SET is_active = false WHERE ...

---

unified_rag_content_staging

Mirrors the schema of unified_rag_content exactly (plus 3 migration columns). Used to stage content before promoting to production. ~3,312 rows.

Additional columns (not in production table):

• migration_status text — Default 'pending'
• migrated_at timestamptz
• migration_batch_id text

---

illustrations

Legacy illustration table — predates unified_rag_content. ~93 rows. New content goes to unified_rag_content. Kept for backward compatibility.

Column	Type	Notes
id	uuid PK
title	text NOT NULL
content	text NOT NULL
source_type	text
source_attribution	text
themes	text[]
appropriateness_rating	integer
audience_suitability	text[]
embedding	vector	pgvector
created_at	timestamptz
organization_id	uuid
theological_lens_id	integer
fair_dealing_purpose	text
copyright_status	text
usage_rights	text
educational_context	text
content_type	text
legal_review_status	text
six_factor_analysis	jsonb	Canadian fair dealing 6-factor test
canadian_compliant	boolean
primary_passage_id	uuid
lens_compatibility	text[]
method_compatibility	text[]
theological_emphasis	text[]
sermon_section_fit	text[]
content_warnings	text[]
cultural_sensitivity_notes	text
ai_generated	boolean	Default false
ai_model	varchar
generation_cost	numeric
generation_tokens_used	integer
account_id	uuid
app_suitability	jsonb	Default {}
usage_types	text[]	Default ['sermon_illustration']
user_id	uuid

---

sai_generated_content

AI-generated sermon content (starters, outlines, reflections). ~23,700 rows. Separate from unified_rag_content — these are AI outputs tied to a specific lens + scripture reference.

Column	Type	Notes
id	uuid PK	gen_random_uuid()
type	text NOT NULL	Content type (sermon_starter, outline, etc.)
title	text NOT NULL
content	text NOT NULL
summary	text
theological_lens_id	integer NOT NULL	FK → sai_theological_lenses.lens_id
scripture_reference	text	Human-readable ref
scripture_book	text
scripture_chapter	integer
scripture_verse_start	integer
scripture_verse_end	integer
generation_source	text NOT NULL	Default 'ai'
ai_model_used	text
prompt_template_used	text
generation_parameters	jsonb	Default {}
status	text NOT NULL	Default 'pending'
relevance_score	numeric
theological_accuracy_score	numeric
readability_score	numeric
usage_count	integer	Default 0
embedding_created	boolean	Default false
embedding_id	uuid
created_by	uuid
organization_id	uuid
reviewed_by	uuid
reviewed_at	timestamptz
review_notes	text
approved_by	uuid
approved_at	timestamptz
tags	text[]	Default {}
audience	text
difficulty_level	text
language_code	text	Default 'en'
metadata	jsonb	Default {}
version_number	integer	Default 1
parent_content_id	uuid
is_current_version	boolean	Default true
generated_at	timestamptz	Default now()
created_at	timestamptz	Default now()
updated_at	timestamptz	Default now()
embedding	vector	pgvector
embedding_updated_at	timestamptz

---

2. Scripture & Bible

sai_scripture

The full Bible text in multiple translations. ~31,000 rows (one per verse). Read-only reference data.

Column	Type	Notes
verse_id	bigint PK	Numeric verse ID
book_name	text NOT NULL	e.g. 'Genesis'
book_number	integer NOT NULL	1–66 (OT 1–39, NT 40–66)
chapter	integer NOT NULL
verse	integer NOT NULL
text_kjv	text
text_amp	text
text_net	text
text_nasb	text
text_web	text
text_original	text	Hebrew/Greek original
text_translit	text	Transliteration
original_language	text	'Hebrew' or 'Greek'
text_esv	text
text_niv	text
verse_numbering_metadata	jsonb	Cross-translation verse number notes
translation_sources	jsonb	Source tracking per translation
language_family	text
canonical_pericope_id	integer	FK → bible_passages_canonical.id
narrative_unit_id	integer	FK → narrative_units.id

---

scripture_word_analysis

Word-level morphological analysis for every verse. ~439,000 rows. One row per word per verse. Used by SermonWise word study feature.

Column	Type	Notes
id	uuid PK
verse_id	bigint NOT NULL	FK → sai_scripture.verse_id
word_position	integer NOT NULL	Word order within verse (1-indexed)
original_word	text NOT NULL	Hebrew/Greek original
transliteration	text
strong_number	text	e.g. H1234, G5678
morphology	text	Full morphology code
part_of_speech	text
stem	text	Verbal stem (Hebrew: Qal, Niphal, etc.)
person	text	1st/2nd/3rd
gender	text	masculine/feminine/neuter
number	text	singular/plural/dual
tense	text
voice	text	active/passive/middle
mood	text	indicative/subjunctive/imperative/etc.
case_type	text	nominative/genitive/dative/accusative/vocative
state	text	absolute/construct (Hebrew)
english_gloss	text	Short English gloss
contextual_translation	text	Contextual rendering
created_at	timestamptz	Default now()

---

word_study_lexicon

Hebrew/Greek lexicon with full definitions. ~14,300 entries. The primary lexicon used by SermonWise word study.

Column	Type	Notes
id	uuid PK
strong_number	text NOT NULL	e.g. H1, G1
original_word	text NOT NULL
transliteration	text NOT NULL
pronunciation	text
language	text NOT NULL	'Hebrew' or 'Greek'
part_of_speech	text
definition	jsonb NOT NULL	Structured definition with glosses and extended senses
etymology	text
usage_notes	text
frequency	integer	Default 0 — occurrence count in Bible
created_at	timestamptz	Default now()
updated_at	timestamptz	Default now()

---

sai_strongs_concordance

Strong's Concordance entries. ~14,200 rows. Parallel to word_study_lexicon — older/simpler structure, used for concordance lookup.

Column	Type	Notes
strongs_number	varchar PK	e.g. H1234
language	varchar NOT NULL	'Hebrew' or 'Greek'
lemma	text	Base lexical form
transliteration	text
pronunciation	text
definition	text	Plain text definition
kjv_definition	text	KJV-specific gloss
derivation	text	Etymological note
created_at	timestamptz	Default CURRENT_TIMESTAMP
updated_at	timestamptz	Default CURRENT_TIMESTAMP

---

bible_passages

All named Bible passages. ~6,984 rows. The working passage registry. May contain duplicates (see bible_passages_canonical).

Column	Type	Notes
id	integer PK
book_number	integer NOT NULL	1–66
book_name	varchar NOT NULL
passage_reference	varchar NOT NULL	e.g. John 3:16
full_text_reference	varchar NOT NULL	Verbose form

---

bible_passages_canonical

Deduplicated canonical passage registry. ~3,727 rows. The preferred table for passage lookups — duplicates from bible_passages have been resolved here.

Column	Type	Notes
id	integer PK
book_number	integer NOT NULL
book_name	varchar NOT NULL
passage_reference	varchar NOT NULL
full_text_reference	varchar NOT NULL
created_at	timestamp	Default now()
migrated_from_ids	int[]	Old bible_passages IDs this replaced
book_number_start	integer
chapter_start	integer
verse_start	integer
book_number_end	integer
chapter_end	integer
verse_end	integer
narrative_unit_id	integer	FK → narrative_units.id
pericope_themes	text[]

---

bible_passages_id_mapping

Migration map from old bible_passages IDs to canonical IDs. ~3,492 rows. Used to resolve foreign key references that used pre-deduplication IDs.

Column	Type	Notes
old_id	integer NOT NULL	Old bible_passages.id
new_canonical_id	integer NOT NULL	FK → bible_passages_canonical.id
book_number	integer NOT NULL
passage_reference	varchar NOT NULL
mapping_reason	varchar	Default 'deduplication'
created_at	timestamp

---

bible_book_metadata

Extended metadata for each Bible book. ~66 rows (one per book).

Column	Type	Notes
id	integer PK
book_number	integer NOT NULL
book_name	text NOT NULL
theme_statement	text
jesus_lens	text	Christological interpretation
historical_context	text
author	text
date_written	text
audience	text
key_themes	text[]
testament	text	'OT' or 'NT'
genre	text
created_at	timestamptz
updated_at	timestamptz
book_outline	jsonb	Chapter-by-chapter structure
key_passages	text[]
literary_features	text[]
popular_sermon_passages	text[]
sermon_series_ideas	jsonb
difficulty_level	integer
common_misconceptions	text[]
related_books	text[]
nt_quotes_ot	jsonb	NT quotations from this OT book
parallel_passages	text[]
thematic_connections	text[]
chapter_count	integer
verse_count	integer
reading_time_minutes	integer

---

sai_bible_book_overviews

Sermon-focused book overviews (simpler than bible_book_metadata). ~132 rows (2 rows per book — OT + NT sets).

Column	Type	Notes
book_number	integer NOT NULL
book_name	varchar NOT NULL
author	varchar
time_written	varchar
language_written	varchar	Hebrew/Greek/Aramaic
main_theme_god_active	text	God's primary action in this book
detailed_theme	text
short_summary	text
detailed_summary	text
jesus_lens	text	Christological connection
cultural_context	text
main_characters	text
genre	varchar
audience	varchar
canonical_division	varchar	Torah, Prophets, Writings, Gospels, etc.
key_verse	varchar

---

3. Theological Framework

sai_theological_lenses

The 18 theological lenses — the core taxonomy that shapes all content and user experience in ITW and SermonWise.

Column	Type	Notes
lens_id	integer PK
lens_name	varchar NOT NULL	e.g. 'Reformed', 'Wesleyan', 'Charismatic'
core_focus	text	One-sentence focus statement
key_question	text	The lens's central interpretive question
principle_authority	text	View of Scripture authority
principle_christology	text	Christological emphasis
principle_soteriology	text	View of salvation
principle_ecclesiology	text	View of church
principle_ethical_mission	text	Ethical and missional emphasis
is_visible	boolean NOT NULL	Default true — hides deprecated lenses

---

theological_rag_documents

Theological commentary and source documents for RAG. ~5,500 rows. Pre-embedding theological content used to enrich sermon preparation.

Column	Type	Notes
id	uuid PK
pericope	text	Associated pericope label
lens_tags	int[] NOT NULL	FK array → sai_theological_lenses.lens_id
content_type	text NOT NULL	Type of theological content
base_content	text NOT NULL	The main content body
lens_variations	jsonb	Per-lens content adaptations
quality_score_id	uuid	FK → quality scoring table
curation_status	text NOT NULL	Default 'pending'
primary_source	text NOT NULL
primary_author	text NOT NULL
license_type	text NOT NULL
attribution_text	text NOT NULL
source_urls	text[]
embedding	vector	pgvector
tokens	integer NOT NULL	Default 0
last_scraped	timestamptz
scrape_version	integer	Default 1
enhancement_level	text	Default 'raw'
ai_enhancement_notes	text
organization_id	uuid
created_by	uuid
reviewed_by	uuid
approved_by	uuid
created_at	timestamptz
updated_at	timestamptz
scripture_scope_type	text	'verse', 'pericope', 'chapter', 'book'
scripture_start	jsonb
scripture_end	jsonb
canonical_pericope_id	integer	FK → bible_passages_canonical.id
scripture_hierarchy	jsonb

---

theological_lens_positions

Per-lens doctrinal positions on contested questions. ~578 rows. Used by the chatbot and voice agent to ensure lens-consistent answers.

Column	Type	Notes
id	uuid PK
lens_id	integer NOT NULL	FK → sai_theological_lenses.lens_id
question_category	text NOT NULL	e.g. 'soteriology', 'eschatology'
lens_specific_answer	text NOT NULL	What this lens teaches on this question
confidence_score	numeric	Default 1.0
created_at	timestamptz
updated_at	timestamptz
contrary_positions	jsonb	Default {} — positions this lens rejects

---

theological_contradictions

Guardrails defining what each lens must NOT say. ~124 rows. Validation rules that prevent theologically inconsistent content generation.

Column	Type	Notes
id	uuid PK
lens_id	integer NOT NULL	FK → sai_theological_lenses.lens_id
doctrine_category	text NOT NULL
doctrine_subcategory	text
primary_position	text NOT NULL	The correct position for this lens
contrary_positions	text[] NOT NULL	Positions that contradict this lens
must_include_terms	text[]	Terms required in valid content
must_exclude_terms	text[]	Terms that invalidate content
severity	text NOT NULL	Default 'error' ('error' or 'warning')
validation_regex	text	Optional regex for automated checks
explanation	text	Human-readable rationale
biblical_references	text[]
historical_context	text
created_at	timestamptz
updated_at	timestamptz
created_by	uuid

---

theological_documents

Full theological source documents (creeds, confessions, academic papers). Rarely queried directly — chunked into theological_document_chunks for RAG.

Column	Type	Notes
id	uuid PK
title	text NOT NULL
author	text
category	text NOT NULL
subcategory	text
denomination	text
year_written	integer
content	text NOT NULL	Full document text
content_hash	text	Deduplication hash
embedding	vector	Document-level embedding
theological_tradition	text
primary_topics	text[]
scripture_references	text[]
theological_weight	numeric	Default 1.0
usage_count	integer	Default 0
last_accessed	timestamp
source_url	text
copyright_info	text
is_public_domain	boolean	Default false
language	text	Default 'en'
metadata	jsonb	Default {}
created_at	timestamp
updated_at	timestamp
created_by	uuid
search_text	tsvector	Full-text index

---

theological_document_chunks

Chunked segments of theological_documents for RAG retrieval. Schema is present; row count shows empty (not yet populated in production).

Column	Type	Notes
id	uuid PK
document_id	uuid	FK → theological_documents.id
chunk_index	integer NOT NULL	Chunk order within document
chunk_text	text NOT NULL
chunk_embedding	vector	pgvector
section_title	text
subsection_title	text
paragraph_number	integer
word_count	integer
start_position	integer	Character offset
end_position	integer
created_at	timestamp

---

method_lens_guidance

How each sermon method should be adapted per theological lens. ~187 rows (method × lens pairs).

Column	Type	Notes
id	integer PK
method_id	integer NOT NULL	FK → sai_sermon_methods.method_id
lens_id	integer NOT NULL	FK → sai_theological_lenses.lens_id
adaptation_summary	text NOT NULL	Brief adaptation description
section_adaptations	jsonb NOT NULL	Per-section guidance, Default {}
adjusted_length_minutes	integer
adjusted_word_counts	jsonb	Per-section word count targets
theological_emphasis	text NOT NULL	What to emphasize for this lens
authority_sources	text[] NOT NULL	Recommended sources for this lens
vocabulary_overrides	jsonb	Lens-specific terminology substitutions
tone_guidance	text NOT NULL
fit_score	numeric NOT NULL	Default 3.0 (1–5 scale)
fit_notes	text
is_recommended	boolean	Default false
created_at	timestamptz

---

lens_affinity

Compatibility scores between pairs of lenses. ~68 rows. Used to suggest complementary secondary lenses.

Column	Type	Notes
id	integer PK
source_lens_id	integer NOT NULL	FK → sai_theological_lenses.lens_id
target_lens_id	integer NOT NULL	FK → sai_theological_lenses.lens_id
affinity_score	numeric NOT NULL	0–1 compatibility score
affinity_category	text	'complementary', 'contrasting', etc.
notes	text
created_at	timestamptz

---

4. Sermon Structure

pericope_lens_perspectives

The core sermon preparation data. ~18,600 rows. One row per (pericope × lens) pair — provides theological interpretation, preaching angles, and sermon starters for every passage through every lens.

Column	Type	Notes
id	integer PK
canonical_pericope_id	integer NOT NULL	FK → bible_passages_canonical.id
lens_id	integer NOT NULL	FK → sai_theological_lenses.lens_id
theological_emphasis	text NOT NULL	Core theological angle for this lens
interpretive_notes	text	Exegetical observations
doctrinal_connections	text[]
application_themes	text[]
preaching_angles	text[]	Distinct angle options for a sermon
sermon_starters	jsonb	Pre-written opening hooks
key_commentators	text[]	Recommended commentators
textual_points	text[]	Key textual observations
show_dont_tell	jsonb	Narrative illustration prompts
imagery_elements	text[]	Visual/sensory elements
cautions	text[]	Theological pitfalls to avoid
liturgical_connections	text	Connection to liturgical season/calendar
source_attribution	text
quality_score	numeric	Default 0.85
review_status	text	Default 'pending'
reviewed_by	text
reviewed_at	timestamptz
created_at	timestamptz
updated_at	timestamptz

---

pericope_thematic_links

Cross-pericope connections by theme or type. ~107 rows.

Column	Type	Notes
id	integer PK
source_pericope_id	integer NOT NULL	FK → bible_passages_canonical.id
target_pericope_id	integer NOT NULL	FK → bible_passages_canonical.id
link_type	text NOT NULL	'typological', 'thematic', 'narrative', etc.
shared_themes	text[]
link_strength	integer	1–5
source_attribution	text
created_at	timestamptz
updated_at	timestamptz

---

narrative_units

Narrative groupings spanning multiple pericopes. ~167 rows. Groups passages into larger narrative arcs (e.g., "The Passion Narrative", "The Exodus").

Column	Type	Notes
id	integer PK
name	text NOT NULL	e.g. 'The Sermon on the Mount'
reference	text NOT NULL	Human-readable range
description	text
book_number_start	integer NOT NULL
chapter_start	integer NOT NULL
verse_start	integer NOT NULL
book_number_end	integer NOT NULL
chapter_end	integer NOT NULL
verse_end	integer NOT NULL
unit_type	text	'narrative', 'discourse', 'poem', etc.
testament	text
parent_unit_id	integer	FK → self (nested units)
themes	text[]
created_at	timestamptz
updated_at	timestamptz

---

bible_book_lens_perspectives

Book-level lens emphasis (macro view). ~133 rows (books × lenses). Complements pericope_lens_perspectives for book-level preaching plans.

Column	Type	Notes
id	integer PK
book_number	integer NOT NULL	1–66
lens_id	integer NOT NULL	FK → sai_theological_lenses.lens_id
lens_emphasis	text NOT NULL	How this lens reads this whole book
key_passages	text[]
key_commentators	text[]
interpretation_notes	text
common_sermon_themes	text[]
hermeneutical_approach	text
doctrinal_connections	text[]
source_attribution	text
quality_score	numeric	Default 0.85
created_at	timestamptz
updated_at	timestamptz

---

theme_pericope_connections

Links themes to pericopes. ~73 rows. Maps curriculum-level thematic entries to canonical passages.

Column	Type	Notes
connection_id	uuid PK
theme_id	uuid NOT NULL
canonical_pericope_id	integer NOT NULL	FK → bible_passages_canonical.id
connection_strength	varchar	Default 'secondary' ('primary', 'secondary')
teaching_notes	text
curriculum_level	integer	Default 101
lens_specific_notes	jsonb	Default {}
display_order	integer
is_exemplar	boolean	Default false
created_at	timestamptz
updated_at	timestamptz
created_by	uuid

---

content_passage_links

Association table linking content chunks to Bible passages. ~9,908 rows. Bridges content_chunks (and unified_content_id) to bible_passages.

Column	Type	Notes
id	uuid PK
content_chunk_id	uuid	FK → content_chunks.id
passage_id	integer	FK → bible_passages.id
relevance_score	float	Default 0.8
link_type	text	Default 'thematic'
created_at	timestamptz
user_id	uuid
unified_content_id	uuid	FK → unified_rag_content.id

---

content_chunks

Legacy chunked content table — predates unified_rag_content. ~1,456 rows. New content goes to unified_rag_content.

Column	Type	Notes
id	uuid PK
content	text NOT NULL
embedding	vector	pgvector
metadata	jsonb
source_type	text
denomination	text
scripture_references	text[]
themes	text[]
created_at	timestamptz
organization_id	uuid
theological_lens_id	integer
account_id	uuid
app_compatibility	text[]	Default ['sermon-companion']
content_type	varchar	Default 'sermon_starter'
usage_contexts	text[]
user_id	uuid
commentary_type	text
author	text
work_title	text
publication_year	integer
scripture_book	text
scripture_chapter	integer
scripture_verse_start	integer
scripture_verse_end	integer

---

sai_passage_lens_data

Structured sermon outline data per (passage × lens × method). Row count: ~0 in production estimates but schema exists. May be populated by scripts.

Column	Type	Notes
passage_lens_data_id	integer PK
passage_id	integer NOT NULL	FK → bible_passages.id
lens_id	integer NOT NULL	FK → sai_theological_lenses.lens_id
method_id	integer NOT NULL	FK → sai_sermon_methods.method_id
sermon_title	varchar
outline_data_json	jsonb	Full sermon outline
show_dont_tell_prompt	text	Narrative illustration prompt

---

sai_sermon_methods

Sermon structure methods (e.g., expository, topical, narrative). ~4 rows.

Column	Type	Notes
method_id	integer PK
method_name	varchar NOT NULL
method_description	text
structure_template	jsonb	Outline template
ai_generation_prompt	text	LLM prompt for this method
section_definitions	jsonb	What each section does
time_allocation	jsonb	Suggested time per section
best_for_passages	text[]	Passage types this method suits best
prompt_variables	jsonb	Template variable definitions
example_sermons	jsonb
method_strengths	text[]
ideal_length_minutes	integer	Default 20
requires_greek_hebrew	boolean	Default false
complexity_level	text	Default 'intermediate'
usage_count	integer	Default 0
avg_user_rating	numeric
last_updated	timestamp
is_custom	boolean	Default false
created_by	uuid

---

sai_people_in_story

Biblical characters and persons referenced in sermon content. ~237 rows.

Column	Type	Notes
id	integer PK
person_name	text NOT NULL
person_type	text NOT NULL	'biblical_figure', 'historical', etc.
description	text
biblical_role	text
key_attributes	text[]
testament	text	'OT', 'NT', 'both'
created_at	timestamptz
updated_at	timestamptz

---

5. Lectionary

The lectionary system covers the Revised Common Lectionary (RCL) and tracks the 3-year cycle of liturgical readings.

sai_lectionary_years

The three lectionary years. 3 rows.

Column	Type	Notes
id	integer PK
year_name	varchar NOT NULL	'Year A', 'Year B', 'Year C'

---

sai_lectionary_sundays

All Sundays and special days within each lectionary year.

Column	Type	Notes
id	integer PK
year_id	integer	FK → sai_lectionary_years.id
sort_order	integer	Display/sort order within year
season	varchar	'Advent', 'Christmas', 'Epiphany', 'Lent', 'Easter', 'Ordinary'
sunday_name	varchar	e.g. 'Second Sunday of Advent'

---

sai_lectionary_calendar

Calendar date to lectionary Sunday mapping. ~208 rows. Ties calendar dates to lectionary positions.

Column	Type	Notes
id	integer PK
calendar_date	date NOT NULL
liturgical_year	char NOT NULL	'A', 'B', or 'C'
liturgical_season	varchar NOT NULL
sunday_name	varchar NOT NULL
week_number	integer	Week within season
is_special_day	boolean	Default false (feast days, holy days)
notes	text
created_at	timestamp	Default now()
lectionary_type	text NOT NULL	Default 'rcl'

---

sai_lectionary_readings

The four assigned readings per lectionary Sunday. ~712 rows (typically 4 per calendar entry: OT, Psalm, Epistle, Gospel).

Column	Type	Notes
id	integer PK
calendar_id	integer NOT NULL	FK → sai_lectionary_calendar.id
reading_type	varchar NOT NULL	'OT', 'Psalm', 'Epistle', 'Gospel'
passage_reference	varchar NOT NULL	Human-readable ref (e.g. Romans 8:1-11)
liturgical_year	varchar NOT NULL	'A', 'B', 'C'
passage_text	text	Cached passage text
created_at	timestamp	Default now()
lectionary_type	text NOT NULL	Default 'rcl'

---

6. Lookup Tables (15 tables)

Fifteen sai_lookup_* tables provide controlled vocabulary for tagging and search filtering across illustration and sermon content. All follow a simple 2-column pattern: id (integer or bigint PK) + a name column.

Table	Name Column	Row Count	Purpose
sai_lookup_themes	theme_name	171	Thematic tags (Covenant, Redemption, Grace, …)
sai_lookup_doctrines	doctrine_name	21	Doctrinal categories
sai_lookup_emotions	emotion_name	17	Emotional tone of content
sai_lookup_spiritual_disciplines	discipline_name	14	Spiritual discipline focus
sai_lookup_theological_emphases	emphasis_name	14	Theological accent
sai_lookup_human_struggles	struggle_name	13	Human struggle type (grief, doubt, …)
sai_lookup_trivia_categories	category_name	12	Bible trivia categories
sai_lookup_human_needs	need_name	12	Human need addressed
sai_lookup_sermon_series_ideas	series_title	11	Series title ideas
sai_lookup_apologetic_objections	objection_name	9	Apologetic objections addressed
sai_lookup_applicational_points	point_tag	9	Application point tags
sai_lookup_gospel_elements	element_name	9	Gospel narrative elements
sai_lookup_seeker_audiences	audience_name	8	Target audience type
sai_lookup_illustrative_anecdotes	anecdote_tag	8	Anecdote/story type tags
sai_lookup_insight_types	type_name	7	Insight classification

These tables are referenced by unified_rag_content array columns (themes, doctrines, emotions, etc.) as controlled vocabulary values.

---

7. Users & Subscriptions

profiles

User profile table for ITW and SermonWise. ~6 rows. Shared auth table — users authenticated via Supabase Auth have a corresponding profile here.

Key columns:

Column	Type	Notes
id	uuid PK	Matches auth.users.id
email	text NOT NULL
full_name	text
denomination	text
church_name	text
role	text	Default 'pastor'
subscription_tier	text	Default 'free' ('free', 'sermon_pro'). Set to 'sermon_pro' by Stripe webhook on free→Pro upgrade; reset on customer.subscription.deleted.
preferences	jsonb	Default {}
stripe_customer_id	text
account_type	text	Default 'individual'
theological_lens_id	integer	Primary lens preference
free_tier_sermon_limit	integer	Default 3
onboarding_completed	boolean NOT NULL	Default false
app_source	text	Default 'churchwiseai'. Set by the handle_new_user() trigger from auth.users.raw_user_meta_data->>'app_source' (migration 2026-05-11-handle-new-user-app-source.sql). SermonWise signups write 'sermon_starter'. Never write normalizePlanTier() output here.
first_app_visit_at	timestamptz	NULL until user's first /sermons/app load. Set via POST /api/sermons/log-first-visit (atomic UPDATE-WHERE-NULL). Gates the PostHog first_app_visit funnel event so it fires once per user (replaces broken localStorage gate that persisted across user accounts in same browser). Migration add_profiles_first_app_visit_at 2026-05-09.
pro_welcome_email_sent_at	timestamptz (in user_metadata)	Idempotency flag for sendSermonProWelcomeEmail(). Stored in auth.users.raw_user_meta_data, not directly on profiles row. Set on first welcome-Pro email send.
created_at	timestamptz
updated_at	timestamptz

Realtime subscription enabled 2026-05-09 (enable_realtime_on_profiles_for_sermonwise_upgrade migration). Required for the useProfileTierRealtime client hook in src/hooks/useProfileTierRealtime.ts — both the SermonWise dashboard page (one-shot subscribe on ?upgraded=1) and SermonAppHeader (long-lived subscribe) listen for UPDATE events on this row to refresh usage data live when the Stripe webhook flips subscription_tier.

Additional gamification/social columns: total_xp, current_level, xp_to_next_level, follower_count, following_count, is_verified, is_moderator, privacy_settings (jsonb), social_links (jsonb) — these are legacy B2C features from ai-sermon-assistant/, currently unused in production.

---

user_subscriptions

Stripe subscription records for ITW/SermonWise users. ~1 row (founder test).

Column	Type	Notes
id	uuid PK
user_id	uuid NOT NULL	FK → profiles.id
pricing_tier_id	uuid NOT NULL	FK → pricing tier
stripe_subscription_id	varchar
stripe_customer_id	varchar
status	varchar NOT NULL	Default 'active'
current_period_start	timestamptz
current_period_end	timestamptz
trial_start	timestamptz
trial_end	timestamptz
cancel_at_period_end	boolean NOT NULL	Default false
canceled_at	timestamptz
ended_at	timestamptz
created_at	timestamptz
updated_at	timestamptz
is_founder_member	boolean
founder_enrolled_at	timestamptz
locked_price_cents	integer	Founder price lock
founder_member_number	integer

---

user_favorites

User-saved illustration favorites. ~0 rows.

Column	Type	Notes
id	uuid PK	gen_random_uuid()
user_id	uuid NOT NULL	FK → profiles.id
illustration_id	uuid NOT NULL	FK → unified_rag_content.id (or illustrations.id)
created_at	timestamptz NOT NULL	Default now()

---

user_theological_preferences

Per-user theological lens preferences. ~11 rows.

Column	Type	Notes
id	uuid PK
user_id	uuid NOT NULL	FK → profiles.id
primary_lens_id	integer	FK → sai_theological_lenses.lens_id
secondary_lenses	int[]	Default []
per_app_preferences	jsonb	Default {} — per-app lens overrides
content_appropriateness_min	integer	Default 5
preferred_sources	text[]	Default []
created_at	timestamptz
updated_at	timestamptz

---

sai_scripture_access_log

Usage telemetry: scripture lookups. ~244,000 rows. Tracks which passages were accessed, by whom, and how fast.

Column	Type	Notes
id	uuid PK	gen_random_uuid()
scripture_reference	text NOT NULL
analysis_type	text NOT NULL	Type of analysis performed
was_cached	boolean	Default false
access_time_ms	integer	Default 0 — response time
user_id	uuid	FK → profiles.id
created_at	timestamptz	Default now()

---

8. Backup / Staging Tables

These are read-only artifacts. Never write to them.

Table	Est. Rows	Notes
unified_rag_content_backup_20251210	~20,600	Snapshot from Dec 10 2025 before a major migration. Same schema as unified_rag_content.
scripture_word_analysis_orphaned_backup	~84,800	Word analysis rows that lost their verse_id FK during a re-import. Same schema as scripture_word_analysis.
content_passage_links_backup	~9,908	Pre-migration backup of content_passage_links. Same schema minus NOT NULL constraints.
quality_scores_backup_20250918	unknown	Backup of quality scoring data from Sep 2025 before quality score schema changed. Columns: id, theological_accuracy, practical_value, scholarly_depth, accessibility, scripture_alignment, overall, confidence, scoring metadata.
sai_passage_lens_data_backup	unknown	Backup of sai_passage_lens_data before a schema migration.
staging_lexicon	unknown	Staging table for word_study_lexicon imports. Mirrors lexicon schema.
staging_word_analysis	unknown	Staging table for scripture_word_analysis imports.

---

Key Relationships

sai_theological_lenses (18 lenses)
  └── pericope_lens_perspectives (18,600 rows: passage × lens)
  └── sai_generated_content (per-lens generated content)
  └── theological_lens_positions (doctrinal positions)
  └── theological_contradictions (guardrails)
  └── method_lens_guidance (method × lens adaptation)
  └── lens_affinity (lens compatibility)
  └── bible_book_lens_perspectives (book-level lens view)
  └── unified_rag_content.theological_lens_id

bible_passages_canonical (3,727 passages)
  └── pericope_lens_perspectives.canonical_pericope_id
  └── theme_pericope_connections.canonical_pericope_id
  └── pericope_thematic_links (cross-passage links)
  └── unified_rag_content.canonical_pericope_id
  └── theological_rag_documents.canonical_pericope_id

sai_scripture (31K verses)
  └── scripture_word_analysis (439K word rows)
  └── sai_scripture.canonical_pericope_id → bible_passages_canonical

sai_lectionary_calendar
  └── sai_lectionary_readings (4 readings per calendar entry)

profiles
  └── user_subscriptions
  └── user_favorites → unified_rag_content
  └── user_theological_preferences → sai_theological_lenses
  └── sai_scripture_access_log