Database Schema — ChurchWiseAI Web

Knowledge > Architecture > Database Schema > ChurchWiseAI Tables

---

Database Schema — ChurchWiseAI Web

All tables owned or primarily used by churchwiseai-web/. Shared Supabase instance: wrwkszmobuhvcfjipasi. Row counts are pg_class estimates (actual may vary). Tables with ~0 or -1 est are empty or unanalyzed.

Core ChurchWiseAI Table Relationships

---

Church Subscriptions & Auth

premium_churches (~6 rows)

The central subscription record for every church using ChurchWiseAI products. One row per church. All auth, plan, and Pro Website content lives here.

Column	Type	Notes
id	uuid	PK
church_id	uuid	FK → churches.id (PewSearch directory record)
stripe_customer_id	text	Stripe customer
stripe_subscription_id	text	Active Stripe subscription
plan	text	starter, pro, suite; default starter
status	text	preview, active, trialing, canceled; default preview
preview_created_at	timestamptz	When preview/trial started
preview_expires_at	timestamptz	14-day preview window
activated_at	timestamptz	When paid subscription started
admin_token	text	Magic-link token for /admin/[token]; auto-generated hex
admin_email	text	Primary contact email
admin_name	text	Primary contact name
admin_phone	text	SMS notification number
admin_role	text	Role label (e.g. "Pastor")
channel	text	chat, voice, both; default chat
custom_name	text	Church display name override
custom_description	text	Church tagline
hero_photo_url	text	Pro Website hero image
logo_url	text	Church logo
hero_slideshow_keys	jsonb	Array of Storage keys for slideshow images
hero_video_key	text	Storage key for hero video
transition_video_key	text	Storage key for transition video
featured_video_url	text	Embedded YouTube/Vimeo URL
what_to_expect	jsonb	First-visit section content
custom_staff	jsonb	Staff members array
custom_ministries	jsonb	Ministries array
custom_hours	jsonb	Service times
custom_social_media	jsonb	Social media links
events	jsonb	Upcoming events array
sermons	jsonb	Recent sermons array
beliefs	jsonb	Beliefs/doctrine section
giving_url	text	Online giving link
contact_cc_email	text	CC email for contact form submissions
website_template	text	Pro Website theme selection
vanity_slug	text	Custom URL slug
chatbot_agent_id	uuid	FK → chatbot_agents.id
chatbot_enabled	boolean	Whether chatbot widget is active
care_enabled	boolean	Whether congregation care module is enabled
custom_domain	text	White-label domain (Suite plan)
cap_info	jsonb	Capacity/plan override info
email_opt_out	boolean	Marketing email opt-out
last_email_sent_at	timestamptz	Drip campaign tracking
email_sequence_step	integer	Current drip step
sms_sent_at	timestamptz	Last SMS notification sent
ministries_updated_at	timestamptz	Last ministries edit
founder_notes	text	Internal notes (not customer-visible)
email_send_domain	text	Custom sending domain registered with Resend (e.g., gracecommunity.church). NULL until set. Added FA-107 v2 2026-05-12.
email_send_from_address	text	Full resolved From address (e.g., pastor@gracecommunity.church). Populated after verification. NULL until verified.
email_send_resend_id	text	Resend domain object ID — required for verify + delete API calls. NULL until domain registered.
email_send_status	text	Verification state machine: unverified (default) | pending | verified | failed. CHECK constraint enforces enum.
email_send_dns_records	jsonb	Cached DNS records from Resend (SPF + DKIM CNAMEs) for Settings panel rendering. NULL until domain registered.
created_at	timestamptz
updated_at	timestamptz

Safety: Only 4–6 rows exist (all founder test accounts). NEVER bulk delete. All mutations must be intentional.

Email domain columns (email_send_*) added in migrations/2026-05-12-per-church-email-domain.sql (FA-107 v2). All five are additive with safe defaults — existing churches start in unverified state automatically. Full state-machine and code-path docs: knowledge/architecture/inbox-email-sender-domain.md.

---

church_team_members (~0 rows)

Additional admin users for a church's account (multi-user access).

Column	Type	Notes
id	uuid	PK
church_id	uuid	FK → premium_churches.church_id
premium_id	uuid	FK → premium_churches.id
access_token	text	Auto-generated hex token
name	text
email	text
role	text	e.g. admin, editor
is_active	boolean	Default true
last_accessed_at	timestamptz
created_at	timestamptz

---

church_admin_identities (~1 row)

Supabase Auth identity records linked to a church admin account.

Column	Type	Notes
id	uuid	PK
church_id	uuid	FK → premium_churches.church_id
email	text
name	text
phone	text
auth_user_id	uuid	FK → Supabase Auth users.id
is_primary_owner	boolean
is_backup_owner	boolean
is_active	boolean	Default true
created_at	timestamptz
updated_at	timestamptz

---

church_admin_sessions (~14 rows)

Hashed session tokens for magic-link admin authentication.

Column	Type	Notes
id	uuid	PK
identity_id	uuid	FK → church_admin_identities.id
church_id	uuid	FK → premium_churches.church_id
token_hash	text	Bcrypt/SHA hash of session token
created_at	timestamptz
last_used_at	timestamptz
expires_at	timestamptz	Default now() + 30 days
revoked_at	timestamptz	Null if active
created_by	uuid
ip	text
user_agent	text

---

church_identity_roles (~1 row)

Role assignments for church admin identities.

Column	Type	Notes
id	uuid	PK
identity_id	uuid	FK → church_admin_identities.id
church_id	uuid	FK → premium_churches.church_id
role	text	Role name
created_at	timestamptz

---

church_access_audit (~4 rows)

Immutable audit log of admin access events (logins, token use, etc.).

Column	Type	Notes
id	uuid	PK
church_id	uuid	FK → premium_churches.church_id
identity_id	uuid	FK → church_admin_identities.id (nullable)
event_type	text	e.g. login, token_used, logout
details	jsonb	Event-specific payload
ip	text
user_agent	text
created_at	timestamptz

Safety: Audit log — never delete rows.

---

Voice Agent

church_voice_agents (~2 rows)

Per-church configuration for the voice agent. One row per church with a voice subscription.

