> ## Documentation Index
> Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Human Resources Database Tables

> The HR module covers employee records, recruitment (ATS), onboarding/offboarding, time and attendance, leave, payroll, benefits, credentials, skills, and emplo…

**Prefix:** `hr_`\
**Table Count:** 85\
**Last Updated:** 2026-02-08

***

## Overview

The HR module covers employee records, recruitment (ATS), onboarding/offboarding, time and attendance, leave, payroll, benefits, credentials, skills, and employee relations.

***

## Table Categories

### 1. Core Employee Management (5 tables)

| Table                     | Columns | Description                    |
| ------------------------- | ------- | ------------------------------ |
| `hr_employees`            | 48      | Core employee records with PII |
| `hr_employee_sites`       | 6       | Employee-to-site assignments   |
| `hr_employee_teams`       | 7       | Team memberships               |
| `hr_team_members`         | 6       | Team member junction table     |
| `hr_employee_preferences` | 14      | Employee app preferences       |

**Key Fields in `hr_employees`:**

* `employee_number`: Unique identifier
* `status`: 'active', 'inactive', 'terminated', 'on\_leave'
* `employment_type`: 'full\_time', 'part\_time', 'contractor', 'intern'
* `ssn_encrypted`: Encrypted SSN (column-level encryption)
* `custom_fields`: JSONB for org-specific data

**Relationships:**

```
hr_employees (1) ──< (many) hr_employee_sites >── (1) pf_sites
hr_employees (1) ──< (many) hr_employee_teams
hr_employees (many) >── (1) pf_departments
hr_employees (many) >── (1) hr_positions
```

### 2. Recruitment / ATS (10 tables)

| Table                    | Columns | Description                      |
| ------------------------ | ------- | -------------------------------- |
| `hr_job_postings`        | 32      | Job listing definitions          |
| `hr_candidates`          | 28      | Candidate profiles               |
| `hr_applications`        | 22      | Job applications                 |
| `hr_interviews`          | 18      | Interview scheduling             |
| `hr_interview_feedback`  | 14      | Interviewer feedback             |
| `hr_offers`              | 26      | Job offer records                |
| `hr_offer_templates`     | 16      | Offer letter templates           |
| `hr_offer_approvals`     | 12      | Offer approval workflow          |
| `hr_offer_counteroffers` | 14      | Counteroffer tracking            |
| `hr_hire_transitions`    | 16      | Candidate-to-employee conversion |

**Hiring Pipeline:**

```
hr_job_postings → hr_applications → hr_candidates
                          ↓
                  hr_interviews → hr_interview_feedback
                          ↓
                     hr_offers → hr_offer_approvals
                          ↓
                  hr_hire_transitions → hr_employees
```

### 3. Onboarding & Offboarding (6 tables)

| Table                            | Columns | Description                   |
| -------------------------------- | ------- | ----------------------------- |
| `hr_onboarding_templates`        | 14      | Onboarding workflow templates |
| `hr_onboarding_tasks`            | 16      | Task definitions in templates |
| `hr_onboarding_instances`        | 18      | Active onboarding processes   |
| `hr_onboarding_task_instances`   | 14      | Task completion tracking      |
| `hr_offboarding_instances`       | 16      | Active offboarding processes  |
| `hr_offboarding_checklist_items` | 12      | Offboarding task tracking     |

### 4. Time & Attendance (9 tables)

| Table                         | Columns | Description                |
| ----------------------------- | ------- | -------------------------- |
| `hr_timesheets`               | 18      | Weekly/biweekly timesheets |
| `hr_timesheet_entries`        | 14      | Individual time entries    |
| `hr_time_punches`             | 12      | Clock in/out records       |
| `hr_time_exceptions`          | 14      | Exception tracking         |
| `hr_shift_templates`          | 16      | Shift pattern definitions  |
| `hr_shifts`                   | 18      | Scheduled shifts           |
| `hr_shift_assignments`        | 10      | Employee shift assignments |
| `hr_shift_swap_requests`      | 14      | Shift swap workflow        |
| `hr_availability_preferences` | 12      | Employee availability      |

**Time Entry Pattern:**

```typescript theme={null}
// Submit timesheet entry
const { data } = await supabase
  .from('hr_timesheet_entries')
  .insert({
    timesheet_id: timesheetId,
    work_date: '2026-01-10',
    hours_worked: 8.0,
    entry_type: 'regular',
    project_code: 'PROJ-001'
  });
```

### 5. Leave Management (5 tables)

