Build an applicant tracking system in an afternoon

Hiring is the cleanest real-world example of a many-to-many relationship, which is why spreadsheet hiring trackers fall apart so predictably.

Get the shape right and everything else is straightforward.

The mistake to avoid

The instinct is a Candidates table with a "Role" field.

That breaks the first time someone applies to two roles. Do you duplicate the candidate? Now their phone number exists twice and can disagree. Do you put both roles in one cell? Then you cannot record that they reached final stage for one and were rejected for the other.

The stage does not belong to the candidate, and it does not belong to the role. It belongs to the pairing.

The model

Roles — title, department, hiring manager, salary band, status (Open/On hold/Filled), target start date.

Candidates — name, email, phone, location, CV attachment, LinkedIn, source-of-first-contact. One record per human, forever, no matter how many times they apply.

Applications — the junction table. Links one candidate to one role, and holds everything true of that combination:

Interviews — links to an Application (not to a candidate), plus date, format, interviewers, score, notes.

Linking interviews to applications rather than candidates is deliberate. It makes every interview unambiguously about one candidate for one role, so a candidate who applies twice has two clean histories rather than one confused one.

Interviewers — if you want load-balancing, panel history and per-interviewer scoring calibration, make this a table. If not, a person field is fine to start.

Stages

Six or seven, each defined by an observable event:

1. Applied — arrived, not yet reviewed.
2. Screening — CV under review.
3. Phone screen — first conversation booked or done.
4. Interview — in the main loop.
5. Final — last stage, decision pending.
6. Offer — extended.
7. Hired / Rejected / Withdrew.

Keep a rejection reason as a single select with a tight list: skills, experience level, salary mismatch, location, timing, better candidate, no response. Six months of these is genuinely valuable — it tells you whether your problem is sourcing, your salary band, or your process speed. It is also the data you need if anyone ever asks how you make decisions.

What the structure gives you

Because applications are records, these are rollups rather than projects:

On a role:

• Applications received, and count by stage.
• Time to fill — from role opened to offer accepted.
• Offer acceptance rate.
• Pipeline health — count at each stage against what you need.

On a candidate:

• Every application they have ever made, across all roles.
• Every interview they have ever had.
• First contacted date — a max/min rollup.

That candidate view is the quiet win. When someone strong is rejected for a senior role and a mid-level one opens six months later, they are already in your system with their full history. Most hiring teams lose that entirely.

Across applications (group and count):

• Which sources produce candidates who reach final stage — not just which produce volume. These are usually different answers, and the difference is where your recruiting budget should move.
• Average days per stage, which is where you find the bottleneck.
• Drop-off rate by stage.

Time-in-stage without typing

Automate a stamp: when Stage changes, write the date to a Stage changed on field. Then a formula gives Days in current stage.

Now you can build the view that actually improves hiring: applications in the same stage for more than seven days. Candidates go cold because nobody moved them, not because they lost interest. This one view fixes more hiring outcomes than any amount of sourcing.

The views

• Pipeline — board of applications grouped by stage, filtered to open roles. The daily working view.
• My interviews this week — calendar of interviews filtered to me.
• Stalled — applications with days-in-stage > 7, sorted descending. Reviewed every Monday.
• Role scorecard — one role, its applications, counts by stage and source.
• Talent pool — candidates rejected for reasons other than capability, for future roles. Filter on rejection reason.
• Offers out — small, urgent, checked daily.

Automations

1. Stage changes → stamp the date. Enables everything above.
2. Application created → acknowledge the candidate. A same-day acknowledgement is the cheapest reputation win in recruiting.
3. Stage = Offer → notify the hiring manager and start the paperwork record.
4. Stalled more than 7 days → nudge the owner. To the person, not to a report.

Be careful automating rejections. An automated rejection email at the wrong moment is a lasting brand cost. Automate the draft; let a human send it.

Getting candidates in

Three routes, in order of how much work they save:

• A public form for the "apply here" link on your careers page. Submissions become applications directly, so nobody retypes a CV.
• An email-in address — jobs@ — that turns inbound mail into records with the CV attached.
• CSV import from a job board's export, matched on email so repeat applicants attach to their existing candidate record rather than creating duplicates.

That last detail matters. Matching on email rather than name is what keeps one human as one record. See why every table needs a stable ID.

Privacy, briefly but seriously

Candidate data is personal data, and hiring records attract real legal obligations in most jurisdictions.

Three things to build in from the start, because retrofitting them is painful:

• Restrict access by field. Salary expectations and interview notes should not be visible to everyone with a login.
• Have a retention rule and actually apply it — a view of applications older than your retention window, reviewed quarterly.
• Keep an audit trail, so you can show how a decision was reached if you are ever asked.

Start here

Four tables — Roles, Candidates, Applications, Interviews — and one automation to stamp stage dates. An afternoon.

The structural decision that matters is Applications as its own table. Everything else can be added later; that one cannot be retrofitted without unpicking every duplicated candidate you created in the meantime.