Schema Reference

# Database

15 tables
91 columns
22 foreign keys
Generated 2026-02-19
15
Tables
91
Columns
22
Foreign Keys
5
Unique Keys
21
Indexes

## Table Definitions

PK Primary Key
FK Foreign Key
UQ Unique
IDX Indexed
NN = Not Null
NULL = Nullable
goal
9 columns

Column | Type | Null | Default | Key | References
--- | --- | --- | --- | --- | ---
id_goal | int | NN | — | PK | —
id_goal_parent | int | NULL | NULL | FK IDX | goal.id_goal
id_student | int | NULL | NULL | FK IDX | student.id_student
id_user_created | int | NULL | NULL | FK IDX | user.id_user
title | varchar(255) | NULL | NULL | — | —
description | text | NULL | — | — | —
category | varchar(100) | NULL | NULL | — | —
created_at | timestamp | NULL | NULL | — | —
updated_at | timestamp | NULL | NULL | — | —

health_note
5 columns

Column | Type | Null | Default | Key | References
--- | --- | --- | --- | --- | ---
id_health_note | int | NN | — | PK | —
id_student | int | NULL | NULL | FK IDX | student.id_student
id_user_created | int | NULL | NULL | FK IDX | user.id_user
content | text | NULL | — | — | —
created_at | timestamp | NULL | NULL | — | —

password_history
4 columns

Column | Type | Null | Default | Key | References
--- | --- | --- | --- | --- | ---
id_password_history | int | NN | — | PK | —
id_user | int | NULL | NULL | FK IDX | user.id_user
password_hash | varchar(255) | NN | — | — | —
created_at | timestamp | NULL | NULL | IDX | —

password_reset_token
8 columns

Column | Type | Null | Default | Key | References
--- | --- | --- | --- | --- | ---
id_password_reset_token | int | NN | — | PK | —
id_user | int | NULL | NULL | FK IDX | user.id_user
token_hash | varchar(255) | NULL | NULL | UQ | —
expires_at | timestamp | NN | — | — | —
created_at | timestamp | NULL | NULL | IDX | —
used_at | timestamp | NULL | NULL | — | —
invalidated_at | timestamp | NULL | NULL | — | —
request_ip | varchar(45) | NULL | NULL | — | —

permission
6 columns

Column | Type | Null | Default | Key | References
--- | --- | --- | --- | --- | ---
id_permission | int | NN | — | PK | —
name | varchar(100) | NULL | NULL | — | —
description | text | NULL | — | — | —
resource | varchar(100) | NULL | NULL | — | —
action | varchar(50) | NULL | NULL | — | —
scope | varchar(50) | NULL | NULL | — | —

program
5 columns

Column | Type | Null | Default | Key | References
--- | --- | --- | --- | --- | ---
id_program | int | NN | — | PK | —
id_school_district | int | NULL | NULL | FK IDX | school_district.id_school_district
name | varchar(255) | NULL | NULL | — | —
description | text | NULL | — | — | —
created_at | timestamp | NULL | NULL | — | —

progress_event
8 columns

Column | Type | Null | Default | Key | References
--- | --- | --- | --- | --- | ---
id_progress_event | int | NN | — | PK | —
id_student | int | NULL | NULL | FK IDX | student.id_student
id_goal | int | NULL | NULL | FK IDX | goal.id_goal
id_user_created | int | NULL | NULL | FK IDX | user.id_user
content | text | NULL | — | — | —
is_sensitive | tinyint(1) | NULL | NULL | — | —
created_at | timestamp | NULL | NULL | — | —
updated_at | timestamp | NULL | NULL | — | —

progress_report
8 columns

Column | Type | Null | Default | Key | References
--- | --- | --- | --- | --- | ---
id_progress_report | int | NN | — | PK | —
id_student | int | NULL | NULL | FK IDX | student.id_student
id_goal | int | NULL | NULL | FK IDX | goal.id_goal
id_user_created | int | NULL | NULL | FK IDX | user.id_user
period | varchar(10) | NULL | NULL | — | —
year | int | NULL | NULL | — | —
summary | text | NULL | — | — | —
generated_at | timestamp | NULL | NULL | — | —

role
4 columns

Column | Type | Null | Default | Key | References
--- | --- | --- | --- | --- | ---
id_role | int | NN | — | PK | —
name | varchar(100) | NULL | NULL | — | —
description | text | NULL | — | — | —
created_at | timestamp | NULL | NULL | — | —

role_permission
3 columns

Column | Type | Null | Default | Key | References
--- | --- | --- | --- | --- | ---
id_role_permission | int | NN | — | PK | —
id_role | int | NULL | NULL | FK IDX | role.id_role
id_permission | int | NULL | NULL | FK IDX | permission.id_permission

school_district
4 columns

Column | Type | Null | Default | Key | References
--- | --- | --- | --- | --- | ---
id_school_district | int | NN | — | PK | —
name | varchar(255) | NULL | NULL | — | —
contact_email | varchar(255) | NULL | NULL | — | —
created_at | timestamp | NULL | NULL | — | —

student
7 columns

Column | Type | Null | Default | Key | References
--- | --- | --- | --- | --- | ---
id_student | int | NN | — | PK | —
id_program | int | NULL | NULL | FK IDX | program.id_program
identifier | varchar(50) | NULL | NULL | — | —
program_year | int | NULL | NULL | — | —
enrollment_date | date | NULL | NULL | — | —
expected_grad | date | NULL | NULL | — | —
created_at | timestamp | NULL | NULL | — | —

user
9 columns

Column | Type | Null | Default | Key | References
--- | --- | --- | --- | --- | ---
id_user | int | NN | — | PK | —
id_role | int | NULL | NULL | FK IDX | role.id_role
email | varchar(255) | NULL | NULL | — | —
name | varchar(255) | NULL | NULL | — | —
password_hash | varchar(255) | NULL | NULL | — | —
password_updated_at | timestamp | NULL | NULL | — | —
failed_login_attempts | int | NULL | '0' | — | —
locked_until | timestamp | NULL | NULL | — | —
created_at | timestamp | NULL | NULL | — | —

user_program
6 columns

Column | Type | Null | Default | Key | References
--- | --- | --- | --- | --- | ---
id_user_program | int | NN | — | PK | —
id_user | int | NULL | NULL | FK UQ IDX | user.id_user
id_program | int | NULL | NULL | FK UQ IDX | program.id_program
is_primary | tinyint(1) | NULL | '0' | — | —
status | varchar(20) | NULL | 'active' | — | —
joined_at | timestamp | NULL | NULL | — | —

user_student
5 columns

Column | Type | Null | Default | Key | References
--- | --- | --- | --- | --- | ---
id_user_student | int | NN | — | PK | —
id_user | int | NULL | NULL | FK IDX | user.id_user
id_student | int | NULL | NULL | FK IDX | student.id_student
access_level | varchar(50) | NULL | NULL | — | —
is_primary | tinyint(1) | NULL | NULL | — | —