Knowledge > Architecture > Database Schema > PewSearch Tables

Database Schema — PewSearch Directory

Overview

These tables form the PewSearch church directory — 261,139 total church records (218,369 directory_visible=true, 42,770 hidden) sourced primarily from Outscraper/Google Places. All tables live in the shared Supabase instance (wrwkszmobuhvcfjipasi). No staging environment exists — all queries hit production.

CRITICAL safety rules:

• NEVER bulk delete from churches — this is the 218K+ directory that is the foundation of the entire business.
• ALWAYS filter WHERE directory_visible = true when showing counts or listings to users. The raw row count (261K) includes hidden/suppressed records. The correct visible count is ~218K.
• NEVER display or log lgbtq_inclusive or inclusivity_signals fields in user-facing UI without editorial review.

PewSearch Directory Enrichment

---

Category: Directory Core

churches (261,139 rows total; 218,369 directory_visible = true)

The central table. Every church listing in the PewSearch directory is a row here. Sourced primarily via Outscraper (Google Places), supplemented by website deep-crawls and manual corrections. This table is the irreplaceable core asset of the PewSearch product.

CRITICAL: The directory_visible column is the gating filter for all public queries. 42,770 rows are hidden (closed, duplicate, or inappropriate listings). ALWAYS filter WHERE directory_visible = true — never quote the raw row count of 261K as the directory size.

Column	Type	Nullable	Notes
id	uuid PK	NO	Auto-generated
name	text	NO	Church display name
slug	text	NO	URL slug for /churches/[slug]
google_place_id	text	YES	Google Places ID (source of truth for deduplication)
address	text	YES	Full formatted address
street	text	YES	Street address component
city	text	YES	City
state	text	YES	Full state name
state_code	text	YES	Two-letter state abbreviation
zip_code	text	YES	ZIP code
country	text	YES	Default: 'United States of America'
latitude	float8	YES	Decimal degrees
longitude	float8	YES	Decimal degrees
time_zone	text	YES	IANA timezone string
phone	text	YES	Primary phone number
email	text	YES	Primary email
emails	text[]	YES	Array of all known emails
website	text	YES	Primary website URL
category	text	YES	Google Places category (e.g., 'church')
subtypes	text[]	YES	Google Places subtypes array
denomination	text	YES	Raw denomination string from source data
rating	numeric	YES	Google rating (0–5)
reviews_count	integer	YES	Number of Google reviews; default 0
reviews_per_score	jsonb	YES	Distribution of star ratings
photos_count	integer	YES	Number of Google photos; default 0
photo_url	text	YES	Primary photo URL
logo_url	text	YES	Logo URL (if available)
google_maps_url	text	YES	Direct Google Maps link
description	text	YES	Short description
about	jsonb	YES	Extended about information from Google
working_hours	jsonb	YES	Service/office hours structured data
business_status	text	YES	Default: 'OPERATIONAL'; may be 'CLOSED_TEMPORARILY' etc.
owner_verified	boolean	YES	True if a church owner has claimed and verified this listing; default false
source	text	YES	Data source identifier; default 'outscraper'
source_query	text	YES	The scraper query that produced this row
last_scraped_at	timestamptz	YES	When data was last refreshed from source
deep_crawled	boolean	YES	True if website has been deep-crawled; default false
deep_crawl_content	text	YES	Raw text extracted from deep website crawl — do not SELECT unless needed; can be large
website_scraped_at	timestamptz	YES	Timestamp of last website scrape
website_status	text	YES	HTTP status or error from last website scrape
is_premium	boolean	YES	True if church has an active Premium Page subscription; default false
directory_visible	boolean	YES	CRITICAL — only true rows are shown in the directory; default true. Always filter on this.
lgbtq_inclusive	boolean	YES	Detected LGBTQ+ inclusivity signal (see inclusivity_signals)
inclusivity_signals	text[]	YES	Raw signals that informed lgbtq_inclusive classification
fts	tsvector	YES	Full-text search vector (auto-maintained by trigger) — do not write directly
created_at	timestamptz	YES	Row creation timestamp
updated_at	timestamptz	YES	Last modification timestamp

