Dynamic Ticket Pricing Without Overselling: Seat Holds in Postgres

Nineteen seats have sold at ₹99. Two runners tap Register in the same second. One of them should pay ₹99 for the 20th seat, the other ₹129, and nobody should be charged twice or ticketed for a seat that does not exist. That is the problem behind the automatic price tiers on Verve Run Club: ₹99 for the first 20 seats, ₹129 for the next 40, ₹149 for the last 20, and 80 seats in total. This post covers how it works, what I tested against a real database, and the edge cases that surprised me, including one I predicted wrong.

The rule: three prices, 80 seats

A detail worth getting right first: the 20th seat still costs ₹99. The price flips for the 21st person. The system thinks in places in line, counted from zero, so places 0 to 19 pay ₹99. Each tier says up to which place it applies, and that list of tiers lives on the run itself, so changing a price never needs a deploy.

Why the obvious version breaks

The obvious version reads how many people have paid, works out the price, takes the payment and then inserts the ticket. It fails in two ways. First, it checks and then acts, so with 19 paid, everyone who taps at the same moment reads 19 and is quoted ₹99. All of them pay, and the cheap tier is oversold. Near the cap it is worse: the 81st person gets a ticket for a seat that does not exist. Second, it only counts people who have finished paying, so someone mid-checkout does not exist yet and the price lags reality. Flip the switch below to watch both problems appear.

The fix: claim the seat first, pay second

Instead, checkout begins by calling a database function, reserve_seat. It records a seat hold that lasts ten minutes and returns the price for that person. Everything else follows from three decisions inside it, shown here trimmed from the real migration.

-- The serialization point: concurrent claimants queue here, first in wins.
select * into v_run from public.runs where id = p_run_id for update;

-- Abandoned checkouts free their seats without a cron job.
delete from public.seat_holds
 where run_id = p_run_id and expires_at < now();

select count(*) into v_taken from public.registrations where run_id = p_run_id;
select count(*) into v_held  from public.seat_holds    where run_id = p_run_id;
v_position := v_taken + v_held; -- 0-based place in line

if v_position >= v_run.spots_total then
  raise exception 'run_full';
end if;

select (t->>'fee')::int into v_fee
  from jsonb_array_elements(v_run.price_tiers) t
 where v_position < (t->>'upTo')::int
 order by (t->>'upTo')::int
 limit 1;

insert into public.seat_holds (run_id, user_id, fee_inr, expires_at)
values (p_run_id, v_uid, v_fee, now() + interval '10 minutes');

The first line takes a row lock on the run. Concurrent claims now queue at that lock, and each one sees the count left behind by the one before it. Expired holds are deleted at the start, so an abandoned checkout releases its seat the next time anyone claims, with no scheduled job. The place in line is paid registrations plus live holds, so someone mid-checkout already occupies a seat. And a retry, like a double click or a reopened checkout, returns the same hold with the same locked price and refreshes its ten minutes instead of sending the person to the back of the queue.

Place in line decides the price, not payment

Because holds count, the next visitor can be quoted ₹129 while only 19 people have paid: the 20th seat is held by someone still typing their UPI PIN. Play with it below. Claim a few seats, let people pay, and let someone walk away.

What happens when several people want the 20th seat

Suppose 19 seats are paid and ten people tap Register in the same second. This is the sequence, and it is the heart of the design:

• Ten requests reach the database. Nine wait at the lock on the run row.
• The first one through counts 19 paid and 0 holds, so their place is 19, inside the ₹99 tier. They get a ten-minute hold at ₹99.
• The second sees 19 paid plus 1 hold, place 20, so ₹129. The third sees place 21, also ₹129, and so on down the line.
• Each request holds the lock for milliseconds, so nobody waits long, but the order is decided by who reaches the lock first.
• When the first person pays, their registration is inserted and their hold is released in the same transaction, so a seat is never counted twice.

I did not trust my reasoning, so I raced it

