V4 - awardPointsTool - Warenga

This workflow manages a gamified points and leveling system for youth entrepreneurs. It awards points for various activities, automatically calculates level progression, maintains a complete audit trail, and updates user records in real-time.

Purpose

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

How It Works

1. Receives Input: Another workflow calls this one with a phone number, event type, points to award, and optional reference information
2. Fetches Current Status: Queries the database to get the user's current points and level
3. Calculates Updates: Determines new point total, checks for level progression, and builds the necessary SQL statements
4. Records Transaction: Inserts a new entry in the points ledger and updates the user's current points and level
5. Returns Results: Sends back success confirmation with before/after details and level-up status

Workflow Diagram

graph TD
    A[When Executed by Another Workflow] --> B[Get Current Points]
    B --> C[Calculate & Build SQL]
    C --> D[Write Ledger & Update]
    D --> E[Return Result]

Trigger

Execute Workflow Trigger: This workflow is designed to be called by other workflows, not triggered directly. It expects the following input parameters: - phoneNumber: User's phone number (identifier) - event_type: Description of the activity earning points - points: Number of points to award - event_ref_type: Optional reference type for the event - event_ref_id: Optional reference ID for the event

Nodes Used

Node	Type	Purpose
When Executed by Another Workflow	Execute Workflow Trigger	Receives input parameters from calling workflow
Get Current Points	PostgreSQL	Retrieves user's current points and level from database
Calculate & Build SQL	Code (JavaScript)	Calculates new totals, determines level changes, builds SQL statements
Write Ledger & Update	PostgreSQL	Executes transaction to record points and update user record
Return Result	Code (JavaScript)	Formats and returns success response with transaction details

External Services & Credentials Required

• PostgreSQL Database: Requires sifaV4Dev credential for database access
  • Used to read current user status and write transaction records
  • Must have access to v4_youthEntrepreneurs and v4_pointsledger tables

Environment Variables

No environment variables are used in this workflow. All configuration is handled through the PostgreSQL credential.

Data Flow

Input

{
  "phoneNumber": "string",
  "event_type": "string", 
  "points": "number",
  "event_ref_type": "string (optional)",
  "event_ref_id": "number (optional)"
}

Output

{
  "success": true,
  "points_awarded": "number",
  "new_total": "number", 
  "level_before": "string",
  "level_after": "string",
  "leveled_up": "boolean"
}

Level System

• Kifaru: 0-499 points
• Chui: 500-999 points
• Ndovu: 1000-1999 points
• Simba: 2000+ points

Error Handling

This workflow does not include explicit error handling nodes. Database errors or invalid inputs will cause the workflow to fail and return an error to the calling workflow. Consider adding error handling for production use.

Known Limitations

• No input validation for phone number format or point values
• No error handling for database connection issues
• SQL injection protection relies on basic string escaping
• No rollback mechanism if the ledger insert succeeds but user update fails

Related Workflows

This is a utility workflow designed to be called by other workflows that need to award points to users. Look for workflows that reference this workflow's ID or name in their "Execute Workflow" nodes.

Setup Instructions

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

2. Configure Database Credential:

   • Create a PostgreSQL credential named sifaV4Dev
   • Configure connection to your database with appropriate permissions
   • Ensure access to tables: v4_youthEntrepreneurs, v4_pointsledger

3. Database Schema Requirements:

   -- v4_youthEntrepreneurs table needs:
   -- phoneNumber (primary key)
   -- sifa_points (integer, nullable)
   -- sifa_level (string, nullable)
   
   -- v4_pointsledger table needs:
   -- phonenumber (string)
   -- event_type (string)
   -- event_ref_type (string, nullable)
   -- event_ref_id (integer, nullable)
   -- points_awarded (integer)
   -- running_total (integer)
   -- level_before (string)
   -- level_after (string)
   

4. Test the Workflow:

   • Use the "Execute Workflow" node in another workflow
   • Pass test data with a valid phone number from your database
   • Verify points are awarded and ledger entries are created

5. Activate: Enable the workflow so it can be called by other workflows