Notify the right people when a chat message contains an @

[HMarzban]

Problem

Three insert triggers on public.messages read an @ token, and they do not agree on what a token is. So some messages notify the wrong people, and some notify nobody.

On 14ab7f9c1. SQL paths are under packages/supabase/. An ALL-state member is a channel member whose chat toggle shows All notifications (notif_state = 'ALL').

1. An uppercase letter ends a username early. The mention fan-out matches (^|[^a-z0-9_-])@<username>($|[^a-z0-9_-]) (scripts/10-func-notifications.sql:52). The @everyone body and its trigger use the same edge (:225, :269). An uppercase letter is outside [a-z0-9_-], so it counts as an edge. So @bobSmith notifies bob, and @everyoneElse notifies the whole channel. The code comment (:223-224), the May migration (migrations/20260512094333_notifications_regex_and_dedupe.sql:3-5), and the chatroom rule (apps/webapp/src/components/chatroom/CLAUDE.md:109) all promise full-token matches.
2. The regular-message fan-out uses a looser test. Its trigger fires only when content !~ '@[A-Za-z0-9_]+|@everyone' (scripts/10-func-notifications.sql:353). Any @ before a word character stops it, even inside bob@example.com. The mention fan-out needs a character outside [a-z0-9_-] before the @ (:52). In bob@example.com that character is b, so the address mentions nobody. An uppercase letter is outside that set, so BOB@example.com mentions a member named example. Offline ALL-state members (:328-334) then get no row. A hand-typed @Bob, or a package name such as @tiptap/core, does the same when no member has that username. The unread badge still counts the message, because increment_unread_count runs on every insert (:537-540). What is lost is the notification row, and with it the panel entry and any push or email.
3. The mention fan-out notifies the sender. The @everyone and regular-message fan-outs skip the sender (:231, :331). The mention query does not (:49-62). The picker hides the sender (apps/webapp/src/components/chatroom/components/MessageComposer/helpers/MentionList.tsx:86), but typed text still reaches the trigger. Push skips a row that a user sends to themselves (scripts/07-4-push-notifications-pgmq.sql:204-207). The email queue has no such check (scripts/07-5-email-notifications-pgmq.sql:296-409). So a sender with email on can get an email about their own message.
4. everyone is a legal username. The format check allows it (scripts/02-users.sql:13-17). Sign-up can derive it from a full name or an email address (scripts/10-1-func-users.sql:59-62). The Settings check accepts it (apps/webapp/src/components/settings/hooks/useUsernameValidation.ts:5, :22). Such a user gets a mention row beside the channel_event row on every @everyone.
5. The mention fan-out tests every user. It runs for every message that holds an @ (scripts/10-func-notifications.sql:96). It then tests each row of public.users with a pattern built for that row (:49-52). Its cost grows with the number of users, on the send path.

Steps to reproduce

Use two signed-in members, A and B, in one heading chat. B's username is bob. To close the mention picker, type a space. Do not pick a row.

Case 1, an uppercase letter after a username (default settings):

1. A sends hi @bobSmith.
2. B opens the notification panel. B has a mention from A.

Case 2, an email address (needs All notifications):

1. B clicks the chat's notification toggle twice. Its tooltip then reads All notifications. The default is Mentions only.
2. B closes every docs.plus tab, then waits four minutes.
3. A sends mail me at bob@example.com. Then A sends see you later.
4. B opens the same pad again and opens its notification panel. Only see you later has a row.

Case 3, a mention of yourself:

1. A sends note to self @<A's username>.
2. A opens the notification panel. A has a mention from A.

Acceptance criteria

• @bobSmith creates no mention row for bob. @bob, @bob., @bob, and @bob! still create one.
• @everyoneElse creates no channel_event rows. @everyone and @everyone! still reach the channel.
• A message whose only @ is inside an email address, such as bob@example.com or BOB@example.com, creates no mention row. It gives offline ALL-state members a message row, as a message with no @ does.
• A message whose @ tokens name no channel member also gives offline ALL-state members a message row.
• A message that mentions a channel member still gives that member one mention row and no message row. Other ALL-state members still get no message row for it, as today.
• A token that names a user who is not a channel member, or a member who muted the chat, still creates no mention row.
• A sender who types their own @username gets no mention row.
• @everyone creates no mention row, even for a user whose username is everyone.
• The mention fan-out looks up the tokens in the message by username. It does not test each public.users row with a pattern.
• The change lands in the source SQL script and in a paired migration, and a local db reset applies it.
• A message with type = 'notification', such as the Pad title change notice, still creates no mention, channel_event, or message row.
• The chatroom rule on mention notifications states the new token rule and the sender rule.

Agent Brief

Category: bug
Summary: Give the three chat notification triggers one @ token rule, and stop the mention fan-out from notifying the sender.

