Knowledge > Processes > Daily Audit Process

Daily Audit Process

A Vercel Cron job runs daily to check system health, detect drift, alert the founder to action items, and purge expired data. The endpoint is /api/cron/daily-audit on churchwiseai.com.

---

Authentication

WHEN the cron endpoint receives a GET request:
  1. Read CRON_SECRET from environment variables
  2. Read the Authorization header
  3. IF header does not equal "Bearer {CRON_SECRET}":
       RETURN 401 "Unauthorized"
  4. PROCEED with the audit

Vercel automatically sends this header when invoking cron jobs configured in vercel.json. The endpoint can also be triggered manually with the correct bearer token.

---

Audit steps (in order)

Step 1: Check pending founder action items

1. Query founder_action_items WHERE status = "pending"
   Order by priority ascending (P0 first)
2. Count P0 items (blocking, immediate attention)
3. Count P1 items (important, not blocking)
4. Count total pending items
5. Add to results: "X pending founder actions (Y P0, Z P1)"

The founder_action_items table is a queue of things that require manual human intervention -- provisioning phone numbers, reviewing edge cases, approving live Stripe changes, etc.

Step 2: Check customer count

1. Count premium_churches WHERE status = "active" AND stripe_subscription_id IS NOT NULL
2. Add to results: "Active paying customers: X"

This distinguishes real paying customers from founder test accounts (which have no subscription ID).

Step 3: Check recent voice calls

1. Count voice_call_logs WHERE created_at >= 24 hours ago
2. Add to results: "Voice calls (24h): X"

Step 4: Check recent chatbot conversations

1. Count chatbot_conversations WHERE created_at >= 24 hours ago
2. Add to results: "Chatbot conversations (24h): X"

Step 5: Check for open ops errors

1. Query ops_errors WHERE created_at >= 24 hours ago AND status = "open"
   Limit to 5 rows
2. IF any exist:
     Add to results: "Open ops errors (24h): X"

Ops errors are created by the reportError() function throughout the codebase when unexpected errors occur in API routes.

Step 6: Send founder email

IF P0 items exist OR there are paying customers:
  1. Build email body from all results collected so far
  2. IF P0 items exist:
       List each P0 item with its action_id and title
  3. Include link to founder dashboard
  4. Send email via Resend:
       From: hello@churchwiseai.com
       To: OPS_ALERT_EMAIL env var (default: john@churchwiseai.com)
       Subject:
         IF P0 items: "[ACTION REQUIRED] X P0 items need your attention"
         ELSE: "Daily Audit: X customers, Y calls"

The email is only sent when there are P0 items or paying customers. During the pre-launch phase (0 customers, no P0 items), no email is sent to avoid noise.

Step 7: Update reminder counts

FOR each pending action item:
  1. SET last_reminded_at = now
  2. INCREMENT remind_count by 1

This tracks how many times the founder has been reminded about each action item. Items with high remind_count but still pending indicate things that are being ignored.

Step 8: Site health checks

1. Ping these URLs with a 10-second timeout each:
     - https://churchwiseai.com
     - https://pewsearch.com
     - https://illustratetheword.com
     - https://sermonwise.ai
     - https://pewsearch.com/churches/grace-church-eden-prairie-3e1961f4  (specific church page)
     - https://illustratetheword.com/illustrations/the-pastors-alarm-clock...  (specific illustration)

2. Run all checks in parallel (Promise.all)
3. For each URL: record whether the response was 2xx (ok) or not
4. Add to results: "Site Health: X of Y sites responding"
5. For any DOWN sites: add "- SiteName: DOWN (status XXX)"

The specific page URLs (church detail page, illustration page) verify that dynamic content rendering works, not just that the homepage loads.

Step 9: Drift detection

This is the core system integrity check. It compares current state against expected baselines.

COLLECT drift issues:

A. Site health failures
   Any site from Step 8 that is DOWN is added as a drift issue

B. Stripe product count
   1. List all active Stripe products (test mode)
   2. IF count is not exactly 15:
        Add drift issue: "expected 15 active, found X"

