Booking Confirmation Flow

Purpose

A stranger with no account picks a slot on an embeddable widget and gets a real, held appointment: the create path takes a row lock on any slot-holding booking for the exact (meeting_type_id, starts_at) before insert, and a partial UNIQUE index (uq_booking_slot_meeting_type_starts_at, calendar_integration_model.py 362 to 371) is the database backstop that lets exactly one INSERT win when the lock finds nothing to lock (the genuine empty-slot race). The invariant that makes it trustworthy is that "slot taken" is always a clean 409, never a 500 and never two calendar events for one time: the router-level lock catches the common case (_lock_slot_or_409, calendar_router.py 1452 to 1485) and the index catches the race the lock cannot see, with the router translating the resulting IntegrityError back into the same 409 (calendar_router.py 1571 to 1583).

When to use

Read it before touching create_public_booking, _lock_slot_or_409, CalendarIntegrationService.create_booking/cancel_booking, _dispatch_booking_messages, _send_ics_invites, run_booking_reminders/send_booking_reminder/cancel_unconfirmed_bookings, or confirm_booking_by_token; before changing what counts as a slot-holding status (SLOT_HOLDING_STATUSES); and when someone asks "why did this slot double-book", "why didn't the reminder SMS go out", "why was this booking auto-cancelled", or "what does the confirmation link actually do". Trigger phrases: "public booking", "slot lock", "double-book", "partial unique index", "meeting type", "iCal feed", "CalDAV", "reconfirmation", "SMS reminder", "confirmation token", "message steps", ".ics invite".

Not this recipe: the admin/workspace CRUD for meeting types and calendar connections (/admin/meeting-types, /workspace/meeting-types, /integrations/ical, /integrations/caldav, OAuth connect/callback, calendar_router.py 249 to 1345) - configuration, not the booking flow itself; get_available_slots/get_availability_slots (calendar_integration_service.py 517 to 1044) - the availability computation this flow depends on but does not own, a neighbouring recipe; the embed widget HTML/JS generator (get_embed_widget, calendar_router.py 1943 to roughly 2390) - the rendered page around this flow, not the flow; services/calendar/booking_lead_bridge.py's target, the CRM lead itself (this recipe cites the call site and its own tests but the lead model is out of scope); the shared messaging_service engine (resolve_steps, dispatch, drain_due, cancel_pending) - this recipe is one of its callers, not its owner; Google/iCal/CalDAV OAuth and feed-fetch mechanics (get_oauth_authorization_url, handle_oauth_callback, services/calendar/ical_provider.py, services/calendar/caldav_provider.py) - connection setup, read for context only.

Stack assumptions

At 62ba4fc: fastapi==0.118.2, synchronous handlers on sqlalchemy.orm.Session (SQLAlchemy==2.0.43), APScheduler==3.11.0 for the reminder/auto-cancel job, google-api-python-client==2.154.0/google-auth==2.41.1/google-auth-oauthlib==1.2.1 for the Google Calendar write path, icalendar==6.1.0 for .ics generation, cryptography==46.0.0 (Fernet) for OAuth-token and iCal/CalDAV-secret encryption at rest, Twilio (via services/sms_service.py) for the reconfirmation SMS, psycopg2-binary==2.9.10, alembic==1.16.5. Foundations assumed: S4 (fastapi-project-layout) for the router mount (mount_router(app, calendar_router.router, prefix="/api/calendar", ...), app_fastapi.py 845); S8 (alembic-migrations) for the slot-lock and message-step columns under Deploy & ops; S6/workspace-scoping for _has_workspace_access/_assert_calendar_connect_access gating the non-public routes (calendar_router.py 959 to 998, out of this recipe's public-flow scope but the pattern this recipe's public routes deliberately skip); S12 (scheduler-and-background-work) for the run_booking_reminders job.

Config/env this flow reads (core/config.py, not independently verified line-by-line beyond the usages cited): API_BASE_URL (builds the /api/calendar/confirm/{token} link, scheduled_jobs.py 1400); CALENDAR_ENCRYPTION_KEY (Fernet key for stored OAuth/feed secrets, calendar_integration_service.py 78 to 112, falls back to a dev-only local key file, raises in non-development without it); TWILIO_ACCOUNT_SID/TWILIO_AUTH_TOKEN/TWILIO_PHONE_NUMBER (sms_service.py 27 to 148, unconfigured Twilio answers {"status": "failed", ...} rather than raising). Fail behaviour: create_public_booking wraps the whole handler in try/except Exception and answers 500 "Failed to create booking. Please try again." for anything not already an HTTPException (calendar_router.py 1649 to 1653); the CAPI block, the lead-bridge call, _dispatch_booking_messages, _notify_host_of_booking and _send_ics_invites are each individually wrapped so a failure in any one of them never fails the booking response (calendar_router.py 1588 to 1633; calendar_integration_service.py 1295 to 1318).