Column	Type	Notes
id	uuid	PK
church_id	uuid	FK → premium_churches.church_id
premium_id	uuid	FK → premium_churches.id
twilio_phone_number	text	Inbound phone number
twilio_phone_sid	text	Twilio Phone Number SID
voice_id	text	Cartesia voice ID; default dIeHOwebB4fO6l6gNfUK
welcome_greeting	text	Custom greeting script
custom_faqs	jsonb	Church-specific FAQs array
pastor_name	text
notification_email	text	Where to send leads/alerts
notification_phone	text	SMS notification number
prayer_requests_enabled	boolean	Default true
visitor_intake_enabled	boolean	Default true
callback_scheduling_enabled	boolean	Default true
giving_enabled	boolean	Default false
giving_url	text
etransfer_email	text	e-Transfer giving email
giving_message	text
status	text	trial, active, suspended; default trial
trial_ends_at	timestamptz	Default now() + 14 days
calls_this_month	integer	Current month call counter
calls_limit	integer	Default 50
recording_enabled	boolean	Default false
pastor_availability	jsonb	Structured availability slots
pastor_availability_text	text	Human-readable availability
church_timezone	text	Default America/New_York
cal_event_type_id	text	Cal.com event type for booking
cal_api_key	text	Cal.com API key
pco_app_id	text	Planning Center app ID
pco_secret	text	Planning Center secret
pco_enabled	boolean	Default false
sermon_topic	text	Current sermon topic (Pastor Pulse)
sermon_series	text	Current sermon series
theme_verse	text	Weekly theme verse
weekly_announcement	text	Announcement for this week
human_request_message	text	Message when human requested
pastor_pulse_updated_at	timestamptz	Last Pastor Pulse update
crisis_message	text	Custom crisis/grief response
crisis_blessing	text	Crisis closing blessing
created_at	timestamptz
updated_at	timestamptz

---

voice_agents (~18 rows)

Cartesia voice option catalog — available voice IDs with labels and routing weights.

Column	Type	Notes
id	uuid	PK
voice_id	text	Cartesia voice ID
label	text	Display name
region	text	Default default
weight	integer	Routing weight for load balancing
enabled	boolean	Whether available for selection
created_at	timestamptz
updated_at	timestamptz

---

voice_call_logs (~118 rows)

Full log of every inbound call handled by the voice agent.

Column	Type	Notes
id	uuid	PK
call_sid	text	Twilio Call SID (unique)
from_number	text	Caller phone number
to_number	text	Church Twilio number
church_id	uuid	FK → premium_churches.church_id
voice_id	text	Cartesia voice used
duration_seconds	integer
transcript	jsonb	Array of turn objects {role, content}
summary	text	AI-generated call summary
status	text	in_progress, completed, failed; default in_progress
recording_sid	text	Twilio Recording SID
recording_url	text
caller_sentiment	numeric	Sentiment score
call_topics	text[]	Topics detected in call
category	text	Call category
urgency	text	low, medium, high; default low
follow_up_needed	boolean	Default false
suggested_assignee	text	Who should follow up
created_at	timestamptz

---

voice_prayer_requests (~34 rows)

Prayer requests captured during voice calls or chatbot sessions. Despite the voice_ prefix, also used by chatbot.

Column	Type	Notes
id	uuid	PK
church_id	uuid	FK → premium_churches.church_id
caller_name	text
caller_phone	text
prayer_text	text	The prayer request content
is_confidential	boolean	Default false
status	text	new, acknowledged, prayed; default new
created_at	timestamptz

---

voice_callback_requests (~49 rows)

Callback requests from callers wanting a pastor to call them back. Also used by chatbot.

Column	Type	Notes
id	uuid	PK
church_id	uuid	FK → premium_churches.church_id
caller_name	text
caller_phone	text
reason	text	Reason for callback
preferred_time	text
urgency	text	normal, urgent; default normal
status	text	pending, scheduled, completed; default pending
agreed_day	text	Agreed callback day
agreed_time_window	text	Agreed time window
created_at	timestamptz

---

voice_visitor_contacts (~0 rows)

Visitor intake records — people who identified themselves as visiting or new to the church. Also used by chatbot.

Column	Type	Notes
id	uuid	PK
church_id	uuid	FK → premium_churches.church_id
caller_name	text
caller_phone	text
caller_email	text
reason	text	Why they're contacting
follow_up_requested	boolean	Default false
status	text	new, contacted; default new
created_at	timestamptz

---

voice_event_registrations (~0 rows)

Event RSVPs captured by the voice agent during a call.

Column	Type	Notes
id	uuid	PK
church_id	uuid	FK → premium_churches.church_id
event_title	text
event_date	text
caller_name	text
caller_phone	text
caller_email	text
party_size	integer	Default 1
notes	text
status	text	registered; default registered
created_at	timestamptz

---

voice_demo_requests (~0 rows)

Sales demo requests captured by the ChurchWiseAI sales voice agent.

Column	Type	Notes
id	uuid	PK
caller_name	text
caller_phone	text
church_name	text
interest	text	Product interest
church_size	text
caller_role	text	e.g. "Pastor", "Administrator"
audience_type	text
preferred_day	text
preferred_time_window	text
notes	text
status	text	new, contacted; default new
sms_sent	boolean	Default false
created_at	timestamptz

---

voice_support_requests (~0 rows)

Support tickets captured by the voice agent.

Column	Type	Notes
id	uuid	PK
caller_name	text
caller_phone	text
church_name	text
issue	text	Issue description
status	text	new, resolved; default new
created_at	timestamptz

---

Chatbot

chatbot_agents (~1 row)

Chatbot agent configuration — personality, capabilities, widget appearance. One per organization.

Column	Type	Notes
id	uuid	PK
organization_id	uuid	FK → organizations.id
name	text	Agent display name
slug	text	Unique identifier
description	text
avatar_url	text
personality	jsonb	Tone, formality, emoji usage, response length
system_prompt	text	Custom system prompt override
theological_lens_id	integer	Theological stance
lens_weights	jsonb	Per-lens weighting
capabilities	jsonb	Allowed/blocked topics, scripture search, escalation
widget_config	jsonb	Position, color, branding, welcome message
status	text	draft, active; default draft
is_public	boolean	Default false
is_template	boolean	Default false
use_shared_rag	boolean	Whether to use unified_rag_content
cloned_from_id	uuid	Source agent if cloned
price_type	text
price_cents	integer	Default 0
created_by	uuid
created_at	timestamptz
updated_at	timestamptz

---

chatbot_conversations (~658 rows)

Session-level conversation records for the chatbot widget.

Column	Type	Notes
id	uuid	PK
organization_id	uuid	FK → organizations.id
user_id	uuid	Supabase Auth user (nullable for anonymous)
visitor_name	text
visitor_email	text
session_id	text	Browser session identifier
title	text	Default New Conversation
summary	text	AI-generated summary
started_at	timestamptz
ended_at	timestamptz
last_message_at	timestamptz
message_count	integer	Default 0
visitor_message_count	integer
bot_message_count	integer
satisfaction_rating	integer	1–5
category	text	Conversation category
primary_topic	text
sentiment	text
avg_response_time_ms	integer
agent_id	uuid	FK → chatbot_agents.id
agent_type	text
persona_type	text
user_agent	text
referrer	text
is_active	boolean
is_archived	boolean
metadata	jsonb
created_at	timestamptz
updated_at	timestamptz

---

chatbot_messages (~15 rows)

Individual messages within a chatbot conversation.

Column	Type	Notes
id	uuid	PK
conversation_id	uuid	FK → chatbot_conversations.id
organization_id	uuid	FK → organizations.id
role	text	user, assistant, system
content	text	Message text
intent	text	Detected user intent
faq_category	text	Matched FAQ category
matched_faq_id	uuid	FK → canned_responses.id
rag_content_ids	uuid[]	RAG chunks used in response
confidence_score	numeric	RAG confidence
tokens_used	integer	LLM tokens consumed
response_time_ms	integer
was_helpful	boolean	Feedback flag
was_saved	boolean	Default false
metadata	jsonb
created_at	timestamptz

