zhaopin/references/database-schema.md

招聘数据库 Schema

路径： ~/.hermes/data/recruitment/recruitment.db 引擎： SQLite 3（WAL 模式） Node.js： better-sqlite3 Python： sqlite3（标准库）

candidates 表

CREATE TABLE candidates (
    id              TEXT PRIMARY KEY,       -- MD5(name|age|edu|exp|school)[:12]
    platform        TEXT NOT NULL DEFAULT 'zhaopin',
    account         TEXT NOT NULL DEFAULT 'hr1',  -- v0.2 多账号：hr1/hr2
    name            TEXT NOT NULL,
    age             INTEGER,
    education       TEXT,                   -- 本科/硕士/大专等
    experience      TEXT,                   -- 原始文本如"3年经验"
    exp_years       REAL,                   -- 数值化经验年数，便于筛选
    school          TEXT,
    expected_city   TEXT,
    expected_salary TEXT,
    activity         TEXT DEFAULT '',      -- 活跃度文本如"10分钟前有回复"
    role_tag        TEXT DEFAULT '',       -- 岗位推荐标签：有经验岗/应届生岗/不限
    score           INTEGER,                -- 7维度总分 7-35
    score_level     TEXT,                   -- 推荐/待定/不推荐
    greeted         INTEGER NOT NULL DEFAULT 0,
    greeted_at      TEXT,
    raw_text        TEXT,                   -- 完整简历原文
    education_detail TEXT,
    work_detail     TEXT,
    cert_detail     TEXT,
    card_info       TEXT,                   -- 列表卡片原始信息 JSON
    score_details   TEXT,                   -- 评分明细 JSON
    scraped_at      TEXT,
    updated_at      TEXT,
    notes           TEXT DEFAULT ''         -- 人工备注
);

greet_log 表

CREATE TABLE greet_log (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    candidate_id TEXT NOT NULL REFERENCES candidates(id),
    account      TEXT NOT NULL DEFAULT 'hr1',  -- v0.2 多账号：hr1/hr2
    action       TEXT NOT NULL,             -- 'greet'
    result       TEXT,                      -- 'success' / 'button_not_found' / ...
    created_at   TEXT NOT NULL DEFAULT (datetime('now','localtime'))
);

scrape_log 表

CREATE TABLE scrape_log (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    mode         TEXT,                      -- 'full' / 'incremental'
    target_count INTEGER,
    new_count    INTEGER,
    skip_count   INTEGER,
    greet_count  INTEGER,
    error_count  INTEGER,
    created_at   TEXT NOT NULL DEFAULT (datetime('now','localtime'))
);

常用查询

-- 推荐人员
SELECT name, age, education, school, score, greeted
FROM candidates WHERE score_level = '推荐' ORDER BY score DESC;

-- 未打招呼的推荐/待定
SELECT name, score, score_level, school
FROM candidates WHERE score_level IN ('推荐','待定') AND greeted = 0;

-- 评分分布
SELECT score_level, COUNT(*) FROM candidates GROUP BY score_level;

-- 最近抓取的10人
SELECT name, score, score_level, scraped_at
FROM candidates ORDER BY scraped_at DESC LIMIT 10;

-- 打招呼成功率
SELECT action, result, COUNT(*) FROM greet_log GROUP BY action, result;