Verano Regional database schema reference

Verano Regional uses a MySQL (or MariaDB-compatible) relational schema named verano. The schema is shipped as schema.sql in the repository root and uses utf8mb4 collation throughout to support full Unicode. All tables follow a consistent soft-delete convention: a status tinyint column where 0 means inactive and 1 means active. Audit columns (createDate, updateDate, idUserCreate, idUserUpdate) appear on most entities.

Tables

user — core user table

Every person who logs in to the platform has a row here, regardless of role. Role-specific data lives in the corresponding profile table (student, researcher, or coordinator).

Column	Type	Notes
`id`	int	Primary key
`idUserType`	int	FK → `usertype.id`
`email`	varchar(100)	Login credential
`password`	varchar(255)	bcrypt hash
`name`	varchar(255)	Given name
`paternalLastName`	varchar(50)
`maternalLastName`	varchar(50)
`birthDate`	date
`genre`	varchar(20)	`masculino`, `femenino`, `otro`
`phone`	varchar(20)
`street`, `zone`, `number`, `zipCode`	varchar	Address fields
`idCampus`	int	FK → `campus.id`
`isValid`	tinyint(1)	`0` = pending validation, `1` = validated
`idUserValidate`	int	FK → `user.id` (coordinator who validated)
`validateDate`	datetime
`status`	tinyint	`0` inactive / `1` active

usertype — role definitions

Defines the five roles in the platform. The id value is used throughout controllers in requireRole() calls.

id	Description
1	Admin (super-admin)
2	Institutional coordinator
3	Campus coordinator
4	Researcher
5	Student

student — student-specific fields

Extends user for students. One row per student per summer program period.

Column	Type	Notes
`id`	int	Primary key
`code`	varchar(20)	Student ID number
`CURP`	varchar(18)	Mexican personal ID code
`semester`	int	Current semester
`noteAverage`	decimal(4,2)	Grade average, base 100
`careerProgressPercentage`	int	Must be ≥ the summer program minimum
`idCareer`	int	FK → `career.id`
`idResearcherRecommends`	int	FK → `researcher.id` (nullable)
`idUser`	int	FK → `user.id`
`idSummer`	int	FK → `summer.id`

researcher — researcher-specific fields

Extends user for researchers. SNI and PRODEP are national research recognition flags.

Column	Type	Notes
`id`	int	Primary key
`title`	varchar(50)	Academic title (e.g., Dr., M.C.)
`department`	varchar(100)	Department or lab name
`SNILevel`	tinyint	`0`=No SNI, `1`=I, `2`=II, `3`=III
`PRODEP`	tinyint	`0`=No, `1`=Yes
`idUser`	int	FK → `user.id` (unique)

coordinator — coordinator-specific fields

Extends user for both campus and institutional coordinators.

Column	Type	Notes
`id`	int	Primary key
`position`	varchar(50)	Job title or position
`itsInstitutionalCoordinator`	tinyint(1)	`1` = institutional, `0` = campus
`idUser`	int	FK → `user.id` (unique)

project — research projects

Each project is proposed by a researcher and goes through a validation flow before students can apply to it.

Column	Type	Notes
`id`	int	Primary key
`title`	varchar(255)	Project title
`profile`	text	Desired student profile
`activity`	text	Description of activities
`skills`	text	Required skills
`modality`	text	In-person, remote, etc.
`observations`	text	Additional notes
`studentAmount`	int	Max students accepted
`careerProgressPercentage`	int	Minimum career progress required
`isValid`	tinyint(1)	`0` = pending, `1` = validated by coordinator
`idUserResearcher`	int	FK → `user.id` (nullable)
`idCampus`	int	FK → `campus.id`
`idInstitution`	int	FK → `institution.id`
`idSummer`	int	FK → `summer.id`
`idArea`	int	FK → `area.id`
`career`	varchar(255)	Target career (free text)
`image`	text	Optional image path

application — student applications

Records a student’s application to a project. A student may apply to up to five projects, each with a distinct priority number.

Column	Type	Notes
`id`	int	Primary key
`idProject`	int	FK → `project.id`
`idUserStudent`	int	FK → `user.id`
`idSummer`	int	FK → `summer.id`
`priority`	tinyint	Application rank (1 = top choice)
`isAccepted`	tinyint(1)	`1` after allocation algorithm runs
`comment`	text	Optional student comment
`lapNumber`	int	Program lap, ≥ 1
`status`	tinyint	`0` inactive / `1` active

A unique index on (idProject, idUserStudent, priority) prevents duplicate priority assignments.

summer — program period configuration

Defines a single summer program instance with all date windows for each phase of the program.

Column	Type	Notes
`id`	int	Primary key
`description`	varchar(300)	e.g., “Verano 2026”
`summerStartDate` / `summerEndDate`	datetime	Overall program dates
`startDate` / `endDate`	datetime	Registration window
`startValidateResearcherDate` / `endValidateResearcherDate`	datetime	Researcher validation window
`startCreateApplicationDate` / `endCreateApplicationDate`	datetime	Student application window
`startValidateApplicationDate` / `endValidateApplicationDate`	datetime	Coordinator validation window
`startCreateReportDate` / `endCreateReportDate`	datetime	Report submission window
`startValidateReportDate` / `endValidateReportDate`	datetime	Report validation window
`studentApplication`	int	Max applications per student
`studentPerProject`	int	Max students per project
`studentProgressMinPercentage`	int	Minimum career progress to apply