---

chatbot_api_keys (~0 rows)

API keys for programmatic chatbot access.

Column	Type	Notes
id	uuid	PK
organization_id	uuid	FK → organizations.id
name	text	Key label
key_prefix	text	First 8 chars (for display)
key_hash	text	Hashed full key
permissions	text[]	Default {read}
is_active	boolean	Default true
last_used_at	timestamptz
created_at	timestamptz

---

organization_settings (~18 rows)

Per-organization chatbot and account configuration. One row per organization.

Column	Type	Notes
id	uuid	PK
organization_id	uuid	FK → organizations.id (unique)
branding	jsonb	Logo, colors, custom CSS
security_settings	jsonb	IP whitelist, 2FA, session timeout
notification_preferences	jsonb	Email/webhook notification config
feature_flags	jsonb	Per-org feature toggles
integration_settings	jsonb	Third-party integration config
compliance_settings	jsonb	GDPR, HIPAA, data retention
account_settings	jsonb	Billing email, tax ID, invoicing
chatbot_config	jsonb	Behavior (tone, language), appearance, availability, knowledge base, welcome message
widget_primary_color	text	Default #1B365D
widget_position	text	Default bottom-right
agent_config	jsonb	Agent routing config
agent_tool_config	jsonb	Tool enablement per agent
business_agent_config	jsonb	Business/general agent config
business_tool_config	jsonb	Business agent tool config
doctrinal_overrides	jsonb	Church-specific theological overrides
created_at	timestamptz
updated_at	timestamptz

---

canned_responses (~26 rows)

Per-church FAQ responses for the chatbot. Scoped to one organization.

Column	Type	Notes
id	uuid	PK
church_id	uuid	FK → premium_churches.church_id
organization_id	uuid	FK → organizations.id
question	text
answer	text
category	text
priority	integer	Default 0
exact_response	boolean	If true, serve verbatim
source	text	manual, ai_suggested; default manual
source_conversation_id	text
question_embedding	vector	Semantic search embedding
match_count	integer	Times matched
last_matched_at	timestamptz
is_active	boolean	Default true
denomination_pack	text
created_at	timestamptz
updated_at	timestamptz

---

canned_response_templates (~21 rows)

System-level FAQ templates that can be adopted by churches. Managed by founder/AI pipeline.

Column	Type	Notes
id	uuid	PK
question	text
answer	text
denomination_pack	text	Denomination filter
category	text
agent_type	text	Which agent uses this
agent_confidence_score	numeric	Quality score
agent_quality_notes	text
agent_denomination_match	text
exact_response	boolean	Default false
question_embedding	vector	Semantic search embedding
source_conversation_id	text
source_church_id	uuid	Originating church
status	text	agent_suggested, approved; default agent_suggested
founder_notes	text
adoption_count	integer	How many churches adopted this
total_match_count	integer	Total times matched across churches
ai_improve_count	integer	Times AI improved this
approved_at	timestamptz
created_at	timestamptz
updated_at	timestamptz

---

moderation_violations (~2 rows)

Source of truth for all safety and crisis events. Both the chatbot (logViolation() in chatbot-tools.ts) and the voice agent write here on any safety trigger. The admin Safety tab (ModerationDashboard.tsx) reads from here. The pendingSafetyFlags badge count in the dashboard Overview also counts from this table — do NOT count from voice_callback_requests SAFETY FLAG patterns (that was the old, incorrect approach).

Column	Type	Notes
id	uuid	PK
organization_id	uuid	FK → organizations.id
church_id	uuid	FK → premium_churches.church_id
session_id	text
user_identifier	text	IP or session fingerprint
violation_type	text	crisis, abuse_mild, abuse_severe, spam, predatory
severity_score	numeric
detected_categories	jsonb	OpenAI moderation categories
original_message	text	The offending message
action_taken	text	What the system did
country_code	text
created_at	timestamptz

---

user_restrictions (~0 rows)

Active user bans or restrictions from the chatbot.

Column	Type	Notes
id	uuid	PK
organization_id	uuid	FK → organizations.id
church_id	uuid	FK → premium_churches.church_id
user_identifier	text	IP or session fingerprint
restriction_type	text	ban, rate_limit, etc.
reason	text
violation_id	uuid	FK → moderation_violations.id
expires_at	timestamptz	Null = permanent
created_by	uuid
created_at	timestamptz

---

Congregation Care

congregation_care_members (~6 rows)

Congregation members who have opted into the care/outreach communication system.

Column	Type	Notes
id	uuid	PK
church_id	uuid	FK → premium_churches.church_id
name	text
phone	text
email	text
topics	text[]	Care topics of interest; default {}
sms_opted_in	boolean	Default false
email_opted_in	boolean	Default false
sms_opted_out	boolean	Default false
delivery_preference	text	sms, email, any; default any
created_at	timestamptz

---

congregation_care_messages (~0 rows)

Outgoing care messages sent to congregation members.

Column	Type	Notes
id	uuid	PK
church_id	uuid	FK → premium_churches.church_id
sent_by	text	Who triggered the send
topic	text	Message topic
subject	text	Email subject
body	text	Message content
sms_count	integer	Number sent via SMS
email_count	integer	Number sent via email
created_at	timestamptz

---

Pastoral Care Response Library

pastoral_care_responses (~597 rows)

Pre-researched, CPE-quality pastoral care response patterns. Used by both chatbot (pastoral-care-library.ts) and voice agent (Care Agent) to serve seminary-quality empathetic responses instead of LLM improvisation. Organized by category → subcategory → response_type.

Column	Type	Notes
id	uuid	PK
category	text	grief_loss, illness_health, addiction_recovery, anxiety_mental_health, relationship_crisis, spiritual_crisis, additional_crisis
subcategory	text	~80 subcategories (e.g. death_of_spouse, suicidal_ideation, domestic_violence)
response_type	text	CHECK: opener, followup, feeling_articulation, bridge_to_human, avoid, context_note, framework, fallback_bridge
tradition	text	Default universal; can be tradition-specific
content	text	The actual response text or instruction
avoid_reason	text	For avoid type: WHY this phrase is harmful
safety_level	text	CHECK: standard, elevated, life_safety
safety_resources	text[]	Crisis hotlines (US + Canadian)
professional_referral	boolean	Default false; true if bridge suggests counselor
theological_sensitivity	text	CHECK: low, medium, high, critical
first_disclosure	boolean	Default false; true for "I've never told anyone"
source_principle	text	Named CPE framework (Ghost Sentence Rule, OARS, etc.)
tags	text[]	Search tags
sort_order	integer	Ordering within (category, subcategory, response_type)
is_active	boolean	Default true
created_at	timestamptz
updated_at	timestamptz

Consumed by: Chatbot src/lib/pastoral-care-library.ts → loadCareLibraryContext(). Voice agent session.py (future per-turn RAG).

---

