Week 037 — Points, Logging, and a Database Column Oversight

Every point transaction should leave a record. Setting that up turned out to require fixing an oversight from when the table was created.

Week of July 28, 2025

Progress

Point transaction logging was implemented: every time points move — item reward allocation, return payout — a record gets written. The implementation was complete by Monday afternoon. Then it started failing.

The transaction table's id column wasn't set to auto-increment. It had been created with an integer primary key but auto-increment wasn't checked. The first insert worked by coincidence (the first ID being whatever value was there). The second failed with a duplicate key error. Enabled auto-increment, logging started working.

The allocated points concept was introduced to distinguish points that are earmarked for a reward from points that are freely available. When a user sets a reward on a lost item, those points are allocated — still in their balance, but not available to spend elsewhere. Available points = total points minus allocated points. The logic for this was implemented in ItemService->reportLost(), then debugged when testing showed it wasn't updating correctly.

By Wednesday, the return item flow was tested end to end and surfaced three simultaneous reward issues: owner-returns-own-item was working, anonymous finder return had a PDO error, and registered finder return had a type error when writing to the points log.

The Hard Part

Testing the return flow with three different finder types at once — owner, anonymous, registered — made it hard to isolate which bug belonged to which case. The approach: fix one case at a time and re-test. The anonymous finder PDO error was traced to a null being passed where the anonymous user's ID was expected. The registered finder type error was a string being passed where an integer was expected in the points log insert.

Also: AI gave incorrect advice about auto-increment this week. Google AI suggested that selecting the PRIMARY KEY checkbox was sufficient for auto-increment. It is not. The AUTO_INCREMENT option is separate and was what was actually missing. Worth verifying AI-provided database configuration advice with the actual tool.

Next Week

Return flow bugs are fixed. Turning attention to production deployment — the application needs to run on a real server before any users can try it.