Lucas RayanandClaude Sonnet 5.5
67d0c8aa5a
A reply now joins the conversation of the message it answers (Message-ID,
In-Reply-To, References), falling back to the subject for replies with no
headers. /mail/threads lists a folder as conversations and /mail/thread/{id}
returns one. Replying from the webmail flags the answered message.
Co-Authored-By: Claude Sonnet 5.5 <noreply@anthropic.com>
42 lines
1.9 KiB
PL/PgSQL
42 lines
1.9 KiB
PL/PgSQL
-- Conversations.
|
|
--
|
|
-- email_messages.thread_id has existed since the first migration and nothing
|
|
-- filled it, and in_reply_to was only ever copied, never written. A reply and
|
|
-- the message it answered were therefore unrelated rows, which is why the
|
|
-- webmail could neither show a conversation nor say whether it had been
|
|
-- answered.
|
|
--
|
|
-- The subject key lives in the database so delivery, the backfill below and
|
|
-- anything later share one definition of "the same subject": the prefixes mail
|
|
-- clients add in several languages are not part of it.
|
|
CREATE FUNCTION lm_subject_key(subject TEXT) RETURNS TEXT AS $$
|
|
SELECT btrim(regexp_replace(
|
|
lower(coalesce(subject, '')),
|
|
'^(\s*(re|fw|fwd|enc|res|rv|aw|wg|tr|antw)(\[\d+\])?\s*:)+', ''))
|
|
$$ LANGUAGE SQL IMMUTABLE;
|
|
|
|
CREATE FUNCTION lm_has_reply_prefix(subject TEXT) RETURNS BOOLEAN AS $$
|
|
SELECT lower(coalesce(subject, '')) ~ '^\s*(re|fw|fwd|enc|res|rv|aw|wg|tr|antw)(\[\d+\])?\s*:'
|
|
$$ LANGUAGE SQL IMMUTABLE;
|
|
|
|
-- Mail that arrived before replies carried their headers cannot be linked by
|
|
-- Message-ID, so it is grouped by subject, and only where a reply is actually
|
|
-- in the mailbox: two unrelated messages that merely share a subject stay apart.
|
|
-- Everything else keeps thread_id NULL, which the webmail reads as "a
|
|
-- conversation of its own" (COALESCE(thread_id, id)).
|
|
WITH groups AS (
|
|
SELECT DISTINCT mailbox_id, lm_subject_key(subject) AS k
|
|
FROM email_messages
|
|
WHERE lm_has_reply_prefix(subject) AND lm_subject_key(subject) <> ''
|
|
)
|
|
UPDATE email_messages m
|
|
SET thread_id = md5(m.mailbox_id::text || '|' || g.k)::uuid
|
|
FROM groups g
|
|
WHERE m.mailbox_id = g.mailbox_id
|
|
AND lm_subject_key(m.subject) = g.k
|
|
AND m.thread_id IS NULL;
|
|
|
|
-- The listing groups by this and filters by mailbox and folder.
|
|
CREATE INDEX idx_msg_mailbox_thread ON email_messages (mailbox_id, (COALESCE(thread_id, id)))
|
|
WHERE expunged_at IS NULL;
|