Live Dashboard - Automations Module | UnifiedBeez Architecture

⚡ FRAME 1: Automations Module Overview

🎯 Purpose

The Automations module enables organizations to create, manage, and execute workflow automations that respond to business events across UnifiedBeez. It provides a visual automation builder, pre-built templates, AI-powered automation suggestions via Beezora, and comprehensive execution tracking and analytics.

✨ Core Automation Features

🔧 Automation Builder

Visual drag-and-drop interface
50+ trigger types
100+ action types
Conditional logic (if/then/else)
Multi-step workflows
Delay & scheduling

📚 Template Library

200+ pre-built templates
Industry-specific workflows
One-click activation
Customizable templates
Community templates

🤖 Beezora AI Assistant

Natural language automation creation
Smart suggestions
Optimization recommendations
Auto-complete workflows
Error detection

🔄 Automation Types

Type	Description	Use Cases	Example
Event-Triggered	Executes when specific event occurs	New contact, message received, form submitted	Send welcome email when contact added to "VIP" list
Time-Based	Runs on schedule or after delay	Daily reports, follow-ups, reminders	Send reminder 2 days after abandoned cart
Conditional	Executes based on conditions	Lead scoring, segmentation, routing	If contact score > 80, assign to sales team
Multi-Step	Complex workflows with branches	Onboarding sequences, drip campaigns	5-day email course with branching paths

📊 Automation Workflow Example

Example: New Contact Onboarding Automation

🎯 TRIGGER: Contact added to "New Customers" list

↓

ACTION 1: Send welcome email (Template: Welcome)

↓

⏱️ DELAY: Wait 1 hour

↓

ACTION 2: Send WhatsApp message with onboarding link

↓

⏱️ DELAY: Wait 24 hours

↓

❓ CONDITION: Did they click the onboarding link?

✅ YES

ACTION 3: Add tag "Engaged"

↓

ACTION 4: Assign to Success Team

❌ NO

ACTION 5: Send reminder email

↓

⏱️ Wait 48 hours

↓

ACTION 6: Create follow-up task

🎯 Popular Automation Templates

Sales & Lead Generation

Lead scoring & assignment
Abandoned cart recovery
Follow-up sequences
Meeting scheduling
Proposal automation

Support & Engagement

Auto-responses
Ticket routing
Escalation workflows
CSAT surveys
Re-engagement campaigns

Retention & Nurture

Onboarding sequences
Birthday/anniversary wishes
Renewal reminders
Upsell/cross-sell
Win-back campaigns

Operations & Admin

Data enrichment
List cleanup
Reporting automation
Team notifications
Compliance workflows

🔧 FRAME 2: Automation Engine Architecture

🏗️ Engine Overview

The Automation Engine is a scalable, event-driven system built with NestJS and Bull Queue (Redis). It processes automation executions asynchronously, supports parallel and sequential workflows, handles retries and error recovery, and provides real-time execution tracking.

📊 Architecture Diagram

Automation Engine Architecture

📡

Event Source

(Inbox, CRM)

↓

⚙️ AutomationService

matchTriggers()
queueExecution()

↓

🔄 Bull Queue (Redis)

automation-queue
Priority queue
Retry mechanism

↓

⚡ AutomationWorkerService

processExecution()
evaluateConditions()
executeActions()

↓

📧

EmailAction Service

📱

WhatsAppAction Service

🔗

WebhookAction Service

↓

📊 AutomationLogService

logExecution()
trackMetrics()

🔹 Core Services

1. AutomationService

Responsibility: Manage automation CRUD and trigger matching

Key Methods:

createAutomation(config) - Create new automation
matchTriggers(event) - Find automations matching event
queueExecution(automationId, context) - Queue for processing
updateAutomation(id, config) - Update automation config
toggleAutomation(id, active) - Enable/disable

2. AutomationWorkerService

Responsibility: Process automation executions from queue

Key Methods:

processExecution(job) - Main execution handler
evaluateConditions(conditions, context) - Evaluate if/then logic
executeActions(actions, context) - Execute action sequence
handleDelay(duration) - Schedule delayed execution
handleError(error, execution) - Error recovery & retry

3. ActionExecutorServices (20+ action types)

Responsibility: Execute specific action types

Action Types:

Communication: Send email, WhatsApp, SMS
CRM: Add/update contact, add to list, add tag
Tasks: Create task, assign user, set reminder
Notifications: Slack, webhook, internal alert
Data: Update field, calculate score, enrich data
Integration: Call external API, trigger Zapier

📡 API Endpoints (25 endpoints)

Method	Endpoint	Description
GET	`/api/automations`	Get all automations
GET	`/api/automations/:id`	Get single automation with stats
POST	`/api/automations`	Create new automation
PUT	`/api/automations/:id`	Update automation
DELETE	`/api/automations/:id`	Delete automation
PATCH	`/api/automations/:id/toggle`	Enable/disable automation
POST	`/api/automations/:id/clone`	Duplicate automation
POST	`/api/automations/:id/test`	Test automation with sample data
GET	`/api/automations/:id/executions`	Get execution history
GET	`/api/automations/:id/analytics`	Get performance metrics
GET	`/api/automations/templates`	Get template library
POST	`/api/automations/from-template/:id`	Create from template
POST	`/api/automations/ai-suggest`	Get AI-powered suggestions (Beezora)
POST	`/api/automations/ai-create`	Create automation from natural language
GET	`/api/automations/triggers`	Get available trigger types
GET	`/api/automations/actions`	Get available action types

💾 Database Schema

automation_templates

CREATE TABLE automation_templates (
  id BIGSERIAL PRIMARY KEY,

  name VARCHAR(255) NOT NULL,
  description TEXT,
  category VARCHAR(100), -- 'sales', 'support', 'marketing', etc.

  -- Template configuration
  config JSONB NOT NULL,
  -- { trigger, conditions, actions, settings }

  -- Metadata
  is_featured BOOLEAN DEFAULT FALSE,
  usage_count INT DEFAULT 0,
  rating DECIMAL(3,2) DEFAULT 0,

  created_at TIMESTAMP DEFAULT NOW()
);

user_automations

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

  name VARCHAR(255) NOT NULL,
  description TEXT,

  -- Status
  is_active BOOLEAN DEFAULT TRUE,

  -- Configuration
  trigger JSONB NOT NULL,
  -- { type, event, filters }

  conditions JSONB DEFAULT '[]',
  -- [{ field, operator, value, logic }]

  actions JSONB NOT NULL,
  -- [{ type, config, delay }]

  settings JSONB DEFAULT '{}',
  -- { runOnce, maxExecutions, schedule }

  -- Stats
  execution_count INT DEFAULT 0,
  success_count INT DEFAULT 0,
  failure_count INT DEFAULT 0,
  last_executed_at TIMESTAMP,

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

  INDEX idx_user_automations_org (organization_id),
  INDEX idx_user_automations_active (is_active)
);

automation_executions

