Home A - Z
Page Index

Commit a360ae

2025-10-08 13:53:02 Vinay Deokar: ERD updated and it's description too
Projects/HireGenius/Technical Document (LLD+HLD)).md ..
@@ 413,7 413,7 @@
### 4. Database Design
#### 4.1 Entity Relationship Diagram (ERD)
- ![](./image-1759857909487.png)
+ ![](./image-1759931553297.png)
---
#### 4.2 Table Specifications
@@ 425,7 425,8 @@
|---------------|-------------|--------------------------------------|-------------------------------|
| user_id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique user identifier |
| email | VARCHAR(255)| UNIQUE, NOT NULL | User email (login credential) |
- | password_hash | VARCHAR(255)| NOT NULL | bcrypt hashed password |
+ | refresh_token | VARCHAR(255)| | Refresh token for session |
+ | access_token | VARCHAR(255)| | Access token for session |
| full_name | VARCHAR(255)| NOT NULL | User's full name |
| role | VARCHAR(50) | NOT NULL, DEFAULT 'recruiter' | User role (admin, recruiter) |
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Account creation time |
@@ 449,7 450,6 @@
| start_date | DATE | NOT NULL | Job posting start date |
| end_date | DATE | NOT NULL | Job posting end date |
| status | VARCHAR(50) | DEFAULT 'active', CHECK (status IN ('active', 'paused', 'closed')) | Job status |
- | min_score | DECIMAL(3,2)| DEFAULT 0.70, CHECK (min_score BETWEEN 0 AND 1) | Minimum match score for contact |
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Job creation time |
| updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Last update time |
@@ 458,9 458,6 @@
- `idx_jobs_dates` on `(start_date, end_date)` (date range queries)
- `idx_jd_embedding` on `jd_embedding USING hnsw (vector_cosine_ops)` (fast similarity search)
- **Constraints:**
- - `CHECK (end_date >= start_date)` (valid date range)
-
---
##### Table: candidates
@@ 469,25 466,18 @@
| Column | Type | Constraints | Description |
|------------------|-------------|-----------------------------------------------------|----------------------------------------------|
| candidate_id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique candidate identifier |
- | job_id | UUID | FOREIGN KEY REFERENCES jobs(job_id) ON DELETE CASCADE | Associated job |
| full_name | VARCHAR(255)| NOT NULL | Candidate's full name |
| email | VARCHAR(255)| NOT NULL | Candidate's email |
| phone | VARCHAR(50) | | Candidate's phone number |
| resume_text | TEXT | NOT NULL | Full resume text (plain) |
| resume_embedding | vector(1536)| NOT NULL | Semantic embedding of resume |
| parsed_data | JSONB | | Structured resume data (skills, experience, education) |
- | match_score | DECIMAL(5,4)| | Final match score (0-1) |
- | ai_reasoning | TEXT | | AI explanation for ranking |
- | status | VARCHAR(50) | DEFAULT 'new', CHECK (status IN ('new', 'contacted', 'replied', 'interviewed', 'rejected', 'hired')) | Candidate status |
| resume_hash | VARCHAR(64) | UNIQUE, NOT NULL | SHA-256 hash for deduplication |
| source | VARCHAR(50) | DEFAULT 'email' | Source of resume (email, upload) |
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Resume received time |
| updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Last update time |
**Indexes:**
- - `idx_candidates_job` on `job_id` (fetch candidates by job)
- - `idx_candidates_job_score` on `(job_id, match_score DESC)` (ranked retrieval)
- - `idx_candidates_status` on `status` (filter by status)
- `idx_candidates_email` on `email` (find by email)
- `idx_candidates_hash` on `resume_hash` (deduplication check)
- `idx_resume_embedding` on `resume_embedding USING hnsw (vector_cosine_ops)` (vector search)
@@ 514,9 504,30 @@
"total_experience_years": 5
}
```
+
+ ##### Table: job_candidate_matches
+ **Purpose:** Store matches between jobs and candidates (many-to-many relationship)
+
+ | Column | Type | Constraints | Description |
+ |---------------|-------------|------------------------------------------|---------------------------------|
+ | match_id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique match identifier |
+ | job_id | UUID | FOREIGN KEY REFERENCES jobs(job_id) | Associated job |
+ | candidate_id | UUID | FOREIGN KEY REFERENCES candidates(candidate_id) | Associated candidate |
+ | match_score | DECIMAL(5,4)| | AI-generated match score (0-1) |
+ | ai_reasoning | TEXT | | AI explanation for match |
+ | status | VARCHAR(50) | DEFAULT 'new', CHECK (status IN ('new','contacted','replied','interviewed','rejected','hired')) | Candidate-job status |
+ | created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Match creation time |
+ | updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Last update time |
+
+ **Indexes:**
+ - `idx_matches_job` on `job_id`
+ - `idx_matches_candidate` on `candidate_id`
+ - `idx_matches_score` on `(job_id, match_score DESC)` (ranked retrieval)
+
---
+
##### Table: communication
- **Purpose:** Log all email communications
+ **Purpose:** Log all email communications with candidates
| Column | Type | Constraints | Description |
|--------------|-------------|----------------------------------------------------------------------------|--------------------------------------|
@@ 535,64 546,18 @@
---
- ##### Table: processing_logs
- **Purpose:** Audit trail for resume processing
-
- | Column | Type | Constraints | Description |
- |-------------------|-------------|------------------------------------------------------------------------------|-----------------------------------|
- | log_id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique log ID |
- | job_id | UUID | FOREIGN KEY REFERENCES jobs(job_id) ON DELETE CASCADE | Associated job |
- | resume_hash | VARCHAR(64) | NOT NULL | Hash of processed resume |
- | status | VARCHAR(50) | NOT NULL, CHECK (status IN ('success', 'failed', 'duplicate', 'skipped')) | Processing outcome |
- | error_message | TEXT | | Error details if failed |
- | processing_time_ms| INTEGER | | Time taken to process |
- | processed_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Processing time |
-
- **Indexes:**
- - `idx_logs_job` on `job_id` (fetch logs by job)
- - `idx_logs_hash` on `resume_hash` (deduplication check)
- - `idx_logs_status` on `status` (filter by status)
-
- **Purpose of Deduplication:**
- - Prevent reprocessing same resume across multiple jobs
- - Track which resumes have been processed
- - Maintain audit trail
-
- ---
-
- ##### Table: scheduled_tasks
- **Purpose:** Manage cron jobs and scheduled tasks
-
- | Column | Type | Constraints | Description |
- |----------------|-------------|----------------------------------------------------------------------------|----------------------------------------------|
- | task_id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique task ID |
- | job_id | UUID | FOREIGN KEY REFERENCES jobs(job_id) ON DELETE CASCADE | Associated job |
- | task_type | VARCHAR(50) | NOT NULL, CHECK (task_type IN ('ingestion', 'ranking', 'response_tracking')) | Type of task |
- | scheduled_time | TIMESTAMP | NOT NULL | When task should execute |
- | status | VARCHAR(50) | DEFAULT 'pending', CHECK (status IN ('pending', 'executing', 'completed', 'failed')) | Task status |
- | executed_at | TIMESTAMP | | Actual execution time |
- | error_message | TEXT | | Error details if failed |
-
- **Indexes:**
- - `idx_tasks_job` on `job_id` (fetch tasks by job)
- - `idx_tasks_status` on `status` (find pending tasks)
- - `idx_tasks_scheduled` on `scheduled_time` (time-based queries)
-
- ---
-
#### 4.3 Database Relationships
- | Parent Table | Child Table | Relationship Type | Foreign Key | On Delete |
- |--------------|-----------------|-----------------|------------------------------|-----------|
- | users | jobs | One-to-Many | jobs.created_by → users.user_id | SET NULL |
- | jobs | candidates | One-to-Many | candidates.job_id → jobs.job_id | CASCADE |
- | candidates | communication | One-to-Many | communication.candidate_id → candidates.candidate_id | CASCADE |
- | jobs | processing_logs | One-to-Many | processing_logs.job_id → jobs.job_id | CASCADE |
- | jobs | scheduled_tasks | One-to-Many | scheduled_tasks.job_id → jobs.job_id | CASCADE |
+ | Parent Table | Child Table | Relationship Type | Foreign Key | On Delete |
+ |--------------|---------------------|-----------------|-----------------------------------------------|-----------|
+ | users | jobs | One-to-Many | jobs.created_by → users.user_id | SET NULL |
+ | jobs | job_candidate_matches | One-to-Many | job_candidate_matches.job_id → jobs.job_id | CASCADE |
+ | candidates | job_candidate_matches | One-to-Many | job_candidate_matches.candidate_id → candidates.candidate_id | CASCADE |
+ | candidates | communication | One-to-Many | communication.candidate_id → candidates.candidate_id | CASCADE |
**Cascade Deletion Logic:**
- - When a job is deleted, all associated candidates, logs, and tasks are deleted
- - When a candidate is deleted, all associated communications are deleted
+ - When a job is deleted, all associated job-candidate matches are deleted
+ - When a candidate is deleted, all associated job-candidate matches and communications are deleted
- When a user is deleted, their jobs remain but `created_by` is set to NULL
---
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