I just converted a nullable timestamp + boolean to a plain nullable timestamp but I'd say, in general, there should be a side table for events and state transitions. All too often a "user" table winds up with 15 timestamps for 15 different events and you will eventually find that some of these events happen more than once: for instance, a student applies for an academic program, gets rejected, submits a better application next year, get accepted.
In the case of those email verification requests I think there should be a table that has one row for each verification attempt because you can track the history of the process, something you'll want to do someday because (1) email is wonky and (2) registration is on the critical path for everything. It may still make sense to have an email_verified flag on the user table because this gets looked at a lot and the join on the side table is a hassle. Not least you might need admins to flip that flag on an account for one reason or another.
PaulHoule•3h ago
In the case of those email verification requests I think there should be a table that has one row for each verification attempt because you can track the history of the process, something you'll want to do someday because (1) email is wonky and (2) registration is on the critical path for everything. It may still make sense to have an email_verified flag on the user table because this gets looked at a lot and the join on the side table is a hassle. Not least you might need admins to flip that flag on an account for one reason or another.