Live Dashboard - CRM Module | UnifiedBeez Architecture

👥 FRAME 1: CRM Module Overview

🎯 Purpose

The CRM (Customer Relationship Management) module is the central hub for managing all customer data, interactions, and relationships in UnifiedBeez. It provides comprehensive contact management, list segmentation, tagging, custom merge fields, communication history tracking, task management, document storage, and activity logging. The CRM integrates seamlessly with the Inbox module for unified customer views.

✨ Core CRM Features

📇 Contacts

Centralized contact database
Custom fields via merge fields
Import/Export (CSV, Excel)
Duplicate detection & merging
360° customer view
Contact scoring

📋 Lists & Segmentation

Static & dynamic lists
Advanced filtering rules
Auto-segmentation
List membership tracking
Bulk operations

🏷️ Tags

Multi-tag support
Tag hierarchies
Auto-tagging rules
Tag-based filtering
Tag analytics

📝 Merge Fields

Custom contact attributes
10+ field types
Validation rules
Use in templates & automations
Conditional fields

📞 Communication History

All-channel tracking
Email, WhatsApp, SMS logs
Call recordings
Meeting notes
Timeline view

✅ Tasks & Activities

Contact-linked tasks
Reminders & due dates
Activity logging
Document attachments
Team collaboration

🔄 Contact Lifecycle

Contact Lifecycle in CRM - 5 Stage Journey

LEAD

Initial contact captured
Source tracking (website, campaign, referral)
Lead scoring

↓

PROSPECT

Qualified lead
Active engagement
Nurture campaigns

↓

CUSTOMER

First purchase/conversion
Customer onboarding
Relationship building

↓

LOYAL CUSTOMER

Repeat purchases
High engagement
Referrals

↓

AT-RISK / CHURNED

Declining engagement
Win-back campaigns
Reactivation efforts

🔗 Integration with Other Modules

Module	Integration Point	Data Flow
Inbox	Contact identification in conversations	CRM → Inbox (contact data), Inbox → CRM (conversation history)
Automations	Trigger automations based on contact changes	CRM → Automations (contact events), Automations → CRM (updates)
Campaigns	List-based targeting	CRM → Campaigns (segmented lists), Campaigns → CRM (engagement data)
Analytics	Contact insights & reporting	CRM → Analytics (contact metrics)

🔧 FRAME 2: Backend Services Architecture

🏗️ Service Layer Overview

The CRM module consists of 6 core NestJS services handling contacts, lists, tags, merge fields, communications, tasks, documents, and activities. All services follow domain-driven design principles with clear separation of concerns.

📊 Service Architecture Diagram

CRM Service Layer Architecture

👤 ContactService

create()
update()
findOne()
search()
merge()
import()
export()

📋 ListService

create()
addContact()
segment()
filter()

↕️

🔖 TagService

create()
assign()
analytics()

📝 MergeFieldService

createField()
validate()
setFieldValue()

↕️

💬 CommunicationService

log()
getHistory()
search()

✅ TaskService

create()
assign()
complete()
remind()

📄 DocumentService

upload()
attach()

🔹 Core Services Description

1. ContactService

Responsibility: Manage contact CRUD operations and data quality

Key Methods:

createContact(data) - Create new contact with duplicate detection
updateContact(id, data) - Update contact information
findContactById(id) - Retrieve contact with full details
searchContacts(query, filters) - Full-text search across contacts
mergeContacts(primaryId, duplicateIds) - Merge duplicate contacts
importContacts(file, mappings) - Bulk import from CSV/Excel
exportContacts(filters, format) - Export to CSV/Excel
deleteContact(id) - Soft delete with GDPR compliance

2. ListService

Responsibility: Manage contact lists and segmentation

Key Methods:

createList(name, type, rules) - Create static or dynamic list
addContactToList(listId, contactIds) - Add contacts to list
removeContactFromList(listId, contactIds) - Remove contacts
evaluateDynamicList(listId) - Re-evaluate segmentation rules
getListMembers(listId, pagination) - Retrieve list contacts
cloneList(listId) - Duplicate list with members

3. TagService

Responsibility: Manage tags and tagging

Key Methods:

createTag(name, color, parentId) - Create tag with hierarchy
assignTags(contactId, tagIds) - Add tags to contact
removeTags(contactId, tagIds) - Remove tags from contact
getTagAnalytics(tagId) - Get contact count and trends
bulkTagContacts(contactIds, tagIds) - Bulk tagging operation

4. MergeFieldService

Responsibility: Custom field schema and validation

Key Methods:

createMergeField(schema) - Define new custom field
validateFieldValue(fieldId, value) - Validate against rules
setContactFieldValue(contactId, fieldId, value) - Update field
getMergeFields(scope) - Get all fields for contact or org

5. CommunicationService

Responsibility: Track all contact communications

Key Methods:

logCommunication(contactId, type, data) - Record interaction
getCommunicationHistory(contactId) - Timeline view
searchCommunications(query) - Full-text search

6. TaskService

Responsibility: Contact-related task management

Key Methods:

createTask(contactId, title, dueDate) - Create task
assignTask(taskId, userId) - Assign to team member
completeTask(taskId) - Mark as done
sendReminder(taskId) - Email/notification reminder

🔌 FRAME 3: API Endpoints

📡 REST API Overview

The CRM module exposes 50+ RESTful API endpoints across contacts, lists, tags, merge fields, communications, tasks, and documents. All endpoints support pagination, filtering, and sorting.

🔑 Contact Endpoints (15 endpoints)

Method	Endpoint	Description
GET	`/api/crm/contacts`	Get all contacts with pagination & filters
GET	`/api/crm/contacts/:id`	Get single contact with full 360° view
POST	`/api/crm/contacts`	Create new contact
PUT	`/api/crm/contacts/:id`	Update contact information
DELETE	`/api/crm/contacts/:id`	Soft delete contact (GDPR compliant)
GET	`/api/crm/contacts/search`	Full-text search across contacts
POST	`/api/crm/contacts/merge`	Merge duplicate contacts
POST	`/api/crm/contacts/import`	Bulk import from CSV/Excel
GET	`/api/crm/contacts/export`	Export contacts to CSV/Excel
POST	`/api/crm/contacts/:id/tags`	Add tags to contact
DELETE	`/api/crm/contacts/:id/tags/:tagId`	Remove tag from contact
POST	`/api/crm/contacts/:id/lists`	Add contact to lists
GET	`/api/crm/contacts/:id/timeline`	Get contact activity timeline
POST	`/api/crm/contacts/bulk-update`	Bulk update multiple contacts
GET	`/api/crm/contacts/:id/duplicates`	Find potential duplicate contacts

📋 List Endpoints (10 endpoints)

Method	Endpoint	Description
GET	`/api/crm/lists`	Get all lists
POST	`/api/crm/lists`	Create new list (static or dynamic)
GET	`/api/crm/lists/:id`	Get list details with member count
PUT	`/api/crm/lists/:id`	Update list settings/rules
DELETE	`/api/crm/lists/:id`	Delete list
GET	`/api/crm/lists/:id/contacts`	Get list members with pagination
POST	`/api/crm/lists/:id/contacts`	Add contacts to list
DELETE	`/api/crm/lists/:id/contacts`	Remove contacts from list
POST	`/api/crm/lists/:id/evaluate`	Re-evaluate dynamic list rules
POST	`/api/crm/lists/:id/clone`	Duplicate list with members

🏷️ Tag & Merge Field Endpoints (12 endpoints)

Method	Endpoint	Description
GET	`/api/crm/tags`	Get all tags with hierarchy
POST	`/api/crm/tags`	Create new tag
PUT	`/api/crm/tags/:id`	Update tag
DELETE	`/api/crm/tags/:id`	Delete tag
GET	`/api/crm/tags/:id/analytics`	Get tag usage analytics
POST	`/api/crm/tags/bulk-assign`	Bulk tag assignment
GET	`/api/crm/merge-fields`	Get all custom fields
POST	`/api/crm/merge-fields`	Create custom field
PUT	`/api/crm/merge-fields/:id`	Update field schema
DELETE	`/api/crm/merge-fields/:id`	Delete custom field
POST	`/api/crm/merge-fields/:id/validate`	Validate field value
PUT	`/api/crm/contacts/:id/fields/:fieldId`	Set contact field value

💾 FRAME 4: Database Schema

🗄️ PostgreSQL Schema Overview

The CRM module uses 10 core tables in PostgreSQL to store contacts, lists, tags, merge fields, communications, tasks, documents, and activities. All tables leverage JSONB for flexible custom data.

📋 Core Database Tables

