A sanity query doubles the rate total

The desktop kit holds five files: schema.sql, load.sql, verify.sql, items.csv and a README. In the extracted folder, open sqlite3 with a database file that does not exist yet, then run .read schema.sql, .read load.sql and .read verify.sql, and .quit. Then do it again into a second new file, rehearsal-again.db, without deleting the first.

-- In a terminal, inside the extracted kit folder
sqlite3 rehearsal.db
.read schema.sql
.read load.sql
.read verify.sql
.quit

Two databases built from the same scripts and the same file should agree row for row. That is what reproducible means on the project checklist: anyone who runs your scripts gets your database. Anything you typed only into the shell is missing from the second build, which is how the rebuild catches it.

.bail on, near the top of each script, stops the script at the first error. It is not a rollback: whatever ran before the error stays done, and the file is half built. Start again in a new file rather than rerunning into it. The scripts expect an empty database on purpose; run load.sql into one that is already loaded and its first insert is refused on the duplicate gear type, so nothing is appended twice.

verify.sql ends with a sanity join: each gear type with its count of items and the sum of their daily rates. Trail gear has two items and 18, Water gear one item and 12. When both builds print 18 and 12, that is reassuring, and not much more.

SELECT g.name AS gear_type, COUNT(i.id) AS items, SUM(i.daily_rate) AS rate_sum
FROM gear_types g
LEFT JOIN items i ON i.type_id = g.id
GROUP BY g.id, g.name
ORDER BY g.id;

A teammate's version of the join pairs the tables ON 1 = 1. Every item meets every type, each type reports 30, and the total across both types doubles from 30 to 60. Run it in both builds and they agree perfectly on the wrong numbers. A sanity query that agrees with itself proves nothing; the ON must pair items.type_id with gear_types.id, the key and not a coincidence.

And 18 is a sum of listed daily rates, not revenue. There are no rentals in this database, so no query on it can say what Trail Gear earned. Say what a number is before you compare it.

Compare the strongest evidence first: the ordered rows of items, column by column; the count in every table; the notes that are NULL (two) against the notes that are empty strings (none); PRAGMA foreign_key_check, which should return no rows, and PRAGMA integrity_check, which should say ok; and only then the totals. Then, on a disposable copy, try the inserts that must fail, a duplicate serial, a negative rate and a type with no parent, and paste the exact errors.