Skip to main content

Knowledge > System Architecture > Database Schema

Database Schema Reference

Overview​

PropertyValue
ProviderSupabase (PostgreSQL)
Project IDwrwkszmobuhvcfjipasi
URLhttps://wrwkszmobuhvcfjipasi.supabase.co
EnvironmentONE instance for dev AND prod — no local DB, no staging
Default row limit1,000 rows — always paginate with .range()
Active tables~148 across 3 codebases (plus ~360 empty legacy B2C tables)

All three active codebases (churchwiseai-web, pewsearch/web, sermon-illustrations) share this single Supabase instance. Every query hits production data.

Shared Database Ownership​

Note: This doc was originally written from code file references (~50 tables). It was split into three codebase-owned docs in March 2026 after a full live-DB audit discovered ~148 active tables. The sub-docs below are authoritative.

Schema by Codebase​

ChurchWiseAI Tables​

76 tables owned/used by churchwiseai-web and voice-agent-livekit.

Key table groups:

  • Subscriptions & Auth: premium_churches, church_team_members, church_admin_identities, church_admin_sessions, church_identity_roles, church_access_audit
  • Voice Agent: church_voice_agents, voice_agents, voice_call_logs, voice_prayer_requests, voice_callback_requests, voice_visitor_contacts, voice_event_registrations, voice_demo_requests, voice_support_requests
  • Chatbot: organization_settings, canned_responses, chatbot_conversations
  • Care Messaging: congregation_care_members, congregation_care_messages
  • ShareWiseAI (0 rows, schema-ready): social_subscriptions, social_accounts, social_posts, and 8 more
  • Operational: articles, ops_errors, ops_quota_snapshots, chat_usage_tracking, tool_invocations, telemetry_breadcrumbs, founder_action_items, voice_cert_runs (daily outbound-trunk-cert audit log; columns: id, created_at, trunk_id, target_number, participant_joined bool, elapsed_ms int, error_text; service-role-only writes)

→ Full detail: db/churchwiseai.md

PewSearch Tables​

17 tables owned/used by pewsearch/web.

Key table groups:

  • Directory Core: churches (~218K visible / ~261K total rows), denominations (64 rows), denomination_aliases (319 rows)
  • Enrichment: church_theological_lenses (~207K rows), church_staff_contacts, church_profiles, church_rag_categories, church_events
  • Operational: church_update_requests, church_scrape_jobs (554 rows), known_issues
  • Geographic: state_boundaries (62 rows, PostGIS geometry)

Critical: Always filter churches by directory_visible = true. Visible: ~218K. Raw total: ~261K.

→ Full detail: db/pewsearch.md

ITW & SermonWise Tables​

55 tables owned/used by sermon-illustrations and shared reference data.

Key table groups:

  • Content Core: unified_rag_content (~326K rows — NEVER bulk delete), unified_rag_content_staging, illustrations, sai_generated_content
  • Scripture & Bible: sai_scripture (~31K rows), scripture_word_analysis (~439K rows — largest table), sai_strongs_concordance, bible_passages, bible_book_metadata
  • Theological Framework: sai_theological_lenses (18 rows), theological_rag_documents, theological_lens_positions, lens_affinity
  • Sermon Structure: pericope_lens_perspectives, narrative_units, sai_sermon_methods
  • Lectionary: sai_lectionary_years, sai_lectionary_sundays, sai_lectionary_calendar, sai_lectionary_readings
  • Lookup Tables: 15 sai_lookup_* tables (7–171 rows each)
  • Users & Subscriptions: profiles, user_subscriptions, user_favorites, user_theological_preferences

Critical: unified_rag_content embeddings use text-embedding-3-small. NEVER change the model — mixed models produce meaningless similarity scores.

→ Full detail: db/itw-sermonwise.md

Critical Safety Rules (All Codebases)​

  1. unified_rag_content (~326K rows): NEVER bulk delete. Always use soft-delete (is_active = false). Irreplaceable generated and curated content.

  2. churches — always filter directory_visible = true: Visible count: ~218K. Raw total: ~261K. ~42K rows are hidden (closed, duplicate, or inappropriate). NEVER quote the raw count publicly.

  3. Embedding consistency: All embeddings in unified_rag_content and church_document_chunks use text-embedding-3-small. NEVER switch embedding models without re-embedding the entire corpus.

  4. No test data in production: No dev/staging DB exists. NEVER write junk test data. Use demo church UUID 00000000-0000-4000-a000-000000000001 for testing.

  5. Supabase 1,000-row default limit: Always paginate with .range(start, end) for larger result sets.

  6. ~360 empty legacy tables: From the legacy ai-sermon-assistant B2C app. Ignore them — not used by any active codebase.

  7. No cascade on premium_churches delete: Deleting a premium_churches row does NOT cascade to church_voice_agents, organization_settings, or voice_* tables. Clean up manually if needed.

  8. premium_churches_public view: A read-only view created for RLS security. Exposes only non-sensitive fields from premium_churches for client-side queries that need to check subscription status without exposing Stripe IDs or internal fields.

Cross-Codebase Table Ownership​

TablePrimary WriterReaders
churchespewsearch/webchurchwiseai-web, pewsearch/web
premium_churcheschurchwiseai-web, pewsearch/webchurchwiseai-web, pewsearch/web
church_voice_agentschurchwiseai-webvoice-agent-livekit
voice_call_logsvoice-agent-livekitchurchwiseai-web
voice_prayer_requestsvoice-agent-livekit, churchwiseai-web (chatbot)churchwiseai-web
voice_callback_requestsvoice-agent-livekit, churchwiseai-web (chatbot)churchwiseai-web
voice_visitor_contactsvoice-agent-livekit, churchwiseai-web (chatbot)churchwiseai-web
organization_settingschurchwiseai-webchurchwiseai-web
unified_rag_contentsermon-illustrations, churchwiseai-websermon-illustrations, churchwiseai-web, voice-agent-livekit
product_knowledgeAll (via SQL migrations)churchwiseai-web, voice-agent-livekit
sai_theological_lensesSeed data (shared)All codebases
denominationspewsearch/webAll codebases
profilessermon-illustrations, churchwiseai-webAll codebases
articleschurchwiseai-webchurchwiseai-web
social_*churchwiseai-webchurchwiseai-web
contact_submissionspewsearch/webchurchwiseai-web

Storage Buckets​

BucketPurposeUsed by
avatarsUser profile photosITW, B2C
church-documentsUploaded KB documents (PDFs, DOCs)ChurchWiseAI admin
church-logosChurch logo imagesPewSearch, ChurchWiseAI
church-photosChurch hero/gallery photosPewSearch, ChurchWiseAI
illustration-imagesIllustration artworkITW
lead-magnetsDownloadable lead magnet PDFsChurchWiseAI marketing
public-uploadsGeneral public uploadsVarious

Key Database Functions (RPCs)​

FunctionPurposeCalled by
search_unified_rag_contentSemantic vector search over RAG corpusChatbot, Voice agent
search_unified_rag_content_hierarchicalHierarchical scripture-aware RAG searchChatbot
search_church_knowledgeChurch-specific knowledge base searchChatbot
get_chatbot_analyticsDashboard analyticsAdmin dashboard
match_canned_responsesSemantic FAQ matchingChatbot
check_chatbot_conversation_limitEnforce conversation capsChatbot
refresh_directory_viewRefresh dir_illustrations viewCron/manual
refresh_denomination_countsUpdate church counts per denominationCron
search_illustrationsIllustration search for ITWITW

See Also​