Skip to content

Weekly Report Timeout Cleanup

This workflow automatically cleans up users who are stuck in weekly report stages every Sunday at 9:30 PM, restoring them to their previous stage and sending a friendly notification that their report is ready for viewing.

Purpose

No business context provided yet — add a context.md to enrich this documentation.

Based on the workflow implementation, this appears to be part of a youth entrepreneurship program that tracks user progress through various stages. The workflow specifically handles users who get stuck in weekly report generation stages, ensuring they don't remain indefinitely blocked and receive appropriate notifications about their report status.

How It Works

  1. Daily Schedule Check: Every day at 9:30 PM, the workflow triggers and checks if it's Sunday
  2. Sunday-Only Execution: If it's not Sunday, the workflow stops; if it is Sunday, it continues
  3. Find Stuck Users: Queries the database for users whose current stage starts with "weekly_report_"
  4. Process Each User: For each stuck user, the workflow:
    • Restores their stage to their previous stage (or "idle" if no previous stage exists)
    • Clears their previous stage field
    • Checks their preferred communication channel
    • Sends an appropriate notification (WhatsApp or SMS) letting them know their report is ready

Workflow Diagram

graph TD
    A[Every Day 9PM] --> B[Check Sunday]
    B --> C[Fetch Stuck Users]
    C --> D[Loop Stuck Users]
    D --> E[Restore Stage]
    E --> F[Is WhatsApp?]
    F -->|Yes| G[Send WhatsApp Close]
    F -->|No| H[Send SMS Close]
    G --> D
    H --> D
    D -->|Complete| I[End]

Trigger

  • Type: Schedule Trigger
  • Frequency: Daily at 9:30 PM (21:30)
  • Timezone: Appears to use EAT (East Africa Time) based on the code logic
  • Actual Execution: Only runs on Sundays despite daily trigger

Nodes Used

Node Type Node Name Purpose
Schedule Trigger Every Day 9PM Triggers workflow daily at 21:30
Code Check Sunday Filters execution to Sundays only using EAT timezone
Postgres Fetch Stuck Users Queries for users stuck in weekly_report stages
Split in Batches Loop Stuck Users Processes each stuck user individually
Postgres Restore Stage Updates user's stage back to previous or idle
IF Is WhatsApp? Routes to appropriate messaging service
Twilio Send WhatsApp Close Sends WhatsApp notification
HTTP Request Send SMS Close Sends SMS via Africa's Talking API

External Services & Credentials Required

Database

  • PostgreSQL: Stores user data in youthEntrepreneursReal table
  • Credential: "Postgres account" (ID: EJPqF6MDH1ZwAzyv)

Messaging Services

  • Twilio: For WhatsApp messaging
    • Credential: "Twilio account" (ID: dEOy4AckE29MTkk3)
    • From Number: +254203892316
  • Africa's Talking: For SMS messaging
    • API Key: Required in headers (currently hardcoded)
    • Username: toll_free_sms_ke
    • Short Code: 24436

Environment Variables

No environment variables are used in this workflow. All configuration is hardcoded, including: - API keys (should be moved to credentials) - Phone numbers - Message templates

Data Flow

Input

  • Trigger: Time-based (Sunday 9:30 PM EAT)
  • Database Query: Users with currentStage LIKE 'weekly_report_%'

Processing

  • Filters by day of week (Sunday only)
  • Loops through each stuck user
  • Updates database records
  • Routes messages by channel preference

Output

  • Database Updates: User stages restored to previous state
  • WhatsApp Messages: Sent via Twilio for WhatsApp users
  • SMS Messages: Sent via Africa's Talking for SMS users
  • Message Content: "{{firstName}}, ripoti yako iko ready ukitaka kuiona. Lala poa! 😊"

Error Handling

This workflow has minimal error handling: - Day Check: Gracefully exits if not Sunday - Database Errors: No explicit handling - will fail workflow execution - API Failures: No retry logic or fallback mechanisms - Missing Data: Uses fallback name "Rafiki" if firstName is null

Known Limitations

  • API keys are hardcoded instead of using proper credentials
  • No error handling for failed API calls
  • No logging or monitoring of cleanup operations
  • Timezone handling relies on manual offset calculation
  • No validation of phone number formats
  • Message content is hardcoded in Swahili

No related workflows identified from the provided context.

Setup Instructions

  1. Import Workflow 

    1
    # Import the JSON into your n8n instance

  2. Configure Database Connection

    • Set up PostgreSQL credential named "Postgres account"
    • Ensure access to youthEntrepreneursReal table with required fields:
      • phoneNumber, firstName, channel, currentStage, previousStage

  3. Configure Twilio

    • Create Twilio credential named "Twilio account"
    • Verify WhatsApp sender number (+254203892316)

  4. Configure Africa's Talking

    • Replace hardcoded API key with proper credential
    • Verify SMS short code (24436) and username (toll_free_sms_ke)

  5. Activate Workflow

    • Enable the workflow to start Sunday cleanup cycles
    • Monitor first few executions to ensure proper operation

  6. Recommended Improvements

    • Move API keys to proper credential management
    • Add error handling and retry logic
    • Implement logging for audit trails
    • Add validation for phone numbers and user data