Home A - Z
Page Index

Commit 14faef

2025-10-07 17:02:56 Vinay Deokar: Added partial system docs covering API, DB, modules, workflows, and JSON examples
Projects/HireGenius/LLD.md ..
@@ 276,3 276,415 @@
- Communication history per candidate
---
+
+ ## 3. API Design
+
+ ### 3.1 Authentication Endpoints
+
+ | Method | Endpoint | Request Body | Response | Description |
+ |--------|----------------|-------------------|--------------|--------------------------|
+ | POST | /api/auth/login | {email, password} | {user, token} | User login, returns JWT |
+ | POST | /api/auth/logout | {token} | {success: true} | Invalidate token |
+ | GET | /api/auth/me | Headers: Authorization: Bearer <token> | {user} | Get current user info |
+
+ **Authentication Flow:**
+ - User submits credentials
+ - Server validates against database (bcrypt password comparison)
+ - Generate JWT with 24-hour expiry
+ - Return token to client
+ - Client includes token in all subsequent requests
+
+ ---
+
+ ### 3.2 Job Management Endpoints
+
+ | Method | Endpoint | Request Body | Response | Description |
+ |--------|-----------------|-------------------------------------------|---------------------------|--------------------------------------------|
+ | POST | /api/jobs | {title, jd_text, start_date, end_date, min_score} | {job_id, jd_embedding, ...} | Create job, generates embedding |
+ | GET | /api/jobs | Query: ?status=active&page=1&limit=20 | {jobs[], total, page} | List jobs with pagination |
+ | GET | /api/jobs/:id | - | {job, candidate_count} | Get job details |
+ | PUT | /api/jobs/:id | {title?, jd_text?, min_score?} | {success: true} | Update job (re-generates embedding if JD changed) |
+ | DELETE | /api/jobs/:id | - | {success: true} | Close job, stop cron tasks |
+
+ **Request Example (POST /api/jobs):**
+ ```json
+ {
+ "title": "Senior Full Stack Developer",
+ "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...",
+ "start_date": "2025-10-01",
+ "end_date": "2025-12-31",
+ "min_score": 0.70
+ }
+ ```
+ **Response Example:**
+ ```json
+ {
+ "job_id": "uuid-123",
+ "title": "Senior Full Stack Developer",
+ "jd_embedding": [0.123, -0.456, ...], // 1536 dimensions
+ "status": "active",
+ "created_at": "2025-10-02T10:00:00Z"
+ }
+ ```
+ ---
+
+ ### 3.3 Candidate Retrieval Endpoints
+
+ | Method | Endpoint | Request Body | Response | Description |
+ |--------|------------------------------|-------------------------------------------|---------------------------|-----------------------------------|
+ | GET | /api/jobs/:jobId/candidates | Query: ?status=new&sort=score&page=1&limit=20 | {candidates[], total} | Get ranked candidates for job |
+ | GET | /api/candidates/:id | - | {candidate, parsed_data, communications[]} | Get candidate details |
+ | PUT | /api/candidates/:id/status | {status} | {success: true} | Update candidate status |
+ | POST | /api/candidates/:id/notes | {note_text} | {note_id} | Add HR notes |
+
+ **Request Example (GET /api/jobs/:jobId/candidates):**
+ ```json
+ {
+ "job_id": "uuid-123",
+ "status": "new",
+ "sort": "score",
+ "page": 1,
+ "limit": 20
+ }
+ ```
+ **Response Example:**
+ ```json
+ {
+ "candidates": [
+ {
+ "candidate_id": "uuid-456",
+ "full_name": "John Doe",
+ "email": "john@example.com",
+ "match_score": 0.87,
+ "status": "new",
+ "parsed_data": {
+ "skills": ["javascript", "react", "node.js", "postgresql"],
+ "total_experience_years": 6
+ },
+ "ai_reasoning": "Strong technical alignment with 5/6 required skills. Experience exceeds minimum requirement.",
+ "created_at": "2025-10-02T12:30:00Z"
+ }
+ ],
+ "total": 45,
+ "page": 1,
+ "limit": 20
+ }
+
+ ```
+ ---
+
+ ### 3.4 Resume Processing Endpoints (Internal)
+
+ | Method | Endpoint | Request Body | Response | Description |
+ |--------|----------------------------------|-------------|---------------------------|-----------------------------------|
+ | POST | /api/internal/process-resumes/:jobId | - | {processed_count, errors[]} | Manually trigger resume processing (admin only) |
+ | GET | /api/internal/processing-logs/:jobId | Query: ?status=success&page=1 | {logs[], total} | View processing logs |
+
+ ---
+ ### 3.5 Dashboard & Analytics Endpoints
+
+ | Method | Endpoint | Request Body | Response | Description |
+ |--------|------------------------------------|-------------|---------------------------|-------------------------------|
+ | GET | /api/dashboard/stats/:jobId | - | {total, by_status{}, avg_score} | Get job pipeline metrics |
+ | GET | /api/dashboard/timeline/:jobId | Query: ?start_date&end_date | {timeline_data[]} | Candidate activity over time |
+ | GET | /api/export/candidates/:jobId | Query: ?format=csv | CSV file | Export candidates |
+
+ **Response Example (GET /api/dashboard/stats/:jobId):**
+ ```json
+ {
+ "total_candidates": 120,
+ "by_status": {
+ "new": 45,
+ "contacted": 50,
+ "replied": 20,
+ "interviewed": 3,
+ "rejected": 2
+ },
+ "avg_match_score": 0.74,
+ "top_skills": ["javascript", "python", "react"],
+ "last_updated": "2025-10-02T14:00:00Z"
+ }
+ ```
+ ---
+ ### 4. Database Design
+
+ #### 4.1 Entity Relationship Diagram (ERD)
+ ---
+
+ ### 4.2 Table Specifications
+
+ #### Table: users
+ **Purpose:** Store HR user accounts
+
+ | Column | Type | Constraints | Description |
+ |---------------|-------------|--------------------------------------|-------------------------------|
+ | 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 |
+ | 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 |
+ | updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Last update time |
+
+ **Indexes:**
+ - `idx_users_email` on `email` (for login queries)
+
+ ---
+
+ #### Table: jobs
+ **Purpose:** Store job descriptions and automation settings
+
+ | Column | Type | Constraints | Description |
+ |---------------|-------------|------------------------------------------|---------------------------------------|
+ | job_id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique job identifier |
+ | created_by | UUID | FOREIGN KEY REFERENCES users(user_id) | Job creator |
+ | title | VARCHAR(255)| NOT NULL | Job title |
+ | jd_text | TEXT | NOT NULL | Full job description |
+ | jd_embedding | vector(1536)| NOT NULL | Semantic embedding of JD |
+ | 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 |
+
+ **Indexes:**
+ - `idx_jobs_status` on `status` (filter active jobs)
+ - `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
+ **Purpose:** Store candidate resumes and parsed data
+
+ | 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)
+
+ **JSONB Structure (parsed_data):**
+ ```json
+ {
+ "skills": ["javascript", "python", "react"],
+ "experience": [
+ {
+ "title": "Software Engineer",
+ "company": "Tech Corp",
+ "duration": "2020-2023",
+ "description": "Built microservices..."
+ }
+ ],
+ "education": [
+ {
+ "degree": "B.S. Computer Science",
+ "institution": "MIT",
+ "year": 2018
+ }
+ ],
+ "total_experience_years": 5
+ }
+ ```
+ ---
+ ### 4.2 Table Specifications (continued)
+
+ #### Table: communication
+ **Purpose:** Log all email communications
+
+ | 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 |
+
+ **Indexes:**
+ - `idx_comm_candidate` on `candidate_id` (fetch communication history)
+ - `idx_comm_type` on `type` (filter by type)
+
+ ---
+
+ #### 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 |
+
+ **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 user is deleted, their jobs remain but `created_by` is set to NULL
+
+ ---
+
+ ### 4.4 pgvector Configuration
+
+ **Extension Setup:**
+ ```sql
+ CREATE EXTENSION IF NOT EXISTS vector;
+ ```
+
+ ---
+
+ ## 5. Business Workflow
+
+ ### 5.1 End-to-End Workflow (Vertical)
+
+ #### PHASE 1: JOB CREATION
+ - HR User opens Dashboard → Create Job Form
+ - Input: Title, JD Text, Dates, Min Score
+ - POST /api/jobs
+ - Validate input (dates, text length)
+ - Generate JD embedding (OpenAI API)
+ - Store in database
+ - Schedule cron job (resume ingestion every 4 hours)
+ - Job Created ✅
+ - Status: Active
+
+ #### PHASE 2: RESUME INGESTION (Automated - Every 4 hours)
+ - Cron Trigger → Application Controller
+ - Gmail Module:
+ - Fetch emails with attachments
+ - Filter: after last fetch, keywords (resume, application)
+ - Download attachments (PDF/DOCX)
+ - Resume Parser Module:
+ - Extract text from PDF/DOCX
+ - Generate SHA-256 hash
+ - Check deduplication (processing_logs)
+ - If duplicate → skip & log as 'duplicate'
+ - If new → continue processing
+ - Parse Resume (Rule-based NLP):
+ - Extract: Name, Email, Phone
+ - Extract: Skills
+ - Extract: Experience
+ - Extract: Education
+ - Calculate total experience years
+ - Structured JSON created: {name, email, skills[], experience[], education[], total_years}
+ - Embedding Module:
+ - Generate embedding (1536-dim vector)
+ - Database Module:
+ - Store candidate record:
+ - parsed_data (JSONB)
+ - resume_embedding
+ - resume_hash
+ - status: 'new'
+ - Log processing in processing_logs:
+ - status: 'success'
+ - processing_time_ms
+ - Candidate Stored ✅
+
+ #### PHASE 3: CANDIDATE RANKING (On-Demand)
+ - HR User → Dashboard → View Job → Click "View Candidates"
+ - GET /api/jobs/:jobId/candidates
+ - Application Controller:
+ - Ranking Module (Stage 1: Vector Search):
+ - Fetch job.jd_embedding
+ - Query top-50 candidates by cosine similarity
+ - Ranking Module (Stage 2: AI Re-Ranking):
+ - Prepare AI context (JD text + top-50 candidates)
+ - Call GPT-4o-mini:
+ - Rank candidates (1-50)
+ - Fit score (0-1)
+ - Brief reasoning
+ - Calculate final_score = 0.4 × vector_sim + 0.6 × AI_score
+ - Update candidates.match_score and ai_reasoning in DB
+ - Return Ranked Candidates → Frontend
+ - HR Dashboard displays Top-10 candidates:
+ - Name, Email, Match Score
+ - AI reasoning
+ - Parsed Data (skills, experience)
+ - Actions: View Details, Contact, Reject
+
+ #### PHASE 4: CANDIDATE INTERACTION (Manual)
+ - HR selects candidate → Actions:
+ - View full resume
+ - Update status ('contacted', 'replied', 'interviewed')
+ - Add notes (stored in notes table)
+ - Send email (logged in communication table)
+ - Status updated in database
+ - Dashboard reflects new status
+
+ #### PHASE 5: JOB CLOSURE
+ - HR closes job → DELETE /api/jobs/:jobId
+ - Update job.status = 'closed'
+ - Stop all scheduled tasks
+ - Archive candidates (records remain in DB)
+ - Job Closed ✅
+ ---
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