Schema Overview

The Booking Kit’s database schema consists of 26 tables managed by Drizzle ORM with full TypeScript type inference.

Table groups

Core booking tables

Table	Description
providers	Service providers (doctors, stylists, consultants). Includes metadata JSONB for settings.
event_types	Bookable services. Duration, price, questions, limits, confirmation mode.
availability_rules	Recurring schedules using RRULE. Linked to providers.
availability_overrides	Date-specific schedule modifications (block/extend hours).
bookings	Customer appointments. starts_at/ends_at in UTC with EXCLUDE USING gist. Includes optional resource_id FK.
booking_events	Append-only audit trail for every booking status change.
out_of_office	Extended unavailability periods (vacations, leave).

Resource tables

Table	Description
resources	Bookable physical/virtual units (restaurant tables, hotel rooms, coworking desks, tennis courts). capacity in units, type for filtering.
resource_availability_rules	Recurring schedules for resources using RRULE (mirrors availability_rules).
resource_availability_overrides	Date-specific resource schedule modifications (mirrors availability_overrides).

Team tables

Table	Description
teams	Groups of providers with assignment strategy.
team_members	Provider membership in teams with role.
team_event_types	Event types associated with teams.

Payment tables

Table	Description
payments	Payment records linked to bookings. Amount, status, Stripe IDs.

Automation tables

Table	Description
workflows	Workflow definitions (trigger, conditions, actions).
workflow_logs	Execution history for workflows.
webhook_subscriptions	Registered webhook endpoints with triggers and secrets.
webhook_deliveries	Delivery attempts with response status and retry count.

Recurring & seats tables

Table	Description
recurring_bookings	Recurring booking series metadata (frequency, count).
booking_seats	Individual attendees for group/seat bookings.

Email & customer tables

Table	Description
email_delivery_log	Log of email delivery attempts with status.
customer_preferences	Customer email preferences and opt-out status.

Walk-In tables

Table	Description
walk_in_queue	Walk-in queue entries with status and wait time estimates.

Multi-tenancy tables

Table	Description
organizations	Tenant organizations with branding and settings.
organization_members	User membership in organizations with roles.

Common columns

All tables include:

Column	Type	Description
id	uuid	Primary key (auto-generated)
created_at	timestamp	Row creation time
updated_at	timestamp	Last modification (auto-updated via trigger)

Most tables include an optional organization_id column for multi-tenancy scoping.

Key constraints

Double-booking prevention

EXCLUDE USING gist (
  provider_id WITH =,
  tstzrange(starts_at, ends_at) WITH &&
) WHERE (status NOT IN ('cancelled', 'rejected', 'rescheduled'))

This constraint prevents overlapping bookings for the same provider, except for cancelled, rejected, and rescheduled bookings. Requires the btree_gist extension.

Enums

Enum	Values
booking_status	pending, confirmed, completed, cancelled, rescheduled, no_show, rejected
payment_status	pending, authorized, succeeded, failed, refunded, partially_refunded, released
payment_type	prepayment, no_show_hold, cancellation_fee
assignment_strategy	round_robin, collective, managed, fixed
recurring_frequency	weekly, biweekly, monthly
seat_status	confirmed, cancelled
workflow_trigger	booking_created, booking_confirmed, booking_cancelled, booking_rescheduled, before_event, after_event, payment_received, payment_failed, no_show_confirmed, form_submitted
booking_source	online, walk_in, phone, admin
walk_in_status	queued, in_service, completed, no_show, cancelled

Example queries

Insert a booking:

import { db } from "@/db";
import { bookings } from "@thebookingkit/db";
import { v4 as uuid } from "uuid";

const newBooking = await db
  .insert(bookings)
  .values({
    id: uuid(),
    providerId: "provider-uuid",
    startsAt: new Date("2026-03-10T14:00:00.000Z"),
    endsAt: new Date("2026-03-10T14:30:00.000Z"),
    customerEmail: "customer@example.com",
    customerName: "John Doe",
    customerPhone: "+1-555-0123",
    status: "pending",
    createdAt: new Date(),
    updatedAt: new Date(),
  })
  .returning();

console.log("Booking created:", newBooking[0].id);

Query bookings with related data:

import { db } from "@/db";
import { bookings } from "@thebookingkit/db";
import { eq } from "drizzle-orm";

const booking = await db.query.bookings.findFirst({
  where: eq(bookings.id, "booking-uuid"),
  with: {
    provider: true,
    eventType: true,
    payments: true,
    bookingEvents: true,
  },
});

Find available providers with their schedules:

import { db } from "@/db";
import { providers, availabilityRules } from "@thebookingkit/db";
import { eq } from "drizzle-orm";

const provider = await db.query.providers.findFirst({
  where: eq(providers.userId, "user-123"),
  with: {
    availabilityRules: true,
    availabilityOverrides: true,
    bookings: {
      where: eq(bookings.status, "confirmed"),
    },
  },
});

Create an availability rule:

import { db } from "@/db";
import { availabilityRules } from "@thebookingkit/db";
import { v4 as uuid } from "uuid";

const rule = await db
  .insert(availabilityRules)
  .values({
    id: uuid(),
    providerId: "provider-uuid",
    rrule: "RRULE:FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR",
    startTime: "09:00",
    endTime: "17:00",
    timezone: "America/New_York",
    createdAt: new Date(),
    updatedAt: new Date(),
  })
  .returning();

Find bookings on a specific date:

import { db } from "@/db";
import { bookings } from "@thebookingkit/db";
import { eq, and, gte, lt } from "drizzle-orm";

const providerId = "provider-uuid";
const dayStart = new Date(Date.UTC(2026, 2, 10, 0, 0, 0));
const dayEnd = new Date(Date.UTC(2026, 2, 10, 23, 59, 59, 999));

const dayBookings = await db.query.bookings.findMany({
  where: and(
    eq(bookings.providerId, providerId),
    gte(bookings.startsAt, dayStart),
    lt(bookings.endsAt, dayEnd),
    eq(bookings.status, "confirmed")
  ),
});

console.log(`${dayBookings.length} confirmed bookings on March 10`);

Schema location

packages/db/src/schema/
├── tables.ts    # All 26 table definitions
└── enums.ts     # All enum definitions