Data Cleanup

The three categories

When deleting a user, every piece of data falls into one of three categories:

Delete: Data that only belongs to the user and has no value to others. Sessions, tokens, TOTP secrets, passkeys, recovery codes, personal settings.

Anonymize: Data that is shared with others but includes the user’s identity. Notes in a shared organization (other members still need the content), comments, messages. Replace the user’s name and ID with a placeholder.

Keep: Data that you must retain for legal or business reasons. Financial transactions, tax records, audit logs (anonymized). Some jurisdictions require keeping certain records for years.

Delete: user-only data

These tables are straightforward — every row belongs to a single user:

// Full deletion — every row is user-specific
db.prepare("DELETE FROM email_verifications WHERE user_id = ?").run(userId);
db.prepare("DELETE FROM recovery_codes WHERE user_id = ?").run(userId);
db.prepare("DELETE FROM totp_secrets WHERE user_id = ?").run(userId);
db.prepare("DELETE FROM passkeys WHERE user_id = ?").run(userId);
db.prepare("DELETE FROM webauthn_challenges WHERE user_id = ?").run(userId);
db.prepare("DELETE FROM device_sessions WHERE user_id = ?").run(userId);
db.prepare("DELETE FROM api_keys WHERE user_id = ?").run(userId);
db.prepare("DELETE FROM deletion_requests WHERE user_id = ?").run(userId);
db.prepare("DELETE FROM memberships WHERE user_id = ?").run(userId);

Anonymize: shared data

Notes in a shared organization should not be deleted when the creator leaves — other members still need them. Replace the user’s identity with a placeholder:

// Anonymize — replace user identity, keep content
db.prepare("UPDATE notes SET created_by = 'deleted-user' WHERE created_by = ?").run(userId);

After anonymization, the note still exists with its content, but created_by shows “deleted-user” instead of the original user’s ID.

For apps with comments, messages, or other user-generated content:

// Comments: keep the text, anonymize the author
db.prepare(
  "UPDATE comments SET author_id = 'deleted-user', author_name = 'Deleted User' WHERE author_id = ?",
).run(userId);

// Messages: anonymize or delete based on your app's model
// If messages are private (1:1), you might delete them entirely
// If messages are in group channels, anonymize

Keep: legal records

Some data must be retained even after deletion:

// Audit logs: anonymize but keep the events
db.prepare(
  "UPDATE audit_log SET user_id = 'deleted-user', user_email = 'deleted' WHERE user_id = ?",
).run(userId);

// Financial records: keep as-is (legal requirement)
// Do NOT delete invoices, payment records, or tax-related data

[!WARNING] Consult your legal team about data retention requirements. Some industries (finance, healthcare) have specific rules about how long records must be kept. Deleting data you are legally required to retain can be worse than keeping data the user asked you to delete.

Orphaned data

After deleting a user, check for orphaned records:

Empty organizations. If the user was the last member of an organization, the org has no members. You might want to delete the org and its data too, or mark it as inactive.

// Find orgs with no members after deletion
const orphanedOrgs = db
  .prepare(
    `
  SELECT o.id FROM organizations o
  LEFT JOIN memberships m ON o.id = m.org_id
  WHERE m.id IS NULL
`,
  )
  .all() as { id: string }[];

for (const org of orphanedOrgs) {
  db.prepare("DELETE FROM notes WHERE org_id = ?").run(org.id);
  db.prepare("DELETE FROM custom_roles WHERE org_id = ?").run(org.id);
  db.prepare("DELETE FROM invites WHERE org_id = ?").run(org.id);
  db.prepare("DELETE FROM organizations WHERE id = ?").run(org.id);
}

Pending invites. If the deleted user sent invites that have not been accepted, delete them:

db.prepare("DELETE FROM invites WHERE invited_by = ?").run(userId);

The deletion order

Delete tables in dependency order to avoid foreign key violations:

1. Tokens and secrets (email_verifications, recovery_codes, totp_secrets, passkeys, webauthn_challenges)
2. Sessions (device_sessions)
3. API keys
4. Deletion request itself
5. Anonymize shared data (notes, comments)
6. Memberships and invites
7. Orphaned orgs and their data
8. The user record (last)

Exercises

Exercise 1: Delete a test user and verify all related tables are cleaned up. Check each table with SELECT COUNT(*) FROM table WHERE user_id = 'deleted-id'.

Exercise 2: Create notes in a shared org, then delete the user who created them. Verify the notes still exist but created_by is 'deleted-user'.

Exercise 3: Delete the last member of an organization. Verify the orphaned org cleanup runs.