Convex Database Setup

Ticket Hub uses Convex as a serverless real-time database with built-in authentication, cron jobs, and optimistic updates.

Prerequisites

Convex account (convex.dev)
Node.js 18+ installed

Environment Variables

CONVEX_DEPLOYMENT=your-deployment-name
CONVEX_DEPLOY_KEY=prod:your-deployment-key

Installation

npm install convex
npx convex dev

Database Schema

Ticket Hub uses four main tables:

convex/schema.ts

import { defineSchema, defineTable } from "convex/server";
import { v } from "convex/values";

export default defineSchema({
  events: defineTable({
    name: v.string(),
    description: v.string(),
    location: v.string(),
    eventDate: v.number(),
    price: v.number(),
    totalTickets: v.number(),
    userId: v.string(),
    imageStorageId: v.optional(v.id("_storage")),
    is_cancelled: v.optional(v.boolean()),
  }),
  
  tickets: defineTable({
    eventId: v.id("events"),
    userId: v.string(),
    purchasedAt: v.number(),
    status: v.union(
      v.literal("valid"),
      v.literal("used"),
      v.literal("refunded"),
      v.literal("cancelled")
    ),
    paymentIntentId: v.optional(v.string()),
    amount: v.optional(v.number()),
  })
    .index("by_event", ["eventId"])
    .index("by_user", ["userId"])
    .index("by_user_event", ["userId", "eventId"])
    .index("by_payment_intent", ["paymentIntentId"]),

  waitingList: defineTable({
    eventId: v.id("events"),
    userId: v.string(),
    status: v.union(
      v.literal("waiting"),
      v.literal("offered"),
      v.literal("purchased"),
      v.literal("expired")
    ),
    offerExpiresAt: v.optional(v.number()),
  })
    .index("by_event_status", ["eventId", "status"])
    .index("by_user_event", ["userId", "eventId"])
    .index("by_user", ["userId"]),

  users: defineTable({
    name: v.string(),
    email: v.string(),
    userId: v.string(),
    phone: v.optional(v.string()),
    stripeConnectId: v.optional(v.string()),
  })
    .index("by_user_id", ["userId"])
    .index("by_email", ["email"]),
});

Tables Overview

events

Stores event information created by organizers.Fields:

name: Event name
description: Event description
location: Venue or address
eventDate: Unix timestamp
price: Ticket price in GBP
totalTickets: Maximum capacity
userId: Event organizer’s Clerk ID
imageStorageId: Optional event image
is_cancelled: Cancellation flag

No indexes - Queries typically fetch all active events

tickets

Purchased tickets with payment information.Fields:

eventId: Reference to event
userId: Ticket owner’s Clerk ID
purchasedAt: Purchase timestamp
status: valid, used, refunded, or cancelled
paymentIntentId: Stripe payment intent
amount: Amount paid in pence

Indexes:

by_event: Find all tickets for an event
by_user: Find user’s tickets
by_user_event: Check if user has ticket for event
by_payment_intent: Find ticket by payment ID

waitingList

Queue management for ticket availability.Fields:

eventId: Reference to event
userId: User’s Clerk ID
status: waiting, offered, purchased, or expired
offerExpiresAt: When offer expires (30 min)

Indexes:

by_event_status: Process queue by event and status
by_user_event: Check user’s position for event
by_user: Find all user’s waiting list entries

Status Flow:

waiting → offered (30 min) → purchased
            ↓
         expired → next user offered

users

User profiles synced from Clerk.Fields:

name: Display name
email: Primary email
userId: Clerk user ID
phone: Optional phone number
stripeConnectId: For event organizers

Indexes:

by_user_id: Primary lookup by Clerk ID
by_email: Email-based queries

Key Queries

Get Event with Availability

convex/events.ts

export const getEventAvailability = query({
  args: { eventId: v.id("events") },
  handler: async (ctx, { eventId }) => {
    const event = await ctx.db.get(eventId);
    if (!event) throw new Error("Event not found");

    // Count purchased tickets
    const purchasedCount = await ctx.db
      .query("tickets")
      .withIndex("by_event", (q) => q.eq("eventId", eventId))
      .collect()
      .then(
        (tickets) =>
          tickets.filter(
            (t) => t.status === "valid" || t.status === "used"
          ).length
      );

    // Count active offers
    const now = Date.now();
    const activeOffers = await ctx.db
      .query("waitingList")
      .withIndex("by_event_status", (q) =>
        q.eq("eventId", eventId).eq("status", "offered")
      )
      .collect()
      .then(
        (entries) => entries.filter((e) => (e.offerExpiresAt ?? 0) > now).length
      );

    const totalReserved = purchasedCount + activeOffers;

    return {
      isSoldOut: totalReserved >= event.totalTickets,
      totalTickets: event.totalTickets,
      purchasedCount,
      activeOffers,
      remainingTickets: Math.max(0, event.totalTickets - totalReserved),
    };
  },
});

