Daily Sales Data Collection

This workflow captures and stores daily business performance data from entrepreneurs, automatically calculating profit margins and maintaining a comprehensive database for tracking business growth and financial health over time.

Purpose

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

How It Works

1. Receive Sales Data: The workflow accepts daily business metrics including sales revenue, various cost categories, and operational notes from another workflow
2. Calculate Profit: Automatically computes daily profit by subtracting all costs (stocking, transport, other costs, and fixed daily costs) from total sales
3. Store in Database: Inserts or updates the daily profit tracking record in PostgreSQL, ensuring no duplicate entries for the same phone number and date
4. Return Key Metrics: Outputs the calculated profit, business date, and sales figures for use by calling workflows

Workflow Diagram

graph TD
    A[When Executed by Another Workflow] --> B[Insert rows in a table]
    B --> C[Edit Fields]

Trigger

Execute Workflow Trigger: This workflow is designed to be called by other workflows, accepting the following input parameters: - date (string): Business date - phoneNumber (string): Entrepreneur's phone number identifier - sales (number): Total daily sales revenue - stockingCost (number): Cost of inventory/products - transportCost (number): Transportation expenses - otherCost (number): Miscellaneous business expenses - Micro actions (string): Notes about daily business activities - isPractice? (boolean): Flag indicating if this is practice/training data

Nodes Used

Node Type	Node Name	Purpose
Execute Workflow Trigger	When Executed by Another Workflow	Receives input data from calling workflows
PostgreSQL	Insert rows in a table	Stores daily business data and calculates profit
Set	Edit Fields	Formats output data for return to calling workflow

External Services & Credentials Required

• PostgreSQL Database: Requires connection to database containing:
  • dailyProfitTracking table for storing daily business records
  • youthEntrepreneursReal table for retrieving fixed daily costs
• Credentials Needed: PostgreSQL database credentials (configured as "Postgres account")

Environment Variables

No environment variables are explicitly used in this workflow. All configuration is handled through n8n's credential system.

Data Flow

Input Data:

{
  "date": "2026-03-16",
  "phoneNumber": "+254115558547",
  "sales": 1800,
  "stockingCost": 420,
  "transportCost": 0,
  "otherCost": 100,
  "Micro actions": "",
  "isPractice?": true
}

Output Data:

{
  "profit": 1280,
  "dateOfBusiness": "2026-03-16",
  "sales": 1800
}

Database Record Created: - All input fields plus calculated profit and retrieved daily fixed costs - Automatic conflict resolution (upsert) based on phone number and date combination

Error Handling

The workflow includes basic error handling through: - COALESCE Function: Handles missing daily fixed costs by defaulting to 0 - Upsert Logic: ON CONFLICT clause prevents duplicate record errors - Type Casting: Explicit PostgreSQL type casting ensures data integrity

No explicit error nodes are present, so database connection failures or invalid data types will cause the workflow to fail.

Known Limitations

• Depends on external youthEntrepreneursReal table for fixed cost lookup
• No validation of input data ranges or business logic constraints
• Limited error handling for edge cases or data quality issues
• Phone number format not validated

Related Workflows

This workflow is designed to be called by other workflows that collect sales data, likely through user interfaces or data import processes.

Setup Instructions

1. Import Workflow: Import the JSON into your n8n instance
2. Configure Database Connection:
   • Set up PostgreSQL credentials named "Postgres account"
   • Ensure access to required database tables:
     • dailyProfitTracking (main storage table)
     • youthEntrepreneursReal (for fixed cost lookup)
3. Database Schema: Ensure the following table structure exists:

   -- dailyProfitTracking table should include columns:
   -- phoneNumber, dateOfBusiness, sales, stockingCost, transportCost, 
   -- otherCost, dailyFixedCost, profit, microActions, isPractice
   

4. Test the Workflow: Use the pinned test data to verify database connectivity and calculations
5. Integration: Configure calling workflows to pass the required input parameters
6. Activate: Enable the workflow to accept calls from other workflows

The workflow is configured to only accept calls from workflows owned by the same user for security purposes.