Build a Resume Database: Extract and Query Hundreds of Resumes as SQLite

{  "candidates": {    "fields": [      { "key": "full_name", "type": "string", "prompt": "Candidate's full name as shown on resume" },      { "key": "email", "type": "string", "prompt": "Primary email address" },      { "key": "phone", "type": "string", "prompt": "Phone number with country code if present" },      { "key": "location", "type": "string", "prompt": "City and state/country (e.g., 'San Francisco, CA')" },      { "key": "linkedin_url", "type": "string", "prompt": "LinkedIn profile URL if present" },      { "key": "portfolio_url", "type": "string", "prompt": "Personal website or portfolio URL if present" },      { "key": "summary", "type": "string", "prompt": "Professional summary or objective statement" },      { "key": "years_experience", "type": "number", "prompt": "Total years of professional work experience" },      { "key": "current_title", "type": "string", "prompt": "Most recent or current job title" }    ]  },  "work_experience": {    "parent": "candidates",    "repeating": true,    "fields": [      { "key": "candidate_id", "type": "number", "prompt": "Link to parent candidate id" },      { "key": "company", "type": "string", "prompt": "Company or organization name" },      { "key": "title", "type": "string", "prompt": "Job title or role" },      { "key": "start_date", "type": "date", "prompt": "Start date in YYYY-MM format" },      { "key": "end_date", "type": "string", "prompt": "End date in YYYY-MM format, or 'Present' if current" },      { "key": "description", "type": "string", "prompt": "Job responsibilities, achievements, and key projects" },      { "key": "technologies_used", "type": "string", "prompt": "Technologies, tools, or skills used in this role" }    ]  },  "education": {    "parent": "candidates",    "repeating": true,    "fields": [      { "key": "candidate_id", "type": "number", "prompt": "Link to parent candidate id" },      { "key": "institution", "type": "string", "prompt": "School, university, or educational institution name" },      { "key": "degree", "type": "string", "prompt": "Degree type: BS, BA, MS, MBA, PhD, etc." },      { "key": "field_of_study", "type": "string", "prompt": "Major or field of study" },      { "key": "graduation_year", "type": "number", "prompt": "Year of graduation (YYYY format)" },      { "key": "gpa", "type": "string", "prompt": "GPA if clearly stated (e.g., '3.8/4.0')" }    ]  },  "skills": {    "parent": "candidates",    "repeating": true,    "fields": [      { "key": "candidate_id", "type": "number", "prompt": "Link to parent candidate id" },      { "key": "skill_name", "type": "string", "prompt": "Name of the skill, technology, or tool" },      { "key": "proficiency_level", "type": "string", "prompt": "Proficiency if stated: beginner, intermediate, advanced, expert" }    ]  }}

skills.skill_name: "Extract individual skills from the skills section. Return each skill separately (e.g., 'Python', 'React', 'AWS'). Do not include proficiency levels in the skill name."work_experience.description: "Complete description of responsibilities and achievements for this role. Include quantifiable results if present (e.g., 'increased sales by 30%')."years_experience: "Calculate total years of professional work experience by summing all work history. Count partial years (e.g., 6 months = 0.5 years). Exclude internships unless specifically labeled as professional roles."

SELECT DISTINCT  c.full_name,  c.email,  c.current_title,  c.years_experience,  GROUP_CONCAT(DISTINCT s.skill_name) as matching_skillsFROM candidates cJOIN skills s ON s.candidate_id = c.idWHERE s.skill_name IN ('Python', 'React', 'AWS', 'Docker')GROUP BY c.idHAVING COUNT(DISTINCT s.skill_name) >= 3  -- Must have at least 3 of the 4 skillsORDER BY c.years_experience DESC;

SELECT  full_name,  email,  location,  current_title,  years_experienceFROM candidatesWHERE years_experience BETWEEN 5 AND 10  AND location LIKE '%San Francisco%'ORDER BY years_experience DESC;

SELECT  c.full_name,  c.email,  c.phone,  we.company,  we.title,  we.start_date,  we.end_dateFROM candidates cJOIN work_experience we ON we.candidate_id = c.idWHERE we.company IN ('Google', 'Meta', 'Amazon', 'Microsoft', 'Apple')ORDER BY c.full_name, we.start_date DESC;

