Schema

Postgres Database Schema

---

users

---

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

tasks

---

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, indexed, foreign key
list_id	integer	not null, indexed, foreign key
title	string	not null, indexed, unique
due_date	datetime	not null, indexed
priority	integer	not null, indexed, default = 4
completed	boolean	not null, indexed, default = false
estimate	integer	indexed
created_at	datetime	not null
updated_at	datetime	not null

• index on [:title, :list_id], unique: true
  • user_id references users
• index on list_id
  • list_id references lists
  • list_id defaults to the id of the user's inbox list, which is created when they create an account
• index on title
• index on due_date
• index on priority
  • values are 1-4, with 1 having the highest priority, and 4 to mean "No Priority"
• index on completed
• index on estimate
  • estimate is estimated time to perform the task, measured in minutes.

lists

---

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, indexed, foreign key
title	string	not null, indexed, unique
created_at	datetime	not null
updated_at	datetime	not null

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