Automation Library + Builders Architecture

FRAME 1: AUTOMATION SYSTEM OVERVIEW

Core Automation Engine Architecture - Foundation for All 6 Builders

User Actions

Backend Services

AWS Infrastructure

Data Storage

GDPR Compliance

👤 SWIM LANE 1: USER ACTIONS - Automation Creation Workflow

1. Create Automation

Select builder type (Email, Form, Campaign, etc.)
Choose template or start from scratch
Name automation
Set automation goal

2. Define Triggers

Select trigger type (50+ options)
Configure trigger conditions
Set trigger frequency
Define trigger filters

3. Build Workflow

Add actions (email, SMS, WhatsApp)
Configure wait states
Add conditional logic (if/then)
Set up branching paths

4. Test & Activate

Preview automation flow
Test with sample data
Review analytics setup
Activate automation

↓

⚙️ SWIM LANE 2: CORE AUTOMATION BACKEND SERVICES

⚡ Automation Engine Service

API Endpoints:

• POST /automations/create
• GET /automations/:id
• PUT /automations/:id
• POST /automations/:id/activate
• POST /automations/:id/deactivate
• DELETE /automations/:id

Features:

• Workflow orchestration
• State management
• Version control
• Template management

🎯 Trigger Evaluation Service

API Endpoints:

• POST /triggers/evaluate
• GET /triggers/types
• POST /triggers/:id/test

Features:

• 50+ trigger types
• Real-time event processing
• Scheduled triggers (cron)
• Webhook triggers

🧠 Condition Logic Engine

Features:

• Smart Rules evaluation
• Boolean logic (AND/OR/NOT)
• Nested conditions
• Dynamic field comparison

Operators:

• Equals, Not Equals, Contains
• Greater than, Less than
• Regex pattern matching
• Date/time comparisons

🚀 Action Executor Service

API Endpoints:

• POST /actions/execute
• GET /actions/types
• GET /actions/:id/status

Action Types:

• Send email, SMS, WhatsApp
• Update contact fields
• Add/remove tags
• Create tasks
• Trigger webhooks
• Wait/delay actions

📊 Analytics Service

API Endpoints:

• GET /automations/:id/analytics
• GET /automations/:id/runs
• GET /automations/:id/performance

Metrics Tracked:

• Execution count
• Success/failure rate
• Average execution time
• Conversion rates
• Revenue attribution

📋 Template Library Service

API Endpoints:

• GET /templates
• GET /templates/:id
• POST /templates/:id/use
• POST /templates/save-custom

Features:

• 200+ preset templates
• Category filtering
• Industry-specific templates
• Custom template saving

↓

☁️ SWIM LANE 3: AWS INFRASTRUCTURE FOR AUTOMATION SYSTEM

ECS Fargate

Services:
• Automation Engine
• Trigger Evaluator
• Action Executor

Configuration:
• Tasks: 3-15 (auto-scaling)
• CPU: 4 vCPU
• RAM: 8GB
• Port: 3000

RDS PostgreSQL

Configuration:
• Multi-AZ
• db.t4g.medium
• 100GB storage

Tables:
• automations
• automation_runs
• automation_actions
• automation_triggers

Port: 5432

SQS Queues

Queues:
• automation-execution-queue
• automation-email-queue
• automation-sms-queue
• automation-webhook-queue

Purpose:
• Async action processing
• Visibility timeout: 5 min
• DLQ for failed actions

ElastiCache Redis

Purpose:
• Bull Queue backend
• Automation state cache
• Rate limiting
• Session locking

Type:
• cache.t4g.micro
• Multi-AZ (Primary + Replica)

Port: 6379

Lambda Functions

Functions:
• automation-scheduler
• automation-cleanup
• automation-webhook-handler

Triggers:
• EventBridge (cron)
• API Gateway
• Timeout: 5 minutes

EventBridge

Purpose:
• Scheduled automation triggers
• Time-based workflows

Rules:
• Daily digest automations
• Weekly reports
• Monthly retention campaigns
• Custom cron expressions

CloudWatch

Log Groups:
• /aws/ecs/automation-engine
• /aws/lambda/automation-scheduler

Metrics:
• Automation execution count
• Success/failure rate
• Average execution time

Retention: 30 days

Secrets Manager

Stored:
• OpenAI API key
• Twilio credentials
• Webhook secrets
• Email service credentials

Encryption:
• KMS encrypted
• Auto-rotation enabled

↓

🗄️ SWIM LANE 4: CORE AUTOMATION DATABASE SCHEMA (PostgreSQL)

TABLE: automations

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Automation identifier

organizationId

INTEGER

FK → organizations(id)

Organization owner

name

VARCHAR(255)

NOT NULL

Automation name

type

VARCHAR(50)

NOT NULL

status

VARCHAR(50)

DEFAULT 'draft'

draft|active|paused|archived

configuration

JSONB

NOT NULL

Automation workflow definition (triggers, actions, conditions)

templateId

INTEGER

FK → automation_templates(id)

Source template (if created from template)

createdBy

INTEGER

FK → users(id)

Creator user

activatedAt

TIMESTAMP

NULLABLE

When automation was activated

createdAt

TIMESTAMP

DEFAULT NOW()

Creation timestamp

updatedAt

TIMESTAMP

DEFAULT NOW()

Last update timestamp

TABLE: automation_runs

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Run identifier

automationId

INTEGER

FK → automations(id)

Automation being executed

contactId

INTEGER

FK → contacts(id)

Target contact

status

VARCHAR(50)

NOT NULL

pending|running|completed|failed

startedAt

TIMESTAMP

NOT NULL

Execution start time

completedAt

TIMESTAMP

NULLABLE

Execution completion time

executionLog

JSONB

NULLABLE

Step-by-step execution details

errorMessage

TEXT

NULLABLE

Error details (if failed)

TABLE: automation_triggers

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Trigger identifier

automationId

INTEGER

FK → automations(id)

Associated automation

triggerType

VARCHAR(100)

NOT NULL

contact_created|tag_added|form_submitted|etc.

conditions

JSONB

NOT NULL

Trigger filter conditions

schedule

VARCHAR(255)

NULLABLE

Cron expression (for scheduled triggers)

TABLE: automation_actions

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Action identifier

automationId

INTEGER

FK → automations(id)

Associated automation

actionType

VARCHAR(100)

NOT NULL

send_email|send_sms|add_tag|create_task|etc.

stepOrder

INTEGER

NOT NULL

Execution sequence

configuration

JSONB

NOT NULL

Action-specific config (template, delay, etc.)

parentActionId

INTEGER

FK → automation_actions(id)

Parent action (for branching)

conditions

JSONB

NULLABLE

Conditional execution rules

↓

📝 Navigation Guide

This Overview Frame establishes the foundation architecture shared by all 6 builders:

Frame 2-4: Email Builder (Component System, Content Management, Delivery)
Frame 5-6: Form Builder (Schema Builder, Integration)
Frame 7-10: Campaign Automation Builder (Workflow Engine, Multi-Channel, Templates, Analytics)
Frame 11-12: Retention & Nurture Builder (Workflow, Execution)
Frame 13-16: Sales & Lead Generation Builder (Lead Capture, CRM Tagging, Consent, Email Config)
Frame 17-18: Support & Escalation Builder (FAQ Management, Channel Integration)

FRAME 2: EMAIL BUILDER - COMPONENT ARCHITECTURE

13 Email Component Types | Template System | Media Management

User Actions

Backend Services

AWS Infrastructure

Data Storage

GDPR Compliance

👤 SWIM LANE 1: EMAIL BUILDER USER ACTIONS

1. Create Email Template

Choose layout (4 layout types)
Select email type (Basic, E-commerce, Socials)
Name email template
Set subject line

2. Add Components

Drag/drop 13 component types
Button, Header, Image, Video, GIF
Paragraph, Link, Divider, Spacer
Social icons, Stickers, HTML, Icon

3. Configure Components

Edit content (text, links, media)
Adjust layout (alignment, padding)
Style customization (colors, fonts)
Add dynamic merge fields

4. Preview & Save

Desktop/mobile preview
Test with sample data
Save as template
Publish to automation

↓

⚙️ SWIM LANE 2: EMAIL BUILDER BACKEND SERVICES

📧 Email Template Service

API Endpoints:

• POST /email-templates/create
• GET /email-templates/:id
• PUT /email-templates/:id
• DELETE /email-templates/:id
• GET /email-templates/list
• POST /email-templates/:id/duplicate
• POST /email-templates/:id/preview

Features:

• Template CRUD operations
• Version control
• Template categorization
• Search & filtering

🧩 Email Component Service

API Endpoints:

• POST /email-components/add
• PUT /email-components/:id
• DELETE /email-components/:id
• POST /email-components/reorder
• GET /email-components/types

Component Types (13):

• Button, Header, Image, Video
• Paragraph, Link, Divider, Spacer
• Social, Sticker, HTML, Icon, GIF

📁 Media Storage Service

API Endpoints:

• POST /media/upload
• GET /media/:id
• DELETE /media/:id
• GET /media/list

Features:

• S3 upload (images, videos, GIFs)
• Automatic image optimization
• CloudFront CDN distribution
• File type validation (10MB limit)

🎨 Email Rendering Service

API Endpoints:

• POST /email-render/preview
• POST /email-render/generate-html
• POST /email-render/test-send

Features:

• Component-to-HTML conversion
• Responsive email CSS
• Cross-client compatibility
• Merge field substitution

🔀 Merge Field Service

API Endpoints:

• GET /merge-fields/list
• POST /merge-fields/validate
• POST /merge-fields/resolve

Available Merge Fields:

• {{firstName}}, {{lastName}}
• {{email}}, {{phone}}
• {{company}}, {{industry}}
• {{customField_*}}
• Dynamic contact properties

📦 Product Grid Service

API Endpoints:

• POST /product-grid/add
• PUT /product-grid/:id
• GET /product-grid/shopify-sync

Features:

• E-commerce product display
• Shopify integration
• Dynamic product insertion
• Grid/list layouts

↓

☁️ SWIM LANE 3: AWS INFRASTRUCTURE FOR EMAIL BUILDER

ECS Fargate

Services:
• Email Template Service
• Email Rendering Service

Configuration:
• Tasks: 2-8
• CPU: 2 vCPU
• RAM: 4GB

RDS PostgreSQL

Tables:
• email_templates
• email_components
• email_layouts
• email_media

Indexes:
• organizationId + status
• templateId + componentOrder

S3 Buckets

Buckets:
• unifiedbeez-email-media/
• unifiedbeez-email-images/
• unifiedbeez-email-videos/

Features:
• Versioning enabled
• Lifecycle: 90 days → Glacier

CloudFront

Purpose:
• Email image CDN
• Video streaming

Configuration:
• Origin: S3 email-media
• Cache TTL: 7 days
• Edge locations: 450+

Lambda Functions

Functions:
• image-optimizer
• video-transcoder
• email-html-generator

Trigger:
• S3 PUT events
• Timeout: 3 minutes

ElastiCache Redis

Purpose:
• Template cache
• Rendered HTML cache
• Component library cache

TTL: 1 hour

↓

🗄️ SWIM LANE 4: EMAIL BUILDER DATABASE SCHEMA

TABLE: email_templates

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Template identifier

organizationId

INTEGER

FK → organizations(id)

Organization owner

name

VARCHAR(255)

NOT NULL

Template name

subject

VARCHAR(255)

NOT NULL

Email subject line (supports merge fields)

preheader

TEXT

NULLABLE

Preview text

layoutType

VARCHAR(50)

NOT NULL

layout1|layout2|layout3|layout4

emailType

VARCHAR(50)

NOT NULL

basic|ecommerce|socials

configuration

JSONB

NOT NULL

Full email structure (components, styles, settings)

renderedHtml

TEXT

NULLABLE

Cached HTML output

thumbnailUrl

TEXT

NULLABLE

S3 path to preview image

createdBy

INTEGER

FK → users(id)

Creator user

createdAt

TIMESTAMP

DEFAULT NOW()

Creation timestamp

updatedAt

TIMESTAMP

DEFAULT NOW()

Last update timestamp

TABLE: email_components

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Component identifier

templateId

INTEGER

FK → email_templates(id)

Parent template

componentType

VARCHAR(50)

NOT NULL

componentOrder

INTEGER

NOT NULL

Display sequence

content

JSONB

NOT NULL

Component-specific content (text, URLs, settings)

styles

JSONB

NOT NULL

Component styling (colors, fonts, padding, alignment)

layout

JSONB

NOT NULL

Layout settings (width, height, responsive rules)

TABLE: email_media

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Media identifier

organizationId

INTEGER

FK → organizations(id)

Organization owner

fileName

VARCHAR(255)

NOT NULL

Original filename

fileType

VARCHAR(50)

NOT NULL

image|video|gif

s3Key

TEXT

NOT NULL

S3 object key

cdnUrl

TEXT

NOT NULL

CloudFront distribution URL

fileSize

BIGINT

NOT NULL

File size in bytes

uploadedAt

TIMESTAMP

DEFAULT NOW()

Upload timestamp

↓

FRAME 3: EMAIL BUILDER - DELIVERY & TRACKING

Email Delivery via Sendy + AWS SES | Open/Click Tracking | Bounce Handling | GDPR Unsubscribe

User Actions

Backend Services

AWS Infrastructure

Data Storage

GDPR Compliance

👤 SWIM LANE 1: EMAIL DELIVERY USER ACTIONS

1. Send Test Email

Enter test recipient email
Preview with merge fields
Send test (delivered within seconds)
Verify inbox delivery

2. Configure Delivery

Set sender name & email
Configure reply-to address
Enable tracking (opens, clicks)
Set unsubscribe link

3. Send to List/Automation

Select contact list or automation
Review recipient count
Schedule or send immediately
Confirm sending

4. Monitor Analytics

View delivery status
Track open rate
Track click-through rate
Review bounce/complaints

↓

⚙️ SWIM LANE 2: EMAIL DELIVERY BACKEND SERVICES

📤 Email Delivery Service

API Endpoints:

• POST /emails/send
• POST /emails/send-test
• POST /emails/send-batch
• GET /emails/:id/status
• POST /emails/schedule

Integration:

• Sendy (self-hosted)
• AWS SES for delivery
• Queue-based sending
• Rate limiting (14 emails/sec)

📊 Email Tracking Service

API Endpoints:

• GET /tracking/open/:trackingId
• GET /tracking/click/:trackingId
• POST /tracking/webhook (AWS SES)
• GET /emails/:id/analytics

Tracking Methods:

• Open tracking (1x1 pixel)
• Click tracking (URL rewriting)
• Bounce notifications (SES SNS)
• Complaint handling (SES SNS)

🚫 Unsubscribe Service

API Endpoints:

• GET /unsubscribe/:token
• POST /unsubscribe/confirm
• GET /unsubscribe/preferences/:token

Features:

• One-click unsubscribe
• Preference center
• Global suppression list
• GDPR-compliant opt-out

⚠️ Bounce Handling Service

Features:

• Hard bounce detection
• Soft bounce retry (3 attempts)
• Auto-suppression after 3 hard bounces
• Complaint (spam) tracking

AWS SES Integration:

• SNS webhook for bounce events
• SNS webhook for complaint events
• Real-time status updates

↓

☁️ SWIM LANE 3: AWS INFRASTRUCTURE FOR EMAIL DELIVERY

ECS Fargate

Services:
• Email Delivery Service
• Email Tracking Service

Configuration:
• Tasks: 2-10
• CPU: 2 vCPU
• RAM: 4GB

AWS SES

Configuration:
• Production access
• DKIM/SPF verified
• Rate: 14 emails/sec
• Daily limit: 50,000 emails

Features:
• Bounce handling
• Complaint tracking
• Delivery notifications

SQS Queue

Queue:
• email-sending-queue

Purpose:
• Async email sending
• Rate limiting enforcement
• Visibility timeout: 5 min
• DLQ for failed sends

RDS PostgreSQL

Tables:
• email_sends
• email_opens
• email_clicks
• email_bounces
• unsubscribes

ElastiCache Redis

Purpose:
• Rate limiting counters
• Suppression list cache
• Tracking pixel cache

TTL: 24 hours

CloudWatch

Metrics:
• Email send count
• Delivery rate
• Bounce rate
• Complaint rate

Alarms:
• Bounce rate > 5%
• Complaint rate > 0.1%

↓

🗄️ SWIM LANE 4: EMAIL DELIVERY DATABASE SCHEMA

TABLE: email_sends

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Send record identifier

templateId

INTEGER

FK → email_templates(id)

Email template used

contactId

INTEGER

FK → contacts(id)

Recipient contact

recipientEmail

VARCHAR(255)

NOT NULL

Recipient email address

subject

VARCHAR(255)

NOT NULL

Rendered subject line

status

VARCHAR(50)

NOT NULL

queued|sent|delivered|bounced|complained

sesMessageId

VARCHAR(255)

NULLABLE

AWS SES message ID

trackingToken

VARCHAR(255)

UNIQUE

Unique tracking token

sentAt

TIMESTAMP

NULLABLE

When sent to SES

deliveredAt

TIMESTAMP

NULLABLE

When delivered (SES webhook)

TABLE: email_opens

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Open event identifier

emailSendId

INTEGER

FK → email_sends(id)

Associated email send

ipAddress

INET

NULLABLE

Opener's IP address

userAgent

TEXT

NULLABLE

Opener's user agent

openedAt

TIMESTAMP

DEFAULT NOW()

Open timestamp

TABLE: email_clicks

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Click event identifier

emailSendId

INTEGER

FK → email_sends(id)

Associated email send

linkUrl

TEXT

NOT NULL

Original link URL

ipAddress

INET

NULLABLE

Clicker's IP address

clickedAt

TIMESTAMP

DEFAULT NOW()

Click timestamp

TABLE: unsubscribes

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Unsubscribe identifier

contactId

INTEGER

FK → contacts(id)

Contact who unsubscribed

email

VARCHAR(255)

NOT NULL

Unsubscribed email

reason

TEXT

NULLABLE

Unsubscribe reason (optional)

unsubscribedAt

TIMESTAMP

DEFAULT NOW()

Unsubscribe timestamp

↓

FRAME 4: FORM BUILDER - SCHEMA & VALIDATION

14+ Field Types | Schema Builder | Validation Rules | CAPTCHA Integration | CRM Mapping

User Actions

Backend Services

AWS Infrastructure

Data Storage

GDPR Compliance

👤 SWIM LANE 1: FORM BUILDER USER ACTIONS

1. Create Form

Name form
Choose form type (lead capture, survey, contact)
Select layout (horizontal/vertical)
Set submission destination

2. Add Form Fields

Drag/drop 14+ field types
Text, Email, Phone, Date, Dropdown
Checkbox, Radio, File Upload, CAPTCHA
Image, List Selector, Multi-select

3. Configure Fields

Set field labels & placeholders
Configure validation rules
Mark required/optional fields
Map to CRM fields

4. Style & Publish

Customize colors & fonts
Preview form
Generate embed code
Publish & activate

↓

⚙️ SWIM LANE 2: FORM BUILDER BACKEND SERVICES

📋 Form Service

API Endpoints:

• POST /forms/create
• GET /forms/:id
• PUT /forms/:id
• DELETE /forms/:id
• GET /forms/list
• POST /forms/:id/duplicate

Features:

• Form CRUD operations
• Schema management
• Version control
• Embed code generation

📝 Form Field Service

API Endpoints:

• POST /forms/:id/fields
• PUT /forms/:id/fields/:fieldId
• DELETE /forms/:id/fields/:fieldId
• POST /forms/:id/fields/reorder

Field Types (14+):

• text, email, phone, url
• number, date, time
• dropdown, radio, checkbox
• file_upload, textarea, captcha
• image, list_selector

✅ Validation Service

API Endpoints:

• POST /forms/validate-field
• POST /forms/validate-submission
• GET /forms/validation-rules

Validation Rules:

• Required field validation
• Email format validation
• Phone number validation (intl)
• Min/max length validation
• Regex pattern matching
• Custom validation functions

🤖 CAPTCHA Service

Integration:

• Google reCAPTCHA v3
• Score-based verification
• Invisible CAPTCHA
• Bot detection

API Endpoints:

• POST /captcha/verify
• GET /captcha/site-key

🗂️ Form Submission Service

API Endpoints:

• POST /forms/:id/submit
• GET /forms/:id/submissions
• GET /forms/:id/submissions/:submissionId
• DELETE /forms/:id/submissions/:submissionId

Features:

• Submission processing
• Data sanitization
• Spam filtering
• Webhook notifications

🔗 CRM Mapping Service

API Endpoints:

• POST /forms/:id/crm-mapping
• GET /forms/:id/crm-mapping
• GET /crm/fields/list

Features:

• Map form fields to CRM fields
• Auto-create contacts
• Tag assignment on submission
• List assignment

↓

☁️ SWIM LANE 3: AWS INFRASTRUCTURE FOR FORM BUILDER

ECS Fargate

Services:
• Form Service
• Form Submission Service
• Validation Service

Configuration:
• Tasks: 2-8
• CPU: 2 vCPU
• RAM: 4GB

RDS PostgreSQL

Tables:
• forms
• form_fields
• form_submissions
• form_validation_rules

Indexes:
• organizationId + status
• formId + submittedAt

Lambda Functions

Functions:
• form-submission-processor
• form-file-upload-handler
• captcha-verifier

Trigger:
• API Gateway
• S3 PUT events
• Timeout: 3 minutes

S3 Buckets

Buckets:
• unifiedbeez-form-uploads/

Purpose:
• File upload storage
• Form submission attachments

Features:
• Versioning enabled
• Lifecycle: 180 days

ElastiCache Redis

Purpose:
• Form schema cache
• Validation rules cache
• Rate limiting (prevent spam)

TTL: 1 hour

SQS Queue

