Skip to content

Daily Data Reconciliation

This workflow automatically validates and corrects business data discrepancies by comparing AI-extracted information from chat conversations against stored database records, ensuring data integrity for sales and credit tracking systems.

Purpose

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

This workflow serves as a data quality assurance system that runs daily to identify and fix inconsistencies between conversational data captured through AI interactions and the actual records stored in the database. It helps maintain accurate business metrics by automatically reconciling sales data, credit information, and micro-actions across user sessions.

How It Works

  1. Daily Trigger: The workflow starts automatically at 6 AM EAT (3 AM UTC) every day
  2. User Identification: Queries the database to find all users who had data collection sessions (sales or credit) on the previous day
  3. Chat Processing: For each user, retrieves their complete chat history from the previous day and formats it into a structured conversation
  4. AI Extraction: Uses GPT-4o-mini to extract all business data points (sales records, credit information, micro-actions) from the conversation
  5. Data Comparison: Fetches existing database records for the same dates and compares them against the AI-extracted data
  6. Reconciliation: Identifies discrepancies (missing records, mismatched values) and generates SQL statements to fix them
  7. Database Updates: Executes upsert operations to correct any found discrepancies
  8. Reporting: Aggregates results across all users and sends a summary report to Slack

Workflow Diagram

graph TD
    A[Daily 6AM EAT Trigger] --> B[Fetch Users With Data Sessions]
    B --> C[Loop Users]
    C --> D[Fetch Chat History]
    D --> E[Aggregate Chat]
    E --> F[Build Extraction Prompt]
    F --> G[AI Data Extractor]
    G --> H[Build Dynamic Query]
    H --> I[Fetch Stored Sales for Dates]
    I --> J[Fetch Stored Credit]
    J --> K[Compare & Reconcile]
    K --> L[Has Discrepancies?]
    L -->|Yes| M[Execute Upserts]
    L -->|No| N[Aggregate Results]
    M --> N
    N --> O[Slack Summary]

    P[GPT-4o-mini] -.-> G
    Q[Structured Output Parser] -.-> G

Trigger

  • Type: Schedule Trigger
  • Frequency: Daily at 6:00 AM East Africa Time (3:00 AM UTC)
  • Purpose: Runs reconciliation for the previous day's data after business hours

Nodes Used

Node Type Node Name Purpose
Schedule Trigger Daily 6AM EAT Trigger Starts the workflow daily at 6 AM EAT
Postgres Fetch Users With Data Sessions Identifies users who had data sessions yesterday
Split In Batches Loop Users Processes each user individually
Postgres Fetch Chat History Retrieves conversation data for each user
Aggregate Aggregate Chat Combines chat messages into a single dataset
Code Build Extraction Prompt Formats conversation for AI analysis
LangChain LLM AI Data Extractor Extracts structured business data from conversations
OpenRouter LLM GPT-4o-mini Provides the AI language model
Output Parser Structured Output Parser Ensures consistent JSON output format
Code Build Dynamic Query Creates SQL queries based on extracted data
Postgres Fetch Stored Sales for Dates Retrieves existing sales records
Postgres Fetch Stored Credit Retrieves existing credit records
Code Compare & Reconcile Identifies discrepancies and generates fix SQL
If Has Discrepancies? Routes to database updates only if needed
Postgres Execute Upserts Applies data corrections to database
Code Aggregate Results Summarizes reconciliation results
HTTP Request Slack Summary Sends daily report to Slack

External Services & Credentials Required

Database

  • PostgreSQL: Main data storage
  • Required Credential: Postgres account
  • Tables Used:
    • n8n_chat_histories (chat data)
    • dailyProfitTracking (sales records)
    • creditTracking (credit records)
    • youthEntrepreneursReal (user profiles)

AI Services

  • OpenRouter API: For GPT-4o-mini access
  • Required Credential: sifa_main_agent_prod
  • Model: openai/gpt-4o-mini

Notifications

  • Slack: For daily reports
  • Authentication: Webhook URL (environment variable)

Environment Variables

Variable Purpose Required
SLACK_WEBHOOK_URL Slack webhook for daily reports Yes

Data Flow

Input

  • Chat conversation histories from the previous day
  • Existing sales and credit records from database
  • User phone numbers from data collection sessions

Processing

  • AI extraction of business data from conversations
  • Comparison against stored database records
  • Generation of SQL correction statements

Output

  • Updated database records with corrected data
  • Slack report with reconciliation statistics
  • Detailed discrepancy information for monitoring

Error Handling

The workflow includes basic error handling: - Invalid AI Output: If JSON parsing fails, the workflow continues with empty data - Missing Records: Gracefully handles cases where no stored data exists - SQL Errors: Database operations are isolated to prevent cascade failures - Empty Results: Workflow completes successfully even with no discrepancies

Known Limitations

  • Relies on AI interpretation of conversational data, which may have accuracy limitations
  • Only processes data from the previous day (no historical reconciliation)
  • Requires specific conversation patterns to extract data effectively
  • Limited to predefined data schemas for sales and credit records

No related workflows specified in the current context.

Setup Instructions

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

  2. Configure Database Credentials:

    • Create a PostgreSQL credential named "Postgres account"
    • Ensure access to required tables: n8n_chat_histories, dailyProfitTracking, creditTracking, youthEntrepreneursReal

  3. Set Up AI Credentials:

    • Create an OpenRouter API credential named "sifa_main_agent_prod"
    • Ensure access to GPT-4o-mini model

  4. Configure Environment Variables:

    • Set SLACK_WEBHOOK_URL to your Slack webhook endpoint

  5. Verify Database Schema:

    • Ensure all referenced tables and columns exist
    • Verify the chat history JSON structure matches expected format

  6. Test the Workflow:

    • Run manually with test data first
    • Verify Slack notifications are working
    • Check database updates are applied correctly

  7. Activate Schedule:

    • Enable the workflow to run automatically
    • Monitor the first few runs to ensure proper operation

  8. Monitor Performance:

    • Watch Slack reports for reconciliation statistics
    • Review database logs for any SQL errors
    • Adjust AI prompts if extraction accuracy is low