Parent: #361

Context

A user who connects their Gmail, lets the twin learn for a week, and then decides "this isn't for me" has no way to delete what the product has accumulated about them. Twin profile, decision history, memory pages, knowledge triples, episodic memories, preferences, OAuth tokens, spend records, encrypted credential vault meta, idle-scan results — all of it remains, indefinitely, with no user-facing way to remove it short of psql and a list of 30+ tables.

This is the "GDPR Article 17 / right to erasure" gap, and it is independently a recovery gap. A user who accidentally trains a bad twin (wrong preferences, garbage signals from a stale connector, a domain enabled by mistake) cannot start over without a developer's help. A user who can't delete their data won't trust the app with new data.

It is also the kind of issue every public download visitor checks for in a privacy policy before installing — and SkyTwin's docs/privacy.html cannot truthfully say "we can delete your data on request" until the endpoint exists.

Root Cause Analysis

Three structural reasons the delete path doesn't exist today:

1. The users router never grew a DELETE. apps/api/src/routes/users.ts has create (POST /), list (GET /), get-by-id (GET /:userId), update-trust-tier (PUT /:userId/trust-tier), update-domains (PUT /:userId/domains), seed-preferences (POST /:userId/seed-preferences) — and no DELETE handler. The route file was last extended for seed-preferences (build(deps): bump pg from 8.20.0 to 8.21.0 #346 era); a DELETE was never added because the launch path didn't force it.

2. Cascade FKs are inconsistent across the 36 migrations. Some tables added later (everything from migration 015 onward — ai_provider_settings, lifebooks, recovery_codes, model_downloads, brain_*, dxt_*, federation_*, oauth_pending_signin, oauth_pkce_pending, external_agent_tokens, promotion_offers, user_onboarding_state, user_risk_profiles, user_credential_vault_meta, draft_email_calls, draft_email_eval_runs) carry ON DELETE CASCADE on their user_id FK. Earlier tables (preferences, decisions, signals, oauth_tokens, twin_profiles, behavioral_patterns, cross_domain_traits, briefings, spend_records, trust_tier_audit, feedback_events, sessions, domain_autonomy_policies, escalation_triggers, preference_proposals, proactive_scans, twin_exports, skill_gap_log, eval_runs, accuracy_metrics, memory_wings, memory_drawers, memory_closets, memory_tunnels, knowledge_entities, knowledge_triples, episodic_memories, entity_codes, connected_accounts) reference users(id) with no ON DELETE clause — meaning a naive DELETE FROM users WHERE id = $1 would fail with a foreign-key violation. Tables further downstream of those (e.g. candidate_actions, decision_outcomes, explanation_records, execution_plans, execution_results) chain off decisions(id) and would orphan unless deleted in dependency order.

3. No transactional contract. Because a per-user wipe has to touch ~30 tables in dependency order, doing it in DELETE statements outside a transaction means a partial failure leaves the user in a half-deleted state — twin profile gone but decisions remain, or memory pages gone but the OAuth token still poll-able. The right primitive is a single CRDB serializable transaction (withTransaction in @skytwin/db) that either deletes everything or rolls back.

The fix is one route, one transactional cascade query, and one Settings-page confirmation dialog — plus a privacy-policy update so the cascade list is publicly documented.

Verified Current State

Verified 2026-05-25 against jayzalowitz/launch-skytwin-demo @ 8bb5379.

Per-user table audit (cascade gap by table)

Verified by scanning packages/db/src/migrations/*.sql + packages/db/src/schemas/schema.sql for REFERENCES users(id):

Tally: ~30 tables need explicit DELETE before the final DELETE FROM users; ~25 cascade automatically. The implementation must walk them in dependency order, in a single serializable transaction.

What's Working Well (Do Not Touch)

• The requireOwnership middleware (apps/api/src/middleware/require-ownership.ts) already gates /:userId routes correctly — a new DELETE inherits the right auth boundary with zero change.
• The CASCADE FKs added in migrations 015+ are the right pattern for new tables. Don't retrofit them onto the old tables in this PR; do the explicit DELETE walk first, then file a follow-up to add ON DELETE CASCADE where it's still missing (less risky, lets us validate the explicit walk works first).
• withTransaction in packages/db/src/connection.ts already gives serializable-isolation semantics. Use it. Do not re-implement transaction handling.

Proposed Change

New endpoint

DELETE /api/users/:userId — authenticated, ownership-gated, requires the request body to confirm intent.

Behavior:

1. Open a serializable transaction.
2. Walk the dependency graph in order: child tables first (decision_outcomes → candidate_actions → decisions; episodic_memories → knowledge_triples → memory_drawers → memory_rooms → memory_wings; etc.).
3. DELETE FROM users WHERE id = $1 last.
4. Commit. On any failure inside the transaction, ROLLBACK and return 500 with a generic message (do not leak the per-table failure).
5. Audit log a single user_deleted event before COMMIT (in a separate gdpr_deletion_log table that does NOT carry the deleted user_id — only a timestamp, IP hash, and table-count manifest, so the audit survives the delete).

New Settings UI

In apps/web/public/js/pages/settings.js, add a "Danger zone" section with a "Delete everything" button that opens a confirmation modal:

• Type the user's full email to confirm.
• Read-aloud cascade summary: "This will delete N tables of data including your twin profile, X decisions, Y memory pages, Z signals."
• "Permanently delete" button is disabled until the typed email matches.
• On success: clear localStorage, route to #/ with a final toast "All your data has been deleted."

Privacy policy update

docs/privacy.html gains a "Right to erasure" subsection that lists every table the delete cascade touches and links to the endpoint.

Implementation Details

Endpoint contract

DELETE /api/users/:userId
Headers: Cookie: session=... (or dev auth bypass)
Body:
  {
    "confirmEmail": "user@example.com"   // must match users.email exactly
  }

Response 200:
  {
    "deleted": true,
    "tablesAffected": 30,
    "rowsAffected": {
      "decisions": 1247,
      "signals": 18394,
      "brain_pages": 4521,
      ...
    },
    "deletedAt": "2026-05-25T12:34:56Z"
  }

Response 400 if confirmEmail missing/mismatched:
  { "error": "confirm_email_required", "message": "Pass confirmEmail matching the user's email." }

Response 404 if user not found:
  { "error": "user_not_found" }

Response 500 on any DB failure:
  { "error": "deletion_failed", "message": "..." }
  (Transaction rolled back; no partial state.)

Cascade walk

apps/api/src/services/user-deletion.ts (new) exports deleteUserCascade(userId) that wraps the walk in withTransaction. The walk has three tiers:

1. decision_id-chained tables first — execution_results (WHERE execution_plan_id IN (SELECT id FROM execution_plans WHERE decision_id IN (SELECT id FROM decisions WHERE user_id = $1))), then execution_events, execution_plans, explanation_records, decision_outcomes, candidate_actions (all WHERE decision_id IN (SELECT id FROM decisions WHERE user_id = $1)).
2. Direct per-user, no ON DELETE CASCADE — loop the TABLES_NEEDING_EXPLICIT_DELETE constant (~30 tables from the audit above), each WHERE user_id = $1.
3. DELETE FROM users WHERE id = $1 last — the migration-015+ ON DELETE CASCADE tables follow automatically.

Each DELETE returns its row count, which accumulates into a manifest: Record<string, number> returned to the caller. Inside the transaction, write a row to gdpr_deletion_log containing (now(), hashIp(reqIp), Object.keys(manifest).length, JSON.stringify(manifest)) — see the new migration below. Any throw rolls back everything.

New migration

packages/db/src/migrations/060-gdpr-deletion-log.sql creates an append-only gdpr_deletion_log table: (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), deleted_at TIMESTAMPTZ NOT NULL DEFAULT now(), ip_hash STRING NOT NULL, table_count INT NOT NULL, manifest_json JSONB NOT NULL) plus an index on deleted_at DESC. Schema deliberately omits the deleted userId — the audit survives the cascade.

Acceptance Criteria

1. DELETE /api/users/:userId with body {confirmEmail: "user@example.com"} returns 200 with the documented manifest.
2. After a successful DELETE, SELECT COUNT(*) FROM <every table> returns 0 for that userId across all ~55 per-user tables.
3. Without confirmEmail, the request returns 400 — the user is NOT deleted.
4. With wrong confirmEmail, the request returns 400 — the user is NOT deleted.
5. A simulated mid-transaction failure (e.g. a DELETE FROM signals that throws) leaves the user fully intact — no rows deleted across any table.
6. The gdpr_deletion_log table gains exactly one row per successful delete, containing the table-count manifest but NOT the deleted userId.
7. Settings page renders a "Delete everything" button; clicking opens a type-to-confirm modal; the confirm button is disabled until the typed email matches exactly.
8. docs/privacy.html lists every cascade-affected table by name and links to the endpoint.
9. A user who deletes their account and re-onboards via the sample-profile path (P0.2 "Try with a sample profile" silently does nothing #363) gets a fresh user row with no carryover from the deleted account.

Testing Plan

Total: +11 tests (3 unit, 6 integration, 1 E2E, 1 security).

Rollback Plan

This is a one-way operation by design. Rollback the code (revert PR) is trivial; rollback the data requires the user has a backup. The PR ships a one-line CHANGELOG warning that the new DELETE is unrecoverable.

If we discover after deploy that the cascade has a bug that silently leaves rows behind, the fix is to add the missing table to the walk and ship a backfill migration that re-runs the delete for any user IDs present in gdpr_deletion_log (cross-check against tables that should be empty).

Database migration 060-gdpr-deletion-log.sql is additive only — rollback is DROP TABLE gdpr_deletion_log if needed.

Effort Estimate

Total: ~8h (1 day for one engineer).

Files Reference

Out of Scope

• Adding ON DELETE CASCADE to the ~30 tables that lack it today. Separate follow-up — risky and orthogonal to shipping a working delete. The explicit walk works without it.
• "Soft delete" mode (mark as deleted, retain rows for N days). The user requested erasure; honor it. If we later need a 30-day grace period, that's a UX option, not a default.
• Cross-instance federation cleanup (federation_peers table). The federation-peer cascade is auto, but if the user has paired with another SkyTwin instance, that instance still has its own copy — out of scope here.
• Server-side data backup before delete (the user is welcome to use skytwin-backup export first; see P2 backup issue).
• IP-hash salt rotation in gdpr_deletion_log — single salt for v1 is fine.

Related

• [MASTER] Pre-launch epic: ship to real strangers #357 — Master pre-launch epic
• [EPIC D] Safety & privacy debt #361 — Epic D (parent)
• feat: milestone 1.5 scope expansion (v0.2.0.0) #5 — original tracking issue for safety/privacy debt
• docs/safety-model.md § "User data lifecycle" — currently silent on deletion; update in same PR
• docs/privacy.html — must list the cascade for the public privacy policy to be honest
• The future /api/backup/export companion endpoint (P2 follow-up: "skytwin-backup export / restore") pairs with this one to complete the data-portability story.