TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1import { pgTable, pgView, text, integer, boolean, real, index, uniqueIndex, jsonb, date } from 'drizzle-orm/pg-core';2import { sql } from 'drizzle-orm';3import { createdAt, updatedAt, ts, money, ratio, jsonObject, textArray } from './_common.js';45/** Verified/observed transactions (§110). Prices keep native currency + USD at historical FX (§136). */6export const sales = pgTable(7 'sales',8 {9 id: text('id').primaryKey(),10 assetId: text('asset_id').notNull(),11 variantId: text('variant_id'),12 sourceId: text('source_id').notNull(),13 connectorId: text('connector_id').notNull(),14 rawRecordId: text('raw_record_id'),15 normalizedRecordId: text('normalized_record_id'),16 sourceUrl: text('source_url').notNull(),17 externalId: text('external_id'),18 saleType: text('sale_type').notNull().default('unknown'),19 saleDate: ts('sale_date').notNull(),20 price: money('price').notNull(),21 currency: text('currency').notNull(),22 priceUsd: money('price_usd').notNull(),23 fxRate: real('fx_rate'),24 fxDate: date('fx_date'),25 buyerPremiumIncluded: boolean('buyer_premium_included'),26 /** buyer-pays price in USD: price_usd + estimated buyer premium when the record is hammer-only (§35) */27 allInUsd: money('all_in_usd'),28 /** included | added_published | added_approximate | added_default | none | unknown */29 feeBasis: text('fee_basis'),30 buyerPremiumRate: real('buyer_premium_rate'),31 quantity: integer('quantity').notNull().default(1),32 isBundle: boolean('is_bundle').notNull().default(false),33 condition: text('condition'),34 grader: text('grader'),35 grade: text('grade'),36 certificationNumber: text('certification_number'),37 location: text('location'),38 auctionHouse: text('auction_house'),39 lotNumber: text('lot_number'),40 imageUrls: jsonb('image_urls').$type<string[]>().notNull().default([]),41 rawTitle: text('raw_title').notNull(),42 confidence: ratio('confidence').notNull().default(0.8),43 dataQuality: real('data_quality').notNull().default(0),44 /** valid | flagged | excluded (never deleted, §116) */45 status: text('status').notNull().default('valid'),46 flags: textArray('flags'),47 dedupeKey: text('dedupe_key').notNull(),48 createdAt: createdAt(),49 },50 (t) => [51 uniqueIndex('sales_dedupe_uq').on(t.dedupeKey),52 index('sales_asset_date_idx').on(t.assetId, t.saleDate),53 index('sales_variant_date_idx').on(t.variantId, t.saleDate),54 index('sales_source_idx').on(t.sourceId, t.saleDate),55 index('sales_date_idx').on(t.saleDate),56 index('sales_price_idx').on(t.priceUsd),57 // every market query filters status = 'valid' with a date/price order58 index('sales_valid_date_idx').on(t.saleDate).where(sql`status = 'valid'`),59 ],60);6162/** Current and historical listings (§111). Listing price is never treated as market value. */63export const listings = pgTable(64 'listings',65 {66 id: text('id').primaryKey(),67 assetId: text('asset_id').notNull(),68 variantId: text('variant_id'),69 sourceId: text('source_id').notNull(),70 connectorId: text('connector_id').notNull(),71 rawRecordId: text('raw_record_id'),72 sourceUrl: text('source_url').notNull(),73 externalId: text('external_id').notNull(),74 listingType: text('listing_type').notNull().default('unknown'),75 price: money('price'),76 currency: text('currency'),77 priceUsd: money('price_usd'),78 seller: text('seller'),79 sellerReputation: text('seller_reputation'),80 location: text('location'),81 shippingCost: money('shipping_cost'),82 quantity: integer('quantity'),83 condition: text('condition'),84 grader: text('grader'),85 grade: text('grade'),86 certificationNumber: text('certification_number'),87 imageUrls: jsonb('image_urls').$type<string[]>().notNull().default([]),88 rawTitle: text('raw_title').notNull(),89 description: text('description'),90 listedAt: ts('listed_at'),91 endsAt: ts('ends_at'),92 availability: text('availability').notNull().default('available'),93 bidCount: integer('bid_count'),94 firstSeenAt: ts('first_seen_at').notNull(),95 lastSeenAt: ts('last_seen_at').notNull(),96 priceChangedAt: ts('price_changed_at'),97 crossListingGroupId: text('cross_listing_group_id'),98 confidence: ratio('confidence').notNull().default(0.8),99 dataQuality: real('data_quality').notNull().default(0),100 flags: textArray('flags'),101 /** value opportunity vs RIV, computed (§123) */102 discountToRiv: ratio('discount_to_riv'),103 createdAt: createdAt(),104 updatedAt: updatedAt(),105 },106 (t) => [107 uniqueIndex('listings_source_external_uq').on(t.sourceId, t.externalId),108 index('listings_asset_avail_idx').on(t.assetId, t.availability),109 index('listings_avail_price_idx').on(t.availability, t.priceUsd),110 index('listings_ends_idx').on(t.endsAt),111 index('listings_last_seen_idx').on(t.lastSeenAt),112 // valuation worker updates asks per variant; deal rails / Deal Radar order by discount113 index('listings_variant_avail_idx').on(t.variantId, t.availability),114 index('listings_discount_idx').on(t.discountToRiv).where(sql`discount_to_riv is not null and availability = 'available'`),115 ],116);117118export const listingEvents = pgTable(119 'listing_events',120 {121 id: text('id').primaryKey(),122 listingId: text('listing_id').notNull(),123 eventType: text('event_type').notNull(), // new | price_changed | sold | removed | relisted | auction_ended124 oldPrice: money('old_price'),125 newPrice: money('new_price'),126 currency: text('currency'),127 occurredAt: ts('occurred_at').notNull(),128 },129 (t) => [index('listing_events_listing_idx').on(t.listingId, t.occurredAt)],130);131132export const auctions = pgTable(133 'auctions',134 {135 id: text('id').primaryKey(),136 sourceId: text('source_id').notNull(),137 auctionHouse: text('auction_house').notNull(),138 name: text('name').notNull(),139 url: text('url').notNull(),140 startsAt: ts('starts_at'),141 endsAt: ts('ends_at'),142 location: text('location'),143 categorySlugs: textArray('category_slugs'),144 lotCount: integer('lot_count'),145 status: text('status').notNull().default('upcoming'), // upcoming | live | ended146 currency: text('currency'),147 createdAt: createdAt(),148 updatedAt: updatedAt(),149 },150 (t) => [uniqueIndex('auctions_url_uq').on(t.url), index('auctions_ends_idx').on(t.endsAt)],151);152153export const auctionLots = pgTable(154 'auction_lots',155 {156 id: text('id').primaryKey(),157 auctionId: text('auction_id').notNull(),158 assetId: text('asset_id'),159 variantId: text('variant_id'),160 sourceId: text('source_id').notNull(),161 lotNumber: text('lot_number'),162 title: text('title').notNull(),163 url: text('url').notNull(),164 estimateLow: money('estimate_low'),165 estimateHigh: money('estimate_high'),166 currentBid: money('current_bid'),167 hammerPrice: money('hammer_price'),168 currency: text('currency'),169 bidCount: integer('bid_count'),170 // ---- USD normalisation + auction intelligence (§33–§35), written by workers/auctions ----171 estimateLowUsd: money('estimate_low_usd'),172 estimateHighUsd: money('estimate_high_usd'),173 currentBidUsd: money('current_bid_usd'),174 hammerPriceUsd: money('hammer_price_usd'),175 fxRate: real('fx_rate'),176 fxDate: date('fx_date'),177 buyerPremiumRate: real('buyer_premium_rate'),178 feeBasis: text('fee_basis'),179 /** buyer-pays cost of the current bid (or low estimate when no bid) in USD, premium included */180 allInBidUsd: money('all_in_bid_usd'),181 allInEstimateLowUsd: money('all_in_estimate_low_usd'),182 allInEstimateHighUsd: money('all_in_estimate_high_usd'),183 rivUsdAtAssessment: money('riv_usd_at_assessment'),184 /** (all-in bid − RIV) / RIV, same sign convention as listings.discount_to_riv; null when ungated */185 bidVsRiv: real('bid_vs_riv'),186 /** (all-in low estimate − RIV) / RIV */187 estimateVsRiv: real('estimate_vs_riv'),188 /** deal | fair | premium | review | anomaly | ungated */189 assessmentVerdict: text('assessment_verdict'),190 assessedAt: ts('assessed_at'),191 startsAt: ts('starts_at'),192 endsAt: ts('ends_at'),193 status: text('status').notNull().default('upcoming'),194 imageUrls: jsonb('image_urls').$type<string[]>().notNull().default([]),195 grader: text('grader'),196 grade: text('grade'),197 createdAt: createdAt(),198 updatedAt: updatedAt(),199 },200 (t) => [uniqueIndex('auction_lots_url_uq').on(t.url), index('auction_lots_auction_idx').on(t.auctionId), index('auction_lots_asset_idx').on(t.assetId), index('auction_lots_ends_idx').on(t.endsAt), index('auction_lots_status_ends_idx').on(t.status, t.endsAt), index('auction_lots_bid_vs_riv_idx').on(t.bidVsRiv).where(sql`bid_vs_riv is not null and status in ('live','upcoming')`)],201);202203/** Price-guide observations (market/low/mid/high) — informative, weighted below transactions. */204export const priceObservations = pgTable(205 'price_observations',206 {207 id: text('id').primaryKey(),208 assetId: text('asset_id').notNull(),209 variantId: text('variant_id'),210 sourceId: text('source_id').notNull(),211 connectorId: text('connector_id').notNull(),212 rawRecordId: text('raw_record_id'),213 sourceUrl: text('source_url').notNull(),214 priceKind: text('price_kind').notNull(),215 price: money('price').notNull(),216 currency: text('currency').notNull(),217 priceUsd: money('price_usd').notNull(),218 observationDate: date('observation_date').notNull(),219 sampleSize: integer('sample_size'),220 dedupeKey: text('dedupe_key').notNull(),221 createdAt: createdAt(),222 },223 (t) => [uniqueIndex('price_observations_dedupe_uq').on(t.dedupeKey), index('price_observations_asset_date_idx').on(t.assetId, t.observationDate)],224);225226export const crossListingGroups = pgTable('cross_listing_groups', {227 id: text('id').primaryKey(),228 assetId: text('asset_id'),229 signals: jsonObject<Record<string, unknown>>('signals'),230 createdAt: createdAt(),231});232233/** Daily FX rates from an official dataset (ECB via frankfurter); base USD (§136). */234export const fxRates = pgTable(235 'fx_rates',236 {237 date: date('date').notNull(),238 base: text('base').notNull(),239 quote: text('quote').notNull(),240 rate: real('rate').notNull(),241 source: text('source').notNull().default('ecb'),242 },243 (t) => [uniqueIndex('fx_rates_uq').on(t.date, t.base, t.quote)],244);245246export const news = pgTable(247 'news',248 {249 id: text('id').primaryKey(),250 sourceId: text('source_id').notNull(),251 url: text('url').notNull(),252 title: text('title').notNull(),253 summary: text('summary'),254 aiSummary: text('ai_summary'),255 publishedAt: ts('published_at'),256 categorySlugs: textArray('category_slugs'),257 newsType: text('news_type'), // auction_results | record_sales | grading | releases | trends | discoveries | events258 imageUrl: text('image_url'),259 fetchedAt: ts('fetched_at').notNull(),260 },261 (t) => [uniqueIndex('news_url_uq').on(t.url), index('news_published_idx').on(t.publishedAt)],262);263264/**265 * Timestamp-aware multi-currency view of sales (SPEC §19): every sale in USD, CAD, EUR, GBP and JPY266 * converted with the ECB rate in force on (or just before) the sale date — never today's rate.267 * fx_rates.rate = quote units per 1 USD (base USD).268 */269export const salesMultiCurrency = pgView('sales_multi_currency', {270 saleId: text('sale_id'),271 assetId: text('asset_id'),272 saleDate: ts('sale_date'),273 price: money('price'),274 currency: text('currency'),275 priceUsd: money('price_usd'),276 priceCad: money('price_cad'),277 priceEur: money('price_eur'),278 priceGbp: money('price_gbp'),279 priceJpy: money('price_jpy'),280 fxDateCad: date('fx_date_cad'),281 fxDateEur: date('fx_date_eur'),282 fxDateGbp: date('fx_date_gbp'),283 fxDateJpy: date('fx_date_jpy'),284}).as(sql`285 select s.id as sale_id, s.asset_id, s.sale_date, s.price, s.currency, s.price_usd,286 s.price_usd * cad.rate as price_cad, s.price_usd * eur.rate as price_eur, s.price_usd * gbp.rate as price_gbp, s.price_usd * jpy.rate as price_jpy,287 cad.date as fx_date_cad, eur.date as fx_date_eur, gbp.date as fx_date_gbp, jpy.date as fx_date_jpy288 from sales s289 left join lateral (select f.rate, f.date from fx_rates f where f.base = 'USD' and f.quote = 'CAD' and f.date <= s.sale_date::date order by f.date desc limit 1) cad on true290 left join lateral (select f.rate, f.date from fx_rates f where f.base = 'USD' and f.quote = 'EUR' and f.date <= s.sale_date::date order by f.date desc limit 1) eur on true291 left join lateral (select f.rate, f.date from fx_rates f where f.base = 'USD' and f.quote = 'GBP' and f.date <= s.sale_date::date order by f.date desc limit 1) gbp on true292 left join lateral (select f.rate, f.date from fx_rates f where f.base = 'USD' and f.quote = 'JPY' and f.date <= s.sale_date::date order by f.date desc limit 1) jpy on true293`);294295/** Same for live/historical listings, converted at the listing's last observation date. */296export const listingsMultiCurrency = pgView('listings_multi_currency', {297 listingId: text('listing_id'),298 assetId: text('asset_id'),299 lastSeenAt: ts('last_seen_at'),300 price: money('price'),301 currency: text('currency'),302 priceUsd: money('price_usd'),303 priceCad: money('price_cad'),304 priceEur: money('price_eur'),305 priceGbp: money('price_gbp'),306 priceJpy: money('price_jpy'),307}).as(sql`308 select l.id as listing_id, l.asset_id, l.last_seen_at, l.price, l.currency, l.price_usd,309 l.price_usd * cad.rate as price_cad, l.price_usd * eur.rate as price_eur, l.price_usd * gbp.rate as price_gbp, l.price_usd * jpy.rate as price_jpy310 from listings l311 left join lateral (select f.rate from fx_rates f where f.base = 'USD' and f.quote = 'CAD' and f.date <= l.last_seen_at::date order by f.date desc limit 1) cad on true312 left join lateral (select f.rate from fx_rates f where f.base = 'USD' and f.quote = 'EUR' and f.date <= l.last_seen_at::date order by f.date desc limit 1) eur on true313 left join lateral (select f.rate from fx_rates f where f.base = 'USD' and f.quote = 'GBP' and f.date <= l.last_seen_at::date order by f.date desc limit 1) gbp on true314 left join lateral (select f.rate from fx_rates f where f.base = 'USD' and f.quote = 'JPY' and f.date <= l.last_seen_at::date order by f.date desc limit 1) jpy on true315`);316