bug: getDerivateByLink callers bypass computedOrchestrator record locking

[dkindlund]

Bug Description

LinkService.getDerivateByLink() is called directly by several code paths that do NOT go through ComputedOrchestratorService.lockImpactedRecords(). This means these operations modify junction table data and link field JSONB without acquiring FOR UPDATE row-level locks, creating a window for concurrent corruption.

Affected Code Paths

Caller	File	Line	Operation
RecordDeleteService.deleteRecords	record-delete.service.ts	57	Record deletion
RecordCreateService.createRecords	record-create.service.ts	86	Record creation
FieldConvertingService	field-converting.service.ts	1002, 1066	Field type conversion

In contrast, RecordUpdateService.updateRecords correctly goes through the orchestrator:

RecordUpdateService.updateRecords
  └── computedOrchestrator.computeCellChangesForRecords
        └── lockImpactedRecords()  ← acquires FOR UPDATE locks
              └── update callback
                    └── linkService.planDerivateByLink + commitForeignKeyChanges

But the three callers above use getDerivateByLink directly:

RecordDeleteService.deleteRecords
  └── linkService.getDerivateByLink()  ← NO locking
        └── saveForeignKeyToDb()       ← modifies junction/FK data unlocked

Impact

Under concurrent API load:

• Record deletion: Deleting a record while another request is updating its links can leave orphaned junction rows or corrupt the symmetric side's JSONB
• Record creation: Creating records with link values while another request modifies the same foreign records can produce duplicate junction rows
• Field conversion: Converting a link field type while records are being updated can leave inconsistent junction/JSONB state

Technical Details

getDerivateByLink calls getDerivateByCellContexts with persistFk=true, which calls saveForeignKeyToDb. For ManyMany relationships, this invokes saveForeignKeyForManyMany which (even with the locking fix in PR #2679) only locks foreign records — the source records are not locked. The orchestrator's lockImpactedRecords would lock both source and computed-impact records, providing complete protection.

Suggested Approach

This is an architectural decision with multiple valid approaches:

1. Add locking within getDerivateByLink itself: Lock both source and foreign records before calling saveForeignKeyToDb. This is the simplest fix but duplicates logic from the orchestrator.

2. Route callers through the orchestrator: Modify RecordDeleteService, RecordCreateService, and FieldConvertingService to use the orchestrator's locking infrastructure. This is cleaner but requires refactoring how these services structure their transactions.

3. Extract locking into a shared utility: Create a LinkLockService that both the orchestrator and getDerivateByLink can call, ensuring consistent locking regardless of the entry point.

We leave the implementation choice to the maintainers, but wanted to document the gap.

Related PRs

• PR fix: add foreign record locking to ManyMany, OneMany, and OneOne link operations #2679: Adds lockForeignRecords to saveForeignKeyForManyMany (partial mitigation — locks foreign records but not source records)
• Commit 77a8291d2: Original FOR UPDATE locking fix (only covered saveForeignKeyForManyOne)

Client Information

• OS: Linux (Docker, Google Cloud Run)
• Database: PostgreSQL 17

Platform

Docker standalone (Google Cloud Run)