Make the chat bell use the online lease instead of the raw status flag #490

Summary

The plain-message chat bell decides "online" from the raw users.status flag. Push decides it from the lease internal.is_user_online: status ONLINE and online_at within 2 minutes. This issue makes the bell use the lease, so the lease is the one Postgres answer to "is this user online".

Why it matters: this is the reader side of the stuck-Online bug (#496). Push and email fire only from a bell row. After #489, the bell and push still disagree for members whose last beat is 2 to 4 minutes old. The bell skips them, so push and email never fire. This issue gives the bell the push rule. After #489, the user-visible gain is bells about 2 minutes sooner after a crash or sleep. The main gain is one online rule. If #489 is late, this change alone also gives the 2 stuck users their bells back.

The change is one migration, a filter edit in one script, comment edits in two others and one CHANGELOG entry. Admin "Online Now" stays on the raw flag (Decision 1).

Root cause

Links are at db481158b. These files are unchanged on main.

• The bell reads the raw flag. 10-func-notifications.sql:379-385 joins public.users and filters AND (u.status IS NULL OR u.status != 'ONLINE'). The IS NULL arm never matches, because status is not null (02-users.sql:32).
• Push reads the lease. 07-4-push-notifications-pgmq.sql:211-213 calls internal.is_user_online(v_receiver_id).
• The lease has two source copies with the same rule.
  • 07-4:161-175 and 10-1-func-users.sql:167-178.
  • seed.sql runs 07-4 first (:1273) and 10-1 second (:4983), so the 10-1 body wins. 10-1:192 then pins search_path = public.
  • In migrations, only 20260519200000_scripts_functions_triggers_parity.sql defines it, at :441 and :3096. The later body wins there too.
• The bell base body. 20261009120300_resolve_mentions_by_user_id.sql:101-176 is byte-identical to scripts/10-func-notifications.sql:314-389 (checked with diff). It is the newest migration that defines the bell function. Its body calls internal.mentioned_user_ids, which only that migration creates.
• No other delivery reader uses the raw flag. Email and the bell broadcast follow the bell row. The other raw reads are the index at 02-users.sql:87, the cron sweep at 16-cron-jobs.sql:14, and admin.
• Admin reads the raw flag. Online Now is admin-stats.controller.ts:53-57. The Users badge is users.tsx:218.

Who sees a change

Measured locally, rolled back. The probe in Verify covers the first four rows. An earlier local probe with the same shape added the AWAY row.

• Stale window. The cron runs every 2 minutes, so the flag can say ONLINE for about 4 minutes after the last beat. These members now get a bell, and push or email if those are on.
• Other statuses. BUSY, INVISIBLE and TYPING (01-enum.sql:26) get a bell today and still do.
• Live readers. useHandleUserStatus.ts:9 writes ONLINE every 60 s while the tab is visible. The lease is 120 s, so one missed beat is tolerated. A hidden tab writes OFFLINE (:70-78), so hidden readers got bells before and still do.
• One message, one answer. now() is fixed for the whole transaction, so the bell and push agree for any one message.

Decisions

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

1. Should admin Online Now switch to the lease? Ruled 2026-10-15: no. Keep the raw flag, with no code change.
   • The Users badge also reads the raw flag. A lease count would disagree with it on the same page.
   • A TypeScript cutoff would be a third copy of the 2-minute TTL.
   • Alternative, only if the badge changes too: filter on online_at newer than 2 minutes after admin-stats.controller.ts:57, commented as a mirror of internal.is_user_online.

Depends on

• Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489 should ship first, but it does not block this issue.
  • Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489 edits 10-1-func-users.sql:133-152. This issue edits the is_user_online doc comment below it.
  • Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489's migration is 20261014120000. Keep that timestamp reserved for Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489, even if Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489 has not landed.
  • The two migrations touch different functions, so either order of apply is safe.
• Make presence heartbeats HOT updates and drop the duplicate username index #491 takes a later migration timestamp than this one. It does not touch this path. The lease reads by primary key, not through idx_users_online_status.
• Stop exposing every user's online status and last-active time #434 (20261009120500_hide_user_presence_columns.sql): no order needed. The bell is SECURITY DEFINER, so the column revoke does not apply to it.
• Resolve chat mentions by user id, not by username text #415 (20261009120300_resolve_mentions_by_user_id.sql) must be live in production. The new body calls internal.mentioned_user_ids. Step 0 checks this.

Fix plan

Work on main. Read AGENTS.md and packages/supabase/CLAUDE.md first. Do the steps in this order.

Step 0. Production read-only checks

The maintainer runs these in the production SQL editor before any write. Production reads run in the main session, never in a subagent. The editor shows only the last result, so run each statement on its own (highlight it, then Run selected).

1. The gates, in one row:

select current_user,
       to_regprocedure('internal.mentioned_user_ids(text,text)') is not null as has_mentions_helper,
       (select md5(prosrc) from pg_proc where proname = 'create_regular_message_notifications') as bell_md5;

2. Run this and save the output word for word before any write. Rollback restores that saved text, not the repo text.

select pg_get_functiondef('public.create_regular_message_notifications()'::regprocedure);

3. Run each of these on its own and read it:

select pg_get_functiondef('internal.is_user_online(uuid)'::regprocedure);

select pg_get_functiondef('public.update_user_online_at()'::regprocedure);

Go only if all of these hold. If one differs, stop and report to the maintainer.

• current_user is postgres.
• has_mentions_helper is t. If it is f, Resolve chat mentions by user id, not by username text #415 is not live, and the new body would fail every message insert.
• bell_md5 is 4695c90da035f8d12ddeb93c64979f60 (measured locally on the 20261009120300 body). If it differs, take only the text between AS $function$ and the closing $function$ in the saved output. Diff it against 20261009120300_resolve_mentions_by_user_id.sql:109-175, ignoring whitespace. Go only if they match.
• is_user_online shows status = 'ONLINE' and online_at > now() - interval '2 minutes'.
• update_user_online_at sets NEW.online_at := now() on each ONLINE write. Before Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489 it stamps on every write, which is also fine. Without that stamp, the lease would call every member offline.

Step 1. Write the migration

Create packages/supabase/migrations/<ts>_bell_reads_online_lease.sql by hand. Use 20261014120100. If any file in packages/supabase/migrations/ sorts at or after it, use a timestamp newer than the newest file. Never use 20261014120000.

• Header: -- Pairs with scripts/10-func-notifications.sql.
• Body: copy 20261009120300_resolve_mentions_by_user_id.sql:101-176 unchanged. Then make the two step 5 edits from Step 2A below.
• Keep SECURITY DEFINER and SET search_path = public inline.
• Do not recreate the trigger. Do not change grants. CREATE OR REPLACE keeps the ACL.
• Do not run generate:functions-parity-migration. It rewrites 20260519200000, which production already applied (generate-functions-parity-migration.ts:35,154).
• internal.is_user_online already ships in 20260519200000:3096, so this migration meets the "ship dependencies you call" rule.

Step 2. Mirror the scripts and add the CHANGELOG entry

A. packages/supabase/scripts/10-func-notifications.sql, step 5 of create_regular_message_notifications.

Replace this comment line (:359):

    -- 5) Create notifications only for members whose notif_state = 'ALL' and who are not online or the sender