tradition_care_context (~22 rows)

Per-tradition context blocks that calibrate pastoral care responses. Includes clergy titles, care structures, sacramental rules, theological notes, bridge language, sensitive topic handling, and tradition-specific avoids. 22 traditions: universal + 21 specific.

Column	Type	Notes
id	uuid	PK
tradition_key	text	universal, southern_baptist, catholic, orthodox, methodist, anglican, mennonite, seventh_day_adventist, churches_of_christ, unitarian_universalist, etc.
tradition_name	text	Display name (e.g. "Roman Catholic", "Southern Baptist Convention")
clergy_titles	text[]	All valid titles (e.g. {Father, Monsignor, Deacon})
clergy_default_title	text	Most common title (e.g. "Father" for Catholic)
care_structures	jsonb	Primary/secondary/lay care providers
sacramental_rules	text[]	What AI must NEVER attempt (confession, anointing, etc.)
theological_notes	text[]	Key theology that shapes care (suffering, saints, etc.)
bridge_language_overrides	jsonb	Tradition-specific bridge phrasing
sensitive_topic_notes	jsonb	Per-topic notes (divorce, LGBTQ+, suicide, etc.)
tradition_avoids	text[]	Phrases NEVER to say for this tradition
context_prompt	text	200-400 word instruction block for the Care Agent
is_active	boolean	Default true
created_at	timestamptz
updated_at	timestamptz

Consumed by: Voice agent session.py → load_tradition_care_context(). Chatbot pastoral-care-library.ts → loadCareLibraryContext().

Mapping: denominations.tradition_key (64 rows) maps each denomination to a tradition context. Python: tradition_care_contexts.py → DENOMINATION_TO_TRADITION_KEY. TypeScript: pastoral-care-library.ts → DENOMINATION_TO_TRADITION_KEY.

---

Knowledge & Product Config

product_knowledge (~75 rows)

Runtime Q&A knowledge base consumed by both the chatbot and voice agent at session initialization. Edit via SQL — changes take effect immediately.

Column	Type	Notes
id	uuid	PK
category	text	pewsearch, churchwiseai, billing, account, troubleshooting, getting_started, integrations, policies
question	text
answer	text
keywords	text[]	Semantic search keywords
is_active	boolean	Default true
priority	integer	0–10; higher = more important
created_at	timestamptz
updated_at	timestamptz

Critical: This is the live product knowledge used by AI agents at runtime. Updates take effect immediately — no redeploy needed.

---

agentic_tools (~39 rows)

Registry of all available agent tools across chat and voice channels.

Column	Type	Notes
id	text	PK (slug, e.g. prayer_request)
name	text	Display name
description	text
category	text	Tool category
tier	text	Minimum plan required
chatbot_default	boolean	Enabled for chatbot by default
voice_default	boolean	Enabled for voice by default
chatbot_only	boolean	Chat-only tool
voice_only	boolean	Voice-only tool
agent_group	text	Which agent group uses this
is_llm_powered	boolean	Whether requires LLM call
sort_order	integer	Display order
created_at	timestamptz
updated_at	timestamptz

---

pricing_tiers (~38 rows)

Plan definitions for all products (ChurchWiseAI, SermonWise, ShareWiseAI). Source for organization_subscriptions tier lookups.

Column	Type	Notes
id	uuid	PK
name	varchar	Plan name
description	text
price_cents	integer	Monthly price in cents
billing_period	varchar	monthly, annual; default monthly
stripe_price_id	varchar	Stripe Price ID
stripe_product_id	varchar	Stripe Product ID
app_access_json	jsonb	Which apps are included
features_json	jsonb	Feature list
sermon_generation_limit	integer	SermonWise monthly limit
chatbot_conversation_limit	integer	Chatbot monthly limit
max_users	integer	Team seats
is_organizational	boolean	Org-level plan
is_featured	boolean	Highlighted on pricing page
trial_days	integer	Default 14
is_founder_pricing	boolean	Founder discount plan
standard_price_cents	integer	Regular price (for founders)
founder_discount_cents	integer	Discount amount
founder_enrollment_cutoff	integer	Max founder members
gamification_multiplier	numeric	Default 1.00
sort_order	integer
is_active	boolean
created_at	timestamptz
updated_at	timestamptz

Note: C:\dev\PRICING.md and knowledge/data/pricing.yaml are the canonical source of truth for pricing. Treat this table as operational, not authoritative.

---

church_document_uploads (~0 rows)

Documents uploaded to a church's knowledge base for RAG retrieval.

Column	Type	Notes
id	uuid	PK
organization_id	uuid	FK → organizations.id
file_name	text
file_type	text	MIME type
file_size	integer	Bytes
file_url	text	Supabase Storage URL
processing_status	varchar	pending, processing, complete, failed
processing_error	text
moderation_status	text	pending, approved, rejected
moderation_categories	jsonb
uploaded_by	uuid
processed_at	timestamptz
metadata	jsonb
created_at	timestamptz

---

church_document_chunks (~0 rows)

Text chunks extracted from uploaded documents, with embeddings for semantic search.

Column	Type	Notes
id	uuid	PK
document_id	uuid	FK → church_document_uploads.id
organization_id	uuid	FK → organizations.id
chunk_index	integer	Position within document
content	text	Chunk text
content_embedding	vector	pgvector embedding
metadata	jsonb
created_at	timestamptz

---

church_local_resources (~19 rows)

Local community resources (food banks, shelters, etc.) associated with a church for the voice/chatbot to reference.

Column	Type	Notes
id	uuid	PK
church_id	uuid	FK → premium_churches.church_id
category	USER-DEFINED	Enum of resource categories
name	text
description	text
phone	text
address	text
website	text
hours	text
notes	text
is_critical	boolean	Priority resource
is_active	boolean
sort_order	integer	Default 0
created_at	timestamptz
updated_at	timestamptz

---

church_rag_content (~53 rows)

Structured knowledge-base content for a church, used in RAG retrieval. Distinct from document chunks — these are manually authored/curated entries.

Column	Type	Notes
id	uuid	PK
organization_id	uuid	FK → organizations.id
category_id	uuid	Optional category grouping
title	text
content	text
content_embedding	vector	pgvector embedding
search_text	text	Denormalized search text
metadata	jsonb
tags	text[]	Default {}
version	integer	Default 1
is_active	boolean	Default true
created_by	uuid
approved_by	uuid
approved_at	timestamptz
created_at	timestamptz
updated_at	timestamptz

---

organization_knowledge_base (~3 rows)

Per-organization FAQ entries for the chatbot (legacy path — prefer canned_responses).

Column	Type	Notes
id	uuid	PK
organization_id	uuid	FK → organizations.id
question	text
answer	text
category	text
priority	integer	Default 0
exact_response	boolean	Default false
active	boolean	Default true
created_at	timestamptz
updated_at	timestamptz

---

agent_knowledge_bases (~2 rows)

Knowledge base configuration for a specific chatbot agent instance.

