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

flowchart TB
    subgraph ADMIN["Admin Panel"]
        direction TB
        
        subgraph SMS_BALANCE["SMS Balance Management"]
            SB["/admin/sms-balances"]
            SBC["SmsBalanceController"]
            SBR["SmsBalanceRepository"]
            SB_VIEW["Index.vue\nCreate.vue\nAmountComponent.vue"]
        end
        
        subgraph SMS_BLAST["SMS Campaign/Blast"]
            BLAST["/admin/sms-blast"]
            BLAST_C["SmsBlastController"]
            BLAST_R["SmsBlastRepository"]
            BLAST_VIEW["Index.vue"]
        end
        
        subgraph MONTHLY_TOPUP["Monthly Top-Up System"]
            MT_CMD["notification:sms-balance\n(command)"]
            MT_JOB["MonthlyTopUpJob"]
            MT_REPO["SmsTopUpRepository"]
        end
    end
    
        subgraph DATABASE["Database"]
            TB_SMS_BAL["sms_balances\n(table)"]
            TB_SUP["suppliers\n(table)"]
            TB_HIST["sms_campaign_histories"]
            TB_HIST_DET["sms_campaign_history_details"]
            TB_LOG["log_notification_sms_balances"]
        end
    
    subgraph QUEUE_SYS["Queue & Jobs"]
        JOB["SendSmsCampaign\n(job)"]
        QUEUE_NODE["sms-campaign\n(queue)"]
    end
    
    subgraph EXTERNAL["External Services"]
        TWILIO["Twilio API"]
        TWILIO_CB["Twilio Callback"]
    end
    
    subgraph NOTIFICATION["Notifications"]
        EVENT["NotificationSmsBalanceEvent"]
        LISTENER["NotificationSmsBalanceListener"]
        MAIL["NotificationSmsBalanceMail"]
    end
    
    SB --> SBC
    SBC --> SBR
    SBC --> TB_SMS_BAL
    SBR --> TB_SUP
    SB_VIEW --> SBC
    
    BLAST --> BLAST_C
    BLAST_C --> BLAST_R
    BLAST_R --> TB_SUP
    BLAST_R --> TB_HIST
    BLAST_R --> TB_HIST_DET
    BLAST_VIEW --> BLAST_C
    
    MT_CMD --> MT_JOB
    MT_JOB --> MT_REPO
    MT_REPO --> TB_SMS_BAL
    MT_REPO --> TB_SUP
    
    TB_HIST_DET --> JOB
    JOB --> QUEUE_NODE
    QUEUE_NODE --> BLAST_R
    
    TWILIO_CB --> BLAST_R
    
    MT_CMD --> EVENT
    EVENT --> LISTENER
    LISTENER --> MAIL

Two Credit Pools System

The system now manages two separate credit pools:

graph LR
    subgraph "Total SMS Balance = sms_monthly_credit + sms_topup_credit"
        
        subgraph "sms_monthly_credit (Free)"
            I1["Initial: Configurable in supplier_package"]
            I2["Auto-refill monthly"]
            I3["Non-compounding"]
        end
        
        subgraph "sms_topup_credit (Paid)"
            C1["Manual top-up by Admin"]
            C2["No expiry"]
            C3["Protected from auto-deduction"]
        end
        
    end
    
    I1 --> I2 --> I3
    C1 --> C2 --> C3

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)

sequenceDiagram
    participant Admin as Admin User
    participant FE as Frontend (Vue)
    participant API as SmsBalanceController
    participant Repo as SmsBalanceRepository
    participant DB as Database
    
    Admin->>FE: Click "Add New SMS Balance"
    FE->>FE: Open Create Modal
    Admin->>FE: Enter Amount ($)
    FE->>API: POST /admin/sms-balances
    API->>API: Validate Request (SmsBalanceRequest)
    API->>DB: Create SmsBalance Record (type: client)
    DB-->>API: Return SmsBalance Object
    
    API->>Repo: addBalanceSupplier(amount, supplierId)
    Repo->>DB: UPDATE suppliers.sms_topup_credit = suppliers.sms_topup_credit + amount
    DB-->>Repo: Confirmation
    Repo-->>API: Success
    API-->>FE: {status: 1, message: "Balance success added"}
    FE->>Admin: Show Success Message

