Skip to main content

Communication Recipient Targeting - Analysis Summary

Your Request​

You asked for an analysis of the business_communication_config migration and suggestions for how to support flexible recipient targeting:

Requirements:

  • A) Send to specific roles or role groups
  • B) Create custom groups with mixed users + external emails/phones
  • C) Send to specific emails/phones directly
  • D) Combine all the above

My Analysis & Recommendation​

Current Structure Assessment​

What you have:

  • ✅ business_communication_config - Controls WHAT and HOW (type + channel + timing)
  • ✅ communication table - Individual communication records
  • ✅ communication_preference - Individual preferences
  • ❌ Missing: System to control WHO receives communications

Current handler behavior:

// Hardcoded: Send to ALL business users
const businessUsers = await this.businessUsersService.findByBusinessId(businessId);

Problems:

  1. Can't filter by role
  2. Can't create custom distribution lists
  3. Can't include external contacts
  4. Requires code changes to modify recipients
  5. No per-business customization

Proposed Solution: 4 New Tables​

1. communication_recipient_group​

Purpose: Businesses create custom groups (e.g., "Finance Team")

Key columns:

  • business_id - Which business owns this group
  • name - Group name (unique per business)
  • description - Purpose
  • is_active - Enable/disable

Example:

INSERT INTO communication_recipient_group 
VALUES ('finance-team-id', 'business-123', 'Finance Team', 'Handles all financial communications');

2. communication_group_member​

Purpose: Define who's in each group (polymorphic: users OR emails OR phones)

Key columns:

  • group_id - Which group
  • member_type - ENUM: 'business_user', 'email', 'phone', 'whatsapp'
  • business_user_id - If internal user
  • email_address - If external email
  • phone_number - If external phone
  • display_name - Friendly name

Example:

-- Add internal user to group
INSERT INTO communication_group_member 
VALUES ('member-1', 'finance-team-id', 'business_user', 'user-123', NULL, NULL, NULL);

-- Add external email to group
INSERT INTO communication_group_member 
VALUES ('member-2', 'finance-team-id', 'email', NULL, 'cpa@firm.com', NULL, 'External CPA');

3. business_communication_recipient_rule​

Purpose: Define WHO receives each communication type/channel

Key columns:

  • business_id - Which business
  • communication_type - Which type (e.g., 'low_stock_alert')
  • channel - Which channel (e.g., 'email')
  • targeting_type - ENUM: 'role', 'group', 'ad_hoc_email', 'ad_hoc_phone', 'ad_hoc_whatsapp'
  • role_name - If targeting by role
  • group_id - If targeting by group
  • ad_hoc_* - If targeting specific email/phone

Example:

-- Rule 1: Send to all inventory managers
INSERT INTO business_communication_recipient_rule 
VALUES ('rule-1', 'business-123', 'low_stock_alert', 'email', 'role', 'inventory_manager', NULL, NULL);

-- Rule 2: Send to purchasing team group
INSERT INTO business_communication_recipient_rule 
VALUES ('rule-2', 'business-123', 'low_stock_alert', 'email', 'group', NULL, 'purchasing-team-id', NULL);

-- Rule 3: Send to specific email
INSERT INTO business_communication_recipient_rule
VALUES ('rule-3', 'business-123', 'low_stock_alert', 'email', 'ad_hoc_email', NULL, NULL, 'supplier@vendor.com');

Looking at your table structure, here's a SQL query to insert a rule that sends to a specific email:

INSERT INTO business_communication_recipient_rule (
    business_id,
    communication_type,
    channel,
    targeting_type,
    ad_hoc_email,
    ad_hoc_name,
    priority,
    is_active
) VALUES (
    'your-business-id-here',           -- Replace with actual business UUID
    'low_stock_alert',                 -- Or other communication_type
    'email',                           -- Channel
    'ad_hoc_email',                    -- Targeting type
    'supplier@vendor.com',             -- The specific email address
    'Supplier Contact',                -- Display name (optional but recommended)
    0,                                 -- Priority (0 = default)
    true                               -- Active status
);

Key Points:

  1. targeting_type must be 'ad_hoc_email' for specific email addresses
  2. ad_hoc_email contains the actual email address
  3. ad_hoc_name is optional but useful for display purposes
  4. communication_config_id is NULL (it will apply to all email configs for this type)
  5. The id, created_at, and updated_at are auto-generated

