Build the mental model
The project now becomes one repeatable migration rather than isolated snippets. Create tables in dependency order and apply consistent identity keys, foreign keys, checks, uniqueness, and timestamps. The migration must build a blank database, with deliberate behavior on reruns.
Connect it to a real scenario
Put the schema and core tables in `001_initial_schema.sql`. Simple foreign keys alone may not prove that an enrollment and lesson belong to the same tutorial, so choose a composite-key design or transactional function and test the invariant.
Try the working example
CREATE TABLE app.enrollments (
enrollment_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id bigint NOT NULL REFERENCES app.users(user_id) ON DELETE CASCADE,
tutorial_id bigint NOT NULL REFERENCES app.tutorials(tutorial_id) ON DELETE CASCADE,
enrolled_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (user_id, tutorial_id),
UNIQUE (enrollment_id, tutorial_id)
);
CREATE TABLE app.lesson_progress (
enrollment_id bigint NOT NULL,
tutorial_id bigint NOT NULL,
lesson_id bigint NOT NULL,
completed boolean NOT NULL DEFAULT false,
completed_at timestamptz,
PRIMARY KEY (enrollment_id, lesson_id),
FOREIGN KEY (enrollment_id, tutorial_id)
REFERENCES app.enrollments(enrollment_id, tutorial_id) ON DELETE CASCADE,
FOREIGN KEY (tutorial_id, lesson_id)
REFERENCES app.lessons(tutorial_id, lesson_id) ON DELETE CASCADE,
CHECK ((completed AND completed_at IS NOT NULL) OR
(NOT completed AND completed_at IS NULL))
);The relational schema prevents progress from crossing tutorial boundaries.5-minute try-it
Explain why `app.lessons` needs `UNIQUE (tutorial_id, lesson_id)`, add it, and run the migration on a blank database.
One important caution
Happy-path inserts alone do not prove the schema protects invariants; add invalid-relationship tests.
PostgreSQL — Table Basics — PostgreSQL Global Development Group