Skip to content

Weekly Report 9PM 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 to view.

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 automation that prevents users from getting permanently stuck in weekly report collection flows. It runs weekly to identify users who haven't completed their weekly reports and gracefully moves them back to their previous interaction stage while notifying them that their report is available.

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 stage to the previous stage (or "idle" if no previous stage exists)
    • Clears the previous stage field
    • Sends a personalized notification via their preferred channel (WhatsApp or SMS)
  4. Channel-Specific Messaging: Uses WhatsApp for users on that channel, falls back to SMS for others

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)

Nodes Used

Node Type Node Name Purpose
Schedule Trigger Every Day 9PM Triggers daily at 9:30 PM
Code Check Sunday Filters execution to Sundays only
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 state
If Is WhatsApp? Determines which messaging channel to use
Twilio Send WhatsApp Close Sends WhatsApp notification
HTTP Request Send SMS Close Sends SMS notification via Africa's Talking

External Services & Credentials Required

Database

  • PostgreSQL: Stores user data and stage information
  • 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: Embedded in workflow (atsk_ae3de07489f045ef1532a7e407df689bcadff2dbc2555361f3055c6774d779c77c5d9954)
    • Username: toll_free_sms_ke
    • Short Code: 24436

Environment Variables

This workflow doesn't use environment variables. All configuration is embedded in the nodes.

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 logic: currentStage = previousStage OR 'idle'

Output

  • Database Updates: User stages restored
  • Notifications: WhatsApp or SMS messages sent
  • Message Content: "{firstName}, ripoti yako iko ready ukitaka kuiona. Lala poa! 😊"

Error Handling

The workflow uses basic error handling through n8n's built-in mechanisms: - Database connection failures will stop execution - API failures for messaging services will be logged but won't prevent other users from being processed - The Split in Batches node ensures one failed user doesn't block others

Known Limitations

  • API keys are hardcoded in the workflow rather than using secure credential management
  • No retry logic for failed message deliveries
  • Timezone handling assumes EAT (UTC+3) for Sunday detection
  • No logging or monitoring of cleanup statistics

No related workflows identified from the provided context.

Setup Instructions

  1. Import Workflow 

    1
    # Import the JSON file into your n8n instance

  2. Configure Database Credential

    • Create a PostgreSQL credential named "Postgres account"
    • Ensure access to the youthEntrepreneursReal table

  3. Configure Twilio Credential

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

  4. Configure Africa's Talking

    • Update the API key in the "Send SMS Close" node
    • Verify the username and short code are correct for your account

  5. Database Schema Requirements 

    1
2
3
4
5
6
7
    -- Ensure your table has these columns:
ALTER TABLE "youthEntrepreneursReal" 
ADD COLUMN IF NOT EXISTS "currentStage" TEXT,
ADD COLUMN IF NOT EXISTS "previousStage" TEXT,
ADD COLUMN IF NOT EXISTS "phoneNumber" TEXT,
ADD COLUMN IF NOT EXISTS "firstName" TEXT,
ADD COLUMN IF NOT EXISTS "channel" TEXT;

  6. Test the Workflow

    • Create test users with currentStage like 'weekly_report_test'
    • Run the workflow manually to verify database updates and messaging

  7. Activate

    • Enable the workflow to run automatically
    • Monitor logs for the first few Sunday executions