Database Schema

users

column Name	data type	details
id	integer	not null, primary key
email	string	not null, indexed, unique
full_name	string	not null
display_name	string	allow null
title	string	allow null
user_description	text	allow null
user_status	string	allow null
user_avatar_url	string	not null
password_digest	string	not null
session_token	string	not null, indexed, unique
created_at	datetime	not null
updated_at	datetime	not null

• has many messages
• has many conversations through memberships
• has many attachments
• has many reactions

messages

column name	data type	details
id	integer	not null, primary key
body	text	not null
author_id	integer	not null, indexed, foreign key
recipient_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• author_id is a foreign key belonging to the users table.
• recipient_id is a foreign key belonging to the conversations` table.
• messages belong to both users and conversations
• has many reactions
• has many attachments

conversations

column name	data type	details
id	integer	not null, primary key
name	string	not null, unique (would not be unique in the case of multiple workspaces)
description	text	allow null
owner_id	integer	indexed, foreign key
is_private?	boolean	not null
playlist_url	string	not if type == "channel"
restricted_playlist	boolean	allow null. If true, only admins can edit playlist
type	text	includes "direct_message", "group_message", or "channel", not null
created_at	datetime	not null
updated_at	datetime	not null

• owner_id is a foreign key of the users table
• has many messages
• has many members users memberships

memberships

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

• belongs to users through foreign key member_id
• belongs to conversations through foreign key conversation_id

admins

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

• belongs to users through foreign key admin_user_id
• belongs to conversations through foreign key conversation_id