SMS Monthly Credit — Implementation Guide

Purpose: Explain how the SMS credit system works with visual workflows and concrete examples.

---

Overview

The system manages two separate credit pools:

Pool	Purpose	How it gets filled
Monthly Credit (sms_monthly_credit)	Auto-refilled monthly based on supplier package	sms:monthly-topup command (1st of each month)
Top-Up Credit (sms_topup_credit)	Manual additions by admin	Admin adds via SMS Balance page

Spending Priority: Monthly pool is used first → falls back to top-up pool when exhausted.

---

1. Workflow Diagrams

A. Manual Top-Up Flow

flowchart TD
    A[Admin: Add SMS Balance] --> B[Enter amount in modal]
    B --> C[POST /admin/sms-balances]
    C --> D[Create SmsBalance record<br/>type=manual_topup]
    D --> E[sms_topup_credit += amount]
    E --> F[Monthly pool unchanged]
    
    style A fill:#e1f5fe
    style E fill:#c8e6c9

Steps:

1. Admin clicks "Add New SMS Balance" button
2. Enters amount in modal form
3. System creates balance record (type: manual_topup)
4. Top-up credit increases — monthly pool is NOT affected

---

B. Monthly Auto Top-Up Flow

flowchart TD
    A[sms:monthly-topup<br/>command runs] --> B[Query all suppliers<br/>with package credit]
    B --> C{For each supplier}
    C --> D[Calculate top-up needed<br/>monthly_limit - current]
    D --> E{Already applied<br/>this month?}
    E -->|Yes| F[Skip supplier]
    E -->|No| G[Add SmsBalance<br/>type=monthly_topup]
    G --> H[sms_monthly_credit += amount<br/>capped at limit]
    H --> C
    
    style A fill:#fff3e0
    style H fill:#c8e6c9

Steps:

1. Command runs (typically 1st of month)
2. For each supplier with a package:
   • Calculate: max(0, monthly_limit - current_monthly_credit)
   • If not already applied this month → add to monthly pool
3. Top-up credit is NEVER touched by auto-top-up

---

C. SMS Campaign Flow

flowchart TD
    A[Admin: Compose SMS] --> B[Calculate segments & cost]
    B --> C{Sufficient balance?}
    C -->|No| D[Show error]
    C -->|Yes| E[Create Campaign History]
    E --> F[Bulk insert recipients]
    F --> G[Dispatch SendSmsCampaign job]
    G --> H[Send via Twilio API]
    H --> I[Twilio callback on delivery]
    I --> J[Update status + deduct balance]
    
    style C fill:#e8f5e9
    style J fill:#ffecb3

Steps:

1. Admin enters message → system calculates segments and cost
2. Validate: monthly_credit + topup_credit >= total_cost
3. If valid: create campaign and queue sending job
4. Twilio sends SMS → callback triggers balance deduction

---

D. Balance Deduction Flow (Twilio Callback)

sequenceDiagram
    participant Twilio
    participant API as TwilioController
    participant Repo as SmsBalanceRepository
    participant DB as Supplier

    Twilio->>API: POST /api/twilio/callback<br/>MessageSid, MessageStatus
    API->>API: Find detail record by SID
    
    alt Status = "delivered"
        API->>Repo: deductFromSmsPools(total_price)
        Repo->>DB: Read monthly & topup pools
        DB-->>Repo: Current balances
        Repo->>Repo: fromMonthly = min(monthly, amount)<br/>fromTopUp = min(topup, remaining)
        Repo->>DB: monthly -= fromMonthly<br/>topup -= fromTopUp
        API->>API: Set status = 2 (Sent)
    else Status = "failed" or "undelivered"
        API->>API: Set status = 3 (Failed)<br/>NO deduction
    end
    
    API-->>Twilio: 200 OK

Key Points:

• Only delivered messages are charged
• Failed/undelivered: status updated, but no deduction
• Deduction always uses monthly first, then top-up

