schema.sql

-- Setup guide: https://freebase.cloud/how-to-connect-claude-to-postgresql  |  Free instance: https://freebase.cloud/free-postgresql-cloud-instance
-- schema.sql — job-application tracker for PostgreSQL 16
--
-- Two ways to apply this:
--   psql "postgresql://freebase@HOST:5432/mydb" -f schema.sql
-- or paste the DDL into ChatGPT and ask it to run it through pg_store.
--
-- The constraints are deliberate. A model writing SQL on your behalf will
-- occasionally invent a status value; a CHECK turns that into an error you see
-- instead of a row you discover months later.

BEGIN;

DROP TABLE IF EXISTS interviews;
DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS applications;

CREATE TABLE applications (
  id           SERIAL PRIMARY KEY,
  company      TEXT    NOT NULL,
  role         TEXT    NOT NULL,
  source       TEXT    CHECK (source IN ('referral', 'board', 'recruiter', 'direct')),
  applied_on   DATE    NOT NULL DEFAULT CURRENT_DATE,
  status       TEXT    NOT NULL DEFAULT 'applied'
                       CHECK (status IN ('applied', 'screening', 'onsite',
                                         'offer', 'rejected', 'withdrawn')),
  salary_min   INTEGER CHECK (salary_min IS NULL OR salary_min > 0),
  salary_max   INTEGER CHECK (salary_max IS NULL OR salary_max >= salary_min),
  location     TEXT,
  remote       BOOLEAN NOT NULL DEFAULT false,
  notes        TEXT,
  UNIQUE (company, role)
);

CREATE TABLE interviews (
  id             SERIAL PRIMARY KEY,
  application_id INTEGER NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
  stage          TEXT    NOT NULL,           -- 'phone screen', 'system design', ...
  scheduled_for  TIMESTAMPTZ NOT NULL,
  interviewer    TEXT,
  outcome        TEXT    CHECK (outcome IN ('passed', 'failed', 'pending', 'cancelled')),
  debrief        TEXT
);

CREATE TABLE contacts (
  id             SERIAL PRIMARY KEY,
  company        TEXT NOT NULL,
  full_name      TEXT NOT NULL,
  title          TEXT,
  email          TEXT,
  relationship   TEXT,                       -- 'former colleague', 'recruiter', ...
  last_contacted DATE,
  UNIQUE (company, full_name)
);

CREATE INDEX applications_status_idx  ON applications (status, applied_on DESC);
CREATE INDEX interviews_app_idx       ON interviews (application_id, scheduled_for);

-- ---------------------------------------------------------------------------
-- A handful of rows so the first questions have something to chew on.
-- ---------------------------------------------------------------------------

INSERT INTO applications (company, role, source, applied_on, status, salary_min, salary_max, location, remote, notes) VALUES
  ('Northwind Labs', 'Backend Engineer',   'board',     '2026-07-14', 'applied',   NULL,   NULL,   'Chicago, IL',  false, 'No band posted. Ghosted so far.'),
  ('Acme Data',      'Platform Engineer',  'board',     '2026-07-22', 'applied',   120000, 150000, 'Remote (US)',  true,  NULL),
  ('Harbor Systems', 'Senior SRE',         'referral',  '2026-07-28', 'onsite',    145000, 175000, 'Boston, MA',   false, 'Intro from Priya.'),
  ('Bluecap',        'Data Engineer',      'recruiter', '2026-08-01', 'screening', 110000, 135000, 'Remote (US)',  true,  'Recruiter reached out cold.'),
  ('Meridian Co',    'Backend Engineer',   'direct',    '2026-08-03', 'rejected',  NULL,   NULL,   'Austin, TX',   false, 'Rejected at resume stage.'),
  ('Ferrous Works',  'Infrastructure Eng', 'referral',  '2026-08-06', 'offer',     150000, 180000, 'Remote (US)',  true,  'Verbal offer, awaiting written.'),
  ('Cobalt Retail',  'Analytics Engineer', 'board',     '2026-08-09', 'screening', 95000,  115000, 'Denver, CO',   false, NULL);

INSERT INTO interviews (application_id, stage, scheduled_for, interviewer, outcome, debrief) VALUES
  (3, 'phone screen',   '2026-08-04 15:00+00', 'Dana R.',  'passed',  'Went long on incident response.'),
  (3, 'system design',  '2026-08-11 17:00+00', 'Marcus T.','pending', NULL),
  (4, 'phone screen',   '2026-08-07 14:30+00', 'Recruiter','passed',  NULL),
  (6, 'phone screen',   '2026-08-07 16:00+00', 'Lena K.',  'passed',  NULL),
  (6, 'system design',  '2026-08-12 18:00+00', 'Lena K.',  'passed',  'Asked about queue backpressure.'),
  (7, 'phone screen',   '2026-08-14 13:00+00', 'Sam O.',   'pending', NULL);

INSERT INTO contacts (company, full_name, title, email, relationship, last_contacted) VALUES
  ('Harbor Systems', 'Priya Raman', 'Staff Engineer',      'priya@example.com', 'former colleague', '2026-07-26'),
  ('Ferrous Works',  'Lena Kovac',  'Engineering Manager', 'lena@example.com',  'hiring manager',   '2026-08-12'),
  ('Bluecap',        'Tom Alvarez', 'Technical Recruiter', 'tom@example.com',   'recruiter',        '2026-08-07');

COMMIT;

-- ---------------------------------------------------------------------------
-- Annotation prompts. Paste each into ChatGPT once, with the app enabled.
-- These are not SQL; they are what makes the model's SQL correct.
-- ---------------------------------------------------------------------------
--
--   Annotate applications: one row per posting I applied to. status moves
--   applied -> screening -> onsite -> offer, or to rejected/withdrawn, and
--   never takes any other value. salary_min/salary_max are the EMPLOYER'S
--   posted band in USD and are NULL when the posting published none — never
--   treat NULL as zero and always report how many rows were excluded.
--   applied_on is the date I submitted, not the date the job was posted.
--
--   Annotate interviews: one row per scheduled conversation, joined to
--   applications by application_id. outcome 'pending' means it has not
--   happened yet or has no verdict, so exclude pending rows from pass rates.
--
--   Annotate contacts: humans, one row per person per company.
--   last_contacted is the most recent outbound message from me.
--
-- ---------------------------------------------------------------------------
-- Questions worth asking once the annotations are in place:
-- ---------------------------------------------------------------------------
--
--   Which applications have been in 'applied' for more than 21 days?
--   Does source correlate with reaching onsite, for sources with 3+ rows?
--   Which companies interviewed me twice or more without an offer?
--   Who have I not contacted in three weeks at a company still in play?