Current behavior:
Three insert triggers do not agree on what an @ token is. The mention and @everyone fan-outs treat an uppercase letter as the end of a token. So @bobSmith notifies bob, and @everyoneElse reaches the whole channel. The regular-message fan-out skips any message with @ before a word character. So an email address, or a token that names nobody, notifies no ALL-state member. The mention fan-out also notifies the sender, and it treats everyone as a username. It finds receivers by testing each user row with a pattern.

Desired behavior:
One token rule serves all three triggers. A token is @ and then the longest run of the ASCII letters a–z and A–Z, digits, _, and -. The @ must be at the start or after a character outside that set. A token names a user only when it equals the username exactly.

• The mention fan-out reads the tokens once and looks them up by username. It skips the sender and never resolves everyone to a user.
• The @everyone fan-out fires only for the exact token everyone.
• The regular-message fan-out skips a message only when it holds @everyone, or a token that names a channel member other than the sender.

A trigger WHEN clause cannot hold a subquery. So a check against channel members must run in the function body.

Key interfaces:

• create_mention_notifications() — the mention fan-out and its insert trigger.
• create_everyone_notifications() — the @everyone fan-out and its trigger condition.
• create_regular_message_notifications() — the fan-out to ALL-state members, and its trigger condition.
• public.users.username — unique and lowercase, format ^[a-z][a-z0-9_-]{2,29}$.
• channel_members.notif_state — ALL, MENTIONS (the default), or MUTED.
• notification_category values mention, channel_event, and message.

Out of scope

• Notifying a mention that an edit adds. Both fan-outs run on insert only, and an edit is an update. This needs a diff of old and new tokens, and a product ruling.
• message rows for other ALL-state members when a message mentions someone. The trigger skips them by its written design.
• The two rows that a named member gets from an @everyone message: one mention row and one channel_event row.
• Reserving the username everyone in sign-up and in the Settings username check.
• Matching tokens without regard to case. A hand-typed @Bob still mentions nobody.
• The message row that an offline ALL-state original author gets beside the reply row.
• A new notification_category value.
• Hand edits to the generated seed file or the generated types file.

Notes

The live trigger conditions come from packages/supabase/migrations/20260908093000_notify_document_title_change.sql:17-36. The live function bodies come from packages/supabase/migrations/20260623120000_chat_media_attachments.sql, with the mention pattern at :607. A new migration must replace both.

Pair the script and the migration, as packages/supabase/CLAUDE.md:27 requires. Change packages/supabase/scripts/10-func-notifications.sql, verify it with a local db reset, then add the paired migration. Run the types script after the SQL change (packages/supabase/CLAUDE.md:15). Include the types file if it changes. Never edit packages/supabase/seed.sql (packages/supabase/CLAUDE.md:12). A new notification_category value reaches two consumers that ignore the type (packages/supabase/CLAUDE.md:35).

An edit goes through updateMessage, a plain update (apps/webapp/src/api/messages/updateMessage.ts:30-33). The composer calls it at apps/webapp/src/components/chatroom/utils/outboundMessagePipeline.ts:257-261. An edit fires only the update triggers that refresh previews, set edited_at, and check media (packages/supabase/scripts/10-3-func-message.sql:162, :195, :513). None of them writes a notification.

The reply overlap in Out of scope is also a code trace. The regular-message fan-out excludes only the sender (packages/supabase/scripts/10-func-notifications.sql:331). The reply fan-out notifies the original author (:101-102, :180-184). So a plain reply gives an offline ALL-state original author both rows.

The email case needs notif_state = 'ALL', and the default is MENTIONS (packages/supabase/scripts/08-channel_members.sql:13). The toggle labels and their order are in apps/webapp/src/components/chatroom/components/ChatroomToolbar/components/NotificationToggle.tsx:14-27 and apps/webapp/src/components/chatroom/hooks/useNotificationToggle.ts:10-12. For a message that fits in one chunk, the stored text comes from the composer's getText() (apps/webapp/src/components/chatroom/utils/outboundMessagePipeline.ts:80). A longer message stores the chunker's text, with no separator between blocks. #264 adds that separator. The token rule needs it when a paragraph opens with an @. A picked mention becomes @<username> in that text (apps/webapp/src/components/chatroom/components/MessageComposer/helpers/MentionList.tsx:121).

Case 2 waits because the regular-message fan-out reads users.status (packages/supabase/scripts/10-func-notifications.sql:332), not presence. A tab close writes OFFLINE (apps/webapp/src/hooks/useHandleUserStatus.ts:99-116). If that write fails, the update-user-status cron job sets it within four minutes (packages/supabase/scripts/16-cron-jobs.sql:8-16).

The evidence is a code trace on 14ab7f9c1, confirmed by two verifiers. It was not reproduced against a database or in a browser.