Appointment Booking

Column-level detail for the entire appointment schema (location/service/staff/journey/booking)

Overview

The appointment schema has 5 tables. In reference order: location (branches) → service (bookable services) / staff (providers) / journey (booking flow configuration with the public LIFF token), all feeding into booking (actual reservations). This schema is applied separately with psql rather than through Prisma (migrations 001-004).

Table `appointment.location`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`line_oa_id`	`INTEGER`	NO	-	LINE OA this branch belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`name`	`VARCHAR(255)`	NO	-	Branch name
`description`	`TEXT`	YES	-	Branch description
`address`	`TEXT`	YES	-	Branch address
`phone`	`VARCHAR(50)`	YES	-	Contact phone number
`working_hours`	`JSONB`	NO	`'{}'`	Per-day opening hours, used to compute bookable slots
`blocked_dates`	`JSONB`	YES	`'[]'`	Dates the branch is closed or not accepting bookings
`timezone`	`VARCHAR(50)`	NO	`'Asia/Bangkok'`	Branch timezone
`status`	`VARCHAR(20)`	NO	`'active'`	Record status
`sort_order`	`INTEGER`	NO	`0`	Display order
`created_by`	`INTEGER`	YES	-	Creator user id
`updated_by`	`INTEGER`	YES	-	Last updater user id
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp
`updated_date`	`TIMESTAMPTZ(3)`	YES	-	Last update timestamp
`deleted_date`	`TIMESTAMPTZ(3)`	YES	-	Soft-delete timestamp

Table `appointment.service`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`location_id`	`INTEGER`	NO	-	FK to `appointment.location(id)`, ON DELETE CASCADE
`line_oa_id`	`INTEGER`	NO	-	LINE OA this service belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`name`	`VARCHAR(255)`	NO	-	Service name
`description`	`TEXT`	YES	-	Service description
`duration_minutes`	`INTEGER`	NO	`30`	Service duration in minutes, used to derive the end time
`max_bookings_per_slot`	`INTEGER`	NO	`1`	Maximum bookings allowed in one time slot
`requires_staff`	`BOOLEAN`	NO	`false`	Whether a provider must be chosen for this service
`price`	`DECIMAL(10,2)`	YES	-	Service price
`currency`	`VARCHAR(10)`	YES	`'THB'`	Price currency
`status`	`VARCHAR(20)`	NO	`'active'`	Record status
`sort_order`	`INTEGER`	NO	`0`	Display order
`created_by`	`INTEGER`	YES	-	Creator user id
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp
`updated_date`	`TIMESTAMPTZ(3)`	YES	-	Last update timestamp
`deleted_date`	`TIMESTAMPTZ(3)`	YES	-	Soft-delete timestamp

Table `appointment.staff`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`location_id`	`INTEGER`	NO	-	FK to `appointment.location(id)`, ON DELETE CASCADE
`line_oa_id`	`INTEGER`	NO	-	LINE OA this provider belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`name`	`VARCHAR(255)`	NO	-	Provider name
`title`	`VARCHAR(255)`	YES	-	Job title shown next to the name
`avatar_url`	`VARCHAR(500)`	YES	-	Profile picture URL
`service_ids`	`JSONB`	YES	`'[]'`	Ids of the services this provider can deliver
`status`	`VARCHAR(20)`	NO	`'active'`	Record status
`sort_order`	`INTEGER`	NO	`0`	Display order
`created_by`	`INTEGER`	YES	-	Creator user id
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp
`updated_date`	`TIMESTAMPTZ(3)`	YES	-	Last update timestamp
`deleted_date`	`TIMESTAMPTZ(3)`	YES	-	Soft-delete timestamp

