Home A - Z
Page Index

Commit 2d5d42

2025-11-09 06:02:15 Vinay Deokar: updated ERD for communications, interview scheduling tables
Projects/HireGenius/Technical Document (LLD+HLD)).md ..
@@ 441,7 441,7 @@
### 4. Database Design
#### 4.1 Entity Relationship Diagram (ERD)
- ![](./image-1762662452833.png)
+ ![](./image-1762666823208.png)
---
#### 4.2 Table Specifications
@@ 554,7 554,7 @@
- `idx_matches_score` on `(job_id, match_score DESC)` (ranked retrieval)
---
- #### Table: managers
+ ##### Table: managers
**Purpose:** Store manager profiles responsible for interviews or job coordination.
@@ 592,7 592,7 @@
- `idx_manager_availabilities_manager_id` on `manager_id`
- `idx_manager_availabilities_date` on `date`
---
- #### Table: job_managers
+ ##### Table: job_managers
**Purpose:** Link managers to specific jobs with defined roles (e.g., hiring manager, interviewer).
@@ 606,39 606,71 @@
| 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).
+ - `idx_job_managers_manager_id` on `manager_id`
---
##### 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 |
+
+ **Purpose:** Stores all communication exchanges between the system, candidates, and managers- including interview invites, responses, and final confirmations.
+
+ | Column | Type | Constraints | Description |
+ | ----------------- | ------------ | ----------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
+ | `comm_id` | UUID | PK | Unique communication identifier |
+ | `candidate_id` | UUID | FK → `candidates.candidate_id`, `CASCADE` | Candidate reference |
+ | `job_id` | UUID | FK → `jobs.job_id`, `SET NULL` | Associated job |
+ | `manager_id` | UUID | FK → `managers.id`, `SET NULL` | Manager reference |
+ | `type` | ENUM | NOT NULL | Type of communication:<br>• `candidate_invite`<br>• `candidate_response`<br>• `manager_invite`<br>• `manager_response`<br>• `final_confirmation` |
+ | `subject` | VARCHAR(500) | | Email subject line |
+ | `body` | TEXT | | Full email/message content |
+ | `slot_date` | DATE | NULLABLE | Date of the interview slot |
+ | `slot_start_time` | TIME | NULLABLE | Start time of the slot |
+ | `slot_end_time` | TIME | NULLABLE | End time of the slot |
+ | `unique_hash` | STRING | NULLABLE | Hash used to prevent duplicate communications for same slot |
+ | `status` | ENUM | DEFAULT: `pending_candidate_response` | Current communication state:<br>• `pending_candidate_response`<br>• `candidate_confirmed`<br>• `pending_manager_response`<br>• `manager_confirmed`<br>• `confirmed`<br>• `declined` |
+ | `gmail_thread_id` | STRING | | Gmail thread tracking ID |
+ | `sent_at` | TIMESTAMP | | When email was sent |
+ | `received_at` | TIMESTAMP | | When response was received |
+ | `created_at` | TIMESTAMP | DEFAULT: `CURRENT_TIMESTAMP` | Record creation timestamp |
+ | `updated_at` | TIMESTAMP | DEFAULT: `CURRENT_TIMESTAMP` | Record last updated timestamp |
+
**Indexes:**
- `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`
+ - `idx_communication_gmail_thread_id` on `gmail_thread_id`
+ ---
+ ##### Table: interview_schedule
+
+ **Purpose:** Maintains the record of interview schedules created for candidates against specific jobs and managers.
+ Each record represents a confirmed or proposed interview slot with associated meeting details.
+ Prevents scheduling conflicts for the same candidate in overlapping slots.
+
+ | **Column** | **Type** | **Constraints** | **Description** |
+ | ------------------- | ------------------------------------------------------------------------ | ------------------------------------------------------------------------- | ----------------------------------------------- |
+ | `interview_id` | `UUID` | **PK**, Default: `UUIDV4` | Unique identifier for each interview |
+ | `job_id` | `UUID` | **FK → jobs.job_id**, `CASCADE` on delete/update | Associated job for which interview is scheduled |
+ | `candidate_id` | `UUID` | **FK → candidates.candidate_id**, `CASCADE` on delete/update | Candidate being interviewed |
+ | `manager_id` | `UUID` | **FK → managers.id**, `CASCADE` on delete/update | Manager conducting or managing the interview |
+ | `comm_id` | `UUID` | **FK → communication.comm_id**, `SET NULL` on delete, `CASCADE` on update | References the related communication thread |
+ | `slot_date` | `DATEONLY` | **NOT NULL** | Date of the scheduled interview |
+ | `slot_start_time` | `TIME` | **NOT NULL** | Interview start time |
+ | `slot_end_time` | `TIME` | **NOT NULL** | Interview end time |
+ | `meeting_link` | `STRING(500)` | NULLABLE | Meeting URL (Google Meet, Zoom, Teams, etc.) |
+ | `meeting_platform` | `ENUM('google_meet', 'zoom', 'teams')` | **NOT NULL**, Default: `google_meet` | Platform used for the meeting |
+ | `calendar_event_id` | `STRING(255)` | NULLABLE | External calendar event identifier |
+ | `status` | `ENUM('proposed', 'scheduled', 'rescheduled', 'cancelled', 'completed')` | **NOT NULL**, Default: `proposed` | Current interview status |
+ | `created_by` | `UUID` | **FK → users.user_id**, `SET NULL` on delete, `CASCADE` on update | User who created the interview record |
+ | `notes` | `TEXT` | NULLABLE | Optional notes or remarks |
+ | `created_at` | `TIMESTAMP` | Default: `CURRENT_TIMESTAMP` | Record creation timestamp |
+ | `updated_at` | `TIMESTAMP` | Default: `CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP` | Record last updated timestamp |
+ **Indexes:**
+ - `idx_interview_job_id` on `job_id`
+ - `idx_interview_candidate_id` on `candidate_id`
+ - `idx_interview_manager_id` on `manager_id`
+ - `idx_interview_slot_date` on `slot_date`
+ - `idx_interview_status` on `status`
+
---
##### Table: templates
**Purpose:** Store user-specific email templates for communication
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