← back to Lifestyle Asset Intel
yolo tick #14: auto-generate valuation_snapshots from real comps
012be8edad17663c605704f7ed292bd1954c9ec4 · 2026-05-10 00:53:35 -0700 · Steve Abrams
The 14 exotic / Birkin 20 / Constance canonical_assets added in tick
#13 had real Sotheby's comps but no valuation_snapshot rows, so they
showed up in the grid as null Q10/Q50/Q90. Adding a one-shot seed
block that auto-fills v0-stub snapshots for any canonical_asset that
has ≥1 transaction but no snapshot for today's date.
Algorithm (per METHODOLOGY § 6 heuristic):
q50 = PERCENTILE_CONT(0.5) of gross_transaction_price (all comps)
q10 = q50 * 0.85
q90 = q50 * 1.27
liquidity_score, expected_dts, confidence stay null — those are
live-recomputed in lib/valuation.js from the 24-month window, so
any seeded constant would be inert. The grid + asset detail pages
pick up live values automatically.
auth_risk = 0.06 (low default for tier-1 auction provenance)
breakdown = { source: 'auto-from-real-comps', generator: 'tick-14',
method: 'median q50 + 0.85/1.27 quantile heuristic' }
No 24-month filter on the snapshot-input window (intentionally broader
than the live liquidity calc), so configs whose only comps are 2+
years stale still surface — they show the historical median Q50 with
a low live liquidity score, which is the correct signal.
Effect on Birkin family rollup:
asset_count 9 → 19
avg_q50 $24,367 → $85,658
total_comps 26 → 37 (24mo filter still applies in this metric)
29 snapshots / 29 assets = zero orphans. 44/44 tests still green.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Files touched
Diff
commit 012be8edad17663c605704f7ed292bd1954c9ec4
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sun May 10 00:53:35 2026 -0700
yolo tick #14: auto-generate valuation_snapshots from real comps
The 14 exotic / Birkin 20 / Constance canonical_assets added in tick
#13 had real Sotheby's comps but no valuation_snapshot rows, so they
showed up in the grid as null Q10/Q50/Q90. Adding a one-shot seed
block that auto-fills v0-stub snapshots for any canonical_asset that
has ≥1 transaction but no snapshot for today's date.
Algorithm (per METHODOLOGY § 6 heuristic):
q50 = PERCENTILE_CONT(0.5) of gross_transaction_price (all comps)
q10 = q50 * 0.85
q90 = q50 * 1.27
liquidity_score, expected_dts, confidence stay null — those are
live-recomputed in lib/valuation.js from the 24-month window, so
any seeded constant would be inert. The grid + asset detail pages
pick up live values automatically.
auth_risk = 0.06 (low default for tier-1 auction provenance)
breakdown = { source: 'auto-from-real-comps', generator: 'tick-14',
method: 'median q50 + 0.85/1.27 quantile heuristic' }
No 24-month filter on the snapshot-input window (intentionally broader
than the live liquidity calc), so configs whose only comps are 2+
years stale still surface — they show the historical median Q50 with
a low live liquidity score, which is the correct signal.
Effect on Birkin family rollup:
asset_count 9 → 19
avg_q50 $24,367 → $85,658
total_comps 26 → 37 (24mo filter still applies in this metric)
29 snapshots / 29 assets = zero orphans. 44/44 tests still green.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
---
db/seed.sql | 54 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 54 insertions(+)
diff --git a/db/seed.sql b/db/seed.sql
index 02e81ca..193e65f 100644
--- a/db/seed.sql
+++ b/db/seed.sql
@@ -1303,3 +1303,57 @@ 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;
← d9aa2ad yolo tick #13: real Sotheby's auction-result data ingest
·
back to Lifestyle Asset Intel
·
yolo tick #15: launchd-driven overnight autonomous loop db84b94 →