Transactions make a set of database changes atomic, but they do not automatically coordinate remote APIs, queues, or client retries. Correct concurrent systems define invariants, choose optimistic or pessimistic control, make repeated commands safe, and handle deadlocks and ambiguous outcomes. Database constraints are the final defense against races.
Use an expected version:
If zero rows change, the resource is missing or stale. Do not silently overwrite. Pessimistic locking with SELECT ... FOR UPDATE is appropriate when contention is real and the protected transaction is short. Always lock multiple resources in a consistent order.
Store (organization_id, actor_id, operation, key) as a unique scope, plus a canonical request hash, processing status, resource/result reference, expiry policy, and timestamps. In one transaction:
Keys are not authentication and should be unguessable, bounded in length, and retained for the documented retry window.
PostgreSQL cannot atomically commit with an ordinary HTTP provider call. Use a transactional outbox: commit the business change and an event record together, then publish the event asynchronously and idempotently. Consumers use an inbox or unique effect key to prevent duplicates. Exactly-once delivery is usually an application illusion built from at-least-once delivery plus deduplication.
Retries must not pay for duplicate model runs or execute tools twice. Give every AI run and tool effect stable application identifiers. If a provider supports an idempotency key, use it in addition to your durable record. Never hold a row lock while waiting for a model response.
Design “start AI draft” so ten identical concurrent requests create one billable run and return the same operation. Then reuse the key with different ticket text.
Acceptance criteria: a concurrency test proves one run; mismatched reuse returns conflict; a crash after commit but before response still returns the original result on retry.