File size: 4,849 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 | -- PostgreSQL schema for Linear Lantern (Neon / any PostgreSQL)
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
name TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS user_profiles (
id SERIAL PRIMARY KEY,
user_id INTEGER UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
resume_path TEXT, -- GitHub raw URL after upload
resume_text TEXT, -- raw extracted text (cached for AI use)
linkedin_url TEXT,
location TEXT,
profile_summary TEXT,
skills TEXT, -- JSON array of strings
experience TEXT, -- JSON array of objects
education TEXT, -- JSON array of objects
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS jobs (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title TEXT,
company TEXT,
location TEXT,
description TEXT,
url TEXT,
source TEXT, -- manual_url | manual_text | search_agent
raw_content TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS pipeline_cards (
id SERIAL PRIMARY KEY,
job_id INTEGER NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
stage TEXT NOT NULL DEFAULT 'wishlist', -- wishlist|applied|interviewing|offer|rejected
position INTEGER NOT NULL DEFAULT 0,
notes TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS generated_kits (
id SERIAL PRIMARY KEY,
pipeline_card_id INTEGER NOT NULL REFERENCES pipeline_cards(id) ON DELETE CASCADE,
type TEXT NOT NULL, -- cover_letter|resume|interview_questions|company_brief
content TEXT, -- editable markdown
file_path TEXT, -- GitHub raw URL after PDF generation
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS job_suggestions (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title TEXT,
company TEXT,
location TEXT,
description TEXT,
url TEXT,
match_reason TEXT,
match_score INTEGER DEFAULT 0,
matching TEXT DEFAULT '[]',
gaps TEXT DEFAULT '[]',
recommendation TEXT DEFAULT 'consider',
source TEXT,
status TEXT NOT NULL DEFAULT 'pending', -- pending|added|dismissed
search_date DATE,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Interviewer mode tables
ALTER TABLE users ADD COLUMN IF NOT EXISTS mode TEXT NOT NULL DEFAULT 'candidate';
CREATE TABLE IF NOT EXISTS interviewer_projects (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title TEXT NOT NULL,
description TEXT,
location TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS interviewer_candidates (
id SERIAL PRIMARY KEY,
project_id INTEGER NOT NULL REFERENCES interviewer_projects(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
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 TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS interviewer_pipeline_cards (
id SERIAL PRIMARY KEY,
candidate_id INTEGER NOT NULL REFERENCES interviewer_candidates(id) ON DELETE CASCADE,
project_id INTEGER NOT NULL REFERENCES interviewer_projects(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
stage TEXT NOT NULL DEFAULT 'applied', -- applied|screening|interview|offer|rejected
position INTEGER NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS interviewer_project_members (
project_id INTEGER NOT NULL REFERENCES interviewer_projects(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role TEXT NOT NULL DEFAULT 'editor',
invited_at TIMESTAMPTZ DEFAULT NOW(),
PRIMARY KEY (project_id, user_id)
);
|