# Technical Document (LLD/HLD) ## HireGenius- AI-Powered Recruitment Automation System ### 📘 Table of Contents | # | Section | |---|---------| | 1 | [System Overview](#1-system-overview) | | 2 | [Module Breakdown](#2-module-breakdown) | | 3 | [API Design](#3-api-design) | | 4 | [Database Design](#4-database-design) | | 5 | [Business Workflow](#5-business-workflow) | | 6 | [System Architecture](#6-system-architecture) | | 7 | [Sequence Diagram](#7-sequence-diagram) | | 8 | [Flow Diagram](#8-flow-diagram) | | 9 | [Phase Two Enhancement](#9-phase-two-enhancements) | ### 1. System Overview #### 1.1 🎯 Purpose Automate the recruitment pipeline from resume ingestion to candidate ranking using **NLP-based parsing**, **vector embeddings**, and **AI-powered re-ranking**. --- #### 1.2 ⚙️ Key Capabilities - **Incremental Resume Ingestion:** Fetch new resumes from Gmail on schedule - **Automated Resume Parsing:** Extract structured data (skills, experience, education, contact) - **Semantic Matching:** Use embeddings to find similar candidates to job descriptions - **AI Re-Ranking:** Re-rank top candidates using GPT-4o-mini for better precision - **Dashboard:** HR views ranked candidates with explainability --- #### 1.3 🧰 Technology Stack | Layer | Technology | |--------|-------------| | **Frontend** | React.js + Tailwind CSS | | **Backend** | Node.js + Express.js | | **Database** | PostgreSQL 15+ with pgvector extension | | **AI/ML** | OpenAI GPT-4o-mini | | **Embeddings** | OpenAI `text-embedding-3-small` | | **Email Integration** | Gmail API with OAuth 2.0 | | **Scheduler** | node-cron | ### 2. Module Breakdown #### Module 1: Unified Resume Ingestion Module **Responsibility:** Multi-source resume ingestion with pluggable adapters | Source Type | Adapter | Trigger Method | Priority | Notes | |-----------------|-------------------|--------------------|---------|-------| | Gmail | GmailAdapter | Scheduled (cron) | High | Phase 1 implementation | | Webhook | WebhookAdapter | Real-time (HTTP POST)| High | Future source | | Cloud Storage | CloudStorageAdapter | Polling/Event-driven | Medium | Future source | | Direct Upload | UploadAdapter | On-demand (UI) | Medium | Future source | | ATS Integration | ATSAdapter | API polling | Low | Future source | --- ###### Core Functions ```javascript // Abstract base adapter class ResumeSourceAdapter { async fetchResumes(config) → Returns ResumeCollection async validateSource(config) → Returns boolean async getSourceMetadata() → Returns SourceInfo } // Unified ingestion controller fetchResumesFromAllSources(jobId) → Orchestrates all active sources registerSource(jobId, sourceType, config) → Activates new source deactivateSource(jobId, sourceId) → Stops fetching from source getActiveSourcesForJob(jobId) → Returns list of active sources ``` ##### Phase 1: Gmail Module **Responsibility:** Email integration and resume fetching **Functions:** - Authenticate with Gmail API using OAuth 2.0 - Fetch emails with attachments based on filters (date, keywords, labels) - Download resume attachments (PDF, DOCX) - Track last fetch timestamp for incremental processing **Key Operations:** - `fetchNewEmails(afterDate, filters)` → Returns array of email objects with attachments - `downloadAttachment(messageId, attachmentId)` → Returns file buffer - `getLastFetchTime(jobId)` → Returns timestamp of last successful fetch **External Dependencies:** Gmail API, Google OAuth 2.0 --- #### Module 2: Resume Parser Module **Responsibility:** Extract structured data from resume text **Parsing Strategy:** Rule-based NLP with regex patterns **Name Extraction:** First line heuristics, capitalization patterns **Email Extraction:** Regex pattern `[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}` **Phone Extraction:** Regex for various formats (+1-XXX-XXX-XXXX, (XXX) XXX-XXXX) **Skills Extraction:** Keyword matching against predefined skill database **Experience Parsing:** Section detection (keywords: "experience", "work history") + date parsing **Education Parsing:** Section detection + degree/institution extraction **Functions:** - `extractText(fileBuffer, format)` → Returns plain text from PDF/DOCX - `parseResume(resumeText)` → Returns structured JSON object - `validateParsedData(data)` → Returns boolean + error messages - `normalizeSkills(skillArray)` → Returns deduplicated, lowercase skills **Output Format (JSON):** ```json { "name": "John Doe", "email": "john@example.com", "phone": "+1-555-0123", "skills": ["javascript", "python", "sql"], "experience": [ { "title": "Software Engineer", "company": "Tech Corp", "duration": "2020-2023", "description": "Built REST APIs..." } ], "education": [ { "degree": "B.S. Computer Science", "institution": "MIT", "year": 2018 } ], "total_experience_years": 5 } ``` --- #### Alternative AI Approach (Optional Enhancement) 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). **Pros:** - Much higher accuracy across different resume formats and layouts - Handles inconsistent structures, missing sections, and varied wording - Reduces need for complex regex/heuristics **Cons:** - Incur API usage costs - Slightly higher latency (~2–5 seconds per resume, depending on length) - Requires API reliability and internet access - --- #### Module 3: Embedding Module **Responsibility:** Generate vector embeddings for semantic search **Embedding Strategy:** - Use OpenAI text-embedding-3-small model (1536 dimensions) - Embed full resume text (truncated to 8000 chars if needed) - Embed JD text once per job creation - Store vectors in PostgreSQL pgvector columns **Functions:** - `generateEmbedding(text)` -> Returns 1536-dimensional float array - `batchGenerateEmbeddings(textArray)` -> Returns array of vectors (for efficiency) - `calculateCosineSimilarity(vec1, vec2)` -> Returns similarity score (0-1) **Caching Strategy:** - Cache JD embeddings in memory (job lifecycle) - Resume embeddings stored permanently in database --- #### Module 4: Database Module **Responsibility:** Data persistence and vector operations **Core Operations:** - CRUD for all entities (users, jobs, candidates, communications) - Vector similarity search using pgvector - Transaction management for atomic operations - Query optimization with proper indexing **Key Functions:** - `storeCandidate(candidateData)` -> Returns candidateId - `findSimilarCandidates(jdEmbedding, topK)` -> Returns top-K candidate IDs by cosine similarity - `updateCandidateScore(candidateId, score)` -> Updates match score - `getCandidatesByJob(jobId, filters)` -> Returns paginated candidate list **Vector Search Query (Conceptual):** - Use pgvector's `<=>` operator for cosine distance - Order by `1 - (jd_embedding <=> resume_embedding)` for similarity - Apply filters (status, score range) after vector search --- #### Module 5: Job Description Module **Responsibility:** JD lifecycle management and candidate retrieval **Functions:** - `createJob(jobData)` -> Validates input, generates embedding, stores in DB - `getActiveJobs()` -> Returns list of jobs with status='active' - `findTopCandidates(jobId, topK)` -> Queries DB for top-K similar candidates - `closeJob(jobId)` -> Updates status, stops scheduled tasks **JD Processing Flow:** - HR submits JD text via UI - Validate JD (length, required fields) - Generate embedding for JD text - Store job with embedding in database - Schedule resume ingestion task (cron) - Return job ID to frontend **Embedding Generation:** - Extract key requirements from JD text - Generate single embedding vector (1536-dim) - Store in `jobs.jd_embedding` column ---Business #### Module 6: Ranking Module **Responsibility:** AI-powered re-ranking of top candidates **Two-Stage Ranking:** **Stage 1: Vector Similarity (Fast)** - Use pgvector to retrieve top-50 candidates by cosine similarity - Initial filter based on semantic matching - Execution time: <100ms **Stage 2: AI Re-Ranking (Precise)** - Use GPT-4o-mini to re-rank top-50 -> top-10 - Provide full context: JD text + parsed resume data - AI considers: skill match, experience relevance, education fit - Execution time: 2-3 seconds **Functions:** - `getTopKSimilar(jobId, k)` -> Returns top-K candidates from vector search - `reRankWithAI(candidates, jdText)` -> Returns re-ranked list with scores and reasoning - `calculateFinalScore(candidate, job)` -> Combines vector similarity + AI score **AI Re-Ranking Prompt Structure:** **System:** You are an expert technical recruiter evaluating candidate fit. **Input:** - Job Description: [JD text] - Candidates: [Array of parsed resume data] **Task:** Rank these candidates from best to worst fit. For each, provide: 1. Rank position (1-N) 2. Fit score (0-1) Business 3. Brief reasoning (2-3 sentences) **Consider:** - Skill alignment with required skills - Experience level match - Domain relevance - Education requirements **Output Format:** JSON array ordered by rank **Scoring Algorithm:** - Initial Vector Similarity: `S_vec` (from pgvector) - AI Re-Ranking Score: `S_ai` (from GPT-4o-mini) **Final Score:** `Final Score = 0.4 × S_vec + 0.6 × S_ai` **Reasoning:** Vector similarity provides broad semantic match, AI re-ranking adds nuanced understanding of requirements. --- #### Module 7: Application Controller **Responsibility:** Orchestrate workflow across modules **Core Workflows:** **Resume Processing Workflow** - **Trigger:** Cron job (every 4 hours) - **Steps:** Fetch emails → Extract text → Parse → Embed → Store **Candidate Ranking Workflow** - **Trigger:** HR requests candidates for a job - **Steps:** Vector search → AI re-rank → Return ranked list **Deduplication Workflow** - **Trigger:** Before storing new candidate - **Steps:** Hash resume text → Check DB → Skip if exists **Functions:** - `processNewResumes(jobId)` -> Orchestrates resume processing - `getRankedCandidates(jobId)` -> Orchestrates ranking workflow - `handleDuplication(resumeHash)` -> Checks and logs duplicates **Error Handling:** - Retry logic for API failures (3 attempts with exponential backoff) - Log all errors with context (job ID, resume ID, error message) - Continue processing remaining resumes on individual failures --- #### Module 8: HR UI Module **Responsibility:** Provide API endpoints for frontend dashboard **API Endpoints:** Detailed in Section 3 **Dashboard Views:** - Job listing with candidate counts - Candidate pipeline (New, Contacted, Replied, Interviewed) - Candidate detail with parsed data and match score - 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### 9. Phase Two Enhancements (AI-assisted Interview Scheduler) 824 | 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 Endpoint### 9. Phase Two Enhancements (AI-assisted Interview Scheduler) 824 s Business | 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 | ### 9. Phase Two Enhancements (AI-assisted Interview Scheduler) 824 **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) | | 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 | | 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')) Business| Job status | | 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) --- ##### 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 | | 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) | | resume_hash | VARCHAR(64) | UNIQUE, NOT NULL | SHA-256 hash for deduplication | | source | VARCHAR(50) | DEFAULT 'email' | Source of resume (email, upload) | | blocked | BOOLEAN | DEFAULT FALSE | Candidate-job status | | created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Resume received time | | updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Last update time | **Indexes:** - `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..." }### 9. Phase Two Enhancements (AI-assisted Interview Scheduler) 824 ], "education": [ { "degree": "B.S. Computer Science", "institution": "MIT", "year": 2018 } ], "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: managers **Purpose:** Store manager profiles responsible for interviews or job coordination. | 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 dBusinessate | | 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: communicationBusiness **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 | Column | Type | Constraints | Description | |--------------|-------------|--------------------------------------|---------------------------------------| | template_id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique template identifier | | created_by | UUID | FOREIGN KEY REFERENCES users(user_id) | User who created the template | | name | VARCHAR(255)| NOT NULL | Template name | | type | VARCHAR(50) | NOT NULL, CHECK (type IN ('outreach','follow_up','reply','manual')) | Type of template | | subject | VARCHAR(500)| NOT NULL | Email subject template | | body | TEXT | NOT NULL | Email body template | | created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Template creation time | | updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Last update time | **Indexes:** - `idx_templates_user` on `created_by` (fetch templates by user) - `idx_templates_type` on `type` (filter templates by type) --- #### 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 | | users | templates | One-to-Many | templates.created_by → users.user_id | CASCADE | | 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 | | templates | communication | One-to-Many | communication.template_id → templates.template_id | SET NULL | **Cascade Deletion Logic:** - 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 - When a user is deleted, all their templates are deleted - When a template is deleted, communications referencing it will have `template_id` set to NULL --- #### 4.4 pgvector Configuration ### 9. Phase Two Enhancements (AI-assisted Interview Scheduler) 824 **Extension Setup:** Business ```sql CREATE EXTENSION IF NOT EXISTS vector; ``` --- ### 5. Business Workflow ##### 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 ✅ --- ### 6. System Architecture  --- ### 7. Sequence Diagram  --- ### 8. Flow Diagram  --- ### 9. Phase Two Enhancements #### AI-assisted interview scheduler - 🧑💼 1. Manager & Availability Setup - HR assigns one or more managers to each job. - Managers define their available time slots for interviews. - System ensures no overlapping interview slots. - 🎯 2. Candidate Invitation - HR shortlists candidates for interviews. - AI automatically drafts and sends invitation emails with available time slots. - Each invitation is recorded for tracking and status updates. - 📬 3. Candidate Response - The system listens for candidate replies. - AI interprets their response (acceptance, decline, or preferred slot). - Once confirmed, the selected slot is blocked to prevent double booking. - 👨💼 4. Manager Confirmation - The system notifies the respective manager about the candidate’s selected slot. - Manager confirms or reschedules based on their convenience. - Their response is automatically updated in the system. - 🗓️ 5. Interview Scheduling - Once both sides confirm, the interview is officially scheduled. - A meeting link (Google Meet / Zoom) is generated automatically. - Confirmation emails are sent to all participants — HR, Manager, and Candidate. - 📊 6. Post-Interview Actions - After the interview, HR can mark it as completed. - Feedback and notes can be added for future reference. - The system reopens or manages availability slots for upcoming interviews.