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
bio	string
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

projects

column name	data type	details
id	integer	not null, primary key
title	string	not null, indexed, unique
description	string	not null
campaign	string
updates	string
faq	string
location	string	not null
start_date	datetime	not null
end_date	datetime	not null
funding_goal	float	not null
creator_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on title, unique: true
• index on creator_id
• creator_id references users

categories

column name	data type	details
id	integer	not null, primary key
name	string	not null, indexed, unique

• index on name, unique: true

backings

column name	data type	details
id	integer	not null, primary key
amount_pledged	float	not null
backer_id	integer	not null, indexed, foreign key
project_id	integer	not null, indexed, foreign key
reward_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on user_id
• index on project_id
• index on reward_id
• backer_id references users
• project_id references projects
• reward_id references rewards

comments

column name	data type	details
id	integer	not null, primary key
body	text	not null
commenter_id	integer	not null, indexed, foreign key
project_id	integer	not null, indexed, foreign key
parent_comment_id	integer	foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on commenter_id
• index on project_id
• commenter_id references users
• project_id references projects
• parent_comment_id references comments

rewards

column name	data type	details
id	integer	not null, primary key
title	string	not null
description	string	not null
project_id	integer	not null, indexed, foreign key
cost	float	not null
created_at	datetime	not null
updated_at	datetime	not null

• index on project_id
• project_id references projects

likes

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

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

project_categories

column name	data type	details
id	integer	not null, primary key
project_id	integer	not null, indexed, foreign key
category_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on project_id
• index on reward_id
• project_id references projects
• category_id references categories