Project 2 — Seed Data and Reporting

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind Project 2 — Seed Data and Reporting
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

Seed scripts make development and tests repeatable. Use natural unique keys plus `RETURNING` or CTEs instead of guessing generated IDs. With `ON CONFLICT`, update only columns the seed truly owns. Reports may need LEFT JOIN so zero-count groups remain visible.

Connect it to a real scenario

Upsert the PostgreSQL course, lessons, and demo learners, then produce completion percentages. Avoid integer division and divide-by-zero using `100.0` and `NULLIF`.

Make seeds idempotent
Query generated IDs
Test zero, NULL, and integer-division report edges

Try the working example

sql

INSERT INTO app.tutorials (title, slug, is_published, published_at)
VALUES ('PostgreSQL', 'postgresql', true, now())
ON CONFLICT (slug) DO UPDATE
SET title = EXCLUDED.title
RETURNING tutorial_id;

SELECT e.enrollment_id, u.display_name, t.title,
       round(100.0 * count(*) FILTER (WHERE p.completed)
             / NULLIF(count(p.lesson_id), 0), 1) AS completion_percent
FROM app.enrollments e
JOIN app.users u USING (user_id)
JOIN app.tutorials t USING (tutorial_id)
LEFT JOIN app.lesson_progress p USING (enrollment_id, tutorial_id)
GROUP BY e.enrollment_id, u.display_name, t.title;

You should see

You have rerunnable seed data and a learner-completion report.

5-minute try-it

Seed learners at not-started, in-progress, and complete states, then verify report output.

One important caution

Prevent seed upserts from overwriting production user-managed fields by defining environment and column ownership.

PostgreSQL — INSERT — PostgreSQL Global Development Group

Easy traps

Prevent seed upserts from overwriting production user-managed fields by defining environment and column ownership.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Seed learners at not-started, in-progress, and complete states, then verify report output.

You'll know it worked when: You have rerunnable seed data and a learner-completion report.