title: How to order room entity by last reply time in a chat application?
body: <p>I am designing a chat application's database schema in Apache Cassandra and I couldn't wrap my head around this.</p>
  
  <p>Here's my schema so far:</p>
  
  <pre><code>CREATE TABLE users(
    user_id bigint,
    nickname text,
    email text,
    chat_rooms set&lt;timeuuid&gt;,
    PRIMARY KEY(user_id)
  );
  
  CREATE TABLE rooms(
    room_id timeuuid,
    creation_date timestamp,
    creator_id bigint,
    participants set&lt;bigint&gt;,
    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);
  </code></pre>
  
  <p>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.</p>
  
  <p>I was thinking adding a new column <code>last_reply_time</code> to <code>rooms</code>, 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?</p>
  
  <p>I have looked at the <a href="https://academy.datastax.com/resources/getting-started-killrchat-example-data-model-messaging" rel="nofollow noreferrer">KillrChat example</a> and <a href="https://blog.discordapp.com/how-discord-stores-billions-of-messages-7fa6ec7ee4c7" rel="nofollow noreferrer">Discord's wonderful piece</a> on their Cassandra implementation but they did not mention anything related to my issue.</p>
  
  <p>Thanks in advance!</p>