campus — campus records

A campus belongs to an institution and is located in a city.

Column	Type	Notes
`id`	int	Primary key
`description`	varchar(100)	Campus name
`campusAbbreviation`	varchar(20)	Short code
`idInstitution`	int	FK → `institution.id`
`idCity`	int	FK → `city.id`

institution — institution records

Top-level academic institution. Campuses and users belong to institutions.

Column	Type	Notes
`id`	int	Primary key
`description`	varchar(255)	Full institution name (unique)
`abbreviation`	varchar(20)	Short name
`idState`	int	FK → `state.id`
`institutional_email`	varchar(255)	Official contact email
`domain`	varchar(255)	Email domain for OTP validation

career — academic careers

A career (degree programme) belongs to a campus. Students register under a specific career.

Column	Type	Notes
`id`	int	Primary key
`description`	varchar(100)	Career name
`idCampus`	int	FK → `campus.id`

area — research areas

A taxonomy of research areas that projects are tagged with.

Column	Type	Notes
`id`	int	Primary key
`description`	varchar(300)	Area name

report — student progress reports

Tracks a report file uploaded by a student for their assigned project.

Column	Type	Notes
`id`	int	Primary key
`idUser`	int	FK → `user.id` (student)
`idProject`	int	FK → `project.id`
`updateDate`	datetime	Last upload timestamp
`status`	tinyint(1)	Validation status

document and studentdocument — document templates and uploads

document defines required document types (e.g., CURP, KARDEX) identified by a unique prefix. studentdocument records which documents a student has uploaded; the uploaded file is stored on disk under uploads/student_documents/{studentId}/{prefix}.pdf.A unique index on (idDocument, idStudent) in studentdocument ensures one upload per document type per student.

verified_emails — OTP verification records

Stores the hashed OTP sent during pre-registration email verification. The is_used flag is set to 1 after the student completes registration.

Column	Type	Notes
`id`	int	Primary key
`email`	varchar(100)	Unique — one active OTP per address
`otp_hash`	varchar(255)	bcrypt hash of the OTP
`expires_at`	datetime	Expiry timestamp
`is_used`	tinyint(1)	`1` after successful verification

passwordresets — password reset OTP records

Stores the hashed OTP issued during the forgot-password flow.

Column	Type	Notes
`id`	int	Primary key
`email`	varchar(100)	Target address (indexed)
`otp_hash`	varchar(255)	bcrypt hash of the OTP
`expires_at`	datetime	Expiry timestamp

approved_registrations — pre-approved registration keys

Contains registration keys (e.g., employee IDs) that must be presented during sign-up to verify eligibility for a given role.

Column	Type	Notes
`id`	int	Primary key
`registration_key`	varchar(50)	Unique key (e.g., institutional ID)
`id_user_type`	int	FK → `usertype.id` — role this key grants

Database views

The schema ships eight pre-built views that join the normalised tables into flat, query-friendly result sets. Controllers and models read from these views instead of writing multi-table joins inline.

View	Description
`vw_projectdetails`	Projects joined with researcher name and title, area, campus, and institution
`vw_studentdetails`	Students joined with career, campus, and institution
`vw_researcherdetails`	Researchers joined with campus and institution
`vw_coordinatordetails`	Coordinators joined with campus and institution; includes `coordinatorType` label
`vw_projectapplications`	Applications joined with `vw_studentdetails` — used by researchers to see who applied
`vw_studentapplications`	Applications joined with `vw_projectdetails` — used by students to see their applications
`vw_campusdetails`	Campus joined with institution name and a `city - state` location string
`vw_careerdetails`	Careers joined with campus and institution

Key relationships

user         → usertype       (user.idUserType)
user         → campus         (user.idCampus)
student      → user           (student.idUser)
student      → career         (student.idCareer)
student      → summer         (student.idSummer)
researcher   → user           (researcher.idUser)
coordinator  → user           (coordinator.idUser)
project      → user           (project.idUserResearcher)
project      → campus         (project.idCampus)
project      → institution    (project.idInstitution)
project      → summer         (project.idSummer)
project      → area           (project.idArea)
application  → project        (application.idProject)
application  → user           (application.idUserStudent)
application  → summer         (application.idSummer)
campus       → institution    (campus.idInstitution)
campus       → city           (campus.idCity)
career       → campus         (career.idCampus)
studentdocument → document    (studentdocument.idDocument)
studentdocument → student     (studentdocument.idStudent)

The status column follows a consistent convention across all tables: 0 = inactive (soft-deleted), 1 = active. Queries should always filter on status = 1 unless you intentionally need to retrieve inactive records.

Get Started

Core Features

Administration

Developer Guide

Verano Regional database schema reference

Tables

Database views

Key relationships

Build docs developers (and LLMs) love

Get Started

Core Features

Administration

Developer Guide

Documentation Index

​Tables

​Database views

​Key relationships

Build docs developers (and LLMs) love

Tables

Database views

Key relationships