V4 - MessageLogger¶

A centralized message logging service that records all outbound communications (WhatsApp, SMS, etc.) into a PostgreSQL database for tracking, analytics, and delivery monitoring across the Sifa V4 platform.

Purpose¶

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

This workflow serves as a critical infrastructure component that maintains a comprehensive audit trail of all messages sent through the platform. It enables delivery rate monitoring, message analytics, and troubleshooting by creating a single source of truth for communication logs. The workflow is designed to be called by any message-sending workflow to ensure consistent logging across the entire system.

How It Works¶

Receive Message Data: Another workflow calls this service with message details including SID, recipient phone, channel, and metadata
Prepare SQL Statement: The workflow sanitizes input data and constructs an UPSERT SQL query to handle both new messages and updates to existing records
Log to Database: Executes the SQL against the PostgreSQL database, inserting new message records or updating existing ones based on the message SID
Return Confirmation: Provides confirmation that the logging operation completed successfully

The workflow uses an UPSERT pattern (INSERT ... ON CONFLICT DO UPDATE) to ensure idempotency - the same message can be logged multiple times without creating duplicates.

Workflow Diagram¶

graph TD
    A[When Executed by Another Workflow] --> B[Prep]
    B --> C[UpsertLog]

    A --> |messageSid, phone, channel, etc.| B
    B --> |Generated SQL query| C
    C --> |Database confirmation| D[Complete]

Trigger¶

Execute Workflow Trigger: This workflow is designed to be called by other workflows using the "Execute Workflow" node. It accepts the following input parameters:

messageSid (required) - Unique Twilio message identifier
phone (required) - Recipient phone number
channel - Communication channel (defaults to 'whatsapp')
messageType - Type of message being sent
templateSid - Twilio template identifier (optional)
variant - Message variant for A/B testing (optional)
cohort - User cohort information (optional)
sourceWorkflow - Name of the calling workflow
bodyPreview - Preview of message content (optional)
status - Message status (defaults to 'sent')

Nodes Used¶

Node Type	Node Name	Purpose
Execute Workflow Trigger	When Executed by Another Workflow	Receives input parameters from calling workflows
Code	Prep	Sanitizes input data and constructs SQL UPSERT query
PostgreSQL	UpsertLog	Executes the SQL query against the database
Sticky Note	Note	Documentation within the workflow

External Services & Credentials Required¶

PostgreSQL Database¶

Credential Name: sifaV4Dev
Purpose: Stores message logs in the v4_message_log table
Required Permissions: INSERT, UPDATE, SELECT on v4_message_log table

Environment Variables¶

No environment variables are directly used by this workflow. All configuration is handled through the PostgreSQL credential and input parameters.

Data Flow¶

Input¶

{
  "messageSid": "string (required)",
  "phone": "string (required)", 
  "channel": "string (optional, defaults to 'whatsapp')",
  "messageType": "string",
  "templateSid": "string (optional)",
  "variant": "string (optional)",
  "cohort": "string (optional)",
  "sourceWorkflow": "string",
  "bodyPreview": "string (optional)",
  "status": "string (optional, defaults to 'sent')"
}

Output¶

{
  "sql": "string (the executed SQL query)",
  "message_sid": "string (the processed message SID)"
}

Database Schema¶

The workflow writes to the v4_message_log table with these columns: - message_sid (primary key) - phone - channel - message_type - template_sid - variant - cohort - source_workflow - body_preview - status - sent_at - updated_at

Error Handling¶

The workflow includes basic error handling:

Missing Message SID: If no messageSid is provided, the workflow skips processing and returns a "skipped" indicator
SQL Injection Protection: All string inputs are escaped by replacing single quotes with double quotes and truncating to 500 characters
Database Errors: The PostgreSQL node is configured with alwaysOutputData: true to ensure the workflow continues even if database operations fail

Known Limitations¶

String inputs are truncated to 500 characters to prevent database overflow
The workflow assumes the v4_message_log table exists with the expected schema
No validation is performed on phone number formats or message SID structure
Database connection failures will cause the workflow to fail without retry logic

Based on the workflow documentation, this logger works in conjunction with: - V4 - TwilioStatusCallback: Updates message delivery status based on Twilio webhooks - V4 - MessageStatusPoller: Polls for message status updates - v4_delivery_rate: Team view for monitoring delivery rates

Setup Instructions¶

Import Workflow: Import the workflow JSON into your n8n instance
Configure Database Credential:
- Create a PostgreSQL credential named sifaV4Dev
- Ensure it has access to the target database with the v4_message_log table

Create Database Table (if not exists):

CREATE TABLE v4_message_log (
  message_sid VARCHAR(500) PRIMARY KEY,
  phone VARCHAR(500) NOT NULL,
  channel VARCHAR(500) DEFAULT 'whatsapp',
  message_type VARCHAR(500),
  template_sid VARCHAR(500),
  variant VARCHAR(500),
  cohort VARCHAR(500),
  source_workflow VARCHAR(500),
  body_preview VARCHAR(500),
  status VARCHAR(500) DEFAULT 'sent',
  sent_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Activate Workflow: Enable the workflow in n8n

Test Integration: Call the workflow from another workflow using the Execute Workflow node with sample data:

{
  "messageSid": "test_sid_123",
  "phone": "+1234567890",
  "sourceWorkflow": "test_workflow",
  "messageType": "template"
}

Verify Logging: Check the v4_message_log table to confirm the test message was recorded

The workflow is now ready to be called by any message-sending workflow in your system to maintain comprehensive message logs.