Database (Email)

Two tables: email templates and delivery logs.

---

email_templates

Overview

Templates for emails sent to candidates. Rows with a NULL project_id are system-wide templates visible to every project.

Table Definition

Logical name	Physical name	Column	Type	PK	Relation	Unique	Nullable	Default	Notes
Email template	email_templates	id	integer	✓				auto-increment
		project_id	integer		projects:id (cascade)		✓		System-wide when NULL
		type	template_type						invitation / reminder / confirmation / cancellation
		name	varchar(255)						Template name
		subject	varchar(500)						Subject (supports placeholders)
		body	text						Body (supports placeholders)
		created_at	timestamp					now()
		updated_at	timestamp					now()

Relations

• project_id → projects.id (onDelete: cascade), nullable
• Child table: email_logs.template_id (no FK constraint)

Indexes

• Primary key (id)
• INDEX: idx_et_project_type (project_id, type)

Notes

• subject / body may embed placeholders such as {{candidate_name}} and {{project_name}}
• System-wide templates come from GET /api/v1/email-templates; project-specific ones from GET /api/v1/projects/{projectId}/email-templates
• type has four values, and a project may hold several templates of the same type (no uniqueness constraint)

---

email_logs

Overview

A record of sent emails. Subject and body are copied in full so past deliveries remain readable even if the template later changes or is deleted.

Table Definition

Logical name	Physical name	Column	Type	PK	Relation	Unique	Nullable	Default	Notes
Email log	email_logs	id	integer	✓				auto-increment
		project_candidate_id	integer		project_candidates:id (cascade)				Recipient candidate
		template_id	integer		email_templates:id (logical)		✓		Template used
		subject	varchar(500)						Rendered subject
		body	text						Rendered body
		sent_at	timestamp					now()	Sent at
		status	email_status					'sent'	sent / delivered / bounced / failed
		sent_by	integer		users:id (logical)				User who triggered the send

Relations

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

Indexes

• Primary key (id)
• INDEX: idx_el_project_candidate (project_candidate_id)
• INDEX: idx_el_sent_at (sent_at)

Notes

• subject / body hold the content after placeholder substitution, so editing a template does not alter past records
• Because template_id has no FK constraint, logs survive template deletion (the value remains but no longer resolves)
• status defaults to sent; the API does not currently update it to delivered / bounced
• While PROTOTYPE_MODE=true no email is actually sent; whether a log row is written follows the implementation in services/email.ts
• Per-candidate history comes from GET /projects/{id}/candidates/{cid}/email-logs; project-wide history from GET /projects/{id}/emails/logs