Recruiting / ATS data model
Normalized (3NF) recruiting/ATS model. Requisition and job backbone, candidate sourcing, application pipeline with explicit stage history, panel interviews with per-interviewer feedback, assessments, and offers. Candidate PII carries GDPR classification, consent, and retention-expiry handling.
Applicant-tracking operations: requisitions against jobs, candidates and their sourcing channels, applications moving through pipeline stages, interviews with panelists, assessments, and offers. Candidate PII is classified for GDPR with consent and retention controls. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 13
- tables
- 107
- columns
- 14
- relationships
- 9
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Application as the join of candidate and requisition
A candidate may apply to many requisitions; modeling the application as its own entity (rather than a column on candidate) lets pipeline state, interviews, assessments, and offers hang off the right grain.
Explicit stage-history table
Pipeline conversion analytics (time-in-stage, drop-off) require the full transition log, not just the current stage; a history table keeps the application row 3NF while preserving the audit trail.
Panel interviews via interview/interviewer junction
Multiple interviewers assess one interview and one interviewer works many interviews; the panelist junction carries per-reviewer scores and feedback without M:N duplication.
Candidate PII with consent and retention
Resume is restricted+full-masked, contact fields partially masked; consent and retention-expiry fields support GDPR right-to-erasure and storage-limitation obligations.
Tables in the Recruiting / ATS model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Tables (11)
department
Grain: one row per hiring department
| Column | Type | Keys |
|---|---|---|
| department_key | bigint | PK |
| department_code | varchar | BK |
| department_name | varchar | |
| cost_center | varchar |
job
Grain: one row per job profile being recruited for
| Column | Type | Keys |
|---|---|---|
| job_key | bigint | PK |
| department_key | bigint | |
| job_code | varchar | BK |
| job_title | varchar | |
| job_family | varchar | |
| seniority_level | varchar | |
| employment_type | varchar | |
| location | varchar | |
| is_remote | boolean |
requisition
Grain: one row per open requisition (a hiring need)
| Column | Type | Keys |
|---|---|---|
| requisition_key | bigint | PK |
| job_key | bigint | |
| requisition_number | varchar | BK |
| openings | smallint | |
| status | varchar | |
| hiring_manager_id | varchar | |
| recruiter_id | varchar | |
| priority | varchar | |
| opened_date | date | |
| target_fill_date | date | |
| filled_date | date |
candidate
Grain: one row per candidate (person)
Candidate PII classified for GDPR with consent + retention.
| Column | Type | Keys |
|---|---|---|
| candidate_key | bigint | PK |
| source_key | bigint | |
| candidate_external_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| location | varchar | PII |
| linkedin_url | varchar | PII |
| current_employer | varchar | |
| current_title | varchar | |
| resume_url | varchar | PII |
| consent_to_store | boolean | |
| retention_expiry_date | date | |
| created_at | timestamp |
application
Grain: one row per candidate application to a requisition
| Column | Type | Keys |
|---|---|---|
| application_key | bigint | PK |
| candidate_key | bigint | |
| requisition_key | bigint | |
| current_stage_key | bigint | |
| application_number | varchar | BK |
| status | varchar | |
| rejection_reason | varchar | |
| applied_at | timestamp | |
| decided_at | timestamp |
application_stage_history
Grain: one row per stage transition for an application
| Column | Type | Keys |
|---|---|---|
| application_stage_history_key | bigint | PK |
| application_key | bigint | |
| stage_key | bigint | |
| entered_at | timestamp | |
| exited_at | timestamp | |
| moved_by_user_id | varchar |
interviewer
Grain: one row per employee who conducts interviews
Interviewer PII classified for GDPR.
| Column | Type | Keys |
|---|---|---|
| interviewer_key | bigint | PK |
| interviewer_external_id | varchar | BK |
| full_name | varchar | PII |
| varchar | PII | |
| department | varchar | |
| is_trained | boolean |
interview
Grain: one row per scheduled interview for an application
| Column | Type | Keys |
|---|---|---|
| interview_key | bigint | PK |
| application_key | bigint | |
| stage_key | bigint | |
| interview_round | smallint | |
| interview_type | varchar | |
| scheduled_at | timestamp | |
| duration_minutes | smallint | |
| status | varchar | |
| overall_recommendation | varchar |
interview_panelist
Grain: one row per interviewer on an interview (junction) with feedback
| Column | Type | Keys |
|---|---|---|
| interview_panelist_key | bigint | PK |
| interview_key | bigint | |
| interviewer_key | bigint | |
| role_in_panel | varchar | |
| score | decimal | |
| recommendation | varchar | |
| feedback | varchar | |
| submitted_at | timestamp |
assessment
Grain: one row per assessment/test taken by an application
| Column | Type | Keys |
|---|---|---|
| assessment_key | bigint | PK |
| application_key | bigint | |
| assessment_name | varchar | |
| assessment_type | varchar | |
| provider | varchar | |
| score | decimal | |
| max_score | decimal | |
| passed | boolean | |
| completed_at | timestamp |
offer
Grain: one row per offer extended for an application
| Column | Type | Keys |
|---|---|---|
| offer_key | bigint | PK |
| application_key | bigint | |
| offer_number | varchar | BK |
| status | varchar | |
| base_salary | decimal | |
| currency_code | varchar | |
| bonus_amount | decimal | |
| equity_units | decimal | |
| start_date | date | |
| extended_at | timestamp | |
| responded_at | timestamp |
Lookup tables (2)
source
Grain: reference list of candidate sourcing channels
| Column | Type | Keys |
|---|---|---|
| source_key | bigint | PK |
| source_code | varchar | BK |
| source_name | varchar | |
| source_type | varchar | |
| cost_per_hire | decimal |
stage
Grain: reference list of pipeline stages
| Column | Type | Keys |
|---|---|---|
| stage_key | bigint | PK |
| stage_code | varchar | BK |
| stage_name | varchar | |
| stage_order | smallint | |
| is_terminal | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- job.department_key → department (many-to-one)
- requisition.job_key → job (many-to-one)
- candidate.source_key → source (many-to-one)
- application.candidate_key → candidate (many-to-one)
- application.requisition_key → requisition (many-to-one)
- application.current_stage_key → stage (many-to-one)
- application_stage_history.application_key → application (many-to-one)
- application_stage_history.stage_key → stage (many-to-one)
- interview.application_key → application (many-to-one)
- interview.stage_key → stage (many-to-one)
- interview_panelist.interview_key → interview (many-to-one)
- interview_panelist.interviewer_key → interviewer (many-to-one)
- assessment.application_key → application (many-to-one)
- offer.application_key → application (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.