[Tracking] Stuck-Online users and the presence review follow-ups #496

Summary

Two production users show ONLINE for good in the admin dashboard, though they left months ago. Their users.online_at is NULL. The cron job update-user-status (16-cron-jobs.sql:12-14) filters on online_at < now() - interval '2 minutes'. NULL < x is never true, so the job never sets them OFFLINE.

How the 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 old trigger stamped online_at only when status changed, so these rows were never stamped.

The harm: the chat bell skips a member whose raw flag reads ONLINE (10-func-notifications.sql:383). Push and email fire only from a bell row. So the stuck users get no bell, push or email for plain chat messages. Admin "Online Now" also counts them.

Root cause

Postgres answers "is this user online" with two rules. The bell creator and admin read the raw flag users.status. Push reads the lease internal.is_user_online: status = 'ONLINE' and online_at within 2 minutes. #489 clears the stuck rows. #490 makes the lease the one rule for the bell. The other children are smaller follow-ups.

Decisions

The recommended answer comes first. Owner: the maintainer. The ruling is posted as a comment here.

1. Leave apps/webapp/src/types/supabase.ts out of Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489, 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? Ruled 2026-10-15: yes. This is an exception to packages/supabase/CLAUDE.md:15, so it needs this ruling.
   • None of the three adds a table, a column, an enum value or a function signature. Function bodies and indexes do not reach the generated types.
   • The pinned CLI output already differs from the committed file on an unchanged local DB: 2,809 diff lines raw, 1,120 after Prettier (measured locally). A commit would put that drift into a bug fix.
   • So these children do not run types. A child that changes a type stops and asks.
   • Alternative: run types in each child and commit the drift with it.
   • The drift fix is Make the generated Supabase types reproducible again #498.

Child issues, in order

• Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489 — Fixes the bug. The trigger stamps online_at only on an ONLINE write. A one-time data fix sets the stuck rows OFFLINE. The sweep does not change. One migration and one production paste.
• Make the chat bell use the online lease instead of the raw status flag #490 — Fixes the root cause. The bell reads the lease, so the lease is the one Postgres online rule. The 07-4 copy of internal.is_user_online stays, with pointer comments.
• Make presence heartbeats HOT updates and drop the duplicate username index #491 — Makes each heartbeat a HOT update and drops a duplicate index. Not urgent; no behaviour change.
• Fix the Status writer's tab race, reload gap and silent errors #492 — Fixes four Status writer faults: reload gap, dedupe guard, dead listener, silent errors. It also fixes the tab race within one window. Webapp only; needs no ruling.
• Use one client presence rule so the @ picker agrees with the avatars #493 — One client rule, isFacePresent, so the @ picker agrees with avatars, the TOC and participants.
• Measure workspace presence join fan-out before changing it #495 — Reads two production dashboards, then closes or ships one small cleanup (Step A).

#494 (always-create bell rows) was closed as not planned. See Rejected by the review.

Shared rules

Children point here instead of repeating these.

• Types: Decision 1 above.
• Scale base: about 181 users, the production public.users row count from the 2026-09-28 Supabase review. Visible tabs were never measured; assume about 5 %.
• Migration timestamps: Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489 keeps 20261014120000. Every other migration takes the next free timestamp after the newest file on main when it lands.
• Two stale doc lines. Owner: the maintainer, as one doc-only commit. Do it before Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489 starts, because every child reads packages/supabase/CLAUDE.md.
  • packages/supabase/CLAUDE.md:53 says to push 20261009120500 later, with 20261009130100, and that production still grants both columns. Both migrations are applied in production: see the Stop exposing every user's online status and last-active time #434 and Scope heading chat channels to their own document #402 closing comments. Replace the "Push it at least 24 hours…" and "Until then…" sentences with "Applied in production (Stop exposing every user's online status and last-active time #434)."
  • CONTEXT.md:98 says 20261009120500 removes the grant "once it is applied". Change it to "removed the client SELECT grant (Stop exposing every user's online status and last-active time #434)".

The next db push

#489, #490 and #491 reach production by hand paste, so production has their objects but no history row. No child runs db push or migration repair for itself. The maintainer's next planned push runs the three files again. Each re-run is harmless: create or replace, an update that matches 0 rows, and if not exists guards. None of them calls cron.schedule.

At that push, the maintainer runs every bunx supabase command from packages/supabase:

1. Check that packages/supabase/.temp/project-ref holds the production ref. If not, run bunx supabase link --project-ref <production ref>. Never paste the ref into an issue.
2. Set [db.migrations] enabled = true in packages/supabase/config.toml.
3. Run bunx supabase migration list. Write down every version with an empty Remote column.
4. Run bunx supabase db push --dry-run. If it lists a version other than the Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489, 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 migrations, check it against packages/supabase/CLAUDE.md:23. If you are not sure, stop.
5. Run bunx supabase db push. If it refuses because a local version sorts before the newest remote row, rerun it with --include-all. Do this only after step 4 passes, because it pushes every listed version.
6. Set [db.migrations] enabled = false again. Never commit it as true.

