Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489

Summary

Some signed-in people stay ONLINE for good, though they left months ago. This is the stuck-Online bug, tracked in #496. This issue fixes its database side with one migration.

• The bug. Their online_at is NULL. The update-user-status sweep filters on online_at < …, and NULL < x is never true. So the sweep never sets them OFFLINE.
• The harm. The chat bell skips a person whose status is ONLINE. Push and email fire only from a bell row, so the stuck people get no bell, push or email for plain messages. Admin "Online Now" also counts them.
• The fix. A one-time data fix sets those rows OFFLINE. First, the trigger update_user_online_at stops stamping online_at on an OFFLINE write (Decision 1). So the data fix leaves online_at NULL, and does not invent activity.

This narrows #417. #417 made the trigger stamp on every status write. The heartbeat writes ONLINE, so it still stamps after this fix.

Root cause

• The sweep is not NULL-safe. 16-cron-jobs.sql:12-14 filters on online_at < now() - interval '2 minutes' and status = 'ONLINE'.
• How the NULL rows got there. From c825e32be (2026-02-12) to 28f90e0b3 (2026-05-15), Settings save sent the whole profile with status ONLINE and online_at NULL. The trigger then stamped only on a status change (20260519200000_scripts_functions_triggers_parity.sql:3065), so these rows were never stamped. The stuck-Online report found 2 such production rows, last seen in February or March. That count comes from the report, not from a measurement in this issue.
• The state cannot come back. Every insert into public.users leaves out status, so a new row is OFFLINE. Clients cannot write online_at (13-RLS.sql:85-92). Every ONLINE write stamps, with the Bring back push on Chrome and Android devices the server switched off #417 body and with the body below. So a one-time fix is enough, and the sweep needs no change (Decision 2).
• The trigger stamps every status write. 10-1-func-users.sql:133-152 sets NEW.online_at := now(); on BEFORE UPDATE OF status. The newest migration copy is 20261006120000_push_online_at_and_preference_checks.sql:9-24. So the sweep and a tab hide count as activity, and a data fix under this body stamps now() on the stuck rows.
• No real activity write is lost. The heartbeat writes ONLINE every 60 s (useHandleUserStatus.ts:34-41). Tab hide, network loss and unload write OFFLINE (:53-56). No client writes AWAY, BUSY, INVISIBLE or TYPING (01-enum.sql:26).

Who sees a change

• Bell (10-func-notifications.sql:383): the stuck people get bell rows again. Then they get push if subscribed, and email if email_enabled is true. This is the intended fix.
• Admin "Online Now" (admin-stats.controller.ts:53-57): drops by the stuck count.
• online_at readers (admin "Last Active", retention DAU, WAU and MAU, the DAU trend): a tab hide or a sweep no longer counts as activity. Last activity moves back by at most about 4 minutes (read from code, not measured). The stuck rows keep NULL.
• Push suppression (internal.is_user_online) and the heartbeat keep working, because an ONLINE write still stamps.

Decisions

Each question lists the recommended answer first. Maintainer rulings are posted as a comment.

1. Ship the stamp guard? Ruled 2026-10-15: yes. No delivery reader needs it: the lease needs status = 'ONLINE', so an OFFLINE stamp cannot change the bell or push. It keeps the data fix from stamping now() on the stuck rows, which would be a fake activity. It also makes online_at mean "last heartbeat" for Fix the Status writer's tab race, reload gap and silent errors #492. Alternative: ship the data fix alone, and accept that the stuck rows read as active on the fix day.
2. Clear the stuck rows with a one-time data fix, not a NULL-safe sweep? Ruled 2026-10-15: data fix. No writer can make the state again (Root cause). A sweep change must call cron.schedule. That adds a job-owner check, a second-job risk at db push, and a cron rollback. One-shot data fixes are hand-written migrations with no scripts/ mirror (packages/supabase/CLAUDE.md:22). A fresh seed has no such rows. Rejected alternative: add online_at is null or to the sweep predicate, as a guard against a future service-role writer.

Depends on

• Nothing must ship first. Stop exposing every user's online status and last-active time #434 is independent: the trigger is security definer, and the paste runs as postgres.
• Make the chat bell use the online lease instead of the raw status flag #490 and Make presence heartbeats HOT updates and drop the duplicate username index #491 take later migration timestamps than 20261014120000. Make the chat bell use the online lease instead of the raw status flag #490 edits 10-1-func-users.sql after this issue.

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 check

The maintainer runs this in the production SQL editor before any write. It is one statement, because the editor shows only the last result.

select json_build_object(
  'stuck', (select json_agg(json_build_object('id', id, 'updated_at', updated_at))
            from public.users where status = 'ONLINE' and online_at is null),
  'body_417', (select prosrc ~* 'new\.online_at := now\(\)' and prosrc !~* 'old\.status' and prosrc !~* 'new\.status'
               from pg_proc where proname = 'update_user_online_at'),
  'definer', (select prosecdef and proconfig = '{search_path=public}'
              from pg_proc where proname = 'update_user_online_at')
);