1. contacts

Purpose: Central contact repository

CREATE TABLE contacts (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),

  -- Basic info
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  email VARCHAR(255),
  phone VARCHAR(50),

  -- Additional fields
  company VARCHAR(255),
  job_title VARCHAR(255),
  website VARCHAR(500),

  -- Address
  address_line1 VARCHAR(255),
  address_line2 VARCHAR(255),
  city VARCHAR(100),
  state VARCHAR(100),
  postal_code VARCHAR(20),
  country VARCHAR(100),

  -- Lifecycle
  lifecycle_stage VARCHAR(50) DEFAULT 'lead',
  -- 'lead', 'prospect', 'customer', 'loyal', 'churned'

  -- Scoring
  score INT DEFAULT 0,

  -- Source tracking
  source VARCHAR(100), -- 'website', 'referral', 'import', etc.
  source_details JSONB DEFAULT '{}',

  -- Custom fields (merge field values)
  custom_fields JSONB DEFAULT '{}',

  -- Metadata
  metadata JSONB DEFAULT '{}',

  -- Timestamps
  first_contact_at TIMESTAMP,
  last_contact_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  deleted_at TIMESTAMP, -- Soft delete

  -- Indexes
  INDEX idx_contacts_org (organization_id),
  INDEX idx_contacts_email (email),
  INDEX idx_contacts_phone (phone),
  INDEX idx_contacts_lifecycle (lifecycle_stage),
  INDEX idx_contacts_score (score DESC),
  INDEX idx_contacts_deleted (deleted_at),

  -- Full-text search
  INDEX idx_contacts_search_fts USING GIN (
    to_tsvector('english',
      COALESCE(first_name, '') || ' ' ||
      COALESCE(last_name, '') || ' ' ||
      COALESCE(email, '') || ' ' ||
      COALESCE(company, '')
    )
  )
);

2. contact_lists

Purpose: Organize contacts into lists

CREATE TABLE contact_lists (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),

  name VARCHAR(255) NOT NULL,
  description TEXT,

  -- List type
  type VARCHAR(50) NOT NULL, -- 'static', 'dynamic'

  -- Dynamic list rules (for auto-segmentation)
  segmentation_rules JSONB DEFAULT '{}',
  -- { operator: 'AND/OR', conditions: [{ field, operator, value }] }

  -- Stats
  contact_count INT DEFAULT 0,

  created_by BIGINT REFERENCES users(id),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_contact_lists_org (organization_id),
  INDEX idx_contact_lists_type (type)
);

3. contact_list_members

Purpose: Many-to-many relationship

CREATE TABLE contact_list_members (
  id BIGSERIAL PRIMARY KEY,
  list_id BIGINT NOT NULL REFERENCES contact_lists(id) ON DELETE CASCADE,
  contact_id BIGINT NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,

  added_by BIGINT REFERENCES users(id),
  added_at TIMESTAMP DEFAULT NOW(),

  UNIQUE INDEX idx_list_members_unique (list_id, contact_id),
  INDEX idx_list_members_list (list_id),
  INDEX idx_list_members_contact (contact_id)
);

4. tags

Purpose: Tagging system for contacts

CREATE TABLE tags (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),

  name VARCHAR(100) NOT NULL,
  color VARCHAR(7), -- Hex color
  description TEXT,

  -- Hierarchy support
  parent_tag_id BIGINT REFERENCES tags(id),

  -- Usage stats
  contact_count INT DEFAULT 0,

  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_tags_org (organization_id),
  UNIQUE INDEX idx_tags_org_name (organization_id, name)
);

5. contact_tags

Purpose: Many-to-many relationship

CREATE TABLE contact_tags (
  id BIGSERIAL PRIMARY KEY,
  contact_id BIGINT NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
  tag_id BIGINT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,

  tagged_by BIGINT REFERENCES users(id),
  tagged_at TIMESTAMP DEFAULT NOW(),

  UNIQUE INDEX idx_contact_tags_unique (contact_id, tag_id),
  INDEX idx_contact_tags_contact (contact_id),
  INDEX idx_contact_tags_tag (tag_id)
);

6. merge_fields (Custom Field Schema)

Purpose: Define custom contact attributes

CREATE TABLE merge_fields (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),

  field_key VARCHAR(100) NOT NULL, -- 'customer_tier', 'purchase_count'
  label VARCHAR(255) NOT NULL, -- Display name

  -- Data type
  data_type VARCHAR(50) NOT NULL,
  -- 'text', 'number', 'date', 'boolean', 'select', 'multiselect',
  -- 'email', 'phone', 'url', 'currency'

  -- Validation
  is_required BOOLEAN DEFAULT FALSE,
  validation_rules JSONB DEFAULT '{}',
  -- { min, max, pattern, options: [], currency: 'USD' }

  -- Display
  default_value TEXT,
  placeholder VARCHAR(255),
  help_text TEXT,
  display_order INT DEFAULT 0,

  -- Visibility
  is_system_field BOOLEAN DEFAULT FALSE,

  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_merge_fields_org (organization_id),
  UNIQUE INDEX idx_merge_fields_org_key (organization_id, field_key)
);

7. communications

Purpose: Track all contact interactions

CREATE TABLE communications (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),
  contact_id BIGINT NOT NULL REFERENCES contacts(id),

  -- Communication type
  type VARCHAR(50) NOT NULL,
  -- 'email', 'whatsapp', 'sms', 'call', 'meeting', 'note'

  -- Direction
  direction VARCHAR(20), -- 'inbound', 'outbound'

  -- Content
  subject VARCHAR(500),
  content TEXT,

  -- Related entities
  user_id BIGINT REFERENCES users(id), -- Who handled it
  conversation_id BIGINT REFERENCES conversations(id), -- Link to inbox

  -- Metadata
  metadata JSONB DEFAULT '{}',
  -- { duration, recording_url, attachments, etc. }

  created_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_communications_contact (contact_id, created_at DESC),
  INDEX idx_communications_org (organization_id),
  INDEX idx_communications_type (type),
  INDEX idx_communications_user (user_id)
);

8. tasks

Purpose: Contact-related task management

CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),
  contact_id BIGINT REFERENCES contacts(id),

  title VARCHAR(500) NOT NULL,
  description TEXT,

  -- Assignment
  assigned_to BIGINT REFERENCES users(id),

  -- Due date & priority
  due_date TIMESTAMP,
  priority VARCHAR(20) DEFAULT 'normal', -- 'low', 'normal', 'high', 'urgent'

  -- Status
  status VARCHAR(50) DEFAULT 'pending',
  -- 'pending', 'in_progress', 'completed', 'cancelled'
  completed_at TIMESTAMP,

  -- Reminders
  reminder_at TIMESTAMP,
  reminder_sent BOOLEAN DEFAULT FALSE,

  created_by BIGINT REFERENCES users(id),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_tasks_org (organization_id),
  INDEX idx_tasks_contact (contact_id),
  INDEX idx_tasks_assigned (assigned_to),
  INDEX idx_tasks_due_date (due_date),
  INDEX idx_tasks_status (status)
);

👤 FRAME 5: Contact Management

🎯 Purpose

Contact Management provides a comprehensive 360-degree view of every customer in UnifiedBeez. It enables organizations to store, enrich, segment, and manage contact data with features including duplicate detection, contact merging, bulk import/export, custom attributes via merge fields, relationship mapping, and full GDPR compliance. This module serves as the central repository for all customer information, integrated seamlessly with Inbox, Automations, and Campaign modules.

✨ Core Contact Management Features

📇 Contact CRUD Operations

Create contacts manually or via API
Update contact information (basic + custom fields)
Delete contacts (soft delete for GDPR compliance)
Bulk operations (create, update, delete multiple contacts)
Contact versioning (track changes over time)
Restore deleted contacts (30-day recovery window)

🔍 Duplicate Detection & Merge

Auto-detect duplicates on creation (email, phone matching)
Fuzzy name matching (e.g., "John Smith" vs "Jon Smithe")
Manual duplicate search
Contact merge workflow (choose master record)
Merge history tracking
Conflict resolution UI

📊 Contact Enrichment

Auto-enrich from external APIs (Clearbit, FullContact)
Social profile matching
Company data enrichment
Firmographic data (industry, size, revenue)
Geocoding (coordinates from address)
Email validation and verification

📥 Import & Export

CSV import with field mapping
Excel (XLSX) import
Validate data before import (email format, phone format)
Duplicate handling options (skip, update, merge)
Bulk export to CSV/Excel
Filtered export (export specific segments)
Import history and rollback

🔗 Contact Relationships

