Complete Automation Workflow Documentation

Overview

This document explains the complete flow of how automation workflows work, covering both scheduled and trigger-based workflows. This follows the same pattern used by major email marketing platforms like Mailchimp, SendGrid, and HubSpot.

---

🏗️ Database Schema & Tables

1. automation_workflows Table

Purpose: Defines the automation campaign (like a "campaign template" in Mailchimp)

Column	Purpose	Example
id	Unique workflow identifier	129
name	Workflow name	"Weekly Executive Report"
workflow_type	Type: scheduled, trigger, or persona	"scheduled"
template_id	Email template to use	135
schedule_config	JSON: Frequency settings (for scheduled workflows)	{"frequency":"daily","time":"04:13"}
trigger_config	JSON: Trigger events (for trigger workflows)	{"events":["morale_drop_baseline"],"last_trigger_check_at":"..."}
audience_config	JSON: Who to send to	{"personas":["Executive"]}
status	active, paused, archived	"active"
last_run_at	When workflow last executed	2025-12-17 04:12:16 UTC
next_run_at	When workflow should run next (scheduled only)	2025-12-18 04:13:00 UTC

Key Points: - next_run_at is the scheduling mechanism for scheduled workflows. When it's NULL, the workflow hasn't been scheduled yet (first run pending). - trigger_config contains events array and last_trigger_check_at timestamp for trigger-based workflows. - schedule_config is only used for scheduled workflows; trigger_config is only used for trigger workflows.

---

2. comms_instances Table

Purpose: Represents one execution of a workflow (like a "campaign send" in Mailchimp)

Column	Purpose	Example
id	Unique instance identifier	27
automation_workflow_id	Links to workflow	129
template_id	Template used	135
scheduled_at	When this instance should send	2025-12-17 04:13:00 UTC
audience_scope	JSON: Snapshot of who received it	{"stakeholder_ids":[45]}
status	scheduled, sent, failed	"scheduled"

Key Point: Each workflow execution creates a NEW CommsInstance. This is like Mailchimp creating a new "campaign send" record each time.

---

3. comms_deliveries Table

Purpose: Represents one email to one person (like a "recipient" in Mailchimp)

Column	Purpose	Example
id	Unique delivery identifier	278
comms_instance_id	Links to instance	27
stakeholder_id	Who receives it	45
channel	email, sms, teams	"email"
send_at	When to send this email	2025-12-17 04:13:00 UTC
status	scheduled, pending, paused, sent, delivered, failed, cancelled	"sent"
merged_payload	JSON: Final email content	{"subject":"...","body":"..."}

Key Point: Each person gets a NEW CommsDelivery for each workflow run. If a workflow runs daily, each person gets a new delivery record every day.

---

🔄 Scheduled Workflows: Complete Flow

Phase 1: Workflow Creation (One-time setup)

User creates workflow:
├─ automation_workflows record created
├─ workflow_type = "scheduled"
├─ schedule_config = {"frequency":"daily","time":"04:13"}
├─ audience_config = {"personas":["Executive"]}
├─ status = "active"
├─ last_run_at = NULL
└─ next_run_at = NULL  ← KEY: Not scheduled yet!

What happens: Workflow exists but hasn't run yet. next_run_at is NULL.

---

Phase 2: First Run (User clicks "Activate" or automated)

Step 2.1: Calculate send_at time

# In DeliveriesConcern or rake task
send_at = workflow.calculate_next_run_at
# Returns: 2025-12-17 04:13:00 UTC (next occurrence based on schedule)

Step 2.2: Create CommsInstance (one per workflow run)

# In DeliveryScheduleService
CommsInstance.create!(
  automation_workflow_id: 129,
  template_id: 135,
  scheduled_at: 2025-12-17 04:13:00 UTC,
  audience_scope: {"stakeholder_ids":[45], "personas":["Executive"]}
)
# Returns: CommsInstance id=27

Key Point: This is a NEW instance. Each workflow run creates a new one.

Step 2.3: Create CommsDelivery records (one per person)

