store: repointing a placement orphans its message row and strands plaintext in FTS #406

New Issue

2026-08-12T16:03:34Z

KofTwentyTwo commented

2026-08-12 16:03:34 +00:00

(Migrated from github.com)

Found during #394 (PR #405); not reachable today, but it blocks a change we intend to make.

save_email_at's placement upsert ends in:

ON CONFLICT (account_id, folder_id, uidvalidity, uid)
DO UPDATE SET message_key = excluded.message_key, read_flag = excluded.read_flag

If the same (account, folder, uidvalidity, uid) is ever re-saved under a different message_key, the old message silently loses its last placement without passing through delete_placements. It is therefore never reaped, and because messages_fts holds decrypted bodies keyed on message_key and is only cleared by the messages_ad trigger on AFTER DELETE ON messages, that message's plaintext body stays readable in the index indefinitely. This is the one path that bypasses the reap the placement model exists to guarantee.

Unreachable today. Under the current FETCH set no fixed placement key can change identity tier, and RFC 3501 forbids UID reuse within a UIDVALIDITY.

Why it matters anyway: it is exactly what happens the moment we start requesting EMAILID (RFC 8474) / X-GM-MSGID. Doing so lifts stored messages from the surrogate or Message-ID tier up to tier 1, changing the derived key for every already-stored message. Every re-synced placement then takes the DO UPDATE arm, orphaning every old messages row and stranding every decrypted body. So this is a hard prerequisite for the identity upgrade, not a tidy-up.

Fix

Reap inside the same transaction as the repoint: when the upsert changes an existing row's message_key, delete the previous message if it has no placements left, exactly as delete_placements does. Add a test that repointing the last placement of a message reaps both the messages row and its messages_fts entry.

The in-code comment at IMAP's place() already records this dependency and should be updated to point here once fixed.

Found during #394 (PR #405); not reachable today, but it blocks a change we intend to make. `save_email_at`'s placement upsert ends in: ```sql ON CONFLICT (account_id, folder_id, uidvalidity, uid) DO UPDATE SET message_key = excluded.message_key, read_flag = excluded.read_flag ``` If the same `(account, folder, uidvalidity, uid)` is ever re-saved under a **different** `message_key`, the old message silently loses its last placement without passing through `delete_placements`. It is therefore never reaped, and because `messages_fts` holds **decrypted** bodies keyed on `message_key` and is only cleared by the `messages_ad` trigger on `AFTER DELETE ON messages`, that message's plaintext body stays readable in the index indefinitely. This is the one path that bypasses the reap the placement model exists to guarantee. **Unreachable today.** Under the current FETCH set no fixed placement key can change identity tier, and RFC 3501 forbids UID reuse within a UIDVALIDITY. **Why it matters anyway:** it is exactly what happens the moment we start requesting `EMAILID` (RFC 8474) / `X-GM-MSGID`. Doing so lifts stored messages from the surrogate or Message-ID tier up to tier 1, changing the derived key for **every already-stored message**. Every re-synced placement then takes the `DO UPDATE` arm, orphaning every old `messages` row and stranding every decrypted body. So this is a hard prerequisite for the identity upgrade, not a tidy-up. ### Fix Reap inside the same transaction as the repoint: when the upsert changes an existing row's `message_key`, delete the previous message if it has no placements left, exactly as `delete_placements` does. Add a test that repointing the last placement of a message reaps both the `messages` row and its `messages_fts` entry. The in-code comment at IMAP's `place()` already records this dependency and should be updated to point here once fixed.

KofTwentyTwo commented

2026-08-14 22:57:24 +00:00

(Migrated from github.com)

Fixed in #413 (62e1b40), merged to dev. The placement upsert now reads the slot's current occupant key, and when a repoint displaces a different message_key it reaps that message in the same transaction on delete_placements' exact NOT EXISTS terms, letting the messages_ad trigger clear its messages_fts row. Two tests, both mutation-verified: disabling the reap fails only the reap test; dropping the NOT EXISTS fails only the survival test.

Fixed in #413 (`62e1b40`), merged to `dev`. The placement upsert now reads the slot's current occupant key, and when a repoint displaces a different `message_key` it reaps that message in the same transaction on `delete_placements`' exact `NOT EXISTS` terms, letting the `messages_ad` trigger clear its `messages_fts` row. Two tests, both mutation-verified: disabling the reap fails only the reap test; dropping the `NOT EXISTS` fails only the survival test.

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: KofTwentyTwo/nuncio#406