CREATE TABLE automation_executions (
  id BIGSERIAL PRIMARY KEY,
  automation_id BIGINT NOT NULL REFERENCES user_automations(id),
  organization_id BIGINT NOT NULL,

  -- Execution context
  trigger_event JSONB,
  -- { type, data, timestamp }

  -- Status
  status VARCHAR(50) DEFAULT 'pending',
  -- 'pending', 'running', 'completed', 'failed', 'cancelled'

  -- Results
  actions_executed INT DEFAULT 0,
  actions_total INT,

  error_message TEXT,
  error_details JSONB,

  -- Performance
  started_at TIMESTAMP,
  completed_at TIMESTAMP,
  duration_ms INT,

  -- Execution log
  execution_log JSONB DEFAULT '[]',
  -- [{ step, action, status, timestamp, result }]

  created_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_executions_automation (automation_id, created_at DESC),
  INDEX idx_executions_status (status)
);

📚 FRAME 3: Templates Library

🎯 Purpose

The Templates Library provides 200+ pre-built automation templates across multiple categories (Sales, Support, E-commerce, Marketing, Retention, Operations). Users can browse, preview, and activate templates with one click, then customize them to their specific business needs. Templates accelerate time-to-value by providing proven automation workflows that can be deployed in minutes rather than hours.

📊 Template Categories Overview

🎯 Sales & Lead Generation (45 templates)

Lead scoring and qualification
Abandoned cart recovery sequences
Follow-up automation after demo/trial
Meeting scheduling workflows
Proposal follow-up sequences
Lead assignment and routing
Sales pipeline notifications
Opportunity stage transitions

💬 Support & Escalation (38 templates)

Auto-response for common questions
Ticket routing by priority/category
Escalation workflows for urgent issues
CSAT survey automation
Follow-up after ticket resolution
SLA breach notifications
Knowledge base article suggestions
After-hours support routing

🛍️ E-commerce (32 templates)

Order confirmation sequences
Shipping notifications
Post-purchase follow-up
Review request automation
Upsell/cross-sell campaigns
Inventory alerts
Loyalty program automation
Referral program workflows

🎉 Retention & Nurture (41 templates)

Welcome series (3, 5, 7-day sequences)
Onboarding workflows
Birthday/anniversary campaigns
Renewal reminders
Re-engagement sequences
Win-back campaigns for churned users
Milestone celebrations
Educational drip campaigns

📧 Marketing Campaigns (28 templates)

Newsletter automation
Event promotion sequences
Webinar registration workflows
Content download follow-up
Lead magnet delivery
Product launch campaigns
Seasonal promotions
Behavior-triggered campaigns

⚙️ Operations & Admin (16 templates)

Data enrichment workflows
List cleanup and hygiene
Reporting automation
Team notifications and alerts
Compliance workflows (GDPR consent)
Internal task automation
Data backup and archival
Performance monitoring

🔹 Backend Services

1. TemplateLibraryService

Responsibility: Manage template catalog and browsing

Key Methods:

getTemplates(filters) - Browse templates by category, industry, use case
getTemplateById(id) - Fetch single template with full configuration
searchTemplates(query) - Full-text search across template names and descriptions
getFeaturedTemplates() - Get curated/popular templates for homepage
getTemplatsByCategory(category) - Filter by specific category
trackTemplateView(templateId) - Analytics on template popularity
rateTemplate(templateId, rating) - User rating system (1-5 stars)

2. AutomationTemplateService

Responsibility: Create user automations from templates

Key Methods:

createFromTemplate(templateId, customization) - Instantiate automation from template
previewTemplate(templateId, sampleData) - Preview with user's actual data
validateTemplateConfig(config) - Ensure template is compatible with org setup
customizeTemplate(templateId, overrides) - Apply user-specific customizations
cloneTemplate(automationId) - Save user automation as personal template

3. TemplateRecommendationService (AI-Powered)

Responsibility: Suggest relevant templates based on user behavior

Key Methods:

getRecommendations(organizationId) - ML-based template suggestions
getSimilarTemplates(templateId) - Find related templates
getIndustryTemplates(industry) - Industry-specific recommendations

📡 API Endpoints (12 endpoints)

Method	Endpoint	Description
GET	`/api/automations/templates`	Get all templates with filters (category, industry, popularity)
GET	`/api/automations/templates/:id`	Get single template with full configuration
GET	`/api/automations/templates/featured`	Get featured/popular templates
GET	`/api/automations/templates/categories`	Get all template categories with counts
GET	`/api/automations/templates/search`	Full-text search across templates
POST	`/api/automations/templates/:id/preview`	Preview template with user's sample data
POST	`/api/automations/templates/:id/activate`	Create automation from template (one-click)
POST	`/api/automations/templates/:id/customize`	Create automation with customizations
POST	`/api/automations/templates/:id/rate`	Rate template (1-5 stars)
GET	`/api/automations/templates/:id/similar`	Get similar/related templates
GET	`/api/automations/templates/recommendations`	AI-powered template recommendations
POST	`/api/automations/:id/save-as-template`	Save user automation as personal template

💾 Database Schema

automation_templates

CREATE TABLE automation_templates (
  id BIGSERIAL PRIMARY KEY,

  -- Template info
  name VARCHAR(255) NOT NULL,
  description TEXT,
  category VARCHAR(100) NOT NULL,
  -- 'sales', 'support', 'ecommerce', 'marketing', 'retention', 'operations'

  subcategory VARCHAR(100),
  industry VARCHAR(100), -- 'saas', 'retail', 'healthcare', 'finance', 'general'

  -- Template configuration
  config JSONB NOT NULL,
  -- {
  --   trigger: { type, event, filters },
  --   conditions: [{ field, operator, value }],
  --   actions: [{ type, config, delay }],
  --   settings: { runOnce, maxExecutions }
  -- }

  -- Customization points
  customizable_fields JSONB DEFAULT '[]',
  -- [{ field: 'actions[0].config.emailTemplate', label: 'Welcome Email', type: 'text' }]

  -- Metadata
  is_featured BOOLEAN DEFAULT FALSE,
  is_premium BOOLEAN DEFAULT FALSE,
  difficulty_level VARCHAR(20) DEFAULT 'beginner',
  -- 'beginner', 'intermediate', 'advanced'

  estimated_setup_time INT, -- minutes

  -- Analytics
  usage_count INT DEFAULT 0,
  view_count INT DEFAULT 0,
  rating DECIMAL(3,2) DEFAULT 0,
  rating_count INT DEFAULT 0,

  -- Tags for search
  tags TEXT[], -- ['welcome', 'onboarding', 'email']

  -- Preview
  preview_image_url VARCHAR(500),
  demo_video_url VARCHAR(500),

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

  INDEX idx_templates_category (category),
  INDEX idx_templates_industry (industry),
  INDEX idx_templates_featured (is_featured),
  INDEX idx_templates_rating (rating DESC),
  INDEX idx_templates_usage (usage_count DESC),
  INDEX idx_templates_tags USING GIN (tags)
);

template_categories

