initial_schema.dbml

// Copyright (C) 2024 Afonso Barracha
// 
// This file is part of KiwiScript.
// 
// KiwiScript is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
// 
// KiwiScript is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
// 
// You should have received a copy of the GNU General Public License
// along with KiwiScript.  If not, see <https://www.gnu.org/licenses/>.

// TODO: Add hashtags to series for better search

Table users as U {
  id serial [pk]
  first_name varchar(50) [not null]
  last_name varchar(50) [not null]
  location varchar(3) [not null]
  email varchar(250) [not null]
  version smallint [not null, default: 1]
  is_admin boolean [not null, default: false]
  is_staff boolean [not null, default: false]
  is_confirmed boolean [not null]
  password text [null]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]

  indexes {
    email [unique, name: 'users_email_unique_idx']
    is_staff [name: 'users_is_staff_idx']
  }
}

Table user_profiles as UP {
  id serial [pk]
  user_id int [not null]
  bio text [not null]
  github varchar(250) [not null]
  linkedin varchar(250) [not null]
  website varchar(250) [not null]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]

  indexes {
    user_id [unique, name: 'user_profiles_user_id_unique_idx']
  }
}
Ref: UP.user_id > U.id [delete: cascade, update: cascade]

Table user_pictures as UPI {
  id uuid [pk]
  user_id int [not null]
  ext varchar(10) [not null]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]

  indexes {
    user_id [unique, name: 'user_pictures_user_id_unique_idx']
  }
}
Ref: UPI.user_id > U.id [delete: cascade, update: cascade]

