schema

Database Schema

users

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

• index on [username, tag], unique: true
• index on email, unique: true
• index on session_token, unique: true

servers

data type	column name	details
integer	id	not null, primary key
integer	owner_id	not null, foreign key
string	name	not null
text	description	not null
boolean	is_private	not null, default: false
string	invite_token	not null
datetime	created_at	not null
datetime	updated_at	not null

• owner_id references users
• index on owner_idwith one channel and two equal members
• invite_token is used for a permanent invitation link

channels

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

• server_id references servers
• index on server_id

messages

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

• channel_id references channels
• author_id references users
• index on channel_id
• index on author_id

memberships

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

• server_id references servers
• user_id references users
• index on [user_id, server_id], unique: true

invitations (Bonus 2 only)

data type	column name	details
integer	id	not null, primary key
integer	server_id	not null, foreign key
integer	invitee_id	foreign key
string	token	not null
datetime	expiry	not null
datetime	created_at	not null
datetime	updated_at	not null

• server_id references servers
• invitee_id references users
• index on [invitee_id, server_id], unique: true
• index on token, unique: true
• used for temporary invitations
• invitee_id used for private invitations; allow null for general invitations

---

Notes

• servers#is_private won't come into play until private servers are introduced
• DMs will require more thought, since they are very similar to channels, but don't belong to a normal server
• Note the distinction between servers#invite_token and invitations#token. They are for permanent and temporary invitation, respectively