CREATE TABLE template_categories (
  id BIGSERIAL PRIMARY KEY,

  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) NOT NULL UNIQUE,
  description TEXT,
  icon VARCHAR(50), -- Icon identifier for UI

  parent_category_id BIGINT REFERENCES template_categories(id),

  display_order INT DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,

  template_count INT DEFAULT 0,

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

  INDEX idx_template_categories_parent (parent_category_id),
  INDEX idx_template_categories_order (display_order)
);

template_ratings

CREATE TABLE template_ratings (
  id BIGSERIAL PRIMARY KEY,
  template_id BIGINT NOT NULL REFERENCES automation_templates(id),
  organization_id BIGINT NOT NULL REFERENCES organizations(id),
  user_id BIGINT NOT NULL REFERENCES users(id),

  rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
  review TEXT,

  created_at TIMESTAMP DEFAULT NOW(),

  UNIQUE INDEX idx_template_ratings_unique (template_id, organization_id),
  INDEX idx_template_ratings_template (template_id)
);

🖼️ UI Screenshots Reference

Automation Templates UI Components

Live Dashboard - Automations Library.png - Main template library view with category filters
Live Dashboard - CRM - Automation Library.png - CRM-specific automation templates
Live Dashboard - Automations Library-1.png - Template cards with descriptions and ratings
Live Dashboard - CRM - Automation Video Preview.png - Template preview modal with video walkthrough
Live Dashboard - CRM - Automation Video Preview-1.png - Template configuration preview
Live Dashboard - CRM - Automation Video Preview-2.png - Template customization options
Live Dashboard - CRM - Automation Video Preview-3.png - One-click activation flow

🔐 GDPR Compliance

Data Protection Considerations

Template Analytics: Usage tracking anonymized at organization level, no PII stored
User Ratings: Associated with organization, not individual users (privacy-first)
Preview Data: Sample data used for previews is never stored, only used in-memory
Custom Templates: User-created templates stored with organization_id, can be deleted on request
Data Retention: Template view history retained for 90 days for recommendations, then aggregated

⚡ FRAME 4: Triggers & Actions

🎯 Purpose

The Triggers & Actions system is the core event-driven automation engine that powers UnifiedBeez workflows. It provides 50+ trigger types (time-based, event-based, condition-based) and 100+ action types across communication, CRM, integrations, and internal operations. The system uses Bull Queue with Redis for reliable asynchronous processing, supports complex conditional logic, and enables sophisticated multi-step workflows with branching paths.

🔔 Trigger Types (50+ triggers)

📅 Time-Based Triggers (12 types)

Schedule: Daily, weekly, monthly at specific time
Delay: X minutes/hours/days after event
Relative Date: X days before/after contact field date
Anniversary: Yearly recurring date (birthday, signup date)
Specific Date: One-time trigger on exact date
Time Window: Trigger between specific hours (9am-5pm)
Timezone-Aware: Respect contact's timezone
Business Hours: Only during business days/hours
Countdown: X days until contract renewal
Inactivity: No action for X days
Recurring Interval: Every X hours/days
Cron Expression: Advanced scheduling with cron syntax

🎯 Event-Based Triggers (22 types)

Contact Created: New contact added
Contact Updated: Specific field changed
Tag Added/Removed: Contact tagged/untagged
List Joined/Left: Added/removed from list
Email Sent/Opened/Clicked: Email engagement
WhatsApp Received: Incoming WhatsApp message
SMS Received: Incoming SMS
Form Submitted: Web form submission
Purchase Made: E-commerce transaction
Cart Abandoned: No checkout within X hours
Conversation Assigned: Inbox conversation assigned to agent
Conversation Closed: Support ticket resolved
Score Changed: Contact score crossed threshold
Lifecycle Stage Changed: Lead → Customer transition
Subscription Status Changed: Subscribed/unsubscribed
API Webhook Received: External system event
File Uploaded: Document attached to contact
Task Created/Completed: CRM task events
Meeting Scheduled: Calendar event created
Payment Received: Invoice paid
Campaign Sent: Bulk campaign delivered
Automation Completed: Another automation finished

🔍 Condition-Based Triggers (16 types)

Field Value Match: Contact field equals/contains value
Score Threshold: Score > X or < X
List Membership: Contact in specific list(s)
Tag Combination: Has all/any/none of specific tags
Engagement Level: High/medium/low engagement
Behavior Pattern: Visited page X times, opened Y emails
RFM Segment: Recency, Frequency, Monetary analysis
Geolocation: Contact in specific country/region
Device Type: Mobile/desktop activity
Channel Preference: Preferred communication channel
Custom Field Logic: Boolean expressions on custom fields
Date Range: Field date within range
Null/Empty Check: Field has no value
Regex Match: Field matches pattern
Mathematical: Field > average, percentile calculations
Sentiment: Message sentiment positive/negative (AI-powered)

⚙️ Action Types (100+ actions)

📧 Communication Actions (18 actions)

Send Email (template or custom)
Send WhatsApp Message
Send SMS
Send Internal Notification
Send Slack Message
Send Push Notification
Schedule Email Send
Cancel Scheduled Message
Send Personalized Video
Send Survey/Poll
Create Email Draft
Forward Email to Team
Reply to Conversation
Close Conversation
Reopen Conversation
Add Conversation Note
Assign Conversation to Agent
Transfer to Another Inbox

👤 CRM Actions (24 actions)

Create Contact
Update Contact Field
Delete Contact
Merge Duplicate Contacts
Add to List
Remove from List
Add Tag
Remove Tag
Update Lifecycle Stage
Update Contact Score (add/subtract points)
Calculate Lead Score
Update Custom Field
Copy Field Value
Append to Field (e.g., notes)
Enrich Contact Data (external API)
Subscribe to Newsletter
Unsubscribe from Communications
Update Consent Preferences
Set Do Not Contact
Create Contact Relationship
Log Activity
Upload Document to Contact
Export Contact Data
Request GDPR Data Deletion

✅ Task & Activity Actions (12 actions)

Create Task
Assign Task to User
Set Task Due Date
Complete Task
Create Calendar Event
Send Task Reminder
Update Task Priority
Add Task Comment
Create Follow-up Sequence
Log Phone Call
Log Meeting Notes
Schedule Meeting

🔗 Integration Actions (16 actions)

Trigger Webhook (HTTP POST)
Call External API (GET/POST/PUT)
Create Shopify Order
Update Stripe Subscription
Add to Google Sheets Row
Create Salesforce Lead
Update HubSpot Contact
Send to Zapier Webhook
Create Jira Ticket
Post to Social Media (Buffer)
Add to Mailchimp List
Update Intercom User
Sync to CRM (Pipedrive, Close)
Create Asana Task
Update Monday.com Board
Send to Make.com (Integromat)

📊 Analytics & Reporting (8 actions)

Track Custom Event
Update Metric/Counter
Generate Report
Send Analytics Summary
Create Dashboard Snapshot
Log Conversion Event
Update A/B Test Results
Export Data to CSV