with:

    -- 5) Notify ALL-state members who are not the sender and not online.
    --    Online is the lease in internal.is_user_online, the rule push uses too.

Replace these 7 lines (:379-385):

    FROM public.channel_members cm
    JOIN public.users u ON u.id = cm.member_id
    WHERE cm.channel_id = NEW.channel_id
      AND cm.member_id  != NEW.user_id
      AND (u.status IS NULL OR u.status != 'ONLINE')
      AND cm.mute_in_app_notifications = FALSE
      AND cm.notif_state = 'ALL';

with these 6 lines:

    FROM public.channel_members cm
    WHERE cm.channel_id = NEW.channel_id
      AND cm.member_id  != NEW.user_id
      AND NOT internal.is_user_online(cm.member_id)
      AND cm.mute_in_app_notifications = FALSE
      AND cm.notif_state = 'ALL';

Nothing else in the function changes. Dropping the join loses no rows: member_id references public.users(id) on delete cascade (08-channel_members.sql:7).

B. packages/supabase/scripts/07-4-push-notifications-pgmq.sql:161. Replace the comment line -- Check if user is currently online (suppress push if online) with:

-- Same rule as internal.is_user_online in 10-1-func-users.sql. Seed runs 10-1 later, so its body wins. Edit both.

Keep the function below it unchanged. Removing this copy would make 07-4 call a function first defined in 10-1, which breaks the seed-order rule in packages/supabase/CLAUDE.md:21.

