Booking backend project · Chapter 1 of 12
The data model
By the end of this chapter you can describe Parda's whole booking system as Postgres tables written in TypeScript with Drizzle: columns with their types and rules, references between tables, unique and check constraints, enums, jsonb and timestamps with a time zone, and you can say why the rule "no seat is sold twice" belongs in the database. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: The data
- 3 tasks
- TypeScript
- Free chapter
Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.
Why you need this
Parda sells tickets for twelve of Pune's own cinemas and stages. Its bookings still run on the old booking script, which keeps every seat in one big file and checks "is E7 free?" before it writes "E7 is Neha's". On Saturday 17 October, the second night of Ghar Wapsi Express at Kala Talkies, two people clicked seat E7 of the 9:30 pm show in the same second. Both checks said free, both writes went in, and both people got a ticket. It happened to 11 more seats that weekend. Datta, who runs support and the box office, sat 12 pairs of people who held the same ticket and refunded 12 of them.
Sayali, the founder, wants Parda's own backend before Diwali week (Friday 6 November): a real API on Postgres that Rukhsana's new React site can call. Nikhil, Parda's senior backend engineer, builds it with you, one chapter at a time. Every chapter starts from his finished code of the chapter before, so you always work in a project that runs.
A backend starts with its data. Before any route can sell a seat, Postgres has to know what a venue, a seat, a showtime and a booking are, and which rules may never break. The old script had no such rules: nothing stopped a second "E7 is someone's". In this chapter you write them down.
The lesson
Tour of the codebase
This is chapter 1, so everything is new. The whole project is in your chapter's folder; the files marked as yours are the only ones you edit.
package.json the scripts: npm run dev, npm run schema:sql
src/server.ts starts the app on port 3000
src/app.ts the Express app (one route so far: /health)
src/config.ts every setting the app reads
src/db/client.ts the connection pool and Drizzle's db object
src/db/schema/index.ts every table, exported from one place
src/db/schema/users.ts the people who log in (finished, a model to copy)
src/db/schema/venues.ts yours: venues, screens, seats (Task 1)
src/db/schema/shows.ts yours: shows, showtimes (Task 2)
src/db/schema/bookings.ts yours: holds, bookings, booking_seats, payments (Task 3)
src/modules/health/ GET /health: is the app up, can it reach Postgres
scripts/schema-sql.ts prints the SQL your tables make
Start the app from the terminal. It runs src/server.ts and restarts when a file changes:
$ npm run dev
> parda-api@1.0.0 dev
> tsx watch src/server.ts
Parda API listening on http://localhost:3000
Send the saved request "Is the app up?". It answers 200 with {"ok":true,"database":"up","counts":null}: Postgres is there, and counts is null because no table exists yet (chapter 2 creates them). "Shows (not built yet)" answers 404, Cannot GET /shows: browsing is chapter 7.
A table is a TypeScript value
Here is the finished users table, from users.ts:
export const roleEnum = pgEnum('role', ['user', 'admin']);
export const users = pgTable('users', {
id: serial('id').primaryKey(),
email: text('email').notNull().unique(),
name: text('name').notNull(),
passwordHash: text('password_hash').notNull(),
role: roleEnum('role').notNull().default('user'),
createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
});
pgTable takes the table's name in Postgres and an object of columns. Each property (passwordHash) is what your TypeScript code writes; the text inside the builder ('password_hash') is the column's name in Postgres. The convention is camelCase in code and snake_case in the database, and the rest of the project uses exactly the names your task gives.
Nothing here talks to Postgres. It is a description that two tools read: Drizzle, to write queries with the right column names and types, and drizzle-kit, to write the SQL that creates the tables. You can see that SQL now:
$ npm run schema:sql
...
CREATE TABLE "users" (
"id" serial PRIMARY KEY NOT NULL,
"email" text NOT NULL,
"name" text NOT NULL,
"password_hash" text NOT NULL,
"role" "role" DEFAULT 'user' NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT "users_email_unique" UNIQUE("email")
);
Every rule you chain onto a builder becomes part of the table: notNull() is NOT NULL, unique() is a named UNIQUE constraint, defaultNow() is DEFAULT now(). Run it again after each task and read what your tables make.
Columns and their rules
The rules are what make the data trustworthy, because Postgres checks them on every insert and update, from every program, forever. To see them work, here is a small table that is not one of yours: the snack counter at Kala Talkies, and the snacks members order with their tickets.
export const snackKindEnum = pgEnum('snack_kind', ['food', 'drink']);
export const snacks = pgTable(
'snacks',
{
id: serial('id').primaryKey(),
name: text('name').notNull().unique(),
kind: snackKindEnum('kind').notNull(),
priceRupees: integer('price_rupees').notNull(),
addedAt: timestamp('added_at', { withTimezone: true }).notNull().defaultNow(),
},
(t) => [check('snacks_price_positive', sql`${t.priceRupees} > 0`)],
);
serial('id').primaryKey(): a whole number Postgres counts up for every new row (1, 2, 3), and the row's identity.textandinteger: Parda keeps money as whole rupees in an integer, never a float: ₹180 is180.- An enum is a list of allowed words.
pgEnumcreates a Postgres type, and a column of that type accepts nothing else. timestamp(..., { withTimezone: true })is Postgres'stimestamptz: a moment in time. 9:30 pm in Pune and 4:00 pm in London are the same moment, and it stays right whatever time zone a server runs in.
Created in a database (the lab's psql), the rules answer like this:
INSERT 0 1
ERROR: duplicate key value violates unique constraint "users_email_unique"
DETAIL: Key (email)=(neha.bapat1005@example.com) already exists.
INSERT 0 2
ERROR: new row for relation "snacks" violates check constraint "snacks_price_positive"
DETAIL: Failing row contains (3, Free popcorn, food, 0, 2026-10-24 01:38:27.24946+00).
ERROR: invalid input value for enum snack_kind: "snack"
The first Neha went in, then a samosa at ₹30 and a cutting chai at ₹15; the second Neha, a free popcorn and a snack kind that is not in the list were refused, each with the name of the rule it broke.
References between tables
A snack order belongs to a member and is for one snack:
export const snackOrders = pgTable(
'snack_orders',
{
id: serial('id').primaryKey(),
userId: integer('user_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
snackId: integer('snack_id')
.notNull()
.references(() => snacks.id),
extras: jsonb('extras').$type<Record<string, boolean>>().notNull(),
},
(t) => [unique('snack_orders_user_snack').on(t.userId, t.snackId)],
);
references makes a foreign key: user_id must be the id of a row that exists in users. It is given as a small function, () => users.id, so that two tables in different files can point at each other in either order. drizzle-kit writes it as a separate statement:
ALTER TABLE "snack_orders" ADD CONSTRAINT "snack_orders_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "snack_orders" ADD CONSTRAINT "snack_orders_snack_id_snacks_id_fk" FOREIGN KEY ("snack_id") REFERENCES "public"."snacks"("id") ON DELETE no action ON UPDATE no action;
What happens when the row a reference points at is deleted is your choice, and it is a business decision:
- The default, no action: the delete is refused while anything still points at the row. Deleting the samosa while an order uses it:
`` ERROR: update or delete on table "snacks" violates foreign key constraint "snack_orders_snack_id_snacks_id_fk" on table "snack_orders" DETAIL: Key (id)=(1) is still referenced from table "snack_orders". ``
onDelete: 'cascade': deleting the row deletes the rows that point at it. Deleting Neha (DELETE 1) took her snack order with her; the count of orders afterwards was 0.
Ask of each reference: if the parent goes, should its children go too, or should Postgres refuse? A screen without its venue means nothing; a booking without its member is money Parda must still account for.
A reference also refuses a row that points at nothing:
ERROR: insert or update on table "snack_orders" violates foreign key constraint "snack_orders_snack_id_snacks_id_fk"
DETAIL: Key (snack_id)=(9) is not present in table "snacks".
Rules about several columns
Neha may order a samosa, and she may order chai, but the counter wants one order line per snack per member. Neither column is unique on its own: many orders have Neha's user_id, many have the samosa's snack_id. The pair may not repeat. That rule lives in the table's third argument, a function that gets the table's columns and returns a list of rules: unique('snack_orders_user_snack').on(t.userId, t.snackId).
ERROR: duplicate key value violates unique constraint "snack_orders_user_snack"
DETAIL: Key (user_id, snack_id)=(1, 1) already exists.
A check constraint goes in the same list: a condition every row must meet, written as raw SQL with Drizzle's sql template so the column's real name is put in for you ("snacks"."price_rupees" > 0 in the SQL above). Give your rules names: the error names them, and later chapters of this project read the name to know which rule broke.
jsonb stores a whole JSON value in one column: $type<...>() tells TypeScript what is inside, and Postgres gives back the same object you stored. A showtime's prices for each seat class are one small object like this, because the classes differ from venue to venue.
Let Postgres say no
Here is the old booking script's mistake, small enough to run. Two requests both order the samosa for Neha. Each one checks first, then inserts. On a busy evening the steps can run in this order: both checks, then both inserts. The script ran exactly that order, once on a table with no unique rule and once on a table with one:
orders_loose: first check free, second check free
first insert: inserted
second insert: inserted
rows: 2
orders_strict: first check free, second check free
first insert: inserted
second insert: error: duplicate key value violates unique constraint "orders_strict_user_id_snack_id_key"
rows: 1
Both checks said free, because when each one ran, the other insert had not happened yet. Checking first cannot fix that: between your check and your insert there is always a moment where someone else can insert. The unique constraint is checked by Postgres inside the insert itself, with every other insert in view, so the second one is refused, whoever sent it and however fast. Your API will still check first, to give a friendly message, but the database rule is what makes the double booking impossible. Chapter 8 builds the seat holds on exactly this.
Your turn
- Task 1, venues.ts: venues, screens and seats. The trap: the seat rule is on the three columns together, not on each.
- Task 2, shows.ts: shows and showtimes, with a check that a show lasts more than 0 minutes and a showtime's prices as jsonb.
- Task 3, bookings.ts: holds, bookings, booking seats and payments. Nikhil will ask: why put "no seat sold twice" in the database as a unique constraint, when the API could check for the seat before it inserts?
The starter files declare every table with only its id, so the project runs from the start. After each task, run npm run schema:sql and read the SQL. Then press Check: it compares your tables with the task, column by column, creates them in a fresh database of its own, and tries to break your rules. A first message looks like this:
venues has no name: the task wants name, the column name (text)What you build: 3 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
venues.ts: venues, screens and seats
Write three Drizzle tables in src/db/schema/venues.ts. Every column is not null unless it says otherwise; the property comes first, then its Postgres column.
venues (the table venues): id, the serial primary key; name (name), text and unique; area (area), text; kind (kind), the given venueKindEnum; createdAt (created_at), timestamptz defaulting to now.
screens (the table screens): id; venueId (venue_id), an integer that references venues.id with on delete cascade; name (name), text; and a unique constraint called screens_venue_name on (venue_id, name).
seats (the table seats): id; screenId (screen_id), an integer that references screens.id with on delete cascade; row (row), text; number (number), integer; class (class), text; and a unique constraint called seats_screen_row_number on (screen_id, row, number).
The Check compares each table with this list, then creates your tables in a fresh database and wants Postgres to refuse a second E7 on one screen, a second venue with the same name and a screen of a venue that does not exist, and to delete a venue's screens and seats with it.
- Task 2
shows.ts: shows and showtimes
Write two tables in src/db/schema/shows.ts. Every column is not null unless it says otherwise.
shows (the table shows): id, the serial primary key; title (title), text; kind (kind), the given showKindEnum; language (language), text; minutes (minutes), integer; certificate (certificate), text; createdAt (created_at), timestamptz defaulting to now; and a check constraint (call it shows_minutes_positive) that minutes is more than 0.
showtimes (the table showtimes): id; showId (show_id), an integer that references shows.id; screenId (screen_id), an integer that references screens.id; startsAt (starts_at) and endsAt (ends_at), both timestamptz; prices (prices), jsonb typed as a Record of string to number; status (status), the given showtimeStatusEnum defaulting to scheduled; and a unique constraint called showtimes_screen_starts on (screen_id, starts_at). Both references keep the default on delete.
The Check compares the tables with this list, then wants Postgres to refuse a show of 0 or -5 minutes and a second showtime on one screen at the same start, to give a new showtime the status scheduled, and to give its prices back as the object stored.
- Task 3 · interview question
Interview: bookings.ts, holds, bookings and payments
Write four tables in src/db/schema/bookings.ts. Every column is not null unless it says otherwise.
holds (the table holds): id; showtimeId (showtime_id) referencing showtimes.id, on delete cascade; seatId (seat_id) referencing seats.id; userId (user_id) referencing users.id, on delete cascade; expiresAt (expires_at), timestamptz; createdAt (created_at), timestamptz defaulting to now; and a unique constraint called holds_showtime_seat on (showtime_id, seat_id).
bookings (the table bookings): id; userId (user_id) referencing users.id; showtimeId (showtime_id) referencing showtimes.id; status (status), the given bookingStatusEnum defaulting to pending; amount (amount) and fee (fee), integers; createdAt (created_at), defaulting to now; confirmedAt (confirmed_at), timestamptz that may be null.
bookingSeats (the table booking_seats): id; bookingId (booking_id) referencing bookings.id, on delete cascade; showtimeId (showtime_id) referencing showtimes.id; seatId (seat_id) referencing seats.id; price (price), integer; and a unique constraint called booking_seats_showtime_seat on (showtime_id, seat_id).
payments (the table payments): id; bookingId (booking_id) referencing bookings.id; eventId (event_id), text and unique; providerPaymentId (provider_payment_id), text; amount (amount), integer; status (status), the given paymentStatusEnum; payload (payload), jsonb; createdAt (created_at), defaulting to now. References with no on delete keep the default.
The Check compares the tables with this list, then wants Postgres to refuse a second hold on one seat of one showtime, a seat sold twice for one showtime and one payment event stored twice, and a new booking to be pending with an empty confirmed_at.
Nikhil will ask: why put "no seat sold twice" in the database as a unique constraint, when the API could check for the seat before it inserts?
What you will learn
- Drizzle tables: a table is a TypeScript value made with pgTable, and its columns are typed builders.
- Column rules: notNull, unique, defaults, and the types Parda needs (serial, text, integer, timestamptz, jsonb, enums).
- References: a column that points at another table's row, and what on delete cascade does.
- Rules about several columns: unique constraints on a pair, and check constraints.
- Why the database guards the rules: two requests that both check before they insert.
More in The data
Build The data model yourself
Parda sells tickets for Pune's own cinemas and stages. You build its booking API, one module a chapter, and take the whole codebase home. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.