# Hosting Database Audit

Dokumen ini merangkum kebutuhan database yang benar-benar dipakai oleh kode saat ini, agar proses hosting tidak hanya mengandalkan `setup-database.php` yang sudah lama dan belum mencakup seluruh fitur.

## Core tables

Tabel inti yang dipakai hampir di seluruh alur:

- `users`
- `classes`
- `class_enrollments`
- `projects`
- `materials`
- `project_phases`
- `project_groups`
- `project_group_members`
- `phase_submissions`
- `submission_files`
- `assessments`

## Assessment and rubric tables

Tabel tambahan yang dipakai fitur penilaian:

- `rubrics`
- `rubric_levels`
- `rubric_assessments`
- `project_rubric_configs`
- `individual_reflections`
- `individual_assessments`

## Phase question tables

Tabel tambahan untuk pertanyaan per fase:

- `phase_questions`
- `phase_question_answers`
- `question_assessments`

## Minimum columns that must exist

### `users`

- `id`
- `username`
- `password`
- `user_type`
- `email`
- `full_name`
- `created_at`
- `updated_at`

### `classes`

- `id`
- `name`
- `code`
- `description`
- `teacher_id`
- `semester`
- `year`
- `created_at`
- `updated_at`

### `class_enrollments`

- `id`
- `class_id`
- `student_id`
- `status`
- `enrolled_at`

### `projects`

- `id`
- `class_id`
- `title`
- `description`
- `sdg_focus`
- `sdgs_goals`
- `learning_objectives`
- `deadline`
- `due_date`
- `end_date`
- `status`
- `created_by`
- `created_at`
- `updated_at`

### `materials`

- `id`
- `class_id`
- `title`
- `description`
- `content`
- `file_path`
- `status`
- `created_by`
- `created_at`
- `updated_at`

### `project_phases`

- `id`
- `project_id`
- `phase_number`
- `phase_name`
- `phase_title`
- `description`
- `status`
- `created_at`

### `project_groups`

- `id`
- `project_id`
- `name`
- `leader_id`
- `status`
- `created_at`
- `updated_at`

### `project_group_members`

- `id`
- `group_id`
- `student_id`
- `role`
- `joined_at`

### `phase_submissions`

Schema ini adalah salah satu area paling sensitif karena dipakai dengan dua gaya akses di beberapa endpoint. Agar kompatibel, tabel ini sebaiknya memiliki:

- `id`
- `project_id`
- `phase_id`
- `group_id`
- `student_id`
- `submitted_by`
- `class_id`
- `status`
- `notes`
- `grade`
- `graded_by`
- `graded_at`
- `submission_date`
- `submitted_at`
- `created_at`
- `updated_at`

### `submission_files`

- `id`
- `submission_id`
- `filename`
- `file_name`
- `filepath`
- `file_path`
- `file_type`
- `file_size`
- `uploaded_at`

### `assessments`

- `id`
- `project_id`
- `group_id`
- `phase_id`
- `score`
- `feedback`
- `graded_by`
- `graded_at`

### `rubrics`

- `id`
- `title`
- `description`
- `project_id`
- `weight`
- `max_score`
- `phase_number`
- `created_at`
- `updated_at`

### `rubric_levels`

- `id`
- `rubric_id`
- `level_number`
- `level_description`
- `min_score`
- `max_score`
- `created_at`

### `rubric_assessments`

- `id`
- `project_id`
- `group_id`
- `phase_id`
- `submission_id`
- `rubric_id`
- `rubric_level_id`
- `score`
- `feedback`
- `assessed_by`
- `assessed_at`

### `project_rubric_configs`

- `id`
- `project_id`
- `rubric_id`
- `phase_name`
- `title`
- `description`
- `weight`
- `is_active`
- `levels_json`
- `updated_by`
- `created_at`
- `updated_at`

### `individual_reflections`

- `id`
- `project_id`
- `student_id`
- `reflection_path`
- `reflection_uploaded_at`
- `analysis_path`
- `analysis_uploaded_at`
- `score`
- `comment`
- `assessed_by`
- `assessed_at`
- `created_at`
- `updated_at`

### `individual_assessments`

- `id`
- `submission_id`
- `project_id`
- `group_id`
- `student_id`
- `phase_id`
- `score`
- `feedback`
- `design_score`
- `implementation_score`
- `results_score`
- `report_score`
- `graded_by`
- `graded_at`

### `phase_questions`

- `id`
- `phase_id`
- `project_id`
- `question_text`

### `phase_question_answers`

- `id`
- `question_id`
- `student_id`
- `answer_text`
- `score`

### `question_assessments`

- `id`
- `project_id`
- `student_id`
- `score`
- `feedback`
- `graded_by`
- `graded_at`

## High-risk schema hotspots

Ini area yang harus diperiksa paling hati-hati di database hosting:

1. `phase_submissions`
   Kode campur memakai `submission_date` dan `submitted_at`, juga `student_id` dan `submitted_by`.

2. `submission_files`
   Ada endpoint yang memakai `filename/filepath`, ada juga yang memakai `file_name/file_path`.

3. `projects`
   Kode mendukung `deadline`, `due_date`, dan `end_date`. Idealnya minimal salah satu ada, lebih aman kalau ketiganya tersedia.

4. `project_phases`
   Beberapa endpoint menganggap `phase_id` adalah `project_phases.id`, sebagian lain memakai `phase_number`. Keduanya perlu tersedia di tabel `project_phases`.

5. `individual_assessments`
   Ada query yang mengharapkan `submission_id`, ada juga yang mengharapkan score per fase seperti `design_score`.

## Practical recommendation

Sebelum hosting penuh:

1. Cocokkan schema database hosting dengan daftar di dokumen ini.
2. Jangan jalankan `setup-database.php` lama di production.
3. Buat migration baru yang menambahkan kolom kompatibilitas, bukan mengganti struktur lama secara agresif.
4. Uji alur ini setelah schema sinkron:
   - login
   - daftar kelas
   - daftar proyek
   - upload materi
   - submit fase proyek
   - simpan penilaian rubrik
   - upload refleksi individu
