2

I am currently learning Cassandra, and I am having difficulty coming up with a good database design that allows for private and group messaging and a message inbox.

In terms of domain, I suppose it should work closely with how chat applications do; an inbox shows a list of available chats where a user has exchanged messages (either with a different user or a group of users). To keep things simple, when a user joins a group, they should be able to view the whole chat history.

For these use cases, I have the following access patterns:

  • Find all inboxes by user_id ORDERED by TIMEUUID
  • Find all messages between two users; or a user and a group ORDERED by TIMEUUID
  • Find all groups by user_id

I have designed the following tables to support these:

-- Query find messages between two users or between a user and a group
-- SELECT * FROM chat.messages WHERE from = "1234" and to = "4321";
CREATE TABLE IF NOT EXISTS chat.messages (
  message_id TIMEUUID,
  from UUID, // user_id
  to UUID, // user_id or group_id
  message TEXT,
  created_at TIMESTAMP,
  PRIMARY KEY ((from, to), message_id)
) WITH CLUSTERING ORDER BY (message_id DESC);

-- Query to find all groups by user_id
-- SELECT * FROM chat.groups_by_user WHERE user_id = "1234";
CREATE TABLE IF NOT EXISTS chat.groups_by_user_id (
  user_id UUID,
  group_id UUID,
  group_name TEXT,
  created_at TIMESTAMP,
  PRIMARY KEY ((user_id, group_id))
);

// Query to find all inboxes by user_id
CREATE TABLE IF NOT EXISTS chat.inbox_by_user_id (
    user_id UUID,
    inbox_id UUID, // Can be user_id or group_id
    name TEXT, // Can be concatenation of user first_name and last_name; or group_name
    last_message TEXT, // To show the last message thumbnail
    last_message_created_at TIMEUUID, // To sort the inbox
    PRIMARY KEY ((user_id, inbox_id), last_message_created_at)
) WITH CLUSTERING ORDER BY (last_message_created_at DESC);

I have thought about creating a group_messages table that could be queried by group_id, but I was wondering if I could get away from it with the design above.

I have looked up some possible solutions in StackOverflow. Still, most use cases only support private and not group messaging or are relational.

I appreciate any help you can provide.

Filipe Costa
  • 51
  • 1
  • 9
  • It would be helpful to describe the domain, rather than jump straight to code. What does the concept "group" represent - what happens when someone joins a group (can they see all historic messages?), leaves a group (do their messages stay visible to the group?), what is an "inbox" (is it more than the total messages for that user?). – Neville Kuyt Jan 03 '23 at 17:45
  • @NevilleKuyt, I have included more information in my original post; it is as follows: In terms of domain, I suppose it should work closely with how chat applications do; an inbox shows a list of available chats where a user has exchanged messages (either with a different user or a group of users). To keep things simple, when a user joins a group, they should be able to view the whole chat history. – Filipe Costa Jan 03 '23 at 18:14

0 Answers0