name: comercialrs-whatsapp-webhook-setup

description: Meta WhatsApp Business API webhook setup for ComercialRS — bypass Supabase Cloud JWT requirement using Python server + nginx proxy

category: devops

ComercialRS WhatsApp Webhook Setup

Context

Meta WhatsApp Business API requires webhook verification via GET request with only query params (hub.mode, hub.verify_token, hub.challenge) — no Authorization header. Supabase Cloud Edge Functions require JWT auth on ALL requests, causing Meta's webhook verification to fail with #N/A:WBxP-1061056854.

Solution Architecture

`

Meta → GET/POST /meta-webhook/

↓ nginx (port 8083 proxy)

Python webhook server (port 8083, psycopg2 direct Postgres)

↓

PostgreSQL Docker container (172.23.0.2:5432)

↓

auth.message_log, auth.tasks, auth.message_replies

`

Key finding 1 (CRITICAL - Updated 2026-05-25): The webhook server was connecting to the LOCAL Docker Postgres (172.23.0.2) instead of the Supabase Cloud Postgres. The Docker DB had EMPTY/WRONG data. All production data lives in Supabase Cloud. The VPS has TWO Postgres instances:

• - Local Docker (172.23.0.2:5432): Supabase's own postgres + Supabase Studio. Tables in auth schema. EMPTY for ComercialRS data.
• - Supabase Cloud (db.dauftiqcvgaydddoxqhh.supabase.co:5432): WHERE ALL DATA LIVES. Tables in public schema. This is what the webhook MUST connect to.

Key finding 2 (CRITICAL): Tables in Supabase Cloud are in public schema, NOT auth. The old webhook code used auth.message_log, auth.tasks, etc. — these return NO DATA from Supabase Cloud because the actual tables are public.message_log, public.tasks.

Key finding 3 (Updated 2026-05-25): DB access credentials for Supabase Cloud:

• - Host: db.dauftiqcvgaydddoxqhh.supabase.co
• - Port: 5432
• - User: postgres
• - Password: tSm?p8n57f+TX5n (Supabase Cloud postgres password — NOT the Docker password)
• - Database: postgres
• - Schema: public (NOT auth)
• - Connection: psycopg2.connect(host='db.dauftiqcvgaydddoxqhh.supabase.co', port=5432, dbname='postgres', user='postgres', password='tSm?p8n57f+TX5n')

Key finding 4 (Updated 2026-05-21): SCP transfer CORRUPTS bytes in binary/string content. The verify token 1c526598c56179f772e896d233badab arrived corrupted (byte at position 19 was e8 96 instead of e9 6b, changing e to f). Never transfer tokens or credentials via SCP. Instead: write Python scripts ON the VPS to construct the correct string in-memory, or use SSH to pipe content directly.

Working server: /var/www/comercialrs/data/meta_webhook_vps.py on port 8083 using Supabase Cloud Postgres with public schema.

Critical gotcha — Button type reply vs custom: When a template uses buttons of type reply (not custom), clicking a button sends the button text (e.g., "Iniciada", "Concluida", "Remarketing") as the payload — NOT the identifier you defined. The webhook must handle BOTH:

`python

BUTTON_STATUSES = {'in_progress', 'completed', 'remarketing', 'Iniciada', 'Concluida', 'Remarketing'}

status_map = {

'in_progress': 'in_progress',

'completed': 'completed',

'remarketing': 'remarketing',

'Iniciada': 'in_progress', # reply text → status

'Concluida': 'completed', # reply text → status

'Remarketing': 'remarketing' # reply text → status

}

new_status = status_map.get(button_payload, button_payload)

`

Also, the find_task_by_phone_and_status logic must use .lower() for case-insensitive comparison:

`python

if button_payload and button_payload.lower() == 'remarketing':

statuses = "('todo', 'in_progress', 'completed')" # always available

else:

statuses = "('todo', 'in_progress')" # others only for non-completed

`

Critical gotcha — due_date is TEXT not DATE in Supabase Cloud: The public.tasks.due_date column is text type, NOT date. Comparisons like due_date < CURRENT_DATE require CURRENT_DATE::text cast. Also due_date values may contain 24:00:00 (midnight represented as 24:00) — parse carefully.

Working alert system: dispatch-automation edge function is NOT automatically triggered for scheduled alerts — it needs an external scheduler. Created /var/www/comercialrs/data/task_alerts.py running via cron /5 * to handle meeting_alert_30min and overdue_daily alerts directly via Meta API, bypassing the edge function for scheduled jobs. Key fixes:

