Schema

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
full name	string	optional
biography	string	optional
`image_url	string	optional
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 session_token, unique: true
• index on email, unique: true
• users have many posts
• users have many followers

posts

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

• user_id references users
• index on user_id
• posts belong to an author (user)
• posts have many likes
• posts have many comments

likes

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

• user_id references users
• post_id references posts
• index on [:post_id, :user_id]
• index on :user_id
• likes belong to users
• likes belong to posts

comments

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

• user_id references users
• post_id references posts
• index on [:post_id, :user_id], unique: true
• index on :user_id
• comments belong to users
• comments belong to posts

following

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

• user_id references users
• follower_id references users
• index on [:user_id, :follower_id], unique: true
• followers belong to users