---

E. Balance Notification Flow

flowchart TD
    A[notification:sms-balance<br/>command runs] --> B[Check today's count]
    B --> C{Max 3 emails<br/>reached?}
    C -->|Yes| D[Skip notification]
    C -->|No| E[Fire event]
    E --> F[Send email to supplier]
    G[Log notification count<br/>increments +1]
    
    style F fill:#e1f5fe
    style G fill:#fff3e0

Rules:

• Max 3 notifications per day per supplier
• Email sent to supplier's registered email address
• Command should run daily (not currently scheduled)

---

F. 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:

1. Supplier package has sms_monthly_credit > 0
2. Top-up not yet applied this month (idempotency check)
3. Supplier is active

---

2. Calculation Scenarios

A. Manual Top-Up (Adding Credit)

	Before	After
Monthly Credit	15	15
Top-Up Credit	35	85
Admin adds	—	50
Total	50	100

NEW_SMS_TOPUP_CREDIT = OLD_SMS_TOPUP_CREDIT + ADDED_AMOUNT
NEW_SMS_TOPUP_CREDIT = 35 + 50 = 85

Note: Monthly pool is NOT affected by manual top-up.

---

B. Monthly Auto Top-Up - No Spend

Setup:

• Package monthly limit: 23
• Month-start monthly credit: 23
• Top-up credit: 77
• SMS Spend: 0

Formula:

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

Result: NO top-up needed — monthly credit unchanged.

Pool	Month Start	Spend	End of Month	Top-Up	Next Month
Monthly	23	0	23	0	23
Top-Up	77	0	77	0	77

---

C. Monthly Auto Top-Up - Partial Spend

Setup:

• Package monthly limit: 23
• Month-start monthly credit: 23
• Top-up credit: 77
• SMS Spend: 20

Formula:

sms_monthly_credit_remaining = 23 - 20 = 3
topUp = max(0, 23 - 3) = 20

Result:

Pool	Month Start	Spend	End of Month	Top-Up	Next Month
Monthly	23	-20	3	+20	23
Top-Up	77	0	77	0	77
Total	100	-20	80	+20	100

---

D. Monthly Auto Top-Up - Full Monthly + Partial Top-Up Spend

Setup:

• Package monthly limit: 23
• Month-start monthly credit: 23
• Top-up credit: 77
• SMS Spend: 50

Formula:

sms_monthly_credit_spent = min(23, 50) = 23
sms_topup_spent = 50 - 23 = 27

sms_monthly_credit_remaining = 23 - 23 = 0
sms_topup_remaining = 77 - 27 = 50

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

Result:

Pool	Month Start	Spend	End of Month	Top-Up	Next Month
Monthly	23	-23	0	+23	23
Top-Up	77	-27	50	0	50
Total	100	-50	50	+23	73

---

E. SMS Campaign - Balance Validation (Sufficient)

Setup:

• Customers: 1,000
• Message: 58 characters (1 segment)
• Price per SMS: $0.10
• Monthly Credit: 23
• Top-Up Credit: 77
• Total: 100

Formula:

segments = ceil(58 / 160) = 1
cost_per_customer = 0.10 * 1 = 0.10
total_required = 0.10 * 1000 = 100

is_sufficient = (23 + 77) >= 100 = TRUE

Result: ✅ Campaign proceeds — balance is sufficient.

---

F. SMS Campaign - Balance Validation (Insufficient)

Setup:

• Customers: 1,500
• Message: 58 characters (1 segment)
• Price per SMS: $0.10
• Monthly Credit: 23
• Top-Up Credit: 77
• Total: 100

Formula:

total_required = 0.10 * 1500 = 150
is_sufficient = 100 >= 150 = FALSE
shortage = 150 - 100 = 50

Result: ❌ Campaign blocked — shortage of $50.

---

G. SMS Spending - Monthly Credit First

Setup:

• Monthly Credit: 23
• Top-Up Credit: 77
• SMS Cost: 10

Formula:

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

Pool	Before	After
Monthly	23	13
Top-Up	77	77

---

H. SMS Spending - Monthly Exhausted, Switch to Top-Up

Setup:

• Monthly Credit: 5
• Top-Up Credit: 77
• SMS Cost: 30

Formula:

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

sms_topup_used = min(77, 25) = 25
sms_topup_remaining = 77 - 25 = 52

Pool	Before	After
Monthly	5	0
Top-Up	77	52

---

I. Idempotency - Prevents Double Top-Up

Scenario: Running monthly top-up twice in the same month.

First Run:

• Monthly credit before: 5
• Top-up needed: max(0, 23 - 5) = 18
• After first top-up: Monthly = 23

Second Run (Same Month):

• System checks: Is there a monthly_topup record this month?
• Result: Skip — already applied

Third Run (Next Month):

• System checks: Is there a monthly_topup record this month?
• Result: Allows new top-up

// Idempotency check
SELECT COUNT(*) FROM sms_balances 
WHERE supplier_id = :id 
  AND type = 'monthly_topup'
  AND created_at >= :month_start;

---

J. Decimal Precision

All balance calculations are rounded to 4 decimal places.

Example:

Input: 33.333333
Stored: 33.3333

---

K. Edge Case - Insufficient Total Balance

Setup:

• Monthly Credit: 5
• Top-Up Credit: 5
• SMS Cost: 20

Formula:

fromMonthly = min(5, 20) = 5
remaining = 20 - 5 = 15
fromTopUp = min(5, 15) = 5
shortfall = 20 - (5 + 5) = 10

Result:

[
    'total_deducted'           => 10,
    'from_monthly'             => 5,
    'from_topup'               => 5,
    'remaining_balance_monthly'=> 0,
    'remaining_balance_topup'  => 0,
    'shortfall'                => 10
]

Note: In practice, the system validates balance before sending, so this scenario should not occur.

---

3. Usage Tracking Methods

The system provides methods to track credit usage over time:

getCurrentMonthUsage()

Returns total successful SMS spend in the current month.

SmsBalanceRepository::getCurrentMonthUsage();
// Returns: float (e.g., 40.00)

Rules:

• Only counts status = 2 (successful/delivered)
• Excludes: pending (status=1), failed (status=3)
• Time range: start of current month to now

---

getCurrentMonthTopUp()

Returns total top-up amounts added in the current month.

SmsBalanceRepository::getCurrentMonthTopUp();
// Returns: float (e.g., 90.50)

Includes:

• Manual top-ups (manual_topup type)
• Auto monthly top-ups (monthly_topup type)

---

getLastMonthRemainingBalance()

Returns remaining balance from last month.

SmsBalanceRepository::getLastMonthRemainingBalance();
// Returns: float

Formula:

= (Manual top-ups last month + Monthly top-ups last month) 
  - (Successful SMS sends last month)

Rules:

• Only counts successful sends (status=2)
• Excludes failed sends
• Excludes current month transactions

---

4. Deduction Priority Logic

┌─────────────────────────────────────────┐
│           SMS Cost = 15                 │
│         (Monthly=3, TopUp=50)           │
└─────────────────┬───────────────────────┘
                  │
                  ▼
        ┌───────────────────┐
        │ From Monthly Pool │
        │ min(3, 15) = 3    │
        └─────────┬─────────┘
                  │
                  ▼
        ┌───────────────────┐
        │ Remaining = 12    │
        └─────────┬─────────┘
                  │
                  ▼
        ┌───────────────────┐
        │ From Top-Up Pool  │
        │ min(50, 12) = 12  │
        └───────────────────┘

deductFromSmsPools() returns:

[
    'total_deducted'            => float,
    'from_monthly'              => float,
    'from_topup'                => float,
    'remaining_balance_monthly' => float,
    'remaining_balance_topup'   => float,
    'shortfall'                 => float
]

---

5. Key Commands

Command	Description	Schedule
sms:monthly-topup	Refill monthly credits	Manual (not scheduled)
notification:sms-balance	Send low balance alerts	Manual (not scheduled)

To enable automatic scheduling, add to app/Console/Kernel.php:

$schedule->command('sms:monthly-topup')->monthlyOn(1, '00:00');
$schedule->command('notification:sms-balance')->dailyAt('08:00');

---

6. Database Schema (Simplified)

suppliers table

Column	Description
sms_monthly_credit	Current monthly pool balance
sms_topup_credit	Current top-up pool balance

supplier_packages table

Column	Description
sms_monthly_credit	Monthly limit (e.g., 23)
price_per_sms	Cost per segment
sms_caracter	Characters per segment (usually 160)

sms_balances table

Column	Description
amount	Credit amount added
type	manual_topup or monthly_topup
created_by	Admin user (null for auto top-up)
created_at	Used for idempotency check

sms_campaign_history_details table

Column	Description
sid	Twilio message SID
status	0=Waiting, 1=Sending, 2=Sent, 3=Failed
total_price	Cost of this SMS
twillio_status	Twilio delivery status

---

7. 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)
Top-Up Protection	N/A	Never touched by auto-topup