Link contacts to organizations/companies
Parent-child relationships (household members)
Referral tracking (who referred whom)
Team member assignments
Relationship types (spouse, colleague, manager)
Network visualization

📌 Custom Attributes

Unlimited custom fields via merge fields
10+ field types (text, number, date, boolean, select, etc.)
Validation rules (regex, min/max, required)
Conditional field visibility
Field groups for organization
Use in automations and campaigns

🔹 Backend Services

1. ContactService

Responsibility: Core contact CRUD and data management

Key Methods:

createContact(data) - Create new contact with duplicate detection
updateContact(id, data) - Update contact information
findContactById(id) - Retrieve contact with full 360° view (includes lists, tags, timeline)
searchContacts(query, filters) - Full-text search with advanced filters
deleteContact(id, permanent) - Soft delete (GDPR compliant) or hard delete
restoreContact(id) - Restore soft-deleted contact within 30 days
bulkCreateContacts(contacts[]) - Batch create multiple contacts
bulkUpdateContacts(updates[]) - Batch update multiple contacts
getContactTimeline(id) - Retrieve activity history

2. ContactEnrichmentService

Responsibility: Auto-enrich contact data from external sources

Key Methods:

enrichContact(contactId) - Enrich single contact from APIs (Clearbit, FullContact)
enrichBulk(contactIds[]) - Batch enrichment
validateEmail(email) - Email verification via external API
geocodeAddress(address) - Convert address to lat/long coordinates
fetchSocialProfiles(email) - Find LinkedIn, Twitter, Facebook profiles
getCompanyData(domain) - Fetch company firmographics

3. MergeService

Responsibility: Duplicate detection and contact merging

Key Methods:

findDuplicates(contactId) - Find potential duplicates using email, phone, name fuzzy matching
findAllDuplicates(organizationId) - Scan all contacts for duplicates
mergeContacts(primaryId, duplicateIds[], mergeStrategy) - Merge contacts into single record
getMergeHistory(contactId) - View merge audit trail
previewMerge(primaryId, duplicateIds[]) - Simulate merge to show conflicts

4. ImportExportService

Responsibility: Bulk import and export operations

Key Methods:

importCSV(file, mappings, options) - Import contacts from CSV with field mapping
validateImport(file, mappings) - Pre-validate import data (check formats, duplicates)
exportContacts(filters, format) - Export contacts to CSV/Excel based on filters
getImportHistory() - View past import jobs with stats
rollbackImport(importId) - Undo import (delete imported contacts)

📡 API Endpoints (15 endpoints)

Method	Endpoint	Description
GET	`/api/crm/contacts`	Get all contacts with pagination & filters
GET	`/api/crm/contacts/:id`	Get single contact with 360° view (lists, tags, timeline)
POST	`/api/crm/contacts`	Create new contact (auto duplicate detection)
PUT	`/api/crm/contacts/:id`	Update contact information
DELETE	`/api/crm/contacts/:id`	Soft delete contact (GDPR compliant, 30-day recovery)
POST	`/api/crm/contacts/:id/restore`	Restore soft-deleted contact
GET	`/api/crm/contacts/search`	Full-text search across contacts (name, email, phone, custom fields)
POST	`/api/crm/contacts/merge`	Merge duplicate contacts into single master record
GET	`/api/crm/contacts/:id/duplicates`	Find potential duplicates for specific contact
POST	`/api/crm/contacts/:id/enrich`	Enrich contact data from external APIs
POST	`/api/crm/contacts/import`	Bulk import from CSV/Excel with field mapping
GET	`/api/crm/contacts/export`	Export contacts to CSV/Excel (with filters)
POST	`/api/crm/contacts/bulk-update`	Bulk update multiple contacts at once
POST	`/api/crm/contacts/bulk-delete`	Bulk delete multiple contacts
GET	`/api/crm/contacts/:id/timeline`	Get contact activity timeline (all interactions)

💾 Database Schema

contacts (Enhanced)

CREATE TABLE contacts (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),

  -- Basic info
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  email VARCHAR(255),
  phone VARCHAR(50),
  mobile VARCHAR(50),

  -- Additional fields
  company VARCHAR(255),
  job_title VARCHAR(255),
  website VARCHAR(500),

  -- Address
  address_line1 VARCHAR(255),
  address_line2 VARCHAR(255),
  city VARCHAR(100),
  state VARCHAR(100),
  postal_code VARCHAR(20),
  country VARCHAR(100),
  timezone VARCHAR(100),

  -- Geolocation (from enrichment)
  latitude DECIMAL(10, 7),
  longitude DECIMAL(10, 7),

  -- Lifecycle
  lifecycle_stage VARCHAR(50) DEFAULT 'lead',
  -- 'lead', 'prospect', 'customer', 'loyal', 'churned'

  -- Scoring
  score INT DEFAULT 0,
  last_score_update TIMESTAMP,

  -- Source tracking
  source VARCHAR(100), -- 'website', 'referral', 'import', etc.
  source_details JSONB DEFAULT '{}',

  -- Custom fields (merge field values)
  custom_fields JSONB DEFAULT '{}',
  -- { 'customer_tier': 'gold', 'purchase_count': 5, 'ltv': 1250.50 }

  -- Enrichment
  enrichment_data JSONB DEFAULT '{}',
  -- { 'socialProfiles': [...], 'companyData': {...}, 'verified': true }
  enriched_at TIMESTAMP,

  -- Engagement tracking
  last_email_opened_at TIMESTAMP,
  last_email_clicked_at TIMESTAMP,
  last_whatsapp_at TIMESTAMP,
  last_activity_at TIMESTAMP,

  -- Metadata
  metadata JSONB DEFAULT '{}',

  -- Owner
  owner_id BIGINT REFERENCES users(id),

  -- Timestamps
  first_contact_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  deleted_at TIMESTAMP, -- Soft delete

  -- Indexes
  INDEX idx_contacts_org (organization_id),
  INDEX idx_contacts_email (email),
  INDEX idx_contacts_phone (phone),
  INDEX idx_contacts_lifecycle (lifecycle_stage),
  INDEX idx_contacts_score (score DESC),
  INDEX idx_contacts_owner (owner_id),
  INDEX idx_contacts_deleted (deleted_at),
  INDEX idx_contacts_last_activity (last_activity_at DESC),

  -- Full-text search
  INDEX idx_contacts_search_fts USING GIN (
    to_tsvector('english',
      COALESCE(first_name, '') || ' ' ||
      COALESCE(last_name, '') || ' ' ||
      COALESCE(email, '') || ' ' ||
      COALESCE(company, '') || ' ' ||
      COALESCE(phone, '')
    )
  ),

  -- JSONB indexes for custom fields
  INDEX idx_contacts_custom_fields USING GIN (custom_fields)
);

contact_timeline

CREATE TABLE contact_timeline (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),
  contact_id BIGINT NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,

  -- Event type
  event_type VARCHAR(100) NOT NULL,
  -- 'created', 'updated', 'email_sent', 'email_opened', 'tag_added',
  -- 'list_joined', 'task_created', 'note_added', 'call_logged', etc.

  -- Event details
  event_data JSONB NOT NULL,
  -- {
  --   field: 'lifecycle_stage',
  --   oldValue: 'lead',
  --   newValue: 'customer',
  --   changedBy: 'userId'
  -- }

  -- Related entities
  related_user_id BIGINT REFERENCES users(id),
  related_automation_id BIGINT REFERENCES user_automations(id),
  related_conversation_id BIGINT REFERENCES conversations(id),

  created_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_timeline_contact (contact_id, created_at DESC),
  INDEX idx_timeline_org (organization_id),
  INDEX idx_timeline_type (event_type)
);

contact_relationships

CREATE TABLE contact_relationships (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),

  contact_id BIGINT NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
  related_contact_id BIGINT NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,

  relationship_type VARCHAR(100) NOT NULL,
  -- 'spouse', 'colleague', 'manager', 'referral', 'household_member', etc.

  notes TEXT,

  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),

  UNIQUE INDEX idx_contact_relationships_unique (contact_id, related_contact_id, relationship_type),
  INDEX idx_contact_relationships_contact (contact_id),
  INDEX idx_contact_relationships_related (related_contact_id)
);

contact_merge_history

CREATE TABLE contact_merge_history (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),

  primary_contact_id BIGINT NOT NULL REFERENCES contacts(id),
  merged_contact_ids BIGINT[] NOT NULL, -- Array of merged contact IDs

  merge_strategy JSONB,
  -- { fieldResolution: { email: 'primary', phone: 'duplicate1' } }

  merged_data JSONB,
  -- Backup of merged contacts data for audit

  merged_by BIGINT REFERENCES users(id),
  merged_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_merge_history_primary (primary_contact_id),
  INDEX idx_merge_history_org (organization_id)
);

