Problem

Pinned chats in the Agents sidebar don't appear until the user scrolls far enough to load the page containing them. The GetChats SQL query orders by (updated_at, id) DESC with no pin_order awareness. A pinned chat with an old updated_at can land on page 2+ and be invisible in the sidebar's "Pinned" section.

Decisions

Implementation flow

Phase 1: Red (prove the bug)

Add new subtests to TestListChats in coderd/exp_chats_test.go.

Test 1: Pinned chat missing from first page. Insert a chat, insert 50+ more chats (filling page 1), pin the first chat, list page 1 with limit=50, assert the pinned chat appears on page 1. This assertion fails on current code because the pinned chat has the oldest updated_at and sorts last.

Test 2: Cursor pagination with pinned chats. Creates a mix of pinned and unpinned chats, paginates with after_id cursors, and verifies all chats appear exactly once with pinned chats before unpinned ones.

Phase 2: Green (fix the SQL)

Edit coderd/database/queries/chats.sql, modifying only the GetChats query.

ORDER BY change: Add pinned-first sort terms before the existing (updated_at, id) DESC. Use the -pin_order negation so all sort columns are DESC.

Cursor WHERE clause change: Prepend the two new computed columns to the existing cursor tuple. The comparison remains a single < on the expanded tuple.

No new SQL parameters are introduced. The pin_order column is already on the chats table.

Phase 3: Refactor

Review the SQL for clarity. Fix the false handler comment claiming PinChatByID bumps updated_at.

Phase 4: Frontend verification

The frontend sidebar already filters pin_order > 0 from loaded chats and sorts by pin_order ascending. No frontend code changes needed.

Output files

Problem context

Pinned chats in the Agents sidebar do not appear until the user scrolls down enough to load the page containing them. This happens because pinned and unpinned chats share a single paginated query ordered by (updated_at, id) DESC. A pinned chat last updated weeks ago may live on page 3+, and the frontend cannot display it in the "Pinned" section until that page loads.

The frontend splits pinned from unpinned chats client-side by filtering pin_order > 0 from chatsQuery.data.pages.flat(). If the chat hasn't been loaded yet, it isn't in the flat list and won't appear.

Key evidence

• SQL ordering: ORDER BY (updated_at, id) DESC with no pin_order awareness.
• Frontend split: Filters pin_order > 0 from already-loaded chats only. No separate pinned-chats query exists.
• Workspace favorites pattern: CASE WHEN favorite ... THEN 0 ELSE 1 END ASC in ORDER BY is the established codebase convention for sort-priority items.
• False comment: Handler comment claims PinChatByID bumps updated_at, but the SQL only sets pin_order. The intended workaround was never implemented.
• Cursor pagination: Uses (updated_at, id) < (SELECT ...) as a 2-tuple with uniform DESC direction. The < operator works because both columns sort DESC.

Cursor pagination with mixed sort directions

The negation trick (-pin_order) keeps all sort columns DESC so a single tuple < comparison works:

ORDER BY
    CASE WHEN pin_order > 0 THEN 1 ELSE 0 END DESC,
    -pin_order DESC,
    updated_at DESC,
    id DESC

Verification with sample data (all DESC, < gives "next page"):

DESC order: (1,-1) > (1,-2) > (0,0,ts4) > (0,0,ts5). Correct.

Decisions

D1: SQL ordering (Approach B)

Three approaches considered: (A) Separate pinned-chats query, (B) SQL ordering change, (C) Handler-level prepend. Chose B for single query/single cache simplicity, following workspace favorites convention.

D2: Negated pin_order for cursor compatibility

No production code uses after_id for chats (frontend uses offset), but the SQL supports it and tests exercise it. The negation approach avoids complex OR/AND decomposition.

D3: Pinned chats participate in normal pagination

If a user pins 60 chats, the first 50 appear on page 1 and the rest on page 2. Human decision: "the pins also falls naturally into pagination."