Column	Type	Notes
id	uuid	PK
agent_id	uuid	FK → chatbot_agents.id
name	text
description	text
content_types	text[]	faq, document, url; default all
chunk_size	integer	Default 512
chunk_overlap	integer	Default 50
similarity_threshold	numeric	Default 0.75
document_count	integer	Default 0
training_status	text	idle, training, ready
last_trained_at	timestamptz
created_at	timestamptz
updated_at	timestamptz

---

Organizations (ShareWiseAI Multi-Org)

organizations (~16 rows)

Top-level organization records for ShareWiseAI and the B2C app system. Each church that signs up gets an organization.

Column	Type	Notes
id	uuid	PK
name	text
slug	text	Unique URL slug
owner_id	uuid	FK → Supabase Auth users.id
organization_type	text	church, ministry; default church
subscription_tier	text	Default free
verification_status	text	pending, verified; default pending
verification_method	text
verification_token	uuid	Auto-generated
verified_at	timestamptz
verified_by	uuid
contact_email	text
contact_phone	text
street_address	text
website	text
description	text
logo_url	text
theological_lens_preferences	text[]
primary_theological_lens_id	integer
theological_lens_weights	jsonb
doctrinal_positions	jsonb
doctrinal_overrides	jsonb	Default {}
custom_exceptions	jsonb
church_faqs	jsonb
church_setup_data	jsonb	Onboarding form data
church_setup_completed	boolean	Default false
church_setup_completed_at	timestamptz
trial_starts_at	timestamptz
trial_ends_at	timestamptz
trial_status	text	Default active
trial_converted_at	timestamptz
setup_completed	boolean	Default false
setup_steps_completed	jsonb	Default []
is_internal_demo	boolean	Default false
max_seats	integer	Default 5
settings	jsonb	Default {}
metadata	jsonb	Default {}
created_at	timestamptz
updated_at	timestamptz

---

organization_members (~0 rows)

Users who belong to an organization (team members).

Column	Type	Notes
id	uuid	PK
organization_id	uuid	FK → organizations.id
user_id	uuid	FK → Supabase Auth users.id
role	varchar	owner, admin, member; default member
invited_by	uuid
invited_at	timestamptz
joined_at	timestamptz
is_active	boolean	Default true
created_at	timestamptz

---

organization_permissions (~0 rows)

Flat permission grants per organization member role.

Column	Type	Notes
id	uuid	PK
organization_id	uuid	FK → organizations.id
role	text
can_invite_members	boolean
can_remove_members	boolean
can_change_roles	boolean
can_enable_apps	boolean
can_configure_apps	boolean
can_edit_profile	boolean
can_manage_billing	boolean
can_delete_organization	boolean
can_create_content	boolean	Default true
can_edit_own_content	boolean	Default true
can_edit_all_content	boolean
can_delete_content	boolean
created_at	timestamptz
updated_at	timestamptz

---

organization_roles (~0 rows)

Custom role definitions for an organization.

Column	Type	Notes
id	uuid	PK
organization_id	uuid	FK → organizations.id
role_name	text
role_description	text
permissions	jsonb	Default {}
is_custom	boolean	Default true
created_by	uuid
created_at	timestamptz
updated_at	timestamptz

---

organization_subscriptions (~0 rows)

Stripe subscription records for organization-level plans (ShareWiseAI, SermonWise via organizations).

Column	Type	Notes
id	uuid	PK
organization_id	uuid	FK → organizations.id
pricing_tier_id	uuid	FK → pricing_tiers.id
stripe_subscription_id	varchar
stripe_customer_id	varchar
status	varchar	active, trialing, canceled; default active
seats_included	integer	Default 1
seats_used	integer	Default 0
billing_email	varchar
admin_user_id	uuid
current_period_start	timestamptz
current_period_end	timestamptz
trial_start	timestamptz
trial_end	timestamptz
cancel_at_period_end	boolean	Default false
canceled_at	timestamptz
ended_at	timestamptz
metadata_json	jsonb
created_at	timestamptz
updated_at	timestamptz

---

organization_app_access (~0 rows)

Which apps (chatbot, voice, social, sermons) an organization has access to.

Column	Type	Notes
id	uuid	PK
organization_id	uuid	FK → organizations.id
app_id	USER-DEFINED	Enum of app identifiers
has_access	boolean	Default true
enabled_at	timestamptz
enabled_by	uuid
created_at	timestamptz
updated_at	timestamptz

---

organization_tool_access (~0 rows)

Per-tool access grants for an organization, with usage tracking.

Column	Type	Notes
id	uuid	PK
organization_id	uuid	FK → organizations.id
tool_slug	text	FK → agentic_tools.id
pricing_tier_id	uuid	FK → pricing_tiers.id
subscription_item_id	uuid
access_level	text	full, limited; default full
usage_count	integer	Default 0
usage_limit	integer	Null = unlimited
trial_expires_at	timestamptz
enabled_at	timestamptz
disabled_at	timestamptz
created_at	timestamptz
updated_at	timestamptz

---

organization_tool_configs (~0 rows)

Per-tool configuration for an organization (custom settings per tool).

Column	Type	Notes
id	uuid	PK
organization_id	uuid	FK → organizations.id
tool_slug	text
config	jsonb	Default {}
enabled	boolean	Default true
created_at	timestamptz
updated_at	timestamptz

---

Social (ShareWiseAI)

ShareWiseAI is code-complete but not yet deployed (OAuth pending as of 2026-03-25). All social tables are empty.

social_platforms (~0 rows)

Platform catalog (Facebook, Instagram, etc.) with OAuth and media capability configs.

Column	Type	Notes
id	text	PK (e.g. facebook, instagram)
display_name	text
icon_name	text
base_api_url	text
max_text_length	integer
max_images	integer	Default 1
max_video_length_sec	integer
supported_media_types	text[]
supports_stories	boolean
supports_reels	boolean
supports_scheduling	boolean
supports_analytics	boolean
oauth_provider	text
oauth_authorize_url	text
oauth_token_url	text
required_scopes	text[]
is_active	boolean	Default true
config	jsonb
created_at	timestamptz
updated_at	timestamptz

---

oauth_connections (~0 rows)

Generic OAuth token store for any provider (used by ShareWiseAI and potentially others).

Column	Type	Notes
id	uuid	PK
user_id	uuid	FK → Supabase Auth users.id
provider	text
access_token	text	Encrypted
refresh_token	text	Encrypted
token_expires_at	timestamptz
scopes	text[]
email	text
created_at	timestamptz
updated_at	timestamptz

---

social_accounts (~0 rows)

Connected social media accounts (pages, profiles) for a church.

Column	Type	Notes
id	uuid	PK
user_id	uuid	FK → Supabase Auth users.id
church_id	uuid	FK → premium_churches.church_id
property_id	text	ShareWiseAI workspace
platform_id	text	FK → social_platforms.id
platform_account_id	text	Platform's account ID
account_name	text
account_handle	text
account_avatar_url	text
account_type	text	page, profile; default page
access_token	text	Encrypted
refresh_token	text	Encrypted
token_expires_at	timestamptz
token_scopes	text[]
status	text	active, expired; default active
last_error	text
last_synced_at	timestamptz
follower_count	integer	Default 0
following_count	integer	Default 0
post_count	integer	Default 0
config	jsonb
created_at	timestamptz
updated_at	timestamptz

---

social_posts (~0 rows)

AI-generated social posts (content queue items tied to blog articles).

Column	Type	Notes
id	uuid	PK
article_id	uuid	FK → articles.id
article_title	text
article_slug	text
article_url	text
platform	text	Target platform
content	text	Post text
hashtags	text[]	Default {}
image_prompt	text
suggested_image_url	text
status	text	pending, approved, rejected, posted; default pending
rejection_reason	text
scheduled_for	timestamptz
posted_at	timestamptz
post_url	text
reviewed_at	timestamptz
reviewed_by	uuid
generation_prompt	text
model_used	text	Default claude-sonnet-4-20250514
regeneration_count	integer	Default 0
engagement_data	jsonb
created_at	timestamptz
updated_at	timestamptz

---

social_post_platform_results (~0 rows)

Actual publishing results and engagement metrics for a post on a specific platform account.

Column	Type	Notes
id	uuid	PK
post_id	uuid	FK → social_posts.id
account_id	uuid	FK → social_accounts.id
platform_id	text	FK → social_platforms.id
platform_post_id	text	Platform's ID for the post
platform_post_url	text
status	text	pending, published, failed; default pending
error_message	text
retry_count	integer	Default 0
published_at	timestamptz
impressions	integer	Default 0
reach	integer	Default 0
engagements	integer	Default 0
likes	integer	Default 0
comments	integer	Default 0
shares	integer	Default 0
saves	integer	Default 0
clicks	integer	Default 0
video_views	integer	Default 0
raw_metrics	jsonb
metrics_updated_at	timestamptz
created_at	timestamptz
updated_at	timestamptz

---

social_analytics (~0 rows)

Daily analytics snapshots per connected social account.

Column	Type	Notes
id	uuid	PK
account_id	uuid	FK → social_accounts.id
platform_id	text
snapshot_date	date
follower_count	integer
follower_change	integer
posts_count	integer
impressions	integer
reach	integer
engagements	integer
clicks	integer
profile_visits	integer
raw_data	jsonb
created_at	timestamptz

---

social_audit_log (~0 rows)

Immutable audit trail for ShareWiseAI actions.

Column	Type	Notes
id	uuid	PK
user_id	uuid
church_id	uuid
property_id	text
action	text
details	jsonb
ip_address	text
user_agent	text
created_at	timestamptz

Safety: Audit log — never delete rows.

---

social_campaigns (~0 rows)

Named campaigns grouping related social posts.

Column	Type	Notes
id	uuid	PK
user_id	uuid
church_id	uuid
property_id	text
name	text
description	text
start_date	date
end_date	date
status	text	draft, active, completed; default draft
goal_type	text
goal_target	jsonb
tags	text[]
color	text	Default #D4AF37
created_at	timestamptz
updated_at	timestamptz

---

social_content_queue (~0 rows)

Community feed posts (internal — not the same as social_posts).

Column	Type	Notes
id	uuid	PK
user_id	uuid
group_id	uuid
post_type	text
content	text
media_urls	text[]
sermon_id	uuid	FK → sermons.id
bible_passage	text
reaction_count	integer	Default 0
comment_count	integer	Default 0
share_count	integer	Default 0
visibility	text	public, members; default public
is_pinned	boolean	Default false
created_at	timestamptz
updated_at	timestamptz

---

social_schedules (~0 rows)

Recurring post schedule definitions for auto-publishing.

Column	Type	Notes
id	uuid	PK
user_id	uuid
church_id	uuid
property_id	text
name	text
description	text
schedule_type	text	recurring, one_time, etc.
timezone	text	Default America/Chicago
recurrence_config	jsonb	Cron or interval config
auto_fill_enabled	boolean	Default false
auto_fill_source	text
is_active	boolean	Default true
last_triggered_at	timestamptz
created_at	timestamptz
updated_at	timestamptz

---

social_subscriptions (~0 rows)

ShareWiseAI billing subscriptions per user/church.

Column	Type	Notes
id	uuid	PK
user_id	uuid
church_id	uuid
property_id	text
tier	text	free, pro, business, agency; default free
stripe_customer_id	text
stripe_subscription_id	text
status	text	Default active
posts_used_this_month	integer	Default 0
ai_generations_used_this_month	integer	Default 0
usage_reset_at	timestamptz	Resets at month boundary
workspace_count	integer	Default 1
current_period_start	timestamptz
current_period_end	timestamptz
trial_ends_at	timestamptz
config	jsonb
created_at	timestamptz
updated_at	timestamptz

---

social_oauth_nonces (~0 rows)

Short-lived OAuth state tokens for the social media connect flow (CSRF protection).

Column	Type	Notes
id	uuid	PK
nonce	text	Random state value
platform	text
owner_type	text	user, church
owner_id	text
expires_at	timestamptz
created_at	timestamptz

---

Sermons (SermonWise)

sermons (~36 rows)

AI-generated sermon drafts created by SermonWise subscribers.

Column	Type	Notes
id	uuid	PK
user_id	uuid	FK → Supabase Auth users.id
organization_id	uuid	FK → organizations.id
title	text
scripture_reference	text
sermon_style	text
content	jsonb	Structured sermon content
status	text	draft, published; default draft
is_public	boolean	Default false
date_preached	date
notes	text
series_id	uuid	FK → sermon series
theological_lens_id	integer
target_audience	text
parent_sermon_id	uuid	For variations
voice_style	jsonb
created_at	timestamptz
updated_at	timestamptz

---

shared_sermons (~35 rows)

Publicly shared sermons for the community discovery feed.

Column	Type	Notes
id	uuid	PK
sermon_id	uuid	FK → sermons.id
user_id	uuid	FK → Supabase Auth users.id
author_name	text	Default Anonymous Pastor
share_type	text	public, lens_only; default public
title	text
scripture_reference	text
description	text
tradition	text
sermon_style	text
tags	text[]	Default {}
primary_lens_id	integer
method_id	integer
introduction_preview	text
key_points	jsonb
word_count	integer
view_count	integer	Default 0
copy_count	integer	Default 0
like_count	integer	Default 0
quality_score	numeric
ai_feedback	text
user_rating	numeric
moderation_status	text	pending, approved, rejected; default pending
last_viewed_at	timestamptz
created_at	timestamptz
updated_at	timestamptz

---

sermon_generation_usage (~1 row)

Monthly generation counter per user for SermonWise rate limiting.

Column	Type	Notes
id	uuid	PK
user_id	uuid	FK → Supabase Auth users.id
month_year	text	Format YYYY-MM
generation_count	integer	Default 0
last_generation_at	timestamptz
created_at	timestamptz
updated_at	timestamptz

---

app_trials (~5 rows)

Trial records for any app (chatbot, sermons, voice).

