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
Permission checks are the most repeated database work in a request, and every one of them asks the same question: which groups is this user in. Today that question cannot use an index.
`group_member` has only its primary key and a `(group_id, user_id)` unique constraint. That constraint leads on `group_id`, so a lookup by `user_id` has to walk the entire membership table, every time. `Groups.get_groups_by_member_id` sits under `has_permission`, `has_access`, `check_model_access` and the `AccessGrants` fallbacks, so an ordinary chat completion pays that walk several times before the model is even called, and the admin user list pays it once per row.
The cost scales with total memberships across all users rather than with the size of any one user's, so it stays invisible on a small instance and then arrives all at once on a large one.
Measured on SQLite, timing the real join from `get_groups_by_member_id`:
| memberships | before | after |
|---|---|---|
| 5,000 | 0.04 ms | 0.03 ms |
| 50,000 | 0.10 ms | 0.04 ms |
| 200,000 | 1.33 ms | 0.04 ms |
| 500,000 | 2.94 ms | 0.04 ms |
The after column is flat because the lookup becomes a seek instead of a scan. Concretely: on a deployment with 500k memberships, say 10,000 users in 50 groups each, one chat completion currently spends roughly 15 ms of database time answering the same question over and over. Afterwards it is under 0.2 ms. On a small install you will not be able to measure the difference, and that is fine, the point is that the curve stops bending.
The index is `(user_id, group_id)`. The trailing column makes those lookups index-only, since `group_id` is the column they select. Queries that lead on `group_id`, such as `get_group_user_ids_by_id` and the `chat_messages` subqueries, are already served by the existing unique constraint and are unaffected.
What to expect when the migration runs: on PostgreSQL this is a plain `CREATE INDEX`, which takes a SHARE lock, so reads continue while writes to `group_member` block until it completes. The table holds one row per membership, so expect sub-second even on the numbers above. `CONCURRENTLY` cannot be used here because the migration runner wraps the upgrade in a transaction, and it is not warranted at this table size.
