File size: 5,073 Bytes
c6034f1 a23d00a 8ebda38 a23d00a 8abe3ca a23d00a 7c95021 | 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 | PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
name TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS user_profiles (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER UNIQUE NOT NULL,
resume_path TEXT,
linkedin_url TEXT,
profile_summary TEXT,
skills TEXT, -- JSON array of strings
experience TEXT, -- JSON array of objects
education TEXT, -- JSON array of objects
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS jobs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
title TEXT,
company TEXT,
location TEXT,
description TEXT,
url TEXT,
source TEXT, -- 'manual_url' | 'manual_text' | 'search_agent'
raw_content TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS pipeline_cards (
id INTEGER PRIMARY KEY AUTOINCREMENT,
job_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
stage TEXT NOT NULL DEFAULT 'wishlist', -- wishlist|applied|interviewing|offer|rejected
position INTEGER NOT NULL DEFAULT 0,
notes TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS generated_kits (
id INTEGER PRIMARY KEY AUTOINCREMENT,
pipeline_card_id INTEGER NOT NULL,
type TEXT NOT NULL, -- cover_letter|resume|interview_questions|company_brief
content TEXT, -- editable markdown
file_path TEXT, -- saved PDF path
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (pipeline_card_id) REFERENCES pipeline_cards(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS job_suggestions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
title TEXT,
company TEXT,
location TEXT,
description TEXT,
url TEXT,
match_reason TEXT,
source TEXT, -- remotive|arbeitnow|ddg|indeed|google
status TEXT NOT NULL DEFAULT 'pending', -- pending|added|dismissed
search_date DATE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- Interviewer mode tables
CREATE TABLE IF NOT EXISTS interviewer_projects (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
title TEXT NOT NULL,
description TEXT,
location TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS interviewer_candidates (
id INTEGER PRIMARY KEY AUTOINCREMENT,
project_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
name TEXT NOT NULL,
email TEXT,
phone TEXT,
location TEXT,
resume_path TEXT,
resume_text TEXT,
ai_summary TEXT,
ai_score INTEGER DEFAULT 0,
ai_matching TEXT DEFAULT '[]',
ai_gaps TEXT DEFAULT '[]',
ai_recommendation TEXT DEFAULT 'consider',
notes TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (project_id) REFERENCES interviewer_projects(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS interviewer_pipeline_cards (
id INTEGER PRIMARY KEY AUTOINCREMENT,
candidate_id INTEGER NOT NULL,
project_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
stage TEXT NOT NULL DEFAULT 'applied',
position INTEGER NOT NULL DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (candidate_id) REFERENCES interviewer_candidates(id) ON DELETE CASCADE,
FOREIGN KEY (project_id) REFERENCES interviewer_projects(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS interviewer_project_members (
project_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
role TEXT NOT NULL DEFAULT 'editor',
invited_at DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (project_id, user_id),
FOREIGN KEY (project_id) REFERENCES interviewer_projects(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
|