🖼️ UI Screenshots Reference

Contact Management UI Components

Live Dashboard - CRM - Contacts.png - Main contacts table view with filters
Live Dashboard - CRM - Contacts-1.png - Contact list with status indicators
Live Dashboard - CRM - Contacts-2.png - Alternate contact view layout
Live Dashboard - CRM - CreatebContacts.png - Create new contact form
Live Dashboard - CRM - Contacts Details (Email).png - Contact detail view showing email information
Live Dashboard - CRM Contacts Details (Activity).png - Contact activity timeline
Live Dashboard - CRM Contacts Details (Activity details).png - Detailed activity breakdown
Live Dashboard - CRM Contacts Details (Communications).png - Communication history view
Live Dashboard - CRM Contacts Details (Communications)-1.png - Alternate communications view
Live Dashboard - CRM Contacts Details (Documents).png - Attached documents
Live Dashboard - CRM Contacts Details (Documents)-1.png - Document management interface
Live Dashboard - CRM Contacts Details (Tasks).png - Contact-related tasks
Live Dashboard - CRM Contacts Details (Tasks)-1.png - Task management view
Live Dashboard - CRM Contacts Details (Send Email).png - Send email to contact
Filter - List.png, Filter - List-1.png, Filter - List-2.png, Filter - List-3.png, Filter - List-4.png - List filtering options
Filter - Tag.png, Filter - Tags.png - Tag filtering interface
Filter - Status.png, Filter - Status-1.png - Status filtering

🔐 GDPR Compliance

Data Protection in Contact Management

Right to Access: Contacts can request full data export (all fields, timeline, communications) via /api/gdpr/data-export/:contactId
Right to Erasure: Soft delete with 30-day recovery window, then permanent deletion with cascading deletes across all related tables
Data Portability: Export contact data in machine-readable format (JSON, CSV) via API
Consent Tracking: Track consent status for email, SMS, WhatsApp in consent_preferences field
Data Minimization: Only collect necessary fields, optional fields clearly marked
Audit Trail: All contact modifications logged in contact_timeline with user attribution
Encryption: PII encrypted at rest in PostgreSQL, encrypted in transit via TLS 1.3
Retention Policy: Deleted contacts permanently purged after 30 days (configurable per organization)
Data Breach Notification: Alert mechanisms if contact data accessed abnormally

📋 FRAME 6: Lists & Segmentation

🎯 Purpose

Lists & Segmentation provides powerful tools to organize contacts into static lists and dynamic segments for targeted marketing campaigns, automations, and analytics. Static lists are manually managed collections, while dynamic segments auto-refresh based on real-time conditions (e.g., "Customers who purchased in last 30 days"). The system supports complex Boolean logic (AND/OR/NOT), nested conditions, and integration with automations for triggered workflows based on list membership changes.

✨ Core List & Segmentation Features

📌 Static Lists

Manual contact addition/removal
Bulk import contacts to list
CSV-based list population
List membership persists until manually changed
Ideal for: Campaign recipients, VIP customers, event attendees
Supports list cloning and archiving
List-level permissions (who can edit)

🎯 Dynamic Segments

Auto-refresh based on conditions
Real-time membership updates
Complex Boolean logic (AND/OR/NOT)
Nested condition groups
Scheduled re-evaluation (hourly, daily)
Ideal for: Active customers, High-value leads, At-risk users
Performance-optimized SQL queries

🔍 Advanced Filtering

Filter by contact fields (name, email, lifecycle, score)
Filter by tags (has all, has any, has none)
Filter by list membership
Filter by custom fields (10+ operators)
Filter by engagement (email opens, clicks, last activity)
Filter by date ranges
Save filters as reusable segment templates

📊 Segment Analytics

Real-time member count
Growth trends over time
Segment overlap analysis (Venn diagrams)
Demographic breakdowns
Engagement metrics per segment
Revenue attribution by segment

🔄 Segment Types

Behavioral: Based on actions (purchased, opened email)
Demographic: Age, location, job title
Firmographic: Company size, industry, revenue
RFM: Recency, Frequency, Monetary analysis
Engagement: Active, inactive, dormant users
Lifecycle: Lead, prospect, customer, churned

⚙️ Automation Integration

Trigger automation when contact joins list
Trigger when contact leaves list
Conditional actions based on list membership
Auto-add contacts to lists based on behavior
List-based campaign targeting

🔹 Backend Services

1. ListService

Responsibility: Manage static and dynamic lists

Key Methods:

createList(name, type, rules) - Create static or dynamic list
addContactToList(listId, contactIds[]) - Add contacts to static list
removeContactFromList(listId, contactIds[]) - Remove contacts from list
getListMembers(listId, pagination) - Retrieve contacts in list
cloneList(listId) - Duplicate list with members
deleteList(listId) - Delete list (contacts remain, only membership removed)
getListAnalytics(listId) - Get member count, growth trends

2. SegmentationEngine

Responsibility: Evaluate and refresh dynamic segments

Key Methods:

evaluateDynamicList(listId) - Re-evaluate segment rules and update membership
previewSegment(rules) - Preview member count before saving
buildSegmentQuery(rules) - Convert rules to optimized SQL
scheduleRefresh(listId, interval) - Schedule auto-refresh (hourly, daily)
getSegmentChanges(listId) - Get contacts who joined/left since last refresh

3. FilterBuilder

Responsibility: Build complex filter queries

Key Methods:

parseFilterRules(rules) - Parse JSON filter rules into SQL WHERE clause
validateRules(rules) - Validate filter syntax before execution
optimizeQuery(query) - Optimize SQL for performance (add indexes hints)
getSupportedOperators() - Get available operators (equals, contains, greater_than, etc.)

📡 API Endpoints (10 endpoints)

Method	Endpoint	Description
GET	`/api/crm/lists`	Get all lists (static and dynamic) with member counts
POST	`/api/crm/lists`	Create new list (static or dynamic with rules)
GET	`/api/crm/lists/:id`	Get list details with member count and rules
PUT	`/api/crm/lists/:id`	Update list settings/rules
DELETE	`/api/crm/lists/:id`	Delete list (contacts remain, only membership removed)
GET	`/api/crm/lists/:id/contacts`	Get list members with pagination
POST	`/api/crm/lists/:id/contacts`	Add contacts to static list (bulk operation)
DELETE	`/api/crm/lists/:id/contacts`	Remove contacts from list (bulk operation)
POST	`/api/crm/lists/:id/evaluate`	Re-evaluate dynamic list rules and refresh membership
POST	`/api/crm/lists/:id/clone`	Duplicate list with members

💾 Database Schema

contact_lists (Enhanced)

CREATE TABLE contact_lists (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),

  name VARCHAR(255) NOT NULL,
  description TEXT,

  -- List type
  type VARCHAR(50) NOT NULL, -- 'static', 'dynamic'

  -- Dynamic list rules (for auto-segmentation)
  segmentation_rules JSONB DEFAULT '{}',
  -- {
  --   operator: 'AND', // Root operator
  --   conditions: [
  --     { field: 'lifecycle_stage', operator: 'equals', value: 'customer' },
  --     {
  --       operator: 'OR',
  --       conditions: [
  --         { field: 'score', operator: 'greater_than', value: 50 },
  --         { field: 'tags', operator: 'contains', value: 'vip' }
  --       ]
  --     }
  --   ]
  -- }

  -- Auto-refresh settings
  auto_refresh BOOLEAN DEFAULT FALSE,
  refresh_interval VARCHAR(50), -- 'hourly', 'daily', 'weekly'
  last_refreshed_at TIMESTAMP,

  -- Stats
  contact_count INT DEFAULT 0,
  growth_rate DECIMAL(5,2), -- % change since last week

  -- Access control
  is_public BOOLEAN DEFAULT TRUE,
  created_by BIGINT REFERENCES users(id),

  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_contact_lists_org (organization_id),
  INDEX idx_contact_lists_type (type),
  INDEX idx_contact_lists_refresh (auto_refresh, last_refreshed_at)
);

contact_list_members (Enhanced)

