Home A - Z
Page Index

Blame

4e31e8 Vinay Deokar 2025-10-07 17:26:31 1 
# Technical Document (LLD/HLD)
b78094 Vinay Deokar 2025-10-08 09:39:55 2
## HireGenius- AI-Powered Recruitment Automation System
f45619 Meenakshi 2025-10-06 11:44:54 3
4
### 📘 Table of Contents
4e31e8 Vinay Deokar 2025-10-07 17:26:31 5
b78094 Vinay Deokar 2025-10-08 09:39:55 6
| # | Section |
7
|---|---------|
8
| 1 | [System Overview](#1-system-overview) |
9
| 2 | [Module Breakdown](#2-module-breakdown) |
10
| 3 | [API Design](#3-api-design) |
11
| 4 | [Database Design](#4-database-design) |
12
| 5 | [Business Workflow](#5-business-workflow) |
13
| 6 | [System Architecture](#6-system-architecture) |
14
| 7 | [Sequence Diagram](#7-sequence-diagram) |
15
| 8 | [Flow Diagram](#8-flow-diagram) |
232e44 Vinay Deokar 2025-11-13 10:58:32 16
| 9 | [Phase Two Enhancement](#9-phase-two-enhancements) |
17
f45619 Meenakshi 2025-10-06 11:44:54 18
19
b78094 Vinay Deokar 2025-10-08 09:39:55 20
### 1. System Overview
21
22
#### 1.1 🎯 Purpose
f45619 Meenakshi 2025-10-06 11:44:54 23
Automate the recruitment pipeline from resume ingestion to candidate ranking using **NLP-based parsing**, **vector embeddings**, and **AI-powered re-ranking**.
24
25
---
26
b78094 Vinay Deokar 2025-10-08 09:39:55 27
#### 1.2 ⚙️ Key Capabilities
f45619 Meenakshi 2025-10-06 11:44:54 28
- **Incremental Resume Ingestion:** Fetch new resumes from Gmail on schedule
29
- **Automated Resume Parsing:** Extract structured data (skills, experience, education, contact)
30
- **Semantic Matching:** Use embeddings to find similar candidates to job descriptions
31
- **AI Re-Ranking:** Re-rank top candidates using GPT-4o-mini for better precision
32
- **Dashboard:** HR views ranked candidates with explainability
33
34
---
35
b78094 Vinay Deokar 2025-10-08 09:39:55 36
#### 1.3 🧰 Technology Stack
f45619 Meenakshi 2025-10-06 11:44:54 37
| Layer | Technology |
38
|--------|-------------|
39
| **Frontend** | React.js + Tailwind CSS |
40
| **Backend** | Node.js + Express.js |
41
| **Database** | PostgreSQL 15+ with pgvector extension |
42
| **AI/ML** | OpenAI GPT-4o-mini |
43
| **Embeddings** | OpenAI `text-embedding-3-small` |
44
| **Email Integration** | Gmail API with OAuth 2.0 |
45
| **Scheduler** | node-cron |
0f7f3b Vinay Deokar 2025-10-07 10:42:15 46
b78094 Vinay Deokar 2025-10-08 09:39:55 47
### 2. Module Breakdown
0f7f3b Vinay Deokar 2025-10-07 10:42:15 48
bb8e75 Vinay Deokar 2025-10-14 07:36:54 49
#### Module 1: Unified Resume Ingestion Module
50
**Responsibility:** Multi-source resume ingestion with pluggable adapters
51
52
| Source Type | Adapter | Trigger Method | Priority | Notes |
53
|-----------------|-------------------|--------------------|---------|-------|
54
| Gmail | GmailAdapter | Scheduled (cron) | High | Phase 1 implementation |
55
| Webhook | WebhookAdapter | Real-time (HTTP POST)| High | Future source |
56
| Cloud Storage | CloudStorageAdapter | Polling/Event-driven | Medium | Future source |
57
| Direct Upload | UploadAdapter | On-demand (UI) | Medium | Future source |
58
| ATS Integration | ATSAdapter | API polling | Low | Future source |
59
60
---
61
62
###### Core Functions
63
```javascript
64
// Abstract base adapter
65
class ResumeSourceAdapter {
66
async fetchResumes(config) → Returns ResumeCollection
67
async validateSource(config) → Returns boolean
68
async getSourceMetadata() → Returns SourceInfo
69
}
70
71
// Unified ingestion controller
72
fetchResumesFromAllSources(jobId) → Orchestrates all active sources
73
registerSource(jobId, sourceType, config) → Activates new source
74
deactivateSource(jobId, sourceId) → Stops fetching from source
75
getActiveSourcesForJob(jobId) → Returns list of active sources
76
```
77
##### Phase 1: Gmail Module
0f7f3b Vinay Deokar 2025-10-07 10:42:15 78
**Responsibility:** Email integration and resume fetching
79
80
**Functions:**
81
- Authenticate with Gmail API using OAuth 2.0
82
- Fetch emails with attachments based on filters (date, keywords, labels)
83
- Download resume attachments (PDF, DOCX)
84
- Track last fetch timestamp for incremental processing
85
86
**Key Operations:**
87
- `fetchNewEmails(afterDate, filters)` → Returns array of email objects with attachments
88
- `downloadAttachment(messageId, attachmentId)` → Returns file buffer
89
- `getLastFetchTime(jobId)` → Returns timestamp of last successful fetch
90
bb8e75 Vinay Deokar 2025-10-14 07:36:54 91
**External Dependencies:** Gmail API, Google OAuth 2.0
0f7f3b Vinay Deokar 2025-10-07 10:42:15 92
93
---
94
b78094 Vinay Deokar 2025-10-08 09:39:55 95
#### Module 2: Resume Parser Module
0f7f3b Vinay Deokar 2025-10-07 10:42:15 96
**Responsibility:** Extract structured data from resume text
97
98
**Parsing Strategy:** Rule-based NLP with regex patterns
99
100
**Name Extraction:** First line heuristics, capitalization patterns
101
**Email Extraction:** Regex pattern `[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}`
102
**Phone Extraction:** Regex for various formats (+1-XXX-XXX-XXXX, (XXX) XXX-XXXX)
103
**Skills Extraction:** Keyword matching against predefined skill database
104
**Experience Parsing:** Section detection (keywords: "experience", "work history") + date parsing
105
**Education Parsing:** Section detection + degree/institution extraction
106
107
**Functions:**
108
- `extractText(fileBuffer, format)` → Returns plain text from PDF/DOCX
109
- `parseResume(resumeText)` → Returns structured JSON object
110
- `validateParsedData(data)` → Returns boolean + error messages
111
- `normalizeSkills(skillArray)` → Returns deduplicated, lowercase skills
112
113
**Output Format (JSON):**
114
```json
115
{
116
"name": "John Doe",
117
"email": "john@example.com",
118
"phone": "+1-555-0123",
119
"skills": ["javascript", "python", "sql"],
120
"experience": [
121
{
122
"title": "Software Engineer",
123
"company": "Tech Corp",
124
"duration": "2020-2023",
125
"description": "Built REST APIs..."
126
}
127
],
128
"education": [
129
{
130
"degree": "B.S. Computer Science",
131
"institution": "MIT",
132
"year": 2018
133
}
134
],
135
"total_experience_years": 5
136
}
137
```
138
---
139
b78094 Vinay Deokar 2025-10-08 09:39:55 140
#### Alternative AI Approach (Optional Enhancement)
0f7f3b Vinay Deokar 2025-10-07 10:42:15 141
Instead of rule-based parsing, use GPT-4o-mini with structured JSON output. The flow would be: extract plain text -> send it to GPT-4o-mini with a schema-enforced prompt -> receive structured JSON (name, email, phone, skills, experience, education, total years).
142
143
**Pros:**
144
- Much higher accuracy across different resume formats and layouts
145
- Handles inconsistent structures, missing sections, and varied wording
146
- Reduces need for complex regex/heuristics
147
148
**Cons:**
149
- Incur API usage costs
150
- Slightly higher latency (~2–5 seconds per resume, depending on length)
151
- Requires API reliability and internet access
291453 Vinay Deokar 2025-10-07 10:48:54 152
-
153
---
b78094 Vinay Deokar 2025-10-08 09:39:55 154
#### Module 3: Embedding Module
291453 Vinay Deokar 2025-10-07 10:48:54 155
**Responsibility:** Generate vector embeddings for semantic search
156
157
**Embedding Strategy:**
158
- Use OpenAI text-embedding-3-small model (1536 dimensions)
159
- Embed full resume text (truncated to 8000 chars if needed)
160
- Embed JD text once per job creation
161
- Store vectors in PostgreSQL pgvector columns
162
163
**Functions:**
164
- `generateEmbedding(text)` -> Returns 1536-dimensional float array
165
- `batchGenerateEmbeddings(textArray)` -> Returns array of vectors (for efficiency)
166
- `calculateCosineSimilarity(vec1, vec2)` -> Returns similarity score (0-1)
167
168
**Caching Strategy:**
169
- Cache JD embeddings in memory (job lifecycle)
170
- Resume embeddings stored permanently in database
171
172
---
173
b78094 Vinay Deokar 2025-10-08 09:39:55 174
#### Module 4: Database Module
291453 Vinay Deokar 2025-10-07 10:48:54 175
**Responsibility:** Data persistence and vector operations
176
177
**Core Operations:**
178
- CRUD for all entities (users, jobs, candidates, communications)
179
- Vector similarity search using pgvector
180
- Transaction management for atomic operations
181
- Query optimization with proper indexing
182
183
**Key Functions:**
184
- `storeCandidate(candidateData)` -> Returns candidateId
185
- `findSimilarCandidates(jdEmbedding, topK)` -> Returns top-K candidate IDs by cosine similarity
186
- `updateCandidateScore(candidateId, score)` -> Updates match score
187
- `getCandidatesByJob(jobId, filters)` -> Returns paginated candidate list
188
189
**Vector Search Query (Conceptual):**
190
- Use pgvector's `<=>` operator for cosine distance
191
- Order by `1 - (jd_embedding <=> resume_embedding)` for similarity
192
- Apply filters (status, score range) after vector search
193
194
---
195
b78094 Vinay Deokar 2025-10-08 09:39:55 196
#### Module 5: Job Description Module
291453 Vinay Deokar 2025-10-07 10:48:54 197
**Responsibility:** JD lifecycle management and candidate retrieval
198
199
**Functions:**
200
- `createJob(jobData)` -> Validates input, generates embedding, stores in DB
201
- `getActiveJobs()` -> Returns list of jobs with status='active'
202
- `findTopCandidates(jobId, topK)` -> Queries DB for top-K similar candidates
203
- `closeJob(jobId)` -> Updates status, stops scheduled tasks
204
205
**JD Processing Flow:**
206
- HR submits JD text via UI
207
- Validate JD (length, required fields)
208
- Generate embedding for JD text
209
- Store job with embedding in database
210
- Schedule resume ingestion task (cron)
211
- Return job ID to frontend
212
213
**Embedding Generation:**
214
- Extract key requirements from JD text
215
- Generate single embedding vector (1536-dim)
216
- Store in `jobs.jd_embedding` column
217
232e44 Vinay Deokar 2025-11-13 10:58:32 218
---Business
291453 Vinay Deokar 2025-10-07 10:48:54 219
b78094 Vinay Deokar 2025-10-08 09:39:55 220
#### Module 6: Ranking Module
291453 Vinay Deokar 2025-10-07 10:48:54 221
**Responsibility:** AI-powered re-ranking of top candidates
222
223
**Two-Stage Ranking:**
224
225
**Stage 1: Vector Similarity (Fast)**
226
- Use pgvector to retrieve top-50 candidates by cosine similarity
227
- Initial filter based on semantic matching
228
- Execution time: <100ms
229
230
**Stage 2: AI Re-Ranking (Precise)**
231
- Use GPT-4o-mini to re-rank top-50 -> top-10
232
- Provide full context: JD text + parsed resume data
233
- AI considers: skill match, experience relevance, education fit
234
- Execution time: 2-3 seconds
235
236
**Functions:**
237
- `getTopKSimilar(jobId, k)` -> Returns top-K candidates from vector search
238
- `reRankWithAI(candidates, jdText)` -> Returns re-ranked list with scores and reasoning
239
- `calculateFinalScore(candidate, job)` -> Combines vector similarity + AI score
240
241
**AI Re-Ranking Prompt Structure:**
242
**System:** You are an expert technical recruiter evaluating candidate fit.
243
244
**Input:**
245
- Job Description: [JD text]
246
- Candidates: [Array of parsed resume data]
247
248
**Task:**
249
Rank these candidates from best to worst fit. For each, provide:
250
1. Rank position (1-N)
232e44 Vinay Deokar 2025-11-13 10:58:32 251
2. Fit score (0-1) Business
291453 Vinay Deokar 2025-10-07 10:48:54 252
3. Brief reasoning (2-3 sentences)
253
254
**Consider:**
255
- Skill alignment with required skills
256
- Experience level match
257
- Domain relevance
258
- Education requirements
259
260
**Output Format:** JSON array ordered by rank
261
262
**Scoring Algorithm:**
263
- Initial Vector Similarity: `S_vec` (from pgvector)
264
- AI Re-Ranking Score: `S_ai` (from GPT-4o-mini)
265
266
**Final Score:** `Final Score = 0.4 × S_vec + 0.6 × S_ai`
267
268
**Reasoning:** Vector similarity provides broad semantic match, AI re-ranking adds nuanced understanding of requirements.
269
270
---
271
b78094 Vinay Deokar 2025-10-08 09:39:55 272
#### Module 7: Application Controller
291453 Vinay Deokar 2025-10-07 10:48:54 273
**Responsibility:** Orchestrate workflow across modules
274
275
**Core Workflows:**
276
277
**Resume Processing Workflow**
278
- **Trigger:** Cron job (every 4 hours)
279
- **Steps:** Fetch emails → Extract text → Parse → Embed → Store
280
281
**Candidate Ranking Workflow**
282
- **Trigger:** HR requests candidates for a job
283
- **Steps:** Vector search → AI re-rank → Return ranked list
284
285
**Deduplication Workflow**
286
- **Trigger:** Before storing new candidate
287
- **Steps:** Hash resume text → Check DB → Skip if exists
288
289
**Functions:**
290
- `processNewResumes(jobId)` -> Orchestrates resume processing
291
- `getRankedCandidates(jobId)` -> Orchestrates ranking workflow
292
- `handleDuplication(resumeHash)` -> Checks and logs duplicates
293
294
**Error Handling:**
295
- Retry logic for API failures (3 attempts with exponential backoff)
296
- Log all errors with context (job ID, resume ID, error message)
297
- Continue processing remaining resumes on individual failures
298
299
---
300
b78094 Vinay Deokar 2025-10-08 09:39:55 301
#### Module 8: HR UI Module
291453 Vinay Deokar 2025-10-07 10:48:54 302
**Responsibility:** Provide API endpoints for frontend dashboard
303
304
**API Endpoints:** Detailed in Section 3
305
306
**Dashboard Views:**
307
- Job listing with candidate counts
308
- Candidate pipeline (New, Contacted, Replied, Interviewed)
309
- Candidate detail with parsed data and match score
310
- Communication history per candidate
311
312
---
14faef Vinay Deokar 2025-10-07 17:02:56 313
b78094 Vinay Deokar 2025-10-08 09:39:55 314
### 3. API Design
14faef Vinay Deokar 2025-10-07 17:02:56 315
b78094 Vinay Deokar 2025-10-08 09:39:55 316
#### 3.1 Authentication Endpoints
14faef Vinay Deokar 2025-10-07 17:02:56 317
318
| Method | Endpoint | Request Body | Response | Description |
319
|--------|----------------|-------------------|--------------|--------------------------|
320
| POST | /api/auth/login | {email, password} | {user, token} | User login, returns JWT |
321
| POST | /api/auth/logout | {token} | {success: true} | Invalidate token |
322
| GET | /api/auth/me | Headers: Authorization: Bearer <token> | {user} | Get current user info |
323
324
**Authentication Flow:**
325
- User submits credentials
326
- Server validates against database (bcrypt password comparison)
327
- Generate JWT with 24-hour expiry
328
- Return token to client
329
- Client includes token in all subsequent requests
330
331
---
332
232e44 Vinay Deokar 2025-11-13 10:58:32 333
#### 3.2 Job Management Endpoints### 9. Phase Two Enhancements (AI-assisted Interview Scheduler)
334
824
335
14faef Vinay Deokar 2025-10-07 17:02:56 336
337
| Method | Endpoint | Request Body | Response | Description |
338
|--------|-----------------|-------------------------------------------|---------------------------|--------------------------------------------|
339
| POST | /api/jobs | {title, jd_text, start_date, end_date, min_score} | {job_id, jd_embedding, ...} | Create job, generates embedding |
340
| GET | /api/jobs | Query: ?status=active&page=1&limit=20 | {jobs[], total, page} | List jobs with pagination |
341
| GET | /api/jobs/:id | - | {job, candidate_count} | Get job details |
342
| PUT | /api/jobs/:id | {title?, jd_text?, min_score?} | {success: true} | Update job (re-generates embedding if JD changed) |
343
| DELETE | /api/jobs/:id | - | {success: true} | Close job, stop cron tasks |
344
345
**Request Example (POST /api/jobs):**
346
```json
347
{
348
"title": "Senior Full Stack Developer",
349
"jd_text": "We are seeking an experienced full stack developer with 5+ years of experience in React, Node.js, and PostgreSQL. The ideal candidate will have strong problem-solving skills and experience with microservices architecture...",
350
"start_date": "2025-10-01",
351
"end_date": "2025-12-31",
352
"min_score": 0.70
353
}
354
```
355
**Response Example:**
356
```json
357
{
358
"job_id": "uuid-123",
359
"title": "Senior Full Stack Developer",
360
"jd_embedding": [0.123, -0.456, ...], // 1536 dimensions
361
"status": "active",
362
"created_at": "2025-10-02T10:00:00Z"
363
}
364
```
365
---
366
232e44 Vinay Deokar 2025-11-13 10:58:32 367
#### 3.3 Candidate Retrieval Endpoint### 9. Phase Two Enhancements (AI-assisted Interview Scheduler)
368
824
369
s
370
Business
14faef Vinay Deokar 2025-10-07 17:02:56 371
| Method | Endpoint | Request Body | Response | Description |
372
|--------|------------------------------|-------------------------------------------|---------------------------|-----------------------------------|
373
| GET | /api/jobs/:jobId/candidates | Query: ?status=new&sort=score&page=1&limit=20 | {candidates[], total} | Get ranked candidates for job |
374
| GET | /api/candidates/:id | - | {candidate, parsed_data, communications[]} | Get candidate details |
375
| PUT | /api/candidates/:id/status | {status} | {success: true} | Update candidate status |
376
| POST | /api/candidates/:id/notes | {note_text} | {note_id} | Add HR notes |
377
378
**Request Example (GET /api/jobs/:jobId/candidates):**
379
```json
380
{
381
"job_id": "uuid-123",
382
"status": "new",
383
"sort": "score",
384
"page": 1,
385
"limit": 20
386
}
387
```
388
**Response Example:**
389
```json
390
{
391
"candidates": [
392
{
393
"candidate_id": "uuid-456",
394
"full_name": "John Doe",
395
"email": "john@example.com",
396
"match_score": 0.87,
397
"status": "new",
398
"parsed_data": {
399
"skills": ["javascript", "react", "node.js", "postgresql"],
400
"total_experience_years": 6
401
},
402
"ai_reasoning": "Strong technical alignment with 5/6 required skills. Experience exceeds minimum requirement.",
403
"created_at": "2025-10-02T12:30:00Z"
404
}
405
],
406
"total": 45,
407
"page": 1,
408
"limit": 20
409
}
410
411
```
412
---
413
b78094 Vinay Deokar 2025-10-08 09:39:55 414
#### 3.4 Resume Processing Endpoints (Internal)
14faef Vinay Deokar 2025-10-07 17:02:56 415
416
| Method | Endpoint | Request Body | Response | Description |
417
|--------|----------------------------------|-------------|---------------------------|-----------------------------------|
418
| POST | /api/internal/process-resumes/:jobId | - | {processed_count, errors[]} | Manually trigger resume processing (admin only) |
419
| GET | /api/internal/processing-logs/:jobId | Query: ?status=success&page=1 | {logs[], total} | View processing logs |
420
421
---
b78094 Vinay Deokar 2025-10-08 09:39:55 422
#### 3.5 Dashboard & Analytics Endpoints
14faef Vinay Deokar 2025-10-07 17:02:56 423
424
| Method | Endpoint | Request Body | Response | Description |
425
|--------|------------------------------------|-------------|---------------------------|-------------------------------|
426
| GET | /api/dashboard/stats/:jobId | - | {total, by_status{}, avg_score} | Get job pipeline metrics |
427
| GET | /api/dashboard/timeline/:jobId | Query: ?start_date&end_date | {timeline_data[]} | Candidate activity over time |
428
| GET | /api/export/candidates/:jobId | Query: ?format=csv | CSV file | Export candidates |
232e44 Vinay Deokar 2025-11-13 10:58:32 429
### 9. Phase Two Enhancements (AI-assisted Interview Scheduler)
430
824
14faef Vinay Deokar 2025-10-07 17:02:56 431
432
**Response Example (GET /api/dashboard/stats/:jobId):**
433
```json
434
{
435
"total_candidates": 120,
436
"by_status": {
437
"new": 45,
438
"contacted": 50,
439
"replied": 20,
440
"interviewed": 3,
441
"rejected": 2
442
},
443
"avg_match_score": 0.74,
444
"top_skills": ["javascript", "python", "react"],
445
"last_updated": "2025-10-02T14:00:00Z"
446
}
447
```
448
---
449
### 4. Database Design
450
451
#### 4.1 Entity Relationship Diagram (ERD)
2d5d42 Vinay Deokar 2025-11-09 06:02:15 452
![](./image-1762666823208.png)
14faef Vinay Deokar 2025-10-07 17:02:56 453
---
454
b78094 Vinay Deokar 2025-10-08 09:39:55 455
#### 4.2 Table Specifications
14faef Vinay Deokar 2025-10-07 17:02:56 456
b78094 Vinay Deokar 2025-10-08 09:39:55 457
##### Table: users
14faef Vinay Deokar 2025-10-07 17:02:56 458
**Purpose:** Store HR user accounts
459
460
| Column | Type | Constraints | Description |
461
|---------------|-------------|--------------------------------------|-------------------------------|
462
| user_id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique user identifier |
463
| email | VARCHAR(255)| UNIQUE, NOT NULL | User email (login credential) |
a360ae Vinay Deokar 2025-10-08 13:53:02 464
| refresh_token | VARCHAR(255)| | Refresh token for session |
465
| access_token | VARCHAR(255)| | Access token for session |
14faef Vinay Deokar 2025-10-07 17:02:56 466
| full_name | VARCHAR(255)| NOT NULL | User's full name |
467
| role | VARCHAR(50) | NOT NULL, DEFAULT 'recruiter' | User role (admin, recruiter) |
468
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Account creation time |
469
| updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Last update time |
470
471
**Indexes:**
472
- `idx_users_email` on `email` (for login queries)
473
474
---
475
b78094 Vinay Deokar 2025-10-08 09:39:55 476
##### Table: jobs
14faef Vinay Deokar 2025-10-07 17:02:56 477
**Purpose:** Store job descriptions and automation settings
478
479
| Column | Type | Constraints | Description |
480
|---------------|-------------|------------------------------------------|---------------------------------------|
481
| job_id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique job identifier |
482
| created_by | UUID | FOREIGN KEY REFERENCES users(user_id) | Job creator |
483
| title | VARCHAR(255)| NOT NULL | Job title |
484
| jd_text | TEXT | NOT NULL | Full job description |
485
| jd_embedding | vector(1536)| NOT NULL | Semantic embedding of JD |
486
| start_date | DATE | NOT NULL | Job posting start date |
487
| end_date | DATE | NOT NULL | Job posting end date |
232e44 Vinay Deokar 2025-11-13 10:58:32 488
| status | VARCHAR(50) | DEFAULT 'active', CHECK (status IN ('active', 'paused', 'closed')) Business| Job status |
14faef Vinay Deokar 2025-10-07 17:02:56 489
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Job creation time |
490
| updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Last update time |
491
492
**Indexes:**
493
- `idx_jobs_status` on `status` (filter active jobs)
494
- `idx_jobs_dates` on `(start_date, end_date)` (date range queries)
495
- `idx_jd_embedding` on `jd_embedding USING hnsw (vector_cosine_ops)` (fast similarity search)
496
497
---
498
b78094 Vinay Deokar 2025-10-08 09:39:55 499
##### Table: candidates
14faef Vinay Deokar 2025-10-07 17:02:56 500
**Purpose:** Store candidate resumes and parsed data
501
502
| Column | Type | Constraints | Description |
503
|------------------|-------------|-----------------------------------------------------|----------------------------------------------|
504
| candidate_id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique candidate identifier |
505
| full_name | VARCHAR(255)| NOT NULL | Candidate's full name |
506
| email | VARCHAR(255)| NOT NULL | Candidate's email |
507
| phone | VARCHAR(50) | | Candidate's phone number |
508
| resume_text | TEXT | NOT NULL | Full resume text (plain) |
509
| resume_embedding | vector(1536)| NOT NULL | Semantic embedding of resume |
510
| parsed_data | JSONB | | Structured resume data (skills, experience, education) |
511
| resume_hash | VARCHAR(64) | UNIQUE, NOT NULL | SHA-256 hash for deduplication |
512
| source | VARCHAR(50) | DEFAULT 'email' | Source of resume (email, upload) |
2febd4 Vinay Deokar 2025-10-13 16:59:42 513
| blocked | BOOLEAN | DEFAULT FALSE | Candidate-job status |
14faef Vinay Deokar 2025-10-07 17:02:56 514
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Resume received time |
515
| updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Last update time |
516
517
**Indexes:**
518
- `idx_candidates_email` on `email` (find by email)
519
- `idx_candidates_hash` on `resume_hash` (deduplication check)
520
- `idx_resume_embedding` on `resume_embedding USING hnsw (vector_cosine_ops)` (vector search)
521
522
**JSONB Structure (parsed_data):**
523
```json
524
{
525
"skills": ["javascript", "python", "react"],
526
"experience": [
527
{
528
"title": "Software Engineer",
529
"company": "Tech Corp",
530
"duration": "2020-2023",
531
"description": "Built microservices..."
232e44 Vinay Deokar 2025-11-13 10:58:32 532
}### 9. Phase Two Enhancements (AI-assisted Interview Scheduler)
533
824
534
14faef Vinay Deokar 2025-10-07 17:02:56 535
],
536
"education": [
537
{
538
"degree": "B.S. Computer Science",
539
"institution": "MIT",
540
"year": 2018
541
}
542
],
543
"total_experience_years": 5
544
}
545
```
a360ae Vinay Deokar 2025-10-08 13:53:02 546
547
##### Table: job_candidate_matches
548
**Purpose:** Store matches between jobs and candidates (many-to-many relationship)
549
550
| Column | Type | Constraints | Description |
551
|---------------|-------------|------------------------------------------|---------------------------------|
552
| match_id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique match identifier |
553
| job_id | UUID | FOREIGN KEY REFERENCES jobs(job_id) | Associated job |
554
| candidate_id | UUID | FOREIGN KEY REFERENCES candidates(candidate_id) | Associated candidate |
555
| match_score | DECIMAL(5,4)| | AI-generated match score (0-1) |
556
| ai_reasoning | TEXT | | AI explanation for match |
557
| status | VARCHAR(50) | DEFAULT 'new', CHECK (status IN ('new','contacted','replied','interviewed','rejected','hired')) | Candidate-job status |
558
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Match creation time |
559
| updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Last update time |
560
561
**Indexes:**
562
- `idx_matches_job` on `job_id`
563
- `idx_matches_candidate` on `candidate_id`
564
- `idx_matches_score` on `(job_id, match_score DESC)` (ranked retrieval)
565
14faef Vinay Deokar 2025-10-07 17:02:56 566
---
2d5d42 Vinay Deokar 2025-11-09 06:02:15 567
##### Table: managers
908f5b Vinay Deokar 2025-11-09 04:48:20 568
569
**Purpose:** Store manager profiles responsible for interviews or job coordination.
a360ae Vinay Deokar 2025-10-08 13:53:02 570
908f5b Vinay Deokar 2025-11-09 04:48:20 571
| Column | Type | Constraints | Description |
572
| ---------- | ------------ | -------------------------------------- | --------------------------------- |
573
| id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique manager identifier |
574
| name | VARCHAR(255) | NOT NULL | Manager's full name |
575
| email | VARCHAR(255) | UNIQUE, NOT NULL | Manager's email address |
576
| skills | JSONB | DEFAULT '[]' | List of skills or expertise areas |
577
| department | VARCHAR(255) | | Department or function area |
578
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Record creation time |
579
| updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Record last update time |
580
581
**Indexes:**
14faef Vinay Deokar 2025-10-07 17:02:56 582
908f5b Vinay Deokar 2025-11-09 04:48:20 583
- `idx_managers_email` on `email`
584
---
585
#### Table: manager_availabilities
586
587
**Purpose:** Track availability slots of managers for scheduling interviews.
588
589
| Column | Type | Constraints | Description |
590
| --------------- | --------- | ----------------------------------------------------- | ------------------------------------- |
591
| availability_id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique availability record identifier |
592
| manager_id | UUID | FOREIGN KEY REFERENCES managers(id) ON DELETE CASCADE | Manager reference |
232e44 Vinay Deokar 2025-11-13 10:58:32 593
| date | DATE | NOT NULL | Available dBusinessate |
908f5b Vinay Deokar 2025-11-09 04:48:20 594
| start_time | TIME | NOT NULL | Start time of availability |
595
| end_time | TIME | NOT NULL | End time of availability |
596
| is_booked | BOOLEAN | DEFAULT false | Marks if slot is already booked |
597
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Record creation time |
598
| updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Record last update time |
599
600
**Indexes:**
601
602
- `idx_manager_availabilities_manager_id` on `manager_id`
603
- `idx_manager_availabilities_date` on `date`
604
---
2d5d42 Vinay Deokar 2025-11-09 06:02:15 605
##### Table: job_managers
908f5b Vinay Deokar 2025-11-09 04:48:20 606
607
**Purpose:** Link managers to specific jobs with defined roles (e.g., hiring manager, interviewer).
608
609
| Column | Type | Constraints | Description |
610
| ---------- | ------------ | ----------------------------------------------------- | ------------------------------------------------- |
611
| id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique record identifier |
612
| job_id | UUID | FOREIGN KEY REFERENCES jobs(job_id) ON DELETE CASCADE | Job reference |
613
| manager_id | UUID | FOREIGN KEY REFERENCES managers(id) ON DELETE CASCADE | Manager reference |
614
| role | VARCHAR(100) | NOT NULL | Role in hiring process (e.g., interviewer, owner) |
615
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Record creation time |
616
| updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Record last update time
617
618
- `idx_job_managers_job_id` on `job_id`
2d5d42 Vinay Deokar 2025-11-09 06:02:15 619
- `idx_job_managers_manager_id` on `manager_id`
908f5b Vinay Deokar 2025-11-09 04:48:20 620
---
232e44 Vinay Deokar 2025-11-13 10:58:32 621
##### Table: communicationBusiness
2d5d42 Vinay Deokar 2025-11-09 06:02:15 622
623
**Purpose:** Stores all communication exchanges between the system, candidates, and managers- including interview invites, responses, and final confirmations.
624
625
| Column | Type | Constraints | Description |
626
| ----------------- | ------------ | ----------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
627
| `comm_id` | UUID | PK | Unique communication identifier |
628
| `candidate_id` | UUID | FK → `candidates.candidate_id`, `CASCADE` | Candidate reference |
629
| `job_id` | UUID | FK → `jobs.job_id`, `SET NULL` | Associated job |
630
| `manager_id` | UUID | FK → `managers.id`, `SET NULL` | Manager reference |
631
| `type` | ENUM | NOT NULL | Type of communication:<br>• `candidate_invite`<br>• `candidate_response`<br>• `manager_invite`<br>• `manager_response`<br>• `final_confirmation` |
632
| `subject` | VARCHAR(500) | | Email subject line |
633
| `body` | TEXT | | Full email/message content |
634
| `slot_date` | DATE | NULLABLE | Date of the interview slot |
635
| `slot_start_time` | TIME | NULLABLE | Start time of the slot |
636
| `slot_end_time` | TIME | NULLABLE | End time of the slot |
637
| `unique_hash` | STRING | NULLABLE | Hash used to prevent duplicate communications for same slot |
638
| `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` |
639
| `gmail_thread_id` | STRING | | Gmail thread tracking ID |
640
| `sent_at` | TIMESTAMP | | When email was sent |
641
| `received_at` | TIMESTAMP | | When response was received |
642
| `created_at` | TIMESTAMP | DEFAULT: `CURRENT_TIMESTAMP` | Record creation timestamp |
643
| `updated_at` | TIMESTAMP | DEFAULT: `CURRENT_TIMESTAMP` | Record last updated timestamp |
644
14faef Vinay Deokar 2025-10-07 17:02:56 645
**Indexes:**
908f5b Vinay Deokar 2025-11-09 04:48:20 646
- `idx_communication_status` on `status`
647
- `idx_communication_candidate_id` on `candidate_id`
648
- `idx_communication_manager_id` on `manager_id`
649
- `idx_communication_job_id` on `job_id`
2d5d42 Vinay Deokar 2025-11-09 06:02:15 650
- `idx_communication_gmail_thread_id` on `gmail_thread_id`
651
---
652
##### Table: interview_schedule
653
654
**Purpose:** Maintains the record of interview schedules created for candidates against specific jobs and managers.
655
Each record represents a confirmed or proposed interview slot with associated meeting details.
656
Prevents scheduling conflicts for the same candidate in overlapping slots.
657
658
| **Column** | **Type** | **Constraints** | **Description** |
659
| ------------------- | ------------------------------------------------------------------------ | ------------------------------------------------------------------------- | ----------------------------------------------- |
660
| `interview_id` | `UUID` | **PK**, Default: `UUIDV4` | Unique identifier for each interview |
661
| `job_id` | `UUID` | **FK → jobs.job_id**, `CASCADE` on delete/update | Associated job for which interview is scheduled |
662
| `candidate_id` | `UUID` | **FK → candidates.candidate_id**, `CASCADE` on delete/update | Candidate being interviewed |
663
| `manager_id` | `UUID` | **FK → managers.id**, `CASCADE` on delete/update | Manager conducting or managing the interview |
664
| `comm_id` | `UUID` | **FK → communication.comm_id**, `SET NULL` on delete, `CASCADE` on update | References the related communication thread |
665
| `slot_date` | `DATEONLY` | **NOT NULL** | Date of the scheduled interview |
666
| `slot_start_time` | `TIME` | **NOT NULL** | Interview start time |
667
| `slot_end_time` | `TIME` | **NOT NULL** | Interview end time |
668
| `meeting_link` | `STRING(500)` | NULLABLE | Meeting URL (Google Meet, Zoom, Teams, etc.) |
669
| `meeting_platform` | `ENUM('google_meet', 'zoom', 'teams')` | **NOT NULL**, Default: `google_meet` | Platform used for the meeting |
670
| `calendar_event_id` | `STRING(255)` | NULLABLE | External calendar event identifier |
671
| `status` | `ENUM('proposed', 'scheduled', 'rescheduled', 'cancelled', 'completed')` | **NOT NULL**, Default: `proposed` | Current interview status |
672
| `created_by` | `UUID` | **FK → users.user_id**, `SET NULL` on delete, `CASCADE` on update | User who created the interview record |
673
| `notes` | `TEXT` | NULLABLE | Optional notes or remarks |
674
| `created_at` | `TIMESTAMP` | Default: `CURRENT_TIMESTAMP` | Record creation timestamp |
675
| `updated_at` | `TIMESTAMP` | Default: `CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP` | Record last updated timestamp |
14faef Vinay Deokar 2025-10-07 17:02:56 676
2d5d42 Vinay Deokar 2025-11-09 06:02:15 677
**Indexes:**
678
- `idx_interview_job_id` on `job_id`
679
- `idx_interview_candidate_id` on `candidate_id`
680
- `idx_interview_manager_id` on `manager_id`
681
- `idx_interview_slot_date` on `slot_date`
682
- `idx_interview_status` on `status`
683
14faef Vinay Deokar 2025-10-07 17:02:56 684
---
a29d45 Vinay Deokar 2025-10-13 16:37:46 685
##### Table: templates
686
**Purpose:** Store user-specific email templates for communication
687
688
| Column | Type | Constraints | Description |
689
|--------------|-------------|--------------------------------------|---------------------------------------|
690
| template_id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique template identifier |
691
| created_by | UUID | FOREIGN KEY REFERENCES users(user_id) | User who created the template |
692
| name | VARCHAR(255)| NOT NULL | Template name |
693
| type | VARCHAR(50) | NOT NULL, CHECK (type IN ('outreach','follow_up','reply','manual')) | Type of template |
694
| subject | VARCHAR(500)| NOT NULL | Email subject template |
695
| body | TEXT | NOT NULL | Email body template |
696
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Template creation time |
697
| updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Last update time |
14faef Vinay Deokar 2025-10-07 17:02:56 698
a29d45 Vinay Deokar 2025-10-13 16:37:46 699
**Indexes:**
700
- `idx_templates_user` on `created_by` (fetch templates by user)
701
- `idx_templates_type` on `type` (filter templates by type)
702
703
---
704
b78094 Vinay Deokar 2025-10-08 09:39:55 705
#### 4.3 Database Relationships
14faef Vinay Deokar 2025-10-07 17:02:56 706
a360ae Vinay Deokar 2025-10-08 13:53:02 707
| Parent Table | Child Table | Relationship Type | Foreign Key | On Delete |
708
|--------------|---------------------|-----------------|-----------------------------------------------|-----------|
709
| users | jobs | One-to-Many | jobs.created_by → users.user_id | SET NULL |
a29d45 Vinay Deokar 2025-10-13 16:37:46 710
| users | templates | One-to-Many | templates.created_by → users.user_id | CASCADE |
a360ae Vinay Deokar 2025-10-08 13:53:02 711
| jobs | job_candidate_matches | One-to-Many | job_candidate_matches.job_id → jobs.job_id | CASCADE |
712
| candidates | job_candidate_matches | One-to-Many | job_candidate_matches.candidate_id → candidates.candidate_id | CASCADE |
713
| candidates | communication | One-to-Many | communication.candidate_id → candidates.candidate_id | CASCADE |
a29d45 Vinay Deokar 2025-10-13 16:37:46 714
| templates | communication | One-to-Many | communication.template_id → templates.template_id | SET NULL |
14faef Vinay Deokar 2025-10-07 17:02:56 715
716
**Cascade Deletion Logic:**
a360ae Vinay Deokar 2025-10-08 13:53:02 717
- When a job is deleted, all associated job-candidate matches are deleted
718
- When a candidate is deleted, all associated job-candidate matches and communications are deleted
a29d45 Vinay Deokar 2025-10-13 16:37:46 719
- When a user is deleted, their jobs remain but `created_by` is set to NULL
720
- When a user is deleted, all their templates are deleted
721
- When a template is deleted, communications referencing it will have `template_id` set to NULL
14faef Vinay Deokar 2025-10-07 17:02:56 722
723
---
724
b78094 Vinay Deokar 2025-10-08 09:39:55 725
#### 4.4 pgvector Configuration
232e44 Vinay Deokar 2025-11-13 10:58:32 726
### 9. Phase Two Enhancements (AI-assisted Interview Scheduler)
727
824
14faef Vinay Deokar 2025-10-07 17:02:56 728
232e44 Vinay Deokar 2025-11-13 10:58:32 729
**Extension Setup:** Business
14faef Vinay Deokar 2025-10-07 17:02:56 730
```sql
731
CREATE EXTENSION IF NOT EXISTS vector;
732
```
733
734
---
735
b78094 Vinay Deokar 2025-10-08 09:39:55 736
### 5. Business Workflow
14faef Vinay Deokar 2025-10-07 17:02:56 737
b78094 Vinay Deokar 2025-10-08 09:39:55 738
##### PHASE 1: JOB CREATION
14faef Vinay Deokar 2025-10-07 17:02:56 739
- HR User opens Dashboard → Create Job Form
740
- Input: Title, JD Text, Dates, Min Score
741
- POST /api/jobs
742
- Validate input (dates, text length)
743
- Generate JD embedding (OpenAI API)
744
- Store in database
745
- Schedule cron job (resume ingestion every 4 hours)
746
- Job Created ✅
747
- Status: Active
748
b78094 Vinay Deokar 2025-10-08 09:39:55 749
##### PHASE 2: RESUME INGESTION (Automated - Every 4 hours)
14faef Vinay Deokar 2025-10-07 17:02:56 750
- Cron Trigger → Application Controller
751
- Gmail Module:
752
- Fetch emails with attachments
753
- Filter: after last fetch, keywords (resume, application)
754
- Download attachments (PDF/DOCX)
755
- Resume Parser Module:
756
- Extract text from PDF/DOCX
757
- Generate SHA-256 hash
758
- Check deduplication (processing_logs)
759
- If duplicate → skip & log as 'duplicate'
760
- If new → continue processing
761
- Parse Resume (Rule-based NLP):
762
- Extract: Name, Email, Phone
763
- Extract: Skills
764
- Extract: Experience
765
- Extract: Education
766
- Calculate total experience years
767
- Structured JSON created: {name, email, skills[], experience[], education[], total_years}
768
- Embedding Module:
769
- Generate embedding (1536-dim vector)
770
- Database Module:
771
- Store candidate record:
772
- parsed_data (JSONB)
773
- resume_embedding
774
- resume_hash
775
- status: 'new'
776
- Log processing in processing_logs:
777
- status: 'success'
778
- processing_time_ms
779
- Candidate Stored ✅
780
b78094 Vinay Deokar 2025-10-08 09:39:55 781
##### PHASE 3: CANDIDATE RANKING (On-Demand)
14faef Vinay Deokar 2025-10-07 17:02:56 782
- HR User → Dashboard → View Job → Click "View Candidates"
783
- GET /api/jobs/:jobId/candidates
784
- Application Controller:
785
- Ranking Module (Stage 1: Vector Search):
786
- Fetch job.jd_embedding
787
- Query top-50 candidates by cosine similarity
788
- Ranking Module (Stage 2: AI Re-Ranking):
789
- Prepare AI context (JD text + top-50 candidates)
790
- Call GPT-4o-mini:
791
- Rank candidates (1-50)
792
- Fit score (0-1)
793
- Brief reasoning
794
- Calculate final_score = 0.4 × vector_sim + 0.6 × AI_score
795
- Update candidates.match_score and ai_reasoning in DB
796
- Return Ranked Candidates → Frontend
797
- HR Dashboard displays Top-10 candidates:
798
- Name, Email, Match Score
799
- AI reasoning
800
- Parsed Data (skills, experience)
801
- Actions: View Details, Contact, Reject
802
b78094 Vinay Deokar 2025-10-08 09:39:55 803
##### PHASE 4: CANDIDATE INTERACTION (Manual)
14faef Vinay Deokar 2025-10-07 17:02:56 804
- HR selects candidate → Actions:
805
- View full resume
806
- Update status ('contacted', 'replied', 'interviewed')
807
- Add notes (stored in notes table)
808
- Send email (logged in communication table)
809
- Status updated in database
810
- Dashboard reflects new status
811
b78094 Vinay Deokar 2025-10-08 09:39:55 812
##### PHASE 5: JOB CLOSURE
14faef Vinay Deokar 2025-10-07 17:02:56 813
- HR closes job → DELETE /api/jobs/:jobId
814
- Update job.status = 'closed'
815
- Stop all scheduled tasks
816
- Archive candidates (records remain in DB)
817
- Job Closed ✅
4e31e8 Vinay Deokar 2025-10-07 17:26:31 818
819
---
820
b78094 Vinay Deokar 2025-10-08 09:39:55 821
### 6. System Architecture
4e31e8 Vinay Deokar 2025-10-07 17:26:31 822
![](./image-1759857600801.drawio.png)
823
---
824
b78094 Vinay Deokar 2025-10-08 09:39:55 825
### 7. Sequence Diagram
4e31e8 Vinay Deokar 2025-10-07 17:26:31 826
![](./image-1759857739651.-2025-10-07-172157.png)
14faef Vinay Deokar 2025-10-07 17:02:56 827
---
69b66d Vinay Deokar 2025-10-08 05:58:57 828
b78094 Vinay Deokar 2025-10-08 09:39:55 829
### 8. Flow Diagram
0c918e Vinay Deokar 2025-10-08 09:16:11 830
![](./image-1759914896125.drawio.png)
69b66d Vinay Deokar 2025-10-08 05:58:57 831
---
232e44 Vinay Deokar 2025-11-13 10:58:32 832
833
### 9. Phase Two Enhancements
834
#### AI-assisted interview scheduler
835
- 🧑‍💼 1. Manager & Availability Setup
836
837
- HR assigns one or more managers to each job.
838
839
- Managers define their available time slots for interviews.
840
841
- System ensures no overlapping interview slots.
842
843
- 🎯 2. Candidate Invitation
844
845
- HR shortlists candidates for interviews.
846
847
- AI automatically drafts and sends invitation emails with available time slots.
848
849
- Each invitation is recorded for tracking and status updates.
850
851
- 📬 3. Candidate Response
852
853
- The system listens for candidate replies.
854
855
- AI interprets their response (acceptance, decline, or preferred slot).
856
857
- Once confirmed, the selected slot is blocked to prevent double booking.
858
859
- 👨‍💼 4. Manager Confirmation
860
861
- The system notifies the respective manager about the candidate’s selected slot.
862
863
- Manager confirms or reschedules based on their convenience.
864
865
- Their response is automatically updated in the system.
866
867
- 🗓️ 5. Interview Scheduling
868
869
- Once both sides confirm, the interview is officially scheduled.
870
871
- A meeting link (Google Meet / Zoom) is generated automatically.
872
873
- Confirmation emails are sent to all participants — HR, Manager, and Candidate.
874
875
- 📊 6. Post-Interview Actions
876
877
- After the interview, HR can mark it as completed.
878
879
- Feedback and notes can be added for future reference.
880
881
- The system reopens or manages availability slots for upcoming interviews.