Balance Expiration — Email — Trigger Query Portal

What it does

Targets customers who have an active rewards card (status = '56001') and whose last completed repair order falls within a 1-week window starting exactly expMonths − 1 months before today. That window represents the final warning period before their balance expires.

The appointment exclusion block removes customers who already have a scheduled appointment in the surrounding window (apptFrom to apptTo), since they don't need a nudge — they're already coming in.

The internal_completed_at condition handles a data gap: that field was introduced on August 5, 2025. ROs before that date don't have it populated, so both conditions are required to avoid excluding valid historical records. This logic can be removed once all active ROs postdate that date.

To switch to SMS: change messageType to 'SMS' at the top of the WITH block. The contact filter and dedup logic both respond to that value automatically.

CTEs

messageType

Switches between SMS and Email

Controls contact field filtering and dedup partition key throughout the query

rewardsBalance

Calculates current balance per customer

credits minus debits from cash_ledgers, scoped to familyID, excludes deleted_at rows

mainQuery

Core candidate selection with dedup

Joins customers → repair_orders → reward_cards → config_client_award_levels → rewardsBalance. Applies all filters and computes row_number() for dedup.

Firing logic

The having clause targets customers whose last RO date falls in a 1-week window at the expMonths − 1 month mark:

max(ro.updated_at)::date between (current_date − (expMonths−1 months)) and (current_date − (expMonths−1 months) + 7 days)

Balance must be ≥ minBalance. Customers with no balance don't get a reminder about nothing.

Output fields

candidateID

companyID

company

"Name"

"FirstName"

last_name

"FullName"

"Email"

"CustPhone"

"RewardsCard"

"RewardsCardPIN"

"RewardsBalance"

"LastService"

"BalExpirDate"

"ExpMonths"

Query

/*  Balance Expiration: for SMS and Email
 *  last updated 4 May 2026
 *  fires when a customer's reward balance is approaching expiration
 *  based on their award level's card_expiry_duration (in months)
 *  window: last service date falls within 1 week of the expiration threshold
 *
 *  exclusion: customers with upcoming appointments are excluded
 *  apptFrom / apptTo define the appointment exclusion window
 *
 *  ro.internal_completed_at logic: field was introduced 2025-08-05
 *  ROs before that date do not have it populated -- both conditions
 *  are required to avoid excluding valid historical records
 *  this logic can be removed once all active ROs postdate 2025-08-05
 */
with recursive
    messageType  as (values ('Email')),
    familyID     as (values (40)),
    shopID       as (values (12)),
    minBalance   as (values (5)),
    apptFrom     as (values (date_trunc('day', current_date - interval '3 days')::date)),
    apptTo       as (values (date_trunc('day', current_date + interval '2 weeks')::date)),
    shopName     as (select clz.name from client_locations clz where clz.id = (table shopID)),
    rewardsBalance as (
        select
            casldg.customer_id,
            casldg.client_id,
            coalesce(sum(casldg.reward_amount_credit), 0) - coalesce(sum(casldg.reward_amount_debit), 0) as rewards_balance
        from cash_ledgers casldg
        where
            casldg.client_id = (table familyID)
            and casldg.deleted_at is null
        group by casldg.customer_id, casldg.client_id
    ),
    mainQuery as (
        select
            cus.id as candidateID,
            (table shopID) as companyID,
            (table shopName) as company,
            trim(concat(regexp_replace(initcap(regexp_replace(regexp_replace(lower(cus.first_name),'(\s*[&/]\s*)', ' and ','g'),'[#*]', '','g')),'\s+And\s+', ' and ','g'))) as "Name",
            trim(concat(regexp_replace(initcap(regexp_replace(regexp_replace(lower(cus.first_name),'(\s*[&/]\s*)', ' and ','g'),'[#*]', '','g')),'\s+And\s+', ' and ','g'))) as "FirstName",
            trim(regexp_replace(initcap(cus.last_name), '[#*]', '', 'g')) as last_name,
            trim(concat(regexp_replace(initcap(regexp_replace(regexp_replace(lower(cus.first_name),'(\s*[&/]\s*)', ' and ','g'),'[#*]', '','g')),'\s+And\s+', ' and ','g'), ' ', regexp_replace(initcap(cus.last_name), '[#*]', '', 'g'))) as "FullName",
            lower(cus.email) as "Email",
            cus.verified_cell as "CustPhone",
            rc.reward_card_number as "RewardsCard",
            rc.reward_card_pin as "RewardsCardPIN",
            rb.rewards_balance as "RewardsBalance",
            max(ro.updated_at)::date as "LastService",
            date_trunc('day', max(ro.updated_at)::date + (ccal.card_expiry_duration || ' months')::interval)::date as "BalExpirDate",
            ccal.card_expiry_duration as "ExpMonths",
            row_number() over (
                partition by
                    case
                        when (table messageType) = 'SMS' then cus.id::text
                        when (table messageType) = 'Email' then lower(cus.email)
                    end
                order by cus.id
            ) as rn
        from
            customers cus
            inner join repair_orders ro on ro.customer_id = cus.id and ro.client_location_id = (table shopID)
            inner join reward_cards rc on rc.customer_id = cus.id and rc.client_location_id = (table shopID) and rc.status = '56001'
            inner join config_client_award_levels ccal on ccal.id = rc.award_level and ccal.status = '56201'
            inner join rewardsBalance rb on rb.customer_id = cus.id
        where
            ro.status = 'Completed'
            and (ro.labour_amount > 0 or ro.parts_amount > 0)
            and (
                (ro.updated_at::date >= '2025-08-05' and ro.internal_completed_at is not null)
                or (ro.updated_at::date < '2025-08-05')
            )
            and not exists (
                select 1 from customer_appointments cusap
                where cusap.customer_id = cus.id
                    and cusap.schedule_date::date between (table apptFrom) and (table apptTo)
                    and cusap.deleted_at is null
                    and cusap.schedule_note ilike '%automated%' is null
            )
            and case
                when (table messageType) = 'SMS' then cus.verified_cell is not null and cus.verified_cell != '' and cus.verified_cell != '000-000-0000'
                when (table messageType) = 'Email' then cus.email is not null and cus.email != ''
                else true
            end
        group by cus.id, rc.reward_card_number, rc.reward_card_pin, rc.award_level, rb.rewards_balance, ccal.card_expiry_duration
        having
            date_trunc('day', max(ro.updated_at))::date between
                (current_date - (ccal.card_expiry_duration - 1 || ' months')::interval) and
                (current_date - (ccal.card_expiry_duration - 1 || ' months')::interval + '1 weeks'::interval)
            and rb.rewards_balance >= (table minBalance)
    )
select
    candidateID, companyID, company,
    "Name", "FirstName", last_name, "FullName",
    "Email", "CustPhone", "RewardsCard", "RewardsCardPIN",
    "RewardsBalance", "LastService", "BalExpirDate", "ExpMonths"
from mainQuery
where rn = 1 and "FirstName" <> last_name
/* end */