Schema

Postgres Database Schema

users

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

• index on email, unique: true
• index on display_name

messages

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

• parent_message_id is a foreign-key that self-references id
• author_id references the author of the message
• channel_id references the channel the messages belong to
• dm_id references the direct message or group message for the message

channels

column name	data type	details
id	integer	not null, primary key
name	string	indexed, unique, not null
topic	string
description	string
creator_id	integer	not null, foreign key
member_id	integer	not null, foreign key, indexed
created_at	datetime	not null
updated_at	datetime	not null

• creator_id references users who created the channel and can rename or delete a channel
• member_id references users in the channel without the above permissions

dms

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

• member_id references users (2 or more) in the channel who can message back and forth

reacts

column name	data type	details
id	integer	not null, primary key
image_url	string	not null
message_id	integer	foreign key, not null
reactor_id	integer	foreign_key, not null
created_at	datetime	not null
updated_at	datetime	not null

• reactor_id references users who reacted to that message
• message_id references the message with emojis