# For each stakeholder in audience
CommsDelivery.create!(
  comms_instance_id: 27,
  stakeholder_id: 45,
  channel: "email",
  send_at: 2025-12-17 04:13:00 UTC,
  status: "scheduled",  # Scheduled workflows start as "scheduled"
  merged_payload: "{...}"
)
# Returns: CommsDelivery id=278

Key Point: Each person gets a NEW delivery record. If 10 people are in the audience, 10 delivery records are created.

Step 2.4: Schedule jobs and update workflow

# Schedule variable replacement job (via after_create callback)
delivery.schedule_variable_replacement_job
# Creates Delayed::Job with run_at = send_at

# Update workflow tracking
workflow.update(
  last_run_at: Time.current,        # 2025-12-17 04:12:16 UTC
  next_run_at: calculated_next,    # 2025-12-18 04:13:00 UTC ← KEY!
  status: 'active'
)

Key Point: next_run_at is now set to the next occurrence. This is what the rake task will check.

---

Phase 3: Email Sending (At send_at time)

Step 3.1: Variable Replacement Job runs

At 2025-12-17 04:13:00 UTC:
├─ TemplateVariableReplacementJob runs
├─ Resolves template variables (e.g., {{project.name}})
├─ Updates CommsDelivery.merged_payload
└─ Triggers CommsDeliverySendJob

Step 3.2: Email Send Job runs

CommsDeliverySendJob runs:
├─ Validates delivery
├─ Calls ApplicationMailer.delivery_email(delivery)
├─ Sends email via SMTP/Postmark
├─ Updates CommsDelivery.status = "sent"
└─ Creates CommsEvent record

Result: Email is sent. CommsDelivery status changes to "sent".

---

Phase 4: Recurring Execution (Automated)

Step 4.1: Rake task runs (every 5 minutes via Cloud66)

# In lib/tasks/automation_workflows.rake
rake automation_workflows:process_due

# Finds workflows where:
AutomationWorkflow.due_to_run
# SQL: WHERE status='active' 
#   AND (next_run_at IS NULL OR next_run_at <= NOW())
#   AND workflow_type='scheduled'

Key Point: The rake task finds workflows where: - next_run_at <= Time.current (due now), OR - next_run_at IS NULL (first run never scheduled)

Step 4.2: Process each due workflow

# For each due workflow:
workflow = AutomationWorkflow.find(129)
# next_run_at = 2025-12-18 04:13:00 UTC (from previous run)

# Calculate send_at (use next_run_at if set, otherwise calculate)
send_at = workflow.next_run_at || workflow.calculate_next_run_at
# send_at = 2025-12-18 04:13:00 UTC

# Create NEW CommsInstance
service = DeliveryScheduleService.new(workflow)
result = service.create_deliveries(send_at: send_at, created_by: nil)
# Creates:
#   - NEW CommsInstance (id=28)
#   - NEW CommsDelivery records (id=279, 280, ...)
#   - Updates workflow.next_run_at = 2025-12-19 04:13:00 UTC

Key Point: Each run creates NEW records. This is exactly like Mailchimp: - Run 1: Creates CommsInstance id=27, CommsDelivery id=278 - Run 2: Creates CommsInstance id=28, CommsDelivery id=279 - Run 3: Creates CommsInstance id=29, CommsDelivery id=280 - And so on...

---

🔔 Trigger-Based Workflows: Complete Flow

Phase 1: Workflow Creation (One-time setup)

User creates workflow:
├─ automation_workflows record created
├─ workflow_type = "trigger"
├─ trigger_config = {"events":["morale_drop_baseline","kiosk_offline"]}
├─ audience_config = {"personas":["Executive"]}
├─ status = "active"
├─ last_run_at = NULL
└─ last_trigger_check_at = NULL  ← Tracked in trigger_config

What happens: Workflow exists but NO deliveries are created. The workflow is just configuration waiting for triggers to fire.

---

Phase 2: Activation (User clicks "Activate")

For trigger workflows, "Activate" does NOT create deliveries:

# In DeliveriesConcern#create_deliveries
if @workflow.workflow_type == 'trigger'
  @workflow.update_columns(status: 'active', last_run_at: Time.current)
  # NO deliveries created - just activates the workflow
  return
end

Key Point: Trigger workflows are event-driven. Deliveries are only created when triggers fire, not when activated.

---

Phase 3: Trigger Evaluation (Every 5 minutes via cron)

Step 3.1: Cron job runs

# Scheduled in Cloud66
*/5 * * * * bundle exec rake automation_workflows:process_triggers

Step 3.2: TriggerEvaluatorService evaluates all active trigger workflows

# In TriggerEvaluatorService
active_trigger_workflows = AutomationWorkflow.trigger_type_active.includes(:template)

active_trigger_workflows.each do |workflow|
  # For each trigger event configured:
  trigger_events.each do |event_key|
    evaluator = resolve_evaluator_class(event_key).new(workflow)
    triggered_entities = evaluator.evaluate

    if triggered_entities.present?
      # Trigger fired! Create deliveries
      process_triggered_entities(workflow, event_key, triggered_entities)
    end
  end

  workflow.update_last_trigger_check_at!(Time.current)
end

Key Point: The system evaluates all configured trigger events. If ANY event fires, deliveries are created.

---

Phase 4: When Trigger Fires (Delivery Creation)

Step 4.1: DeliveryScheduleService creates deliveries

# In TriggerEvaluatorService#process_triggered_entities
service = DeliveryScheduleService.new(workflow)
result = service.create_deliveries(
  send_at: Time.current,  # ALWAYS immediate for triggers
  created_by: nil
)

Step 4.2: Creates CommsInstance and CommsDelivery records

# Creates NEW CommsInstance
CommsInstance.create!(
  automation_workflow_id: 129,
  template_id: 135,
  scheduled_at: Time.current,  # Immediate
  audience_scope: {...}
)

# Creates NEW CommsDelivery records (one per stakeholder)
CommsDelivery.create!(
  comms_instance_id: 27,
  stakeholder_id: 45,
  channel: "email",
  send_at: Time.current,  # ALWAYS immediate, never future
  status: "pending",  # Immediate processing (not "scheduled")
  merged_payload: "{...}"
)

Key Points: - send_at is ALWAYS Time.current for trigger workflows (never future) - status is ALWAYS 'pending' for trigger workflows (immediate processing) - Variables are resolved at send_at time (which is immediate) for fresh data

---

Phase 5: Email Sending (Immediate)

Since send_at = Time.current and status = 'pending', the email sending process starts immediately:

1. TemplateVariableReplacementJob runs (scheduled immediately)
   ├─ Resolves template variables with fresh data
   ├─ Updates CommsDelivery.merged_payload
   └─ Triggers CommsDeliverySendJob

2. CommsDeliverySendJob runs
   ├─ Validates delivery
   ├─ Calls ApplicationMailer.delivery_email(delivery)
   ├─ Sends email via SMTP/Postmark
   ├─ Updates CommsDelivery.status = "sent"
   └─ Creates CommsEvent record

Result: Email is sent immediately when trigger fires.

---

📊 Available Trigger Events

1. Morale Drop Below Baseline

Trigger Key: morale_drop_baseline

Condition: Current morale drops below 80% of baseline morale

Logic: - Calculates current morale from last 7 days of check-ins - Compares against 3-month baseline morale average - Triggers when: Current_Morale < (Baseline_Morale * 0.8)

Use Case: Alert when team morale significantly drops below historical average

---

2. Morale Drop 2-Week Streak

Trigger Key: morale_drop_streak

Condition: Morale decreases for 10+ consecutive days with 5+ check-ins

Logic: - Analyzes last 14 days of daily morale data - Checks for consecutive days where morale decreased - Requires minimum 5 check-ins during the period - Triggers when: 10+ consecutive days show declining morale

Use Case: Alert when there's a sustained negative trend in team morale

---

3. Kiosk Device Offline

Trigger Key: kiosk_offline