What only the maintainer can do

• Production reads (read-only): Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489 Step 0, Make the chat bell use the online lease instead of the raw status flag #490 Step 0, Make presence heartbeats HOT updates and drop the duplicate username index #491 Steps 0 and 6, and Measure workspace presence join fan-out before changing it #495 Steps 0 to 2.
• Production pastes: Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489 Step 4, Make the chat bell use the online lease instead of the raw status flag #490 Step 4 and Make presence heartbeats HOT updates and drop the duplicate username index #491 Step 4.
• The next db push: see Shared rules.
• Rulings: Decision 1 above, and each child's Decisions section:
  • Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489: the stamp guard, and a data fix instead of a NULL-safe sweep.
  • Make the chat bell use the online lease instead of the raw status flag #490: admin Online Now.
  • Make presence heartbeats HOT updates and drop the duplicate username index #491: the partial index shape.
  • Use one client presence rule so the @ picker agrees with the avatars #493: which rule is the one rule.
  • Measure workspace presence join fan-out before changing it #495: measure first, then close or ship Step A.
• Follow-up issues (filed 2026-10-15):
  • Skip a chat notification email when the member already read the message in the channel #497: a channel read does not stop the email. Live today; independent of this set.
  • Make the generated Supabase types reproducible again #498: make the generated Supabase types reproducible. It removes the Decision 1 exception.

Acceptance criteria

• Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489 and Make the chat bell use the online lease instead of the raw status flag #490 are live in production.
• Every other child is closed.
• Decision 1 is ruled, and Skip a chat notification email when the member already read the message in the channel #497 and Make the generated Supabase types reproducible again #498 are filed.
• The two stale doc lines are fixed.

Close this tracker when all three hold.

Already tracked

These issues overlap the set. Each one stays as it is.

• Bring back push on Chrome and Android devices the server switched off #417 (closed) made the trigger stamp online_at on every status write. Stamp online_at only on ONLINE writes and clear users stuck ONLINE #489 narrows that to ONLINE writes.
• Stop exposing every user's online status and last-active time #434 (closed) hid users.status and users.online_at from client SELECT. It also took public.users out of supabase_realtime. Migration 20261009120500 is applied in production. Every child works with it.
• Expire a peer's typing state when its stop signal never arrives #342 expires a peer's typing state. Use one client presence rule so the @ picker agrees with the avatars #493 relies on the same contract: TYPING counts as present. Both edit apps/webapp/src/components/chatroom/CLAUDE.md, in different bullets.
• [Tracking] Push notification follow-ups after #417 #447 tracks push follow-ups after Bring back push on Chrome and Android devices the server switched off #417. Push keeps its lease gate, so no child changes push.

Rejected by the review

• Delete the sweeper cron and drop idx_users_online_status. It saves at most 1.25 s of sweep time a day. The cron keeps the raw flag correct at no extra cost.
• An internal.live_users view plus admin_online_count(). Its gain appears only at 1,000-member channels among 100k synthetic users. It also adds a second name for the rule.
• A localStorage cross-tab heartbeat. It saves about a third of a write rate below 1 per second at 1x. It also adds an untested cross-tab protocol.
• A heartbeat-only Status writer (no tab writes OFFLINE). It ends the tab race, but each leave then takes 60 to 120 s. In that window a plain message makes no bell, push or email. Fix the Status writer's tab race, reload gap and silent errors #492 keeps the hide write.
• Push reads Redis occupancy in the worker. It adds a Redis call per push to save about 2 µs of SQL. Also, a mention in pad B would push while the user reads pad A.
• The server writes Status from pad-socket edges. It overturns "Presence writes stay on Supabase" (apps/hocuspocus.server/CLAUDE.md:88). It also needs an atomic per-user count across replicas.
• A presence-source interface or a per-user Redis key. Only one server-readable source exists, so the seam is hypothetical, and Postgres triggers cannot read Redis.
• Always create a plain-message bell row (always-create). Make the chat bell use the online lease instead of the raw status flag #490 fixes the stuck members. Always-create alone emails content the member already read. Decide whether plain chat messages always create a bell row #494 (closed) holds the details.
• Applying a presenceSync as one store write. At 50 faces it costs microseconds.
• Industry-scale presence (a presence process, CRDT replication, Teams-style state precedence). Those designs serve millions of sessions; docs.plus has hundreds of users.

Generated by Claude Code