Postgres in depth · Chapter 64 of 65

Capstone: launching bootcamps

By the end of this chapter you have designed a new part of LCO from an empty schema: tables, the rules as constraints, the data, who may see it, and an index you can prove the planner uses. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Extensions and graduation
  • 5 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.

Why you need this

Kabir is launching bootcamps: live, instructor-led batches with start and end dates, a fixed number of seats and a price. Everyone has a requirement:

  • Kabir: students who finished the right course should be invited automatically, first come, first served, until the seats run out.
  • Priya: an instructor can never run two bootcamps at the same time, and bad data must be impossible, not merely unlikely.
  • Sana: instructors will log in to see their applicants, and must see only their own.
  • Arjun: the support page looks up one student's applications all day; it must stay fast as the table grows.

No new feature this time: this chapter puts the course together. Each requirement maps to something you already know, and the work is choosing well and proving it.

What you build: 5 tasks

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

  1. Task 1

    schema.sql: the bootcamp schema and its rules

    Create a schema bootcamp with two tables.

    bootcamp.bootcamps: id (bigint, generated always as identity, the primary key), slug (text, required, unique), title (text, required), instructor_id (bigint, required, an instructor), starts_on and ends_on (dates, required, ends_on never before starts_on), seats (integer, required, more than 0) and price (price_inr, required). An instructor can never have two bootcamps whose days overlap, counting both the first and the last day.

    bootcamp.applications: bootcamp_id (bigint, required, a bootcamp; deleting the bootcamp deletes its applications), student_id (bigint, required, a student; deleting the student deletes theirs), applied_at (timestamptz, required) and status (text, required, applied by default, and only applied, accepted or rejected). One application per student per bootcamp: the two ids together are the primary key.

    Install btree_gist in the file if it is missing.

  2. Task 2

    launch.sql: the first two bootcamps and their applicants

    Kabir launches two bootcamps: fullstack-bootcamp (Full-Stack Bootcamp, by Kabir Mehta, 2026-11-02 to 2026-12-13, 3 seats, 14999) and data-bootcamp (Data Bootcamp, by Fatima Sheikh, 2026-11-09 to 2026-12-20, 2 seats, 12999). Find the instructors by name.

    Then every student who has not been deleted and has completed React from Zero applies to fullstack-bootcamp, and every one who has completed Python for Data applies to data-bootcamp. applied_at is the moment they completed that course; the status is the default. The Check's copy has the schema from task 1.

    Afterwards: the two bootcamps exist, with exactly those applications.

  3. Task 3

    accept.sql: first come, first served

    For each bootcamp, accept the earliest applications up to its number of seats: order each bootcamp's applications by applied_at, with the student's email as the tie-breaker, and set the status of the first ones (as many as the bootcamp has seats) to accepted. Leave every other application as it is. The Check's copy has the data from tasks 1 and 2.

    Afterwards: each bootcamp has as many accepted applications as it has seats (or all of them, if fewer applied), and they are its earliest.

  4. Task 4

    rls.sql: instructors see only their own applicants

    Instructors connect as the role instructor, and their app sets lco.instructor_email to the instructor's email for the session. Make bootcamp.applications show the role instructor only the applications to bootcamps of the instructor with that email: no rows at all when the setting is missing. The role instructor may read bootcamp.bootcamps and bootcamp.applications (and whatever your policy needs to read), and change nothing. The Check's copy has the data from tasks 1 to 3.

    Afterwards: as instructor with lco.instructor_email set to kabir@lco.dev, bootcamp.applications shows exactly the Full-Stack Bootcamp's applications; with no setting, none.

  5. Task 5 · interview question

    Interview: proof.sql, an index and its proof

    The support page runs this all day: the bootcamp_id and status of every application of the student whose email is diya.patel@example.com. On a realistic amount of data (the Check fills 600 bootcamps and 69,600 applications, as the lesson shows), that query reads the whole table.

    Add an index so the planner uses it for that query, and end the file with EXPLAIN of the query, written as a subquery that finds Diya's id by email, so the file's own output is the proof.

    Afterwards: the plan of that query uses your index on bootcamp.applications.

What you will learn

  • From requirements to tables: names, keys, types, a schema of its own.
  • Rules as constraints: CHECK, UNIQUE, foreign keys, an exclusion constraint on an expression.
  • Data with INSERT ... SELECT and UPDATE ... FROM: look-ups by name, first come first served with a window function.
  • Row-level security driven by a session setting, and the privileges a policy itself needs.
  • An index and its proof: EXPLAIN before and after, on realistic data.

More in Extensions and graduation

Build "Capstone: launching bootcamps" yourself

LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.