Database reference

Schema documentation

Complete data model for the HRMS platform: multi-tenant organizations, workforce records, payroll, billing, recruitment, and integrations. Use this reference when designing APIs, migrations, or reports.

Tables: 34
Columns: 428
Domains: 8

How tables connect

organizations is the tenant root. Nearly every table includes organization_id.
users handle authentication; employees optionally link via user_id.
departments, designations, and locations structure the workforce; employees.manager_id forms a self-referential hierarchy.
Recruitment flows from job_descriptions → jobs → job_applications with pipeline_stages and history tables for audit.
Billing uses global billing_plans; each org has subscriptions and invoices.

Organization & access

Tenant boundary, company profile, and authenticated users who operate the HRMS.

organizations

Root tenant record. Every other business table is scoped by organization_id.

15 columns

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
name	varchar
email	varchar
phone	varchar
website	varchar
industry	varchar
company_size	varchar
street_address	varchar
city	varchar
state	varchar
country	varchar
postal_code	varchar
logo_url	varchar
created_at	datetime
updated_at	datetime

users

17 columns1 FK

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
first_name	varchar
last_name	varchar
email Unique	varchar	Unique
phone	varchar
password_hash	varchar
role	varchar
status	varchar
is_admin	boolean
email_notifications	boolean
marketing_opt_in	boolean
two_factor_enabled	boolean
last_login	datetime
preferences	json
created_at	datetime
updated_at	datetime

Workforce structure

Org units, physical sites, roles, teams, and the employee master record.

departments

Organizational units within a company.

7 columns2 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
name	varchar
description	text
manager_id employees.id Department head	int	employees.id Department head
created_at	datetime
updated_at	datetime

locations

Offices, branches, or remote hubs used for attendance and assignment.

11 columns1 FK

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
name	varchar
address	varchar
city	varchar
state	varchar
country	varchar
postal_code	varchar
type	varchar
created_at	datetime
updated_at	datetime

designations

Job titles or grades, optionally tied to a department.

7 columns2 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
name	varchar
description	text
department_id departments.id	int	departments.id
created_at	datetime
updated_at	datetime

teams

Cross-functional or project teams with an optional people manager.

7 columns2 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
name	varchar
manager_id employees.id	int	employees.id
description	text
created_at	datetime
updated_at	datetime

team_members

Many-to-many membership between teams and employees.

5 columns2 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
team_id teams.id	int	teams.id
employee_id employees.id	int	employees.id
role	varchar
joined_at	datetime

employees

Canonical HR record: personal, employment, payroll, and compliance fields.

43 columns6 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
user_id users.id	int	users.id
employee_code Unique	varchar	Unique
first_name	varchar
last_name	varchar
email	varchar
personal_email	varchar
phone	varchar
dob	date
gender	varchar
marital_status	varchar
blood_group	varchar
nationality	varchar
department_id departments.id	int	departments.id
designation_id designations.id	int	designations.id
employment_type	varchar
joining_date	date
work_location	varchar
location_id locations.id	int	locations.id
shift	varchar
branch	varchar
manager_id employees.id Self-referential reporting line	int	employees.id Self-referential reporting line
status	varchar
address	varchar
city	varchar
state	varchar
postal_code	varchar
pan_number	varchar
bank_name	varchar
account_number	varchar
ifsc_code	varchar
ctc	decimal
salary	decimal
emergency_contact_name	varchar
emergency_contact_relation	varchar
emergency_contact_phone	varchar
profile_photo_url	varchar
resume_url	varchar
pan_card_url	varchar
aadhaar_card_url	varchar
created_at	datetime
updated_at	datetime

employee_documents

Uploaded files and metadata attached to an employee profile.

7 columns2 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
employee_id employees.id	int	employees.id
type	varchar
url	varchar
description	text
uploaded_by users.id	int	users.id
uploaded_at	datetime

Payroll

Monthly payroll runs and per-employee line items.

payroll_batches

A payroll cycle for a given month and year for the organization.

13 columns2 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
name	varchar
month	int
year	int
generated_on	datetime
pay_date	date
total_employees	int
total_amount	decimal
status	varchar
created_by users.id	int	users.id
created_at	datetime
updated_at	datetime

payroll_entries

Computed pay for one employee within a batch.

