0018. Store source datetimes as UTC instants¶

Date: 2026-09-04 Status: Accepted

Context¶

Conflux had no stated rule for what happens to a datetime that arrives from an external system. Every field was therefore decided on its own, at the point someone first needed it, and the decisions contradicted each other.

Five columns held a vendor datetime string as varchar(255) with no cast: customers.date_of_birth, orders.date_paid, customer_consents.subscribed_at, customer_consents.confirmed_at and customer_consents.unsubscribed_at. Whatever the shop wrote is what the column held, and for orders.date_paid that was WooCommerce's date_paid, the shop-local twin of a field the same payload also carries as date_paid_gmt. Every value in the column was therefore displaced from UTC by the shop's own offset, with nothing recording what that offset was. PrestaShopTransformer never set datePaid, and OrderData::$datePaid defaults to null, so for a PrestaShop connection the column held NULL.

No column ever mixed two platforms' spellings, because none of the five had two writers: only PrestaShop wrote date_of_birth, only WooCommerce wrote date_paid, and nothing wrote the three consent columns at all. The defect was a missing zone and a missing type rather than a collision. A varchar carrying an undeclared zone cannot be sorted, compared across connections, or resolved to an instant, and the type is what let the raw string in unexamined.

AbstractTransformer discarded the source's own timestamps at the persistence boundary. All three canonical DTOs carried createdAt and updatedAt, but none of the fill() arrays named a column for either, so Eloquent stamped its own created_at and updated_at and the source's values went no further than the mapper that read them.

What made that loss invisible is the outbound side. Each projector refilled the DTO's createdAt and updatedAt from the row's own timestamps, so a projection came back with both fields populated and a round trip compared equal. The payload said "created" and meant "first stored by Conflux", and no test could tell the difference, because the value the projector put back was the only value there had ever been to compare against.

The written contract said the same thing on purpose. Until now reference/projection-contract.md specified the opposite semantics as the intended design: that createdAt and updatedAt are the Conflux row's own timestamps, that on the way out they mean when Conflux first stored the record, and that the source's values are dropped on the way in. So this is not a case of documentation lagging behind code. The semantics themselves were wrong, and they were wrong in writing.

Two further facts about the inbound path made the rule necessary rather than merely tidy. WooCommerce sends a _gmt variant of every datetime field and the integration read the local variant throughout, discarding the one unambiguous value on offer. PrestaShop resolved the shop's timezone only when modifiedAfter was set, so a full sync, the run that writes the historical values for the first time, was exactly the run that resolved nothing.

No outbound writer exists yet, which makes this the cheapest window there will ever be. Nothing has to be unwound, and no timezone assumption has been committed to code that a writer would then have to keep honouring. A writer built before this rule would have had to work out what each value it reads actually means, and that depends on the source platform, so "a writer knows one target" would have quietly become "a writer knows one target and every source". Each writer would also have answered the zone question separately, and the answer would have been whatever Carbon::parse() happened to do at that call site.

Decision¶

Six rules govern every datetime Conflux reads from an external system: what it stores, what it passes through, and what it discards. The delta cursor is the only datetime Conflux sends outbound today, and it is a CarbonImmutable wherever Conflux holds it, which removed the last exception to the parse rule. It is rendered to a string in two places: WooCommerceIntegration::deltaFilterParams() puts it in a query parameter, and BulkSyncJob formats it for FetchDeletionLogJob, whose since is still a ?string. The second rendering is an inconsistency rather than a design choice: the deletion log contract landed separately and still takes the cursor as a string. That is worth naming as outstanding, not as though both were intended. What these rules leave open about it is not its zone, which is UTC by construction, but which column the remote platform compares it against. That is settled per integration; see reference/woocommerce-integration.md for WooCommerce's answer and why the query has to say so explicitly.