Queue:
• form-submission-queue

Purpose:
• Async submission processing
• CRM contact creation
• Webhook notifications
• Visibility timeout: 2 min

↓

🗄️ SWIM LANE 4: FORM BUILDER DATABASE SCHEMA

TABLE: forms

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Form identifier

organizationId

INTEGER

FK → organizations(id)

Organization owner

name

VARCHAR(255)

NOT NULL

Form name

formType

VARCHAR(50)

NOT NULL

lead_capture|survey|contact|registration

layout

VARCHAR(50)

DEFAULT 'vertical'

vertical|horizontal

status

VARCHAR(50)

DEFAULT 'draft'

draft|published|archived

configuration

JSONB

NOT NULL

Form settings (colors, fonts, submit button text)

embedCode

TEXT

NULLABLE

Generated embed HTML/JS

successMessage

TEXT

NULLABLE

Message shown after submission

redirectUrl

TEXT

NULLABLE

Redirect after submission (optional)

createdAt

TIMESTAMP

DEFAULT NOW()

Creation timestamp

updatedAt

TIMESTAMP

DEFAULT NOW()

Last update timestamp

TABLE: form_fields

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Field identifier

formId

INTEGER

FK → forms(id)

Parent form

fieldType

VARCHAR(50)

NOT NULL

label

VARCHAR(255)

NOT NULL

Field label

placeholder

VARCHAR(255)

NULLABLE

Field placeholder text

required

BOOLEAN

DEFAULT false

Is field required?

fieldOrder

INTEGER

NOT NULL

Display order

validationRules

JSONB

NULLABLE

Validation config (min, max, regex, custom)

options

JSONB

NULLABLE

Options for dropdown/radio/checkbox

crmFieldMapping

VARCHAR(255)

NULLABLE

CRM field to map to (e.g., firstName, email)

TABLE: form_submissions

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Submission identifier

formId

INTEGER

FK → forms(id)

Form submitted

contactId

INTEGER

FK → contacts(id)

Contact created/updated from submission

submissionData

JSONB

NOT NULL

Full form submission data (field_id: value)

ipAddress

INET

NULLABLE

Submitter's IP address

userAgent

TEXT

NULLABLE

Submitter's user agent

captchaScore

DECIMAL(3,2)

NULLABLE

reCAPTCHA score (0.0-1.0)

isSpam

BOOLEAN

DEFAULT false

Spam detection flag

submittedAt

TIMESTAMP

DEFAULT NOW()

Submission timestamp

↓

FRAME 5: CAMPAIGN AUTOMATION - WORKFLOW ENGINE

50+ Trigger Types | Smart Rules & Conditions | Boolean Logic | Workflow Orchestration | Action Branching

User Actions

Backend Services

AWS Infrastructure

Data Storage

GDPR Compliance

👤 SWIM LANE 1: CAMPAIGN AUTOMATION WORKFLOW USER ACTIONS

1. Create Campaign

Name campaign
Select campaign type (re-engagement, nurture, sales)
Choose template or start from scratch
Set campaign goal & KPIs

2. Define Triggers

Select trigger (50+ types)
Contact created, tag added, form submitted
Email opened, link clicked, purchase made
Set trigger filters & conditions

3. Build Workflow

Add actions (email, SMS, WhatsApp, wait)
Configure Smart Rules (if/then logic)
Add condition groups (AND/OR/NOT)
Create branching paths

4. Test & Launch

Preview workflow visualization
Test with sample contacts
Review trigger conditions
Activate campaign

↓

⚙️ SWIM LANE 2: CAMPAIGN WORKFLOW BACKEND SERVICES

🎯 Campaign Service

API Endpoints:

• POST /campaigns/create
• GET /campaigns/:id
• PUT /campaigns/:id
• POST /campaigns/:id/activate
• POST /campaigns/:id/pause
• DELETE /campaigns/:id
• GET /campaigns/list

Features:

• Campaign CRUD operations
• Workflow orchestration
• State management
• Version control

⚡ Trigger Evaluation Service

API Endpoints:

• POST /triggers/evaluate
• GET /triggers/types
• POST /triggers/:id/test
• POST /triggers/register-event

Trigger Types (50+):

• Contact: created, updated, tag_added
• Email: opened, clicked, bounced
• Form: submitted, abandoned
• Purchase: completed, refunded
• Time-based: date_reached, anniversary
• Custom: webhook, API event

🧠 Smart Rules Engine

API Endpoints:

• POST /smart-rules/evaluate
• POST /smart-rules/validate
• GET /smart-rules/operators

Logic Operations:

• Boolean: AND, OR, NOT
• Comparison: equals, not_equals, contains
• Numeric: greater_than, less_than
• Date: before, after, between
• Regex: pattern matching
• Nested condition groups

🔀 Workflow Orchestration Service

API Endpoints:

• POST /workflows/execute
• GET /workflows/:id/status
• POST /workflows/:id/pause
• POST /workflows/:id/resume

Features:

• State machine management
• Branching path execution
• Wait state handling
• Error recovery & retries
• Parallel action execution

⏱️ Wait Action Service

Features:

• Time-based delays (minutes to months)
• Wait until specific date/time
• Wait for event (e.g., email opened)
• Smart wait (optimal send time)

Implementation:

• EventBridge scheduled rules
• SQS delayed messages
• Redis TTL for short delays
• Database polling for event waits

📊 Campaign Analytics Service

API Endpoints:

• GET /campaigns/:id/analytics
• GET /campaigns/:id/performance
• GET /campaigns/:id/funnel
• GET /campaigns/:id/revenue

Metrics Tracked:

• Contacts entered
• Conversion rate
• Revenue attribution
• Action completion rates
• Drop-off analysis

↓

☁️ SWIM LANE 3: AWS INFRASTRUCTURE FOR CAMPAIGN WORKFLOWS

ECS Fargate

Services:
• Campaign Service
• Trigger Evaluation Service
• Workflow Orchestration
• Smart Rules Engine

Configuration:
• Tasks: 5-15
• CPU: 4 vCPU
• RAM: 8GB

RDS PostgreSQL

Tables:
• campaigns
• campaign_triggers
• campaign_actions
• campaign_conditions
• campaign_runs
• campaign_analytics

Indexes:
• campaignId + status
• contactId + campaignId

SQS Queues

Queues:
• campaign-trigger-queue
• campaign-action-queue
• campaign-delay-queue (delay 0-15min)

Purpose:
• Async trigger processing
• Action execution
• Wait state delays
• DLQ for failed actions

ElastiCache Redis

Purpose:
• Campaign state cache
• Active workflow tracking
• Rate limiting
• Session locking

Data Structures:
• Sorted sets (scheduled actions)
• Hash maps (workflow state)

EventBridge

Purpose:
• Scheduled campaign triggers
• Time-based automation
• Delayed action execution

Rules:
• Daily/weekly campaign checks
• Anniversary automations
• Custom cron schedules

Lambda Functions

Functions:
• campaign-trigger-evaluator
• campaign-workflow-executor
• campaign-cleanup

Triggers:
• EventBridge (scheduled)
• SQS (delayed messages)
• Timeout: 5 minutes

CloudWatch

Log Groups:
• /aws/ecs/campaign-service
• /aws/lambda/campaign-executor

Metrics:
• Campaign execution count
• Success/failure rate
• Average execution time

Alarms:
• Failed workflow rate > 5%

Secrets Manager

Stored:
• Webhook secrets
• Third-party API keys
• Channel credentials

Encryption:
• KMS encrypted
• Auto-rotation enabled

↓

🗄️ SWIM LANE 4: CAMPAIGN WORKFLOW DATABASE SCHEMA

TABLE: campaigns

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Campaign identifier

organizationId

INTEGER

FK → organizations(id)

Organization owner

name

VARCHAR(255)

NOT NULL

Campaign name

campaignType

VARCHAR(50)

NOT NULL

re_engagement|nurture|sales|retention

status

VARCHAR(50)

DEFAULT 'draft'

draft|active|paused|completed|archived

workflowDefinition

JSONB

NOT NULL

Full workflow graph (triggers, actions, conditions, branches)

goal

VARCHAR(255)

NULLABLE

Campaign objective

targetKPI

JSONB

NULLABLE

KPI targets (conversion_rate, revenue, etc.)

activatedAt

TIMESTAMP

NULLABLE

When campaign was activated

createdAt

TIMESTAMP

DEFAULT NOW()

Creation timestamp

updatedAt

TIMESTAMP

DEFAULT NOW()

Last update timestamp

TABLE: campaign_triggers

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Trigger identifier

campaignId

INTEGER

FK → campaigns(id)

Associated campaign

triggerType

VARCHAR(100)

NOT NULL

contact_created|tag_added|email_opened|form_submitted|etc.

filterConditions

JSONB

NULLABLE

Trigger filter rules (e.g., only if tag = "vip")

schedule

VARCHAR(255)

NULLABLE

Cron expression (for time-based triggers)

isActive

BOOLEAN

DEFAULT true

Is trigger enabled?

TABLE: campaign_actions

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Action identifier

campaignId

INTEGER

FK → campaigns(id)

Associated campaign

actionType

VARCHAR(100)

NOT NULL

stepOrder

INTEGER

NOT NULL

Execution sequence

configuration

JSONB

NOT NULL

Action config (templateId, delay, channel settings)

parentActionId

INTEGER

FK → campaign_actions(id)

Parent action (for branching workflows)

conditionGroupId

INTEGER

FK → campaign_conditions(id)

Condition required for execution

TABLE: campaign_conditions

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Condition group identifier

campaignId

INTEGER

FK → campaigns(id)

Associated campaign

logicOperator

VARCHAR(10)

NOT NULL

AND|OR|NOT

conditions

JSONB

NOT NULL

Array of condition rules [{field, operator, value}]

parentConditionId

INTEGER

FK → campaign_conditions(id)

Parent condition group (for nesting)

TABLE: campaign_runs

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Run identifier

campaignId

INTEGER

FK → campaigns(id)

Campaign being executed

contactId

INTEGER

FK → contacts(id)

Target contact

status

VARCHAR(50)

NOT NULL

currentActionId

INTEGER

FK → campaign_actions(id)

Current workflow step

workflowState

JSONB

NOT NULL

Workflow execution state & history

startedAt

TIMESTAMP

NOT NULL

Execution start time

completedAt

TIMESTAMP

NULLABLE

Execution completion time

↓

FRAME 6: CAMPAIGN AUTOMATION - MULTI-CHANNEL MESSAGING

User Actions

Backend Services

AWS Infrastructure

Data Storage

GDPR Compliance

👤 SWIM LANE 1: MULTI-CHANNEL MESSAGING USER ACTIONS

1. Select Channel

Choose message channel (Email, SMS, WhatsApp)
View channel availability per contact
Set fallback channel (if primary fails)
Configure channel priorities

2. Create Message

Email: Use email builder template
SMS: Write text message (160 chars)
WhatsApp: Select approved template
Add dynamic merge fields

3. Configure Settings

Set sender information
Configure delivery timing
Enable/disable tracking
Set rate limits

4. Test & Deploy

Send test messages
Preview across channels
Verify template approval (WhatsApp)
Add to campaign workflow

↓

⚙️ SWIM LANE 2: MULTI-CHANNEL MESSAGING BACKEND SERVICES

📧 Email Channel Service

API Endpoints:

• POST /channels/email/send
• GET /channels/email/templates
• POST /channels/email/test

Integration:

• Sendy + AWS SES
• Rate: 14 emails/sec
• Open/click tracking
• Bounce/complaint handling

💬 SMS Channel Service

API Endpoints:

• POST /channels/sms/send
• GET /channels/sms/status/:id
• POST /channels/sms/test
• GET /channels/sms/pricing

Integration:

• Twilio SMS API
• International support (190+ countries)
• Delivery receipts
• Character count: 160 (single), 1600 (concat)

📱 WhatsApp Channel Service

API Endpoints:

• POST /channels/whatsapp/send
• GET /channels/whatsapp/templates
• POST /channels/whatsapp/templates/submit
• GET /channels/whatsapp/template-status/:id

Integration:

• WhatsApp Business Cloud API
• Pre-approved message templates
• Rich media support (images, videos, docs)
• Interactive buttons

🔀 Channel Router Service

API Endpoints:

• POST /channel-router/send
• POST /channel-router/select-optimal
• GET /channel-router/availability/:contactId

Features:

• Auto-select optimal channel
• Fallback channel logic
• Contact preference tracking
• Channel availability validation

📋 Message Template Service

API Endpoints:

• GET /message-templates
• POST /message-templates/create
• PUT /message-templates/:id
• POST /message-templates/:id/approve

Features:

• Channel-specific templates
• WhatsApp template approval workflow
• Template versioning
• Variable/merge field management

📊 Message Tracking Service

API Endpoints:

• POST /message-tracking/delivery
• POST /message-tracking/read
• POST /message-tracking/failed
• GET /message-tracking/:messageId

Tracking Events:

• Sent, Delivered, Read
• Failed, Bounced
• Clicked (email/WhatsApp buttons)
• Replied

↓

☁️ SWIM LANE 3: AWS INFRASTRUCTURE FOR MULTI-CHANNEL MESSAGING

ECS Fargate

Services:
• Email Channel Service
• SMS Channel Service
• WhatsApp Channel Service
• Channel Router Service

Configuration:
• Tasks: 3-12
• CPU: 2 vCPU
• RAM: 4GB

RDS PostgreSQL

Tables:
• messages
• message_templates
• channel_preferences
• whatsapp_templates
• message_delivery_status

Indexes:
• messageId + channel
• contactId + sentAt

SQS Queues

Queues:
• email-sending-queue
• sms-sending-queue
• whatsapp-sending-queue
• message-status-queue

Purpose:
• Channel-specific message queues
• Rate limiting per channel
• Delivery status updates

SNS Topics

Topics:
• message-delivered
• message-failed
• whatsapp-webhook-events

Purpose:
• Webhook notifications
• Delivery status broadcasting
• Multi-subscriber pattern

ElastiCache Redis

Purpose:
• Rate limiting (per channel)
• Message template cache
• WhatsApp template status
• Contact channel preference

TTL: 6 hours

Lambda Functions

Functions:
• whatsapp-webhook-handler
• twilio-webhook-handler
• message-status-processor

Triggers:
• API Gateway (webhooks)
• SNS (status events)
• Timeout: 2 minutes

Secrets Manager

Stored:
• Twilio Account SID & Auth Token
• WhatsApp Business API credentials
• Sendy API key
• AWS SES credentials

Encryption: KMS

CloudWatch

Metrics:
• Messages sent (per channel)
• Delivery success rate
• Failed message count
• Average delivery time

Alarms:
• Email delivery rate < 95%
• SMS failure rate > 5%

↓

🗄️ SWIM LANE 4: MULTI-CHANNEL MESSAGING DATABASE SCHEMA

TABLE: messages

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Message identifier

campaignRunId

INTEGER

FK → campaign_runs(id)

Associated campaign run

contactId

INTEGER

FK → contacts(id)

Recipient contact

channel

VARCHAR(50)

NOT NULL

email|sms|whatsapp

templateId

INTEGER

FK → message_templates(id)

Template used

content

TEXT

NOT NULL

Rendered message content

status

VARCHAR(50)

NOT NULL

externalMessageId

VARCHAR(255)

NULLABLE

Provider message ID (Twilio SID, WhatsApp WAMID, SES ID)

sentAt

TIMESTAMP

NULLABLE

When sent to provider

deliveredAt

TIMESTAMP

NULLABLE

When delivered to recipient

readAt

TIMESTAMP

NULLABLE

When read by recipient (WhatsApp, email)

errorMessage

TEXT

NULLABLE

Error details (if failed)

TABLE: message_templates

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Template identifier

organizationId

INTEGER

FK → organizations(id)

Organization owner

name

VARCHAR(255)

NOT NULL

Template name

channel

VARCHAR(50)

NOT NULL

email|sms|whatsapp

content

TEXT

NOT NULL

Template content with merge fields

variables

JSONB

NULLABLE

Template variables/merge fields

category

VARCHAR(100)

NULLABLE

Template category (marketing, transactional, etc.)

createdAt

TIMESTAMP

DEFAULT NOW()

Creation timestamp

TABLE: whatsapp_templates

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

WhatsApp template identifier

organizationId

INTEGER

FK → organizations(id)

Organization owner

templateName

VARCHAR(255)

NOT NULL

WhatsApp template name

language

VARCHAR(10)

NOT NULL

Template language (en_US, es_ES, etc.)

category

VARCHAR(50)

NOT NULL

UTILITY|MARKETING|AUTHENTICATION

components

JSONB

NOT NULL

Template structure (header, body, footer, buttons)

approvalStatus

VARCHAR(50)

DEFAULT 'pending'

pending|approved|rejected

metaTemplateId

VARCHAR(255)

NULLABLE

Meta/WhatsApp template ID (after approval)

submittedAt

TIMESTAMP

NULLABLE

When submitted for approval

approvedAt

TIMESTAMP

NULLABLE

When approved by Meta

TABLE: channel_preferences

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Preference identifier

contactId

INTEGER

FK → contacts(id)

Contact

preferredChannel

VARCHAR(50)

NOT NULL

email|sms|whatsapp

emailEnabled

BOOLEAN

DEFAULT true

Email channel enabled?

smsEnabled

BOOLEAN

DEFAULT true

SMS channel enabled?

whatsappEnabled

BOOLEAN

DEFAULT true

WhatsApp channel enabled?

updatedAt

TIMESTAMP

DEFAULT NOW()

Last preference update

↓

FRAME 7: RETENTION & NURTURE BUILDER - WORKFLOW

Customer Lifecycle Campaigns | Drip Sequences | Engagement Scoring | Automated Nurturing | Win-back Campaigns

User Actions

Backend Services

AWS Infrastructure

Data Storage

GDPR Compliance

👤 SWIM LANE 1: RETENTION & NURTURE USER ACTIONS

1. Create Nurture Campaign

Select campaign type (onboarding, engagement, win-back)
Define customer lifecycle stage
Set nurture duration (days/weeks)
Choose sequence template

2. Build Drip Sequence

Add sequence steps (Day 1, Day 3, Day 7, etc.)
Create email/SMS for each step
Set time delays between steps
Configure step conditions

3. Configure Engagement Rules

Set engagement scoring criteria
Define exit conditions (goal reached)
Configure re-entry rules
Set engagement thresholds

4. Launch & Monitor

Preview full sequence
Test sequence with sample contacts
Review engagement metrics
Activate nurture campaign

↓

⚙️ SWIM LANE 2: RETENTION & NURTURE BACKEND SERVICES

🌱 Nurture Campaign Service

API Endpoints:

• POST /nurture-campaigns/create
• GET /nurture-campaigns/:id
• PUT /nurture-campaigns/:id
• POST /nurture-campaigns/:id/activate
• DELETE /nurture-campaigns/:id
• GET /nurture-campaigns/list

Features:

• Campaign CRUD operations
• Lifecycle stage tracking
• Template library (50+ nurture sequences)
• Multi-step workflow management

💧 Drip Sequence Service

API Endpoints:

• POST /drip-sequences/create
• GET /drip-sequences/:id
• POST /drip-sequences/:id/steps
• PUT /drip-sequences/:id/steps/:stepId
• POST /drip-sequences/:id/enroll-contact

Features:

• Multi-step sequence builder
• Time-based delays (hours to months)
• Conditional step execution
• Dynamic content per step
• Sequence cloning & versioning

📈 Engagement Scoring Service

API Endpoints:

• GET /engagement/score/:contactId
• POST /engagement/calculate
• PUT /engagement/scoring-rules
• GET /engagement/leaderboard

Scoring Criteria:

• Email opens (+5 points)
• Link clicks (+10 points)
• Form submissions (+20 points)
• Purchases (+50 points)
• Inactivity decay (-2 points/day)
• Custom scoring rules

🔄 Lifecycle Stage Service

API Endpoints:

• GET /lifecycle/stages
• POST /lifecycle/update-stage
• GET /lifecycle/contacts-by-stage

Lifecycle Stages:

• Subscriber → Lead → Customer
• Engaged → At-Risk → Churned
• VIP → Inactive
• Automated stage transitions
• Stage-specific nurture sequences

🎯 Win-Back Campaign Service

Features:

• Inactive contact detection
• Automated win-back triggers
• Special offer management
• Re-engagement incentives

Triggers:

• No activity in 30/60/90 days
• Engagement score below threshold
• No email opens in X days
• Cart abandonment

📊 Retention Analytics Service

API Endpoints:

• GET /retention/cohort-analysis
• GET /retention/churn-prediction
• GET /retention/ltv-calculation
• GET /retention/campaign-performance

Metrics Tracked:

• Retention rate (30/60/90 days)
• Churn rate
• Customer Lifetime Value (LTV)
• Engagement trends
• Win-back success rate

↓

☁️ SWIM LANE 3: AWS INFRASTRUCTURE FOR RETENTION & NURTURE

ECS Fargate

Services:
• Nurture Campaign Service
• Drip Sequence Service
• Engagement Scoring Service
• Lifecycle Stage Service

Configuration:
• Tasks: 2-10
• CPU: 2 vCPU
• RAM: 4GB

RDS PostgreSQL

Tables:
• nurture_campaigns
• drip_sequences
• sequence_steps
• sequence_enrollments
• engagement_scores
• lifecycle_stages

Indexes:
• contactId + lifecycleStage
• enrollmentId + status

SQS Queues

Queues:
• nurture-sequence-queue
• engagement-score-update-queue
• lifecycle-transition-queue

Purpose:
• Async sequence step execution
• Score calculation processing
• Stage transition notifications

ElastiCache Redis

Purpose:
• Engagement score cache
• Active sequence tracking
• Lifecycle stage cache
• Upcoming step schedule

TTL: 1 hour

EventBridge