More Complete Example with Optional Fields:

INSERT INTO business_communication_recipient_rule (
    business_id,
    communication_type,
    channel,
    targeting_type,
    ad_hoc_email,
    ad_hoc_name,
    priority,
    notes,
    created_by,
    is_active
) VALUES (
    '123e4567-e89b-12d3-a456-426614174000',  -- business_id
    'low_stock_alert',
    'email',
    'ad_hoc_email',
    'manager@company.com',
    'Operations Manager',
    0,
    'External supplier contact for low stock notifications',
    '987e6543-e21b-12d3-a456-426614174000',  -- user_id who created this
    true
);

4. communication_recipient_log​

Purpose: Audit trail of who was selected and why

Key columns:

  • communication_id - Which communication
  • recipient_contact - Email/phone sent to
  • selection_method - How they were selected ('role', 'group', 'ad_hoc')
  • selection_source_id - Which rule selected them
  • selected_at - Timestamp

Example:

-- Log that john@company.com was selected via role rule
INSERT INTO communication_recipient_log 
VALUES ('log-1', 'comm-456', 'john@company.com', 'John Doe', 'role', 'rule-1', NOW());

How It Works: Complete Example​

Scenario: Low Stock Alert​

Business Configuration:

// Create "Purchasing Team" group
Group: {
  name: "Purchasing Team",
  members: [
    { type: 'business_user', userId: 'user-1' },      // Internal staff
    { type: 'business_user', userId: 'user-2' },      // Internal staff
    { type: 'email', email: 'supplier@vendor.com' },  // External
  ]
}

// Create recipient rules for low_stock_alert
Rules: [
  { targetingType: 'role', roleName: 'inventory_manager', channel: 'email' },
  { targetingType: 'group', groupId: 'purchasing-team', channel: 'email' },
  { targetingType: 'ad_hoc_phone', phone: '+1234567890', channel: 'sms' },
]

When Alert Triggers:

// 1. Handler calls RecipientResolverService
const recipients = await recipientResolverService.resolveRecipients(
  'business-123',
  'low_stock_alert',
  'email'
);

// 2. Service queries rules and resolves:
// Rule 1 (role) → Queries all users with role 'inventory_manager'
//   Result: john@company.com, jane@company.com

// Rule 2 (group) → Queries all members of 'purchasing-team'
//   Result: staff1@company.com, staff2@company.com, supplier@vendor.com

// Rule 3 (ad_hoc_phone) → Uses phone directly
//   Result: +1234567890

// 3. Deduplicate (if same person via multiple rules)

// 4. Return final list:
recipients = [
  { contact: 'john@company.com', source: { method: 'role', ruleId: 'rule-1' } },
  { contact: 'jane@company.com', source: { method: 'role', ruleId: 'rule-1' } },
  { contact: 'staff1@company.com', source: { method: 'group', ruleId: 'rule-2' } },
  { contact: 'staff2@company.com', source: { method: 'group', ruleId: 'rule-2' } },
  { contact: 'supplier@vendor.com', source: { method: 'group', ruleId: 'rule-2' } },
]

// 5. Handler sends to each recipient + logs selection

Key Benefits​

1. No Code Changes Needed ✅​

Businesses configure recipients via admin UI.

Before:

// Change recipients → Modify code → Deploy
const businessUsers = await this.businessUsersService.findByBusinessId(businessId);

After:

// Change recipients → Update rules in database (via UI)
const recipients = await recipientResolverService.resolveRecipients(...);

2. Support for External Contacts ✅​

Include suppliers, accountants, partners, etc.

Example:

Purchasing Team group:
- 3 internal staff (business_user)
- 2 supplier contacts (email)
- 1 manager's phone (phone)

3. Flexible Combinations ✅​

Mix roles, groups, and ad-hoc recipients.

Example:

Low stock alerts sent to:
- All inventory managers (role)
+ Purchasing team (group with 5 members)
+ Supplier backup email (ad-hoc)
+ Owner's phone (ad-hoc)

4. Per-Business Customization ✅​

Each business configures their own rules.

Business A:

low_stock_alert → inventory_manager role only

Business B:

low_stock_alert → inventory_manager role + Purchasing Team group + 2 supplier emails

