Remove pessimistic Entity locking from note indexing and defer relation resolution

[phernandez]

Problem

Concurrent note indexing can deadlock while persisting resolved relations even though relation inserts already use ON CONFLICT DO NOTHING.

Development Logfire issue #2372: 998c31ca-ccc6-4688-96cd-826e01909c57.

Representative trace: concurrent relation persistence deadlock.

Four Markdown files were indexed concurrently. Two relation-persistence transactions became PostgreSQL deadlock victims and the batch ultimately failed after exhausting its retry budget.

Production recurrence

Production Logfire issue #2380: 73b58d08-b86e-46f6-a48b-87c132d53c17.

Observed release: 835e2c78361987722d049d2f2a48d5db854b2ed9.

Representative trace: production relation persistence deadlock.

A bounded 24-hour production scan found 229 DeadlockDetectedError rows across 116 traces, 75 PGQ jobs, and 15 tenants. Seven jobs exhausted retries and ended terminally failed because of this deadlock shape. Representative PGQ job 7398676 encountered eight deadlocks across worker executions before terminal failure. Sibling note writes and completion events committed during failed executions, so the durable S3 source files remained intact while database indexing and relation projections could be partial or stale.

Logfire fragments this lock graph into multiple fingerprints based on the final SQL and parameters. Issue #2380's count of one therefore does not represent the recurrence of the root cause.

Root cause

This is not a duplicate-relation conflict and not an input-order problem inside the final bulk insert.

The current note-indexing transaction:

1. loads the source Entity with SELECT ... FOR UPDATE;
2. replaces that Entity's observations and outgoing relations;
3. eagerly resolves relation targets to other Entity rows;
4. inserts resolved relations whose foreign keys cause PostgreSQL to acquire locks on those target Entity rows.

Concurrent cross-linked notes each hold an exclusive lock on their own source Entity and then touch other transactions' source Entity rows through relation foreign-key validation. That creates a multi-entity lock graph. In the representative trace, the deadlock victims had different from_id values and were blocked by different concurrent transactions; they were not contending on the same unique relation identity.

ON CONFLICT DO NOTHING correctly handles uniqueness conflicts, but it cannot remove the foreign-key lock cycle created earlier by pessimistically locking the source Entity. Sorting relation values or adding another lock only changes the shape of the same transaction graph.

Design direction

Use database-native conflict semantics without constructing a cross-entity pessimistic lock graph:

• Keep uniqueness and idempotency in database constraints and INSERT ... ON CONFLICT DO NOTHING / DO UPDATE.
• Replace the source Entity FOR UPDATE dependency with optimistic checksum, version, or generation compare-and-swap where stale replacement must be detected.
• Treat inserted, existing, updated, and stale/conflict as explicit repository outcomes rather than exception-driven control flow.
• Keep the note transaction owned by one note: persist its Entity, observations, and unresolved outgoing relations without locking referenced Entity rows.
• Resolve cross-note relation targets as separate idempotent projection work after note commits. Resolution should update relation rows, not acquire or mutate ownership of the target Entity.
• Protect deferred work from stale generations so an older resolver cannot overwrite newer note state.

A temporarily unresolved relation that converges is preferable to a valid note write or indexing job failing because concurrent notes reference one another.

Acceptance criteria

• Note indexing no longer requires SELECT ... FOR UPDATE on the source Entity for ordinary replacement.
• Entity replacement uses a database-enforced optimistic precondition when stale writes must be rejected.
• Relation creation remains idempotent through the actual SQLite and PostgreSQL unique constraints and upsert semantics.
• A note transaction can persist an unresolved cross-note relation without locking the target Entity row.
• Relation resolution runs separately, is idempotent, and cannot apply stale generation work.
• A PostgreSQL concurrency regression indexes mutually linked notes with concurrency greater than one and completes without deadlock or exhausted retries.
• PostgreSQL coverage exercises the actual foreign keys and unique constraints.
• SQLite coverage proves unresolved creation, idempotent insertion, later resolution, and stale-write protection have equivalent domain semantics.
• Existing self-relation, deduplication, target-validation, and stale-relation behavior remains explicit and covered.
• No project-wide advisory lock, forced single-worker mode, broad deadlock retry, or deterministic insert ordering is accepted as the root fix.

Related work

• Supersedes Order concurrent relation inserts deterministically to prevent PostgreSQL batch deadlocks #1208, which attributed the failure to input-dependent relation insert order.
• Revisit the locking introduced for Prevent Entity-NoteContent lock-order deadlocks during materialization #1187 / fix(core): lock entity before observation replacement #1202 while implementing this change; consistent pessimistic order can avoid one cycle while still creating a larger cross-entity graph.