Purpose:
• Daily engagement score calculation
• Scheduled sequence step execution
• Churn prediction jobs

Rules:
• Daily: Score decay calculation
• Hourly: Check upcoming steps
• Weekly: Cohort analysis

Lambda Functions

Functions:
• sequence-step-executor
• engagement-score-calculator
• churn-predictor
• lifecycle-stage-updater

Triggers:
• EventBridge (scheduled)
• SQS (step execution)
• Timeout: 5 minutes

CloudWatch

Metrics:
• Active sequences count
• Sequence completion rate
• Average engagement score
• Churn rate

Alarms:
• Sequence failure rate > 5%
• Churn rate > 10%

Secrets Manager

Stored:
• Analytics API keys
• Third-party integration tokens

Encryption:
• KMS encrypted
• Auto-rotation enabled

↓

🗄️ SWIM LANE 4: RETENTION & NURTURE DATABASE SCHEMA

TABLE: nurture_campaigns

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Nurture campaign identifier

organizationId

INTEGER

FK → organizations(id)

Organization owner

name

VARCHAR(255)

NOT NULL

Campaign name

campaignType

VARCHAR(50)

NOT NULL

onboarding|engagement|win_back|lifecycle

targetLifecycleStage

VARCHAR(50)

NULLABLE

Lifecycle stage this campaign targets

status

VARCHAR(50)

DEFAULT 'draft'

draft|active|paused|archived

durationDays

INTEGER

NULLABLE

Total campaign duration in days

goalMetric

VARCHAR(100)

NULLABLE

engagement_score|conversion|retention

createdAt

TIMESTAMP

DEFAULT NOW()

Creation timestamp

activatedAt

TIMESTAMP

NULLABLE

When campaign was activated

TABLE: drip_sequences

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Sequence identifier

nurtureCampaignId

INTEGER

FK → nurture_campaigns(id)

Parent nurture campaign

name

VARCHAR(255)

NOT NULL

Sequence name

description

TEXT

NULLABLE

Sequence description

totalSteps

INTEGER

NOT NULL

Number of steps in sequence

enrollmentTrigger

JSONB

NOT NULL

Conditions to enroll contact in sequence

exitConditions

JSONB

NULLABLE

Conditions to exit sequence early

TABLE: sequence_steps

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Step identifier

sequenceId

INTEGER

FK → drip_sequences(id)

Parent sequence

stepNumber

INTEGER

NOT NULL

Step order (1, 2, 3, etc.)

delayValue

INTEGER

NOT NULL

Delay before this step (in delayUnit)

delayUnit

VARCHAR(20)

NOT NULL

hours|days|weeks

actionType

VARCHAR(50)

NOT NULL

send_email|send_sms|add_tag|update_score

actionConfiguration

JSONB

NOT NULL

Step action details (templateId, content, etc.)

conditions

JSONB

NULLABLE

Conditional execution rules for this step

TABLE: sequence_enrollments

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Enrollment identifier

sequenceId

INTEGER

FK → drip_sequences(id)

Sequence enrolled in

contactId

INTEGER

FK → contacts(id)

Contact enrolled

status

VARCHAR(50)

NOT NULL

active|completed|exited|paused

currentStep

INTEGER

NOT NULL

Current step number in sequence

enrolledAt

TIMESTAMP

DEFAULT NOW()

Enrollment timestamp

completedAt

TIMESTAMP

NULLABLE

When sequence was completed

nextStepAt

TIMESTAMP

NULLABLE

When next step should execute

TABLE: engagement_scores

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Score identifier

contactId

INTEGER

FK → contacts(id)

Contact

score

INTEGER

DEFAULT 0

Current engagement score

lastActivityDate

DATE

NULLABLE

Date of last engagement activity

engagementLevel

VARCHAR(50)

NOT NULL

high|medium|low|inactive

updatedAt

TIMESTAMP

DEFAULT NOW()

Last score update

TABLE: lifecycle_stages

Column Name

Type

Constraints

Purpose

id

SERIAL

PRIMARY KEY

Stage assignment identifier

contactId

INTEGER

FK → contacts(id)

Contact

currentStage

VARCHAR(50)

NOT NULL

previousStage

VARCHAR(50)

NULLABLE

Previous lifecycle stage

stageChangedAt

TIMESTAMP

DEFAULT NOW()

When stage was last updated

↓

FRAME 8: SALES & LEAD GENERATION BUILDER - LEAD CAPTURE

Multi-Source Lead Capture | Form Integration | BeeBot WebChat | Lead Scoring & Qualification

User Actions

Backend Services

AWS Infrastructure

Data Storage

GDPR Compliance

👤 SWIM LANE 1: USER ACTIONS

1. Configure Lead Capture Sources

Form Integration: Connect website forms (native, WordPress, custom HTML)
BeeBot WebChat: Enable AI-powered chat widget with lead qualification
Landing Pages: Create high-converting landing pages with form builder
Import Leads: CSV/Excel bulk import with field mapping
API Integration: Connect third-party CRMs (Salesforce, HubSpot, Zoho)

2. Set Up Lead Scoring Rules

Demographic Scoring: Points based on job title, company size, industry
Behavioral Scoring: Website visits, email engagement, content downloads
Firmographic Scoring: Company revenue, employee count, location
Engagement Scoring: Form submissions, chat interactions, meeting bookings
Decay Rules: Reduce score for inactivity over time

3. Configure Lead Qualification

BANT Framework: Budget, Authority, Need, Timeline qualification
Lead Stages: Cold → Warm → Hot → MQL → SQL → Opportunity
Disqualification Rules: Competitors, students, invalid emails, spam domains
Auto-Assignment: Route qualified leads to sales reps based on territory/product
Notification Settings: Real-time alerts for hot leads (score >80)

4. Enable Lead Enrichment

Company Data: Auto-fetch company info from domain (Clearbit API)
Social Profiles: LinkedIn, Twitter profile enrichment
Email Validation: Real-time email verification (syntax, MX records, disposable detection)
Duplicate Detection: Merge duplicate leads by email/phone
Custom Fields: Add industry-specific qualification fields

⚙️ SWIM LANE 2: BACKEND SERVICES (NestJS + TypeScript)

🎯 Lead Capture Service

API Endpoints:

• POST /api/leads/capture - Create new lead from any source
• POST /api/leads/bulk-import - CSV/Excel import with validation
• GET /api/leads/:id - Retrieve lead details with enrichment data
• PUT /api/leads/:id - Update lead information
• POST /api/leads/:id/merge - Merge duplicate leads

Core Logic:

• Multi-source lead ingestion (forms, chat, API, import)
• Real-time email validation (regex, MX lookup, disposable check)
• Duplicate detection algorithm (email/phone/name fuzzy match)
• Lead deduplication with merge conflict resolution
• Source tracking (UTM parameters, referrer, campaign)

📝 Form Integration Service

API Endpoints:

• POST /api/forms/integrate - Connect external form
• POST /api/forms/webhook - Receive form submissions
• GET /api/forms/:id/embed-code - Generate embed code
• POST /api/forms/:id/map-fields - Map form fields to CRM fields

Integrations:

• Native HTML forms (JavaScript SDK)
• WordPress plugin integration
• Typeform, Jotform, Google Forms webhooks
• Custom webhook receiver with signature validation
• reCAPTCHA v3 spam prevention (score threshold 0.5)

💬 BeeBot WebChat Service

API Endpoints:

• POST /api/beechat/initialize - Initialize chat session
• POST /api/beechat/message - Send/receive chat messages
• POST /api/beechat/qualify-lead - Run qualification logic
• POST /api/beechat/handoff - Transfer to human agent

AI Features:

• Natural language qualification (BANT extraction)
• Intent detection (pricing, demo, support, general inquiry)
• Sentiment analysis (positive, neutral, negative)
• Smart follow-up questions based on context
• Auto-book meetings with calendar integration (Calendly API)

⭐ Lead Scoring Service

API Endpoints:

• POST /api/scoring/calculate - Calculate lead score
• POST /api/scoring/rules - Create/update scoring rules
• GET /api/scoring/history/:leadId - Score change history
• POST /api/scoring/batch - Recalculate scores for segment

Scoring Algorithm:

• Demographic: Job title (+20 decision-maker), company size (+15 enterprise)
• Behavioral: Email open (+5), link click (+10), form submit (+25)
• Engagement: Chat interaction (+30), demo request (+50), pricing page (+40)
• Firmographic: Revenue >$10M (+25), employees >500 (+20)
• Decay: -2 points per day of inactivity

✅ Lead Qualification Service

API Endpoints:

• POST /api/qualification/evaluate - Evaluate lead against BANT
• PUT /api/qualification/:leadId/stage - Update lead stage
• POST /api/qualification/disqualify - Mark lead as disqualified
• POST /api/qualification/assign - Auto-assign to sales rep

Qualification Logic:

• MQL (Marketing Qualified): Score >50, engaged last 30 days
• SQL (Sales Qualified): Score >80, BANT criteria met
• Opportunity: SQL + meeting booked/demo requested
• Disqualification: Competitor domains, role-based emails, spam patterns
• Round-robin assignment: Distribute leads evenly to sales team

🔍 Lead Enrichment Service

API Endpoints:

• POST /api/enrichment/company - Enrich company data from domain
• POST /api/enrichment/email-validate - Validate email deliverability
• POST /api/enrichment/social - Fetch social profiles
• POST /api/enrichment/batch - Bulk enrichment job

Enrichment Sources:

• Clearbit API: Company data (name, logo, industry, employee count, revenue)
• Hunter.io: Email verification (deliverability score, catch-all detection)
• LinkedIn API: Professional profile data (requires OAuth)
• DNS Lookup: MX records validation for email domains
• IP Geolocation: Location data from submission IP

☁️ SWIM LANE 3: AWS INFRASTRUCTURE

ECS Fargate - Lead Services

Task Definition: lead-capture-service, lead-scoring-service, lead-qualification-service
CPU/Memory: 1 vCPU, 2GB RAM per task
Auto Scaling: 2-15 tasks based on SQS queue depth
Scaling Metric: ApproximateNumberOfMessagesVisible >100
Health Check: /health endpoint every 30s
Deployment: Rolling update, 50% minimum healthy

RDS PostgreSQL 15.4 - Lead Database

Instance: db.r6g.xlarge (Multi-AZ for HA)
Storage: 500GB GP3 SSD (auto-scaling to 1TB)
Tables: leads (1M+ rows), lead_scores, lead_sources, lead_activities
Indexes: email (unique), phone, company_domain, created_at, score
Partitioning: lead_activities partitioned by month
Backup: Automated daily snapshots, 7-day retention

SQS - Lead Processing Queues

lead-capture-queue: New lead ingestion (FIFO, message retention 14 days)
lead-enrichment-queue: Asynchronous enrichment jobs
lead-scoring-queue: Score recalculation events
Visibility Timeout: 5 minutes (enrichment), 30 seconds (scoring)
Dead Letter Queue: 3 retries before DLQ
Throughput: ~500 messages/second peak

ElastiCache Redis - Lead Cache

Node Type: cache.r6g.large (Cluster Mode enabled)
Use Cases:
• Lead deduplication cache (email/phone hashes, TTL 24h)
• Scoring rules cache (TTL 1h, invalidate on rule update)
• API rate limiting (sliding window, 100 req/min per IP)
• Session data for BeeBot chat (TTL 30 min)
• Enrichment API response cache (TTL 7 days)

Lambda Functions

lead-enrichment-worker: Calls Clearbit/Hunter APIs (timeout 60s, 1GB RAM)
lead-score-decay: Daily job to apply inactivity decay (EventBridge trigger)
duplicate-lead-merger: Nightly duplicate detection + merge (6GB RAM)
lead-export: Generate CSV/Excel exports for GDPR requests (15min timeout)
Runtime: Node.js 20.x, bundled with esbuild

SNS - Lead Notifications

hot-lead-alerts: Real-time alerts for leads with score >80
mql-notifications: Notify sales team when lead becomes MQL
sql-notifications: High-priority SQL notifications
Subscribers: Email, SMS (Twilio), Slack webhooks, PagerDuty
Filtering: Subscription filters by lead source, territory, product

CloudWatch - Monitoring & Alerts

Metrics: Leads captured/min, avg enrichment time, scoring latency, duplicate rate
Alarms: Enrichment failures >5%, scoring queue depth >1000
Logs: Lead capture events, validation errors, API call logs
Dashboards: Lead funnel metrics, source performance, conversion rates
Retention: 90 days log retention

Secrets Manager - API Credentials

Secrets Stored:
• unifiedbeez/clearbit-api-key (company enrichment)
• unifiedbeez/hunter-api-key (email validation)
• unifiedbeez/linkedin-oauth (social enrichment)
• unifiedbeez/recaptcha-secret (spam prevention)
Encryption: KMS key (unifiedbeez-secrets-key)
Rotation: 90-day automatic rotation

🗄️ SWIM LANE 4: DATABASE SCHEMA & RELATIONSHIPS

TABLE: leads

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique lead identifier

email

VARCHAR(255)

UNIQUE, NOT NULL

Lead email address (primary deduplication key)

phone

VARCHAR(20)

NULLABLE

Phone number (E.164 format, secondary dedup key)

first_name

VARCHAR(100)

NOT NULL

First name

last_name

VARCHAR(100)

NOT NULL

Last name

company_name

VARCHAR(255)

NULLABLE

Company name (enriched from domain or manually entered)

company_domain

VARCHAR(255)

NULLABLE, INDEX

Company website domain (for enrichment)

job_title

VARCHAR(255)

NULLABLE

Job title/role

industry

VARCHAR(100)

NULLABLE

Industry vertical (enriched)

company_size

VARCHAR(50)

NULLABLE

Employee count range (1-10, 11-50, 51-200, 201-500, 500+)

annual_revenue

VARCHAR(50)

NULLABLE

Revenue range ($0-1M, $1-10M, $10-50M, $50M+)

country

VARCHAR(2)

NULLABLE

ISO 3166-1 alpha-2 country code

state

VARCHAR(100)

NULLABLE

State/province

city

VARCHAR(100)

NULLABLE

City

lead_source_id

UUID

FOREIGN KEY → lead_sources(id)

Reference to source (form, chat, API, import)

score

INTEGER

DEFAULT 0, INDEX

Current lead score (0-100)

stage

ENUM

'cold', 'warm', 'hot', 'mql', 'sql', 'opportunity', 'customer', 'disqualified'

Current lead stage in funnel

disqualification_reason

TEXT

NULLABLE

Reason if stage = 'disqualified'

assigned_to_user_id

UUID

FOREIGN KEY → users(id), NULLABLE

Assigned sales rep

utm_source

VARCHAR(255)

NULLABLE

UTM source parameter

utm_medium

VARCHAR(255)

NULLABLE

UTM medium parameter

utm_campaign

VARCHAR(255)

NULLABLE

UTM campaign parameter

referrer_url

TEXT

NULLABLE

HTTP referrer URL

ip_address

INET

NULLABLE

Submission IP address (for geolocation)

enrichment_data

JSONB

NULLABLE

Enriched data from Clearbit/Hunter (company logo, social profiles, etc.)

custom_fields

JSONB

NULLABLE

Industry-specific custom fields

consent_marketing

BOOLEAN

DEFAULT false

Consent to receive marketing emails (GDPR)

consent_sales

BOOLEAN

DEFAULT false

Consent to be contacted by sales (GDPR)

consent_ip

INET

NULLABLE

IP address when consent was given

consent_timestamp

TIMESTAMP

NULLABLE

When consent was given

created_at

TIMESTAMP

DEFAULT NOW(), INDEX

Lead capture timestamp

updated_at

TIMESTAMP

DEFAULT NOW()

Last update timestamp

last_activity_at

TIMESTAMP

NULLABLE, INDEX

Last engagement timestamp (for score decay)

TABLE: lead_sources

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique source identifier

tenant_id

UUID

FOREIGN KEY → tenants(id)

Multi-tenancy isolation

name

VARCHAR(255)

NOT NULL

Source name (e.g., "Contact Form", "BeeBot Chat", "LinkedIn Campaign")

type

ENUM

'form', 'chat', 'api', 'import', 'landing_page', 'webhook'

Source type

integration_config

JSONB

NULLABLE

Integration-specific config (webhook URL, API keys, field mappings)

is_active

BOOLEAN

DEFAULT true

Source status (active/paused)

total_leads_captured

INTEGER

DEFAULT 0

Lifetime lead count from this source

created_at

TIMESTAMP

DEFAULT NOW()

Source creation timestamp

TABLE: lead_scores

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique score record identifier

lead_id

UUID

FOREIGN KEY → leads(id), INDEX

Associated lead

previous_score

INTEGER

NOT NULL

Score before this change

new_score

INTEGER

NOT NULL

Score after this change

score_delta

INTEGER

NOT NULL

Score change (+/-)

reason

VARCHAR(255)

NOT NULL

Reason for score change (e.g., "Email opened", "Form submitted", "Inactivity decay")

category

ENUM

'demographic', 'behavioral', 'firmographic', 'engagement', 'decay'

Score change category

created_at

TIMESTAMP

DEFAULT NOW(), INDEX

When score changed

TABLE: lead_activities (Partitioned by month)

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique activity identifier

lead_id

UUID

FOREIGN KEY → leads(id), INDEX

Associated lead

activity_type

VARCHAR(100)

NOT NULL, INDEX

Activity type (email_open, link_click, form_submit, chat_message, etc.)

activity_data

JSONB

NULLABLE

Activity-specific data (email subject, clicked URL, form fields, etc.)

ip_address

INET

NULLABLE

IP address of activity

user_agent

TEXT

NULLABLE

Browser user agent

created_at

TIMESTAMP

DEFAULT NOW(), INDEX, PARTITION KEY

Activity timestamp (partition by month for performance)

TABLE: scoring_rules

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique rule identifier

tenant_id

UUID

FOREIGN KEY → tenants(id)

Multi-tenancy isolation

name

VARCHAR(255)

NOT NULL

Rule name (e.g., "C-Level Decision Maker", "Enterprise Company")

category

ENUM

'demographic', 'behavioral', 'firmographic', 'engagement'

Rule category

condition

JSONB

NOT NULL

Condition logic (e.g., {"field": "job_title", "operator": "contains", "value": "CEO"})

points

INTEGER

NOT NULL

Points to add/subtract when condition met

is_active

BOOLEAN

DEFAULT true

Rule status

priority

INTEGER

DEFAULT 0

Rule evaluation order (higher priority = evaluated first)

created_at

TIMESTAMP

DEFAULT NOW()

Rule creation timestamp

FRAME 9: SALES & LEAD GENERATION BUILDER - CRM INTEGRATION & TAGGING

Two-Way CRM Sync | Smart Tagging | Custom Fields | Pipeline Management | Activity Tracking

User Actions

Backend Services

AWS Infrastructure

Data Storage

GDPR Compliance

👤 SWIM LANE 1: USER ACTIONS

1. Connect CRM Platforms

Salesforce: OAuth 2.0 connection with API permissions (read/write contacts, leads, opportunities)
HubSpot: Private app integration with contact/company/deal scopes
Zoho CRM: OAuth connection with CRM.modules.ALL scope
Pipedrive: API token authentication for person/deal/activity sync
Field Mapping: Map UnifiedBeez fields to CRM custom fields

2. Configure Sync Rules

Sync Direction: One-way (UnifiedBeez → CRM) or Two-way (bidirectional)
Sync Frequency: Real-time (webhook), Hourly, Daily batch sync
Conflict Resolution: CRM wins, UnifiedBeez wins, or manual review
Sync Filters: Only sync leads with score >50 or stage = MQL/SQL
Deduplication: Match by email, prevent duplicate contact creation

3. Set Up Smart Tagging System

Behavioral Tags: Auto-tag based on actions (opened_pricing_page, downloaded_whitepaper)
Segmentation Tags: Industry, company_size, job_role, region
Lifecycle Tags: new_lead, engaged, nurture_ready, sales_ready
Campaign Tags: Tag leads by campaign source (webinar_q1_2024, linkedin_ads)
Custom Tags: Create unlimited custom tags with color coding

4. Manage Sales Pipelines

Pipeline Stages: Prospect → Contact Made → Demo Scheduled → Proposal Sent → Negotiation → Closed Won/Lost
Stage Automation: Auto-move leads between stages based on actions
Deal Value Tracking: Track estimated deal value and close probability
Sales Rep Assignment: Round-robin, territory-based, or manual assignment
Activity Logging: Track calls, emails, meetings, notes synced to CRM

⚙️ SWIM LANE 2: BACKEND SERVICES (NestJS + TypeScript)

🔗 CRM Integration Service

API Endpoints:

• POST /api/crm/connect - Initiate OAuth flow for CRM connection
• POST /api/crm/sync - Trigger manual sync job
• GET /api/crm/status - Check sync status and last sync time
• POST /api/crm/field-mapping - Configure field mappings
• DELETE /api/crm/disconnect - Revoke CRM connection

Supported Platforms:

• Salesforce: REST API v59.0, OAuth 2.0, SOQL queries
• HubSpot: CRM API v3, private apps, batch endpoints
• Zoho CRM: REST API v2, OAuth 2.0, bulk read/write
• Pipedrive: REST API v1, token auth, webhook support
• Generic Webhook: Custom webhook receiver for any CRM

🔄 Data Sync Service

API Endpoints:

• POST /api/sync/lead-to-crm - Sync single lead to CRM
• POST /api/sync/batch - Batch sync multiple leads
• POST /api/sync/crm-to-lead - Pull updates from CRM
• GET /api/sync/conflicts - Get sync conflicts requiring resolution
• POST /api/sync/resolve-conflict - Resolve sync conflict

Sync Logic:

• Real-time Sync: Webhook triggers on lead create/update
• Batch Sync: Scheduled jobs (hourly/daily) via EventBridge
• Conflict Detection: Compare updated_at timestamps
• Retry Logic: Exponential backoff for API failures (3 retries)
• Error Handling: Invalid field mapping errors logged to CloudWatch

🏷️ Tagging Service

API Endpoints:

• POST /api/tags/create - Create new tag with color/category
• POST /api/tags/apply - Apply tag(s) to lead(s)
• DELETE /api/tags/remove - Remove tag from lead
• GET /api/tags/search - Search leads by tag(s)
• POST /api/tags/auto-tag - Configure auto-tagging rules

Auto-Tagging Rules:

• Behavioral: Tag "pricing_interested" when pricing page visited 3+ times
• Engagement: Tag "highly_engaged" when score >80
• Lifecycle: Tag "hot_lead" when stage = SQL
• Temporal: Tag "stale_lead" when no activity for 60 days
• Boolean Logic: AND/OR/NOT conditions for complex rules

📋 Custom Fields Service

API Endpoints:

• POST /api/custom-fields/create - Create custom field definition
• PUT /api/custom-fields/:fieldId - Update field config
• POST /api/custom-fields/set-value - Set custom field value for lead
• GET /api/custom-fields/values/:leadId - Get all custom values for lead

Field Types:

• Text: Single-line text (max 255 chars)
• Number: Integer or decimal with min/max validation
• Date: ISO 8601 date/datetime
• Dropdown: Single-select from predefined options
• Multi-select: Multiple values from options
• Boolean: Yes/No checkbox
• URL: URL with validation

📊 Pipeline Management Service

API Endpoints:

• POST /api/pipeline/create - Create custom pipeline
• POST /api/pipeline/:id/stages - Add/reorder stages
• PUT /api/pipeline/move-lead - Move lead to different stage
• GET /api/pipeline/metrics - Pipeline conversion metrics
• POST /api/pipeline/forecast - Revenue forecasting

Pipeline Features:

• Stage Automation: Auto-advance on trigger (demo_booked → Demo Scheduled)
• Deal Tracking: Estimated value, close date, probability
• Conversion Metrics: Stage conversion rates, avg time in stage
• Revenue Forecast: Weighted pipeline value (probability × deal value)
• Lost Reason Tracking: Categorize why deals are lost

📝 Activity Tracking Service

API Endpoints:

• POST /api/activities/log - Log sales activity (call, email, meeting, note)
• GET /api/activities/:leadId - Get activity timeline for lead
• POST /api/activities/task - Create follow-up task
• PUT /api/activities/task/:id/complete - Mark task as complete

Activity Types:

• Call: Duration, outcome (connected, voicemail, no answer), notes
• Email: Subject, body, sent/received, opened/clicked
• Meeting: Date, duration, attendees, meeting notes, outcome
• Note: Free-form text notes about lead interaction
• Task: Follow-up tasks with due date, priority, assignee
• CRM Sync: All activities synced to connected CRM

☁️ SWIM LANE 3: AWS INFRASTRUCTURE

ECS Fargate - CRM Services

Task Definition: crm-integration-service, data-sync-service, pipeline-service
CPU/Memory: 1 vCPU, 2GB RAM per task
Auto Scaling: 2-10 tasks based on SQS sync queue depth
Scaling Metric: ApproximateNumberOfMessagesVisible >50
Health Check: /health endpoint every 30s
Deployment: Rolling update, 50% minimum healthy

RDS PostgreSQL 15.4 - CRM Data

Instance: db.r6g.xlarge (Multi-AZ for HA)
Storage: 500GB GP3 SSD (auto-scaling to 1TB)
Tables: crm_connections, sync_logs, tags, custom_fields, pipelines, activities
Indexes: lead_id, tag_name, activity_type, created_at
Partitioning: activities partitioned by quarter
Backup: Automated daily snapshots, 7-day retention

SQS - Sync Queues

crm-sync-queue: Real-time lead sync events (FIFO, retention 14 days)
batch-sync-queue: Scheduled batch sync jobs
webhook-receiver-queue: Incoming webhooks from CRMs
Visibility Timeout: 10 minutes (CRM API calls can be slow)
Dead Letter Queue: 3 retries before DLQ
Throughput: ~200 messages/second peak

ElastiCache Redis - CRM Cache

Node Type: cache.r6g.large (Cluster Mode enabled)
Use Cases:
• CRM OAuth tokens (TTL = token expiry, auto-refresh)
• Field mapping cache (TTL 24h, invalidate on update)
• Tag lookup cache (TTL 1h)
• CRM API rate limit tracking (sliding window)
• Recent sync status (TTL 5 min)

EventBridge - Scheduled Sync

hourly-crm-sync: cron(0 * * * ? *) - Hourly batch sync
daily-full-sync: cron(0 2 * * ? *) - 2 AM daily full reconciliation
weekly-cleanup: cron(0 3 ? * SUN *) - Clean old sync logs
Target: Lambda function → SQS batch-sync-queue
Retry Policy: 3 retries with exponential backoff

Lambda Functions

crm-sync-worker: Process sync jobs, call CRM APIs (timeout 5min, 2GB RAM)
webhook-processor: Process incoming CRM webhooks (timeout 30s, 512MB RAM)
auto-tag-evaluator: Evaluate auto-tagging rules hourly (1GB RAM)
sync-conflict-detector: Detect and flag sync conflicts (1GB RAM)
Runtime: Node.js 20.x, bundled with esbuild

SNS - Sync Notifications

sync-failure-alerts: Notify admins when sync fails (>5% error rate)
conflict-notifications: Alert when sync conflicts require resolution
webhook-errors: Notify when CRM webhook fails to process
Subscribers: Email, Slack webhooks, PagerDuty (critical only)
Filtering: Severity-based filtering (info, warning, critical)

CloudWatch - Monitoring

Metrics: Sync success rate, API call latency, sync queue depth, conflicts/hour
Alarms: Sync failure rate >5%, CRM API timeout >10s, queue depth >500
Logs: Sync events, field mapping errors, CRM API responses
Dashboards: CRM sync health, tag usage, pipeline metrics
Retention: 90 days log retention

Secrets Manager - CRM Credentials

Secrets Stored (per tenant):
• unifiedbeez/crm/salesforce-oauth (access + refresh tokens)
• unifiedbeez/crm/hubspot-api-key (private app key)
• unifiedbeez/crm/zoho-oauth (OAuth credentials)
• unifiedbeez/crm/pipedrive-token (API token)
Encryption: KMS key (unifiedbeez-secrets-key)
Rotation: OAuth tokens refreshed automatically before expiry

🗄️ SWIM LANE 4: DATABASE SCHEMA & RELATIONSHIPS

TABLE: crm_connections

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique connection identifier

tenant_id

UUID

FOREIGN KEY → tenants(id), UNIQUE

Multi-tenancy isolation (one CRM per tenant)

crm_platform

ENUM

'salesforce', 'hubspot', 'zoho', 'pipedrive', 'custom'

Connected CRM platform

auth_type

ENUM

'oauth2', 'api_token', 'api_key'

Authentication method used

credentials_secret_arn

TEXT

NOT NULL

AWS Secrets Manager ARN for credentials

sync_direction

ENUM

'one_way_to_crm', 'one_way_from_crm', 'two_way'

Sync direction configuration

sync_frequency

ENUM

'realtime', 'hourly', 'daily'

How often to sync data

conflict_resolution

ENUM

'crm_wins', 'unifiedbeez_wins', 'manual'

How to handle sync conflicts

field_mappings

JSONB

NOT NULL

Field mapping config (UnifiedBeez field → CRM field)

sync_filters

JSONB

NULLABLE

Conditions for syncing (e.g., only sync if score >50)

is_active

BOOLEAN

DEFAULT true

Connection status

last_sync_at

TIMESTAMP

NULLABLE

Last successful sync timestamp

webhook_url

TEXT

NULLABLE

Webhook URL for receiving CRM updates

created_at

TIMESTAMP

DEFAULT NOW()

Connection creation timestamp

TABLE: sync_logs

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique log identifier

crm_connection_id

UUID

FOREIGN KEY → crm_connections(id), INDEX

Associated CRM connection

lead_id

UUID

FOREIGN KEY → leads(id), INDEX

Lead being synced

sync_direction

ENUM

'to_crm', 'from_crm'

Direction of this sync

sync_type

ENUM

'create', 'update', 'delete'

Type of sync operation

status

ENUM

'success', 'failed', 'conflict', 'skipped'

Sync result

crm_record_id

VARCHAR(255)

NULLABLE

CRM's record ID (for mapping)

error_message

TEXT

NULLABLE

Error details if status = failed

conflict_details

JSONB

NULLABLE

Field-level conflict info (UnifiedBeez value vs CRM value)

synced_fields

JSONB

NULLABLE

Fields that were synced in this operation

api_response

JSONB

NULLABLE

CRM API response (for debugging)

created_at

TIMESTAMP

DEFAULT NOW(), INDEX

Sync attempt timestamp

TABLE: tags

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique tag identifier

tenant_id

UUID

FOREIGN KEY → tenants(id), INDEX

Multi-tenancy isolation

name

VARCHAR(100)

NOT NULL, INDEX

Tag name (e.g., "hot_lead", "enterprise", "churned")

category

ENUM

'behavioral', 'segmentation', 'lifecycle', 'campaign', 'custom'

Tag category

color

VARCHAR(7)

DEFAULT '#3B82F6'

Hex color for UI display

description

TEXT

NULLABLE

Tag description/purpose

auto_apply_rules

JSONB

NULLABLE

Auto-tagging rules (conditions for automatic application)

is_system_tag

BOOLEAN

DEFAULT false

System tags cannot be deleted by users

created_at

TIMESTAMP

DEFAULT NOW()

Tag creation timestamp

TABLE: lead_tags (Many-to-Many Relationship)

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique relationship identifier

lead_id

UUID

FOREIGN KEY → leads(id), INDEX

Lead being tagged

tag_id

UUID

FOREIGN KEY → tags(id), INDEX

Applied tag

applied_by

ENUM

'user', 'auto_rule', 'system'

How tag was applied

applied_by_user_id

UUID

FOREIGN KEY → users(id), NULLABLE

User who applied tag (if manual)

created_at

TIMESTAMP

DEFAULT NOW()

When tag was applied

-

UNIQUE (lead_id, tag_id)

Prevent duplicate tag applications

TABLE: custom_fields

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique field identifier

tenant_id

UUID

FOREIGN KEY → tenants(id), INDEX

Multi-tenancy isolation

field_name

VARCHAR(100)

NOT NULL

Field name (e.g., "annual_contract_value", "use_case")

field_type

ENUM

'text', 'number', 'date', 'dropdown', 'multi_select', 'boolean', 'url'

Data type of field

options

JSONB

NULLABLE

Options for dropdown/multi_select (array of strings)

validation_rules

JSONB

NULLABLE

Validation config (min/max for numbers, regex for text)

is_required

BOOLEAN

DEFAULT false

Whether field must have value

display_order

INTEGER

DEFAULT 0

Order in UI

created_at

TIMESTAMP

DEFAULT NOW()

Field definition creation

TABLE: pipelines

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique pipeline identifier

tenant_id

UUID

FOREIGN KEY → tenants(id), INDEX

Multi-tenancy isolation

name

VARCHAR(255)

NOT NULL

Pipeline name (e.g., "Sales Pipeline", "Enterprise Deals")

stages

JSONB

NOT NULL

Array of stage objects [{name, order, probability}]

is_default

BOOLEAN

DEFAULT false

Default pipeline for new leads

created_at

TIMESTAMP

DEFAULT NOW()

Pipeline creation timestamp

FRAME 10: SALES & LEAD GENERATION BUILDER - EMAIL SENDER VERIFICATION

SPF/DKIM/DMARC Setup | Domain Authentication | Sender Reputation | Deliverability Monitoring

User Actions

Backend Services

AWS Infrastructure

Data Storage

GDPR Compliance

👤 SWIM LANE 1: USER ACTIONS

1. Configure Sender Domains

Add Domain: Register custom domain for sending (e.g., sales@company.com)
DNS Verification: Add TXT record to verify domain ownership
Multiple Senders: Configure multiple sender addresses (sales, marketing, support)
Default Sender: Set default "From" address for campaigns
Reply-To Configuration: Specify reply-to address (can differ from sender)

2. Set Up Email Authentication

SPF Record: Add SPF TXT record to authorize AWS SES servers
DKIM Setup: Generate DKIM keys and add CNAME records to DNS
DMARC Policy: Configure DMARC record (quarantine/reject policy)
Verification Status: Monitor DNS propagation and verification status
Alignment Check: Ensure SPF/DKIM alignment with DMARC requirements

3. Monitor Sender Reputation

Reputation Score: View sender reputation score (0-100) from AWS SES
Bounce Rate: Monitor bounce rate (hard/soft bounces, target <5%)
Complaint Rate: Track spam complaint rate (target <0.1%)
Suppression List: View suppressed emails (bounces, complaints, unsubscribes)
Blocklist Check: Check if domain/IP is on public email blocklists

4. Optimize Deliverability

Warm-up Schedule: Gradual sending volume increase for new domains (10/day → 1000/day over 2 weeks)
Email Testing: Send test emails to check inbox placement (Gmail, Outlook, Yahoo)
Content Analysis: Scan emails for spam trigger words/phrases
Authentication Check: Verify SPF/DKIM/DMARC pass on test emails
Feedback Loops: Subscribe to ISP feedback loops for complaint notifications

⚙️ SWIM LANE 2: BACKEND SERVICES (NestJS + TypeScript)

📧 Domain Verification Service

API Endpoints:

• POST /api/sender-domains/add - Add new sender domain
• POST /api/sender-domains/:id/verify - Verify domain via DNS TXT record
• GET /api/sender-domains/:id/dns-records - Get required DNS records
• DELETE /api/sender-domains/:id - Remove sender domain
• GET /api/sender-domains/status - Get verification status for all domains

Verification Logic:

• DNS Lookup: Query TXT records for verification token
• AWS SES Integration: VerifyDomainIdentity API call
• Propagation Check: Retry DNS lookup every 5 minutes (max 72 hours)
• Verification Token: Random 32-character alphanumeric string
• Auto-verification: Background job checks pending domains hourly

🔐 Email Authentication Service

API Endpoints:

• POST /api/auth/generate-dkim - Generate DKIM key pair for domain
• GET /api/auth/spf-record - Get SPF record configuration
• GET /api/auth/dmarc-record - Get DMARC record configuration
• POST /api/auth/verify-alignment - Check SPF/DKIM/DMARC alignment
• GET /api/auth/status/:domain - Get authentication status

Authentication Setup:

• SPF: v=spf1 include:amazonses.com ~all
• DKIM: 2048-bit RSA key, AWS SES manages keys
• DMARC: v=DMARC1; p=quarantine; rua=mailto:dmarc@company.com
• Alignment Check: From domain must match SPF/DKIM signing domain
• DNS Propagation: Check nameservers for record updates

📊 Reputation Monitoring Service

API Endpoints:

• GET /api/reputation/score - Get sender reputation score
• GET /api/reputation/metrics - Bounce/complaint rates
• GET /api/reputation/suppression-list - View suppressed emails
• POST /api/reputation/blocklist-check - Check domain against blocklists
• GET /api/reputation/trends - Historical reputation data

Reputation Metrics:

• Bounce Rate: (bounces / sent) × 100 (target <5%)
• Complaint Rate: (complaints / sent) × 100 (target <0.1%)
• Reputation Score: AWS SES account reputation (0-100)
• Blocklist Status: Check against Spamhaus, Barracuda, SURBL
• Sending Limits: Current SES sending quota (14/sec default)

📈 Deliverability Service

API Endpoints:

• POST /api/deliverability/test-email - Send test email to seed accounts
• POST /api/deliverability/analyze-content - Scan for spam triggers
• GET /api/deliverability/inbox-placement - Inbox vs spam folder rate
• POST /api/deliverability/warmup-schedule - Create domain warmup plan
• GET /api/deliverability/recommendations - Get improvement suggestions

Deliverability Checks:

• Spam Score: Content analysis (0-10, <3 is good)
• Inbox Placement: Gmail (85%+), Outlook (80%+), Yahoo (75%+)
• Authentication Pass Rate: SPF/DKIM/DMARC pass percentage
• Engagement Rate: Opens + clicks (indicates good deliverability)
• Warmup Progress: Track daily send volume vs warmup schedule

🚨 Bounce & Complaint Handler

API Endpoints:

• POST /api/bounces/webhook - SNS webhook for bounce notifications
• POST /api/complaints/webhook - SNS webhook for complaints
• GET /api/bounces/:leadId - Get bounce history for lead
• POST /api/suppression/add - Manually suppress email
• DELETE /api/suppression/:email - Remove from suppression list

Handling Logic:

