Skip to main content

Knowledge > Runbooks > SermonWise Account Deletion

SermonWise Account & Data Deletion

How to honour a SermonWise customer's deletion request under GDPR (Art. 17 — "right to erasure") and CCPA (§1798.105 — "right to delete"). Three procedures, each with a distinct scope.

Closes: MED-07 from the 2026-04-24 SermonWise adversarial review. Status as of 2026-04-25: Tier 1 + Tier 2 are live in production. Tier 3 is documented but has no scripted helper yet — file FA-XXX if a real request lands before the helper is written.

When to use which procedure​

TriggerProcedureTier
User clicks "Delete sermon" in the appSoft-delete (already automated)1
30 days pass after a soft-deleteCron sweep (already automated)2
User emails "delete my account" / GDPR request / CCPA verifiable requestAccount wipe (manual SQL with checklist)3
Stripe chargeback dispute requires proof of refund + data deletionAccount wipe (Tier 3) + retain audit log3

Tier 1 and Tier 2 happen automatically — no human action. Tier 3 is the only path that needs a human.

Tier 1 — Soft-delete (automatic, user-initiated)​

Trigger: User clicks the delete button on a sermon in /sermons/app/[id].

What happens:

  • DELETE /api/sermons/[id] flips sermons.status = 'deleted'.
  • The sermon disappears from /api/sermons/list (filtered by .neq('status','deleted')).
  • The row is not removed from Postgres. It sits in a 30-day grace bucket.

Source: src/app/api/sermons/[id]/route.ts lines 54-77.

Why grace? Two reasons:

  1. Stripe chargeback / dispute window — we may need to prove the user generated content before deletion.
  2. User regret — gives ops a chance to restore via a manual UPDATE sermons SET status='draft' WHERE id=$1 if the user emails within the window.

No restore UI is exposed today. If a customer asks, restore manually via the Supabase SQL editor.

Tier 2 — Cron sweep (automatic, 30 days post-soft-delete)​

Trigger: Vercel cron /api/cron/sweep-deleted-sermons runs daily at 06:00 UTC.

What happens:

  • Hard-deletes any sermons row where status = 'deleted' AND updated_at < now() - interval '30 days'.
  • Logs the count of rows swept; emits a founder_action_items row only if a sweep batch exceeds 100 rows (canary for runaway deletes).

Source: src/app/api/cron/sweep-deleted-sermons/route.ts.

Schedule: 0 6 * * * in vercel.json.

Auth: Bearer ${CRON_SECRET} or x-vercel-cron: 1 header (Vercel sets this automatically).

Manual trigger (verification):

curl -H "Authorization: Bearer $CRON_SECRET" \
  https://churchwiseai.com/api/cron/sweep-deleted-sermons

Expected response on a quiet day (no eligible rows):

{ "ok": true, "swept": 0, "cutoff": "2026-03-26T06:00:00.000Z" }

What this does NOT touch:

  • sermon_generation_usage — usage counters are retained (capped 100/month, 12-month retention is fine for billing audits).
  • shared_sermons — community shares are governed by their own moderation lifecycle, not the user's soft-delete.
  • community_reviews — same.
  • Any derivative content stored in the same row's content JSONB.

If the user wants ALL of those gone, escalate to Tier 3.

Tier 3 — Full account wipe (manual, GDPR/CCPA verifiable request)​

Trigger: User emails john@churchwiseai.com from the address on file with a deletion request, OR a verifiable GDPR/CCPA request comes through privacy@.

Pre-flight checklist:

  1. Verify the requester — confirm the email address on the request matches the address on auth.users.email. If they don't match, ask the requester to send a confirmation from the registered address. Per CCPA §1798.140(y), a "verifiable consumer request" requires authentication of identity.
  2. Cancel active subscription first — if the user is subscription_tier='pro', cancel via Stripe (see runbooks/customer-ops/cancel-subscription.md) before wiping. Otherwise the webhook will try to update a deleted profile and 500 in the inbox.
  3. Capture an audit row — add a founder_action_items entry with priority='P2', title='GDPR account wipe — <email>', description=<request text>, created_by='gdpr-handler'. This is the legal paper trail.
  4. Get founder approval — Tier 3 deletes auth.users rows. Per CLAUDE.md rule, destructive SQL needs explicit founder approval. Do not proceed without it.

Order of operations (chase FK constraints — children before parents):

-- Run as service role in the Supabase SQL editor.
-- Replace $USER_ID with the actual auth.users.id (NOT the email).

-- 1. Resolve the user_id from email
SELECT id, email, created_at FROM auth.users WHERE email = '<email>';

