Model time like you mean it (and commit migrations as one unit)

Some decisions are invisible when you make them and expensive when they surface. Two I now make deliberately, both about time, in different senses.

Never store a timestamp whose meaning is ambiguous#

The default way to store “when did this happen” in a lot of stacks is a bare date-time type. The problem is that a bare date-time does not carry whether it is UTC or local, and then some layer has to guess. In the .NET and Postgres world the guessing happens in the driver, keyed off a “kind” flag on the value, and the day that flag is wrong your timestamps quietly shift by your offset from UTC and every report is subtly off.

So the rule I settled on: every moment-in-time column is a DateTimeOffset mapped to timestamptz, which stores the instant unambiguously, and anything that is genuinely a calendar day with no time (a birthday, a due date) is a DateOnly mapped to date. Never a bare DateTime. The ambiguity is designed out at the column, so no driver ever has to decide what your data means.

The same “design out the sharp edge” instinct applied to migrations themselves: keep them additive. Add a nullable column rather than doing the nullable, backfill, then not-null dance that rewrites and locks a big table. An additive migration is a boring migration, and boring is exactly what you want touching production data.

A timestamp should carry its own meaning. If a column’s value depends on some other flag to be interpreted correctly, that flag will eventually be wrong.

A migration and its snapshot are one commit#

The sharper lesson was operational, and it is a version-control trap more than a database one. The ORM keeps a shared model snapshot file alongside the numbered migrations, describing the current shape of everything. When you generate a new migration, that snapshot updates too.

I once authored a new migration while two earlier migrations were still sitting uncommitted in my working tree. The snapshot on disk now described all three. If I had committed only the newest migration’s files, the snapshot on the branch would reference two migrations that did not exist there: a migration chain that compiles fine and is broken the moment a teammate or a deploy pulls it.

The lesson is to treat the migration files and the model snapshot as a single atomic unit in version control. They are a single change split across several filenames. Commit them together, or you ship a schema history that is internally inconsistent and only fails on someone else’s machine, which is the worst place for it to fail.

(A small operational cousin of this: running two local database instances on different ports makes it very easy to migrate the wrong one. Name your connections so “which database am I about to change” is never a guess.)

None of this is glamorous. But time and migrations are exactly the areas where a quiet, reasonable-looking choice sits harmless for months and then produces a bug that is maddening to trace, because the code is “correct” and the data just means something slightly different than you thought. In practice that is a report reading a few hours wrong for everyone outside UTC, or a schema change that works on your laptop and breaks the moment a colleague or the deploy pulls it.