SELECT  c.full_name,  c.email,  we.company,  we.title,  we.descriptionFROM candidates cJOIN work_experience we ON we.candidate_id = c.idWHERE we.description LIKE '%machine learning%'   OR we.description LIKE '%artificial intelligence%'   OR we.technologies_used LIKE '%TensorFlow%'   OR we.technologies_used LIKE '%PyTorch%';

SELECT  c.full_name,  c.email,  e.degree,  e.field_of_study,  e.institution,  e.graduation_yearFROM candidates cJOIN education e ON e.candidate_id = c.idWHERE e.degree IN ('MS', 'PhD')  AND e.field_of_study LIKE '%Computer Science%'ORDER BY e.graduation_year DESC;

SELECT  skill_name,  COUNT(*) as candidate_count,  ROUND(COUNT(*) * 100.0 / (SELECT COUNT(DISTINCT candidate_id) FROM skills), 2) as percentageFROM skillsGROUP BY skill_nameORDER BY candidate_count DESCLIMIT 20;

SELECT DISTINCT  c.full_name,  c.email,  c.years_experience,  we.title,  we.company,  we.end_dateFROM candidates cJOIN work_experience we ON we.candidate_id = c.idWHERE (we.title LIKE '%Senior%'   OR we.title LIKE '%Lead%'   OR we.title LIKE '%Principal%'   OR we.title LIKE '%Staff%')  AND (we.end_date = 'Present' OR we.end_date >= '2023-01')ORDER BY c.years_experience DESC;

import sqlite3import pandas as pdfrom typing import List, Dictdef find_matching_candidates(    db_path: str,    required_skills: List[str],    min_experience: int = 0,    target_companies: List[str] = None,    location: str = None) -> pd.DataFrame:    """    Find candidates matching specific criteria.    Args:        db_path: Path to the downloaded SQLite database        required_skills: List of required skills (e.g., ['Python', 'React'])        min_experience: Minimum years of experience        target_companies: Optional list of companies to filter by        location: Optional location filter (partial match)    Returns:        DataFrame of matching candidates with their details    """    conn = sqlite3.connect(db_path)    # Build dynamic query based on filters    query = """    SELECT DISTINCT        c.id,        c.full_name,        c.email,        c.phone,        c.location,        c.current_title,        c.years_experience,        c.linkedin_url,        GROUP_CONCAT(DISTINCT s.skill_name) as all_skills,        GROUP_CONCAT(DISTINCT we.company) as companies    FROM candidates c    LEFT JOIN skills s ON s.candidate_id = c.id    LEFT JOIN work_experience we ON we.candidate_id = c.id    WHERE c.years_experience >= ?    """    params = [min_experience]    if location:        query += " AND c.location LIKE ?"        params.append(f"%{location}%")    query += " GROUP BY c.id"    # Execute query    df = pd.read_sql_query(query, conn, params=params)    # Filter by required skills    if required_skills:        def has_required_skills(skills_str):            if pd.isna(skills_str):                return False            skills = skills_str.lower().split(',')            return all(                any(req.lower() in skill for skill in skills)                for req in required_skills            )        df = df[df['all_skills'].apply(has_required_skills)]    # Filter by target companies    if target_companies:        def worked_at_target(companies_str):            if pd.isna(companies_str):                return False            return any(                target.lower() in companies_str.lower()                for target in target_companies            )        df = df[df['companies'].apply(worked_at_target)]    conn.close()    return df.sort_values('years_experience', ascending=False)# Example usagecandidates = find_matching_candidates(    db_path='resumes.sqlite',    required_skills=['Python', 'AWS', 'Docker'],    min_experience=5,    target_companies=['Google', 'Amazon', 'Microsoft'],    location='San Francisco')print(f"Found {len(candidates)} matching candidates:")print(candidates[['full_name', 'email', 'years_experience', 'current_title']])