Data model

Three tables carry this flow (models/calendar_integration_model.py); columns not touched by this recipe (OAuth token fields, iCal/CalDAV credentials, style/embed settings) are omitted here - see When to use for the recipe that owns them.

calendar_integrations (
  id                uuid PRIMARY KEY,
  workspace_id      uuid REFERENCES workspaces(id) ON DELETE CASCADE,   -- NULL = platform-level
  provider          varchar(50),                -- google | outlook | apple | ical_feed | caldav
  provider_account_id varchar(255),
  timezone          varchar(100) DEFAULT 'Europe/Zurich',
  is_active         boolean DEFAULT true,
  default_messages  jsonb                        -- tri-state fallback for every meeting type's `messages`
);

booking_meeting_types (
  id                       uuid PRIMARY KEY,
  calendar_integration_id  uuid NOT NULL REFERENCES calendar_integrations(id) ON DELETE CASCADE,
  slug                     varchar(100) NOT NULL,
  duration_minutes         integer NOT NULL DEFAULT 60,
  buffer_before_minutes    integer DEFAULT 0,
  buffer_after_minutes     integer DEFAULT 15,
  min_notice_hours         integer DEFAULT 24,
  availability_schedule    jsonb,
  location_type            varchar(50) DEFAULT 'google_meet',
  booking_questions        jsonb DEFAULT '[]',
  confirmation_message     text,
  send_confirmation_email  boolean DEFAULT true,
  send_reminder_email      boolean DEFAULT true,
  reminder_hours_before    integer DEFAULT 24,
  messages                 jsonb,                -- NULL = inherit calendar_integrations.default_messages, [] = explicitly none
  embed_settings           jsonb,
  is_active                boolean DEFAULT true,
  UNIQUE (calendar_integration_id, slug)          -- uq_meeting_type_integration_slug
);

calendar_bookings (
  id                       uuid PRIMARY KEY,
  meeting_type_id          uuid REFERENCES booking_meeting_types(id) ON DELETE SET NULL,   -- nullable: direct/onboarding bookings
  calendar_integration_id  uuid NOT NULL REFERENCES calendar_integrations(id) ON DELETE CASCADE,
  google_event_id          varchar(255),
  contact_id               uuid REFERENCES tracking_contacts(id) ON DELETE SET NULL,
  attendee_email           varchar(255) NOT NULL,
  attendee_name            varchar(255),
  attendee_phone           varchar(50),
  attendee_timezone        varchar(100) DEFAULT 'Europe/Zurich',
  starts_at                timestamptz NOT NULL,
  ends_at                  timestamptz NOT NULL,
  status                   varchar(50) DEFAULT 'confirmed',   -- pending | pending_payment | confirmed | cancelled | rescheduled | completed | no_show
  cancelled_at             timestamptz, cancellation_reason text, cancelled_by varchar(50),
  source                   varchar(100) DEFAULT 'direct',      -- direct | embed | onboarding | project | api
  sms_reminder_24h_sent_at timestamptz,
  sms_reminder_1h_sent_at  timestamptz,
  confirmation_token       varchar(64) UNIQUE,                 -- ix_calendar_bookings_confirmation_token
  reconfirmation_status    varchar(20) DEFAULT 'pending',       -- pending | confirmed | cancelled_no_confirm | no_response
  reconfirmed_at           timestamptz,
  sms_opt_in               boolean DEFAULT false
);
-- Partial UNIQUE index, the double-book backstop:
CREATE UNIQUE INDEX uq_booking_slot_meeting_type_starts_at
  ON calendar_bookings (meeting_type_id, starts_at)
  WHERE meeting_type_id IS NOT NULL AND status IN ('confirmed', 'pending_payment');