Condition: Kiosk heartbeat missing for 30+ minutes

Logic: - Checks latest heartbeat timestamp for each kiosk - Compares against current time - Triggers when: Kiosk_Last_Heartbeat < (Current_Time - 30 minutes)

Use Case: Alert when a kiosk device goes offline or stops reporting

---

4. New SpotCheck Launched

Trigger Key: spotcheck_new

Condition: A new SpotCheck is created in the database

Logic: - Tracks last_trigger_check_at timestamp - Finds SpotChecks created since last check - Triggers immediately when new SpotCheck is detected

Use Case: Notify stakeholders when a new SpotCheck is launched

---

5. Low SpotCheck Completion

Trigger Key: spotcheck_low_completion

Condition: No responses received 8 hours after SpotCheck creation

Logic: - Finds SpotChecks older than 8 hours - Checks for any responses (answers or submissions) - Triggers when: SpotCheck created > 8 hours ago with zero responses

Use Case: Alert when a SpotCheck isn't getting engagement

---

🔑 Key Differences: Scheduled vs Trigger Workflows

Aspect	Scheduled	Trigger-Based
When created	Configuration only	Configuration only
When deliveries created	Ahead of time (when workflow created/activated)	ONLY when trigger fires (via cron)
Deliveries on creation	✅ Creates delivery for next scheduled time	❌ NO deliveries
Deliveries on activation	✅ Creates delivery for next scheduled time	❌ NO deliveries
send_at	Calculated from schedule (future time)	Time.current (immediate, never future)
Delivery status	'scheduled' (future processing)	'pending' (immediate processing)
Schedule config	Required (frequency, time, etc.)	Not used
Trigger config	Not used	Required (events)
nextrunat	Used for scheduling	Not used
lasttriggercheck_at	Not used	Tracked in trigger_config
Data freshness	Fresh at each scheduled run	Fresh when trigger fires
Preview	"Preview" button	"View Details" button
Delivery Schedule Page	Shows "Scheduled", "Sent", or "Failed"	Shows "Sent" or "Failed" only

---

📊 Recurring Execution: How New Records Are Created

Key Question: Do We Create New Records Each Time?

YES! Each time the workflow runs, we create NEW CommsInstance and CommsDelivery records. Old records with status: "sent" remain as historical data.

Example: Daily Scheduled Workflow at 9 AM

Day 1: First Run

User clicks "Activate" → Creates:

┌─────────────────────────────────────────┐
│ CommsInstance (NEW)                     │
│ - id: 27                                │
│ - automation_workflow_id: 129           │
│ - scheduled_at: 2025-12-17 09:00:00    │
└─────────────────────────────────────────┘
           │
           ├─ Creates CommsDelivery (NEW)
           │  - id: 278
           │  - status: "scheduled"
           │
           └─ Creates CommsDelivery (NEW)
              - id: 279
              - status: "scheduled"

At 09:00:00:
- TemplateVariableReplacementJob runs
- CommsDeliverySendJob runs
- Emails sent!

CommsDelivery updates:
- id: 278, status: "sent"
- id: 279, status: "sent"

Workflow updates:
- last_run_at: 2025-12-17 09:00:00
- next_run_at: 2025-12-18 09:00:00

Day 2: Second Run (Automated via rake task)

Rake task finds workflow where next_run_at <= NOW():
- automation_workflows.id = 129
- next_run_at = 2025-12-18 09:00:00 (due now)

Creates NEW records:

┌─────────────────────────────────────────┐
│ CommsInstance (NEW - Different ID!)     │
│ - id: 28  ← NEW ID                      │
│ - automation_workflow_id: 129           │
│ - scheduled_at: 2025-12-18 09:00:00    │
└─────────────────────────────────────────┘
           │
           ├─ Creates CommsDelivery (NEW)
           │  - id: 280  ← NEW ID (not 278!)
           │  - status: "scheduled"
           │
           └─ Creates CommsDelivery (NEW)
              - id: 281  ← NEW ID (not 279!)
              - status: "scheduled"