15 columns2 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
payroll_batch_id payroll_batches.id	int	payroll_batches.id
employee_id employees.id	int	employees.id
attendance_days	int
overtime_hours	decimal
leaves	int
base_salary	decimal
deductions	decimal
bonus	decimal
custom_fields	json
net_salary	decimal
status	varchar
notes	text
created_at	datetime
updated_at	datetime

Finance & expenses

Company spend tracking with hierarchical categories.

expense_categories

Nested chart of expense types per organization.

7 columns2 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
name	varchar
description	text
parent_category_id expense_categories.id Self-referential parent	int	expense_categories.id Self-referential parent
created_at	datetime
updated_at	datetime

expenses

Individual expense claims or company purchases.

16 columns3 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
category_id expense_categories.id	int	expense_categories.id
title	varchar
description	text
amount	decimal
currency	varchar
expense_date	date
status	varchar
added_by users.id	int	users.id
vendor	varchar
payment_method	varchar
receipt_url	varchar
approval_date	date
created_at	datetime
updated_at	datetime

Billing & subscriptions

SaaS plans, tenant subscriptions, invoices, and stored payment methods.

billing_plans

Global catalog of subscription tiers and feature flags.

10 columns

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
name	varchar
description	text
cycle	varchar
price	decimal
currency	varchar
status	varchar
features	json
created_at	datetime
updated_at	datetime

subscriptions

Active or historical plan assignment for an organization.

13 columns3 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
plan_id billing_plans.id	int	billing_plans.id
start_date	date
end_date	date
next_billing_date	date
amount	decimal
currency	varchar
status	varchar
billing_email	varchar
payment_method_id payment_methods.id	int	payment_methods.id
created_at	datetime
updated_at	datetime

invoices

Billing documents generated from subscriptions.

13 columns2 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
subscription_id subscriptions.id	int	subscriptions.id
invoice_number	varchar
invoice_date	date
due_date	date
amount	decimal
currency	varchar
status	varchar
payment_method	varchar
transaction_reference	varchar
created_at	datetime
updated_at	datetime

payment_methods

Tokenized cards or wallets saved for billing.

12 columns2 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
user_id users.id	int	users.id
provider	varchar
card_brand	varchar
card_last4	varchar
expiration_month	int
expiration_year	int
billing_address	varchar
status	varchar
created_at	datetime
updated_at	datetime

Operations

In-app messaging, leave management, and calendar events.

inbox_messages

Internal notifications and messages tied to users or employees.

13 columns3 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
sender_name	varchar
sender_email	varchar
recipient_user_id users.id	int	users.id
subject	varchar
body	text
type	varchar
status	varchar
unread	boolean
related_employee_id employees.id	int	employees.id
created_at	datetime
updated_at	datetime

leave_requests

Time-off requests with approval workflow.

14 columns3 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
employee_id employees.id	int	employees.id
leave_type	varchar
start_date	date
end_date	date
total_days	int
status	varchar
reason	text
manager_id employees.id	int	employees.id
requested_at	datetime
approved_at	datetime
created_at	datetime
updated_at	datetime

meeting_events

Scheduled meetings with participants stored as JSON.

13 columns2 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
title	varchar
description	text
start_time	datetime
end_time	datetime
organizer_id users.id	int	users.id
participants	json
location	varchar
meeting_link	varchar
status	varchar
created_at	datetime
updated_at	datetime

Recruitment & ATS

Job descriptions, postings, candidates, applications, pipelines, and interviews.

job_descriptions

Authoring workspace for JD content before publishing as a job.

31 columns3 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
creator_id users.id	int	users.id
title	varchar
department_id departments.id	int	departments.id
experience_level	varchar
employment_type	varchar
work_mode	varchar
role_focus	varchar
role_level	varchar
company_stage	varchar
jd_style	varchar
location	varchar
salary_range	varchar
openings	int
notice_period	varchar
education	varchar
industry	varchar
reporting_to	varchar
interview_process	varchar
experience_years	varchar
hiring_priority	varchar
shift_timing	varchar
skills	json
technical_stack	json
benefits	json
manual_description	text
published	boolean
published_at	datetime
created_at	datetime
updated_at	datetime

jobs

Published requisitions candidates can apply to.

