Trigger Query Portal/Balance Expiration — SMS
SMS

Balance Expiration

Same query as the Email version — change messageType to 'SMS' at the top of the WITH block. Fires when a customer's reward balance is within one week of expiring based on their award level's configured expiry duration. Customers with upcoming appointments are excluded.

Key Parameters
messageTypeSMS
familyIDclient family
shopIDlocation
minBalance5
apptFromtoday − 3 days
apptTotoday + 2 weeks
Tables
customers repair_orders reward_cards config_client_award_levels cash_ledgers customer_appointments
Purpose
Recover balance value before it's lost. Motivate a visit before expiry.
Send Time
10:12 AM
What it does
Targets customers whose last completed RO falls in the 1-week window at the expMonths − 1 month mark before today. That window is the final warning period before their balance expires.

Appointment exclusion removes customers who already have something scheduled in the surrounding window. The internal_completed_at dual condition handles the August 2025 data gap — ROs before that date don't have the field populated.
SMS dedup partitions by cus.id::text. Email dedup partitions by lower(cus.email). The single query handles both — only messageType needs to change.
CTEs
rewardsBalance
Current balance per customer
credits − debits from cash_ledgers, familyID scoped, deleted_at excluded
mainQuery
Candidate selection with dedup
Joins customers → repair_orders → reward_cards → config_client_award_levels → rewardsBalance. having clause applies expiry window and minBalance.
Output fields
candidateID
companyID
company
"Name"
"FirstName"
last_name
"FullName"
"Email"
"CustPhone"
"RewardsCard"
"RewardsCardPIN"
"RewardsBalance"
"LastService"
"BalExpirDate"
"ExpMonths"