Youth Entrepreneurs Weekly Report Cleanup

This workflow automatically resets users who are stuck in weekly report stages back to their previous stage and sends them a friendly notification. It runs on Sundays to clean up any users who may have gotten stuck in the reporting flow during the week.

Purpose

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

Based on the workflow structure, 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 stages, restoring them to their previous position in the program flow and notifying them that their report is ready for viewing.

How It Works

1. Manual Trigger: The workflow starts with a manual trigger (no automatic scheduling in this demo version)
2. Sunday Check: Verifies that today is Sunday using East Africa Time (EAT) - if not Sunday, the workflow stops
3. Find Stuck Users: Queries the database for users whose current stage starts with "weekly_report_"
4. Process Each User: Loops through each stuck user individually
5. Restore Stage: Updates the user's current stage back to their previous stage (or 'idle' if no previous stage exists)
6. Channel Detection: Checks if the user's preferred communication channel is WhatsApp
7. Send Notification: Sends either a WhatsApp message or SMS in Swahili, letting them know their report is ready

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
    D -->|Complete| I[End]

Trigger

• Type: Manual Trigger
• Schedule: None (this is a demo/backup version)
• Notes: The workflow name indicates this should normally run on a cron schedule, but this version requires manual execution

Nodes Used

Node Type	Node Name	Purpose
Manual Trigger	Manual Trigger Only	Starts the workflow manually
Code	Check Sunday	Validates that today is Sunday in 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
Twilio	Send WhatsApp Close	Sends WhatsApp notification
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: Hardcoded in workflow (should be moved to environment variable)
• Username: toll_free_sms_ke
• From Number: 24436

Environment Variables

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

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

Data Flow

Input

• No external input required
• Reads from youthEntrepreneursReal database table

Database Schema (inferred)

youthEntrepreneursReal:
- phoneNumber (string)
- firstName (string) 
- channel (string) - "WhatsApp" or other
- currentStage (string)
- previousStage (string, nullable)

Output

• Database updates: Users moved from weekly_report_ stages back to previous stages
• Notifications sent via WhatsApp or SMS in Swahili

Error Handling

The workflow has minimal error handling: - If it's not Sunday, the workflow stops gracefully with an empty array - Database and API errors would cause the workflow to fail - No retry logic or error notifications are implemented

Known Limitations

• API key is hardcoded instead of using environment variables
• No error handling for failed API calls
• Manual trigger only (no automatic scheduling in this version)
• Timezone calculation assumes EAT (UTC+3) but doesn't account for daylight saving
• No logging or monitoring of successful operations

Related Workflows

No related workflows identified from the current context.

Setup Instructions

1. Import Workflow

   • Import the JSON into your n8n instance
   • The workflow will be inactive by default

2. Configure Database Connection

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

3. Set Up 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 your Africa's Talking API key
   • Verify the username "toll_free_sms_ke" and sender ID "24436" are correct

5. Test the Workflow

   • Run manually to test functionality
   • Verify database updates and message delivery

6. Production Deployment

   • Replace manual trigger with a cron trigger for Sundays
   • Move API keys to environment variables
   • Add proper error handling and logging
   • Remove "DEMO TARGET" and "BACKUP" from the workflow name