Cassendra schema design suggestion for chat archive
I created a chat program a while back which is available on github

https://github.com/akc42/MBChat

which has some of the characteristics you are talking about.  In particular it provides both rooms which people can enter and discuss on an open way, and whisper boxes where a few people can get together and discuss things.

When people enter a room, or join a whisper, they can see a little bit of the past conversation.

Before explaining the schema a bit more, it is worth just pointing out that I have two methods of validating users.  This is because the normal use is a chat extension to an smf forum - so users are already logged in to the forum, and they carry their same identity over to chat (and have some characteristics that are derived from membership groups in the forum).  However so chat can be used stand alone without a forum, there is a separate user database which can be queried during the logon phase which identifies the users and their capabilities.

So first that database is at inc/user.sql in the repository.

BEGIN;

CREATE TABLE users (
  uid integer primary key autoincrement NOT NULL,
  time bigint DEFAULT (strftime('%s','now')) NOT NULL,
  name character varying NOT NULL,
  role text NOT NULL DEFAULT 'R', -- A (CEO), L (DIRECTOR), G (DEPT HEAD), H (SPONSOR) R(REGULAR)
  cap integer DEFAULT 0 NOT NULL, -- 1 = blind, 2 = committee secretary, 4 = admin, 8 = mod, 16 = speaker 32 = can't whisper( OR of capabilities).
  password character varying NOT NULL, --raw password
  rooms character varying, -- a ":" separated list of rooms nos which define which rooms the user can go in
  isguest boolean DEFAULT 0 NOT NULL
);
CREATE INDEX userindex ON users(name);
-- Below here you can add the specific users for your set up in the form of INSERT Statements


-- This list is test users to cover the complete range of functions. Note names are converted to lowercase, so only put lowercase names in here
INSERT INTO users(uid,name,role,cap,password,rooms,isguest) VALUES
(1,'alice','A',4,'password','7',0),
(2,'bob','L',3,'password','8',0),
(3,'carol','G',2,'password','7:8:9',0),
(4,'dave','H',0,'password','10',0),
(5,'eileen','R',8,'password','',0),
(6,'fred','R',16,'password','',0),
(7,'gail','R',0,'password','',0),
(8,'harry','R',0,'password','',1),
(9,'irene','R',32,'password','',0);




COMMIT;
VACUUM;
-- set it all up as Write Ahead Log for max performance and minimum contention with other users.
PRAGMA journal_mode=WAL;


However once they have been logged on, these credentials are copied over into the actual chat database that is being used

The following are elements of the schema (taken from data/chat.sql file from that repository) that might help you.

Firstly the the open rooms (actually not all rooms are equal, there is some limitations - see comments)

CREATE TABLE rooms (
  rid integer primary key NOT NULL,
  name varchar(30) NOT NULL,
  type integer NOT NULL -- 0 = Open, 1 = meeting, 2 = guests can't speak, 3 moderated, 4 members(adult) only, 5 guests(child) only, 6 creaky door
) ;

INSERT INTO rooms (rid, name, type) VALUES 
(1, 'The Forum', 0),
(2, 'Operations Gallery', 2), --Guests Can't Speak
(3, 'Dungeon Club', 6), --creaky door
(4, 'Auditorium', 3), -- Moderated Room
(5, 'Blue Room', 4), -- Members Only (in Melinda's Backups this is Adults)
(6, 'Green Room', 5), -- Guest Only (in Melinda's Backups this is Juveniles AKA Baby Backups)
(7, 'The Board Room', 1), --various meeting rooms - need to be on users room list
(8, 'Marketing', 1),
(9, 'Engineering',1),
(10, 'IT Dept', 1),
(11, 'Finance', 1);


Now users themselves are carried over from the previous database (or from the forum and have a matching table)

CREATE TABLE users (
  uid integer primary key NOT NULL,
  time bigint DEFAULT (strftime('%s','now')) NOT NULL,
  name character varying NOT NULL,
  role char(1) NOT NULL default 'R',
  rid integer NOT NULL default 0,
  mod char(1) NOT NULL default 'N',
  question character varying,
  private integer NOT NULL default 0,
  cap integer NOT NULL default 0,
  rooms character_varying 
);


But this is not all - I have another concept - which is that of a Participant in a conversation - with a conversation being just an ID.

CREATE table wid_sequence ( value integer);
INSERT INTO wid_sequence (value) VALUES (1);

CREATE TABLE participant (
  uid integer NOT NULL REFERENCES users (uid) ON DELETE CASCADE ON UPDATE CASCADE,
  wid integer NOT NULL,
  primary key (uid,wid)
);


Not there is a wid_sequence table which my code manually increments when anyone creates a new conversation - (I call it a whisper box, but sometimes it becomes what I call a private room ), and then a record in the participant table whilst the conversation is in place.

Finally, so I have an archive of messages - which I can reference and play back at a later date (either a short list of recent messages when someone enters a room, or someone with the secretary capability can enter a room and obtain a printout of the entire conversation - as minutes of a meeting for instance).

I think it is this table that is your archive that you mention in the question

The schema for this is

CREATE TABLE log (
  lid integer primary key,
  time bigint DEFAULT (strftime('%s','now')) NOT NULL,
  uid integer NOT NULL,
  name character varying NOT NULL,
  role char(1) NOT NULL,
  rid integer NOT NULL,
  type char(2) NOT NULL,
  text character varying
);


The important item here is the type field.  It is in essence a replay of the actions as well as the conversation, so we also can replay new conversations starting up (where rid then maps to the wid created from wid_sequence), and people entering and exiting rooms etc.

I trust that gives you an idea how you can proceed.  Feel free to examine my code on github and ask any questions that you might have of it.

As a postscript, there is a table I didn't mention called 'parameters' which controls a lot of how things work.  One of those is whether encryption needs to be used.  It is beyond the scope of this answer to go into that - as it is fairly complicated - but you may need to consider how private you want private conversations to be and whether or not they should be stored encyrpted in your archive.