• - pt_BR for 1_reunioes_atrasadas_2 (not pt_PT)
• - ON CONFLICT (meta_message_id) DO NOTHING on message_log insert to handle duplicate sends
• - Phone filter: skip team members with empty phone
• - Deduplication: task_alerts table prevents double-sending per task per alert type
• - Daily overdue sends only once per day (tracked in task_alerts with NULL task_id)

Cron setup: /5 * python3 /var/www/comercialrs/data/task_alerts.py >> /var/log/task_alerts.log 2>&1

Log file: /var/log/task_alerts.log

Working payload structure:

`python

payload = {

"messaging_product": "whatsapp",

"to": "5511913412661",

"type": "template",

"template": {

"name": "nova_reuniao_criada",

"language": {"code": "pt_BR"},

"components": [{

"type": "body",

"parameters": [

{"type": "text", "text": "DAVI FREIRE CARVALHO"},

{"type": "text", "text": "Reuniao"},

{"type": "text", "text": "25/05/2026"},

{"type": "text", "text": "15:00"},

{"type": "text", "text": "Reuniao de Teste"},

{"type": "text", "text": "mensagem"}

]

}, {...buttons...}]

}

}

`

When dispatch-automation Edge Function (which sends suas_reunioes_de_hoje template) fails for some members with #132001 Template name does not exist in pt_PT, those members likely don't have that template translated to pt_PT yet. This is an expected failure for some members and doesn't block the overall flow.

Critical Gotchas

• - DO NOT kill deploy-vps-meta-1 — it is the Supabase postgres-meta service, not a WhatsApp component
• - Tables created by Supabase migrate CLI go to auth schema, NOT public. Supabase REST API (/rest/v1/) accesses public schema only.
• - Management PATs (sbp_...) do NOT work for REST API calls — they are for management API, not PostgREST. Always use the project's service_role JWT from Supabase Dashboard → Project Settings → API.
• - RPC functions must be in public schema (not auth) with SECURITY DEFINER SET search_path = public to bypass RLS when called via REST API
• - Phone comparison: always normalize both numbers (remove +, 55 prefix, non-digits) before comparing
• - Meta sends webhook verification as GET with no auth — must be handled by a server that doesn't require JWT
• - Port 8082 was the original attempt using Supabase REST API — failed due to JWT issues. Port 8083 uses psycopg2 direct Postgres connection — working solution.
• - When starting Python server, always kill existing process first: fuser -k 8083/tcp
• - Verify DB connection works before deploying: psycopg2.connect(host='172.23.0.2', port=5432, dbname='postgres', user='postgres', password='Rochasales2024')

Send API — Confirmation Messages (Working as of 2026-05-25)

The automations_config row has the correct credentials (confirmed working for sending):

• - Token from meta_access_token: EAASNGf6ucRcBRYZC7Bt... (truncated)
• - Phone ID from meta_phone_number_id: 812251211981254
• - WABA from meta_waba_id: 532448808878595

These credentials were verified working on 2026-05-25 when sending nova_reuniao_criada template to Davi — message ID returned: wamid.HBgNNTUxMTkxMzQxMjY2MRUCABEYEjMxRTlBOEI5Mzg4MkFDNUZBNwA=.

1. Token + Phone Number ID must belong to the SAME Meta App — if they mismatch, Send API returns HTTP 400: Object with ID 'PHONE_ID' does not exist

2. There are TWO Meta Apps involved in this project:

- App 1281042693386519 — N8N.RS (used by n8n automation). Token: EAASNGf6ucRcBRYZC7BtZAZB... — does NOT have CommercialRS phone numbers

- App 3014923135375381 — Dashboard Rocha Sales (CommercialRS WhatsApp). Token: EAAq2Dn6CWBUBRpwPA1KagzJ4ZAfsck0i9gSJr0oNCD64cJxTRzPb3ye5LGI8vgHoMvjGone4EeHBUV681PZCd7A3218ugL20ruNmxnioz8oHLQmUWsuUgj5XzyM8L6fbla5Tv4e4GYqViPG9vMK7bPuUWGeZAAT2z1PZCCxhNMR7hSuit2kRjYqMn1nrdwZDZD

3. Current issue: Phone Number ID 578716225306497 appears to belong to the N8N.RS App, not Dashboard Rocha Sales App. The Send API confirmation is BLOCKED until a valid Phone Number ID for App 3014923135375381 is obtained.

Token validation steps:

