Use pessimistic row locking for modification ordering
Context
The git-like modification tree (ADR-0046) requires ordered appending — each new modification must point to the current HEAD, and HEAD must advance atomically. With multiple devices or concurrent requests, two modifications could read the same HEAD and both try to append, creating a fork where none was intended.
Two approaches: optimistic concurrency (version numbers, retry on conflict) or pessimistic locking (lock the row, read-modify-write, release).
Decision
Use PostgreSQL FOR UPDATE row-level locking. When storing a modification, the transaction locks the trip row, reads head_modification_id, inserts the new modification with that as parent_id, updates HEAD, and commits. The lock is transaction-scoped — released automatically on commit or rollback.
SELECT head_modification_id FROM trips WHERE id = $1 FOR UPDATE
Same pattern used in version switching.
Consequences
No retry logic needed — concurrent writers queue on the lock and execute sequentially. Simple to reason about. The trade-off is that concurrent modifications to the same trip serialize (one waits for the other), but trip edits are infrequent enough that this is negligible.