import sqlite3import csvdef export_candidates_to_csv(db_path: str, output_file: str):    """Export all candidates to CSV for ATS import."""    conn = sqlite3.connect(db_path)    cursor = conn.cursor()    cursor.execute("""        SELECT            c.full_name,            c.email,            c.phone,            c.location,            c.current_title,            c.years_experience,            c.linkedin_url,            GROUP_CONCAT(DISTINCT s.skill_name, '; ') as skills,            GROUP_CONCAT(DISTINCT we.company || ' (' || we.title || ')', '; ') as experience        FROM candidates c        LEFT JOIN skills s ON s.candidate_id = c.id        LEFT JOIN work_experience we ON we.candidate_id = c.id        GROUP BY c.id        ORDER BY c.full_name    """)    with open(output_file, 'w', newline='', encoding='utf-8') as f:        writer = csv.writer(f)        writer.writerow([            'Full Name', 'Email', 'Phone', 'Location',            'Current Title', 'Years Experience', 'LinkedIn',            'Skills', 'Work History'        ])        writer.writerows(cursor.fetchall())    conn.close()    print(f"Exported to {output_file}")# Export all candidatesexport_candidates_to_csv('resumes.sqlite', 'candidates_import.csv')

from anthropic import Anthropicimport sqlite3def ai_screen_candidate(    db_path: str,    candidate_id: int,    job_description: str) -> Dict[str, any]:    """    Use Claude to evaluate candidate fit for a specific role.    Args:        db_path: Path to SQLite database        candidate_id: ID of candidate to evaluate        job_description: Full job description text    Returns:        Dictionary with score, reasoning, and strengths/concerns    """    conn = sqlite3.connect(db_path)    cursor = conn.cursor()    # Get complete candidate profile    cursor.execute("""        SELECT            c.*,            GROUP_CONCAT(DISTINCT we.company || ' - ' || we.title ||                         ' (' || we.start_date || ' to ' || we.end_date || '): ' ||                         COALESCE(we.description, 'No description'), '\n\n') as experience,            GROUP_CONCAT(DISTINCT s.skill_name, ', ') as skills,            GROUP_CONCAT(DISTINCT e.degree || ' in ' || e.field_of_study ||                         ' from ' || e.institution || ' (' || e.graduation_year || ')', '\n') as education        FROM candidates c        LEFT JOIN work_experience we ON we.candidate_id = c.id        LEFT JOIN skills s ON s.candidate_id = c.id        LEFT JOIN education e ON e.candidate_id = c.id        WHERE c.id = ?        GROUP BY c.id    """, [candidate_id])    candidate = cursor.fetchone()    conn.close()    if not candidate:        return {"error": "Candidate not found"}    # Build candidate summary    candidate_summary = f"""CANDIDATE PROFILEName: {candidate[1]}Location: {candidate[4]}Current Title: {candidate[9]}Years of Experience: {candidate[8]}Email: {candidate[2]}LinkedIn: {candidate[5]}SUMMARY{candidate[7] or 'No summary provided'}WORK EXPERIENCE{candidate[10] or 'No work experience listed'}SKILLS{candidate[11] or 'No skills listed'}EDUCATION{candidate[12] or 'No education listed'}"""    client = Anthropic(api_key="your-api-key")    message = client.messages.create(        model="claude-sonnet-4-5-20250929",        max_tokens=2000,        messages=[{            "role": "user",            "content": f"""You are an expert technical recruiter. Evaluate this candidate's fit for the following role.JOB DESCRIPTION:{job_description}{candidate_summary}Provide your evaluation in this format:1. OVERALL FIT SCORE: [1-10]2. KEY STRENGTHS: [bullet points]3. POTENTIAL CONCERNS: [bullet points]4. RECOMMENDATION: [Hire/Interview/Pass with reasoning]5. SUGGESTED INTERVIEW QUESTIONS: [3-5 questions to validate fit]"""        }]    )    return {        "candidate_id": candidate_id,        "candidate_name": candidate[1],        "evaluation": message.content[0].text    }# Screen a candidatejob_desc = """Senior Backend Engineer - Python/AWSWe're seeking a senior backend engineer with 5+ years of experience buildingscalable APIs using Python, AWS, and modern database technologies..."""result = ai_screen_candidate('resumes.sqlite', candidate_id=42, job_description=job_desc)print(result['evaluation'])

