11 minutes / published
Schema First: Flyway, Exclusion Constraints, and Letting the Database Do Its Job
Why Phase 2A writes SQL before entities, and why the no-overlap rule belongs in the database.
Start With The Invariant
A showtime API is not a CRUD table with movie_id and starts_at. The real invariant is that one screen cannot be occupied by two showtimes at the same time.
Once that sentence is clear, the schema has a job: make the invalid state impossible even if application code takes the wrong path.
Why The Window Is Stored
The occupancy window is stored as starts_at and occupied_until because the database constraint must be row-local. It cannot join to the movie table to calculate duration, and history should not move when a runtime is corrected later.
That makes movie_duration_minutes_snapshot and cleaning_buffer_minutes boring but important. They record the booking fact at creation time.
Postgres Gets A Native Constraint
Postgres can enforce no overlaps with an exclusion constraint over screen_id equality and a half-open tstzrange overlap check.
The btree_gist extension is not decoration. It is what lets the scalar screen_id comparison live in the same GiST constraint as the time range.
The Planner Is Part Of The Lesson
Phase 2A also adds seed data and an EXPLAIN artifact. The goal is not to force an index hit. The goal is to learn what the planner chose and why.
If the data set is small enough that a sequential scan is cheaper, the transcript should say so. That is a better SQL lesson than pretending every index proves itself immediately.
Boot 3 To 4 Delta
The main Spring discipline is unchanged: do not use ddl-auto update as a schema authoring tool. Flyway owns the migration history.
Boot 4 changes surrounding defaults and dependency versions, but the senior habit is older than the framework line: write down the invariant, encode it in SQL, and test the behavior against the database you actually use.