When adding a nullable audit-snapshot column to a populated table, prefer fill-once-from-NULL + warn-on-drift over backfill scripts or always-overwrite

Adding a new audit-snapshot column (one whose value should record what was true at link/observation time, e.g. an OAuth identity's verified tenant domain, an order's currency at purchase) to a table that already has rows raises three options: (a) write a backfill script, (b) always overwrite the column on every relevant write, (c) treat it as write-once-from-NULL and warn on later drift. Backfill is often impossible, the data needed to reconstruct historical values isn't on hand. Always-overwriting breaks snapshot semantics: a stored "mozilla.ai" gets clobbered to NULL the next time the user signs in from a device without the Workspace cookie, and you lose the audit signal you wanted in the first place.

The fill-once+warn pattern: on the next legitimate write for an existing row, (1) if stored is NULL and the source asserts a value, set the column and persist; (2) if stored is non-NULL and the source differs (different value or no longer asserted), emit a WARNING log (provider/user/stored/current, not PII) and leave the stored value alone; (3) otherwise no-op. This gives a no-script migration path for pre-existing rows AND surfaces anomalous transitions (employer domain rename, dropped tenancy cookie, etc.) for an operator to investigate without overwriting once-verified data.

Observed once, in a Python/SQLAlchemy codebase persisting OAuth tenant-domain audit captures, 2026-05. The pattern's value is the framing, "audit snapshot ≠ live value; treat NULL→value as a one-way upgrade, surface anything else", not a specific library or column type. Before applying, sanity-check whether the column you're adding is genuinely a snapshot (link-time, observation-time) vs a live value (current state). If it's the latter, plain UPSERT semantics are usually right.

When adding a nullable audit-snapshot column to a table that already has rows, default to fill-once-from-NULL plus warn-on-drift instead of writing a backfill script or always-overwriting on subsequent writes. On the next write for an existing row: fill if stored is NULL and the source asserts a value; log a WARNING (with the entity ID, stored value, and current value, avoid PII) if stored is non-NULL and the source differs; no-op otherwise. Verify the column is genuinely a snapshot (link-time / observation-time, never a lookup key) before applying, for live-value columns, regular UPSERT semantics are correct instead.