How to Build a Recruitment Platform with Replit

1// Build a recruitment platform (ATS) with Express and PostgreSQL using Drizzle ORM.
2// Use Replit Auth for authentication.
3//
4// Tables:
5// 1. job_postings: id serial primary key, title text not null, department text,
6//    description text not null, requirements text, location text,
7//    type text default 'full_time' (enum: full_time/part_time/contract/remote),
8//    salary_min integer, salary_max integer,
9//    status text default 'draft' (enum: draft/open/closed), posted_by text not null,
10//    created_at timestamp default now()
11// 2. candidates: id serial primary key, name text not null, email text unique not null,
12//    phone text, resume_url text, linkedin_url text,
13//    source text (enum: direct/referral/linkedin/indeed/other),
14//    notes text, created_at timestamp default now()
15// 3. applications: id serial primary key,
16//    job_posting_id integer references job_postings not null,
17//    candidate_id integer references candidates not null,
18//    stage text default 'applied' (enum: applied/screening/interview/offer/hired/rejected),
19//    cover_letter text, applied_at timestamp default now(),
20//    updated_at timestamp default now(), unique(job_posting_id, candidate_id)
21// 4. interviews: id serial primary key,
22//    application_id integer references applications not null,
23//    interviewer text not null, scheduled_at timestamp not null,
24//    duration_minutes integer default 60,
25//    type text default 'video' (enum: phone/video/onsite),
26//    location text, feedback text, score integer (1-5),
27//    status text default 'scheduled' (enum: scheduled/completed/cancelled),
28//    created_at timestamp default now()
29// 5. pipeline_history: id serial primary key,
30//    application_id integer references applications not null,
31//    from_stage text, to_stage text not null,
32//    changed_by text not null, notes text, created_at timestamp default now()
33//
34// Routes:
35// GET/POST /api/jobs, PATCH /api/jobs/:id/status
36// GET /api/jobs/:id/applications, POST /api/candidates
37// POST /api/applications, PATCH /api/applications/:id/stage
38// POST/GET /api/interviews, PATCH /api/interviews/:id/feedback
39// GET /api/pipeline/:jobId (Kanban — applications grouped by stage)
40// GET /api/reports/time-to-hire, GET /api/reports/source-effectiveness
41//
42// Bind to 0.0.0.0:3000.

1const express = require('express');
2const { db } = require('../db');
3const { applications, pipelineHistory, interviews } = require('../schema');
4const { eq, and } = require('drizzle-orm');
5const { withDbRetry } = require('../lib/retryDb');
6
7const router = express.Router();
8
9const VALID_STAGES = ['applied', 'screening', 'interview', 'offer', 'hired', 'rejected'];
10
11router.patch('/api/applications/:id/stage', express.json(), async (req, res) => {
12  if (!req.user) return res.status(401).json({ error: 'Login required' });
13  const { stage, notes } = req.body;
14
15  if (!VALID_STAGES.includes(stage)) {
16    return res.status(400).json({ error: `Invalid stage. Must be: ${VALID_STAGES.join(', ')}` });
17  }
18
19  const appId = parseInt(req.params.id);
20
21  // Get current application
22  const [app] = await db.select().from(applications)
23    .where(eq(applications.id, appId)).limit(1);
24  if (!app) return res.status(404).json({ error: 'Application not found' });
25
26  if (app.stage === stage) {
27    return res.status(400).json({ error: 'Application is already in this stage' });
28  }
29
30  // Update stage and log to pipeline_history atomically
31  await withDbRetry(async () => {
32    await db.update(applications)
33      .set({ stage, updatedAt: new Date() })
34      .where(eq(applications.id, appId));
35
36    await db.insert(pipelineHistory).values({
37      applicationId: appId,
38      fromStage: app.stage,
39      toStage: stage,
40      changedBy: req.user.id,
41      notes: notes || null,
42    });
43  });
44
45  return res.json({ id: appId, stage, movedFrom: app.stage });
46});
47
48// GET Kanban view — applications grouped by stage for a job
49router.get('/api/pipeline/:jobId', async (req, res) => {
50  if (!req.user) return res.status(401).json({ error: 'Login required' });
51  const jobId = parseInt(req.params.jobId);
52
53  const rows = await db.execute({
54    sql: `
55      SELECT a.id, a.stage, a.applied_at, a.updated_at,
56        c.id AS candidate_id, c.name, c.email, c.source,
57        c.linkedin_url, c.resume_url,
58        EXTRACT(DAY FROM NOW() - a.updated_at) AS days_in_stage,
59        COUNT(i.id) AS interview_count
60      FROM applications a
61      JOIN candidates c ON a.candidate_id = c.id
62      LEFT JOIN interviews i ON i.application_id = a.id
63      WHERE a.job_posting_id = $1
64      GROUP BY a.id, c.id
65      ORDER BY a.updated_at DESC
66    `,
67    params: [jobId],
68  });
69
70  // Group by stage for Kanban
71  const pipeline = {};
72  for (const stage of VALID_STAGES) pipeline[stage] = [];
73  for (const row of rows.rows) {
74    pipeline[row.stage]?.push(row);
75  }
76
77  return res.json({ jobId, pipeline });
78});
79
80module.exports = router;