Table `appointment.journey`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`location_id`	`INTEGER`	NO	-	FK to `appointment.location(id)`, ON DELETE CASCADE
`line_oa_id`	`INTEGER`	NO	-	LINE OA this flow belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`name`	`VARCHAR(255)`	NO	-	Booking flow name
`description`	`TEXT`	YES	-	Flow description
`token`	`VARCHAR(64)`	NO	-	Public token that opens the LIFF booking page (unique)
`auto_confirm`	`BOOLEAN`	NO	`true`	Confirm bookings automatically, or hold for admin approval
`steps`	`JSONB`	YES	-	Step / form definition shown during booking
`status`	`VARCHAR(20)`	NO	`'active'`	Record status
`created_by`	`INTEGER`	YES	-	Creator user id
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp
`updated_date`	`TIMESTAMPTZ(3)`	YES	-	Last update timestamp
`deleted_date`	`TIMESTAMPTZ(3)`	YES	-	Soft-delete timestamp
`reminder_hours_before`	`INTEGER`	YES	`24`	How many hours before the appointment the reminder is sent (migration 003)
`no_show_grace_minutes`	`INTEGER`	YES	`30`	Grace period in minutes before a booking counts as a no-show (migration 003)

Table `appointment.booking`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`journey_id`	`INTEGER`	NO	-	FK to `appointment.journey(id)` — the flow used to book
`location_id`	`INTEGER`	NO	-	FK to `appointment.location(id)` — the branch booked
`service_id`	`INTEGER`	NO	-	FK to `appointment.service(id)` — the service booked
`staff_id`	`INTEGER`	YES	-	FK to `appointment.staff(id)` — the provider, if chosen
`user_id`	`VARCHAR(255)`	NO	-	LINE user id of the person booking
`line_oa_id`	`INTEGER`	NO	-	LINE OA this booking belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`booking_date`	`DATE`	NO	-	Appointment date
`start_time`	`TIME`	NO	-	Appointment start time
`end_time`	`TIME`	NO	-	Appointment end time
`status`	`VARCHAR(20)`	NO	`'pending'`	Booking status
`notes`	`TEXT`	YES	-	Notes from the customer or the admin
`form_data`	`JSONB`	YES	-	Answers to the form defined in `journey.steps`
`cancel_reason`	`TEXT`	YES	-	Cancellation reason
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp
`updated_date`	`TIMESTAMPTZ(3)`	YES	-	Last update timestamp
`confirmed_date`	`TIMESTAMPTZ(3)`	YES	-	When the booking was confirmed
`cancelled_date`	`TIMESTAMPTZ(3)`	YES	-	When the booking was cancelled
`completed_date`	`TIMESTAMPTZ(3)`	YES	-	When the service was completed
`reminder_sent`	`BOOLEAN`	YES	`false`	Whether the advance reminder has been sent (migration 003)

Notes

Foreign keys: service.location_id, staff.location_id and journey.location_id all point at appointment.location(id) with ON DELETE CASCADE, so deleting a branch removes its children. The four FKs on booking (journey_id, location_id, service_id, staff_id) are not cascading, so master data cannot be deleted out from under existing reservations
Unique: appointment.journey.token is unique both as a column constraint and via index idx_appt_journey_token, since it is the public key that opens the booking page
Partial indexes: the status-filtered indexes on location, service, staff and journey all carry WHERE deleted_date IS NULL, matching queries that only look at live rows. idx_appt_booking_reminder carries WHERE reminder_sent = false so the reminder worker scans only rows still awaiting a notification
Triggers (migration 004): trg_booking_event_insert and trg_booking_event_update on appointment.booking call appointment.notify_booking_event(), which issues a pg_notify on the booking_event channel (type booking_created on INSERT, booking_status_changed when status changes), consumed by the pg-listener worker. The same function also attempts an INSERT into event_outbox for reliability, silently skipping if that table does not exist
booking.user_id stores the LINE user id as a string; it is not a foreign key to line_user in the public schema

Overview​

Table appointment.location​

Table appointment.service​

Table appointment.staff​

Table appointment.journey​

Table appointment.booking​

Notes​