Sort archived conversations by latest message timestamp

[exedev-shelley]

Author: realami
Channel: #feedback
Link: https://discord.com/channels/1405685085923049482/1405686161791516873/1470862481369862165

---

Description

Archived conversations currently appear in reverse-chronological order of when they were archived. This is not useful for finding recently active conversations.

Requested behavior: Sort archived conversations by the latest timestamp of any message within the conversation, rather than by the archive timestamp.

Implementation Details

The issue is in db/query/conversations.sql at line 75, where updated_at = CURRENT_TIMESTAMP is set during archival. This should be removed so that updated_at reflects the actual latest message timestamp instead.

Workaround

Users can manually update the database with:

UPDATE conversations
SET updated_at = (
    SELECT MAX(m.created_at)
    FROM messages m
    WHERE m.conversation_id = conversations.conversation_id
)
WHERE archived = TRUE

Related Context

There may be related UI issues with archived conversations jumping around in the conversation list during archival operations (see #tonezime's report).