​

Upsert

​

When to use it

1. Idempotent ingestion — an external producer (payment processor, CSV import, message-queue consumer) sends events with stable IDs that you want to converge to, not duplicate
2. Cache rehydration — re-deriving a projection from a source-of-truth stream where each event already carries the resulting row state
3. CRDT-style materializations — when the input fully describes the desired state and you don’t care whether the row was new

​

Eligibility

// ✅ Eligible — client supplies the id.
model Tag {
  id Uuid @id
  label String
}

// ❌ Not eligible — server generates the id, so no conflict target
// is reachable by the caller.
model Account {
  id Cuid @id @default(cuid())
  ownerEmail String
}

// Upsert on a composite unique key instead of the primary key.
let setting = cool
    .ownerSetting()
    .upsert(CreateOwnerSettingInput { owner_id, provider, value })
    .on_conflict(ConflictTarget::columns(&["owner_id", "provider"]))
    .run(&ctx)
    .await?;

​

Programmatic use

// Server (sqlx) — async, scoped to a request context.
let tag = cool
    .tag()
    .upsert(CreateTagInput {
        id: external_id,
        label: payload.label,
    })
    .run(&ctx)
    .await?;

// Or pre-bound for a request-scoped delegate:
let tag = cool
    .tag()
    .bind(ctx)
    .upsert(CreateTagInput { id, label })
    .run()
    .await?;

// Embedded (rusqlite) — sync, no policy/audit layer.
let tag = delegate
    .upsert(CreateTagInput { id, label })
    .run()?;

for _ in 0..3 {
    delegate.upsert(input.clone()).run()?;
}
// Exactly one row, with the final input's values.

​

.do_nothing(): converge without overwriting

use cratestack::UpsertOutcome;

let outcome: UpsertOutcome<CashInClaim> = cool
    .cashInClaim()
    .upsert(CreateCashInClaimInput { id: idempotency_key, status: Status::Pending, amount })
    .do_nothing()
    .run(&ctx)
    .await?;

match outcome {
    UpsertOutcome::Inserted(claim) => {
        // First time we've seen this key — proceed with the cash-in.
    }
    UpsertOutcome::Existing(claim) => {
        // Already in flight (or already COMPLETED) — the stored row is
        // untouched, so a retry can never blank out `claim.status`.
    }
}

​

Server semantics

1. Validate input — schema-derived validators (@length, @regex, …) run before any SQL
2. Apply create defaults — @default(auth().*) and @default(...) columns are filled in
3. Evaluate create policies — @@allow(create, …) and @@deny(create, …) must permit the call, against the input values plus defaults
4. Begin transaction, ensure outbox / audit tables exist
5. Probe with SELECT … FOR UPDATE on the primary key — this both predicts insert vs. update and serializes concurrent upserts on the same key
6. If the probe found a row → evaluate the update policy against the live row, capture the before snapshot, and run DO UPDATE. Denial is indistinguishable from a missing row, matching ordinary .update(...) semantics.
7. If the probe found no row → execute INSERT … ON CONFLICT (<target>) DO NOTHING RETURNING …, so the database itself answers “did I actually insert?”
8. A returned row means a genuine insert — Created, AuditOperation::Create, no before snapshot, one statement, no extra round trip
9. No returned row means the probe lost a race, and the winning row has not been touched yet. The runtime re-enters the update branch from the top, re-running the same probe — which blocks until the winning transaction commits, so it reads that transaction’s final data:
   • Re-probe finds the winner (the ordinary case) → run the update policy gate, capture a real before snapshot, then DO UPDATE. Outcome is Updated / AuditOperation::Update with the winner’s row as before
   • Re-probe still finds nothing → the conflict is real but invisible to the probe. DO UPDATE runs without the update policy gate and the outcome is reported as Inserted / Created with no before. Two causes: the winning row was deleted again between the two statements (in which case the statement really did insert, and Inserted is correct), or a soft-delete tombstone sits at the conflict target — see below, where that second case is a known defect rather than the correct answer
10. Enqueue the event and the audit entry reflecting what the database actually did, then commit and drain the outbox

​

Policies: both must allow

1. write @@allow(create, …) and @@allow(update, …) so the intersection of permitted callers is exactly the set you want to be able to upsert
2. don’t reach for .upsert(...) on models where create and update audiences are deliberately disjoint — that’s a sign the operation wants to be split into separate create / update routes

​

@version is bumped, but if_match isn’t honored

​

.do_nothing()’s policy, event, and concurrency contract

1. Create policy still gates the insert branch unconditionally, same as .create() and the DO UPDATE path — .do_nothing() still performs a real INSERT when no conflicting row exists.
2. The update policy is still evaluated against an existing row, even though it’s never mutated. Skipping this check would let a caller with only create authorization use .do_nothing() to probe for a row’s existence and read its current contents — exactly the leak the DO UPDATE path’s “both policies must allow” rule already exists to close (see Policies: both must allow above). Denial surfaces the identical "update policy denied this upsert" error either way.
3. Only the Inserted branch emits anything. A Created event and audit entry fire exactly like .create(...)’s. Existing emits neither — the row genuinely didn’t change, so there’s nothing to record.
4. The insert branch races honestly, not naively. The probe finding no row doesn’t itself lock anything, so a concurrent transaction can still commit a conflicting row in the gap before this transaction’s own INSERT … ON CONFLICT DO NOTHING runs. When that race is lost, the runtime performs one more locked read and hands back the winning transaction’s row as Existing — never a phantom Existing built from stale data. In the doubly-unlikely case that that row is deleted before the fallback read completes, the call returns CratestackError::Conflict rather than inventing a result; retry the call.

​

Soft-deleted rows and the two paths

​

Auth-derived defaults are insert-only

​

Embedded semantics

1. no policy enforcement (the embedded backend is single-user and trusts its caller)
2. no transactional probe — the upsert is a single statement
3. no event outbox or audit log to discriminate

​

HTTP

PUT /accounts/123 HTTP/1.1
If-None-Match: *           # require insert
Content-Type: application/json

{"balance": 100}

PUT /accounts/123 HTTP/1.1
If-Match: "0"              # require update at version 0
Content-Type: application/json

{"balance": 100}

​

Comparison with idempotency