---

Summary

Flow	Trigger	What Happens
Manual Top-Up	Admin adds balance	Top-up pool increases
Monthly Top-Up	sms:monthly-topup command	Monthly pool refilled to limit
SMS Campaign	Admin sends blast	Balance validated → SMS sent → deducted on delivery
Balance Notification	notification:sms-balance command	Email to supplier (max 3/day)

Core Rules:

1. Monthly pool is always used first
2. Top-up pool is only used when monthly is exhausted
3. Only delivered messages are charged
4. Failed/undelivered messages: no deduction
5. Monthly top-up is non-compounding (max = package limit)
6. Idempotency prevents double top-up in same month

---

Formula Reference

MONTHLY_LIMIT = supplier_package.sms_monthly_credit
TOP-UP = max(0, MONTHLY_LIMIT - sms_monthly_credit_remaining)

SPEND_FROM_MONTHLY = min(sms_monthly_credit, cost)
REMAINING_COST = cost - SPEND_FROM_MONTHLY
SPEND_FROM_TOPUP = min(sms_topup_credit, REMAINING_COST)

TOTAL_DEDUCTED = SPEND_FROM_MONTHLY + SPEND_FROM_TOPUP

---

8. SMS Credit System Fix (March 2026)

Fixes applied to address critical issues with credit validation and deduction.