CREATE TABLE contact_list_members (
  id BIGSERIAL PRIMARY KEY,
  list_id BIGINT NOT NULL REFERENCES contact_lists(id) ON DELETE CASCADE,
  contact_id BIGINT NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,

  -- Membership source
  added_method VARCHAR(50), -- 'manual', 'automation', 'import', 'segment_evaluation'
  added_by BIGINT REFERENCES users(id),
  added_at TIMESTAMP DEFAULT NOW(),

  -- For dynamic lists: track when added by auto-evaluation
  auto_added BOOLEAN DEFAULT FALSE,

  UNIQUE INDEX idx_list_members_unique (list_id, contact_id),
  INDEX idx_list_members_list (list_id),
  INDEX idx_list_members_contact (contact_id),
  INDEX idx_list_members_auto (auto_added)
);

segments (Predefined segment templates)

CREATE TABLE segments (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),

  name VARCHAR(255) NOT NULL,
  description TEXT,
  segment_type VARCHAR(100),
  -- 'behavioral', 'demographic', 'rfm', 'engagement', 'lifecycle'

  -- Segment definition
  conditions JSONB NOT NULL,

  -- Usage tracking
  usage_count INT DEFAULT 0,
  is_template BOOLEAN DEFAULT FALSE,

  created_by BIGINT REFERENCES users(id),
  created_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_segments_org (organization_id),
  INDEX idx_segments_type (segment_type)
);

segment_conditions (Granular condition tracking)

CREATE TABLE segment_conditions (
  id BIGSERIAL PRIMARY KEY,
  segment_id BIGINT NOT NULL REFERENCES segments(id) ON DELETE CASCADE,

  -- Condition definition
  field VARCHAR(255) NOT NULL, -- 'contact.score', 'contact.lifecycle_stage', 'contact.custom_fields.tier'
  operator VARCHAR(50) NOT NULL, -- 'equals', 'not_equals', 'contains', 'greater_than', 'in_list', 'between'
  value JSONB, -- Flexible value (string, number, array, object)

  -- Logical grouping
  logic VARCHAR(10) DEFAULT 'AND', -- 'AND', 'OR', 'NOT'
  group_id INT, -- For grouping conditions with parentheses
  parent_group_id INT, -- For nested groups

  created_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_segment_conditions_segment (segment_id),
  INDEX idx_segment_conditions_field (field)
);

🎨 Segment Query Examples

Example 1: Active High-Value Customers

-- Segment Rule (JSON):
{
  "operator": "AND",
  "conditions": [
    { "field": "lifecycle_stage", "operator": "equals", "value": "customer" },
    { "field": "score", "operator": "greater_than", "value": 80 },
    { "field": "last_activity_at", "operator": "greater_than", "value": "30_days_ago" }
  ]
}

-- Generated SQL:
SELECT c.*
FROM contacts c
WHERE c.lifecycle_stage = 'customer'
  AND c.score > 80
  AND c.last_activity_at > (NOW() - INTERVAL '30 days')
  AND c.deleted_at IS NULL;

Example 2: At-Risk Customers (Complex Boolean)

-- Segment Rule (JSON):
{
  "operator": "AND",
  "conditions": [
    { "field": "lifecycle_stage", "operator": "equals", "value": "customer" },
    {
      "operator": "OR",
      "conditions": [
        { "field": "last_activity_at", "operator": "less_than", "value": "90_days_ago" },
        { "field": "score", "operator": "less_than", "value": 30 }
      ]
    },
    { "field": "tags", "operator": "not_contains", "value": "churned" }
  ]
}

-- Generated SQL:
SELECT c.*
FROM contacts c
LEFT JOIN contact_tags ct ON c.id = ct.contact_id
LEFT JOIN tags t ON ct.tag_id = t.id
WHERE c.lifecycle_stage = 'customer'
  AND (
    c.last_activity_at < (NOW() - INTERVAL '90 days')
    OR c.score < 30
  )
  AND (t.name IS NULL OR t.name != 'churned')
  AND c.deleted_at IS NULL;

🖼️ UI Screenshots Reference

Lists & Segmentation UI Components

Live Dashboard - CRM - Lists.png - Main lists management view
Live Dashboard - CRM - Lists-1.png - List detail view with members
Live Dashboard - CRM - Lists-2.png - Dynamic segment builder interface
Filter - List.png through Filter - List-4.png - Advanced list filtering UI showing Boolean logic builder

🔐 GDPR Compliance

Data Protection in Lists & Segmentation

Right to Access: Contacts can view which lists they belong to via data export
Right to Erasure: When contact deleted, list memberships automatically removed (cascading delete)
Consent Tracking: Segments can filter by consent status (e.g., "Contacts who consented to marketing emails")
Data Retention: List membership history retained for 90 days for audit, then purged
Audit Trail: All list operations logged (who added/removed contacts, when)

🏷️ FRAME 7: Tags & Merge Fields

🎯 Purpose

Tags & Merge Fields provide flexible mechanisms for organizing contacts and personalizing communications. Tags are simple labels for categorization (e.g., "VIP", "Newsletter Subscriber"), while Merge Fields are custom contact attributes with data types, validation, and deep integration across UnifiedBeez. Together, they enable sophisticated segmentation, personalization in emails/WhatsApp, conditional content, and automation triggers based on tag or field changes.

✨ Core Features

📝 Merge Fields

10+ field types (text, number, date, boolean, select, multiselect, email, phone, URL, currency)
Validation rules (regex patterns, min/max, required)
Default values and placeholders
Conditional field visibility
Field groups for organization
Use in email templates ({{first_name}}, {{custom_tier}})
Use in automations for conditions and actions
Calculations (e.g., LTV = sum of purchases)

🎨 Personalization Engine

Dynamic content in emails based on merge fields
Conditional blocks (show/hide based on field values)
Fallback values (if field empty, show default)
Date formatting ({{signup_date | date:'MM/DD/YYYY'}})
Number formatting ({{revenue | currency:'USD'}})
String transformations (uppercase, lowercase, capitalize)
Logical operations (if/else in templates)

⚙️ Auto-Tagging Rules

Tag contacts who open email
Tag contacts who click specific link
Tag based on purchase amount (e.g., "High-Spender")
Tag based on form submissions
Tag based on page visits
Remove tags after period of inactivity
Rule priority and conflict resolution

📊 Field Types & Use Cases

Field Type	Description	Example Use Case	Validation
Text	Single-line text input	Middle name, nickname, department	Max length, regex pattern
Number	Integer or decimal	Age, purchase count, credit limit	Min/max value, decimal places
Date	Date or datetime	Birthday, contract renewal date	Date range, must be future/past
Boolean	Yes/No or True/False	Newsletter subscriber, has account	N/A
Select	Single choice dropdown	Customer tier (Bronze/Silver/Gold)	Must be from predefined options
Multiselect	Multiple choice dropdown	Interests (Sports, Music, Tech)	Must be from predefined options
Email	Email address (with validation)	Secondary email, work email	Email format validation
Phone	Phone number (with formatting)	Alternate phone, office phone	Phone format validation
URL	Web address	LinkedIn profile, portfolio	URL format validation
Currency	Monetary value	Lifetime value, credit balance	Currency code, min/max

🔹 Backend Services

1. TagService

Responsibility: Manage tags and tagging operations

Key Methods:

createTag(name, color, parentId) - Create tag with optional hierarchy
assignTags(contactId, tagIds[]) - Add multiple tags to contact
removeTags(contactId, tagIds[]) - Remove tags from contact
getTagAnalytics(tagId) - Get contact count, growth trends
bulkTagContacts(contactIds[], tagIds[]) - Tag multiple contacts at once
getTagHierarchy() - Get full tag tree structure
mergeTag(sourceTagId, targetTagId) - Merge two tags (move all contacts to target)

2. MergeFieldService

Responsibility: Manage custom field schema and values

Key Methods:

createMergeField(schema) - Define new custom field with type and validation
updateMergeField(fieldId, schema) - Update field definition
validateFieldValue(fieldId, value) - Validate value against field rules
setContactFieldValue(contactId, fieldId, value) - Set value for contact
getContactFieldValues(contactId) - Get all custom field values for contact
deleteMergeField(fieldId) - Delete field (optionally preserve data)
getMergeFields(scope) - Get all fields for organization

3. PersonalizationEngine

Responsibility: Render personalized content using merge fields

Key Methods:

renderTemplate(template, contactId) - Replace merge tags with contact data
evaluateConditional(expression, contactId) - Evaluate if/else logic
formatValue(value, type, format) - Format dates, numbers, currencies
getFallbackValue(field, defaultValue) - Return default if field empty
previewTemplate(template, sampleContact) - Preview with sample data

📡 API Endpoints (12 endpoints)

