Booking reservation flow

Everything a customer buys passes through a hold first. The hold is what stops two people from buying the same seat, and almost every rule below exists because that guarantee is hard to keep across a pooled database and an external payment processor.

Primary code: packages/core/src/server/trpc/routers/reservations.ts, packages/core/src/server/db/reservation-families.ts, packages/core/src/http/create-payment-intent.ts. Design record: docs/booking-payments/architecture.md.

End to end

sequenceDiagram
    autonumber
    actor U as Customer
    participant App as Cart UI
    participant H as reservations.createHold
    participant DB as Postgres
    participant PI as create-payment-intent
    participant S as Stripe
    participant WH as Stripe webhook
    participant Cron as cleanup-expired

    U->>App: add item
    App->>H: createHold(cartId, item)
    H->>DB: advisory lock + capacity re-read + insert (one tx)
    DB-->>H: reservation status='held', expiresAt
    H-->>App: hold, refreshed cart window

    U->>App: checkout
    App->>PI: POST { cartId }
    PI->>DB: recompute total from HELD rows only
    PI->>S: create (or reuse) PaymentIntent
    PI->>DB: claim cart (stripe_payment_intent_id set = cart locked)
    S-->>U: pay

    S->>WH: payment_intent.succeeded
    WH->>DB: booking + payment row, reservations -> 'confirmed'
    Note over WH,DB: unique stripe_payment_intent_id absorbs redelivery

    Cron->>DB: expire stale holds, send abandonment email

The six reservation families

One column contract — { id, userId, cartId, status, expiresAt, updatedAt } — shared by every family, so extend, release, cleanup and count are written once in reservation-families.ts rather than per family:

Family	Table	Window
Signing slot	slot_reservations	15 min
Entry ticket	entry_ticket_reservations	15 min
Comic cover	comic_cover_reservations	15 min
Event bundle	event_bundle_reservations	15 min
Grading add-on	grading_addon_reservations	15 min
Vendor booth	booth reservations	60 min (45 during payment)

Plus the artist-hour ledger (artist_hour_reservations), which is not a cart family of its own: it is a child of a signing hold that consumes shared per-artist/hour capacity.

Two predicates decide everything:

• activeHoldPredicate — held and not expired. Used when pricing or extending a cart. A confirmed row is already paid for and is not a hold.
• activeReservationPredicate — held (unexpired) or confirmed. This is the one behind every availability count in the app: slot silos, the entry-ticket silo, the bundle silo and the artist-hour ledger all answer “how many seats are gone” with exactly this rule. Retyping it per call site is what let the à-la-carte availability read drift from what createHold enforces (decision D-026).

createHold, step by step

1. Refuse a locked cart. If the cart already carries a stripe_payment_intent_id, a payment is in flight and items cannot be added. Before rejecting, autoHealStalePaymentLock runs — see Auto-healing a stale lock.

2. Upsert the cart with a fresh 15-minute expiry and clear abandonmentEmailSentAt, so a reused cart can receive a new abandonment email.

3. Refresh every existing hold in the cart to that same expiry. Without this, the first item expires while the last one is still live and the cart appears active past its own window.

4. Take the locks, in a fixed order. Inside one transaction: user+package lock first (only when the package has a per-user limit), then the slot lock. That order is what prevents deadlocks between two customers buying two slots of the same package.

   SELECT pg_advisory_xact_lock(hashtext('<userId>:<packageId>'));  -- optional
   SELECT pg_advisory_xact_lock(hashtext('<timeSlotId>'));

5. Re-read the slot inside the transaction. The capacity read before the lock is stale by definition; the one after it is the only one that counts.

6. Check, in order: package active and not walk-in-only → slot capacity → per-user limit (perUserLimit <= 0 means unlimited) → artist-hour allocation capacity, under its own namespaced lock.

7. Insert quantity separate hold rows, plus one artist-hour child row per hold when the slot maps to an allocation.

The lock and the insert share one transaction

pg_advisory_xact_lock is released at commit. On the pooled Neon endpoint a separate execute-then-insert can land on different connections, so the lock protects nothing and double booking re-opens. Lock, re-read, check and insert all belong in the same db.transaction().

Advisory lock namespaces

pg_advisory_xact_lock has a 1-arg and a 2-arg form, and they are different keyspaces. packages/core/src/server/lib/advisory-locks.ts owns the numbers:

Namespace	Constant	Guards
1-arg hashtext(id)	—	individual time slots
7102026	BUNDLE_LOCK_NAMESPACE	event_bundle_time_slots
7102027	MERCH_INVENTORY_LOCK_NAMESPACE	merch variant inventory
7102029	ALLOCATION_LOCK_NAMESPACE	shared per-artist/hour allocations

Storefront (reservations.ts) and admin (admin-bookings.ts) paths must use the same namespace for the same kind of resource. Different namespaces means the two paths take locks that do not see each other, and a storefront hold races an admin reschedule or walk-in silently.

Auto-healing a stale payment lock

A cart locked by a PaymentIntent that nobody is paying would be stuck forever, so autoHealStalePaymentLock decides whether the lock can be cleared. It refuses to clear while any family still has a live hold — booth holds run 60 minutes and legitimately outlive the 15-minute ones — and then asks Stripe:

PI state	Decision
Stripe unreachable	Keep the lock (fail closed)
processing (FPX / bank redirect)	Keep the lock — money may still land
Succeeded, payment row exists	Webhook ran, cleanup was missed → clear
Succeeded, no payment row	Webhook has not fired yet → keep
requires_* or already canceled, no live holds	Cancel the PI and clear
Cancel call fails	Keep the lock — the PI may have moved to processing

Every branch that cannot prove the money is dead keeps the lock. The failure mode being avoided is “customer charged, no booking”.

Expiry

cleanupExpired (called by /api/cron/cleanup-expired) cancels expired holds family by family, stamping one now across the whole sweep. The load-bearing part is not the expiry check but the guard beside it:

NOT EXISTS (
  SELECT 1 FROM web.carts c
  WHERE c.id = <table>.cart_id
    AND c.stripe_payment_intent_id IS NOT NULL
    AND c.updated_at > NOW() - INTERVAL '2 hours'
)

A cart whose PaymentIntent was touched in the last two hours may still be settling — bank redirects routinely outrun the hold window — and cancelling under it produces exactly the failure above. The two-hour bound stops an abandoned lock from leaking holds forever.

Rules to keep

• Only slot_kind = 'signing' is bookable. createHold refuses every other kind (break, performance, live_drawing).
• The payment window is longer than the hold window: PAYMENT_HOLD_DURATION_MS is 45 minutes, for FPX and bank redirects.
• Side effects that must not survive a rollback (the Meta CAPI AddToCart mirror, emails) fire after the transaction commits — after() schedules outlive a rollback, so firing inside would report holds that never existed.
• Any change to this path gets an entry in docs/booking-payments/decisions.md.