Real-Time Slot Locking Implementation Plan

packages/backend/convex/
├── domains/
│   ├── reservations.ts      # Change RESERVATION_EXPIRY_MINUTES to 5
│   └── events.ts            # Add getEventAvailabilityWithPending query
├── crons.ts                  # Change interval to 30 seconds
└── functions/
    └── scheduled.ts          # expireStaleReservations (from previous plan)

apps/frontend/
├── components/booking/
│   └── step-tickets.tsx      # Already wired (from previous fix)
├── components/home/
│   └── experience-schedule-preview.tsx  # Real-time availability display
└── hooks/
    └── booking/
        └── use-ticket-selection.ts   # Local state (no changes needed)

// Line ~214: Change from 10 to 5
const RESERVATION_EXPIRY_MINUTES = 5;

import { cronJobs } from "convex/server";
import { internal } from "./_generated/api";
 
const crons = cronJobs();
 
// D-1 low occupancy check: daily at 09:00 Vietnam time = 02:00 UTC
crons.daily(
  "d1LowOccupancyCheck",
  { hourUTC: 2, minuteUTC: 0 },
  internal.functions.scheduled.d1LowOccupancyCheck,
);
 
// Payment expiry: every 30 seconds to release stale reservations
crons.interval(
  "expireStaleReservations",
  { seconds: 30 },
  internal.functions.scheduled.expireStaleReservations,
);
 
export default crons;

/**
 * expireStaleReservations — release seats for expired pending reservations
 *
 * Runs every 30 seconds via Convex cron. Finds all PENDING reservations where
 * bookingExpiresAt < now and:
 * - Releases seats back to event capacity (decrements bookedCount)
 * - Marks reservation as FAILED
 * - Clears payment-related fields
 *
 * Idempotent: only processes PENDING reservations, skips PAID/REFUNDED
 */
export const expireStaleReservations = internalMutation({
  args: v.object({}),
  handler: async (ctx) => {
    const now = Date.now();
    const expired = await ctx.db
      .query("reservations")
      .withIndex("by_expires", (q) => q.lt("bookingExpiresAt", now))
      .collect();
 
    let released = 0;
    for (const reservation of expired) {
      // Only process PENDING reservations
      if (reservation.paymentStatus !== "PENDING") continue;
 
      // Release seats back to event (decrement bookedCount)
      const event = await ctx.db.get(reservation.eventId);
      if (event) {
        await ctx.db.patch(event._id, {
          bookedCount: Math.max(0, event.bookedCount - reservation.quantity),
          updatedAt: now,
        });
      }
 
      // Mark reservation as failed and clear payment fields
      await ctx.db.patch(reservation._id, {
        paymentStatus: "FAILED",
        bookingExpiresAt: undefined,
        paymentExpiresAt: undefined,
        onePayOrderId: undefined,
        vaNumber: undefined,
        qrCode: undefined,
        qrCodeUrl: undefined,
        updatedAt: now,
      });
 
      released++;
    }
 
    return { released, checked: expired.length };
  },
});

/**
 * getEventAvailabilityWithPending — real-time availability with pending breakdown
 *
 * Returns availability info including separate counts for paid vs pending (in-checkout)
 * reservations. This enables the "X being processed" display on show listings.
 *
 * Usage: Frontend subscribes to this query for real-time updates via Convex subscriptions.
 */
