Idempotent Ticket Minting: Guaranteeing Zero Duplicate Transactions Across Webhook Retries

Experiential raffle and flash-sale platforms face a high-stakes checkout challenge. During major digital campaign pushes, thousands of mobile buyers flood the checkout funnel simultaneously. Forcing users through multi-step account registration forms introduces a 40% to 50% mobile drop-off penalty.

However, allowing unauthenticated guest checkouts introduces dangerous downstream failure modes: duplicate webhook dispatches from payment processors (such as Square, Stripe, or Apple Pay), race conditions during entry number allocation, and fragmented customer order histories.

During the architecture of the Fans Chance experiential charity draw platform, Kaprin resolved these challenges using a deterministic PostgreSQL procedure with Row-Level Security (RLS) and idempotent session claiming.

The Webhook Retry Dilemma

Payment webhooks are fundamentally asynchronous and non-deterministic. When a customer completes an Apple Pay or card transaction, the payment gateway dispatches a webhook event to your backend.

If your server takes longer than 2.5 seconds to acknowledge receipt due to database load, the payment provider automatically retries, sending an identical webhook payload 10 seconds later.

Under naive implementations, both webhooks process simultaneously:

• Webhook Worker 1 executes: INSERT INTO raffle_entries ...
• Webhook Worker 2 executes: INSERT INTO raffle_entries ...

The buyer is allocated double the tickets they paid for, the audited charitable allocation is corrupted, and regulatory prize draw compliance is violated.

The PL/pgSQL Idempotent Procedure

To eliminate this failure mode, entry allocation is enclosed within an atomic PostgreSQL stored procedure enforcing a pessimistic row lock (SELECT FOR UPDATE) on the checkout session record:

-- Idempotent Ticket Minting Procedure with Pessimistic Lock
CREATE OR REPLACE FUNCTION mint_entries_after_payment(
    p_checkout_session_id UUID,
    p_payment_intent_id TEXT,
    p_quantity INT,
    p_draw_id UUID
)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    v_session RECORD;
    v_start_number INT;
    v_end_number INT;
BEGIN
    -- 1. Lock the session record exclusively to prevent parallel webhook execution
    SELECT * INTO v_session
    FROM checkout_sessions
    WHERE id = p_checkout_session_id
    FOR UPDATE;

    -- 2. Idempotency Check: If already processed, return immediately
    IF v_session.status = 'COMPLETED' THEN
        RETURN jsonb_build_object(
            'status', 'ALREADY_PROCESSED',
            'session_id', p_checkout_session_id
        );
    END IF;

    -- 3. Atomically allocate consecutive ticket range
    UPDATE draws
    SET current_ticket_counter = current_ticket_counter + p_quantity
    WHERE id = p_draw_id
    RETURNING (current_ticket_counter - p_quantity + 1), current_ticket_counter
    INTO v_start_number, v_end_number;

    -- 4. Record ticket range and mark session completed
    INSERT INTO draw_entries (session_id, draw_id, ticket_start, ticket_end, quantity)
    VALUES (p_checkout_session_id, p_draw_id, v_start_number, v_end_number, p_quantity);

    UPDATE checkout_sessions
    SET status = 'COMPLETED',
        payment_reference = p_payment_intent_id,
        completed_at = NOW()
    WHERE id = p_checkout_session_id;

    RETURN jsonb_build_object(
        'status', 'SUCCESS',
        'ticket_start', v_start_number,
        'ticket_end', v_end_number
    );
END;
$$;

Even if four identical webhooks arrive simultaneously across four parallel serverless containers, only the first transaction obtains the row lock on checkout_sessions. The remaining three serialize behind the lock, see the COMPLETED status, and exit cleanly without minting duplicate entries.

Unauthenticated Checkout with Post-Purchase Claiming

To eliminate the 45% mobile drop-off penalty caused by upfront account registration, the platform enables 1-click unauthenticated guest purchases. Customer metadata (name, email, phone) is extracted directly from the verified Apple Pay or card billing payload.

If the user subsequently registers an account or signs in with magic links weeks later, a database procedure (claim_session_to_current_user) safely re-associates past guest orders, ticket numbers, and receipts to their authenticated profile using cryptographically signed session tokens.

Operational Results & Regulatory Compliance

This architecture powered the Fans Chance draw platform in partnership with the Barmy Army and Bat for a Chance:

Summary: Determinism in Event-Driven Systems

When architecting high-velocity transactional platforms, never trust client-side state or assume network idempotency. Enforcing pessimistic row locking at the database layer and establishing clear session re-association boundaries allows businesses to maximize top-of-funnel conversion while maintaining uncompromised financial auditability.