Key relationships:

• churches.id is referenced by church_theological_lenses.church_id, church_update_requests.church_id, church_local_resources.church_id, contact_submissions.church_id, church_leads.church_id, church_submissions.approved_church_id
• premium_churches.church_id → churches.id (owned by ChurchWiseAI, not PewSearch)

---

denominations (64 rows)

Reference table of recognized denominations used to power the denomination filter on the directory. Each denomination belongs to a broader theological family.

Column	Type	Nullable	Notes
id	integer PK	NO	Auto-increment
name	text	NO	Canonical denomination name
family	text	NO	Theological family (e.g., 'Baptist', 'Reformed', 'Methodist')
slug	text	YES	URL slug for denomination pages
display_order	integer	NO	Sort order in UI; default 100
church_count	integer	NO	Cached count of matched churches; default 0
website	text	YES	Denomination's official website
logo_url	text	YES	Denomination logo
founded_year	integer	YES	Year the denomination was founded
headquarters	text	YES	City/state of national headquarters
about_text	text	YES	Short editorial description
beliefs	jsonb	YES	Key beliefs structured data
rss_feed_url	text	YES	RSS feed for denomination news (if available)
created_at	timestamptz	NO	Row creation timestamp

---

denomination_aliases (319 rows)

Maps raw denomination strings (as they appear in scraped data) to canonical denominations.name values. Used to normalize the messy denomination field on the churches table into filterable, structured values.

Column	Type	Nullable	Notes
id	integer PK	NO	Auto-increment
canonical	text	NO	The canonical denomination name (matches denominations.name)
alias	text	NO	Raw string variant found in source data

---

Category: Enrichment

church_theological_lenses (215,327 rows)

Join table between churches and sai_theological_lenses (the reference table of theological positions). Used to power advanced theological filtering in the directory. Each row links one church to one theological lens, with a confidence level. 215,323 rows are 'inferred' (from denomination data); only 4 are 'confirmed' (manually assigned).

Column	Type	Nullable	Notes
church_id	uuid FK	NO	References churches.id
theological_lens_id	integer FK	NO	References sai_theological_lenses.lens_id
confidence	text	YES	Default: 'inferred'; 'confirmed' for manual assignments
created_at	timestamptz	YES	Row creation timestamp

Note: This is a high-volume join table (215,327 rows). Queries filtering by theological lens should use indexed lookups on theological_lens_id. The sai_theological_lenses reference table is owned by the legacy B2C app namespace (sai_ prefix) but is read by PewSearch. FK target is sai_theological_lenses.lens_id (integer), not theological_lenses.id (uuid).

---

church_local_resources (19 rows)

Community resources associated with a specific church — food pantries, counseling services, shelters, etc. Used on church detail pages to surface how the church serves its local community.

Column	Type	Nullable	Notes
id	uuid PK	NO	Auto-generated
church_id	uuid FK	NO	References churches.id
category	USER-DEFINED	NO	Enum type for resource category (e.g., food, counseling, shelter)
name	text	NO	Resource name
description	text	YES	Description of the resource
phone	text	YES	Contact phone
address	text	YES	Resource address (may differ from church)
website	text	YES	Resource website
hours	text	YES	Operating hours
notes	text	YES	Additional notes
is_critical	boolean	NO	True for high-priority resources (e.g., crisis hotlines); default false
is_active	boolean	NO	Show/hide without deleting; default true
sort_order	integer	NO	Display order; default 0
created_at	timestamptz	NO	Row creation timestamp
updated_at	timestamptz	NO	Last modification timestamp

---

church_staff_contacts (~2 rows)

Named staff members at a church who receive chatbot-routed contacts (prayer requests, callback requests, visitor inquiries). Despite the name, this table is also used by the ChurchWiseAI voice/chatbot system to route notifications to the right staff member.

