Troubleshooting: SMS Campaigns and Routing

Campaign progress is stored in several tables in the sms_router schema:

• sms_router.campaign stores the main campaign status;
• sms_router.campaign_upload stores CSV upload progress;
• sms_router.campaign_recipient stores each campaign recipient;
• sms_router.sms_request stores generated SMS requests;
• sms_router.sms_request_chunk stores the operator and route chunks created from each SMS request;
• sms_router.sms_recipient stores each routed recipient and delivery status;
• sms_router.outbox_event stores events waiting to be published;
• sms_router.shedlock stores scheduler leases.

Start with the campaign ID and check each related record before changing anything.

Campaign remains in BUILDING

A campaign does not use the PENDING status. During a CSV upload:

• the campaign status is BUILDING;
• the upload moves from PENDING to IN_PROGRESS, then DONE or FAILED;
• generated recipients start in PENDING.

Check the campaign and upload

This query does not return the uploaded file or message content:

SELECT c.id,
       c.status AS campaign_status,
       c.recipient_count,
       c.rejected_count,
       c.total_cost,
       c.last_error AS campaign_error,
       c.created_date,
       c.updated_date,
       u.id AS upload_id,
       u.status AS upload_status,
       u.byte_size,
       u.processed_rows,
       u.last_error AS upload_error,
       u.updated_date AS upload_updated_at,
       COALESCE(r.staged_recipients, 0) AS staged_recipients
FROM sms_router.campaign c
LEFT JOIN sms_router.campaign_upload u ON u.campaign_id = c.id
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS staged_recipients
    FROM sms_router.campaign_recipient cr
    WHERE cr.campaign_id = c.id
) r ON TRUE
WHERE c.id = :campaign_id;

Check the result:

State	What to do
Upload is PENDING and no recipients exist	The worker has not started. Check campaign.bulk.build.enabled, scheduler health, the campaignBuildWorker lease, and router errors.
Upload is IN_PROGRESS and processed_rows is increasing	The build is working. Large files may take several minutes.
Upload is IN_PROGRESS but updated_date is old	The worker may have stopped. Check router logs and recipient count. The worker can reclaim it after the configured 30-minute interval.
Campaign is BUILDING and recipients already exist	An earlier attempt created some work. Recovery will fail the build instead of risking a second charge. Create a replacement upload after checking the error.
Campaign or upload is FAILED	Read last_error, fix the CSV, tags, routing, pricing, or balance issue, and upload again through the API.
Upload is DONE and campaign is QUEUED or SCHEDULED	The build finished. Continue with dispatch checks.

CSV files must use UTF-8. A leading UTF-8 BOM is accepted. Personalized files must contain an exact msisdn header and a column for every required template tag. Invalid and duplicate rows are skipped and counted in rejected_count.

Use the campaign or upload ID to check router logs for parsing, pricing, routing, database, and balance errors.

Do not change a BUILDING campaign to DRAFT in SQL. It may already contain recipients or have reached the balance-deduction step. The stale recovery task marks an uncharged build with no recipients as FAILED after the configured 45-minute grace period. Fix the cause and create a new upload or campaign through the API.

Scheduler or ShedLock is not moving

A ShedLock row means a scheduler task acquired a lease. It does not mean the database is deadlocked. A future lock_until value is normal while a task is running.

Check scheduler health

Call the admin-protected router endpoint GET /actuator/health/scheduler. A DOWN response includes stale lease details.

Check the lease rows using the same UTC database clock as the application:

SELECT name,
       locked_by,
       locked_at,
       lock_until,
       lock_until > timezone('utc', CURRENT_TIMESTAMP) AS lease_is_active,
       EXTRACT(EPOCH FROM (
           timezone('utc', CURRENT_TIMESTAMP) - locked_at
       ))::bigint AS lease_age_seconds
FROM sms_router.shedlock
WHERE name IN (
    'campaignBuildWorker',
    'campaignDispatcher',
    'campaignRetryDispatcher',
    'campaignDlrRollupPoller',
    'campaignRefundRetryPoller',
    'campaignStaleRecoverer'
)
ORDER BY name;

Find the lease related to the stalled work:

