Skip to content

Data Model

Conceptual Model

erDiagram
  users ||--o{ projects : "creates"
  users ||--o{ project_interviewers : "participates as interviewer"
  users ||--o{ reservations : "conducts interview"
  projects ||--o{ project_candidates : "has candidates"
  projects ||--o{ project_interviewers : "has interviewers"
  projects ||--o{ email_templates : "has templates"
  projects ||--o{ import_logs : "has import history"
  candidates ||--o{ project_candidates : "participates in"
  project_candidates ||--o| reservations : "has confirmed reservation"
  project_candidates ||--o{ scheduling_tokens : "has scheduling tokens"
  project_candidates ||--o{ candidate_available_dates : "has preferred slots"
  project_candidates ||--o{ email_logs : "has email history"
  project_candidates ||--o{ status_histories : "has status history"
  email_templates ||--o{ email_logs : "is used by"

Entity Overview

Core

Entity Physical name Description
User users A console user, one-to-one with a Google account. Interviewers live here too
Project projects One interview initiative. Holds interview conditions, scoring rules, survey import settings and arrange settings
Candidate candidates The person themselves. Unique across projects via external_user_id
Project candidate project_candidates A candidate's participation in a project. Holds status, score, selection type and answers
Project interviewer project_interviewers Link between a project and an interviewer (user)

Scheduling

Entity Physical name Description
Scheduling token scheduling_tokens Access token for the public candidate page, with an expiry
Candidate available date candidate_available_dates Slots submitted by the candidate. Fully replaced on each submission
Reservation reservations A confirmed interview. At most one per project candidate (UNIQUE)
Status history status_histories Record of candidate status transitions

Email

Entity Physical name Description
Email template email_templates Typed template. System-wide when project_id is NULL
Email log email_logs Subject, body and result of a sent email

Import & Survey Metadata Cache

Entity Physical name Description
Import log import_logs Execution history of TSV / survey imports with counts and error breakdown
Survey metadata databricks_surveys Cached survey metadata, refreshed daily from Databricks

Key Business Rules

Separating candidates from project candidates

  • The person (candidates) is separated from their participation in a project (project_candidates)
  • candidates.external_user_id is globally UNIQUE โ€” one person joining several projects is still a single candidates row
  • project_candidates is UNIQUE on (project_id, candidate_id) (uk_project_candidate); duplicate enrolment in one project is impossible
  • Answers (survey_responses), attributes (attributes), score and selection type are held per project

Reservations

  • reservations.project_candidate_id is UNIQUE โ€” at most one confirmed reservation per candidate per project
  • Cancellation updates status = 'cancelled' rather than deleting the row
  • interviewer_id references users with onDelete: restrict, so a user with outstanding reservations cannot be deleted

Status transitions

  • project_candidates.status only moves along allowed transitions (see the state diagram in Features)
  • Every transition appends one row to status_histories
  • completed and declined are terminal. bounced can return to contacted, and cancelled back to not_contacted

Cascading deletes

Deleting a projects row cascades to project_candidates / project_interviewers / email_templates / import_logs. Deleting a project_candidates row further cascades to email_logs / scheduling_tokens / candidate_available_dates / reservations / status_histories.

Survey metadata cache

  • databricks_surveys caches external data and is not linked to application tables by foreign keys
  • Answers are not stored in Postgres. They are read straight from cs.cs_dm.dm_answers_<survey_id> in Databricks (see Databricks integration)
  • Answers are vertical: one row per panel_id + question sentence. There is no question ID, so the question text itself is the identifier
  • Importing into candidates pivots this vertical data into project_candidates.survey_responses

Personal data retention

  • Personal data (name, email, phone on candidates) is anonymized by the pii-cleanup batch once past its retention period (3 months by default)
  • Related records such as project_candidates are retained for aggregation