Cameron Cordes
7d7d7c713c
Neither migration had shipped, so the #115 snapshot columns join the #114 new-contract columns in 2026-09-24-000000_add_true_utc_dates. Pure ADD COLUMN either way — population is runtime (drains), so nothing needs re-migrating. Column order is unchanged, so schema.rs stands.
33 lines
1.8 KiB
SQL
33 lines
1.8 KiB
SQL
-- True-instant date contract (ADR-0001 / spec #113, tickets #114 + #115).
|
||
--
|
||
-- image_exif gains the new-contract columns alongside the untouched legacy
|
||
-- `date_taken`:
|
||
-- date_utc — true UTC instant (unix seconds) under the new
|
||
-- contract; NULL while the row is still on the
|
||
-- legacy convention (drain pending).
|
||
-- camera_offset_minutes — camera local − UTC in minutes that produced
|
||
-- date_utc (NULL for direct-UTC branches).
|
||
-- date_taken_tz_source — why the offset is what it is:
|
||
-- offset_tag | gps | assumed_server | utc_direct
|
||
-- | manual.
|
||
--
|
||
-- Manual date edits (ticket #115) write the new-contract triple alongside
|
||
-- the legacy pair and must be revertible, so image_exif also snapshots the
|
||
-- pre-override new-contract columns next to the existing legacy snapshot
|
||
-- pair (original_date_taken / original_date_taken_source):
|
||
-- original_date_utc / original_camera_offset_minutes /
|
||
-- original_date_taken_tz_source.
|
||
--
|
||
-- All columns are additive and nullable; population happens at runtime
|
||
-- (ingest + the per-tick backfill drains), so no backfill SQL is needed
|
||
-- here. user_ai_prefs gains the DST-proof display zone (IANA name); the
|
||
-- legacy tz_offset_minutes column stays frozen for rollback.
|
||
|
||
ALTER TABLE image_exif ADD COLUMN date_utc BIGINT;
|
||
ALTER TABLE image_exif ADD COLUMN camera_offset_minutes INTEGER;
|
||
ALTER TABLE image_exif ADD COLUMN date_taken_tz_source TEXT;
|
||
ALTER TABLE image_exif ADD COLUMN original_date_utc BIGINT;
|
||
ALTER TABLE image_exif ADD COLUMN original_camera_offset_minutes INTEGER;
|
||
ALTER TABLE image_exif ADD COLUMN original_date_taken_tz_source TEXT;
|
||
ALTER TABLE user_ai_prefs ADD COLUMN display_tz TEXT;
|