Database Schema

BonardaHR uses PostgreSQL 15+ as its database with Flyway for version-controlled schema migrations.

Core Design Principles

Hybrid Data Model — Fixed columns for core fields + JSONB for configurable employee sections
Audit Trail — All main tables include created_at, updated_at, created_by, updated_by
Soft Deletes — Where appropriate, status flags are used instead of hard deletes
Referential Integrity — Foreign keys with appropriate CASCADE / SET NULL actions
Performance — Indexes on foreign keys, search fields, and JSONB queries (GIN)
Public IDs — Every entity exposes a UUID public_id to clients; the internal BIGSERIAL id is never returned by the API

Entity Relationship Overview

employees (1) ──── (N) employee_field_values
    │                          │
    │ (self-ref)          section_fields
    └── reports_to_id          │
                          employee_sections
    │
    ├── (N) ──── (N) roles (via employee_roles)
    │                  │
    │            roles (N) ──── (N) permissions (via role_permissions)
    │
    ├── (1) ──── (N) time_off_balances ──── time_off_types
    │
    └── (1) ──── (N) time_off_requests ──── time_off_types
                           │
                      reviewer_id ──── employees

Core Tables

`employees`

Core employee information with a fixed schema.

Column	Type	Notes
`id`	`BIGSERIAL PK`	Internal identifier — never exposed via API
`public_id`	`UUID NOT NULL UNIQUE`	API-facing identifier (added in V3)
`first_name`	`VARCHAR(100) NOT NULL`
`last_name`	`VARCHAR(100) NOT NULL`
`email`	`VARCHAR(255) UNIQUE NOT NULL`	Company email
`phone_number`	`VARCHAR(20)`
`position`	`VARCHAR(100)`	Job title
`location`	`VARCHAR(100)`	Office, city, or remote
`birthday`	`DATE`
`hire_date`	`DATE NOT NULL`
`status`	`VARCHAR(20) NOT NULL`	`ACTIVE`, `INACTIVE`, `ON_LEAVE`, `TERMINATED`
`reports_to_id`	`BIGINT FK`	Self-referencing — manager
`microsoft_user_id`	`VARCHAR(255) UNIQUE`	Azure AD user ID for SSO
Audit fields		`version`, `created_at`, `updated_at`, `created_by`, `updated_by`

Indexes: public_id (unique), reports_to_id, email, status, hire_date, microsoft_user_id

Constraints:

fk_employees_reports_to — self-referencing FK ON DELETE SET NULL
chk_employee_status — status must be one of (ACTIVE, INACTIVE, ON_LEAVE, TERMINATED)

Application-computed fields:

tenure — Period between hire_date and today
fullName — concatenation of first_name + ' ' + last_name
effectiveStatus — if stored status is ACTIVE and employee has approved time-off today → displayed as ON_LEAVE

`employee_sections`

Defines configurable section categories that can appear on employee profiles.

Column	Type	Notes
`id`	`BIGSERIAL PK`
`name`	`VARCHAR(100) UNIQUE NOT NULL`	Internal key (e.g. `"payroll"`)
`display_name`	`VARCHAR(100) NOT NULL`	User-facing label (e.g. `"Payroll Information"`)
`description`	`TEXT`
`display_order`	`INTEGER NOT NULL DEFAULT 0`
`is_active`	`BOOLEAN NOT NULL DEFAULT true`
`required_permission`	`VARCHAR(100)`	Permission to view this section on another profile; `NULL` = visible to all

`section_fields`

Field definitions within a section (the schema for dynamic fields).

Each field has a field_type (TEXT, NUMBER, DATE, BOOLEAN, SELECT, MULTI_SELECT), validation_rules (JSONB), and editable_by (SYSTEM, HR_ONLY, EMPLOYEE).

`employee_field_values`

Stores the actual dynamic field values for each employee as JSONB.

Column	Type	Notes
`employee_id`	`BIGINT FK`	Which employee
`field_id`	`BIGINT FK`	Which field definition
`value`	`JSONB`	The stored value

GIN index on value for efficient JSONB queries.

Time Off Tables

`time_off_types`

Defines leave categories (e.g. Annual Leave, Sick Leave, Maternity).

Key columns: name, is_unlimited (boolean), default_days, attachment_requirement (NEVER/ALWAYS/CONDITIONAL), attachment_required_after_days, counts_towards_bradford (boolean — see ADR-026).

`time_off_balances`

Denormalised per-employee, per-type, per-year balance records.

Column	Type	Notes
`employee_id`	`BIGINT FK NOT NULL`
`time_off_type_id`	`BIGINT FK NOT NULL`
`year`	`INTEGER NOT NULL`	Calendar year
`total_allocated`	`NUMERIC(5,1) DEFAULT 0`
`used`	`NUMERIC(5,1) DEFAULT 0`	Approved and consumed days
`pending`	`NUMERIC(5,1) DEFAULT 0`	Days in pending requests (denormalised)
`carry_over`	`NUMERIC(5,1) DEFAULT 0`	Days carried from previous year

Constraints: UNIQUE(employee_id, time_off_type_id, year); CHECK(used + pending <= total_allocated + carry_over)

Application-computed: remaining = total_allocated + carry_over - used - pending

`time_off_requests`

Leave requests with full approval workflow. See HR Workflows for lifecycle details.

