WhatsApp Verification Checklist

Audience: external (Meta vendor / partner integrations). Written in English — sibling docs in this folder (architecture.md, deploy-runbook.md, extraction-plan.md, whatsapp-service-status.md) are in Bahasa Indonesia for internal team use. Indonesian translation tracked at merchant_docs/docs/i18n/id/services/whatsapp/verification-checklist.md.

This document is used to verify the WhatsApp production flow after merchant_core_api has been split off into whatsapp-service.

Verification targets:

1. merchant_core_api successfully calls whatsapp-service
2. whatsapp-service successfully writes the message into the notification schema
3. whatsapp-service successfully sends to the Meta provider
4. the Meta webhook callback lands on whatsapp-service
5. delivery status is stored in notification.whatsapp_delivery_events
6. the final status also updates notification.whatsapp_messages

Prerequisites

• migration 032_create_notification_whatsapp_tables.sql has been run
• merchant_core_api/.env.production uses:
  • WHATSAPP_TRANSPORT=service
  • WHATSAPP_SERVICE_BASE_URL=...
  • WHATSAPP_SERVICE_API_KEY=...
• services/whatsapp_service/.env.production uses:
  • WHATSAPP_PROVIDER=meta-cloud-api
  • WHATSAPP_BASE_URL=https://graph.facebook.com/v23.0
  • WHATSAPP_API_KEY=...
  • WHATSAPP_PHONE_NUMBER_ID=...
  • WHATSAPP_VERIFY_TOKEN=...
• the Meta webhook points to:
  • https://<public-domain>/webhooks/whatsapp/status
• the messages field is subscribed in Meta

Verification Steps

1. start whatsapp-service
2. start merchant_core_api
3. trigger an OTP from the application or POST /auth/request-otp
4. check the notification.whatsapp_messages table
5. check the notification.whatsapp_send_attempts table
6. wait for the Meta callback
7. check the notification.whatsapp_delivery_events table
8. confirm that delivery_status on the message updates accordingly

SQL Queries

1. View the latest messages

SELECT
  id,
  message_type,
  reference_type,
  reference_id,
  recipient_phone,
  template_code,
  provider_code,
  provider_message_id,
  delivery_status,
  error_code,
  error_message,
  queued_at,
  sent_at,
  delivered_at,
  read_at,
  failed_at,
  created_at,
  updated_at
FROM notification.whatsapp_messages
ORDER BY created_at DESC
LIMIT 20;

2. Find messages by phone number

SELECT
  id,
  recipient_phone,
  message_type,
  provider_message_id,
  delivery_status,
  queued_at,
  sent_at,
  delivered_at,
  failed_at
FROM notification.whatsapp_messages
WHERE recipient_phone = '628114169868'
ORDER BY created_at DESC;

3. Find the latest OTP messages

SELECT
  id,
  recipient_phone,
  provider_message_id,
  delivery_status,
  template_code,
  queued_at,
  sent_at,
  delivered_at,
  failed_at
FROM notification.whatsapp_messages
WHERE message_type = 'otp'
ORDER BY created_at DESC
LIMIT 20;

4. Get the details of one message

Replace <message_id> with the message UUID.

SELECT
  id,
  message_type,
  reference_type,
  reference_id,
  recipient_phone,
  template_code,
  provider_code,
  provider_message_id,
  delivery_status,
  message_subject,
  rendered_body,
  payload_json,
  variables_json,
  error_code,
  error_message,
  queued_at,
  sent_at,
  delivered_at,
  read_at,
  failed_at,
  created_at,
  updated_at
FROM notification.whatsapp_messages
WHERE id = '<message_id>'::uuid;

5. View send attempts for one message

SELECT
  id,
  message_id,
  attempt_number,
  response_status_code,
  provider_message_id,
  attempt_status,
  error_code,
  error_message,
  attempted_at,
  request_payload,
  response_payload
FROM notification.whatsapp_send_attempts
WHERE message_id = '<message_id>'::uuid
ORDER BY attempt_number ASC, attempted_at ASC;

6. View delivery events for one message

SELECT
  id,
  message_id,
  provider_code,
  provider_message_id,
  event_type,
  event_status,
  payload_json,
  occurred_at,
  created_at
FROM notification.whatsapp_delivery_events
WHERE message_id = '<message_id>'::uuid
ORDER BY occurred_at DESC, created_at DESC;

7. Find a message by provider_message_id

SELECT
  id,
  recipient_phone,
  provider_message_id,
  delivery_status,
  created_at,
  updated_at
FROM notification.whatsapp_messages
WHERE provider_message_id = '<provider_message_id>';

8. Check incoming webhook events from Meta

SELECT
  id,
  provider_code,
  provider_message_id,
  event_type,
  event_status,
  occurred_at,
  created_at
FROM notification.whatsapp_delivery_events
ORDER BY created_at DESC
LIMIT 50;

9. Delivery status summary

SELECT
  delivery_status,
  COUNT(*) AS total
FROM notification.whatsapp_messages
GROUP BY delivery_status
ORDER BY total DESC;

10. Find delivery errors

SELECT
  id,
  recipient_phone,
  provider_message_id,
  delivery_status,
  error_code,
  error_message,
  created_at
FROM notification.whatsapp_messages
WHERE error_code IS NOT NULL
   OR error_message IS NOT NULL
   OR delivery_status = 'failed'
ORDER BY created_at DESC;

Success Indicators

The flow is considered successful if:

• a new record appears in notification.whatsapp_messages
• provider_message_id is populated after a successful send
• a new record appears in notification.whatsapp_send_attempts
• a new record appears in notification.whatsapp_delivery_events after the Meta callback
• delivery_status transitions from queued/sent to delivered or read

Failure Indicators

Investigation is needed if:

• the message does not enter notification.whatsapp_messages
• the message enters but provider_message_id is empty
• whatsapp_send_attempts shows attempt_status = failed
• error_code or error_message is populated
• whatsapp_delivery_events is empty even though the provider claims it sent a callback
• delivery_status does not change after the callback

Quick Check Sequence

After triggering an OTP:

1. run:

SELECT id, recipient_phone, provider_message_id, delivery_status, created_at
FROM notification.whatsapp_messages
ORDER BY created_at DESC
LIMIT 5;

2. take the id
3. check attempts:

SELECT *
FROM notification.whatsapp_send_attempts
WHERE message_id = '<message_id>'::uuid
ORDER BY attempted_at DESC;

4. check delivery events:

SELECT *
FROM notification.whatsapp_delivery_events
WHERE message_id = '<message_id>'::uuid
ORDER BY occurred_at DESC;

5. check the final status:

SELECT id, provider_message_id, delivery_status, sent_at, delivered_at, read_at, failed_at
FROM notification.whatsapp_messages
WHERE id = '<message_id>'::uuid;