SMS Balance Workflow - Complete Documentation (With Monthly Top-Up)

Overview

This document describes the enhanced SMS Balance system that includes:

Manual Balance Management - Admin adding/removing SMS credits
SMS Campaign/Blast - Sending bulk SMS to customers
Monthly Auto Top-Up - Automatic credit based on supplier_package.sms_monthly_credit

Note: Monthly top-up amount is configurable by main admin in supplier_packages table. Changes take effect in the next month.

High-Level Architecture Diagram

Two Credit Pools System

The system now manages two separate credit pools:

Pool	Source	Max Limit	Expiry	Deduction
sms_monthly_credit	Auto top-up from supplier_package	supplier_package.sms_monthly_credit	Monthly	Used first for SMS
sms_topup_credit	Manual top-up by Admin	Unlimited	Never	Used after sms_monthly_credit depleted

Complete Data Flow

1. Adding sms_topup_credit (Manual Top-Up)

2. Monthly Auto Top-Up Flow

3. SMS Campaign - Balance Validation (With Two Pools)

4. SMS Spending - Which Pool First?

5. Twilio Callback (Delivery Status)

Complete Calculation Scenarios

SCENARIO A: Manual sms_topup_credit Addition

Setup:

Current sms_monthly_credit: $15.00
Current sms_topup_credit: $35.00
Total Balance: $50.00
Admin adds: $50.00

Formula:

NEW_SMS_TOPUP_CREDIT = OLD_SMS_TOPUP_CREDIT + ADDED_AMOUNT
NEW_TOTAL_BALANCE = OLD_TOTAL_BALANCE + ADDED_AMOUNT

NEW_SMS_TOPUP_CREDIT = $35 + $50 = $85
NEW_TOTAL_BALANCE = $50 + $50 = $100

sms_monthly_credit stays: $15.00 (unchanged)

Result:

Pool	Before	After
sms_monthly_credit	$15.00	$15.00
sms_topup_credit	$35.00	$85.00
Total	$50.00	$100.00

SCENARIO B: Monthly Auto Top-Up - No Spend

Setup:

Month: April 2026
Supplier Package: sms_monthly_credit = $23 (configured in supplier_package)
sms_monthly_credit at month-start: $23.00
sms_topup_credit: $77.00
SMS Spend during April: $0.00

Formula:

monthly_limit = supplier_package.sms_monthly_credit  # $23

topUp = max(0, monthly_limit - sms_monthly_credit_remaining)
topUp = max(0, 23 - 23)
topUp = max(0, 0)
topUp = 0

NO TOP-UP ADDED

Result:

Pool	Month Start	End of April	Top-Up	Next Month Start
sms_monthly_credit	$23.00	$23.00	$0.00	$23.00
sms_topup_credit	$77.00	$77.00	$0.00	$77.00
Total	$100.00	$100.00	$0.00	$100.00

Visual:

Opening:    [█████23%███....77%....] = $100
Spend:      [......................] = $0
End:        [█████23%███....77%....] = $100
Top-up:     [......................] = $0
New Start:  [█████23%███....77%....] = $100

SCENARIO C: Monthly Auto Top-Up - Partial Spend

Setup:

Month: April 2026
Supplier Package: sms_monthly_credit = $23 (configured in supplier_package)
sms_monthly_credit at month-start: $23.00
sms_topup_credit: $77.00
SMS Spend during April: $20.00 (uses sms_monthly_credit credit first)

Formula:

monthly_limit = supplier_package.sms_monthly_credit  # $23

# Step 1: Calculate remaining sms_monthly_credit credit after spend
sms_monthly_credit_remaining = sms_monthly_credit_start - sms_monthly_credit_spent
sms_monthly_credit_remaining = $23.00 - $20.00 = $3.00

# Step 2: Calculate top-up needed
topUp = max(0, monthly_limit - sms_monthly_credit_remaining)
topUp = max(0, 23 - 3)
topUp = max(0, 20)
topUp = $20.00

Result:

Pool	Month Start	Spend	End of April	Top-Up	Next Month Start
sms_monthly_credit	$23.00	-$20.00	$3.00	+$20.00	$23.00
sms_topup_credit	$77.00	$0.00	$77.00	$0.00	$77.00
Total	$100.00	-$20.00	$80.00	+$20.00	$100.00

Visual:

Opening:    [█████23%███....77%....] = $100
Spend:      [▓▓▓▓▓20%▓▓▓..........] = -$20 (uses sms_monthly_credit)
End:        [█3%.......77%.........] = $80
Top-up:     [█████████████20%.......] = +$20
New Start:  [█████23%███....77%....] = $100