Column	Type	Nullable	Notes
id	uuid PK	NO	Auto-generated
organization_id	uuid FK	NO	References churches.id
name	text	NO	Staff member's name
role	text	NO	Role title (e.g., 'Pastor', 'Office Manager')
department	text	YES	Department or ministry area
email	text	YES	Staff email for notifications
phone	text	YES	Staff phone
bio	text	YES	Staff bio for public display
availability	jsonb	YES	Availability schedule; default {}
notification_preferences	jsonb	YES	How and when to notify; default {}
is_active	boolean	YES	Default true
created_at	timestamptz	YES	Row creation timestamp
updated_at	timestamptz	YES	Last modification timestamp

---

church_profiles (low row count)

Chatbot widget configuration for a church — welcome message, branding color, logo, service times, contact details, and social media links. Created when a church activates the ChurchWiseAI chatbot. Referenced by both the PewSearch directory widget embed and the ChurchWiseAI admin.

Column	Type	Nullable	Notes
id	uuid PK	NO	Auto-generated
organization_id	uuid FK	NO	References churches.id
welcome_message	text	YES	Default: 'Welcome! How can I help you today?'
primary_color	varchar	YES	Hex color for widget branding; default '#6B46C1'
logo_url	text	YES	Church logo URL
timezone	varchar	YES	Default: 'America/New_York'
service_times	jsonb	YES	Array of service time objects; default []
location	jsonb	YES	Structured location data; default {}
contact_email	text	YES	Public contact email
contact_phone	text	YES	Public contact phone
website_url	text	YES	Church website
social_media	jsonb	YES	Social media links; default {}
created_at	timestamptz	YES	Row creation timestamp
updated_at	timestamptz	YES	Last modification timestamp

---

church_rag_categories (low row count)

Hierarchical categories for organizing RAG knowledge base content within the admin. Allows churches to group their FAQ content into named sections with optional parent/child nesting.

Column	Type	Nullable	Notes
id	uuid PK	NO	Auto-generated
organization_id	uuid FK	NO	References churches.id
name	text	NO	Category display name
slug	text	NO	URL-safe slug
description	text	YES	Optional description
parent_id	uuid FK	YES	Self-referential parent for nesting; references church_rag_categories.id
display_order	integer	YES	Sort order; default 0
icon	text	YES	Icon identifier
is_active	boolean	YES	Default true
created_at	timestamptz	YES	Row creation timestamp
updated_at	timestamptz	YES	Last modification timestamp

---

church_events (low row count)

Events for churches with Pro Website or chatbot plans. Supports one-time and recurring events via an iCal-style recurrence rule. Used on church detail pages and surfaced by the chatbot when visitors ask about upcoming events.

Column	Type	Nullable	Notes
id	uuid PK	NO	Auto-generated
organization_id	uuid FK	NO	References churches.id
title	text	NO	Event name
description	text	YES	Event description
start_time	timestamptz	NO	Event start (UTC)
end_time	timestamptz	NO	Event end (UTC)
location	text	YES	Location string (may differ from church address)
event_type	varchar	YES	Category label (e.g., 'worship', 'outreach')
is_recurring	boolean	YES	Default false
recurrence_rule	text	YES	iCal RRULE string for recurring events
registration_url	text	YES	External registration link
max_attendees	integer	YES	Capacity cap (null = unlimited)
current_attendees	integer	YES	Current RSVP count; default 0
is_active	boolean	YES	Default true
created_by	uuid	YES	Staff member or admin who created the event
created_at	timestamptz	YES	Row creation timestamp
updated_at	timestamptz	YES	Last modification timestamp

---

Category: Operational

church_update_requests (1 row — all-time total is low)

Incoming correction requests from church staff who spot incorrect data on their listing. A pastor can submit a phone/website/hours correction without having claimed the listing. Requires admin review before the churches row is updated. Currently has 1 row (rejected).

