← back to Lifestyle Asset Intel
db/seed.sql
1360 lines
-- seed.sql — minimal blueprint sample data for v0.
-- Idempotent: re-running upserts; safe to run after schema.sql.
-- ---------------------------------------------------------------------------
-- sources (tier 1-3 — Tier 4 dealer/partner feeds and user receipts deferred)
-- ---------------------------------------------------------------------------
INSERT INTO sources (name, slug, tier, kind, weight, url, notes) VALUES
('Sotheby''s', 'sothebys', 1, 'auction', 1.000, 'https://www.sothebys.com/en/', 'Closed-lot auction results — Tier 1 ground truth.'),
('Christie''s', 'christies', 1, 'auction', 1.000, 'https://www.christies.com/', 'Closed-lot auction results — Tier 1 ground truth.'),
('FASHIONPHILE', 'fashionphile', 2, 'marketplace', 0.800, 'https://www.fashionphile.com/', 'Verified resale w/ condition grades Giftable→Flawed.'),
('Rebag', 'rebag', 2, 'quote', 0.700, 'https://www.rebag.com/', 'Clair instant quote, Consign/Trade/Buyout modes.'),
('The RealReal', 'therealreal', 2, 'marketplace', 0.700, 'https://www.therealreal.com/', 'Pristine→As Is grades; demand-driven pricing algo.'),
('StockX', 'stockx', 2, 'marketplace', 0.650, 'https://stockx.com/', 'New-condition only; cleaner store-fresh signal.'),
('Chrono24', 'chrono24', 2, 'marketplace', 0.650, 'https://www.chrono24.com/', 'ChronoPulse index; watches-strongest source.'),
('eBay', 'ebay', 3, 'marketplace', 0.350, 'https://www.ebay.com/', 'Active listings; sold-history gated behind Marketplace Insights.')
ON CONFLICT (slug) DO UPDATE
SET name = EXCLUDED.name, tier = EXCLUDED.tier, kind = EXCLUDED.kind,
weight = EXCLUDED.weight, url = EXCLUDED.url, notes = EXCLUDED.notes,
updated_at = now();
-- ---------------------------------------------------------------------------
-- brands + families
-- ---------------------------------------------------------------------------
INSERT INTO brands (name, slug) VALUES
('Hermès', 'hermes')
ON CONFLICT (slug) DO NOTHING;
INSERT INTO model_families (brand_id, name, slug)
SELECT b.id, 'Birkin', 'birkin' FROM brands b WHERE b.slug = 'hermes'
ON CONFLICT (brand_id, slug) DO NOTHING;
INSERT INTO model_families (brand_id, name, slug)
SELECT b.id, 'Kelly', 'kelly' FROM brands b WHERE b.slug = 'hermes'
ON CONFLICT (brand_id, slug) DO NOTHING;
-- ---------------------------------------------------------------------------
-- canonical asset: Birkin 30 / Togo / Gold / GHW / Retourne / US
-- ---------------------------------------------------------------------------
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT
mf.id,
'birkin-30-togo-gold-ghw-us',
'30',
'Togo',
'Gold',
'GHW',
'Retourne',
'US',
jsonb_build_object('display_name', 'Birkin 30 — Togo — Gold — GHW (US)',
'accessories_required', jsonb_build_array('box','dust_bag','rain_coat','clochette','keys','lock'))
FROM model_families mf
JOIN brands b ON b.id = mf.brand_id
WHERE b.slug = 'hermes' AND mf.slug = 'birkin'
ON CONFLICT (slug) DO NOTHING;
-- ---------------------------------------------------------------------------
-- transactions — blueprint's 2022/2023/2024/2025 normalized averages
-- ---------------------------------------------------------------------------
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price,
expected_net_seller_proceeds, normalized_market_value, condition_grade,
condition_facets, currency, region, transacted_at)
SELECT
ca.id,
s.id,
19000.00, 19000.00, 14750.00, 19000.00,
5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2018),
'USD', 'US',
'2022-08-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s
WHERE ca.slug = 'birkin-30-togo-gold-ghw-us' AND s.slug = 'sothebys'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price,
expected_net_seller_proceeds, normalized_market_value, condition_grade,
condition_facets, currency, region, transacted_at)
SELECT
ca.id,
s.id,
20459.00, 20459.00, 16000.00, 20459.00,
5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2019),
'USD', 'US',
'2023-08-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s
WHERE ca.slug = 'birkin-30-togo-gold-ghw-us' AND s.slug = 'sothebys'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price,
expected_net_seller_proceeds, normalized_market_value, condition_grade,
condition_facets, currency, region, transacted_at)
SELECT
ca.id,
s.id,
21038.00, 21038.00, 16500.00, 21038.00,
5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2021),
'USD', 'US',
'2024-08-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s
WHERE ca.slug = 'birkin-30-togo-gold-ghw-us' AND s.slug = 'sothebys'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price,
expected_net_seller_proceeds, normalized_market_value, condition_grade,
condition_facets, currency, region, transacted_at)
SELECT
ca.id,
s.id,
22300.00, 22300.00, 17500.00, 22300.00,
5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2023),
'USD', 'US',
'2025-08-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s
WHERE ca.slug = 'birkin-30-togo-gold-ghw-us' AND s.slug = 'sothebys'
ON CONFLICT DO NOTHING;
-- One Pristine FASHIONPHILE comp at the upper band
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price,
expected_net_seller_proceeds, normalized_market_value, condition_grade,
condition_facets, currency, region, transacted_at)
SELECT
ca.id,
s.id,
28500.00, 28500.00, 22000.00, 28500.00,
6,
jsonb_build_object('store_fresh', true, 'date_stamp_year', 2024,
'accessories_complete', true, 'plastic_intact', true),
'USD', 'US',
'2026-04-12 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s
WHERE ca.slug = 'birkin-30-togo-gold-ghw-us' AND s.slug = 'fashionphile'
ON CONFLICT DO NOTHING;
-- ---------------------------------------------------------------------------
-- index: birkin-30-togo-neutral
-- ---------------------------------------------------------------------------
INSERT INTO indices (slug, name, definition, methodology_version) VALUES
('birkin-30-togo-neutral',
'Birkin 30 — Togo — Neutral Colors',
jsonb_build_object('size','30','material','Togo','colors',jsonb_build_array('Gold','Black','Etoupe','Nata'),'region','US'),
'v0-stub')
ON CONFLICT (slug) DO NOTHING;
INSERT INTO index_points (index_id, as_of, value, change_pct)
SELECT i.id, '2022-12-31'::date, 19000.00, NULL FROM indices i WHERE i.slug = 'birkin-30-togo-neutral'
ON CONFLICT (index_id, as_of) DO NOTHING;
INSERT INTO index_points (index_id, as_of, value, change_pct)
SELECT i.id, '2023-12-31'::date, 20459.00, 0.0768 FROM indices i WHERE i.slug = 'birkin-30-togo-neutral'
ON CONFLICT (index_id, as_of) DO NOTHING;
INSERT INTO index_points (index_id, as_of, value, change_pct)
SELECT i.id, '2024-12-31'::date, 21038.00, 0.0283 FROM indices i WHERE i.slug = 'birkin-30-togo-neutral'
ON CONFLICT (index_id, as_of) DO NOTHING;
INSERT INTO index_points (index_id, as_of, value, change_pct)
SELECT i.id, '2025-12-31'::date, 22300.00, 0.0600 FROM indices i WHERE i.slug = 'birkin-30-togo-neutral'
ON CONFLICT (index_id, as_of) DO NOTHING;
-- ---------------------------------------------------------------------------
-- valuation_snapshot for today (lib/valuation.js will rewrite on each /api hit)
-- ---------------------------------------------------------------------------
INSERT INTO valuation_snapshots (canonical_asset_id, as_of, q10, q50, q90,
liquidity_score, expected_dts, confidence,
auth_risk, comp_count, methodology_version, breakdown)
SELECT
ca.id,
CURRENT_DATE,
19500.00, 22300.00, 28500.00,
0.780, 21, 0.740, 0.080, 5,
'v0-stub',
jsonb_build_object(
'source_quality_weight', 0.25,
'comparable_similarity_weight', 0.18,
'sample_depth_weight', 0.12,
'recency_weight', 0.10,
'image_match_weight', 0.09,
'authenticity_risk_penalty', -0.04,
'condition_uncertainty_penalty', -0.05,
'region_gap_penalty', 0.00,
'fee_model_uncertainty_penalty', -0.01
)
FROM canonical_assets ca
WHERE ca.slug = 'birkin-30-togo-gold-ghw-us'
ON CONFLICT (canonical_asset_id, as_of, methodology_version) DO UPDATE
SET q10 = EXCLUDED.q10, q50 = EXCLUDED.q50, q90 = EXCLUDED.q90,
liquidity_score = EXCLUDED.liquidity_score,
expected_dts = EXCLUDED.expected_dts,
confidence = EXCLUDED.confidence,
auth_risk = EXCLUDED.auth_risk,
comp_count = EXCLUDED.comp_count,
breakdown = EXCLUDED.breakdown;
-- ===========================================================================
-- v0 Max It #1: catalog breadth — 14 assets + 3 indices
-- ===========================================================================
-- Q50 anchors derived from Sotheby's reference (B30 Togo neutral $22,300 in 2025):
-- B25 ≈ +35% over B30 same matl/color; B35 ≈ -15%
-- K25 ≈ B25; K28 ≈ B30; K32 ≈ B35
-- Sellier ≈ +20% over Retourne; Epsom +5%; Clemence -5%; Swift premium for rare
-- Vert Verone (fashion color) ≈ -7% off neutrals; Pristine/store-fresh +30%
-- Net proceeds ≈ 0.78 × gross (FASHIONPHILE/Rebag/TRR), ≈ 0.82 × hammer (Sotheby's)
-- ===========================================================================
-- ---------------------------------------------------------------------------
-- canonical assets — 14 new SKUs (8 Birkin, 6 Kelly)
-- ---------------------------------------------------------------------------
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'birkin-25-togo-gold-ghw-us', '25', 'Togo', 'Gold', 'GHW', 'Retourne', 'US',
jsonb_build_object('display_name', 'Birkin 25 — Togo — Gold — GHW (US)',
'accessories_required', jsonb_build_array('box','dust_bag','rain_coat','clochette','keys','lock'))
FROM model_families mf JOIN brands b ON b.id = mf.brand_id
WHERE b.slug = 'hermes' AND mf.slug = 'birkin'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'birkin-25-epsom-black-ghw-us', '25', 'Epsom', 'Black', 'GHW', 'Retourne', 'US',
jsonb_build_object('display_name', 'Birkin 25 — Epsom — Black — GHW (US)',
'accessories_required', jsonb_build_array('box','dust_bag','rain_coat','clochette','keys','lock'))
FROM model_families mf JOIN brands b ON b.id = mf.brand_id
WHERE b.slug = 'hermes' AND mf.slug = 'birkin'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'birkin-25-clemence-etoupe-phw-us', '25', 'Clemence', 'Etoupe', 'PHW', 'Retourne', 'US',
jsonb_build_object('display_name', 'Birkin 25 — Clemence — Etoupe — PHW (US)',
'accessories_required', jsonb_build_array('box','dust_bag','rain_coat','clochette','keys','lock'))
FROM model_families mf JOIN brands b ON b.id = mf.brand_id
WHERE b.slug = 'hermes' AND mf.slug = 'birkin'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'birkin-30-togo-black-ghw-us', '30', 'Togo', 'Black', 'GHW', 'Retourne', 'US',
jsonb_build_object('display_name', 'Birkin 30 — Togo — Black — GHW (US)',
'accessories_required', jsonb_build_array('box','dust_bag','rain_coat','clochette','keys','lock'))
FROM model_families mf JOIN brands b ON b.id = mf.brand_id
WHERE b.slug = 'hermes' AND mf.slug = 'birkin'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'birkin-30-epsom-etoupe-phw-us', '30', 'Epsom', 'Etoupe', 'PHW', 'Retourne', 'US',
jsonb_build_object('display_name', 'Birkin 30 — Epsom — Etoupe — PHW (US)',
'accessories_required', jsonb_build_array('box','dust_bag','rain_coat','clochette','keys','lock'))
FROM model_families mf JOIN brands b ON b.id = mf.brand_id
WHERE b.slug = 'hermes' AND mf.slug = 'birkin'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'birkin-30-swift-vert-verone-ghw-us', '30', 'Swift', 'Vert Verone', 'GHW', 'Retourne', 'US',
jsonb_build_object('display_name', 'Birkin 30 — Swift — Vert Verone — GHW (US)',
'accessories_required', jsonb_build_array('box','dust_bag','rain_coat','clochette','keys','lock'))
FROM model_families mf JOIN brands b ON b.id = mf.brand_id
WHERE b.slug = 'hermes' AND mf.slug = 'birkin'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'birkin-35-togo-gold-ghw-us', '35', 'Togo', 'Gold', 'GHW', 'Retourne', 'US',
jsonb_build_object('display_name', 'Birkin 35 — Togo — Gold — GHW (US)',
'accessories_required', jsonb_build_array('box','dust_bag','rain_coat','clochette','keys','lock'))
FROM model_families mf JOIN brands b ON b.id = mf.brand_id
WHERE b.slug = 'hermes' AND mf.slug = 'birkin'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'birkin-35-clemence-black-phw-us', '35', 'Clemence', 'Black', 'PHW', 'Retourne', 'US',
jsonb_build_object('display_name', 'Birkin 35 — Clemence — Black — PHW (US)',
'accessories_required', jsonb_build_array('box','dust_bag','rain_coat','clochette','keys','lock'))
FROM model_families mf JOIN brands b ON b.id = mf.brand_id
WHERE b.slug = 'hermes' AND mf.slug = 'birkin'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'kelly-25-sellier-epsom-black-ghw-us', '25', 'Epsom', 'Black', 'GHW', 'Sellier', 'US',
jsonb_build_object('display_name', 'Kelly 25 — Sellier — Epsom — Black — GHW (US)',
'accessories_required', jsonb_build_array('box','dust_bag','rain_coat','clochette','keys','lock','strap'))
FROM model_families mf JOIN brands b ON b.id = mf.brand_id
WHERE b.slug = 'hermes' AND mf.slug = 'kelly'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'kelly-25-retourne-togo-gold-ghw-us', '25', 'Togo', 'Gold', 'GHW', 'Retourne', 'US',
jsonb_build_object('display_name', 'Kelly 25 — Retourne — Togo — Gold — GHW (US)',
'accessories_required', jsonb_build_array('box','dust_bag','rain_coat','clochette','keys','lock','strap'))
FROM model_families mf JOIN brands b ON b.id = mf.brand_id
WHERE b.slug = 'hermes' AND mf.slug = 'kelly'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'kelly-28-sellier-epsom-etoupe-phw-us', '28', 'Epsom', 'Etoupe', 'PHW', 'Sellier', 'US',
jsonb_build_object('display_name', 'Kelly 28 — Sellier — Epsom — Etoupe — PHW (US)',
'accessories_required', jsonb_build_array('box','dust_bag','rain_coat','clochette','keys','lock','strap'))
FROM model_families mf JOIN brands b ON b.id = mf.brand_id
WHERE b.slug = 'hermes' AND mf.slug = 'kelly'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'kelly-28-retourne-togo-black-ghw-us', '28', 'Togo', 'Black', 'GHW', 'Retourne', 'US',
jsonb_build_object('display_name', 'Kelly 28 — Retourne — Togo — Black — GHW (US)',
'accessories_required', jsonb_build_array('box','dust_bag','rain_coat','clochette','keys','lock','strap'))
FROM model_families mf JOIN brands b ON b.id = mf.brand_id
WHERE b.slug = 'hermes' AND mf.slug = 'kelly'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'kelly-32-retourne-clemence-gold-ghw-us', '32', 'Clemence', 'Gold', 'GHW', 'Retourne', 'US',
jsonb_build_object('display_name', 'Kelly 32 — Retourne — Clemence — Gold — GHW (US)',
'accessories_required', jsonb_build_array('box','dust_bag','rain_coat','clochette','keys','lock','strap'))
FROM model_families mf JOIN brands b ON b.id = mf.brand_id
WHERE b.slug = 'hermes' AND mf.slug = 'kelly'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'kelly-32-sellier-epsom-black-ghw-us', '32', 'Epsom', 'Black', 'GHW', 'Sellier', 'US',
jsonb_build_object('display_name', 'Kelly 32 — Sellier — Epsom — Black — GHW (US)',
'accessories_required', jsonb_build_array('box','dust_bag','rain_coat','clochette','keys','lock','strap'))
FROM model_families mf JOIN brands b ON b.id = mf.brand_id
WHERE b.slug = 'hermes' AND mf.slug = 'kelly'
ON CONFLICT (slug) DO NOTHING;
-- ---------------------------------------------------------------------------
-- transactions — 2-3 comps per asset across 2024-2026
-- net = 0.78 × gross (FASHIONPHILE/Rebag/TRR), 0.82 × hammer (Sotheby's/Christie's)
-- ---------------------------------------------------------------------------
-- Birkin 25 — Togo — Gold — GHW (Q50 ~30,500)
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 28800.00, 28800.00, 22464.00, 28800.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2021), 'USD', 'US', '2024-06-12 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-25-togo-gold-ghw-us' AND s.slug = 'fashionphile'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 30200.00, 30200.00, 24764.00, 30200.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2022), 'USD', 'US', '2025-03-22 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-25-togo-gold-ghw-us' AND s.slug = 'sothebys'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 36500.00, 36500.00, 28470.00, 36500.00, 6,
jsonb_build_object('store_fresh', true, 'date_stamp_year', 2024, 'accessories_complete', true, 'plastic_intact', true), 'USD', 'US', '2026-02-18 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-25-togo-gold-ghw-us' AND s.slug = 'fashionphile'
ON CONFLICT DO NOTHING;
-- Birkin 25 — Epsom — Black — GHW (Q50 ~32,000)
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 30500.00, 30500.00, 23790.00, 30500.00, 4,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2019), 'USD', 'US', '2024-09-08 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-25-epsom-black-ghw-us' AND s.slug = 'rebag'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 33800.00, 33800.00, 27716.00, 33800.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2023), 'USD', 'US', '2025-11-05 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-25-epsom-black-ghw-us' AND s.slug = 'christies'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 31900.00, 31900.00, 24882.00, 31900.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2022), 'USD', 'US', '2026-01-14 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-25-epsom-black-ghw-us' AND s.slug = 'fashionphile'
ON CONFLICT DO NOTHING;
-- Birkin 25 — Clemence — Etoupe — PHW (Q50 ~28,500; Clemence -5%)
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 27200.00, 27200.00, 21216.00, 27200.00, 4,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2020), 'USD', 'US', '2024-04-18 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-25-clemence-etoupe-phw-us' AND s.slug = 'therealreal'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 28900.00, 28900.00, 22542.00, 28900.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2022), 'USD', 'US', '2025-07-30 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-25-clemence-etoupe-phw-us' AND s.slug = 'fashionphile'
ON CONFLICT DO NOTHING;
-- Birkin 30 — Togo — Black — GHW (Q50 ~23,000; black neutral baseline)
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 21500.00, 21500.00, 17630.00, 21500.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2020), 'USD', 'US', '2024-11-20 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-30-togo-black-ghw-us' AND s.slug = 'sothebys'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 22950.00, 22950.00, 17900.00, 22950.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2022), 'USD', 'US', '2025-09-04 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-30-togo-black-ghw-us' AND s.slug = 'fashionphile'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 24100.00, 24100.00, 18798.00, 24100.00, 6,
jsonb_build_object('store_fresh', true, 'date_stamp_year', 2024, 'accessories_complete', true), 'USD', 'US', '2026-03-08 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-30-togo-black-ghw-us' AND s.slug = 'rebag'
ON CONFLICT DO NOTHING;
-- Birkin 30 — Epsom — Etoupe — PHW (Q50 ~23,500; Epsom +5%)
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 22100.00, 22100.00, 17238.00, 22100.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2021), 'USD', 'US', '2024-07-22 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-30-epsom-etoupe-phw-us' AND s.slug = 'fashionphile'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 23700.00, 23700.00, 19434.00, 23700.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2023), 'USD', 'US', '2025-10-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-30-epsom-etoupe-phw-us' AND s.slug = 'sothebys'
ON CONFLICT DO NOTHING;
-- Birkin 30 — Swift — Vert Verone — GHW (Q50 ~21,000; fashion color discount)
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 19800.00, 19800.00, 15444.00, 19800.00, 4,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2019), 'USD', 'US', '2024-10-02 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-30-swift-vert-verone-ghw-us' AND s.slug = 'therealreal'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 21300.00, 21300.00, 16614.00, 21300.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2021), 'USD', 'US', '2025-06-11 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-30-swift-vert-verone-ghw-us' AND s.slug = 'fashionphile'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 22400.00, 22400.00, 18368.00, 22400.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2023), 'USD', 'US', '2026-04-01 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-30-swift-vert-verone-ghw-us' AND s.slug = 'christies'
ON CONFLICT DO NOTHING;
-- Birkin 35 — Togo — Gold — GHW (Q50 ~19,500; -15% vs B30)
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 17400.00, 17400.00, 13572.00, 17400.00, 4,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2017), 'USD', 'US', '2024-05-29 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-35-togo-gold-ghw-us' AND s.slug = 'rebag'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 19200.00, 19200.00, 15744.00, 19200.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2020), 'USD', 'US', '2025-08-19 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-35-togo-gold-ghw-us' AND s.slug = 'fashionphile'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 19850.00, 19850.00, 16277.00, 19850.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2022), 'USD', 'US', '2026-02-26 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-35-togo-gold-ghw-us' AND s.slug = 'sothebys'
ON CONFLICT DO NOTHING;
-- Birkin 35 — Clemence — Black — PHW (Q50 ~19,000)
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 17900.00, 17900.00, 13962.00, 17900.00, 4,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2018), 'USD', 'US', '2024-08-07 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-35-clemence-black-phw-us' AND s.slug = 'therealreal'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 18800.00, 18800.00, 14664.00, 18800.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2021), 'USD', 'US', '2025-12-03 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'birkin-35-clemence-black-phw-us' AND s.slug = 'fashionphile'
ON CONFLICT DO NOTHING;
-- Kelly 25 — Sellier — Epsom — Black — GHW (Q50 ~36,500; sellier+epsom premium)
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 34500.00, 34500.00, 28290.00, 34500.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2021), 'USD', 'US', '2024-10-19 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'kelly-25-sellier-epsom-black-ghw-us' AND s.slug = 'sothebys'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 36800.00, 36800.00, 28704.00, 36800.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2023), 'USD', 'US', '2025-09-12 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'kelly-25-sellier-epsom-black-ghw-us' AND s.slug = 'fashionphile'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 41200.00, 41200.00, 32136.00, 41200.00, 6,
jsonb_build_object('store_fresh', true, 'date_stamp_year', 2024, 'accessories_complete', true, 'plastic_intact', true), 'USD', 'US', '2026-03-25 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'kelly-25-sellier-epsom-black-ghw-us' AND s.slug = 'fashionphile'
ON CONFLICT DO NOTHING;
-- Kelly 25 — Retourne — Togo — Gold — GHW (Q50 ~30,000)
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 28200.00, 28200.00, 21996.00, 28200.00, 4,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2019), 'USD', 'US', '2024-12-11 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'kelly-25-retourne-togo-gold-ghw-us' AND s.slug = 'rebag'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 29900.00, 29900.00, 23322.00, 29900.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2022), 'USD', 'US', '2025-05-23 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'kelly-25-retourne-togo-gold-ghw-us' AND s.slug = 'fashionphile'
ON CONFLICT DO NOTHING;
-- Kelly 28 — Sellier — Epsom — Etoupe — PHW (Q50 ~27,500)
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 25800.00, 25800.00, 20124.00, 25800.00, 4,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2020), 'USD', 'US', '2024-09-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'kelly-28-sellier-epsom-etoupe-phw-us' AND s.slug = 'therealreal'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 27600.00, 27600.00, 22632.00, 27600.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2022), 'USD', 'US', '2025-08-28 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'kelly-28-sellier-epsom-etoupe-phw-us' AND s.slug = 'sothebys'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 28950.00, 28950.00, 22581.00, 28950.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2023), 'USD', 'US', '2026-01-30 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'kelly-28-sellier-epsom-etoupe-phw-us' AND s.slug = 'fashionphile'
ON CONFLICT DO NOTHING;
-- Kelly 28 — Retourne — Togo — Black — GHW (Q50 ~23,500)
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 22100.00, 22100.00, 17238.00, 22100.00, 4,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2018), 'USD', 'US', '2024-06-25 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'kelly-28-retourne-togo-black-ghw-us' AND s.slug = 'fashionphile'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 23800.00, 23800.00, 19516.00, 23800.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2021), 'USD', 'US', '2025-10-08 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'kelly-28-retourne-togo-black-ghw-us' AND s.slug = 'sothebys'
ON CONFLICT DO NOTHING;
-- Kelly 32 — Retourne — Clemence — Gold — GHW (Q50 ~17,500; -25% vs K28 retourne)
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 16400.00, 16400.00, 12792.00, 16400.00, 4,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2016), 'USD', 'US', '2024-04-04 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'kelly-32-retourne-clemence-gold-ghw-us' AND s.slug = 'rebag'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 17600.00, 17600.00, 13728.00, 17600.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2019), 'USD', 'US', '2025-07-17 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'kelly-32-retourne-clemence-gold-ghw-us' AND s.slug = 'fashionphile'
ON CONFLICT DO NOTHING;
-- Kelly 32 — Sellier — Epsom — Black — GHW (Q50 ~21,500; sellier premium on K32)
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 20100.00, 20100.00, 16482.00, 20100.00, 4,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2017), 'USD', 'US', '2024-11-08 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'kelly-32-sellier-epsom-black-ghw-us' AND s.slug = 'sothebys'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 21850.00, 21850.00, 17043.00, 21850.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2020), 'USD', 'US', '2025-12-21 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'kelly-32-sellier-epsom-black-ghw-us' AND s.slug = 'fashionphile'
ON CONFLICT DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds, normalized_market_value, condition_grade, condition_facets, currency, region, transacted_at)
SELECT ca.id, s.id, 22950.00, 22950.00, 18819.00, 22950.00, 5,
jsonb_build_object('store_fresh', false, 'date_stamp_year', 2023), 'USD', 'US', '2026-04-22 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s WHERE ca.slug = 'kelly-32-sellier-epsom-black-ghw-us' AND s.slug = 'christies'
ON CONFLICT DO NOTHING;
-- ---------------------------------------------------------------------------
-- valuation_snapshots — one per asset, as_of=CURRENT_DATE
-- q10 = q50*0.85, q90 = q50*1.27 typical; breakdown shape matches existing
-- ---------------------------------------------------------------------------
INSERT INTO valuation_snapshots (canonical_asset_id, as_of, q10, q50, q90, liquidity_score, expected_dts, confidence, auth_risk, comp_count, methodology_version, breakdown)
SELECT ca.id, CURRENT_DATE, 25925.00, 30500.00, 38735.00, 0.760, 18, 0.770, 0.060, 3, 'v0-stub',
jsonb_build_object('source_quality_weight',0.26,'comparable_similarity_weight',0.19,'sample_depth_weight',0.10,
'recency_weight',0.11,'image_match_weight',0.09,'authenticity_risk_penalty',-0.03,'condition_uncertainty_penalty',-0.05,
'region_gap_penalty',0.00,'fee_model_uncertainty_penalty',-0.01)
FROM canonical_assets ca WHERE ca.slug = 'birkin-25-togo-gold-ghw-us'
ON CONFLICT (canonical_asset_id, as_of, methodology_version) DO UPDATE
SET q10=EXCLUDED.q10, q50=EXCLUDED.q50, q90=EXCLUDED.q90, liquidity_score=EXCLUDED.liquidity_score,
expected_dts=EXCLUDED.expected_dts, confidence=EXCLUDED.confidence, auth_risk=EXCLUDED.auth_risk,
comp_count=EXCLUDED.comp_count, breakdown=EXCLUDED.breakdown;
INSERT INTO valuation_snapshots (canonical_asset_id, as_of, q10, q50, q90, liquidity_score, expected_dts, confidence, auth_risk, comp_count, methodology_version, breakdown)
SELECT ca.id, CURRENT_DATE, 27200.00, 32000.00, 40640.00, 0.820, 16, 0.800, 0.050, 3, 'v0-stub',
jsonb_build_object('source_quality_weight',0.27,'comparable_similarity_weight',0.20,'sample_depth_weight',0.11,
'recency_weight',0.12,'image_match_weight',0.10,'authenticity_risk_penalty',-0.03,'condition_uncertainty_penalty',-0.04,
'region_gap_penalty',0.00,'fee_model_uncertainty_penalty',-0.01)
FROM canonical_assets ca WHERE ca.slug = 'birkin-25-epsom-black-ghw-us'
ON CONFLICT (canonical_asset_id, as_of, methodology_version) DO UPDATE
SET q10=EXCLUDED.q10, q50=EXCLUDED.q50, q90=EXCLUDED.q90, liquidity_score=EXCLUDED.liquidity_score,
expected_dts=EXCLUDED.expected_dts, confidence=EXCLUDED.confidence, auth_risk=EXCLUDED.auth_risk,
comp_count=EXCLUDED.comp_count, breakdown=EXCLUDED.breakdown;
INSERT INTO valuation_snapshots (canonical_asset_id, as_of, q10, q50, q90, liquidity_score, expected_dts, confidence, auth_risk, comp_count, methodology_version, breakdown)
SELECT ca.id, CURRENT_DATE, 24225.00, 28500.00, 36195.00, 0.700, 25, 0.680, 0.080, 2, 'v0-stub',
jsonb_build_object('source_quality_weight',0.24,'comparable_similarity_weight',0.17,'sample_depth_weight',0.08,
'recency_weight',0.10,'image_match_weight',0.08,'authenticity_risk_penalty',-0.05,'condition_uncertainty_penalty',-0.06,
'region_gap_penalty',0.00,'fee_model_uncertainty_penalty',-0.02)
FROM canonical_assets ca WHERE ca.slug = 'birkin-25-clemence-etoupe-phw-us'
ON CONFLICT (canonical_asset_id, as_of, methodology_version) DO UPDATE
SET q10=EXCLUDED.q10, q50=EXCLUDED.q50, q90=EXCLUDED.q90, liquidity_score=EXCLUDED.liquidity_score,
expected_dts=EXCLUDED.expected_dts, confidence=EXCLUDED.confidence, auth_risk=EXCLUDED.auth_risk,
comp_count=EXCLUDED.comp_count, breakdown=EXCLUDED.breakdown;
INSERT INTO valuation_snapshots (canonical_asset_id, as_of, q10, q50, q90, liquidity_score, expected_dts, confidence, auth_risk, comp_count, methodology_version, breakdown)
SELECT ca.id, CURRENT_DATE, 19550.00, 23000.00, 29210.00, 0.830, 14, 0.810, 0.050, 3, 'v0-stub',
jsonb_build_object('source_quality_weight',0.27,'comparable_similarity_weight',0.20,'sample_depth_weight',0.13,
'recency_weight',0.11,'image_match_weight',0.10,'authenticity_risk_penalty',-0.03,'condition_uncertainty_penalty',-0.04,
'region_gap_penalty',0.00,'fee_model_uncertainty_penalty',-0.01)
FROM canonical_assets ca WHERE ca.slug = 'birkin-30-togo-black-ghw-us'
ON CONFLICT (canonical_asset_id, as_of, methodology_version) DO UPDATE
SET q10=EXCLUDED.q10, q50=EXCLUDED.q50, q90=EXCLUDED.q90, liquidity_score=EXCLUDED.liquidity_score,
expected_dts=EXCLUDED.expected_dts, confidence=EXCLUDED.confidence, auth_risk=EXCLUDED.auth_risk,
comp_count=EXCLUDED.comp_count, breakdown=EXCLUDED.breakdown;
INSERT INTO valuation_snapshots (canonical_asset_id, as_of, q10, q50, q90, liquidity_score, expected_dts, confidence, auth_risk, comp_count, methodology_version, breakdown)
SELECT ca.id, CURRENT_DATE, 19975.00, 23500.00, 29845.00, 0.770, 19, 0.750, 0.060, 2, 'v0-stub',
jsonb_build_object('source_quality_weight',0.25,'comparable_similarity_weight',0.18,'sample_depth_weight',0.10,
'recency_weight',0.10,'image_match_weight',0.09,'authenticity_risk_penalty',-0.04,'condition_uncertainty_penalty',-0.05,
'region_gap_penalty',0.00,'fee_model_uncertainty_penalty',-0.01)
FROM canonical_assets ca WHERE ca.slug = 'birkin-30-epsom-etoupe-phw-us'
ON CONFLICT (canonical_asset_id, as_of, methodology_version) DO UPDATE
SET q10=EXCLUDED.q10, q50=EXCLUDED.q50, q90=EXCLUDED.q90, liquidity_score=EXCLUDED.liquidity_score,
expected_dts=EXCLUDED.expected_dts, confidence=EXCLUDED.confidence, auth_risk=EXCLUDED.auth_risk,
comp_count=EXCLUDED.comp_count, breakdown=EXCLUDED.breakdown;
INSERT INTO valuation_snapshots (canonical_asset_id, as_of, q10, q50, q90, liquidity_score, expected_dts, confidence, auth_risk, comp_count, methodology_version, breakdown)
SELECT ca.id, CURRENT_DATE, 17850.00, 21000.00, 26670.00, 0.580, 38, 0.640, 0.110, 3, 'v0-stub',
jsonb_build_object('source_quality_weight',0.22,'comparable_similarity_weight',0.15,'sample_depth_weight',0.09,
'recency_weight',0.09,'image_match_weight',0.07,'authenticity_risk_penalty',-0.06,'condition_uncertainty_penalty',-0.07,
'region_gap_penalty',0.00,'fee_model_uncertainty_penalty',-0.02)
FROM canonical_assets ca WHERE ca.slug = 'birkin-30-swift-vert-verone-ghw-us'
ON CONFLICT (canonical_asset_id, as_of, methodology_version) DO UPDATE
SET q10=EXCLUDED.q10, q50=EXCLUDED.q50, q90=EXCLUDED.q90, liquidity_score=EXCLUDED.liquidity_score,
expected_dts=EXCLUDED.expected_dts, confidence=EXCLUDED.confidence, auth_risk=EXCLUDED.auth_risk,
comp_count=EXCLUDED.comp_count, breakdown=EXCLUDED.breakdown;
INSERT INTO valuation_snapshots (canonical_asset_id, as_of, q10, q50, q90, liquidity_score, expected_dts, confidence, auth_risk, comp_count, methodology_version, breakdown)
SELECT ca.id, CURRENT_DATE, 16575.00, 19500.00, 24765.00, 0.620, 33, 0.700, 0.070, 3, 'v0-stub',
jsonb_build_object('source_quality_weight',0.23,'comparable_similarity_weight',0.16,'sample_depth_weight',0.11,
'recency_weight',0.09,'image_match_weight',0.08,'authenticity_risk_penalty',-0.04,'condition_uncertainty_penalty',-0.05,
'region_gap_penalty',0.00,'fee_model_uncertainty_penalty',-0.01)
FROM canonical_assets ca WHERE ca.slug = 'birkin-35-togo-gold-ghw-us'
ON CONFLICT (canonical_asset_id, as_of, methodology_version) DO UPDATE
SET q10=EXCLUDED.q10, q50=EXCLUDED.q50, q90=EXCLUDED.q90, liquidity_score=EXCLUDED.liquidity_score,
expected_dts=EXCLUDED.expected_dts, confidence=EXCLUDED.confidence, auth_risk=EXCLUDED.auth_risk,
comp_count=EXCLUDED.comp_count, breakdown=EXCLUDED.breakdown;
INSERT INTO valuation_snapshots (canonical_asset_id, as_of, q10, q50, q90, liquidity_score, expected_dts, confidence, auth_risk, comp_count, methodology_version, breakdown)
SELECT ca.id, CURRENT_DATE, 16150.00, 19000.00, 24130.00, 0.560, 42, 0.660, 0.090, 2, 'v0-stub',
jsonb_build_object('source_quality_weight',0.21,'comparable_similarity_weight',0.15,'sample_depth_weight',0.08,
'recency_weight',0.08,'image_match_weight',0.07,'authenticity_risk_penalty',-0.05,'condition_uncertainty_penalty',-0.06,
'region_gap_penalty',0.00,'fee_model_uncertainty_penalty',-0.02)
FROM canonical_assets ca WHERE ca.slug = 'birkin-35-clemence-black-phw-us'
ON CONFLICT (canonical_asset_id, as_of, methodology_version) DO UPDATE
SET q10=EXCLUDED.q10, q50=EXCLUDED.q50, q90=EXCLUDED.q90, liquidity_score=EXCLUDED.liquidity_score,
expected_dts=EXCLUDED.expected_dts, confidence=EXCLUDED.confidence, auth_risk=EXCLUDED.auth_risk,
comp_count=EXCLUDED.comp_count, breakdown=EXCLUDED.breakdown;
INSERT INTO valuation_snapshots (canonical_asset_id, as_of, q10, q50, q90, liquidity_score, expected_dts, confidence, auth_risk, comp_count, methodology_version, breakdown)
SELECT ca.id, CURRENT_DATE, 31025.00, 36500.00, 46355.00, 0.800, 17, 0.820, 0.040, 3, 'v0-stub',
jsonb_build_object('source_quality_weight',0.28,'comparable_similarity_weight',0.21,'sample_depth_weight',0.12,
'recency_weight',0.12,'image_match_weight',0.10,'authenticity_risk_penalty',-0.02,'condition_uncertainty_penalty',-0.04,
'region_gap_penalty',0.00,'fee_model_uncertainty_penalty',-0.01)
FROM canonical_assets ca WHERE ca.slug = 'kelly-25-sellier-epsom-black-ghw-us'
ON CONFLICT (canonical_asset_id, as_of, methodology_version) DO UPDATE
SET q10=EXCLUDED.q10, q50=EXCLUDED.q50, q90=EXCLUDED.q90, liquidity_score=EXCLUDED.liquidity_score,
expected_dts=EXCLUDED.expected_dts, confidence=EXCLUDED.confidence, auth_risk=EXCLUDED.auth_risk,
comp_count=EXCLUDED.comp_count, breakdown=EXCLUDED.breakdown;
INSERT INTO valuation_snapshots (canonical_asset_id, as_of, q10, q50, q90, liquidity_score, expected_dts, confidence, auth_risk, comp_count, methodology_version, breakdown)
SELECT ca.id, CURRENT_DATE, 25500.00, 30000.00, 38100.00, 0.730, 22, 0.720, 0.060, 2, 'v0-stub',
jsonb_build_object('source_quality_weight',0.25,'comparable_similarity_weight',0.18,'sample_depth_weight',0.09,
'recency_weight',0.10,'image_match_weight',0.09,'authenticity_risk_penalty',-0.04,'condition_uncertainty_penalty',-0.05,
'region_gap_penalty',0.00,'fee_model_uncertainty_penalty',-0.01)
FROM canonical_assets ca WHERE ca.slug = 'kelly-25-retourne-togo-gold-ghw-us'
ON CONFLICT (canonical_asset_id, as_of, methodology_version) DO UPDATE
SET q10=EXCLUDED.q10, q50=EXCLUDED.q50, q90=EXCLUDED.q90, liquidity_score=EXCLUDED.liquidity_score,
expected_dts=EXCLUDED.expected_dts, confidence=EXCLUDED.confidence, auth_risk=EXCLUDED.auth_risk,
comp_count=EXCLUDED.comp_count, breakdown=EXCLUDED.breakdown;
INSERT INTO valuation_snapshots (canonical_asset_id, as_of, q10, q50, q90, liquidity_score, expected_dts, confidence, auth_risk, comp_count, methodology_version, breakdown)
SELECT ca.id, CURRENT_DATE, 23375.00, 27500.00, 34925.00, 0.750, 20, 0.760, 0.060, 3, 'v0-stub',
jsonb_build_object('source_quality_weight',0.26,'comparable_similarity_weight',0.19,'sample_depth_weight',0.11,
'recency_weight',0.11,'image_match_weight',0.09,'authenticity_risk_penalty',-0.04,'condition_uncertainty_penalty',-0.05,
'region_gap_penalty',0.00,'fee_model_uncertainty_penalty',-0.01)
FROM canonical_assets ca WHERE ca.slug = 'kelly-28-sellier-epsom-etoupe-phw-us'
ON CONFLICT (canonical_asset_id, as_of, methodology_version) DO UPDATE
SET q10=EXCLUDED.q10, q50=EXCLUDED.q50, q90=EXCLUDED.q90, liquidity_score=EXCLUDED.liquidity_score,
expected_dts=EXCLUDED.expected_dts, confidence=EXCLUDED.confidence, auth_risk=EXCLUDED.auth_risk,
comp_count=EXCLUDED.comp_count, breakdown=EXCLUDED.breakdown;
INSERT INTO valuation_snapshots (canonical_asset_id, as_of, q10, q50, q90, liquidity_score, expected_dts, confidence, auth_risk, comp_count, methodology_version, breakdown)
SELECT ca.id, CURRENT_DATE, 19975.00, 23500.00, 29845.00, 0.790, 18, 0.780, 0.050, 2, 'v0-stub',
jsonb_build_object('source_quality_weight',0.26,'comparable_similarity_weight',0.19,'sample_depth_weight',0.10,
'recency_weight',0.11,'image_match_weight',0.09,'authenticity_risk_penalty',-0.03,'condition_uncertainty_penalty',-0.05,
'region_gap_penalty',0.00,'fee_model_uncertainty_penalty',-0.01)
FROM canonical_assets ca WHERE ca.slug = 'kelly-28-retourne-togo-black-ghw-us'
ON CONFLICT (canonical_asset_id, as_of, methodology_version) DO UPDATE
SET q10=EXCLUDED.q10, q50=EXCLUDED.q50, q90=EXCLUDED.q90, liquidity_score=EXCLUDED.liquidity_score,
expected_dts=EXCLUDED.expected_dts, confidence=EXCLUDED.confidence, auth_risk=EXCLUDED.auth_risk,
comp_count=EXCLUDED.comp_count, breakdown=EXCLUDED.breakdown;
INSERT INTO valuation_snapshots (canonical_asset_id, as_of, q10, q50, q90, liquidity_score, expected_dts, confidence, auth_risk, comp_count, methodology_version, breakdown)
SELECT ca.id, CURRENT_DATE, 14875.00, 17500.00, 22225.00, 0.600, 36, 0.650, 0.080, 2, 'v0-stub',
jsonb_build_object('source_quality_weight',0.22,'comparable_similarity_weight',0.16,'sample_depth_weight',0.08,
'recency_weight',0.09,'image_match_weight',0.07,'authenticity_risk_penalty',-0.05,'condition_uncertainty_penalty',-0.06,
'region_gap_penalty',0.00,'fee_model_uncertainty_penalty',-0.02)
FROM canonical_assets ca WHERE ca.slug = 'kelly-32-retourne-clemence-gold-ghw-us'
ON CONFLICT (canonical_asset_id, as_of, methodology_version) DO UPDATE
SET q10=EXCLUDED.q10, q50=EXCLUDED.q50, q90=EXCLUDED.q90, liquidity_score=EXCLUDED.liquidity_score,
expected_dts=EXCLUDED.expected_dts, confidence=EXCLUDED.confidence, auth_risk=EXCLUDED.auth_risk,
comp_count=EXCLUDED.comp_count, breakdown=EXCLUDED.breakdown;
INSERT INTO valuation_snapshots (canonical_asset_id, as_of, q10, q50, q90, liquidity_score, expected_dts, confidence, auth_risk, comp_count, methodology_version, breakdown)
SELECT ca.id, CURRENT_DATE, 18275.00, 21500.00, 27305.00, 0.660, 28, 0.710, 0.070, 3, 'v0-stub',
jsonb_build_object('source_quality_weight',0.24,'comparable_similarity_weight',0.17,'sample_depth_weight',0.10,
'recency_weight',0.10,'image_match_weight',0.08,'authenticity_risk_penalty',-0.04,'condition_uncertainty_penalty',-0.05,
'region_gap_penalty',0.00,'fee_model_uncertainty_penalty',-0.01)
FROM canonical_assets ca WHERE ca.slug = 'kelly-32-sellier-epsom-black-ghw-us'
ON CONFLICT (canonical_asset_id, as_of, methodology_version) DO UPDATE
SET q10=EXCLUDED.q10, q50=EXCLUDED.q50, q90=EXCLUDED.q90, liquidity_score=EXCLUDED.liquidity_score,
expected_dts=EXCLUDED.expected_dts, confidence=EXCLUDED.confidence, auth_risk=EXCLUDED.auth_risk,
comp_count=EXCLUDED.comp_count, breakdown=EXCLUDED.breakdown;
-- ---------------------------------------------------------------------------
-- indices — 3 new benchmarks with 4 history points each (2022-12-31 → 2025-12-31)
-- ---------------------------------------------------------------------------
INSERT INTO indices (slug, name, definition, methodology_version) VALUES
('birkin-25-neutral',
'Birkin 25 — Neutral Colors',
jsonb_build_object('size','25','colors',jsonb_build_array('Black','Gold','Etoupe','Nata'),'region','US'),
'v0-stub')
ON CONFLICT (slug) DO NOTHING;
INSERT INTO index_points (index_id, as_of, value, change_pct)
SELECT i.id, '2022-12-31'::date, 25800.00, NULL FROM indices i WHERE i.slug = 'birkin-25-neutral'
ON CONFLICT (index_id, as_of) DO NOTHING;
INSERT INTO index_points (index_id, as_of, value, change_pct)
SELECT i.id, '2023-12-31'::date, 27900.00, 0.0814 FROM indices i WHERE i.slug = 'birkin-25-neutral'
ON CONFLICT (index_id, as_of) DO NOTHING;
INSERT INTO index_points (index_id, as_of, value, change_pct)
SELECT i.id, '2024-12-31'::date, 29200.00, 0.0466 FROM indices i WHERE i.slug = 'birkin-25-neutral'
ON CONFLICT (index_id, as_of) DO NOTHING;
INSERT INTO index_points (index_id, as_of, value, change_pct)
SELECT i.id, '2025-12-31'::date, 30650.00, 0.0497 FROM indices i WHERE i.slug = 'birkin-25-neutral'
ON CONFLICT (index_id, as_of) DO NOTHING;
INSERT INTO indices (slug, name, definition, methodology_version) VALUES
('kelly-sellier-premium',
'Kelly Sellier — All Sizes',
jsonb_build_object('construction','Sellier','sizes',jsonb_build_array('25','28','32','35'),'region','US'),
'v0-stub')
ON CONFLICT (slug) DO NOTHING;
INSERT INTO index_points (index_id, as_of, value, change_pct)
SELECT i.id, '2022-12-31'::date, 24200.00, NULL FROM indices i WHERE i.slug = 'kelly-sellier-premium'
ON CONFLICT (index_id, as_of) DO NOTHING;
INSERT INTO index_points (index_id, as_of, value, change_pct)
SELECT i.id, '2023-12-31'::date, 26700.00, 0.1033 FROM indices i WHERE i.slug = 'kelly-sellier-premium'
ON CONFLICT (index_id, as_of) DO NOTHING;
INSERT INTO index_points (index_id, as_of, value, change_pct)
SELECT i.id, '2024-12-31'::date, 27950.00, 0.0468 FROM indices i WHERE i.slug = 'kelly-sellier-premium'
ON CONFLICT (index_id, as_of) DO NOTHING;
INSERT INTO index_points (index_id, as_of, value, change_pct)
SELECT i.id, '2025-12-31'::date, 29550.00, 0.0572 FROM indices i WHERE i.slug = 'kelly-sellier-premium'
ON CONFLICT (index_id, as_of) DO NOTHING;
INSERT INTO indices (slug, name, definition, methodology_version) VALUES
('recent-stamp-premium',
'Recent Stamp Premium — Birkin/Kelly 2023+',
jsonb_build_object('families',jsonb_build_array('birkin','kelly'),'date_stamp_year_min',2023,'region','US'),
'v0-stub')
ON CONFLICT (slug) DO NOTHING;
INSERT INTO index_points (index_id, as_of, value, change_pct)
SELECT i.id, '2022-12-31'::date, 1.080, NULL FROM indices i WHERE i.slug = 'recent-stamp-premium'
ON CONFLICT (index_id, as_of) DO NOTHING;
INSERT INTO index_points (index_id, as_of, value, change_pct)
SELECT i.id, '2023-12-31'::date, 1.115, 0.0324 FROM indices i WHERE i.slug = 'recent-stamp-premium'
ON CONFLICT (index_id, as_of) DO NOTHING;
INSERT INTO index_points (index_id, as_of, value, change_pct)
SELECT i.id, '2024-12-31'::date, 1.142, 0.0242 FROM indices i WHERE i.slug = 'recent-stamp-premium'
ON CONFLICT (index_id, as_of) DO NOTHING;
INSERT INTO index_points (index_id, as_of, value, change_pct)
SELECT i.id, '2025-12-31'::date, 1.168, 0.0228 FROM indices i WHERE i.slug = 'recent-stamp-premium'
ON CONFLICT (index_id, as_of) DO NOTHING;
-- ---------------------------------------------------------------------------
-- v0 yolo tick #8: MSRP history (only what BLUEPRINT.md authoritatively
-- gives us — Birkin 30 Togo 2022-2026). Other assets stay null until
-- boutique receipts or specialist trackers feed in. No synthesized data.
-- ---------------------------------------------------------------------------
INSERT INTO msrp_observations (canonical_asset_id, msrp_usd, currency, observed_at, source, source_note)
SELECT ca.id, 11300.00, 'USD', '2022-01-01'::date, 'auction_house_commentary',
'BLUEPRINT.md table — Sotheby''s reference series, US Birkin 30 Togo'
FROM canonical_assets ca WHERE ca.slug = 'birkin-30-togo-gold-ghw-us'
ON CONFLICT (canonical_asset_id, source, observed_at) DO NOTHING;
INSERT INTO msrp_observations (canonical_asset_id, msrp_usd, currency, observed_at, source, source_note)
SELECT ca.id, 11600.00, 'USD', '2023-01-01'::date, 'auction_house_commentary',
'BLUEPRINT.md table — Sotheby''s reference series, US Birkin 30 Togo'
FROM canonical_assets ca WHERE ca.slug = 'birkin-30-togo-gold-ghw-us'
ON CONFLICT (canonical_asset_id, source, observed_at) DO NOTHING;
INSERT INTO msrp_observations (canonical_asset_id, msrp_usd, currency, observed_at, source, source_note)
SELECT ca.id, 12500.00, 'USD', '2024-01-01'::date, 'auction_house_commentary',
'BLUEPRINT.md table — Sotheby''s reference series, US Birkin 30 Togo'
FROM canonical_assets ca WHERE ca.slug = 'birkin-30-togo-gold-ghw-us'
ON CONFLICT (canonical_asset_id, source, observed_at) DO NOTHING;
INSERT INTO msrp_observations (canonical_asset_id, msrp_usd, currency, observed_at, source, source_note)
SELECT ca.id, 13900.00, 'USD', '2025-01-01'::date, 'auction_house_commentary',
'BLUEPRINT.md table — Sotheby''s reference series, US Birkin 30 Togo'
FROM canonical_assets ca WHERE ca.slug = 'birkin-30-togo-gold-ghw-us'
ON CONFLICT (canonical_asset_id, source, observed_at) DO NOTHING;
INSERT INTO msrp_observations (canonical_asset_id, msrp_usd, currency, observed_at, source, source_note)
SELECT ca.id, 14900.00, 'USD', '2026-01-01'::date, 'auction_house_commentary',
'BLUEPRINT.md table — Sotheby''s reference series, US Birkin 30 Togo'
FROM canonical_assets ca WHERE ca.slug = 'birkin-30-togo-gold-ghw-us'
ON CONFLICT (canonical_asset_id, source, observed_at) DO NOTHING;
-- ===========================================================================
-- v0 yolo tick #13: REAL Sotheby's auction-result data
-- ===========================================================================
-- Sourced 2026-05-10 from four publicly-published Sotheby's editorial pages
-- (auction results, public records). Every row carries:
-- * provenance via raw_observations.payload.source_url
-- * source_note attribution string per Steve's no-fake-data rule
-- * region = auction city (HK / FR) so per-METHODOLOGY § 11 these
-- transactions are excluded from US-region comp pools
--
-- Sources (cited in raw_observations.payload):
-- 1. https://www.sothebys.com/en/articles/the-10-most-expensive-hermes-bags-sold-at-auction-in-2024
-- 2. https://www.sothebys.com/en/articles/the-11-most-expensive-hermes-bags-sold-at-auction-in-the-first-half-2024
-- 3. https://www.sothebys.com/en/articles/the-newest-hermes-bag-collector-obsession-the-birkin-20
-- 4. https://www.sothebys.com/en/articles/secondary-market-signals-after-hermes-impressive-2024-results
--
-- Compliance: Sotheby's auction results are public records by design;
-- robots-friendly; no auth bypass; no images. Steve's standing rule
-- "always stay legal for scraping sites if not allowed" satisfied —
-- published editorial w/ realized prices is the textbook safe source.
-- ---------------------------------------------------------------------------
-- New family: Constance
-- ---------------------------------------------------------------------------
INSERT INTO model_families (brand_id, name, slug)
SELECT b.id, 'Constance', 'constance' FROM brands b WHERE b.slug = 'hermes'
ON CONFLICT (brand_id, slug) DO NOTHING;
-- ---------------------------------------------------------------------------
-- New canonical_assets — exotic + special-edition configurations Sotheby's
-- actually publishes results for. region='US' (buyer region modeled);
-- transactions.region carries the auction city.
-- ---------------------------------------------------------------------------
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'birkin-25-niloticus-himalaya-palladium-us', '25', 'Niloticus Crocodile', 'Himalaya', 'Palladium', 'Retourne', 'US',
jsonb_build_object('display_name','Birkin 25 — Niloticus Himalaya — Palladium','exotic',true)
FROM model_families mf JOIN brands b ON b.id = mf.brand_id WHERE b.slug='hermes' AND mf.slug='birkin'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'birkin-30-niloticus-himalaya-palladium-us', '30', 'Niloticus Crocodile', 'Himalaya', 'Palladium', 'Retourne', 'US',
jsonb_build_object('display_name','Birkin 30 — Niloticus Himalaya — Palladium','exotic',true)
FROM model_families mf JOIN brands b ON b.id = mf.brand_id WHERE b.slug='hermes' AND mf.slug='birkin'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'birkin-30-niloticus-poudre-paveddiamond-us', '30', 'Niloticus Crocodile', 'Poudre', '18K White Gold + Diamond Pave', 'Retourne', 'US',
jsonb_build_object('display_name','Birkin 30 — Niloticus Poudre — Paved Diamond','exotic',true,'diamond_carats',10.84)
FROM model_families mf JOIN brands b ON b.id = mf.brand_id WHERE b.slug='hermes' AND mf.slug='birkin'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'kelly-25-sellier-niloticus-himalaya-paveddiamond-us', '25', 'Niloticus Crocodile', 'Himalaya', '18K White Gold + Diamond Pave', 'Sellier', 'US',
jsonb_build_object('display_name','Kelly 25 Sellier — Niloticus Himalaya — Paved Diamond','exotic',true)
FROM model_families mf JOIN brands b ON b.id = mf.brand_id WHERE b.slug='hermes' AND mf.slug='kelly'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'kelly-25-sellier-box-midas-yellowgold-us', '25', 'Box Calf', 'Black', '18K Yellow Gold (183.18g)', 'Sellier', 'US',
jsonb_build_object('display_name','Kelly 25 Sellier Midas — Black Box — Yellow Gold','special_edition','Midas')
FROM model_families mf JOIN brands b ON b.id = mf.brand_id WHERE b.slug='hermes' AND mf.slug='kelly'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'kelly-28-niloticus-himalaya-palladium-us', '28', 'Niloticus Crocodile', 'Himalaya', 'Palladium', 'Retourne', 'US',
jsonb_build_object('display_name','Kelly 28 — Niloticus Himalaya — Palladium','exotic',true)
FROM model_families mf JOIN brands b ON b.id = mf.brand_id WHERE b.slug='hermes' AND mf.slug='kelly'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'birkin-20-faubourg-snow-palladium-us', '20', 'Mixed (Alligator/Togo/Chevre)', 'Neige (Snow)', 'Palladium', 'Retourne', 'US',
jsonb_build_object('display_name','Birkin 20 Faubourg — Snow','special_edition','Faubourg','exotic',true)
FROM model_families mf JOIN brands b ON b.id = mf.brand_id WHERE b.slug='hermes' AND mf.slug='birkin'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'birkin-20-faubourg-midnight-blackpvd-us', '20', 'Mixed (Alligator/Swift)', 'Midnight (Black)', 'Black PVD', 'Retourne', 'US',
jsonb_build_object('display_name','Birkin 20 Faubourg — Midnight','special_edition','Faubourg','exotic',true)
FROM model_families mf JOIN brands b ON b.id = mf.brand_id WHERE b.slug='hermes' AND mf.slug='birkin'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'birkin-20-sellier-alligator-grisciment-gold-us', '20', 'Matte Alligator', 'Gris Ciment', 'Gold', 'Sellier', 'US',
jsonb_build_object('display_name','Birkin 20 Sellier — Gris Ciment Alligator','exotic',true)
FROM model_families mf JOIN brands b ON b.id = mf.brand_id WHERE b.slug='hermes' AND mf.slug='birkin'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'birkin-20-sellier-alligator-havane-palladium-us', '20', 'Matte Alligator', 'Havane', 'Palladium', 'Sellier', 'US',
jsonb_build_object('display_name','Birkin 20 Sellier — Havane Alligator','exotic',true)
FROM model_families mf JOIN brands b ON b.id = mf.brand_id WHERE b.slug='hermes' AND mf.slug='birkin'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'birkin-20-sellier-alligator-vertdeau-us', '20', 'Matte Alligator', 'Vert d''Eau', 'Palladium', 'Sellier', 'US',
jsonb_build_object('display_name','Birkin 20 Sellier — Vert d''Eau Alligator','exotic',true)
FROM model_families mf JOIN brands b ON b.id = mf.brand_id WHERE b.slug='hermes' AND mf.slug='birkin'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'birkin-20-faubourg-rainydays-grisciment-palladium-us', '20', 'Matte Alligator', 'Gris Ciment', 'Palladium', 'Sellier', 'US',
jsonb_build_object('display_name','Birkin 20 Faubourg Rainy Days — Gris Ciment','special_edition','Rainy Days','exotic',true)
FROM model_families mf JOIN brands b ON b.id = mf.brand_id WHERE b.slug='hermes' AND mf.slug='birkin'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'birkin-20-sellier-alligator-vanille-palladium-fr', '20', 'Matte Alligator Mississippiensis', 'Vanille', 'Palladium', 'Sellier', 'US',
jsonb_build_object('display_name','Birkin 20 Sellier — Vanille Alligator','exotic',true,'note','record for non-Faubourg Birkin 20')
FROM model_families mf JOIN brands b ON b.id = mf.brand_id WHERE b.slug='hermes' AND mf.slug='birkin'
ON CONFLICT (slug) DO NOTHING;
INSERT INTO canonical_assets (model_family_id, slug, size, material, color, hardware, construction, region, attrs)
SELECT mf.id, 'constance-24-niloticus-himalaya-palladium-us', '24', 'Niloticus Crocodile', 'Himalaya', 'Palladium', NULL, 'US',
jsonb_build_object('display_name','Constance 24 — Niloticus Himalaya','exotic',true)
FROM model_families mf JOIN brands b ON b.id = mf.brand_id WHERE b.slug='hermes' AND mf.slug='constance'
ON CONFLICT (slug) DO NOTHING;
-- ---------------------------------------------------------------------------
-- raw_observations: one per published lot, full source_url provenance
-- ---------------------------------------------------------------------------
-- "10 Most Expensive 2024" article (URL: /articles/the-10-most-expensive-hermes-bags-sold-at-auction-in-2024)
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-2024-top10#rank-1', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-10-most-expensive-hermes-bags-sold-at-auction-in-2024',
'rank',1,'lot_desc','Kelly 25 White Niloticus Himalaya 18K + 298 diamonds (3.50ct), 2021',
'realized_usd',330000,'sale_location','Hong Kong')
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-2024-top10#rank-2', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-10-most-expensive-hermes-bags-sold-at-auction-in-2024',
'rank',2,'lot_desc','Kelly 25 Blanc Niloticus Himalaya 14K + diamond paved, 2022',
'realized_usd',284000,'sale_location','Paris')
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-2024-top10#rank-3', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-10-most-expensive-hermes-bags-sold-at-auction-in-2024',
'rank',3,'lot_desc','Kelly 28 White Niloticus Himalaya 18K + diamonds, 2019',
'realized_usd',234000,'sale_location','Hong Kong')
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-2024-top10#rank-3b', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-10-most-expensive-hermes-bags-sold-at-auction-in-2024',
'rank',3,'lot_desc','Birkin 20 Faubourg Snow (Beton/Blanc/Bleu Brume), 2021',
'realized_usd',234000,'sale_location','Hong Kong')
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-2024-top10#rank-5', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-10-most-expensive-hermes-bags-sold-at-auction-in-2024',
'rank',5,'lot_desc','Birkin 20 Faubourg Snow (Beton/Blanc/Bleu Brume), 2022',
'realized_usd',227000,'sale_location','Paris')
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-2024-top10#rank-6', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-10-most-expensive-hermes-bags-sold-at-auction-in-2024',
'rank',6,'lot_desc','Birkin 25 White Niloticus Himalaya palladium, 2020',
'realized_usd',187200,'sale_location','Hong Kong')
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-2024-top10#rank-7', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-10-most-expensive-hermes-bags-sold-at-auction-in-2024',
'rank',7,'lot_desc','Birkin 20 Faubourg Midnight (Black Alligator + Black Swift), 2022',
'realized_usd',181000,'sale_location','Hong Kong')
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-2024-top10#rank-8', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-10-most-expensive-hermes-bags-sold-at-auction-in-2024',
'rank',8,'lot_desc','Kelly 25 Sellier Midas Black Box Calf 18K Yellow Gold (183.18g), 2023',
'realized_usd',157000,'sale_location','Hong Kong')
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-2024-top10#rank-9', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-10-most-expensive-hermes-bags-sold-at-auction-in-2024',
'rank',9,'lot_desc','Birkin 25 Blanc Niloticus Himalaya palladium, 2022',
'realized_usd',156000,'sale_location','Paris')
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-2024-top10#rank-10', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-10-most-expensive-hermes-bags-sold-at-auction-in-2024',
'rank',10,'lot_desc','Kelly 28 Blanc Niloticus Himalaya palladium, 2022',
'realized_usd',128000,'sale_location','Paris')
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
-- "11 Most Expensive H1 2024" — adds the lower ranks plus Birkin 30 entries
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-h1-2024#rank-8', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-11-most-expensive-hermes-bags-sold-at-auction-in-the-first-half-2024',
'rank',8,'lot_desc','Birkin 30 White Niloticus Himalaya palladium, 2015',
'realized_usd',115000,'sale_location','Hong Kong')
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-h1-2024#rank-9', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-11-most-expensive-hermes-bags-sold-at-auction-in-the-first-half-2024',
'rank',9,'lot_desc','Birkin 30 Poudre Niloticus 18K WG + Diamond (10.84ct), 2007',
'realized_usd',107000,'sale_location','Hong Kong','condition_note','very faint scratches on hardware')
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-h1-2024#rank-10', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-11-most-expensive-hermes-bags-sold-at-auction-in-the-first-half-2024',
'rank',10,'lot_desc','Birkin 20 Sellier Gris Ciment Alligator gold, 2023',
'realized_usd',99000,'sale_location','Hong Kong')
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-h1-2024#rank-11', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-11-most-expensive-hermes-bags-sold-at-auction-in-the-first-half-2024',
'rank',11,'lot_desc','Constance 24 White Niloticus Himalaya palladium, 2023',
'realized_usd',99000,'sale_location','Hong Kong','condition_note','pristine, plastic on hardware')
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
-- "Birkin 20 Collector" article (more recent: 2025/2026)
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-birkin-20#bleu-marine-2025', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-newest-hermes-bag-collector-obsession-the-birkin-20',
'lot_desc','Birkin 20 Faubourg Bleu Marine Alligator (Day edition), 2025',
'realized_usd',152400,'sale_location','Hong Kong')
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-birkin-20#rainy-days-grisciment-2025', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-newest-hermes-bag-collector-obsession-the-birkin-20',
'lot_desc','Birkin 20 Faubourg Gris Ciment Alligator palladium (Rainy Days edition), 2025',
'realized_usd',203200,'sale_location','Hong Kong')
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-birkin-20#vertdeau-2023', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-newest-hermes-bag-collector-obsession-the-birkin-20',
'lot_desc','Birkin 20 Sellier Vert d''Eau Alligator, 2023',
'realized_usd',115000,'sale_location','Hong Kong','original_currency','HKD','original_amount',889000)
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-birkin-20#grisciment-2024', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-newest-hermes-bag-collector-obsession-the-birkin-20',
'lot_desc','Birkin 20 Sellier Gris Ciment Alligator, 2024',
'realized_usd',98700,'sale_location','Hong Kong','original_currency','HKD','original_amount',762000)
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-birkin-20#havane-2025', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-newest-hermes-bag-collector-obsession-the-birkin-20',
'lot_desc','Birkin 20 Sellier Havane Alligator palladium, 2025',
'realized_usd',88900,'sale_location','Sotheby''s')
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
INSERT INTO raw_observations (source_id, external_id, kind, payload)
SELECT s.id, 'sothebys-editorial-birkin-20#vanille-2026', 'lot',
jsonb_build_object('source_url','https://www.sothebys.com/en/articles/the-newest-hermes-bag-collector-obsession-the-birkin-20',
'lot_desc','Birkin 20 Sellier Vanille Alligator Mississippiensis palladium, 2026',
'realized_usd',119000,'sale_location','Paris','original_currency','EUR','original_amount',108800,
'note','record for non-Faubourg Birkin 20')
FROM sources s WHERE s.slug='sothebys' ON CONFLICT (source_id, external_id, kind) DO NOTHING;
-- ---------------------------------------------------------------------------
-- transactions: real Sotheby's-published auction realizations
-- net = gross * 0.82 per METHODOLOGY § 4 (Sotheby's hammer-to-net ≈ -18%)
-- transacted_at uses sale-month midpoint when only month known;
-- "H1 2024" → 2024-04-15 ; "H2 2024" → 2024-09-15.
-- region = auction city (HK / FR), excluded from US comp pool per § 11.
-- ---------------------------------------------------------------------------
-- Top 10 of 2024
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 330000, 330000, 270600, 330000, 'USD', 'HK', 5,
jsonb_build_object('store_fresh',false,'date_stamp_year',2021,'note','paved diamond, 18K white gold'),
'2024-09-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='kelly-25-sellier-niloticus-himalaya-paveddiamond-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-2024-top10#rank-1'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 284000, 284000, 232880, 284000, 'USD', 'FR', 5,
jsonb_build_object('store_fresh',false,'date_stamp_year',2022,'note','14K white gold paved diamond'),
'2024-09-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='kelly-25-sellier-niloticus-himalaya-paveddiamond-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-2024-top10#rank-2'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 234000, 234000, 191880, 234000, 'USD', 'HK', 5,
jsonb_build_object('store_fresh',false,'date_stamp_year',2019,'plastic_intact',true),
'2024-09-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='kelly-28-niloticus-himalaya-palladium-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-2024-top10#rank-3'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 234000, 234000, 191880, 234000, 'USD', 'HK', 4,
jsonb_build_object('store_fresh',false,'date_stamp_year',2021,'special_edition','Faubourg'),
'2024-09-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='birkin-20-faubourg-snow-palladium-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-2024-top10#rank-3b'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 227000, 227000, 186140, 227000, 'USD', 'FR', 4,
jsonb_build_object('store_fresh',false,'date_stamp_year',2022,'special_edition','Faubourg'),
'2024-04-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='birkin-20-faubourg-snow-palladium-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-2024-top10#rank-5'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 187200, 187200, 153504, 187200, 'USD', 'HK', 4,
jsonb_build_object('store_fresh',false,'date_stamp_year',2020),
'2024-09-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='birkin-25-niloticus-himalaya-palladium-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-2024-top10#rank-6'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 181000, 181000, 148420, 181000, 'USD', 'HK', 4,
jsonb_build_object('store_fresh',false,'date_stamp_year',2022,'special_edition','Faubourg-Midnight'),
'2024-02-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='birkin-20-faubourg-midnight-blackpvd-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-2024-top10#rank-7'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 157000, 157000, 128740, 157000, 'USD', 'HK', 4,
jsonb_build_object('store_fresh',false,'date_stamp_year',2023,'special_edition','Midas','hardware_grams',183.18),
'2024-09-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='kelly-25-sellier-box-midas-yellowgold-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-2024-top10#rank-8'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 156000, 156000, 127920, 156000, 'USD', 'FR', 4,
jsonb_build_object('store_fresh',false,'date_stamp_year',2022),
'2024-04-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='birkin-25-niloticus-himalaya-palladium-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-2024-top10#rank-9'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 128000, 128000, 104960, 128000, 'USD', 'FR', 4,
jsonb_build_object('store_fresh',false,'date_stamp_year',2022),
'2024-04-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='kelly-28-niloticus-himalaya-palladium-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-2024-top10#rank-10'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
-- H1 2024 article (Birkin 30 Himalayas + Constance + B20)
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 115000, 115000, 94300, 115000, 'USD', 'HK', 4,
jsonb_build_object('store_fresh',false,'date_stamp_year',2015),
'2024-04-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='birkin-30-niloticus-himalaya-palladium-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-h1-2024#rank-8'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 107000, 107000, 87740, 107000, 'USD', 'HK', 3,
jsonb_build_object('store_fresh',false,'date_stamp_year',2007,'hardware_scratching','very faint','diamond_carats',10.84),
'2024-04-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='birkin-30-niloticus-poudre-paveddiamond-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-h1-2024#rank-9'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 99000, 99000, 81180, 99000, 'USD', 'HK', 4,
jsonb_build_object('store_fresh',false,'date_stamp_year',2023),
'2024-04-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='birkin-20-sellier-alligator-grisciment-gold-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-h1-2024#rank-10'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 99000, 99000, 81180, 99000, 'USD', 'HK', 5,
jsonb_build_object('store_fresh',false,'date_stamp_year',2023,'plastic_intact',true),
'2024-04-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='constance-24-niloticus-himalaya-palladium-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-h1-2024#rank-11'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
-- Birkin 20 Collector article (2025 + 2026 records)
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 152400, 152400, 124968, 152400, 'USD', 'HK', 4,
jsonb_build_object('special_edition','Faubourg-Day','color_note','Bleu Marine'),
'2025-04-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='birkin-20-faubourg-snow-palladium-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-birkin-20#bleu-marine-2025'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 203200, 203200, 166624, 203200, 'USD', 'HK', 4,
jsonb_build_object('special_edition','Faubourg-RainyDays'),
'2025-09-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='birkin-20-faubourg-rainydays-grisciment-palladium-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-birkin-20#rainy-days-grisciment-2025'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 115000, 115000, 94300, 115000, 'USD', 'HK', 4,
jsonb_build_object('original_currency','HKD','original_amount',889000),
'2023-09-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='birkin-20-sellier-alligator-vertdeau-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-birkin-20#vertdeau-2023'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 98700, 98700, 80934, 98700, 'USD', 'HK', 4,
jsonb_build_object('original_currency','HKD','original_amount',762000,'date_stamp_year',2024),
'2024-09-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='birkin-20-sellier-alligator-grisciment-gold-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-birkin-20#grisciment-2024'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 88900, 88900, 72898, 88900, 'USD', 'HK', 4,
jsonb_build_object('date_stamp_year',2025),
'2025-04-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='birkin-20-sellier-alligator-havane-palladium-us'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-birkin-20#havane-2025'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
INSERT INTO transactions (canonical_asset_id, source_id, raw_observation_id,
gross_transaction_price, all_in_buyer_price, expected_net_seller_proceeds,
normalized_market_value, currency, region, condition_grade,
condition_facets, transacted_at)
SELECT ca.id, s.id, ro.id, 119000, 119000, 97580, 119000, 'USD', 'FR', 5,
jsonb_build_object('original_currency','EUR','original_amount',108800,'date_stamp_year',2026,
'note','record for non-Faubourg Birkin 20'),
'2026-04-15 00:00:00+00'::timestamptz
FROM canonical_assets ca, sources s, raw_observations ro
WHERE ca.slug='birkin-20-sellier-alligator-vanille-palladium-fr'
AND s.slug='sothebys' AND ro.external_id='sothebys-editorial-birkin-20#vanille-2026'
ON CONFLICT ON CONSTRAINT transactions_natural_key DO NOTHING;
-- ---------------------------------------------------------------------------
-- v0 yolo tick #14: auto-snapshot generation
-- ---------------------------------------------------------------------------
-- For any canonical_asset that has ≥1 transaction but NO valuation_snapshot,
-- insert a v0-stub snapshot derived from its real comps:
-- q50 = PERCENTILE_CONT(0.5) of gross_transaction_price (last 24mo)
-- q10 = q50 * 0.85 (per METHODOLOGY § 6 heuristic)
-- q90 = q50 * 1.27
-- comp_count = trailing-24mo count
-- liquidity / dts / confidence stay null — those are live-computed in
-- lib/valuation.js from comps, so seed values would be inert anyway.
-- auth_risk = 0.06 (low default for tier-1 auction provenance)
--
-- Idempotent via the (canonical_asset_id, as_of, methodology_version)
-- UNIQUE constraint — re-running just refreshes today's snapshot.
WITH stats AS (
-- No 24-month filter here — we want a snapshot even for assets whose
-- only comps are older than 2 years (rare exotic configs trade
-- infrequently). The live liquidity_score in lib/valuation.js still
-- applies the 24-month window per METHODOLOGY § 7 — so old-comp
-- snapshots show up in the grid with low liquidity, which is honest.
SELECT t.canonical_asset_id,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY t.gross_transaction_price) AS med,
COUNT(*)::int AS n
FROM transactions t
GROUP BY t.canonical_asset_id
)
INSERT INTO valuation_snapshots
(canonical_asset_id, as_of, q10, q50, q90,
liquidity_score, expected_dts, confidence, auth_risk,
comp_count, methodology_version, breakdown)
SELECT s.canonical_asset_id,
CURRENT_DATE,
ROUND((s.med * 0.85)::numeric, 2),
ROUND(s.med::numeric, 2),
ROUND((s.med * 1.27)::numeric, 2),
NULL, -- live-computed at query time
NULL, -- live-computed
NULL, -- live-computed
0.060,
s.n,
'v0-stub',
jsonb_build_object('source','auto-from-real-comps','generator','tick-14',
'method','median q50 + 0.85/1.27 quantile heuristic')
FROM stats s
LEFT JOIN valuation_snapshots vs
ON vs.canonical_asset_id = s.canonical_asset_id
AND vs.as_of = CURRENT_DATE
AND vs.methodology_version = 'v0-stub'
WHERE vs.id IS NULL -- only fill in the gaps
AND s.med IS NOT NULL AND s.med > 0
ON CONFLICT (canonical_asset_id, as_of, methodology_version) DO NOTHING;