1. A datetime Conflux stores in a column of its own is parsed at the integration boundary and stored as an instant in UTC.
2. A calendar date is stored as a date and never converted, because it means the same day in every zone.
3. The source zone is established per system connection, never assumed. Where the zone is a fact about the platform, it is a constant in the integration's own code. Where it varies per connection, it lives in system_connections.source_timezone. Where it cannot be established at all, rule 4 applies.
4. A failed parse is an event, never a silent default.
5. What a system connection passes through without storing it in a column of its own is not covered by rule 1, and must not be presented as though it were.
6. A datetime an integration reads and Conflux does not store is recorded as discarded rather than quietly disappearing.

The canonical payload describes the record in the source. That is what settles the naming question the rules raise: the DTO fields stay createdAt and updatedAt, because a canonical record describes an entity as it exists in the shop, and there the source is the only authority for when it was created. Conflux's row timestamps were never part of the payload; they are an artefact of storing it. Conflux's own view of change is a separate signal and stays where it already was, on the delivery envelope as contentChangedAt. The columns keep a source_ prefix while the fields do not, which is the data layer's normal shape rather than an inconsistency: orders_count is already orderCount and total_spent already totalSpent.

The type convention follows from the rules:

Typing the fields is what makes rule 1 checkable rather than merely stated. A string field accepts $data['date_created'] unchanged, so assigning the raw string was the obvious thing to do and the type permitted it. A CarbonImmutable field does not, so a mapper has to parse, and parsing forces the zone question into the open. Rule 5 gains the same benefit from the other direction: leaving passed-through values as strings puts the limit of the promise in the type instead of in a paragraph of documentation.

The ! in the calendar-date cast format is load-bearing. Without it createFromFormat() fills the unparsed time fields from the current wall clock, which would give every birthday the time of the sync. The cast protects the hydration path only, not construction: a CarbonImmutable carrying a time survives being passed to the constructor, and the mappers construct their DTOs directly with named arguments rather than through from(), so the parse context has to force midnight as well. The attribute and the parse context divide the work; neither replaces the other.

The zone is declared or resolved, never assumed. Where it is a platform fact it is a constant, which is the WooCommerce case: WooCommerce sends its _gmt values as 2024-03-01T09:05:00, naive strings with no offset whose zone is statically known, so WooCommerceIntegration::SOURCE_TIMEZONE is 'UTC'. That constant is the zone of the strings Conflux reads, not the zone the shop is in, and that is exactly why it belongs in code rather than in a column. Where the zone varies per connection it is resolved and written to system_connections.source_timezone, which is the PrestaShop case: date_add and date_upd arrive in shop-local time with no offset, so resolveSourceTimezone() reads PS_TIMEZONE on every run and falls back to the value already stored on the connection, never to the application timezone. For PrestaShop, the only reader of that column, a null therefore means exactly one thing: not established, so every instant parsed against it is a failure under rule 4. A wrong zone produces a plausible-looking wrong instant, which is worse than no instant at all.

Carbon::parse() on a source datetime string is banned. It reads an undeclared format by heuristic, and the heuristic turns on the separator:

DateTimeImmutable::createFromFormat('!d.m.Y', '04.12.1985')  ->  1985-12-04
DateTimeImmutable::createFromFormat('!m.d.Y', '04.12.1985')  ->  1985-04-12
Carbon::parse('04.12.1985')                                  ->  1985-12-04
Carbon::parse('04/12/1985')                                  ->  1985-04-12

The same day, written two ways, eight months apart, with no warning. Every integration therefore declares its date and datetime formats as constants and hands them to App\Services\SourceDateTimes, which is the one place a source datetime is parsed. A value that does not match the declared format is a parse failure under rule 4. A format list with an ordered fallback is not permitted, because the order would silently decide the meaning of an ambiguous value. If a platform genuinely sends two formats, that is written down as a fact and overridden per field, rather than built as a fallback chain.