At 09:00:00:
- TemplateVariableReplacementJob runs
- CommsDeliverySendJob runs
- Emails sent!

CommsDelivery updates:
- id: 280, status: "sent"  ← NEW record
- id: 281, status: "sent"  ← NEW record

Workflow updates:
- last_run_at: 2025-12-18 09:00:00
- next_run_at: 2025-12-19 09:00:00

Day 3: Third Run (Automated)

Creates ANOTHER set of NEW records:

CommsInstance:
- id: 29  ← NEW ID

CommsDelivery:
- id: 282  ← NEW ID, status: "scheduled" → "sent"
- id: 283  ← NEW ID, status: "scheduled" → "sent"

Old records (id: 278, 279, 280, 281) remain with status: "sent" as history

Key Points

1. Each run creates NEW records - CommsInstance and CommsDelivery get new IDs each time
2. Old "sent" records remain - They're historical data, not reused
3. New records start with status: "scheduled" - They then progress to "sent"
4. Status "sent" doesn't block anything - Because we create new records, not update old ones
5. This is exactly how Mailchimp/SendGrid work - Each campaign send creates new records

---

📊 Visual Flow Diagrams

Scheduled Workflow Flow

┌─────────────────────────────────────────────────────────────┐
│ 1. WORKFLOW CREATION (One-time)                              │
│    automation_workflows:                                     │
│    - id: 129                                                 │
│    - next_run_at: NULL                                       │
└─────────────────────────────────────────────────────────────┘
                          │
                          ▼
┌─────────────────────────────────────────────────────────────┐
│ 2. FIRST RUN (User clicks "Activate" or rake task)          │
│    ├─ Calculate send_at: 2025-12-17 04:13:00                 │
│    ├─ Create CommsInstance (id=27)                          │
│    ├─ Create CommsDelivery records (id=278, 279, ...)        │
│    └─ Update workflow:                                      │
│       - last_run_at: 2025-12-17 04:12:16                    │
│       - next_run_at: 2025-12-18 04:13:00  ← KEY!            │
└─────────────────────────────────────────────────────────────┘
                          │
                          ▼
┌─────────────────────────────────────────────────────────────┐
│ 3. EMAIL SENDING (At send_at time)                           │
│    ├─ TemplateVariableReplacementJob runs                   │
│    ├─ CommsDeliverySendJob runs                             │
│    └─ Email sent, status = "sent"                           │
└─────────────────────────────────────────────────────────────┘
                          │
                          ▼
┌─────────────────────────────────────────────────────────────┐
│ 4. RECURRING EXECUTION (Every 5 min, rake task checks)      │
│    ├─ Find workflows where next_run_at <= NOW()            │
│    ├─ For workflow 129: next_run_at = 2025-12-18 04:13:00  │
│    ├─ Create NEW CommsInstance (id=28)                      │
│    ├─ Create NEW CommsDelivery records (id=279, ...)        │
│    └─ Update workflow:                                      │
│       - last_run_at: 2025-12-18 04:12:16                    │
│       - next_run_at: 2025-12-19 04:13:00  ← Next cycle!     │
└─────────────────────────────────────────────────────────────┘
                          │
                          ▼
                    (Repeats forever)

Trigger-Based Workflow Flow

┌─────────────────────────────────────────────────────────────┐
│ 1. WORKFLOW CREATION                                         │
│    User creates workflow with:                             │
│    - Workflow Type: "Trigger-based Alert"                   │
│    - Trigger Events: Select one or more events               │
│    - Template: Communication template to send              │
│    - Audience: Personas and projects to target              │
│                                                              │
│    Result: AutomationWorkflow record created                │
│    (No deliveries created yet - just configuration)         │
└─────────────────────────────────────────────────────────────┘
                          │
                          ▼
┌─────────────────────────────────────────────────────────────┐
│ 2. ACTIVATION (User clicks "Activate")                      │
│    ├─ Updates status: 'active'                              │
│    ├─ Updates last_run_at: Time.current                     │
│    └─ NO deliveries created (just activates workflow)       │
└─────────────────────────────────────────────────────────────┘
                          │
                          ▼
