Database Overview

Ground Rules

Item	Value
DBMS	PostgreSQL 17
ORM	Drizzle ORM
Schema definition	`src/db/schema.ts` (a single file is the sole source of truth)
Development workflow	`bun run db:push` (applies the schema directly to the DB)
Migrations	`bun run db:generate` produces files; `scripts/migrate.ts` runs them in production
GUI	`bun run db:studio` (Drizzle Studio)

Names are snake_case and table names are plural. Every primary key is a serial (auto-incrementing integer). Timestamps use timestamp (without time zone).

No soft deletes

There is no deleted_at soft-delete pattern. Deletes are physical, and related rows go with them via onDelete: cascade. Only reservations express cancellation as status = 'cancelled'.

ER Diagram

erDiagram
  users ||--o{ projects : "created_by"
  users ||--o{ project_interviewers : "user_id"
  users ||--o{ reservations : "interviewer_id"
  users ||--o{ candidate_available_dates : "interviewer_id"
  projects ||--o{ project_candidates : "project_id"
  projects ||--o{ project_interviewers : "project_id"
  projects ||--o{ email_templates : "project_id"
  projects ||--o{ import_logs : "project_id"
  candidates ||--o{ project_candidates : "candidate_id"
  project_candidates ||--o| reservations : "project_candidate_id"
  project_candidates ||--o{ scheduling_tokens : "project_candidate_id"
  project_candidates ||--o{ candidate_available_dates : "project_candidate_id"
  project_candidates ||--o{ email_logs : "project_candidate_id"
  project_candidates ||--o{ status_histories : "project_candidate_id"
  email_templates ||--o{ email_logs : "template_id"

The survey metadata cache (databricks_surveys) has no foreign keys to any of the above. Answers are not stored in Postgres at all; they are read straight from Databricks (see Databricks integration).

Table Index

Table	Logical name	Group	Page
`users`	User	Core	Core
`projects`	Project	Core	Core
`candidates`	Candidate	Core	Core
`project_candidates`	Project candidate	Core	Core
`project_interviewers`	Project interviewer	Core	Core
`scheduling_tokens`	Scheduling token	Scheduling	Scheduling
`candidate_available_dates`	Candidate available date	Scheduling	Scheduling
`reservations`	Reservation	Scheduling	Scheduling
`status_histories`	Status history	Scheduling	Scheduling
`email_templates`	Email template	Email	Email
`email_logs`	Email log	Email	Email
`import_logs`	Import log	Import	Import & Survey Metadata Cache
`databricks_surveys`	Survey metadata	Import	Import & Survey Metadata Cache

Enums

Enum	Values	Used by
`user_role`	`admin` / `member` / `viewer`	`users.role`
`project_status`	`draft` / `active` / `completed` / `archived`	`projects.status`
`interview_type`	`online_meet` / `online_zoom` / `offline` / `any`	`projects.interview_type`
`selection_type`	`primary` / `reserve`	`project_candidates.selection_type`
`pc_status`	`not_contacted` / `contacted` / `waiting_response` / `scheduling` / `scheduled` / `interviewed` / `completed` / `declined` / `bounced` / `cancelled`	`project_candidates.status`
`template_type`	`invitation` / `reminder` / `confirmation` / `cancellation`	`email_templates.type`
`email_status`	`sent` / `delivered` / `bounced` / `failed`	`email_logs.status`
`reservation_interview_type`	`online_meet` / `online_zoom` / `offline`	`reservations.interview_type`
`reservation_status`	`confirmed` / `cancelled`	`reservations.status`
`import_type`	`manual_tsv` / `manual_survey` / `auto_survey`	`import_logs.import_type`
`import_status`	`pending` / `processing` / `completed` / `partial` / `failed`	`import_logs.status`

Two interview type enums

Projects allow "unspecified", hence any; a confirmed reservation always has a concrete format, so reservation_interview_type has no any.

JSON Columns

Structured JSON columns have their types declared in src/db/schema.ts or src/types/survey-import.ts.

Table.column	Type	Contents
`projects.scoring_rules`	`ScoringRules`	Array of scoring rules plus a maximum
`projects.survey_import_mapping`	`ImportMapping`	Field mapping from survey answers
`projects.survey_import_filter`	`ImportFilter`	Filter conditions applied during a survey import
`projects.arrange_settings`	`ArrangeSettings`	Settings for arrange flow STEP1–3
`project_candidates.attributes`	`Record<string, unknown>`	Candidate attributes (jsonb)
`project_candidates.survey_responses`	`Record<string, unknown>`	Survey answers (jsonb)
`import_logs.error_details`	`ImportError[]`	Per-row import errors
`import_logs.payload`	`Record<string, unknown>`	The request that triggered the import

arrange_settings is defined twice

Both the backend's src/db/schema.ts and the frontend's src/types/arrange-settings.ts declare it. Change one and you must change the other.

Indexing Policy

Composite indexes cover the columns used for list filtering (idx_pc_project_status, idx_res_interviewer_scheduled, etc.)
Uniqueness is declared explicitly with unique() (uk_project_candidate, uk_project_user)
The survey metadata cache indexes synced_at