Multi-vertical tenant architecture (2026-04-21)

Context

ChurchWiseAI is expanding from a single-vertical church product into a multi-vertical platform (WiseAI Agency family). FuneralWiseAI is live on marketing + demo with 741 prospects in outreach_contacts. VetWiseAI, LegalWiseAI, and ShopWiseAI exist as parked domains per narrative/strategy.md but have no active customers or campaigns.

The existing schema was designed entirely around churches:

• churches (218K-row directory, church-only)
• premium_churches (34 subscribers/demos)
• church_voice_agents, church_team_members, church_knowledge_base
• voice_prayer_requests, voice_visitor_contacts, voice_callback_requests — church-flavored naming for per-call data

Phase 5 scrape-and-demo currently provisions funeral-home prospects into the church tables (4 rows in premium_churches with plan='cwa_demo_prospect'). This works but creates three downstream problems:

1. Naming pollution — funeral homes are not churches; storing them in churches makes the 218K directory query noisy and creates cognitive drag.
2. Schema drift risk — church-specific fields (denomination, ministries, theological_lens_id) do not apply to funeral homes; adding vet/law clinic fields later forces either optional-everywhere columns or per-vertical JSON blobs.
3. Undefined growth path — with zero paying non-church customers, the right architecture hasn't been chosen. Waiting until vertical #3 forces a schema migration under customer load.

This decision is made now, before the first paying non-church customer, so that the foundations are right before scale forces it.

Decision

Adopt a shared-core tenant architecture:

1. Per-vertical tenant tables — each vertical gets its own tenant identity tables. Churches keep their existing tables unchanged. Funeral homes get new funeral_homes + premium_funeral_homes + funeral_knowledge_base. Future verticals follow the same pattern.

2. Shared-core tables with vertical column — mechanical primitives that are domain-independent (voice agent config, call logs, subscriptions lifecycle, tool invocations) stay single tables. A vertical column disambiguates rows.

3. unified_rag_content remains the single RAG corpus — per-tenant FAQs use content_category (e.g., 'church', 'funeral') to isolate retrieval scope. Theological/sermon/scripture content stays church-specific via existing content_category='church_theological' / 'illustration' / etc.

4. Funeral homes scaffolded now; vet/law/shop deferred — build the pattern once for FuneralWiseAI, document the playbook, apply to future verticals on launch rather than pre-scaffolding empty tables.

5. Four existing demo rows deleted, re-provisioned into new tables — rather than writing careful migration SQL for four rows, re-run scrape-and-demo after new tables exist.

Options considered

Polymorphism style

• (A) Parallel duplication — full stack of tables per vertical (funeral_homes, premium_funeral_homes, funeral_voice_agents, funeral_team_members, per-vertical call/visitor/callback tables, etc.). Rejected — causes table sprawl (12+ new tables per vertical) and forces every schema change on shared concerns (voice config, call logs) to be replicated N times.

• (B) Base + extension — single tenants base table with vertical + common fields, per-vertical extension tables (church_details, funeral_home_details) for domain-specific columns. Rejected — every read requires a JOIN, forces refactor of 34 existing premium_churches rows into the new base, and premature abstraction before we know what future verticals actually need.

• (C) Shared-core, vertical-scoped rows — selected. Keeps existing church tables untouched. Shares tables where the data model is genuinely the same (voice agent config, call logs, subscriptions state). Separates tables only where domain differs (tenant identity, per-vertical FAQ editor).

Scaffolding scope

• (A) Scaffold all 4 verticals now — create funeral + vet + legal + shop tables up front. Rejected — commits to schema before validating with real tenants; three-quarters of tables would sit empty for months; domain specifics (e.g., vet clinics may need multi-clinic-chain support) aren't knowable until customer #1 is in the room.

• (B) Funeral only, document the pattern — selected. Build for the vertical that exists; write runbooks/business-ops/new-vertical-launch.md with the exact playbook derived from what we just did; apply on next launch.