• stuck: record the ids for Step 4. Any count is fine, including null (none). The report found 2.
• body_417 and definer must both be true. Then production runs the Bring back push on Chrome and Android devices the server switched off #417 body, and Rollback restores it exactly. Measured locally: {"stuck" : null, "body_417" : true, "definer" : true}.

If body_417 or definer is false, stop and report to the maintainer.

Step 1. Write the migration

Create packages/supabase/migrations/20261014120000_presence_stamp_online_only.sql by hand. Do not use generate:functions-parity-migration, because it also writes unrelated functions.

-- #489. online_at now means "last ONLINE heartbeat", so an OFFLINE write keeps it.
-- Then a one-time data fix clears rows stuck ONLINE with a NULL online_at.
-- The function pairs with scripts/10-1-func-users.sql. The data fix has no scripts/ mirror (packages/supabase/CLAUDE.md:22).

create or replace function public.update_user_online_at()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
    -- Only an ONLINE write is activity. A sweep or tab-hide OFFLINE write keeps the last heartbeat.
    -- now() is timestamptz, which matches online_at whatever the session TimeZone.
    if new.status = 'ONLINE' then
        new.online_at := now();
    end if;
    return new;
end;
$$;

comment on function public.update_user_online_at() is 'Stamps online_at on every ONLINE write, so online_at is the last heartbeat that is_user_online and last-seen read.';
comment on trigger trigger_update_user_online_at on public.users is 'Stamps online_at on every ONLINE write; OFFLINE writes keep the last heartbeat.';

-- A NULL online_at never matches the sweep's "<", so these rows stayed ONLINE forever.
-- Every ONLINE write stamps now, so no writer can make this state again. A re-run matches 0 rows.
update public.users set status = 'OFFLINE' where status = 'ONLINE' and online_at is null;

The function comes first, so the update does not stamp the rows it clears. The signature does not change, so create or replace keeps the grants, including 29-lint-hardening.sql:211.

Step 2. Mirror the script and add the CHANGELOG entry

• packages/supabase/scripts/10-1-func-users.sql:137-142: use the same function body as the migration.
• Replace the comment text at 10-1:145 and :152 with the two migration comments.
• Keep CREATE TRIGGER at :147-150, and keep the hardening at :191 and :197.
• Do not edit 16-cron-jobs.sql.
• apps/hocuspocus.server/CHANGELOG.md: add this entry under ## [Unreleased], in its ### Fixed list. Copy it word for word.

