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, grouped and counted:

• 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. This one needs a row per stage change, not one date on the application. The next section has it.
• 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. A formula can then show Days in current stage on every row.

Now you can build the view that actually improves hiring: applications in the same stage for more than seven days. It is one filter, Stage changed on is more than 7 days ago. The condition is counted from today, so the view is still right next Monday without anybody editing it. 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 stamp says how long an application has been where it is now. It cannot say how long it spent in the stages it has already left, because each move overwrites it. For average days per stage, add one small table, Stage moves: the application, the stage, the date it entered and the date it left. Have the same automation add a row for the stage being left before it writes the new stamp (keep the previous stage in a field of its own so the automation can read it). A report over that table, grouped by stage, is your bottleneck chart.

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 where Stage changed on is more than 7 days ago, oldest first. 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. Start it from the stage change: wait seven days, then continue only if Stage changed on is more than 6 days ago, which means nothing has moved since. To the person, not to a report, and once.

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. On the email step, tick Hold for a person to approve: the message is written, nothing is sent, and it waits in the approvals queue until somebody has read it and pressed send.

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. A submission is an application, and an application needs its candidate. Put the Candidate link on the form and choose Ask for their details: the form asks for a name and an email address, finds the candidate with that address or creates one, and links the new application to them. Somebody who applies for a second role is still one candidate. Put the Role link on it too and let them pick from your roles.
• 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.

The same detail matters on all three routes. 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 where Applied on is more than 365 days ago (or whatever your window is), 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.