software

Tables (18)

audit_logs

  • id — BIGSERIAL
  • table_name — VARCHAR(50) — NOT NULL
  • record_id — BIGINT — NOT NULL
  • field_name — VARCHAR(100) — NOT NULL
  • old_value — TEXT
  • new_value — TEXT
  • changed_by — BIGINT — Foreign key → users.id

classes

  • id — BIGSERIAL
  • academic_year — INTEGER — NOT NULL
  • grade — INTEGER — NOT NULL
  • class_num — INTEGER — NOT NULL
  • homeroom_teacher_id — BIGINT — Foreign key → teachers.id

counselings

  • id — BIGSERIAL
  • student_id — BIGINT — Foreign key → students.id — NOT NULL
  • teacher_id — BIGINT — Foreign key → teachers.id — NOT NULL
  • category — VARCHAR(30) — NOT NULL
  • content — TEXT — NOT NULL
  • version — BIGINT

feedbacks

  • id — BIGSERIAL
  • student_id — BIGINT — Foreign key → students.id — NOT NULL
  • teacher_id — BIGINT — Foreign key → teachers.id — NOT NULL
  • category — VARCHAR(30) — NOT NULL
  • content — TEXT — NOT NULL
  • version — BIGINT

invite_tokens

  • id — BIGSERIAL
  • phone_hash — VARCHAR(64) — NOT NULL
  • otp_code — VARCHAR(6) — NOT NULL
  • purpose — VARCHAR(20) — NOT NULL
  • expires_at — TIMESTAMPTZ — NOT NULL

notifications

  • id — BIGSERIAL
  • recipient_id — BIGINT — Foreign key → users.id — NOT NULL
  • type — VARCHAR(30) — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • message — TEXT — NOT NULL
  • is_read — BOOLEAN

parent_student

  • parent_id — BIGINT — Foreign key → parents.id — NOT NULL
  • student_id — BIGINT — Foreign key → students.id — NOT NULL
  • relationship — VARCHAR(20) — NOT NULL

parents

  • id — BIGSERIAL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL

refresh_tokens

  • id — BIGSERIAL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • token_hash — VARCHAR(64) — NOT NULL
  • expires_at — TIMESTAMPTZ — NOT NULL

scores

  • id — BIGSERIAL
  • student_id — BIGINT — Foreign key → students.id — NOT NULL
  • subject_id — BIGINT — Foreign key → subjects.id — NOT NULL
  • teacher_id — BIGINT — Foreign key → teachers.id — NOT NULL
  • year — INTEGER — NOT NULL
  • semester — INTEGER — NOT NULL
  • score — DECIMAL(5,2) — NOT NULL
  • version — BIGINT

student_enrollments

  • id — BIGSERIAL
  • student_id — BIGINT — Foreign key → students.id — NOT NULL
  • class_id — BIGINT — Foreign key → classes.id — NOT NULL
  • student_num — INTEGER — NOT NULL
  • academic_year — INTEGER — NOT NULL

student_records

  • id — BIGSERIAL
  • student_id — BIGINT — Foreign key → students.id — NOT NULL
  • subject_id — BIGINT — Foreign key → subjects.id
  • academic_year — INTEGER — NOT NULL
  • semester — INTEGER — NOT NULL
  • record_type — VARCHAR(10) — NOT NULL
  • content — JSONB — NOT NULL
  • version — BIGINT

students

  • id — BIGSERIAL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • admission_year — INTEGER — NOT NULL

subjects

  • id — BIGSERIAL
  • name — VARCHAR(100) — NOT NULL
  • code — VARCHAR(20) — NOT NULL

teacher_assignments

  • id — BIGSERIAL
  • teacher_id — BIGINT — Foreign key → teachers.id — NOT NULL
  • class_id — BIGINT — Foreign key → classes.id — NOT NULL
  • subject_id — BIGINT — Foreign key → subjects.id — NOT NULL
  • academic_year — INTEGER — NOT NULL

teachers

  • id — BIGSERIAL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • department — VARCHAR(50)

token_blacklist

  • id — BIGSERIAL
  • token_hash — VARCHAR(64) — NOT NULL
  • expires_at — TIMESTAMPTZ — NOT NULL

users

  • id — BIGSERIAL
  • email — TEXT — NOT NULL
  • email_hash — VARCHAR(64) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • phone — TEXT — NOT NULL
  • phone_hash — VARCHAR(64) — NOT NULL
  • role — VARCHAR(20) — NOT NULL
  • is_active — BOOLEAN
  • failed_login_count — INTEGER
  • login_locked_until — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL