Booking & Reservation System

Concordia’s booking system enables customers to reserve services with providers through an availability-based scheduling system with status management and cancellation policies.

Overview

The booking system consists of two main components:

Availability Schedules (services_availability.schema.ts) - Provider defines when they’re available
Bookings (services_bookings.schema.ts) - Customer reservations of specific time slots

Availability Management

From src/database/schemas/services_availability.schema.ts:

export const servicesAvailability = pgTable("services_availability", {
  id: text("id").primaryKey(),
  serviceId: text("service_id").notNull(),
  dayOfWeek: integer("day_of_week").notNull(), // 0 (dim) → 6 (sam)
  startTime: text("start_time").notNull(),
  endTime: text("end_time").notNull(),
  isAvailable: boolean("is_available").notNull().default(true),
  createdAt: timestamp("created_at").defaultNow().notNull(),
}, (table) => [
  uniqueIndex("idx_services_availability_unique").on(
    table.serviceId,
    table.dayOfWeek,
    table.startTime,
  ),
]);

Availability Fields

text

required

Unique availability entry identifier

serviceId

text

required

Reference to the service listing

dayOfWeek

integer

required

Day of the week using numeric representation:

0 = Sunday
1 = Monday
2 = Tuesday
3 = Wednesday
4 = Thursday
5 = Friday
6 = Saturday

startTime

text

required

Start time in 24-hour HH:MM formatExample: "09:00", "14:30"

endTime

text

required

End time in 24-hour HH:MM formatExample: "17:00", "18:30"

isAvailable

boolean

default:true

Whether this time slot is currently available for bookingSet to false to temporarily block a recurring time slot without deleting it

createdAt

timestamp

required

When this availability entry was created

Unique Constraint

The schema enforces uniqueness on (serviceId, dayOfWeek, startTime) to prevent duplicate time slots:

You cannot create two availability entries with the same service, day, and start time. This prevents scheduling conflicts.

Recurring Weekly Schedule

Availability entries define a recurring weekly schedule. For example:

[
  {
    "serviceId": "service-123",
    "dayOfWeek": 1,
    "startTime": "09:00",
    "endTime": "12:00",
    "isAvailable": true
  },
  {
    "serviceId": "service-123",
    "dayOfWeek": 1,
    "startTime": "14:00",
    "endTime": "17:00",
    "isAvailable": true
  },
  {
    "serviceId": "service-123",
    "dayOfWeek": 3,
    "startTime": "09:00",
    "endTime": "17:00",
    "isAvailable": true
  }
]

This creates:

Monday: Available 9am-12pm and 2pm-5pm
Wednesday: Available 9am-5pm

Multiple availability windows per day are supported, perfect for providers with lunch breaks or split schedules.

Booking Management

From src/database/schemas/services_bookings.schema.ts:

