Cassandra modelling on conversation messaging

Question

I am still learning to understand Cassandra. I have read similar questions and answers on conversation messaging yet am not satisfied because it doesn't meet my needs. These are the problems i want to solve

• A user can send messages to one or many users
• A conversation tables - saves messages between user-user
• A conversation tables - Show recent conversation message for all users you have established conversation with, marked read or unread with a date.
• A conversation message tables - User A can load all conversations with user B or user C
• A conversation message tables - message sent will be marked read or unread
• A conversation message tables - User A can delete a message but user B message won't be deleted. very Important

I have the following tables

CREATE TABLE user (
   username text PRIMARY KEY,
   password text
);
CREATE TABLE friends (
   username text,
   friend text,
   since timestamp,
   PRIMARY KEY (username, friend)
);
CREATE TABLE followers (
   username text,
   follower text,
   since timestamp,
   PRIMARY KEY (username, follower)
);
CREATE TABLE conversation_A (
   participantA text,
   participantB text,
   conversationid text,
   message text,
   read boolean,
   date timestamp,
   PRIMARY KEY(participantA, date)
);
CREATE TABLE conversation_B (
   participantA text,
   participantB text,
   conversationid text,
   message text,
   read boolean,
   date timestamp,
   PRIMARY KEY(participantA, date)
);
CREATE TABLE conversation_message_sent (
   conversationid text,
   messageid bigint,
   sender text,
   recipient text,
   message text,
   read boolean,
   date timestamp,
   PRIMARY KEY(conversationid, date)
};
CREATE TABLE conversation_message_receive (
   conversationid text,
   messageid bigint,
   sender text,
   recipient text,
   message text,
   read boolean,
   date timestamp,
   PRIMARY KEY(conversationid, date)
};
CREATE TABLE messages_sent (
   messageid bigint,
   message text,
   date timestamp,
   PRIMARY KEY(messageid, date)
);
CREATE TABLE messages_receive (
   messageid bigint,
   message text,
   date timestamp,
   PRIMARY KEY(messageid, date)
);

If user A id is 100, establishes conversation with user B and User B id is 101 then conversationid will be 100-101.
Please am new to Cassandra i want to know if my modelling is right.
If User A send a message to User B which conversation tables belongs to either User A or UserB

If user A established conversation with User B and User C establishes conversation with user A and user A wants to load all conversations with User C which conversation message table will the messages fetched from?

And how will i query conversation table to list all users, User A have established conversation with and all users who established conversation with user A containing the last message sent or receive.

Answer 1

create table users(
    id uuid,
    username text,
    name text,
    pass text,
    roles text,
    thread_ids set<uuid>,
    PRIMARY KEY (username),
);

you can add avatar url and other stuff too

create table thread (
    id uuid,
    participants set<uuid>,
    created_at timestamp,
    PRIMARY KEY (id),
);

participants is useless now for me but it doesnt bother to have more information,

create table thread_users (
    thread_id uuid,
    from uuid,
    to uuid,
    PRIMARY KEY (thread_id),
);



create table thread_messages (
    id timeuuid,
    from uuid,
    to uuid,
    edited boolean,
    deleted boolean,
    seen boolean,
    body text,
    thread_id uuid,
    year int,
    month int,
    day int,
    hour int,
    minute int,
    PRIMARY KEY ((thread_id, year), id)
) WITH CLUSTERING ORDER BY (id DESC);

this should be written twice (swap from and to) for easier read query, you need to have 2 kinds of deleted (deleted_from and deleted_to) which covers your last question point

create table thread_last_message_by_user (
    message_id uuid,
    user_id uuid,
    thread_id uuid,
    owner_id uuid,
    reply_of uuid,
    edited boolean,
    body text,
    year int,
    month int,
    day int,
    hour int,
    minute int,
    updated_at timestamp,
    PRIMARY KEY (user_id, thread_id, updated_at, message_id)
) WITH CLUSTERING ORDER BY (thread_id DESC, updated_at DESC);

I use it for first page of messager which it just shows last message

this is what i got for messager app im working on, there are some diffrence btw mine and your cause u dont have such thread thing but you can implement what i've done to what suits you