Column	Type	Nullable	Notes
id	uuid PK	NO	Auto-generated
church_id	uuid FK	NO	References churches.id
submitter_name	text	NO	Name of the person submitting the correction
submitter_email	text	NO	Contact email
submitter_role	text	NO	Self-reported role (e.g., 'Pastor')
proposed_phone	text	YES	Corrected phone number
proposed_website	text	YES	Corrected website URL
proposed_working_hours	jsonb	YES	Corrected hours in structured format
proposed_description	text	YES	Corrected church description
status	text	NO	'pending' / 'approved' / 'rejected'; default 'pending'
reviewer_notes	text	YES	Internal admin notes
reviewed_at	timestamptz	YES	When the review was completed
created_at	timestamptz	YES	Submission timestamp
updated_at	timestamptz	YES	Last modification timestamp

---

church_submissions (1 row)

New church listing submissions from the public — churches not yet in the directory. Requires Apostles' Creed confirmation and church leader attestation to qualify. Reviewed by admin before a new churches row is created.

Column	Type	Nullable	Notes
id	uuid PK	NO	Auto-generated
name	text	NO	Submitted church name
street	text	YES	Street address
city	text	NO	City
state	text	YES	State
zip_code	text	YES	ZIP code
country	text	NO	Default: 'US'
phone	text	YES	Phone number
email	text	YES	Church email
website	text	YES	Church website
denomination	text	YES	Self-reported denomination
description	text	YES	Church description
submitter_name	text	NO	Name of the person submitting
submitter_email	text	NO	Submitter contact email
submitter_role	text	NO	Submitter's role at the church
confirms_church_leader	boolean	NO	Attestation that submitter is a church leader; default false
confirms_apostles_creed	boolean	NO	Confirms agreement with the Apostles' Creed; default false
status	text	NO	'pending' / 'approved' / 'rejected'; default 'pending'
reviewer_notes	text	YES	Internal admin notes
reviewed_at	timestamptz	YES	Review timestamp
approved_church_id	uuid FK	YES	Set to the new churches.id if approved
submitter_ip	text	YES	IP address for spam detection
created_at	timestamptz	YES	Submission timestamp
updated_at	timestamptz	YES	Last modification timestamp

---

church_scrape_jobs (605 rows)

Scraper job queue — each row represents one Outscraper batch query (by city + state + keyword). Tracks progress from pending through completion or error. Used to manage the ongoing expansion of the churches database.

Column	Type	Nullable	Notes
id	uuid PK	NO	Auto-generated
query	text	NO	The search query string sent to Outscraper
city	text	YES	Target city
state	text	YES	Target state
status	text	YES	'pending' / 'running' / 'completed' / 'failed'; default 'pending'
results_count	integer	YES	Number of churches returned; default 0
error_message	text	YES	Error details if status is 'failed'
started_at	timestamptz	YES	When job began processing
completed_at	timestamptz	YES	When job finished
created_at	timestamptz	YES	Row creation timestamp

---

church_leads (low row count)

Lead capture from church detail pages — when a pastor or admin fills out a "Claim your listing" or contact form before completing the full claim flow. Tracked for follow-up outreach. Distinct from church_submissions (which are new listing requests) and from claimed churches in premium_churches.

Column	Type	Nullable	Notes
id	uuid PK	NO	Auto-generated
church_id	text	NO	Church identifier (text, not FK — may be a slug or UUID string)
name	text	NO	Lead's name
email	text	NO	Lead's email
role	text	NO	Self-reported role
source	text	NO	Where the lead originated; default 'church_detail'
status	text	NO	'new' / 'contacted' / 'converted' / 'dead'; default 'new'
follow_up_sent_at	timestamptz	YES	When follow-up email was sent
converted_at	timestamptz	YES	When lead converted to a paying customer
created_at	timestamptz	NO	Row creation timestamp
updated_at	timestamptz	NO	Last modification timestamp

---

contact_submissions (23 rows)

Visitor-to-church contact messages submitted through the PewSearch church detail page contact form. Stored for the church's reference and optionally forwarded via email. Written by pewsearch/web; read by ChurchWiseAI admin dashboard.

Column	Type	Nullable	Notes
id	uuid PK	NO	Auto-generated
church_id	uuid FK	NO	References churches.id
name	text	YES	Visitor's name
email	text	YES	Visitor's email
message	text	YES	Message content
created_at	timestamptz	YES	Submission timestamp

---

known_issues / known_issue_history (low row counts)