C. Key table row counts
   FOR each table check:
     - churches: expect 250,000+ rows
     - unified_rag_content: expect 300,000+ rows
     - product_knowledge: expect 50+ rows
     - denominations: expect 50+ rows
   1. Count rows in the table
   2. IF count < minimum:
        Add drift issue: "TableName: expected X+ rows, found Y -- POSSIBLE DATA LOSS"

D. Demo church configuration
   1. Query premium_churches for the demo church (UUID: 00000000-0000-4000-a000-000000000001)
   2. IF not found OR status is not "active" OR chatbot_enabled is false:
        Add drift issue: "Demo church not properly configured -- chatbot demo will fail"

E. Required environment variables
   1. Check that these env vars are set:
        STRIPE_SECRET_KEY, STRIPE_PRICE_STARTER_CHAT, STRIPE_PRICE_PRO_CHAT,
        RESEND_API_KEY, NEXT_PUBLIC_SUPABASE_URL, CARTESIA_API_KEY
   2. IF any are missing:
        Add drift issue: "Missing env vars: VAR1, VAR2"

IF drift issues were found:
  1. Add all issues to the results log
  2. Create (or update) a P0 founder action item:
       action_id: "DRIFT-2026-03-25" (date-stamped, one per day)
       title: "System drift detected: X issue(s)"
       description: all drift issues joined by newlines
       priority: P0
       created_by: "daily-audit-cron"
     Using UPSERT so re-running the same day updates rather than duplicates

Step 10: Data retention purge

FUNCTION purgeExpiredData():

  A. Delete expired user restrictions
     DELETE FROM user_restrictions WHERE expires_at < now AND expires_at IS NOT NULL
     (These are moderation cooldowns that have expired -- safe to remove)

  B. Delete old response reviews (1 year retention)
     DELETE FROM response_reviews WHERE created_at < 1 year ago
     (Chat quality review data -- per privacy policy, retained max 1 year)

  C. Delete old moderation violations (1 year retention)
     DELETE FROM moderation_violations WHERE created_at < 1 year ago
     (Moderation event records -- same 1-year retention policy)

---

Response format

The endpoint returns JSON with the full audit results:

{
  "ok": true,
  "timestamp": "2026-03-25T12:00:00.000Z",
  "results": [
    "3 pending founder actions (1 P0, 2 P1)",
    "Active paying customers: 0",
    "Voice calls (24h): 12",
    "Chatbot conversations (24h): 45",
    "Site Health: 6 of 6 sites responding",
    "Data retention purge: complete"
  ],
  "pending_actions": 3,
  "p0_count": 1,
  "drift_issues": 0
}

---

Alert escalation

Condition	Action
P0 founder action items exist	Email sent with "[ACTION REQUIRED]" subject
Any site is DOWN	Drift issue created, P0 action item auto-generated
Key table row count below threshold	Drift issue "POSSIBLE DATA LOSS", P0 action item
Demo church misconfigured	Drift issue, P0 action item
Missing env vars	Drift issue, P0 action item
Stripe product count mismatch	Drift issue, P0 action item
No P0 items and 0 customers	No email sent (avoids noise pre-launch)
Customers exist but no P0 items	Email sent with customer/call summary

---

Timing and scheduling

• Configured as a Vercel Cron job (in vercel.json)
• Runs daily (typically at 7:00 AM ET)
• Each health check has a 10-second timeout
• The full audit typically completes in 5-15 seconds

---

What the audit does NOT check

• LiveKit voice agent availability -- the voice agent runs on LiveKit Cloud, outside Vercel's reach. Voice uptime is monitored via call logs (if calls stop arriving, something is wrong).
• Stripe live mode products -- only test mode products are counted (the STRIPE_SECRET_KEY env var points to test mode by default).
• Knowledge derivation drift -- use pnpm derive --check separately for that (see derive-pipeline.md).
• SSL certificate expiry -- handled by Vercel and Porkbun DNS providers automatically.
• Database backup status -- handled by Supabase's managed backups.