Get Queue Position

convex/waiting_list.ts

export const getQueuePosition = query({
  args: {
    eventId: v.id("events"),
    userId: v.string(),
  },
  handler: async (ctx, { eventId, userId }) => {
    // Get user's entry
    const entry = await ctx.db
      .query("waitingList")
      .withIndex("by_user_event", (q) =>
        q.eq("userId", userId).eq("eventId", eventId)
      )
      .filter((q) => q.neq(q.field("status"), "expired"))
      .first();

    if (!entry) return null;

    // Count people ahead in line
    const peopleAhead = await ctx.db
      .query("waitingList")
      .withIndex("by_event_status", (q) => q.eq("eventId", eventId))
      .filter((q) =>
        q.and(
          q.lt(q.field("_creationTime"), entry._creationTime),
          q.or(
            q.eq(q.field("status"), "waiting"),
            q.eq(q.field("status"), "offered")
          )
        )
      )
      .collect()
      .then((entries) => entries.length);

    return {
      ...entry,
      position: peopleAhead + 1,
    };
  },
});

Key Mutations

Join Waiting List

convex/events.ts

export const joinWaitingList = mutation({
  args: { eventId: v.id("events"), userId: v.string() },
  handler: async (ctx, { eventId, userId }) => {
    // Check for existing entry
    const existingEntry = await ctx.db
      .query("waitingList")
      .withIndex("by_user_event", (q) =>
        q.eq("userId", userId).eq("eventId", eventId)
      )
      .filter((q) => q.neq(q.field("status"), "expired"))
      .first();

    if (existingEntry) {
      throw new Error("Already in waiting list for this event");
    }

    // Check availability
    const event = await ctx.db.get(eventId);
    if (!event) throw new Error("Event not found");

    const { available } = await checkAvailability(ctx, eventId);
    const now = Date.now();

    if (available) {
      // Offer ticket immediately
      const waitingListId = await ctx.db.insert("waitingList", {
        eventId,
        userId,
        status: "offered",
        offerExpiresAt: now + DURATIONS.TICKET_OFFER, // 30 minutes
      });

      // Schedule expiration
      await ctx.scheduler.runAfter(
        DURATIONS.TICKET_OFFER,
        internal.waiting_list.expireOffer,
        { waitingListId, eventId }
      );
      
      return { success: true, status: "offered" };
    } else {
      // Add to queue
      await ctx.db.insert("waitingList", {
        eventId,
        userId,
        status: "waiting",
      });
      
      return { success: true, status: "waiting" };
    }
  },
});

Purchase Ticket

convex/events.ts

export const purchaseTicket = mutation({
  args: {
    eventId: v.id("events"),
    userId: v.string(),
    waitingListId: v.id("waitingList"),
    paymentInfo: v.object({
      paymentIntentId: v.string(),
      amount: v.number(),
    }),
  },
  handler: async (ctx, { eventId, userId, waitingListId, paymentInfo }) => {
    // Verify waiting list entry
    const waitingListEntry = await ctx.db.get(waitingListId);
    if (!waitingListEntry) {
      throw new Error("Waiting list entry not found");
    }

    if (waitingListEntry.status !== "offered") {
      throw new Error("Invalid waiting list status - ticket offer may have expired");
    }

    if (waitingListEntry.userId !== userId) {
      throw new Error("Waiting list entry does not belong to this user");
    }

    // Create ticket
    await ctx.db.insert("tickets", {
      eventId,
      userId,
      purchasedAt: Date.now(),
      status: "valid",
      paymentIntentId: paymentInfo.paymentIntentId,
      amount: paymentInfo.amount,
    });

    // Update waiting list
    await ctx.db.patch(waitingListId, {
      status: "purchased",
    });

    // Process queue for next person
    await processQueueX(ctx, eventId);
  },
});

Cron Jobs

Automatic cleanup of expired ticket offers:

convex/crons.ts

import { cronJobs } from "convex/server";
import { internal } from "./_generated/api";

const crons = cronJobs();

crons.interval(
  "cleanup-expired-offers",
  { minutes: 1 }, // Run every 1 minute
  internal.waiting_list.cleanupExpiredOffers
);

export default crons;

