Database Schema | CHRIS Help Center

Complete reference for all database tables in CHRIS.

Entity Relationship Diagram

erDiagram
    profiles ||--o{ leave_requests : submits
    profiles ||--o{ contracts : has
    profiles }o--|| teams : belongs_to
    profiles }o--|| holiday_schemes : uses

    teams ||--o{ profiles : contains

    leave_requests }o--|| leave_types : has_type
    leave_requests ||--o{ leave_request_attachments : has

    contracts }o--|| contract_types : has_type
    contracts }o--|| currencies : uses
    contracts }o--|| holiday_schemes : uses
    contracts ||--o{ contract_attachments : has

    holiday_schemes ||--o{ holidays : contains

    user_roles }o--|| profiles : assigns_to

Core Tables

profiles

Employee profiles linked to Supabase Auth users.

Column	Type	Nullable	Description
id	uuid	No	Primary key (matches auth.users.id)
full_name	text	No	Employee's full name
email	text	Yes	Email address
status	text	Yes	active, inactive
team_id	uuid	Yes	FK to teams
holiday_scheme_id	uuid	Yes	FK to holiday_schemes
manager_user_id	uuid	Yes	Direct manager
annual_entitlement_days	integer	Yes	Leave days per year
carryover_days	integer	Yes	Days carried from last year
employment_start_date	date	Yes	Start of employment
contract_type	text	Yes	Employment type
avatar_url	text	Yes	Profile picture URL
cv_file_path	text	Yes	Path to uploaded CV
cv_file_name	text	Yes	Original CV filename
cv_uploaded_at	timestamp	Yes	CV upload timestamp
emergency_contact	jsonb	Yes	Emergency contact details
hr_notes	text	Yes	Notes visible to HR only
salary_band	text	Yes	Salary band classification
created_at	timestamp	No	Creation timestamp
updated_at	timestamp	No	Last update timestamp

Relationships:

team_id → teams.id
holiday_scheme_id → holiday_schemes.id

teams

Team structure with leaders.

Column	Type	Nullable	Description
id	uuid	No	Primary key
name	text	No	Team name
lead_user_id	uuid	Yes	FK to profiles (team leader)
created_at	timestamp	No	Creation timestamp
updated_at	timestamp	No	Last update timestamp

leave_requests

Employee leave requests.

Column	Type	Nullable	Description
id	uuid	No	Primary key
user_id	uuid	No	FK to profiles (requester)
leave_type_id	uuid	No	FK to leave_types
start_date	date	No	First day of leave
end_date	date	No	Last day of leave
working_days	integer	No	Calculated working days
status	leave_status	Yes	pending, approved, rejected, cancelled
reason	text	Yes	Employee's reason
approver_user_id	uuid	Yes	Who approved/rejected
approver_comment	text	Yes	Approver's comment
approved_at	timestamp	Yes	Decision timestamp
entitled_days	integer	Yes	Days entitled at request time
adds_entitlement	boolean	Yes	If request adds to entitlement
created_at	timestamp	No	Creation timestamp
updated_at	timestamp	No	Last update timestamp

Relationships:

user_id → profiles.id
leave_type_id → leave_types.id

leave_types

Types of leave available.

Column	Type	Nullable	Description
id	uuid	No	Primary key
code	text	No	Unique code (annual_leave, sick_short)
name_hr	text	No	Croatian name
name_en	text	No	English name
is_active	boolean	Yes	If type is available
max_days_per_year	integer	Yes	Maximum allowed per year
requires_documentation	boolean	Yes	If documents needed
allows_attachments	boolean	Yes	If attachments allowed
created_at	timestamp	No	Creation timestamp
updated_at	timestamp	No	Last update timestamp

contracts

Employment contracts.

Column	Type	Nullable	Description
id	uuid	No	Primary key
employee_id	uuid	No	FK to profiles
contract_type_id	uuid	No	FK to contract_types
currency_id	uuid	No	FK to currencies
holiday_scheme_id	uuid	Yes	FK to holiday_schemes
position	text	Yes	Job title
start_date	date	No	Contract start
end_date	date	Yes	Contract end (null = ongoing)
rate_type	text	No	hourly, monthly, yearly
rate_amount	numeric	No	Salary/rate value
yearly_leave_days	integer	No	Annual leave entitlement
created_at	timestamp	No	Creation timestamp
updated_at	timestamp	No	Last update timestamp

Relationships:

employee_id → profiles.id
contract_type_id → contract_types.id
currency_id → currencies.id
holiday_scheme_id → holiday_schemes.id

Supporting Tables

holiday_schemes

Holiday scheme definitions.

Column	Type	Nullable	Description
id	uuid	No	Primary key
name	text	No	Scheme name
country	text	No	Country code
year	integer	No	Calendar year
description	text	Yes	Optional description
is_active	boolean	No	If scheme is active
created_at	timestamp	No	Creation timestamp
updated_at	timestamp	No	Last update timestamp

holidays

Individual public holidays.

Column	Type	Nullable	Description
id	uuid	No	Primary key
holiday_scheme_id	uuid	Yes	FK to holiday_schemes
date	date	No	Holiday date
name_hr	text	No	Croatian name
name_en	text	No	English name
country	text	Yes	Country code
year	integer	No	Year
is_public	boolean	Yes	If public holiday
created_at	timestamp	No	Creation timestamp

translations

Internationalization key-value pairs.

Column	Type	Nullable	Description
id	uuid	No	Primary key
key	text	No	Unique translation key
croatian	text	No	Croatian translation
english	text	No	English translation
russian	text	No	Russian translation
hindi	text	No	Hindi translation
category	text	Yes	Category for organization
created_at	timestamp	No	Creation timestamp
updated_at	timestamp	No	Last update timestamp

user_roles

Role assignments for users.

Column	Type	Nullable	Description
id	uuid	No	Primary key
user_id	uuid	No	FK to auth.users
role	app_role	No	admin, hr_manager, employee
created_at	timestamp	No	Creation timestamp

audit_logs

Change tracking for compliance.

Column	Type	Nullable	Description
id	uuid	No	Primary key
actor_user_id	uuid	No	Who made the change
entity_type	text	No	Table name
entity_id	uuid	No	Record ID
action	text	No	create, update, delete
old_values	jsonb	Yes	Previous state
new_values	jsonb	Yes	New state
created_at	timestamp	No	Change timestamp

company_settings

Global application settings.

Column	Type	Nullable	Description
id	uuid	No	Primary key
company_name	text	No	Organization name
country	text	No	Country code
time_zone	text	No	Timezone
fiscal_year_start	text	No	Fiscal year start month
working_days_per_week	integer	No	Work days (usually 5)
working_hours_per_day	integer	No	Work hours (usually 8)
leave_policy	text	Yes	Leave policy document
smtp_enabled	boolean	Yes	Email enabled
smtp_host	text	Yes	SMTP server
smtp_port	integer	Yes	SMTP port
smtp_username	text	Yes	SMTP user
smtp_password	text	Yes	SMTP password (encrypted)
smtp_from_email	text	Yes	Sender email
smtp_from_name	text	Yes	Sender name
smtp_use_tls	boolean	Yes	Use TLS
email_reroute_enabled	boolean	Yes	Reroute all emails
email_reroute_addresses	text	Yes	Reroute destination
created_at	timestamp	No	Creation timestamp
updated_at	timestamp	No	Last update timestamp

Enums

app_role

User roles in the system.

Value	Description
admin	Full system access
hr_manager	HR operations, all employees
employee	Own profile and requests

leave_status

Leave request workflow states.

Value	Description
pending	Awaiting approval
approved	Request approved
rejected	Request denied
cancelled	Cancelled by employee

attachment_visibility

Who can view attachments.

Value	Description
employee	Visible to employee
hr_only	Only HR and admin

Database Functions

get_user_role(_user_id uuid)

Returns the role for a given user.

SELECT get_user_role('user-uuid-here');
-- Returns: 'admin', 'hr_manager', or 'employee'

has_role(_user_id uuid, _role app_role)

Checks if user has a specific role.

SELECT has_role('user-uuid', 'admin');
-- Returns: true or false

is_team_leader(_user_id uuid)

Checks if user leads any team.

SELECT is_team_leader('user-uuid');
-- Returns: true or false

get_team_leadership(_user_id uuid)

Returns teams led by user.

SELECT * FROM get_team_leadership('user-uuid');
-- Returns: team_id, team_name