5. Full Audit Trail ✅​

Know exactly who received what and why.

Query:

SELECT * FROM communication_recipient_log 
WHERE communication_id = 'comm-123';

-- Results show:
-- john@company.com (via role: inventory_manager)
-- jane@company.com (via role: inventory_manager)
-- supplier@vendor.com (via group: Purchasing Team)

Integration with Existing Structure​

Extends business_communication_config​

Current table controls:

  • WHAT: communication_type
  • HOW: channel, template_id
  • WHEN: send_delay_minutes, time windows
  • WHETHER: is_enabled, is_automatic

New tables add:

  • WHO: recipient rules (roles + groups + ad-hoc)

Relationship:

business_communication_config (1) ───< (N) business_communication_recipient_rule
     "What to send"                        "Who receives it"

Implementation Roadmap​

Phase 1: Database (1 day)​

  • Run migration to create 4 tables
  • Verify constraints and indexes

Phase 2: Backend Services (5-7 days)​

  • Create RecipientResolverService (core logic)
  • Create repositories for groups, rules, logs
  • Create management services
  • Write comprehensive tests

Phase 3: Backend APIs (2-3 days)​

  • Group management endpoints (CRUD)
  • Rule management endpoints (CRUD)
  • Preview endpoint (see recipients before saving)

Phase 4: Update Handlers (2 days)​

  • Update low stock alert handler
  • Update other communication handlers
  • Add recipient logging

Phase 5: Frontend UI (5-7 days)​

  • Group management pages
  • Rule configuration pages
  • Preview feature

Phase 6: Testing & Deployment (3-4 days)​

  • Comprehensive testing
  • Staging deployment
  • Production deployment

Total: 4-6 weeks

Files Created​

I've created comprehensive documentation for this solution:

  1. 2025-10-26t00:00:00.000z-communication-recipient-targeting.mjs

    • Database migration with 4 tables
    • Includes rollback script
    • Commented with examples

  2. RECIPIENT-TARGETING-PROPOSAL.md

    • Executive summary for stakeholders
    • Real-world examples
    • UI mockups
    • Timeline and risks

  3. recipient-targeting-system-design.md

    • Detailed architecture
    • Service design patterns
    • Code examples
    • Future enhancements

  4. recipient-targeting-changes-summary.md

    • Before/after comparisons
    • Configuration examples
    • API usage examples

  5. recipient-targeting-erd.md

    • Visual ERD diagrams
    • Table relationships
    • Query examples
    • Index strategies

  6. recipient-targeting-implementation-checklist.md

    • 10-phase implementation plan
    • Detailed tasks
    • Code templates
    • Testing strategies

  7. README-RECIPIENT-TARGETING.md

    • Navigation guide to all docs
    • Quick start guides
    • Success criteria

Recommendation​

✅ Approve this design and proceed with implementation.

Why:

  1. Solves all your requirements (A, B, C, D)
  2. Extends existing structure (no breaking changes)
  3. Backward compatible (handlers still work without rules)
  4. Scalable and flexible
  5. Full audit trail
  6. Admin UI for self-service

Next Steps:

  1. Review the proposal document
  2. Get stakeholder approval
  3. Run database migration in dev/staging
  4. Begin Phase 2 (backend services)

Questions?​

Q: What if no rules are configured?​

A: Fallback to existing behavior (send to all business users). You can also create default rules during business onboarding.

Q: Can recipients opt-out?​

A: Not in initial implementation. Use existing communication_preference table for this in the future.

Q: How do we prevent duplicate recipients?​

A: RecipientResolverService deduplicates based on contact (email/phone).

Q: What about performance?​

A: Proper indexes + caching = sub-500ms resolution. See ERD document for index strategy.

Summary​

Your current business_communication_config table is excellent for controlling WHAT and HOW communications are sent. It's missing the WHO piece.

My proposed solution adds 4 tables that integrate seamlessly with your existing structure, providing flexible recipient targeting with:

  • ✅ Role-based targeting (A)
  • ✅ Custom groups with mixed members (B)
  • ✅ Ad-hoc emails/phones (C)
  • ✅ Combination of all the above (D)

All configurable via admin UI with full audit trail and no code changes needed!

Start with: RECIPIENT-TARGETING-PROPOSAL.md