Database Schema

Users

Column Name	Data Type	Details
id	integer	not null, primary key
username	string	not null, indexed, unique
email	string	not null, indexed, unique
password_digest	string	not null, unique
session_token	string	not null, indexed, unique
status	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• index on username, unique: true
• index on email, unique: true
• index on session_token, unique: true

Friends

Column Name	Data Type	Details
id	integer	not null, primary key
user_id_1	integer	not null, indexed, unique
user_id_2	integer	not null, indexed, unique
created_at	datetime	not null
updated_at	datetime	not null

• index on [user_id_1, user_id_2], unique: true
• user_id_1 references User
• user_id_2 references User

Servers

Column Name	Data Type	Details
id	integer	not null, primary key
name	string	not null, indexed, unique
owner_id	integer	foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on name, unique: true
• owner_id references Users
• Note: owner_id can be null in the case of private servers

Affiliations

Column Name	Data Type	Details
id	integer	not null, primary key
user_id	integer	not null, indexed, foreign key
server_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on [user_id, server_id], unique: true
• user_id references Users
• server_id references Servers

Channels

Column Name	Data Type	Details
id	integer	not null, primary key
name	string	not null, indexed, unique
server_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on name, unique: true
• index on server_id
• server_id references Server

Messages

Column Name	Data Type	Details
id	integer	not null, primary key
body	text	not null
author_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on author_id
• author_id references Users

Channel_Messages

Column Name	Data Type	Details
id	integer	not null, primary key
message_id	integer	not null, foreign key
channel_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on channel_id
• message_id references Messages
• channel_id references Channel

Direct_Messages

Column Name	Data Type	Details
id	integer	not null
message_id	integer	not null, foreign key
server_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on recipient_id
• message_id references recipient_id
• recipient_id references Users