Users

column name	data type	details
id	integer	not null, primary key
username	string	not null, indexed, unique
password_digest	string	not null
session_token	string	not null, indexed, unique
created_at	datetime	not null
updated_at	datetime	not null

• Indexed on username, email, and session_token
• All of the above required to be unique
• Space for potential details to include (pronoun, nickname, etc)

Channels

column name	data type	details
id	integer	not null, primary key
admin_id	integer	not null, indexed, foreign key
name	string	not null, indexed, unique
description	string	not null
public	boolean	not null
created_at	datetime	not null
updated_at	datetime	not null

• admin_id references the user who created the channel
• Indexed on admin_id
• Indexed on name (channel's name) and unique

DM/Multi-person DM (this will be in the same db as I don't see a difference

column name	data type	details
id	integer	not null, primary key
admin_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• admin_id references the user who created the channel
• Indexed on admin_id
• Indexed on name (channel's name) and unique

Live Chat (this is the common chat that everything uses)

column name	data type	details
id	integer	not null, primary key
created_at	datetime	not null
updated_at	datetime	not null

• Live Chat sits in the middle between live chat and the channels/dms

Message

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, indexed, foreign key
chat_id	integer	not null, indexed, foreign key
message	text
created_at	datetime	not null
updated_at	datetime	not null

• user_id references the user who send the message
• chat_id references the Live Chat
• IS THERE A WAY TO keep it sorted by created_at time

Joins Tables below

Channel Membership

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, indexed, foreign key
channel_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• user_id references the user
• channel_id references the channel

DM Membership

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, indexed, foreign key
dm_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• user_id references the user
• dm_id references the dm/group dm

Channel Chat

column name	data type	details
id	integer	not null, primary key
channel_id	integer	not null, indexed, foreign key
chat_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• channel_id references the channel
• chat_id references the chat

DM Chat

column name	data type	details
id	integer	not null, primary key
dm_id	integer	not null, indexed, foreign key
chat_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• dm_id references the channel
• chat_id references the chat