Time and Timestamps: Architecture and Conventions

Valid time records when the fact was true in the real world.

• valid_from: the instant from which this version of the record is authoritative (set by the INSERT trigger to current_timestamp; the application never writes it explicitly).
• valid_to: the instant from which this version is no longer current (set to the "infinity" sentinel 9999-12-31 23:59:59 for the live record; closed to current_timestamp when the record is updated or deleted).

Together they form an open interval [valid_from, valid_to). A query for "current" data filters valid_to = MAX_TIMESTAMP. A query for the state at a historical instant t filters valid_from < t AND valid_to > t=.

The application never writes valid_from or valid_to directly. The database trigger manages both columns. Entity structs therefore declare them as read-only from the application's perspective: valid_from and valid_to are populated on SELECT and ignored on INSERT/UPDATE.

recorded_at is the domain object field that surfaces valid_from to the application layer. The mapper reads it from valid_from on SELECT and never writes it back (the trigger owns it).

Every persisted entity also carries:

The stamp() helper in handler_helpers.hpp fills modified_by, performed_by, and change_reason_code on every write.

All timestamp columns are declared TIMESTAMP WITH TIME ZONE NOT NULL (or nullable where the DB schema allows NULL). PostgreSQL stores timestamps internally as UTC microseconds since 2000-01-01; the timezone is only relevant for display and input parsing. The repositories read and bind timestamps in the binary format, which is that count of microseconds, so no text and no session timezone take part. Every transaction still sets TimeZone to UTC, for the hand-written statements that read a timestamp as text.

A generated repository entity holds each timestamp column as a time_point, or a std::optional<time_point> for a nullable column. The row decoder turns the binary column into the time_point directly, and the statement binds a time_point parameter in the binary format.

• A NOT NULL column has a plain time_point field, so a row that lacks the value fails to decode; it never becomes a default time.
• A nullable column has an optional field, and SQL NULL is std::nullopt.

The canonical time type in ORE Studio. All domain structs use time_point (or std::optional<time_point> for nullable instants).

• UTC by definition (C++20 guarantees system_clock epoch is Unix epoch in UTC).
• Supports arithmetic and comparison.
• Strongly typed — no accidental local/UTC confusion.
• The mapper assigns it from the entity field; there is no conversion.

The writer and the reader of The Wire Shape. The reader accepts the wire shape and the same text with the +00 or +00:00 designator that PostgreSQL states, and throws on a missing UTC designator, an empty string or an invalid date. It never returns the epoch in place of a failure.

Formats a time_point as "YYYY-MM-DD HH:MM:SS.ffffff", six digits of microseconds and no designator, for a text parameter of a hand-written statement. The transaction's TimeZone = UTC makes PostgreSQL read it as UTC.

The generated JSON and MessagePack codecs write and read a time_point field through to_iso8601_utc and from_iso8601_utc, so the grammar is The Wire Shape above and is not restated there:

• Write: time_point → to_iso8601_utc → the wire shape.
• Read: the wire shape → from_iso8601_utc → time_point. It throws on a missing UTC designator.

The microsecond survives every layer. The binary column is a count of microseconds, the entity and the domain hold a time_point, and the wire shape carries six digits. A time_point therefore survives a round trip through the database or the wire to the microsecond, and loses only what a microseconds duration cannot hold.

An instant that falls between two microseconds is taken down to the one it falls in, in every path that writes it: the binary codec, to_iso8601_utc and therefore to_db_string. The rule is one rule because the paths are read against each other: a hand-written statement passes an instant as text and the server parses it, so a lookup by that text must find the row a generated repository wrote through the codec, and the two agree only if they chose the same microsecond.

-- ores.sql/create/utility/utility_functions_create.sql
CREATE OR REPLACE FUNCTION ores_utility_infinity_timestamp_fn()
RETURNS timestamptz AS $$
    SELECT '9999-12-31 23:59:59+00'::timestamptz;
$$ LANGUAGE sql IMMUTABLE;

The function returns an explicit UTC timestamp (with +00 offset) to remain timezone-independent when PostgreSQL optimizes the predicate at plan time. Every SQL file that references the sentinel — triggers, stored procedures, populate scripts, WHERE clauses in raw SQL strings — must call ores_utility_infinity_timestamp_fn(). Grep to audit:

# Any remaining literal usage in SQL is a bug:
grep -rn "'9999-12-31" projects/ores.sql --include="*.sql" | grep -v build

// ores.database/repository/helpers.hpp
inline constexpr const char* MAX_TIMESTAMP = "9999-12-31 23:59:59+00";

MAX_TIMESTAMP is the sentinel as text, for a text parameter of a hand-written statement. Its +00 offset matches ores_utility_infinity_timestamp_fn(), so a comparison does not depend on the session timezone. A statement that can name the sentinel in SQL calls the function instead.

The database INSERT trigger sets valid_from and valid_to on every write, so the sentinel an entity holds before a write never reaches the database. A generated row defaults both members to db_timestamp::max() all the same, so a row read back and a row built in memory mean the same thing by "never".