.

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
  • changed_at — TIMESTAMPTZ — NOT NULL

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
  • created_at — TIMESTAMPTZ — NOT NULL

counselings

  • id — BIGSERIAL
  • student_id — BIGINT — Foreign key → students.id — NOT NULL
  • teacher_id — BIGINT — Foreign key → teachers.id — NOT NULL
  • counsel_date — DATE — NOT NULL
  • category — VARCHAR(30) — NOT NULL
  • content — TEXT — NOT NULL
  • next_plan — TEXT
  • next_counsel_date — DATE
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • version — BIGINT — NOT NULL

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
  • is_visible_to_student — BOOLEAN — NOT NULL
  • is_visible_to_parent — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • version — BIGINT — NOT NULL

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
  • used_at — TIMESTAMPTZ
  • attempt_count — INTEGER — NOT NULL
  • created_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
  • reference_type — VARCHAR(30)
  • reference_id — BIGINT
  • is_read — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

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
  • created_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
  • grade_letter — VARCHAR(5)
  • rank — INTEGER
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • created_by — BIGINT — Foreign key → users.id
  • updated_by — BIGINT — Foreign key → users.id
  • version — BIGINT — NOT NULL

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
  • created_at — TIMESTAMPTZ — 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
  • category — VARCHAR(50) — NOT NULL
  • content — JSONB — NOT NULL
  • is_visible_to_student — BOOLEAN — NOT NULL
  • is_visible_to_parent — BOOLEAN — NOT NULL
  • review_status — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • created_by — BIGINT — Foreign key → users.id
  • updated_by — BIGINT — Foreign key → users.id
  • version — BIGINT — NOT NULL

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
  • description — TEXT
  • created_at — TIMESTAMPTZ — 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
  • created_at — TIMESTAMPTZ — 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 — NOT NULL
  • failed_login_count — INTEGER — NOT NULL
  • login_locked_until — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • deleted_at — TIMESTAMPTZ