Column	Type	Notes
id	uuid	PK
user_id	uuid	FK → Supabase Auth users.id
organization_id	uuid	FK → organizations.id
app_id	text	Which app is being trialed
bundle_type	text
trial_started_at	timestamptz	Default now()
trial_ends_at	timestamptz	Default now() + 14 days
is_converted	boolean	Default false
converted_at	timestamptz
created_at	timestamptz

---

user_subscriptions (~1 row)

User-level Stripe subscriptions (SermonWise individual users, not org-level).

Column	Type	Notes
id	uuid	PK
user_id	uuid	FK → Supabase Auth users.id
pricing_tier_id	uuid	FK → pricing_tiers.id
stripe_subscription_id	varchar
stripe_customer_id	varchar
status	varchar	active, trialing, canceled; default active
current_period_start	timestamptz
current_period_end	timestamptz
trial_start	timestamptz
trial_end	timestamptz
cancel_at_period_end	boolean	Default false
canceled_at	timestamptz
ended_at	timestamptz
is_founder_member	boolean	Default false
founder_enrolled_at	timestamptz
locked_price_cents	integer	Founder locked price
founder_member_number	integer	Sequential founder number
created_at	timestamptz
updated_at	timestamptz

---

Operational

articles (~54 rows)

Blog articles for churchwiseai.com. Rendered at /blog/[slug].

Column	Type	Notes
id	uuid	PK
title	text
slug	text	Unique URL slug
content	text	HTML content
excerpt	text
status	text	draft, published; default draft
category	jsonb	Category object
primary_category_id	integer
content_type_id	integer
category_migration_status	text	Default legacy
tags	jsonb	Default []
author	jsonb	Author object
featured_image_url	text
featured_image_alt	text
read_time	integer	Minutes; default 0
cta	jsonb	Call-to-action block
seo	jsonb	SEO title/description/OG
table_of_contents	jsonb	Default []
sync_metadata	jsonb	External sync info
published_at	timestamptz
created_at	timestamptz
updated_at	timestamptz

---

email_subscribers (~6 rows)

Email list subscribers (lead capture, blog signups, etc.).

Column	Type	Notes
id	uuid	PK
email	text	Unique
name	text
source	text	Signup source (e.g. blog, landing_page)
status	text	active, unsubscribed; default active
tags	text[]	Default {}
church_size	text
city	text
state_code	text
interests	text[]	Default {}
subscribed_at	timestamptz
last_email_sent_at	timestamptz
email_open_count	integer	Default 0
email_click_count	integer	Default 0
converted_to_customer	boolean	Default false
conversion_date	timestamptz
metadata	jsonb
created_at	timestamptz
updated_at	timestamptz

---

chat_usage_tracking (~93 rows)

Per-response LLM usage tracking for cost monitoring.

Column	Type	Notes
id	uuid	PK
church_id	uuid	FK → premium_churches.church_id
session_id	text
agent_type	text
response_source	text	llm, canned, rag
input_tokens	integer	Default 0
output_tokens	integer	Default 0
estimated_cost_usd	numeric	Default 0
model	text
canned_response_id	uuid	FK → canned_responses.id if canned
created_at	timestamptz

---

tool_invocations (~256 rows)

Log of every agent tool call across chat and voice channels.

Column	Type	Notes
id	uuid	PK
church_id	uuid	FK → premium_churches.church_id
tool_id	text	FK → agentic_tools.id
agent_type	text
persona_type	text
channel	text	chat, voice
session_id	text
created_at	timestamptz

---

ops_errors (~0 rows)

System error log — deduped by fingerprint, with occurrence counting.

Column	Type	Notes
id	uuid	PK
source	text	Error source system
severity	text	p0–p3; default p1
property	text	Which site/app
fingerprint	text	Deduplication key (unique)
message	text
stack	text
route	text	URL route
repo	text	Git repo
http_status	integer
service	text
raw_payload	jsonb
status	text	new, acknowledged, resolved; default new
occurrences	integer	Default 1
first_seen_at	timestamptz
last_seen_at	timestamptz
alert_sent_at	timestamptz
dispatched_at	timestamptz
resolved_at	timestamptz
fix_commit	text
fix_pr_url	text
fix_summary	text
created_at	timestamptz

---

ops_quota_snapshots (~1,452 rows)

Time-series quota/metric snapshots for operational monitoring.

Column	Type	Notes
id	uuid	PK
service	text	Service name
metric	text	Metric name
value	numeric
recorded_at	timestamptz	Default now()

---

founder_action_items (~0 rows)

Action items requiring manual founder intervention. Surfaced in the admin FounderDashboard.

Column	Type	Notes
id	uuid	PK
action_id	text	Unique stable identifier
title	text
priority	text	P0, P1, P2
description	text
location	text	Where to act (URL or system)
reason	text	Why this needs attention
status	text	pending, completed; default pending
created_by	text	Default agent
remind_count	integer	Default 0
last_reminded_at	timestamptz
created_at	timestamptz
completed_at	timestamptz

---

founder_google_tokens (~0 rows)

Google OAuth tokens for the founder's Gmail/Calendar integrations used by agents.

Column	Type	Notes
id	uuid	PK
email	text	Google account email
access_token	text
refresh_token	text
expires_at	timestamptz
scopes	text	Granted scopes
created_at	timestamptz
updated_at	timestamptz

Safety: Contains live OAuth credentials. Never expose or bulk-read.

---

golive_checklist_progress (~8 rows)

Launch checklist progress state saved per user session.

Column	Type	Notes
id	uuid	PK
user_identifier	text	Session/user ID
checklist_version	text	Default 285
progress_data	jsonb	Check states per item
notes_data	jsonb	Per-item notes
custom_tests	jsonb	User-added test items; default []
issue_mapping	jsonb	Test-to-issue links
last_saved_at	timestamptz
created_at	timestamptz
updated_at	timestamptz

---

known_issues (~217 rows)

Issue tracker for known bugs and feature gaps.

Column	Type	Notes
id	uuid	PK
title	text
description	text
category	varchar
status	varchar	reported, in_progress, resolved; default reported
priority	varchar	low, medium, high, critical; default medium
affected_app	varchar
upvote_count	integer	Default 1
reporter_email	text
reporter_name	text
admin_notes	text
fix_details	text
feedback_count	integer	Default 0
screenshot_paths	jsonb	Default []
device	text
resolved_at	timestamptz
created_at	timestamptz
updated_at	timestamptz

---

known_issue_history (~196 rows)

Status change history for known_issues.

Column	Type	Notes
id	uuid	PK
issue_id	uuid	FK → known_issues.id
old_status	text
new_status	text
changed_by	text	Default system
change_reason	text
metadata	jsonb
created_at	timestamptz

---

response_reviews (~283 rows)

Chatbot responses flagged for human review and potential promotion to canned responses.

Column	Type	Notes
id	uuid	PK
church_id	uuid	FK → premium_churches.church_id
conversation_id	text
user_message	text
ai_response	text
agent_type	text
model	text
review_status	text	pending, approved, rejected; default pending
edited_answer	text	Improved version of response
reviewer_notes	text
promoted_to_canned_id	uuid	FK → canned_responses.id if promoted
detected_language	varchar	Default en
created_at	timestamptz
reviewed_at	timestamptz

