# Database design

Status: **APPROVED 5 Nov 2026 and implemented.** Migrations are in `database/migrations/2026_11_05_*` and have been run on MySQL. Decisions D1 to D5 approved. `audit_logs` and `notifications` follow the design; MySQL CHECK constraints are added only on MySQL.
Target: MySQL 8 (database `ajdan_hr`, currently empty), Laravel 12 migrations, utf8mb4.

## Design principles

- Small: 8 domain tables plus the standard Laravel framework tables, so the client can understand and import the data.
- One source of truth per fact. Reviewers and weights are copied onto a review when it is created (snapshot) so later changes to the org chart or questions never rewrite history.
- Business rules that can change are data, not code: section weights and scorer roles live in `review_sections`.
- Integrity in the database where MySQL can enforce it (foreign keys, unique keys, CHECK constraints), and again in the application.
- Soft deletes only where a record can be removed from view but must stay referenced: `employees`, `questions`. Reviews and answers are never deleted.

## Domain tables

### departments
| Column | Type | Notes |
|---|---|---|
| id | bigint unsigned PK | |
| name_en | varchar(120) | unique |
| name_ar | varchar(120) null | |
| created_at, updated_at | timestamp | |

### employees
Everyone in the company: people who log in (team leads, department heads, HR, admins) and people who are reviewed. This table is the authentication principal (Microsoft 365 sign-in, no passwords for normal users).

| Column | Type | Notes |
|---|---|---|
| id | bigint unsigned PK | |
| employee_code | varchar(30) | unique |
| name_en | varchar(150) | |
| name_ar | varchar(150) null | |
| email | varchar(190) | unique, case-insensitive; must match the Microsoft 365 address |
| entra_oid | char(36) null | unique; Microsoft object id, bound at first sign-in so a changed or re-used email cannot take over an account |
| job_title_en / job_title_ar | varchar(150) null | |
| department_id | FK departments null | restrict on delete |
| role | varchar(20) | `employee`, `team_lead`, `department_head`, `hr` (PHP backed enum). Only the last three can sign in |
| is_admin | boolean default false | admin may also hold a role |
| is_active | boolean default true | inactive accounts cannot sign in |
| team_lead_id | FK employees null | current reporting line, used when a review is created |
| department_head_id | FK employees null | |
| hr_reviewer_id | FK employees null | |
| last_login_at | timestamp null | |
| created_at, updated_at, deleted_at | timestamp | soft delete |

Indexes: `role`, `department_id`, `team_lead_id`, `department_head_id`, `hr_reviewer_id`, `is_active`.
Rule: an employee is *reviewed* when all three reviewer columns are set.

### review_sections
The three fixed parts of every form. Weights and who scores them are data.

| Column | Type | Notes |
|---|---|---|
| id | tinyint unsigned PK | |
| code | varchar(30) | unique: `general`, `technical`, `hr_competencies` |
| scorer_role | varchar(20) | **unique**: `team_lead`, `department_head`, `hr`. One section per role, so sections cannot overwrite each other |
| name_en / name_ar | varchar(100) | |
| weight_percent | tinyint unsigned | seeded 40 / 50 / 10, must total 100 |
| sort_order | tinyint unsigned | |

### appraisal_cycles
| Column | Type | Notes |
|---|---|---|
| id | bigint unsigned PK | |
| year | smallint unsigned | unique |
| status | varchar(10) | `draft`, `open`, `closed` (CHECK). Only one cycle may be `open` (enforced in the service layer) |
| opens_on / closes_on | date null | informational |
| timestamps | | |

### questions
Each employee has their own question set (confirmed in the meeting).

| Column | Type | Notes |
|---|---|---|
| id | bigint unsigned PK | |
| employee_id | FK employees | restrict |
| section_id | FK review_sections | restrict; decides who answers it |
| code | varchar(40) null | competency code, unique per employee when set |
| title_en / title_ar | varchar(200) | |
| description_en / description_ar | text null | |
| weight | decimal(5,2) | share of the 100-point total, CHECK > 0 |
| sort_order | smallint unsigned | |
| created_at, updated_at, deleted_at | | soft delete |

Index: `(employee_id, section_id, sort_order)`.
Rule: for each employee, the weights in a section must add up to that section's `weight_percent`. Checked when saving questions, and re-checked before a review can be created.

### reviews
One per employee per cycle. Holds the workflow state.

