Weekly Report Cleanup - Youth Entrepreneurs

This workflow automatically identifies youth entrepreneurs who are stuck in weekly report stages and restores them to their previous stage while sending them a friendly notification. It runs on Sundays to clean up any users who may have gotten stuck in the reporting flow.

Purpose

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

How It Works

1. Sunday Check: The workflow first verifies it's running on a Sunday (using East Africa Time zone offset)
2. Find Stuck Users: Queries the database for users whose current stage matches the pattern weekly_report_%
3. 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

The workflow processes users in batches to avoid overwhelming external services and ensures each user gets personalized communication in their preferred language (Swahili).

Workflow Diagram

graph TD
    A[Manual Trigger Only] --> 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

Trigger

• Type: Manual Trigger
• Schedule: None (this is a backup/demo version - production version would likely use a cron trigger)
• Note: The workflow name indicates this is a backup/demo target that should not be activated

Nodes Used

Node Type	Node Name	Purpose
Manual Trigger	Manual Trigger Only	Starts the workflow manually for testing
Code	Check Sunday	Validates that today is Sunday using EAT timezone
Postgres	Fetch Stuck Users	Queries for users stuck in weekly_report stages
Split in Batches	Loop Stuck Users	Processes users one by one
Postgres	Restore Stage	Updates user's stage back to previous stage
If	Is WhatsApp?	Determines communication channel preference
Twilio	Send WhatsApp Close	Sends WhatsApp message to users
HTTP Request	Send SMS Close	Sends SMS via Africa's Talking API

External Services & Credentials Required

Postgres Database

• Credential Name: "Postgres account"
• Purpose: Access to youthEntrepreneursReal table
• Required Fields: Host, database name, username, password

Twilio (WhatsApp)

• Credential Name: "Twilio account"
• Purpose: Send WhatsApp messages
• Required Fields: Account SID, Auth Token
• From Number: +254203892316

Africa's Talking (SMS)

• API Key: Embedded in workflow (should be moved to credentials)
• Username: toll_free_sms_ke
• From Number: 24436
• Purpose: Send SMS messages

Environment Variables

No environment variables are used in this workflow. The Africa's Talking API key is hardcoded and should be moved to a secure credential store.

Data Flow

Input

• No external input required
• Workflow reads from youthEntrepreneursReal database table

Database Schema Expected

youthEntrepreneursReal:
- phoneNumber (string)
- firstName (string) 
- channel (string) - "WhatsApp" or other
- currentStage (string) - patterns like "weekly_report_*"
- previousStage (string) - stage to restore to

Output

• Database updates (stage restoration)
• WhatsApp/SMS notifications sent to users
• No return data from workflow

Error Handling

The workflow has minimal error handling: - If it's not Sunday, the workflow stops early (returns empty array) - No explicit error handling for database failures or messaging service failures - Split in Batches node will continue processing remaining users if one fails

Known Limitations

• This is marked as a backup/demo version and should not be activated
• API key is hardcoded instead of using secure credentials
• No error handling for failed database operations or message sending
• No logging or monitoring of successful operations
• Timezone handling is hardcoded for East Africa Time (+3 hours)

Related Workflows

No related workflows mentioned in the provided context.

Setup Instructions

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

2. Configure Database Connection:

   • Create a Postgres credential named "Postgres account"
   • Connect to your database containing the youthEntrepreneursReal table

3. Configure Twilio:

   • Create a Twilio credential named "Twilio account"
   • Ensure your Twilio number (+254203892316) is configured for WhatsApp

4. Configure Africa's Talking:

   • Replace the hardcoded API key with a proper credential
   • Verify the username "toll_free_sms_ke" and from number "24436"

5. Database Setup:

   • Ensure your youthEntrepreneursReal table has the required columns
   • Test with sample data where currentStage contains "weekly_report_" pattern

6. Testing:

   • This is a manual trigger workflow - test carefully before creating a production version
   • Consider adding proper error handling and logging before production use

7. Production Deployment:

   • Create a new workflow based on this one
   • Add a cron trigger for Sunday execution
   • Implement proper credential management
   • Add comprehensive error handling and monitoring