Problem

The hash_ratchet_encrypted_messages table is utilized for storing messages that are unable to be decrypted. Currently, there is no mechanism in place for cleaning up this storage, particularly when the necessary encryption keys are not obtained. This lack of cleanup becomes problematic for clients who participate in communities but lack access to certain private channels, leading to uncontrolled and potentially infinite growth of the table. This growth adversely impacts performance, as the table is queried each time a hash ratchet is received, whether from store nodes or relays.

SELECT count(*) FROM hash_ratchet_encrypted_messages -> 653
SELECT timestamp FROM hash_ratchet_encrypted_messages ORDER BY timestamp ASC LIMIT 1 -> 1709136641 (Feb 28 2024)

Implementation

Clean up the hash_ratchet_encrypted_messages periodically. The condition would be: If a message remains unencrypted for 14 days, remove it.

Acceptance Criteria

• remove unencrypted messages received more than 14 days ago from hash_ratchet_encrypted_messages

Notes

• add index to hash_ratchet_encrypted_messages.key_id for immediate improvement

Future Steps

As a future improvement, we should not subscribe to chats (channels) we do not have permission for. This way, we would not be saving messages from private channels at all.