Skip to content

_temp_run_sql_then_delete

A temporary database setup workflow that creates a PostgreSQL stored procedure for retrieving comprehensive session context data and adds a unique constraint to prevent duplicate daily profit entries.

Purpose

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

How It Works

This workflow performs two sequential database operations:

  1. Creates a stored procedure called get_session_context() that retrieves comprehensive user data including profile information, profit tracking history, credit details, pending messages, and chat history for a given phone number
  2. Adds a unique constraint to the dailyProfitTracking table to prevent duplicate entries for the same phone number and business date combination

The stored procedure also automatically resets daily session flags (eveningSessionStarted and todayDataCollected) when a user hasn't submitted data for the current date.

Workflow Diagram

graph TD
    A[Manual Trigger] --> B[Create Stored Procedure]
    B --> C[Create Unique Constraint]

Trigger

Manual Trigger - This workflow must be started manually and is intended for one-time database setup or maintenance tasks.

Nodes Used

Node Type Node Name Purpose
Manual Trigger Manual Trigger Initiates the workflow when manually executed
PostgreSQL Create Stored Procedure Creates the get_session_context() function in the database
PostgreSQL Create Unique Constraint Adds a unique constraint to prevent duplicate profit tracking entries

External Services & Credentials Required

  • PostgreSQL Database: Requires connection to a PostgreSQL database with credentials named "kdpTestEnv"

Environment Variables

No environment variables are used in this workflow.

Data Flow

Input: None (manual trigger only)

Processing: - Creates a PostgreSQL function that returns JSON containing user data, profit history, credit info, pending messages, and chat history - Adds database constraint for data integrity

Output: Database schema changes (stored procedure and constraint creation)

Error Handling

No explicit error handling is implemented in this workflow. Database errors will cause the workflow to fail at the respective SQL execution step.

Known Limitations

  • This is marked as a temporary workflow (indicated by the "temp" prefix)
  • The workflow is currently inactive and appears to be intended for deletion after use
  • No rollback mechanism is provided if the database changes need to be reverted

No related workflows identified from the available context.

Setup Instructions

  1. Import the workflow into your n8n instance
  2. Configure PostgreSQL credentials:
    • Create a PostgreSQL credential named "kdpTestEnv"
    • Ensure the database user has permissions to:
      • Create functions/procedures
      • Alter table constraints
      • Read from tables: youthEntrepreneursReal, dailyProfitTracking, creditTracking, pendingDaytimeMessages, n8n_chat_histories
  3. Verify database schema: Ensure all referenced tables exist in your database
  4. Test the workflow by manually triggering it
  5. Verify results by checking that:
    • The get_session_context() function exists
    • The unique constraint uq_phone_date is added to dailyProfitTracking
  6. Delete the workflow after successful execution (as indicated by the temporary nature)