`db/migrations/0005_msrp.sql`

36 lines
-- 0005_msrp.sql — primary-market MSRP history for canonical assets.
--
-- BLUEPRINT.md flags MSRP as a critical v0 problem: Hermès sells the
-- Birkin/Kelly/Constance "exclusively in stores" (per their own site),
-- so there's no normal e-commerce feed to harvest. MSRPs therefore
-- come from boutique receipts, specialist price trackers, client
-- uploads, and high-trust market commentary (auction houses).
--
-- One observation per (asset, source, observed_at). The valuation
-- engine reads the most recent observation per asset to compute the
-- premium-to-retail ratio (Sotheby's-quoted "2.4x retail" headline).

CREATE TABLE IF NOT EXISTS msrp_observations (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  canonical_asset_id  uuid NOT NULL REFERENCES canonical_assets(id) ON DELETE CASCADE,
  msrp_usd            numeric(12,2) NOT NULL,
  currency            text NOT NULL DEFAULT 'USD',
  observed_at         date NOT NULL,
  source              text NOT NULL CHECK (source IN (
                        'boutique_receipt',
                        'specialist_tracker',
                        'auction_house_commentary',
                        'user_upload',
                        'client_pdf_invoice'
                      )),
  source_note         text,
  created_at          timestamptz NOT NULL DEFAULT now(),
  UNIQUE (canonical_asset_id, source, observed_at)
);

CREATE INDEX IF NOT EXISTS msrp_observations_asset_observed_idx
  ON msrp_observations(canonical_asset_id, observed_at DESC);

INSERT INTO schema_migrations(filename) VALUES ('0005_msrp.sql')
  ON CONFLICT (filename) DO NOTHING;