| Table                        | Columns | Description                |
| ---------------------------- | ------- | -------------------------- |
| `hr_leave_policies`          | 20      | Leave policy definitions   |
| `hr_leave_accrual_tiers`     | 12      | Tenure-based accrual rates |
| `hr_employee_leave_balances` | 14      | Current leave balances     |
| `hr_leave_requests`          | 22      | Leave request records      |
| `hr_fmla_cases`              | 24      | FMLA case tracking         |

**Leave Types:**

* PTO (Paid Time Off)
* Sick Leave
* Vacation
* Personal
* FMLA
* Bereavement
* Jury Duty
* Military

### 6. Payroll (9 tables)

| Table                     | Columns | Description             |
| ------------------------- | ------- | ----------------------- |
| `hr_payroll_runs`         | 22      | Payroll processing runs |
| `hr_payroll_line_items`   | 18      | Individual pay items    |
| `hr_payroll_exports`      | 14      | Export file tracking    |
| `hr_payroll_audit_log`    | 12      | Payroll changes audit   |
| `hr_pay_rates`            | 16      | Employee pay rates      |
| `hr_pay_adjustments`      | 14      | One-time adjustments    |
| `hr_tax_documents`        | 14      | W-2, W-4, I-9 tracking  |
| `hr_ssn_access_log`       | 10      | SSN access audit        |
| `hr_document_access_logs` | 12      | Sensitive doc access    |

**Security Notes:**

* SSN stored with column-level encryption
* All SSN access logged to `hr_ssn_access_log`
* Payroll changes tracked in `hr_payroll_audit_log`

### 7. Benefits (6 tables)

| Table                                 | Columns | Description              |
| ------------------------------------- | ------- | ------------------------ |
| `hr_benefits_plans`                   | 24      | Benefit plan definitions |
| `hr_benefits_enrollments`             | 18      | Employee enrollments     |
| `hr_benefits_enrollment_dependents`   | 12      | Dependent coverage       |
| `hr_benefits_dependents`              | 16      | Dependent information    |
| `hr_benefits_eligibility_rules`       | 14      | Eligibility criteria     |
| `hr_benefits_open_enrollment_periods` | 12      | OE period tracking       |

### 8. Credentials & Certifications (5 tables)

| Table                             | Columns | Description            |
| --------------------------------- | ------- | ---------------------- |
| `hr_credential_types`             | 16      | Credential definitions |
| `hr_employee_credentials`         | 20      | Employee credentials   |
| `hr_credential_requirements`      | 12      | Position requirements  |
| `hr_credential_alerts`            | 14      | Expiration alerts      |
| `hr_credential_renewal_workflows` | 14      | Renewal tracking       |

**Credential Tracking:**

```typescript theme={null}
// Get expiring credentials
const { data } = await supabase
  .from('hr_employee_credentials')
  .select(`
    *,
    employee:hr_employees(first_name, last_name),
    credential_type:hr_credential_types(name)
  `)
  .lte('expiration_date', addDays(new Date(), 30))
  .eq('status', 'active');
```

### 9. Skills & Competencies (6 tables)

| Table                            | Columns | Description             |
| -------------------------------- | ------- | ----------------------- |
| `hr_skills_library`              | 14      | Skill definitions       |
| `hr_employee_skills`             | 12      | Employee skill records  |
| `hr_skills_assessments`          | 16      | Skill evaluations       |
| `hr_competency_frameworks`       | 14      | Competency models       |
| `hr_competency_framework_skills` | 8       | Framework-skill mapping |
| `hr_position_skill_requirements` | 10      | Position requirements   |

### 10. Positions (1 table)

| Table          | Columns | Description              |
| -------------- | ------- | ------------------------ |
| `hr_positions` | 22      | Job position definitions |

**Position Fields:**

* `title`, `description`, `department_id`
* `pay_grade`, `min_salary`, `max_salary`
* `reports_to_position_id` (hierarchy)
* `headcount_authorized`, `headcount_filled`

### 11. Employee Relations (4 tables)

| Table                     | Columns | Description            |
| ------------------------- | ------- | ---------------------- |
| `hr_disciplinary_actions` | 20      | Disciplinary records   |
| `hr_grievances`           | 18      | Employee grievances    |
| `hr_incidents`            | 22      | Workplace incidents    |
| `hr_investigations`       | 20      | Investigation tracking |

**ER Workflow:**

```
hr_incidents → hr_investigations → hr_disciplinary_actions
hr_grievances → hr_investigations
```

### 12. Workload Planning (8 tables)

