saveCreditRecordTool

A utility workflow that saves and tracks credit management data for business owners, maintaining both current credit status and historical micro-action records in a PostgreSQL database.

Purpose

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

This workflow serves as a data persistence layer for credit management systems, handling the storage and updating of credit tracking information including customer counts, credit amounts, recovery targets, and micro-actions taken by users.

How It Works

1. Input Processing: The workflow receives credit-related data through its trigger, including phone numbers, credit amounts, customer information, and micro-action details
2. Data Encoding: All input parameters are safely encoded as Base64 JSON to prevent SQL injection and handle complex data types securely
3. Credit Record Management: The main credit tracking record is inserted or updated in the database using an upsert operation that intelligently handles existing records
4. Micro-Action Logging: Simultaneously, any micro-action outcomes are logged to a separate history table for audit trails
5. Response Generation: A formatted response is returned showing the updated credit status including total locked credit, weekly recovery progress, and current 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, not triggered directly by external events. It accepts 18 input parameters related to credit management.

Nodes Used

Node Name	Type	Purpose
When Executed by Another Workflow	Execute Workflow Trigger	Receives input parameters from calling workflows
encodeParams	Code (JavaScript)	Safely encodes all input data as Base64 JSON to prevent SQL injection
upsertCreditRecord	PostgreSQL	Inserts new or updates existing credit tracking records with complex business logic
logMicroActionHistory	PostgreSQL	Records micro-action history for audit and tracking purposes
setOutput	Set	Formats and returns a human-readable status message

External Services & Credentials Required

• PostgreSQL Database: Requires a PostgreSQL connection with credentials named "Postgres account"
  • Must have access to creditTracking and microActionHistory tables
  • Requires INSERT, UPDATE, and SELECT permissions

Environment Variables

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

Data Flow

Input Parameters

• phoneNumber (string): User's phone number (primary identifier)
• creditCustomersCount (number): Number of credit customers
• totalCreditBaseline (number): Baseline 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 taken
• microActionScript (string): Script or description of micro-action
• microActionAttempted (boolean): Whether micro-action was attempted
• microActionOutcome (string): Outcome of the micro-action
• amountRecovered (number): Amount recovered from micro-action
• nextAction (string): Next recommended 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 status message showing total credit locked, weekly recovered amount, and current stage

Error Handling

• Micro-Action Logging: The logMicroActionHistory node is configured with "Continue on Error" to ensure the main credit record is saved even if history logging fails
• Data Sanitization: Phone numbers are sanitized to remove non-digit characters (except +) to prevent data corruption
• SQL Safety: All database operations use parameterized queries with Base64 encoding to prevent SQL injection
• Error Workflow: The workflow has an error workflow configured (ID: cuHEGQjAfvuGwIOD) to handle unexpected failures

Known Limitations

• The workflow assumes specific database table structures for creditTracking and microActionHistory
• Phone number validation is basic (only removes non-digit/+ characters)
• No validation of business logic constraints (e.g., negative credit amounts)
• Micro-action history is only logged when microActionOutcome is provided

Related Workflows

No related workflows are documented in the provided context.

Setup Instructions

1. Import the Workflow

   • Import the JSON into your n8n instance
   • The workflow will be named "saveCreditRecordTool"

2. Configure Database Connection

   • Set up a PostgreSQL credential named "Postgres account"
   • Ensure the database contains the required tables:
     • creditTracking with appropriate columns for credit management
     • microActionHistory for logging micro-actions

3. Database Schema Requirements

   • The creditTracking table should have phoneNumber as a unique constraint
   • Both tables need columns matching the input parameters
   • Ensure proper data types (TEXT, INTEGER, NUMERIC, BOOLEAN, DATE)

4. Test the Workflow

   • Call this workflow from another workflow with sample credit data
   • Verify that records are properly inserted/updated in both tables
   • Check that the output message formats correctly

5. Error Handling Setup

   • Optionally configure the error workflow (ID: cuHEGQjAfvuGwIOD) for custom error handling
   • Monitor the workflow execution logs for any data validation issues