Database Schema: HRM Module

This document details the database structure for the HRM module, including core entities, relationships, and data types.

---

1. Overview

The HRM module uses a PostgreSQL database with Drizzle ORM. It follows a multi-tenant architecture where data is strictly isolated by orgId (Organization ID), which is synchronized with the Shell's organization registry.

Core Domain Model

---

2. Table Definitions

organizations

Synchronized from the Shell; stores HRM-specific settings like carry-forward limits.

Field	Type	Description
id	uuid	PK. Matches the Shell's organization_id.
name	text	Organization name.
carry_forward_limit	integer	Max days allowed to carry over (default: 5).

departments

Company organizational units.

Field	Type	Description
id	uuid	PK.
org_id	uuid	FK to organizations.id.
name	text	Department name.
parent_department_id	uuid	Self-reference for hierarchy.

employees

Core record for every staff member.

Field	Type	Description
id	uuid	PK. Internal HRM ID.
org_id	uuid	FK to organizations.id.
user_id	uuid	Shell User ID (used for Auth sync).
job_title	text	Official job title.
department_id	uuid	FK to departments.id.
manager_id	uuid	FK to employees.id.
status	enum	active, on_leave, inactive, new.
phone	text	Work contact number.
emergency_contact	jsonb	Structured emergency data.

leave_balances

Tracks available time off per employee.

Field	Type	Description
id	uuid	PK.
employee_id	uuid	FK to employees.id.
leave_type	enum	annual, sick, unpaid, public_holiday_top_up.
total_days	decimal	Total allocation for the year.
used_days	decimal	Days already taken.
carry_forward	decimal	Days brought over from previous year.

leave_requests

Specific absence requests.

Field	Type	Description
id	uuid	PK.
org_id	uuid	FK to organizations.id.
employee_id	uuid	FK to employees.id.
leave_type	enum	Same as balances.
start_date	date	Start of absence.
end_date	date	End of absence.
status	enum	pending, approved, rejected, cancelled.
working_days	integer	Calculated duration (business days).
approved_by	uuid	FK to employees.id.

---

3. Enumerated Types

employee_status

• active: Fully employed and working.
• on_leave: Currently away on long-term leave.
• inactive: Resigned or terminated.
• new: In onboarding phase.

leave_type

• annual: Standard paid time off.
• sick: Medical leave.
• unpaid: Self-explanatory.
• public_holiday_top_up: Specific VN labor law compliance.

---

4. Multi-tenant Isolation

All queries MUST include the orgId filter. In the HRM API, this is enforced by the EmployeeService and LeaveService by extracting the organizationId from the Platform JWT.

// Example Isolation Pattern
const results = await db.select()
  .from(employees)
  .where(eq(employees.orgId, orgId));