Schema

Database Schema

Main Tables

users

columns name	data type	details
id	integer	not null, primary key
name	string	not null
username	string	not null, indexed, unique
email	string	not null, indexed, unique
avatar_url	string
company	string	not null
job_title	string
admin	boolean	default false
owner	boolean	default false
password-digest	string	not null
session_token	string	not null, unique, indexed
created_at	datetime	not null
updated_at	datetime	not null

projects

columns name	data type	details
id	integer	not null, primary key
name	string	not null
description	string
type	string	not null, allowed values: company, team, project
admin_id	integer	not null
created_at	datetime	not null
updated_at	datetime	not null

messages

columns name	data type	details
id	integer	not null, primary key
title	string	not null
body	text
type	string
author_id	integer	not null
project_id	integer	not null
created_at	datetime	not null
updated_at	datetime	not null

todo_lists

columns name	data type	details
id	integer	not null, primary key
title	string	not null
description	text
author_id	integer	not null
project_id	integer	not null
created_at	datetime	not null
updated_at	datetime	not null

todo_lists

columns name	data type	details
id	integer	not null, primary key
title	string	not null
description	text
author_id	integer	not null
created_at	datetime	not null
updated_at	datetime	not null

todos

columns name	data type	details
id	integer	not null, primary key
title	string	not null
description	text
author_id	integer	not null
done	boolean	not null, default false
todolist_id	integer	not null
due_date	datetime
created_at	datetime	not null
updated_at	datetime	not null

events

columns name	data type	details
id	integer	not null, primary key
title	string	not null
description	text
author_id	integer	not null
start_date	datetime	not null
end_date	datetime	not null
created_at	datetime	not null
updated_at	datetime	not null

comments

columns name	data type	details
id	integer	not null, primary key
body	string	not null
author_id	integer	not null
parent_type	string	not null
created_at	datetime	not null
updated_at	datetime	not null

Join Tables

user_projects

columns name	data type	details
id	integer	not null, primary key
user_id	integer	not null
project_id	integer	not null

user_todos

columns name	data type	details
id	integer	not null, primary key
user_id	integer	not null
todo_id	integer	not null

user_events

columns name	data type	details
id	integer	not null, primary key
user_id	integer	not null
event_id	integer	not null

message_comments

columns name	data type	details
id	integer	not null, primary key
message_id	integer	not null
comment_id	integer	not null

todolist_comments

columns name	data type	details
id	integer	not null, primary key
todolist_id	integer	not null
comment_id	integer	not null

todo_comments

columns name	data type	details
id	integer	not null, primary key
todo_id	integer	not null
comment_id	integer	not null

event_comments

columns name	data type	details
id	integer	not null, primary key
event_id	integer	not null
comment_id	integer	not null