Concurrent check-ins for the same day crash instead of staying idempotent

create_checkin's own docstring promises: "Idempotent: re-checking-in the same day returns the existing row rather than raising or creating a duplicate." That promise is only true for requests that don't overlap in time. A mobile app that retries a slow request in the background, a flaky network causing a client to double-submit, or simply a user tapping "check in" twice quickly (a very normal double-tap) can send two requests for the same habit+day close enough together that both read "no existing check-in" before either has written one. The second one then crashes with an unhandled database error -- an ordinary double-tap becomes a 500 instead of the harmless no-op the docstring promises. On-call gets paged for what is, from the user's perspective, nothing going wrong at all.

create_checkin is a check-then-act race: the "does it already exist?" read and the "create it" write are two separate, non-atomic database operations. app/models.py::CheckIn does have a UniqueConstraint("habit_id", "date", name="uq_checkin_habit_date") -- so the data can never actually end up duplicated -- but the second caller's db.commit() raises sqlalchemy.exc.IntegrityError, which nothing here catches. That exception propagates all the way out as an unhandled 500.

cd fastapi/habit_tracker source .venv/bin/activate pytest practice_tickets/tests/test_ticket07_checkin_race.py -v

forces the exact interleaving deterministically (two DB sessions, both performing the

"does this exist?" read before either performs the write) -- see that test file's module

docstring for why real thread timing was not used.

This is a genuine TOCTOU (time-of-check to time-of-use) bug, not a contrived one -- it's already present in the code exactly as written today; nothing needed to be injected to produce it. It's also directly related to TICKET-05 (bulk check-in backfill): any correct fix here is almost certainly the same primitive TICKET-05's create_checkins_bulk should reuse, since a bulk backfill hitting an already-checked-in date is structurally the same "insert that might collide with an existing row" problem, just without real concurrency involved.

There are two standard ways to close a check-then-act race against a uniqueness constraint: (a) catch the IntegrityError that the losing commit raises, roll back, and re-fetch/return the row that won the race instead of letting the exception escape, or (b) use the database's own "insert, but do nothing (or return the existing row) on conflict" support (SQLAlchemy's sqlalchemy.dialects.sqlite.insert(...).on_conflict_do_nothing(...) for this SQLite-backed project) so the race can't produce an exception in the first place. Either is a reasonable, real-world fix; think about what happens to db's session state after a failed commit() if you go with (a) -- a session that just raised on commit needs a rollback() before it can be used again.