🔧 Automation Control (14 actions)

Wait/Delay (seconds to months)
Wait Until Condition Met
Wait for Event
Stop This Automation
Trigger Another Automation
Cancel Pending Actions
Branch (If/Else)
Switch (Multiple Conditions)
Loop (Repeat Actions)
Random Path (A/B Testing)
Set Variable
Calculate Value (math expressions)
Send Error Alert to Admin
Retry Failed Action

🎨 AI-Powered Actions (8 actions)

Generate Email with AI (Beezora)
Analyze Sentiment
Classify Intent
Extract Entities (name, date, etc.)
Suggest Next Best Action
Generate Summary
Translate Message
Detect Language

🔹 Backend Services

1. TriggerEngine

Responsibility: Monitor events and match against automation triggers

Key Methods:

registerTrigger(automationId, triggerConfig) - Register new trigger
matchTriggers(event) - Find automations matching event
evaluateTriggerConditions(trigger, context) - Check if conditions met
queueAutomationExecution(automationId, context) - Add to Bull queue
scheduleTimedTrigger(trigger, executeAt) - Schedule future execution
cancelScheduledTrigger(triggerId) - Cancel pending scheduled trigger

2. ActionExecutor

Responsibility: Execute automation actions in sequence

Key Methods:

executeAction(action, context) - Execute single action
executeActionSequence(actions, context) - Execute multiple actions in order
executeParallelActions(actions, context) - Execute actions concurrently
handleActionError(error, action, context) - Retry logic and error handling
validateActionConfig(action) - Validate action configuration before execution

3. EventBus

Responsibility: Centralized event publishing and subscription

Key Methods:

publish(eventType, payload) - Publish event to all subscribers
subscribe(eventType, handler) - Subscribe to specific event type
unsubscribe(eventType, handler) - Unsubscribe from event
getEventHistory(filters) - Retrieve past events for debugging

📡 API Endpoints (18 endpoints)

Method	Endpoint	Description
GET	`/api/automations/triggers`	Get all available trigger types with metadata
GET	`/api/automations/triggers/categories`	Get trigger categories (time, event, condition)
GET	`/api/automations/triggers/:type/schema`	Get JSON schema for specific trigger type
POST	`/api/automations/triggers/validate`	Validate trigger configuration
POST	`/api/automations/triggers/test`	Test trigger with sample data
GET	`/api/automations/actions`	Get all available action types
GET	`/api/automations/actions/categories`	Get action categories (communication, CRM, etc.)
GET	`/api/automations/actions/:type/schema`	Get JSON schema for specific action type
POST	`/api/automations/actions/validate`	Validate action configuration
POST	`/api/automations/actions/test`	Test action with sample data (safe mode)
GET	`/api/automations/events`	Get event history for debugging
POST	`/api/automations/events/publish`	Manually publish custom event
GET	`/api/automations/scheduled`	Get all scheduled automation executions
DELETE	`/api/automations/scheduled/:id`	Cancel scheduled execution
GET	`/api/automations/conditions/operators`	Get available condition operators (equals, contains, etc.)
POST	`/api/automations/conditions/evaluate`	Evaluate condition logic with test data
GET	`/api/automations/fields`	Get available fields for conditions (contact fields, system fields)
POST	`/api/automations/builder/validate`	Validate entire automation workflow

💾 Database Schema

automation_triggers

CREATE TABLE automation_triggers (
  id BIGSERIAL PRIMARY KEY,
  automation_id BIGINT NOT NULL REFERENCES user_automations(id) ON DELETE CASCADE,

  -- Trigger type
  type VARCHAR(100) NOT NULL,
  -- 'contact_created', 'tag_added', 'schedule', 'field_changed', etc.

  -- Trigger configuration
  config JSONB NOT NULL,
  -- {
  --   event: 'contact.created',
  --   filters: { lifecycle_stage: 'lead' },
  --   schedule: '0 9 * * 1-5', // cron
  --   delay: { value: 2, unit: 'days' }
  -- }

  -- Conditions (AND/OR logic)
  conditions JSONB DEFAULT '[]',
  -- [{ field: 'score', operator: '>', value: 50, logic: 'AND' }]

  is_active BOOLEAN DEFAULT TRUE,

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

  INDEX idx_automation_triggers_automation (automation_id),
  INDEX idx_automation_triggers_type (type),
  INDEX idx_automation_triggers_active (is_active)
);

automation_actions

CREATE TABLE automation_actions (
  id BIGSERIAL PRIMARY KEY,
  automation_id BIGINT NOT NULL REFERENCES user_automations(id) ON DELETE CASCADE,

  -- Action type
  type VARCHAR(100) NOT NULL,
  -- 'send_email', 'add_tag', 'update_field', 'wait', 'webhook', etc.

  -- Action configuration
  config JSONB NOT NULL,
  -- {
  --   emailTemplate: 'welcome',
  --   subject: 'Welcome {{first_name}}!',
  --   to: '{{email}}',
  --   from: 'team@unifiedbeez.com'
  -- }

  -- Execution order
  step_order INT NOT NULL,
  parent_step_id BIGINT REFERENCES automation_actions(id),
  -- For branching (if/else), points to parent decision step

  -- Delay before execution
  delay JSONB,
  -- { value: 1, unit: 'hours' }

  -- Conditional execution
  execute_if JSONB,
  -- { field: 'score', operator: '>', value: 80 }

  is_active BOOLEAN DEFAULT TRUE,

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

  INDEX idx_automation_actions_automation (automation_id, step_order),
  INDEX idx_automation_actions_type (type),
  INDEX idx_automation_actions_parent (parent_step_id)
);

automation_conditions

CREATE TABLE automation_conditions (
  id BIGSERIAL PRIMARY KEY,
  automation_id BIGINT REFERENCES user_automations(id) ON DELETE CASCADE,
  action_id BIGINT REFERENCES automation_actions(id) ON DELETE CASCADE,

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

  -- Logical operators
  logic VARCHAR(10) DEFAULT 'AND', -- 'AND', 'OR', 'NOT'
  group_id INT, -- For grouping conditions (parentheses)

  created_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_automation_conditions_automation (automation_id),
  INDEX idx_automation_conditions_action (action_id)
);

⚙️ Bull Queue + Redis Implementation

Queue Architecture

Queue Name: automation-execution-queue

Redis Configuration:

Host: ElastiCache Redis Cluster Mode (Multi-AZ)
Concurrency: 10 concurrent jobs per worker
Retry Strategy: Exponential backoff (3 attempts: 1m, 5m, 15m)
Job Timeout: 5 minutes per action
Priority Levels: High (urgent), Normal (default), Low (batch)

Job Structure:

{
  "jobId": "auto_exec_12345",
  "automationId": 567,
  "triggerId": 890,
  "context": {
    "contactId": 123,
    "organizationId": 45,
    "triggerEvent": { "type": "contact.created", "data": {...} }
  },
  "actions": [
    { "type": "send_email", "config": {...}, "delay": null },
    { "type": "wait", "config": { "value": 1, "unit": "hours" } },
    { "type": "add_tag", "config": { "tagId": 99 } }
  ],
  "currentStep": 0,
  "priority": "normal",
  "attempts": 0,
  "maxAttempts": 3
}

