database.dbml

// Use DBML to define your database structure
// Docs: https://dbml.dbdiagram.io/docs

Table follow {
  id integer [primary key, not null]
  following_user_id integer [not null, note: The user sending the follow]
  followed_user_id integer [not null, note: The user reciving the follow]
  created_at timestamp [not null]
}

ref: users.id < follow.following_user_id
ref: users.id < follow.followed_user_id

Table users {
  id integer [primary key]
  username varchar [not null, note: 'username assigned to user.']
  name varchar [note: 'real name of user optional.']
  recent_client integer [note: 'the last client to be used by the user']
  role integer [not null, default: 0, note: 'weather the user has administrative permisions']
  created_at timestamp [not null, note: 'user creation timestamp']
}

Ref: users.recent_client - clients.id


Table clients {
  id integer [not null, primary key, note: 'index not used by user']
  user_id integer [not null, note: 'client owner']
  identifier varchar [not null, note: 'hashed unique client identifier']
  secret varchar [not null, note: 'client secret']
}

Ref: clients.user_id > users.id

Table posts {
  id integer [primary key]
  user_id integer [not null]
  up_votes integer [not null]
  down_votes integer [not null]
  title varchar [not null]
  body text [note: 'Content of the post', not null]
  created_at timestamp [not null, note: 'timestamp of posting']
  edited_at timestamp [note: 'can be nulled if has not been edited']
}

Ref: posts.user_id > users.id

Table comments {
  id integer [primary key]
  user_id integer [not null]
  post_id integer [not null]
  reply_coment_id integer [note: 'can be null if not a reply']
  body text [not null, note: 'comment content']
  created_at timestamp [not null, note:'timespamp of posting']
  edited_at timestamp [note: 'can be null if not been edited']
}

Ref: comments.user_id > users.id
Ref: comments.id < comments.reply_coment_id
Ref: posts.id < comments.post_id

Table one_time_codes {
  id integer [primary key]
  user_id integer [not null, note: the user that the code is atached too]
  code varchar [not null, note: otp code to be used to authenticate new clients]
  expiry_date timestamp [note: optional time stamp if its a recovery code this field will be absent]
}

Ref: one_time_codes.user_id > users.id