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
image_url	string	not null
password_digest	string	not null
session_token	string	not null, indexed, unique
created_at	datetime	not null
updated_at	datetime	not null

---

tracks

column name	data type	details
id	integer	not null, primary key
mp4_data	bit varying	not null, primary key
title	string	not null, indexed
description	text
user_id	integer	not null, indexed
playlist_id	integer	indexed
playlist_ord	integer
created_at	datetime	not null
updated_at	datetime	not null

• user_id references users
• playlist_id references playlists
• Index on [:playlist_id, :playlist_ord], unique: true

---

playlists

column name	data type	details
id	integer	not null, primary key
title	string	not null, indexed
description	text
image_url	string	not null
user_id	integer	not null, indexed
created_at	datetime	not null
updated_at	datetime	not null

• user_id references users
• Index on [:user_id, :title_id], unique: true

---

comments

column name	data type	details
id	integer	not null, primary key
body	text	not null
user_id	integer	not null, indexed
timestamp	integer
track_id	integer	indexed
playlist_id	integer	indexed
parent_comment_id	integer	indexed
created_at	datetime	not null
updated_at	datetime	not null

• user_id references users
• track_id references tracks
• playlist_id references playlists
• parent_comment_id references comments
• Constraint so that only one of track_id and playlist_id is not null

---

follows

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

• follower_id and followee_id references users
• Index on [:follower_id, :followee_id], unique: true

---

likes

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

• user_id references users
• track_id references tracks
• playlist_id references playlists
• Index on [:user_id, :track_id], unique: true
• Index on [:user_id, :playlist_id], unique: true
• Constraint so that only one of track_id and playlist_id is not null

---

reposts

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

• user_id references users
• track_id references tracks
• playlist_id references playlists
• Index on [:user_id, :track_id], unique: true
• Index on [:user_id, :playlist_id], unique: true
• Constraint so that only one of track_id and playlist_id is not null