Title: Deleted users still appear across the app as deleted_<uuid>
Description
- Problem: Account deletion is a soft delete:
deleteUser sets deletedAt, rewrites the username to deleted_<uuid>, nulls displayName/avatarUrl and keeps the row (user-repository.ts:137-155). Every listing query still joins users without filtering deletedAt, so the anonymized handle leaks into the UI. Example: https://plotwist.app/en-US/movies/157336 → Reviews tab shows a 5-star review signed deleted_b8ac47655ca344abaa16debbcd959200.
-
Where it leaks — all of these leftJoin(schema.users, ...) with no deletedAt filter:
| Repository |
Surface |
reviews-repository.ts (selectReviews) |
reviews on media pages |
review-replies-repository.ts (selectReviewReplies) |
replies |
likes-repository.ts (selectLikes) |
who-liked list |
followers-repository.ts (selectFollowers) |
followers / following |
recommendations-repository.ts (selectReceivedRecommendations) |
received recommendations |
list-repository.ts (selectLists) |
public lists |
user-activities.ts (selectUserActivities) |
activity feed (activity owner, and the FOLLOW_USER target) |
Also nested and unfiltered: buildReviewInfo / buildReplyInfo in user-activities.ts build an author object via subquery, so a LIKE_REVIEW activity can surface a deleted author even if the parent row is filtered.
-
Reproduction: delete an account that has reviews → open any media page it reviewed → the review is still listed under deleted_<uuid>.
Possible solutions
-
Hide the content (backend-only, smallest diff). Turn each leftJoin(users) into innerJoin(users, and(<join cond>, isNull(users.deletedAt))). The whole row disappears.
Pros: one-line change per repository, no API/UI/i18n work, consistent across web + iOS at once.
Cons: community history (ratings, reviews, list authorship) silently disappears; likeCount / replyCount still count likes and replies made by deleted users, so the counters can drift from the visible list.
-
Show a generic "Deleted user". Keep the row, return an isDeleted flag (or null username) from the API and render a neutral label + default avatar on the client.
Pros: preserves ratings and discussion threads; profile links can be disabled instead of 404'ing.
Cons: touches backend + web + iOS + 7 locales; every place that renders a username needs the fallback.
-
Hard delete / cascade on account deletion. Make deleteUserService also remove reviews, replies, likes, follows and activities.
Pros: no orphan data at all, simplest privacy story.
Cons: irreversible, destroys threads other users replied to, and requires a backfill for accounts already soft-deleted.
-
Mixed. Anonymize what carries value (reviews / lists, option 2) and delete what is purely social (follows, likes, activities, recommendations — option 3).
Whichever is picked, deleteUserService should probably also clean up follow edges and activity rows, and a backfill is needed for the accounts already soft-deleted.
Out of scope: the deleted user's own profile page (/users/deleted_<uuid>) and search — listUsersByUsernameLike already filters deletedAt.
Title: Deleted users still appear across the app as
deleted_<uuid>Description
deleteUsersetsdeletedAt, rewrites the username todeleted_<uuid>, nullsdisplayName/avatarUrland keeps the row (user-repository.ts:137-155). Every listing query still joinsuserswithout filteringdeletedAt, so the anonymized handle leaks into the UI. Example: https://plotwist.app/en-US/movies/157336 → Reviews tab shows a 5-star review signeddeleted_b8ac47655ca344abaa16debbcd959200.Where it leaks — all of these
leftJoin(schema.users, ...)with nodeletedAtfilter:reviews-repository.ts(selectReviews)review-replies-repository.ts(selectReviewReplies)likes-repository.ts(selectLikes)followers-repository.ts(selectFollowers)recommendations-repository.ts(selectReceivedRecommendations)list-repository.ts(selectLists)user-activities.ts(selectUserActivities)FOLLOW_USERtarget)Also nested and unfiltered:
buildReviewInfo/buildReplyInfoinuser-activities.tsbuild anauthorobject via subquery, so aLIKE_REVIEWactivity can surface a deleted author even if the parent row is filtered.Reproduction: delete an account that has reviews → open any media page it reviewed → the review is still listed under
deleted_<uuid>.Possible solutions
Hide the content (backend-only, smallest diff). Turn each
leftJoin(users)intoinnerJoin(users, and(<join cond>, isNull(users.deletedAt))). The whole row disappears.Pros: one-line change per repository, no API/UI/i18n work, consistent across web + iOS at once.
Cons: community history (ratings, reviews, list authorship) silently disappears;
likeCount/replyCountstill count likes and replies made by deleted users, so the counters can drift from the visible list.Show a generic "Deleted user". Keep the row, return an
isDeletedflag (ornullusername) from the API and render a neutral label + default avatar on the client.Pros: preserves ratings and discussion threads; profile links can be disabled instead of 404'ing.
Cons: touches backend + web + iOS + 7 locales; every place that renders a username needs the fallback.
Hard delete / cascade on account deletion. Make
deleteUserServicealso remove reviews, replies, likes, follows and activities.Pros: no orphan data at all, simplest privacy story.
Cons: irreversible, destroys threads other users replied to, and requires a backfill for accounts already soft-deleted.
Mixed. Anonymize what carries value (reviews / lists, option 2) and delete what is purely social (follows, likes, activities, recommendations — option 3).
Whichever is picked,
deleteUserServiceshould probably also clean up follow edges and activity rows, and a backfill is needed for the accounts already soft-deleted.Out of scope: the deleted user's own profile page (
/users/deleted_<uuid>) and search —listUsersByUsernameLikealready filtersdeletedAt.