fix(core): use upsert to prevent FK constraint violations in task DB (#34977)

leosvelperez · nx-cloud[bot] · web-flow · commit 95621cd3294b · 2026-03-24T09:42:26.000-04:00
## Current Behavior

`INSERT OR REPLACE` is used in `task_details` and `cache_outputs`
tables. The bundled SQLite (`libsqlite3-sys`) is compiled with
`SQLITE_DEFAULT_FOREIGN_KEYS=1`, so FK constraints are enforced by
default. `INSERT OR REPLACE` does a DELETE + INSERT on PK conflict, and
the implicit DELETE on `task_details` fails when child rows exist in
`task_history` or `cache_outputs`:

```
NX   DB transaction error: SqliteFailure(Error { code: ConstraintViolation, extended_code: 787 }, Some("FOREIGN KEY constraint failed"))
```

This happens because `record_task_details` is called multiple times with
the same hash across different code paths (`hashTask`, `hashTasks`,
`hashBatchTasks`), and by the second call, child rows already reference
that hash.

## Expected Behavior

Use `INSERT ... ON CONFLICT DO UPDATE` (upsert) which updates the
existing row in-place without deleting it. No DELETE means no FK
violation, while preserving the same idempotent behavior the code relies
on.

---------

Co-authored-by: nx-cloud[bot] &lt;71083854+nx-cloud[bot]@users.noreply.github.com&gt;
diff --git a/packages/nx/src/native/cache/cache.rs b/packages/nx/src/native/cache/cache.rs
@@ -237,7 +237,8 @@ impl NxCache {
     fn record_to_cache(&self, hash: String, code: i16, size: i64) -> anyhow::Result<()> {
         trace!("Recording to cache: {}, {}, {}", &hash, code, size);
         self.db.lock().unwrap().execute(
-            "INSERT OR REPLACE INTO cache_outputs (hash, code, size) VALUES (?1, ?2, ?3)",
+            "INSERT INTO cache_outputs (hash, code, size) VALUES (?1, ?2, ?3)
+             ON CONFLICT(hash) DO UPDATE SET code = excluded.code, size = excluded.size, created_at = CURRENT_TIMESTAMP, accessed_at = CURRENT_TIMESTAMP",
             params![hash, code, size],
         )?;
         if self.max_cache_size != 0 {
diff --git a/packages/nx/src/native/tasks/details.rs b/packages/nx/src/native/tasks/details.rs
@@ -40,7 +40,10 @@ impl TaskDetails {
     pub fn record_task_details(&mut self, tasks: Vec<HashedTask>) -> anyhow::Result<()> {
         trace!("Recording task details");
         self.db.lock().unwrap().transaction(|conn| {
-            let mut stmt = conn.prepare("INSERT OR REPLACE INTO task_details (hash, project, target, configuration) VALUES (?1, ?2, ?3, ?4)")?;
+            let mut stmt = conn.prepare(
+                "INSERT INTO task_details (hash, project, target, configuration) VALUES (?1, ?2, ?3, ?4)
+                 ON CONFLICT(hash) DO UPDATE SET project = excluded.project, target = excluded.target, configuration = excluded.configuration"
+            )?;
             for task in tasks.iter() {
                 stmt.execute(
                     params![task.hash, task.project, task.target, task.configuration],