Column	Type	Notes
`employee_id`	`BIGINT FK NOT NULL`	Requester
`time_off_type_id`	`BIGINT FK NOT NULL`	Leave type
`start_date`	`DATE NOT NULL`
`end_date`	`DATE NOT NULL`
`half_day`	`BOOLEAN DEFAULT false`
`half_day_period`	`VARCHAR(20)`	`MORNING` or `AFTERNOON`
`business_days`	`NUMERIC(5,1) NOT NULL`	Calculated weekday count; half-day = 0.5
`status`	`VARCHAR(20) DEFAULT 'PENDING'`	`PENDING`, `APPROVED`, `REJECTED`, `CANCELLED`
`reviewer_id`	`BIGINT FK`	Approving/rejecting employee
`review_note`	`TEXT`	Reviewer's comment
`reviewed_at`	`TIMESTAMP`

Indexes: employee_id, status, (start_date, end_date)

Reports & Analytics Tables

`bradford_settings`

Single-row configuration table for Bradford Factor risk thresholds.

Column	Default	Description
`low_threshold`	50	Scores below this are LOW risk
`medium_threshold`	200	Scores below this (and ≥ low) are MEDIUM risk
`high_threshold`	500	Scores below this (and ≥ medium) are HIGH; ≥ high are CRITICAL

Bradford Factor Formula: S² × D

S = number of separate absence spells (approved requests where counts_towards_bradford = true)
D = total days absent
Rolling 52-week window

Reports Performance Indexes (V12)

Index	Table	Purpose
`idx_time_off_requests_bradford`	`time_off_requests`	Bradford queries (approved requests)
`idx_employees_exit_date`	`employees`	Turnover analysis
`idx_time_off_requests_employee_approved`	`time_off_requests`	Per-employee approved requests
`idx_timesheets_status_week`	`timesheets`	Timesheet compliance queries

Migration History

All schema changes go through Flyway migrations in src/main/resources/db/migration/.

Naming convention: V{version}__{description}.sql

Version	File	Purpose
V1	`create_employee_tables.sql`	Core schema: employees, sections, fields, roles, permissions, RBAC, audit
V2	`create_time_off_tables.sql`	Time off types, balances, requests, unlimited leave, attachments
V3	`create_timesheet_tables.sql`	Timesheet management with weekly entries
V4	`create_departments_and_positions.sql`	Departments and positions
V5	`create_document_tables.sql`	Document management with signatures
V6	`create_company_events.sql`	Company-wide events for dashboard
V7	`create_sites.sql`	Site/location management
V8–V10	Various	Notifications, folder conditions, HR oversight
V11	`add_reports_feature.sql`	`counts_towards_bradford` flag, `REPORT_READ` permission
V12	`add_reports_indexes.sql`	Performance indexes for Bradford Factor and reports queries
V13	`add_bradford_settings.sql`	Configurable Bradford Factor thresholds
V14–V19	Various	Time-off attachments, folder conditions, additional features

caution

Never modify an existing migration file. Always create a new V{next}__description.sql for any schema change.

Backup & Recovery

Daily Backups

pg_dump bonarda_hr > backup_$(date +%Y%m%d).sql

Point-in-Time Recovery

PostgreSQL is configured with WAL archiving for PITR capability in production.

Security Considerations

Audit Logging — All changes tracked via created_by / updated_by audit fields
Connection Pooling — HikariCP with prepared statements to prevent SQL injection
Row-Level Security (planned) — For future multi-tenant support
Encrypted Columns (planned) — Sensitive data (SSN, salary) to be encrypted at rest

Monitoring & Maintenance

Key Metrics to Monitor

Table sizes and growth rates
Index usage and bloat
Slow query log analysis
Connection pool utilisation
JSONB field value sizes

Regular Maintenance Schedule

Task	Frequency
`VACUUM ANALYZE`	Weekly
Index rebuild	Quarterly
Statistics update	After bulk operations
Slow query review	Monthly

Time Off Permissions Reference (V4)

Permission	Assigned To
`TIME_OFF_TYPE_CREATE`	ADMIN, HR_MANAGER
`TIME_OFF_TYPE_READ`	ADMIN, HR_MANAGER, MANAGER, EMPLOYEE
`TIME_OFF_TYPE_UPDATE`	ADMIN, HR_MANAGER
`TIME_OFF_TYPE_DELETE`	ADMIN
`TIME_OFF_REQUEST_CREATE`	ADMIN, HR_MANAGER, MANAGER, EMPLOYEE
`TIME_OFF_REQUEST_READ_OWN`	ADMIN, HR_MANAGER, MANAGER, EMPLOYEE
`TIME_OFF_REQUEST_READ_TEAM`	ADMIN, HR_MANAGER, MANAGER
`TIME_OFF_REQUEST_READ_ALL`	ADMIN, HR_MANAGER
`TIME_OFF_REQUEST_APPROVE`	ADMIN, HR_MANAGER, MANAGER
`TIME_OFF_BALANCE_READ_OWN`	ADMIN, HR_MANAGER, MANAGER, EMPLOYEE
`TIME_OFF_BALANCE_READ_ALL`	ADMIN, HR_MANAGER
`TIME_OFF_BALANCE_ADJUST`	ADMIN, HR_MANAGER

Core Design Principles​

Entity Relationship Overview​

Core Tables​

employees​

employee_sections​

section_fields​

employee_field_values​

Time Off Tables​

time_off_types​

time_off_balances​

time_off_requests​

Reports & Analytics Tables​

bradford_settings​

Reports Performance Indexes (V12)​

Migration History​

Backup & Recovery​

Daily Backups​

Point-in-Time Recovery​

Security Considerations​

Monitoring & Maintenance​

Key Metrics to Monitor​

Regular Maintenance Schedule​

Time Off Permissions Reference (V4)​