AI-LIT

Tables (39)

admin_groups

  • admin_group_id — SERIAL — Primary key
  • institution_id — SERIAL — Foreign key → institutions.institution_id — NOT NULL
  • name — VARCHAR(100)
  • permissions — JSON — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME
  • deleted_at — DATETIME

admins

  • admin_id — SERIAL — Primary key
  • type — ENUM(super, kindergarten, normal)
  • institution_id — SERIAL — Foreign key → institutions.institution_id — NOT NULL
  • name — VARCHAR(100)
  • login_id — VARCHAR(10)
  • password — VARCHAR(255)
  • admin_group_id — SERIAL — Foreign key → admin_groups.admin_group_id
  • phone — VARCHAR(20)
  • is_actice — BOOLEAN
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

ai_outputs

  • ai_output_id — SERIAL — Primary key — NOT NULL
  • student_id — SERIAL — Foreign key → students.student_id — NOT NULL
  • output — TEXT
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

auth_histories

  • auth_history_id — BIGSERIAL — Primary key — NOT NULL
  • user_id — INT
  • role — ENUM(admin, user)
  • permissions — JSON
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

classes

  • class_id — SERIAL — Primary key
  • institution_id — SERIAL — Foreign key → institutions.institution_id — NOT NULL
  • year — CHAR(4)
  • class_name — VARCHAR(100) — NOT NULL
  • student_count — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME
  • start_date — DATE
  • end_date — DATE

contents

  • content_id — SERIAL — Primary key — NOT NULL
  • institution_id — SERIAL — Foreign key → institutions.institution_id — NOT NULL
  • title — VARCHAR(100)
  • content — JSON
  • content_type — VARCHAR(255)
  • content_name — VARCHAR(100)
  • content_size — VARCHAR(100)
  • week — INT
  • period — INT
  • manual_content_id — VARCHAR(30)
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

institutions

  • institution_id — SERIAL — Primary key — NOT NULL
  • primary_admin_id — SERIAL — Foreign key → admins.admin_id
  • name — VARCHAR(100)
  • address — VARCHAR(500)
  • student_count — INT
  • is_active — BOOLEAN
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

lesson_attendances

  • lesson_attendance_id — SERIAL — Primary key — NOT NULL
  • lesson_id — SERIAL — Foreign key → lessons.lesson_id — NOT NULL
  • student_id — SERIAL — Foreign key → students.student_id — NOT NULL
  • status — ENUM(late, attendance, absence)
  • memo — VARCHAR(100)
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

lessons

  • lesson_id — SERIAL — Primary key — NOT NULL
  • content_id — SERIAL — Foreign key → contents.content_id — NOT NULL
  • class_id — SERIAL — Foreign key → classes.class_id
  • teacher_id — SERIAL — Foreign key → teachers.teacher_id — NOT NULL
  • lesson_time — INT
  • lessoned_at — DATETIME
  • name — VARCHAR(255)
  • content_type — VARCHAR(255)
  • teacher_memo — TEXT
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

logs

  • log_id — BIGSERIAL — Primary key — NOT NULL
  • table_type — ENUM(institution, user, admin)
  • table_id — INT
  • content — VARCHAR(500)
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

notice_comments

  • notice_comment_id — SERIAL — Primary key — NOT NULL
  • notice_id — SERIAL — Foreign key → notices.notice_id — NOT NULL
  • commenter_id — SERIAL — Foreign key → users.user_id — NOT NULL
  • content — TEXT
  • is_show — BOOLEAN
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

notice_files

  • notice_file_id — SERIAL — Primary key — NOT NULL
  • notice_id — SERIAL — Foreign key → notices.notice_id — NOT NULL
  • url — VARCHAR(500)
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

notice_view_logs

  • notice_view_log_id — BIGSERIAL — Primary key — NOT NULL
  • notice_id — SERIAL — Foreign key → notices.notice_id — NOT NULL
  • viewer_id — SERIAL — Foreign key → users.user_id — NOT NULL
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

notices

  • notice_id — SERIAL — Primary key — NOT NULL
  • admin_id — SERIAL — Foreign key → admins.admin_id
  • target — ENUM(all, class)
  • target_class_id — SERIAL — Foreign key → classes.class_id
  • view_count — INT
  • comment_count — INT
  • title — VARCHAR(400)
  • content — TEXT
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

