storage-sqlite: `save_account_projection_state` group prune cascade-deletes unsent message drafts + attachment plaintext under the documented concurrent-writer path

[erskingardner]

Severity: MEDIUM
Component: crates/storage-sqlite (src/account_projection.rs, src/migrations/0026_message_drafts.rs)

Summary

save_account_projection_state snapshot-replaces the group set with DELETE FROM account_groups WHERE group_id_hex NOT IN (...retained...). The message_drafts and message_draft_attachments tables declare FOREIGN KEY (group_id_hex) ... ON DELETE CASCADE (ultimately to account_groups), and PRAGMA foreign_keys = true is set. So pruning a group row silently destroys that group's unsent draft body and attachment plaintext BLOBs — irreplaceable user content — even though the projection-save model was scoped to re-derivable metadata.

Location

• crates/storage-sqlite/src/account_projection.rs:456-473 — the DELETE FROM account_groups WHERE group_id_hex NOT IN (...) prune inside save_account_projection_state.
• crates/storage-sqlite/src/account_projection.rs:363-390 — the method's own doc: "Multiple runtimes may save over the same account database concurrently (for example a main app process and a short-lived notification-wake process)"; "Group and component rows are snapshot-replaced (last writer wins). Full multi-writer reconciliation is an explicit non-goal."
• crates/storage-sqlite/src/migrations/0026_message_drafts.rs:8-37 — message_drafts.group_id_hex ... REFERENCES account_groups(group_id_hex) ON DELETE CASCADE (holds content plaintext + reply_to), and message_draft_attachments.group_id_hex ... REFERENCES message_drafts(group_id_hex) ON DELETE CASCADE (holds plaintext BLOB).
• crates/storage-sqlite/src/connection.rs — PRAGMA foreign_keys = true.

Concrete failure scenario

1. The user types a draft (with an attachment) in group G. save_message_draft persists it into message_drafts / message_draft_attachments, keyed by G's group_id_hex.
2. A short-lived notification-wake process (documented as a concurrent writer) whose StoredAccountState snapshot was built slightly earlier — before G was joined/created, or simply not yet reflecting G — calls save_account_projection_state.
3. state.groups for that stale writer does not include G, so the NOT IN prune deletes G's account_groups row.
4. The FK cascade wipes G's message_drafts row and its message_draft_attachments plaintext BLOBs. The draft and its attachments are gone, with no error surfaced to either process.

The "last writer wins / re-derivable metadata" model (introduced for projection metadata) did not account for drafts and attachment plaintext now hanging off account_groups via cascade. Group metadata is re-derivable from the engine; unsent drafts are not.

Impact

Silent, unrecoverable loss of unsent user content (draft text + attachment plaintext) whenever a concurrent projection save is performed by a writer whose group snapshot is momentarily stale — precisely the multi-writer scenario the method documents as supported.

Suggested fix

Options:

• Exclude groups that still hold drafts from the prune (only delete account_groups rows with no dependent draft/attachment content), or
• Store drafts in a table not transitively cascade-linked to the snapshot-replaced account_groups, or
• Make draft/attachment retention independent of the "last writer wins" projection prune (e.g. soft-reconcile rather than NOT IN delete when dependent user content exists).

Dedup

Distinct from #846 (the unchunked NOT IN bind-limit at this same statement) and #756 (insecure/recoverable delete in delete_local_group_data). This is a third, separate defect at this site: unintended destruction of user drafts via FK cascade under the documented concurrent-writer model.

---

Filed from a meticulous automated code-review pass; verified against source (cascade chain + PRAGMA + documented concurrency model) and deduplicated against the existing open/closed issue set.