database_schema.dbml

Table chats {
  chatId int [pk, not null]
  username varchar
  date bigint
  title text
  description text
  
  // other rather unimportant info:
  member_count int
  
  is_channel tinyint [not null, default: 0]
  is_verified tinyint [not null, default: 0]
  is_scam tinyint [not null, default: 0]
  
  
  // update info
  last_updated bigint [not null]
  
  // should we monitor this channel?
  monitor tinyint [not null, default: 0]
}

Table chats_updates {
  updateId int [pk, increment, not null]
  chatId int [not null]
  
  key varchar [not null]
  old_value text
  new_value text
  
  date bigint [not null, note: "Date of update"]
}
Ref: chats_updates.chatId > chats.chatId

// > many-to-one; < one-to-many; - one-to-one

Table users {
  userId int [pk, not null]
  username varchar
  
  first_name varchar
  last_name varchar
  phonr_nr varchar
  
  is_verified tinyint [not null, default: 0]
  is_support tinyint [not null, default: 0]
  is_scam tinyint [not null, default: 0]
}

Table users_group_memberships {
  userId int [pk, not null]
  groupId int [pk, not null]
}
Ref: users_group_memberships.userId > users.userId
Ref: users_group_memberships.groupId > chats.chatId

Table users_updates {
  updateId int [pk, increment, not null]
  userId int [not null]
  key varchar [not null]
  old_value text
  new_value text
  date bigint [not null]
}

Ref: users_updates.userId > users.userId

Table content_types {
  typeId int [pk, not null]
  type varchar [not null]
}

Table messages {
  messageId int [pk, not null]
  chatId int [not null]
  userId int [not null]
  content_type int [not null]
  content text
  date bigint [not null]
  deleted_on bigint
  is_channel_post tinyint [default: 0]
}
Ref: messages.chatId > chats.chatId
Ref: messages.userId > users.userId
Ref: messages.content_type > content_types.typeId

Table messages_urls {
  urlId int [pk, increment, not null]
  messageId int [not null]
  url text [not null]
  domain text
  path text
}
Ref: messages_urls.messageId > messages.messageId

Table messages_edits {
  editId int [pk, increment, not null]
  messageId int [not null]
  old_content text
  new_content text
  date bigint [not null]
}
Ref: messages_edits.messageId > messages.messageId