BIBLE DATABASE & APP MASTER PROMPT

🔑 BIBLE DATABASE & APP MASTER PROMPT

Complete UUID System → PostgreSQL → SQLite → Obsidian Pipeline

For: Claude, Gemini, ChatGPT, or any AI building David's Bible App

🎯 YOUR MISSION

Build a Bible Knowledge Engine that:

  1. Starts with UUID (the root coordinate system)
  2. Organizes all data into logical SQL databases
  3. Syncs to Obsidian for visual Bible study
  4. Powers the Bible Desktop App (Bible Desk App + Bible REACT)

🧠 FOUNDATIONAL CONCEPT: THE UUID IS EVERYTHING

What is a UUID?

A UUID (Unique Universal Identifier) is a permanent, unchanging reference for every piece of Scripture.

It's the spine of the entire system.

UUID Format: VR-KJV-XXXXXX
              ├─ VR = Scripture Verse Record
              ├─ KJV = King James Version (locked)
              └─ XXXXXX = Sequential ID or encoded book-chapter-verse

Why UUID First?

  • ✅ Every piece of data (commentary, topic, person, place, event) references a UUID
  • ✅ Never changes - if you re-import data, UUID stays the same
  • ✅ Enables linking - comment from MacArthur points to same UUID as topical entry
  • ✅ Survives updates - data evolves, UUID remains constant

Real Example:

Genesis 1:1 = VR-KJV-010101-AA (whole verse)
             = VR-KJV-010101-001 (first word: "In")
             = VR-KJV-010101-002 (second word: "the")
             = VR-KJV-010101-VH (verse header/label)

EVERYTHING referencing Genesis 1:1 uses one of these UUIDs

🏗️ SYSTEM ARCHITECTURE

Layer 1: UUID Core (Immutable)

┌─────────────────────────────────────┐
│ MASTER VERSE INDEX (31,102 verses)  │
│ Every verse gets UUID in format:    │
│ VR-KJV-BBCCVVWW                     │
│ B=book, C=chapter, V=verse, W=word  │
└─────────────────────────────────────┘

Layer 2: Entity UIDs (Stable)

┌──────────────────────────────────────┐
│ PEOPLE    = PER-XXXXXX (e.g., PER-00001 = Moses)
│ PLACES    = PLC-XXXXXX (e.g., PLC-00045 = Egypt)
│ THINGS    = THG-XXXXXX (e.g., THG-00012 = Ark)
│ TOPICS    = TOP-XXXXXX (e.g., TOP-00101 = Faith)
│ EVENTS    = EVT-XXXXXX (e.g., EVT-00089 = Red Sea)
│ COMMENTARY= COM-XXXXXX (e.g., COM-MA-001 = MacArthur)
│ LEXICON   = LNG-XXXXXX (e.g., LNG-H7225 = Hebrew word)
└──────────────────────────────────────┘

Layer 3: SQL Databases (Grouped by domain)

