PeopleHub Documentation

Database Design ​

Overview ​

PeopleHub uses PostgreSQL 17.5 on AWS RDS Multi-AZ with 140+ tables supporting the complete HRMS data model.

Database Configuration ​

  • Engine: PostgreSQL 17.5
  • Deployment: RDS Multi-AZ (automatic failover)
  • Region: ap-south-1 (Mumbai)
  • Encryption: Enabled (AWS KMS)
  • Backup: Automated daily snapshots + point-in-time recovery (1-35 days)
  • Connection: Via standard PostgreSQL protocol (port 5432)

Schema Organization ​

Core Entity Tables ​

User & Authentication:

  • user_master - User accounts, login credentials
  • role - System roles
  • permission - Available permissions
  • role_permission - Role-permission mapping

Company & Organization:

  • company_master - Company/tenant information
  • customer_master - Customers/clients
  • agency_master - Recruitment agencies
  • data_group - Master data categories
  • master_data - Master data values (departments, designations, etc.)

Employee Management:

  • user_employment_details - Employment information
  • user_onboarding - Onboarding status and data
  • onboarding_references - Referee information
  • project_allocation - Employee project assignments

Recruitment:

  • demand_master - Job requisitions
  • demand_skill - Required skills for jobs
  • demand_document - Job-related documents
  • employee_demand_status - Candidate applications

Leave & Attendance:

  • leave_application (implied) - Leave requests
  • leave_balance (implied) - Employee leave balances
  • attendance_policy (implied) - Attendance rules

Performance:

  • performance_cycle - Review periods
  • okr - Objectives and key results
  • okr_goal - Individual goals
  • sub_goal - Goal sub-tasks
  • okr_review - Review records
  • okr_change_log - Goal change history
  • rating_scale - Performance rating definitions
  • performance_stage - Review stages

Separation:

  • separation_request - Resignation/termination requests
  • separation_approval - Approval workflows
  • separation_document - Exit-related documents
  • separation_change_log - Status changes
  • exit_survey_question, exit_survey_response, exit_survey_answer - Exit surveys

Skills & Talent:

  • skill - Skill definitions
  • skill_group - Skill groupings
  • skill_cluster - Higher-level skill categories
  • skill_adjacency - Related skills
  • skill_prerequisite - Skill dependencies
  • certifications - Employee certifications
  • certification_providers - Cert issuing bodies

Policies & Configuration:

  • policy - Company policies
  • company_holiday - Holiday calendar
  • company_work_policy - Work hours and schedules
  • confirmation_policy_master - Probation period rules
  • notice_period_config - Notice period settings

Notifications:

  • notification_definition - Notification types
  • notification_schedule - Scheduled notifications
  • notification_recipient - Recipient rules
  • notification_channel_map - Channel configuration

Workflow & Approvals:

  • request - Generic approval requests
  • request_approval - Approval steps
  • request_escalation_map - Escalation rules

Support:

  • support_ticket - Helpdesk tickets
  • support_ticket_comment - Ticket discussions
  • support_ticket_attachment - Attached files
  • support_ticket_sla - SLA tracking

Surveys:

  • survey - Survey definitions
  • onboarding_survey_question, onboarding_survey_response, onboarding_survey_answer - Onboarding feedback

Tasks (Onboarding):

  • it_form_task - IT provisioning tasks
  • facility_admin_task - Admin tasks

Clearance (Separation):

  • it_noc, admin_noc, finance_noc, hr_noc, manager_noc, employee_noc - No objection certificates

Data Relationships ​

Primary Relationships ​

  • User → Employee (one-to-one)
  • Employee → Manager (self-referencing hierarchy)
  • Company → Departments/Locations (one-to-many)
  • Employee → Project Allocations (many-to-many via allocation table)
  • Demand (Job) → Employee Applications (one-to-many)
  • Performance Cycle → OKRs → Goals (hierarchical)

Cross-Module Relationships ​

  • Notifications reference various entities (employee, leave, performance, etc.)
  • Documents can be linked to employees, onboarding, separation, etc.
  • Workflows support multiple modules (leave, separation, confirmation)

Indexing Strategy ​

Indexes created on:

  • Primary keys (automatic)
  • Foreign keys (for join performance)
  • Frequently queried columns (employee status, dates)
  • Unique constraints (email, employee ID)

Data Integrity ​

  • Foreign Keys: Enforce referential integrity
  • Not Null Constraints: Required fields enforced at database level
  • Unique Constraints: Prevent duplicate emails, employee IDs
  • Check Constraints: Validate data ranges and enums
  • Triggers: Audit trail automation, timestamp updates

Schema Evolution ​

Migration Tool: Drizzle Kit Process:

  1. Define schema changes in TypeScript (Drizzle)
  2. Generate migration SQL
  3. Review and test migration
  4. Apply to dev → staging → production

Backward Compatibility: Maintain for at least one deployment cycle

Database Performance ​

Connection Pooling:

  • Each Lambda maintains 10 connections
  • RDS max connections configured based on instance size

Query Optimization:

  • Drizzle ORM generates efficient SQL
  • Indexes on join columns
  • Pagination for large result sets

Monitoring:

  • RDS Performance Insights enabled
  • Slow query logging
  • CloudWatch metrics (connections, CPU, IOPS)

Security ​

Access Control:

  • Main API: Full access
  • Candidate API: Limited to onboarding tables
  • Read-only access for reporting (future)

Encryption:

  • At rest: AWS KMS encryption
  • In transit: SSL/TLS enforced
  • Sensitive fields: Additional application-level encryption (planned for PII)

Backup & Recovery:

  • Automated daily snapshots
  • 30-day retention
  • Cross-region backup replication (planned)
  • Point-in-time recovery (PITR) up to 35 days

Planned Enhancements ​

  • Read Replicas: For reporting and analytics
  • Cross-Region Replication: For global offices (7 locations)
  • Partitioning: For large tables (audit logs, notifications)
  • Materialized Views: For complex reports

Schema Documentation ​

Full database schema diagram will be provided by the database team separately.