Chat schema with one side deletion

I’m struggling with a chat schema that permits deletion from one side of the conversation in a relational database (PostgreSQL).

Here is my best approach: my schema

All schemas I found were about deletion for both sides, and this is not what I need.
I want to be able to delete from one side of the conversation, and if the conversation restarts, user 1 still has all messages and user 2 only those which was sent after the deletion.
So my idea is to have many to many relational table(deleted_messages) that relates the messages with users, and full fill that table when a user deletes a conversation with those messages that were in that conversation.

However, I’m not sure if this approach is the best one because my database will need to see if the message wasn’t deleted for every message that is retrieved. This will be messier and messier as the deleted_messages table grows and I worried about the performance.

There is any better solution for my problem, or this approach is good?
Thank you for your attention.

Answer

As you mentioned “if the conversation restarts, user 1 still has all messages and user 2 only those what was sent after the deletion”. This come up to 2 scenarios:

1. If user deletes specific message, then you should go with the schema of what you already have.

2. If user deletes chat history which is point of time reference, then you should create separate table as written below. Having one record for each message in deleted message for single user will cause serious performance issues as the data grows.
   Create below table and filter the data based on timestamp while you fetch the data.

   create table deleted_chat_history
   (
   userId int,
   chatRoomId int,
   [timestamp] timestamp,
   foreign key (userId) references user,
   foreign key (chatRoomId) references chat_room
   )