32 columns4 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
job_description_id job_descriptions.id	int	job_descriptions.id
title	varchar
company_name	varchar
department_id departments.id	int	departments.id
location	varchar
job_type	varchar
salary_range	varchar
openings	int
views_count	int
applicants_count	int
match_score	int
status	varchar
description	text
responsibilities	text
requirements	text
benefits	text
experience_level	varchar
education	varchar
industry	varchar
notice_period	varchar
interview_process	varchar
hiring_priority	varchar
work_mode	varchar
employment_type	varchar
reporting_to	varchar
created_by users.id	int	users.id
published_at	datetime
closed_at	datetime
created_at	datetime
updated_at	datetime

job_skills

Required or preferred skills for a job posting.

4 columns1 FK

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
job_id jobs.id	int	jobs.id
name	varchar
proficiency	varchar

job_benefits

Benefit lines associated with a job.

3 columns1 FK

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
job_id jobs.id	int	jobs.id
benefit	varchar

candidates

Talent pool records independent of a specific application.

15 columns1 FK

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
first_name	varchar
last_name	varchar
email	varchar
phone	varchar
location	varchar
current_company	varchar
current_ctc	decimal
expected_ctc	decimal
experience_years	decimal
resume_url	varchar
profile_summary	text
created_at	datetime
updated_at	datetime

candidate_skills

Skills extracted or entered for a candidate.

4 columns1 FK

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
candidate_id candidates.id	int	candidates.id
skill	varchar
proficiency	varchar

job_applications

Links a candidate to a job with pipeline stage and score.

13 columns4 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
job_id jobs.id	int	jobs.id
candidate_id candidates.id	int	candidates.id
assigned_recruiter_id users.id	int	users.id
stage_id pipeline_stages.id	int	pipeline_stages.id
status	varchar
score	int
source	varchar
applied_at	datetime
last_updated	datetime
resume_url	varchar
cover_letter	text
notes	text

pipeline_stages

Reusable stage definitions for hiring workflows.

10 columns1 FK

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
name	varchar
description	text
default_steps	json
order_index	int
stage_type	varchar
is_active	boolean
created_at	datetime
updated_at	datetime

hiring_pipelines

Pipeline instance bound to a specific job requisition.

8 columns3 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
name	varchar
job_id jobs.id	int	jobs.id
created_by users.id	int	users.id
status	varchar
created_at	datetime
updated_at	datetime

pipeline_stage_assignments

Ordered stages within a hiring pipeline.

6 columns2 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
pipeline_id hiring_pipelines.id	int	hiring_pipelines.id
stage_id pipeline_stages.id	int	pipeline_stages.id
sequence	int
custom_steps	json
is_custom	boolean

candidate_stage_history

Audit trail when an application moves between stages.

8 columns3 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
application_id job_applications.id	int	job_applications.id
stage_id pipeline_stages.id	int	pipeline_stages.id
changed_by users.id	int	users.id
from_status	varchar
to_status	varchar
note	text
changed_at	datetime

interview_schedules

Interview slots linking candidates, jobs, and interviewers.

14 columns5 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
candidate_id candidates.id	int	candidates.id
job_application_id job_applications.id	int	job_applications.id
interviewer_id employees.id	int	employees.id
scheduled_at	datetime
duration_minutes	int
mode	varchar
meeting_link	varchar
status	varchar
notes	text
created_by users.id	int	users.id
created_at	datetime
updated_at	datetime

Integrations & automation

Third-party connections and workflow automations.

integrations

Connected external systems with sync metadata.

10 columns1 FK

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
name	varchar
category	varchar
status	varchar
connection_metadata	json
last_sync_at	datetime
automations_count	int
created_at	datetime
updated_at	datetime

automation_workflows

Trigger/action rules driven by integration events.

12 columns2 FKs

Column	Type	Constraints
id PK Auto-increment	int	PK Auto-increment
organization_id organizations.id	int	organizations.id
integration_id integrations.id	int	integrations.id
name	varchar
trigger_event	varchar
actions	json
status	varchar
success_rate	decimal
pending_actions	int
last_run_at	datetime
created_at	datetime
updated_at	datetime

How tables connect

Quick jump

Organization & access

Workforce structure

Payroll

Finance & expenses

Billing & subscriptions

Operations

Recruitment & ATS

Integrations & automation