How to order room entity by last reply time in a chat application?

I am designing a chat application's database schema in Apache Cassandra and I couldn't wrap my head around this.

Here's my schema so far:

CREATE TABLE users(
  user_id bigint,
  nickname text,
  email text,
  chat_rooms set<timeuuid>,
  PRIMARY KEY(user_id)
);

CREATE TABLE rooms(
  room_id timeuuid,
  creation_date timestamp,
  creator_id bigint,
  participants set<bigint>,
  PRIMARY KEY(room_id)
);

CREATE TABLE messages(
  message_id timeuuid,
  room_id timeuuid,
  author_id bigint,
  time_bucket int,
  content text,
  PRIMARY KEY((room_id, time_bucket), message_id)
) WITH CLUSTERING ORDER BY (message_id DESC);

I would like to get a list of room by a user's id ordered by last reply time, similar to Facebook Messenger and Telegram.

I was thinking adding a new column last_reply_time to rooms, use it as clustering key and update that when there is new message in the room. However it is impossible to update clustering key value in Cassandra. How should I go about modelling this?

I have looked at the KillrChat example and Discord's wonderful piece on their Cassandra implementation but they did not mention anything related to my issue.

Thanks in advance!

Solution

You could have a table like this

create table test (
   creator_id bigint ,
   room_id timeuuid ,
   last_reply_time timestamp,
   primary KEY ((creator_id), room_id))
with CLUSTERING ORDER BY
   (room_id ASC );

and you could insert into it all last_reply_time and select your data with

select * from test where creator_id = ? and room_id = ?

You will do only inserts that will update the same entry in the database, since you will have the same creator_id and room_id.