Cleanup Implementation

convex/waiting_list.ts

export const cleanupExpiredOffers = internalMutation({
  args: {},
  handler: async (ctx) => {
    const now = Date.now();
    
    // Find all expired offers
    const expiredOffers = await ctx.db
      .query("waitingList")
      .filter((q) =>
        q.and(
          q.eq(q.field("status"), "offered"),
          q.lt(q.field("offerExpiresAt"), now)
        )
      )
      .collect();

    // Group by event for batch processing
    const grouped = groupByEvent(expiredOffers);

    // Process each event's expired offers
    for (const [eventId, offers] of Object.entries(grouped)) {
      await Promise.all(
        offers.map((offer) =>
          ctx.db.patch(offer._id, {
            status: "expired",
          })
        )
      );

      // Process queue to offer to next person
      await processQueueX(ctx, eventId as Id<"events">);
    }
  },
});

Real-time Subscriptions

Convex automatically handles real-time updates:

import { useQuery } from "convex/react";
import { api } from "../convex/_generated/api";

export function EventAvailability({ eventId }) {
  // Automatically updates when availability changes
  const availability = useQuery(
    api.events.getEventAvailability,
    { eventId }
  );

  if (!availability) return <div>Loading...</div>;

  return (
    <div>
      <p>Remaining: {availability.remainingTickets}</p>
      <p>Sold: {availability.purchasedCount}</p>
      <p>Reserved: {availability.activeOffers}</p>
    </div>
  );
}

Rate Limiting

Protect against abuse with Convex Rate Limiter:

convex/events.ts

import { RateLimiter } from "@convex-dev/rate-limiter";
import { MINUTE } from "@convex-dev/rate-limiter";

const rateLimiter = new RateLimiter(components.rateLimiter, {
  queueJoin: {
    kind: "fixed window",
    rate: 3, // 3 joins allowed
    period: 30 * MINUTE, // in 30 minutes
  },
});

export const joinWaitingList = mutation({
  handler: async (ctx, { eventId, userId }) => {
    // Check rate limit
    const status = await rateLimiter.limit(ctx, "queueJoin", { key: userId });
    if (!status.ok) {
      throw new ConvexError(
        `You've joined the waiting list too many times. Please wait ${
          Math.ceil(status.retryAfter / (60 * 1000))
        } minutes before trying again.`
      );
    }
    
    // Continue with join logic...
  },
});

Constants

Centralize timing and status values:

convex/constant.ts

import { Doc } from "./_generated/dataModel";

// Time constants in milliseconds
export const DURATIONS = {
  TICKET_OFFER: 30 * 60 * 1000, // 30 minutes
} as const;

// Status types for better type safety
export const WAITING_LIST_STATUS: Record<string, Doc<"waitingList">["status"]> = {
  WAITING: "waiting",
  OFFERED: "offered",
  PURCHASED: "purchased",
  EXPIRED: "expired",
} as const;

export const TICKET_STATUS: Record<string, Doc<"tickets">["status"]> = {
  VALID: "valid",
  USED: "used",
  REFUNDED: "refunded",
  CANCELLED: "cancelled",
} as const;

Deployment

Deploy your schema and functions:

npx convex deploy

For production:

npx convex deploy --prod

Overview

Getting Started

Core Features

Integrations

Guides

Convex Database Setup

Prerequisites

Environment Variables

Installation

Database Schema

Tables Overview

Key Queries

Get Event with Availability

Get Queue Position

Key Mutations

Join Waiting List

Purchase Ticket

Cron Jobs

Cleanup Implementation

Real-time Subscriptions

Rate Limiting

Constants

Deployment

Next Steps

Stripe Payments

Clerk Auth

Build docs developers (and LLMs) love

Overview

Getting Started

Core Features

Integrations

Guides

​Prerequisites

​Environment Variables

​Installation

​Database Schema

​Tables Overview

​Key Queries

​Get Event with Availability

​Get Queue Position

​Key Mutations

​Join Waiting List

​Purchase Ticket

​Cron Jobs

​Cleanup Implementation

​Real-time Subscriptions

​Rate Limiting

​Constants

​Deployment

​Next Steps

Stripe Payments

Clerk Auth

Build docs developers (and LLMs) love

Prerequisites

Environment Variables

Installation

Database Schema

Tables Overview

Key Queries

Get Event with Availability

Get Queue Position

Key Mutations

Join Waiting List

Purchase Ticket

Cron Jobs

Cleanup Implementation

Real-time Subscriptions

Rate Limiting

Constants

Deployment

Next Steps