Database Design - Githubissues

For the first MVP these are all features that we're going to consider. If there are others features or modules to bear in mind. Let us know 😉✌️.

Enum action_type {
  like
  unlike
  comment
  view
  follow
  reply
  repost
}

Enum post_type {
  post
  comment
  reply
}

Enum entity_type {
  user
  post
}

Table users{
  id varchar [pk]
  email varchar [unique]
  password varchar
  username varchar [unique]
  phone varchar
  description text
  first_name varchar
  last_name varchar
  birtdate timestamp
  followers_count integer [default: '0']
  created_at timestamp [default: 'now()']
  updated_at timestamp [default: 'now()']

  indexes {
    username
    email
  }
}

Table followers {
  id varchar [pk]
  follower_id varchar // User
  followee_id varchar // User
  followed_at timestamp [default: 'now()']

  indexes {
    (follower_id, followee_id) [unique]
  }
}

// / User Activity Module

Table user_activity {
  id integer [pk]
  actioned_by varchar // user_id
  action_type_id integer
  target_id varchar
  target_type entity_type
  action_at timestamp [default: 'now()']

  indexes {
    action_type_id
  }
}

Table action_types {
  id integer [pk]
  name varchar
  action action_type
}

// / Posts Module

Table posts{
  id varchar [pk]
  content text
  likes_count integer [default: '0']
  comments_count integer [default: '0']
  reposts_count integer [default: '0']
  parent_post_id varchar
  post_type post_type
  created_at timestamp [default: 'now()']
  updated_at timestamp [default: 'now()']
}

Table likes {
  id varchar [pk]
  post_id varchar // unique [post_id, user_id]
  user_id varchar
  created_at timestamp [default: 'now()']

  indexes {
    (post_id, user_id) [unique]
  }
}

// Media Module

Table media {
  id varchar [pk]
  file_path varchar
  file_type varchar
  entity_type entity_type
  entity_id varchar
  uploaded_by varchar // user_id
  created_at timestamp [default: 'now()']

  indexes {
    entity_id
  }
}

Ref {
  followers.followee_id > users.id
}

Ref {
  followers.follower_id > users.id
}

Ref {
  likes.user_id > users.id
}

Ref {
  user_activity.actioned_by > users.id
}

Ref {
  user_activity.action_type_id > action_types.id
}

Ref {
  likes.post_id > posts.id
}

Ref {
  posts.parent_post_id > posts.id
}

sleeplesscofi / express-backend-threads

Database Design #6