Skip to content

saveCreditRecordTool - Francis

This workflow manages credit tracking records for business owners by storing and updating comprehensive credit information including customer counts, recovery targets, micro-actions, and financial metrics in a PostgreSQL database.

Purpose

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

How It Works

  1. Receives Credit Data: The workflow is triggered by another workflow with comprehensive credit tracking information including phone number, customer counts, financial baselines, and micro-action details.

  2. Updates Database Record: Executes a sophisticated PostgreSQL upsert operation that either creates a new credit tracking record or updates an existing one based on the phone number.

  3. Calculates Financial Metrics: The database operation automatically calculates running totals for locked credit amounts and weekly recovery progress based on daily credit given and recovered amounts.

  4. Returns Summary: Provides a formatted output message showing the current credit status including total locked credit, weekly recovery progress, and current 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, accepting 18 different input parameters related to credit tracking and management.

Nodes Used

Node Type Node Name Purpose
Execute Workflow Trigger When Executed by Another Workflow Receives credit tracking data from calling workflows
PostgreSQL upsertCreditRecord Performs complex database upsert with financial calculations
Set setOutput Formats and returns a summary of the credit record status

External Services & Credentials Required

  • PostgreSQL Database: Requires connection to a Supabase-hosted PostgreSQL instance
    • Credential: "PostgresOnSupabase"
    • Access needed: Read/Write permissions on creditTracking table

Environment Variables

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

Data Flow

Input Parameters (18 fields):

  • phoneNumber (string): Unique identifier for the credit record
  • creditCustomersCount (number): Number of customers receiving credit
  • totalCreditBaseline (number): Base credit amount
  • creditCustomerTypes (string): Types of customers receiving credit
  • 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 taken
  • 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 activities
  • weekNumber (number): Current week number in tracking cycle

Output:

  • output (string): Formatted status message showing total credit locked, weekly recovery progress, and current module stage

Error Handling

The workflow uses PostgreSQL's built-in error handling. The COALESCE functions in the upsert query ensure that NULL values don't overwrite existing data. The GREATEST function prevents negative credit amounts.

Known Limitations

No specific limitations documented in the provided context.

This workflow is designed to be called by other workflows in the credit management system, but no specific related workflows are identified in the current context.

Setup Instructions

  1. Import the Workflow:

    • Copy the workflow JSON and import it into your n8n instance

  2. Configure Database Connection:

    • Set up a PostgreSQL credential named "PostgresOnSupabase"
    • Ensure connection to your Supabase PostgreSQL instance
    • Verify the creditTracking table exists with all required columns

  3. Database Schema Requirements: The workflow expects a creditTracking table with these columns:

    • phoneNumber (TEXT, PRIMARY KEY)
    • creditCustomersCount, totalCreditBaseline, creditTypicalDays, weeklyRecoveryTarget, targetCustomers, creditGivenToday, creditRecoveredToday, amountRecovered, weekNumber (NUMERIC)
    • creditCustomerTypes, microActionType, microActionScript, microActionOutcome, nextAction, creditModuleStage, creditSkipReason (TEXT)
    • microActionDate (DATE)
    • microActionAttempted, creditModuleActive (BOOLEAN)
    • totalCreditLocked, weeklyRecoveredSoFar (NUMERIC)
    • updatedAt (TIMESTAMP)

  4. Test the Workflow:

    • Call this workflow from another workflow with sample credit data
    • Verify that records are created/updated correctly in the database
    • Check that the output message formats correctly

  5. Integration:

    • Configure other workflows to call this workflow using the Execute Workflow node
    • Ensure all 18 required parameters are passed from calling workflows