1const express = require('express');
2const { db } = require('../db');
3const { interviews } = require('../schema');
4const { eq, and } = require('drizzle-orm');
5
6const router = express.Router();
7
8router.post('/api/interviews', express.json(), async (req, res) => {
9  if (!req.user) return res.status(401).json({ error: 'Login required' });
10  const { applicationId, interviewer, scheduledAt, durationMinutes, type, location } = req.body;
11
12  if (!applicationId || !interviewer || !scheduledAt) {
13    return res.status(400).json({ error: 'applicationId, interviewer, and scheduledAt are required' });
14  }
15
16  const row = await db.insert(interviews).values({
17    applicationId: parseInt(applicationId),
18    interviewer,
19    scheduledAt: new Date(scheduledAt),
20    durationMinutes: durationMinutes || 60,
21    type: type || 'video',
22    location: location || null,
23  }).returning();
24
25  return res.status(201).json(row[0]);
26});
27
28router.get('/api/interviews', async (req, res) => {
29  if (!req.user) return res.status(401).json({ error: 'Login required' });
30  const { applicationId } = req.query;
31
32  const rows = await db.select().from(interviews)
33    .where(applicationId ? eq(interviews.applicationId, parseInt(applicationId)) : undefined)
34    .orderBy(interviews.scheduledAt);
35
36  return res.json({ interviews: rows });
37});
38
39router.patch('/api/interviews/:id/feedback', express.json(), async (req, res) => {
40  if (!req.user) return res.status(401).json({ error: 'Login required' });
41  const { feedback, score, status } = req.body;
42
43  if (score && (score < 1 || score > 5)) {
44    return res.status(400).json({ error: 'Score must be 1-5' });
45  }
46
47  const updated = await db.update(interviews)
48    .set({
49      feedback: feedback || null,
50      score: score ? parseInt(score) : null,
51      status: status || 'completed',
52    })
53    .where(eq(interviews.id, parseInt(req.params.id)))
54    .returning();
55
56  if (!updated[0]) return res.status(404).json({ error: 'Interview not found' });
57  return res.json(updated[0]);
58});
59
60module.exports = router;

1const express = require('express');
2const { db } = require('../db');
3
4const router = express.Router();
5
6// Average days from application to hired, by job
7router.get('/api/reports/time-to-hire', async (req, res) => {
8  if (!req.user) return res.status(401).json({ error: 'Login required' });
9
10  const result = await db.execute({
11    sql: `
12      SELECT
13        jp.id AS job_id,
14        jp.title AS job_title,
15        COUNT(a.id) AS hired_count,
16        ROUND(AVG(
17          EXTRACT(DAY FROM ph_hired.created_at - a.applied_at)
18        )) AS avg_days_to_hire
19      FROM applications a
20      JOIN job_postings jp ON a.job_posting_id = jp.id
21      JOIN pipeline_history ph_hired
22        ON ph_hired.application_id = a.id
23        AND ph_hired.to_stage = 'hired'
24      WHERE a.stage = 'hired'
25      GROUP BY jp.id, jp.title
26      ORDER BY avg_days_to_hire ASC
27    `,
28    params: [],
29  });
30
31  return res.json({ report: result.rows });
32});
33
34// Candidates by source with hire rates
35router.get('/api/reports/source-effectiveness', async (req, res) => {
36  if (!req.user) return res.status(401).json({ error: 'Login required' });
37
38  const result = await db.execute({
39    sql: `
40      SELECT
41        c.source,
42        COUNT(DISTINCT c.id) AS total_candidates,
43        COUNT(DISTINCT CASE WHEN a.stage = 'hired' THEN c.id END) AS hired,
44        COUNT(DISTINCT CASE WHEN a.stage = 'rejected' THEN c.id END) AS rejected,
45        ROUND(
46          100.0 * COUNT(DISTINCT CASE WHEN a.stage = 'hired' THEN c.id END)
47          / NULLIF(COUNT(DISTINCT c.id), 0)
48        ) AS hire_rate_pct
49      FROM candidates c
50      LEFT JOIN applications a ON a.candidate_id = c.id
51      GROUP BY c.source
52      ORDER BY hire_rate_pct DESC NULLS LAST
53    `,
54    params: [],
55  });
56
57  return res.json({ report: result.rows });
58});
59
60module.exports = router;

1// Ask Agent to build the React frontend with this prompt:
2// Build a React ATS frontend with these pages:
3//
4// 1. Jobs List page (/):
5//    - Table of job_postings with columns: Title, Department, Type, Salary Range,
6//      Status badge (draft=gray, open=green, closed=red), Application Count
7//    - 'Create Job' button opens a modal form
8//    - Click job row navigates to the Pipeline page for that job
9//    - Status toggle button: open <-> closed
10//
11// 2. Pipeline Kanban page (/jobs/:id/pipeline):
12//    - Fetch GET /api/pipeline/:jobId
13//    - Six columns: Applied, Screening, Interview, Offer, Hired, Rejected
14//    - Each column has a count badge and a scrollable list of candidate cards
15//    - Candidate card shows: name, source badge, days-in-stage indicator
16//      (orange if >7 days, red if >14 days), interview count badge
17//    - Drag card between columns calls PATCH /api/applications/:id/stage
18//    - Click card opens the Candidate Detail panel
19//
20// 3. Candidate Detail panel (right-side sheet):
21//    - Shows: name, email, phone, source, resume link, LinkedIn link
22//    - Application history across all jobs as a timeline
23//    - Interview list with scheduled time, interviewer, score (star display)
24//    - 'Add Interview' button opens scheduling dialog
25//    - 'Add Note' textarea that updates candidate notes
26//    - Pipeline history timeline showing stage changes with timestamps
27//
28// 4. Reports page (/reports):
29//    - Two sections: Time to Hire (bar chart by job) and Source Effectiveness (table)
30//    - Bar chart uses recharts BarChart
31//
32// Use React Router and Replit Auth login gate.