User-reported bug and feedback tracking tables, used across all properties. known_issues stores each reported issue with status lifecycle. known_issue_history is an append-only audit log of every status transition.

known_issues

Column	Type	Nullable	Notes
id	uuid PK	NO	Auto-generated
title	text	NO	Short issue title
description	text	NO	Full description
category	varchar	NO	Issue category
status	varchar	NO	'reported' / 'investigating' / 'in_progress' / 'resolved' / 'wont_fix'; default 'reported'
priority	varchar	YES	'low' / 'medium' / 'high' / 'critical'; default 'medium'
affected_app	varchar	YES	Which app/property is affected
upvote_count	integer	NO	User upvote count; default 1
reporter_email	text	YES	Reporter's email
reporter_name	text	YES	Reporter's name
admin_notes	text	YES	Internal admin notes
fix_details	text	YES	Description of how it was fixed
screenshot_paths	jsonb	YES	Array of screenshot storage paths; default []
device	text	YES	Device/browser info from reporter
created_at	timestamptz	NO	Report submission timestamp
updated_at	timestamptz	NO	Last modification timestamp
resolved_at	timestamptz	YES	When the issue was resolved
feedback_count	integer	YES	Number of feedback responses; default 0

known_issue_history

Column	Type	Nullable	Notes
id	uuid PK	NO	Auto-generated (uuid_generate_v4())
issue_id	uuid FK	NO	References known_issues.id
old_status	text	YES	Previous status value
new_status	text	NO	New status value
changed_by	text	YES	Who made the change; default 'system'
change_reason	text	YES	Optional explanation
metadata	jsonb	YES	Additional context; default {}
created_at	timestamptz	YES	Transition timestamp

---

Category: Geographic

state_boundaries (62 rows)

PostGIS geometry table — one polygon per US state (plus territories). Used for geographic search queries, e.g., filtering churches within a state boundary or "near me" radius searches that need to stay within state lines.

Column	Type	Nullable	Notes
id	integer PK	NO	Auto-increment
state_code	text	NO	Two-letter state code (e.g., 'CA', 'TX')
country	text	NO	Default: 'US'
geom	geometry	NO	PostGIS geometry column (polygon/multipolygon) — requires PostGIS extension

Note: Queries against this table require the PostGIS extension. Use ST_Within, ST_Intersects, or ST_DWithin for geographic lookups. Direct column reads on geom return WKB hex — use ST_AsText(geom) or ST_AsGeoJSON(geom) for human-readable output.

---

Row Count Summary

Counts verified against live production database on 2026-03-25.

Table	Exact / Estimated Rows	Notes
churches	261,139 total	218,369 visible (directory_visible = true); 42,770 hidden
church_theological_lenses	215,327	Join table; 215,323 inferred, 4 confirmed
denomination_aliases	319	Raw → canonical denomination mapping
church_scrape_jobs	605	Outscraper job history
denominations	64	Reference table
state_boundaries	62	One row per US state/territory
contact_submissions	23	Visitor contact messages
church_local_resources	19	Community resources per church
church_update_requests	1	Correction requests (all-time)
church_submissions	1	New listing requests (all-time)
church_staff_contacts	~2	Staff notification routing
All others	~0	Operational queues, empty or near-empty

---

Key Safety Rules

1. Always filter directory_visible = true for public-facing queries. Visible: 218,369. Raw total: 261,139.
2. Never bulk delete churches. Hidden listings still have historical value and relationships.
3. deep_crawl_content can be large. Do not SELECT it unless the column is actually needed.
4. lgbtq_inclusive and inclusivity_signals are sensitive classification fields. Never display in user-facing UI without editorial review.
5. fts is auto-maintained. Do not write to this column directly — it is updated by a database trigger on INSERT/UPDATE.
6. church_theological_lenses.theological_lens_id is an integer FK to sai_theological_lenses.lens_id, not to theological_lenses.id (which is a separate uuid-keyed table used by the legacy B2C app).

---

See Also

• Database Schema Index
• ChurchWiseAI Tables
• ITW/SermonWise Tables