export const servicesBookings = pgTable("services_bookings", {
  id: text("id").primaryKey(),
  serviceId: text("service_id").notNull(),
  customerId: text("customer_id").notNull(),
  providerId: text("provider_id").notNull(),
  bookingDate: text("booking_date").notNull(),
  bookingTime: text("booking_time").notNull(),
  durationMinutes: integer("duration_minutes").notNull(),
  totalPrice: text("total_price"),
  currency: text("currency").default("EUR"),
  status: text("status").notNull().default("pending"),
  customerMessage: text("customer_message"),
  providerResponse: text("provider_response"),
  cancelledAt: timestamp("cancelled_at"),
  completedAt: timestamp("completed_at"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

Core Booking Fields

text

required

Unique booking identifier

serviceId

text

required

Reference to the service being booked

customerId

text

required

User ID of the customer making the booking

providerId

text

required

User ID of the service provider

Schedule Details

bookingDate

text

required

Date of the service in YYYY-MM-DD formatExample: "2026-03-15"

bookingTime

text

required

Start time of the service in HH:MM formatExample: "14:00"

durationMinutes

integer

required

Duration of the booked service in minutesExample: 90 for 1.5 hours

The combination of bookingDate, bookingTime, and durationMinutes defines the exact time slot reserved.

Payment Information

totalPrice

text

Final price as a string for decimal precisionExample: "75.00"

currency

text

default:"EUR"

ISO 4217 currency codeExample: "EUR", "USD", "GBP"

Booking Status

status

text

default:"pending"

Current booking status with possible values:

pending - Awaiting provider confirmation
confirmed - Provider has confirmed the booking
completed - Service has been delivered
cancelled - Booking was cancelled
no_show - Customer didn’t show up

Communication

customerMessage

text

Optional message from customer to providerExample: "I need wheelchair access. Is that available?"

providerResponse

text

Provider’s response to the booking or customer messageExample: "Yes, we have wheelchair access. See you at 2pm!"

Lifecycle Timestamps

cancelledAt

timestamp

When the booking was cancelled (if applicable)Only set when status transitions to cancelled

completedAt

timestamp

When the service was marked as completedSet when status transitions to completed

createdAt

timestamp

required

When the booking was initially created

updatedAt

timestamp

required

Last modification time, automatically updated

Booking Status Workflow

Status Transitions

pending → confirmed

Provider accepts the booking requestTypically within 24-48 hours of booking

pending → cancelled

Either party cancels before confirmationSets cancelledAt timestamp

confirmed → completed

Service successfully deliveredSets completedAt timestamp, enables review

confirmed → cancelled

Cancellation after confirmationMay have policy implications (see cancellation policies)

confirmed → no_show

Customer didn’t attend appointmentProvider marks as no-show

Cancellation Policies

From services_listings.schema.ts, services define cancellation rules:

export const servicesListings = pgTable("services_listings", {
  // ... other fields
  bookingAdvanceHours: integer("booking_advance_hours"),
  cancellationHours: integer("cancellation_hours"),
});

bookingAdvanceHours

integer

Minimum hours in advance required for bookingExample: 24 means customers must book at least 1 day ahead

cancellationHours

integer

Hours before service when free cancellation is allowedExample: 48 means free cancellation up to 2 days before

Example Policy Enforcement

function canCancelFree(booking, service) {
  const bookingDateTime = new Date(`${booking.bookingDate}T${booking.bookingTime}`);
  const hoursUntilBooking = (bookingDateTime - new Date()) / (1000 * 60 * 60);
  
  return hoursUntilBooking >= service.cancellationHours;
}

function canBookNow(bookingDate, bookingTime, service) {
  const requestedDateTime = new Date(`${bookingDate}T${bookingTime}`);
  const hoursUntilBooking = (requestedDateTime - new Date()) / (1000 * 60 * 60);
  
  return hoursUntilBooking >= service.bookingAdvanceHours;
}

Database Indexes

From the schema files:

Availability Indexes

CREATE INDEX idx_services_availability_service ON services_availability(service_id);

Optimizes queries for all availability slots for a service.

Booking Indexes

CREATE INDEX idx_services_bookings_service ON services_bookings(service_id);
CREATE INDEX idx_services_bookings_customer ON services_bookings(customer_id);
CREATE INDEX idx_services_bookings_provider ON services_bookings(provider_id);
CREATE INDEX idx_services_bookings_status ON services_bookings(status);
CREATE INDEX idx_services_bookings_date ON services_bookings(booking_date);

Indexes support efficient queries for:

All bookings for a service
Customer’s booking history
Provider’s booking calendar
Bookings by status for dashboards
Bookings by date for calendar views

Integration with Services

From services_listings.schema.ts, the relationship is defined:

export const servicesListingsRelations = relations(servicesListings, ({ one, many }) => ({
  // ... other relations
  availability: many(servicesAvailability),
  bookings: many(servicesBookings),
}));

Each service can have:

Multiple availability windows (recurring weekly schedule)
Multiple bookings (past and future reservations)

Booking Flow Example

1. Provider Sets Availability

// Provider defines Monday 9am-5pm availability
const availability = {
  id: generateId(),
  serviceId: "service-123",
  dayOfWeek: 1, // Monday
  startTime: "09:00",
  endTime: "17:00",
  isAvailable: true,
};

2. Customer Requests Booking

// Customer books for Monday, March 15, 2026 at 2pm
const booking = {
  id: generateId(),
  serviceId: "service-123",
  customerId: "customer-456",
  providerId: "provider-789",
  bookingDate: "2026-03-15",
  bookingTime: "14:00",
  durationMinutes: 60,
  totalPrice: "50.00",
  currency: "EUR",
  status: "pending",
  customerMessage: "First time customer, looking forward to it!",
};

3. Provider Confirms

// Provider accepts booking
booking.status = "confirmed";
booking.providerResponse = "Confirmed! See you on Monday at 2pm.";
booking.updatedAt = new Date();

4. Service Completed

// After service delivery
booking.status = "completed";
booking.completedAt = new Date();
booking.updatedAt = new Date();

// Customer can now leave a review

Conflict Prevention

To prevent double-booking:

Check availability - Verify day/time matches an active availability entry
Check existing bookings - Ensure no overlapping confirmed bookings
Consider duration - Account for service duration when checking conflicts

function hasConflict(newBooking, existingBookings) {
  const newStart = new Date(`${newBooking.bookingDate}T${newBooking.bookingTime}`);
  const newEnd = new Date(newStart.getTime() + newBooking.durationMinutes * 60000);
  
  return existingBookings.some(existing => {
    if (existing.status !== 'confirmed') return false;
    
    const existingStart = new Date(`${existing.bookingDate}T${existing.bookingTime}`);
    const existingEnd = new Date(existingStart.getTime() + existing.durationMinutes * 60000);
    
    return (newStart < existingEnd && newEnd > existingStart);
  });
}

Best Practices

For Providers

Set realistic availability - Only mark times you can reliably serve customers
Use isAvailable: false - Temporarily block slots without deleting recurring schedule
Respond promptly - Confirm or decline bookings within 24 hours
Communicate clearly - Use providerResponse for important details

For Booking System

Validate against availability - Check day/time matches provider schedule
Prevent double-booking - Lock slots or use transactions
Enforce policies - Respect bookingAdvanceHours and cancellationHours
Send notifications - Email/notify on booking, confirmation, cancellation
Enable reviews - Only allow reviews for completed bookings

For Customers

Book in advance - Respect bookingAdvanceHours requirement
Provide details - Use customerMessage for special requests
Cancel early - Follow cancellationHours policy for free cancellation
Leave reviews - Help other customers after completed bookings

Overview

Core Features

User Guides

Booking & Reservation System

Booking & Reservation System

Overview

Availability Management

Availability Fields

Unique Constraint

Recurring Weekly Schedule

Booking Management

Core Booking Fields

Schedule Details

Payment Information

Booking Status

Communication

Lifecycle Timestamps

Booking Status Workflow

Status Transitions

Cancellation Policies

Example Policy Enforcement

Database Indexes

Availability Indexes

Booking Indexes

Integration with Services

Booking Flow Example

1. Provider Sets Availability

2. Customer Requests Booking

3. Provider Confirms

4. Service Completed

Conflict Prevention

Best Practices

For Providers

For Booking System

For Customers

Build docs developers (and LLMs) love

Overview

Core Features

User Guides

Documentation Index

​Booking & Reservation System

​Overview

​Availability Management

​Availability Fields

​Unique Constraint

​Recurring Weekly Schedule

​Booking Management

​Core Booking Fields

​Schedule Details

​Payment Information

​Booking Status

​Communication

​Lifecycle Timestamps

​Booking Status Workflow

​Status Transitions

​Cancellation Policies

​Example Policy Enforcement

​Database Indexes

​Availability Indexes

​Booking Indexes

​Integration with Services

​Booking Flow Example

​1. Provider Sets Availability

​2. Customer Requests Booking

​3. Provider Confirms

​4. Service Completed

​Conflict Prevention

​Best Practices

​For Providers

​For Booking System

​For Customers

Build docs developers (and LLMs) love

Booking & Reservation System

Overview

Availability Management

Availability Fields

Unique Constraint

Recurring Weekly Schedule

Booking Management

Core Booking Fields

Schedule Details

Payment Information

Booking Status

Communication

Lifecycle Timestamps

Booking Status Workflow

Status Transitions

Cancellation Policies

Example Policy Enforcement

Database Indexes

Availability Indexes

Booking Indexes

Integration with Services

Booking Flow Example

1. Provider Sets Availability

2. Customer Requests Booking

3. Provider Confirms

4. Service Completed

Conflict Prevention

Best Practices

For Providers

For Booking System

For Customers