Skip to main content

Knowledge > Architecture > Multi-vertical tenant schema

Multi-vertical tenant schema — design spec

Implementation spec for the architecture decided in 2026-04-21-multi-vertical-tenant-architecture. This doc has the exact DDL, migration sequence, and code paths.

Shared-Core + Per-Vertical Architecture​

Principles​

  1. Existing church tables stay functionally unchanged — 13 real church subscribers are never touched by migrations.
  2. Shared-core tables get vertical column with DEFAULT 'church' — backward compat is automatic; all existing rows become vertical='church' on add.
  3. Rename via CREATE VIEW for zero-downtime — new names become primary; old names become views for a deprecation window, then dropped in a follow-up migration.
  4. FK constraints explicitly documented per migration — nothing silently cascades.

vertical column canonical values​

'church'       -- churches, ChurchWiseAI product
'funeral'      -- funeral homes, FuneralWiseAI product
'veterinary'   -- reserved, not yet active
'legal'        -- reserved, not yet active
'shop'         -- reserved, not yet active (auto-repair)

The column is text NOT NULL DEFAULT 'church'. CHECK constraint vertical IN ('church', 'funeral', 'veterinary', 'legal', 'shop'). Adding a new vertical = ALTER the constraint.

New tables (FuneralWiseAI)​

funeral_homes​

Tenant identity table, equivalent to churches conceptually but without the 218K-row directory aspect. Funeral homes exist here only as customers/demos/prospects-provisioned-for-demo.

