Ordering backend project · Chapter 1 of 12

The data model

By the end of this chapter you can describe DabbaDrop's whole ordering system as Postgres tables written in Python with SQLAlchemy: columns with their types and rules, foreign keys between tables, unique and check constraints, enums, JSONB and timestamps with a time zone, and you can say why a rule like "one payment per event" 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
  • Python
  • Free chapter

Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.

Why you need this

DabbaDrop cooks home-style lunch in three Mumbai kitchens and its riders carry the dabbas to offices and homes. Orders still arrive on WhatsApp and go into a shared sheet. The sheet has no idea how many dabbas a rider can carry. On Monday 19 October it took 52 orders for the 12:30 Goregaon slot, whose two riders carry 40. Twelve lunches reached people after 2:15 pm, cold, and Rafiq, who keeps the accounts, refunded all twelve.

Farah, the founder, wants DabbaDrop's own ordering API before launch week (Monday 2 November): members order a dabba for a half-hour delivery slot, and the API, not a person reading a sheet, decides what is allowed. Kunal, DabbaDrop's senior developer, 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 take an order, Postgres has to know what a kitchen, a dish, a slot and an order are, and which rules may never break. The sheet had no rules: nothing stopped a second row for the same payment, a dish at 0 rupees or a slot for 0 dabbas. In this chapter you write the rules down, as tables.

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.

pyproject.toml              the packages and their versions
app/__main__.py             python -m app: the server on port 8000, with reload
app/main.py                 create_app(): the FastAPI app (one route so far)
app/config.py               every setting the app reads
app/db/base.py              Base: every table class inherits from it
app/db/session.py           the engine (the pool of connections) and get_db
app/db/models/__init__.py   every table class, imported from one place
app/db/models/users.py      the people who log in (finished, a model to copy)
app/db/models/kitchens.py   yours: Kitchen, Dish (Task 1)
app/db/models/delivery.py   yours: Zone, Slot, Subscription (Task 2)
app/db/models/orders.py     yours: SlotHold, Order, OrderItem, Payment (Task 3)
app/modules/health/         GET /health: is the app up, can it reach Postgres
scripts/schema_sql.py       prints the SQL your tables make

Start the app from the terminal. It runs uvicorn on port 8000 and restarts it when a file in app/ changes:

$ python -m app
INFO:     Will watch for changes in these directories: ['/workspace/chapters/w-lead-01/app']
INFO:     Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)
INFO:     Started reloader process [10265] using WatchFiles
INFO:     Started server process [10268]
INFO:     Waiting for application startup.
INFO:     Application startup complete.

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). "Dishes (not built yet)" answers 404, {"detail":"Not Found"}: browsing the menu is chapter 7.

A table is a Python class

Here is the finished users table, from users.py:

Role = Enum("member", "admin", name="role")


class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String, unique=True)
    name: Mapped[str] = mapped_column(String)
    password_hash: Mapped[str] = mapped_column(String)
    role: Mapped[str] = mapped_column(Role, server_default="member")
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

