Users

column name	data type	details
id	integer	not null, primary key
username	string	not null, indexed, unique
name.	string	not null
website	string	optional, unique
birthday	date	not null
bio	text	optional
email	string	not null, indexed, unique
password_digest	string	not null
session_token	string	not null, indexed, unique
created	datetime	not null
updated	datetime	not null

• Index on username, unique: true
• Index on email, unique: true
• Index on session_token, unique: true
• Including birthday, presence: true at model level to enforce age restrictions
• email will be validated with Regex at the model level

Users associations

• has_many :posts
• has_many :comments
• has_many :tags
• has_many :tagged
• has_many :post_likes
• has_many :comment_likes

Posts

column name	data type	details
id	integer	not null, primary key
caption	string	optional
author_id	integer	not null, indexed, foreign key
created	datetime	not null
updated	datetime	not null

• Index on author_id, unique: true
• author_id is keyed to a user's id

Posts associations

• belongs_to :user
• has_many :hashtags
• has_many :tags
• has_many :post_likes
• has_many :comments

Post_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	datetime	not null
updated	datetime	not null

• Index on [:user_id, :likable_id] for faster recall
• Joins table for likes

Post_like associations

• belongs_to :user
• belongs_to :post

Comments

column name	data type	details
id	integer	not null, primary key
body	text	not null
user_id	integer	not null, indexed, foreign key
post_id	integer	not null, indexed, foreign key
created	datetime	not null
updated	datetime	not null

• indexed on [:photo_id]
• indexed on [:post_id]

Comments associations

• belongs_to :user
• belongs_to :post
• has_many :likes

Comment_likes

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, indexed, foreign key
comment_id	integer	not null, indexed, foreign key
created	datetime	not null
updated	datetime	not null

• Index on [:user_id, :likable_id] for faster recall
• Joins table for likes

Comment_likes

• belongs_to :user
• belongs_to :comment

Follows

column name	data type	details
id	integer	not null, primary key
followed_id	integer	not null, indexed, foreign key
follower_id	integer	not null, indexed, foreign key
created	datetime	not null
updated	datetime	not null

• Joins table for follows

Follows associations

• belongs_to :follower
• belongs_to :followed

Bonus features

Tags

column name	data type	details
id	integer	not null, primary key
tagged_id	integer	not null, foreign key
tagger_id	integer	not null, foreign key

• tags are universal and don't have any "scope" users are notified and link-able on posts and comments through tags

Post_hashtag

column name	data type	details
id	integer	not null, primary key
hashtag_id	integer	not null, indexed, foreign key
post_id	integer	not null, indexed, foreign key

Comment_hashtag

column name	data type	details
id	integer	not null, primary key
hashtag_id	integer	not null, indexed, foreign key

• post_id analog is not required as Instagram does not seem to display posts with hashtags in their comments to their respective hashtag page