Daily Sales Data Collection - Francis

This workflow processes daily business data for youth entrepreneurs, calculating profit by combining sales figures with various cost categories and user-specific configuration data. It serves as a comprehensive financial tracking system that maintains daily business records and updates user progress tracking.

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 daily business metrics including sales, costs, and operational details
2. Fetches User Configuration: Retrieves the user's fixed costs, durable stock items, and current progress day from the database
3. Calculates Profit: Processes all cost components (variable costs, fixed costs, durable item depreciation) to determine daily profit
4. Saves Daily Record: Stores or updates the complete daily business record in the profit tracking table
5. Updates User Status: Marks data as collected for the day and updates user progress tracking
6. Returns Results: Provides a formatted response with the calculated profit and confirmation message

Workflow Diagram

graph TD
    A[When Executed by Another Workflow] --> B[getUserConfig]
    B --> C[calculateProfit]
    C --> D[upsertDailyRecord]
    D --> E[updateUserState]
    E --> F[Edit Fields]

Trigger

Execute Workflow Trigger: This workflow is designed to be called by another workflow and expects the following input parameters: - date (string): Business date - phoneNumber (string): User identifier - sales (number): Daily sales amount - stockingCost (number): Cost of inventory/stock - transportCost (number): Transportation expenses - otherCost (number): Miscellaneous costs - Micro actions (string): User actions or notes - isPractice (boolean): Whether this is practice data

Nodes Used

Node Type	Node Name	Purpose
Execute Workflow Trigger	When Executed by Another Workflow	Receives input data from calling workflow
Postgres	getUserConfig	Retrieves user's fixed costs and durable items configuration
Code	calculateProfit	Performs profit calculations combining all cost components
Postgres	upsertDailyRecord	Saves/updates daily business record in database
Postgres	updateUserState	Updates user progress and data collection status
Set	Edit Fields	Formats output response with profit and confirmation message

External Services & Credentials Required

• PostgreSQL Database: Requires "PostgresOnSupabase" credential for database operations
  • Used for reading user configuration and writing daily records
  • Manages two main tables: youthEntrepreneursReal and dailyProfitTracking

Environment Variables

No environment variables are used in this workflow. All configuration is handled through database credentials and input parameters.

Data Flow

Input Data:

{
  "date": "2024-01-15",
  "phoneNumber": "+254712345678",
  "sales": 1500,
  "stockingCost": 800,
  "transportCost": 100,
  "otherCost": 50,
  "Micro actions": "Visited 3 new customers",
  "isPractice": false
}

Output Data:

{
  "profit": 350,
  "output": "Profit for 2024-01-15 saved. Profit: KES 350"
}

Internal Processing: - Retrieves user's dailyFixedCost, durableStockItems, and currentDay - Calculates total variable costs (stocking + transport + other) - Computes durable item depreciation from cost-per-day values - Final profit = sales - variable costs - fixed costs - durable depreciation

Error Handling

The workflow includes basic error handling: - JSON parsing errors for durable items are caught and default to empty array - Numeric conversions use fallback values (0) for undefined inputs - Database operations use upsert patterns to handle duplicate entries

Known Limitations

• No validation of input data ranges or business rules
• Durable item cost calculation assumes linear depreciation
• No handling of negative profit scenarios or alerts
• Practice vs. real data distinction affects user progress but not profit calculation

Related Workflows

This workflow is designed to be called by other workflows that collect daily business data from users, likely through forms or messaging interfaces.

Setup Instructions

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

2. Configure Database Credential:

   • Create a PostgreSQL credential named "PostgresOnSupabase"
   • Ensure connection to database with required tables

3. Database Schema Requirements:

   -- User configuration table
   CREATE TABLE "youthEntrepreneursReal" (
     "phoneNumber" VARCHAR PRIMARY KEY,
     "dailyFixedCost" DECIMAL,
     "durableStockItems" JSONB,
     "currentDay" INTEGER,
     "todayDataCollected" BOOLEAN,
     "lastDataDate" DATE,
     "consecutiveMissedDays" INTEGER
   );
   
   -- Daily tracking table
   CREATE TABLE "dailyProfitTracking" (
     "phoneNumber" VARCHAR,
     "dateOfBusiness" DATE,
     "sales" DECIMAL,
     "stockingCost" DECIMAL,
     "transportCost" DECIMAL,
     "otherCost" DECIMAL,
     "totalDailyCosts" DECIMAL,
     "dailyFixedCost" DECIMAL,
     "durableCostDeducted" DECIMAL,
     "profit" DECIMAL,
     "isPractice" BOOLEAN,
     "microActions" TEXT,
     PRIMARY KEY ("phoneNumber", "dateOfBusiness")
   );
   

4. Test the Workflow: Execute with sample data to verify database connections and calculations

5. Integration: Configure calling workflows to pass the required input parameters in the expected format