Postgres Database Schema

users

column name	data type	details
id	bigint	not null, primary key
first_name	string	not null
last_name	string	not null
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 email, unique: true
• index on session_token, unique: true

workspaces

column name	data type	details
id	bigint	not null, primary key
name	string	not null
owner_id	bigint	not null, indexed, foreign_key
created_at	datetime	not null
updated_at	datetime	not null

• index on owner_id

workspace_users

column name	data type	details
id	bigint	not null, primary key
workspace_id	bigint	not null, indexed, foreign_key
user_id	bigint	not null, indexed, foreign_key
created_at	datetime	not null
updated_at	datetime	not null

• index on workspace_id
• index on user_id

messages

column name	data type	details
id	bigint	not null, primary key
body	text	not null
parent_message_id	bigint	indexed, foreign_key
author_id	bigint	not null, indexed, foreign_key
workspace_id	bigint	not null, indexed, foreign_key
created_at	datetime	not null
updated_at	datetime	not null

• index on author_id
• index on workspace_id
• index on parent_message_id

channels

column name	data type	details
id	bigint	not null, primary key
name	string	not null
description	text
owner_id	bigint	not null, indexed, foreign_key
workspace_id	bigint	not null, indexed, foreign_key
created_at	datetime	not null
updated_at	datetime	not null

• index on owner_id
• index on workspace_id

chats

column name	data type	details
id	bigint	not null, primary key
owner_id	bigint	not null, indexed, foreign_key
workspace_id	bigint	not null, indexed, foreign_key
created_at	datetime	not null
updated_at	datetime	not null

• index on owner_id
• index on workspace_id

channel_messages

column name	data type	details
id	bigint	not null, primary key
channel_id	bigint	not null, indexed, foreign_key
message_id	bigint	not null, foreign_key
created_at	datetime	not null
updated_at	datetime	not null

• index on channel_id

chat_messages

column name	data type	details
id	bigint	not null, primary key
chat_id	bigint	not null, indexed, foreign_key
message_id	bigint	not null, foreign_key
created_at	datetime	not null
updated_at	datetime	not null

• index on chat_id