Resume/CV Database

Objective

Create a resume database with skills extraction and candidate matching. This enables recruitment teams to search candidates by skills, experience, and qualifications efficiently.

Step 1: Create Candidates Table

Create a table for candidate information.

CREATE TABLE candidates (
    id INTEGER PRIMARY KEY,
    resume_pdf PDF,
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    location VARCHAR(100),
    current_title VARCHAR(100),
    years_experience INTEGER,
    education_level VARCHAR(50),
    highest_degree VARCHAR(100),
    summary TEXT,
    resume_embedding VECTOR(384),
    source VARCHAR(50),
    status VARCHAR(20) DEFAULT 'new',
    applied_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Skills Table

Create a table for candidate skills.

CREATE TABLE candidate_skills (
    id INTEGER PRIMARY KEY,
    candidate_id INTEGER NOT NULL,
    skill_name VARCHAR(100) NOT NULL,
    skill_category VARCHAR(50),
    proficiency_level VARCHAR(20),
    years_experience INTEGER,
    is_primary BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id)
);

Step 3: Create Experience Table

Track work experience.

CREATE TABLE candidate_experience (
    id INTEGER PRIMARY KEY,
    candidate_id INTEGER NOT NULL,
    company_name VARCHAR(255) NOT NULL,
    job_title VARCHAR(100) NOT NULL,
    start_date DATE,
    end_date DATE,
    is_current BOOLEAN DEFAULT FALSE,
    description TEXT,
    location VARCHAR(100),
    FOREIGN KEY (candidate_id) REFERENCES candidates(id)
);

Step 4: Insert Sample Candidates

Add sample candidate records.

INSERT INTO candidates (id, full_name, email, phone, location, current_title, years_experience, education_level, highest_degree, summary, source, status, applied_date) VALUES
    (1, 'Alice Johnson', 'alice.johnson@email.com', '555-0101', 'San Francisco, CA', 'Senior Software Engineer', 8, 'Masters', 'MS Computer Science', 'Experienced full-stack developer specializing in cloud-native applications and microservices architecture.', 'LinkedIn', 'screening', '2024-01-15'),
    (2, 'Bob Smith', 'bob.smith@email.com', '555-0102', 'New York, NY', 'Data Scientist', 5, 'Masters', 'MS Data Science', 'Machine learning specialist with experience in NLP and computer vision projects.', 'Indeed', 'interview', '2024-01-18'),
    (3, 'Carol Williams', 'carol.williams@email.com', '555-0103', 'Austin, TX', 'DevOps Engineer', 6, 'Bachelors', 'BS Information Technology', 'Cloud infrastructure expert with strong Kubernetes and CI/CD experience.', 'Referral', 'new', '2024-01-20'),
    (4, 'David Chen', 'david.chen@email.com', '555-0104', 'Seattle, WA', 'Product Manager', 7, 'MBA', 'MBA', 'Technical product manager with background in software development and agile methodologies.', 'Company Website', 'offer', '2024-01-10'),
    (5, 'Emily Davis', 'emily.davis@email.com', '555-0105', 'Boston, MA', 'Frontend Developer', 4, 'Bachelors', 'BS Computer Science', 'React and TypeScript specialist focused on building accessible user interfaces.', 'LinkedIn', 'screening', '2024-01-22'),
    (6, 'Frank Miller', 'frank.miller@email.com', '555-0106', 'Chicago, IL', 'Backend Developer', 3, 'Bachelors', 'BS Software Engineering', 'Java and Python developer with experience in API development and database design.', 'Indeed', 'new', '2024-01-25');

Step 5: Insert Candidate Skills

Add skills for candidates.

INSERT INTO candidate_skills (id, candidate_id, skill_name, skill_category, proficiency_level, years_experience, is_primary) VALUES
    -- Alice's skills
    (1, 1, 'Python', 'Programming', 'expert', 8, TRUE),
    (2, 1, 'JavaScript', 'Programming', 'advanced', 6, TRUE),
    (3, 1, 'AWS', 'Cloud', 'expert', 5, TRUE),
    (4, 1, 'Docker', 'DevOps', 'advanced', 4, FALSE),
    (5, 1, 'PostgreSQL', 'Database', 'advanced', 6, FALSE),
    -- Bob's skills
    (6, 2, 'Python', 'Programming', 'expert', 5, TRUE),
    (7, 2, 'Machine Learning', 'AI/ML', 'expert', 5, TRUE),
    (8, 2, 'TensorFlow', 'AI/ML', 'advanced', 4, TRUE),
    (9, 2, 'SQL', 'Database', 'advanced', 5, FALSE),
    (10, 2, 'NLP', 'AI/ML', 'advanced', 3, FALSE),
    -- Carol's skills
    (11, 3, 'Kubernetes', 'DevOps', 'expert', 5, TRUE),
    (12, 3, 'AWS', 'Cloud', 'expert', 6, TRUE),
    (13, 3, 'Terraform', 'DevOps', 'advanced', 4, TRUE),
    (14, 3, 'CI/CD', 'DevOps', 'expert', 6, FALSE),
    -- Emily's skills
    (15, 5, 'React', 'Frontend', 'expert', 4, TRUE),
    (16, 5, 'TypeScript', 'Programming', 'expert', 4, TRUE),
    (17, 5, 'CSS', 'Frontend', 'advanced', 4, FALSE),
    (18, 5, 'Accessibility', 'Frontend', 'advanced', 3, FALSE);