parent_contacts

  • parent_contact_id — SERIAL — Primary key — NOT NULL
  • parent_id — SERIAL — Foreign key → parents.parent_id — NOT NULL
  • relationship — ENUM(부, 모, 조부모, 기타)
  • relationship_detail — VARCHAR(100)
  • name — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(20)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME
  • deleted_at — DATETIME

parent_post_comments

  • parent_post_comment_id — SERIAL — Primary key — NOT NULL
  • parent_post_id — SERIAL — Foreign key → parent_posts.parent_post_id — NOT NULL
  • author_id — SERIAL — Foreign key → users.user_id — NOT NULL
  • content — TEXT
  • is_show — BOOLEAN
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

parent_post_files

  • notice_file_id — SERIAL — Primary key — NOT NULL
  • parent_post_id — SERIAL — Foreign key → parent_posts.parent_post_id — NOT NULL
  • url — VARCHAR(500)
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

parent_posts

  • parent_post_id — SERIAL — Primary key — NOT NULL
  • author_id — SERIAL — Foreign key → users.user_id — NOT NULL
  • view_count — INT
  • comment_count — INT
  • title — VARCHAR(255)
  • content — TEXT
  • is_show — BOOLEAN
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

parents

  • parent_id — SERIAL — Foreign key → users.user_id — NOT NULL
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

push_notifications

  • push_notification_id — SERIAL — Primary key — NOT NULL
  • institution_id — SERIAL — Foreign key → institutions.institution_id — NOT NULL
  • push_condition — ENUM(now,reserved)
  • reserved_at — DATETIME
  • target — ENUM(all, group)
  • target_user_group_id — SERIAL — Foreign key → user_groups.user_group_id — NOT NULL
  • target_count — INT
  • title — VARCHAR(100)
  • content — TEXT
  • image_url — VARCHAR(500)
  • url — VARCHAR(500)
  • requested_at — DATETIME
  • pushed_at — DATETIME
  • status — ENUM(wait,done,canceled)
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

qna_answers

  • qna_answer_id — SERIAL — Primary key — NOT NULL
  • qna_id — SERIAL — Foreign key → qnas.qna_id — NOT NULL
  • student_id — SERIAL — Foreign key → students.student_id — NOT NULL
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

qna_question_answers

  • qna_question_answer_id — SERIAL — Primary key — NOT NULL
  • qna_question_id — SERIAL — Foreign key → qna_questions.qna_question_id — NOT NULL
  • qna_answer_id — SERIAL — Foreign key → qna_answers.qna_answer_id — NOT NULL
  • answer — VARCHAR(500)
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

qna_questions

  • qna_question_id — SERIAL — Primary key — NOT NULL
  • qna_id — SERIAL — Foreign key → qnas.qna_id — NOT NULL
  • question — VARCHAR(500)
  • type — ENUM(checkbox, radio, text)
  • image_url — VARCHAR(500)
  • is_required — BOOLEAN
  • values — JSON
  • sorting — INT
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