Queue Monitoring:

Bull Dashboard: Real-time queue monitoring UI
Metrics Tracked: Queue depth, processing rate, failed jobs, average duration
Alerts: Queue depth > 1000, failed job rate > 5%, processing time > 10 minutes

🖼️ UI Screenshots Reference

Triggers & Actions UI Components

Live Dashboard - Automations Library.png - Pre-built trigger and action combinations
Live Dashboard - CRM - Automation Video Preview.png - Visual workflow builder showing triggers and actions

Note: Main automation builder UI is part of the visual canvas, showing drag-and-drop trigger and action configuration

🔐 GDPR Compliance

Data Protection in Automation Processing

Execution Logs: Redact PII in execution logs (email addresses masked as e***@domain.com)
Data Retention: Execution history retained for 90 days, then summarized (no contact data)
Right to Erasure: When contact deleted, cancel all pending automation jobs for that contact
Consent Tracking: Automations check consent before communication actions (email, SMS, WhatsApp)
Audit Trail: All automation actions logged with timestamp, user, and contact ID for compliance audits
Data Minimization: Only pass necessary contact fields to action executors, not full contact object

🤖 FRAME 5: Beezora AI Integration

🎯 Purpose

Beezora is UnifiedBeez's AI-powered assistant that helps users create, optimize, and manage automations through natural language. Powered by OpenAI GPT-4, Beezora understands user intent, suggests automation workflows, generates email content, optimizes performance, and provides intelligent recommendations. Users can simply describe what they want to automate (e.g., "Send a welcome email to new VIP customers"), and Beezora builds the complete automation workflow automatically.

✨ Core AI Features

💬 Natural Language Automation Creation

Describe automation in plain English
AI interprets intent and builds workflow
Suggests triggers, conditions, and actions
Handles complex multi-step sequences
Supports follow-up questions for refinement
Conversational interface (chat-style)

Example:

"When someone abandons their cart, wait 2 hours, then send them a 10% discount code via email and WhatsApp"

→ Beezora creates: Trigger (cart abandoned) + Delay (2 hours) + Email action + WhatsApp action

💡 Smart Suggestions

Analyze existing automations and suggest improvements
Recommend missing workflows based on industry best practices
Suggest A/B test variations
Identify automation gaps (e.g., "No re-engagement workflow detected")
Recommend optimal send times based on engagement data
Suggest personalization opportunities

📈 Performance Optimization

Analyze automation performance metrics
Suggest changes to improve conversion rates
Recommend better timing/delays
Identify underperforming actions
A/B test recommendation based on results
Predict automation effectiveness before launch

✍️ AI Content Generation

Generate email subject lines
Write email body copy
Create WhatsApp message templates
Generate SMS copy (160 char optimized)
Personalization suggestions
Tone adjustment (professional, casual, friendly)
Multi-language support (translate content)

🔍 Intent Recognition

Detect user goals from description
Map to appropriate automation templates
Identify trigger types from context
Suggest relevant actions
Handle ambiguous requests with clarifying questions

🎓 Automation Learning

Learn from successful automations in your org
Adapt suggestions to your industry
Personalize recommendations over time
Track what works best for your audience
Continuous improvement based on feedback

🔹 Backend Services

1. BeezoraAIService

Responsibility: Core AI orchestration and OpenAI integration

Key Methods:

processNaturalLanguageRequest(prompt, context) - Parse user request and generate automation
generateAutomationFromIntent(intent, parameters) - Build automation config from parsed intent
refineAutomation(automationId, refinementRequest) - Iteratively improve automation
explainAutomation(automationConfig) - Generate human-readable explanation
validateAIGeneratedConfig(config) - Ensure AI output is valid and safe

2. OpenAI GPT-4 Integration

Model: gpt-4-turbo (128k context window)

Configuration:

Temperature: 0.3 (more deterministic for config generation)
Max Tokens: 4000 (enough for complex automations)
System Prompt: UnifiedBeez automation expert persona with knowledge of all triggers/actions
Function Calling: Structured output for automation configs (JSON schema enforcement)
Context Injection: User's org data, existing automations, contact fields, available integrations

3. AutomationOptimizer

Responsibility: Analyze and improve automation performance

Key Methods:

analyzeAutomationPerformance(automationId) - Deep analysis of metrics
suggestOptimizations(automationId) - AI-powered improvement recommendations
predictPerformance(automationConfig) - Estimate likely success rate before launch
generateABTestVariants(automationId) - Create test variations
analyzeABTestResults(testId) - Determine winning variation with statistical significance

4. ContentGeneratorService

Responsibility: AI-powered content creation for messages

Key Methods:

generateEmailSubject(context, tone) - Create compelling subject lines
generateEmailBody(prompt, personalization) - Write full email copy
generateWhatsAppMessage(context) - Create WhatsApp templates
generateSMSCopy(context, maxLength) - Optimize for 160 characters
improveExistingCopy(originalCopy, improvements) - Refine user-written content
translateContent(content, targetLanguage) - Multi-language support

📡 API Endpoints (14 endpoints)

Method	Endpoint	Description
POST	`/api/beezora/chat`	Send message to Beezora (conversational interface)
POST	`/api/beezora/suggest-automation`	Generate automation from natural language description
POST	`/api/beezora/explain-automation/:id`	Get plain-English explanation of automation
POST	`/api/beezora/refine-automation/:id`	Iteratively improve automation with AI
POST	`/api/beezora/optimize/:automationId`	Analyze and suggest performance improvements
POST	`/api/beezora/predict-performance`	Predict automation success rate
GET	`/api/beezora/recommendations`	Get personalized automation recommendations
POST	`/api/beezora/generate-content/email-subject`	Generate email subject line
POST	`/api/beezora/generate-content/email-body`	Generate email body copy
POST	`/api/beezora/generate-content/whatsapp`	Generate WhatsApp message
POST	`/api/beezora/generate-content/sms`	Generate SMS copy (160 char optimized)
POST	`/api/beezora/improve-copy`	Improve existing message copy
POST	`/api/beezora/translate`	Translate content to target language
GET	`/api/beezora/conversation-history`	Get past Beezora chat history

💾 Database Schema

beezora_suggestions