| Table                           | Columns | Description                 |
| ------------------------------- | ------- | --------------------------- |
| `hr_workload_drivers`           | 16      | Workload factor definitions |
| `hr_workload_driver_versions`   | 12      | Driver version history      |
| `hr_workload_calculations`      | 18      | FTE calculations            |
| `hr_workload_forecasts`         | 16      | Future workload projections |
| `hr_workload_scenarios`         | 14      | What-if scenarios           |
| `hr_workload_approvals`         | 12      | Approval workflow           |
| `hr_workload_accuracy_tracking` | 14      | Forecast vs actual          |
| `hr_workload_pto_patterns`      | 12      | PTO impact analysis         |

### 13. Module Settings (1 table)

| Table                | Columns | Description             |
| -------------------- | ------- | ----------------------- |
| `hr_module_settings` | 46      | HR module configuration |

### 14. ATS Enhanced (10 tables)

| Table                             | Columns | Description                             |
| --------------------------------- | ------- | --------------------------------------- |
| `hr_references`                   | 28      | Reference request tracking and feedback |
| `hr_reference_emails`             | 12      | Reference email audit log               |
| `hr_background_check_webhook_dlq` | 14      | Failed webhook dead letter queue        |
| `hr_background_check_providers`   | 16      | BGC provider configuration              |
| `hr_communication_templates`      | 18      | Email/SMS templates with variables      |
| `hr_candidate_communications`     | 20      | Communication tracking and delivery     |
| `hr_candidate_portal_accounts`    | 16      | Portal authentication accounts          |
| `hr_candidate_portal_sessions`    | 10      | Portal JWT sessions                     |
| `hr_job_board_integrations`       | 18      | Job board API configuration             |
| `hr_job_board_postings`           | 16      | Job posting sync tracking               |

**Reference Checking Workflow:**

```text theme={null}
hr_references → hr_reference_emails (audit)
     ↓
Request → Sent → Viewed → Completed
     ↓
Ratings & Feedback stored in hr_references
```

**Background Check DLQ Pattern:**

```text theme={null}
Webhook fails → hr_background_check_webhook_dlq
     ↓
Retry (up to 3) → Success/Abandoned
```

**Key Settings:**

| Setting                      | Type       | Description         |
| ---------------------------- | ---------- | ------------------- |
| `default_pto_accrual_rate`   | decimal    | Annual PTO accrual  |
| `overtime_threshold_hours`   | decimal    | Weekly OT threshold |
| `require_timesheet_approval` | boolean    | Approval workflow   |
| `credential_alert_days`      | integer\[] | Alert thresholds    |
| `payroll_approval_required`  | boolean    | Payroll approval    |

***

## Common Query Patterns

### Get Employee with Related Data

```typescript theme={null}
const { data } = await supabase
  .from('hr_employees')
  .select(`
    *,
    department:pf_departments(name),
    position:hr_positions(title),
    manager:hr_employees!manager_id(first_name, last_name),
    credentials:hr_employee_credentials(
      *,
      credential_type:hr_credential_types(name)
    )
  `)
  .eq('id', employeeId)
  .single();
```

### Get Team Members

```typescript theme={null}
const { data } = await supabase
  .from('hr_team_members')
  .select(`
    employee:hr_employees(
      id, first_name, last_name, email,
      position:hr_positions(title)
    )
  `)
  .eq('team_id', teamId);
```

### Leave Balance Check

```typescript theme={null}
const { data } = await supabase
  .from('hr_employee_leave_balances')
  .select('*')
  .eq('employee_id', employeeId)
  .eq('leave_type', 'pto')
  .single();
```

***

## RLS Policies

HR tables use layered RLS:

1. Organization isolation via `organization_id`
2. Manager access to direct reports
3. Self-service for employee's own data
4. HR admin access for sensitive operations

**Helper Functions:**

* `hr_user_can_view_employee()` - View permission check
* `hr_user_can_edit_employee()` - Edit permission check
* `hr_user_is_manager_of()` - Manager relationship check

***

## Sensitive Data Handling

| Column                       | Protection              |
| ---------------------------- | ----------------------- |
| `hr_employees.ssn_encrypted` | Column-level encryption |
| `hr_employees.date_of_birth` | RLS restricted          |
| `hr_pay_rates.hourly_rate`   | RLS restricted          |
| `hr_disciplinary_actions.*`  | Manager/HR only         |

***

## See Also

* [HR Module Spec](https://github.com/Encore-OS/encoreos/tree/development/specs/hr)
* [Database Schema Overview](/architecture/DATABASE_SCHEMA)
* [Workforce Platform Integration](/architecture/integrations/PLATFORM_INTEGRATION_LAYERS)