DBA + Security Report -- Acceptance Specs
Date: 2026-03-28
Author: DBA + Security Review Lead (Claude Agent)
Scope: Cross-reference all 12 acceptance specs against actual Supabase database schema
Database: wrwkszmobuhvcfjipasi (production)
Summary
| Category | Count |
|---|---|
| Schema mismatches (specs reference columns that do not exist) | 5 |
| Migrations needed (new columns/tables required) | 7 |
| Security concerns | 6 |
| Hard delete violations (specs say soft delete, code uses hard delete) | 3 |
1. Schema Mismatches (Specs Reference Things That Do Not Exist)
1.1 CRITICAL: source column on request tables
Referenced in: starter-both.md (Touchpoint 50), pro-both.md (Touchpoint 50), suite-both.md (Touchpoint 50)
Spec says: "Source column with icon: Phone icon for voice, chat bubble for chat" and "Filter dropdown: All / Voice / Chat" on the Requests tab for bundle plans.
Reality: None of the three request tables (voice_prayer_requests, voice_callback_requests, voice_visitor_contacts) have a source column. There is no way to distinguish whether a prayer request came from voice or chat.
The tables also have no channel column.
Impact: Bundle plan customers cannot filter requests by voice vs chat. The merged request view with source icons described in the specs is impossible without schema changes.
Migration needed: Add source TEXT DEFAULT 'voice' to all three tables. Chatbot code must write 'chat' when creating requests via the chat API.
1.2 CRITICAL: phone_provider column does not exist
Referenced in: starter-voice.md (Touchpoint 12), starter-both.md (Touchpoint 12), pro-both.md (Touchpoint 12), suite-both.md (Touchpoint 12)
Spec says: "Who is your phone provider?" dropdown field on the onboard form, saved for carrier-specific forwarding instructions in Email 2.
Reality: There is no phone_provider column on premium_churches, church_voice_agents, or any other table. The onboarding form code does not reference phoneProvider either (grep returned zero matches in churchwiseai-web/src).
Impact: The voice provisioning two-email flow cannot include carrier-specific forwarding instructions because the carrier is never collected or stored.
Migration needed: Add phone_provider TEXT to premium_churches (or church_voice_agents). Update the onboard form and the provisioning email template.
1.3 CRITICAL: cancellation_reason column does not exist
Referenced in: cancelled.md (Touchpoint CX-3)
Spec says: Exit survey response stored in premium_churches.cancellation_reason.
Reality: No cancellation_reason column exists on premium_churches. No code references cancellationReason or cancel_reason in the codebase (grep returned zero matches).
Impact: The exit survey described in the cancellation spec has nowhere to store its data.
Migration needed: Add cancellation_reason TEXT and cancelled_at TIMESTAMPTZ to premium_churches.
1.4 MODERATE: cancel_at_period_end column does not exist on premium_churches
Referenced in: cancelled.md (state transition diagram, Touchpoint CX-4)
Spec says: Stripe sets cancel_at_period_end = true and dashboard shows "Cancelling -- ends [date]" banner.
Reality: premium_churches has no cancel_at_period_end column and no billing_period_end or current_period_end column. The Stripe subscription object has this data, but the webhook must persist it locally for the dashboard to render the cancellation banner without calling Stripe.
Impact: Dashboard cannot show the "Cancelling" state or the end date without either: (a) adding columns, or (b) making a Stripe API call on every dashboard load (slow, rate-limited).
Migration needed: Add cancel_at_period_end BOOLEAN DEFAULT false and current_period_end TIMESTAMPTZ to premium_churches. Webhook handler must populate them.
1.5 LOW: intent column missing from voice_call_logs
Referenced in: starter-voice.md (Touchpoint 49), starter-both.md (Touchpoint 49)
Spec says: Calls tab expandable row shows "Intent" as one of the columns.
Reality: voice_call_logs has no intent column. It does have category and call_topics which may serve a similar purpose, but the specs specifically call out "Intent" as a displayed field.
Impact: Minor -- category can likely be mapped to "Intent" in the UI, but the spec and schema terminology diverge.
Recommendation: Either rename category to intent or update the spec to say "Category" instead of "Intent". No migration strictly needed if the UI maps category to the "Intent" label.
2. Migrations Needed
2.1 Soft delete column on request tables
Tables: voice_prayer_requests, voice_callback_requests, voice_visitor_contacts, voice_call_logs
Spec says (multiple specs): "Soft delete" for call logs and requests in the Calls tab and Requests tab.
Reality: None of these four tables have a soft delete column (hidden, soft_deleted, is_deleted, archived, is_archived). The only mechanism for removing records is hard deletion.
SQL:
ALTER TABLE voice_prayer_requests ADD COLUMN is_hidden BOOLEAN DEFAULT false;
ALTER TABLE voice_callback_requests ADD COLUMN is_hidden BOOLEAN DEFAULT false;
ALTER TABLE voice_visitor_contacts ADD COLUMN is_hidden BOOLEAN DEFAULT false;
ALTER TABLE voice_call_logs ADD COLUMN is_hidden BOOLEAN DEFAULT false;
2.2 Source column on request tables (for bundle plans)
ALTER TABLE voice_prayer_requests ADD COLUMN source TEXT DEFAULT 'voice' CHECK (source IN ('voice', 'chat'));
ALTER TABLE voice_callback_requests ADD COLUMN source TEXT DEFAULT 'voice' CHECK (source IN ('voice', 'chat'));
ALTER TABLE voice_visitor_contacts ADD COLUMN source TEXT DEFAULT 'voice' CHECK (source IN ('voice', 'chat'));
2.3 Phone provider column
ALTER TABLE premium_churches ADD COLUMN phone_provider TEXT;
2.4 Cancellation tracking columns
ALTER TABLE premium_churches ADD COLUMN cancellation_reason TEXT;
ALTER TABLE premium_churches ADD COLUMN cancelled_at TIMESTAMPTZ;
ALTER TABLE premium_churches ADD COLUMN cancel_at_period_end BOOLEAN DEFAULT false;
ALTER TABLE premium_churches ADD COLUMN current_period_end TIMESTAMPTZ;
2.5 Win-back email suppression tracking
Referenced in: cancelled.md (Touchpoint CX-23)
Spec says: "Win-back emails may be suppressed after 2+ cancellation cycles."
Reality: No cancellation_count column exists anywhere.
ALTER TABLE premium_churches ADD COLUMN cancellation_count INTEGER DEFAULT 0;
2.6 Lifecycle email keys for new email types
Referenced in: trial-expired.md, cancelled.md
New email keys needed in lifecycle_emails_sent: trial_expired_*, win_back_day21, win_back_day44, cancellation_confirmation, service_ended, win_back_post_cancel_7d, win_back_post_cancel_30d, welcome_back.
No migration needed -- lifecycle_emails_sent.email_key is a free-form TEXT field, so new keys can be used immediately.
2.7 Exit survey storage
Already covered in 2.4 (cancellation_reason), but consider a richer schema if multiple-choice answers need to be stored:
-- Alternative: use JSONB to capture both the option and optional free text
ALTER TABLE premium_churches ADD COLUMN exit_survey JSONB;
-- Example: {"reason": "too_expensive", "free_text": "Budget cuts this quarter"}
3. Tier/Plan Storage Analysis
3.1 How plans are stored in premium_churches
The plan column is TEXT NOT NULL DEFAULT 'starter'. The channel column is TEXT DEFAULT 'chat'.
Current data in production (4 founder test accounts):
| plan | status | channel |
|---|---|---|
pro | active | chat |
pro_website | active | both |
pro_website | active | chat |
starter | active | both |
starter | preview | chat |
Specs reference combined plan names like: starter_chat, starter_voice, starter_both, pro_chat, pro_both, suite_chat, suite_both, pro_chat_annual, etc.
How this works: The tier-config.ts code accepts BOTH formats. It handles starter_chat and also starter + channel='chat' separately. The DB stores plan + channel as two columns, while the code constructs compound names like starter_chat from plan + _ + channel.
Risk: The plan column has no CHECK constraint. Any string can be stored. There are already inconsistencies: some records use pro, others might use pro_chat. The code must handle both forms.
Recommendation: Add a CHECK constraint or at least document the canonical values:
-- After standardizing existing data:
ALTER TABLE premium_churches ADD CONSTRAINT plan_valid
CHECK (plan IN ('starter', 'pro', 'suite', 'pro_website', 'premium', 'free'));
ALTER TABLE premium_churches ADD CONSTRAINT channel_valid
CHECK (channel IN ('chat', 'voice', 'both'));
3.2 Feature gating mechanism
Feature gating is handled entirely in code, not in the database. The file churchwiseai-web/src/lib/tier-config.ts contains:
getTier(plan)-- maps plan string to tier (starter/pro/suite)planIncludesVoice(plan)-- checks if plan has voiceTIER_FEATURES-- maps tier to feature flags
There is NO tier_features or plan_features table in the database. All gating decisions are made at the TypeScript level.
This is fine for the current architecture but means:
- Any plan change in Stripe must be reflected in the
plancolumn for gating to update - Feature gating is not queryable from SQL (e.g., for analytics or reports)
4. Table Existence Verification
4.1 All tables referenced in spec db-tables frontmatter exist
| Table | Exists | Notes |
|---|---|---|
premium_churches | YES | Core CWA/PewSearch subscription table |
organization_settings | YES | Legacy B2C table, repurposed for chatbot config |
church_voice_agents | YES | Voice agent config per church |
voice_prayer_requests | YES | 8 columns, no soft delete |
voice_callback_requests | YES | 11 columns, no soft delete |
voice_visitor_contacts | YES | 9 columns, no soft delete |
voice_call_logs | YES | 19 columns, no soft delete |
congregation_care_members | YES | 11 columns, all fields specs reference exist |
congregation_care_messages | YES | 9 columns |
unified_rag_content | YES | ~327K rows, 65+ columns |
product_knowledge | YES | 9 columns |
churches | YES | 218K+ visible, 48 columns |
lifecycle_emails_sent | YES | 8 columns |
chatbot_conversations | YES | 29 columns |
chatbot_messages | YES | 16 columns |
chatbot_agents | YES | 21 columns |
church_team_members | YES | 10 columns |
contact_submissions | YES | 6 columns |
user_subscriptions | YES | 19 columns (ITW/SermonWise) |
user_favorites | YES | 4 columns (ITW) |
dir_illustrations | YES | ITW illustration table |
canned_responses | YES | FAQ/responses table (PewSearch) |
profiles | YES | Has denomination column (SermonWise) |
church_chatbot_analytics | YES | 12 columns |
voice_event_registrations | YES | 11 columns |
5. Analytics Data Source Analysis
Specs mention: 7d, 30d, 90d analytics with top questions, category breakdown, trends, CSV export.
Data sources:
church_chatbot_analytics-- daily aggregate table withtotal_conversations,total_messages,unique_visitors,popular_topics,escalation_count. Date-indexed. This supports time-range filtering.chatbot_conversations-- individual conversation records withcategory,primary_topic,sentiment. Can be aggregated for ad-hoc queries.chatbot_messages-- individual message records withintent,tokens_used,response_time_ms.- There is also an RPC function
get_chatbot_analyticsmentioned in CLAUDE.md.
Voice analytics: No equivalent of church_chatbot_analytics exists for voice. voice_call_logs has individual records that can be aggregated by date, but there is no pre-aggregated daily table.
Impact for bundle plans: The "5 stat cards" (calls + chat + combined requests) spec for bundle plans requires joining voice and chat data. This is doable from raw tables but there is no unified analytics view.
6. Voice Call Log Analysis
Specs describe Calls tab columns: Date/time, Duration, Caller phone number, Intent, Status badge, expandable transcript, agent, actions taken, soft delete.
Actual voice_call_logs columns:
created_at-- Date/time (YES)duration_seconds-- Duration (YES)from_number-- Caller phone (YES)category-- Intent (close enough, see 1.5)status-- Status badge (YES)transcript(JSONB) -- Expandable transcript (YES)suggested_assignee-- Agent (partially)call_topics,urgency,follow_up_needed-- Actions context (YES)- No soft delete column (see 2.1)
Missing for spec: No agent column (which of the 4 agents handled the call). The suggested_assignee is text, not an agent type reference.
7. Security Concerns
7.1 HIGH: Token-based auth has no expiration or rotation enforcement
Auth mechanism: CWA dashboard uses admin_token (64-char hex string stored in premium_churches.admin_token). The URL /admin/[token] directly authenticates the user.
Concerns:
- Tokens never expire. A leaked URL grants permanent dashboard access.
- Token is passed in the URL (visible in browser history, referrer headers, shared screenshots).
- Only 1 RLS policy exists on
premium_churches:anon_read_safe_columns(read-only for anon). No RLS policies exist forvoice_prayer_requests,voice_callback_requests,voice_visitor_contacts, orchurch_voice_agents. - API routes authenticate by matching
admin_tokenagainst the database, which is correct, but there is no rate limiting on token guessing.
Recommendation:
- Add token rotation on admin request (already referenced in pewsearch-premium.md Touchpoint 22)
- Consider adding session-based auth with a short-lived JWT derived from the token
- Add rate limiting on
/admin/[token]route - Severity: HIGH -- a single URL leak compromises the entire church dashboard
7.2 HIGH: No RLS on request tables -- service role bypass
Tables affected: voice_prayer_requests, voice_callback_requests, voice_visitor_contacts, voice_call_logs, church_voice_agents
Reality: Zero RLS policies on any of these tables. All access goes through API routes using the Supabase service role key, which bypasses RLS entirely.
Risk: If any API route has an auth bypass bug (fails to validate the token), all request data across ALL churches is exposed. There is no defense-in-depth at the database level.
Recommendation: Add RLS policies that scope reads to church_id matching the authenticated user's church. Even if the API routes are correct today, RLS provides a safety net.
Severity: HIGH -- one API bug could expose prayer requests, visitor contacts, and call transcripts across all churches.
7.3 MEDIUM: Hard deletes found in codebase (specs say never hard delete)
The cancelled.md spec explicitly states "No data deletion (soft delete pattern)" and all specs say "data preserved indefinitely." However, I found hard DELETEs in the codebase:
/api/care/members/route.ts--.delete()on care members/api/admin/kb-proxy/route.ts--.delete()on FAQ/document records + storage removal/api/admin/moderation/route.ts--.delete()on moderation content/api/admin/resources/route.ts--.delete()on local resources/api/admin/theology/route.ts--.delete()on theology overrides/api/onboard/route.ts--DELETE FROM churches(deletes a church record during onboard failure cleanup)/api/cron/daily-audit/route.ts-- Multiple.delete()calls
Risk assessment:
- Items 1-5 are admin-initiated content management operations (deleting FAQs, resources, theology entries). These are not customer data in the "prayer requests / call logs / training data" sense. The specs' "never delete" principle applies to customer-submitted data and church training data, not content management.
- Item 6 deletes a
churchesrow during onboard failure -- acceptable cleanup. - Item 7 (daily audit cron) needs investigation to confirm it's not deleting customer data.
Most concerning: No code currently deletes from voice_prayer_requests, voice_callback_requests, voice_visitor_contacts, or voice_call_logs. The "never hard delete" principle is upheld for these tables today, but only because no delete code exists -- not because soft delete infrastructure prevents it.
Severity: MEDIUM -- principle is upheld by omission, not by design. Adding soft delete columns (Migration 2.1) would make this enforceable.
7.4 MEDIUM: PewSearch claim flow -- honor system verification
Referenced in: pewsearch-premium.md (Touchpoint 4)
Flow: Anyone can visit /claim/[slug], enter their name, email, and role (Pastor, Office Admin, etc.), and start the Stripe checkout. There is no verification that the person actually represents the church.
Risks:
- A competitor or bad actor could claim a church they don't represent
- A disgruntled member could claim and control a church listing
- The only remediation is manual founder intervention
Current mitigation: The admin_role field stores the claimed role, and the admin email is visible. If a real church representative contacts support, the founder can revoke and reassign.
Recommendation: Accept this risk for launch (honor system with manual override). At scale, consider email domain verification (e.g., claim from a @churchname.org email) or a verification code sent to the church's publicly listed phone/email.
Severity: MEDIUM -- acceptable for 0-100 customers; needs solution before 500+.
7.5 MEDIUM: Cross-tier access via URL navigation
Question: Can a Starter customer access Pro features by navigating directly to a URL (e.g., adding ?tab=training&sub=faqs to the dashboard)?
Answer: Feature gating is handled in tier-config.ts and the dashboard components (TrainingTab.tsx, AdminDashboard.tsx, etc.). The components check tier before rendering tabs and sub-tabs. The "hidden not locked" principle means tabs are not rendered at all -- there is no URL-accessible hidden page.
However, the API routes also need to enforce tier restrictions. For example:
- Can a Starter customer POST FAQs to
/api/admin/kb-proxy? If the API route does not check the tier, a Starter customer who knows the API endpoint could add FAQs even though the UI hides the feature.
Needs verification: Each API route that implements a Pro+ feature must check the customer's tier, not just rely on the UI hiding the feature.
Severity: MEDIUM -- UI hides features correctly; API-level enforcement needs audit.
7.6 LOW: No auto-cleanup threatens data retention promise
Spec says (cancelled.md): "Data preserved indefinitely."
Cron job: /api/cron/daily-audit/route.ts contains multiple .delete() calls. If any of these target customer data from cancelled accounts, it would violate the indefinite retention promise.
Recommendation: Audit the daily-audit cron to confirm it only deletes operational/ephemeral data (expired sessions, temp records) and never touches cancelled customer data.
Severity: LOW -- unlikely but should be verified.
8. Schema Alignment Matrix
| Spec | Tables Referenced | All Exist? | Schema Issues |
|---|---|---|---|
| starter-chat.md | premium_churches, organization_settings, church_voice_agents | YES | None for chat-only |
| pro-suite-chat.md | premium_churches, organization_settings, church_voice_agents | YES | None for chat-only |
| starter-voice.md | premium_churches, organization_settings, church_voice_agents | YES | Missing: phone_provider, soft delete on call logs |
| starter-both.md | premium_churches, organization_settings, church_voice_agents | YES | Missing: source on requests, phone_provider, soft delete |
| pro-both.md | premium_churches, organization_settings, church_voice_agents | YES | Missing: source on requests, phone_provider, soft delete |
| suite-both.md | premium_churches, organization_settings, church_voice_agents | YES | Missing: source on requests, phone_provider, soft delete |
| trial-expired.md | premium_churches, lifecycle_emails_sent | YES | Missing: new email keys (minor -- free-form field) |
| cancelled.md | premium_churches, lifecycle_emails_sent, church_voice_agents | YES | Missing: cancellation_reason, cancel_at_period_end, current_period_end, cancellation_count |
| itw-premium.md | dir_illustrations, unified_rag_content, user_subscriptions, user_favorites | YES | None found |
| pewsearch-premium.md | churches, premium_churches, church_team_members, canned_responses, congregation_care_members | YES | None found |
| pewsearch-pro-website.md | churches, premium_churches, church_team_members, canned_responses, contact_submissions | YES | None found |
| sermonwise-pro.md | profiles, sai_theological_lenses, user_subscriptions | YES | None found -- profiles.denomination exists |
9. Consolidated Migration Script
All migrations needed, in dependency order:
-- 1. Source tracking for bundle plan request filtering
ALTER TABLE voice_prayer_requests ADD COLUMN source TEXT DEFAULT 'voice' CHECK (source IN ('voice', 'chat'));
ALTER TABLE voice_callback_requests ADD COLUMN source TEXT DEFAULT 'voice' CHECK (source IN ('voice', 'chat'));
ALTER TABLE voice_visitor_contacts ADD COLUMN source TEXT DEFAULT 'voice' CHECK (source IN ('voice', 'chat'));
-- 2. Soft delete for request and call log tables
ALTER TABLE voice_prayer_requests ADD COLUMN is_hidden BOOLEAN DEFAULT false;
ALTER TABLE voice_callback_requests ADD COLUMN is_hidden BOOLEAN DEFAULT false;
ALTER TABLE voice_visitor_contacts ADD COLUMN is_hidden BOOLEAN DEFAULT false;
ALTER TABLE voice_call_logs ADD COLUMN is_hidden BOOLEAN DEFAULT false;
-- 3. Phone provider for voice provisioning emails
ALTER TABLE premium_churches ADD COLUMN phone_provider TEXT;
-- 4. Cancellation tracking
ALTER TABLE premium_churches ADD COLUMN cancellation_reason TEXT;
ALTER TABLE premium_churches ADD COLUMN cancelled_at TIMESTAMPTZ;
ALTER TABLE premium_churches ADD COLUMN cancel_at_period_end BOOLEAN DEFAULT false;
ALTER TABLE premium_churches ADD COLUMN current_period_end TIMESTAMPTZ;
ALTER TABLE premium_churches ADD COLUMN cancellation_count INTEGER DEFAULT 0;
-- 5. Plan validation constraints (optional, recommended)
-- Run AFTER standardizing existing plan values
-- ALTER TABLE premium_churches ADD CONSTRAINT plan_valid
-- CHECK (plan IN ('starter', 'pro', 'suite', 'pro_website', 'premium', 'free'));
-- ALTER TABLE premium_churches ADD CONSTRAINT channel_valid
-- CHECK (channel IN ('chat', 'voice', 'both'));
Total columns to add: 14 across 5 tables.
10. Recommendations Prioritized
P0 -- Block launch
- Add
sourcecolumn to request tables -- Bundle plans cannot function without voice/chat attribution - Add soft delete columns -- Specs promise "never hard delete" but no mechanism exists
- Audit API routes for tier enforcement -- UI hides Pro features but API routes may not check tier
P1 -- Before first 10 customers
- Add cancellation tracking columns -- Dashboard "Cancelling" state and exit survey need storage
- Add
phone_providercolumn -- Voice provisioning emails need carrier-specific instructions - Add rate limiting on
/admin/[token]-- Prevent token brute-forcing
P2 -- Before 100 customers
- Add RLS policies on request tables -- Defense-in-depth against API auth bugs
- Add plan CHECK constraints -- Prevent data quality drift
- Audit daily-audit cron -- Confirm no customer data deletion
- Consider token expiration/rotation -- Currently tokens are permanent