schema.dbml

////People Depot Schema Entity Relationship Diagram
// Use DBML to define your database structure
// Docs: https://dbml.dbdiagram.io/docs

TablePartial AbstractBaseModel {
  id uuid [pk, unique, not null]
  created_at DateTimeField [not null, default: `now()`]
  updated_at DateTimeField [not null, default: `now()`]
}

Table Accomplishment {
  ~AbstractBaseModel
  project uuid [ref: > Project.id]
  title varchar
  description varchar
  url URLField
  accomplished_on DateField
}

Table Affiliate {
  //created
  //formerly sponsor_partner
  ~AbstractBaseModel
  partner_name varchar
  partner_logo URLField
  is_active boolean
  url URLField
  is_org_sponsor boolean
  is_org_partner boolean
  //considering adding org_id
}

Table CancelledEvent {
  ~AbstractBaseModel
  event int [ref: > Event.id]
  start_time DateTimeField
  cancelled_on DateField
  reason varchar
  cancelled_by int [ref: > User.id]
}

Table CheckIn {
  ~AbstractBaseModel
  occurance int [ref: > EventOccurrence.id]
  user int [ref: > User.id]
  checked_in DateTimeField
}

Table CheckType [note: 'Data tab: https://docs.google.com/spreadsheets/d/1x_zZ8JLS2hO-zG0jUocOJmX16jh-DF5dccrd_OEGNZ0/edit?gid=1784987068#gid=1784987068'] {
  //created
  ~AbstractBaseModel
  name varchar
  description varchar
}

Table Event {
  //created
  ~AbstractBaseModel
  name varchar
  description varchar
  event_type int [ref: > EventType.id]
  start_time DateTimeField
  duration_in_min int
  rrule RecurrenceField [note: 'recurrence rule, used by django-recurrence']
  project int [ref: > Project.id]
  video_conference_url URLField
  location int [ref: > Location.id]
  org int [ref: > Org.id]
  additional_info varchar
  must_attend JSON
  should_attend JSON
  could_attend JSON
  last_generated DateTimeField
  previous_version uuid [ref: > Event.id]
}

Table EventOccurrence {
  ~AbstractBaseModel
  event int [ref: > Event.id]
  start_time DateTimeField
  previous_version uuid [ref: > Event.id]
  changes uuid [ref: > EventOccurrenceChange.id]
}

Table EventOccurrenceChange {
  ~AbstractBaseModel
  event int [ref: > Event.id]
  start_time DateTimeField
  name_change varchar
  description_change varchar
  event_type_change int [ref: > EventType.id]
  start_time_change DateTimeField
  duration_in_min_change int
  video_conference_url_change URLField
  location_change int [ref: > Location.id]
  additional_info_change varchar
  must_attend_change JSON
  should_attend_change JSON
  could_attend_change JSON
}

Table EventType {
  //created
  ~AbstractBaseModel
  name varchar
  description varchar
}

Table Faq {
  //created
  ~AbstractBaseModel
  question varchar
  answer varchar
  project int [ref: > Project.id]
  permission_type int [ref: > PermissionType.id]
  title varchar
}

Table FaqViewed {
  //created
  ~AbstractBaseModel
  faq int [ref: > Faq.id]
  read DateTimeField
}

Table Language {
  ~AbstractBaseModel
  name varchar
  description varchar
  logo URLField
}

Table LeadershipType {
  //created
  ~AbstractBaseModel
  name varchar
  description varchar
}

Table Location {
  //created
  ~AbstractBaseModel
  name varchar
  address_line_1 varchar
  address_line_2 varchar
  city varchar
  state varchar
  zip varchar
  phone varchar
}

Table ModernJobTitle {
  ~AbstractBaseModel
  soc_detailed int [ref: > SOCDetailed.id]
  title varchar
}

Table Org {
  ~AbstractBaseModel
  name varchar
  time_zone varchar
}

Table UserPermission {
  //created
  ~AbstractBaseModel
  user int [ref: > User.id]
  project int [ref: > Project.id]
  permission_type int [ref: > PermissionType.id]
  practice_area int [ref: > PracticeArea.id]
  granted DateTimeField
  ended DateTimeField
  created_by int [ref: > User.id]
  updated_by int [ref: > User.id]
}

Table PermissionHistory {
  ~AbstractBaseModel
  user int [ref: > User.id]
  project int [ref: > Project.id]
  permission_type int [ref: > PermissionType.id]
  practice_area int [ref: > PracticeArea.id]
  granted DateTimeField
  ended DateTimeField
  created_by int
  updated_by int
}

Table PermissionType {
  //created
  ~AbstractBaseModel
  name varchar
  description varchar
}

Table PracticeArea {
  //created
  ~AbstractBaseModel
  name varchar
  description varchar
  image URLField
}

Table ProgramArea {
  //created
  ~AbstractBaseModel
  name varchar
  description varchar
  image URLField
}

Table Project {
  //created
  ~AbstractBaseModel
  name String
  description String
  completed_on date
  github_org int [ref: - ProjectUrl.id, note: 'The unique id assigned to GitHub orgs. A foreign key to a row in ProjectUrl that corresponds to the org repo link']
  github_primary_repo int [ref: - ProjectUrl.id, note: 'A project might have many repos, this is for the primary starting place for new users. A foreign key to a row in ProjectUrl that corresponds to the primary repo']
  project_status_type int [ref: > ProjectProgramAreaStatusType.id]
  hide Boolean
  location int [ref: > Location.id]
  google_drive int [ref: > ProjectUrl.id, note: 'The unique id of the google drive, used for adding people with automation. A foreign key to a row in ProjectUrl that corresponds to the google drive.']
  project_leads int[] (user_id)
  leadership_type int [ref: > LeadershipType.id]
  image_logo URLField
  image_hero URLField
  image_icon URLField
}

