Database Diagram

table users {
  id integer [pk, default: `identity()`]
  username varchar(100) [not null, unique, note: 'Username is unique']
  created_at timestamp
  user_type user_type [not null, default: 'employee', note: 'type of users']

  note: '''
  Manage user system
  which map from Social login or Self register from the system
  username should map with email default
   10 | my.it@outlook.com | 02-02-2024 | administrator | {roles: [{name: admin}]}
  '''
  // user database
}

enum user_type {
    employee
    manager
    administrator
    sysops
    maintainer
}

table roles {
  id integer [pk]
  name varchar(50)
  created_at timestamp
  updated_at timestamp
  
  updated_by varchar(250)
  created_by varchar(250)

  note: '''
  The role of users
  For example: maybe we have multiple role likes: Administrator, ContentWriter, Reviewer, etc..
  '''
}

table role_permission {
  id integer [pk]
  permission varchar(200)
  role_id integer [ref: > roles.id]
}

table user_roles {
  user_id integer [pk]
  role_id integer [pk]
}

ref: user_roles.user_id > users.id
ref: user_roles.role_id > roles.id

table posts {
  id integer [pk]
  title varchar() [note: 'Relate to title of Post']
  body text [note: 'Stored content of post']
  author_id integer [ref: > users.id] // wrong relation ship 
  created_at timestamp [note: 'Created user for document']
  name varchar(200)
  post_status varchar(20) [note: 'Draft-published-privated'] 
  seo json  [note: '''
   type SEO = {
     geoGraph: string
     metaTitle: string
   }
  ''']
}