Concert Ticketing Platform

Node.js + TypeScript + Express + TypeORM + SQLite (sql.js driver) implementation for a high-demand concert ticketing assignment.

API base URL:

http://localhost:3000

Swagger UI:

http://localhost:3000/docs

This project uses TypeORM sql.js, so setup does not require native sqlite3 build tools on Windows.

API

GET /concerts

Lists concerts with current available ticket stock.

POST /reserve

Reserves one ticket for five minutes.

{
  "userId": "user-1",
  "concertId": 1,
  "category": "General"
}

If stock is zero, the request returns 409.

POST /purchase

Converts a pending reservation to completed.

{
  "userId": "user-1",
  "reservationId": 1
}

POST /cleanup

Manual cleanup trigger that releases expired pending reservations back to stock.

Migrations

synchronize is set to false in src/data-source.ts.

The migration folder contains schema evolution:

1. 1710000000000-InitialSchema.ts creates concerts, tickets, reservations, foreign keys, and indexes.
2. 1710000001000-AddCategoryToTickets.ts adds category to the tickets table.

In a real project I would generate the first migration with:

npm run migration:generate -- src/migrations/InitialSchema

Then after adding Ticket.category, generate the second migration with:

npm run migration:generate -- src/migrations/AddCategoryToTickets

Double-Selling Protection

The reservation flow uses queryRunner.startTransaction() in src/services/reservationService.ts.

Inside the transaction, stock is reduced with a guarded update:

UPDATE tickets
SET availableStock = availableStock - 1
WHERE id = ? AND availableStock > 0

The reservation is only saved after that update succeeds. If anything fails after the stock decrement, the catch block calls rollbackTransaction(), so the stock decrement is undone. This keeps the stock and reservation record consistent.

SQLite allows only one writer at a time, and the guarded update also prevents stock from going below zero when many requests arrive together.

Rollback Proof

Run:

npm run test:rollback

Expected output:

Expected failure: Intentional reservation failure after stock decrement
Stock before failed reservation: 1
Stock after failed reservation: 1
PASS: stock was rolled back

Indexing Choices

B-Tree index on tickets.concertId

SQLite indexes are B-Tree indexes by default. The index below speeds up finding ticket inventory for a concert:

CREATE INDEX "IDX_tickets_concertId" ON "tickets" ("concertId")

This helps both GET /concerts joins and reservation lookups by concertId.

Partial index for pending cleanup

The cleanup job only cares about expired reservations where status = 'PENDING', so the migration creates:

CREATE INDEX "IDX_reservations_pending_expiresAt"
ON "reservations" ("expiresAt")
WHERE "status" = 'PENDING'

A partial index is better than a standard status index for cleanup because it only stores pending rows. Completed and expired reservations can grow very large over time, but cleanup does not need them. The smaller index is cheaper to scan, cheaper to keep updated, and better focused on the query:

SELECT * FROM reservations
WHERE status = 'PENDING' AND expiresAt < datetime('now')

EXPLAIN QUERY PLAN

Run:

npm run explain

You should see SQLite use:

SEARCH tickets USING INDEX IDX_tickets_concertId
SEARCH reservations USING INDEX IDX_reservations_pending_expiresAt

That proves the concert ticket search and pending cleanup query are using the intended indexes.