Database (Core)

The five tables centred on projects and candidates.

---

users

Overview

A console user, one-to-one with a Google account. Interviewers are rows in this table too.

Table Definition

Logical name	Physical name	Column	Type	PK	Relation	Unique	Nullable	Default	Notes
User	users	id	integer	✓				auto-increment
		google_id	varchar(255)			✓			Unique ID of the Google account
		email	varchar(255)			✓
		name	varchar(255)						Display name
		avatar_url	varchar(500)				✓		Google profile image
		role	user_role					'member'	admin / member / viewer
		created_at	timestamp					now()
		updated_at	timestamp					now()

Indexes

• Primary key (id)
• UNIQUE: google_id
• UNIQUE: email
• INDEX: idx_users_email (email)

Notes

• Users are created automatically during the Google OAuth callback (registered on first login)
• role defaults to member; demotion to viewer or promotion to admin is currently done directly in the DB
• reservations.interviewer_id references this table with onDelete: restrict, so a user with outstanding reservations cannot be deleted

---

projects

Overview

One interview initiative. Holds the interview conditions, scoring rules, survey import settings, and the entire arrange flow state.

Table Definition

Logical name	Physical name	Column	Type	PK	Relation	Unique	Nullable	Default	Notes
Project	projects	id	integer	✓				auto-increment
		name	varchar(255)						Project name
		description	text				✓
		status	project_status					'draft'	draft / active / completed / archived
		interview_duration_minutes	integer					60	Duration of one interview in minutes
		scheduling_range_start_days	integer					1	Start of the scheduling window (N days from today)
		scheduling_range_end_days	integer					14	End of the scheduling window (N days from today)
		scoring_rules	json				✓		ScoringRules type
		interview_type	interview_type					'any'	online_meet / online_zoom / offline / any
		google_calendar_id	varchar(255)				✓		Project-specific calendar
		survey_auto_import_enabled	boolean					false	Enables automatic import
		survey_id	varchar(50)				✓		Linked survey ID (logical reference to databricks_surveys.survey_id)
		survey_import_mapping	json				✓		ImportMapping type
		survey_import_filter	json				✓		ImportFilter type
		survey_last_imported_at	timestamp				✓		Last import timestamp
		arrange_settings	json				✓		ArrangeSettings type (STEP1–3)
		created_by	integer		users:id (logical)				Creator
		created_at	timestamp					now()
		updated_at	timestamp					now()

Relations

• created_by → users.id: declared only in Drizzle's relations(); there is no database-level foreign key constraint
• Child tables (onDelete: cascade): project_candidates / project_interviewers / email_templates / import_logs

Indexes

• Primary key (id)
• INDEX: idx_projects_status (status)
• INDEX: idx_projects_created_by (created_by)

Notes

• arrange_settings stores the whole arrange flow state in a single JSON column by design; there is no per-step table
• When scoring_rules.max_score is set, it caps the computed score
• The scheduling window is stored as days relative to today, not as absolute dates

---

candidates

Overview

The candidate as a person. Unique across projects — one row even if the same person joins several projects.

Table Definition

Logical name	Physical name	Column	Type	PK	Relation	Unique	Nullable	Default	Notes
Candidate	candidates	id	integer	✓				auto-increment
		external_user_id	varchar(255)			✓			Respondent ID from the external survey platform
		email	varchar(255)						Personal data
		phone	varchar(50)				✓		Personal data
		name	varchar(255)						Personal data
		created_at	timestamp					now()
		updated_at	timestamp					now()

Indexes

• Primary key (id)
• UNIQUE: external_user_id
• INDEX: idx_candidates_email (email)

Notes

• email is not unique — different respondents may share an address, so external_user_id is the uniqueness key
• email / phone / name are personal data and are anonymized by the pii-cleanup batch once past the retention period (3 months by default)
• Related rows such as project_candidates survive anonymization for aggregation

---

project_candidates

Overview

A candidate's participation in a project. Holds all project-scoped information — status, score, selection type, answers — and is the central table of the system.

Table Definition

Logical name	Physical name	Column	Type	PK	Relation	Unique	Nullable	Default	Notes
Project candidate	project_candidates	id	integer	✓				auto-increment
		project_id	integer		projects:id (cascade)
		candidate_id	integer		candidates:id (cascade)
		attributes	jsonb				✓		Attributes (prefecture, age, etc.)
		survey_responses	jsonb				✓		Survey answers (pivoted)
		score	decimal(10,2)				✓		Scoring result
		selection_type	selection_type				✓		primary / reserve
		segment_id	varchar(255)				✓		Logical key into arrange_settings.step2.segments[].id
		memo	text				✓		Free-form memo
		priority	varchar(1)				✓		Priority (A / B / C)
		status	pc_status					'not_contacted'	10-value enum
		created_at	timestamp					now()
		updated_at	timestamp					now()

Relations

• project_id → projects.id (onDelete: cascade)
• candidate_id → candidates.id (onDelete: cascade)
• Child tables (onDelete: cascade): email_logs / scheduling_tokens / candidate_available_dates / reservations / status_histories

Indexes

• Primary key (id)
• UNIQUE INDEX: uk_project_candidate (project_id, candidate_id)
• INDEX: idx_pc_project_status (project_id, status)
• INDEX: idx_pc_project_score (project_id, score)

Notes

• uk_project_candidate makes duplicate enrolment in one project impossible
• status may only move along the transitions in VALID_STATUS_TRANSITIONS (see the diagram in Features); invalid moves raise InvalidStatusTransitionError
• segment_id is not a foreign key — it is a string pointing at a segment definition inside projects.arrange_settings
• attributes and survey_responses are both jsonb; the candidate list's responseFilter query filters across the two

---

project_interviewers

Overview

Join table between projects and interviewers (users).

Table Definition

Logical name	Physical name	Column	Type	PK	Relation	Unique	Nullable	Default	Notes
Project interviewer	project_interviewers	id	integer	✓				auto-increment
		project_id	integer		projects:id (cascade)
		user_id	integer		users:id (cascade)
		created_at	timestamp					now()

Relations

• project_id → projects.id (onDelete: cascade)
• user_id → users.id (onDelete: cascade)

Indexes

• Primary key (id)
• UNIQUE INDEX: uk_project_user (project_id, user_id)

Notes

• The main / sub distinction is not stored here but in projects.arrange_settings.step1.mainInterviewerIds / subInterviewerIds
• Availability calculation reads the Google Calendars of the interviewers registered here