Booking & Reservation System

Service booking system with availability scheduling, reservation management, and status workflows

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

id · 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 format

Example: "09:00", "14:30"

endTime · text · required

End time in 24-hour HH:MM format

Example: "17:00", "18:30"

isAvailable · boolean · default: true

Whether this time slot is currently available for booking

Set 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

id · 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 format

Example: "2026-03-15"

bookingTime · text · required

Start time of the service in HH:MM format

Example: "14:00"

durationMinutes · integer · required

Duration of the booked service in minutes

Example: 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 precision

Example: "75.00"

currency · text · default: "EUR"

ISO 4217 currency code

Example: "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 provider

Example: "I need wheelchair access. Is that available?"

providerResponse · text

Provider’s response to the booking or customer message

Example: "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 completed

Set 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 request

Typically within 24-48 hours of booking

pending → cancelled

Either party cancels before confirmation

Sets cancelledAt timestamp

confirmed → completed

Service successfully delivered

Sets completedAt timestamp, enables review

confirmed → cancelled

Cancellation after confirmation

May have policy implications (see cancellation policies)

confirmed → no_show

Customer didn’t attend appointment

Provider 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 booking

Example: 24 means customers must book at least 1 day ahead

cancellationHours · integer

Hours before service when free cancellation is allowed

Example: 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:

1. Check availability - Verify day/time matches an active availability entry
2. Check existing bookings - Ensure no overlapping confirmed bookings
3. 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

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

For Booking System

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

For Customers

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