V4 - awardPointsTool

A utility workflow that manages a gamified points and leveling system for users. It awards points for specific events, tracks running totals, manages level progression, and maintains a complete audit trail of all point transactions.

Purpose

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

How It Works

1. Receive Input: The workflow is triggered by another workflow with user phone number, event details, and points to award
2. Get Current State: Queries the database to retrieve the user's current points balance and level
3. Calculate Updates: Determines the new point total, calculates if a level change occurs, and builds the necessary SQL statements
4. Update Records: Executes a transaction that both logs the point award in the ledger and updates the user's current totals
5. Return Results: Provides confirmation with before/after states and level change information

The system uses a four-tier level structure: - Kifaru (Rhino): 0-499 points - Chui (Leopard): 500-999 points
- Ndovu (Elephant): 1000-1999 points - Simba (Lion): 2000+ points

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]

    F[Doc] -.-> A

Trigger

Execute Workflow Trigger: This workflow is designed to be called by other workflows, not run directly. It expects the following input parameters: - phoneNumber: User identifier - event_type: Description of what earned the points - points: Number of points to award - event_ref_type: Optional reference type for the triggering event - event_ref_id: Optional reference ID for the triggering event

Nodes Used

Node	Type	Purpose
When Executed by Another Workflow	Execute Workflow Trigger	Receives input parameters from calling workflow
Get Current Points	Postgres	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	Postgres	Executes transaction to log points and update user record
Return Result	Code (JavaScript)	Formats and returns success response with transaction details
Doc	Sticky Note	Documentation and reference information

External Services & Credentials Required

• PostgreSQL Database: Requires sifaV4Dev credential for database access
  • Tables used: v4_youthEntrepreneurs, v4_pointsledger
  • Permissions needed: SELECT, INSERT, UPDATE

Environment Variables

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

Data Flow

Input Parameters:

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

Output Response:

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

Error Handling

The workflow does not include explicit error handling nodes. Database errors or invalid inputs will cause the workflow to fail at the respective node. Consider adding error handling for: - Invalid phone numbers - Database connection failures - SQL execution errors - Invalid point values

Known Limitations

• No validation of input parameters
• No error handling for database failures
• SQL injection protection relies on basic string escaping
• No rollback mechanism if partial transaction fails
• Level thresholds are hardcoded in the JavaScript logic

Related Workflows

This is a utility workflow designed to be called by other workflows that need to award points for user actions.

Setup Instructions

1. Import the Workflow: Copy the workflow JSON and import it into your n8n instance

2. Configure Database Credential:

   • Create a PostgreSQL credential named sifaV4Dev
   • Configure connection to your database containing the required tables

3. Verify Database Schema:

   • Ensure v4_youthEntrepreneurs table exists with columns: phoneNumber, sifa_points, sifa_level
   • Ensure v4_pointsledger table exists with columns: phonenumber, event_type, event_ref_type, event_ref_id, points_awarded, running_total, level_before, level_after

4. Test the Workflow:

   • Use the "Execute Workflow" node in another workflow to test
   • Verify points are awarded and levels update correctly
   • Check that ledger entries are created properly

5. Integration: Call this workflow from other workflows using the "Execute Workflow" node with the required input parameters