V4 - saveCreditRecordTool

This workflow saves and updates credit tracking records for users, managing their credit balances, recovery targets, and micro-action history in a PostgreSQL database. It handles both new credit records and updates to existing ones, automatically calculating locked credit amounts and weekly recovery progress.

Purpose

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

How It Works

1. Receives Input Data: The workflow is triggered by another workflow with comprehensive credit tracking parameters including phone number, credit amounts, customer counts, and micro-action details.

2. Sanitizes and Encodes Parameters: All input fields are validated, sanitized (especially phone numbers), and encoded as Base64 JSON to prevent SQL injection and handle n8n's parameter limitations.

3. Updates Credit Record: Performs an upsert operation on the creditTracking table, either creating a new record or updating an existing one based on the phone number. The operation includes complex calculations for:

   • Total credit locked (baseline minus recoveries plus new credit given)
   • Weekly recovery progress tracking
   • Micro-action date stamping

4. Logs Micro-Action History: If a micro-action outcome is provided, it creates a separate historical record in the microActionHistory table for audit and analysis purposes.

5. Returns Summary: Outputs a formatted message showing the current credit status including total locked amount, weekly recovered amount, and current module stage.

Workflow Diagram

graph TD
    A[When Executed by Another Workflow] --> B[encodeParams]
    B --> C[upsertCreditRecord]
    B --> D[logMicroActionHistory]
    C --> E[setOutput]

Trigger

Execute Workflow Trigger: This workflow is designed to be called by other workflows within the same n8n instance. It accepts 18 different input parameters related to credit tracking and micro-actions.

Nodes Used

Node Type	Node Name	Purpose
Execute Workflow Trigger	When Executed by Another Workflow	Receives input parameters from calling workflows
Code	encodeParams	Sanitizes input data and encodes as Base64 JSON for secure SQL execution
Postgres	upsertCreditRecord	Performs complex upsert operation on creditTracking table with credit calculations
Postgres	logMicroActionHistory	Inserts micro-action records into history table for tracking
Set	setOutput	Formats and returns a summary of the credit record operation

External Services & Credentials Required

• PostgreSQL Database: Requires a configured Postgres credential in n8n with access to:
  • creditTracking table (read/write permissions)
  • microActionHistory table (write permissions)

Environment Variables

No specific environment variables are required for this workflow.

Data Flow

Input Parameters

• phoneNumber (string): User's phone number (primary identifier)
• creditCustomersCount (number): Number of customers receiving credit
• totalCreditBaseline (number): Base credit amount
• creditCustomerTypes (string): Types of credit customers
• creditTypicalDays (number): Typical credit duration in days
• weeklyRecoveryTarget (number): Target amount to recover weekly
• targetCustomers (number): Target number of customers
• creditGivenToday (number): Credit amount given today
• creditRecoveredToday (number): Credit amount recovered today
• microActionType (string): Type of micro-action performed
• microActionScript (string): Script or description of micro-action
• microActionAttempted (boolean): Whether micro-action was attempted
• microActionOutcome (string): Result of the micro-action
• amountRecovered (number): Amount recovered from micro-action
• nextAction (string): Planned next action
• creditModuleStage (string): Current stage in credit module
• creditSkipReason (string): Reason for skipping credit actions
• weekNumber (number): Current week number in program

Output

• output (string): Formatted summary message containing:
  • Total credit locked amount in KES
  • Weekly recovered amount in KES
  • Current credit module stage

Error Handling

• Input Sanitization: Phone numbers are cleaned to remove non-numeric characters (except +)
• Null Handling: Undefined values are converted to null for proper database insertion
• Micro-Action Logging: The history logging node has continueRegularOutput error handling, allowing the workflow to complete even if history logging fails
• Global Error Workflow: The workflow is configured with error workflow ID cuHEGQjAfvuGwIOD for centralized error handling

Known Limitations

No specific limitations documented in the provided context.

Related Workflows

No related workflows specified in the current context.

Setup Instructions

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

2. Configure Database Connection:

   • Set up a PostgreSQL credential in n8n
   • Ensure the database contains the required tables:
     • creditTracking with all referenced columns
     • microActionHistory with all referenced columns

3. Set Caller Policy: The workflow is configured to only accept calls from workflows owned by the same user (workflowsFromSameOwner)

4. Configure Error Handling:

   • Set up error workflow with ID cuHEGQjAfvuGwIOD or update the error workflow reference
   • Alternatively, remove the error workflow setting if not needed

5. Test the Integration: Call this workflow from another workflow with sample credit tracking data to verify proper operation

6. Activate: Set the workflow to active status when ready for production use