Table ProjectAffiliateXref {
  //created
  ~AbstractBaseModel
  affiliate int [ref: > Affiliate.id]
  project int [ref: > Project.id]
  ended_on DateField
  is_sponsor boolean
  is_partner boolean
}

Table ProjectLanguageXref {
  ~AbstractBaseModel
  project int [ref: > Project.id]
  language int [ref: > Language.id]
  started_on DateTimeField
  ended_on DateTimeField
}

Table ProjectProgramAreaXref {
  //created
  ~AbstractBaseModel
  project int [ref: > Project.id]
  program_area int [ref: > ProgramArea.id]
}

Table ProjectSDGXref {
  //created
  ~AbstractBaseModel
  sdg int [ref: > SDG.id]
  project int [ref: > Project.id]
  ended_on DateField
}

Table ProjectStackElementXref {
  ~AbstractBaseModel
  project int [ref: > Project.id]
  stack_element int [ref: > StackElement.id]
  removed_on DateField
}

Table ProjectProgramAreaStatusType [note: 'currently named projectstatus in code'] {
  //created
  ~AbstractBaseModel
  name varchar
  description varchar
}

Table ProjectProgramAreaStatusHistory [note: 'currently named project_program_area_status in open issue'] {
  ~AbstractBaseModel
  project int [ref: > Project.id]
  status int [ref: > ProjectProgramAreaStatusType.id]
}

Table ProjectUrl {
  ~AbstractBaseModel
  project int [ref: > Project.id]
  url_type int [ref: > UrlType.id]
  name CharField
  external_id CharField [note: 'resource id provided by GitHub or google']
  url URLField
  url_status_type int [ref: > UrlStatusType.id]
}

Table Referrer {
  //created
  ~AbstractBaseModel
  name varchar
  url URLField
  referrer_type int [ref: > ReferrerType.id]
  contact_name varchar
  contact_email EmailField
}

Table ReferrerType {
  //created
  ~AbstractBaseModel
  name varchar
  description varchar
}

Table SDG {
  //created
  ~AbstractBaseModel
  name varchar
  description varchar
  image URLField
}

Table SDGTargetIndicator {
  ~AbstractBaseModel
  sdg int [ref: > SDG.id]
  code varchar
  description_number varchar
  description_text varchar
}

Table Skill {
  //created
  ~AbstractBaseModel
  name varchar
  description varchar
}

Table SOCBroad {
  ~AbstractBaseModel
  soc_minor int [ref: > SOCMinor.id]
  occ_code varchar
  title varchar
}

Table SOCDetailed {
  ~AbstractBaseModel
  soc_broad int [ref: > SOCBroad.id]
  occ_code varchar
  title varchar
  description varchar
}


Table SOCMajor {
  //created
  ~AbstractBaseModel
  occ_code varchar
  title varchar
}

Table SOCMinor {
  ~AbstractBaseModel
  soc_major int [ref: > SOCMajor.id]
  occ_code varchar
  title varchar
}

Table StackElement {
  //created
  ~AbstractBaseModel
  name varchar
  description varchar
  url URLField
  logo URLField
  active boolean
  stack_element_type int [ref: > StackElementType.id]
}

Table StackElementType {
  //created
  ~AbstractBaseModel
  name varchar
  description varchar
}

Table UrlStatusType {
  ~AbstractBaseModel
  name varchar
  description varchar
}

Table UrlType {
  //created
  ~AbstractBaseModel
  name varchar
  description varchar
}

Table User {
  //created
  ~AbstractBaseModel
  first_name varchar
  last_name varchar
  email EmailField [note:'The email given during intake']
  email_gmail EmailField
  email_preferred EmailField
  is_active bool [default: True, note:'Required by django. A boolean field that indicates whether the user\'s account is active.']
  user_status_type int [ref: > UserStatusType.id]
  practice_area_primary int [ref: > PracticeArea.id]
  practice_area_secondary int[] [ref: <> PracticeArea.id]
  job_title_current varchar
  practice_area_target int[] [ref: <> PracticeArea.id]
  job_title_target varchar
  intake_present_job_title	varchar
  intake_present_skills	varchar
  intake_target_job_titles	varchar
  intake_target_skills	int[ ]
  current_job_title	varchar
  current_skills	int[ ]
  current_target_job_titles	varchar
  current_target_skills	varchar
  linkedin_account URLField
  referred_by int [ref: > Referrer.id]
  github_handle varchar
  phone varchar
  texting_ok boolean
  slack_id varchar(11)
  time_zone varchar [note: 'full time zone name']
}

Table UserAvailability {
  ~AbstractBaseModel
  user int [ref: > User.id, not null]
  day_of_week day (enum)
  start TimeField
  duration_minutes int
}

Table UserCheck {
  ~AbstractBaseModel
  user int [ref: > User.id]
  org int [ref: > Org.id]
  check_type int [ref: > CheckType.id]
  result boolean
  reminder_start DateTimeField
  completed_at DateTimeField
  project int [ref: > Project.id]
}

Table UserEmploymentHistory {
  ~AbstractBaseModel
  user int [ref: > User.id]
  soc_detailed int [ref: > SOCDetailed.id]
  modern_job_title int [ref: > ModernJobTitle.id]
}

Table UserStatusType {
  //created
  ~AbstractBaseModel
  name varchar
  description varchar
}

Table Win {
  ~AbstractBaseModel
  user int [ref: > User.id]
  practice_areas int[] [ref: <> PracticeArea.id]
  teams int[] [ref: <> Project.id]
  description varchar
  win_type int [ref: > WinType.id]
  can_use_photo boolean
}

Table WinType {
  ~AbstractBaseModel
  name varchar
  display_text varchar
}