PostgreSQL

The importer stores marketplace, external_order_id, status, and the original payload in JSONB. A unique constraint on the marketplace and external_order_id pair prevents two parallel retries from creating the same order. The order, its items, and an integration record are stored in one transaction.

A foreign key protects the relationship between items and the order, while an index on status and time supports a common administration view. If a step fails, rollback reverts all local changes. JSONB is indexed only when real queries frequently search inside the external payload.

CREATE TABLE marketplace_order (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  marketplace text NOT NULL,
  external_order_id text NOT NULL,
  status text NOT NULL,
  payload jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT marketplace_order_external_key
    UNIQUE (marketplace, external_order_id)
);

CREATE INDEX marketplace_order_status_created_idx
  ON marketplace_order (status, created_at DESC);