Skip to content

V4 - updateStreakTool

A utility workflow that manages weekly streak tracking for users, calculating daily progress and applying bonus points when users complete a full 6-day week (Monday through Saturday). This workflow maintains streak data in the database and handles the logic for doubling points when weekly goals are achieved.

Purpose

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

This workflow serves as a core component for tracking user engagement streaks on a weekly basis. It processes daily activity tracking, maintains cumulative progress, and rewards users with bonus points for completing full weeks of activity.

How It Works

  1. Input Processing: Receives a phone number, tracking date, and base points from another workflow
  2. Week Calculation: Determines the current week boundaries (Monday to Saturday) based on the tracking date
  3. Database Upsert: Creates or updates a streak record for the user's current week, marking the specific day as completed
  4. Progress Evaluation: Checks if the user has completed 6 days (full week) and whether bonus points should be applied
  5. Bonus Application: If a full week is completed and bonus hasn't been applied yet, doubles the total points for that week
  6. Result Return: Provides feedback on the streak status including days completed and bonus application

Workflow Diagram

graph TD
    A[When Executed by Another Workflow] --> B[Calculate Week & Build SQL]
    B --> C[Upsert Streak]
    C --> D[Check Streak Bonus]
    D --> E[Bonus Needed?]
    E -->|Yes| F[Apply Streak Bonus]
    E -->|No| G[Return Result]
    F --> G[Return Result]

Trigger

Execute Workflow Trigger: This workflow is designed to be called by other workflows, not triggered directly. It expects three input parameters: - phoneNumber: User's phone number identifier - trackingDate: The date for which activity is being tracked - basePoints: Points earned for the day's activity

Nodes Used

Node Type Node Name Purpose
Execute Workflow Trigger When Executed by Another Workflow Receives input parameters from calling workflow
Code Calculate Week & Build SQL Calculates week boundaries and generates SQL for streak upsert
Postgres Upsert Streak Executes database operation to create/update streak record
Code Check Streak Bonus Evaluates streak completion and determines if bonus should be applied
If Bonus Needed? Conditional logic to check if streak bonus should be applied
Postgres Apply Streak Bonus Updates database to apply 2x points bonus for completed weeks
Code Return Result Formats and returns the final workflow results

External Services & Credentials Required

PostgreSQL Database: - Credential Name: sifaV4Dev - Required Tables: v4_streaks - Permissions: INSERT, UPDATE, SELECT on v4_streaks table

Environment Variables

No environment variables are used in this workflow. All configuration is handled through the database credentials and input parameters.

Data Flow

Input: 

1
2
3
4
5
{
  "phoneNumber": "string",
  "trackingDate": "YYYY-MM-DD",
  "basePoints": "number"
}

Output: 

1
2
3
4
5
6
7
{
  "success": true,
  "days_completed": "number",
  "full_week": "boolean",
  "streak_bonus_applied": "boolean",
  "day_tracked": "string"
}

Database Schema (v4_streaks): - phonenumber: User identifier - week_start: Monday of the tracking week - week_end: Saturday of the tracking week - days_tracked: JSONB object tracking which days are completed - days_completed: Count of completed days - base_points: Accumulated points before bonus - total_points: Final points including any bonus - streak_bonus_applied: Boolean flag for bonus application - updated_at: Last modification timestamp

Error Handling

The workflow includes basic error handling through: - SQL injection prevention by escaping single quotes in phone numbers - Default value handling for missing or invalid base points (defaults to 0) - Conditional logic to prevent duplicate bonus applications

No explicit error nodes are present, so database errors or invalid inputs will cause the workflow to fail and should be handled by the calling workflow.

Known Limitations

  • Week definition is hardcoded as Monday-Saturday (6 days), excluding Sunday
  • Bonus points are always 2x the base points (not configurable)
  • No validation of phone number format
  • Tracking date defaults to current date if not provided, which may cause timezone issues
  • No mechanism to remove or correct incorrectly tracked days

This workflow is designed to be called by other workflows in the system that handle user activity tracking. Check for workflows that reference "updateStreakTool" or make calls to this workflow ID.

Setup Instructions

  1. Import Workflow: Import the JSON into your n8n instance
  2. Configure Database Credential:
    • Create a PostgreSQL credential named sifaV4Dev
    • Ensure connection to database with v4_streaks table
  3. Database Setup: Create the required table structure: 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
    CREATE TABLE v4_streaks (
  phonenumber VARCHAR PRIMARY KEY,
  week_start DATE,
  week_end DATE,
  days_tracked JSONB DEFAULT '{}'::jsonb,
  days_completed INTEGER DEFAULT 0,
  base_points INTEGER DEFAULT 0,
  total_points INTEGER DEFAULT 0,
  streak_bonus_applied BOOLEAN DEFAULT FALSE,
  updated_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(phonenumber, week_start)
);
  4. Test the Workflow: Execute with sample data to verify database connectivity and logic
  5. Integration: Update calling workflows to use the correct workflow ID and input format