`bash

Step 1: Check which App a token belongs to

curl -s "https://graph.facebook.com/v21.0/debug_token?input_token=TOKEN&access_token=TOKEN"

Step 2: Verify token has whatsapp_business_messaging scope

Should show: "whatsapp_business_messaging" in scopes array

Step 3: Test Send API with known phone number

curl -X POST "https://graph.facebook.com/v21.0/PHONE_ID/messages" \

-H "Authorization: Bearer TOKEN" \

-H "Content-Type: application/json" \

-d '{"messaging_product":"whatsapp","to":"5511945678901","type":"text","text":{"body":"test"}}'

If HTTP 400 + "does not exist" → phone number belongs to different App than token

`

Database storage: Token stored in auth.automations_config (added 2026-05-21):

• - Column meta_access_token — the Meta Access Token
• - Column meta_phone_number_id — the Phone Number ID for Send API
• - Column meta_webhook_verify_token — verify token (currently 1c526598c56179f772b...)
• - Table was empty — inserted row with id=ff55d766-4f8a-45d1-9918-d4187adc2681

Get correct Phone Number ID from Meta Dashboard:

1. Go to developers.facebook.com → App 3014923135375381

2. Left menu → WhatsApp → API Setup or Configuration

3. Copy the Phone Number ID shown there (NOT the one from n8n's App)

Current Status (2026-05-25)

✅ Task update flow WORKING: When vendors click WhatsApp buttons (payload: in_progress, completed, remarketing), tasks are correctly updated in public.tasks via webhook. Verified end-to-end:

• - Davi clicked "Iniciada" button on 2026-05-25
• - Task f796f907-4d10-4b09-ad61-a5558c8b2bb9 updated from todo → in_progress in Supabase Cloud
• - Confirmation message sent via Send API with HTTP 200

✅ Confirmation messages WORKING: Send API using credentials from automations_config table (meta_access_token + meta_phone_number_id from Supabase Cloud) successfully sends template messages to WhatsApp.

✅ Webhook server running: /var/www/comercialrs/data/meta_webhook_vps.py on port 8083, connected to Supabase Cloud Postgres, schema public, logging to /var/log/meta_webhook.log.

✅ dispatch-automation FIXED (2026-05-25): Edge function was returning #131008 Required parameter is missing because phone numbers were being stripped of the 55 prefix before sending to Meta API. Meta requires international format (55 + DDD + number, e.g., 5511913412661). Fix applied in /var/www/comercialrs/supabase/functions/dispatch-automation/index.ts:

`typescript

let cleanPhone = phone.replace(/\D/g, '');

if (!cleanPhone.startsWith('55')) {

cleanPhone = '55' + cleanPhone;

}

`

Deployed with supabase functions deploy dispatch-automation --project-ref dauftiqcvgaydddoxqhh --no-verify-jwt

✅ Remarketing button availability logic (2026-05-25): meta_webhook_vps.py now has button-specific status filtering:

• - remarketing → queries ('todo', 'in_progress', 'completed') — always available as post-treatment action
• - in_progress / completed → queries ('todo', 'in_progress') only — already-completed tasks ignore these buttons

`python

STATUSES_FOR_BUTTON = {'todo', 'in_progress', 'completed'}

if button_payload == 'remarketing':

statuses = "('todo', 'in_progress', 'completed')" # always available

else:

statuses = "('todo', 'in_progress')" # only non-completed tasks

`

✅ Frontend null guard fixes (2026-05-25): task.dueDate can be null in database — causes blank screen "Cannot read properties of null" on components that call .includes() or .split() on null. Fixed in:

• - TaskCard.tsx: isOverdue, formatDate, getTime — all added null guards
• - TaskViewDialog.tsx: formatTime and line 106 task.dueDate?.includes('T')
• - TaskEditDialog.tsx: buildForm — corrupted time line replaced, null guards on .split('T')

After fixes, rebuild with nvm use 20 && npm run build.

Verification

Test GET (webhook verification):

`bash

curl "https://comercialrs.rochasalesseguros.com.br/meta-webhook/?hub.mode=subscribe&hub.verify_token=YOUR_TOKEN&hub.challenge=test123"

Should return: test123

`

Test POST (incoming message simulation):

`bash

curl -X POST "https://comercialrs.rochasalesseguros.com.br/meta-webhook/" \

-H "Content-Type: application/json" \

-d '{"object":"whatsapp_business_account","entry":[...]}'

Should return: OK

`

Meta Dashboard

https://developers.facebook.com/apps//webhooks/