🤝 Manage coupon campaigns and customer chats with WhatsApp and PostgreSQL

⚡ 1 views · 🤝 CRM & Sales Operations

Description

Coupon Bot Dashboard & WhatsApp Management System

A complete bilingual admin dashboard and WhatsApp bot solution for managing discount coupons, partner companies, and customer conversations through an intuitive web interface and automated chatbot.

What does this workflow do?

This workflow provides a comprehensive coupon management system with dual interfaces: a modern Vue.js admin dashboard for managers and an interactive WhatsApp bot for customers. It handles everything from company onboarding and coupon issuance to real-time customer support and conversation tracking.

Key features:

Setup Requirements

Integrations Needed:

  1. PostgreSQL - Primary database for all system data
  2. WhatsApp Business API - For customer bot interactions via Facebook Graph API
  3. n8n - Workflow automation platform (self-hosted or cloud)

Credentials Required:

How to Use

Initial Setup:

  1. Import the workflow into your n8n instance
  2. Configure PostgreSQL credentials - Connect to your database (named “discounts DB” in the workflow)
  3. Configure WhatsApp credentials - Add your Facebook Graph API Bearer Token
  4. Initialize the database - Send POST request to /webhook/coupon-bot/execute-init to create all tables
  5. Set admin phone - Access dashboard at /webhook/coupon-bot/dashboard, go to Settings, and set your admin phone number
  6. Configure WhatsApp webhook - Point your WhatsApp Business webhook to /webhook/whatsapp

Dashboard Access:

Admin WhatsApp Commands:

Once your phone is set as admin, send any message to the bot to access:

Workflow Structure

1. Dashboard Webhook (/coupon-bot/dashboard)

Serves the complete Vue.js 3 admin interface with:

2. API Endpoints (/coupon-bot/api/*)

RESTful API routes for dashboard operations:

3. Database Management (/coupon-bot/execute-init, /coupon-bot/migrate)

4. WhatsApp Bot Handler (/webhook/whatsapp)

Processes incoming WhatsApp messages with:

5. Customer Flow (Regular Users)

  1. Receive welcome message from settings
  2. Browse list of partner companies
  3. Select company to view available coupons
  4. Choose coupon to receive details
  5. Get end message with coupon code and terms

6. Admin Flow (Authenticated Users)

  1. Access main management menu
  2. Manage companies (add, edit, delete)
  3. Manage coupons (add with advanced options, edit, delete)
  4. Customize welcome and end messages
  5. Test customer experience

Database Schema

customers

- customer_id (SERIAL PRIMARY KEY)
- name (VARCHAR 255)
- phone (VARCHAR 50, UNIQUE)
- selected_sites (TEXT)
- created_at (TIMESTAMP)
- updated_at (TIMESTAMP)

companies

- company_id (SERIAL PRIMARY KEY)
- name (VARCHAR 255, NOT NULL)
- info (TEXT)

coupons

- coupon_id (SERIAL PRIMARY KEY)
- company_id (INT, FOREIGN KEY)
- coupon_text (TEXT, NOT NULL) -- The actual coupon code
- coupon_value (NUMERIC 10,2)
- is_percentage (BOOLEAN DEFAULT FALSE)
- has_max (BOOLEAN DEFAULT FALSE)
- max_value (NUMERIC 10,2)
- is_max_percentage (BOOLEAN DEFAULT FALSE)
- has_min_purchase (BOOLEAN DEFAULT FALSE)
- min_purchase_value (NUMERIC 10,2)
- is_on_items_quantity (BOOLEAN DEFAULT FALSE)
- registration_date (TIMESTAMP)
- has_expiry (BOOLEAN DEFAULT FALSE)
- expiry_date (TIMESTAMP)
- other_info (TEXT)

records

- record_id (SERIAL PRIMARY KEY)
- customer_id (INT, FOREIGN KEY)
- company_id (INT, FOREIGN KEY)
- coupon_id (INT, FOREIGN KEY)
- created_at (TIMESTAMP)

messages

- message_id (SERIAL PRIMARY KEY)
- customer_id (INT, FOREIGN KEY, nullable)
- sender_phone (VARCHAR 50)
- sender_name (VARCHAR 255)
- external_message_id (VARCHAR 255)
- message_text (TEXT, NOT NULL)
- message_type (VARCHAR 50 DEFAULT 'text')
- direction (VARCHAR 20 DEFAULT 'INCOMING')
- is_from_customer (BOOLEAN DEFAULT TRUE)
- created_at (TIMESTAMP)

settings

- key (VARCHAR 50 PRIMARY KEY)
- value (TEXT)
-- Keys: admin_phone, welcome_message, end_message

sessions

- phone (VARCHAR 50 PRIMARY KEY)
- state (VARCHAR 50, NOT NULL)
- data (JSONB DEFAULT '{}')
- last_updated (TIMESTAMP)

Session States (State Machine)

The bot uses PostgreSQL sessions for conversation persistence:

Message Templates

Default Welcome Message (Customizable):

Default End Message (Customizable):

Customization Tips

Use Cases

Technical Notes

Tags: #n8n #whatsapp #coupons #dashboard #vuejs #postgresql #automation #customer-service #bilingual

🔗 Nodes Used

HTTP Request, Postgres, Webhook

📥 Import

Download workflow.json and import into n8n: Workflow menu → Import from File

📖 Importing guide · 🔑 Credential setup