Weekly Report Conversation Handler

This workflow manages interactive weekly business report conversations with young entrepreneurs, guiding them through a structured review of their profit, credit management, and business advice completion. It provides personalized feedback and sets goals for the upcoming week while adapting message formatting for both WhatsApp and SMS channels.

Purpose

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

This workflow serves as the conversational interface for delivering weekly business performance reports to users. It handles the interactive flow where users can choose to view their full report, get a quick summary, or defer the report to later. The system provides detailed breakdowns of profit trends, credit recovery progress, and advice completion rates, then transitions users to optional Sunday data collection or gracefully ends the conversation.

How It Works

1. Data Gathering: The workflow starts by fetching the user's latest weekly report, user profile information, and active credit tracking data from the database
2. State Machine Processing: A comprehensive JavaScript state machine processes the user's current conversation stage and input message, determining the appropriate response and next stage
3. Response Generation: Based on the user's channel (WhatsApp vs SMS), the system formats responses with appropriate emojis, menu options, and message length
4. Stage Management: The workflow updates the user's conversation stage in the database to maintain conversation flow continuity
5. Chat History: All AI responses are logged to the chat history table for conversation tracking
6. Output Delivery: The final formatted message is returned for delivery to the user

The conversation flows through multiple stages (6.1 through 6.6) covering report delivery, credit analysis, advice review, next week preview, and optional Sunday work tracking.

Workflow Diagram

graph TD
    A[When Executed by Another Workflow] --> B[Fetch Weekly Report]
    B --> C[Fetch User]
    C --> D[Fetch Credit]
    D --> E[State Machine]
    E --> F[Update Stage]
    F --> G[Save to Chat History]
    G --> H[Return Output]

Trigger

Execute Workflow Trigger: This workflow is designed to be called by another workflow, receiving input data including the user's phone number and message content.

Nodes Used

Node Type	Node Name	Purpose
Execute Workflow Trigger	When Executed by Another Workflow	Receives input from calling workflow with phone number and message
Postgres	Fetch Weekly Report	Retrieves the user's most recent weekly business report data
Postgres	Fetch User	Gets user profile information including name, stage, and communication channel
Postgres	Fetch Credit	Fetches active credit tracking information for debt management features
Code	State Machine	Complex JavaScript logic handling conversation flow and response generation
Postgres	Update Stage	Updates the user's current conversation stage in the database
Postgres	Save to Chat History	Logs the AI response to chat history for conversation tracking
Set	Return Output	Formats and returns the final message for delivery

External Services & Credentials Required

• PostgreSQL Database: Requires "Postgres account" credential for database operations
  • Used for accessing user data, weekly reports, credit tracking, and chat history
  • Tables accessed: weeklyReports, youthEntrepreneursReal, creditTracking, n8n_chat_histories

Environment Variables

No environment variables are directly referenced in this workflow. All configuration appears to be handled through database queries and hardcoded logic in the state machine.

Data Flow

Input Data: - phoneNumber: User's phone number for database lookups - message: User's response/input message

Processing Data: - Weekly report metrics (profit, sales, costs, trends) - User profile (name, channel, conversation stage) - Credit tracking data (recovery targets, balances)

Output Data: - output: Formatted message ready for delivery to user - Updated conversation stage in database - Chat history entry

Error Handling

• Fetch nodes: Weekly Report and Credit nodes are configured with alwaysOutputData: true to handle cases where no data exists
• Chat History: Save to Chat History node has onError: continueRegularOutput to prevent workflow failure if logging fails
• State Machine: Includes default case handling for unexpected stages, providing fallback error messages
• Data Validation: JavaScript code includes null/undefined checks and safe fallbacks for missing data

Known Limitations

Based on the code comments, this workflow addresses several edge cases and fixes: - Fuzzy reply matching to handle various input formats (emojis, text, numbers) - SMS vs WhatsApp formatting differences - Safe SQL string escaping to prevent injection issues - Proper handling of null/undefined previous stages - Message length limitations for SMS channels

Related Workflows

This workflow is designed to be called by other workflows in the system, likely: - A main conversation router that determines when to trigger weekly reports - Potentially a Sunday data collection workflow (referenced in stage transitions)

Setup Instructions

1. Import Workflow: Import the JSON into your n8n instance
2. Configure Database Credential:
   • Create a PostgreSQL credential named "Postgres account"
   • Ensure it has access to tables: weeklyReports, youthEntrepreneursReal, creditTracking, n8n_chat_histories
3. Database Schema: Ensure your database has the required tables with appropriate columns as referenced in the queries
4. Calling Workflow: Set up a parent workflow to call this one with the required input format:

   {
     "phoneNumber": "+1234567890",
     "message": "user response text"
   }
   

5. Error Workflow: Configure error workflow "cuHEGQjAfvuGwIOD" if you want centralized error handling
6. Test: Execute with sample data to verify database connections and conversation flow