`db/migrations/0006_service_commitments.sql`

27 lines
-- 0006_service_commitments.sql — merchant promises tied to a purchase

BEGIN;

CREATE TABLE IF NOT EXISTS service_commitment (
  id              text PRIMARY KEY,
  user_id         text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  purchase_id     text REFERENCES purchase(id) ON DELETE CASCADE,
  source_message_id text REFERENCES source_message(id) ON DELETE SET NULL,
  provider_name   text,                       -- merchant or service provider name
  commitment_type text NOT NULL,              -- 'money_back_guarantee' | 'returns_window' | 'satisfaction_guarantee' | 'price_match' | 'service_sla'
  guarantee_text  text NOT NULL,              -- the verbatim phrase ("Money-back guarantee within 30 days")
  promised_outcome text,                      -- 'refund' | 'replacement' | 'credit' | 'service_redo'
  window_days     integer,                    -- 30 / 60 / 90 / etc.
  refund_window_ends_at timestamptz,          -- absolute deadline when computable from purchase_date
  conditions      text,                       -- extracted conditions ("with original receipt", "unworn", etc.)
  source          text NOT NULL DEFAULT 'heuristic',  -- 'heuristic' | 'heuristic+llm' | 'manual'
  confidence      numeric(3,2),
  raw_extract     jsonb,
  created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS service_commitment_user_idx ON service_commitment (user_id, refund_window_ends_at);
CREATE INDEX IF NOT EXISTS service_commitment_purchase_idx ON service_commitment (purchase_id);
CREATE INDEX IF NOT EXISTS service_commitment_type_idx ON service_commitment (commitment_type);

COMMIT;