// Google Apps Script to query SQLite via web servicefunction importCandidates() {  // First, upload your SQLite to a web-accessible endpoint  // or use a service like Datasette to expose an API  const response = UrlFetchApp.fetch('https://your-datasette-instance.com/resumes/candidates.json?_shape=array');  const candidates = JSON.parse(response.getContentText());  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  // Clear existing data  sheet.clear();  // Write headers  sheet.getRange(1, 1, 1, 6).setValues([[    'Name', 'Email', 'Location', 'Title', 'Experience', 'Skills'  ]]);  // Write candidate data  const rows = candidates.map(c => [    c.full_name,    c.email,    c.location,    c.current_title,    c.years_experience,    c.skills  ]);  sheet.getRange(2, 1, rows.length, 6).setValues(rows);}

import express from 'express';import Database from 'better-sqlite3';import cors from 'cors';const app = express();const db = new Database('resumes.sqlite', { readonly: true });app.use(cors());app.use(express.json());// Search candidates by multiple criteriaapp.get('/api/candidates/search', (req, res) => {  const {    skills,    minExperience,    maxExperience,    location,    company,    degree  } = req.query;  let query = `    SELECT DISTINCT c.*    FROM candidates c    WHERE 1=1  `;  const params: any[] = [];  if (minExperience) {    query += ' AND c.years_experience >= ?';    params.push(Number(minExperience));  }  if (maxExperience) {    query += ' AND c.years_experience <= ?';    params.push(Number(maxExperience));  }  if (location) {    query += ' AND c.location LIKE ?';    params.push(`%${location}%`);  }  let candidates = db.prepare(query).all(...params);  // Filter by skills (requires join)  if (skills) {    const skillList = (skills as string).split(',').map(s => s.trim().toLowerCase());    candidates = candidates.filter(c => {      const candidateSkills = db.prepare(        'SELECT skill_name FROM skills WHERE candidate_id = ?'      ).all(c.id);      const candidateSkillNames = candidateSkills.map(        (s: any) => s.skill_name.toLowerCase()      );      return skillList.every(requiredSkill =>        candidateSkillNames.some(candidateSkill =>          candidateSkill.includes(requiredSkill)        )      );    });  }  // Filter by company experience  if (company) {    candidates = candidates.filter(c => {      const experience = db.prepare(        'SELECT company FROM work_experience WHERE candidate_id = ?'      ).all(c.id);      return experience.some((exp: any) =>        exp.company.toLowerCase().includes((company as string).toLowerCase())      );    });  }  // Filter by education  if (degree) {    candidates = candidates.filter(c => {      const education = db.prepare(        'SELECT degree FROM education WHERE candidate_id = ?'      ).all(c.id);      return education.some((edu: any) =>        edu.degree.toLowerCase() === (degree as string).toLowerCase()      );    });  }  res.json({    count: candidates.length,    candidates: candidates  });});// Get full candidate profileapp.get('/api/candidates/:id', (req, res) => {  const candidate = db.prepare('SELECT * FROM candidates WHERE id = ?').get(req.params.id);  if (!candidate) {    return res.status(404).json({ error: 'Candidate not found' });  }  const workExperience = db.prepare(    'SELECT * FROM work_experience WHERE candidate_id = ? ORDER BY start_date DESC'  ).all(req.params.id);  const education = db.prepare(    'SELECT * FROM education WHERE candidate_id = ? ORDER BY graduation_year DESC'  ).all(req.params.id);  const skills = db.prepare(    'SELECT * FROM skills WHERE candidate_id = ?'  ).all(req.params.id);  res.json({    ...candidate,    work_experience: workExperience,    education: education,    skills: skills  });});// Get skills analyticsapp.get('/api/analytics/skills', (req, res) => {  const skillStats = db.prepare(`    SELECT      skill_name,      COUNT(*) as candidate_count,      ROUND(COUNT(*) * 100.0 / (SELECT COUNT(DISTINCT candidate_id) FROM skills), 2) as percentage    FROM skills    GROUP BY skill_name    ORDER BY candidate_count DESC    LIMIT 50  `).all();  res.json(skillStats);});// Get company distributionapp.get('/api/analytics/companies', (req, res) => {  const companyStats = db.prepare(`    SELECT      company,      COUNT(DISTINCT candidate_id) as candidate_count    FROM work_experience    GROUP BY company    ORDER BY candidate_count DESC    LIMIT 30  `).all();  res.json(companyStats);});app.listen(3000, () => {  console.log('Resume API running on http://localhost:3000');});