| Column | Type | Notes |
|---|---|---|
| id | bigint unsigned PK | |
| cycle_id | FK appraisal_cycles | restrict |
| employee_id | FK employees | restrict |
| team_lead_id, department_head_id, hr_reviewer_id | FK employees | snapshot of the reviewers when the review was created |
| stage | varchar(20) | `team_lead`, `department_head`, `hr`, `completed` (CHECK). Whose turn it is. Moves forward only |
| team_lead_submitted_at, department_head_submitted_at, hr_submitted_at | timestamp null | final submit times. A stage is submitted when its timestamp is set |
| last_saved_at | timestamp null | last Save Progress by the current stage owner |
| final_score | decimal(5,2) null | set once, at HR final submit |
| created_at, updated_at | | |

Indexes: unique `(cycle_id, employee_id)`, `(cycle_id, stage)`, `team_lead_id`, `department_head_id`, `hr_reviewer_id`.
No soft delete: reviews are not deleted.

### review_answers
One row per question per review, created together with the review. This is the "same row" idea from the meeting: the rating lives next to the question it answers.

| Column | Type | Notes |
|---|---|---|
| id | bigint unsigned PK | |
| review_id | FK reviews | cascade |
| question_id | FK questions | restrict |
| weight | decimal(5,2) | snapshot of the question weight at review creation, so scores never change retroactively |
| rating | tinyint unsigned null | NULL = not answered yet. CHECK 1..5 |
| answered_by_id | FK employees null | who last set the rating |
| answered_at | timestamp null | |
| timestamps | | |

Indexes: unique `(review_id, question_id)`.
Completeness of a section = no NULL rating among its rows. This makes Final Submit validation a single query.

### audit_logs
Who did what, including admin "view as" and every Save/Submit.

| Column | Type | Notes |
|---|---|---|
| id | bigint unsigned PK | |
| actor_id | FK employees null | the real signed-in person |
| acting_as_id | FK employees null | set when an admin acts through "view as" |
| action | varchar(60) | e.g. `review.saved`, `review.submitted`, `auth.login`, `admin.impersonate`, `employee.updated` |
| auditable_type / auditable_id | varchar(60) / bigint null | the record concerned |
| description | varchar(255) null | no personal data, no ratings content |
| ip_address | varchar(45) null | |
| created_at | timestamp | append-only |

Indexes: `(auditable_type, auditable_id)`, `(actor_id, created_at)`, `action`.

## Framework tables (Laravel standard)
`sessions` (user_id points to `employees.id`), `cache`, `cache_locks`, `jobs`, `job_batches`, `failed_jobs` (queued emails), `notifications` (in-app notification feed and email log; its `data` JSON column is Laravel's standard).
The skeleton's `users` and `password_reset_tokens` tables are replaced by `employees` (see decision D1). The skeleton migrations have never been run, so editing them destroys nothing.

## Relationships
- Department 1—N Employee.
- Employee N—1 Employee three times (team lead, department head, HR reviewer).
- ReviewSection 1—N Question. Employee 1—N Question.
- AppraisalCycle 1—N Review. Employee 1—N Review (one per cycle).
- Review N—1 Employee three more times (snapshot reviewers).
- Review 1—N ReviewAnswer. Question 1—N ReviewAnswer.
- AuditLog N—1 Employee (actor, acting_as).

## Business rules the schema supports
1. Stage order is fixed: team_lead, department_head, hr, completed. No going back.
2. Each role only writes ratings for questions in its own section (`review_sections.scorer_role`), and only while `reviews.stage` equals that role.
3. Save Progress writes ratings (any subset) without changing stage. Final Submit requires every answer row of the role's section to have a rating, then sets the submit timestamp and advances the stage in one transaction under a row lock.
4. Score = sum of rating / 5 x weight over all answers. Stored in `final_score` on completion.
5. Question weight or section may not change once answers exist for that employee in any review.
6. Reviewer columns on `reviews` are a snapshot: reassigning someone in `employees` affects only future reviews.

## Open items
- Decision D1: use `employees` as the login table and drop `users` (recommended).
- Decision D2: whether admins can also sign in with a password (break-glass), which needs a nullable `password` column. Default: no, SSO only.
- Per-employee questions mean about 15 rows per employee (about 3,000 for 200 people). An alternative is shared question templates per job title. Kept per-employee because the meeting said questions differ per employee.