- **A person who left is no longer stuck `ONLINE`.** A one-time data fix sets
  `ONLINE` rows with a NULL `online_at` to `OFFLINE`. The `update-user-status`
  sweep never matched them, so those people got no bell, push or email.
  `update_user_online_at` now stamps `online_at` only on an `ONLINE` write, so
  a sweep or tab hide keeps the last heartbeat. This narrows the 2.1.0 #417
  entry. Apply the Supabase migration `20261014120000_presence_stamp_online_only` (#489).

The release-product skill adds the release Migration table row later. Do not add it now.

Step 3. Apply locally and regenerate

Run these from the repository root.

docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres < packages/supabase/migrations/20261014120000_presence_stamp_online_only.sql
bun run --filter @docs.plus/supabase_back seed

Do not run types, and do not commit apps/webapp/src/types/supabase.ts (#496 Decision 1). Trigger bodies are not in that file (grep -c update_user_online_at gives 0).

Land these as one change: the migration, 10-1-func-users.sql, the regenerated packages/supabase/seed.sql and the CHANGELOG entry. Then run Verify.

Step 4. Production paste

No app deploy is needed. In the production SQL editor, open one tab. Paste begin;, then the Step 1 migration unchanged, then commit;. Run the tab once.

Then run this check. Put the Step 0 ids, quoted, in cleared, or delete that cell if Step 0 found none. Expect stuck 0, both flags true, and each cleared entry OFFLINE with null_kept true.

select json_build_object(
  'stuck', (select count(*) from public.users where status = 'ONLINE' and online_at is null),
  'cleared', (select json_agg(json_build_object('id', id, 'status', status, 'null_kept', online_at is null))
              from public.users where id in ('<id 1>', '<id 2>')),
  'guard_live', (select prosrc ~* 'if new\.status = ''ONLINE'' then' from pg_proc where proname = 'update_user_online_at'),
  'definer', (select prosecdef and proconfig = '{search_path=public}' from pg_proc where proname = 'update_user_online_at')
);

Measured locally in a rolled-back run, with one probe id: {"stuck" : 0, "cleared" : [{"id" : "…a3", "status" : "OFFLINE", "null_kept" : true}], "guard_live" : true, "definer" : true}.

Then check the heartbeat. Sign in from Home, then open any pad in a fresh tab. The heartbeat runs only on document-shell routes, such as a pad (AppProviders.tsx:45, routePolicy.ts:9). Keep that tab visible for 3 minutes, so at least one sweep runs. Then run this once, with your own email.

select u.status, now() - u.online_at as age
from public.users u join auth.users a on a.id = u.id
where a.email = '<your sign-in email>';

It must show ONLINE, with age under 90 s. A broken stamp would let the sweep set the row OFFLINE.

The next db push. Do not run db push for this issue alone. Do not run migration repair for 20261014120000. The next planned push re-runs it with no harm: the function is replaced again, and the update matches 0 rows. #496 The next db push owns that push.

Rollback

• Production: in one tab, paste begin;, then 20261006120000_push_online_at_and_preference_checks.sql:9-24, then commit;. Step 0 proved that this is the body production ran.
• Repo: if bunx supabase migration list (run from packages/supabase) shows no Remote value for 20261014120000, revert the commit. Otherwise keep the migration file, and add a forward migration with a later timestamp that holds those same lines. In that case also revert the script and the CHANGELOG entry, then run bun run --filter @docs.plus/supabase_back seed.
• Rollback does not set the cleared rows back to ONLINE. That is correct.

Verify

The logic is one branch, so this issue adds no test file (AGENTS.md §Test Policy). The rolled-back probe below checks it on the local stack and proves itself by sabotage. It creates its own users, so it works after a fresh db reset.

Save this as /tmp/p489-setup.sql. It makes one heartbeat user and one stuck row.

insert into auth.users (id, email, aud, role) values
  ('00000000-0000-4000-8000-0000000000a1', 'p489-beat@probe.local', 'authenticated', 'authenticated'),
  ('00000000-0000-4000-8000-0000000000a3', 'p489-null@probe.local', 'authenticated', 'authenticated');
update public.users set status = 'ONLINE' where id in ('00000000-0000-4000-8000-0000000000a1', '00000000-0000-4000-8000-0000000000a3');
update public.users set online_at = null where id = '00000000-0000-4000-8000-0000000000a3';  -- the stuck row

Save this as /tmp/p489-check.sql.

select status, online_at is null as null_kept from public.users where id = '00000000-0000-4000-8000-0000000000a3';
update public.users set online_at = now() - interval '50 seconds' where id = '00000000-0000-4000-8000-0000000000a1';
update public.users set status = 'ONLINE' where id = '00000000-0000-4000-8000-0000000000a1';  -- heartbeat
select online_at = now() as beat_stamps from public.users where id = '00000000-0000-4000-8000-0000000000a1';
update public.users set online_at = now() - interval '20 seconds' where id = '00000000-0000-4000-8000-0000000000a1';
update public.users set status = 'OFFLINE' where id = '00000000-0000-4000-8000-0000000000a1';  -- tab hide
select online_at = now() - interval '20 seconds' as hide_kept_stamp from public.users where id = '00000000-0000-4000-8000-0000000000a1';

Run it four ways from the repository root. Each run is one transaction that rolls back. now() does not change inside a transaction, so the equality checks are exact.

M=packages/supabase/migrations/20261014120000_presence_stamp_online_only.sql
run() { ( echo 'begin;'; cat "$@"; echo 'rollback;' ) | docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres -q -X -t -A; }
sed "s/if new.status = 'ONLINE' then/if true then/" $M > /tmp/p489-every.sql
sed "s/if new.status = 'ONLINE' then/if new.status = 'ONLINE' and old.status is distinct from new.status then/" $M > /tmp/p489-change.sql
run /tmp/p489-setup.sql $M /tmp/p489-check.sql                  # the fix
run /tmp/p489-setup.sql /tmp/p489-every.sql /tmp/p489-check.sql  # sabotage: stamp every write (#417)
run /tmp/p489-setup.sql /tmp/p489-change.sql /tmp/p489-check.sql # sabotage: stamp on a change only (pre-#417)
run /tmp/p489-setup.sql /tmp/p489-check.sql                      # baseline: no data fix

Expected output, measured locally:

After Step 3, the baseline row reads ONLINE, t, then t and t. The stuck row stays ONLINE, because the baseline runs no data fix. beat_stamps guards the heartbeat and the #417 push suppression.

Scale: the trigger adds one comparison per write. The update locks only the stuck rows, and comment on trigger takes AccessShareLock on public.users. Neither blocks heartbeat writes, and there is no rewrite or new index.

Acceptance criteria

• Step 0 reads body_417 and definer as true, and the stuck ids are recorded.
• 20261014120000_presence_stamp_online_only.sql, 10-1-func-users.sql, the regenerated seed.sql and the [Unreleased] CHANGELOG entry land as one change. apps/webapp/src/types/supabase.ts is not in it.
• Locally, after Step 3, the fix and both sabotage runs give their Verify rows.
• On production, the Step 4 check reads stuck 0, guard_live true and definer true.
• On production, each Step 4 cleared entry reads OFFLINE with null_kept true.
• A fresh pad tab, visible for 3 minutes, reads ONLINE with age under 90 s.

Out of scope

• A backfill of the stuck rows' online_at. Their real last activity is unknown. get_inactive_users has no app caller, and ghost deletion reads auth.users only (adminGhostAccounts.service.ts:75-94). So the NULL rows carry no deletion risk.
• The release Migration table row. The release-product skill adds it at the next release.
• Siblings. The lease, the Status writer, HOT heartbeats and the next db push belong to other issues; see [Tracking] Stuck-Online users and the presence review follow-ups #496.

Generated by Claude Code