import { useState } from 'react';interface Candidate {  id: number;  full_name: string;  email: string;  location: string;  current_title: string;  years_experience: number;}export default function CandidateSearch() {  const [results, setResults] = useState<Candidate[]>([]);  const [filters, setFilters] = useState({    skills: '',    minExperience: '',    location: '',    company: ''  });  const searchCandidates = async () => {    const queryParams = new URLSearchParams();    if (filters.skills) queryParams.append('skills', filters.skills);    if (filters.minExperience) queryParams.append('minExperience', filters.minExperience);    if (filters.location) queryParams.append('location', filters.location);    if (filters.company) queryParams.append('company', filters.company);    const response = await fetch(`http://localhost:3000/api/candidates/search?${queryParams}`);    const data = await response.json();    setResults(data.candidates);  };  return (    <div className="p-6">      <h1 className="text-2xl font-bold mb-6">Resume Database Search</h1>      <div className="grid grid-cols-2 gap-4 mb-6">        <input          type="text"          placeholder="Skills (comma-separated)"          value={filters.skills}          onChange={e => setFilters({...filters, skills: e.target.value})}          className="border p-2 rounded"        />        <input          type="number"          placeholder="Min years experience"          value={filters.minExperience}          onChange={e => setFilters({...filters, minExperience: e.target.value})}          className="border p-2 rounded"        />        <input          type="text"          placeholder="Location"          value={filters.location}          onChange={e => setFilters({...filters, location: e.target.value})}          className="border p-2 rounded"        />        <input          type="text"          placeholder="Company"          value={filters.company}          onChange={e => setFilters({...filters, company: e.target.value})}          className="border p-2 rounded"        />      </div>      <button        onClick={searchCandidates}        className="bg-blue-600 text-white px-6 py-2 rounded hover:bg-blue-700"      >        Search Candidates      </button>      <div className="mt-8">        <h2 className="text-xl font-semibold mb-4">          Results ({results.length} candidates)        </h2>        <div className="space-y-4">          {results.map(candidate => (            <div key={candidate.id} className="border p-4 rounded shadow-sm">              <h3 className="font-bold">{candidate.full_name}</h3>              <p className="text-gray-600">{candidate.current_title}</p>              <div className="mt-2 text-sm text-gray-500">                <p>{candidate.location} • {candidate.years_experience} years experience</p>                <p>{candidate.email}</p>              </div>            </div>          ))}        </div>      </div>    </div>  );}

-- Define your target skill set and compare against available candidatesWITH desired_skills(skill) AS (  VALUES    ('Kubernetes'),    ('Docker'),    ('AWS'),    ('Terraform'),    ('Python'),    ('Go'),    ('React'),    ('PostgreSQL'))SELECT  d.skill,  COUNT(s.skill_name) as candidates_with_skill,  ROUND(COUNT(s.skill_name) * 100.0 / (SELECT COUNT(*) FROM candidates), 2) as coverage_percentage,  (SELECT COUNT(*) FROM candidates) - COUNT(s.skill_name) as candidates_missing_skillFROM desired_skills dLEFT JOIN skills s ON LOWER(s.skill_name) = LOWER(d.skill)GROUP BY d.skillORDER BY coverage_percentage DESC;

SELECT  CASE    WHEN years_experience < 2 THEN '0-2 years (Junior)'    WHEN years_experience < 5 THEN '2-5 years (Mid-level)'    WHEN years_experience < 10 THEN '5-10 years (Senior)'    ELSE '10+ years (Expert/Lead)'  END as experience_level,  COUNT(*) as candidate_count,  ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM candidates), 2) as percentageFROM candidatesGROUP BY experience_levelORDER BY MIN(years_experience);