CREATE TABLE beezora_suggestions (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),
  user_id BIGINT REFERENCES users(id),

  -- User request
  user_prompt TEXT NOT NULL,
  -- "Send welcome email to new VIP customers"

  -- AI response
  ai_response JSONB NOT NULL,
  -- {
  --   explanation: "I'll create an automation that...",
  --   automationConfig: { trigger: {...}, actions: [...] },
  --   confidence: 0.95
  -- }

  -- Generated automation (if accepted)
  automation_id BIGINT REFERENCES user_automations(id),
  was_accepted BOOLEAN DEFAULT FALSE,

  -- Context provided to AI
  context JSONB DEFAULT '{}',
  -- { availableTriggers, availableActions, contactFields, existingAutomations }

  -- AI model metadata
  model VARCHAR(100), -- 'gpt-4-turbo'
  tokens_used INT,
  processing_time_ms INT,

  created_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_beezora_suggestions_org (organization_id),
  INDEX idx_beezora_suggestions_user (user_id),
  INDEX idx_beezora_suggestions_accepted (was_accepted),
  INDEX idx_beezora_suggestions_created (created_at DESC)
);

ai_optimization_logs

CREATE TABLE ai_optimization_logs (
  id BIGSERIAL PRIMARY KEY,
  automation_id BIGINT NOT NULL REFERENCES user_automations(id),
  organization_id BIGINT NOT NULL REFERENCES organizations(id),

  -- Optimization type
  optimization_type VARCHAR(100),
  -- 'performance_analysis', 'ab_test_suggestion', 'content_improvement'

  -- AI analysis
  analysis JSONB NOT NULL,
  -- {
  --   currentMetrics: { openRate: 0.22, clickRate: 0.05 },
  --   issues: ["Low open rate", "Suboptimal send time"],
  --   suggestions: [
  --     { type: "subject_line", current: "...", suggested: "...", expectedLift: 0.15 },
  --     { type: "send_time", current: "10am", suggested: "2pm", expectedLift: 0.08 }
  --   ]
  -- }

  -- Implementation status
  was_implemented BOOLEAN DEFAULT FALSE,
  implemented_at TIMESTAMP,

  -- Results tracking (if implemented)
  results JSONB,
  -- { actualLift: 0.12, metricImproved: 'openRate' }

  created_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_ai_optimization_automation (automation_id),
  INDEX idx_ai_optimization_org (organization_id),
  INDEX idx_ai_optimization_type (optimization_type),
  INDEX idx_ai_optimization_implemented (was_implemented)
);

beezora_conversations

CREATE TABLE beezora_conversations (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id),
  user_id BIGINT NOT NULL REFERENCES users(id),

  -- Conversation metadata
  title VARCHAR(500), -- Auto-generated from first message
  context_type VARCHAR(100), -- 'automation_creation', 'optimization', 'general_help'

  -- Message history
  messages JSONB DEFAULT '[]',
  -- [
  --   { role: 'user', content: 'Create welcome automation', timestamp: '...' },
  --   { role: 'assistant', content: 'I can help with that...', timestamp: '...' }
  -- ]

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

  -- Status
  status VARCHAR(50) DEFAULT 'active',
  -- 'active', 'completed', 'abandoned'

  last_message_at TIMESTAMP DEFAULT NOW(),
  created_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_beezora_conversations_org (organization_id),
  INDEX idx_beezora_conversations_user (user_id),
  INDEX idx_beezora_conversations_status (status),
  INDEX idx_beezora_conversations_last_message (last_message_at DESC)
);

🔧 OpenAI API Integration Details

API Configuration

API Key Storage: AWS Secrets Manager (rotated every 90 days)
Rate Limiting: 100 requests/minute per organization, 1000/minute global
Timeout: 30 seconds per request
Retry Logic: 3 attempts with exponential backoff (1s, 2s, 4s)
Fallback: If OpenAI unavailable, show cached suggestions from similar past requests
Cost Tracking: Log tokens used per request, alert if daily budget exceeded
Caching: Cache identical requests for 24 hours to reduce API calls

System Prompt Template

You are Beezora, an AI assistant for UnifiedBeez automation platform.

Your role:
- Help users create marketing automations using natural language
- Suggest optimizations for existing automations
- Generate email/WhatsApp/SMS content
- Explain complex automation workflows in simple terms

Available triggers: [contact_created, tag_added, email_opened, ...]
Available actions: [send_email, add_tag, update_field, wait, ...]

Organization context:
- Industry: {{organization.industry}}
- Contact fields: {{organization.contactFields}}
- Existing automations: {{organization.automationCount}}
- Integrations: {{organization.activeIntegrations}}

Guidelines:
- Always suggest practical, proven automation patterns
- Explain your reasoning
- Ask clarifying questions if request is ambiguous
- Generate valid JSON configs that match our schema
- Be concise but helpful

Response format: JSON with 'explanation' and 'automationConfig' fields

🖼️ UI Screenshots Reference

Beezora AI Interface

Live Dashboard - Automations Library.png - Beezora chat widget for natural language automation creation
Live Dashboard - CRM - Automation Video Preview.png - AI-suggested automation templates

Note: Main Beezora interface shows chat-style interaction with "Create anything with a simple sentence" input field

🔐 GDPR Compliance

AI Data Privacy

Data Sent to OpenAI: Only automation configs, field names, and anonymized metrics (NO contact PII)
Data Retention: OpenAI API set to zero retention (data not used for training)
Conversation History: Stored encrypted, user can delete anytime
Generated Content: User owns all AI-generated content, no OpenAI claims
Audit Logging: All AI requests logged with user_id, timestamp, and prompt (for compliance audits)
Opt-Out: Users can disable AI features entirely in settings
Transparency: Clear labeling of AI-generated content vs user-created

📊 FRAME 6: Execution & Analytics

🎯 Purpose

The Execution & Analytics system provides real-time monitoring, comprehensive metrics, and performance insights for all automation workflows. It tracks every execution from trigger to completion, logs detailed action results, captures errors with full context, and generates actionable analytics. This system enables users to understand automation performance, identify bottlenecks, optimize conversion rates, and ensure reliable execution at scale using Bull Queue for asynchronous processing.

📈 Core Analytics Features

⚡ Real-Time Execution Tracking

Live execution status dashboard
Active automation count
Pending/running/completed jobs
Queue depth monitoring
Processing rate (executions/minute)
Average execution time
Current bottlenecks

✅ Success & Failure Metrics

Overall success rate (percentage)
Failed executions with error details
Action-level success breakdown
Most common failure reasons
Error categorization (transient vs permanent)
Retry success rate
Time-to-resolution for errors

📊 Performance Analytics

Execution time distribution (p50, p95, p99)
Action execution duration
Queue wait time
Throughput trends over time
Resource usage (CPU, memory)
API call latency
Database query performance

🎯 Conversion & Engagement Metrics

Email open rates
Click-through rates
WhatsApp delivery/read rates
SMS delivery rates
Goal completion tracking
Revenue attribution
Conversion funnel analysis

📉 Trend Analysis

Performance over time (hourly, daily, weekly)
Seasonal patterns
Day-of-week analysis
Time-of-day optimization
Cohort analysis
Comparative analysis (automation A vs B)

🔍 Advanced Analytics

Contact journey visualization
Drop-off analysis
A/B test results
Attribution modeling
Predicted performance (ML)
Anomaly detection

🔹 Backend Services

1. ExecutionEngine

Responsibility: Process automation executions from Bull Queue

Key Methods:

