Escalate bookings no provider has accepted

The trigger

One read-only SELECT against PostgreSQL. Rowfire polls it for new rows, and the rule fires once per booking.

SELECT b.id AS booking_id,
       b.service,
       b.city,
       b.customer_email,
       b.created_at,
       b.scheduled_for,
       LEAST(b.created_at + interval '2 hours',
             b.scheduled_for - interval '12 hours') AS escalate_at
FROM bookings b
WHERE b.accepted_at IS NULL
  AND b.cancelled_at IS NULL
  AND b.scheduled_for > now()

The setup, if you don’t run a marketplace

A marketplace has two sides. A customer books something: a cleaner for Thursday, a tutor for Saturday, a plumber for tomorrow morning. Then someone on the other side, the provider, has to accept the job. Until they do, the booking is a promise nobody has agreed to keep.

Most of the time a provider accepts within minutes. Sometimes nobody does. The job is too far away, the time is awkward, the pay looks low. The customer sees “confirmed” in their inbox and assumes it’s handled. The operations team finds out when the customer calls on the day, asking where everyone is.

Why the app can’t tell you

When a provider accepts, code runs and the app can emit an event. When nobody accepts, no code runs. There is nothing to emit. The only trace is a row in bookings with an empty accepted_at, and a clock that keeps going.

So the query finds it instead. Table and column names are a sketch; adapt them to your schema.

The clock

escalate_at is the earlier of two deadlines:

• two hours after the booking was made, so nothing waits all afternoon;
• twelve hours before the job starts, so a booking made late at night for 8am tomorrow still leaves time to find someone.

When a poll sees escalate_at pass for a row that still matches, the rule fires. The key is booking_id, so the rule fires once per booking.

The alert

No provider yet for {{ service }} in {{ city }}, booked for {{ scheduled_for }}. Made at {{ created_at }} by {{ customer_email }}.

Post it to an operations channel in Slack, open a Zendesk ticket so someone owns it, or both, with one rule each on the same trigger. Backtest the rule over the last few weeks to see how many bookings it would have caught, then run it in shadow before it goes live.

The same shape elsewhere

Anything that waits for a person to pick it up fits this query: a timestamp, an empty “picked up” column, and a deadline. See ticket orders paid but not shipped and escalate urgent tickets nobody has answered, or try a trigger like it on the live demo.