SELECT  e.institution,  COUNT(DISTINCT e.candidate_id) as candidate_count,  GROUP_CONCAT(DISTINCT e.degree) as degrees_offered,  GROUP_CONCAT(DISTINCT e.field_of_study) as fieldsFROM education eGROUP BY e.institutionORDER BY candidate_count DESCLIMIT 20;

-- Find candidates who moved from Company A to Company BSELECT  c.full_name,  c.email,  we1.company as previous_company,  we1.title as previous_title,  we2.company as current_company,  we2.title as current_titleFROM candidates cJOIN work_experience we1 ON we1.candidate_id = c.idJOIN work_experience we2 ON we2.candidate_id = c.idWHERE we1.company = 'Google'  AND we2.company = 'Amazon'  AND we1.start_date < we2.start_date;

SELECT  CASE    WHEN location LIKE '%San Francisco%' OR location LIKE '%SF%' THEN 'San Francisco Bay Area'    WHEN location LIKE '%New York%' OR location LIKE '%NYC%' THEN 'New York'    WHEN location LIKE '%Seattle%' THEN 'Seattle'    WHEN location LIKE '%Austin%' THEN 'Austin'    WHEN location LIKE '%Remote%' THEN 'Remote'    ELSE 'Other'  END as location_group,  COUNT(*) as candidate_countFROM candidatesGROUP BY location_groupORDER BY candidate_count DESC;

import sqlite3def merge_resume_databases(output_db: str, input_dbs: list):    """Merge multiple resume databases into one."""    conn_out = sqlite3.connect(output_db)    cursor_out = conn_out.cursor()    # Create tables in output database    cursor_out.execute('''        CREATE TABLE IF NOT EXISTS candidates (            id INTEGER PRIMARY KEY,            full_name TEXT,            email TEXT,            phone TEXT,            location TEXT,            linkedin_url TEXT,            portfolio_url TEXT,            summary TEXT,            years_experience REAL,            current_title TEXT        )    ''')    # Similar CREATE TABLE statements for other tables...    candidate_id_offset = 0    for db_file in input_dbs:        conn_in = sqlite3.connect(db_file)        cursor_in = conn_in.cursor()        # Copy candidates with ID offset        cursor_in.execute('SELECT * FROM candidates')        for row in cursor_in.fetchall():            new_row = (row[0] + candidate_id_offset,) + row[1:]            cursor_out.execute('''                INSERT INTO candidates VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)            ''', new_row)        # Copy work_experience with updated foreign keys        cursor_in.execute('SELECT * FROM work_experience')        for row in cursor_in.fetchall():            new_row = (None, row[1] + candidate_id_offset) + row[2:]            cursor_out.execute('''                INSERT INTO work_experience (id, candidate_id, company, title, start_date, end_date, description, technologies_used)                VALUES (?, ?, ?, ?, ?, ?, ?, ?)            ''', new_row)        # Update offset for next database        cursor_in.execute('SELECT MAX(id) FROM candidates')        max_id = cursor_in.fetchone()[0] or 0        candidate_id_offset += max_id        conn_in.close()    conn_out.commit()    conn_out.close()    print(f"Merged {len(input_dbs)} databases into {output_db}")# Merge multiple job posting databasesmerge_resume_databases(    output_db='all_candidates_2025.sqlite',    input_dbs=[        'backend_engineer_q1.sqlite',        'frontend_engineer_q1.sqlite',        'data_scientist_q1.sqlite'    ])

-- Create indexes for common query patternsCREATE INDEX idx_candidates_experience ON candidates(years_experience);CREATE INDEX idx_candidates_location ON candidates(location);CREATE INDEX idx_skills_name ON skills(skill_name);CREATE INDEX idx_skills_candidate ON skills(candidate_id);CREATE INDEX idx_work_company ON work_experience(company);CREATE INDEX idx_work_candidate ON work_experience(candidate_id);-- Analyze database for query optimizationANALYZE;