Schema

Fully-qualified column definitions. Types map to Laravel migration types; MySQL types are shown for clarity.

organizations

id             BIGINT UNSIGNED PK
name           VARCHAR(150) NOT NULL
code           VARCHAR(30)  NOT NULL UNIQUE
is_active      BOOLEAN      NOT NULL DEFAULT TRUE
contact_email  VARCHAR(150) NULL
settings       JSON         NULL
created_at, updated_at

organization_units

id                  BIGINT UNSIGNED PK
organization_id     BIGINT UNSIGNED FK organizations.id
name                VARCHAR(150) NOT NULL
code                VARCHAR(30)  NOT NULL
timezone            VARCHAR(64)  NOT NULL DEFAULT 'UTC'
geo_enabled         BOOLEAN      NOT NULL DEFAULT FALSE
geo_lat             DECIMAL(10,6) NULL
geo_lon             DECIMAL(10,6) NULL
geo_radius_m        INT           NULL
device_binding      BOOLEAN      NOT NULL DEFAULT FALSE
is_active           BOOLEAN      NOT NULL DEFAULT TRUE
settings            JSON         NULL
UNIQUE (organization_id, code)
created_at, updated_at

users

id                  BIGINT UNSIGNED PK
name                VARCHAR(150) NOT NULL
email               VARCHAR(191) NOT NULL UNIQUE
email_verified_at   TIMESTAMP NULL
password            VARCHAR(255) NOT NULL
type                ENUM('employee','staff') NOT NULL DEFAULT 'staff'
is_active           BOOLEAN NOT NULL DEFAULT TRUE
remember_token      VARCHAR(100) NULL
created_at, updated_at

employees

id                  BIGINT UNSIGNED PK
user_id             BIGINT UNSIGNED FK users.id UNIQUE
employee_code       VARCHAR(30) NOT NULL UNIQUE
category            ENUM('permanent','intern','consultant','locum') NOT NULL
phone               VARCHAR(20)  NULL        -- encrypted at rest
date_of_joining     DATE NULL
is_active           BOOLEAN NOT NULL DEFAULT TRUE
meta                JSON NULL
created_at, updated_at

employee_deployments

id                  BIGINT UNSIGNED PK
employee_id         BIGINT UNSIGNED FK employees.id
organization_id     BIGINT UNSIGNED FK organizations.id
organization_unit_id BIGINT UNSIGNED FK organization_units.id
starts_on           DATE NOT NULL
ends_on             DATE NULL
is_primary          BOOLEAN NOT NULL DEFAULT TRUE
INDEX (employee_id, organization_id, starts_on)
created_at, updated_at

shifts

id                  BIGINT UNSIGNED PK
organization_unit_id BIGINT UNSIGNED FK organization_units.id
name                VARCHAR(60) NOT NULL
code                VARCHAR(20) NOT NULL
start_time          TIME NOT NULL
end_time            TIME NOT NULL
is_overnight        BOOLEAN NOT NULL DEFAULT FALSE
grace_in_min        SMALLINT NOT NULL DEFAULT 15
grace_out_min       SMALLINT NOT NULL DEFAULT 30
early_in_min        SMALLINT NOT NULL DEFAULT 30
early_out_min       SMALLINT NOT NULL DEFAULT 15
is_active           BOOLEAN NOT NULL DEFAULT TRUE
UNIQUE (organization_unit_id, code)
created_at, updated_at

shift_assignments

id                  BIGINT UNSIGNED PK
employee_id         BIGINT UNSIGNED FK employees.id
shift_id            BIGINT UNSIGNED FK shifts.id
organization_unit_id BIGINT UNSIGNED FK organization_units.id
assigned_for        DATE NOT NULL          -- shift start date
locum_for_id        BIGINT UNSIGNED NULL FK employees.id
status              ENUM('planned','replaced','cancelled') DEFAULT 'planned'
created_by          BIGINT UNSIGNED FK users.id
INDEX (employee_id, assigned_for)
INDEX (organization_unit_id, assigned_for)
created_at, updated_at

punch_logs

id                  BIGINT UNSIGNED PK
employee_id         BIGINT UNSIGNED FK employees.id
organization_unit_id BIGINT UNSIGNED FK organization_units.id
punched_at          DATETIME NOT NULL       -- UTC
channel             ENUM('web','mobile','telegram','kiosk','manual') NOT NULL
direction           ENUM('in','out','auto') NOT NULL DEFAULT 'auto'
device_id           BIGINT UNSIGNED NULL FK devices.id
lat                 DECIMAL(10,6) NULL
lon                 DECIMAL(10,6) NULL
accuracy_m          FLOAT NULL
raw_payload         JSON NULL
processed_at        TIMESTAMP NULL
INDEX (employee_id, punched_at)

