← back to Lifestyle Asset Intel
yolo tick #13: real Sotheby's auction-result data ingest
d9aa2ade2de15fca943090cdd572dc7801452dfe · 2026-05-10 00:40:16 -0700 · Steve Abrams
Pivoted from feature polish to actual data acquisition. Per Steve's
"build and look anywhere for data" + "always stay legal for scraping"
instructions, I went after Sotheby's editorial pages — public records
of realized auction prices, robots-friendly, no auth bypass, no
images. NOT eBay sold-history (Marketplace Insights API gated, HTML
scrape is TOS-prohibited). NOT third-party aggregators without
license review.
Sources (cited in raw_observations.payload.source_url for every row):
1. /articles/the-10-most-expensive-hermes-bags-sold-at-auction-in-2024
2. /articles/the-11-most-expensive-hermes-bags-sold-at-auction-in-the-first-half-2024
3. /articles/the-newest-hermes-bag-collector-obsession-the-birkin-20
4. /articles/secondary-market-signals-after-hermes-impressive-2024-results
Schema additions:
- 1 new model_family: Constance
- 14 new canonical_assets (mostly exotic Niloticus Himalaya configs +
Faubourg / Midas / Rainy Days / Vert d'Eau / Vanille / Havane —
the Sotheby's headline lots)
- 20 raw_observations with full source_url + lot description in
payload jsonb (idempotent via UNIQUE source+external_id+kind)
- 22 transactions linked back to their raw_observation. Net seller
proceeds computed as gross * 0.82 per METHODOLOGY § 4 Sotheby's
hammer-to-net haircut.
Region attribution: transactions.region carries the auction city
(HK / FR), so per METHODOLOGY § 11 they're stored as cross-region
comps and excluded from US comp pools — the canonical assets retain
region='US' (buyer region we model).
Top realized prices now in DB:
$330k Kelly 25 Sellier Niloticus Himalaya paved diamond, HK 2024
$284k Kelly 25 Sellier Niloticus Himalaya 14K + diamond, Paris 2024
$234k Kelly 28 Niloticus Himalaya 18K + diamond, HK 2024
$234k Birkin 20 Faubourg Snow, HK 2024
$227k Birkin 20 Faubourg Snow, Paris 2024
$203k Birkin 20 Faubourg Rainy Days, HK 2025
$187k Birkin 25 Niloticus Himalaya palladium, HK 2024
$181k Birkin 20 Faubourg Midnight, HK 2024
$157k Kelly 25 Sellier Midas (yellow gold), HK 2024
…
Counts after: 29 canonical_assets (13 exotic), 61 transactions,
20 raw_observations. 44/44 tests still green.
Note: new exotic assets have comps but no valuation_snapshot rows —
detail page renders "No snapshot yet for this asset" honestly. Auto-
snapshot generation from comps is a future tick.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Files touched
Diff
commit d9aa2ade2de15fca943090cdd572dc7801452dfe
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sun May 10 00:40:16 2026 -0700
yolo tick #13: real Sotheby's auction-result data ingest
Pivoted from feature polish to actual data acquisition. Per Steve's
"build and look anywhere for data" + "always stay legal for scraping"
instructions, I went after Sotheby's editorial pages — public records
of realized auction prices, robots-friendly, no auth bypass, no
images. NOT eBay sold-history (Marketplace Insights API gated, HTML
scrape is TOS-prohibited). NOT third-party aggregators without
license review.
Sources (cited in raw_observations.payload.source_url for every row):
1. /articles/the-10-most-expensive-hermes-bags-sold-at-auction-in-2024
2. /articles/the-11-most-expensive-hermes-bags-sold-at-auction-in-the-first-half-2024
3. /articles/the-newest-hermes-bag-collector-obsession-the-birkin-20
4. /articles/secondary-market-signals-after-hermes-impressive-2024-results
Schema additions:
- 1 new model_family: Constance
- 14 new canonical_assets (mostly exotic Niloticus Himalaya configs +
Faubourg / Midas / Rainy Days / Vert d'Eau / Vanille / Havane —
the Sotheby's headline lots)
- 20 raw_observations with full source_url + lot description in
payload jsonb (idempotent via UNIQUE source+external_id+kind)
- 22 transactions linked back to their raw_observation. Net seller
proceeds computed as gross * 0.82 per METHODOLOGY § 4 Sotheby's
hammer-to-net haircut.
Region attribution: transactions.region carries the auction city
(HK / FR), so per METHODOLOGY § 11 they're stored as cross-region
comps and excluded from US comp pools — the canonical assets retain
region='US' (buyer region we model).
Top realized prices now in DB:
$330k Kelly 25 Sellier Niloticus Himalaya paved diamond, HK 2024
$284k Kelly 25 Sellier Niloticus Himalaya 14K + diamond, Paris 2024
$234k Kelly 28 Niloticus Himalaya 18K + diamond, HK 2024
$234k Birkin 20 Faubourg Snow, HK 2024
$227k Birkin 20 Faubourg Snow, Paris 2024
$203k Birkin 20 Faubourg Rainy Days, HK 2025
$187k Birkin 25 Niloticus Himalaya palladium, HK 2024
$181k Birkin 20 Faubourg Midnight, HK 2024
$157k Kelly 25 Sellier Midas (yellow gold), HK 2024
…
Counts after: 29 canonical_assets (13 exotic), 61 transactions,
20 raw_observations. 44/44 tests still green.
Note: new exotic assets have comps but no valuation_snapshot rows —
detail page renders "No snapshot yet for this asset" honestly. Auto-
snapshot generation from comps is a future tick.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
---
db/seed.sql | 517 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 517 insertions(+)
diff --git a/db/seed.sql b/db/seed.sql
index ef7f522..02e81ca 100644
--- a/db/seed.sql
+++ b/db/seed.sql
@@ -786,3 +786,520 @@ 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;
← 41d8213 yolo tick #12: family taxonomy pages (/family/:slug)
·
back to Lifestyle Asset Intel
·
yolo tick #14: auto-generate valuation_snapshots from real c 012be8e →