• Hard Bounce: Permanently suppress email (invalid, doesn't exist)
• Soft Bounce: Retry 3 times over 72 hours (mailbox full, temp error)
• Complaint: Immediately suppress + mark lead as unsubscribed
• Suppression Sync: Update leads table (email_valid = false)
• SNS Topic: ses-bounce-topic, ses-complaint-topic

🔍 DNS Management Service

API Endpoints:

• POST /api/dns/lookup - Perform DNS record lookup
• GET /api/dns/nameservers/:domain - Get domain nameservers
• POST /api/dns/validate-records - Validate all required DNS records
• GET /api/dns/propagation-status - Check DNS propagation globally

DNS Operations:

• TXT Lookup: Verification token, SPF, DMARC records
• CNAME Lookup: DKIM keys (3 CNAME records per domain)
• MX Lookup: Verify inbound email routing
• NS Lookup: Get authoritative nameservers
• Global Check: Query DNS from multiple regions (8 locations)

☁️ SWIM LANE 3: AWS INFRASTRUCTURE

ECS Fargate - Sender Services

Task Definition: domain-verification-service, email-auth-service, reputation-service
CPU/Memory: 0.5 vCPU, 1GB RAM per task
Auto Scaling: 2-8 tasks based on CPU utilization
Scaling Metric: CPUUtilization >70%
Health Check: /health endpoint every 30s
Deployment: Rolling update, 50% minimum healthy

AWS SES - Email Sending

Region: us-east-1 (production), us-west-2 (backup)
Sending Limits: 14 emails/sec, 50,000 emails/day (default)
Domain Identities: SPF/DKIM/DMARC verified domains
Configuration Sets: Track opens/clicks, bounce/complaint events
Dedicated IPs: Optional dedicated IP pools for high-volume senders
Reputation: Account-level reputation dashboard

RDS PostgreSQL 15.4 - Sender Data

Instance: db.r6g.large (Multi-AZ for HA)
Storage: 200GB GP3 SSD (auto-scaling to 500GB)
Tables: sender_domains, dns_records, bounce_logs, complaint_logs, reputation_history
Indexes: domain, email, bounce_type, created_at
Partitioning: bounce_logs/complaint_logs partitioned by month
Backup: Automated daily snapshots, 7-day retention

SNS - SES Event Notifications

ses-bounce-topic: Hard/soft bounce notifications from SES
ses-complaint-topic: Spam complaint notifications from SES
ses-delivery-topic: Successful delivery confirmations
Subscribers: Lambda functions, SQS queues, HTTP/S endpoints
Message Format: JSON with bounce type, recipient, timestamp
Retry Policy: 3 retries with exponential backoff

SQS - Event Processing Queues

bounce-processing-queue: Process bounce events (retention 14 days)
complaint-processing-queue: Process complaint events
dns-verification-queue: Domain verification checks
Visibility Timeout: 2 minutes (DNS lookups can be slow)
Dead Letter Queue: 3 retries before DLQ
Throughput: ~100 messages/second peak

ElastiCache Redis - DNS Cache

Node Type: cache.r6g.large (Cluster Mode enabled)
Use Cases:
• DNS lookup results (TTL 1 hour)
• Domain verification status (TTL 5 min)
• Reputation scores (TTL 15 min)
• Suppression list cache (TTL 10 min)
• Blocklist check results (TTL 24 hours)

Lambda Functions

bounce-handler: Process bounce SNS events (timeout 30s, 512MB RAM)
complaint-handler: Process complaint SNS events (timeout 30s, 512MB RAM)
dns-verification-worker: Check domain verification status hourly (1GB RAM)
reputation-calculator: Calculate daily reputation metrics (1GB RAM)
blocklist-checker: Check domains against public blocklists (2GB RAM)
Runtime: Node.js 20.x, bundled with esbuild

EventBridge - Scheduled Jobs

hourly-dns-check: cron(0 * * * ? *) - Verify pending domains
daily-reputation-report: cron(0 8 * * ? *) - Calculate daily metrics
daily-blocklist-check: cron(0 9 * * ? *) - Check all domains
weekly-suppression-cleanup: cron(0 2 ? * SUN *) - Clean old records
Target: Lambda functions
Retry Policy: 3 retries with exponential backoff

CloudWatch - Monitoring

Metrics: Bounce rate, complaint rate, send quota utilization, reputation score
Alarms: Bounce rate >5%, complaint rate >0.1%, reputation score <80
Logs: DNS verification attempts, bounce/complaint events, SES API calls
Dashboards: Sender health, domain status, deliverability metrics
Retention: 90 days log retention
SNS Alerts: Critical reputation issues (score <60)

Secrets Manager - DKIM Keys

Secrets Stored:
• unifiedbeez/ses/dkim-private-keys (per domain)
• unifiedbeez/blocklist-api-keys (for premium blocklist checks)
• unifiedbeez/dns-api-credentials (DNS provider APIs)
Encryption: KMS key (unifiedbeez-secrets-key)
Rotation: DKIM keys rotated every 6 months
Access Control: IAM role-based (ECS tasks only)

🗄️ SWIM LANE 4: DATABASE SCHEMA & RELATIONSHIPS

TABLE: sender_domains

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique domain identifier

tenant_id

UUID

FOREIGN KEY → tenants(id), INDEX

Multi-tenancy isolation

domain

VARCHAR(255)

NOT NULL, INDEX

Domain name (e.g., company.com)

sender_email

VARCHAR(255)

NOT NULL

Full sender email (e.g., sales@company.com)

reply_to_email

VARCHAR(255)

NULLABLE

Reply-to address (if different from sender)

verification_token

VARCHAR(64)

NOT NULL, UNIQUE

DNS TXT record verification token

verification_status

ENUM

'pending', 'verified', 'failed'

Domain verification status

verified_at

TIMESTAMP

NULLABLE

When domain was verified

spf_configured

BOOLEAN

DEFAULT false

SPF record detected

dkim_configured

BOOLEAN

DEFAULT false

DKIM keys configured (3 CNAME records)

dmarc_configured

BOOLEAN

DEFAULT false

DMARC policy configured

dkim_tokens

JSONB

NULLABLE

Array of 3 DKIM tokens for CNAME records

is_default

BOOLEAN

DEFAULT false

Default sender for campaigns

is_active

BOOLEAN

DEFAULT true

Domain status (active/paused)

reputation_score

INTEGER

DEFAULT 100

Current reputation score (0-100)

warmup_status

ENUM

'not_started', 'in_progress', 'completed'

Domain warmup phase

warmup_daily_limit

INTEGER

NULLABLE

Current daily send limit during warmup

created_at

TIMESTAMP

DEFAULT NOW()

Domain creation timestamp

updated_at

TIMESTAMP

DEFAULT NOW()

Last update timestamp

TABLE: dns_records

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique record identifier

sender_domain_id

UUID

FOREIGN KEY → sender_domains(id), INDEX

Associated sender domain

record_type

ENUM

'TXT', 'CNAME', 'MX'

DNS record type

purpose

ENUM

'verification', 'spf', 'dkim', 'dmarc'

Record purpose

record_name

VARCHAR(255)

NOT NULL

DNS record name (e.g., _amazonses.company.com)

record_value

TEXT

NOT NULL

DNS record value

is_configured

BOOLEAN

DEFAULT false

Record detected in DNS

last_checked_at

TIMESTAMP

NULLABLE

Last DNS lookup timestamp

created_at

TIMESTAMP

DEFAULT NOW()

Record creation timestamp

TABLE: bounce_logs (Partitioned by month)

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique bounce log identifier

sender_domain_id

UUID

FOREIGN KEY → sender_domains(id), INDEX

Associated sender domain

lead_id

UUID

FOREIGN KEY → leads(id), INDEX

Recipient lead

email

VARCHAR(255)

NOT NULL, INDEX

Bounced email address

bounce_type

ENUM

'hard', 'soft', 'transient'

Type of bounce

bounce_subtype

VARCHAR(100)

NULLABLE

Detailed bounce reason (e.g., 'mailbox_full', 'invalid_recipient')

diagnostic_code

TEXT

NULLABLE

SMTP diagnostic message

message_id

VARCHAR(255)

NULLABLE

SES message ID

sns_message

JSONB

NULLABLE

Full SNS bounce notification

created_at

TIMESTAMP

DEFAULT NOW(), INDEX, PARTITION KEY

Bounce timestamp (partition by month)

TABLE: complaint_logs (Partitioned by month)

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique complaint log identifier

sender_domain_id

UUID

FOREIGN KEY → sender_domains(id), INDEX

Associated sender domain

lead_id

UUID

FOREIGN KEY → leads(id), INDEX

Recipient lead who complained

email

VARCHAR(255)

NOT NULL, INDEX

Email that complained

complaint_type

VARCHAR(50)

NULLABLE

Complaint type (spam, abuse, fraud, etc.)

feedback_type

VARCHAR(50)

NULLABLE

ISP feedback loop type

user_agent

TEXT

NULLABLE

Email client user agent

message_id

VARCHAR(255)

NULLABLE

SES message ID

sns_message

JSONB

NULLABLE

Full SNS complaint notification

created_at

TIMESTAMP

DEFAULT NOW(), INDEX, PARTITION KEY

Complaint timestamp (partition by month)

TABLE: reputation_history

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique history record identifier

sender_domain_id

UUID

FOREIGN KEY → sender_domains(id), INDEX

Associated sender domain

date

DATE

NOT NULL, INDEX

Metrics date

emails_sent

INTEGER

DEFAULT 0

Total emails sent this day

emails_delivered

INTEGER

DEFAULT 0

Successfully delivered

hard_bounces

INTEGER

DEFAULT 0

Hard bounce count

soft_bounces

INTEGER

DEFAULT 0

Soft bounce count

complaints

INTEGER

DEFAULT 0

Spam complaint count

bounce_rate

DECIMAL(5,2)

DEFAULT 0.00

Bounce rate percentage

complaint_rate

DECIMAL(5,2)

DEFAULT 0.00

Complaint rate percentage

reputation_score

INTEGER

DEFAULT 100

Daily reputation score snapshot

created_at

TIMESTAMP

DEFAULT NOW()

Record creation timestamp

-

UNIQUE (sender_domain_id, date)

One record per domain per day

FRAME 11: SALES & LEAD GENERATION BUILDER - SMART CONSENT & COMPLIANCE

Double Opt-In | Preference Centers | Unsubscribe Management | Consent Tracking | Privacy Compliance

User Actions

Backend Services

AWS Infrastructure

Data Storage

GDPR Compliance

👤 SWIM LANE 1: USER ACTIONS

1. Configure Consent Collection

Double Opt-In: Require email confirmation for new subscribers
Consent Checkboxes: Separate opt-ins for marketing, sales, newsletter
Granular Consent: Allow subscribers to choose topic preferences (product updates, promotions, events)
Pre-checked Disabled: All consent checkboxes unchecked by default (GDPR-compliant)
Clear Language: Plain-language consent text (e.g., "I agree to receive marketing emails")

2. Design Preference Center

Custom Branding: Logo, colors, company name for preference center page
Communication Preferences: Toggle email, SMS, WhatsApp channels on/off
Topic Subscriptions: Subscribe/unsubscribe from specific email topics
Frequency Control: Choose email frequency (daily, weekly, monthly, digest)
Profile Update: Allow subscribers to update name, email, preferences

3. Set Up Unsubscribe Flow

One-Click Unsubscribe: RFC 8058 List-Unsubscribe header in emails
Unsubscribe Page: Custom branded confirmation page
Exit Survey: Optional feedback form (Why are you unsubscribing?)
Partial Unsubscribe: Option to reduce frequency instead of full unsubscribe
Re-subscribe Option: Link to re-subscribe if unsubscribed by mistake

4. Monitor Compliance

Consent Audit Log: View all consent changes with timestamps and IP addresses
Opt-In Rate: Track percentage of leads who confirm double opt-in
Unsubscribe Rate: Monitor unsubscribe rate by campaign (target <1%)
Compliance Dashboard: GDPR/CAN-SPAM/CASL compliance status
Export Consent Records: Generate compliance reports for audits

⚙️ SWIM LANE 2: BACKEND SERVICES (NestJS + TypeScript)

✅ Consent Management Service

API Endpoints:

• POST /api/consent/grant - Record consent grant (opt-in)
• POST /api/consent/revoke - Record consent revocation (opt-out)
• GET /api/consent/:leadId - Get consent status for lead
• GET /api/consent/audit-log/:leadId - Get consent change history
• POST /api/consent/verify-token - Verify double opt-in token

Consent Logic:

• Double Opt-In: Generate unique token, send confirmation email, expire after 72 hours
• Consent Metadata: Capture IP, user agent, timestamp, checkbox text, form URL
• Granular Consent: Track consent per channel (email, sms, whatsapp) and topic
• Consent Versioning: Track consent under specific privacy policy version
• Withdrawal: Immediate consent revocation, stop all campaigns instantly

⚙️ Preference Center Service

API Endpoints:

• GET /api/preferences/:token - Load preference center (authenticated by token)
• PUT /api/preferences/update - Update communication preferences
• POST /api/preferences/generate-link - Generate magic link for preference center
• GET /api/preferences/topics - Get available subscription topics

Preference Features:

• Magic Link: Tokenized URL (no login required, expires 30 days)
• Channel Toggle: Enable/disable email, SMS, WhatsApp independently
• Topic Subscriptions: Subscribe to specific topics (newsletters, promotions, events)
• Frequency Control: Choose digest frequency (immediate, daily, weekly)
• Profile Update: Update name, email, phone (with re-verification)

🚫 Unsubscribe Service

API Endpoints:

• POST /api/unsubscribe/:token - Unsubscribe via email link
• POST /api/unsubscribe/list-unsubscribe - RFC 8058 List-Unsubscribe handler
• POST /api/unsubscribe/feedback - Submit unsubscribe reason
• POST /api/resubscribe/:token - Re-subscribe if unsubscribed by mistake
• GET /api/unsubscribe/status/:leadId - Check unsubscribe status

Unsubscribe Logic:

• One-Click: List-Unsubscribe header (mailto + HTTPS URL)
• Instant Processing: Unsubscribe effective immediately (no delay)
• Global Unsubscribe: Option to unsubscribe from all emails vs specific topics
• Feedback Collection: Optional exit survey (frequency, content, relevance)
• Suppression Sync: Add to suppression list, mark consent_marketing = false

📋 Compliance Reporting Service

API Endpoints:

• GET /api/compliance/report - Generate compliance report
• GET /api/compliance/consent-stats - Opt-in/opt-out statistics
• GET /api/compliance/audit-export - Export consent audit log (CSV/JSON)
• POST /api/compliance/validate - Validate compliance status

Compliance Metrics:

• Opt-In Rate: (confirmed / sent confirmation emails) × 100
• Unsubscribe Rate: (unsubscribes / emails sent) × 100 (target <1%)
• Consent Coverage: % of leads with explicit consent records
• GDPR Compliance Score: Automated checks (consent metadata, retention, DPAs)
• Audit Readiness: Exportable consent records with full metadata

🔐 Privacy Policy Service

API Endpoints:

• POST /api/privacy-policy/create-version - Create new policy version
• GET /api/privacy-policy/current - Get current active policy
• GET /api/privacy-policy/history - List all policy versions
• POST /api/privacy-policy/notify-changes - Send policy update notifications

Policy Features:

• Version Control: Track policy changes with version numbers (1.0, 1.1, 2.0)
• Consent Linking: Link consent records to specific policy version
• Change Notifications: Notify subscribers of material policy changes
• Re-consent Required: Optionally require re-consent for major policy changes
• Public Archive: All policy versions publicly accessible

📊 Consent Analytics Service

API Endpoints:

• GET /api/analytics/consent-funnel - Consent funnel metrics
• GET /api/analytics/unsubscribe-reasons - Top unsubscribe reasons
• GET /api/analytics/preference-trends - Preference center usage trends
• GET /api/analytics/consent-by-source - Consent rates by lead source

Analytics Metrics:

• Consent Funnel: Form submit → Confirmation sent → Confirmed (double opt-in)
• Unsubscribe Reasons: Too frequent (40%), not relevant (30%), never subscribed (20%)
• Preference Center Usage: Visits, updates, partial unsubscribes
• Consent by Source: Compare opt-in rates across lead sources
• Re-engagement: Track re-subscribe rate after unsubscribe

☁️ SWIM LANE 3: AWS INFRASTRUCTURE

ECS Fargate - Consent Services

Task Definition: consent-service, preference-center-service, unsubscribe-service
CPU/Memory: 0.5 vCPU, 1GB RAM per task
Auto Scaling: 2-8 tasks based on request count
Scaling Metric: RequestCountPerTarget >500
Health Check: /health endpoint every 30s
Deployment: Rolling update, 50% minimum healthy

RDS PostgreSQL 15.4 - Consent Data

Instance: db.r6g.large (Multi-AZ for HA)
Storage: 200GB GP3 SSD (auto-scaling to 500GB)
Tables: consent_records, consent_audit_log, preference_topics, unsubscribe_feedback, privacy_policies
Indexes: lead_id, consent_type, created_at, policy_version
Partitioning: consent_audit_log partitioned by quarter
Backup: Automated daily snapshots, 7-day retention

ElastiCache Redis - Consent Cache

Node Type: cache.r6g.large (Cluster Mode enabled)
Use Cases:
• Consent status cache (TTL 5 min, invalidate on update)
• Double opt-in tokens (TTL 72 hours)
• Preference center magic links (TTL 30 days)
• Unsubscribe tokens (TTL 90 days)
• Privacy policy current version (TTL 1 hour)

SQS - Consent Processing Queues

consent-confirmation-queue: Double opt-in confirmation emails (retention 14 days)
unsubscribe-processing-queue: Process unsubscribe requests
preference-update-queue: Sync preference changes to campaigns
Visibility Timeout: 1 minute
Dead Letter Queue: 3 retries before DLQ
Throughput: ~200 messages/second peak

Lambda Functions

consent-confirmation-sender: Send double opt-in emails (timeout 30s, 512MB RAM)
unsubscribe-processor: Process unsubscribe requests (timeout 30s, 512MB RAM)
consent-audit-logger: Log all consent changes (timeout 15s, 256MB RAM)
preference-sync-worker: Sync preference changes to campaigns (1GB RAM)
compliance-report-generator: Generate compliance reports (5min timeout, 2GB RAM)
Runtime: Node.js 20.x, bundled with esbuild

S3 - Compliance Documents

Bucket: unifiedbeez-compliance-docs
Storage:
• Privacy policy versions (HTML + PDF)
• Terms of service versions
• Consent audit exports (CSV + JSON)
• Compliance reports (monthly/quarterly/annual)
Versioning: Enabled (track all document changes)
Lifecycle: Transition to Glacier after 2 years, retain 7 years

CloudFront - Preference Center CDN

Distribution: Global edge locations (200+ POPs)
Origin: S3 bucket (static assets) + ECS Fargate (dynamic API)
Caching: Static assets 1 year, API responses no-cache
SSL/TLS: TLS 1.3, custom domain (preferences.unifiedbeez.com)
Performance: <100ms response time globally
Security: AWS WAF integration, DDoS protection

EventBridge - Scheduled Jobs

daily-consent-metrics: cron(0 8 * * ? *) - Calculate daily consent stats
weekly-compliance-report: cron(0 9 ? * MON *) - Generate weekly report
monthly-audit-export: cron(0 2 1 * ? *) - Export consent audit logs
token-cleanup: cron(0 3 * * ? *) - Delete expired double opt-in tokens
Target: Lambda functions
Retry Policy: 3 retries with exponential backoff

CloudWatch - Monitoring

Metrics: Opt-in rate, unsubscribe rate, preference center visits, consent changes/day
Alarms: Unsubscribe rate >1%, opt-in rate <30%, API errors >5%
Logs: Consent grants/revocations, preference updates, unsubscribe requests
Dashboards: Consent funnel, compliance health, preference trends
Retention: 90 days log retention
Audit Trail: All consent changes logged for 7 years (regulatory requirement)

Secrets Manager - API Keys

Secrets Stored:
• unifiedbeez/consent/jwt-signing-key (magic link tokens)
• unifiedbeez/consent/encryption-key (PII encryption)
Encryption: KMS key (unifiedbeez-secrets-key)
Rotation: JWT keys rotated every 90 days
Access Control: IAM role-based (ECS tasks + Lambda only)

🗄️ SWIM LANE 4: DATABASE SCHEMA & RELATIONSHIPS

TABLE: consent_records

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique consent record identifier

lead_id

UUID

FOREIGN KEY → leads(id), INDEX

Associated lead

consent_type

ENUM

'marketing', 'sales', 'newsletter', 'sms', 'whatsapp'

Type of consent

consent_status

ENUM

'pending', 'granted', 'revoked'

Current consent status

double_opt_in_required

BOOLEAN

DEFAULT true

Whether double opt-in is required

double_opt_in_confirmed_at

TIMESTAMP

NULLABLE

When confirmation email was clicked

consent_method

ENUM

'form_checkbox', 'api', 'import', 'admin'

How consent was obtained

consent_text

TEXT

NOT NULL

Exact text of consent checkbox/agreement

privacy_policy_version

VARCHAR(20)

NOT NULL

Privacy policy version at time of consent

form_url

TEXT

NULLABLE

URL of form where consent was given

ip_address

INET

NULLABLE

IP address when consent was granted

user_agent

TEXT

NULLABLE

Browser user agent

geolocation

JSONB

NULLABLE

Country, region, city from IP lookup

granted_at

TIMESTAMP

NULLABLE

When consent was granted

revoked_at

TIMESTAMP

NULLABLE

When consent was revoked (unsubscribed)

revocation_reason

TEXT

NULLABLE

Reason for unsubscribe (from exit survey)

created_at

TIMESTAMP

DEFAULT NOW(), INDEX

Record creation timestamp

updated_at

TIMESTAMP

DEFAULT NOW()

Last update timestamp

-

UNIQUE (lead_id, consent_type)

One consent record per lead per type

TABLE: consent_audit_log (Partitioned by quarter)

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique audit log identifier

consent_record_id

UUID

FOREIGN KEY → consent_records(id), INDEX

Associated consent record

lead_id

UUID

FOREIGN KEY → leads(id), INDEX

Associated lead

action

ENUM

'granted', 'confirmed', 'revoked', 'updated'

Action performed

previous_status

ENUM

'pending', 'granted', 'revoked', NULL

Status before change

new_status

ENUM

'pending', 'granted', 'revoked'

Status after change

changed_by

ENUM

'user', 'system', 'admin'

Who initiated the change

ip_address

INET

NULLABLE

IP address of change

user_agent

TEXT

NULLABLE

Browser user agent

metadata

JSONB

NULLABLE

Additional context (form URL, campaign ID, etc.)

created_at

TIMESTAMP

DEFAULT NOW(), INDEX, PARTITION KEY

Audit log timestamp (partition by quarter)

TABLE: preference_topics

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique topic identifier

tenant_id

UUID

FOREIGN KEY → tenants(id), INDEX

Multi-tenancy isolation

topic_name

VARCHAR(255)

NOT NULL

Topic name (e.g., "Product Updates", "Weekly Newsletter", "Promotions")

topic_description

TEXT

NULLABLE

Description shown in preference center

is_default_subscribed

BOOLEAN

DEFAULT false

Auto-subscribe new leads to this topic

display_order

INTEGER

DEFAULT 0

Display order in preference center

is_active

BOOLEAN

DEFAULT true

Topic status

created_at

TIMESTAMP

DEFAULT NOW()

Topic creation timestamp

TABLE: lead_topic_subscriptions (Many-to-Many)

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique subscription identifier

lead_id

UUID

FOREIGN KEY → leads(id), INDEX

Associated lead

topic_id

UUID

FOREIGN KEY → preference_topics(id), INDEX

Associated topic

is_subscribed

BOOLEAN

DEFAULT true

Subscription status

subscribed_at

TIMESTAMP

NULLABLE

When subscribed to topic

unsubscribed_at

TIMESTAMP

NULLABLE

When unsubscribed from topic

created_at

TIMESTAMP

DEFAULT NOW()

Record creation timestamp

-

UNIQUE (lead_id, topic_id)

One subscription per lead per topic

TABLE: unsubscribe_feedback

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique feedback identifier

lead_id

UUID

FOREIGN KEY → leads(id), INDEX

Lead who unsubscribed

reason

ENUM

'too_frequent', 'not_relevant', 'never_subscribed', 'privacy_concern', 'other'

Primary unsubscribe reason

additional_feedback

TEXT

NULLABLE

Free-form feedback text

created_at

TIMESTAMP

DEFAULT NOW(), INDEX

Feedback submission timestamp

TABLE: privacy_policies

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique policy identifier

tenant_id

UUID

FOREIGN KEY → tenants(id), INDEX

Multi-tenancy isolation

version

VARCHAR(20)

NOT NULL, UNIQUE

Policy version (e.g., "1.0", "2.0", "2.1")

policy_html

TEXT

NOT NULL

Full HTML content of privacy policy

policy_pdf_url

TEXT

NULLABLE

S3 URL to PDF version

effective_date

DATE

NOT NULL

When this version becomes effective

is_current

BOOLEAN

DEFAULT false

Current active policy version

change_summary

TEXT

NULLABLE

Summary of changes from previous version

requires_reconsent

BOOLEAN

DEFAULT false

Whether material changes require re-consent

created_at

TIMESTAMP

DEFAULT NOW()

Policy version creation timestamp

FRAME 12: SUPPORT & ESCALATION BUILDER - FAQ MANAGEMENT & MULTI-CHANNEL

Knowledge Base | Smart FAQ Suggestions | Multi-Channel Support Selection | Auto-Responses | BeeBot Integration

User Actions

Backend Services

AWS Infrastructure

Data Storage

GDPR Compliance

👤 SWIM LANE 1: USER ACTIONS

1. Build Knowledge Base

Create FAQ Categories: Product, Billing, Account, Technical Support, Shipping
Add FAQ Articles: Rich text editor with images, videos, code snippets
Keyword Tagging: Add keywords for semantic search (synonyms, variations)
Multi-Language Support: Translate FAQs to multiple languages (auto-translate + manual review)
Version Control: Track article changes, rollback to previous versions

2. Configure Smart FAQ Suggestions

Intent Detection: Analyze customer query to suggest relevant FAQs
Contextual Suggestions: Show FAQs based on current page/product viewed
Search Bar Integration: Real-time FAQ search with autocomplete
Feedback Loop: "Was this helpful?" voting to improve suggestions
Related Articles: Show related FAQs at end of article

3. Set Up Multi-Channel Support

Email Support: Support@company.com inbox integration
Live Chat: BeeBot AI chat with human handoff
WhatsApp Support: WhatsApp Business API for messaging
SMS Support: Two-way SMS support via Twilio
Phone Support: Callback request scheduling, IVR integration

4. Configure Auto-Responses

Business Hours: Set support hours (9 AM - 6 PM Mon-Fri)
After-Hours Message: Auto-response with expected reply time
Acknowledgment Messages: "We received your message, agent will reply in 2 hours"
Canned Responses: Pre-written responses for common queries
Language Detection: Auto-detect customer language, respond accordingly

⚙️ SWIM LANE 2: BACKEND SERVICES (NestJS + TypeScript)

📚 Knowledge Base Service

API Endpoints:

• POST /api/kb/categories - Create FAQ category
• POST /api/kb/articles - Create FAQ article
• PUT /api/kb/articles/:id - Update article (versioned)
• GET /api/kb/search - Search articles by keyword/query
• GET /api/kb/popular - Get most viewed/helpful articles

Knowledge Base Features:

• Rich Content: Markdown, HTML, images, videos, code blocks
• SEO Optimization: Meta tags, structured data, sitemap generation
• Analytics: View count, search impressions, helpfulness votes
• Multi-Language: Google Translate API integration for auto-translation
• Version History: Git-like versioning, diff view, rollback capability

🔍 FAQ Search Service

API Endpoints:

• GET /api/faq/search - Full-text search with ranking
• POST /api/faq/suggest - AI-powered FAQ suggestions based on query
• GET /api/faq/autocomplete - Real-time autocomplete suggestions
• POST /api/faq/feedback - Submit helpfulness feedback (thumbs up/down)

Search Algorithm:

• Full-Text Search: PostgreSQL full-text search with ranking
• Semantic Search: OpenAI embeddings for similarity matching
• Keyword Matching: Exact match, fuzzy match, synonym expansion
• Relevance Ranking: TF-IDF + helpfulness votes + recency
• Personalization: Rank higher based on user's previous interactions

💬 Multi-Channel Support Service

API Endpoints:

• POST /api/support/ticket - Create support ticket
• POST /api/support/message - Send message (email/chat/whatsapp/sms)
• GET /api/support/conversations/:id - Get conversation history
• POST /api/support/assign - Assign ticket to agent
• PUT /api/support/status/:id - Update ticket status

Channel Integration:

• Email: IMAP/SMTP integration, parse incoming emails
• Live Chat: WebSocket for real-time messaging
• WhatsApp: WhatsApp Business Cloud API webhook receiver
• SMS: Twilio SMS API with two-way messaging
• Unified Inbox: All channels visible in single support dashboard

🤖 BeeBot AI Service

API Endpoints:

• POST /api/beebot/chat - Process chat message
• POST /api/beebot/intent - Detect intent from user query
• POST /api/beebot/handoff - Transfer to human agent
• GET /api/beebot/context - Get conversation context

AI Capabilities:

• Intent Classification: OpenAI GPT-4 for intent detection (FAQ, billing, technical)
• FAQ Retrieval: Vector search in knowledge base (embeddings)
• Response Generation: Generate natural language responses from FAQs
• Sentiment Analysis: Detect frustrated customers, prioritize handoff
• Language Support: 95+ languages with auto-translation

⏰ Auto-Response Service

API Endpoints:

• POST /api/auto-response/configure - Set business hours
• POST /api/auto-response/templates - Create canned responses
• POST /api/auto-response/send - Send auto-response
• GET /api/auto-response/is-business-hours - Check availability

Auto-Response Logic:

• Business Hours Check: Timezone-aware availability check
• After-Hours Response: "Our support team is available Mon-Fri 9 AM - 6 PM EST"
• Acknowledgment: "Thank you for contacting us. We'll respond within 2 hours."
• Canned Responses: 50+ pre-written templates for common queries
• Variable Replacement: {{customer_name}}, {{order_id}}, {{ticket_id}}

📊 Support Analytics Service

API Endpoints:

• GET /api/analytics/ticket-volume - Tickets by channel/category/time
• GET /api/analytics/response-time - Avg first response time, resolution time
• GET /api/analytics/csat - Customer satisfaction scores
• GET /api/analytics/faq-performance - Most viewed/helpful FAQs

Analytics Metrics:

• Ticket Volume: Tickets created/resolved per day/week/month
• First Response Time: Avg time to first agent response (target <2 hours)
• Resolution Time: Avg time to close ticket (target <24 hours)
• CSAT Score: Customer satisfaction (1-5 stars, target >4.5)
• Self-Service Rate: % of queries resolved via FAQs (target >60%)

☁️ SWIM LANE 3: AWS INFRASTRUCTURE

ECS Fargate - Support Services

Task Definition: kb-service, faq-search-service, support-service, beebot-service
CPU/Memory: 1 vCPU, 2GB RAM per task
Auto Scaling: 3-15 tasks based on request count
Scaling Metric: RequestCountPerTarget >1000
Health Check: /health endpoint every 30s
Deployment: Rolling update, 50% minimum healthy

RDS PostgreSQL 15.4 - Support Data

Instance: db.r6g.xlarge (Multi-AZ for HA)
Storage: 500GB GP3 SSD (auto-scaling to 1TB)
Tables: kb_articles, kb_categories, support_tickets, conversations, faq_feedback
Full-Text Search: PostgreSQL tsvector for article search
Indexes: GIN index on tsvector, category_id, status, created_at
Backup: Automated daily snapshots, 7-day retention

ElastiCache Redis - Support Cache

Node Type: cache.r6g.large (Cluster Mode enabled)
Use Cases:
• FAQ article cache (TTL 1 hour, invalidate on update)
• Search results cache (TTL 15 min)
• BeeBot conversation context (TTL 30 min)
• Business hours config (TTL 24 hours)
• Rate limiting (100 req/min per IP)

SQS - Support Processing Queues

ticket-creation-queue: New ticket creation events (retention 14 days)
notification-queue: Send notifications to customers/agents
translation-queue: Translate FAQs/messages (async processing)
Visibility Timeout: 2 minutes
Dead Letter Queue: 3 retries before DLQ
Throughput: ~300 messages/second peak

S3 - Knowledge Base Assets

Bucket: unifiedbeez-kb-assets
Storage:
• FAQ images (screenshots, diagrams)
• Tutorial videos (MP4, WebM)
• PDF guides (user manuals, troubleshooting guides)
• Ticket attachments (customer uploads)
CloudFront: CDN for fast asset delivery
Lifecycle: Move to Glacier after 1 year

Lambda Functions

ticket-router: Route tickets to appropriate agent/queue (1GB RAM)
auto-response-sender: Send auto-responses (timeout 30s, 512MB RAM)
faq-indexer: Index FAQs for search (2GB RAM, timeout 5min)
translation-worker: Translate content via Google Translate API (1GB RAM)
analytics-aggregator: Calculate daily support metrics (2GB RAM)
Runtime: Node.js 20.x, bundled with esbuild

SNS - Support Notifications

new-ticket-topic: Notify agents of new tickets
urgent-ticket-topic: High-priority ticket alerts
ticket-resolved-topic: Notify customers of resolution
Subscribers: Email, SMS, Slack webhooks, mobile push
Filtering: Category-based routing (billing, technical, account)
Priority: Urgent tickets trigger SMS/push, normal = email

EventBridge - Scheduled Jobs

hourly-sla-check: cron(0 * * * ? *) - Check for SLA violations
daily-analytics: cron(0 8 * * ? *) - Calculate daily support metrics
weekly-faq-report: cron(0 9 ? * MON *) - Most viewed/helpful FAQs
nightly-cleanup: cron(0 2 * * ? *) - Archive resolved tickets >90 days
Target: Lambda functions
Retry Policy: 3 retries with exponential backoff

CloudWatch - Monitoring

Metrics: Ticket volume, response time, resolution time, CSAT score, self-service rate
Alarms: Response time >2hrs, open tickets >100, CSAT <4.0
Logs: Ticket creation, message exchanges, FAQ searches, BeeBot interactions
Dashboards: Support health, agent performance, channel metrics
Retention: 90 days log retention
Insights: Log Insights for query analysis and troubleshooting

Secrets Manager - API Keys

Secrets Stored:
• unifiedbeez/openai-api-key (BeeBot AI)
• unifiedbeez/google-translate-api-key (translations)
• unifiedbeez/whatsapp-api-token (WhatsApp Business)
• unifiedbeez/twilio-credentials (SMS support)
Encryption: KMS key (unifiedbeez-secrets-key)
Rotation: API keys rotated every 90 days
Access Control: IAM role-based (ECS tasks + Lambda only)

🗄️ SWIM LANE 4: DATABASE SCHEMA & RELATIONSHIPS

TABLE: kb_categories

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique category identifier

tenant_id

UUID

FOREIGN KEY → tenants(id), INDEX

Multi-tenancy isolation

name

VARCHAR(255)

NOT NULL

Category name (e.g., "Billing", "Technical Support", "Account")

slug

VARCHAR(255)

NOT NULL, UNIQUE

URL-friendly slug (e.g., "billing", "technical-support")

description

TEXT

NULLABLE

Category description

icon

VARCHAR(50)

NULLABLE

Icon name (e.g., "💰", "🔧", "👤")

display_order

INTEGER

DEFAULT 0

Display order in KB

is_active

BOOLEAN

DEFAULT true

Category status

created_at

TIMESTAMP

DEFAULT NOW()

Category creation timestamp

TABLE: kb_articles

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique article identifier

category_id

UUID

FOREIGN KEY → kb_categories(id), INDEX

Associated category

title

VARCHAR(500)

NOT NULL

Article title

slug

VARCHAR(500)

NOT NULL, UNIQUE

URL-friendly slug

content

TEXT

NOT NULL

Article content (Markdown or HTML)

content_html

TEXT

NULLABLE

Rendered HTML (cached)

search_vector

TSVECTOR

GIN INDEX

Full-text search vector (title + content)

keywords

TEXT[]

NULLABLE

Array of keywords/tags for search

language

VARCHAR(5)

DEFAULT 'en'

ISO 639-1 language code (en, es, fr, de, etc.)

translated_from

UUID

FOREIGN KEY → kb_articles(id), NULLABLE

Original article if this is a translation

view_count

INTEGER

DEFAULT 0

Number of times article viewed

helpful_count

INTEGER

DEFAULT 0

Number of "helpful" votes

not_helpful_count

INTEGER

DEFAULT 0

Number of "not helpful" votes

version

INTEGER

DEFAULT 1

Article version number

is_published

BOOLEAN

DEFAULT false

Publication status

published_at

TIMESTAMP

NULLABLE

When article was published

created_by

UUID

FOREIGN KEY → users(id)

Article author

created_at

TIMESTAMP

DEFAULT NOW(), INDEX

Article creation timestamp

updated_at

TIMESTAMP

DEFAULT NOW()

Last update timestamp

TABLE: support_tickets

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique ticket identifier

ticket_number

VARCHAR(20)

UNIQUE, NOT NULL

Human-readable ticket number (e.g., "TICKET-12345")

tenant_id

UUID

FOREIGN KEY → tenants(id), INDEX

Multi-tenancy isolation

customer_id

UUID

FOREIGN KEY → leads(id), INDEX

Customer who created ticket

category_id

UUID

FOREIGN KEY → kb_categories(id), INDEX

Ticket category

channel

ENUM

'email', 'chat', 'whatsapp', 'sms', 'phone'

Support channel

subject

VARCHAR(500)

NOT NULL

Ticket subject/title

description

TEXT

NOT NULL

Initial ticket description

priority

ENUM

'low', 'normal', 'high', 'urgent'

Ticket priority

status

ENUM

'new', 'open', 'pending', 'resolved', 'closed'

Ticket status

assigned_to

UUID

FOREIGN KEY → users(id), NULLABLE

Assigned support agent

first_response_at

TIMESTAMP

NULLABLE

When agent first responded (SLA tracking)

resolved_at

TIMESTAMP

NULLABLE

When ticket was resolved

closed_at

TIMESTAMP

NULLABLE

When ticket was closed

csat_score

INTEGER

CHECK (csat_score BETWEEN 1 AND 5), NULLABLE

Customer satisfaction score (1-5 stars)

csat_feedback

TEXT

NULLABLE

Customer feedback text

created_at

TIMESTAMP

DEFAULT NOW(), INDEX

Ticket creation timestamp

updated_at

TIMESTAMP

DEFAULT NOW()

Last update timestamp

TABLE: conversations

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique message identifier

ticket_id

UUID

FOREIGN KEY → support_tickets(id), INDEX

Associated ticket

sender_type

ENUM

'customer', 'agent', 'system', 'beebot'

Who sent the message

sender_id

UUID

NULLABLE

User ID (customer or agent)

message

TEXT

NOT NULL

Message content

attachments

JSONB

NULLABLE

Array of attachment URLs (S3 links)

is_internal

BOOLEAN

DEFAULT false

Internal note (not visible to customer)

created_at

TIMESTAMP

DEFAULT NOW(), INDEX

Message timestamp

TABLE: faq_feedback

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique feedback identifier

article_id

UUID

FOREIGN KEY → kb_articles(id), INDEX

Article being rated

customer_id

UUID

FOREIGN KEY → leads(id), NULLABLE

Customer who gave feedback (if logged in)

is_helpful

BOOLEAN

NOT NULL

Was article helpful? (thumbs up/down)

feedback_text

TEXT

NULLABLE

Optional feedback comment

created_at

TIMESTAMP

DEFAULT NOW(), INDEX

Feedback timestamp

FRAME 13: SUPPORT & ESCALATION BUILDER - ESCALATION RULES ENGINE

Smart Escalation Rules | SLA Management | Priority Routing | Agent Assignment | Escalation Notifications

User Actions

Backend Services

AWS Infrastructure

Data Storage

GDPR Compliance

👤 SWIM LANE 1: USER ACTIONS

1. Define Escalation Rules

Time-Based Escalation: Escalate if no response in 2 hours, no resolution in 24 hours
Priority Escalation: Urgent tickets auto-escalate to senior agents
Keyword Triggers: Escalate if ticket contains "angry", "lawsuit", "refund"
Sentiment Escalation: Escalate if customer sentiment is very negative
VIP Escalation: Enterprise customers get priority escalation

2. Configure SLA Policies

First Response SLA: Low=4h, Normal=2h, High=1h, Urgent=15min
Resolution SLA: Low=48h, Normal=24h, High=8h, Urgent=4h
Business Hours: SLA applies only during business hours (9 AM - 6 PM Mon-Fri)
SLA Exceptions: Pause SLA when waiting for customer response
Breach Notifications: Alert agents 30min before SLA breach

3. Set Up Agent Assignment

Round-Robin: Distribute tickets evenly across available agents
Skill-Based Routing: Route billing tickets to billing specialists
Load Balancing: Assign to agent with fewest open tickets
Availability Check: Only assign to online agents (active in last 5 min)
Reassignment Rules: Reassign if agent doesn't respond in 30 minutes

4. Configure Escalation Notifications

Agent Alerts: Slack/email notification when ticket escalated
Manager Alerts: Notify support manager of urgent escalations
Customer Updates: Inform customer when ticket is escalated
SLA Warnings: Alert when ticket is 80% through SLA
Escalation Digest: Daily summary of all escalations to management

⚙️ SWIM LANE 2: BACKEND SERVICES (NestJS + TypeScript)

⚡ Escalation Rules Engine

API Endpoints:

• POST /api/escalation/rules - Create escalation rule
• POST /api/escalation/evaluate - Evaluate ticket against rules
• POST /api/escalation/trigger - Manually trigger escalation
• GET /api/escalation/history/:ticketId - Get escalation history
• PUT /api/escalation/rules/:id - Update rule

Escalation Logic:

• Time-Based: Check ticket age vs SLA thresholds
• Priority-Based: Urgent priority → immediate escalation to L2
• Keyword Detection: Regex/NLP to detect escalation triggers
• Sentiment Analysis: OpenAI API for sentiment scoring (very negative → escalate)
• Boolean Logic: Complex rules with AND/OR/NOT conditions
• Rule Priority: Higher priority rules evaluated first

⏱️ SLA Management Service

API Endpoints:

• POST /api/sla/policies - Create SLA policy
• GET /api/sla/status/:ticketId - Get SLA status for ticket
• POST /api/sla/pause - Pause SLA (waiting for customer)
• POST /api/sla/resume - Resume SLA timer
• GET /api/sla/violations - Get all SLA violations

SLA Calculations:

• First Response SLA: Time from ticket creation to first agent response
• Resolution SLA: Time from creation to ticket resolved status
• Business Hours Only: Count only business hours (9 AM - 6 PM Mon-Fri)
• Pause Conditions: Auto-pause when status = "pending" (waiting for customer)
• SLA Breach: Flag ticket if deadline exceeded
• Warning Threshold: Alert at 80% of SLA time

👥 Agent Assignment Service

API Endpoints:

• POST /api/assignment/auto-assign - Auto-assign ticket to agent
• POST /api/assignment/reassign - Reassign to different agent
• GET /api/assignment/available-agents - Get online agents
• GET /api/assignment/agent-workload - Get ticket count per agent
• POST /api/assignment/set-skills - Set agent skill tags

Assignment Algorithms:

• Round-Robin: Cycle through agents sequentially
• Least Loaded: Assign to agent with fewest open tickets
• Skill-Based: Match ticket category to agent skill tags (billing, technical, account)
• Availability Check: Only assign to agents with status=online (last active <5 min)
• Fallback: If no skilled agent available, use round-robin
• Reassignment: If agent doesn't respond in 30 min, reassign automatically

🔔 Notification Service

API Endpoints:

• POST /api/notifications/send - Send notification
• POST /api/notifications/configure - Set notification preferences
• GET /api/notifications/history - Get notification history
• POST /api/notifications/digest - Send daily digest

Notification Channels:

• Email: AWS SES for email alerts
• Slack: Webhook integration for team notifications
• SMS: Twilio for urgent escalations
• Push Notifications: Mobile app notifications (Firebase Cloud Messaging)
• In-App: Real-time alerts in support dashboard (WebSocket)
• PagerDuty: Integration for on-call escalations

📊 Escalation Analytics Service

API Endpoints:

• GET /api/analytics/escalation-rate - % of tickets escalated
• GET /api/analytics/sla-compliance - SLA met vs breached
• GET /api/analytics/escalation-reasons - Why tickets escalated
• GET /api/analytics/agent-performance - Avg response/resolution time per agent

Analytics Metrics:

• Escalation Rate: (escalated tickets / total tickets) × 100 (target <10%)
• SLA Compliance: (tickets met SLA / total tickets) × 100 (target >95%)
• Avg Response Time: Avg time to first response (target <2 hours)
• Avg Resolution Time: Avg time to resolution (target <24 hours)
• Escalation Reasons: SLA breach (40%), customer request (30%), sentiment (20%), keyword (10%)

🤖 Smart Routing Service

API Endpoints:

• POST /api/routing/predict-category - AI-powered category prediction
• POST /api/routing/predict-priority - AI-powered priority prediction
• POST /api/routing/recommend-agent - Recommend best agent for ticket
• POST /api/routing/train-model - Retrain routing ML model

ML Features:

• Category Prediction: OpenAI GPT-4 analyzes ticket content, suggests category
• Priority Prediction: ML model predicts urgency based on keywords + sentiment
• Agent Recommendation: Historical data → which agent best suited for ticket type
• Resolution Time Prediction: Estimate resolution time based on similar tickets
• Model Training: Retrain models monthly on new ticket data

☁️ SWIM LANE 3: AWS INFRASTRUCTURE

ECS Fargate - Escalation Services

Task Definition: escalation-engine, sla-service, assignment-service, notification-service
CPU/Memory: 1 vCPU, 2GB RAM per task
Auto Scaling: 3-12 tasks based on queue depth
Scaling Metric: SQS queue depth >50
Health Check: /health endpoint every 30s
Deployment: Rolling update, 50% minimum healthy

RDS PostgreSQL 15.4 - Escalation Data

Instance: db.r6g.xlarge (Multi-AZ for HA)
Storage: 500GB GP3 SSD (auto-scaling to 1TB)
Tables: escalation_rules, sla_policies, escalation_logs, agent_skills, agent_workload
Indexes: ticket_id, agent_id, rule_id, created_at
Partitioning: escalation_logs partitioned by month
Backup: Automated daily snapshots, 7-day retention

ElastiCache Redis - Escalation Cache

Node Type: cache.r6g.large (Cluster Mode enabled)
Use Cases:
• Escalation rules cache (TTL 1 hour)
• SLA policies cache (TTL 1 hour)
• Agent availability status (TTL 5 min)
• Agent workload counter (real-time)
• Round-robin assignment state

SQS - Escalation Processing Queues

escalation-evaluation-queue: Evaluate tickets against rules (retention 14 days)
sla-check-queue: Check SLA status for all open tickets
notification-queue: Send escalation notifications
Visibility Timeout: 2 minutes
Dead Letter Queue: 3 retries before DLQ
Throughput: ~500 messages/second peak

Lambda Functions

escalation-evaluator: Evaluate escalation rules (timeout 1min, 1GB RAM)
sla-monitor: Check SLA status hourly (timeout 5min, 2GB RAM)
auto-assigner: Auto-assign tickets to agents (timeout 30s, 512MB RAM)
notification-sender: Send escalation notifications (timeout 30s, 512MB RAM)
analytics-aggregator: Calculate escalation metrics daily (2GB RAM)
Runtime: Node.js 20.x, bundled with esbuild

SNS - Escalation Notifications

escalation-alerts: Notify agents of escalated tickets
sla-breach-alerts: Critical SLA breach notifications
sla-warning-alerts: 80% SLA threshold warnings
manager-digest: Daily escalation summary to managers
Subscribers: Email, SMS (urgent), Slack, PagerDuty
Priority Routing: Urgent → SMS/PagerDuty, Normal → Email/Slack

EventBridge - Scheduled Jobs

every-5min-sla-check: cron(*/5 * * * ? *) - Check SLA status
hourly-escalation-eval: cron(0 * * * ? *) - Evaluate escalation rules
daily-analytics: cron(0 8 * * ? *) - Calculate escalation metrics
daily-manager-digest: cron(0 17 * * ? *) - Send manager summary (5 PM)
monthly-model-training: cron(0 2 1 * ? *) - Retrain ML models
Target: Lambda functions → SQS queues
Retry Policy: 3 retries with exponential backoff

CloudWatch - Monitoring

Metrics: Escalation rate, SLA compliance, agent workload, notification delivery
Alarms: Escalation rate >10%, SLA compliance <95%, queue depth >100
Logs: Escalation events, SLA calculations, rule evaluations, notifications
Dashboards: Escalation health, SLA compliance, agent performance
Retention: 90 days log retention
Insights: Query patterns to identify escalation trends

EventBridge - Real-Time Events

Event Bus: custom-event-bus (escalation events)
Events:
• ticket.created → Trigger auto-assignment
• ticket.escalated → Send escalation notifications
• sla.breach → Alert manager + escalate
• agent.response → Pause SLA if waiting for customer
Event Patterns: JSON pattern matching for routing
Targets: Lambda, SQS, SNS, Step Functions

Secrets Manager - Integration Keys

Secrets Stored:
• unifiedbeez/slack-webhook-url (Slack notifications)
• unifiedbeez/pagerduty-api-key (on-call escalations)
• unifiedbeez/twilio-credentials (SMS alerts)
• unifiedbeez/firebase-credentials (push notifications)
Encryption: KMS key (unifiedbeez-secrets-key)
Rotation: API keys rotated every 90 days
Access Control: IAM role-based (ECS tasks + Lambda only)

🗄️ SWIM LANE 4: DATABASE SCHEMA & RELATIONSHIPS

TABLE: escalation_rules

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique rule identifier

tenant_id

UUID

FOREIGN KEY → tenants(id), INDEX

Multi-tenancy isolation

name

VARCHAR(255)

NOT NULL

Rule name (e.g., "Urgent Priority Escalation", "2 Hour No Response")

description

TEXT

NULLABLE

Rule description

trigger_type

ENUM

'time_based', 'priority_based', 'keyword', 'sentiment', 'manual'

What triggers this escalation

conditions

JSONB

NOT NULL

Rule conditions (e.g., {priority: 'urgent', time_threshold: 120})

escalate_to_tier

ENUM

'L1', 'L2', 'L3', 'manager'

Which support tier to escalate to

escalate_to_user_id

UUID

FOREIGN KEY → users(id), NULLABLE

Specific user to escalate to (optional)

priority

INTEGER

DEFAULT 0

Rule evaluation priority (higher = evaluated first)

is_active

BOOLEAN

DEFAULT true

Rule status

notification_channels

TEXT[]

DEFAULT ARRAY['email']

Array of notification channels (email, slack, sms, push)

created_at

TIMESTAMP

DEFAULT NOW()

Rule creation timestamp

TABLE: sla_policies

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique SLA policy identifier

tenant_id

UUID

FOREIGN KEY → tenants(id), INDEX

Multi-tenancy isolation

name

VARCHAR(255)

NOT NULL

Policy name (e.g., "Standard SLA", "Enterprise SLA")

priority

ENUM

'low', 'normal', 'high', 'urgent'

Ticket priority this policy applies to

first_response_minutes

INTEGER

NOT NULL

First response SLA in minutes (e.g., 120 = 2 hours)

resolution_minutes

INTEGER

NOT NULL

Resolution SLA in minutes (e.g., 1440 = 24 hours)

business_hours_only

BOOLEAN

DEFAULT true

Whether SLA applies only during business hours

business_hours_config

JSONB

NULLABLE

Business hours config (e.g., {start: "09:00", end: "18:00", days: [1,2,3,4,5]})

pause_conditions

TEXT[]

DEFAULT ARRAY['pending']

Ticket statuses that pause SLA (waiting for customer)

warning_threshold_percent

INTEGER

DEFAULT 80

Alert when SLA is X% through (e.g., 80 = alert at 80%)

is_default

BOOLEAN

DEFAULT false

Default policy for all tickets

created_at

TIMESTAMP

DEFAULT NOW()

Policy creation timestamp

TABLE: escalation_logs (Partitioned by month)

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique escalation log identifier

ticket_id

UUID

FOREIGN KEY → support_tickets(id), INDEX

Escalated ticket

rule_id

UUID

FOREIGN KEY → escalation_rules(id), INDEX

Rule that triggered escalation

escalation_reason

TEXT

NOT NULL

Why ticket was escalated (e.g., "SLA breach: no response in 2 hours")

escalated_from_tier

ENUM

'L1', 'L2', 'L3'

Original support tier

escalated_to_tier

ENUM

'L1', 'L2', 'L3', 'manager'

New support tier

escalated_from_user_id

UUID

FOREIGN KEY → users(id), NULLABLE

Previous assigned agent

escalated_to_user_id

UUID

FOREIGN KEY → users(id), NULLABLE

New assigned agent

escalation_type

ENUM

'automatic', 'manual'

How escalation was triggered

triggered_by

UUID

FOREIGN KEY → users(id), NULLABLE

User who manually triggered (if manual)

notifications_sent

JSONB

NULLABLE

Array of notifications sent (channel, recipient, status)

created_at

TIMESTAMP

DEFAULT NOW(), INDEX, PARTITION KEY

Escalation timestamp (partition by month)

TABLE: agent_skills

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique skill mapping identifier

user_id

UUID

FOREIGN KEY → users(id), INDEX

Support agent

category_id

UUID

FOREIGN KEY → kb_categories(id), INDEX

Category agent is skilled in

skill_level

ENUM

'beginner', 'intermediate', 'expert'

Agent's skill level in this category

support_tier

ENUM

'L1', 'L2', 'L3'

Agent's support tier

created_at

TIMESTAMP

DEFAULT NOW()

Skill assignment timestamp

-

UNIQUE (user_id, category_id)

One skill level per agent per category

TABLE: agent_workload

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique workload record identifier

user_id

UUID

FOREIGN KEY → users(id), UNIQUE, INDEX

Support agent

open_tickets_count

INTEGER

DEFAULT 0

Number of open/assigned tickets

status

ENUM

'online', 'offline', 'away', 'busy'

Agent availability status

last_active_at

TIMESTAMP

NOT NULL, INDEX

Last activity timestamp (for availability check)

max_concurrent_tickets

INTEGER

DEFAULT 10

Max tickets agent can handle simultaneously

updated_at

TIMESTAMP

DEFAULT NOW()

Last update timestamp

FRAME 14: CAMPAIGN AUTOMATION - A/B TESTING & ADVANCED SEGMENTATION ENGINE

User Actions

Backend Services

AWS Infrastructure

Data Storage

GDPR Compliance

👤 SWIM LANE 1: A/B TESTING & SEGMENTATION USER ACTIONS

1. Create A/B Test

Select campaign to test
Choose test type (A/B or multivariate)
Define variants (subject line, content, send time)
Set traffic split % (e.g., 50/50, 33/33/33)
Set winning metric (open rate, click rate, conversion)
Define test duration & sample size

2. Build Advanced Segment

Name segment (e.g., "High-value inactive users")
Add filter conditions (demographics, behavior, engagement)
Use Boolean logic (AND/OR/NOT groups)
Set dynamic refresh (real-time, daily, weekly)
Preview segment size
Save & activate segment

3. Monitor Test Results

View real-time test performance
Compare variant metrics (opens, clicks, conversions)
Check statistical significance
Declare manual winner OR auto-winner (confidence threshold)
Send winning variant to remaining audience

4. Apply Segment to Campaign

Select saved segment
View segment count (refreshed)
Apply exclusions (unsubscribed, bounced)
Launch targeted campaign

↓

⚙️ SWIM LANE 2: A/B TESTING & SEGMENTATION BACKEND SERVICES

🧪 A/B Test Engine Service

API Endpoints:

• POST /api/ab-tests/create - Create new A/B test
• GET /api/ab-tests/:id - Get test details & results
• PUT /api/ab-tests/:id/variants - Add/edit variants
• POST /api/ab-tests/:id/start - Start test execution
• POST /api/ab-tests/:id/declare-winner - Manually declare winner
• GET /api/ab-tests/:id/significance - Calculate statistical significance

Core Logic:

• Traffic splitting algorithm (weighted distribution)
• Variant assignment (consistent per contact)
• Test orchestration (queue variants to SQS)
• Test duration management (auto-end after X hours)

📊 Variant Performance Tracker Service

API Endpoints:

• POST /api/variants/:id/track-send - Track variant sent
• POST /api/variants/:id/track-open - Track variant opened
• POST /api/variants/:id/track-click - Track variant clicked
• POST /api/variants/:id/track-conversion - Track variant conversion
• GET /api/variants/:id/metrics - Get variant performance metrics

Metrics Tracked:

• Sent count, delivered count, bounced count
• Open rate, click rate, conversion rate
• Unsubscribe rate, spam complaint rate
• Revenue per variant (if e-commerce tracking enabled)

🏆 Winner Detection Service

API Endpoints:

• POST /api/winner/calculate - Calculate statistical significance
• POST /api/winner/auto-declare - Auto-declare winner (confidence ≥95%)
• GET /api/winner/:testId/recommendation - Get winner recommendation

Winner Logic:

• Chi-square test for statistical significance
• Confidence level calculation (90%, 95%, 99%)
• Minimum sample size validation (≥100 opens per variant)
• Auto-send winner to remaining 50% of audience
• Test results stored for learning & optimization

🎯 Advanced Segmentation Service

API Endpoints:

• POST /api/segments/create - Create new segment
• GET /api/segments/:id - Get segment details & count
• PUT /api/segments/:id/rules - Update segment rules
• POST /api/segments/:id/preview - Preview segment contacts
• POST /api/segments/:id/refresh - Manually refresh segment
• GET /api/segments/list - List all segments

Segment Types:

• Static: Fixed list (snapshot in time)
• Dynamic: Auto-refresh based on rules
• Behavioral: Based on engagement actions
• Demographic: Based on contact properties
• RFM: Recency, frequency, monetary value

🔍 Audience Builder Service

API Endpoints:

• POST /api/audience/filter - Apply complex filters
• POST /api/audience/count - Count matching contacts
• POST /api/audience/export - Export filtered contacts
• GET /api/audience/filters/available - Get available filter fields

Filter Capabilities:

• Contact Properties: Name, email, phone, company, location
• Tags: Has tag, does not have tag
• Engagement: Opened email, clicked link, submitted form
• Lifecycle: Lead stage, customer status, churn risk
• Date Ranges: Created date, last activity, birthday
• Boolean Logic: AND/OR/NOT condition groups

⚡ Smart Segment Auto-Refresh Service

API Endpoints:

• POST /api/segments/:id/schedule-refresh - Schedule auto-refresh
• GET /api/segments/:id/refresh-history - Get refresh logs
• POST /api/segments/refresh-all - Refresh all dynamic segments

Refresh Strategy:

• Real-time: Refresh on every query (small segments <10K)
• Hourly: EventBridge cron every hour
• Daily: EventBridge cron at 3am UTC
• Weekly: EventBridge cron Monday 3am UTC
• Segment cache invalidation (Redis TTL)
• Incremental refresh (only changed contacts)

↓

☁️ SWIM LANE 3: AWS INFRASTRUCTURE FOR A/B TESTING & SEGMENTATION

ECS Fargate

Services:
• ab-test-service (NestJS)
• variant-tracker-service (NestJS)
• winner-detection-service (Python/FastAPI)
• segmentation-service (NestJS)
• audience-builder-service (NestJS)

Scaling: Auto-scale 2-20 tasks (CPU >70% trigger)
Resources: 2 vCPU, 4 GB RAM per task

RDS PostgreSQL 15.4

Tables:
• ab_tests (test definitions)
• test_variants (variant configurations)
• variant_metrics (performance data)
• variant_assignments (contact → variant mapping)
• segments (segment definitions)
• segment_rules (filter conditions)
• segment_contacts (junction table)
• segment_refresh_logs (audit trail)

Instance: db.r6g.xlarge (4 vCPU, 32 GB RAM)
Multi-AZ: Yes (failover support)

ElastiCache Redis

Cluster Mode: Enabled (sharding)
Use Cases:
• Variant assignment cache (contact_id → variant_id)
• Segment count cache (segment_id → count, TTL 1h)
• Test results cache (test_id → metrics, TTL 5min)
• Audience filter cache (filter_hash → contact_ids, TTL 15min)

Instance: cache.r6g.large (2 vCPU, 13.07 GB RAM)
Replication: 2 read replicas

SQS Queues

Queues:
1. ab-test-variant-queue - Variant send jobs (FIFO)
2. variant-tracking-queue - Event tracking (Standard)
3. winner-calculation-queue - Winner detection jobs (Standard)
4. segment-refresh-queue - Segment refresh jobs (Standard)

Retention: 14 days
Dead Letter Queue: Yes (3 retry attempts)

Lambda Functions

Functions:
1. variant-assignment-function - Assign contacts to variants (512MB RAM)
2. winner-detector-function - Calculate statistical significance (1GB RAM)
3. auto-winner-sender-function - Send winner to remaining audience (1GB RAM)
4. segment-refresh-function - Refresh dynamic segments (2GB RAM, 5min timeout)
5. segment-cleanup-function - Delete expired segments (512MB RAM)

Runtime: Node.js 20.x (TypeScript)
Concurrency: Reserved 50 concurrent executions

SNS Topics

Topics:
1. ab-test-started - Test started notification
2. ab-test-winner-declared - Winner declared notification
3. segment-refreshed - Segment refresh completed
4. segment-error - Segment refresh failed

Subscribers:
• Email (user notifications)
• SQS (downstream processing)
• Lambda (event handlers)

EventBridge Scheduled Jobs

Cron Schedules:
• hourly-segment-refresh: cron(0 * * * ? *) - Refresh hourly segments
• daily-segment-refresh: cron(0 3 * * ? *) - Refresh daily segments (3am UTC)
• weekly-segment-refresh: cron(0 3 ? * MON *) - Refresh weekly segments
• auto-winner-check: cron(*/15 * * * ? *) - Check for auto-winners (every 15min)
• test-expiration-check: cron(0 * * * ? *) - End expired tests (hourly)
• segment-cleanup: cron(0 4 * * ? *) - Delete expired segments (4am UTC)

CloudWatch Monitoring

Metrics:
• Active A/B tests count
• Variant send rate (per minute)
• Winner detection time (avg)
• Segment refresh duration (avg)
• Segment size (min/max/avg)
• Audience builder query time

Alarms:
• Winner detection failure (SNS alert)
• Segment refresh failure (SNS alert)
• Large segment query timeout (>30s)

Secrets Manager

Secrets Stored:
• Database connection strings (RDS)
• Redis connection string (ElastiCache)
• OpenAI API key (for BeeBot segment suggestions)
• External analytics API keys (Google Analytics, Mixpanel)

Encryption: AWS KMS (AES-256)
Rotation: Automatic every 90 days

S3 Buckets

Buckets:
• segment-exports/ - Exported segment contact lists (CSV, JSON)
• test-reports/ - A/B test result reports (PDF, Excel)

Lifecycle Policy:
• Transition to S3 Glacier after 90 days
• Delete after 2 years

Access: Pre-signed URLs (expiry 24h)

↓

🗄️ SWIM LANE 4: DATABASE SCHEMA & RELATIONSHIPS

TABLE: ab_tests

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique test identifier

campaignId

UUID

FOREIGN KEY → campaigns(id), NOT NULL

Associated campaign being tested

testName

VARCHAR(255)

NOT NULL

Test name (e.g., "Subject Line Test - Welcome Email")

testType

ENUM

'ab', 'multivariate', NOT NULL

Type of test (A/B or multivariate)

winningMetric

ENUM

'open_rate', 'click_rate', 'conversion_rate', 'revenue', NOT NULL

Metric used to determine winner

trafficSplit

JSONB

NOT NULL

Traffic split percentages (e.g., {"A": 50, "B": 50})

testDurationHours

INTEGER

DEFAULT 24

Test duration in hours (default 24h)

minSampleSize

INTEGER

DEFAULT 100

Minimum sample size per variant

confidenceThreshold

DECIMAL(3,2)

DEFAULT 0.95

Confidence level for auto-winner (0.90, 0.95, 0.99)

autoSendWinner

BOOLEAN

DEFAULT true

Auto-send winner to remaining audience

status

ENUM

'draft', 'running', 'completed', 'cancelled', DEFAULT 'draft'

Test status

winnerId

UUID

FOREIGN KEY → test_variants(id), NULLABLE

Winning variant ID (NULL until winner declared)

startedAt

TIMESTAMP

NULLABLE

Test start timestamp

endedAt

TIMESTAMP

NULLABLE

Test end timestamp

createdAt

TIMESTAMP

DEFAULT NOW()

Test creation timestamp

TABLE: test_variants

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique variant identifier

testId

UUID

FOREIGN KEY → ab_tests(id), NOT NULL, INDEX

Parent test

variantName

VARCHAR(100)

NOT NULL

Variant name (e.g., "Variant A", "Control")

variantLetter

VARCHAR(1)

NOT NULL

Variant letter (A, B, C, etc.)

isControl

BOOLEAN

DEFAULT false

Is this the control variant?

subjectLine

TEXT

NULLABLE

Email subject line (if testing subject)

preheaderText

TEXT

NULLABLE

Email preheader (if testing preheader)

emailContentId

UUID

FOREIGN KEY → emails(id), NULLABLE

Email content (if testing content)

sendTime

TIME

NULLABLE

Send time (if testing send time)

fromName

VARCHAR(255)

NULLABLE

Sender name (if testing from name)

trafficPercentage

DECIMAL(5,2)

NOT NULL

Traffic percentage allocated (e.g., 50.00)

createdAt

TIMESTAMP

DEFAULT NOW()

Variant creation timestamp

TABLE: variant_metrics (Partitioned by Month)

Column Name

Type

Constraints

Purpose

id

BIGSERIAL

PRIMARY KEY

Unique metric record ID

variantId

UUID

FOREIGN KEY → test_variants(id), NOT NULL, INDEX

Variant being tracked

sentCount

INTEGER

DEFAULT 0

Total emails sent for this variant

deliveredCount

INTEGER

DEFAULT 0

Successfully delivered emails

bouncedCount

INTEGER

DEFAULT 0

Bounced emails

openedCount

INTEGER

DEFAULT 0

Unique opens

clickedCount

INTEGER

DEFAULT 0

Unique clicks

convertedCount

INTEGER

DEFAULT 0

Conversions (goal completions)

unsubscribedCount

INTEGER

DEFAULT 0

Unsubscribes from this variant

spamComplaintCount

INTEGER

DEFAULT 0

Spam complaints

revenue

DECIMAL(10,2)

DEFAULT 0.00

Total revenue attributed to variant (if e-commerce)

openRate

DECIMAL(5,2)

GENERATED ALWAYS AS (CASE WHEN deliveredCount > 0 THEN (openedCount::DECIMAL / deliveredCount) * 100 ELSE 0 END) STORED

Calculated open rate %

clickRate

DECIMAL(5,2)

GENERATED ALWAYS AS (CASE WHEN deliveredCount > 0 THEN (clickedCount::DECIMAL / deliveredCount) * 100 ELSE 0 END) STORED

Calculated click rate %

conversionRate

DECIMAL(5,2)

GENERATED ALWAYS AS (CASE WHEN deliveredCount > 0 THEN (convertedCount::DECIMAL / deliveredCount) * 100 ELSE 0 END) STORED

Calculated conversion rate %

lastUpdatedAt

TIMESTAMP

DEFAULT NOW()

Last metric update timestamp

createdAt

TIMESTAMP

DEFAULT NOW()

Metric record creation (partition key for monthly partitions)

TABLE: segments

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique segment identifier

userId

UUID

FOREIGN KEY → users(id), NOT NULL, INDEX

Segment owner

segmentName

VARCHAR(255)

NOT NULL

Segment name (e.g., "High-value inactive users")

segmentType

ENUM

'static', 'dynamic', 'behavioral', 'demographic', 'rfm', NOT NULL

Segment type

refreshStrategy

ENUM

'realtime', 'hourly', 'daily', 'weekly', 'manual', DEFAULT 'daily'

How often segment refreshes

contactCount

INTEGER

DEFAULT 0

Current number of contacts in segment (cached)

isActive

BOOLEAN

DEFAULT true

Is segment active?

lastRefreshedAt

TIMESTAMP

NULLABLE

Last segment refresh timestamp

createdAt

TIMESTAMP

DEFAULT NOW()

Segment creation timestamp

updatedAt

TIMESTAMP

DEFAULT NOW()

Last segment update timestamp

TABLE: segment_rules

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique rule identifier

segmentId

UUID

FOREIGN KEY → segments(id) ON DELETE CASCADE, NOT NULL, INDEX

Parent segment

groupIndex

INTEGER

NOT NULL, DEFAULT 0

Rule group index (for AND/OR grouping)

groupLogic

ENUM

'AND', 'OR', DEFAULT 'AND'

Logic within this group (AND/OR)

fieldName

VARCHAR(255)

NOT NULL

Contact field being filtered (e.g., "email", "tags", "lastActivityDate")

operator

ENUM

'equals', 'not_equals', 'contains', 'not_contains', 'starts_with', 'ends_with', 'greater_than', 'less_than', 'is_empty', 'is_not_empty', 'in', 'not_in', NOT NULL

Comparison operator

filterValue

TEXT

NULLABLE

Filter value (e.g., "VIP", "30", "2024-01-01")

filterValueType

ENUM

'string', 'number', 'date', 'boolean', 'array', NOT NULL

Data type of filter value

createdAt

TIMESTAMP

DEFAULT NOW()

Rule creation timestamp

TABLE: segment_contacts (Junction Table)

Column Name

Type

Constraints

Purpose

id

BIGSERIAL

PRIMARY KEY

Unique junction record ID

segmentId

UUID

FOREIGN KEY → segments(id) ON DELETE CASCADE, NOT NULL, INDEX

Segment

contactId

UUID

FOREIGN KEY → contacts(id) ON DELETE CASCADE, NOT NULL, INDEX

Contact in segment

addedAt

TIMESTAMP

DEFAULT NOW()

When contact was added to segment

CONSTRAINT

UNIQUE

UNIQUE(segmentId, contactId)

Prevent duplicate segment memberships

↓

FRAME 15: CAMPAIGN AUTOMATION - ANALYTICS & PERFORMANCE REPORTING DASHBOARD

User Actions

Backend Services

AWS Infrastructure

Data Storage

GDPR Compliance

👤 SWIM LANE 1: ANALYTICS & REPORTING USER ACTIONS

1. View Campaign Dashboard

View overview dashboard (all campaigns)
See real-time metrics (sent, delivered, opened, clicked)
View campaign performance trends (last 7/30/90 days)
Compare campaign performance (best/worst performers)
View engagement heatmap (by time/day)
Monitor active campaigns (live status)

2. Drill into Campaign Details

Select specific campaign
View detailed metrics (opens, clicks, conversions, revenue)
See geographic distribution (clicks by location)
View device breakdown (desktop, mobile, tablet)
Analyze link performance (click map)
View email client breakdown (Gmail, Outlook, Apple Mail)

3. Generate Custom Report

Select report type (campaign, automation, contact engagement)
Choose date range (custom or preset)
Select metrics to include (open rate, click rate, ROI)
Add filters (campaign type, tags, status)
Preview report
Generate & download (PDF, Excel, CSV)

4. Create Custom Dashboard

Name custom dashboard
Add widgets (charts, tables, KPIs)
Configure widget data sources
Set refresh frequency (real-time, hourly, daily)
Save & share dashboard with team

↓

⚙️ SWIM LANE 2: ANALYTICS & REPORTING BACKEND SERVICES

📊 Campaign Analytics Service

API Endpoints:

• GET /api/analytics/dashboard - Get overview dashboard data
• GET /api/analytics/campaign/:id - Get campaign-specific analytics
• GET /api/analytics/campaigns/compare - Compare multiple campaigns
• GET /api/analytics/trends - Get performance trends (time series)
• GET /api/analytics/engagement-heatmap - Get engagement by time/day
• GET /api/analytics/top-performers - Get top/bottom performing campaigns

Metrics Calculated:

• Delivery Metrics: Sent, delivered, bounced, failed
• Engagement Metrics: Open rate, click rate, CTOR (click-to-open rate)
• Conversion Metrics: Conversion rate, revenue, ROI
• Behavioral Metrics: Avg time to open, avg time to click
• Audience Metrics: Reach, frequency, unique contacts

📈 Performance Dashboard Service

API Endpoints:

• GET /api/dashboards/list - Get all user dashboards
• POST /api/dashboards/create - Create custom dashboard
• GET /api/dashboards/:id - Get dashboard configuration & data
• PUT /api/dashboards/:id/widgets - Update dashboard widgets
• DELETE /api/dashboards/:id - Delete dashboard
• POST /api/dashboards/:id/share - Share dashboard with team

Widget Types:

• KPI Card: Single metric with trend indicator
• Line Chart: Time series performance trends
• Bar Chart: Compare campaigns or segments
• Pie Chart: Distribution (e.g., device breakdown)
• Table: Detailed campaign list with metrics
• Heatmap: Engagement by time/day
• Funnel Chart: Conversion funnel visualization
• Geographic Map: Click distribution by location

📄 Report Generation Service

API Endpoints:

• POST /api/reports/generate - Generate custom report
• GET /api/reports/:id - Get report status & download link
• GET /api/reports/templates - Get report templates
• POST /api/reports/schedule - Schedule recurring report
• GET /api/reports/scheduled - Get all scheduled reports
• DELETE /api/reports/scheduled/:id - Delete scheduled report

Report Types:

• Campaign Summary: Overview of all campaigns (date range)
• Campaign Deep Dive: Detailed single campaign analysis
• A/B Test Report: Test performance comparison
• Automation Performance: All automation workflows
• Contact Engagement: Contact-level engagement metrics
• Revenue Report: E-commerce sales attribution
• Deliverability Report: Bounce/spam complaint analysis
• Executive Summary: High-level KPIs for leadership

⚡ Real-time Metrics Service

API Endpoints:

• GET /api/metrics/realtime/campaign/:id - Get live campaign metrics
• GET /api/metrics/realtime/active-campaigns - All active campaigns
• WebSocket /ws/metrics/:campaignId - Stream real-time updates
• GET /api/metrics/live-dashboard - Live dashboard data (5s refresh)

Real-time Data:

• Send Rate: Emails sent per minute (live graph)
• Open Rate: Opens in last 5 minutes (streaming)
• Click Rate: Clicks in last 5 minutes (streaming)
• Live Activity Feed: Recent opens/clicks (scrolling feed)
• Campaign Progress: % completion for active sends
• Server Health: Queue depth, processing rate

🔢 Data Aggregation Service

API Endpoints:

• POST /api/aggregation/campaign/:id/aggregate - Trigger manual aggregation
• GET /api/aggregation/status - Get aggregation job status
• GET /api/aggregation/history - Get aggregation history logs

Aggregation Strategy:

• Hourly Aggregation: Roll up metrics every hour (campaign_metrics_hourly)
• Daily Aggregation: Daily summary at 3am UTC (campaign_metrics_daily)
• Monthly Aggregation: Monthly summary on 1st of month (campaign_metrics_monthly)
• Incremental Updates: Only process new events since last aggregation
• Lambda Function: Triggered by EventBridge (hourly, daily, monthly)
• Performance: Aggregation completes in <5min for 1M events

💾 Export Service

API Endpoints:

• POST /api/export/analytics - Export analytics data
• POST /api/export/campaign/:id - Export campaign data
• GET /api/export/:jobId/status - Get export job status
• GET /api/export/:jobId/download - Download exported file
• GET /api/export/history - Get export history

Export Formats:

• CSV: Tabular data (metrics, campaign lists)
• Excel (XLSX): Multi-sheet reports with charts
• PDF: Formatted reports with branding
• JSON: Raw data for API integration
• Google Sheets: Direct export to Google Sheets (OAuth)
• Export Size Limit: 100MB per export (paginated for larger datasets)

↓

☁️ SWIM LANE 3: AWS INFRASTRUCTURE FOR ANALYTICS & REPORTING

ECS Fargate

Services:
• campaign-analytics-service (NestJS)
• dashboard-service (NestJS)
• report-generation-service (NestJS + Puppeteer for PDF)
• realtime-metrics-service (NestJS + WebSocket)
• export-service (NestJS)

Scaling: Auto-scale 2-20 tasks (CPU >70% trigger)
Resources: 2 vCPU, 4 GB RAM per task (8 GB for report-generation)

RDS PostgreSQL 15.4

Tables:
• campaign_metrics_hourly (time-series aggregates, partitioned by month)
• campaign_metrics_daily (daily aggregates)
• campaign_metrics_monthly (monthly aggregates)
• custom_dashboards (user dashboard configurations)
• dashboard_widgets (widget definitions)
• scheduled_reports (recurring report configs)
• export_jobs (export job tracking)
• report_history (generated report metadata)

Read Replicas: 2 replicas for read-heavy analytics queries
Query Optimization: Materialized views for expensive aggregations

ElastiCache Redis

Cluster Mode: Enabled (sharding)
Use Cases:
• Dashboard data cache (dashboard_id → data, TTL 5min)
• Real-time metrics cache (campaign_id → metrics, TTL 10s)
• Report preview cache (report_hash → preview_data, TTL 1h)
• Aggregation lock (prevent duplicate aggregation jobs)
• WebSocket connection pool (socket_id → user_id mapping)

Instance: cache.r6g.xlarge (4 vCPU, 26.32 GB RAM)
Replication: 2 read replicas (high availability)

SQS Queues

Queues:
1. analytics-aggregation-queue - Aggregation jobs (Standard)
2. report-generation-queue - Report generation jobs (Standard)
3. export-processing-queue - Export jobs (Standard)
4. dashboard-refresh-queue - Dashboard data refresh (Standard)

Retention: 14 days
Dead Letter Queue: Yes (3 retry attempts)
Visibility Timeout: 15 minutes (long-running reports)

Lambda Functions

Functions:
1. hourly-aggregation-function - Hourly metric aggregation (2GB RAM, 5min timeout)
2. daily-aggregation-function - Daily metric aggregation (3GB RAM, 10min timeout)
3. monthly-aggregation-function - Monthly metric aggregation (3GB RAM, 10min timeout)
4. report-generator-function - Generate PDF/Excel reports (4GB RAM, 15min timeout)
5. export-processor-function - Process large data exports (4GB RAM, 15min timeout)
6. dashboard-snapshot-function - Create dashboard snapshots (1GB RAM)

Runtime: Node.js 20.x (TypeScript) + Chromium (Puppeteer for PDF)
Concurrency: Reserved 100 concurrent executions

SNS Topics

Topics:
1. report-generated - Report generation completed
2. export-completed - Export job completed
3. aggregation-completed - Aggregation job completed
4. aggregation-failed - Aggregation job failed (alert)

Subscribers:
• Email (user notifications for report downloads)
• SQS (downstream processing)
• Lambda (event handlers)
• Slack webhook (team notifications)

EventBridge Scheduled Jobs

Cron Schedules:
• hourly-aggregation: cron(5 * * * ? *) - Run at :05 every hour
• daily-aggregation: cron(0 3 * * ? *) - Run at 3am UTC daily
• monthly-aggregation: cron(0 4 1 * ? *) - Run 1st of month at 4am UTC
• weekly-report-digest: cron(0 9 ? * MON *) - Monday 9am UTC
• dashboard-snapshot: cron(0 2 * * ? *) - Daily dashboard backups at 2am UTC
• export-cleanup: cron(0 5 * * ? *) - Delete expired exports (5am UTC)

CloudWatch Monitoring

Metrics:
• Analytics query response time (avg, p95, p99)
• Dashboard load time (avg)
• Report generation time (avg)
• Export job duration (avg)
• Aggregation job duration (avg)
• Real-time WebSocket connections (active count)
• Cache hit rate (Redis)

Alarms:
• Query timeout (>30s) → SNS alert
• Aggregation failure → SNS alert
• Report generation failure → SNS alert
• High Redis memory usage (>80%) → SNS alert

S3 Buckets

Buckets:
• analytics-reports/ - Generated reports (PDF, Excel, CSV)
• analytics-exports/ - Data exports (CSV, JSON)
• dashboard-snapshots/ - Dashboard backups (JSON)

Lifecycle Policy:
• Reports: Transition to S3 Glacier after 90 days, delete after 2 years
• Exports: Delete after 30 days (user responsibility to download)
• Snapshots: Delete after 90 days

Access: Pre-signed URLs (expiry 24h for downloads)

CloudFront CDN

Purpose: Accelerate report/export downloads globally
Origin: S3 buckets (analytics-reports, analytics-exports)
Cache Behavior:
• TTL: 24 hours (reports are static once generated)
• Signed URLs: Yes (security)
• GZIP Compression: Enabled

Performance:
• <100ms download start time globally
• Bandwidth optimization (cost savings)

Secrets Manager

Secrets Stored:
• Database connection strings (RDS + read replicas)
• Redis connection string (ElastiCache)
• Google Sheets API credentials (OAuth tokens)
• Slack webhook URLs (notifications)
• External analytics API keys (Google Analytics, Mixpanel)

Encryption: AWS KMS (AES-256)
Rotation: Automatic every 90 days

↓

🗄️ SWIM LANE 4: DATABASE SCHEMA & RELATIONSHIPS

TABLE: campaign_metrics_hourly (Partitioned by Month)

Column Name

Type

Constraints

Purpose

id

BIGSERIAL

PRIMARY KEY

Unique record ID

campaignId

UUID

FOREIGN KEY → campaigns(id), NOT NULL, INDEX

Campaign being tracked

hourBucket

TIMESTAMP

NOT NULL, INDEX

Hour bucket (e.g., 2025-01-15 14:00:00)

sentCount

INTEGER

DEFAULT 0

Emails sent in this hour

deliveredCount

INTEGER

DEFAULT 0

Successfully delivered

bouncedCount

INTEGER

DEFAULT 0

Bounced emails

openedCount

INTEGER

DEFAULT 0

Unique opens

clickedCount

INTEGER

DEFAULT 0

Unique clicks

convertedCount

INTEGER

DEFAULT 0

Conversions

unsubscribedCount

INTEGER

DEFAULT 0

Unsubscribes

spamComplaintCount

INTEGER

DEFAULT 0

Spam complaints

revenue

DECIMAL(12,2)

DEFAULT 0.00

Revenue attributed in this hour

openRate

DECIMAL(5,2)

GENERATED ALWAYS AS (CASE WHEN deliveredCount > 0 THEN (openedCount::DECIMAL / deliveredCount) * 100 ELSE 0 END) STORED

Calculated open rate %

clickRate

DECIMAL(5,2)

GENERATED ALWAYS AS (CASE WHEN deliveredCount > 0 THEN (clickedCount::DECIMAL / deliveredCount) * 100 ELSE 0 END) STORED

Calculated click rate %

CTOR

DECIMAL(5,2)

GENERATED ALWAYS AS (CASE WHEN openedCount > 0 THEN (clickedCount::DECIMAL / openedCount) * 100 ELSE 0 END) STORED

Click-to-open rate (CTOR) %

createdAt

TIMESTAMP

DEFAULT NOW()

Record creation timestamp (partition key)

CONSTRAINT

UNIQUE

UNIQUE(campaignId, hourBucket)

One record per campaign per hour

TABLE: campaign_metrics_daily

Column Name

Type

Constraints

Purpose

id

BIGSERIAL

PRIMARY KEY

Unique record ID

campaignId

UUID

FOREIGN KEY → campaigns(id), NOT NULL, INDEX

Campaign being tracked

date

DATE

NOT NULL, INDEX

Date (e.g., 2025-01-15)

sentCount

INTEGER

DEFAULT 0

Total emails sent on this date

deliveredCount

INTEGER

DEFAULT 0

Successfully delivered

openedCount

INTEGER

DEFAULT 0

Unique opens

clickedCount

INTEGER

DEFAULT 0

Unique clicks

convertedCount

INTEGER

DEFAULT 0

Conversions

revenue

DECIMAL(12,2)

DEFAULT 0.00

Total revenue on this date

openRate

DECIMAL(5,2)

GENERATED ALWAYS AS (CASE WHEN deliveredCount > 0 THEN (openedCount::DECIMAL / deliveredCount) * 100 ELSE 0 END) STORED

Calculated open rate %

clickRate

DECIMAL(5,2)

GENERATED ALWAYS AS (CASE WHEN deliveredCount > 0 THEN (clickedCount::DECIMAL / deliveredCount) * 100 ELSE 0 END) STORED

Calculated click rate %

conversionRate

DECIMAL(5,2)

GENERATED ALWAYS AS (CASE WHEN deliveredCount > 0 THEN (convertedCount::DECIMAL / deliveredCount) * 100 ELSE 0 END) STORED

Calculated conversion rate %

createdAt

TIMESTAMP

DEFAULT NOW()

Record creation timestamp

CONSTRAINT

UNIQUE

UNIQUE(campaignId, date)

One record per campaign per day

TABLE: custom_dashboards

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique dashboard identifier

userId

UUID

FOREIGN KEY → users(id), NOT NULL, INDEX

Dashboard owner

dashboardName

VARCHAR(255)

NOT NULL

Dashboard name (e.g., "Q1 Campaign Performance")

description

TEXT

NULLABLE

Dashboard description

layout

JSONB

NOT NULL

Dashboard layout configuration (grid positions)

refreshFrequency

ENUM

'realtime', 'hourly', 'daily', DEFAULT 'hourly'

Data refresh frequency

isShared

BOOLEAN

DEFAULT false

Is dashboard shared with team?

sharedWith

UUID[]

NULLABLE

Array of user IDs dashboard is shared with

createdAt

TIMESTAMP

DEFAULT NOW()

Dashboard creation timestamp

updatedAt

TIMESTAMP

DEFAULT NOW()

Last dashboard update timestamp

TABLE: dashboard_widgets

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique widget identifier

dashboardId

UUID

FOREIGN KEY → custom_dashboards(id) ON DELETE CASCADE, NOT NULL, INDEX

Parent dashboard

widgetType

ENUM

'kpi_card', 'line_chart', 'bar_chart', 'pie_chart', 'table', 'heatmap', 'funnel', 'map', NOT NULL

Widget type

widgetTitle

VARCHAR(255)

NOT NULL

Widget title (e.g., "Open Rate Trend")

dataSource

JSONB

NOT NULL

Data source configuration (query params, filters)

widgetConfig

JSONB

NOT NULL

Widget-specific config (colors, axes, legends)

position

JSONB

NOT NULL

Widget position & size on grid (x, y, width, height)

createdAt

TIMESTAMP

DEFAULT NOW()

Widget creation timestamp

updatedAt

TIMESTAMP

DEFAULT NOW()

Last widget update timestamp

TABLE: scheduled_reports

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique scheduled report identifier

userId

UUID

FOREIGN KEY → users(id), NOT NULL, INDEX

Report owner

reportName

VARCHAR(255)

NOT NULL

Report name (e.g., "Weekly Campaign Summary")

reportType

VARCHAR(100)

NOT NULL

Report type (e.g., "campaign_summary", "automation_performance")

reportConfig

JSONB

NOT NULL

Report configuration (metrics, filters, date range)

schedule

ENUM

'daily', 'weekly', 'monthly', NOT NULL

Report frequency

scheduleDay

INTEGER

NULLABLE

Day of week (1-7) for weekly, day of month (1-31) for monthly

scheduleTime

TIME

NOT NULL

Time to generate report (e.g., 09:00:00)

format

ENUM

'pdf', 'excel', 'csv', DEFAULT 'pdf'

Report file format

recipients

TEXT[]

NOT NULL

Array of email addresses to send report to

isActive

BOOLEAN

DEFAULT true

Is scheduled report active?

lastGeneratedAt

TIMESTAMP

NULLABLE

Last report generation timestamp

createdAt

TIMESTAMP

DEFAULT NOW()

Scheduled report creation timestamp

TABLE: export_jobs

Column Name

Type

Constraints

Purpose

id

UUID

PRIMARY KEY

Unique export job identifier

userId

UUID

FOREIGN KEY → users(id), NOT NULL, INDEX

User who requested export

exportType

VARCHAR(100)

NOT NULL

Export type (e.g., "campaign_analytics", "contact_list")

exportConfig

JSONB

NOT NULL

Export configuration (date range, filters)

format

ENUM

'csv', 'excel', 'json', DEFAULT 'csv'

Export file format

status

ENUM

'pending', 'processing', 'completed', 'failed', DEFAULT 'pending'

Export job status

s3Key

TEXT

NULLABLE

S3 object key for exported file

downloadUrl

TEXT

NULLABLE

Pre-signed S3 URL for download (24h expiry)

fileSize

BIGINT

NULLABLE

File size in bytes

rowCount

INTEGER

NULLABLE

Number of rows exported

errorMessage

TEXT

NULLABLE

Error message if export failed

expiresAt

TIMESTAMP

NULLABLE

Export file expiration timestamp (30 days)

createdAt

TIMESTAMP

DEFAULT NOW()

Export job creation timestamp

completedAt

TIMESTAMP

NULLABLE

Export job completion timestamp

↓

UnifiedBeez Automation Library + Builders - Backend Architecture

1. Create Automation

2. Define Triggers

3. Build Workflow

4. Test & Activate

⚡ Automation Engine Service

🎯 Trigger Evaluation Service

🧠 Condition Logic Engine

🚀 Action Executor Service

📊 Analytics Service

📋 Template Library Service

ECS Fargate

RDS PostgreSQL

SQS Queues

ElastiCache Redis

Lambda Functions

EventBridge

CloudWatch

Secrets Manager

✅ Automation Data Retention

✅ Right to Data Portability

✅ Right to Erasure

✅ Consent Management

📝 Navigation Guide

1. Create Email Template

2. Add Components

3. Configure Components

4. Preview & Save

📧 Email Template Service

🧩 Email Component Service

📁 Media Storage Service

🎨 Email Rendering Service

🔀 Merge Field Service

📦 Product Grid Service

ECS Fargate

RDS PostgreSQL

S3 Buckets

CloudFront

Lambda Functions

ElastiCache Redis

✅ Email Media Retention

✅ Template Data Export

✅ Template Deletion

✅ Email Content Encryption

1. Send Test Email

2. Configure Delivery

3. Send to List/Automation

4. Monitor Analytics

📤 Email Delivery Service

📊 Email Tracking Service

🚫 Unsubscribe Service

⚠️ Bounce Handling Service

ECS Fargate

AWS SES

SQS Queue

RDS PostgreSQL

ElastiCache Redis

CloudWatch

✅ Email Consent Tracking

✅ One-Click Unsubscribe

✅ Email Data Retention

✅ Email Analytics Export

1. Create Form

2. Add Form Fields

3. Configure Fields

4. Style & Publish

📋 Form Service

📝 Form Field Service

✅ Validation Service

🤖 CAPTCHA Service

🗂️ Form Submission Service

🔗 CRM Mapping Service

ECS Fargate

RDS PostgreSQL

Lambda Functions

S3 Buckets

ElastiCache Redis

SQS Queue

✅ Form Submission Consent

✅ Form Data Retention