Detecting Broken Payment Event Chains in Stripe Using SQL Server

Stripe processes payments as event chains, and in real-world applications, it’s important to validate that each payment follows the expected sequence. For example:

Expected Event Order:

payment_created
payment_captured
payment_settled
Optional: payment_refunded

When events happen out of order, or some events are missing, it can indicate a broken payment chain, which can be costly in production systems.

In this post, we’ll walk through how to detect broken or incomplete payment chains using SQL Server with a real Stripe event example.

The Problem

We have a table called payment_events:

payment_id	event_type	event_time
101	created	2026-01-01 10:00
101	captured	2026-01-01 10:05
101	settled	2026-01-01 10:10
104	created	2026-01-02 09:00
104	captured	2026-01-02 09:05
104	refunded	2026-01-02 09:15

Here we can see:

Payment 101: follows the correct order → valid payment
Payment 104: refund happened without a settlement → broken chain

Our goal is to identify payment IDs with missing events or invalid transitions.

Step 1: Identify the Previous Event

To check the sequence of events, we use the LAG() window function. This lets us see the previous event for each payment:


WITH previous_events AS (
    SELECT
        payment_id,
        event_type,
        event_time,
        LAG(event_type) OVER (
            PARTITION BY payment_id
            ORDER BY event_time
        ) AS prev_event
    FROM payment_events
)

PARTITION BY payment_id ensures we check events within each payment.

ORDER BY event_time preserves the correct timeline.

Now, we know the previous event for every row.

Step 2: Identify Missing Event Types

We can use CASE statements to flag if any required events are missing:


SELECT
    payment_id,
    MAX(CASE WHEN event_type = 'payment_created' THEN 1 ELSE 0 END) AS has_created,
    MAX(CASE WHEN event_type = 'payment_captured' THEN 1 ELSE 0 END) AS has_captured,
    MAX(CASE WHEN event_type = 'payment_settled' THEN 1 ELSE 0 END) AS has_settled
FROM previous_events
GROUP BY payment_id

MAX(CASE …) returns 1 if the event exists, 0 otherwise.
Using MAX instead of COUNT is critical because Stripe may retry events — duplicates should not affect the validation.

✅ MAX checks existence, COUNT counts frequency, which can mislead in event pipelines.

Step 3: Identify Invalid Event Transitions

We also need to flag events that happen out of order:


MAX(
    CASE
        WHEN event_type = 'payment_captured' AND prev_event <> 'payment_created' THEN 1
        WHEN event_type = 'payment_settled' AND prev_event <> 'payment_captured' THEN 1
        WHEN event_type = 'payment_refunded' AND prev_event <> 'payment_settled' THEN 1
        ELSE 0
    END
) AS invalid_transition

Any invalid transition gets flagged as 1, otherwise 0.
Using MAX ensures that if even one invalid transition exists, the payment ID is flagged.

Step 4: Aggregate and Validate

Now we can aggregate results into a single record per payment:


WITH cte_validation AS (
    SELECT
        payment_id,
        MAX(CASE WHEN event_type = 'payment_created' THEN 1 ELSE 0 END) AS has_created,
        MAX(CASE WHEN event_type = 'payment_captured' THEN 1 ELSE 0 END) AS has_captured,
        MAX(CASE WHEN event_type = 'payment_settled' THEN 1 ELSE 0 END) AS has_settled,
        MAX(
            CASE
                WHEN event_type = 'payment_captured' AND prev_event <> 'payment_created' THEN 1
                WHEN event_type = 'payment_settled' AND prev_event <> 'payment_captured' THEN 1
                WHEN event_type = 'payment_refunded' AND prev_event <> 'payment_settled' THEN 1
                ELSE 0
            END
        ) AS invalid_transition
    FROM previous_events
    GROUP BY payment_id
)
SELECT payment_id
FROM cte_validation
WHERE has_created = 0
   OR has_captured = 0
   OR has_settled = 0
   OR invalid_transition = 1;

This query returns all payment IDs with missing or out-of-order events.

Step 5: Why MAX + CASE Works

MAX ensures existence is checked, ignoring duplicates.
COUNT would return the number of events, which is misleading if Stripe retries events.
CASE allows multiple conditions to be checked cleanly:

- If the first condition is met, the case exits

- This ensures invalid transitions are flagged immediately

✅ Result for Our Example

payment_id
104

Payment 104 is flagged because the refund happened without a settlement.
Payment 101 is valid and not flagged.

Bonus Tips

Use window functions like LAG to check event order explicitly.
Use MAX + CASE for existence checks.
Use SUM + CASE or COUNT + CASE only when you want to count duplicates.

Key Takeaways

Stripe payments should follow a strict sequence.
LAG() helps track the previous event for each payment.
MAX(CASE…) is better than COUNT for event existence validation.
Always flag missing events or invalid transitions to catch broken payment chains.

💡 Practice This Yourself

If you found this useful, try applying the same logic to other event pipelines like:

Order shipments (created → packed → shipped → delivered)
User actions (signup → verify_email → first_login)

You can also get more hands-on practice in my SQL Interview Practice Workbook →

https://knowstar.gumroad.com/l/gyaous