┌─────────────────────────────────────────────────────────────┐
│ 3. TRIGGER EVALUATION (Every 5 minutes via cron)            │
│    Cron Job: rake automation_workflows:process_triggers      │
│      → TriggerEvaluatorService.evaluate_all                 │
│        → For each active trigger workflow:                  │
│          → Check which trigger events are enabled            │
│          → Evaluate each trigger condition                  │
│          → If trigger fires: Continue to step 4            │
│          → If no trigger: Nothing happens                   │
└─────────────────────────────────────────────────────────────┘
                          │
                          ▼
┌─────────────────────────────────────────────────────────────┐
│ 4. WHEN TRIGGER FIRES                                        │
│    DeliveryScheduleService.create_deliveries called:         │
│      ✅ Creates 1 CommsInstance (snapshot of the send)      │
│      ✅ Creates CommsDelivery records (one per stakeholder)  │
│      ✅ send_at = Time.current (immediate)                   │
│      ✅ status = 'pending' (immediate processing)            │
└─────────────────────────────────────────────────────────────┘
                          │
                          ▼
┌─────────────────────────────────────────────────────────────┐
│ 5. EMAIL SENDING (Immediate)                                 │
│    ├─ TemplateVariableReplacementJob runs (immediate)      │
│    ├─ CommsDeliverySendJob runs                             │
│    └─ Email sent, status = "sent"                           │
└─────────────────────────────────────────────────────────────┘

---

🔑 Key Concepts (Like Mailchimp/SendGrid)

1. Workflow = Campaign Template

• Defines: Who, What, When (or What triggers)
• Stored in: automation_workflows
• Tracks: last_run_at, next_run_at (scheduled), last_trigger_check_at (trigger)

2. Instance = Campaign Send

• One execution of the workflow
• Stored in: comms_instances
• Each run creates a NEW instance

3. Delivery = Email to One Person

• One email to one recipient
• Stored in: comms_deliveries
• Each person gets a NEW delivery per run

4. Scheduling = next_run_at (Scheduled Only)

• Workflow tracks when to run next
• Rake task checks next_run_at <= NOW()
• After each run, next_run_at is updated to next cycle

5. Trigger Evaluation = Cron Job (Trigger Only)

• Runs every 5 minutes via rake automation_workflows:process_triggers
• Evaluates all active trigger workflows
• Creates deliveries when triggers fire

---

🚀 Cloud66 Deployment Requirements

Required Cron Jobs

1. Scheduled Workflows (if not already configured): bash */5 * * * * bundle exec rake automation_workflows:process_due RAILS_ENV=production

2. Trigger-Based Workflows (REQUIRED): bash */5 * * * * bundle exec rake automation_workflows:process_triggers RAILS_ENV=production

Required Background Worker

• Worker Process: worker_default (already in config/.service.yml)
  • Command: bundle exec rake jobs:work
  • Queue: default
  • MUST be running to process TemplateVariableReplacementJob and CommsDeliverySendJob

Verification Commands

# Check trigger workflows status
bundle exec rake automation_workflows:trigger_status

# Manually test trigger processing
bundle exec rake automation_workflows:process_triggers

# Check scheduled workflows
bundle exec rake automation_workflows:status

Critical: Both the cron job and background worker must be running for the system to work properly.

---

📝 Summary

1. Workflow = Template (defines schedule/triggers, audience, template)
2. Instance = One execution (created each time workflow runs)
3. Delivery = One email (created for each person, each run)
4. Scheduled workflows: Use next_run_at for scheduling, create deliveries ahead of time
5. Trigger workflows: Use cron job for evaluation, create deliveries only when triggers fire
6. Rake tasks = Automated processors (runs every 5 min, creates new instances/deliveries)
7. Each run creates NEW records - old "sent" records remain as history

This is exactly how Mailchimp, SendGrid, and HubSpot handle recurring campaigns and trigger-based automations! 🎯