Booking persistence schema

This reference covers the booking core migration and its forward extensions. It describes database state and constraints. The API's booking feature slices read and write these tables; their behavior is described in the API feature pages, such as Booking schedules and Professional appointments. No notification delivery worker is implemented.

Migration	Schema contribution
20261016120000_booking_core.sql	Schedules, services, availability, appointments, and hashed management links.
20261016171000_booking_feature_scaffold.sql	Group sessions, recurrence, blocks, client accounts and claims, notification state, entitlements, and onboarding state.
20261016173000_notification_event_preferences.sql	Independent event preference fields for professional and client recipients.
20261016180000_schedule_fixed_slot_duration.sql	The professional's fixed-slot duration on each schedule.

Schedules and capacity

schedules belongs to users.id. A public code is unique without regard to case, including after its schedule is archived. Archiving retains the schedule and its child history; an archived row must have browser, Telegram, and repeat channels disabled. The step settings added by the scaffold are:

Column	Default	Allowed values
client_step_minutes	30	15, 30, 60
slot_step_minutes	15	15, 30, 60
fixed_slot_duration_minutes	30	15 through 720

services.max_bookings defaults to 1 and accepts 1 through 99. booking_sessions identifies one (schedule_id, service_id, starts_at) and records the service's current limit in capacity_snapshot when inserted. A trigger requires that initial match and prevents later changes to the session's owner, schedule, service, start, or capacity. Changing max_bookings affects new sessions only.

Public (browser or telegram) appointments must reference a session with the same schedule, service, start, and capacity snapshot, and carry a seat_number from 1 through that capacity. A partial unique index reserves each seat for at most one confirmed appointment. A cancelled appointment keeps its seat history while the number becomes available again. Existing public appointments were backfilled into sessions at seat 1.

Manual appointments have no session or seat. A separate partial unique index allows at most one confirmed manual appointment for an owner at the same start instant. The database does not resolve overlapping intervals or conflicts between manual and public bookings; future booking transactions must check those under an owner lock.

Recurrence and phone blocks

recurring_appointment_rules stores a schedule and owner, local first date, start minute (0–1439), duration (15–480 minutes), nonempty time-zone string, and cadence (every_weeks from 1 to 4). An optional stop_before_date must follow the first date. A rule may reference a service and then requires its service snapshots. The database does not validate that the time-zone string names an IANA zone.

recurring_appointment_exceptions stores one skipped local date per rule. Generated appointments, when present, are manual appointments linked to a same-owner, same-schedule rule and an occurrence_local_date; only one appointment row can represent a rule and local date. A conflict or daylight saving anomaly can be recorded in recurring_generation_issues with reason conflict, dst_gap, or dst_overlap and an optional resolution time. The migration does not generate occurrences or resolve time-zone transitions.

blocked_client_phones stores an E.164-formatted number per professional owner. Its partial unique index permits only one active row for an owner and number; setting removed_at retains old block history and permits a later block. These rows are separate from appointment contacts, so erasing an appointment's contact fields does not erase a block. Booking use cases must check active blocks before accepting a client.

Telegram accounts and appointment claims

client_accounts gives a positive, unique Telegram user ID a separate client UUID. It stores an E.164-formatted verified phone, verification time, display name, and bot-write grant and revoke times. The database checks field shape and timestamp order; a trusted application flow must actually verify the contact and manage bot permission.

appointment_claims has one row per appointment, linking a client account and a management link that belongs to that same appointment. Its primary key rejects a second claim row. The table does not verify a presented link token, make an INSERT retry idempotent, or prevent a trusted writer from updating an existing claim; those are application transaction rules. Anonymous browser appointments can remain unclaimed.

Notifications

Preference rows are optional and keyed by professional owner_user_id or client_account_id. All stored switches default to true:

Recipient	Event switches	Channel switches
Professional	new_booking_enabled, cancellation_enabled	inbox_enabled, telegram_enabled
Client	reminder_enabled, cancellation_enabled	inbox_enabled, telegram_enabled

notification_inbox requires exactly one recipient. Its event_key is unique per recipient, and read_at and dismissed_at retain state without deleting the row or its optional appointment reference. event_kind is nonempty text; the database does not restrict it to a fixed vocabulary.

notification_deliveries stores one Telegram delivery per inbox item with a due time, attempts, optional last-attempt and delivered times, and state pending, processing, sent, failed, or cancelled. (inbox_item_id, channel) is unique across all states. A due-time index covers pending and failed rows. The schema records delivery work but does not enqueue or send it. Bot-write permission and recipient preferences must be checked by the future delivery use case.

Entitlements and onboarding

professional_entitlements has one row per owner after that owner's first schedule is inserted. The trigger fixes a trial window of 720 hours (30 days) from the first schedule's created_at; later schedules cannot restart it. Existing schedule owners were backfilled from their earliest schedule. renewal_intent is a separate boolean and does not grant access.

professional_entitlement_grants records keyed, time-bounded grants. A grant can provide access or 1–19 extra schedules. The professional_entitlement_eligibility view computes current trial and grant status, active schedule count, public-booking eligibility, and an allowed active schedule count of 0 without access or up to 20 with access. The view itself enforces nothing. Schedule creation reads it under an owner lock; see Booking schedules. There are no prices, payment records, or payment-provider callbacks in this schema.

professional_onboarding_progress stores an owner's defer, skip, and complete timestamps per positive flow_version. The professional_onboarding_milestones view derives has_service, has_availability, and has_booking from existing records. It counts unarchived services and availability on unarchived schedules, and any appointment history for the owner. The schema does not copy those milestones into progress flags or drive an onboarding UI.

Access and verification boundary

The API uses pg with DATABASE_URL; the Supabase Data API stays disabled. These tables do not expose client-side access or add booking RLS policies. Application use cases must authorize writes, validate client claims, enforce cross-session booking conflicts, and perform notification delivery. The database integration suite exercises defaults, checks, foreign keys, unique indexes, and eligibility views; it is gated by DATABASE_INTEGRATION=1 and requires the ordered migrations to be applied.

See Database connection for the runtime boundary and Manage database migrations for the migration workflow.