`db/migrations/0017_review_draft.sql`

24 lines
-- 0017_review_draft.sql — Amazon/seller review + thank-you drafts, keyed to purchase.
-- ADDITIVE ONLY. One draft row per (purchase, target). Nothing here posts/sends;
-- posting is gated in lib/reviews/executors.js (approve + content_hash seal +
-- REVIEWS_EXECUTORS_LIVE + per-item confirm). Reviews never auto-submit.
CREATE TABLE IF NOT EXISTS review_draft (
  id              text PRIMARY KEY,
  user_id         text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  purchase_id     text NOT NULL REFERENCES purchase(id) ON DELETE CASCADE,
  target          text NOT NULL CHECK (target IN ('amazon_review','seller_email','google_review')),
  product_name    text,
  draft_title     text,
  draft_text      text,
  rating          integer,
  status          text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','approved','posted','skipped')),
  content_hash    text,                 -- seal captured at approval (anti stale-approval-replay)
  seller_email_to text,
  resolved        jsonb,                -- resolver output (google place_id / amazon product url, etc.)
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (purchase_id, target)
);
CREATE INDEX IF NOT EXISTS review_draft_user_idx ON review_draft (user_id, status);
CREATE INDEX IF NOT EXISTS review_draft_purchase_idx ON review_draft (purchase_id);