bug(sync): pull halts permanently on a FOREIGN KEY violation from an orphaned remote observation

[Alexso9410]

📝 Bug Description

Autosync's pull phase halts permanently when a remote observation or user prompt refers to a session_id that does not exist locally. The resulting FOREIGN KEY constraint failed error aborts the entire pull cycle, leaves last_pulled_seq frozen, and blocks all subsequent remote mutations from being applied.

🔄 Steps to Reproduce

1. On the cloud instance, have an observation or user_prompt row whose session_id never had its corresponding session mutation synchronized to the local client.
2. Enable autosync on the client (ENGRAM_CLOUD_AUTOSYNC=1).
3. Wait for a pull cycle to attempt to apply the orphaned observation/prompt.
4. Observe the FK error and the last_pulled_seq cursor remaining stuck at its previous value.

Minimal real-world scenario observed:

• last_pulled_seq frozen at 16284.
• ~1200 remote mutations pending behind the failing one.
• Error repeats deterministically every cycle.

✅ Expected Behavior

Pull should defer or quarantine any FK miss—not only FK misses for relations—advance the cursor, and log the incident. This would mirror the safety net that push already provides for irreparable pending mutations.

❌ Actual Behavior

[autosync] phase=degraded last_error="pull: apply pulled mutation seq=16311: constraint failed: FOREIGN KEY constraint failed (787)"

ApplyPulledMutation does defer FK misses via sync_apply_deferred, but that mechanism currently covers only relations FK misses. For observation and prompt entity types the error propagates and aborts the whole pull cycle. The cursor does not advance, no quarantine occurs, and the client never recovers automatically.

On the same host, push behavior contrasts sharply:

[autosync] quarantined 141 irreparable pending mutation(s)

Pull has no equivalent quarantine path.

🖥️ Environment

• Operating System: Linux (Ubuntu client) / LXC (cloud server)
• Engram Version: v2.0.0-rc.11 (both client and cloud)
• Agent / Client: OpenCode
• Cloud topology: Engram Cloud self-hosted in LXC, binary v2.0.0-rc.11, behind a TLS reverse proxy with hostname engram-cloud.lan.
• Real scenario: Multiple machines (Windows + Linux) syncing to the same cloud; content in Spanish with accents; years of history; ~1500 mutations in queue.

📋 Relevant Logs

[autosync] phase=degraded last_error="pull: apply pulled mutation seq=16311: constraint failed: FOREIGN KEY constraint failed (787)"

💡 Additional Context

Workaround applied and verified:
Manually insert the missing sessions into the local sessions table (id, project, directory, started_at, ownership_mode) so the FK is satisfied. After doing this, pull advanced from 16284 to 17798 and push completed 3565/3565.

Root cause:
The local schema has FK constraints such as observations.session_id -> sessions.id and user_prompts.session_id -> sessions.id. A remote mutation for an observation/prompt can arrive before (or without) its parent session mutation, creating an orphaned row. The pull applier treats this as a fatal error instead of a deferrable/quarantinable condition.

[dnlrsls]

Thanks for the detailed report. I confirmed the gap against current main: pull-side evidence handling covers relation FK misses and malformed session/observation identities, but a missing parent session for an observation or prompt can still surface as a raw SQLite FK error and pin the pull cursor.\n\nApproving this narrowly: add evidence-preserving defer/quarantine behavior for missing-parent FK failures on pulled observations and prompts, advance the cursor, and cover both entity types plus a later mutation continuing successfully.