Home A - Z
Page Index

Commit a29d45

2025-10-13 16:37:46 Vinay Deokar: "Templates" table added
Projects/HireGenius/Technical Document (LLD+HLD)).md ..
@@ 545,20 545,43 @@
- `idx_comm_type` on `type` (filter by type)
---
+ ##### Table: templates
+ **Purpose:** Store user-specific email templates for communication
+
+ | Column | Type | Constraints | Description |
+ |--------------|-------------|--------------------------------------|---------------------------------------|
+ | template_id | UUID | PRIMARY KEY, DEFAULT gen_random_uuid() | Unique template identifier |
+ | created_by | UUID | FOREIGN KEY REFERENCES users(user_id) | User who created the template |
+ | name | VARCHAR(255)| NOT NULL | Template name |
+ | type | VARCHAR(50) | NOT NULL, CHECK (type IN ('outreach','follow_up','reply','manual')) | Type of template |
+ | subject | VARCHAR(500)| NOT NULL | Email subject template |
+ | body | TEXT | NOT NULL | Email body template |
+ | created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Template creation time |
+ | updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Last update time |
+ **Indexes:**
+ - `idx_templates_user` on `created_by` (fetch templates by user)
+ - `idx_templates_type` on `type` (filter templates by type)
+
+ ---
+
#### 4.3 Database Relationships
| Parent Table | Child Table | Relationship Type | Foreign Key | On Delete |
|--------------|---------------------|-----------------|-----------------------------------------------|-----------|
| users | jobs | One-to-Many | jobs.created_by → users.user_id | SET NULL |
+ | users | templates | One-to-Many | templates.created_by → users.user_id | CASCADE |
| jobs | job_candidate_matches | One-to-Many | job_candidate_matches.job_id → jobs.job_id | CASCADE |
| candidates | job_candidate_matches | One-to-Many | job_candidate_matches.candidate_id → candidates.candidate_id | CASCADE |
| candidates | communication | One-to-Many | communication.candidate_id → candidates.candidate_id | CASCADE |
+ | templates | communication | One-to-Many | communication.template_id → templates.template_id | SET NULL |
**Cascade Deletion Logic:**
- When a job is deleted, all associated job-candidate matches are deleted
- When a candidate is deleted, all associated job-candidate matches and communications are deleted
- - When a user is deleted, their jobs remain but `created_by` is set to NULL
+ - When a user is deleted, their jobs remain but `created_by` is set to NULL
+ - When a user is deleted, all their templates are deleted
+ - When a template is deleted, communications referencing it will have `template_id` set to NULL
---
0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9