---

A. Pre-Flight Credit Check Flow

flowchart TD
    A[SendSmsServices::sendMessage] --> B[checkSufficientCredit]
    B --> C{Has Credit?}
    C -->|No| D[Return error<br/>SMS blocked]
    C -->|Yes| E[Proceed to Twilio API]
    
    style A fill:#e1f5fe
    style C fill:#ffecb3
    style D fill:#ffcdd2
    style E fill:#c8e6c9

Features:

• Pessimistic locking via Supplier::lockForUpdate()
• Cost calculation based on message segments
• Returns: has_credit, cost, balance, message

---

B. Updated Balance Deduction Flow

sequenceDiagram
    participant Twilio
    participant API as TwilioController
    participant Repo as SmsBalanceRepository
    participant DB as Database
    participant Event as SmsCreditShortfall

    Twilio->>API: POST callback<br/>MessageSid, MessageStatus
    API->>DB: Find detail 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<br/>monthly first
            Repo->>DB: Set credit_deducted_at = now()
            
            alt shortfall > 0
                Repo->>Event: Fire SmsCreditShortfall
            end
        end
        
        API->>API: Update status<br/>delivered=2, undelivered=3
    else Status = "failed"
        API->>API: Set status = 3
        Note over API: NO deduction for failed
    end
    
    API-->>Twilio: 200 OK