SLOT_HOLDING_STATUSES = (CONFIRMED, PENDING_PAYMENT) (calendar_integration_model.py 63 to 66) is the single source of truth both the availability query and the slot-lock use for "this slot is taken" - cancelled/rescheduled/completed/no_show never hold a slot. What is NOT stored: no card or payment detail (Payrexx/pending_payment handling itself is out of this recipe's scope); no raw PSP or Google API response body, only google_event_id/google_event_link; the reminder/confirmation email body is not persisted on the booking row, it is rendered from the meeting type's/calendar's messages steps at dispatch time (messaging_service.dispatch).

API surface

Route	Auth	Tenancy	Codes	Notes
POST /api/calendar/public/book/{slug} (create_public_booking, calendar_router.py 1487 to 1653)	none (public)	integration_id query param selects the workspace's calendar, or falls back to the platform-level integration	200 BookingResponse; 400 invalid attendee_timezone; 404 meeting type/integration not found; 409 slot taken; 429 anti-abuse (form_protection.validate_submission); 500 unhandled	Body is BookingRequest (calendar_router.py 161 to 197): starts_at, attendee_email, attendee_name, optional attendee_phone/sms_opt_in/answers/notes/tracking fields
GET /api/calendar/public/booking/{booking_id}?email= (get_public_booking, 1656 to 1682)	none	attendee_email query param is the only lookup key (email-as-secret)	200 BookingResponse; 404 no match
POST /api/calendar/public/cancel/{booking_id}?email= (cancel_public_booking, 1685 to 1711)	none	same email check	200 {"success": true, ...}; 400 already cancelled; 404 no match	Calls calendar_service.cancel_booking(..., cancelled_by="attendee")
GET /api/calendar/confirm/{token} (confirm_booking_by_token, 1714 to 1781)	none	token is CalendarBooking.confirmation_token, a 64-char unguessable value from secrets.token_urlsafe(32)	always 200, an HTML page (_confirmation_page); no JSON error path	States: token not found ("Link ungültig"), already confirmed, cancelled, in the past, or freshly confirmed (flips reconfirmation_status='confirmed', stamps reconfirmed_at)
GET /api/calendar/public/meeting-type/{slug} / GET /api/calendar/public/availability/{slug}	none	same integration_id fallback pattern	200 / 400 / 403 (embed disallowed) / 404	Read-only prerequisites this flow depends on, not detailed here (see When to use)

_lock_slot_or_409 (calendar_router.py 1452 to 1485) is not a route; it is the function the create path calls before create_booking, documented under Application logic.

Application logic

create_public_booking, in commit order:

1. Extracts client IP (X-Forwarded-For first hop, else request.client.host) and runs form_protection.validate_submission when a protection token or PoW challenge is present, 429 on failure (calendar_router.py 1502 to 1520).
2. Validates attendee_timezone with validate_timezone, 400 on failure (1523 to 1524).
3. Resolves the meeting type via integration_id or the platform-level integration; 404 if either is missing (1526 to 1535).
4. Derives source from referrer_url ("embed" substring), onboarding_session_id, or project_id, defaulting to direct (1537 to 1545).
5. Lock: _lock_slot_or_409 runs SELECT ... FOR UPDATE on any existing CalendarBooking for (meeting_type_id, starts_at) whose status is in SLOT_HOLDING_STATUSES; if one is found, 409 immediately (calendar_router.py 1452 to 1485). Scoped to meeting_type.id, which is itself workspace-isolated through its CalendarIntegration; direct/onboarding bookings (no meeting type) are never slot-locked.
6. Insert: calendar_service.create_booking(...) (see below). If the empty-slot race let a second racer past the lock check, the partial UNIQUE index raises IntegrityError on commit; the router catches it, rolls back, logs, and answers the same 409 "This time slot is no longer available" (1571 to 1583).
7. Conversion (best-effort, never fails the booking): fire_booking_conversion (services/calendar/booking_conversion.py) sends a server-side Meta CAPI event - Schedule by default, or a per-slug mapping via get_calendar_conversion_mapping - and always returns an event_id even when the send is skipped (test mode, consent denied, mapping disabled), so the widget always has something to dedupe a browser-side pixel against (1585 to 1617).
8. CRM bridge (best-effort): create_or_update_booking_lead (services/calendar/booking_lead_bridge.py) creates or updates a workspace-scoped Lead keyed on (workspace_id, email), stitches the visitor's tracking session via the shared link_lead_to_tracking_contact primitive, stamps booking.contact_id, and logs a meeting_scheduled ClientActivityLog row; it commits its own transaction and never raises out to the router (calendar_router.py 1619 to 1633; booking_lead_bridge.py 44 to 163). Skipped entirely for a platform-level (tenant-less) integration.
9. Returns BookingResponse including the CAPI conversion payload when one was sent.

CalendarIntegrationService.create_booking (calendar_integration_service.py 1050 to 1320), the function step 6 calls:

1. Resolves integration/duration/ends_at/location_type from meeting_type, or from integration_id+ends_at for a direct booking (1081 to 1102).
2. Builds the event description from meeting_type.description + configured booking_questions (matched by id) + any unmatched answer keys, so a custom front-end's own question keys are never silently dropped (_build_event_description, 1663 to 1712).
3. Provider branch: Google gets a real Calendar API events().insert(..., sendUpdates='all') with a Google Meet conferenceData request when location_type == google_meet (1176 to 1199); CalDAV gets a remote write via services/calendar/caldav_provider.create_event (1200 to 1219); an iCal-feed integration performs no remote write at all - the booking exists only in this database, and step 5 below ships the .ics invite instead (1221 to 1227).
4. Inserts the CalendarBooking row with status=CONFIRMED (1254 to 1282) - this call itself does not construct a pending_payment row; that status exists for a separate paid-appointment path this recipe does not own (see the slot-lock migration's own docstring under Deploy & ops).
5. Message fan-out (best-effort): _dispatch_booking_messages resolves the step list via _resolve_message_steps (meeting type's own messages, else the calendar's default_messages, via the shared messaging_service.resolve_steps tri-state: None inherits, [] means explicitly none, [...] is used as-is), drops any before_event-anchored step when meeting_type.send_reminder_email is False, and calls messaging_service.dispatch(..., surface_kind="booking", anchor_at=booking.starts_at, ref_id=booking.id, variables={name, meeting, date, time, location, meeting_link}) (1286 to 1389). dispatch fires any on_event step immediately and enqueues before_event/after_event steps into scheduled_messages, drained every 5 minutes by run_message_drain (scheduled_jobs.py 593 to 603, 211 to 214).
6. Host notification (best-effort): _notify_host_of_booking emails the workspace owner (preferred) or the connected provider account (fallback) a plain summary table of the booking, including any pre-check answers keyed by their configured labels (1391 to 1481). Exists because Google's own sendUpdates='all' notifies the attendee, never the calendar owner about an event on their own calendar.
7. .ics invites (best-effort, iCal-feed and CalDAV only): _send_ics_invites builds a METHOD:REQUEST .ics via build_booking_ics and emails it to the attendee always, and additionally to the calendar owner when the integration is iCal-feed (which wrote no remote event at all) (1310 to 1318, 1483 to 1564).

Cancellation, cancel_booking (1566 to 1620): deletes the remote event when one exists (Google events().delete(sendUpdates='all'), or CalDAV cancel_event); flips status=CANCELLED, stamps cancelled_at/cancelled_by/cancellation_reason; calls messaging_service.cancel_pending(surface_kind="booking", ref_id=booking.id) to drop any not-yet-sent queued reminder/follow-up (so a cancelled attendee never gets "see you tomorrow"); for iCal-feed/CalDAV integrations, emails a METHOD:CANCEL .ics reusing the original event UID so the attendee's own calendar client resolves it as a cancellation of the same event, not a new one (_send_cancel_ics, 1622 to 1661).

SMS reconfirmation-by-token, run_booking_reminders (scheduled_jobs.py 1304 to 1485, IntervalTrigger(minutes=15), scheduled_jobs.py 202 to 208):

1. Selects CONFIRMED bookings with sms_opt_in=True, a non-empty attendee_phone, starting 23 to 25 hours out and sms_reminder_24h_sent_at IS NULL; and separately, starting 50 to 70 minutes out with sms_reminder_1h_sent_at IS NULL (1324 to 1359).
2. send_booking_reminder: for the 24h reminder only, mints confirmation_token = secrets.token_urlsafe(32) if the booking does not already have one and flushes it before sending, then builds {API_BASE_URL}/api/calendar/confirm/{token} and sends via sms_service.send_booking_reminder (1376 to 1413). On status in ('sent', 'mock') it stamps the matching sms_reminder_*_sent_at column and commits (1416 to 1421); a send is only ever selected while its own _sent_at column is still NULL, so a resend can never fire twice for the same window.
3. cancel_unconfirmed_bookings: separately selects CONFIRMED bookings starting within 3 hours whose 24h reminder was sent (sms_reminder_24h_sent_at IS NOT NULL) but whose reconfirmation_status != 'confirmed', calls cancel_booking(cancelled_by="system", reason="Termin wurde nicht bestätigt (automatische Absage)"), sets reconfirmation_status = 'cancelled_no_confirm', and sends a plain cancellation SMS (1430 to 1485).
4. confirm_booking_by_token (see API surface) is the only writer of reconfirmation_status='confirmed'; it 404-equivalent-HTML's an unknown token, and refuses (with an HTML message, not an error status) an already-confirmed, cancelled, or past booking before flipping status.

Invariants

• At most one slot-holding (CONFIRMED or PENDING_PAYMENT) booking exists per (meeting_type_id, starts_at), enforced by the database itself, not application logic alone (ck/index uq_booking_slot_meeting_type_starts_at; test_second_confirmed_same_slot_rejected, test_pending_payment_conflicts_with_confirmed_slot).
• Two genuinely concurrent bookers for the same empty slot: exactly one wins, the other gets a clean rejection, and the database ends with exactly one surviving slot-holding row (test_two_racing_bookings_one_wins, real threads + a threading.Barrier against a committed Postgres session, not the shared rolled-back db_session fixture).
• A cancelled (or rescheduled/completed/no_show) booking never blocks a rebooking of the same slot (test_cancelled_slot_can_be_rebooked), because those statuses are excluded from the partial index's WHERE clause.
• A reminder SMS is sent at most once per window: selection and the _sent_at IS NULL write are the same guard, so neither a second scheduler tick nor a second worker can re-select an already-sent booking (module-level comment, scheduled_jobs.py 1316 to 1321; no dedicated concurrency test for this path was found - see Tests).
• Cancelling a booking always drops its not-yet-sent queued messages, messaging_service.cancel_pending is called unconditionally inside cancel_booking, not left to the caller (calendar_integration_service.py 1602 to 1610).
• messages: None on a meeting type always falls back to the calendar's default_messages; messages: [] never does. This is messaging_service.resolve_steps's contract, and _resolve_message_steps calls it rather than re-implementing the tri-state (calendar_integration_service.py 1323 to 1335).

Frontend

The module that would render this, by id:

calendar-booking/ui/calendar-settings-panel   the workspace-facing configuration surface (meeting
                                               types, connected calendar, default messages) whose
                                               settings this flow reads at booking time; at 62ba4fc
                                               nothing in this recipe's own flow has a dedicated
                                               admin UI beyond that settings panel - the booking
                                               widget itself (embed HTML) is a neighbouring
                                               recipe (see When to use)

Minimum admin/debug surface (no dedicated route exists for these at this commit, they are queries a human runs by hand): SELECT id, status, reconfirmation_status, sms_reminder_24h_sent_at, sms_reminder_1h_sent_at, confirmation_token FROM calendar_bookings WHERE starts_at > now() AND status = 'confirmed' ORDER BY starts_at (the live reminder queue); SELECT count(*) FROM calendar_bookings WHERE reconfirmation_status = 'cancelled_no_confirm' AND created_at > now() - interval '7 days' (auto-cancel rate, a spike here means the SMS or the confirm link itself is broken); the log lines SCHEDULER: Processed %s 24h reminders, %s 1h reminders, %s auto-cancelled (scheduled_jobs.py 1367) and SCHEDULER: Auto-cancelled unconfirmed booking %s (1480).

Security & tenancy

Auth dependency: none on any route in this recipe - create_public_booking, get_public_booking, cancel_public_booking, and confirm_booking_by_token all take only db (and the public Query params), by design, this is the embeddable-widget surface. Tenancy is carried by integration_id (an opaque UUID query param, not validated against any session) rather than a workspace-scoped session; a platform-level booking (no integration_id) falls back to the single platform integration. get_public_booking/cancel_public_booking use attendee_email as the entire access credential - the query filters WHERE id = :id AND attendee_email = :email, so knowing the booking id alone is not enough, but the email is transmitted and compared in plaintext over the query string, not a signed token. confirm_booking_by_token uses confirmation_token, a secrets.token_urlsafe(32) value (scheduled_jobs.py 1396) with a database-level UNIQUE constraint - a lookup miss and an already-consumed token both render the same class of HTML page rather than distinguishing "wrong" from "used".

Fail direction: the slot-lock is closed on the database (a raised IntegrityError becomes a 409, never a silently-accepted second booking); every side effect that is NOT the booking-and-slot itself (CAPI, CRM lead, message dispatch, host notification, .ics email) is wrapped so its failure can only be logged, never surfaces as a failed booking or a 500 - meaning a production incident in any one of those systems is invisible to the attendee and to the booking's own success/failure signal, visible only in logs (Gotchas).

Secrets and PII at rest: OAuth tokens and iCal/CalDAV feed secrets are Fernet-encrypted with a key from CALENDAR_ENCRYPTION_KEY (or a dev-only local file, calendar_integration_service.py 78 to 112); attendee_email/attendee_name/attendee_phone/free-text answers/notes are stored in plain columns with no field-level encryption - this recipe does not add any beyond what the base table already does. Rate limits: form_protection.validate_submission (honeypot + optional proof-of-work + IP) gates create_public_booking only when the client supplies a protection token/challenge; there is no server-mandated minimum, so a caller that omits those fields bypasses the check entirely (calendar_router.py 1509 to 1520, not independently verified beyond this call site - form_protection's own enforcement is out of this recipe's scope). Attacker-controlled inputs: starts_at (must still pass the slot-lock/index, so a malicious or clock-skewed value cannot create a second booking for a taken slot, but nothing in this flow revalidates it against the meeting type's own availability window at insert time - that check lives in get_available_slots, read at quote time, not enforced again at booking time); attendee_email/attendee_phone/answers/notes (rendered into the Google Calendar event description and the host-notification email as HTML via simple _html.escape, calendar_integration_service.py 1465 to 1470); integration_id (any UUID is accepted and looked up directly, with no ownership check tying it to the request's origin).

Tests

Command, run 2026-09-12 against the alchemy_labs .venv at 62ba4fc, exact output not reproduced here (not executed as part of authoring this recipe - the process instructed reading, not running, these tests; treat the following as sourced from the test files' own structure, not a captured run):

pytest backend/tests/v2/coverage/test_booking_g1_public_booking_flow_availability_book_cancel_confirm_.py \
       backend/tests/v2/coverage/test_booking_g5_slot_lock_double_book.py \
       backend/tests/test_booking_lead_bridge.py -q

test_booking_g1_public_booking_flow_availability_book_cancel_confirm_.py (738 lines): 30 def test_ functions against the unauthenticated client fixture, covering get_public_meeting_type (5 tests, including embed-disabled 403 and inactive-invisible), get_public_availability (5), create_public_booking (7, including test_create_public_booking_persists_booking, test_create_public_booking_capi_failure_swallowed - proves a CAPI exception never fails the booking response), get_public_booking (4, email-match/mismatch), cancel_public_booking (3), confirm_booking_by_token (5: invalid token, test_confirm_success_flips_status_and_stamps_time, already-confirmed, cancelled, past-booking), plus 3 direct service-level tests (test_service_create_booking_computes_ends_at_and_status, test_service_get_meeting_type_by_slug_is_integration_scoped, test_service_get_meeting_type_by_slug_excludes_inactive, test_service_get_available_slots_shape_and_timezone). Uses an iCal-feed integration and an autouse fixture that monkeypatches _send_ics_invites/_send_cancel_ics to no-ops, so the suite is fully hermetic (no outbound network).

test_booking_g5_slot_lock_double_book.py (297 lines): 5 tests, hermetic against a real Postgres (not the shared rolled-back db_session fixture - this file opens its own create_engine/sessionmaker because two racing connections cannot be modeled inside one rolled-back transaction, per its own module docstring). test_pending_payment_status_exists_and_holds_slot (enum/set assertions, no DB); test_second_confirmed_same_slot_rejected and test_pending_payment_conflicts_with_confirmed_slot (both expect IntegrityError on the second commit); test_cancelled_slot_can_be_rebooked (second commit must NOT raise); test_two_racing_bookings_one_wins (two real threading.Threads synchronized on a threading.Barrier, mirrors the router's own lock-then-insert sequence, asserts exactly one "won" and one "conflict_*" outcome, then independently re-queries the database and asserts exactly 1 surviving slot-holding row).

test_booking_lead_bridge.py (257 lines): 7 tests against create_or_update_booking_lead directly - test_new_email_creates_lead_in_integration_workspace, test_existing_lead_email_no_duplicate_and_no_source_flip (an existing non-booking-sourced lead keeps its original source), test_workspace_isolation_same_email_two_workspaces, test_platform_level_integration_skips (a None workspace_id returns None, writes nothing), test_session_stitch_and_aggregate_recompute, test_fingerprint_stitch_is_workspace_scoped, test_no_tracking_payload_still_creates_lead.

What this recipe genuinely does not know: whether these three files pass at 62ba4fc (not run during authoring); whether a dedicated test exists for the SMS-reconfirmation concurrency guarantee described under Invariants (scheduled_jobs.py's run_booking_reminders/send_booking_reminder/cancel_unconfirmed_bookings have no test file among the three named in this recipe's source list, and a search for one was not performed beyond those three files - do not assume its absence, only that this recipe did not locate one); the exact pytest total/pass count and any skip conditions (unlike the billing-credits recipe's bar, no PRIMA_CREDITS_TEST_DATABASE_URL-style env-gate marker was found on these three files, but this was not exhaustively verified against conftest.py).

Deploy & ops

Config keys: API_BASE_URL, CALENDAR_ENCRYPTION_KEY, TWILIO_ACCOUNT_SID/TWILIO_AUTH_TOKEN/TWILIO_PHONE_NUMBER (all above). Migrations touching this flow's schema, in the order they were introduced (migrations/versions/, revision IDs and down_revision read directly from each file): baseline_prod_schema_001 (revision baseline_prod_schema_001, down_revision: None) already contains calendar_bookings.confirmation_token/sms_reminder_24h_sent_at/sms_reminder_1h_sent_at/reconfirmation_status and the calendar_bookings_confirmation_token_key UNIQUE constraint plus ix_calendar_bookings_confirmation_token - meaning the SMS-reconfirmation columns predate this repo's incremental Alembic history, they were already live in production at the baseline snapshot; 20260427b_add_ical_caldav.py (ical_caldav_001, revises content_review_001) adds the iCal/CalDAV integration columns this flow's provider branch depends on; 20260610_0003_contact_spine_001.py (contact_spine_001, revises encrypt_ad_oauth_tokens_001) adds calendar_bookings.contact_id; 20260610_0004_booking_slot_lock.py (booking_slot_lock_001, revises commerce_subscriptions_001) adds the partial UNIQUE index this recipe's core invariant depends on, additive and reversible per its own docstring; 20260901_0001_booking_message_steps.py (booking_msg_steps_001, revises sparks_team_001) adds booking_meeting_types.messages and calendar_integrations.default_messages, both nullable with no server default, deliberately, so the tri-state (inherit / explicit-none / explicit-list) cannot collapse; 20260907_0001_leadsource_add_booking.py (leadsource_booking_001, revises desk_email_ingest_001) adds 'booking' to the Postgres leadsource enum the CRM bridge writes.

Scheduler: run_booking_reminders on IntervalTrigger(minutes=15) (scheduled_jobs.py 202 to 208, job id booking_reminders); the message-step fan-out this flow triggers at booking time is drained separately by run_message_drain on IntervalTrigger(minutes=5) (211 to 214, job id message_drain) via messaging_service.drain_due. Both run inside the same BackgroundScheduler a singleton-lock promotes one worker to own (_start_full_scheduler, 191 to 199) - not independently re-verified beyond this recipe's own reading of _start_full_scheduler's surrounding singleton-lock comments.

Rollback: the slot-lock index (booking_slot_lock_001) has an explicit, reversible downgrade() per its own docstring; leadsource_booking_001's downgrade() is a documented no-op (Postgres cannot drop an enum value without recreating the type) - rolling back the code after that migration has run leaves the 'booking' enum value in place harmlessly. No Argus-style check for this flow was found in the reference implementation; the closest measured proxy is the SELECT under Frontend.

Gotchas

• 2026-09-12 (measured at 62ba4fc): every side effect after the booking is committed - Meta CAPI, the CRM lead bridge, message dispatch, host notification, .ics email - is individually try/excepted and only logged on failure (calendar_router.py 1588 to 1633; calendar_integration_service.py 1295 to 1318). This is the same trade-off the auto-topup recipe's outer catch-all makes for money: a production outage in any of these five systems produces a normal 200 booking response and a log line, nothing else. There is no dead-letter queue or retry for a failed _dispatch_booking_messages or _notify_host_of_booking call at this commit.
• 2026-09-12 (measured at 62ba4fc, calendar_integration_service.py 1221 to 1227): an iCal-feed integration performs no remote calendar write at all when a booking is created - the booking exists only in this app's database until the .ics email is opened and manually added. A rebuild that assumes every confirmed booking has a corresponding external calendar event will be wrong for this provider.
• 2026-09-12 (measured at 62ba4fc): create_booking's direct-booking mode (meeting_type=None, integration_id+ends_at supplied instead) is not slot-locked by _lock_slot_or_409 at all - that guard only runs in create_public_booking's meeting-type path. Any other caller of create_booking in direct mode gets no double-book protection beyond the partial index's own meeting_type_id IS NOT NULL condition, which does not even apply to a row with no meeting type.
• 2026-09-12 (measured at 62ba4fc): confirm_booking_by_token's "invalid or expired" HTML page ("Link ungültig") is returned identically whether the token was never valid or was already consumed on an unrelated booking (calendar_router.py 1730 to 1735) - an attacker or a support agent cannot distinguish "wrong token" from "this token belongs to a different, already-resolved booking" from the response alone.
• 2026-09-12 (measured at 62ba4fc, calendar_router.py 1509 to 1520): the anti-abuse check on create_public_booking only runs when the request supplies _protection_token or _protection_pow_challenge; a client that omits both fields entirely skips the check, it is not a server-mandated minimum.

Reference implementation

Paths relative to the repo root; all at main 62ba4fc41009b454e1c033e5036c7e8577db2061 unless noted.

• alchemy_labs/backend/api/routers/calendar_router.py (2486 lines): BookingRequest/BookingResponse/CancelBookingRequest (161 to 224); _lock_slot_or_409 (1452 to 1485); create_public_booking (1487 to 1653); get_public_booking (1656 to 1682); cancel_public_booking (1685 to 1711); confirm_booking_by_token (1714 to 1781); _confirmation_page (1784 to 1867).
• alchemy_labs/backend/services/calendar_integration_service.py (1717 lines): get_platform_integration/get_primary_workspace_integration (413 to 457); get_meeting_type_by_slug (500 to 511); create_booking (1050 to 1320); _resolve_message_steps (1323 to 1335); _dispatch_booking_messages (1337 to 1389); _notify_host_of_booking (1391 to 1481); _send_ics_invites (1483 to 1564); cancel_booking (1566 to 1620); _send_cancel_ics (1622 to 1661); _build_event_description (1663 to 1712).
• alchemy_labs/backend/services/calendar/booking_conversion.py (146 lines, whole file in scope): fire_booking_conversion (38 to 145).
• alchemy_labs/backend/services/calendar/booking_lead_bridge.py (164 lines, whole file in scope): create_or_update_booking_lead (44 to 163).
• alchemy_labs/backend/services/calendar/ics_builder.py (98 lines, whole file in scope): build_booking_ics (19 to 86).
• alchemy_labs/backend/models/calendar_integration_model.py (440 lines): BookingStatus/SLOT_HOLDING_STATUSES (43 to 66); CalendarIntegration (82 to 154); BookingMeetingType (161 to 253); CalendarBooking including the partial unique index (260 to 372).
• alchemy_labs/backend/services/messaging_service.py: resolve_steps (184 to 202); dispatch (460 to 522); cancel_pending (596 onward, whole-function range not fully re-verified beyond the call site).
• alchemy_labs/backend/services/scheduled_jobs.py: job registration (191 to 214); run_booking_reminders (1304 to 1373); send_booking_reminder (1376 to 1427); cancel_unconfirmed_bookings (1430 to 1485).
• alchemy_labs/backend/app_fastapi.py: router mount (845).
• alchemy_labs/backend/migrations/versions/: baseline_prod_schema_001.py, 20260427b_add_ical_caldav.py, 20260610_0003_contact_spine_001.py, 20260610_0004_booking_slot_lock.py, 20260901_0001_booking_message_steps.py, 20260907_0001_leadsource_add_booking.py.
• Tests as listed under Tests: alchemy_labs/backend/tests/v2/coverage/test_booking_g1_public_booking_flow_availability_book_cancel_confirm_.py (738 lines), test_booking_g5_slot_lock_double_book.py (297 lines), alchemy_labs/backend/tests/test_booking_lead_bridge.py (257 lines).

Deviations of the reference from an ideal implementation, all dated 2026-09-12: direct/onboarding bookings bypass the application-level slot lock entirely (Gotchas); every post-commit side effect is best-effort with no retry or dead-letter path; the confirmation-token flow cannot distinguish an invalid token from an already-resolved one in its response; the anti-abuse check is opt-in per request rather than server-enforced.

Acceptance checks

These are read from the source and test files, not captured from a live run (the authoring process specified reading the tests, not executing them; a verifier should run the below and record real output).

1. grep -n "uq_booking_slot_meeting_type_starts_at" backend/models/calendar_integration_model.py shows the Index(...) definition at line 362, with postgresql_where containing meeting_type_id IS NOT NULL AND status IN ('confirmed', 'pending_payment').
2. grep -c "^def test_" backend/tests/v2/coverage/test_booking_g1_public_booking_flow_availability_book_cancel_confirm_.py prints 30; the same on test_booking_g5_slot_lock_double_book.py prints 5; on backend/tests/test_booking_lead_bridge.py prints 7.
3. pytest backend/tests/v2/coverage/test_booking_g5_slot_lock_double_book.py -q against a real (not sqlite/mocked) Postgres test database is expected to pass all 5, with test_two_racing_bookings_one_wins in particular printing no IntegrityError escaping the thread (it is caught and recorded, not re-raised) and the final DB re-query returning count == 1. Not run as part of authoring this recipe; a verifier should confirm the actual output.
4. grep -n "confirmation_token character varying(64)" backend/migrations/versions/baseline_prod_schema_001.py matches line 719, confirming the SMS-reconfirmation columns are baseline, not a later addition.
5. grep -n "IntervalTrigger(minutes=15)" backend/services/scheduled_jobs.py | head -1 matches line 204, the run_booking_reminders registration.