Notify members who are mentioned in the first message of a heading chat #330

Parent

#328. Related: #293

What to build

A mention in the first message of a new heading chat notifies nobody who never opened that heading chat. @everyone in a first message fails the same way. The cause is trigger order: the mention fan-out runs before the step that adds workspace members to the chat. After this fix, a first-message mention reaches the named member, as every later message already does. In the same change, the chat mention picker stops listing people who left the workspace.

Acceptance criteria

• In a new PUBLIC chat, a first message with @username creates one mention notification for that member.
• In a new PUBLIC chat, a first message with @everyone creates one channel_event notification for each other active member.
• Unread counts do not change: a member added by the first message has unread_message_count = 1 after it, not 0 and not 2.
• Auto-enrolment still runs on PUBLIC chats only. No other chat type gains channel_members rows.
• fetch_mentioned_users no longer returns a user whose workspace_members.left_at is set, for an empty query and for a typed query.
• packages/supabase/scripts/ and one new paired migration carry the same SQL.
• packages/supabase/seed.sql is regenerated by its script, never edited by hand.
• Types are regenerated. If apps/webapp/src/types/supabase.ts changes, the change includes it.
• The packages/supabase/CLAUDE.md bullet that names increment_unread_count_on_new_message as the enroller names the new function. The COMMENT ON texts match the new split.

Blocked by

None — can start now.

Agent brief

Type: AFK — an agent can finish this alone.

Category: bug

Current behavior:

• All message fan-outs are AFTER INSERT triggers on public.messages. PostgreSQL fires triggers of one kind in name order.
• create_everyone_notifications and create_mention_notifications sort before increment_unread_count. So they read channel_members before enrolment adds anyone.
• The mention fan-out joins channel_members (packages/supabase/scripts/10-func-notifications.sql:72). Its trigger is at :86.
• The @everyone fan-out loops over channel_members (:218-222). Its trigger is at :255.
• Enrolment is the PUBLIC-only INSERT inside increment_unread_count_on_new_message (:487-512). Its trigger is at :534.
• Reproduced on local Supabase on 2026-09-28, in a rolled-back transaction: first-message @username gave 0 notices, and first-message @everyone gave 0 notices.
• fetch_mentioned_users builds both rosters from channel_members joined to channels (packages/supabase/scripts/10-functions.sql:745-748 and :765-768). Neither roster query checks workspace_members.left_at.
• Reproduced in the same transaction: after B left, a search for B's username still listed B.

Desired behavior:

• A new BEFORE INSERT trigger on public.messages does the enrolment. A BEFORE trigger fires before every AFTER trigger, whatever the names. Do not rename triggers to fix the order.
• increment_unread_count_on_new_message keeps only its unread UPDATE, unchanged. That UPDATE still takes a fresh row from 0 to 1.
• Both roster queries in fetch_mentioned_users add a join to public.workspace_members on workspace_id = _workspace_id, member_id = u.id and left_at IS NULL.

Build notes. Read these before you write SQL.

1. The seed offset changes meaning. In the AFTER trigger, NEW is already in messages, so LIMIT 1 OFFSET 1 reads the previous message. In a BEFORE trigger, NEW is not there yet. Use LIMIT 1 with no offset, or the seed skips the real previous message.
2. Move the enrolment INSERT as it is. Keep the channel_type_var = 'PUBLIC' gate and the comment above it. Keep wm.left_at IS NULL and wm.member_id != NEW.user_id.
3. Keep the NOT EXISTS guard. check_duplicate_member (10-2-func-channels.sql:343-370) raises an exception on a duplicate. ON CONFLICT does not catch a trigger exception.
4. Mark the new function like its siblings. Use SECURITY DEFINER and SET search_path = public. Add it to both ALTER FUNCTION lists at the end of 10-func-notifications.sql. It writes other users' channel_members rows.
5. Trigger shape. Use FOR EACH ROW, WHEN (NEW.type IS DISTINCT FROM 'notification'), and end with RETURN NEW. No existing BEFORE INSERT trigger on messages returns NULL. One of them, validate_message_medias (10-3-func-message.sql), raises on bad media. A raise rolls back the whole insert, and the enrolment with it.
6. Side effects that stay the same. channel_members.notif_state defaults to MENTIONS (08-channel_members.sql:13). So the regular-message fan-out, which needs ALL, sends nothing new.
7. Restate definer in the migration. In scripts/, increment_unread_count_on_new_message gets SECURITY DEFINER and its search_path only from the ALTER FUNCTION lists. CREATE OR REPLACE FUNCTION resets what it does not state. So the migration states security definer and set search_path = public on both trigger functions, as the model migration does.
8. The roster join works as the caller. fetch_mentioned_users is not SECURITY DEFINER. The workspace_members_select policy admits rows for a workspace the caller belongs to (13-RLS.sql:109-111). Do not make it SECURITY DEFINER. In scripts/ it gets its search_path from an ALTER FUNCTION line (10-functions.sql:1006), so the migration states set search_path = public on it too.

Where to start:

• packages/supabase/scripts/10-func-notifications.sql: increment_unread_count_on_new_message, the increment_unread_count trigger, and the hardening lists at the end.
• packages/supabase/scripts/10-functions.sql: fetch_mentioned_users.
• Model the migration on packages/supabase/migrations/20260921120000_chat_mention_token_rule.sql. Write it by hand. Make it idempotent with create or replace function and drop trigger if exists. Do not use the functions parity generator.
• Name the migration <YYYYMMDDHHMMSS>_<snake_case_name>.sql, with a timestamp later than every file already in packages/supabase/migrations/.

