Decide whether plain chat messages always create a bell row #494

Summary

This issue records one option from the stuck-Online review: always create the bell row for a plain chat message, even when the receiver is online. This body calls it "always-create". The recommendation is not to take it. The maintainer rules, and the issue then closes as not planned.

Why it came up: today a member whose users.status is stuck at ONLINE gets no bell row for plain messages. Always-create would remove that gate. #490 fixes the same harm in a smaller way: the bell reads the lease internal.is_user_online, not the raw flag.

Why not always-create:

• A channel read does not mark the bell row read. So active readers get unread bells for messages they already read.
• Email checks only notifications.readed_at. So a member with email on also gets a real email for those messages (probe A).
• A read fix (a channel read clears plain-message bells) is a new interaction with no majority to follow (Decision 2). It also makes a live reader's bell badge go up and down on every plain message. useNotificationCount.ts:86-102 adds 1 on each INSERT broadcast. It subtracts 1 on a read that is not a client click.
• Make the chat bell use the online lease instead of the raw status flag #490 alone gives the stuck member a bell and keeps a live reader quiet. See Make the chat bell use the online lease instead of the raw status flag #490 Verify.

Part of #496.

Problem

The gate always-create would remove

create_regular_message_notifications() filters at 10-func-notifications.sql:379-385. Today the line is AND (u.status IS NULL OR u.status != 'ONLINE'). #490 replaces it with AND NOT internal.is_user_online(cm.member_id). Always-create would delete that line.

A channel read never sets readed_at

• Opening a channel calls advance_read_cursor (useReadCursor.ts:26, 1 s debounce).
• It writes only channel_members (10-functions.sql:1325-1329).
• notifications.readed_at has two writers, both client clicks: markNotificationAsRead.ts:5 and markAllNotificationsAsRead.ts:6.

Email has no online gate

• Every notifications INSERT may queue an email when email_enabled is true (07-5-email-notifications-pgmq.sql:300; default false).
• The only read check is readed_at: process_email_queue at :455 and the digest pre-pass at :562-568.

So, for plain messages, the presence line is the only thing that keeps active readers out of email.

Evidence (measured locally, rolled back)

Probe A (in Verify), a live reader with email immediate, after always-create:

today_bell_rows|0
always_bell_rows|1
after_read|1|0          <- read in the channel: bell still unread, channel unread 0
email_after_read|1      <- process_email_queue enqueued the email
email_status|processing

The output is the same on today's body and on the #490 body.

The cross-document gap

This gap exists today. #490 makes it smaller, because the raw flag can stay ONLINE for about 4 minutes after the last beat (#490 "Stale window").

• The lease is global, not per document. The heartbeat refreshes it every 60 s while any pad or chat tab is visible (useHandleUserStatus.ts:34-40; routes at routePolicy.ts:9).
• So a member who reads document A gets no plain-message bell, push or email from document B. This lasts for the visible session, plus up to 2 minutes.
• The bell is never made later. Only channel_members.unread_message_count records the message (10-func-notifications.sql:567-577). The member sees it only by opening document B.

Decisions

Maintainer rulings are posted as a comment.

1. Take always-create? Ruled 2026-10-15: no. Keep the Make the chat bell use the online lease instead of the raw status flag #490 lease gate, and close this issue as not planned. Without a read fix it emails content already read (probe A). With a read fix it adds an unruled interaction and a flickering badge. The only harm it removes is the cross-document gap, which exists today and is smaller after Make the chat bell use the online lease instead of the raw status flag #490.
2. Keep the presence gate on the bell row as a deliberate exception to the AGENTS.md rule on Google Docs, Word and Notion? Ruled 2026-10-15: yes, as a deliberate exception. It follows from Decision 1.
   • Notion's help page, read 2026-10-15, ties mobile push to viewing. A push comes "if you don't view a mention within 5 minutes", or "if you don't have Notion open on your device". It names no rule that skips the inbox item.
   • A search on 2026-10-14 found no primary source for Google Docs or Word.
   • So the gate on the bell row does not match Notion.
3. File the readed_at email gap as its own issue? Yes. [Tracking] Stuck-Online users and the presence review follow-ups #496 lists it under "New issues to file"; once filed, it moves to [Tracking] Stuck-Online users and the presence review follow-ups #496's child table. It is live today, with or without always-create.
   • Measured locally, rolled back: a mention read in the channel still enqueues its email (mention_rows|mention|1, after_read|1|0, email_after_read|1).
   • Mention, reply and channel_event rows have no presence gate (10-func-notifications.sql:123-127, :160-212, :268-273). @everyone rows use type channel_event (:289).
   • Smallest shape: process_email_queue and the digest pre-pass skip a row when the receiver's channel_members.last_read_seq >= messages.seq. Bell rows stay as they are, so no new interaction.

Depends on

• Make the chat bell use the online lease instead of the raw status flag #490 must ship before any always-create work. Both edit the same WHERE line.
• Fix the Status writer's tab race, reload gap and silent errors #492 Decision 1 changes the size of the gap in Decision 2.

Fix plan

If Decision 1 is "No" (recommended)

1. File the Decision 3 issue, and add it to [Tracking] Stuck-Online users and the presence review follow-ups #496's child table.
2. Post the ruling comment, with the Decision 2 exception.
3. Post one comment on Make the chat bell use the online lease instead of the raw status flag #490 that links here, under the heading "Considered, not taken".
4. Close this issue as not planned. No code changes.