processExecution(job) - Main execution handler (Bull Queue consumer)
executeAutomationStep(step, context) - Execute single action
handleConditionalBranching(condition, context) - Evaluate if/else logic
processDelayedAction(action, delay) - Schedule future action
handleExecutionError(error, execution) - Error recovery and retry
finalizeExecution(executionId, status) - Mark execution complete and log results

2. AnalyticsService

Responsibility: Aggregate and analyze automation performance data

Key Methods:

getAutomationMetrics(automationId, dateRange) - Comprehensive metrics for single automation
getOrganizationMetrics(organizationId, dateRange) - Org-wide automation performance
calculateSuccessRate(automationId) - Success percentage over time
getExecutionTimeTrends(automationId) - Performance trends (latency, throughput)
getFailureAnalysis(automationId) - Error categorization and common failure modes
getConversionFunnel(automationId) - Step-by-step conversion rates
compareAutomations(automationIds) - Side-by-side comparison
exportAnalytics(automationId, format) - Export to CSV/PDF

3. PerformanceMonitor

Responsibility: Real-time performance monitoring and alerting

Key Methods:

getQueueMetrics() - Queue depth, processing rate, wait time
getActiveExecutions() - Currently running automations
getPendingExecutions() - Queued automations waiting to run
getExecutionRate() - Executions per minute/hour
checkPerformanceThresholds() - Alert if metrics exceed limits
getBottlenecks() - Identify slow actions or congested queues
getResourceUsage() - CPU, memory, network utilization

4. ExecutionLogService

Responsibility: Store and retrieve detailed execution logs

Key Methods:

logExecution(executionId, status, details) - Record execution result
logActionResult(executionId, actionId, result) - Record individual action outcome
getExecutionHistory(automationId, pagination) - Retrieve past executions
getExecutionDetails(executionId) - Full execution log with all steps
searchExecutionLogs(query, filters) - Search logs by contact, status, date
purgeOldLogs(retentionDays) - Cleanup logs older than retention period

📡 API Endpoints (22 endpoints)

Method	Endpoint	Description
GET	`/api/automations/:id/executions`	Get execution history for automation (paginated)
GET	`/api/automations/:id/executions/:executionId`	Get single execution with full step-by-step log
GET	`/api/automations/:id/analytics`	Get comprehensive analytics for automation
GET	`/api/automations/:id/analytics/success-rate`	Get success rate over time (daily breakdown)
GET	`/api/automations/:id/analytics/performance`	Get execution time metrics (p50, p95, p99)
GET	`/api/automations/:id/analytics/errors`	Get error analysis and failure breakdown
GET	`/api/automations/:id/analytics/funnel`	Get conversion funnel (step-by-step drop-off)
GET	`/api/automations/:id/analytics/trends`	Get trend data (hourly/daily/weekly)
GET	`/api/automations/analytics/compare`	Compare multiple automations side-by-side
GET	`/api/automations/analytics/overview`	Org-wide automation analytics dashboard
GET	`/api/automations/executions/active`	Get currently running executions
GET	`/api/automations/executions/pending`	Get queued executions waiting to run
GET	`/api/automations/executions/failed`	Get recent failed executions
POST	`/api/automations/executions/:id/retry`	Manually retry failed execution
POST	`/api/automations/executions/:id/cancel`	Cancel pending/running execution
GET	`/api/automations/queue/metrics`	Get Bull Queue metrics (depth, rate, wait time)
GET	`/api/automations/performance/bottlenecks`	Identify slow actions or performance issues
GET	`/api/automations/:id/analytics/export`	Export analytics to CSV/PDF
GET	`/api/automations/:id/executions/search`	Search execution logs by contact, status, date
GET	`/api/contacts/:id/automation-history`	Get all automation executions for specific contact
GET	`/api/automations/analytics/anomalies`	Detect performance anomalies (ML-powered)
POST	`/api/automations/analytics/custom-report`	Generate custom analytics report with filters

💾 Database Schema

automation_executions

CREATE TABLE automation_executions (
  id BIGSERIAL PRIMARY KEY,
  automation_id BIGINT NOT NULL REFERENCES user_automations(id),
  organization_id BIGINT NOT NULL REFERENCES organizations(id),

  -- Trigger context
  trigger_event JSONB,
  -- { type: 'contact.created', data: { contactId: 123 }, timestamp: '...' }

  -- Contact context
  contact_id BIGINT REFERENCES contacts(id),

  -- Execution status
  status VARCHAR(50) DEFAULT 'pending',
  -- 'pending', 'queued', 'running', 'completed', 'failed', 'cancelled'

  -- Progress tracking
  total_actions INT,
  completed_actions INT DEFAULT 0,
  current_step INT DEFAULT 0,

  -- Results
  success_count INT DEFAULT 0,
  failure_count INT DEFAULT 0,
  skipped_count INT DEFAULT 0,

  -- Error details
  error_message TEXT,
  error_details JSONB,
  -- { errorCode, stackTrace, failedAction, retriesAttempted }

  -- Performance metrics
  started_at TIMESTAMP,
  completed_at TIMESTAMP,
  duration_ms INT,
  queue_wait_time_ms INT,

  -- Execution log (detailed step-by-step)
  execution_log JSONB DEFAULT '[]',
  -- [
  --   { step: 1, action: 'send_email', status: 'success', duration_ms: 245, result: {...} },
  --   { step: 2, action: 'wait', status: 'pending', scheduledFor: '...' }
  -- ]

  -- Retry tracking
  retry_count INT DEFAULT 0,
  max_retries INT DEFAULT 3,
  parent_execution_id BIGINT REFERENCES automation_executions(id),
  -- If this is a retry, points to original execution

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

  INDEX idx_executions_automation (automation_id, created_at DESC),
  INDEX idx_executions_org (organization_id),
  INDEX idx_executions_contact (contact_id),
  INDEX idx_executions_status (status),
  INDEX idx_executions_started (started_at),
  INDEX idx_executions_completed (completed_at)
);

execution_logs

CREATE TABLE execution_logs (
  id BIGSERIAL PRIMARY KEY,
  execution_id BIGINT NOT NULL REFERENCES automation_executions(id) ON DELETE CASCADE,
  automation_id BIGINT NOT NULL REFERENCES user_automations(id),

  -- Step details
  step_number INT NOT NULL,
  action_type VARCHAR(100) NOT NULL,
  action_config JSONB,

  -- Execution result
  status VARCHAR(50) NOT NULL,
  -- 'success', 'failed', 'skipped', 'pending'

  result JSONB,
  -- Action-specific result data (e.g., email sent, message ID)

  error_message TEXT,
  error_code VARCHAR(100),

  -- Performance
  started_at TIMESTAMP,
  completed_at TIMESTAMP,
  duration_ms INT,

  created_at TIMESTAMP DEFAULT NOW(),

  INDEX idx_execution_logs_execution (execution_id, step_number),
  INDEX idx_execution_logs_automation (automation_id),
  INDEX idx_execution_logs_status (status)
);

automation_metrics