Work	Lease	Stale after
Bulk upload build	campaignBuildWorker	35 minutes
SCHEDULED/QUEUED dispatch	campaignDispatcher	6 minutes
RETRY_PENDING recipients	campaignRetryDispatcher	6 minutes
Delivery status roll-up	campaignDlrRollupPoller	6 minutes
Campaign refund retry	campaignRefundRetryPoller	6 minutes
Stale campaign recovery	campaignStaleRecoverer	7 minutes

Also check:

• the locked_by service instance;
• router logs for scheduler errors;
• database connection-pool health;
• long-running database transactions;
• all router replicas.

If several leases are stale, investigate the scheduler, application clock, and database connection before investigating one campaign.

Controlled lease recovery

Never delete a row from sms_router.shedlock. The application may not recreate it until restart.

Do not expire a lease while its owner may still be running. Another replica could start the same build or dispatch and cause duplicate work.

An authorized operator may expire one lease only after:

• the owning process is stopped or confirmed dead;
• the affected campaign stage has been checked;
• no charge or send operation is still running.

Expire only the affected lease:

BEGIN;

SELECT name, locked_by, locked_at, lock_until
FROM sms_router.shedlock
WHERE name = :lease_name
FOR UPDATE;

UPDATE sms_router.shedlock
SET lock_until = timezone('utc', CURRENT_TIMESTAMP) - INTERVAL '1 second'
WHERE name = :lease_name;

COMMIT;

Record the original row and the incident approval. After recovery, confirm that locked_at advances, scheduler health returns UP, and the campaign or upload changes only once.

Scheduled or queued campaign does not start

Campaign dispatch depends on the campaignDispatcher scheduler and its ShedLock lease. If the lease stops advancing or remains owned by a failed instance, campaigns can remain in SCHEDULED or QUEUED even when they are ready to run.

Check campaign and recipient status

SELECT c.id,
       c.status,
       c.schedule_mode,
       c.scheduled_at,
       c.dispatch_deadline,
       c.dispatch_started_at,
       c.dispatch_ended_at,
       c.last_error,
       cr.status AS recipient_status,
       COUNT(*) AS recipient_count,
       MIN(cr.next_retry_at) AS earliest_retry_at
FROM sms_router.campaign c
LEFT JOIN sms_router.campaign_recipient cr ON cr.campaign_id = c.id
WHERE c.id = :campaign_id
GROUP BY c.id, c.status, c.schedule_mode, c.scheduled_at,
         c.dispatch_deadline, c.dispatch_started_at, c.dispatch_ended_at,
         c.last_error, cr.status
ORDER BY cr.status;

Check the result in this order:

1. A SCHEDULED campaign cannot start before scheduled_at. Compare the time in the configured BTRC time zone.
2. A PAUSED campaign will not start. Resume it with POST /api/campaigns/{id}/resume instead of editing the database.
3. Check campaign.dispatcher.enabled and GET /actuator/health/scheduler. Inspect the campaignDispatcher row in sms_router.shedlock, including locked_by, locked_at, and lock_until. Follow Scheduler or ShedLock is not moving if the lease is stale.
4. A DISPATCHING campaign may still be working. The stale recovery task returns it to QUEUED after the configured dispatch grace period, which is 10 minutes by default.
5. If dispatch_deadline has passed, stale recovery changes the campaign to EXPIRED and refunds outstanding cost. Check the campaignStaleRecoverer lease and refund record. Do not reschedule the row in SQL.
6. For RETRY_PENDING recipients, check next_retry_at, attempt errors, and the campaignRetryDispatcher lease.
7. If recipients have an sms_request_id, continue with sms_request_chunk, the router outbox, Kafka, and the operator and delivery-report guide.

Campaign creation fails with outside_promotional_window

Promotional campaigns can run only during the configured BTRC time window. The default is 09:00 to 24:00 in Asia/Dhaka, but deployment settings can change it.

Check the following:

1. The SMS type is PROMOTIONAL.
2. The language is Bengali (lang=bn).
3. The request contains the required operator-approved campaign ID.
4. The running router has the expected BTRC_ENABLED, BTRC_ZONE_ID, BTRC_WINDOW_START_HOUR, and BTRC_WINDOW_END_HOUR values.
5. An instant campaign uses the current time in that zone. A scheduled campaign uses scheduled_at.

Submit a valid time through the API. Do not bypass the rule by changing scheduled_at in the database.