C. packages/supabase/scripts/10-1-func-users.sql, the is_user_online doc comment. Replace the whole block from /** to */ just above CREATE OR REPLACE FUNCTION internal.is_user_online (:157-166 at db481158b). The new block:

/**
 * Function: is_user_online
 * The one Postgres rule for "online": status ONLINE and online_at within 2 minutes.
 * Callers: create_regular_message_notifications (bell) and enqueue_push_notification (push).
 * 07-4-push-notifications-pgmq.sql keeps a copy that this later body replaces. Edit both.
 */

Do not touch the function body, the COMMENT ON FUNCTION, the GRANT or the ALTER at :192.

D. apps/hocuspocus.server/CHANGELOG.md. Add this bullet under ## [Unreleased], in its ### Fixed list. If Step 1 took a later timestamp, use the real migration name.

- **The plain-message bell now uses the online lease, as push does.** A member
  counts as online only when `status` is `ONLINE` and `online_at` is under
  2 minutes old. Before, a member left `ONLINE` with a stale or NULL
  `online_at` got no bell, push or email. Apply the Supabase migration
  `20261014120100_bell_reads_online_lease` (#490).

Step 3. Apply locally and regenerate the seed

Run these from the repo root:

bun run --filter @docs.plus/supabase_back seed
docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres < packages/supabase/migrations/<ts>_bell_reads_online_lease.sql
git diff packages/supabase/seed.sql

• seed.sql should change only from Steps 2A to 2C. If it shows other hunks, check that each one comes from a scripts/ change already on main, for example Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489. Stop and report to the maintainer any hunk that does not.
• Do not run types, and do not commit apps/webapp/src/types/supabase.ts ([Tracking] Stuck-Online users and the presence review follow-ups #496 Decision 1). No signature changes.
• Land the migration, the three script edits, the regenerated packages/supabase/seed.sql and the CHANGELOG entry as one change.

Then run the probe in Verify.

Step 4. Production hand paste

The maintainer runs this; an agent never does. No app deploy is needed, because no app code changes. In the production SQL editor:

1. Run select current_user;. It must return postgres.
2. Paste the Step 1 migration file into one tab, unchanged, and run it once.
3. Run the production query in Verify.

CREATE OR REPLACE FUNCTION takes no table lock and does no rewrite. Inserts already running finish on the old body; later ones use the new body.

A later db push re-runs this CREATE OR REPLACE with no harm. Do not mark it applied with migration repair. The #496 section The next db push covers that push.

Rollback

• Production: the maintainer runs the create_regular_message_notifications definition saved in Step 0. pg_get_functiondef output has no trailing ;. Add one if you paste it next to other statements.
• Repo: if bunx supabase migration list shows no Remote value for this migration, revert the change. Otherwise keep the migration file and add a forward migration with the saved definition.

Verify

Local probe. Local only, never production. It drops users_id_fkey, which takes an ACCESS EXCLUSIVE lock on public.users even inside a rolled-back transaction. It makes its own workspace, so it also runs after a fresh db reset. Save it as a file outside the repo and run docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres -q < <file>.

BEGIN;
ALTER TABLE public.users DROP CONSTRAINT users_id_fkey;  -- rolled back
CREATE TEMP TABLE p AS SELECT g AS n, gen_random_uuid() AS id FROM generate_series(0, 4) g;
INSERT INTO public.users (id, username, email, status, online_at)
SELECT id, 'i2p' || n, 'i2p' || n || '@probe.test',
       (ARRAY['OFFLINE','ONLINE','ONLINE','ONLINE','OFFLINE'])[n + 1]::user_status,
       (ARRAY[now(), now(), now() - interval '5 minutes', NULL, now()])[n + 1] FROM p;
INSERT INTO public.workspaces (id, name, slug, created_by) SELECT 'i2pw', 'i2pw', 'i2pw', id FROM p WHERE n = 0;
INSERT INTO public.channels (id, workspace_id, slug, name, heading_id, type, created_by)
SELECT 'i2p', 'i2pw', 'i2p', 'i2p', 'i2p', 'PUBLIC', id FROM p WHERE n = 0;
INSERT INTO public.channel_members (channel_id, member_id, notif_state, mute_in_app_notifications)
SELECT 'i2p', id, 'ALL', false FROM p WHERE n > 0;
INSERT INTO public.messages (user_id, channel_id, content, html) SELECT id, 'i2p', 'hi', '<p>hi</p>' FROM p WHERE n = 0;
SELECT count(*) AS bells FROM public.notifications WHERE channel_id = 'i2p' AND type = 'message';
ROLLBACK;

User 0 sends. Members 1 to 4 are fresh ONLINE, stale ONLINE, NULL ONLINE, and OFFLINE.

• Expect bells = 3 after the apply. Measured locally: 3 with the new body.
• The current body gives bells = 1 (measured locally). Run the probe before the apply to see it fail.

Local function settings. Run:

select proname, prosecdef, proconfig, proacl from pg_proc
where proname in ('create_regular_message_notifications', 'is_user_online');

Expect, measured locally before and after the new body:

create_regular_message_notifications | t | {search_path=public} | {postgres=X/postgres,service_role=X/postgres}
is_user_online                       | f | {search_path=public} | {=X/postgres,postgres=X/postgres,service_role=X/postgres}

Production, after Step 4. Read-only, main session only:

select pg_get_functiondef(p.oid) like '%is_user_online(cm.member_id)%' as uses_lease,
       pg_get_functiondef(p.oid) like '%u.status%' as reads_flag,
       pg_get_userbyid(p.proowner) as owner, p.prosecdef, p.proconfig
from pg_proc p where p.proname = 'create_regular_message_notifications';

Expect uses_lease = t, reads_flag = f, owner = postgres, prosecdef = t, proconfig = {search_path=public}. Measured locally in a rolled-back transaction: the current body gives f | t, and the new body gives t | f.

Scale

• About +0.01 ms per message at 10 members, and about +2 ms at 1,000 online members (measured locally, 101k users). The lease costs about 2 µs per member.
• When members are offline, fan-out of about 100 ms per 1,000 members dominates both bodies. Measure workspace presence join fan-out before changing it #495 owns it.

Acceptance criteria

• Step 0 passed: current_user = postgres, has_mentions_helper = t, the md5 or the whitespace-free body diff matched, and the definition was saved.
• scripts/10-func-notifications.sql step 5 calls NOT internal.is_user_online(cm.member_id) and has no JOIN public.users.
• The 07-4 and 10-1 comments name each other's copy, and the 10-1 comment names the bell and push as callers.
• Every seed.sql hunk comes from Steps 2A to 2C or from a scripts/ change already on main.
• packages/supabase/migrations/<ts>_bell_reads_online_lease.sql exists, sorts after every other migration on main, and is not 20261014120000.
• The [Unreleased] ### Fixed bullet is in apps/hocuspocus.server/CHANGELOG.md and names the real migration.
• apps/webapp/src/types/supabase.ts is not in the change.
• The local probe prints bells = 3, and the function settings query matches Verify.
• The production query returns uses_lease = t, reads_flag = f, owner = postgres, prosecdef = t, proconfig = {search_path=public}.

Out of scope

• Admin Online Now and the Users badge. Decision 1 keeps the raw flag.
• Deleting the 07-4 copy of internal.is_user_online. It would add a forward reference across seed files. The pointer comments in Step 2 cover the drift risk.
• Revoking EXECUTE on internal.is_user_online from public. The internal schema is not exposed (packages/supabase/config.toml:13). Stop exposing every user's online status and last-active time #434 already blocks client reads of both columns.
• Dropping SET search_path to get inlining back. Lint hardening needs it, and the gain is about 2 ms per 1,000 members.
• Tests. The probe is a one-off check, not a repo file. AGENTS.md §Test Policy asks for no new test here.
• Always create a plain-message bell row. Rejected in Decide whether plain chat messages always create a bell row #494 (closed). This issue keeps the online gate.
• Siblings. The stamp guard, the index, the Status writer, the client rule and fan-out each have their own issue; see [Tracking] Stuck-Online users and the presence review follow-ups #496.

Generated by Claude Code