Skip a chat notification email when the member already read the message in the channel #497

Summary

A member who reads a mention, reply or @everyone in the channel still gets the email for it. The email waits 15 minutes, then checks only notifications.readed_at. Reading in the channel never sets that column, so the email goes out anyway.

This came out of the stuck-Online review (#496). It was found while #494 was checked, and it is live today. It does not depend on any other issue in that set. Ruled as its own issue on 2026-10-15 (#494 Decision 3).

Root cause

• The email skip reads only readed_at. process_email_queue skips a row when pending_email.readed_at is not null (07-5-email-notifications-pgmq.sql:454-461). The digest pre-pass does the same (:559-568).
• Only the bell writes readed_at. Its two writers are client clicks: markNotificationAsRead.ts:5 and markAllNotificationsAsRead.ts:6.
• A channel read moves a different cursor. advance_read_cursor updates channel_members.last_read_seq only (10-functions.sql:1278-1320). Each message has a global seq (05-0-message.sql:11). Each notification row keeps message_id and channel_id (07-0-notifications.sql:14-15).
• Measured locally, rolled back (2026-10-15, Decide whether plain chat messages always create a bell row #494 review): a mention read in the channel still enqueued its email: mention_rows|mention|1, after_read|1|0, email_after_read|1.

Who sees it: members with email_enabled true. Email is off by default (07-5:300).

Decisions

The recommended answer comes first. Maintainer rulings are posted as a comment.

1. Treat a channel read as read for email? Ruled 2026-10-16: yes. Notion sends a mention push only "if you don't view a mention within 5 minutes" (Notion help). An email for a message the member has seen is noise. The bell rows stay as they are, so no new interaction appears.

Depends on

Nothing. No open issue in #496 edits this file.

Fix plan

Step 1. Scripts

In packages/supabase/scripts/07-5-email-notifications-pgmq.sql:

1. In process_email_queue, add n.message_id to the loop select (:426-440). Change the skip test at :455 to:

   if pending_email.readed_at is not null
      or exists (
          select 1
            from public.channel_members cm
            join public.messages m on m.id = pending_email.message_id
           where cm.channel_id = pending_email.channel_id
             and cm.member_id = pending_email.user_id
             and cm.last_read_seq >= m.seq
      ) then

   Keep the error_message as 'Notification already read'.

2. In compile_digest_emails, extend the pre-pass where at :568 the same way:

   and (
       n.readed_at is not null
       or exists (
           select 1
             from public.channel_members cm
             join public.messages m on m.id = n.message_id
            where cm.channel_id = n.channel_id
              and cm.member_id = eq.user_id
              and cm.last_read_seq >= m.seq
       )
   );

A row with no message_id (for example content_change) fails the join, so it keeps today's behaviour. channel_members (channel_id, member_id, last_read_seq) is indexed (11-indexes.sql:79), and messages.id is the primary key.

Step 2. Migration

1. Create packages/supabase/migrations/<timestamp>_skip_email_when_channel_read.sql. Take the next free timestamp after the newest file on main.

2. Put in it the two full create or replace function bodies from the edited script. Before you copy, diff each script body against its newest migration copy:

   • compile_digest_emails: migrations/20261009115900_scope_heading_channels.sql.
   • process_email_queue: migrations/20260519200000_scripts_functions_triggers_parity.sql.

   If a body differs in more than this change, stop and ask. Production may not match the script.

3. Keep each function's grants as they are (07-5:1008-1013, service role only).

4. Run bun run --filter @docs.plus/supabase_back seed. Apply locally: docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres -v ON_ERROR_STOP=1 < packages/supabase/migrations/<file>.sql.

5. Function bodies do not reach the generated types, so do not run types. This follows the [Tracking] Stuck-Online users and the presence review follow-ups #496 Decision 1 rule.

Step 3. Production paste (maintainer)

Paste the migration in the Supabase SQL editor. Then check the bodies are live:

select proname, prosrc ilike '%last_read_seq%' as has_read_check
  from pg_proc
 where proname in ('process_email_queue', 'compile_digest_emails');

Expect t for both. Rollback: paste the two previous bodies from the migrations named in Step 2.

Verify

Run a rolled-back local probe. Use a member with email_enabled true and email_frequency immediate:

1. Post a mention of that member. Expect one email_queue row, pending.
2. Call advance_read_cursor as that member, up to the message seq. Do not touch readed_at.
3. Set the row's scheduled_for to the past, then select public.process_email_queue();. Expect the row skipped with Notification already read.
4. Repeat without step 2. Expect the row to leave pending, as today.
5. End with ROLLBACK.

Acceptance criteria

• A mention, reply or @everyone read in the channel before its email is due sends no email. It is marked skipped.
• An unread one still sends, on the same schedule as today.
• content_change emails do not change.
• The script, the migration and the regenerated seed.sql land together.
• The production check in Step 3 returns t for both functions.

Out of scope

• Marking bell rows read when the channel is read. That is a new interaction, and Decide whether plain chat messages always create a bell row #494 rejected it.
• Push. Push already skips a member online by the lease.

Generated by Claude Code