Database Schema

Users

column name	data type	details
id	integer	not null, primary key
username	string	not null
email	string	not null, unique
profile_pic_url	string	allow null
bio	text	allow null
hashed_password	string	not null, BINARY
created_at	timestamp	not null
updated_at	timestamp	not null

Servers

column name	data type	details
id	integer	not null, primary key
name	string	not null
banner_url	string	allow null
server_icon_url	string	allow null
owner_id	integer	not null, foreign key
dm_channel	boolean	default: False
public	boolean	default: True
created_at	timestamp	not null
updated_at	timestamp	not null

• owner_id references Users table

Server_Users

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, foreign key
server_id	integer	not null, foreign key

• user_id references Users table
• server_id references Servers table

Channels

column name	data type	details
id	integer	not null, primary key
name	string	not null
server_id	integer	not null, foreign key
created_at	timestamp	not null
updated_at	timestamp	not null

• server_id references Servers table

Channel_Messages

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, foreign key
content	text	not null
channel_id	integer	not null, foreign key
created_at	timestamp	not null
updated_at	timestamp	not null

• user_id references Users table
• channel_id references Channels table

Friends

column name	data type	details
id	integer	not null, primary key
status	boolean	default: False
user1_id	integer	not null, foreign key
user2_id	integer	not null, foreign key
created_at	timestamp	not null
updated_at	timestamp	not null

• user1_id references Users table
• user2_id references Users table

DM_Server

column name	data type	details
id	integer	not null, primary key
user1_id	integer	not null, foreign key
user2_id	integer	not null, foreign key
created_at	timestamp	not null
updated_at	timestamp	not null

• user1_id references Users table
• user2_id references Users table

Direct_Messages

column name	data type	details
id	integer	not null, primary key
chat_id	integer	not null, foreign key
content	text	not null
sender_id	integer	not null, foreign key
created_at	timestamp	not null
updated_at	timestamp	not null

• sender_id references Users table
• chat_id references DM_Server table