Key Changes:

• Deducts for both delivered AND undelivered (Twilio charges for delivery attempts)
• credit_deducted_at timestamp prevents double deduction
• Shortfall event fires when shortfall > 0

---

C. Stale Record Sweep Flow

flowchart TD
    A[sms:deduct-stale-credits<br/>runs every 30 min] --> B[Query records with<br/>twillio_status='sent']
    B --> C{created_at > 2 hours?}
    C -->|No| D[Skip - Too recent]
    C -->|Yes| E{credit_deducted_at null?}
    E -->|No| F[Skip - Already deducted]
    E -->|Yes| G[Deduct via<br/>deductSmsCampaign]
    G --> H[Mark credit_deducted_at]
    
    style A fill:#fff3e0
    style G fill:#c8e6c9
    style D fill:#ffe4b5
    style F fill:#ffe4b5

Command: sms:deduct-stale-credits (scheduled every 30 minutes)

---

D. Calculation Scenario: Insufficient Balance

Setup:

• Monthly Credit: 5.00
• Top-Up Credit: 5.00
• SMS Cost: 20.00

Formula:

fromMonthly = min(5, 20) = 5
remaining = 20 - 5 = 15
fromTopUp = min(5, 15) = 5
shortfall = 20 - (5 + 5) = 10

Result:

Pool	Before	After
Monthly	5.00	0.00
Top-Up	5.00	0.00
Shortfall	—	10.00

Note: Shortfall event SmsCreditShortfall is fired with amount 10.00.

---

E. Updated Key Commands

Command	Description	Schedule
sms:monthly-topup	Refill monthly credits	Manual (not scheduled)
notification:sms-balance	Send low balance alerts	Manual (not scheduled)
sms:deduct-stale-credits	Deduct credit for stale 'sent' records	Every 30 minutes

---

F. Updated Database Schema

sms_campaign_history_details table

Column	Description
sid	Twilio message SID
status	0=Waiting, 1=Sending, 2=Sent, 3=Failed
total_price	Cost of this SMS
twillio_status	Twilio delivery status
credit_deducted_at	NEW: Timestamp to prevent double deduction

---

G. Updated Core Rules

1. Monthly pool is always used first
2. Top-up pool is only used when monthly is exhausted
3. Delivered AND undelivered messages are charged (Twilio billing policy)
4. Failed messages: no deduction
5. Monthly top-up is non-compounding (max = package limit)
6. Idempotency prevents double top-up in same month
7. credit_deducted_at prevents double deduction
8. Pre-flight check blocks SMS when balance is insufficient

---

Summary

Flow	Trigger	What Happens
Manual Top-Up	Admin adds balance	Top-up pool increases
Monthly Top-Up	sms:monthly-topup command	Monthly pool refilled to limit
SMS Campaign	Admin sends blast	Pre-flight check → SMS sent → deducted on delivery
Balance Notification	notification:sms-balance command	Email to supplier (max 3/day)
Stale Credit Sweep	sms:deduct-stale-credits command	Deduct for 'sent' records > 2 hours old

---

Formula Reference

MONTHLY_LIMIT = supplier_package.sms_monthly_credit
TOP-UP = max(0, MONTHLY_LIMIT - sms_monthly_credit_remaining)

SPEND_FROM_MONTHLY = min(sms_monthly_credit, cost)
REMAINING_COST = cost - SPEND_FROM_MONTHLY
SPEND_FROM_TOPUP = min(sms_topup_credit, REMAINING_COST)

TOTAL_DEDUCTED = SPEND_FROM_MONTHLY + SPEND_FROM_TOPUP
SHORTFALL = cost - TOTAL_DEDUCTED

PRE_FLIGHT_CHECK = (monthly_credit + topup_credit) >= calculated_cost