Home A - Z
Page Index

Commit 908f5b

2025-11-09 04:48:20 Vinay Deokar: ERD updated for interview scheduling
Projects/HireGenius/Technical Document (LLD+HLD)).md ..
@@ 441,8 441,7 @@
### 4. Database Design
#### 4.1 Entity Relationship Diagram (ERD)
- ![](./image-1760421542881.png)
-
+ ![](./image-1762662452833.png)
---
#### 4.2 Table Specifications
@@ 555,24 554,90 @@
- `idx_matches_score` on `(job_id, match_score DESC)` (ranked retrieval)
---
+ #### Table: managers
+
+ **Purpose:** Store manager profiles responsible for interviews or job coordination.
- ##### Table: communication
- **Purpose:** Log all email communications with candidates
-
- | Column | Type | Constraints | Description |
- |--------------|-------------|----------------------------------------------------------------------------|--------------------------------------|
- | comm_id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique communication ID |
- | candidate_id | UUID | FOREIGN KEY REFERENCES candidates(candidate_id) ON DELETE CASCADE | Associated candidate |
- | type | VARCHAR(50) | NOT NULL, CHECK (type IN ('outreach', 'follow_up', 'reply', 'manual')) | Communication type |
- | subject | VARCHAR(500)| | Email subject |
- | body | TEXT | | Email body |
- | sent_at | TIMESTAMP | | Email sent time |
- | status | VARCHAR(50) | DEFAULT 'sent' | Delivery status |
- | created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Log creation time |
+ | Column | Type | Constraints | Description |
+ | ---------- | ------------ | -------------------------------------- | --------------------------------- |
+ | id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique manager identifier |
+ | name | VARCHAR(255) | NOT NULL | Manager's full name |
+ | email | VARCHAR(255) | UNIQUE, NOT NULL | Manager's email address |
+ | skills | JSONB | DEFAULT '[]' | List of skills or expertise areas |
+ | department | VARCHAR(255) | | Department or function area |
+ | created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Record creation time |
+ | updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Record last update time |
+
+ **Indexes:**
+ - `idx_managers_email` on `email`
+ ---
+ #### Table: manager_availabilities
+
+ **Purpose:** Track availability slots of managers for scheduling interviews.
+
+ | Column | Type | Constraints | Description |
+ | --------------- | --------- | ----------------------------------------------------- | ------------------------------------- |
+ | availability_id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique availability record identifier |
+ | manager_id | UUID | FOREIGN KEY REFERENCES managers(id) ON DELETE CASCADE | Manager reference |
+ | date | DATE | NOT NULL | Available date |
+ | start_time | TIME | NOT NULL | Start time of availability |
+ | end_time | TIME | NOT NULL | End time of availability |
+ | is_booked | BOOLEAN | DEFAULT false | Marks if slot is already booked |
+ | created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Record creation time |
+ | updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Record last update time |
+
+ **Indexes:**
+
+ - `idx_manager_availabilities_manager_id` on `manager_id`
+ - `idx_manager_availabilities_date` on `date`
+ ---
+ #### Table: job_managers
+
+ **Purpose:** Link managers to specific jobs with defined roles (e.g., hiring manager, interviewer).
+
+ | Column | Type | Constraints | Description |
+ | ---------- | ------------ | ----------------------------------------------------- | ------------------------------------------------- |
+ | id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique record identifier |
+ | job_id | UUID | FOREIGN KEY REFERENCES jobs(job_id) ON DELETE CASCADE | Job reference |
+ | manager_id | UUID | FOREIGN KEY REFERENCES managers(id) ON DELETE CASCADE | Manager reference |
+ | role | VARCHAR(100) | NOT NULL | Role in hiring process (e.g., interviewer, owner) |
+ | created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Record creation time |
+ | updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Record last update time
+
+ - `idx_job_managers_job_id` on `job_id`
+ - `idx_job_managers_manager_id` on `manager_id`
+ ---
+ #### Table: job_managers
+
+ **Purpose:** Link managers to specific jobs with defined roles (e.g., hiring manager, interviewer).
+ ---
+ ##### Table: communication
+ **Purpose:** Store all communication records between candidates, managers, and the system (emails, invites, responses, confirmations).
+
+ | Column | Type | Constraints | Description |
+ | --------------- | ------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------- |
+ | comm_id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique communication identifier |
+ | candidate_id | UUID | FOREIGN KEY REFERENCES candidates(candidate_id) ON DELETE CASCADE | Related candidate |
+ | job_id | UUID | FOREIGN KEY REFERENCES jobs(job_id) ON DELETE SET NULL | Related job |
+ | manager_id | UUID | FOREIGN KEY REFERENCES managers(id) ON DELETE SET NULL | Related manager (if applicable) |
+ | type | ENUM | CHECK (type IN ('candidate_invite','candidate_response','manager_invite','manager_response','final_confirmation')) | Type of communication |
+ | subject | VARCHAR(255) | | Email or message subject |
+ | body | TEXT | | Message body |
+ | slot_date | DATE | | Interview slot date |
+ | slot_start_time | TIME | | Slot start time |
+ | slot_end_time | TIME | | Slot end time |
+ | status | ENUM | DEFAULT 'pending_candidate_response', CHECK (status IN ('pending_candidate_response','candidate_confirmed','pending_manager_response','manager_confirmed','confirmed','declined')) | Current communication status |
+ | gmail_thread_id | VARCHAR(255) | | Gmail thread reference (for sync) |
+ | sent_at | TIMESTAMP | | Time message was sent |
+ | received_at | TIMESTAMP | | Time message was received |
+ | created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Record creation time |
+ | updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Record last update time |
**Indexes:**
- - `idx_comm_candidate` on `candidate_id` (fetch communication history)
- - `idx_comm_type` on `type` (filter by type)
+ - `idx_communication_status` on `status`
+ - `idx_communication_candidate_id` on `candidate_id`
+ - `idx_communication_manager_id` on `manager_id`
+ - `idx_communication_job_id` on `job_id`
---
##### Table: templates
0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9