[In-app chat] Database design

[Pjaijai]

Design a database schemas for in app chat

Assumptions:
It is 1 on 1 chat.
Allow text only, 1000 words per message
Will allow user to send images and pdf in the future

Current design :
https://dbdiagram.io/d/Referalah-651b7b71ffbf5169f0e71a7a

[Pjaijai]

Hi all, for full schema please check https://dbdiagram.io/d/Referalah-651b7b71ffbf5169f0e71a7a

The assumption for the in-app chat feature:

1. 1 on 1
2. Text only
3. media (image, pdf )will be supported in the feature

Two tables will be created. The relationship of table conversation and message is one-to-many.

Table message {
  id int pk
  uuid uuid   
  conversation_uuid uuid  [ref: > conversation.uuid]
  from_uuid uuid [ref: > user.uuid]
  to_uuid uuid [ref: > user.uuid]
  created_at datetime
  message vachar
}

Table conversation {
  id int pk
  uuid uuid
  user_one_uuid uuid [ref: > user.uuid]
  user_two_uuid uuid [ref: > user.uuid]
  created_at datetime
  last_message uuid  [ref: > message.uuid]
  last_sent_user_uuid uuid  [ref: > user.uuid]
}

last_message uuid and last_sent_user_uuid is to display latest message so we can avoid query the all message to get latest message.

This design does not support group chat. However, I am not considering this for now or in the near future. If we find the need for group chat, we can create new tables or consider using NoSQL.
I consider leaving some flexibility, but it may complicate things.

[ken31ee]

Will we consider doing encryption on the message? Users might send sensitive info in the message.

[14104647ls3]

Suggestion:

to_uuid in public.message shall be removed since we assume 1-to-1 conversation, so no need to use extra storage.

last_message and last_sent_user_uuid can replaced with message_uuid

[Pjaijai]

@ken31ee Thank you for reminding me. Will do some research on it

[Pjaijai]

Updated, please check

[Pjaijai]

added two seen column to chat room table

[Pjaijai]

@ken31ee how can store the private key in client ?

[14104647ls3]

is this what you want?
https://developer.mozilla.org/en-US/docs/Learn/JavaScript/Client-side_web_APIs/Client-side_storage

[Pjaijai]

is this what you want?

https://developer.mozilla.org/en-US/docs/Learn/JavaScript/Client-side_web_APIs/Client-side_storage

Thank you but this cannot solve my concern.

The problem is that every user have two thing to do end to end encryption- public key and private key.

For public key is fine, I can store it in db and share to every one.

For the private key, we need to store on client (web). I did some research, storing on web using cookies or local storage is not a good way. Let say if the user remove it or using other browsers, they cannot decode previous message.

Plz check this article

https://cronokirby.com/posts/2021/06/e2e_in_the_browser/

[Pjaijai]

I an thinking using supabase vault to do e2ee
https://supabase.com/blog/supabase-vault

[Pjaijai]

done