Database (Scheduling)

The four tables covering scheduling with candidates and the resulting reservations and history.

---

scheduling_tokens

Overview

Access token for the public candidate page (/scheduling/{token}). It stands in for authentication as proof of identity.

Table Definition

Logical name	Physical name	Column	Type	PK	Relation	Unique	Nullable	Default	Notes
Scheduling token	scheduling_tokens	id	integer	✓				auto-increment
		project_candidate_id	integer		project_candidates:id (cascade)
		token	varchar(255)			✓			Token embedded in the URL
		expires_at	timestamp						Expiry
		created_at	timestamp					now()

Relations

• project_candidate_id → project_candidates.id (onDelete: cascade)

Indexes

• Primary key (id)
• UNIQUE: token
• INDEX: idx_st_project_candidate (project_candidate_id)
• INDEX: idx_st_expires_at (expires_at)

Notes

• The expiry is derived from SCHEDULING_TOKEN_TTL_DAYS (default 14 days) at issue time
• A candidate may have several tokens (one is added on each resend)
• Access with an expired token raises SchedulingTokenError (400 / SCHEDULING_TOKEN_ERROR)

---

candidate_available_dates

Overview

Preferred slots submitted by the candidate from the public page — the "candidates" before confirmation.

Table Definition

Logical name	Physical name	Column	Type	PK	Relation	Unique	Nullable	Default	Notes
Candidate available date	candidate_available_dates	id	integer	✓				auto-increment
		project_candidate_id	integer		project_candidates:id (cascade)
		interviewer_id	integer		users:id (cascade)				Interviewer owning the slot
		slot_id	varchar(255)						Slot ID issued during availability calculation
		start_at	timestamp						Start time
		end_at	timestamp						End time
		created_at	timestamp					now()

Relations

• project_candidate_id → project_candidates.id (onDelete: cascade)
• interviewer_id → users.id (onDelete: cascade)

Indexes

• Primary key (id)
• INDEX: idx_cad_project_candidate (project_candidate_id)

Notes

• Submission (POST /scheduling/{token}/submit-availability) is a full replacement: existing rows are deleted before the new slots are created
• One submission accepts between 1 and 20 slots
• On submission, a candidate whose status is contacted / waiting_response / scheduling is moved to scheduling
• An admin picks one of these rows and calls confirm-schedule to create the reservations row

---

reservations

Overview

A confirmed interview. At most one per project candidate.

Table Definition

Logical name	Physical name	Column	Type	PK	Relation	Unique	Nullable	Default	Notes
Reservation	reservations	id	integer	✓				auto-increment
		project_candidate_id	integer		project_candidates:id (cascade)	✓			One per candidate
		interviewer_id	integer		users:id (restrict)				Assigned interviewer
		scheduled_at	timestamp						Interview start time
		duration_minutes	integer						Duration in minutes
		interview_type	reservation_interview_type						online_meet / online_zoom / offline
		meeting_url	varchar(500)				✓		Google Meet / Zoom URL
		calendar_event_id	varchar(255)				✓		Google Calendar event ID
		zoom_meeting_id	varchar(255)				✓		Zoom meeting ID
		status	reservation_status					'confirmed'	confirmed / cancelled
		created_at	timestamp					now()
		updated_at	timestamp					now()

Relations

• project_candidate_id → project_candidates.id (onDelete: cascade, UNIQUE)
• interviewer_id → users.id (onDelete: restrict)

Indexes

• Primary key (id)
• UNIQUE: project_candidate_id
• INDEX: idx_res_interviewer_scheduled (interviewer_id, scheduled_at)
• INDEX: idx_res_scheduled_at (scheduled_at)

Notes

• Cancellation updates status = 'cancelled' rather than deleting the row
• interview_type is a separate enum from the project's and has no any, since the format is settled at confirmation time
• On confirmation, the held tentative Google Calendar event is converted to a confirmed one and its ID stored in calendar_event_id
• The event lives on the project calendar (projects.google_calendar_id), so calendar_event_id has to be paired with that calendar ID when calling the Calendar API
• While PROTOTYPE_MODE=true calendar operations are skipped, so calendar_event_id stays NULL

---

status_histories

Overview

Record of candidate status transitions; doubles as an audit log.

Table Definition

Logical name	Physical name	Column	Type	PK	Relation	Unique	Nullable	Default	Notes
Status history	status_histories	id	integer	✓				auto-increment
		project_candidate_id	integer		project_candidates:id (cascade)
		old_status	varchar(50)				✓		Previous status; NULL on initial registration
		new_status	varchar(50)						New status
		changed_by	integer		users:id (logical)		✓		Actor; NULL for system-driven changes
		changed_at	timestamp					now()
		note	text				✓		Free-form note

Relations

• project_candidate_id → project_candidates.id (onDelete: cascade)
• changed_by → users.id: declared only in Drizzle's relations(); there is no database-level foreign key constraint

Indexes

• Primary key (id)
• INDEX: idx_sh_pc_changed (project_candidate_id, changed_at)

Notes

• old_status / new_status are varchar(50) rather than enums, so removing a value from the enum does not break historical rows
• One row is appended every time the status update API (PATCH /projects/{id}/candidates/{cid}/status) succeeds