[BUG] Account deletion handler misses notifications, ai_insights, webhook_deliveries, and data_export_audit tables — orphaned rows persist after user deletion

[nyxsky404]

Describe the bug

The account deletion handler in src/app/api/user/data-export/route.ts (DELETE method) explicitly deletes user data from 9 tables before removing the user row, but it misses at least 4 tables that also store user-scoped data:

Tables deleted (current):

const tablesToDelete = [
  "streak_freezes",
  "streak_milestones",
  "local_coding_sessions",
  "local_coding_api_keys",
  "jira_credentials",
  "webhook_configs",
  "user_github_accounts",
  "goals",
  "metric_snapshots",
];

Tables missed:

1. notifications — used in src/app/api/notifications/route.ts and src/app/api/stream/route.ts with .eq("user_id", userId)
2. ai_insights — used in src/app/api/ai-insights/route.ts with .eq("user_id", userId)
3. webhook_deliveries — used in the GET export handler itself (line 240) with .in("webhook_id", webhookIds) — these become orphaned when webhook_configs is deleted first
4. data_export_audit — used in the same file for audit logging with .eq("user_id", userId)

Impact:

• Data privacy violation — user data persists in the database after the user explicitly requests account deletion. This may violate GDPR Article 17 (Right to Erasure) if the app serves EU users.
• Storage waste — orphaned rows accumulate over time with no cleanup mechanism.
• webhook_deliveries ordering bug — the current code deletes webhook_configs first, which means the webhook_deliveries query in the GET handler (which joins on webhook_id) would return empty results even before the user is deleted. But after deletion, the delivery rows remain with no parent.

Expected behavior:

All user-scoped tables should be included in the deletion list. The order matters — webhook_deliveries must be deleted before webhook_configs (foreign key dependency).

Suggested fix:

const tablesToDelete = [
  "notifications",
  "ai_insights",
  "data_export_audit",
  "webhook_deliveries",  // must come before webhook_configs
  "streak_freezes",
  "streak_milestones",
  "local_coding_sessions",
  "local_coding_api_keys",
  "jira_credentials",
  "webhook_configs",
  "user_github_accounts",
  "goals",
  "metric_snapshots",
];

File location: src/app/api/user/data-export/route.ts (lines 305–315)

[nyxsky404]

hey! i'd like to work on this issue pls assign it to me under gssoc'26?

[github-actions]

Assigned to @nyxsky404! You have 7 days to open a PR. No PR after 7 days = auto-unassigned so others can pick it up. Good luck!