The class inherits from Base (app/db/base.py, SQLAlchemy's DeclarativeBase), and __tablename__ is the table's name in Postgres. Each attribute is a column: the annotation (Mapped[str]) is what your Python code gets back, and mapped_column says what Postgres stores. The attribute's name is the column's name, snake_case in both.

The annotation does more than help your editor. Mapped[str] makes the column NOT NULL; Mapped[str | None] would let it be empty. You will use that in Task 3.

Nothing here talks to Postgres. It is a description that two tools read: SQLAlchemy, to write queries with the right columns and types, and the code that creates tables (in this chapter Base.metadata.create_all, from chapter 2 Alembic). You can see the SQL it makes now:

$ python -m scripts.schema_sql
CREATE TYPE role AS ENUM ('member', 'admin');

CREATE TABLE zones (
	id SERIAL NOT NULL, 
	PRIMARY KEY (id)
);
...
CREATE TABLE users (
	id SERIAL NOT NULL, 
	email VARCHAR NOT NULL, 
	name VARCHAR NOT NULL, 
	password_hash VARCHAR NOT NULL, 
	role role DEFAULT 'member' NOT NULL, 
	created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, 
	PRIMARY KEY (id), 
	UNIQUE (email)
);

Your starter classes have only their id, so they make tables of one column. Every rule you add becomes part of a table: unique=True is a UNIQUE constraint, server_default=func.now() is DEFAULT now(), and an integer primary key is SERIAL, a number Postgres counts up for every new row. 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 chutneys a kitchen could sell beside a dabba.

ChutneyKind = Enum("sweet", "spicy", name="chutney_kind")


class Chutney(Base):
    __tablename__ = "chutneys"
    __table_args__ = (CheckConstraint("price > 0", name="chutneys_price_positive"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String, unique=True)
    kind: Mapped[str] = mapped_column(ChutneyKind)
    price: Mapped[int] = mapped_column(Integer)
    added_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
  • String is text (VARCHAR with no length, the same as TEXT in Postgres). Integer: DabbaDrop keeps money as whole rupees in an integer, never a float: ₹140 is 140.
  • An enum is a list of allowed words. Enum(..., name="chutney_kind") creates a Postgres type, and a column of that type accepts nothing else.
  • DateTime(timezone=True) is Postgres's timestamptz: a moment in time. 12:30 pm in Mumbai and 7:00 am in London are the same moment, and it stays right whatever time zone a server runs in.
  • server_default is a default Postgres fills in when an insert leaves the column out. Its cousin default= is filled in by SQLAlchemy in Python, so a row inserted by psql or another program would not get it: the tasks want server_default.

The SQL it makes:

CREATE TYPE chutney_kind AS ENUM ('sweet', 'spicy');

CREATE TABLE chutneys (
	id SERIAL NOT NULL, 
	name VARCHAR NOT NULL, 
	kind chutney_kind NOT NULL, 
	price INTEGER NOT NULL, 
	added_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, 
	PRIMARY KEY (id), 
	CONSTRAINT chutneys_price_positive CHECK (price > 0), 
	UNIQUE (name)
);

Created in a database, the rules answer like this in psql:

INSERT 0 1
ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=(tara.shetty@example.com) already exists.
INSERT 0 2
ERROR:  new row for relation "chutneys" violates check constraint "chutneys_price_positive"
DETAIL:  Failing row contains (3, Free chutney, sweet, 0, 2026-10-24 09:21:00.598662+05:30).
ERROR:  invalid input value for enum chutney_kind: "garlicky"

Tara went in, then a green chutney at ₹20 and an imli chutney at ₹25; a second Tara, a free chutney and a kind that is not in the list were refused, each with the name of the rule it broke. Postgres named the unique email users_email_key itself; name your own rules, because the error names them and later chapters read the name.

Foreign keys between tables

A chutney order belongs to a member and is for one chutney:

class ChutneyOrder(Base):
    __tablename__ = "chutney_orders"
    __table_args__ = (UniqueConstraint("user_id", "chutney_id", name="chutney_orders_user_chutney"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"))
    chutney_id: Mapped[int] = mapped_column(ForeignKey("chutneys.id"))
    extras: Mapped[dict[str, Any]] = mapped_column(JSONB)

ForeignKey("users.id") makes user_id point at a row of users: it must be the id of a row that exists. It is written as text, the table and the column, so a class can point at a table defined in another file. The column's type comes from the column it points at (an integer). A reference also refuses a row that points at nothing:

ERROR:  insert or update on table "chutney_orders" violates foreign key constraint "chutney_orders_chutney_id_fkey"
DETAIL:  Key (chutney_id)=(9) is not present in table "chutneys".

What happens when the row a foreign key 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 green chutney while an order uses it:

`` ERROR: update or delete on table "chutneys" violates foreign key constraint "chutney_orders_chutney_id_fkey" on table "chutney_orders" DETAIL: Key (id)=(1) is still referenced from table "chutney_orders". ``

  • ondelete="CASCADE": deleting the row deletes the rows that point at it. Deleting Tara (DELETE 1) took her chutney order with her; the count of orders afterwards was 0.

Ask of each foreign key: if the parent goes, should its children go too, or should Postgres refuse? A dish without its kitchen means nothing; an order without its slot is money Rafiq must still account for.

JSONB stores a whole JSON value in one column, and Postgres gives back the same object: psql showed {"extra_spicy": true}, and Python gets a dict. A payment provider's message is one such object.

Rules about several columns

Tara may order green chutney, and she may order imli, but the kitchen wants one line per chutney per member. Neither column is unique on its own: many orders have Tara's user_id, many have the green chutney's chutney_id. The pair may not repeat. A rule about several columns is not on any one column: it goes in __table_args__, a tuple of rules for the whole table, here UniqueConstraint("user_id", "chutney_id", name="chutney_orders_user_chutney"):

ERROR:  duplicate key value violates unique constraint "chutney_orders_user_chutney"
DETAIL:  Key (user_id, chutney_id)=(1, 1) already exists.

A check constraint goes in the same tuple: a condition every row must meet, written as a little SQL with the table's own columns (CheckConstraint("price > 0", name=...)).

Let Postgres say no

Here is the shared sheet's mistake, small enough to run. A payment provider sends DabbaDrop a message for every payment, with an event id, and sometimes sends the same message twice. Two copies arrive together. Each one looks for the event first, then stores it. On a busy lunchtime the steps can run in this order: both looks, then both inserts. This script ran exactly that order on two connections, once on a table with no unique rule and once on a table with one:

events_loose: first check new, second check new
  first insert: stored
  second insert: stored
  rows: 2
events_strict: first check new, second check new
  first insert: stored
  second insert: error: duplicate key value violates unique constraint "events_strict_event_id_key"
  rows: 1

Both looks said new, because when each one ran, the other insert had not happened yet. Looking first cannot fix that: between your look 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 look first when it wants a friendly message, but the database rule is what makes the second copy impossible. Chapter 9 builds the payment webhook on exactly this.

One rule cannot be a constraint: "at most 40 dabbas in the 12:30 slot" is about many rows at once. Chapter 8 keeps it with a lock instead.

Your turn

  • Task 1, kitchens.py: kitchens and their dishes. The trap: a dish's name is unique within its kitchen, the pair, not the name alone.
  • Task 2, delivery.py: zones, slots and subscriptions, with checks that a slot carries more than 0 dabbas and ends after it starts, and a date for a subscription's first and last day.
  • Task 3, orders.py: slot holds, orders, order items and payments, and an order's confirmed_at that may be empty. Kunal will ask: why put rules like "one payment per event id" in the database as a unique constraint, when the API could look for the event before it inserts?

The starter classes have only their id, so the project runs from the start. After each task, run python -m scripts.schema_sql and read the SQL. Then press Check: it compares your classes 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:

kitchens has no column name: the task wants name (text)

and a rule Python fills in instead of Postgres looks like this:

dishes.available has no server_default (default= is filled in by Python, not by Postgres); the task wants Postgres to default it to true

What you build: 3 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    kitchens.py: kitchens and their dishes

    Write two SQLAlchemy table classes in app/db/models/kitchens.py. Every column is not null unless it says otherwise.

    Kitchen (the table kitchens): id, the integer primary key; name, text and unique; area, text; created_at, timestamptz that Postgres defaults to now().

    Dish (the table dishes): id; kitchen_id, an integer that references kitchens.id with ondelete CASCADE; name, text; category, the given DishCategory enum; price, an integer; available, a boolean that Postgres defaults to true; and in __table_args__ a unique constraint called dishes_kitchen_name on (kitchen_id, name) and a check constraint called dishes_price_positive, price > 0.

    The Check compares each class with this list, then creates your tables in a fresh database and wants Postgres to refuse a second Dal Tadka on one kitchen's menu (while another kitchen may list it), a dish of 0 rupees, a second kitchen with the same name and a dish of a kitchen that does not exist, to fill in available as true, and to delete a kitchen's dishes with it.

  2. Task 2

    delivery.py: zones, slots and subscriptions

    Write three classes in app/db/models/delivery.py. Every column is not null unless it says otherwise.

    Zone (the table zones): id, the integer primary key; name, text and unique; kitchen_id, an integer that references kitchens.id.

    Slot (the table slots): id; zone_id, an integer that references zones.id; starts_at and ends_at, both timestamptz; capacity, an integer; status, the given SlotStatus enum that Postgres defaults to open; and in __table_args__ a unique constraint called slots_zone_starts on (zone_id, starts_at) and two check constraints: slots_capacity_positive (capacity > 0) and slots_ends_after_starts (ends_at > starts_at).

    Subscription (the table subscriptions): id; user_id, an integer that references users.id with ondelete CASCADE; plan, text; starts_on and ends_on, both dates; created_at, timestamptz defaulting to now(); and a check constraint called subscriptions_dates, ends_on >= starts_on. The references to kitchens.id and zones.id keep the default on delete.

    The Check compares the classes with this list, then wants Postgres to refuse a second zone called Goregaon, a slot for 0 dabbas, a slot that ends before it starts, a second Goregaon slot at the same start, deleting a kitchen a zone still uses and a subscription that ends before it starts; a new slot to be open with a starts_at that keeps its time zone; and a member's subscription to go when the member does.

  3. Task 3 · interview question

    Interview: orders.py, holds, orders and payments

    Write four classes in app/db/models/orders.py. Every column is not null unless it says otherwise.

    SlotHold (the table slot_holds): id; slot_id referencing slots.id, ondelete CASCADE; user_id referencing users.id, ondelete CASCADE; expires_at, timestamptz; created_at, timestamptz defaulting to now(); and a unique constraint called slot_holds_slot_user on (slot_id, user_id).

    Order (the table orders): id; user_id referencing users.id; slot_id referencing slots.id; status, the given OrderStatus enum that Postgres defaults to pending; amount and delivery_fee, integers; created_at, defaulting to now(); confirmed_at, timestamptz that may be null.

    OrderItem (the table order_items): id; order_id referencing orders.id, ondelete CASCADE; dish_id referencing dishes.id; quantity and price, integers; a unique constraint called order_items_order_dish on (order_id, dish_id) and a check constraint called order_items_quantity_positive, quantity > 0.

    Payment (the table payments): id; order_id referencing orders.id; event_id, text and unique; provider_payment_id, text; amount, integer; status, the given PaymentStatus enum; payload, JSONB; created_at, defaulting to now(). References with no ondelete keep the default.

    The Check compares the classes with this list, then wants Postgres to refuse a second hold by one member on one slot, an item of quantity 0, one dish twice in an order and one payment event stored twice, a new order to be pending with an empty confirmed_at, the payload to come back as the JSON object stored, a dish in an order not to be deleted, and an order's items to go with it.

    Kunal will ask: why put rules like "one payment per event id" in the database as a unique constraint, when the API could look for the event before it inserts?

What you will learn

  • SQLAlchemy table classes: a table is a Python class with Mapped columns; mapped_column says what Postgres stores.
  • Column rules: not null from the type hint, unique, defaults Postgres fills in (server_default), and the types DabbaDrop needs (integer, text, boolean, date, timestamptz, JSONB, enums).
  • Foreign keys: a column that points at another table's row, and what ondelete="CASCADE" does.
  • Rules about several columns: named unique and check constraints in __table_args__.
  • Why the database guards the rules: two requests that both look before they insert.

More in The data

Build The data model yourself

DabbaDrop delivers tiffins across Mumbai. You build its ordering 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.