Skip to main content
Version: RK Auto

Technical Specification: 1-Day Service Booking Reminder

Overview​

The 1-Day Service Booking Reminder feature automatically sends notifications (Push and Email) to customers exactly one day before their scheduled service appointment.

To guarantee that exactly one notification is sent per appointment without requiring database schema changes, tracking tables, or Redis-based deduplication, this feature utilizes a Stateless 30-Minute Rolling Window Architecture.

1. Architectural Design​

The "Rolling Window" Deduplication Strategy​

Instead of polling all upcoming appointments and tracking which ones have already received a reminder, the background service relies entirely on strict time-bounding.

  1. The BookingReminderBackgroundService runs precisely every 30 minutes.
  2. During each tick, it executes a SQL query looking for service requests where the scheduledstartat time is exactly between 24 hours and 24.5 hours from NOW().
  3. Because time moves forward linearly, the "window" of eligible appointments shifts by 30 minutes on every tick.
  4. Result: An appointment will only ever fall into the 30-minute query window exactly once. Once the 30 minutes pass, the appointment falls out of the query scope naturally, preventing duplicate sends.

Hyper-Accurate Local Timezones​

Because the scheduledstartat column is stored in UTC, sending a notification saying "Your appointment is tomorrow at 14:00" is confusing to the end user unless it is translated to their exact local time.

Instead of relying on a generic dealership timezone, this system uses the GeoTimeZone package.

  • It takes the exact Latitude and Longitude attached to the service request.
  • It resolves this geographical point to a standard IANA timezone string (e.g., America/New_York).
  • It uses TimeZoneConverter to reliably map that string across Windows/Linux environments to accurately format the appointment time in the email/push body.

2. Components​

A. Data Access Layer (BookingReminderDal)​

Executes the highly optimized, time-bounded SQL query:

SELECT ...
FROM servicerequests sr
...
WHERE sr.status NOT IN ('Canceled', 'Completed')
  AND sr.scheduledstartat IS NOT NULL
  AND sr.latitude IS NOT NULL 
  AND sr.longitude IS NOT NULL
  -- Target bookings exactly 24 hours to 24.5 hours from NOW
  AND sr.scheduledstartat > NOW() + INTERVAL '24 hours' 
  AND sr.scheduledstartat <= NOW() + INTERVAL '24 hours 30 minutes'

B. Background Service (BookingReminderBackgroundService)​

A .NET BackgroundService registered as a Hosted Service.

  • Interval: 30 Minutes.
  • Responsibilities:
    1. Invokes the DAL.
    2. Resolves timezones per-customer.
    3. Checks customer opt-in/opt-out preferences via INotificationSettingsDal.
    4. Dispatches the notification request to INotificationManager.

C. Notification Configuration (NotifyMsgSettings.cs & NotifyMsgKeys.cs)​

Registers the new NotificationType.ServiceReminderOneDay and binds it to the Push Notification text and the Email HTML template.

D. Email Template (ServiceReminder1Day.html)​

A mobile-responsive, branded HTML template utilizing substitution tokens like {FirstName}, {RequestNumber}, and {ScheduledWindow}.

3. Failure & Edge Case Handling​

ScenarioSystem Behavior
User opts out of emailsThe service respects INotificationSettingsDal. If the user has disabled "Appointments" emails, the background service will gracefully skip dispatching the email.
Server goes down for 1 hourBecause the architecture relies on strict time windows, any appointment whose 24-hour mark falls within the exact hour the server was down will not receive a reminder. This is an intentional tradeoff: it favors guaranteeing zero duplicates (statelessness) over guaranteed delivery in catastrophe scenarios.
Customer cancels appointmentThe SQL query excludes Canceled or Completed statuses, ensuring reminders are only sent for active bookings.
Missing Lat/LongThe SQL query mandates latitude IS NOT NULL and longitude IS NOT NULL. Appointments without location data are skipped to prevent sending incorrect UTC times.

4. Maintenance & Configuration​

To modify this feature in the future:

  • Change the 1-Day interval to a 2-Day interval: Modify the SQL query intervals in BookingReminderDal.cs from 24 hours to 48 hours.
  • Change the email text: Edit VanTrackerService/EmailTemplates/ServiceReminder1Day.html.
  • Change the push text: Edit VanTrackerService/NotificationCore/NotifyMsgSettings.cs.