If Decision 1 is "Yes"

Open a separate build issue. This issue's edit history keeps the full pre-review draft: the read-fix SQL, migration, prod paste and rollback. The gate drop and the read fix ship together, in one migration and one prod transaction; never ship the gate drop alone. The read fix needs its own ruling under the AGENTS.md rule. Probe A is the check: it must print after_read|0|0, email_after_read|0 and email_status|skipped (measured locally with both changes, rolled back).

Verify

Probe A runs on the current local database, needs no reset, and ends in ROLLBACK. Save it outside the repo, then run:

docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres -At -q -v ON_ERROR_STOP=1 < probe.sql

BEGIN;
SELECT cm.channel_id AS ch, cm.member_id AS snd,
       (SELECT u.id FROM public.users u WHERE u.id <> cm.member_id ORDER BY u.created_at LIMIT 1) AS rcv
  FROM public.channel_members cm
  JOIN public.channels c ON c.id = cm.channel_id AND NOT c.mute_in_app_notifications
 LIMIT 1 \gset
INSERT INTO public.channel_members (channel_id, member_id, notif_state) VALUES (:'ch', :'rcv', 'ALL')
ON CONFLICT (channel_id, member_id) DO UPDATE SET notif_state = 'ALL', mute_in_app_notifications = false;
UPDATE public.users SET status = 'ONLINE', online_at = now(),
       notification_preferences = '{"email_enabled":true,"email_frequency":"immediate"}'
 WHERE id = :'rcv';
-- (a) Today: a live reader gets no bell row.
INSERT INTO public.messages (user_id, channel_id, content, html) VALUES (:'snd', :'ch', 'ac today', '<p>x</p>');
SELECT 'today_bell_rows', count(*) FROM public.notifications WHERE receiver_user_id = :'rcv' AND message_preview LIKE 'ac today%';
-- (b) Always-create: drop the users join and the presence line (either form).
DO $do$
DECLARE src text;
BEGIN
  src := pg_get_functiondef('public.create_regular_message_notifications()'::regprocedure);
  src := replace(src, E'    JOIN public.users u ON u.id = cm.member_id\n', '');
  src := replace(src, E'      AND (u.status IS NULL OR u.status != ''ONLINE'')\n', '');
  src := replace(src, E'      AND NOT internal.is_user_online(cm.member_id)\n', '');
  IF position('is_user_online(cm.member_id)' in src) > 0 OR position('u.status' in src) > 0 THEN
    RAISE EXCEPTION 'always-create shape not applied';
  END IF;
  EXECUTE src;
END $do$;
INSERT INTO public.messages (user_id, channel_id, content, html) VALUES (:'snd', :'ch', 'ac shape', '<p>x</p>');
SELECT 'always_bell_rows', count(*) FROM public.notifications WHERE receiver_user_id = :'rcv' AND message_preview LIKE 'ac shape%';
-- (c) The receiver reads the channel as themself.
SELECT set_config('request.jwt.claims', json_build_object('sub', :'rcv', 'role', 'authenticated')::text, true) \g /dev/null
SET LOCAL ROLE authenticated;
SELECT public.advance_read_cursor(:'ch', (SELECT max(seq) FROM public.messages WHERE channel_id = :'ch')) \g /dev/null
RESET ROLE;
SELECT 'after_read', count(*) FILTER (WHERE readed_at IS NULL) AS unread_bells,
       (SELECT unread_message_count FROM public.channel_members WHERE channel_id = :'ch' AND member_id = :'rcv') AS channel_unread
  FROM public.notifications WHERE receiver_user_id = :'rcv' AND message_preview LIKE 'ac shape%';
-- (d) Make the 15-minute email due and run the queue.
UPDATE public.email_queue eq SET scheduled_for = now() - interval '1 second'
  FROM public.notifications n WHERE n.id = eq.notification_id AND n.receiver_user_id = :'rcv' AND n.message_preview LIKE 'ac shape%';
SELECT 'email_after_read', (public.process_email_queue())->>'enqueued' AS enqueued;
SELECT 'email_status', eq.status FROM public.email_queue eq JOIN public.notifications n ON n.id = eq.notification_id
 WHERE n.receiver_user_id = :'rcv' AND n.message_preview LIKE 'ac shape%';
ROLLBACK;

The guard raises an error if the DO block cannot build its shape, so a silent no-op cannot pass. It matches the call is_user_online(cm.member_id), because the #490 comment also names internal.is_user_online.

Acceptance criteria

• The Decision 3 issue exists and is a row in [Tracking] Stuck-Online users and the presence review follow-ups #496's child table.
• A maintainer ruling comment answers Decisions 1 to 3, and records the Decision 2 exception.
• If Decision 1 is "No": Make the chat bell use the online lease instead of the raw status flag #490 has a comment that links here, and this issue is closed as not planned.
• If Decision 1 is "Yes": a separate build issue exists, and this issue links to it.

Out of scope

• The stuck-Online fix itself. Make the chat bell use the online lease instead of the raw status flag #490 owns the lease gate. Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489 owns the stamp guard.
• A per-document lease. New work. It goes to an Ideas Discussion only if the maintainer asks.
• The readed_at email gap. Decision 3 sends it to its own issue.
• Tests. Nothing ships under the recommended path.

Generated by Claude Code