• (C) Funeral + vet (priority #1 and #2 per strategy doc) — rejected for the same reason as (A) but at smaller scale. Vet is months out.

Migration strategy for 4 existing funeral-home demos

• (A) Delete + re-provision — selected. At 4 rows, writing careful migration SQL (FK constraints, related-table cascade across 8+ tables referencing church_id) is more expensive than re-running scrape-and-demo. Worst case: a prospect's demo URL breaks for a day until re-scraped.

• (B) Migrate via INSERT ... SELECT — rejected — careful FK work for 4 rows is not worth it; no paying customer value at stake.

• (C) Leave in place as dead data — rejected — pollutes church-directory queries; drift risk compounds over time.

Table-by-table inventory

Stay untouched (church-only)

• churches — 218K-row directory, church-only product
• premium_churches — 34 church subscribers/demos (excluding the 4 funeral demos being deleted per migration decision above)
• church_knowledge_base — church FAQ editor source

Shared-core (rename + add vertical column)

Current name	New name	Why
church_voice_agents	tenant_voice_agents	Voice config shape is identical across verticals (phone, voice_id, greeting, escalation)
church_team_members	tenant_team_members	RBAC join shape is identical; role strings differ per vertical (pastor vs. director vs. senior partner)
voice_prayer_requests	voice_urgent_requests	Underlying data = "urgent caller concern captured by voice agent"; pastor UI shows "Prayer Requests", funeral director UI shows "Urgent Inquiries"
voice_visitor_contacts	voice_visitor_inquiries	Generalizes "visitor" across verticals

Shared-core (add vertical column, no rename)

• voice_call_logs
• voice_callback_requests
• tool_invocations (also rename church_id → tenant_id)
• product_knowledge
• chatbot_messages, chatbot_conversations, chatbot_questions_log
• organization_settings
• unified_rag_content — already has content_category; add 'funeral' as a valid value

New per-vertical tables (FuneralWiseAI)

• funeral_homes — tenant identity for funeral homes (vertical equivalent of churches for directory-less verticals — funeral homes don't live in a directory, only as prospects/customers)
• premium_funeral_homes — subscription state (vertical equivalent of premium_churches)
• funeral_knowledge_base — FAQ editor source (vertical equivalent of church_knowledge_base)

Plan-key naming convention

• cwa_* — ChurchWiseAI plan keys (existing: cwa_pro_website, cwa_starter_voice, cwa_suite_both, etc.)
• fwa_* — FuneralWiseAI plan keys (new: fwa_starter — $999 setup + $199/mo)
• vwa_*, lwa_*, swa_* — future verticals

All plan keys remain globally unique (no collisions across verticals).

Implementation

Design spec at knowledge/architecture/multi-vertical-schema.md for the exact DDL. Playbook at knowledge/runbooks/business-ops/new-vertical-launch.md (to be updated) for the per-vertical launch checklist.

Execution phases (each phase is a separate PR):

1. Migration 1 — new tables: funeral_homes, premium_funeral_homes, funeral_knowledge_base. DDL only, no data.
2. Migration 2 — add vertical column to shared tables (default 'church' for backward compat on existing rows), rename church_voice_agents → tenant_voice_agents + others per inventory above. Includes views preserving old names temporarily if needed for safe rollout.
3. Migration 3 — delete the 4 cwa_demo_prospect rows from church tables (+ cascading deletes via FK).
4. Code updates — src/lib/outreach/provision.ts rewrite (vertical-aware table selection), admin-route scaffolding (/admin/funeral/[token]), voice-agent verticals/funeral/ prompt wiring.
5. Runbook — update runbooks/business-ops/new-vertical-launch.md with exact playbook derived from this work.
6. Re-provisioning — run scrape-and-demo against the 4 deleted prospects to restore them in the correct tables.

Consequences

Immediate

• 13 real church premium_churches rows do not move. Zero risk to existing subscribers.
• 4 funeral-home demos temporarily offline during migration — no customer impact.
• 741 outreach_contacts with vertical='funeral' untouched (that table was already polymorphic).
• unified_rag_content continues as the single RAG corpus — hybrid RAG port (separate workstream) proceeds in parallel unaffected.
• FuneralWiseAI signup path will provision into proper tables from day one.

Medium-term

• Next vertical launch (VetWiseAI) follows the documented playbook: create 3 new tables, register plan keys, wire voice prompts, scaffold admin routes.
• Shared-core tables never need schema changes to support a new vertical; only tenant identity tables do.

Long-term

• Plan-key naming convention (<vertical>_<tier>) enforced across Stripe + product_knowledge + webhook routing.
• RBAC role strings diverge per vertical (pastor / funeral director / veterinarian / attorney) but join-table structure stays uniform.
• Cross-vertical reporting is straightforward (SELECT vertical, count(*) FROM premium_churches UNION ALL premium_funeral_homes UNION ALL ...).

Not considered / rejected

• Renaming churches → tenants — rejected. The 218K-row churches directory is a church-specific public product (not a tenant table). Funeral homes are not in a directory. Different concerns; different names. Keep churches as-is.
• Single tenants base with polymorphic FK — covered in Option B rejection above.
• Per-vertical Supabase instances — overkill. One auth system, one Stripe account, one database.
• Per-vertical voice-agent deployments — rejected. The LiveKit agent is already multi-tenant; voice-agent-livekit/verticals/ directory pattern scales per-vertical prompts without separate deployments.

References

• Parent strategy: knowledge/narrative/strategy.md (vertical expansion sequencing)
• Product map: knowledge/products/README.md
• FuneralWiseAI product doc: knowledge/products/funeralwiseai/overview.md (one inaccurate paragraph — will be corrected in this PR)
• WiseAI Agency meta-brand: knowledge/products/wiseaiagency/overview.md
• Plan column contract: knowledge/architecture/db/plan-column-contract.md
• Scrape-and-demo provisioning code: churchwiseai-web/src/lib/outreach/provision.ts