Booking System
The booking is the central entity. All business logic revolves around creating, managing, and tracking bookings across 4 channels with a database-enforced status machine.
Booking Status Machine
Every booking progresses through a finite set of states. Transitions are enforced in application code and validated by the booking_status column (enum stored as string via HasConversion<string>() in EF Core, with a CHECK constraint at the database level).
4 Booking Channels
Bookings enter the system through four distinct channels, each with different user flows and business characteristics.
Admin Channel 70.6% of volume
Operators create bookings for walk-in and phone customers via the web admin dashboard. This is the dominant channel today and will remain high-volume even as customer self-booking grows.
Field Manager Channel FM App
On-site staff create bookings from the FM mobile app. Field managers are scoped to their assigned fields only — they cannot see or book other fields.
Customer Channel 2.5%, target 15%+
Authenticated customers self-book through the mobile app. Currently the smallest channel, but growing it is a top business priority. Uses slot holds to prevent conflicts during the booking flow.
Contract Channel 6.8%
Automated generation from recurring agreements. Contract bookings have the lowest cancellation rate at 12.7%, making them the most reliable revenue source.
Slot Hold Management
When a customer begins the booking form in the mobile app, a hold is placed on the selected slot to prevent another customer from booking the same time while the first customer is completing their form.
- Hold duration: 5 minutes from creation
- Cleanup job: A background job runs every 30 seconds to expire stale holds
- One hold per slot: Enforced by a
UNIQUE(field_id, date, start_time)constraint on thebooking_holdstable - Problem solved: In v2, customers would fill out the entire booking form only to discover the slot was taken at submission time. This caused a 50.1% booking page drop-off rate. Holds eliminate the "discover-unavailability-too-late" problem.
Conflict Prevention
UNIQUE index on (field_id, date, start_time) combined with a serializable transaction or INSTEAD OF INSERT trigger that validates no overlapping time ranges. SQL Server will reject conflicting inserts. Even if application code has a bug, the database constraint prevents the double-booking.
In v2, conflict prevention was handled entirely in application code. The contract generator bypassed these checks, leading to 4 data corruption incidents where two bookings occupied the same field and time slot. In v3, the database itself enforces this invariant — no application code path can violate it.
Auto-Transitions (Background Jobs)
Background jobs handle automatic status progression so bookings never get stuck in stale states.
| Transition | Trigger | Purpose |
|---|---|---|
| confirmed → completed | 24h after event end time | Automatically marks sessions as completed after they occur |
| booked → cancelled | 24h after session date if still unconfirmed | Auto-cancels bookings that were never confirmed (no payment, no admin action) |
Mandatory Fields on Every Booking
| Field | Type | Description |
|---|---|---|
field_id |
string (max 15 chars) | Which field the booking is for |
customer_id |
string (max 15 chars) | Who booked (the customer) |
creator_id |
string (max 15 chars) | Who created the booking (may differ from customer — e.g., admin booking on behalf of customer) |
date |
DATE | Session date |
start_time |
TIME | Slot start time |
end_time |
TIME | Slot end time |
status |
string (enum stored as string) | Current state: booked, confirmed, completed, cancelled, no_show |
source |
string (enum stored as string) | Channel: admin, fm, customer, contract, guest |
price |
DECIMAL, NOT NULL, > 0 | Mandatory pricing — v2 had 58.3% of bookings with zero price. DB constraint prevents this. |
payment_status |
string (enum stored as string) | Payment state: unpaid, partial, paid, refunded |
Cancellation
Cancellation is a controlled process with mandatory documentation to support business analysis and reduce cancellation rates.
- Allowed from: booked or confirmed state
- Mandatory reason code: Every cancellation requires a reason (why was it cancelled?). This is not optional.
- Audit trail: Cancellation creates an immutable audit log entry recording who cancelled, when, and why
- Cannot cancel completed or no_show: Once a booking reaches a terminal state, it cannot be cancelled