Line numbers are hints as of 2026-09-28; the agent searches by symbol.

Rules that apply:

• packages/supabase/CLAUDE.md §Supabase, these bullets:
  • the PUBLIC-only enrolment bullet;
  • "Migrations and scripts/* are paired";
  • "Migrations must ship dependencies they call";
  • "Editing an uncommitted migration file requires re-apply";
  • the types regeneration rule.
• apps/webapp/src/components/chatroom/CLAUDE.md §Mention Picker: the "Mention notifications (SQL)" bullet. The token rule stays as it is.
• .cursor/rules/supabase.mdc for SQL style.
• AGENTS.md §Test Policy: add no test file. The rolled-back check below is the proof.

Verify:
Local db reset does not run migrations/ (packages/supabase/config.toml [db.migrations] enabled = false). It loads only seed.sql. So test the migration by hand over the old schema, as production gets it. Run every command from the repo root.

1. Save the check below as check.sql outside the repo. Run it with docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres -v ON_ERROR_STOP=1 < check.sql.
2. Prove it by sabotage. Before any edit, run bun run --filter @docs.plus/supabase_back reset, then the check. On 2026-09-28 it printed mention 0, @everyone 0, unread 1, and left_member_listed 1. The unread line is a guard that must not change, not a proof.
3. Write the migration. Apply it over this old schema with docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres -v ON_ERROR_STOP=1 < packages/supabase/migrations/<file>.sql. Apply it twice: both runs must succeed.
4. Run the check. It must print mention 1, @everyone 1, unread 1, and left_member_listed 0.
5. Run select proname, prosecdef, proconfig from pg_proc where proname in ('increment_unread_count_on_new_message', '<new function>');. Both rows must show prosecdef = t and search_path=public.
6. Edit scripts/ to match. Run bun run --filter @docs.plus/supabase_back reset. It regenerates seed.sql and resets the local database.
7. Run the check and the pg_proc query again. Both must give the same results as steps 4 and 5.
8. Apply the migration once more over this new schema. It must succeed, and the check must still pass.
9. Run bun run --filter @docs.plus/supabase_back types.
10. In the app, sign in as two members. Member A sends @<B's username> as the first message in a heading chat that B never opened. B sees the notification. No UI changes, so one theme and one screen size are enough.

begin;
do $$
declare
  a uuid := '00000000-0000-4000-8000-00000000e001';
  b uuid := '00000000-0000-4000-8000-00000000e002';
  b_name text;
  ws varchar(36) := 'verify-enrol-ws';
  m1 uuid; m2 uuid;
begin
  -- on_auth_user_created makes each public.users row and its username.
  insert into auth.users (id, email) values
    (a, 'verify-enrol-a@example.test'), (b, 'verify-enrol-b@example.test');
  select username into b_name from public.users where id = b;
  perform set_config('verify.a', a::text, true);
  perform set_config('verify.b', b::text, true);
  perform set_config('verify.b_name', b_name, true);
  insert into public.workspaces (id, name, slug, created_by) values (ws, ws, ws, a);
  insert into public.workspace_members (workspace_id, member_id) values (ws, a), (ws, b);
  insert into public.channels (id, workspace_id, slug, name, created_by, type) values
    ('verify-enrol-h1', ws, 'verify-enrol-h1', 'h1', a, 'PUBLIC'),
    ('verify-enrol-h2', ws, 'verify-enrol-h2', 'h2', a, 'PUBLIC');
  insert into public.messages (channel_id, user_id, content)
    values ('verify-enrol-h1', a, '@' || b_name || ' first message') returning id into m1;
  insert into public.messages (channel_id, user_id, content)
    values ('verify-enrol-h2', a, '@everyone first message') returning id into m2;
  raise notice 'mention on a first message, notices for B: %',
    (select count(*) from public.notifications where message_id = m1 and receiver_user_id = b);
  raise notice '@everyone on a first message, notices for B: %',
    (select count(*) from public.notifications where message_id = m2 and receiver_user_id = b);
  raise notice 'unread for B in h1 after one message: %',
    (select unread_message_count from public.channel_members
      where channel_id = 'verify-enrol-h1' and member_id = b);
  update public.workspace_members set left_at = now() where workspace_id = ws and member_id = b;
end $$;
set local role authenticated;
select set_config('request.jwt.claims',
  json_build_object('sub', current_setting('verify.a'), 'role', 'authenticated')::text, true);
select count(*) as left_member_listed
  from public.fetch_mentioned_users('verify-enrol-ws', current_setting('verify.b_name'))
 where id = current_setting('verify.b')::uuid;
rollback;

The check runs as postgres, and the last query runs as member A through set local role authenticated. It makes its own two users, so it works right after a reset. It rolls everything back.

Out of scope

• Renaming existing triggers.
• Any change to who the unread UPDATE or the notification fan-outs select, beyond the enrolment order.
• Other fetch_mentioned_users changes, such as the empty-query order or its result limits.
• The pad @ list — Add a pad @ list where Members open a Comment and Headings insert a link #349. It waits on this issue.