Ticketing & Commerce Flow

Relevant source files

The following files were used as context for generating this wiki page:

• src/components/modals/booking-checkout-modal.tsx
• src/app/api/tickets/checkout/route.ts
• supabase/migrations/20260503011925_event_phase1.sql
• supabase/functions/ticket-payment-webhook/index.ts
• src/components/events/event-ticket-tiers.tsx
• src/lib/supabase/database.types.ts
• supabase/migrations/20260504005355_event_phase1_5_taxes_and_fees.sql

The Ticketing & Commerce Flow is a robust, AI-first transactional system designed to manage event discovery, ticket selection, secure checkout, and payment finalization within the Medellín platform. It integrates a Next.js frontend with Supabase Edge Functions and Stripe to provide a seamless purchasing experience while maintaining strict inventory integrity and financial audit trails.

Sources: README.md:1-15, supabase/migrations/20260503011925_event_phase1.sql:1-15

System Architecture

The architecture follows a distributed pattern where the frontend manages user interactions and initial validation, while database-level RPCs (Remote Procedure Calls) and Edge Functions handle atomic inventory operations and payment webhooks.

Transactional Sequence

The following diagram illustrates the end-to-end flow from ticket selection to payment confirmation.

The flow ensures that tickets are reserved during the checkout session to prevent overselling.
Sources: src/app/api/tickets/checkout/route.ts:25-60, supabase/migrations/20260503011925_event_phase1.sql:350-420, supabase/functions/ticket-payment-webhook/index.ts:150-180

Database Schema & Logic

The commerce system relies on several core tables and specialized Postgres functions to maintain atomicity and security.

Core Entities

Table	Description	Key Constraints
event_tickets	Defines ticket tiers, pricing, and availability.	qty_sold + qty_pending <= qty_total
event_orders	Tracks individual purchase attempts and final statuses.	short_id and access_token are unique.
event_attendees	Per-person record for entry, linked to orders.	qr_token must be unique.
event_check_ins	Immutable audit log of every door scan.	Result-based check constraints.

Sources: supabase/migrations/20260503011925_event_phase1.sql:160-320

Inventory Protection

Inventory integrity is enforced through the ticket_checkout_create_pending RPC. This function uses advisory locks at the event level and FOR UPDATE row locks on the ticket tier to prevent race conditions during high-volume sales.

-- Lock at event_id granularity
PERFORM pg_advisory_xact_lock(hashtext(p_event_id::text));

-- Capacity check with row lock
SELECT * INTO v_ticket FROM public.event_tickets WHERE id = p_ticket_id FOR UPDATE;

IF v_ticket.qty_sold + v_ticket.qty_pending + p_quantity > v_ticket.qty_total THEN
  RAISE EXCEPTION 'OUT_OF_STOCK';
END IF;

Sources: supabase/migrations/20260503011925_event_phase1.sql:362-378

Payment Integration

The system uses Stripe for secure payment processing. The integration is divided into session creation and webhook handling.

Checkout Session Initiation

The /api/tickets/checkout route prepares the data and communicates with the ticket-checkout Edge Function. It generates success and cancel URLs that preserve the user's context through a returnPath.

Sources: src/app/api/tickets/checkout/route.ts:40-55

Webhook Processing

The ticket-payment-webhook Edge Function processes asynchronous events from Stripe. It uses the idempotency_keys table to ensure that retried Stripe events do not result in duplicate processing.

Event Type	Logic Triggered
checkout.session.completed	Calls ticket_payment_finalize RPC.
checkout.session.expired	Calls ticket_checkout_cancel RPC to release qty_pending.
charge.refunded	Calls ticket_payment_refund RPC.

Sources: supabase/functions/ticket-payment-webhook/index.ts:100-140

Taxes & Fees

The system supports complex tax and fee calculations, particularly for Colombia-specific requirements like IVA (19%).

Calculation Logic

Fees can be calculated as FIXED amounts per ticket or PERCENTAGE rates. The compute_ticket_total RPC aggregates these rules into a subtotal, tax, and fee breakdown before the final order is persisted.

User Experience Components

Ticket Selection

The EventTicketTiers component provides a user interface for selecting quantities across different tiers. It dynamically calculates "lowest available price" and handles "Sold out" states based on the combined qty_sold and qty_total fields.
Sources: src/components/events/event-ticket-tiers.tsx:25-50

Booking Checkout Modal

The BookingCheckoutModal captures buyer details (Name, Email) and initiates the checkout process. It uses an idempotency key generated in the browser (crypto.randomUUID()) to prevent duplicate form submissions.
Sources: src/components/modals/booking-checkout-modal.tsx:35-55

Summary

The Ticketing & Commerce Flow provides a secure and scalable mechanism for managing event sales. By moving critical logic (inventory decrementing and payment finalization) into database-level transactions, it ensures data consistency across high-concurrency environments. The use of Edge Functions and Stripe webhooks allows the system to handle asynchronous payment states reliably, while the taxes and fees module provides necessary flexibility for regional compliance.