Idempotent Embedding Pipelines and Job State

An idempotent embedding pipeline can receive the same event repeatedly and converge on one correct derived result. It uses deterministic identities, source and chunk checksums, explicit model versions, short job leases, bounded retries, optimistic write conditions, and reconciliation. External embedding calls happen outside database transactions; final writes succeed only if the source version remains current.

What will you be able to do?

Model a durable job state machine.
Claim work safely with concurrent workers.
Prevent stale completion writes.
Separate retryable from terminal failures.
Reconcile missed events.

What is the state machine?

text

pending -> processing -> ready
   |           |          |
   |           v          v
   +-------> retry <--- superseded
               |
               v
             failed

Store attempt count, available_at, lease owner/expiry, expected source checksum, model ID, and a sanitized error code. failed means human or policy attention; it is not an infinite retry loop.

How do workers claim jobs?

sql

WITH claim AS (
  SELECT id
  FROM app.embedding_jobs
  WHERE status IN ('pending', 'retry')
    AND available_at <= now()
  ORDER BY available_at, id
  FOR UPDATE SKIP LOCKED
  LIMIT 20
)
UPDATE app.embedding_jobs AS j
SET status = 'processing',
    lease_owner = $1,
    lease_expires_at = now() + interval '2 minutes',
    attempts = attempts + 1
FROM claim
WHERE j.id = claim.id
RETURNING j.*;

Commit immediately, call the provider, then open a new short transaction. Before storing, verify source checksum, model ID, lease owner, and job status still match. Otherwise mark the result superseded.

Why is idempotency more than ON CONFLICT?

Database conflict handling prevents duplicate keys, but the external provider may still be called twice. A complete strategy also deduplicates events, bounds financial work, handles ambiguous timeouts, and ensures repeated completion cannot overwrite newer content.

How do you verify it?

Run two workers against the same queue; prove each job is claimed once per lease. Crash a worker after the provider call but before commit. Expire the lease and retry. Change the source during processing and prove the stale result cannot become current. Reconcile all current source checksums against ready embeddings.

What breaks in production?

A worker holds row locks during a network call.
Retries create provider cost storms.
Poison documents retry forever.
Clock assumptions expire healthy long jobs.
An event queue is treated as the source of truth and missed events never reconcile.

AI pair-work prompt

Design a PostgreSQL-backed embedding job state machine for [workload]. Include DDL, claim SQL with SKIP LOCKED, leases, retry classification, exponential backoff with jitter, stale-source protection, dead-letter review, metrics, and reconciliation. Show crash timelines.

Verification contract: Chaos tests must cover crash-before-call, crash-after-call, timeout ambiguity, duplicate event, stale source, expired lease, and poison input.

Check your understanding

Why call the provider outside the transaction?
What does SKIP LOCKED accomplish?
Which fields prevent a stale result from becoming current?

Official references