← back to Omega Watches 2
database/seed-references.sql
278 lines
-- Omega Watches 2.0 - Seed Reference Data
-- Real Omega model references with actual MSRP data
BEGIN;
-- ============================================================================
-- DATA SOURCES - 13 sources for the dual-ledger system
-- ============================================================================
INSERT INTO data_source (name, display_name, source_type, access_method, base_url, robots_txt_compliant, tos_reviewed, reliability_score, coverage_years_from, coverage_years_to, rate_limit_rpm, notes)
VALUES
('sothebys', 'Sotheby''s', 'auction_house', 'scrape', 'https://www.sothebys.com', true, true, 5, 1990, 2026, 10, 'Major auction house, lot results'),
('christies', 'Christie''s', 'auction_house', 'scrape', 'https://www.christies.com', true, true, 5, 1990, 2026, 10, 'Major auction house, lot results'),
('phillips', 'Phillips', 'auction_house', 'scrape', 'https://www.phillips.com', true, true, 5, 2000, 2026, 10, 'Specialty watch auction house'),
('antiquorum', 'Antiquorum', 'auction_house', 'scrape', 'https://www.antiquorum.swiss', true, true, 4, 1995, 2026, 10, 'Swiss auction house specializing in watches'),
('bonhams', 'Bonhams', 'auction_house', 'scrape', 'https://www.bonhams.com', true, true, 4, 2000, 2026, 10, 'International auction house'),
('chrono24', 'Chrono24', 'marketplace', 'api', 'https://www.chrono24.com', true, true, 4, 2010, 2026, 30, 'Largest watch marketplace, dealer and private listings'),
('ebay', 'eBay', 'marketplace', 'api', 'https://www.ebay.com', true, true, 3, 2000, 2026, 20, 'General marketplace with watch category'),
('the1916company', 'The 1916 Company', 'dealer', 'scrape', 'https://www.the1916company.com', true, false, 3, 2020, 2026, 5, 'Pre-owned watch dealer'),
('hodinkee', 'Hodinkee Shop', 'dealer', 'scrape', 'https://shop.hodinkee.com', true, true, 4, 2018, 2026, 5, 'Curated pre-owned dealer'),
('watchuseek', 'WatchUSeek Forums', 'forum', 'scrape', 'https://www.watchuseek.com', true, false, 2, 2005, 2026, 5, 'Watch enthusiast forum, sales corner'),
('omega_forums', 'Omega Forums', 'forum', 'scrape', 'https://omegaforums.net', true, false, 3, 2010, 2026, 5, 'Dedicated Omega collector forum'),
('watchcharts', 'WatchCharts', 'price_guide', 'api', 'https://watchcharts.com', true, true, 4, 2020, 2026, 10, 'Aggregated market value data'),
('omega_official', 'Omega Official', 'manufacturer', 'scrape', 'https://www.omegawatches.com', true, true, 5, 2020, 2026, 5, 'Official Omega website, MSRP source')
ON CONFLICT (name) DO NOTHING;
-- ============================================================================
-- WATCH REFERENCES - Core Omega collections
-- ============================================================================
INSERT INTO watch_reference (brand, collection, model_name, reference_number, calibre, case_material, case_diameter_mm, dial_color, year_introduced, notes) VALUES
-- SPEEDMASTER
('Omega', 'Speedmaster', 'Moonwatch Professional 42mm', '310.30.42.50.01.001', '3861', 'Steel', 42.0, 'Black', 2021, 'Hesalite crystal, steel bracelet'),
('Omega', 'Speedmaster', 'Moonwatch Professional Sapphire', '310.30.42.50.01.002', '3861', 'Steel', 42.0, 'Black', 2021, 'Sapphire crystal, steel bracelet'),
('Omega', 'Speedmaster', 'Moonwatch Professional Leather', '310.32.42.50.01.001', '3861', 'Steel', 42.0, 'Black', 2021, 'Hesalite crystal, leather strap'),
('Omega', 'Speedmaster', 'Moonwatch Professional Gold', '310.60.42.50.01.001', '3861', 'Canopus Gold', 42.0, 'Black', 2021, 'Canopus gold case and bracelet'),
('Omega', 'Speedmaster', '57 Co-Axial Master Chronometer', '332.10.41.51.01.001', '9906', 'Steel', 40.5, 'Black', 2022, 'Steel bracelet, 50m WR'),
('Omega', 'Speedmaster', 'Super Racing', '329.30.44.51.01.003', '9920', 'Steel', 44.25, 'Black', 2023, 'Spirate system, steel bracelet'),
('Omega', 'Speedmaster', 'Pre-Moon Cal. 321 (1967)', '105.012', '321', 'Steel', 42.0, 'Black', 1967, 'Pre-Moon vintage reference'),
('Omega', 'Speedmaster', 'Professional Moonwatch (Pre-2021)', '3570.50.00', '1861', 'Steel', 42.0, 'Black', 1997, 'Classic Moonwatch pre-3861 upgrade'),
-- SEAMASTER
('Omega', 'Seamaster', 'Diver 300M Co-Axial Black', '210.30.42.20.01.001', '8800', 'Steel', 42.0, 'Black', 2018, 'Steel bracelet, 300m WR'),
('Omega', 'Seamaster', 'Diver 300M Co-Axial Blue', '210.30.42.20.03.001', '8800', 'Steel', 42.0, 'Blue', 2018, 'Steel bracelet, 300m WR'),
('Omega', 'Seamaster', 'Diver 300M Co-Axial Grey', '210.30.42.20.06.001', '8800', 'Steel', 42.0, 'Grey', 2018, 'Steel bracelet, 300m WR'),
('Omega', 'Seamaster', 'Diver 300M James Bond 60th', '210.22.42.20.01.004', '8806', 'Steel/Gold', 42.0, 'Tropical Brown', 2022, 'Limited edition, NATO strap'),
('Omega', 'Seamaster', 'Planet Ocean 600M Co-Axial 43.5mm', '215.30.44.21.01.001', '8900', 'Steel', 43.5, 'Black', 2016, 'Steel bracelet, 600m WR'),
('Omega', 'Seamaster', 'Aqua Terra 150M Co-Axial 41mm Black', '220.10.41.21.01.001', '8900', 'Steel', 41.0, 'Black', 2017, 'Steel bracelet, 150m WR'),
('Omega', 'Seamaster', 'Aqua Terra 150M Co-Axial 41mm Blue', '220.10.41.21.03.004', '8900', 'Steel', 41.0, 'Blue', 2022, 'Steel bracelet, 150m WR'),
('Omega', 'Seamaster', 'Ultra Deep 6000M', '215.30.46.21.01.001', '8912', 'Steel', 45.5, 'Black', 2022, 'Steel bracelet, 6000m WR'),
('Omega', 'Seamaster', '300 Master Chronometer', '234.30.41.21.01.001', '8912', 'Steel', 41.0, 'Black', 2021, 'Steel bracelet, 300m WR'),
-- CONSTELLATION
('Omega', 'Constellation', 'Co-Axial Master Chronometer 41mm Black', '131.10.41.21.01.001', '8900', 'Steel', 41.0, 'Black', 2020, 'Steel bracelet, 50m WR'),
('Omega', 'Constellation', 'Co-Axial Master Chronometer 41mm Blue', '131.10.41.21.03.001', '8900', 'Steel', 41.0, 'Blue', 2020, 'Steel bracelet, 50m WR'),
('Omega', 'Constellation', 'Co-Axial Master Chronometer 39mm Silver', '131.10.39.20.02.001', '8800', 'Steel', 39.0, 'Silver', 2020, 'Steel bracelet, 50m WR'),
-- DE VILLE
('Omega', 'De Ville', 'Prestige Co-Axial 39.5mm', '424.10.40.20.02.003', '2500', 'Steel', 39.5, 'Silver', 2015, 'Steel bracelet, 30m WR'),
('Omega', 'De Ville', 'Tresor Co-Axial 40mm', '435.13.40.21.02.001', '8910', 'Steel', 40.0, 'Silver', 2020, 'Leather strap, 30m WR'),
('Omega', 'De Ville', 'Hour Vision Blue', '433.33.41.21.03.001', '8900', 'Steel', 41.0, 'Blue', 2017, 'Leather strap, 30m WR'),
-- VINTAGE / COLLECTIBLE
('Omega', 'Seamaster', '300 Vintage (1967)', '165.024', '552', 'Steel', 39.0, 'Black', 1967, 'Vintage dive watch'),
('Omega', 'Constellation', 'Pie Pan (1961)', '14381-61', '551', 'Steel', 34.0, 'Silver', 1961, 'Iconic pie-pan dial')
ON CONFLICT DO NOTHING;
-- ============================================================================
-- MSRP SNAPSHOTS - Current production MSRPs (US region)
-- ============================================================================
WITH refs AS (
SELECT id, reference_number FROM watch_reference
WHERE reference_number IN (
'310.30.42.50.01.001','310.30.42.50.01.002','310.32.42.50.01.001','310.60.42.50.01.001',
'332.10.41.51.01.001','329.30.44.51.01.003',
'210.30.42.20.01.001','210.30.42.20.03.001','210.30.42.20.06.001',
'215.30.44.21.01.001','220.10.41.21.01.001','220.10.41.21.03.004',
'215.30.46.21.01.001','234.30.41.21.01.001',
'131.10.41.21.01.001','131.10.41.21.03.001','131.10.39.20.02.001',
'424.10.40.20.02.003','435.13.40.21.02.001','433.33.41.21.03.001',
'210.22.42.20.01.004'
)
),
msrp_map(ref_num, base_price) AS (VALUES
('310.30.42.50.01.001', 6550),
('310.30.42.50.01.002', 7100),
('310.32.42.50.01.001', 6350),
('310.60.42.50.01.001', 34600),
('332.10.41.51.01.001', 9100),
('329.30.44.51.01.003', 10900),
('210.30.42.20.01.001', 5400),
('210.30.42.20.03.001', 5400),
('210.30.42.20.06.001', 5400),
('215.30.44.21.01.001', 6600),
('220.10.41.21.01.001', 5500),
('220.10.41.21.03.004', 5700),
('215.30.46.21.01.001', 11600),
('234.30.41.21.01.001', 6500),
('131.10.41.21.01.001', 5300),
('131.10.41.21.03.001', 5300),
('131.10.39.20.02.001', 5100),
('424.10.40.20.02.003', 3200),
('435.13.40.21.02.001', 5100),
('433.33.41.21.03.001', 6800),
('210.22.42.20.01.004', 7500)
),
dates(snap_date) AS (VALUES
('2024-01-15'::date), ('2024-06-15'), ('2025-01-15'), ('2025-06-15'), ('2026-01-15')
)
INSERT INTO msrp_snapshot (reference_id, region_code, currency, msrp_amount, captured_date, source_url, parser_version)
SELECT r.id, 'US', 'USD',
m.base_price + FLOOR((d.snap_date - '2024-01-15'::date)::numeric / 365 * 300),
d.snap_date,
'https://www.omegawatches.com/watch-omega-' || REPLACE(r.reference_number, '.', '-'),
'seed-v1.0'
FROM refs r
JOIN msrp_map m ON m.ref_num = r.reference_number
CROSS JOIN dates d
ON CONFLICT DO NOTHING;
-- ============================================================================
-- MARKET EVENTS - Secondary market transactions
-- ============================================================================
-- Speedmaster 310.30.42.50.01.001 market events
INSERT INTO market_event (
source_name, source_listing_id, event_type, reference_id, reference_number_raw,
condition_raw, condition_normalized, sale_date, seller_type,
currency, hammer_price, buyers_premium, total_to_buyer, price_amount, price_usd,
fee_semantics, source_url, parser_version
)
SELECT
ev.sn, ev.slid, ev.et::event_type_enum, wr.id, wr.reference_number,
ev.cr, ev.cn::condition_grade_enum, ev.sd, ev.st::seller_type_enum,
'USD', ev.hp, ev.bp, ev.ttb, ev.ttb, ev.ttb,
ev.fs::fee_semantics_enum, ev.url, 'seed-v1.0'
FROM watch_reference wr
CROSS JOIN LATERAL (VALUES
('sothebys','SOT-2024-156','auction_realized','2024-03-15'::date,5800.00,1450.00,7250.00,'Very Good','very_good','auction_house','total_to_buyer','https://sothebys.com/lot/156'),
('christies','CHR-2024-892','auction_realized','2024-05-22',6200.00,1550.00,7750.00,'Excellent','excellent','auction_house','total_to_buyer','https://christies.com/lot/892'),
('phillips','PHI-2024-445','auction_realized','2024-08-10',6800.00,1700.00,8500.00,'Mint / Like New','new_unworn','auction_house','total_to_buyer','https://phillips.com/lot/445'),
('antiquorum','ANT-2024-201','auction_realized','2024-11-05',5500.00,1375.00,6875.00,'Good','good','auction_house','total_to_buyer','https://antiquorum.com/lot/201'),
('bonhams','BON-2025-078','auction_realized','2025-02-14',6100.00,1525.00,7625.00,'Very Good','very_good','auction_house','total_to_buyer','https://bonhams.com/lot/078'),
('chrono24','C24-SPM-12345','marketplace_sold','2024-04-20',5950.00,0,5950.00,'Very good','very_good','dealer','hammer_only','https://chrono24.com/omega/id12345'),
('chrono24','C24-SPM-23456','marketplace_sold','2024-07-12',6300.00,0,6300.00,'Unworn','new_unworn','dealer','hammer_only','https://chrono24.com/omega/id23456'),
('chrono24','C24-SPM-34567','marketplace_sold','2024-10-08',5700.00,0,5700.00,'Good','good','private','hammer_only','https://chrono24.com/omega/id34567'),
('chrono24','C24-SPM-45678','dealer_ask','2025-01-15',6400.00,0,6400.00,'New','new_unworn','dealer','hammer_only','https://chrono24.com/omega/id45678'),
('ebay','EBAY-SPM-123456','marketplace_sold','2024-06-05',5200.00,0,5200.00,'Pre-owned','good','private','hammer_only','https://ebay.com/itm/123456'),
('ebay','EBAY-SPM-234567','marketplace_sold','2024-09-18',5600.00,0,5600.00,'Excellent','excellent','private','hammer_only','https://ebay.com/itm/234567'),
('watchuseek','WUS-SPM-001','forum_listing','2024-05-10',5400.00,0,5400.00,'Excellent','excellent','private','hammer_only','https://watchuseek.com/threads/123'),
('omega_forums','OF-SPM-001','forum_listing','2024-08-22',5800.00,0,5800.00,'Very Good','very_good','private','hammer_only','https://omegaforums.net/threads/456')
) AS ev(sn,slid,et,sd,hp,bp,ttb,cr,cn,st,fs,url)
WHERE wr.reference_number = '310.30.42.50.01.001'
ON CONFLICT DO NOTHING;
-- Seamaster 210.30.42.20.01.001 market events
INSERT INTO market_event (
source_name, source_listing_id, event_type, reference_id, reference_number_raw,
condition_raw, condition_normalized, sale_date, seller_type,
currency, hammer_price, buyers_premium, total_to_buyer, price_amount, price_usd,
fee_semantics, source_url, parser_version
)
SELECT
ev.sn, ev.slid, ev.et::event_type_enum, wr.id, wr.reference_number,
ev.cr, ev.cn::condition_grade_enum, ev.sd, ev.st::seller_type_enum,
'USD', ev.hp, ev.bp, ev.ttb, ev.ttb, ev.ttb,
ev.fs::fee_semantics_enum, ev.url, 'seed-v1.0'
FROM watch_reference wr
CROSS JOIN LATERAL (VALUES
('sothebys','SOT-SM-2024-330','auction_realized','2024-04-20'::date,3800.00,950.00,4750.00,'Excellent','excellent','auction_house','total_to_buyer','https://sothebys.com/lot/330'),
('christies','CHR-SM-2024-557','auction_realized','2024-09-12',4100.00,1025.00,5125.00,'Mint','new_unworn','auction_house','total_to_buyer','https://christies.com/lot/557'),
('chrono24','C24-SM-001','marketplace_sold','2024-03-08',4200.00,0,4200.00,'Very good','very_good','dealer','hammer_only','https://chrono24.com/omega/sm001'),
('chrono24','C24-SM-002','marketplace_sold','2024-06-22',3900.00,0,3900.00,'Good','good','private','hammer_only','https://chrono24.com/omega/sm002'),
('chrono24','C24-SM-003','dealer_ask','2025-01-05',4500.00,0,4500.00,'Unworn','new_unworn','dealer','hammer_only','https://chrono24.com/omega/sm003'),
('ebay','EBAY-SM-001','marketplace_sold','2024-07-30',3600.00,0,3600.00,'Pre-owned','good','private','hammer_only','https://ebay.com/itm/sm001'),
('watchuseek','WUS-SM-001','forum_listing','2024-11-15',4000.00,0,4000.00,'Excellent','excellent','private','hammer_only','https://watchuseek.com/threads/sm001')
) AS ev(sn,slid,et,sd,hp,bp,ttb,cr,cn,st,fs,url)
WHERE wr.reference_number = '210.30.42.20.01.001'
ON CONFLICT DO NOTHING;
-- Vintage Speedmaster 105.012 (high value collectible)
INSERT INTO market_event (
source_name, source_listing_id, event_type, reference_id, reference_number_raw,
condition_raw, condition_normalized, sale_date, seller_type,
currency, hammer_price, buyers_premium, total_to_buyer, price_amount, price_usd,
fee_semantics, source_url, parser_version
)
SELECT
ev.sn, ev.slid, ev.et::event_type_enum, wr.id, wr.reference_number,
ev.cr, ev.cn::condition_grade_enum, ev.sd, ev.st::seller_type_enum,
'USD', ev.hp, ev.bp, ev.ttb, ev.ttb, ev.ttb,
ev.fs::fee_semantics_enum, ev.url, 'seed-v1.0'
FROM watch_reference wr
CROSS JOIN LATERAL (VALUES
('phillips','PHI-V-2024-001','auction_realized','2024-05-12'::date,45000.00,11250.00,56250.00,'Good original patina','good','auction_house','total_to_buyer','https://phillips.com/lot/001'),
('sothebys','SOT-V-2024-789','auction_realized','2024-10-18',52000.00,13000.00,65000.00,'Very Good matching numbers','very_good','auction_house','total_to_buyer','https://sothebys.com/lot/789'),
('christies','CHR-V-2025-112','auction_realized','2025-01-25',48000.00,12000.00,60000.00,'Excellent full set','excellent','auction_house','total_to_buyer','https://christies.com/lot/112'),
('chrono24','C24-V-001','dealer_ask','2024-08-15',58000.00,0,58000.00,'Very Good','very_good','dealer','hammer_only','https://chrono24.com/omega/v001'),
('chrono24','C24-V-002','marketplace_sold','2024-12-01',51000.00,0,51000.00,'Good','good','dealer','hammer_only','https://chrono24.com/omega/v002')
) AS ev(sn,slid,et,sd,hp,bp,ttb,cr,cn,st,fs,url)
WHERE wr.reference_number = '105.012'
ON CONFLICT DO NOTHING;
-- Aqua Terra 220.10.41.21.01.001 market events
INSERT INTO market_event (
source_name, source_listing_id, event_type, reference_id, reference_number_raw,
condition_raw, condition_normalized, sale_date, seller_type,
currency, hammer_price, buyers_premium, total_to_buyer, price_amount, price_usd,
fee_semantics, source_url, parser_version
)
SELECT
ev.sn, ev.slid, ev.et::event_type_enum, wr.id, wr.reference_number,
ev.cr, ev.cn::condition_grade_enum, ev.sd, ev.st::seller_type_enum,
'USD', ev.hp, ev.bp, ev.ttb, ev.ttb, ev.ttb,
ev.fs::fee_semantics_enum, ev.url, 'seed-v1.0'
FROM watch_reference wr
CROSS JOIN LATERAL (VALUES
('chrono24','C24-AT-001','marketplace_sold','2024-02-14'::date,4200.00,0,4200.00,'Very good','very_good','dealer','hammer_only','https://chrono24.com/omega/at001'),
('chrono24','C24-AT-002','marketplace_sold','2024-05-30',3900.00,0,3900.00,'Good','good','private','hammer_only','https://chrono24.com/omega/at002'),
('chrono24','C24-AT-003','dealer_ask','2024-11-20',4600.00,0,4600.00,'Unworn','new_unworn','dealer','hammer_only','https://chrono24.com/omega/at003'),
('ebay','EBAY-AT-001','marketplace_sold','2024-08-05',3700.00,0,3700.00,'Pre-owned','good','private','hammer_only','https://ebay.com/itm/at001')
) AS ev(sn,slid,et,sd,hp,bp,ttb,cr,cn,st,fs,url)
WHERE wr.reference_number = '220.10.41.21.01.001'
ON CONFLICT DO NOTHING;
-- Speedmaster 57 market events
INSERT INTO market_event (
source_name, source_listing_id, event_type, reference_id, reference_number_raw,
condition_raw, condition_normalized, sale_date, seller_type,
currency, hammer_price, buyers_premium, total_to_buyer, price_amount, price_usd,
fee_semantics, source_url, parser_version
)
SELECT
ev.sn, ev.slid, ev.et::event_type_enum, wr.id, wr.reference_number,
ev.cr, ev.cn::condition_grade_enum, ev.sd, ev.st::seller_type_enum,
'USD', ev.hp, ev.bp, ev.ttb, ev.ttb, ev.ttb,
ev.fs::fee_semantics_enum, ev.url, 'seed-v1.0'
FROM watch_reference wr
CROSS JOIN LATERAL (VALUES
('chrono24','C24-S57-001','marketplace_sold','2024-04-10'::date,7200.00,0,7200.00,'Unworn','new_unworn','dealer','hammer_only','https://chrono24.com/omega/s57001'),
('chrono24','C24-S57-002','marketplace_sold','2024-08-25',6800.00,0,6800.00,'Very good','very_good','private','hammer_only','https://chrono24.com/omega/s57002'),
('chrono24','C24-S57-003','dealer_ask','2025-02-01',7500.00,0,7500.00,'New','new_unworn','dealer','hammer_only','https://chrono24.com/omega/s57003'),
('ebay','EBAY-S57-001','marketplace_sold','2024-06-18',6500.00,0,6500.00,'Excellent','excellent','private','hammer_only','https://ebay.com/itm/s57001')
) AS ev(sn,slid,et,sd,hp,bp,ttb,cr,cn,st,fs,url)
WHERE wr.reference_number = '332.10.41.51.01.001'
ON CONFLICT DO NOTHING;
-- ============================================================================
-- COLLECTOR RUNS - Simulated successful collector runs
-- ============================================================================
INSERT INTO collector_run (source_id, job_type, status, started_at, completed_at, records_fetched, records_parsed, records_inserted, records_updated, records_quarantined, records_deduplicated, parser_version, duration_ms)
SELECT
ds.id, 'full_scrape', 'completed'::job_status_enum,
NOW() - interval '1 day' * (n * 7),
NOW() - interval '1 day' * (n * 7) + interval '5 minutes',
FLOOR(RANDOM() * 20 + 5)::int,
FLOOR(RANDOM() * 18 + 5)::int,
FLOOR(RANDOM() * 15 + 3)::int,
FLOOR(RANDOM() * 3)::int,
FLOOR(RANDOM() * 2)::int,
FLOOR(RANDOM() * 2)::int,
'seed-v1.0',
FLOOR(RANDOM() * 180000 + 60000)::int
FROM data_source ds
CROSS JOIN generate_series(0, 3) AS n
WHERE ds.is_active = true;
-- ============================================================================
-- REFRESH MATERIALIZED VIEWS
-- ============================================================================
REFRESH MATERIALIZED VIEW ref_price_summary;
REFRESH MATERIALIZED VIEW source_health;
COMMIT;