qnas

  • qna_id — SERIAL — Primary key — NOT NULL
  • admin_id — SERIAL — Foreign key → admins.admin_id
  • title — VARCHAR(255)
  • description — VARCHAR(500
  • status — ENUM(BEFORE,ONGOING,DONE)
  • created_at — DATETIME
  • updatd_at (updated_at) — DATETIME
  • deleted_at — DATETIME

report_files

  • report_file_id — SERIAL — Primary key — NOT NULL
  • report_id — SERIAL — Foreign key → reports.report_id — NOT NULL
  • type — ENUM(collage, face_image, voice)
  • url — VARCHAR(500)
  • sorting — INT
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

reports

  • report_id — SERIAL — Primary key — NOT NULL
  • student_id — SERIAL — Foreign key → students.student_id — NOT NULL
  • content_id — SERIAL — Foreign key → contents.content_id — NOT NULL
  • lesson_id — SERIAL — Foreign key → lessons.lesson_id — NOT NULL
  • content_type — VARCHAR(255)
  • learned_at — DATETIME
  • week — INT
  • period — INT
  • activity_time — INT
  • activity_goal — TEXT
  • achievement_rate — INT
  • activity_summary — TEXT
  • compliment_reward — TEXT
  • activity_result — TEXT
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

statistics

  • statistic_id — BIGSERIAL — Primary key — NOT NULL
  • type — ENUM(student, parent, class, teacher, content_total_activity, content_type_activity)
  • content_type_name — VARCHAR(255)
  • count — INT
  • create_date — DATE
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

student_classes

  • student_class_id — SERIAL — Primary key — NOT NULL
  • class_id — SERIAL — Foreign key → classes.class_id
  • student_id — SERIAL — Foreign key → students.student_id — NOT NULL
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

students

  • student_id — SERIAL — Foreign key → users.user_id — NOT NULL
  • parent_id — SERIAL — Foreign key → parents.parent_id — NOT NULL
  • image_url — VARCHAR(500)
  • birth — DATE
  • address — VARCHAR(500)
  • phone — VARCHAR(20)
  • gender — ENUM
  • enrollment_date — DATE
  • graduation_date — DATETIME
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

survey_answers

  • survey_answer_id — SERIAL — Primary key — NOT NULL
  • survey_id (survery_id) — SERIAL — Foreign key → surveys.survey_id — NOT NULL
  • answerer_id — SERIAL — Foreign key → users.user_id — NOT NULL
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

survey_question_answers

  • survey_question_answer_id — SERIAL — Primary key — NOT NULL
  • survey_question_id — SERIAL — Foreign key → survey_questions.survey_question_id — NOT NULL
  • survey_answer_id — SERIAL — Foreign key → survey_answers.survey_answer_id — NOT NULL
  • answer — VARCHAR(500)
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

survey_questions

  • survey_question_id — SERIAL — Primary key — NOT NULL
  • survey_id (survery_id) — SERIAL — Foreign key → surveys.survey_id — NOT NULL
  • question — VARCHAR(500)
  • type — ENUM(checkbox, radio, text)
  • image_url — VARCHAR(500)
  • is_required — BOOLEAN
  • values — JSON
  • sorting — INT
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

surveys

  • survey_id (survery_id) — SERIAL — Primary key — NOT NULL
  • admin_id — SERIAL — Foreign key → admins.admin_id
  • title — VARCHAR(255)
  • description — VARCHAR(500)
  • target — ENUM(parent,teacher)
  • start_date — DATE
  • end_date — DATE
  • status — ENUM(BEFORE,ONGOING,DONE)
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

teacher_classes

  • teacher_class_id — SERIAL — Primary key — NOT NULL
  • class_id — SERIAL — Foreign key → classes.class_id
  • teacher_id — SERIAL — Foreign key → teachers.teacher_id — NOT NULL
  • is_primary — BOOLEAN — NOT NULL
  • assigned_at — DATETIME — NOT NULL
  • ended_at — DATETIME
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

teachers

  • teacher_id — SERIAL — Foreign key → users.user_id — NOT NULL
  • image_url — VARCHAR(500)
  • birth — DATE
  • address — VARCHAR(500)
  • phone — VARCHAR(20)
  • gender — ENUM
  • hired_date — DATE — NOT NULL
  • terminate_date — DATE
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

user_groups

  • user_group_id — SERIAL — Primary key — NOT NULL
  • institution_id — SERIAL — Foreign key → institutions.institution_id — NOT NULL
  • name — VARCHAR(255)
  • description — VARCHAR(500)
  • user_ids — JSON
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

users

  • user_id — SERIAL — Primary key — NOT NULL
  • institution_id — SERIAL — Foreign key → institutions.institution_id — NOT NULL
  • login_id — VARCHAR(36)
  • password — VARCHAR(255)
  • type — ENUM(teacher,student,parent)
  • name — VARCHAR(100)
  • is_active — BOOLEAN
  • last_login_at — DATETIME
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

vote_answers

  • vote_answer_id — SERIAL — Primary key — NOT NULL
  • vote_id — SERIAL — Foreign key → votes.vote_id — NOT NULL
  • answer — VARCHAR(500)
  • answerer_id — SERIAL — Foreign key → users.user_id — NOT NULL
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME

votes

  • vote_id — SERIAL — Primary key — NOT NULL
  • admin_id — SERIAL — Foreign key → admins.admin_id
  • title — VARCHAR(255)
  • description — VARCHAR(500)
  • question — VARCHAR(500)
  • type — ENUM(checkbox,radio)
  • image_url — VARCHAR(500)
  • values — JSON
  • status — ENUM(BEFORE,ONGOING,DONE)
  • progress_date — DATE
  • created_at — DATETIME
  • updated_at — DATETIME
  • deleted_at — DATETIME