The timer scheduler polls once a second and cancels on every message send and chat open, the sidebar lists chats ordered by `updated_at`, and the folder badges count unread chats per folder. None of those could be served by an index, so each call read most of the `chat` table, and because `meta` sits after the chat payload column SQLite had to walk every row's overflow pages to get there. On a large history that stalls the sidebar, every chat switch and every send, and the idle poll alone burns about a quarter of a CPU core.
Timers now keep their due time in a dedicated `chat.timer_at` column behind a partial index, and the chat list, unread and unfinished-reply queries each get an index matching their filter and ordering. Existing pending timers are backfilled from their meta by the migration. Dropping the `internal` and `type` checks also makes a forked timer chat inert, where a fork used to copy `meta` verbatim and become a second claim target that could fire a duplicate timer.
Measured on SQLite, same rows returned:
| query | before | after |
|---|---|---|
| idle timer poll (2000 chats, 0.43 GB) | 170 ms | 0.04 ms |
| cancel on send and chat open (4000 chats, 377 MB) | 200 ms | 0.04 ms |
| sidebar chat list (15000 chats, 1.26 GB) | 157 ms | 1.8 ms |
| folder unread badges (15000 chats, 1.4 GB) | 54 ms | 0.2 ms |
PostgreSQL 17 serves all of them as index-only scans with no sort node. Exercised through fresh install, upgrade with seeded data, downgrade and re-upgrade on SQLite and PostgreSQL 17.
Fixes#27622
The events:chat socket handler called the ownership-checked update for last_read_at, discarded the boolean it returns, and then cancelled the chat's pending timers regardless of the answer. cancel_timers_for_chat selected on the internal marker, the type, the parent chat id and the status, and never on the owner, so it matched rows belonging to any user. An authenticated user who knew another user's chat id could mark that chat read over their own socket session and silently cancel the owner's pending timers, and the owner got no notification: the scheduled action simply never fired.
The missing owner predicate also cut the other way in ordinary use. Because the query matched every timer sharing a parent chat id, one user reading a chat cancelled the timers of anyone else holding one on the same chat, so this was collateral damage as much as an attack.
cancel_timers_for_chat now requires a user_id and filters on it, which is the durable fix, and the socket handler returns early unless the ownership-checked update reports that the caller owns the chat. The parameter is required rather than defaulted so a later caller cannot reintroduce the unscoped query by omission. Both existing call sites already know the acting user. Timer rows are created with the same owner as the parent chat and the execution path already refuses to run one whose owner does not match, so scoping the cancellation the same way cannot strand a timer that would otherwise have fired.
One behaviour change worth noting: an administrator posting into another user's chat no longer cancels that user's chat.user_message timers, because the acting user is the administrator. The timer fires instead of being cancelled, which is the safe direction.
Classic298andGitHub
G30andGitHub
Timothy Jaeryang Baek