What type and logic to choose for the messenger database?

Question

SQL

So, the idea of ​​this messenger is that dialogs are stored only locally on users' devices, nothing goes to the server. The server will store only the necessary information, without which users will not be able to find each other. The question is, what type and logic is better to choose for a database with such a task, I am not strong in database planning, I would like to hear sound criticism and, of course, the answer to the question

Database logic diagram

Answer 1

Nafig storage of ip of the user.
Do it right away on the basis of WebRTC and design the system based on its capabilities. Then you can easily make web clients in the future that can also use p2p.
The server will be used as a proxy when both (or if group correspondence - all from the group) users are behind an impenetrable NAT.
Naturally, all transmitted data is asymmetrically encrypted in any case.

Answer 2

what you drew is not really a data schema. Yes, and they don’t draw it in fragments ... well, I haven’t seen this. This is your mixture of the system state diagram and database entities ...

Answer 3

You will still sooner or later run into the need to store history on the server. For synchronization, for multilogin on different devices. Users will force, tested in their own skin :)
For a local database, SQLite is ideal, because it already exists natively and works well, and you don’t need the power of adult DBMSs here. The structure is not fundamental, it depends on your data.
For the server base, you need to choose, again, a base that does what you need well. nosql databases are good for storing and retrieving data, but terrible for changing. For logs and history - ok, to work with user data - you need normal sql. Base type - depends on the loads.
And cache everything in memory as much as possible. Working directly with the database on the server is a performance loss. Again, I do not know how much you will have online, it is difficult to say something definite in this situation.