CREATE TABLE IF NOT EXISTS "command_records" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "command_key" text NOT NULL,
  "command_type" text NOT NULL,
  "aggregate_type" text NOT NULL,
  "aggregate_id" uuid NOT NULL,
  "payload_hash" text NOT NULL,
  "payload" jsonb NOT NULL,
  "status" text NOT NULL DEFAULT 'CLAIMED',
  "claimed_by" text NOT NULL,
  "claimed_at" timestamp with time zone DEFAULT now() NOT NULL,
  "completed_at" timestamp with time zone,
  "failed_at" timestamp with time zone,
  "result_payload" jsonb,
  "failure_code" text,
  "failure_message" text,
  CONSTRAINT "command_records_status_ck" CHECK ("status" IN ('CLAIMED', 'COMPLETED', 'FAILED'))
);

CREATE UNIQUE INDEX IF NOT EXISTS "command_records_command_key_uq"
  ON "command_records" USING btree ("command_key");

CREATE INDEX IF NOT EXISTS "command_records_status_claimed_at_idx"
  ON "command_records" USING btree ("status", "claimed_at" DESC);
