Resolve chat mentions by user id, not by username text #415

Summary

A mention picked in the composer is stored in messages.html as a node with the user's id (data-id) and username (data-label). The mention triggers ignore that id. They match each @token in messages.content against users.username when the message is inserted. Users can change their username, and someone else can then claim the freed name. So a mention node with a stale label notifies the new holder of that name. The feed still links the same mention to the original user.

A stale label comes from a saved draft, or from a mention copied out of an older message. Nobody can force a user to give up a name, so this is a routing flaw, not account takeover.

• Severity: Low. The review lists it under "Info only".
• Area: Supabase notification triggers
• Source: security review of 2026-10-06, username reuse note

Production check (2026-10-06, read-only)

authenticated holds UPDATE on users.username.

Where

• Mention fan-out, create_mention_notifications: packages/supabase/migrations/20260921120000_chat_mention_token_rule.sql:67-76 (join public.users u on u.username = tokens.username).
• Mention-message check, create_regular_message_notifications: same file :171-181.
• Script mirror: packages/supabase/scripts/10-func-notifications.sql:71 and :299. The trigger WHEN clause reads content only (:86-90).
• Mention node: the composer inserts command({ id: item.id, label: item.username }) at apps/webapp/src/components/chatroom/components/MessageComposer/helpers/MentionList.tsx:134. @tiptap/extension-mention renders it as a span with data-type="mention", data-id and data-label. sanitizeMessageContent keeps data-* attributes.
• A click on a mention in the feed opens the profile by id: apps/webapp/src/components/chatroom/hooks/useMentionClick.tsx:12-15.
• Username is user-editable: packages/supabase/scripts/13-RLS.sql:84-86 (UPDATE grant includes username). Format and uniqueness: packages/supabase/scripts/02-users.sql:13-16.

Fix plan

1. In create_mention_notifications, build the receivers from two sources, then keep today's filters (sender skipped, channel member, mute, notif_state):
   • Each mention node in new.html gives its data-id. Skip an id that is not a uuid, such as everyone. Read each span tag on its own, so the match does not depend on attribute order.
   • Each text token in new.content still matches by username, unless it equals the data-label of a mention node in the same message.
   • When new.html is null, use the text rule alone, as today.
2. Apply the same receiver rule to the mention-message check in create_regular_message_notifications. The migration header says all fan-outs share one token rule, so the two must match.
3. Keep both functions security definer; notifications has no INSERT policy for authenticated (packages/supabase/CLAUDE.md). Keep the trigger WHEN clauses: every mention node also writes @label into content.
4. Repo rules:
   • Add a new migration with create or replace function for both functions.
   • Mirror it in packages/supabase/scripts/10-func-notifications.sql, then regenerate seed.sql with bun run --filter @docs.plus/supabase_back seed.
   • Run bun run --filter @docs.plus/supabase_back types. No signature changes, so expect no diff.
5. Update apps/webapp/src/components/chatroom/CLAUDE.md §Mention Picker. Its "Mention notifications (SQL)" bullet and its label note say the SQL reads plain text only. This issue changes that rule.

Layer: the fix stays in the two SQL trigger functions. Do not change the composer, the mention node, or fetch_mentioned_users. Trust does not change: the sender already writes both content and html, and the member join still limits receivers.

Out of scope

• Typed text with no picked node. It keeps today's rule and names whoever holds the username when the message is sent. That is the user the picker shows for that name.
• A cooldown before a released username can be claimed again.
• A new mentioned_user_ids column. The ids are already in messages.html.
• MCP post_chat_message. It strips every @ (apps/hocuspocus.server/src/modules/mcp/domain/toChatPost.ts:7), so it sends no mentions today.

Acceptance criteria

• A message whose mention node holds user A's id, with a label that is now user B's username, notifies A and not B.
• A typed @username with no mention node still notifies the current holder of that name.
• @everyone still notifies every member who has not muted the channel.
• A message that mentions a channel member still sends no message notification to the other members.
• A mention node that names a non-member notifies nobody, and the sender is never notified.
• A message with html null behaves as today.

Verify

• Apply the migration locally: docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres < packages/supabase/migrations/<file>.sql.
• In psql, inside a transaction that you roll back, insert one test message per acceptance case. Then read the new rows in public.notifications for that message_id.
• In the app, mention a user from the picker, send, and check that the bell shows the mention for that user.

Related

• Stop clients from writing server-owned columns on chat messages #400 (server-owned messages columns; html stays client-written)

Generated by Claude Code