Trigger Query Portal/Day of Appointment Balance Reminder — SMS
SMS

Day of Appointment — Balance Reminder

Fires on the day of a customer's appointment when they have a loyalty balance at or above the minimum threshold. Reminds them to mention their balance to the service advisor.

Key Parameters
messageTypeSMS
familyIDclient family
shopIDlocation
minBalance5
Tables
customer_appointments vehicles customers client_locations reward_cards cash_ledgers
Purpose
Ensure customer doesn't forget to apply their balance at today's visit.
Send Time
Morning — before appointment
What it does
Pulls all appointments where date_trunc('day', start_time)::date = current_date for the target shop. Filters to customers with a rewards balance ≥ minBalance.

No date stepping needed — this trigger always fires for today. Vehicle information is included (VehicleYearModel, AppTime) so the message can reference the specific visit.

Dedup via row_number() partitioned by customer_id (SMS) or email (Email), ordered by start_time — so if a customer has two appointments today, the earlier one wins.
This is the simplest query in the set — no date walking, no interval math. It anchors directly on current_date.
CTEs
rewardsBalance
Current balance per customer
credits − debits from cash_ledgers, familyID scoped, deleted_at excluded
mainQuery
Candidate selection with dedup
Anchors on customer_appointments for today. Joins vehicles, customers, reward_cards, rewardsBalance. Balance threshold applied in where clause.
Output fields
companyID
"Name"
"FirstName"
last_name
"FullName"
candidateID
"RewardsBalance"
reward_card_number
"Email"
"lvehicleID"
"VehicleYearModel"
"ScheduleDate"
"AppDate"
"AppTime"