SCENARIO D: Monthly Auto Top-Up - Full sms_monthly_credit + Partial sms_topup_credit

Setup:

Month: April 2026
Supplier Package: sms_monthly_credit = $23
sms_monthly_credit at month-start: $23.00
sms_topup_credit: $77.00
SMS Spend during April: $50.00

Formula:

monthly_limit = supplier_package.sms_monthly_credit  # $23

# Step 1: Calculate spend from each pool
# sms_monthly_credit used first, then client

sms_monthly_credit_spent = min(sms_monthly_credit_start, total_spend)
sms_monthly_credit_spent = min($23, $50) = $23

client_spent = total_spend - sms_monthly_credit_spent
client_spent = $50 - $23 = $27

# Step 2: Calculate remaining
sms_monthly_credit_remaining = sms_monthly_credit_start - sms_monthly_credit_spent
sms_monthly_credit_remaining = $23 - $23 = $0

client_remaining = client_start - client_spent
client_remaining = $77 - $27 = $50

# Step 3: Calculate top-up
topUp = max(0, monthly_limit - sms_monthly_credit_remaining)
topUp = max(0, 23 - 0)
topUp = $23.00

Result:

Pool	Month Start	Spend	End of April	Top-Up	Next Month Start
sms_monthly_credit	$23.00	-$23.00	$0.00	+$23.00	$23.00
sms_topup_credit	$77.00	-$27.00	$50.00	$0.00	$50.00
Total	$100.00	-$50.00	$50.00	+$23.00	$73.00

Visual:

Opening:    [█████23%███....77%....] = $100
Spend:      [█████23%...▓▓▓▓27%....] = -$50
End:        [..........50%.........] = $50
Top-up:     [████████████████23%....] = +$23
New Start:  [█████23%███..50%......] = $73

SCENARIO E: SMS Campaign - Balance Validation

Setup:

Total Customers: 1,000
Message Length: 58 characters
Supplier Package: price_per_sms = $0.10, sms_character = 160
sms_monthly_credit: $23.00
sms_topup_credit: $77.00
Total Balance: $100.00

Formula (Pseudocode):

FUNCTION validate_sms_balance(message, customers):
    
    # Step 1: Calculate segments
    message_length = LENGTH(message)           # 58
    per_segment = supplier_package.sms_character  # 160
    segments = CEIL(message_length / per_segment)  # 1
    
    # Step 2: Calculate cost per customer
    price_per_sms = supplier_package.price_per_sms  # $0.10
    cost_per_customer = price_per_sms * segments   # $0.10
    
    # Step 3: Calculate total required
    total_customers = COUNT(customers)          # 1,000
    total_required = cost_per_customer * total_customers  # $100.00
    
    # Step 4: Check total balance (both pools)
    sms_monthly_credit = supplier.sms_monthly_credit          # $23.00
    sms_topup_credit = supplier.sms_topup_credit      # $77.00
    total_balance = sms_monthly_credit + sms_topup_credit  # $100.00
    
    is_sufficient = total_balance >= total_required  # $100 >= $100 = TRUE
    
    IF NOT is_sufficient THEN
        shortage = total_required - total_balance
        RETURN {status: FALSE, shortage: shortage}
    END IF
    
    RETURN {status: TRUE}
END FUNCTION

Result: ✅ SUFFICIENT - Campaign proceeds ($100 >= $100)

SCENARIO F: SMS Campaign - Insufficient Balance

Setup:

Total Customers: 1,500
Message Length: 58 characters (1 segment)
Price per SMS: $0.10
Total Required: $150.00
sms_monthly_credit: $23.00
sms_topup_credit: $77.00
Total Balance: $100.00

Formula:

total_required = $0.10 × 1,500 = $150.00
total_balance = $23 + $77 = $100.00

is_sufficient = $100 >= $150 = FALSE
shortage = $150 - $100 = $50.00

Result: ❌ INSUFFICIENT - Shortage: $50.00

SCENARIO G: SMS Spending - sms_monthly_credit First

Setup:

sms_monthly_credit: $23.00
sms_topup_credit: $77.00
SMS Cost: $10.00

Formula:

IF sms_monthly_credit > 0 THEN
    # Use sms_monthly_credit credit first
    sms_monthly_credit_used = min(sms_monthly_credit, cost)
    sms_monthly_credit_remaining = sms_monthly_credit - sms_monthly_credit_used
    
    IF cost > sms_monthly_credit_used THEN
        # Still have remaining cost
        client_used = cost - sms_monthly_credit_used
        client_remaining = sms_topup_credit - client_used
    ELSE
        client_remaining = sms_topup_credit  # Unchanged
    END IF
