Skip to main content

Knowledge > Runbooks > Technical Ops > Verify Data Backups Are Intact

Verify Data Backups Are Intact

Confirm that Supabase automated backups are running successfully and that key data can be recovered if needed.

Prerequisites​

  • Access to Supabase Dashboard: https://supabase.com/dashboard/project/wrwkszmobuhvcfjipasi
  • Access to Supabase SQL editor to run record count verification

Backup Architecture​

Supabase Pro plan provides:

  • Daily automated backups — retained for 7 days
  • Point-in-time recovery (PITR) — available on Pro plan
  • No manual backup process — Supabase automated backups are the only backup layer

There is no separate backup system for this portfolio. All data recovery goes through Supabase.

Steps​

  1. Check the last successful backup timestamp

    In Supabase Dashboard:

    • Navigate to https://supabase.com/dashboard/project/wrwkszmobuhvcfjipasi/settings/storage
    • Or check: Database → Backups (if available in the dashboard navigation)
    • Confirm the most recent backup completed successfully
    • Expected: a backup within the last 24 hours

    If no backup appears or the last backup is older than 24 hours, escalate to Supabase support immediately.

  2. Verify key record counts match expected baseline

    Run these sanity checks to confirm data has not been corrupted or bulk-deleted:

    -- Churches: expect ~261K total, ~218K visible
    SELECT
      count(*) as total,
      count(*) FILTER (WHERE directory_visible = true) as visible
    FROM churches;

    -- Unified RAG content: expect ~327K rows
    SELECT count(*) FROM unified_rag_content;

    -- Premium churches: expect 4 founder test accounts (currently)
    SELECT count(*), plan FROM premium_churches GROUP BY plan;

    -- Product knowledge: expect active entries across all categories
    SELECT category, count(*) FROM product_knowledge WHERE is_active = true GROUP BY category;

    Document baseline counts when you first run this. Flag any count that drops by more than 100 rows without a known reason.

  3. Verify critical tables have recent activity

    Confirm that operational tables are being written to (indicates the system is alive):

    -- Most recent voice call
    SELECT max(created_at) as last_call FROM voice_call_logs;

    -- Most recent ops snapshot (should be within 15 minutes if system is running)
    SELECT max(recorded_at) as last_snapshot FROM ops_quota_snapshots;

    -- Most recent error report
    SELECT max(created_at) as last_error FROM ops_error_reports;

  4. Test PITR restore procedure (do this quarterly, NOT on production)

    To test that a restore actually works:

    • In Supabase Dashboard, use the PITR feature to create a NEW Supabase project (never restore over production)
    • Select a restore point from 24 hours ago
    • After restore, run the record count queries from Step 2 against the new project
    • Confirm counts match the production baseline
    • Delete the test restore project after verification

    CRITICAL: Never use PITR to restore over the production project wrwkszmobuhvcfjipasi.

  5. Check for accidental bulk operations in recent history

    If you suspect data was accidentally deleted or modified:

    -- Check if unified_rag_content had recent bulk changes
    SELECT date_trunc('hour', created_at) as hour, count(*)
    FROM unified_rag_content
    WHERE created_at > now() - interval '7 days'
    GROUP BY hour ORDER BY hour DESC;

    A sudden large count in one hour may indicate a bulk insert (expected for content runs). A sudden drop in total count indicates accidental deletion — contact Supabase support for PITR immediately.

Verification​

  • Last backup timestamp is within 24 hours
  • Record counts match expected baselines:
    • churches total: ~261K, visible: ~218K
    • unified_rag_content: ~327K
    • premium_churches: 4 (current state — update this as customers are added)
  • PITR restore test (quarterly) completes successfully on a test project

Rollback / Incident Response​

If data loss is detected:

  1. Do NOT write anything new to the database — freeze operations if possible
  2. Contact Supabase support immediately: https://supabase.com/dashboard/support
  3. Use PITR to restore to just before the loss event on a new project
  4. Verify recovered data
  5. Swap application connection strings to the recovered project (update Vercel env vars)
  6. Document in DECISION_LOG.md and add P0 item to FOUNDER_ACTIONS.md

See Also​