Step 6: Insert Work Experience

Add work history.

INSERT INTO candidate_experience (id, candidate_id, company_name, job_title, start_date, end_date, is_current, description, location) VALUES
    -- Alice's experience
    (1, 1, 'TechCorp Inc', 'Senior Software Engineer', '2021-03-01', NULL, TRUE, 'Lead development of microservices platform handling 1M+ daily transactions', 'San Francisco, CA'),
    (2, 1, 'StartupXYZ', 'Software Engineer', '2018-06-01', '2021-02-28', FALSE, 'Built full-stack web applications using React and Node.js', 'San Francisco, CA'),
    (3, 1, 'BigTech Co', 'Junior Developer', '2016-01-01', '2018-05-31', FALSE, 'Developed internal tools and automation scripts', 'Palo Alto, CA'),
    -- Bob's experience
    (4, 2, 'AI Solutions Ltd', 'Data Scientist', '2022-01-01', NULL, TRUE, 'Developing NLP models for document classification', 'New York, NY'),
    (5, 2, 'DataAnalytics Inc', 'ML Engineer', '2019-06-01', '2021-12-31', FALSE, 'Built recommendation systems for e-commerce platform', 'New York, NY'),
    -- Carol's experience
    (6, 3, 'CloudOps Inc', 'DevOps Engineer', '2020-08-01', NULL, TRUE, 'Managing Kubernetes clusters across multiple cloud providers', 'Austin, TX'),
    (7, 3, 'WebServices Co', 'Systems Administrator', '2018-02-01', '2020-07-31', FALSE, 'Maintained on-premise infrastructure and began cloud migration', 'Austin, TX');

Step 7: Search Candidates by Skills

Find candidates with specific skills.

SELECT
    c.full_name,
    c.current_title,
    c.years_experience,
    c.location,
    c.status,
    cs.skill_name,
    cs.proficiency_level
FROM candidates c
INNER JOIN candidate_skills cs ON c.id = cs.candidate_id
WHERE cs.skill_name IN ('Python', 'AWS')
  AND cs.proficiency_level IN ('expert', 'advanced')
ORDER BY c.years_experience DESC;

Step 8: Candidate Profile View

Get complete candidate profile.

SELECT
    c.full_name,
    c.email,
    c.current_title,
    c.years_experience,
    c.education_level,
    c.highest_degree,
    c.summary,
    c.status
FROM candidates c
WHERE c.id = 1;

Step 9: Skills Summary for Candidate

Get all skills for a candidate.

SELECT
    skill_name,
    skill_category,
    proficiency_level,
    years_experience,
    is_primary
FROM candidate_skills
WHERE candidate_id = 1
ORDER BY is_primary DESC, years_experience DESC;

Step 10: Experience Timeline

Get work history for a candidate.

SELECT
    company_name,
    job_title,
    start_date,
    end_date,
    is_current,
    location
FROM candidate_experience
WHERE candidate_id = 1
ORDER BY start_date DESC;

Step 11: Skills Inventory

Analyze available skills in database.

SELECT
    skill_name,
    skill_category,
    COUNT(*) as candidate_count,
    AVG(years_experience) as avg_experience
FROM candidate_skills
GROUP BY skill_name, skill_category
ORDER BY candidate_count DESC;

Step 12: Pipeline Status

Get recruitment pipeline status.

SELECT
    status,
    COUNT(*) as candidate_count,
    AVG(years_experience) as avg_experience
FROM candidates
GROUP BY status
ORDER BY
    CASE status
        WHEN 'new' THEN 1
        WHEN 'screening' THEN 2
        WHEN 'interview' THEN 3
        WHEN 'offer' THEN 4
        WHEN 'hired' THEN 5
        ELSE 6
    END;

Cleanup (Optional)

DROP TABLE IF EXISTS candidate_experience;
DROP TABLE IF EXISTS candidate_skills;
DROP TABLE IF EXISTS candidates;

Expected Outcomes

• Resumes stored with metadata
• Skills searchable
• Experience tracked
• Pipeline status monitored
• Skills inventory available

Proficiency Levels

Key Concepts Learned

• Resume data extraction
• Skills-based searching
• Work history tracking
• Recruitment pipeline
• Skills inventory analysis