2. Monthly Auto Top-Up Flow

sequenceDiagram
    participant System as Laravel Scheduler
    participant Job as MonthlyTopUpJob
    participant Repo as SmsTopUpRepository
    participant DB as Database
    participant BalRepo as SmsBalanceRepository
    
    System->>Job: Run Monthly (1st of month)
    Job->>DB: GET all suppliers with supplier_package.sms_monthly_credit > 0
    
    loop For Each Qualifying Supplier
        Job->>Repo: calculateTopUpAmount(supplierId)
        Repo->>DB: Get supplier_package.sms_monthly_credit as monthly_limit
        Repo->>DB: Get supplier.sms_monthly_credit_remaining
        Repo->>DB: Calculate: topUp = max(0, monthly_limit - sms_monthly_credit_remaining)
        
        alt topUp > 0
            Repo->>DB: Check: Any monthly_topup this month?
            DB-->>Repo: Check created_at + type='monthly_topup'
            
            alt Already processed this month
                Note over Repo: Skip - Already has monthly_topup
            else Not yet processed
                Repo->>DB: CREATE SmsBalance (type: monthly_topup)
                Repo->>BalRepo: addSmsMonthlyCredit(topUp, supplierId)
                Repo->>DB: UPDATE suppliers.sms_monthly_credit = monthly_limit
            end
        else
            Note over Repo: No top-up needed (sms_monthly_credit still available)
        end
    end
    
    Job-->>System: Complete

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

sequenceDiagram
    participant Admin as Admin User
    participant FE as Frontend
    participant API as SmsBlashController
    participant Repo as SmsBlashRepository
    participant DB as Database
    
    Admin->>FE: Click "Send SMS Blast"
    FE->>FE: Select Customers, Enter Message
    FE->>API: POST /admin/sms-blash
    
    rect rgb(240, 248, 255)
        Note over API: VALIDATION PHASE
    end
    
    API->>Repo: checkEnoughSmsBalanceWithCustomer(message, customers)
    Repo->>DB: Get supplier.sms_monthly_credit + supplier.sms_topup_credit
    Repo->>DB: Get supplier_package.price_per_sms & sms_caracter
    Repo->>DB: Calculate total_required
    
    alt TOTAL_BALANCE < REQUIRED
        Repo-->>API: {status: false, shortage: $X}
        API-->>FE: {status: 0, message: "Insufficient balance"}
        FE->>Admin: Show Error
    else Balance SUFFICIENT
        Repo-->>API: {status: true}
        API->>DB: Create SmsCampaignHistory + Details
        API->>Job: dispatch(smsCampaignHistoryDetails)
        API-->>FE: Success
    end

4. SMS Spending - Which Pool First?

sequenceDiagram
    participant Customer as Customer receives SMS
    participant Job as SendSmsCampaign Job
    participant Repo as SmsBlashRepository
    participant DB as Database
    
    Job->>Repo: sendSmsToCustomer(detail)
    
    rect rgb(255, 240, 240)
        Note over Repo: SPEND ATTRIBUTION ORDER
    end
    
    # Step 1: Check if sms_monthly_credit available
    DB->>Repo: sms_monthly_credit = supplier.sms_monthly_credit
    
    alt sms_monthly_credit > 0
        Note over Repo: Use sms_monthly_credit FIRST
        Repo->>DB: DEDUCT from sms_monthly_credit
        Repo->>DB: LOG deduction as "sms_monthly_spent"
    else
        Note over Repo: sms_monthly_credit exhausted, use sms_topup_credit
        Repo->>DB: DEDUCT from sms_topup_credit
        Repo->>DB: LOG deduction as "sms_topup_spent"
    end
    
    Repo->>Twilio: Send SMS

