saveCreditRecordTool - Francis

This workflow provides a centralized tool for saving and updating credit tracking records in a PostgreSQL database. It handles both new credit record creation and updates to existing records, automatically calculating running totals for credit amounts and recovery progress while maintaining a complete audit trail of micro-actions and credit module stages.

Purpose

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

How It Works

1. Receives Input Data: The workflow accepts comprehensive credit tracking data including customer information, credit amounts, recovery targets, and micro-action details
2. Upserts Credit Record: Executes a sophisticated PostgreSQL query that either inserts a new credit tracking record or updates an existing one based on phone number
3. Calculates Running Totals: Automatically computes total credit locked and weekly recovery progress using the provided daily amounts
4. Returns Summary: Provides a formatted output showing the current credit status including total locked amount, weekly recovery progress, and module stage

Workflow Diagram

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

Trigger

Execute Workflow Trigger: This workflow is designed to be called by other workflows and accepts 18 different input parameters including phone number, credit amounts, customer data, and micro-action tracking information.

Nodes Used

Node	Type	Purpose
When Executed by Another Workflow	Execute Workflow Trigger	Receives input parameters from calling workflows
upsertCreditRecord	PostgreSQL	Inserts or updates credit tracking records with complex calculations
setOutput	Set	Formats and returns a summary of the saved credit record

External Services & Credentials Required

• PostgreSQL Database: Requires connection to a Supabase PostgreSQL instance
  • Credential: PostgresOnSupabase
  • Used for: Storing and updating credit tracking records

Environment Variables

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

Data Flow

Input Parameters

• phoneNumber (string): Customer phone number (primary key)
• 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 recovery amount per week
• 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 details of micro-action
• microActionAttempted (boolean): Whether micro-action was attempted
• microActionOutcome (string): Result of micro-action
• amountRecovered (number): Amount recovered from micro-action
• nextAction (string): Next planned action
• creditModuleStage (string): Current stage in credit module
• creditSkipReason (string): Reason for skipping if applicable
• weekNumber (number): Current week number

Output

• output (string): Formatted summary including total credit locked, weekly recovered amount, and current module stage

Error Handling

This workflow does not implement explicit error handling. Database errors from the PostgreSQL node would cause the workflow to fail and would need to be handled by the calling workflow.

Known Limitations

• No validation of input parameters before database insertion
• No error handling for database connection issues
• Assumes phone number is always provided and valid
• Weekly recovery calculations reset logic is not clearly defined

Related Workflows

No related workflows are documented in the provided context.

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 PostgresOnSupabase
   • Configure connection to your Supabase PostgreSQL instance
   • Ensure the credential has read/write access to the creditTracking table

3. Database Schema Requirements

   • Ensure your PostgreSQL database has a creditTracking table with all the required columns
   • The table should have phoneNumber as the primary key for conflict resolution
   • Required columns include all input parameters plus calculated fields like totalCreditLocked and weeklyRecoveredSoFar

4. Test the Workflow

   • Execute the workflow with sample data to verify database connectivity
   • Confirm that upsert logic works correctly for both new and existing records

5. Integration

   • Call this workflow from other workflows using the Execute Workflow node
   • Pass all required parameters as defined in the trigger configuration