Preserving Original Local Time vs UTC-Normalized Storage in Ecto Schemas

When modeling timestamps that must retain a user's original wall-clock time and time zone (for example, a scheduling feature where "2024-03-10 02:30" in America/New_York is distinct from the same wall-clock in Europe/London), two documented Ecto patterns emerge. The built-in :utc_datetime_usec type maps to PostgreSQL timestamptz, which normalizes all input to UTC on write and discards the source offset. The alternative is :naive_datetime_usec (timestamp without time zone) paired with a separate varchar or int column holding the IANA zone name or offset, preserving the exact local representation.

Both approaches work with Ecto 3.10+ and PostgreSQL, but they impose different constraints: timestamptz simplifies range queries and ordering in UTC while requiring application-level zone tracking; the naive-plus-zone column keeps original semantics intact but complicates SQL-level filtering, sorting, and migrations. Custom composite types (e.g., Timex.Ecto.DateTimeWithTimezone) embed zone data in a single column yet remain third-party and adapter-specific.

Which strategy do you adopt when the product requirement is to reconstruct the user's original local time exactly as entered, while still supporting efficient UTC-based analytics queries?