┌─────────────────────────────────────────┐
│ PostgreSQL (Master Database)            │
│ ├─ core/                                │
│ │  └─ scripture (31,102 verses + UIDs)  │
│ ├─ people/                              │
│ │  ├─ persons (PER entities)            │
│ │  └─ mentions (person_uuid → verse_uuid)
│ ├─ places/                              │
│ │  ├─ locations (PLC entities)          │
│ │  └─ mentions (place_uuid → verse_uuid)
│ ├─ things/                              │
│ │  ├─ objects (THG entities)            │
│ │  └─ mentions (thing_uuid → verse_uuid)
│ ├─ commentary/                          │
│ │  └─ entries (verse_uuid → text)       │
│ ├─ topics/                              │
│ │  ├─ topical_index (TOP entities)      │
│ │  └─ refs (topic_uuid → verse_uuid)    │
│ ├─ timeline/                            │
│ │  ├─ events (EVT entities)             │
│ │  └─ refs (event_uuid → verse_uuid)    │
│ ├─ lexicon/                             │
│ │  ├─ words (LNG Strong's data)         │
│ │  └─ links (word_uuid → verse_uuid)    │
│ └─ system/                              │
│    └─ ingestion_log (audit trail)       │
└─────────────────────────────────────────┘

Layer 4: SQLite Cache (Local + Obsidian)

┌──────────────────────────────┐
│ SQLite Mirror Database       │
│ (Same schema as Postgres)    │
│ Syncs nightly or on-demand   │
│ Lightweight for desktop app  │
└──────────────────────────────┘

Layer 5: Obsidian Vault (Visual Layer)

┌──────────────────────────────────────┐
│ Obsidian Note Format                 │
│                                      │
│ Genesis_1_1.md                       │
│ ─────────────────────────────        │
│ uuid: VR-KJV-010101-AA               │
│ book: Genesis                        │
│ chapter: 1                           │
│ verse: 1                             │
│ people: [PER-00001]                  │
│ places: [PLC-00045]                  │
│ topics: [TOP-00101, TOP-00202]       │
│ commentary_count: 12                 │
│ ─────────────────────────────        │
│ # Genesis 1:1                        │
│                                      │
│ In the beginning God created...      │
│                                      │
│ ## People Mentioned                  │
│ - [[PER-00001|God]]                  │
│                                      │
│ ## Topics                            │
│ - [[TOP-00101|Creation]]             │
│ - [[TOP-00202|God's Power]]          │
│                                      │
│ ## Commentary                        │
│ - MacArthur: "..." (COM-MA-001)      │
│ - Matthew Henry: "..." (COM-MH-001)  │
└──────────────────────────────────────┘

📊 THE 4-PHASE INGESTION WORKFLOW

⏱️ PHASE 1: FOUNDATION (Days 1-3)

Goal: Build the UUID spine - every verse gets a stable reference ID

Tasks:

  1. Parse Master Workbook → Extract all verses
  2. Generate verse UUIDs in format: VR-KJV-BBCCVVXX
  3. Create core.scripture table in Postgres
  4. Load all 31,102 verses with their UUIDs
  5. Create chapter/verse anchors (VH and AA suffixes)

Output:

  • ✅ core.scripture table populated
  • ✅ Every verse has a permanent UUID
  • ✅ Chapter and verse anchors created

Commands:

# Parse Master Workbook and generate UUIDs
python phase1_parse_master.py \
  --input "Bible_Tagging_Master_Workbook.xlsx" \
  --output "verse_registry.csv"

# Load into Postgres
psql -U postgres -d bible_data -c \
  "\copy core.scripture (verse_uuid, book, chapter, verse, text) FROM 'verse_registry.csv' CSV HEADER"

# Verify
psql -U postgres -d bible_data -c "SELECT COUNT(*) FROM core.scripture;"
# Expected output: 31102

🔗 PHASE 2: ENTITY LINKING (Days 4-6)

Goal: Create stable IDs for People, Places, Things + link them to verses

Tasks:

  1. Extract people list → Create PER-XXXXXX IDs
  2. Extract places list → Create PLC-XXXXXX IDs
  3. Extract things/objects → Create THG-XXXXXX IDs
  4. Create junction tables linking entities to verse UUIDs
  5. Map all mentions (e.g., "Moses" appears in Genesis 2:3, Exodus 1:1, etc.)

Output:

  • ✅ people.persons table (2,000+ people with stable PER IDs)
  • ✅ places.locations table (500+ places with stable PLC IDs)
  • ✅ things.objects table (objects with stable THG IDs)
  • ✅ link_person, link_place, link_thing junction tables
  • ✅ All entities mapped to verse UUIDs

Commands:

# Phase 2A: Import People
python phase2a_import_people.py \
  --input "People.csv" \
  --dsn "postgresql://postgres:PASSWORD@localhost:5432/bible_data"

# Phase 2B: Import Places
python phase2b_import_places.py \
  --input "Places.csv" \
  --dsn "postgresql://postgres:PASSWORD@localhost:5432/bible_data"

# Phase 2C: Link entities to verses
python phase2c_entity_linking.py \
  --dsn "postgresql://postgres:PASSWORD@localhost:5432/bible_data"

# Verify
psql -U postgres -d bible_data -c "SELECT COUNT(*) FROM people.persons;"

📖 PHASE 3: COMMENTARY & TOPICAL DATA (Days 7-10)

Goal: Import all commentary, topics, and lexicon data linked by UUID

Tasks:

  1. Import MacArthur commentary → attach to verse UUIDs
  2. Import Matthew Henry notes → attach to verse UUIDs
  3. Import BSB topical index → create TOP-XXXXXX IDs
  4. Import Nave's topical dictionary → link to verses
  5. Import Strong's Hebrew/Greek → create LNG-XXXXXX IDs
  6. Link all commentary and lexicon to verse UUIDs

Output:

  • ✅ commentary.entries (17,594+ commentary entries)
  • ✅ topics.topical_index (5,319 topics)
  • ✅ topics.refs (junction table linking topics to verses)
  • ✅ lexicon.words (8,675 Hebrew + Greek roots)
  • ✅ lexicon.word_links (word-to-verse mappings)

Commands:

# Phase 3A: Import Commentary
python phase3a_import_commentary.py \
  --dsn "postgresql://postgres:PASSWORD@localhost:5432/bible_data" \
  --macarthur "KJV_with_MacArthur_Commentary.xlsx" \
  --henry "matthew_henry_enhanced.xlsx"

# Phase 3B: Import Topics
python phase3b_import_topics.py \
  --dsn "postgresql://postgres:PASSWORD@localhost:5432/bible_data" \
  --input "bsb_topical_index.xlsx"

# Phase 3C: Import Lexicon
python phase3c_import_lexicon.py \
  --dsn "postgresql://postgres:PASSWORD@localhost:5432/bible_data" \
  --hebrew "HebrewStrongs.csv"

# Verify
psql -U postgres -d bible_data -c "SELECT COUNT(*) FROM commentary.entries;"
psql -U postgres -d bible_data -c "SELECT COUNT(*) FROM topics.topical_index;"

⏰ PHASE 4: TIMELINE & ANALYTICS (Days 11-14)

Goal: Import timeline events and compute word statistics

Tasks:

  1. Parse timeline data (from Excel, YAML, web pages, OCR'd images)
  2. Extract events with date ranges
  3. Create EVT-XXXXXX IDs
  4. Link events to verses by reference
  5. Compute word frequency analytics
  6. Create materialized views for fast querying

Output:

  • ✅ timeline.events (210+ events with dates)
  • ✅ timeline.event_refs (events linked to verses)
  • ✅ analytics.word_index (word frequency, occurrence count)
  • ✅ Materialized views for Obsidian export

Commands:

# Phase 4A: Import Timeline
python phase4a_import_timeline.py \
  --dsn "postgresql://postgres:PASSWORD@localhost:5432/bible_data" \
  --input "timeline_data/" \
  --ocr-images "timeline_images/"

# Phase 4B: Compute Analytics
python phase4b_compute_analytics.py \
  --dsn "postgresql://postgres:PASSWORD@localhost:5432/bible_data"

# Phase 4C: Create Master View
python phase4c_create_master_view.py \
  --dsn "postgresql://postgres:PASSWORD@localhost:5432/bible_data"

# Verify
psql -U postgres -d bible_data -c "SELECT COUNT(*) FROM timeline.events;"
psql -U postgres -d bible_data -c "SELECT * FROM analytics.word_index LIMIT 10;"

🗄️ POSTGRES TO SQLITE SYNC

Why SQLite?

  • ✅ Lightweight (fits on desktop)
  • ✅ No server required
  • ✅ Obsidian can query directly
  • ✅ Offline-friendly

Sync Command:

# Export Postgres to SQLite
python sync_postgres_to_sqlite.py \
  --source "postgresql://postgres:PASSWORD@localhost:5432/bible_data" \
  --target "bible_data.sqlite" \
  --schemas "core,people,places,things,commentary,topics,timeline,lexicon,analytics"

# Verify SQLite
sqlite3 bible_data.sqlite "SELECT COUNT(*) FROM core.scripture;"

Result:

One bible_data.sqlite file containing all data with same UUID system.

🧠 OBSIDIAN INTEGRATION

How It Works:

  1. Query SQLite from Obsidian plugin
  2. Generate Markdown notes automatically
  3. Link by UUID (Genesis_1_1.md references VR-KJV-010101-AA)
  4. Display all linked data (commentary, topics, people, places)
  5. Enable visual browsing with color-coded overlays

Example Obsidian Note:

File: Genesis_1_1.md

---
uuid: VR-KJV-010101-AA
book: Genesis
chapter: 1
verse: 1
people: ["PER-00001"]
places: ["PLC-00045"]
topics: ["TOP-00101", "TOP-00202"]
lexicon: ["LNG-H0430"]
events: ["EVT-00089"]
---

# Genesis 1:1

In the beginning God created the heaven and the earth.

## People Mentioned
- [[PER-00001|God]] (appears 2,643 times)

## Topics Linked
- [[TOP-00101|Creation]] (Faith & God's Power)
- [[TOP-00202|God's Power]] (Omnipotence)

## Lexicon Analysis
- **Elohim** (H0430) - God (plural of majesty, appearing 2,571 times)

## Commentary
**MacArthur (COM-MA-001):**
> "The opening phrase establishes God's eternal nature and creative power..."

**Matthew Henry (COM-MH-001):**
> "The creation of all things by God declares His infinite power, wisdom, and goodness..."

## Cross-References
- [[John_1_1|John 1:1]] - "In the beginning was the Word..."
- [[Proverbs_8_22|Proverbs 8:22]] - "The Lord possessed me in the beginning..."

## Related Events
- [[EVT-00089|Red Sea Crossing]] (exodus 14:21)
- [[EVT-00090|Giving of the Law]] (exodus 20:1)

Obsidian Plugin Requirements:

// Pseudo-code for Obsidian plugin
async function onVerseLookup(verseName) {
  // 1. Generate UUID from verse name
  const uuid = generateUUID(verseName); // "Genesis 1:1" → "VR-KJV-010101-AA"
  
  // 2. Query SQLite for all linked data
  const verse = await sqlite.query(`
    SELECT * FROM core.scripture WHERE verse_uuid = ?
  `, [uuid]);
  
  const people = await sqlite.query(`
    SELECT p.* FROM people.persons p
    JOIN link_person lp ON p.person_id = lp.person_id
    WHERE lp.verse_uuid = ?
  `, [uuid]);
  
  const commentary = await sqlite.query(`
    SELECT * FROM commentary.entries WHERE verse_uuid = ?
  `, [uuid]);
  
  const topics = await sqlite.query(`
    SELECT t.* FROM topics.topical_index t
    JOIN topics.refs tr ON t.topic_id = tr.topic_id
    WHERE tr.verse_uuid = ?
  `, [uuid]);
  
  // 3. Generate Markdown from results
  return generateMarkdownNote(verse, people, commentary, topics);
}

🖥️ BIBLE DESKTOP APP & REACT APP

Desktop App Architecture (Bible Desk App)

Bible Desk App/
├── database.sqlite (synced from Postgres)
├── main.js (Electron)
├── app/
│  ├── components/
│  │  ├── VerseBrowser.jsx (shows current verse)
│  │  ├── CommentaryPanel.jsx (shows all commentary)
│  │  ├── PeoplePanel.jsx (people mentioned)
│  │  ├── PlacesPanel.jsx (places/maps)
│  │  ├── TopicsPanel.jsx (linked topics)
│  │  └── LexiconPanel.jsx (Hebrew/Greek)
│  └── services/
│     └── DatabaseService.js (SQLite queries)
└── package.json

React App Architecture (Bible REACT)

Bible REACT/
├── src/
│  ├── components/
│  │  ├── BibleViewer.jsx (main reader)
│  │  ├── CommentaryViewer.jsx
│  │  ├── TopicExplorer.jsx
│  │  ├── PersonSearch.jsx
│  │  └── MapViewer.jsx
│  ├── services/
│  │  ├── PostgresService.js (API calls)
│  │  └── UUIDService.js (UUID generation/parsing)
│  └── db/
│     └── queries.sql
└── package.json

How Apps Use UUIDs:

// User clicks "Genesis 1:1" in app
// App generates UUID: VR-KJV-010101-AA
// App queries: SELECT * FROM core.scripture WHERE verse_uuid = 'VR-KJV-010101-AA'
// App displays verse + all linked data (commentary, people, topics, etc.)
// User clicks on "Moses" link → UUID becomes PER-00001
// App queries: SELECT * FROM people.persons WHERE person_uuid = 'PER-00001'
// Shows all verses where Moses appears

🚀 COMPLETE DATA FLOW SUMMARY

┌─────────────────────────────────┐
│ Master Workbook (XLSX/CSV)      │
│ + 20GB Additional Data          │
│ (Commentary, Lexicon, Timeline) │
└──────────────┬──────────────────┘
               │
               ▼
┌─────────────────────────────────┐
│ PHASE 1: UUID Generation        │
│ Create verse UIDs (VR-KJV-*)    │
│ & entity UIDs (PER-, PLC-, etc) │
└──────────────┬──────────────────┘
               │
               ▼
┌─────────────────────────────────┐
│ PHASE 2-4: Data Ingestion       │
│ Load all domains into Postgres  │
│ (commentary, topics, lexicon)   │
└──────────────┬──────────────────┘
               │
               ▼
┌─────────────────────────────────┐
│ PostgreSQL (Master DB)          │
│ All 31,102 verses + metadata    │
│ Queried by UUID                 │
└──────────────┬──────────────────┘
               │
               ▼
┌─────────────────────────────────┐
│ SQLite Sync                     │
│ Lightweight copy for apps       │
│ Same UUID system                │
└──────────────┬──────────────────┘
               │
        ┌──────┴──────┐
        ▼             ▼
    ┌──────────┐  ┌──────────────┐
    │ Obsidian │  │ Desktop/React│
    │ Vault    │  │ Bible Apps   │
    └──────────┘  └──────────────┘
        │             │
        └─────────────┘
         All linked by UUID

📋 STEP-BY-STEP FOR AN AI AGENT

Your Instructions (Copy-Paste to Claude/Gemini):

You are the Bible Database Ingestion Agent.

MISSION: Consolidate 20GB+ Bible data into PostgreSQL using UUID system.

FOUNDATIONAL RULE: Everything is organized by UUID. UUID is immutable.

PROCEDURE:

1. PHASE 1 (Foundation):
   - Parse Master Workbook
   - Generate verse UUIDs: VR-KJV-BBCCVVXX
   - Load into core.scripture table
   - Create chapter/verse anchors
   - Verify: 31,102 verses loaded

2. PHASE 2 (Entities):
   - Extract people → PER-XXXXXX
   - Extract places → PLC-XXXXXX
   - Extract things → THG-XXXXXX
   - Create junction tables
   - Map all mentions to verse UUIDs

3. PHASE 3 (Metadata):
   - Import commentary → link by verse UUID
   - Import topics → create TOP-XXXXXX
   - Import lexicon → create LNG-XXXXXX
   - Create all junction tables

4. PHASE 4 (Analytics):
   - Import timeline → EVT-XXXXXX
   - Compute word frequency
   - Create materialized views
   - Prepare for export

5. SYNC TO SQLite:
   - Export all tables to SQLite
   - Same UUID system
   - Lightweight for apps

6. OBSIDIAN INTEGRATION:
   - Generate Markdown notes per verse
   - Include UUID in frontmatter
   - Link entities by UUID
   - Create visual network

DELIVERABLES:
- PostgreSQL database (31,102 verses + all metadata)
- SQLite database (synced copy)
- Obsidian vault (auto-generated notes)
- Desktop/React apps ready to query

RULES:
- Never change a UUID after creation
- All links use UUIDs, never plain text
- Log every ingestion phase
- Validate after each phase
- Handle 20GB+ data with batch processing

✅ SUCCESS CRITERIA

When complete, you should have:

  • ✅ 31,102 verses with permanent UUIDs in Postgres
  • ✅ 17,594+ commentary entries linked by UUID
  • ✅ 5,319 topics with stable TOP-XXXXXX IDs
  • ✅ 2,000+ people with stable PER-XXXXXX IDs
  • ✅ 500+ places with stable PLC-XXXXXX IDs
  • ✅ 210+ events with dates and stable EVT-XXXXXX IDs
  • ✅ 8,675+ lexicon entries (Hebrew/Greek)
  • ✅ SQLite mirror of all Postgres data
  • ✅ Obsidian vault with auto-generated notes
  • ✅ Desktop & React apps querying by UUID

🎯 THE CORE IDEA

Everything starts with UUID.

Every piece of information in your Bible database is referenced by a UUID. This UUID never changes. Whether you're browsing in Obsidian, querying in the desktop app, or searching in React – everything goes through the UUID system.

This is what makes your system:

  • ✅ Unified (all data speaks the same language)
  • ✅ Scalable (add new data types without breaking old ones)
  • ✅ Reliable (references never break)
  • ✅ Intelligent (AI can traverse and connect anything by UUID)

READY TO BUILD THE BIBLE APP 🚀