Per-field timestamps of client edits survive only in some scenarios #583

Description

In #574, the mock database started tracking modificationDate and along the way, it enabled the step that stores the in-flight record as the last-known server record when a batch is prepared for send. Before that, the step was skipped in production.

We have discussed this step in #386, in particular whether a client edit that wins a merge should carry the time of the edit or the time of the merge. The test clientRecordUpdatedBeforeServerRecord, as it stands now, settles on the time of the edit. The stored in-flight record is what makes that work.

However, I looked at the neighboring scenarios and the approach does not hold up there.

Conflict on Send

The send is rejected and retried after the server change. This is the existing test and differentFieldsChange_conflictOnSend_serverNewer in #414.

This works because the in-flight record stored when preparing the batch already contains isCompleted stamped at 30. When the send is retried, the row and the stored record have the same value for that field, so the field is not restamped.

Conflict on Fetch

Same edits, but the server record is fetched before the client’s record is sent. There is no such test on main yet. It is differentFieldsChange_conflictOnFetch_serverNewer in #414, which is marked as a known issue.

There is no in-flight record on this path. The only copy of the 30 is the row’s userModificationTime, which the merge raises to the server’s time. The send that follows stamps isCompleted with it.

Multiple Edits

The client edits title, the server edits title later, then the client edits priority and the send is rejected and retried. Not covered in #414 yet.

The server’s edit should win title, since it is newer than the client’s. Instead the client’s edit wins, because the in-flight record is built at send time from the row’s single userModificationTime and both client fields thus go out stamped 60. The merge then sees a client value from 60 against a server value from 45. This is basically the three-device scenario from #573 with the in-flight record raising the timestamp.

Takeaway

The last-known server record in its current shape cannot carry per-field timestamps of client edits across all scenarios. It only ever sees one time per row for all fields, and it only sees it on the send path. The stamp of an edit therefore depends on how the sync happened to play out rather than on the edit itself, producing nondeterministic results. And it can still result in an older edit of a field beating a newer one.

I see two ways out:

1. Accept the approximation and stamp client edits with the time of the merge consistently on both paths. The in-flight record would no longer be needed for timestamps and the existing test would change to isCompleted@t=60. This means accepting the three-device scenario from #573 as a known limitation, which it already is today, as shown above.
2. Track modification timestamps for client edits per field separately in the metadata. That would give exact timestamps in all three scenarios.

While 2 is the more correct approach, I don’t think it would be easy to fit into the current shape of the library. Given that, I’d lean towards option 1. At least the behavior would be predictable, regardless of which path the sync takes.

I’d be interested in whether you see the issue the same way and what you feel should be done about it. There might be options I have missed.

Checklist

• I wrote this in my own words, and aimed to be as succinct as possible, even if I used AI to help research its content.
• I have determined whether this bug is also reproducible in a vanilla SwiftUI project.
• I have determined whether this bug is also reproducible in a vanilla GRDB project.
• If possible, I've reproduced the issue using the main branch of this package.
• This issue hasn't been addressed in an existing GitHub issue or discussion.

SQLiteData version information

1.13.0