CREATE TABLE public.funeral_homes (
  id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name                 text NOT NULL,
  legal_name           text,
  address              text,
  city                 text,
  state_province       text,
  postal_code          text,
  country              text DEFAULT 'US',
  phone                text,
  website              text,
  email                text,
  director_name        text,
  directors_count      integer,
  chapel_names         text[],
  service_types        text[],
  established_year     integer,
  slug                 text UNIQUE,
  timezone             text,
  business_hours       jsonb,
  metadata             jsonb DEFAULT '{}'::jsonb,
  scraped_from_url     text,
  created_at           timestamptz NOT NULL DEFAULT now(),
  updated_at           timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_funeral_homes_slug ON public.funeral_homes(slug);
CREATE INDEX idx_funeral_homes_state ON public.funeral_homes(state_province);

premium_funeral_homes​

Subscription state, mirror of premium_churches structure minus church-specific fields (denomination, ministries, theological_lens_id).

CREATE TABLE public.premium_funeral_homes (
  id                        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  funeral_home_id           uuid NOT NULL REFERENCES public.funeral_homes(id) ON DELETE CASCADE,
  admin_token               text NOT NULL UNIQUE,
  plan                      text NOT NULL,                 -- fwa_starter, fwa_demo_prospect, etc.
  status                    text NOT NULL DEFAULT 'active',
  stripe_customer_id        text,
  stripe_subscription_id    text,
  has_voice_subscription    boolean DEFAULT false,
  has_website_subscription  boolean DEFAULT false,
  setup_fee_paid            boolean DEFAULT false,
  setup_completed_at        timestamptz,
  contact_email             text,
  contact_phone             text,
  logo_url                  text,
  brand_colors              jsonb,
  metadata                  jsonb DEFAULT '{}'::jsonb,
  created_at                timestamptz NOT NULL DEFAULT now(),
  updated_at                timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT premium_funeral_homes_plan_check
    CHECK (plan ~ '^fwa_')
);

CREATE INDEX idx_pfh_admin_token ON public.premium_funeral_homes(admin_token);
CREATE INDEX idx_pfh_stripe_sub ON public.premium_funeral_homes(stripe_subscription_id) WHERE stripe_subscription_id IS NOT NULL;
CREATE INDEX idx_pfh_status ON public.premium_funeral_homes(status);

funeral_knowledge_base​

Per-tenant FAQ editor source (mirror of church_knowledge_base). Synced into unified_rag_content with content_category='funeral' on publish.

CREATE TABLE public.funeral_knowledge_base (
  id                         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  funeral_home_id            uuid NOT NULL REFERENCES public.funeral_homes(id) ON DELETE CASCADE,
  question                   text NOT NULL,
  answer                     text NOT NULL,
  category                   text,
  visibility                 text NOT NULL DEFAULT 'public',
  ai_suggested_response      text,
  approved_direct_response   boolean DEFAULT false,
  response_generated_at      timestamptz,
  similarity_threshold       double precision DEFAULT 0.8,
  approved_at                timestamptz,
  approved_by                uuid,
  last_reviewed_at           timestamptz,
  quality_score              numeric,
  curation_status            text DEFAULT 'pending',
  view_count                 integer DEFAULT 0,
  use_count                  integer DEFAULT 0,
  last_used_at               timestamptz,
  created_at                 timestamptz NOT NULL DEFAULT now(),
  updated_at                 timestamptz NOT NULL DEFAULT now(),
  created_by                 uuid
);

CREATE INDEX idx_fkb_funeral_home ON public.funeral_knowledge_base(funeral_home_id);
CREATE INDEX idx_fkb_curation ON public.funeral_knowledge_base(curation_status);

Shared-core tables — column and rename changes​

Renames (executed in migration 2)​

ALTER TABLE public.church_voice_agents RENAME TO tenant_voice_agents;
ALTER TABLE public.church_team_members RENAME TO tenant_team_members;
ALTER TABLE public.voice_prayer_requests RENAME TO voice_urgent_requests;
ALTER TABLE public.voice_visitor_contacts RENAME TO voice_visitor_inquiries;

Column renames (rename FK columns to match new semantics)​

ALTER TABLE public.tenant_voice_agents RENAME COLUMN church_id TO tenant_id;
ALTER TABLE public.tenant_team_members RENAME COLUMN church_id TO tenant_id;
ALTER TABLE public.voice_urgent_requests RENAME COLUMN church_id TO tenant_id;
ALTER TABLE public.voice_visitor_inquiries RENAME COLUMN church_id TO tenant_id;
ALTER TABLE public.voice_callback_requests RENAME COLUMN church_id TO tenant_id;
ALTER TABLE public.voice_call_logs RENAME COLUMN church_id TO tenant_id;
ALTER TABLE public.tool_invocations RENAME COLUMN church_id TO tenant_id;

The tenant_id column is NOT an FK to a single parent table (since tenants live in two places: premium_churches and premium_funeral_homes). It is a loose UUID — application code picks the right parent based on vertical. This is intentional — enforcing FK here would require a polymorphic FK (not natively supported in Postgres) or a single tenants base table (rejected as Option B in the decision doc).

Add vertical column to shared tables​

ALTER TABLE public.tenant_voice_agents      ADD COLUMN vertical text NOT NULL DEFAULT 'church';
ALTER TABLE public.tenant_team_members      ADD COLUMN vertical text NOT NULL DEFAULT 'church';
ALTER TABLE public.voice_urgent_requests    ADD COLUMN vertical text NOT NULL DEFAULT 'church';
ALTER TABLE public.voice_visitor_inquiries  ADD COLUMN vertical text NOT NULL DEFAULT 'church';
ALTER TABLE public.voice_callback_requests  ADD COLUMN vertical text NOT NULL DEFAULT 'church';
ALTER TABLE public.voice_call_logs          ADD COLUMN vertical text NOT NULL DEFAULT 'church';
ALTER TABLE public.tool_invocations         ADD COLUMN vertical text NOT NULL DEFAULT 'church';
ALTER TABLE public.product_knowledge        ADD COLUMN vertical text NOT NULL DEFAULT 'church';
ALTER TABLE public.chatbot_messages         ADD COLUMN vertical text NOT NULL DEFAULT 'church';
ALTER TABLE public.chatbot_conversations    ADD COLUMN vertical text NOT NULL DEFAULT 'church';
ALTER TABLE public.chatbot_questions_log    ADD COLUMN vertical text NOT NULL DEFAULT 'church';
ALTER TABLE public.organization_settings    ADD COLUMN vertical text NOT NULL DEFAULT 'church';

-- Uniform CHECK constraint (templated via function for easy future updates)
DO $$
DECLARE
  t text;
BEGIN
  FOR t IN SELECT unnest(ARRAY[
    'tenant_voice_agents', 'tenant_team_members',
    'voice_urgent_requests', 'voice_visitor_inquiries', 'voice_callback_requests',
    'voice_call_logs', 'tool_invocations', 'product_knowledge',
    'chatbot_messages', 'chatbot_conversations', 'chatbot_questions_log',
    'organization_settings'
  ]) LOOP
    EXECUTE format(
      'ALTER TABLE public.%I ADD CONSTRAINT %I_vertical_check CHECK (vertical IN (''church'',''funeral'',''veterinary'',''legal'',''shop''))',
      t, t
    );
  END LOOP;
END $$;

Backward-compat views (optional, for staged rollout)​

If any existing queries hardcode the old table names and cannot be updated atomically, add views during the deprecation window:

CREATE VIEW public.church_voice_agents AS
  SELECT id, tenant_id AS church_id, vertical, ...other cols...
  FROM public.tenant_voice_agents
  WHERE vertical = 'church';
-- etc. for the other renames

Drop views in a follow-up migration once all code has been updated.

RAG content-category taxonomy​

unified_rag_content.content_category is the source of truth for per-vertical RAG isolation. Valid values:

ValueUsed byPurpose
'church'ChurchWiseAI chatbot + voicePer-church FAQs synced from church_knowledge_base
'church_theological'Sermon tools, chatbot theological-lens retrievalCurated theological content (sermons, illustrations, scripture)
'illustration'ITW, sermon toolsSermon illustrations
'funeral'FuneralWiseAI chatbot + voice (new)Per-funeral-home FAQs synced from funeral_knowledge_base
'veterinary', 'legal', 'shop'Future verticalsReserved

Cross-vertical content (e.g., a generic "what is a HEAR protocol" explainer) uses is_universal=true and is available to all verticals regardless of content_category.

Plan key naming convention​

  • cwa_* — ChurchWiseAI (existing: cwa_pro_website, cwa_starter_voice, cwa_suite_both, cwa_demo_playground, cwa_demo_prospect, etc.)
  • fwa_* — FuneralWiseAI
    • fwa_starter — $999 setup + $199/mo
    • fwa_demo_prospect — scrape-and-demo prospects (equivalent of cwa_demo_prospect)
    • fwa_demo_playground — reserved for future demo playground rows
  • vwa_*, lwa_*, swa_* — reserved for future verticals

Enforce via CHECK constraint on premium_funeral_homes.plan (shown in DDL above): plan ~ '^fwa_'.

Migration sequence​

#MigrationDependenciesRollback strategy
1NNN_create_funeral_tenant_tables.sqlNoneDROP TABLE IF EXISTS ... (tables will be empty)
2NNN_shared_core_rename_and_vertical_col.sqlMigration 1 merged & deployedReverse renames, drop vertical column (must be run BEFORE any row has vertical ≠ 'church')
3NNN_delete_cwa_demo_prospect_funeral_rows.sqlMigration 1 + provision.ts rewrittenCannot rollback — re-provision from outreach_contacts if needed
4NNN_drop_backward_compat_views.sqlAll code updated to use new table namesRe-create views

Each migration is its own PR. No big-bang merge. Production code must be reading+writing the new names before migration 4 ships.

Code path changes​

churchwiseai-web/src/lib/outreach/provision.ts​

Today:

const { error: premiumErr } = await supabase.from('premium_churches').insert({
  church_id: churchId,
  plan: 'cwa_demo_prospect',
  ...
});

After:

const tableMap: Record<string, { tenant: string; premium: string; plan_prefix: string }> = {
  'church':  { tenant: 'churches',       premium: 'premium_churches',       plan_prefix: 'cwa_' },
  'funeral': { tenant: 'funeral_homes',  premium: 'premium_funeral_homes',  plan_prefix: 'fwa_' },
};
const { tenant, premium, plan_prefix } = tableMap[vertical];
const { error: premiumErr } = await supabase.from(premium).insert({
  [vertical === 'church' ? 'church_id' : 'funeral_home_id']: tenantId,
  plan: `${plan_prefix}demo_prospect`,
  ...
});

churchwiseai-web/src/app/admin/​

Add src/app/admin/funeral/[token]/ directory, parallel structure to admin/[token]/. Admin UI shares components where possible; domain-specific sections (chapel management, service types, staff with credentials) are funeral-only.

churchwiseai-web/voice-agent-livekit/verticals/​

Create verticals/funeral/ with:

  • prompts.py — funeral-director persona, HEAR protocol for bereaved families
  • tools.py — funeral-specific tools (arrangement_inquiry, director_callback, chapel_availability)
  • agents.py — funeral Coordinator + Care agent definitions

Pattern cloned from verticals/church/.

churchwiseai-web/src/lib/tier-config.ts​

Add vertical-aware tier resolution:

export function getTierConfig(plan: string): TierConfig {
  const vertical = planToVertical(plan);  // 'church' | 'funeral' | ...
  return VERTICAL_TIERS[vertical][plan];
}

Readiness + testing implications​

  • knowledge/readiness/ Axis A cap for FuneralWiseAI unlocks on first real premium_funeral_homes row with stripe_subscription_id IS NOT NULL (parallel to church cap logic).
  • knowledge/tests/registry.yaml — new critical-path entries for funeral-signup and funeral-demo-provisioning.
  • journeys/ — new journey YAML for funeral-home signup flow.
  • Playwright specs cloned from church flows, retargeted at /funeralwiseai/* routes.

Open questions (to settle during implementation)​

  1. Where do 218K-style directory-level funeral homes come from if we ever want a funeral-home directory? Answer: not today. FuneralWiseAI does not compete with funeral-directory products; it is SaaS-only. If a directory is ever needed, that's a separate decision.
  2. Do funeral homes need the theological_lens concept? No. The field stays in premium_churches only.
  3. Cross-vertical reporting — one view or N queries? Defer. Build per-vertical dashboards first; add cross-vertical when there are 2+ paying verticals.

See also​