Expert assistant for diagnosing and fixing Strong's concordance issues in the Raamattu Nyt project. Use when (1) debugging infinite loops or performance issues with Strong's lookups, (2) validating lexicon data against authoritative sources, (3) checking KJV verses point to correct Strong's numbers, (4) fixing corrupted kjv_strongs_words data, (5) auditing strongs_lexicon entries for format/content issues, or (6) troubleshooting Strong's search functionality. Triggers: strongs issue, lexicon error, infinite loop strongs, strongs validation, kjv strongs, fix strongs, lexicon fix.
Strongs Doctor
Diagnose and fix all Strong's concordance and lexicon issues in the Raamattu Nyt Bible application.
Learnings / gotchas: See references/learnings.md (e.g. Strong's search 57014 timeout = OR-of-ILIKE missing a trigram index).
CRITICAL: bible_schema Usage
All Strong's tables and RPC functions reside in bible_schema, NOT public.
Supabase Client Queries
// WRONG - looks in public schema, will fail with "function not found"const { data } = await supabase.rpc("search_verses_by_strongs", { ... });
// CORRECT - explicitly specify bible_schemaconst { data } = await (supabase asany)
.schema("bible_schema")
.rpc("search_verses_by_strongs", { ... });
{ data } = supabase.().();
{ data } = (supabase )
.()
.()
.();
// WRONG - table query without schema
const
await
from
"strongs_lexicon"
select
"*"
// CORRECT - with schema prefix
const
await
as
any
schema
"bible_schema"
from
"strongs_lexicon"
select
"*"
PostgREST Nested Selects Issue
Complex nested selects with !inner joins often fail silently in bible_schema due to PostgREST relationship inference issues. Solution: Use RPC functions instead.
Example: search_verses_by_strongs(p_strongs_number, p_limit) handles the complex join logic server-side.
SQL Queries
Always prefix table names with bible_schema.:
-- WRONGSELECT*FROM strongs_lexicon WHERE strongs_number ='G25';
-- CORRECTSELECT*FROM bible_schema.strongs_lexicon WHERE strongs_number ='G25';
Quick Diagnosis Checklist
When a Strong's issue is reported, check these in order:
Infinite Loop? - Check LexiconCard useEffect dependencies and async state updates
Wrong Data? - Verify strongs_number format (H/G prefix, leading zeros)
Missing Data? - Check if strongs_lexicon entry exists
Corrupted KJV? - Check for David's Psalm corruption pattern
Performance? - Check for missing indexes or N+1 queries
Database Schema
strongs_lexicon (14,197 entries)
Primary lexicon data for Hebrew (H) and Greek (G) Strong's numbers.
-- Key columns
strongs_number TEXT PRIMARY KEY-- 'H1', 'G26', etc.language TEXT -- 'H' or 'G'
lemma TEXT -- Original word meaning
transliterations TEXT[] -- Phonetic representations
pronunciations TEXT[] -- Pronunciation guides
part_of_speech TEXT
definition_short TEXT
definition_lit TEXT
definition_long TEXT
derivation TEXT -- Cross-refs like "from [[G123]]" or "(h0085)"
notes TEXT
see_also TEXT[] -- Related Strong's numbers
compare TEXT[] -- Comparison Strong's numbers
kjv_strongs_words (939,793 entries)
Word-by-word Strong's mappings for KJV Bible.
verse_id UUID REFERENCES bible_schema.verses(id)
word_order INTEGER
word_text TEXT
strongs_number TEXT -- Can be NULL for punctuationPRIMARY KEY (verse_id, word_order)
Common Issues & Fixes
Issue 1: Infinite Loop in LexiconCard
Symptoms: Browser freezes, excessive API calls, memory usage spikes
Root Cause: Async state updates in useEffect triggering re-renders
Root Cause: The kjv_strongs_words table stores Strong's numbers on trailing empty strings or punctuation, NOT directly on words.
Data Pattern:
word_order 4: "God" strongs_number=null <- actual word
word_order 5: "" strongs_number="G3588" <- article (often ignored)
word_order 6: "" strongs_number="G2316" <- THIS is the Strong's for "God"
word_order 7: "so" strongs_number=null <- next word
Resolution Rules (used by get_kjv_verses_tagged RPC):
Group by word: Assign each row to a "word group" - counter increments on each actual word
Find trailing carriers: Empty strings and punctuation after a word belong to that word's group
Take the LAST Strong's: When multiple carriers exist, the LAST one has the main Strong's number
Ignore direct Strong's: Strong's numbers directly on words (like "For" with G3588) are often incorrect artifacts
Verification Query:
-- See word groups for John 3:16WITH words_classified AS (
SELECT w.word_order, w.word_text, w.strongs_number,
w.word_text !=''AND w.word_text !~'^[\s\.,;:?!\-\(\)''\"]+$'AS is_actual_word
FROM bible_schema.kjv_strongs_words w
WHERE w.verse_id = (
SELECT v.id FROM bible_schema.verses v
JOIN bible_schema.verse_keys vk ON vk.id = v.verse_key_id
JOIN bible_schema.bible_versions bv ON bv.id = v.version_id
WHERE vk.osis ='John.3.16'AND bv.code ='KJV'
)
)
SELECT*, SUM(CASEWHEN is_actual_word THEN1ELSE0END)
OVER (ORDERBY word_order) AS word_group
FROM words_classified ORDERBY word_order LIMIT 20;
Detection Query (corrupted punctuation count):
-- Count punctuation with Strong's numbers (expected: ~86,000)SELECT word_text, COUNT(*) as count
FROM bible_schema.kjv_strongs_words
WHERE word_text IN ('.', ',', ';', ':', '?', '!', '-', '(', ')')
AND strongs_number ISNOT NULLGROUPBY word_text ORDERBY count DESC;
DO NOT delete these entries - they are intentional carriers for the Strong's numbers!
Issue 4: Cross-Reference Parsing Errors
Symptoms: Links not working, wrong Strong's displayed
Formats in derivation/notes fields:
[[H1234]] - Bracket format
(h0085) - Parentheses format (lowercase, with leading zeros)
Symptoms: Slow searches, timeouts, high database load
Root Causes:
Sequential pattern searches (7 patterns tried one by one)
No caching of fetchStrongsName results
N+1 queries for reference names
Check Indexes:
-- Ensure indexes existSELECT indexname FROM pg_indexes
WHERE tablename ='kjv_strongs_words'AND schemaname ='bible_schema';
-- Should have index on strongs_numberCREATE INDEX IF NOTEXISTS idx_kjv_strongs_words_strongs_number
ON bible_schema.kjv_strongs_words(strongs_number);
User reports "Strong's not working"
│
├─ Infinite loop/freeze → Check LexiconCard useEffect
├─ Wrong/missing data → Check number format, check lexicon entry exists
├─ Performance issue → Check indexes, check for N+1 queries
└─ Display issue → Check cross-reference parsing
Step 2: Gather Data
-- Check if Strong's number exists in lexiconSELECT*FROM bible_schema.strongs_lexicon WHERE strongs_number ='G26';
-- Check KJV word mappingsSELECT*FROM bible_schema.kjv_strongs_words WHERE strongs_number ='G26' LIMIT 10;
-- Check for null Strong's numbers (should only be punctuation)SELECTCOUNT(*) FROM bible_schema.kjv_strongs_words WHERE strongs_number ISNULL;
Step 3: Apply Fix
Based on issue type, apply appropriate fix from the Common Issues section above.
Step 4: Verify Fix
-- After fix, verify data integritySELECTCOUNT(*) as total,
COUNT(DISTINCT strongs_number) as unique_strongs,
COUNT(*) FILTER (WHERE strongs_number ISNULL) as null_count
FROM bible_schema.kjv_strongs_words;
Article/Particle Detection
Common grammatical words that should be displayed with subdued styling:
Greek Articles:
G3588 (the) - definite article, appears 18,109 times
