Spaces:
Sleeping
Sleeping
File size: 8,649 Bytes
9a951d7 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 | -- =============================================================
-- MODULE: Resume & AI Analysis
-- File: docs/schema-resume.sql
-- Managed by: TypeORM (synchronize: true in development)
-- =============================================================
-- -----------------------------------------------------------
-- ENUMS
-- -----------------------------------------------------------
CREATE TYPE lens_type_enum AS ENUM (
'targeted', -- Lens A: Job-specific analysis (requires job description)
'general' -- Lens B: General resume quality analysis
);
CREATE TYPE confidence_level_enum AS ENUM (
'High',
'Medium',
'Low'
);
-- -----------------------------------------------------------
-- TABLE: resumes
-- Entity: src/modules/resume/entities/resume.entity.ts
-- Description: Stores metadata for every uploaded resume file.
-- The actual file lives in uploads/ (not in DB).
-- Extracted text is cached here after Python service runs.
-- -----------------------------------------------------------
CREATE TABLE resumes (
-- Primary key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Ownership
user_id UUID NOT NULL
REFERENCES "user"(id) ON DELETE CASCADE,
-- File metadata
original_file_name VARCHAR(255) NOT NULL,
file_path VARCHAR(500) NOT NULL, -- Relative path: uploads/<uuid>-<filename>
file_size_bytes INTEGER NOT NULL,
-- Extracted content (populated by Python service POST /extract)
extracted_text TEXT, -- Full raw text from pdfplumber
extraction_metadata JSONB,
/*
* extraction_metadata structure:
* {
* "pageCount": 2,
* "hasTables": true,
* "hasImages": false,
* "confidence": 0.95
* }
*/
-- State flag
is_extracted BOOLEAN NOT NULL DEFAULT FALSE,
-- Timestamp
uploaded_at TIMESTAMP NOT NULL DEFAULT NOW()
);
-- Indexes
CREATE INDEX idx_resumes_user_id ON resumes (user_id);
CREATE INDEX idx_resumes_is_extracted ON resumes (user_id, is_extracted);
-- -----------------------------------------------------------
-- TABLE: resume_analyses
-- Entity: src/modules/resume/entities/resume-analysis.entity.ts
-- Description: AI-generated analysis result for one resume.
-- Each analysis is for a specific lens type.
-- A single resume can have multiple analyses over time.
-- -----------------------------------------------------------
CREATE TABLE resume_analyses (
-- Primary key
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Foreign keys
resume_id UUID NOT NULL
REFERENCES resumes(id) ON DELETE CASCADE,
user_id UUID NOT NULL
REFERENCES "user"(id) ON DELETE CASCADE,
-- Analysis configuration
lens_type lens_type_enum NOT NULL,
-- Lens A (targeted) fields — NULL when lens_type = 'general'
job_description TEXT,
job_title VARCHAR(200),
company VARCHAR(200),
-- Scores (0-100, 2 decimal places)
overall_score NUMERIC(5, 2) NOT NULL,
ats_score NUMERIC(5, 2) NOT NULL, -- ATS keyword match
content_quality_score NUMERIC(5, 2) NOT NULL, -- Writing quality & clarity
experience_score NUMERIC(5, 2) NOT NULL, -- Impact of work history
skills_score NUMERIC(5, 2) NOT NULL, -- Skills section quality
achievement_score NUMERIC(5, 2) NOT NULL, -- Quantified achievements
formatting_score NUMERIC(5, 2) NOT NULL, -- Layout & structure
relevance_score NUMERIC(5, 2), -- Job-match relevance (Lens A only)
-- Qualitative outcome
interpretation_band VARCHAR(100) NOT NULL, -- e.g. "Strong Match", "Needs Work"
confidence_level confidence_level_enum NOT NULL DEFAULT 'High',
-- Detailed feedback (JSONB)
remarks JSONB NOT NULL,
/*
* remarks structure:
* {
* "strengths": ["Strong quantified achievements", "ATS-friendly keywords present"],
* "weaknesses": ["Missing skills section", "No measurable impact in role 2"],
* "actionable": ["Add 3 bullet points with metrics", "Include a skills section"]
* }
*/
-- Debug / audit
raw_llm_response TEXT, -- Full LLM JSON string before parsing
processing_time_ms INTEGER, -- End-to-end latency for this analysis
-- Timestamp
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
-- Indexes
CREATE INDEX idx_resume_analyses_resume_id ON resume_analyses (resume_id);
CREATE INDEX idx_resume_analyses_user_id ON resume_analyses (user_id);
CREATE INDEX idx_resume_analyses_lens ON resume_analyses (user_id, lens_type);
CREATE INDEX idx_resume_analyses_created ON resume_analyses (user_id, created_at DESC);
-- -----------------------------------------------------------
-- VARIABLE REFERENCE — RESUME & AI MODULE
-- -----------------------------------------------------------
--
-- resumes:
-- user_id → Owner; used to scope all resume queries
-- original_file_name → Displayed in UI; original filename from upload
-- file_path → Server disk path; fed to Python extraction service
-- file_size_bytes → Validated at upload (max 5 MB = 5,242,880 bytes)
-- extracted_text → Full PDF text; injected into Groq LLM prompt
-- extraction_metadata → JSONB: pageCount, hasTables, hasImages, confidence
-- is_extracted → Guards analysis endpoint (400 if FALSE)
--
-- resume_analyses:
-- resume_id → Which resume was analysed
-- user_id → Redundant FK for fast per-user queries without JOIN
-- lens_type → 'targeted' (Lens A) or 'general' (Lens B)
-- job_description → Full JD text; used in targeted scoring prompt
-- job_title → Job title for context; shown in results UI
-- company → Company name for context; shown in results UI
-- overall_score → Weighted average of all subscores (0-100)
-- ats_score → Keyword/format ATS compatibility (0-100)
-- content_quality_score → Writing clarity and resume content quality (0-100)
-- experience_score → Depth and impact of work experience (0-100)
-- skills_score → Skills section completeness and relevance (0-100)
-- achievement_score → Quantified and measurable achievements (0-100)
-- formatting_score → Layout, structure, visual readability (0-100)
-- relevance_score → Job description match relevance — Lens A only (0-100)
-- interpretation_band → Human-readable verdict: "Excellent" / "Good" / "Needs Work" / etc.
-- confidence_level → LLM confidence: 'High' | 'Medium' | 'Low'
-- remarks → JSONB: { strengths[], weaknesses[], actionable[] }
-- raw_llm_response → Unprocessed Groq API response; kept for debugging
-- processing_time_ms → Latency metric; useful for performance monitoring
--
-- -----------------------------------------------------------
-- AI MODULE VARIABLES (no database table — stateless service)
-- -----------------------------------------------------------
-- Defined in: src/modules/ai/groq.service.ts
--
-- GROQ_API_KEY → .env — Groq API key (starts with gsk_...)
-- MODEL → Hardcoded: 'llama-3.3-70b-versatile'
-- TEMPERATURE → 0.1 (deterministic scoring output)
-- MAX_TOKENS → 2048 (enough for full JSON analysis response)
-- System prompt → Built by UserContextService.generateUserContext()
-- User prompt → Built by ResumeAnalysisService with resume text + JD
--
-- ATS Checker (ats-checker.util.ts):
-- extractedText → Resume plain text
-- jobDescription → Job description (Lens A only)
-- Returns: { score, matchedKeywords[], missingKeywords[] }
--
-- Score Parser (score-parser.util.ts):
-- rawJson → LLM JSON string
-- Returns typed AnalysisResult with all numeric scores parsed
|