-- P014: duplicate-delivery idempotency for POST /api/orders (docs/04, §01).
-- lib/orders-store.ts previously had NO idempotency mechanism at all -- a
-- retried POST (a prospect double-clicking "submit", a client retrying on a
-- dropped response, a proxy replaying a request) created a second, distinct
-- `orders` row for what was semantically the same submission every time,
-- with no way to detect or collapse the duplicate afterward (P014 recon:
-- "Duplicate delivery idempotent: NOT MET").
--
-- `idempotency_key` is caller-supplied (the standard `Idempotency-Key`
-- header convention this skill's own docs already name as the expected
-- 0pt/orders convention) and OPTIONAL: an order created with no key behaves
-- exactly as before (multiple distinct rows on retry) -- this migration adds
-- a mechanism, it does not retroactively require every caller to use it.
-- When present, it must be unique across ALL orders (not scoped per-org):
-- `/api/orders` POST is intentionally anonymous (a prospect can draft/quote
-- before signing in -- see app/api/orders/route.ts), so there is no org_id
-- to scope by at creation time; a caller-generated key (e.g. a UUID minted
-- once per logical submission) is expected to be globally unique on its own,
-- matching the standard Stripe-style idempotency-key contract.
ALTER TABLE orders ADD COLUMN IF NOT EXISTS idempotency_key text;

CREATE UNIQUE INDEX IF NOT EXISTS orders_idempotency_key_uq
  ON orders (idempotency_key)
  WHERE idempotency_key IS NOT NULL;