Database Schema

Database Schema

users

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

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

---

messages

column name	data type	details	foreign key
id	integer	not null, primary key
body	text	not null
chatroom_id	integer	not null, indexed	true
author_id	integer	not null, indexed	true
parent_id	integer	indexed	true
created_at	datetime	not null
updated_at	datetime	not null

• index on :chatroom_id
• index on :author_id
• index on :parent_id
• :author_id references users id
• :chatroom_id references chatrooms id
• :parent_id references messages id

---

chatrooms

column name	data type	details	foreign key
id	integer	not null, primary key
title	string	indexed
topic	string	not null
chatroom_type	string	not null
admin_id	integer	not null	true
created_at	datetime	not null
updated_at	datetime	not null

• :admin_id references users id
• :type (Model level validation: inclusion: { in: [channel, direct_message] }

---

chatroom_subscriptions

column name	data type	details	foreign key
id	integer	not null, primary key
chatroom_id	integer	not null	true
user_id	integer	not null	true
last_read_message	integer	not null, indexed	true
created_at	datetime	not null
updated_at	datetime	not null

• index on [:chatroom_id, :user_id], unique: true
• index on [:chatroom_id, :user_id, last_read_message]
• :user_id references users id
• :chatroom_id references chatrooms id
• :last_read_message references messages id

---

stars

column name	data type	details	foreign key
id	integer	not null, primary key
starrable	references	polymorphic, indexed	true
user_id	integer	not null	true
created_at	datetime	not null
updated_at	datetime	not null

• index on [:user_id, :starrable_id, :starrable_type], unique: true
• :user_id references users id
• :starrable references either messages id or chatrooms id

---

pinned_messages

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

• index on [:chatroom_id, :message_id], unique: true
• :message_id references messages id
• :chatroom_id references chatrooms id

---

reactions

column name	data type	details	foreign key
id	integer	not null, primary key
emoji_id	text	not null	true
message_id	integer	not null	true
user_id	integer	not null	true
created_at	datetime	not null
updated_at	datetime	not null

• index on [:message_id, :user_id, emoji_id], unique: true
• :user_id references users id
• :message_id references messages id
• :emoji_id references emojis id

---

emojis

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

---