Two things are absent rather than broken, and are treated as such: a field the source did not send, and the literal values 0000-00-00 and 0000-00-00 00:00:00. Both are how a MySQL-derived source spells "no value", not a value it sent incorrectly, and PrestaShop's birthday sentinel for a customer with no birthday is the case that motivates it. They yield null without recording a failure. The check is an explicit list of those two literals rather than a general shape rule, so a third platform spelling its own sentinel differently is not silently absorbed: it reaches the parser, fails, and surfaces as a warning.

Consequences¶

Seven columns are added. customers, orders and products each gain source_created_at and source_updated_at as nullable timestamps, cast immutable_datetime, and system_connections gains source_timezone. The five varchar columns become real types: customers.date_of_birth a date cast immutable_date, and the other four timestamp cast immutable_datetime. The cast choice is itself a rule worth stating, because two kinds now sit in one casts() method and would otherwise look like an oversight: a column that fills a typed DTO field is immutable, while Conflux's own bookkeeping columns (content_changed_at, last_connected_at, disconnected_at) keep 'datetime'.

source_created_at and source_updated_at join SYNC_NOISE_FIELDS in AbstractTransformer, so neither counts as a meaningful change. A shop that re-saves a record without touching anything Conflux mirrors still moves source_updated_at, and that must not move content_changed_at or report the record as updated. Both columns are still written on every sync; they simply do not by themselves make a record deliverable. Putting source_created_at on the list has one deliberate side effect: a shop correcting a record's creation date after the fact does not count as a content change either, which is rare enough to accept for the symmetry.

The outbound contract does not change. createdAt and updatedAt on the DTOs become nullable, because the alternative is the '' fallback that rule 4 forbids, but the projectors prefer the source columns and fall back to the row's own timestamps, so a null from a mapper does not reach a consumer as a null. The nullability is a statement about the inbound path only. The fallback also covers every record written before this decision, whose source columns are null and cannot be recovered, so the payload carries a value there too.

The projectors' own return type stays nullable, and it means it: a row with neither a source column nor a row timestamp projects to null. Nothing guards that case, because a persisted row cannot reach it, Eloquent setting created_at and updated_at on insert. The fallback narrows the gap to a row that does not exist rather than closing it in the type.

The wire format is unchanged. There is no config/data.php, so spatie's default DATE_ATOM applies and an instant serialises as 2024-03-01T09:05:00+00:00, which is what the round-trip test already asserted. A calendar date serialises as a bare 1985-04-12 with no zone at all, so no consumer can shift the day by reading it as UTC midnight and converting westwards.

reference/projection-contract.md is corrected rather than left standing. Its passage on the two fields that "do not survive a round trip" described the defect as the contract. This ADR supersedes no existing ADR, because no ADR ever recorded the old semantics: the only place they were written down was that reference page, and a reference page is corrected in place rather than superseded.

ROUND_TRIP_DROPPED in tests/Integration/Services/ProjectionRoundTripTest.php is now empty, and it stays there as a named constant for that reason. An empty list is the assertion: there is no longer a field the inbound path fails to persist and no projection therefore can return.

This is what makes CFX-203's UTC labelling true. Labelling the admin panel's datetimes as UTC would have been a false statement while orders.date_paid held WooCommerce's shop-local string for every connection that wrote the column at all.

Historical accuracy is lost until a full sync rewrites the values. The migration reads each existing varchar value as UTC and changes the column's type, which introduces no interpretation of its own, but the original zone was never stored and cannot be recovered, so for a shop outside UTC the historical date_paid values are off by the shop's offset until a full sync rewrites them from date_paid_gmt. source_created_at and source_updated_at are not backfilled at all, for the same reason.