ELSE
    # Use sms_topup_credit only
    client_remaining = sms_topup_credit - cost
END IF

Execution:

sms_monthly_credit_used = min($23, $10) = $10
sms_monthly_credit_remaining = $23 - $10 = $13
client_remaining = $77 (unchanged)

Result:

Pool	Before	After
sms_monthly_credit	$23.00	$13.00
sms_topup_credit	$77.00	$77.00

SCENARIO H: SMS Spending - sms_monthly_credit Exhausted, Switch to sms_topup_credit

Setup:

sms_monthly_credit: $5.00
sms_topup_credit: $77.00
SMS Cost: $30.00

Formula:

sms_monthly_credit_used = min($5, $30) = $5
remaining_cost = $30 - $5 = $25
sms_monthly_credit_remaining = $5 - $5 = $0

client_used = min($77, $25) = $25
client_remaining = $77 - $25 = $52

Result:

Pool	Before	After
sms_monthly_credit	$5.00	$0.00
sms_topup_credit	$77.00	$52.00

SCENARIO I: Pre-Flight Credit Check - Insufficient Balance (SMS Blocked)

Setup:

sms_monthly_credit: $3.00
sms_topup_credit: $2.00
Total Available: $5.00
SMS Cost: $10.00 (100 characters = 1 segment at $0.10/segment, but high base cost)

Formula:

total_balance = sms_monthly_credit + sms_topup_credit
total_balance = $3 + $2 = $5

has_credit = total_balance >= cost
has_credit = $5 >= $10 = FALSE

Result: SMS sending BLOCKED

Result:

Check	Value	Result
Total Balance	$5.00	—
SMS Cost	$10.00	—
Has Credit	FALSE	SMS BLOCKED

Behavior:

SendSmsServices::sendMessage() returns error
No SMS sent to Twilio
No credit deducted
Event: SmsCreditShortfall fired with shortfall = $5.00

SCENARIO J: Shortfall on Deduction (Negative Balance)

Setup:

sms_monthly_credit: $5.00
sms_topup_credit: $5.00
Total Available: $10.00
SMS Cost: $25.00

Formula:

fromMonthly = min($5, $25) = $5
remainingCost = $25 - $5 = $20
fromTopUp = min($5, $20) = $5
remainingCost = $20 - $5 = $15

shortfall = remainingCost = $15

sms_monthly_credit_remaining = $5 - $5 = $0
sms_topup_credit_remaining = $5 - $5 = $0

Result:

Pool	Before	After	Used
sms_monthly_credit	$5.00	$0.00	$5.00
sms_topup_credit	$5.00	$0.00	$5.00
Shortfall	—	$15.00	—

Behavior:

Credit deducted from both pools (exhausted)
credit_deducted_at timestamp set
SmsCreditShortfall event fired with shortfall = $15.00
SMS marked as sent but balance is negative

SCENARIO K: Undelivered Status - Credit Still Deducted

Setup:

sms_monthly_credit: $23.00
sms_topup_credit: $77.00
SMS Cost: $10.00
Twilio Status: undelivered

Context: Twilio charges for delivery attempts even if the message is not successfully delivered to the recipient. The undelivered status means the carrier accepted the message but could not deliver it (e.g., invalid number, phone off, etc.).

Formula:

fromMonthly = min($23, $10) = $10
remainingCost = $10 - $10 = $0
fromTopUp = min($77, $0) = $0

sms_monthly_credit_remaining = $23 - $10 = $13
sms_topup_credit_remaining = $77 - $0 = $77

Result: CREDIT DEDUCTED for undelivered

Result:

Pool	Before	After
sms_monthly_credit	$23.00	$13.00
sms_topup_credit	$77.00	$77.00
credit_deducted_at	NULL	2026-03-31 14:30:00
status	1 (Sending)	3 (Failed)

Comparison:

Status	Chargeable	Deduction	Status Update
`delivered`	Yes	Deduct credit	status = 2 (Sent)
`undelivered`	Yes	Deduct credit	status = 3 (Failed)
`failed`	No	No deduction	status = 3 (Failed)
`sent`	No*	Deducted by sweep job if stale	status = 1 (Sending)

* If stuck in sent for > 2 hours, sms:deduct-stale-credits command will deduct

Qualifying Criteria for Monthly Top-Up

Criteria:

Supplier package has sms_monthly_credit > 0
Top-up not yet applied this month
Supplier is active (status = active)

Note: When main admin changes sms_monthly_credit value in supplier_package table, the new value will be applied in the next month. Current month retains the previous configuration.

Database Schema Updates

Idempotency Check for Monthly Top-Up

