Skip to content

V4 - MessageStatusPoller

This workflow acts as a safety net for SMS message delivery tracking by periodically checking the status of messages that may have missed their Twilio status callbacks. It runs every 15 minutes to ensure message delivery statuses are accurately recorded in the database.

Purpose

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

This workflow serves as a backup mechanism for message status tracking in the V4 system. When Twilio sends SMS messages, it normally provides status updates via webhooks (status callbacks). However, these callbacks can sometimes fail or be missed due to network issues, server downtime, or other technical problems. This poller ensures that message statuses are eventually updated by actively querying Twilio's API for messages that appear to be stuck in intermediate states.

How It Works

  1. Schedule Trigger: Every 15 minutes, the workflow automatically starts
  2. Find Pending Messages: Queries the database for messages that are still in intermediate states (queued, sent, sending, accepted, scheduled) and were sent more than 3 minutes ago but less than 2 days ago
  3. Check Status with Twilio: For each pending message, makes an API call to Twilio to get the current status
  4. Parse Response: Extracts the message SID, status, and any error codes from Twilio's response
  5. Validate Data: Checks if the response contains valid status information
  6. Update Database: If valid status data is found, updates the message log with the new status and sets appropriate timestamps for delivery, read, or failure events

Mermaid Diagram

graph TD
    A[Every 15 min] --> B[SelectPending]
    B --> C[GetStatus]
    C --> D[Parse]
    D --> E[IF status]
    E -->|Valid Status| F[UpdateLog]
    E -->|No Status| G[End]

Trigger

  • Type: Schedule Trigger
  • Frequency: Every 15 minutes
  • Purpose: Automated polling to catch missed status updates

Nodes Used

Node Type Purpose
Every 15 min Schedule Trigger Starts the workflow every 15 minutes
SelectPending PostgreSQL Finds messages with pending statuses older than 3 minutes
GetStatus HTTP Request Queries Twilio API for current message status
Parse Code Extracts SID, status, and error code from Twilio response
IF status If Validates that response contains usable status data
UpdateLog PostgreSQL Updates message status and timestamps in database

External Services & Credentials Required

Twilio API

  • Service: Twilio Messages API
  • Endpoint: https://api.twilio.com/2010-04-01/Accounts/{AccountSid}/Messages/{MessageSid}.json
  • Authentication: HTTP Basic Auth
  • Required Credential: "Waringa twilio auth" (Account SID and Auth Token)

Database

  • Service: PostgreSQL
  • Required Credential: "sifaV4Dev" (database connection details)
  • Tables Used: v4_message_log

Environment Variables

No environment variables are explicitly used in this workflow. All configuration is handled through: - Hardcoded Twilio Account SID in the API URL - Database credentials stored in n8n credential manager - Fixed polling interval (15 minutes)

Data Flow

Input

  • Trigger: Time-based (every 15 minutes)
  • Database Query: Message SIDs from v4_message_log table where status is pending

Processing

  • API Calls: Individual Twilio API requests for each pending message
  • Data Transformation: Raw Twilio responses parsed into structured status data

Output

  • Database Updates: Updated message statuses, timestamps, and error codes in v4_message_log
  • Batch Processing: Handles up to 100 messages per execution

Error Handling

  • HTTP Request: Configured with neverError: true to handle API failures gracefully
  • Response Validation: IF node filters out invalid or empty responses before database updates
  • Database Safety: Uses NULLIF and CASE statements to prevent invalid data insertion
  • Batch Limiting: LIMIT 100 prevents overwhelming the system with too many API calls

Known Limitations

  • Batch Size: Limited to 100 messages per execution to avoid API rate limits
  • Time Window: Only processes messages between 3 minutes and 2 days old
  • No Retry Logic: Failed API calls are not retried within the same execution
  • Hardcoded Account: Twilio Account SID is hardcoded in the URL

No related workflows specified in the context.

Setup Instructions

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

  2. Configure Database Credential:

    • Create PostgreSQL credential named "sifaV4Dev"
    • Configure connection to database containing v4_message_log table

  3. Configure Twilio Credential:

    • Create HTTP Basic Auth credential named "Waringa twilio auth"
    • Set username to your Twilio Account SID
    • Set password to your Twilio Auth Token

  4. Update Account SID:

    • Edit the GetStatus node
    • Replace AC906ca9524b640bdad7d029fd565b6a02 with your actual Twilio Account SID

  5. Verify Database Schema:

    • Ensure v4_message_log table exists with required columns:
      • message_sid, status, error_code, sent_at, delivered_at, read_at, failed_at, updated_at

  6. Test Execution:

    • Run manually to verify database connectivity and API access
    • Check that status updates are properly recorded

  7. Activate Workflow:

    • Enable the workflow to start automatic polling every 15 minutes