---

rate_limit_entries (~24 rows)

Sliding-window rate limit counters keyed by identifier.

Column	Type	Notes
key	text	PK — rate limit identifier (IP, user, etc.)
window_start	timestamptz	Window start time
count	integer	Request count in window

---

telemetry_breadcrumbs (~26,032 rows)

Frontend breadcrumb events for session replay and debugging. High volume — prune periodically.

Column	Type	Notes
id	uuid	PK
event_id	text	Groups breadcrumbs to a telemetry event
type	text	Breadcrumb type
category	text
level	text	info, warning, error; default info
message	text
data	jsonb	Event data
timestamp	timestamptz
sequence_number	integer	Order within event

---

telemetry_events (~1,846 rows)

Frontend telemetry events (errors, performance, user actions).

Column	Type	Notes
id	uuid	PK
event_id	text	Unique event identifier
project_id	text	Which project
environment	text	development, production; default development
release	text	App version/release
platform	text	Default javascript
level	text
event_type	text
message	text
title	text
culprit	text	File/function that caused the event
timestamp	timestamptz
time_spent	integer
tags	jsonb
extra	jsonb
contexts	jsonb	Browser, OS, runtime context
user_id	text
user_email	text
user_name	text
request_url	text
request_method	text
request_headers	jsonb
exception_type	text
exception_value	text
stack_trace	jsonb
performance_metrics	jsonb
status	text	unresolved, resolved; default unresolved
assigned_to	text
fingerprint	text[]
checksum	text
created_at	timestamptz
updated_at	timestamptz

---

telemetry_performance (~476 rows)

Core Web Vitals and page load metrics.

Column	Type	Notes
id	uuid	PK
event_id	text	FK → telemetry_events.event_id
first_contentful_paint	numeric	ms
largest_contentful_paint	numeric	ms
first_input_delay	numeric	ms
cumulative_layout_shift	numeric	Score
navigation_start	numeric
dom_content_loaded	numeric	ms
load_complete	numeric	ms
custom_metrics	jsonb
page_url	text
user_agent	text
viewport_width	integer
viewport_height	integer
connection_type	text
created_at	timestamptz

---

Inbox (Compose + Safety)

inbox_outbox_messages (~0 rows)

One row per compose-message send (email, SMS, or internal note) across every inbox card type (prayer, visitor, callback, call). Multi-vertical design — tenant_id is premium_churches.id in v1; future verticals write their own tenant ID. Added 2026-05-12 (migrations/2026-05-12-inbox-outbox-messages.sql, FA-082/Inbox compose feature).

Column	Type	Notes
id	uuid	PK
tenant_id	uuid	Owning tenant (premium_churches.id in v1). Not declared FK — multi-vertical design.
vertical	text	Vertical discriminator: church | funeral | vet | dental | restaurant | law | real_estate. Default church.
item_type	text	Source inbox card type: prayer | visitor | callback | call (CHECK constraint)
item_id	uuid	UUID of the source inbox row (soft/discriminated ref, not FK)
channel	text	Delivery channel: email | sms | internal_note (CHECK constraint)
to_address	text	e164 for SMS; email address for email; NULL for internal_note
subject	text	Email subject line (email only; NULL for SMS/internal_note)
body	text	Message content (1–4000 chars enforced by API layer)
composed_by_member_id	uuid	church_team_members.id if a member token was used; NULL for admin-token callers
composed_by_name	text	Display name of the composer
composed_at	timestamptz	Default NOW()
status	text	State machine: queued | sending | sent | failed | suppressed (CHECK constraint). Default queued.
status_detail	text	Human-readable failure reason or suppression note
sent_at	timestamptz	When the provider accepted the message
provider	text	resend | twilio | null for internal_note
provider_message_id	text	Resend message-ID or Twilio SID

Indexes:

• inbox_outbox_messages_tenant_item_idx — (tenant_id, item_type, item_id, composed_at DESC) — primary retrieval for "all messages for this tenant+item in reverse chron order"
• inbox_outbox_messages_status_idx — partial index on (status, composed_at) WHERE status IN ('queued','failed') — for future outbox cron retry processor

Access: Server-only writes via Next.js API routes (src/app/api/inbox/compose/route.ts). No direct client access. service_role has SELECT/INSERT/UPDATE.

---

inbox_item_state (rows vary)

Per-item per-viewer state for inbox cards: read receipts, assignments, Safety resolution. The resolved_by_members column was added 2026-05-12 for per-viewer Safety resolution (PR #419, migrations/2026-05-12-inbox-item-state-resolved-by-members.sql).

Selected columns of note (full schema from migration file):

Column	Type	Notes
id	uuid	PK
church_id	uuid	FK → premium_churches.church_id
item_type	text	e.g. safety, prayer, visitor, callback
item_id	uuid	UUID of the source inbox row
resolved_at	timestamptz	Scalar: timestamp of first/last resolve (backward-compat)
resolved_by	uuid	Scalar: UUID of first/last resolver (backward-compat)
resolved_by_members	jsonb	Per-viewer array: [{member_id, resolved_at}, ...]. NULL until a viewer resolves. Each member can resolve independently. Admin-token callers write member_id: null. Populated by resolve_inbox_safety_item() RPC.
updated_at	timestamptz

Per-viewer Safety: resolved_by_members replaces the scalar-only scheme for multi-member churches. One team member resolving no longer hides the safety card from all others. The scalar resolved_at + resolved_by columns are preserved for one-release backward-compat.

---

Safety Rules

The following tables require special care:

Table	Rule
premium_churches	Only ~6 rows exist (founder test accounts). Every mutation is consequential. NEVER bulk delete. Confirm before any UPDATE/DELETE.
product_knowledge	Consumed by AI agents at runtime. Changes take effect immediately — write carefully.
unified_rag_content	327K rows of irreplaceable church content (owned by PewSearch codebase, listed in pewsearch schema doc). NEVER bulk delete from any codebase.
church_access_audit	Immutable security audit log. Never delete rows.
social_audit_log	Immutable audit log. Never delete rows.
known_issue_history	Historical record. Never delete rows.
voice_call_logs	Production call transcripts and summaries. Never bulk delete.
telemetry_breadcrumbs	High volume (~26K rows). Safe to prune old rows, but use date-bounded DELETEs only.
founder_google_tokens	Contains live OAuth credentials. Never expose in logs or API responses.
ops_errors	Deduplicated by fingerprint — use UPSERT pattern, not blind INSERT.
church_document_uploads / church_document_chunks	Always delete chunks before deleting the parent upload (no cascade).
canned_response_templates	Shared system templates — changes affect all churches. Founder approval required before modifying.
inbox_outbox_messages	Audit log of all compose-message sends. Never delete sent rows — they are the paper trail. Use suppressed status for intentional no-ops.
inbox_item_state	Per-viewer state including Safety resolutions. Never delete rows — use RPC resolve_inbox_safety_item() for writes.