Skip to main content

Appointment Booking

Overview​

An add-on app for booking appointments and queue slots through LIFF. The customer picks a location, then a service, a staff member and a date and time, and receives a booking with reminders sent ahead of the appointment.

Architecturally, the important point is that all of its tables live in a separate PostgreSQL schema named appointment, not in public, and none of them exist in Prisma. The app maintains its own numbered SQL files that are applied with psql — the same approach used by the bulletin and loyalty apps.

Core Data Structure​

appointment.location — branches and venues​

  • Basic details: name, description, address, phone
  • working_hours as JSONB holds the daily opening hours; blocked_dates holds closures
  • timezone (defaults to Asia/Bangkok), sort_order, status and soft delete deleted_date
  • Scoped by line_oa_id and organization_id

appointment.service — bookable services​

  • location_id is a foreign key with ON DELETE CASCADE
  • duration_minutes (defaults to 30), max_bookings_per_slot (defaults to 1) and requires_staff are the three values that drive slot availability
  • Pricing: price as DECIMAL(10,2) and currency (defaults to THB)

appointment.staff — service providers​

  • location_id foreign key, plus name, title and avatar_url
  • service_ids as JSONB lists the services this person can perform, stored as an array rather than in a separate join table

appointment.journey — the booking flow configuration​

One journey equals one booking link.

  • token VARCHAR(64) is unique and serves as the key in the LIFF URL
  • auto_confirm (defaults to true) and steps as JSONB define the sequence the customer sees
  • reminder_hours_before (defaults to 24) and no_show_grace_minutes (defaults to 30)

appointment.booking — the booking itself​

  • Foreign keys: journey_id, location_id, service_id and staff_id (nullable)
  • user_id VARCHAR(255) is the LINE userId of the person booking
  • Timing: booking_date as DATE, with start_time and end_time as TIME
  • State: status (defaults to pending), notes, form_data as JSONB and cancel_reason, plus a timestamp for each terminal state — confirmed_date, cancelled_date, completed_date
  • reminder_sent comes with the partial index (status, reminder_sent, booking_date) WHERE reminder_sent = false, which lets the cron find un-reminded bookings quickly
  • The (location_id, booking_date, status) index backs the CMS calendar view

Booking event triggers​

trg_booking_event_insert and trg_booking_event_update call appointment.notify_booking_event(), which emits pg_notify on the booking_event channel and also writes to event_outbox on a best-effort basis, catching undefined_table and skipping if the outbox is not present.

Events fire when a booking is created (booking_created) and only when its status changes (booking_status_changed) — not on every row update.

  • apps/appointment/migrations/001_create_schema.sql — the CREATE SCHEMA appointment statement
  • apps/appointment/migrations/002_create_tables.sql — all five core tables
  • apps/appointment/migrations/003_add_reminder_columns.sql — the reminder columns
  • apps/appointment/migrations/004_add_booking_event_trigger.sql — pg_notify and the outbox
  • schema-dumps/2026-07-24/schema.sql:26 (CREATE SCHEMA appointment) and the :582–:807 range

Connections to Other Services​

  • client-api-go serves the customer-facing LIFF. It computes available slots from working_hours combined with blocked_dates, duration_minutes and max_bookings_per_slot before creating the booking.
  • cms-api-go manages locations, services, staff, journeys and the booking calendar.
  • worker-go listens on booking_event to send LINE notifications and to act as a trigger-rule source, and runs the cron that sends reminders on schedule.
  • The app is enabled per channel through line_oa_app.app_id in LINE OA Channels, and its events flow into pg_notify & Event Outbox.