The system uses the existing created_at column to prevent double top-ups:

-- Check if monthly_topup already applied this month
SELECT COUNT(*) FROM sms_balances 
WHERE supplier_id = :id 
  AND type = 'monthly_topup'
  AND created_at >= :month_start;

If a record exists for the current month, the job skips that supplier.

API Endpoints Summary

Method	Endpoint	Purpose
GET	`/admin/sms-balances`	Show index page
GET	`/admin/sms-balances/get-datas`	Paginated list
GET	`/admin/sms-balances/get-amount`	Current balance (total + split)
POST	`/admin/sms-balances`	Create sms_topup_credit
PUT	`/admin/sms-balances/{id}`	Update balance record
DELETE	`/admin/sms-balances/{id}`	Delete balance record
POST	`/admin/sms-blash`	Send SMS campaign
POST	`/api/twilio/callback`	Twilio webhook
POST	`/admin/sms-topup/manual`	Manual trigger top-up
GET	`/admin/sms-topup/logs`	Top-up history

Key Differences: Old vs New System

Aspect	Old System	New System
Balance Tracking	Single pool (`sms_balance`)	Two pools (`sms_monthly_credit` + `sms_topup_credit`)
Top-Up Source	Manual only	Manual + Auto monthly
Monthly Credit	None	Configurable in `supplier_packages.sms_monthly_credit`
Top-Up Formula	N/A	`max(0, package.sms_monthly_credit - remaining_sms_monthly_credit)`
Spend Order	N/A	`sms_monthly_credit` first, then `sms_topup_credit`
Compounding	N/A	No (max equals package.sms_monthly_credit)
sms_topup_credit Protection	N/A	Never touched by auto-topup

SMS Credit System Fix (March 2026)

Overview

A comprehensive fix was implemented to address critical issues in the SMS credit deduction system:

Problems Addressed

Issue	Description	Impact
Missing Pre-Flight Check	SMS sent without credit validation	Overspending, negative balances
Incorrect Chargeable Statuses	Only `delivered` deducted, not `undelivered`	Revenue loss (Twilio charges for both)
No Idempotency	Double deduction risk	Customer disputes
Stuck 'sent' Records	Messages stuck in `sent` status never charged	Revenue loss
No Shortfall Handling	Negative balances not detected	Uncaught overdrafts
Race Conditions	Concurrent sends could overspend	Data inconsistency

Solutions Implemented

1. Pre-Flight Credit Check

Method: SmsBalanceRepository::checkSufficientCredit()
Feature: Pessimistic locking (lockForUpdate())
Usage: SendSmsServices::sendMessage() blocks if insufficient credit

2. Correct Twilio Billing

Chargeable Statuses: delivered + undelivered
Non-Chargeable: failed, sending
Implementation: TwilioController::callback() uses deductSmsCampaign()

3. Idempotency via Timestamp

Column: credit_deducted_at (datetime, nullable)
Check: Skips deduction if already set
Prevents: Double deduction from callback + sweep job

4. Stale Record Sweep Job

Command: sms:deduct-stale-credits
Schedule: Every 30 minutes
Criteria: twillio_status = 'sent', created_at > 2 hours, credit_deducted_at IS NULL

5. Shortfall Event System

Event: SmsCreditShortfall
Trigger: When shortfall > 0 in deduction result
Purpose: Alert system for negative balance scenarios

6. Centralized Deduction Method

Method: SmsBalanceRepository::deductSmsCampaign()
Benefits: Consistent logging, event firing, idempotency across all paths

Updated Database Schema

Updated Twilio Callback Flow

Key Commands

Command	Description	Schedule
`sms:deduct-stale-credits`	Deduct credit for stale 'sent' records	Every 30 minutes

Test Coverage

New tests added to tests/Feature/SmsMonthlyCreditTest.php:

test_check_sufficient_credit_returns_true_when_balance_is_sufficient
test_check_sufficient_credit_returns_false_when_balance_is_insufficient
test_check_sufficient_credit_calculates_multi_segment_cost
test_deduct_sms_campaign_deducts_from_pools_and_marks_deducted
test_deduct_sms_campaign_is_idempotent
test_twilio_callback_deducts_on_undelivered_status
test_twilio_callback_does_not_deduct_on_failed_status
test_deduct_stale_sms_credits_command_processes_stale_records
test_deduct_stale_sms_credits_command_skips_recent_records
test_deduct_stale_sms_credits_command_skips_already_deducted_records
test_sms_credit_shortfall_event_is_fired_on_shortfall
test_credit_deducted_at_prevents_double_deduction_in_callback_and_sweep

Summary

The enhanced SMS Balance system now supports:

Manual sms_topup_credit - Admin adds paid SMS credit anytime
Monthly Auto Top-Up - Configurable amount per package (from supplier_packages.sms_monthly_credit)
Smart Spend Attribution - sms_monthly_credit used first, then sms_topup_credit
Non-Compounding - sms_monthly_credit never exceeds package limit
sms_topup_credit Protection - Paid credit never touched by auto-topup
Configurable Monthly Credit - Main admin sets sms_monthly_credit in supplier_package table

Important: Changes to supplier_package.sms_monthly_credit take effect in the next month, not immediately.

Formula Reference:

MONTHLY_LIMIT = supplier_package.sms_monthly_credit
TOP-UP = max(0, MONTHLY_LIMIT - sms_monthly_credit_remaining)
sms_monthly_credit_SPEND = min(sms_monthly_credit, cost)
sms_topup_credit_SPEND = cost - sms_monthly_credit_SPEND (if cost > sms_monthly_credit)

Overview​

High-Level Architecture Diagram​

Two Credit Pools System​

Complete Data Flow​

1. Adding sms_topup_credit (Manual Top-Up)​

2. Monthly Auto Top-Up Flow​

3. SMS Campaign - Balance Validation (With Two Pools)​

4. SMS Spending - Which Pool First?​

5. Twilio Callback (Delivery Status)​

Complete Calculation Scenarios​

SCENARIO A: Manual sms_topup_credit Addition​

SCENARIO B: Monthly Auto Top-Up - No Spend​

SCENARIO C: Monthly Auto Top-Up - Partial Spend​

SCENARIO D: Monthly Auto Top-Up - Full sms_monthly_credit + Partial sms_topup_credit​

SCENARIO E: SMS Campaign - Balance Validation​

SCENARIO F: SMS Campaign - Insufficient Balance​

SCENARIO G: SMS Spending - sms_monthly_credit First​

SCENARIO H: SMS Spending - sms_monthly_credit Exhausted, Switch to sms_topup_credit​

SCENARIO I: Pre-Flight Credit Check - Insufficient Balance (SMS Blocked)​

SCENARIO J: Shortfall on Deduction (Negative Balance)​

SCENARIO K: Undelivered Status - Credit Still Deducted​

Qualifying Criteria for Monthly Top-Up​

Database Schema Updates​

Idempotency Check for Monthly Top-Up​

API Endpoints Summary​

Key Differences: Old vs New System​

SMS Credit System Fix (March 2026)​

Overview​

Problems Addressed​

Solutions Implemented​

1. Pre-Flight Credit Check​

2. Correct Twilio Billing​

3. Idempotency via Timestamp​

4. Stale Record Sweep Job​

5. Shortfall Event System​

6. Centralized Deduction Method​

Updated Database Schema​

Updated Twilio Callback Flow​

Key Commands​

Test Coverage​

Summary​

Overview

High-Level Architecture Diagram

Two Credit Pools System

Complete Data Flow

1. Adding sms_topup_credit (Manual Top-Up)

2. Monthly Auto Top-Up Flow

3. SMS Campaign - Balance Validation (With Two Pools)

4. SMS Spending - Which Pool First?

5. Twilio Callback (Delivery Status)

Complete Calculation Scenarios

SCENARIO A: Manual sms_topup_credit Addition

SCENARIO B: Monthly Auto Top-Up - No Spend

SCENARIO C: Monthly Auto Top-Up - Partial Spend

SCENARIO D: Monthly Auto Top-Up - Full sms_monthly_credit + Partial sms_topup_credit

SCENARIO E: SMS Campaign - Balance Validation

SCENARIO F: SMS Campaign - Insufficient Balance

SCENARIO G: SMS Spending - sms_monthly_credit First

SCENARIO H: SMS Spending - sms_monthly_credit Exhausted, Switch to sms_topup_credit

SCENARIO I: Pre-Flight Credit Check - Insufficient Balance (SMS Blocked)

SCENARIO J: Shortfall on Deduction (Negative Balance)

SCENARIO K: Undelivered Status - Credit Still Deducted

Qualifying Criteria for Monthly Top-Up

Database Schema Updates

Idempotency Check for Monthly Top-Up

API Endpoints Summary

Key Differences: Old vs New System

SMS Credit System Fix (March 2026)

Overview

Problems Addressed

Solutions Implemented

1. Pre-Flight Credit Check

2. Correct Twilio Billing

3. Idempotency via Timestamp

4. Stale Record Sweep Job

5. Shortfall Event System

6. Centralized Deduction Method

Updated Database Schema

Updated Twilio Callback Flow

Key Commands

Test Coverage

Summary