Table auth_providers as AP {
  id serial [pk]
  email varchar(250) [not null]
  provider varchar(8) [not null]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`] 

  indexes {
    email [name: 'auth_providers_email_idx']
    (email, provider) [unique, name: 'auth_providers_email_provider_unique_idx']
  }
}
Ref: AP.email > U.email [delete: cascade, update: cascade]

Table languages as L {
  id serial [pk]
  name varchar(50) [not null]
  slug varchar(50) [not null]
  icon text [not null]
  series_count smallint [not null, default: 0]
  author_id int [not null]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]

  indexes {
    name [unique, name: 'languages_name_unique_idx']
    slug [unique, name: 'languages_slug_unique_idx']
  }
}
Ref: L.author_id > U.id [delete: cascade, update: cascade]

Table series as S {
  id serial [pk]
  title varchar(100) [not null]
  slug varchar(100) [not null]
  description text [not null]
  sections_count smallint [not null, default: 0]
  lessons_count smallint [not null, default: 0]
  watch_time_seconds int [not null, default: 0]
  read_time_seconds int [not null, default: 0]
  is_published boolean [not null, default: false]
  language_slug varchar(50) [not null]
  author_id int [not null]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]

  indexes {
    title [unique, name: 'series_title_unique_idx']
    slug [unique, name: 'series_slug_unique_idx']
    is_published [name: 'series_is_published_idx']
    language_slug [name: 'series_language_slug_idx']
    (slug, language_slug) [name: 'series_slug_language_slug_idx']
    (id, language_slug) [name: 'series_id_language_slug_idx']
    (id, language_slug, is_published) [name: 'series_id_language_slug_is_published_idx']
    (slug, language_slug, is_published) [name: 'series_slug_is_published_idx']
    author_id [name: 'series_author_id_idx']
  }
}
Ref: S.author_id > U.id [delete: cascade, update: cascade]
Ref: S.language_slug > L.slug [delete: cascade, update: cascade]

Table series_pictures as SPI {
  id uuid [pk]
  series_id int [not null]
  author_id int [not null]
  ext varchar(10) [not null]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]

  indexes {
    series_id [unique, name: 'series_images_series_id_unique_idx']
    author_id [name: 'series_images_author_id_idx']
  }
}
Ref: SPI.author_id > U.id [delete: cascade, update: cascade]
Ref: SPI.series_id > S.id [delete: cascade, update: cascade]

Table sections as SP {
  id serial [pk]
  title varchar(250) [not null]
  language_slug varchar(50) [not null]
  series_slug varchar(100) [not null]
  description text [not null]
  position smallint [not null]
  lessons_count smallint [not null, default: 0]
  watch_time_seconds int [not null, default: 0]
  read_time_seconds int [not null, default: 0]
  is_published boolean [not null, default: false]
  author_id int [not null]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]

  indexes {
    (title, series_slug) [unique, name: 'sections_title_series_slug_unique_idx']
    (series_slug, position) [name: 'sections_series_slug_position_idx']
    (language_slug, series_slug) [name: 'sections_language_slug_series_slug_idx']
    (language_slug, series_slug, is_published) [name: 'sections_language_slug_series_slug_is_published_idx']
    (language_slug, series_slug, id) [name: 'sections_language_slug_series_slug_id_idx']
    language_slug  [name: 'sections_language_slug_idx']
    series_slug [name: 'sections_series_slug_idx']
    author_id [name: 'sections_author_id_idx']
    is_published [name: 'sections_is_published_idx']
    position [name: 'sections_position_idx']
  }
}
Ref: SP.language_slug > L.slug [delete: cascade, update: cascade]
Ref: SP.series_slug > S.slug [delete: cascade, update: cascade]
Ref: SP.author_id > U.id [delete: cascade, update: cascade]

Table lessons as LES {
  id serial [pk]
  title varchar(250) [not null]
  position smallint [not null]
  is_published boolean [not null, default: false]
  watch_time_seconds int [not null, default: 0]
  read_time_seconds int [not null, default: 0]
  author_id int [not null]
  language_slug varchar(50) [not null]
  series_slug varchar(100) [not null]
  section_id int [not null]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]

  indexes {
    (title, section_id) [unique, name: 'lessons_title_section_id_unique_idx']
    (language_slug, series_slug) [name: 'lessons_language_slug_series_slug_idx']
    (language_slug, series_slug, is_published) [name: 'lessons_language_slug_series_slug_is_published_idx']
    (language_slug, series_slug, id) [name: 'lessons_language_slug_series_slug_id_idx']
    language_slug [name: 'lessons_language_slug_idx']
    series_slug [name: 'lessons_series_slug_idx']
    section_id [name: 'lessons_section_id_idx']
    author_id [name: 'lessons_author_id_idx']
    is_published [name: 'lessons_is_listed_idx']
    position [name: 'lessons_position_idx']
  }
}
Ref: LES.author_id > U.id [delete: cascade, update: cascade]
Ref: LES.language_slug > L.slug [delete: cascade, update: cascade]
Ref: LES.series_slug > S.slug [delete: cascade, update: cascade]
Ref: LES.section_id > SP.id [delete: cascade, update: cascade]

Table lesson_articles as LAR {
  id serial [pk]
  lesson_id int [not null]
  author_id int [not null]
  content text [not null]
  read_time_seconds int [not null, default: 0]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]

  indexes {
    lesson_id [unique, name: 'lesson_articles_lesson_id_unique_idx']
    author_id [name: 'lesson_articles_author_id_idx']
  }
}
Ref: LAR.lesson_id > LES.id [delete: cascade, update: cascade]
Ref: LAR.author_id > U.id [delete: cascade, update: cascade]


Table lesson_videos as LV {
  id serial [pk]
  lesson_id int [not null]
  author_id int [not null]
  url varchar(250) [not null]
  watch_time_seconds int [not null, default: 0]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]

  indexes {
    lesson_id [unique, name: 'lesson_videos_lesson_id_unique_idx']
    author_id [name: 'lesson_videos_author_id_idx']
  }
}
Ref: LV.lesson_id > LES.id [delete: cascade, update: cascade]
Ref: LV.author_id > U.id [delete: cascade, update: cascade]

Table lesson_files as LEF {
  id uuid [pk]
  lesson_id int [not null]
  author_id int [not null]
  ext varchar(10) [not null]
  name varchar(250) [not null]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]

  indexes {
    lesson_id [name: 'lesson_files_lesson_id_idx']
    author_id [name: 'lesson_files_author_id_idx']
    created_at [name: 'lesson_files_created_at_idx']
    (lesson_id, name) [unique, name: 'lesson_files_lesson_id_name_unique_idx']
  }
}
Ref: LEF.lesson_id > LES.id [delete: cascade, update: cascade]

Table language_progress as LPG {
  id serial [pk]
  user_id int [not null]
  language_slug varchar(50) [not null]
  completed_series smallint [not null, default: 0]
  viewed_at timestamp [not null, default: `now()`]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]

  indexes {
    (user_id, language_slug) [unique, name: 'language_progress_user_id_language_slug_unique_idx']
    user_id [name: 'language_progress_user_id_idx']
    language_slug [name: 'language_progress_language_slug_idx']
    viewed_at [name: 'language_progress_viewed_at_idx']
  }
}
Ref: LPG.user_id > U.id [delete: cascade, update: cascade]
Ref: LPG.language_slug > L.slug [delete: cascade, update: cascade]

Table series_progress as SPR  {
  id serial [pk]
  user_id int [not null]
  series_slug varchar(100) [not null]
  language_slug varchar(50) [not null]
  language_progress_id int [not null]
  completed_sections smallint [not null, default: 0]
  completed_lessons smallint [not null, default: 0]
  parts_count smallint [not null, default: 0]
  completed_at timestamp [null]
  viewed_at timestamp [not null, default: `now()`]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]

  indexes {
    (user_id, series_slug, language_slug) [unique, name: 'series_progress_user_id_series_slug_language_slug_unique_idx']
    user_id [name: 'series_progress_user_id_idx']
    series_slug [name: 'series_progress_series_slug_idx']
    language_slug [name: 'series_progress_language_slug_idx']
    language_progress_id [name: 'series_progress_language_progress_id_idx']
    viewed_at [name: 'series_progress_viewed_at_idx']
  }
}
Ref: SPR.user_id > U.id [delete: cascade, update: cascade]
Ref: SPR.series_slug > S.slug [delete: cascade, update: cascade]
Ref: SPR.language_slug > L.slug [delete: cascade, update: cascade]
Ref: SPR.language_progress_id > LPG.id [delete: cascade, update: cascade]

Table section_progress as SPP {
  id serial [pk]
  user_id int [not null]
  language_slug varchar(50) [not null]
  series_slug varchar(100) [not null]
  section_id int [not null]
  language_progress_id int [not null]
  series_progress_id int [not null]
  completed_lessons smallint [not null, default: 0]
  completed_at timestamp [null]
  viewed_at timestamp [not null, default: `now()`]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]

  indexes {
    (user_id, language_slug, series_slug, section_id) [unique, name: 'section_progress_user_id_lang_ser_sec_unique_idx']
    user_id [name: 'section_progress_user_id_idx']
    language_slug [name: 'section_progress_language_slug_idx']
    series_slug [name: 'section_progress_series_slug_idx']
    section_id [name: 'section_progress_section_id_idx']
    language_progress_id [name: 'section_progress_language_progress_id_idx']
    series_progress_id [name: 'section_progress_series_progress_id_idx']
    viewed_at [name: 'section_progress_viewed_at_idx']
  }
}
Ref: SPP.user_id > U.id [delete: cascade, update: cascade]
Ref: SPP.language_slug > L.slug [delete: cascade, update: cascade]
Ref: SPP.series_slug > S.slug [delete: cascade, update: cascade]
Ref: SPP.section_id > SP.id [delete: cascade, update: cascade]
Ref: SPP.language_progress_id > LPG.id [delete: cascade, update: cascade]
Ref: SPP.series_progress_id > SPR.id [delete: cascade, update: cascade]

Table lesson_progress as LP {
  id serial [pk]
  user_id int [not null]
  language_slug varchar(50) [not null]
  series_slug varchar(100) [not null]
  section_id int [not null]
  lesson_id int [not null]
  language_progress_id int [not null]
  series_progress_id int [not null]
  section_progress_id int [not null]
  completed_at timestamp [null]
  viewed_at timestamp [not null, default: `now()`]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]

  indexes {
    (user_id, language_slug, series_slug, section_id, lesson_id) [unique, name: 'lesson_progress_user_id_lang_ser_sec_les_unique_idx']
    user_id [name: 'lesson_progress_user_id_idx']
    language_slug [name: 'lesson_progress_language_slug_idx']
    series_slug [name: 'lesson_progress_series_slug_idx']
    section_id [name: 'lesson_progress_section_id_idx']
    lesson_id [name: 'lesson_progress_lesson_id_idx']
    language_progress_id [name: 'lesson_progress_language_progress_id_idx']
    series_progress_id [name: 'lesson_progress_series_progress_id_idx']
    section_progress_id [name: 'lesson_progress_section_progress_id_idx']
    viewed_at [name: 'lesson_progress_viewed_at_idx']
  }
}
Ref: LP.user_id > U.id [delete: cascade, update: cascade]
Ref: LP.language_slug > L.slug [delete: cascade, update: cascade]
Ref: LP.series_slug > S.slug [delete: cascade, update: cascade]
Ref: LP.section_id > SP.id [delete: cascade, update: cascade]
Ref: LP.lesson_id > LES.id [delete: cascade, update: cascade]
Ref: LP.language_progress_id > LPG.id [delete: cascade, update: cascade]
Ref: LP.series_progress_id > SPR.id [delete: cascade, update: cascade]
Ref: LP.section_progress_id > SPP.id [delete: cascade, update: cascade]

Table certificates as CERT {
  id uuid [pk]
  user_id int [not null]
  series_title varchar(100) [not null]
  lessons smallint [not null]
  watch_time_seconds int [not null]
  read_time_seconds int [not null]
  language_slug varchar(50) [not null]
  series_slug varchar(100) [not null]
  completed_at timestamp [not null]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]

  indexes {
    (user_id, series_slug) [unique, name: 'certificates_user_id_series_slug_unique_idx']
    user_id [name: 'certificates_user_id_idx']
    language_slug [name: 'certificates_language_slug_idx']
    series_slug [name: 'certificates_series_slug_idx']
  }
}
Ref: CERT.user_id > U.id [delete: cascade, update: cascade]
Ref: CERT.language_slug > L.slug [delete: cascade, update: cascade]
Ref: CERT.series_slug > S.slug [delete: cascade, update: cascade]