5. Twilio Callback (Delivery Status)

sequenceDiagram
    participant Twilio as Twilio Webhook
    participant API as TwilioController
    participant Repo as SmsBlashRepository
    participant DB as Database
    
    Twilio->>API: POST /api/twilio/callback
    API->>DB: Find SmsCampaignHistoryDetail by sid
    
    alt Record Found
        API->>DB: Update twillio_status
        
        alt Status = "delivered"
            Note over API: ⚠️ BALANCE DEDUCTION STILL COMMENTED OUT
            API->>Repo: updateStatusCampaignHistoryDetail(id, 2)
        else Status = "failed" or "undelivered"
            API->>Repo: updateStatusCampaignHistoryDetail(id, 3, error)
        else Other Status
            API->>Repo: updateStatusCampaignHistoryDetail(id, 1)
        end
        
        API-->>Twilio: 200 OK
    else Record Not Found
        API-->>Twilio: 404 Not Found
    end

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

graph TD
    A["Supplier Package"] --> B["Get sms_monthly_credit from package"]
    
    B --> C{sms_monthly_credit > 0?}
    
    C -->|Yes| D["Eligible for monthly top-up"]
    C -->|No| E["Not Eligible"]
    
    D --> F["Check: Is top-up already applied this month?"]
    F --> G["No"] --> H["Apply Top-Up"]
    F --> I["Yes"] --> J["Skip - Already Processed"]
    
    E --> K["Manual sms_topup_credit only"]
    
    style D fill:#90EE90
    style E fill:#FFB6C1
    style H fill:#90EE90
    style J fill:#FFE4B5

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

erDiagram
    supplier_packages {
        bigint id PK
        string name
        double price
        double sms_monthly_credit "Monthly credit amount (e.g., 23)"
        int validity_days
    }
    
    suppliers {
        bigint id PK
        string name
        double sms_monthly_credit "Current remaining monthly credit"
        double sms_topup_credit "Manual top-up by Admin (migrated from sms_balance)"
        bigint supplier_package_id FK
    }
    
    sms_balances {
        bigint id PK
        double amount
        string type "manual_topup, monthly_topup"
        bigint supplier_id FK
        bigint created_by FK
        bigint updated_by FK
        timestamp created_at "Used to check if monthly_topup already applied this month"
    }
    
    supplier_packages ||--o{ suppliers : "has many"
    suppliers ||--o{ sms_balances : "has many"

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

erDiagram
    sms_campaign_history_details {
        bigint id PK
        bigint sms_campaign_history_id FK
        string sid "Twilio message SID"
        int status "0=Waiting, 1=Sending, 2=Sent, 3=Failed"
        string twillio_status "delivered, undelivered, failed, sent"
        double total_price "Cost of this SMS"
        datetime credit_deducted_at "NEW: Prevents double deduction"
    }

Updated Twilio Callback Flow

sequenceDiagram
    participant Twilio as Twilio Webhook
    participant API as TwilioController
    participant Repo as SmsBalanceRepository
    participant DB as Database
    participant Event as SmsCreditShortfall
    
    Twilio->>API: POST /api/twilio/callback<br/>MessageSid, MessageStatus
    API->>DB: Find SmsCampaignHistoryDetail by SID
    
    alt Status = "delivered" or "undelivered"
        API->>Repo: deductSmsCampaign(detail)
        Repo->>DB: Check credit_deducted_at
        
        alt Not yet deducted
            Repo->>DB: Deduct from pools (monthly first)
            Repo->>DB: Update credit_deducted_at = now()
            
            alt shortfall > 0
                Repo->>Event: Fire SmsCreditShortfall event
            end
        end
        
        API->>API: Update status (delivered=2, undelivered=3)
    else Status = "failed"
        API->>API: Update status = 3 (Failed)
        Note over API: NO deduction for failed
    end
    
    API-->>Twilio: 200 OK

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