export const getEventAvailabilityWithPending = zQuery({
  args: { eventId: zid("experienceEvents") },
  returns: z
    .object({
      eventId: zid("experienceEvents"),
      code: z.string(),
      experienceTitle: z.string(),
      date: z.string(),
      time: z.string(),
      totalCapacity: z.number(),
      paidCount: z.number(), // Confirmed paid reservations
      pendingCount: z.number(), // In-checkout (PENDING status)
      available: z.number(), // total - paid - pending
      status: z.enum(["SCHEDULED", "CANCELLED", "SOLD_OUT"]),
      experienceOnlyEnabled: z.boolean(),
      dinnerPrice: z.number(),
      experienceOnlyPrice: z.number(),
      badge: z.enum(["AVAILABLE", "FEW_LEFT", "ALMOST_FULL", "SOLD_OUT"]),
    })
    .nullable(),
  handler: async (ctx, { eventId }) => {
    const evt = await ctx.db.get(eventId);
    if (!evt) return null;
 
    const experience = await ctx.db.get(evt.experienceId);
 
    // Get all non-failed reservations for this event
    const allReservations = await ctx.db
      .query("reservations")
      .withIndex("by_event", (q) => q.eq("eventId", eventId))
      .collect();
 
    // Separate paid vs pending
    const paidReservations = allReservations.filter(
      (r) => r.paymentStatus === "PAID",
    );
    const pendingReservations = allReservations.filter(
      (r) => r.paymentStatus === "PENDING",
    );
 
    const paidCount = paidReservations.reduce((sum, r) => sum + r.quantity, 0);
    const pendingCount = pendingReservations.reduce(
      (sum, r) => sum + r.quantity,
      0,
    );
    const available = Math.max(
      0,
      evt.actualCapacity - paidCount - pendingCount,
    );
 
    // Badge logic with "being processed" urgency
    const hasPending = pendingCount > 0;
    let badge: "AVAILABLE" | "FEW_LEFT" | "ALMOST_FULL" | "SOLD_OUT";
 
    if (available === 0) {
      badge = "SOLD_OUT";
    } else if (available <= 5) {
      badge = hasPending ? "ALMOST_FULL" : "FEW_LEFT";
    } else if (available <= Math.ceil(evt.actualCapacity * 0.2)) {
      // Within 20% capacity remaining
      badge = hasPending ? "ALMOST_FULL" : "FEW_LEFT";
    } else {
      badge = "AVAILABLE";
    }
 
    return {
      eventId: evt._id,
      code: evt.code,
      experienceTitle: experience?.title ?? "Unknown Experience",
      date: evt.date,
      time: evt.time,
      totalCapacity: evt.actualCapacity,
      paidCount,
      pendingCount,
      available,
      status: evt.status,
      experienceOnlyEnabled: evt.experienceOnlyEnabled,
      dinnerPrice: evt.dinnerPrice,
      experienceOnlyPrice: evt.experienceOnlyPrice,
      badge,
    };
  },
});

reservations: defineTable({
  // ... existing fields
}).index("by_event", ["eventId"]),

// In the availability display section, replace existing display with:
<div className="flex items-center gap-2 text-sm">
  <span>{paidCount} paid</span>
  {pendingCount > 0 && (
    <span className="text-orange-500 font-medium">
      • {pendingCount} being processed
    </span>
  )}
  <span>• {available} available</span>
</div>

// In badge styling:
"ALMOST_FULL": "bg-orange-500/10 text-orange-500 border-orange-500/30"
// This creates urgency: "almost full AND someone is paying right now"

reservations: defineTable({
  // ... existing fields ...
  eventId: v.id("experienceEvents"),
  // ...
}).index("by_event", ["eventId"]),

npx convex run internal.functions.scheduled.expireStaleReservations --json

git add packages/backend/convex/domains/reservations.ts \
      packages/backend/convex/domains/events.ts \
      packages/backend/convex/crons.ts \
      packages/backend/convex/functions/scheduled.ts \
      packages/backend/convex/schema.ts \
      apps/frontend/components/home/experience-schedule-preview.tsx
 
git commit -m "feat(booking): real-time slot locking with 'being processed' indicator
 
- 5-minute slot lock on Book Now click (was 10 min)
- 30-second cron to release expired holds (was 1 min)
- New getEventAvailabilityWithPending query for real-time breakdown:
  - paidCount: confirmed reservations
  - pendingCount: in-checkout (being processed)
  - available: remaining bookable slots
- Frontend shows 'X being processed' badge with urgency
- ALMOST_FULL badge when pending > 0 and available is low
 
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>"