A lock argument feels convincing, and that is exactly when to test it. So I wrote a script that starts a throwaway PostgreSQL 18.4 server, applies the real migration files, and opens one database connection for every simulated runner. Supabase's auth function is stubbed with the same behaviour, so the migrations run unchanged. It fires the requests in the same instant and then checks the invariants: how many seats at each price, how many holds, how many tickets. It has 24 checks in nine scenarios, and it lives in the repo under scripts/race-test.

Some honest limits. This ran on my laptop against a local database, so I am not quoting timings, and 200 connections on Windows says nothing about production latency. A race test can find a bug but can never prove there is none: the proof is the lock, and the test guards it against being broken later. And it covers the database, not Razorpay's side, so I have not simulated payment retries or a real gateway outage.

Edge cases, including one I predicted wrong

An abandoned cheap seat is not re-offered cheaply

I expected that if the ₹99 holder walked away, the next person would get ₹99. The test said ₹129. The price follows the place in line, which is paid plus live holds, and nine people were still holding seats behind the abandoned one. The next person's place is 19 plus 9, so 28. If those nine all pay, only 19 seats end up sold at ₹99 instead of 20. When nobody is queued behind the abandoned seat, it is offered at ₹99 again, and the test confirms that too. I kept the behaviour on purpose: prices already quoted stay stable, and the alternative means renegotiating a price with someone mid-checkout. The cost is at most ₹30 on one seat.

Paying after your hold expired

Holds last ten minutes, and people take longer than that more often than you would think: a UPI app switch, a call, a tab left open. If someone pays after their hold expired, a trigger checks the run. If it is full, or the seat now belongs to someone else's hold, the insert is refused and the app refunds the payment through Razorpay automatically. If the refund call itself fails, it writes a loud log line so the payment can be refunded by hand instead of vanishing. If nobody else needed the seat, the late payment is simply accepted.

Two paths, one payment

After a payment there are two ways a ticket can be created: the browser calls a verify endpoint, and Razorpay calls a webhook from its servers. Both can fire, at almost the same moment. Both insert the same registration, and a unique constraint on the run and the person lets exactly one win. The loser sees a unique violation and treats it as success, not as an error.

The price on screen is a preview

The run page shows a price computed from paid registrations, which is cheap to read. The reservation counts holds too. So the button can say ₹99 while your locked price turns out to be ₹129, because someone claimed the last cheap seat a second ago. Nobody should meet a bigger number for the first time inside a payment popup, so checkout now stops and says so before it opens the payment sheet.

// The price is locked when the seat is claimed, so it can differ from the
// one on screen. Never open the payment sheet at a surprise amount.
if (Math.round(order.amount / 100) !== run.feeInr) {
  setLockedOrder(order);
  setStep("confirm-price");
  return;
}
openRazorpay(order);

The receipt that quoted today's price

Testing this exposed a quieter bug. The receipt email printed the run's current price, not what the person actually paid. Since two people can legitimately pay different amounts, and payments finish in a different order than seats were claimed, a receipt could quote the wrong number. The registration now stores the amount Razorpay captured, and the receipt uses it. Older registrations simply have no amount and fall back to the old behaviour.

Never trust the browser's amount

The amount for the payment comes from the hold on the server, and the order notes carry the hold id. The client never sends a price, so a tampered request cannot buy a ₹1 ticket for a ₹149 seat.

What I would build next

• Show the seats left at this price including live holds, so the banner matches what a claim would actually return.
• Decide deliberately whether abandoned cheap seats should be re-offered, and measure how often it really happens.
• A waiting list for a full run whose holds might expire, instead of a flat run full.
• Run the race test in CI against every migration that touches seats.
• Compare the stored amounts with Razorpay in the reconcile tool.

Frequently asked questions

Want tiered pricing and ticketing built for your event?

I build the whole engine for clubs, communities and small businesses: pricing tiers, payments, tickets, live results and the admin tools that keep an event running. If a seat, a price or a payment is going wrong at your events, you can find me at smit.website.