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
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_id
• invite_token is used for a permanent invitation link
• index on invite_token, unique: true

server_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

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	author_id	not null, foreign key
integer	thread_id	not null, foreign key
string	thread_type	not null
text	body	not null
datetime	created_at	not null
datetime	updated_at	not null

• author_id references users
• thread represents a polymorphic association between messages and channels/dm_threads
• index on author_id
• index on [thread_id, thread_type]

dm_threads

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

dm_memberships

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

• member_id references users
• dm_thread_id references dm_threads
• index on member_id
• index on dm_thread_id

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