How to build a job application tracker with ChatGPT

Set up a job application tracker for me in databasedmcp, in one go.

1. Create a database called job-search, described as "Job search: roles I've saved and applied to, the people involved, and every interview, filed by ChatGPT as things happen".
2. Create three tables:
   - applications: id, company (required), role (required), url, source (where I found it), status (saved, applied, interviewing, offer, rejected or withdrawn, defaults to saved), applied_on (date, set when I apply), notes, created_at (defaults to now).
   - contacts: id, application_id (references applications), name (required), role, email.
   - events: id, application_id (references applications), kind (screen, interview, takehome, offer, rejection or note), happened_on (date, defaults to today, and can be in the future for something scheduled), notes.
3. Write the database a guide that says what each table is for, to find an application by company before adding one so there are no duplicates, and the queries for "what's coming up" (events from today on) and "who should I follow up with" (applied over 14 days ago with nothing since).

From then on: when I paste or describe a posting, save the role. When I apply, set the status to applied and applied_on to today. When a recruiter or interviewer gets in touch, add them as a contact. When something is scheduled or happens, add an event, and move the status along when it changes. Show me the tables when you're finished.

CREATE TABLE applications (
  id INTEGER PRIMARY KEY,
  company TEXT NOT NULL,
  role TEXT NOT NULL,
  url TEXT,
  source TEXT,
  status TEXT NOT NULL DEFAULT 'saved' CHECK (status IN ('saved', 'applied', 'interviewing', 'offer', 'rejected', 'withdrawn')),
  applied_on TEXT,
  notes TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE contacts (
  id INTEGER PRIMARY KEY,
  application_id INTEGER NOT NULL REFERENCES applications(id),
  name TEXT NOT NULL,
  role TEXT,
  email TEXT
);

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  application_id INTEGER NOT NULL REFERENCES applications(id),
  kind TEXT NOT NULL CHECK (kind IN ('screen', 'interview', 'takehome', 'offer', 'rejection', 'note')),
  happened_on TEXT NOT NULL DEFAULT (date('now')),
  notes TEXT
);

# job-search: applications, contacts, events

- **applications**: one row per role. status: saved, applied, interviewing, offer, rejected, withdrawn. `applied_on` is set when I apply.
- **contacts**: recruiters and interviewers, linked to the application they came from.
- **events**: screens, interviews, take-homes, offers, rejections. `happened_on` can be in the future for something scheduled.

Find an application by company (LIKE) before adding one.

Coming up: `SELECT e.happened_on, a.company, a.role, e.kind, e.notes FROM events e JOIN applications a ON a.id = e.application_id WHERE e.happened_on >= date('now') ORDER BY e.happened_on`

Gone quiet: applied over 14 days ago with nothing since.

INSERT INTO applications (company, role, url, source) VALUES (?, ?, ?, ?) RETURNING id
-- params: ["Fernhill Labs","Senior product designer","https://fernhill-labs.example/jobs/senior-product-designer","Careers page"]

UPDATE applications SET status = 'applied', applied_on = date('now') WHERE id = ? AND status = 'saved'
-- params: [1]

INSERT INTO contacts (application_id, name, role, email) VALUES (?, ?, ?, ?)
-- params: [1,"Priya Raman","Recruiter","priya@fernhill-labs.example"]

INSERT INTO events (application_id, kind, happened_on, notes) VALUES (?, 'screen', date('now', 'weekday 4'), ?)
-- params: [1,"30 minutes with Priya. Portfolio review comes after."]

UPDATE applications SET status = 'interviewing' WHERE id = ?
-- params: [1]

SELECT status, count(*) AS roles FROM applications GROUP BY status ORDER BY CASE status WHEN 'offer' THEN 1 WHEN 'interviewing' THEN 2 WHEN 'applied' THEN 3 WHEN 'saved' THEN 4 ELSE 5 END

SELECT e.happened_on, a.company, a.role, e.kind, e.notes FROM events e JOIN applications a ON a.id = e.application_id WHERE e.happened_on >= date('now') ORDER BY e.happened_on

SELECT a.id, a.company, a.role, a.applied_on FROM applications a WHERE a.status = 'applied' AND a.applied_on < date('now', '-14 days') AND NOT EXISTS (SELECT 1 FROM events e WHERE e.application_id = a.id AND e.happened_on > a.applied_on) ORDER BY a.applied_on