`db/migrations/0010_savings.sql`

57 lines
-- 0010_savings.sql
-- The "life optimizer" layer: turn what Steve actually buys into money-saving /
-- quality-improving suggestions, plus coupon leads at the places he shops.
--   savings_suggestion — one actionable idea (cheaper/better substitute, reorder timing,
--                        price drop, or a coupon) generated from purchase + reorder_item data.
--   merchant_coupon    — a discount/coupon lead tied to a merchant Steve buys from.
-- Both feed the /savings dashboard. Local-LLM ($0) generated; nothing is auto-purchased.
-- Idempotent. Safe to re-run.

BEGIN;

CREATE TABLE IF NOT EXISTS savings_suggestion (
  id               text PRIMARY KEY,
  user_id          text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  kind             text NOT NULL DEFAULT 'substitute',  -- substitute|upgrade|coupon|reorder-timing|price-drop|bundle
  title            text NOT NULL,
  current_item     text,                                 -- what Steve buys today
  current_price    numeric(12,2),
  suggested_item   text,                                 -- the cheaper/better alternative
  suggested_price  numeric(12,2),
  est_savings      numeric(12,2),                         -- estimated $ saved
  savings_basis    text,                                  -- "per order" | "per year" | "one-time"
  quality_note     text,                                  -- quality tradeoff or upgrade rationale
  merchant         text,                                  -- where to buy the suggestion
  source_url       text,                                  -- link to verify (never auto-buy)
  rationale        text,                                  -- why this suggestion
  confidence       numeric(4,3) NOT NULL DEFAULT 0.5,     -- 0..1
  reorder_item_id  text REFERENCES reorder_item(id) ON DELETE SET NULL,
  status           text NOT NULL DEFAULT 'new',           -- new|saved|dismissed|done
  metadata_jsonb   jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at       timestamptz NOT NULL DEFAULT now(),
  updated_at       timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS savings_user_status_idx ON savings_suggestion (user_id, status, created_at DESC);
CREATE INDEX IF NOT EXISTS savings_kind_idx        ON savings_suggestion (user_id, kind);

CREATE TABLE IF NOT EXISTS merchant_coupon (
  id              text PRIMARY KEY,
  user_id         text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  merchant        text NOT NULL,                          -- e.g. "Amazon", "Costco", "Nespresso"
  merchant_domain text,
  title           text NOT NULL,
  code            text,                                   -- promo code, if any
  discount_text   text,                                   -- "15% off", "$10 off $50"
  description      text,
  url             text,
  expires_on      date,
  source          text,                                   -- where the lead came from
  status          text NOT NULL DEFAULT 'active',         -- active|expired|dismissed
  metadata_jsonb  jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS coupon_user_merchant_idx ON merchant_coupon (user_id, merchant, status);

COMMIT;