Method	Endpoint	Description
GET	`/api/crm/tags`	Get all tags with hierarchy and usage counts
POST	`/api/crm/tags`	Create new tag
PUT	`/api/crm/tags/:id`	Update tag (name, color, parent)
DELETE	`/api/crm/tags/:id`	Delete tag (remove from all contacts)
GET	`/api/crm/tags/:id/analytics`	Get tag usage analytics (contact count, trends)
POST	`/api/crm/tags/bulk-assign`	Bulk tag assignment to multiple contacts
GET	`/api/crm/merge-fields`	Get all custom fields for organization
POST	`/api/crm/merge-fields`	Create custom field with type and validation
PUT	`/api/crm/merge-fields/:id`	Update field schema
DELETE	`/api/crm/merge-fields/:id`	Delete custom field
POST	`/api/crm/merge-fields/:id/validate`	Validate field value against rules
PUT	`/api/crm/contacts/:id/fields/:fieldId`	Set contact field value

💾 Database Schema

tags (Enhanced)

CREATE TABLE tags (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),

  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100), -- URL-friendly version
  color VARCHAR(7), -- Hex color (#FF5733)
  description TEXT,

  -- Hierarchy support
  parent_tag_id BIGINT REFERENCES tags(id),
  hierarchy_path VARCHAR(500), -- e.g., "1.5.12" for nested tags

  -- Usage stats
  contact_count INT DEFAULT 0,

  -- Auto-tagging rules
  auto_tag_rules JSONB DEFAULT '[]',
  -- [{ event: 'email_opened', campaignId: 123, action: 'add' }]

  is_system_tag BOOLEAN DEFAULT FALSE, -- System tags can't be deleted

  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_tags_org (organization_id),
  INDEX idx_tags_parent (parent_tag_id),
  UNIQUE INDEX idx_tags_org_name (organization_id, name)
);

contact_tags (Enhanced)

CREATE TABLE contact_tags (
  id BIGSERIAL PRIMARY KEY,
  contact_id BIGINT NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
  tag_id BIGINT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,

  -- Tag source
  tagged_method VARCHAR(50), -- 'manual', 'automation', 'auto_rule'
  tagged_by BIGINT REFERENCES users(id),
  tagged_at TIMESTAMP DEFAULT NOW(),

  -- Auto-removal
  auto_remove_at TIMESTAMP, -- For temporary tags

  UNIQUE INDEX idx_contact_tags_unique (contact_id, tag_id),
  INDEX idx_contact_tags_contact (contact_id),
  INDEX idx_contact_tags_tag (tag_id)
);

merge_fields (Custom Field Schema)

CREATE TABLE merge_fields (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),

  field_key VARCHAR(100) NOT NULL, -- 'customer_tier', 'purchase_count', 'ltv'
  label VARCHAR(255) NOT NULL, -- Display name "Customer Tier"

  -- Data type
  data_type VARCHAR(50) NOT NULL,
  -- 'text', 'number', 'date', 'boolean', 'select', 'multiselect',
  -- 'email', 'phone', 'url', 'currency'

  -- Validation
  is_required BOOLEAN DEFAULT FALSE,
  validation_rules JSONB DEFAULT '{}',
  -- {
  --   min: 0,
  --   max: 100,
  --   pattern: '^[A-Za-z]+$',
  --   options: ['Bronze', 'Silver', 'Gold', 'Platinum'],
  --   currency: 'USD'
  -- }

  -- Display
  default_value TEXT,
  placeholder VARCHAR(255),
  help_text TEXT,
  display_order INT DEFAULT 0,

  -- Field group (for organizing in UI)
  field_group VARCHAR(100), -- 'Contact Info', 'Company', 'Custom'

  -- Visibility
  is_system_field BOOLEAN DEFAULT FALSE,
  is_visible BOOLEAN DEFAULT TRUE,

  -- Calculation (for computed fields)
  calculation_formula TEXT,
  -- e.g., "SUM(purchases.amount)" for LTV

  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_merge_fields_org (organization_id),
  UNIQUE INDEX idx_merge_fields_org_key (organization_id, field_key)
);

🖼️ UI Screenshots Reference

Tags & Merge Fields UI Components

Live Dashboard - CRM - Tags.png - Main tags management interface
Live Dashboard - CRM - Tags-1.png - Tag details and analytics
Live Dashboard - CRM - Tags-2.png - Tag hierarchy view
Live Dashboard - CRM - Merge Fields.png - Merge fields management
Live Dashboard - CRM - Merge Fields-1.png - Create/edit merge field
Live Dashboard - CRM - Merge Fields-2.png - Field validation settings
Tags Dropdown.png - Tag selector dropdown in UI
Filter - Tag.png, Filter - Tags.png - Tag-based filtering interface

🔐 GDPR Compliance

Data Protection for Tags & Merge Fields

Right to Access: Contacts can view tags and custom field values in data export
Right to Erasure: Tags removed when contact deleted; custom field values deleted with contact
Sensitive Data: Option to mark merge fields as "sensitive" (encrypted storage, restricted access)
Audit Trail: All tag assignments and field updates logged with timestamp and user
Data Minimization: Only create necessary custom fields, avoid collecting excessive data

📞 FRAME 8: Communication History

🎯 Purpose

Communication History provides a complete, unified timeline of all interactions with each contact across multiple channels (email, WhatsApp, SMS, web chat, social media, phone calls, meetings). This 360-degree communication view enables agents to understand the full context of customer relationships, track engagement patterns, analyze conversation sentiment, and ensure continuity across team members. All communications are logged automatically and linked to the Inbox module for seamless workflow integration.

✨ Core Communication Features

🕐 Timeline View

Chronological activity stream
Filter by channel or date range
Group by conversation thread
Show only outbound or inbound
Highlight important interactions
Search within history
Export history to PDF/CSV

📈 Interaction Analytics

Total interaction count
Channel breakdown (% email vs WhatsApp)
Response time averages
Engagement trends over time
Sentiment analysis (positive/negative/neutral)
Conversation topics (AI-extracted)
Team member activity

🔗 Integration with Inbox

Link communications to conversations
View full conversation thread from CRM
Reply directly from history view
Auto-log all inbox interactions
Sync read/unread status
Unified notification system

📎 Attachments & Media

Track all file attachments
Preview images, PDFs, documents
Download history
Link to contact documents
File versioning

🔐 Privacy & Compliance

Redaction of sensitive info
Data retention policies
Right to access (export history)
Right to erasure (delete history)
Audit trail for all access
Encrypted storage

🔹 Backend Services

1. CommunicationHistoryService

Responsibility: Track and retrieve all contact communications

Key Methods:

logCommunication(contactId, type, data) - Record interaction (email, WhatsApp, call, etc.)
getCommunicationHistory(contactId, filters) - Retrieve timeline with filters
getConversationThread(conversationId) - Get full thread of related messages
searchCommunications(contactId, query) - Full-text search within history
exportHistory(contactId, format) - Export to PDF/CSV for GDPR or legal
deleteCommunication(id) - Remove single interaction (with audit trail)
getChannelBreakdown(contactId) - Analytics by channel

2. TimelineService

Responsibility: Build comprehensive contact timeline

Key Methods:

getContactTimeline(contactId) - Full activity stream (communications + CRM events)
getTimelineEvents(contactId, dateRange) - Filter by date
groupByConversation(events) - Organize into conversation threads
getImportantMoments(contactId) - Highlight key interactions (first contact, purchases)

3. ActivityLogger

Responsibility: Auto-log activities from other modules

Key Methods:

logEmailEvent(contactId, emailId, event) - Log email sent/opened/clicked
logWhatsAppMessage(contactId, messageId) - Log WhatsApp interaction
logPhoneCall(contactId, callData) - Log call with duration, recording URL
logMeetingNote(contactId, noteData) - Log meeting outcome
logInternalNote(contactId, userId, note) - Private team note

📡 API Endpoints (8 endpoints)

Method	Endpoint	Description
GET	`/api/crm/contacts/:id/history`	Get communication history for contact (all channels)
GET	`/api/crm/contacts/:id/timeline`	Get full activity timeline (communications + CRM events)
GET	`/api/crm/contacts/:id/history/search`	Search communication history
GET	`/api/crm/contacts/:id/history/export`	Export communication history to PDF/CSV
POST	`/api/crm/contacts/:id/history`	Manually log communication (e.g., phone call, meeting)
DELETE	`/api/crm/communications/:id`	Delete communication record (with audit trail)
GET	`/api/crm/contacts/:id/analytics/engagement`	Get engagement analytics (channel breakdown, trends)
GET	`/api/crm/conversations/:conversationId/thread`	Get full conversation thread from inbox

💾 Database Schema

communications (Enhanced)