The clean-up the migration runs first is destructive rather than merely imprecise. Before each type change it sets to NULL every value MySQL cannot read in the target format, because strict mode would otherwise reject the ->change() over a single malformed row. For the four columns becoming timestamp that NULL-ing is preceded by rewriting a T separator to a space, which is what WooCommerce writes into orders.date_paid; customers.date_of_birth takes no such step, because its %Y-%m-%d target has no time component for a separator to sit in. A value nulled that way is gone from the database rather than shifted by an offset, and only a full sync brings it back. There are no production connections, so both this and the offset above reach local and staging data only.

The failure policy distinguishes a datetime that describes a record from one that is evidence of something, and the distinction is recorded as policy rather than built as a second code path. A datetime that describes a record (createdAt, updatedAt, datePaid, dateOfBirth) is nulled on failure: the record is then less precise but still entirely usable, and skipping it would be worse, because one systematically broken field would stop a whole shop from syncing while every other field was fine. That systematic case is precisely what the aggregated log line makes visible. A datetime that is the evidence of something (the three consent timestamps, of which confirmed_at is the proof of a double opt-in) fails the record instead: nulling it silently turns a confirmed recipient into one with no proof. Everything written today is of the first kind, so the skip path has no caller yet and is deliberately not built ahead of one.

Parse failures are aggregated per run, per domain and per field, and flushed from the integration's export entry point in a finally so a run that throws still reports what it already found. Logging one line per record would put 200,000 identical lines in the log for one systematically broken field on a full sync of 200,000 orders, which buries every other line while saying nothing the count does not. Each line carries the driver, the domain, the field, the zone, the count and the first few source ids. The domain and the field are separate keys rather than one composite value, so an operator filtering a structured log on field=date_add finds the line.

A datetime read only to decide whether a record belongs in a batch is parsed the same way and reported differently, which is why the parse context has a second entry point. PrestaShopIntegration::wasModifiedAfter() compares date_upd against the delta cursor through SourceDateTimes::instantForComparison(): the declared format and the established zone, and no recorded failure. It fails open: a value it cannot read, a zone that was never established, and a field the shop did not send at all each leave the record in the batch. The two errors are not symmetrical here: a record wrongly kept is mapped and then reported as unchanged, whereas a record wrongly dropped is invisible, and a shop whose zone cannot be resolved would look like an empty shop rather than a broken one. An unreadable value and an unestablished zone still surface exactly once, from the mapper that parses the same field of the same record moments later, which is why the filter's entry point is handed no field, domain or source id to report against. Recording either one there as well would inflate every aggregated line by the size of the delta batch. A field the shop did not send at all is different: it is absent rather than broken, so it yields null without recording a failure at either entry point, and nothing about it ever surfaces.

Several alternatives were considered and rejected.

A bespoke CalendarDate value object would model the one thing PHP has no type for, and it was rejected on cost: four files plus tests, for a single field, to prevent an error that has no path today. The two spatie attributes plus the parse context cover the same ground with no new type.

Heuristic parsing was rejected because it is not a shortcut, it is a coin flip. 04.12.1985 is 4 December read as d.m.Y and 12 April read as m.d.Y, and Carbon::parse() picks between them by looking at the separator. A wrong date that parses cleanly is worse than a parse failure, because nothing surfaces it.

Reusing Conflux's own updated_at as the source signal was rejected because the column already means something else. It means "when Conflux last wrote this row", it is rewritten by every full sync along with last_bulk_sync_batch_id, and it carries delta sync and the change signal. Giving it a second meaning would break both readers rather than serve a third.

Storing the zone as a key in meta was rejected because meta is an editable KeyValue field in the Filament form. A hand-set value there would be silently overwritten by the next sync, and a zone that changes without anyone noticing is the failure mode rule 3 exists to prevent. A column of its own is not writable from the form.

A parse-failure counter on TransformationResult was rejected because it sits on the wrong side of the boundary. Parsing happens in the mappers, which the integration calls while fetching; TransformationResult is only constructed afterwards, in AbstractTransformer::transform(). The counter would need new plumbing threaded through the entire fetch path to reach the object that reports it, and the aggregated log line already answers the question it would have answered.