-- 2. Hard-delete sermon-specific data (children first to avoid FK violations)
DELETE FROM community_reviews WHERE user_id = '$USER_ID';
DELETE FROM shared_sermons   WHERE user_id = '$USER_ID';
DELETE FROM sermons          WHERE user_id = '$USER_ID';
DELETE FROM sermon_generation_usage WHERE user_id = '$USER_ID';

-- 3. SermonWise-side artifacts
DELETE FROM social_subscriptions WHERE user_id = '$USER_ID';      -- if any
DELETE FROM email_subscribers    WHERE email   = '<email>';

-- 4. Profile + auth row (parent — last)
DELETE FROM profiles    WHERE id = '$USER_ID';
DELETE FROM auth.users  WHERE id = '$USER_ID';

Out-of-band cleanup:

  1. MailerLite — remove the email from any active groups via the MailerLite API:

    curl -X DELETE \
      "https://connect.mailerlite.com/api/subscribers/<subscriber-id>" \
      -H "Authorization: Bearer $MAILERLITE_API_KEY"

    Look up the subscriber id by email first via GET /api/subscribers?filter[email]=<email>.

  2. Stripe — if a customer exists, anonymise it (Stripe doesn't allow full deletion of customers attached to historical charges):

    stripe customers update cus_XXXXX \
      --metadata[gdpr_deleted]=2026-04-25 \
      --email=deleted+cus_XXXXX@churchwiseai.invalid \
      --name="Deleted Customer"

    The Stripe row stays for 7-year tax retention; PII is anonymised per Art. 17(3)(b) ("retention required for compliance with a legal obligation").

  3. Resend / email logs — search any sent emails to that address in Resend dashboard. Resend retains email logs for 30 days; document this in your reply to the requester so the 30-day expiry is on the record.

Confirmation reply template:

Subject: Your SermonWise account has been deleted

Hi <name>,

We've deleted your SermonWise account. Specifically:

- All sermons, community shares, and reviews you created have been permanently removed from our database.
- Your usage history and login profile have been deleted.
- Your email address has been unsubscribed from all our lists.
- Your Stripe customer record has been anonymised. (Stripe retains a placeholder for tax compliance; no personal data remains.)
- Email delivery logs in Resend will expire automatically within 30 days.

If you signed up again with the same email, it would be a fresh account with no history.

If you have any questions, reply to this email and I'll respond personally.

— John

Verification queries​

After a Tier 3 wipe, run these to confirm zero residue:

-- All should return 0
SELECT COUNT(*) FROM sermons              WHERE user_id = '$USER_ID';
SELECT COUNT(*) FROM sermon_generation_usage WHERE user_id = '$USER_ID';
SELECT COUNT(*) FROM shared_sermons       WHERE user_id = '$USER_ID';
SELECT COUNT(*) FROM community_reviews    WHERE user_id = '$USER_ID';
SELECT COUNT(*) FROM email_subscribers    WHERE email   = '<email>';
SELECT COUNT(*) FROM profiles             WHERE id      = '$USER_ID';
SELECT COUNT(*) FROM auth.users           WHERE id      = '$USER_ID';

Why three tiers, not one?​

ConcernOne-tier (immediate hard-delete on click)Three-tier (current)
User-regret windowNone — accident is permanent30 days
Stripe chargeback evidenceLostRetained until cron sweep
GDPR Art. 17 complianceMet instantlyMet at Tier 3 (within 30 days, well within Art. 17's "without undue delay")
Engineering complexityMinimalModerate (cron + runbook)
Risk of irreversible mistakeHighLow

The 30-day grace is conservative but defensible. GDPR Art. 17(1) requires "without undue delay" not "instantly"; the EDPB has consistently treated 30 days as reasonable for backup/audit purposes.

Open follow-ups​

  • Build scripts/wipe-sermonwise-account.mjs that accepts an email, runs the verification queries, then executes the DELETE chain transactionally. Currently each request is hand-run — fine while volume is low (< 1/year so far) but a script will reduce error risk.
  • Add a self-service "Delete my account" button in /sermons/app/settings. Today users have to email; the button would soft-delete the auth.users row by enqueuing a Tier 3 job. Out of scope for the 2026-04 hardening pass.
  • Wire a P1 founder_action_items alert when the cron sweep deletes >100 rows in a single run (canary for runaway delete bugs).

Change log​

DateChange
2026-04-25Runbook created. Closes MED-07 from the 2026-04-24 adversarial review. Adds the cron sweep + documents Tier 3 procedure.