CREATE TABLE communications (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),
  contact_id BIGINT NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,

  -- Communication type
  type VARCHAR(50) NOT NULL,
  -- 'email', 'whatsapp', 'sms', 'call', 'meeting', 'note',
  -- 'web_chat', 'facebook', 'instagram', 'twitter'

  -- Direction
  direction VARCHAR(20), -- 'inbound', 'outbound'

  -- Content
  subject VARCHAR(500),
  content TEXT,
  content_html TEXT, -- For email

  -- Related entities
  user_id BIGINT REFERENCES users(id), -- Who handled it
  conversation_id BIGINT REFERENCES conversations(id), -- Link to inbox
  automation_id BIGINT REFERENCES user_automations(id), -- If triggered by automation

  -- Channel-specific data
  channel_data JSONB DEFAULT '{}',
  -- For email: { messageId, openedAt, clickedAt, bounced, spamScore }
  -- For WhatsApp: { messageId, status, readAt }
  -- For call: { duration, recordingUrl, outcome }

  -- Attachments
  attachments JSONB DEFAULT '[]',
  -- [{ filename, url, size, mimeType }]

  -- Sentiment (AI-powered)
  sentiment VARCHAR(20), -- 'positive', 'negative', 'neutral'
  sentiment_score DECIMAL(3,2), -- -1.0 to 1.0

  -- Engagement tracking
  was_opened BOOLEAN DEFAULT FALSE,
  opened_at TIMESTAMP,
  was_clicked BOOLEAN DEFAULT FALSE,
  clicked_at TIMESTAMP,

  -- Status
  status VARCHAR(50), -- 'sent', 'delivered', 'read', 'failed', 'bounced'

  -- Privacy
  is_private BOOLEAN DEFAULT FALSE, -- Internal notes not visible to contact

  -- Metadata
  metadata JSONB DEFAULT '{}',

  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_communications_contact (contact_id, created_at DESC),
  INDEX idx_communications_org (organization_id),
  INDEX idx_communications_type (type),
  INDEX idx_communications_user (user_id),
  INDEX idx_communications_conversation (conversation_id),

  -- Full-text search
  INDEX idx_communications_search_fts USING GIN (
    to_tsvector('english',
      COALESCE(subject, '') || ' ' ||
      COALESCE(content, '')
    )
  )
);

contact_interactions (Aggregated engagement metrics)

CREATE TABLE contact_interactions (
  id BIGSERIAL PRIMARY KEY,
  contact_id BIGINT NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),

  -- Time period
  date DATE NOT NULL,

  -- Interaction counts by channel
  email_sent INT DEFAULT 0,
  email_received INT DEFAULT 0,
  email_opened INT DEFAULT 0,
  email_clicked INT DEFAULT 0,
  whatsapp_sent INT DEFAULT 0,
  whatsapp_received INT DEFAULT 0,
  sms_sent INT DEFAULT 0,
  sms_received INT DEFAULT 0,
  calls_inbound INT DEFAULT 0,
  calls_outbound INT DEFAULT 0,
  meetings INT DEFAULT 0,

  -- Total interactions
  total_interactions INT DEFAULT 0,

  -- Response times (in minutes)
  avg_response_time_inbound INT,
  avg_response_time_outbound INT,

  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),

  UNIQUE INDEX idx_contact_interactions_contact_date (contact_id, date),
  INDEX idx_contact_interactions_org (organization_id)
);

activity_logs (Full audit trail)

CREATE TABLE activity_logs (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),
  contact_id BIGINT REFERENCES contacts(id) ON DELETE CASCADE,

  -- Activity type
  activity_type VARCHAR(100) NOT NULL,
  -- 'contact_created', 'field_updated', 'tag_added', 'list_joined',
  -- 'email_sent', 'call_logged', 'note_added', etc.

  -- Activity details
  activity_data JSONB NOT NULL,
  -- {
  --   field: 'email',
  --   oldValue: 'old@example.com',
  --   newValue: 'new@example.com',
  --   changedBy: 'userId'
  -- }

  -- Actor
  user_id BIGINT REFERENCES users(id),

  -- Related entities
  related_communication_id BIGINT REFERENCES communications(id),
  related_automation_id BIGINT REFERENCES user_automations(id),

  -- IP and metadata (for security audits)
  ip_address VARCHAR(45),
  user_agent VARCHAR(500),

  created_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_activity_logs_contact (contact_id, created_at DESC),
  INDEX idx_activity_logs_org (organization_id),
  INDEX idx_activity_logs_type (activity_type),
  INDEX idx_activity_logs_user (user_id)
);

🖼️ UI Screenshots Reference

Communication History UI Components

Live Dashboard - CRM Contacts Details (Activity).png - Activity timeline view
Live Dashboard - CRM Contacts Details (Activity details).png - Detailed activity breakdown
Live Dashboard - CRM Contacts Details (Communications).png - Communication history main view
Live Dashboard - CRM Contacts Details (Communications)-1.png - Alternate communications layout
Live Dashboard - CRM Contacts Details (Send Email).png - Send email from contact view

🔐 GDPR Compliance

Data Protection in Communication History

Data Retention: Communications retained for configurable period (default 7 years for legal, 90 days for marketing)
Right to Access: Contacts can export full communication history via /api/gdpr/data-export/:contactId
Right to Erasure: Delete all communications when contact requests deletion (cascading delete)
Redaction: Option to redact sensitive information from communications before export
Consent Tracking: Log consent for recording calls, storing chat transcripts
Audit Trail: All access to communication history logged in activity_logs table
Encryption: Communications encrypted at rest (PostgreSQL encryption), in transit (TLS 1.3)
Anonymization: After retention period, option to anonymize (remove PII but keep metadata for analytics)

📅 FRAME 9: Diary Alerts & Reminders

🎯 Purpose

The Diary Alerts system provides proactive reminders and notifications for tasks, appointments, follow-ups, and important contact interactions. It ensures sales teams, support agents, and relationship managers never miss critical customer touchpoints, helping maintain engagement and driving conversions.

✨ Core Features

📌 Alert Types

Task Reminders: Upcoming task due dates, overdue tasks
Appointment Alerts: Meeting reminders (15 min, 1 hour, 1 day before)
Follow-Up Notifications: Contact follow-up schedules
Deal Stage Changes: Alerts when deals move between stages
Activity Deadlines: Time-sensitive actions requiring attention
Custom Alerts: User-defined reminder rules

📨 Delivery Channels

In-App Notifications: Real-time browser notifications
Email Alerts: Digest emails (daily/weekly summaries)
SMS Notifications: Critical reminders via Twilio
Push Notifications: Mobile app alerts (future)
Slack Integration: Team channel notifications (optional)

⚙️ Customization Options

Alert Timing: Configure reminder intervals (5 min to 7 days)
Frequency Control: One-time vs. recurring reminders
Priority Levels: Low, Medium, High, Urgent
Snooze Functionality: Postpone alerts (15 min, 1 hour, 1 day)
Do Not Disturb: Quiet hours configuration
Notification Preferences: Per-alert-type channel selection

🔧 Backend Services

DiaryAlertsService

Responsibilities:

Create, update, delete diary alerts
Schedule alert delivery via Bull Queue
Process snooze and dismiss actions
Track alert status (pending, sent, read, snoozed, dismissed)
Generate alert history and analytics

Key Methods:

createAlert(userId, contactId, type, dueDate, message)
scheduleReminder(alertId, interval)
snoozeAlert(alertId, snoozeDuration)
dismissAlert(alertId)
getUpcomingAlerts(userId, dateRange)

NotificationDeliveryService

Responsibilities:

Send notifications via multiple channels (email, SMS, in-app)
Manage notification preferences per user
Handle delivery failures and retries
Track delivery status and read receipts
Generate notification analytics

Key Methods:

sendEmailAlert(userId, alertData)

sendSMSAlert(userId, alertData)

sendInAppNotification(userId, alertData)

updateNotificationPreferences(userId, preferences)

getDeliveryStatus(notificationId)

AlertSchedulerService

Responsibilities:

Calculate reminder trigger times based on due dates
Queue alerts for future delivery
Handle recurring reminders (daily, weekly, monthly)
Manage alert priorities and delivery order
Process bulk alert creation for campaigns

Key Methods:

calculateReminderTime(dueDate, interval)
queueAlert(alertId, scheduledTime)
processRecurringAlerts()
bulkCreateAlerts(contactIds, alertTemplate)

AlertAnalyticsService

Responsibilities:

Track alert engagement metrics (open rate, dismiss rate)
Generate alert effectiveness reports
Identify alert fatigue patterns
Provide optimization recommendations

