Weekly Report Timeout Cleanup

This workflow automatically cleans up users who get stuck in weekly report stages by restoring them to their previous stage and sending a friendly notification message. It runs every Sunday at 9:30 PM to ensure users don't remain indefinitely trapped in report-related workflow states.

Purpose

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

Based on the workflow implementation, this appears to be a maintenance system that prevents users from getting permanently stuck in weekly report generation flows. When users enter report-related stages but don't complete them, this cleanup process automatically restores their previous state and notifies them that their report is ready if they want to view it.

How It Works

1. Daily Check: Every day at 9:30 PM, the workflow triggers but only proceeds on Sundays
2. Find Stuck Users: Queries the database for users whose current stage starts with "weekly_report_"
3. Process Each User: For each stuck user, restores their previous stage and clears the current report stage
4. Send Notification: Sends a friendly message via their preferred channel (WhatsApp or SMS) letting them know their report is ready
5. Loop Continue: Processes all stuck users one by one until complete

The workflow ensures users don't get permanently trapped in report generation flows while providing a gentle nudge that their report is available.

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)
• Actual Execution: Only runs on Sundays (filtered by JavaScript code)
• Timezone: Appears to use EAT (East Africa Time) based on the +3 hour offset in the code

Nodes Used

Node	Type	Purpose
Every Day 9PM	Schedule Trigger	Triggers the workflow daily at 21:30
Check Sunday	Code (JavaScript)	Filters execution to only run on Sundays
Fetch Stuck Users	PostgreSQL	Queries for users stuck in weekly_report_ stages
Loop Stuck Users	Split in Batches	Processes each stuck user individually
Restore Stage	PostgreSQL	Updates user's stage back to previousStage
Is WhatsApp?	IF Condition	Routes to appropriate messaging service
Send WhatsApp Close	Twilio	Sends WhatsApp message to user
Send SMS Close	HTTP Request	Sends SMS via Africa's Talking API

External Services & Credentials Required

PostgreSQL Database

• Credential: "Postgres account"
• Purpose: Store and update user stage information
• Tables: youthEntrepreneursReal

Twilio (WhatsApp)

• Credential: "Twilio account"
• Purpose: Send WhatsApp messages
• From Number: +254203892316

Africa's Talking (SMS)

• Purpose: Send SMS messages
• API Key: Hardcoded in workflow (should be moved to environment variable)
• Username: toll_free_sms_ke
• From: 24436

Environment Variables

Currently, the Africa's Talking API key is hardcoded in the workflow. This should be moved to an environment variable:

• AFRICASTALKING_API_KEY: API key for Africa's Talking SMS service

Data Flow

Input

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

Processing

• User records with fields: phoneNumber, firstName, channel, previousStage
• Stage restoration: currentStage = previousStage, previousStage = NULL

Output

• Database Updates: User stages restored to previous state
• WhatsApp Messages: Friendly notification in Swahili/English mix
• SMS Messages: Same notification content via SMS

Message Template

{firstName}, ripoti yako iko ready ukitaka kuiona. Lala poa! 😊

(Translation: "{firstName}, your report is ready if you want to see it. Sleep well! 😊")

Error Handling

The workflow does not implement explicit error handling. Consider adding: - Try-catch blocks around database operations - Retry logic for failed message sends - Error notifications to administrators - Logging of failed cleanup attempts

Known Limitations

• API key is hardcoded instead of using environment variables
• No error handling for failed database operations or message sending
• No logging or monitoring of cleanup success/failure rates
• Assumes all users have valid phone numbers
• No validation of restored stage values

Related Workflows

Based on the database structure and stage names, this workflow likely relates to: - Weekly report generation workflows - User onboarding/stage management systems - Other youth entrepreneur program workflows

Setup Instructions

1. Import Workflow: Import the JSON into your n8n instance

2. Configure Credentials:

   • Set up PostgreSQL connection with access to youthEntrepreneursReal table
   • Configure Twilio account for WhatsApp messaging
   • Update Africa's Talking API key (move to environment variable)

3. Database Requirements:

   -- Ensure table exists with required columns
   CREATE TABLE IF NOT EXISTS "youthEntrepreneursReal" (
     "phoneNumber" VARCHAR PRIMARY KEY,
     "firstName" VARCHAR,
     "channel" VARCHAR,
     "currentStage" VARCHAR,
     "previousStage" VARCHAR
   );
   

4. Test Setup:

   • Create test users with currentStage starting with 'weekly_report_'
   • Manually trigger the workflow to verify database updates and messaging
   • Check that users are properly restored to their previous stages

5. Security Improvements:

   • Move hardcoded API key to environment variable
   • Add error handling and logging
   • Consider adding admin notifications for cleanup results

6. Activate: Enable the workflow to run automatically on Sundays

Note: This is marked as a backup/unverified workflow. Thoroughly test before activating in production.