Backend exercise · 2026
Ticket sales API
An events, seats and orders API. One endpoint lists an event's free seats; the other reserves a specific seat — and two requests for the same seat can never both succeed, even across separate server processes.
Problem
Selling the same seat twice is the classic ticketing bug: two requests ask "is it free?", both hear "yes", and both insert.
Context and motivation
A backend exercise where correctness under concurrency and query performance had to be demonstrated, not just claimed.
What I built
Built on my own: data model, API, deterministic seed, tests, measurement scripts and documentation.
Technical decisions
4Let the database decide
The code never asks whether a seat is free; it tries the insert. A UNIQUE(seat_id) constraint on order_seats is the guarantee, however many processes are running. A BEGIN IMMEDIATE transaction means the losing request leaves no orphan order behind.
No status column
A seat is free when no row in order_seats points to it. There is no separate status field that could drift out of sync with the orders.
Test with two real processes
Inside a single process, node:sqlite runs queries synchronously, so the race never happens — a test there would pass even with unsafe code. The tests start two servers on the same database.
An index shaped by the query
The (event_id, section, row_no, seat_no) index follows the WHERE and ORDER BY, removing the full scan and the temporary sort. With 200,000 seats, the p50 drops from 11.25 ms to 2.80 ms.
What it does
- GET /events/:id/seats/free with correct 404 and 400 responses
- Reservation endpoint returning 201, 409, 404 or 400
- Deterministic seed: 12 events, 12,000 seats, about 30% sold
- Scripts for the query plan, an HTTP benchmark and a 50-request race
Evidence
what was checked, and how| What | Result | Source | Checked |
|---|---|---|---|
| Automated tests | 8 passed | npm test run locally (includes two processes racing for one seat) | |
| 50-request race | 1 × 201, 49 × 409 | scripts/race.js, result documented in the README |
Limitations and next steps
Known limitations
- SQLite in a single file: right for the exercise, not for several servers on different machines.