Key Methods:

trackAlertEngagement(alertId, action)
generateEffectivenessReport(userId, dateRange)
detectAlertFatigue(userId)

🔌 API Endpoints

Endpoint	Method	Description	Request Body
`/api/crm/alerts`	GET	Get all alerts for current user (with filters: status, type, dateRange)	Query params
`/api/crm/alerts`	POST	Create a new diary alert	`{ contactId, type, dueDate, message, priority, reminderIntervals }`
`/api/crm/alerts/:id`	PUT	Update an existing alert	`{ dueDate, message, priority, reminderIntervals }`
`/api/crm/alerts/:id`	DELETE	Delete an alert	-
`/api/crm/alerts/:id/snooze`	POST	Snooze an alert for a specified duration	`{ snoozeDuration: '15min' \| '1hour' \| '1day' }`
`/api/crm/alerts/:id/dismiss`	POST	Dismiss an alert (mark as read/completed)	-
`/api/crm/alerts/upcoming`	GET	Get upcoming alerts (next 7 days by default)	Query params: days
`/api/crm/alerts/overdue`	GET	Get all overdue alerts	-
`/api/crm/alerts/preferences`	GET	Get user notification preferences	-
`/api/crm/alerts/preferences`	PUT	Update notification preferences (channels, quiet hours, frequency)	`{ emailEnabled, smsEnabled, quietHoursStart, quietHoursEnd }`
`/api/crm/alerts/analytics`	GET	Get alert effectiveness analytics	Query params: userId, dateRange
`/api/crm/alerts/bulk`	POST	Create bulk alerts for multiple contacts	`{ contactIds: [], alertTemplate: {...} }`

🗄️ Database Schema

CREATE TABLE diary_alerts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    contact_id UUID REFERENCES contacts(id) ON DELETE CASCADE,
    task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
    deal_id UUID REFERENCES deals(id) ON DELETE SET NULL,

    -- Alert details
    type VARCHAR(50) NOT NULL, -- 'task', 'appointment', 'follow_up', 'deal_stage', 'custom'
    title VARCHAR(255) NOT NULL,
    message TEXT,
    priority VARCHAR(20) DEFAULT 'medium', -- 'low', 'medium', 'high', 'urgent'

    -- Scheduling
    due_date TIMESTAMP WITH TIME ZONE NOT NULL,
    reminder_intervals INTEGER[] DEFAULT '{15, 60, 1440}', -- minutes before (15min, 1hr, 1day)
    is_recurring BOOLEAN DEFAULT false,
    recurrence_rule VARCHAR(100), -- 'daily', 'weekly', 'monthly', 'custom'

    -- Status tracking
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'sent', 'read', 'snoozed', 'dismissed', 'completed'
    snoozed_until TIMESTAMP WITH TIME ZONE,
    dismissed_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE,

    -- Delivery tracking
    last_sent_at TIMESTAMP WITH TIME ZONE,
    delivery_channel VARCHAR(50), -- 'email', 'sms', 'in_app', 'slack'
    delivery_status VARCHAR(20), -- 'queued', 'sent', 'delivered', 'failed'
    read_at TIMESTAMP WITH TIME ZONE,

    -- Metadata
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID REFERENCES users(id),

    -- Indexes
    INDEX idx_diary_alerts_user_id (user_id),
    INDEX idx_diary_alerts_contact_id (contact_id),
    INDEX idx_diary_alerts_due_date (due_date),
    INDEX idx_diary_alerts_status (status),
    INDEX idx_diary_alerts_type (type)
);

CREATE TABLE alert_notification_preferences (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE UNIQUE,

    -- Channel preferences
    email_enabled BOOLEAN DEFAULT true,
    sms_enabled BOOLEAN DEFAULT false,
    in_app_enabled BOOLEAN DEFAULT true,
    slack_enabled BOOLEAN DEFAULT false,

    -- Alert type preferences
    task_alerts_enabled BOOLEAN DEFAULT true,
    appointment_alerts_enabled BOOLEAN DEFAULT true,
    follow_up_alerts_enabled BOOLEAN DEFAULT true,
    deal_alerts_enabled BOOLEAN DEFAULT true,

    -- Timing preferences
    quiet_hours_start TIME,
    quiet_hours_end TIME,
    digest_frequency VARCHAR(20) DEFAULT 'daily', -- 'realtime', 'hourly', 'daily', 'weekly'

    -- Metadata
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE alert_delivery_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    alert_id UUID NOT NULL REFERENCES diary_alerts(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id),

    -- Delivery details
    channel VARCHAR(50) NOT NULL, -- 'email', 'sms', 'in_app', 'slack'
    status VARCHAR(20) NOT NULL, -- 'queued', 'sent', 'delivered', 'failed', 'bounced'
    sent_at TIMESTAMP WITH TIME ZONE,
    delivered_at TIMESTAMP WITH TIME ZONE,
    read_at TIMESTAMP WITH TIME ZONE,

    -- Error tracking
    error_message TEXT,
    retry_count INTEGER DEFAULT 0,

    -- Metadata
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    INDEX idx_alert_delivery_log_alert_id (alert_id),
    INDEX idx_alert_delivery_log_status (status)
);
            

🖼️ UI Screenshots Reference

Location: c:\Users\Aorus15\Desktop\Tech\UnifiedBeez\UnifiedBeez_UI_UX\Live Dashboard UI - CRM\

Enhance UI for Diary Alerts.png - Main diary alerts dashboard view
Enhance UI for Diary Alerts-1.png - Alert creation modal
Enhance UI for Diary Alerts-2.png - Alert notification preview
Enhance UI for Diary Alerts-3.png - Upcoming alerts list view
Enhance UI for Diary Alerts-4.png - Alert preferences/settings
Enhance UI for Diary Alerts-5.png - Alert analytics dashboard

🔒 GDPR Compliance

Data Protection Measures

Data Minimization: Alerts store only essential information (title, due date, contact reference)
Retention Policy: Dismissed alerts retained for 90 days, then auto-deleted
Right to Access: Users can export all their alerts via API endpoint /api/crm/alerts/export
Right to Erasure: Deleting a contact cascades to delete all associated alerts
Data Portability: Alert export available in JSON and CSV formats
Consent for Notifications: Users must opt-in for SMS and email alerts; in-app is default
Audit Trail: All alert actions logged in alert_delivery_log table

UnifiedBeez Backend Architecture

👥 FRAME 1: CRM Module Overview

🎯 Purpose

✨ Core CRM Features

📇 Contacts

📋 Lists & Segmentation

🏷️ Tags

📝 Merge Fields

📞 Communication History

✅ Tasks & Activities

🔄 Contact Lifecycle

Contact Lifecycle in CRM - 5 Stage Journey

LEAD

PROSPECT

CUSTOMER

LOYAL CUSTOMER

AT-RISK / CHURNED

🔗 Integration with Other Modules

🔗 See Also

🔧 FRAME 2: Backend Services Architecture

🏗️ Service Layer Overview

📊 Service Architecture Diagram

CRM Service Layer Architecture

👤 ContactService

📋 ListService

🔖 TagService

📝 MergeFieldService

💬 CommunicationService

✅ TaskService

📄 DocumentService

🔹 Core Services Description

1. ContactService

2. ListService

3. TagService

4. MergeFieldService

5. CommunicationService

6. TaskService

🔗 See Also

🔌 FRAME 3: API Endpoints

📡 REST API Overview

🔑 Contact Endpoints (15 endpoints)

📋 List Endpoints (10 endpoints)

🏷️ Tag & Merge Field Endpoints (12 endpoints)

🔗 See Also

💾 FRAME 4: Database Schema

🗄️ PostgreSQL Schema Overview

📋 Core Database Tables

1. contacts

2. contact_lists

3. contact_list_members

4. tags

5. contact_tags

6. merge_fields (Custom Field Schema)

7. communications

8. tasks

🔗 See Also

👤 FRAME 5: Contact Management

🎯 Purpose

✨ Core Contact Management Features

📇 Contact CRUD Operations

🔍 Duplicate Detection & Merge

📊 Contact Enrichment

📥 Import & Export

🔗 Contact Relationships

📌 Custom Attributes

🔹 Backend Services

1. ContactService

2. ContactEnrichmentService

3. MergeService

4. ImportExportService

📡 API Endpoints (15 endpoints)

💾 Database Schema

contacts (Enhanced)

contact_timeline

contact_relationships

contact_merge_history

🖼️ UI Screenshots Reference

Contact Management UI Components

🔐 GDPR Compliance

Data Protection in Contact Management