Idempotent Retry of a Duplicated Create (external_id Anchor)

every retry-able business resource carries a caller-controlled external_id + partial unique index on (tenancy, external_id)

Given a business resource with an existing row created via a first call carrying external_id ext_vend_001

create path is INSERT … ON CONFLICT (tenancy, external_id) DO UPDATE … RETURNING id, never a plain insert

identity is the caller-stable external_id, NOT a hash of mutable business fields (a field edit must not change identity

negative) when external_id is omitted from a retry-able create then 422 with error code external_id_required.

same fixture → same external_id → same logical row across dev/staging/prod

A caller (integration sync, retried API request, re-run seeder) submits the same logical create twice - network retry, at-least-once delivery, day-3 re-sync. Each create carries a caller-controlled external_id. The owning service performs an upsert keyed on (tenancy, external_id): the first call inserts, the second matches the existing row and updates mutable fields in place, returning the same record id. No duplicate business row is ever created, even after the short-lived HTTP idempotency-key cache has expired.

every retry-able business resource carries a caller-controlled external_id + partial unique index on (tenancy, external_id);
create path is INSERT … ON CONFLICT (tenancy, external_id) DO UPDATE … RETURNING id, never a plain insert;
identity is the caller-stable external_id, NOT a hash of mutable business fields (a field edit must not change identity);
durable beyond the 24h HTTP idempotency-key TTL;
same fixture → same external_id → same logical row across dev/staging/prod;
second create returns the original id and zero new rows;
audit reflects an update, not a second create.

Given a business resource with an existing row created via a first call carrying external_id ext_vend_001;
when the same create payload is submitted again with the same external_id after the 24h HTTP idempotency cache has expired;
then the response returns the original record id, zero new rows are inserted, mutable fields are updated in place, and the audit trail reflects an update not a second create;
(negative) when external_id is omitted from a retry-able create then 422 with error code external_id_required.

vendor { id: string, entity_id: string, name: string, external_id: string, status: enum, created_at: timestamp, updated_at: timestamp };
partial unique index on (entity_id, external_id) WHERE external_id IS NOT NULL;
(reference, product may differ).

POST /v1/vendors { name, external_id, ... } -> 201 on first call { id, external_id }; -> 200 on duplicate external_id { id, external_id, updated: true };
GET /v1/vendors/{id};
idempotent via external_id;
does NOT use name as uniqueness key.

ACTIVE -> ARCHIVED;
guard: duplicate external_id triggers upsert not 409;
plain insert without external_id on a retry-able resource is structurally blocked by service layer.