attendances

id                  BIGINT UNSIGNED PK
shift_assignment_id BIGINT UNSIGNED FK shift_assignments.id
employee_id         BIGINT UNSIGNED FK employees.id
attendance_date     DATE NOT NULL
first_in_at         DATETIME NULL
last_out_at         DATETIME NULL
status              ENUM('present','absent','half_day','leave','holiday','weekoff') NOT NULL
worked_minutes      INT NOT NULL DEFAULT 0
locked_at           TIMESTAMP NULL
recomputed_at       TIMESTAMP NULL
UNIQUE (shift_assignment_id)
INDEX (employee_id, attendance_date)

attendance_flags

id              BIGINT UNSIGNED PK
attendance_id   BIGINT UNSIGNED FK attendances.id
code            VARCHAR(40) NOT NULL      -- e.g. LATE_CHECK_IN
severity        ENUM('info','warn','critical') NOT NULL
context         JSON NULL
created_at

leaves

id              BIGINT UNSIGNED PK
employee_id     BIGINT UNSIGNED FK employees.id
organization_id BIGINT UNSIGNED FK organizations.id
starts_on       DATE NOT NULL
ends_on         DATE NOT NULL
type            ENUM('casual','sick','earned','unpaid','other') NOT NULL
reason          TEXT NULL
status          ENUM('pending','shift_manager_approved','hr_approved','rejected','overridden') NOT NULL DEFAULT 'pending'
submitted_at    TIMESTAMP NOT NULL
decided_at      TIMESTAMP NULL

regularizations

id              BIGINT UNSIGNED PK
employee_id     BIGINT UNSIGNED FK employees.id
attendance_id   BIGINT UNSIGNED NULL FK attendances.id
for_date        DATE NOT NULL
requested_in    DATETIME NULL
requested_out   DATETIME NULL
reason          TEXT NULL
status          ENUM('pending','shift_manager_approved','hr_approved','rejected','overridden') NOT NULL DEFAULT 'pending'
submitted_at    TIMESTAMP NOT NULL
decided_at      TIMESTAMP NULL

devices

id              BIGINT UNSIGNED PK
employee_id     BIGINT UNSIGNED FK employees.id
fingerprint     VARCHAR(120) NOT NULL        -- hash(platform|model|installId)
platform        VARCHAR(20)  NOT NULL
model           VARCHAR(60)  NULL
os_version      VARCHAR(30)  NULL
status          ENUM('pending','approved','revoked') NOT NULL DEFAULT 'pending'
approved_by     BIGINT UNSIGNED NULL FK users.id
last_seen_at    TIMESTAMP NULL
UNIQUE (employee_id, fingerprint)

attendance_locks

id                     BIGINT UNSIGNED PK
organization_id        BIGINT UNSIGNED FK organizations.id
organization_unit_id   BIGINT UNSIGNED NULL FK organization_units.id
month                  CHAR(7) NOT NULL   -- YYYY-MM
locked_at              TIMESTAMP NOT NULL
locked_by              BIGINT UNSIGNED FK users.id
UNIQUE (organization_id, organization_unit_id, month)

audit_logs

id              BIGINT UNSIGNED PK
actor_id        BIGINT UNSIGNED NULL FK users.id
action          VARCHAR(60) NOT NULL   -- attendance.update, leave.approve, lock.unlock, ...
subject_type    VARCHAR(120) NOT NULL
subject_id      BIGINT UNSIGNED NOT NULL
before          JSON NULL
after           JSON NULL
context         JSON NULL
created_at      TIMESTAMP NOT NULL
INDEX (subject_type, subject_id)
INDEX (actor_id, created_at)

red_flag_policies

id                     BIGINT UNSIGNED PK
organization_unit_id   BIGINT UNSIGNED FK organization_units.id
code                   VARCHAR(40) NOT NULL  -- flag code
action                 ENUM('allow_flag','block','require_approval') NOT NULL
UNIQUE (organization_unit_id, code)

holidays

id               BIGINT UNSIGNED PK
organization_id  BIGINT UNSIGNED FK organizations.id
organization_unit_id BIGINT UNSIGNED NULL FK organization_units.id
date             DATE NOT NULL
name             VARCHAR(120) NOT NULL

exports

id           BIGINT UNSIGNED PK
requested_by BIGINT UNSIGNED FK users.id
type         ENUM('csv','xlsx','pdf') NOT NULL
filters      JSON NOT NULL
status       ENUM('queued','processing','ready','failed') NOT NULL
path         VARCHAR(255) NULL
error        TEXT NULL
created_at, updated_at