Recipient Targeting System - Entity Relationship Diagram
High-Level Architecture
┌─────────────────────────────────────────────────────────────────────┐
│ COMMUNICATION CONFIGURATION │
│ │
│ business_communication_config │
│ ┌────────────────────────────────────────────┐ │
│ │ Controls: WHAT + HOW + WHEN │ │
│ │ • communication_type (low_stock_alert) │ │
│ │ • channel (email, sms, whatsapp) │ │
│ │ • timing, rate limits, etc. │ │
│ └───────────────────┬────────────────────────┘ │
│ │ │
│ │ Extended by │
│ ▼ │
│ ┌────────────────────────────────────────────┐ │
│ │ business_communication_recipient_rule │ │
│ │ Controls: WHO receives │ │
│ │ • targeting_type (role/group/ad_hoc) │ │
│ │ • role_name OR group_id OR ad_hoc_* │ │
│ └────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────────┘
Detailed Entity Relationship Diagram
┌──────────────┐
│ business │
│──────────────│
│ id (PK) │
│ name │
└──────┬───────┘
│
┌──────────────────┼──────────────────┐
│ │ │
│ 1:N │ 1:N │ 1:N
▼ ▼ ▼
┌─────────────────────┐ ┌─────────────────┐ ┌─────────────────────┐
│ business_user │ │ business_ │ │ communication_ │
│─────────────────────│ │ communication_ │ │ recipient_group │
│ id (PK) │ │ config │ │─────────────────────│
│ business_id (FK) │ │─────────────────│ │ id (PK) │
│ user_id (FK) │ │ id (PK) │ │ business_id (FK) │
│ unique_role_name │ │ business_id (FK)│ │ name │
│ is_active │ │ comm_type │ │ description │
└──────────┬──────────┘ │ channel │ │ is_active │
│ │ is_enabled │ └──────────┬──────────┘
│ │ is_automatic │ │
│ └────────┬────────┘ │ 1:N
│ │ │
│ │ 1:N ▼
│ ▼ ┌──────────────────────┐
│ ┌──────────────────────┐ │ group_member │
│ │ business_ │ │──────────────────────│
│ │ communication_ │ │ id (PK) │
│ │ recipient_rule │ │ group_id (FK) │
│ │──────────────────────│ │ member_type [ENUM] │
│ │ id (PK) │ │ • business_user │
│ │ business_id (FK) │ │ • email │
│ │ communication_type │ │ • phone │
│ │ channel │ │ • whatsapp │
│ │ targeting_type [ENUM]│ │ │
│ │ • role │ │ Polymorphic: │
│ │ • group │ │ business_user_id (FK)│◄───┘
│ │ • ad_hoc_email │ │ email_address │
│ │ • ad_hoc_phone │ │ phone_number │
│ │ • ad_hoc_whatsapp │ │ display_name │
│ │ │ │ is_active │
│ │ Polymorphic: │ └──────────────────────┘
│ │ role_name │
│ │ group_id (FK) │───────┘
│ │ ad_hoc_email │
│ │ ad_hoc_phone │
│ │ ad_hoc_whatsapp │
│ │ priority │
│ │ is_active │
│ └──────────────────────┘
│
│
┌──────────────┴───────────────┐
│ │
│ RecipientResolverService │
│ ───────────────────────── │
│ Resolves rules to actual │
│ recipients: │
│ │
│ 1. Query active rules │
│ 2. For each rule: │
│ • role → get users │
│ • group → get members │
│ • ad_hoc → use directly │
│ 3. Deduplicate │
│ 4. Return final list │
└──────────────┬───────────────┘
│
│ Returns list of
▼
┌──────────────────────┐
│ ResolvedRecipient[] │
│──────────────────────│
│ type │
│ userId │
│ contact (email/phone)│
│ name │
│ source (how selected)│
└──────────┬───────────┘
│
│ Used by
▼
┌──────────────────────┐
│ CommunicationsService│
│ (Send messages) │
└──────────┬───────────┘
│
│ Creates
▼
┌──────────────────────┐ ┌──────────────────────┐
│ communication │ │ communication_ │
│──────────────────────│ 1:N │ recipient_log │
│ id (PK) │───────►│──────────────────────│
│ business_id │ │ id (PK) │
│ type │ │ communication_id (FK)│
│ channel │ │ recipient_contact │
│ recipient_contact │ │ recipient_name │
│ status │ │ selection_method │
│ ... │ │ selection_source_id │
└──────────────────────┘ │ selected_at │
└──────────────────────┘
(Audit Trail)
Table Relationships Explained
1. business → communication_recipient_group (1:N)
Each business can create multiple recipient groups.
Example:
- Business A has: "Finance Team", "Purchasing Team", "Emergency Team"
2. communication_recipient_group → communication_group_member (1:N)
Each group has multiple members (polymorphic).
Example: "Finance Team" group contains:
- Member 1:
business_user→ John Doe (internal) - Member 2:
business_user→ Jane Smith (internal) - Member 3:
email→ cpa@firm.com (external) - Member 4:
phone→ +1234567890 (external)
3. business_user ← communication_group_member (Optional FK)
Group members can reference business users.
When to use:
member_type = 'business_user'→ Setbusiness_user_idmember_type = 'email'→ Setemail_addressmember_type = 'phone'→ Setphone_number
4. business_communication_config ← business_communication_recipient_rule
Rules extend configs to specify recipients.
Relationship:
- Config says: "Low stock alerts via email are enabled"
- Rules say: "Send to inventory_manager role + Purchasing Team group"
Note: communication_config_id is optional. If null, rules apply to all channels for that type.
5. communication_recipient_group ← business_communication_recipient_rule
Rules can target groups.
Example:
-- Rule targeting a group
{
targeting_type: 'group',
group_id: 'purchasing-team-uuid'
}
6. communication → communication_recipient_log (1:N)
Each communication logs all recipients selected.
Example:
For communication ID abc-123, log entries:
- john@company.com (selected via role: inventory_manager)
- jane@company.com (selected via role: inventory_manager)
- supplier@vendor.com (selected via group: Purchasing Team)
Data Flow Example: Low Stock Alert
1. Event: Low Stock Alert Created
└─ Alert ID: alert-123
Business ID: business-456
2. Handler: OnCreateLowStockAlertHandler
└─ Calls: RecipientResolverService.resolveRecipients(
business-456,
'low_stock_alert',
'email'
)
3. RecipientResolverService queries recipient rules:
Query: SELECT * FROM business_communication_recipient_rule
WHERE business_id = 'business-456'
AND communication_type = 'low_stock_alert'
AND channel = 'email'
AND is_active = true
Results:
┌────────┬──────────────────┬─────────────────┬────────────────┐
│ Rule 1 │ targeting_type │ role │ │
│ │ role_name │ inventory_mgr │ │
├────────┼──────────────────┼─────────────────┼────────────────┤
│ Rule 2 │ targeting_type │ group │ │
│ │ group_id │ purchase-team │ │
├────────┼──────────────────┼─────────────────┼────────────────┤
│ Rule 3 │ targeting_type │ ad_hoc_email │ │
│ │ ad_hoc_email │ supplier@... │ │
└────────┴──────────────────┴─────────────────┴────────────────┘
4. Resolve Rule 1 (role = 'inventory_manager'):
Query: SELECT u.id, u.email, u.full_name
FROM business_user bu
JOIN user u ON u.id = bu.user_id
WHERE bu.business_id = 'business-456'
AND bu.unique_role_name = 'inventory_manager'
AND bu.is_active = true
Results:
- john@company.com (John Doe)
- jane@company.com (Jane Smith)
5. Resolve Rule 2 (group = 'purchase-team'):
Query: SELECT gm.*, u.email, u.full_name
FROM communication_group_member gm
LEFT JOIN business_user bu ON bu.id = gm.business_user_id
LEFT JOIN user u ON u.id = bu.user_id
WHERE gm.group_id = 'purchase-team'
AND gm.is_active = true
Results:
- staff1@company.com (Staff One) [business_user]
- staff2@company.com (Staff Two) [business_user]
- supplier@vendor.com (Main Supplier) [email]
6. Resolve Rule 3 (ad_hoc_email):
Direct use:
- supplier2@vendor.com (Backup Supplier)
7. Deduplicate recipients:
Combined list (6 total):
✓ john@company.com
✓ jane@company.com
✓ staff1@company.com
✓ staff2@company.com
✓ supplier@vendor.com
✓ supplier2@vendor.com
8. Send communications:
FOR EACH recipient:
- Create communication record
- Queue for sending
- Log recipient selection
9. Log recipient selections:
INSERT INTO communication_recipient_log:
- john@company.com (method: role, source: rule-1)
- jane@company.com (method: role, source: rule-1)
- staff1@company.com (method: group, source: rule-2)
- staff2@company.com (method: group, source: rule-2)
- supplier@vendor.com (method: group, source: rule-2)
- supplier2@vendor.com (method: ad_hoc, source: rule-3)
10. Result: 6 emails sent ✅
Polymorphic Relationships
Group Members (Polymorphic by Type)
member_type = 'business_user'
├─ business_user_id → NOT NULL
├─ email_address → NULL
└─ phone_number → NULL
member_type = 'email'
├─ business_user_id → NULL
├─ email_address → NOT NULL
└─ phone_number → NULL
member_type = 'phone'
├─ business_user_id → NULL
├─ email_address → NULL
└─ phone_number → NOT NULL
Recipient Rules (Polymorphic by Targeting Type)
targeting_type = 'role'
├─ role_name → NOT NULL
├─ group_id → NULL
└─ ad_hoc_* → NULL
targeting_type = 'group'
├─ role_name → NULL
├─ group_id → NOT NULL
└─ ad_hoc_* → NULL
targeting_type = 'ad_hoc_email'
├─ role_name → NULL
├─ group_id → NULL
├─ ad_hoc_email → NOT NULL
└─ other ad_hoc_* → NULL
Indexes for Performance
Critical Query Paths
1. Resolve Rules for Business + Type + Channel
-- Index on business_communication_recipient_rule
CREATE INDEX idx_recipient_rule_type_channel
ON business_communication_recipient_rule (business_id, communication_type, channel);
2. Resolve Role to Users
-- Index on business_user
CREATE INDEX idx_business_user_role
ON business_user (business_id, unique_role_name, is_active);
3. Resolve Group Members
-- Index on communication_group_member
CREATE INDEX idx_group_member_active
ON communication_group_member (group_id, is_active);
4. Query Recipient Logs
-- Index on communication_recipient_log
CREATE INDEX idx_recipient_log_communication
ON communication_recipient_log (communication_id);
Summary
The recipient targeting system uses:
- 4 new tables for flexible recipient configuration
- Polymorphic relationships for extensibility
- Service-based resolution for clean separation of concerns
- Audit logging for compliance and debugging
The design allows businesses to configure complex recipient scenarios without code changes while maintaining full audit trails and performance through proper indexing.