CREATE TABLE automation_metrics (
  id BIGSERIAL PRIMARY KEY,
  automation_id BIGINT NOT NULL REFERENCES user_automations(id),
  organization_id BIGINT NOT NULL REFERENCES organizations(id),

  -- Time period
  date DATE NOT NULL,
  hour INT, -- NULL for daily aggregates, 0-23 for hourly

  -- Execution counts
  total_executions INT DEFAULT 0,
  successful_executions INT DEFAULT 0,
  failed_executions INT DEFAULT 0,
  cancelled_executions INT DEFAULT 0,

  -- Performance metrics
  avg_duration_ms INT,
  p50_duration_ms INT,
  p95_duration_ms INT,
  p99_duration_ms INT,

  avg_queue_wait_ms INT,

  -- Action-level metrics
  action_success_counts JSONB,
  -- { 'send_email': { success: 45, failed: 2 }, 'add_tag': { success: 47 } }

  action_avg_duration JSONB,
  -- { 'send_email': 234, 'add_tag': 12 }

  -- Error breakdown
  error_types JSONB,
  -- { 'EmailDeliveryError': 3, 'TimeoutError': 1 }

  -- Engagement metrics (for communication actions)
  emails_sent INT DEFAULT 0,
  emails_opened INT DEFAULT 0,
  emails_clicked INT DEFAULT 0,
  whatsapp_sent INT DEFAULT 0,
  whatsapp_delivered INT DEFAULT 0,
  sms_sent INT DEFAULT 0,
  sms_delivered INT DEFAULT 0,

  -- Conversion tracking
  goals_completed INT DEFAULT 0,
  revenue_generated DECIMAL(12,2) DEFAULT 0,

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

  UNIQUE INDEX idx_metrics_automation_date_hour (automation_id, date, hour),
  INDEX idx_metrics_automation_date (automation_id, date DESC),
  INDEX idx_metrics_org (organization_id)
);

⚙️ Bull Queue Processing Details

Queue Configuration

Queue Name: automation-execution-queue

Redis Backend: ElastiCache Redis Cluster Mode (Multi-AZ for high availability)

Processing Parameters

Concurrency: 10 concurrent jobs per worker instance
Worker Scaling: Auto-scale from 2 to 10 workers based on queue depth
Job Timeout: 5 minutes per execution (configurable per automation)
Retry Strategy:
- Attempt 1: Immediate
- Attempt 2: After 1 minute (exponential backoff)
- Attempt 3: After 5 minutes
- Attempt 4: After 15 minutes (final attempt)
Priority Levels:
- Critical (1): User-triggered test executions, manual retries
- High (5): Time-sensitive triggers (abandoned cart within 1 hour)
- Normal (10): Standard event-based automations
- Low (15): Batch operations, scheduled daily reports
Delayed Jobs: Support for scheduling executions up to 1 year in advance

Queue Monitoring

Bull Dashboard: Web UI for real-time queue visualization
Metrics Tracked:
- Queue depth (pending jobs)
- Processing rate (jobs/minute)
- Failed job count
- Average wait time
- Average processing time
- Worker utilization
Alerts:
- Queue depth > 1000 jobs (scale up workers)
- Failed job rate > 5% (investigate errors)
- Average wait time > 5 minutes (capacity issue)
- Processing time > 10 minutes (timeout risk)

Error Handling

Transient Errors: Automatically retried (network timeouts, rate limits)
Permanent Errors: Failed immediately (invalid config, missing required fields)
Dead Letter Queue: Failed jobs after max retries moved to DLQ for manual review
Error Categorization: Automatic classification for analytics (EmailError, WebhookError, etc.)

🖼️ UI Screenshots Reference

Analytics & Execution UI

Analytics dashboards show execution history, success rates, performance charts, and error logs. Real-time monitoring displays active executions and queue status.

🔐 GDPR Compliance

Data Protection in Execution Logs

PII Redaction: Contact email/phone masked in logs (e***@example.com, +234****6789)
Data Retention: Execution logs retained for 90 days, then aggregated into metrics (no contact data)
Right to Access: Provide full execution history for specific contact on request
Right to Erasure: When contact deleted, purge all execution logs mentioning that contact
Audit Trail: All executions logged with timestamp, automation ID, and anonymized contact reference
Data Minimization: Logs contain only necessary fields for debugging (no full contact objects)
Encryption: Execution logs encrypted at rest in PostgreSQL, encrypted in transit via TLS 1.3

UnifiedBeez Backend Architecture

⚡ FRAME 1: Automations Module Overview

🎯 Purpose

✨ Core Automation Features

🔧 Automation Builder

📚 Template Library

🤖 Beezora AI Assistant

🔄 Automation Types

📊 Automation Workflow Example

Example: New Contact Onboarding Automation

🎯 Popular Automation Templates

Sales & Lead Generation

Support & Engagement

Retention & Nurture

Operations & Admin

🔗 See Also

🔧 FRAME 2: Automation Engine Architecture

🏗️ Engine Overview

📊 Architecture Diagram

Automation Engine Architecture

Event Source

⚙️ AutomationService

🔄 Bull Queue (Redis)

⚡ AutomationWorkerService

EmailAction Service

WhatsAppAction Service

WebhookAction Service

📊 AutomationLogService

🔹 Core Services

1. AutomationService

2. AutomationWorkerService

3. ActionExecutorServices (20+ action types)

📡 API Endpoints (25 endpoints)

💾 Database Schema

automation_templates

user_automations

automation_executions

🔗 See Also

📚 FRAME 3: Templates Library

🎯 Purpose

📊 Template Categories Overview

🎯 Sales & Lead Generation (45 templates)

💬 Support & Escalation (38 templates)

🛍️ E-commerce (32 templates)

🎉 Retention & Nurture (41 templates)

📧 Marketing Campaigns (28 templates)

⚙️ Operations & Admin (16 templates)

🔹 Backend Services

1. TemplateLibraryService

2. AutomationTemplateService

3. TemplateRecommendationService (AI-Powered)

📡 API Endpoints (12 endpoints)

💾 Database Schema

automation_templates

template_categories

template_ratings

🖼️ UI Screenshots Reference

Automation Templates UI Components

🔐 GDPR Compliance

Data Protection Considerations

🔗 See Also

⚡ FRAME 4: Triggers & Actions

🎯 Purpose

🔔 Trigger Types (50+ triggers)

📅 Time-Based Triggers (12 types)

🎯 Event-Based Triggers (22 types)

🔍 Condition-Based Triggers (16 types)

⚙️ Action Types (100+ actions)

📧 Communication Actions (18 actions)

👤 CRM Actions (24 actions)

✅ Task & Activity Actions (12 actions)

🔗 Integration Actions (16 actions)

📊 Analytics & Reporting (8 actions)

🔧 Automation Control (14 actions)

🎨 AI-Powered Actions (8 actions)

🔹 Backend Services

1. TriggerEngine

2. ActionExecutor

3. EventBus

📡 API Endpoints (18 endpoints)