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 quantity: integer('quantity').notNull().default(1),27 isBundle: boolean('is_bundle').notNull().default(false),28 condition: text('condition'),29 grader: text('grader'),30 grade: text('grade'),31 certificationNumber: text('certification_number'),32 location: text('location'),33 auctionHouse: text('auction_house'),34 lotNumber: text('lot_number'),35 imageUrls: jsonb('image_urls').$type<string[]>().notNull().default([]),36 rawTitle: text('raw_title').notNull(),37 confidence: ratio('confidence').notNull().default(0.8),38 dataQuality: real('data_quality').notNull().default(0),39 /** valid | flagged | excluded (never deleted, §116) */40 status: text('status').notNull().default('valid'),41 flags: textArray('flags'),42 dedupeKey: text('dedupe_key').notNull(),43 createdAt: createdAt(),44 },45 (t) => [46 uniqueIndex('sales_dedupe_uq').on(t.dedupeKey),47 index('sales_asset_date_idx').on(t.assetId, t.saleDate),48 index('sales_variant_date_idx').on(t.variantId, t.saleDate),49 index('sales_source_idx').on(t.sourceId, t.saleDate),50 index('sales_date_idx').on(t.saleDate),51 index('sales_price_idx').on(t.priceUsd),52 ],53);5455/** Current and historical listings (§111). Listing price is never treated as market value. */56export const listings = pgTable(57 'listings',58 {59 id: text('id').primaryKey(),60 assetId: text('asset_id').notNull(),61 variantId: text('variant_id'),62 sourceId: text('source_id').notNull(),63 connectorId: text('connector_id').notNull(),64 rawRecordId: text('raw_record_id'),65 sourceUrl: text('source_url').notNull(),66 externalId: text('external_id').notNull(),67 listingType: text('listing_type').notNull().default('unknown'),68 price: money('price'),69 currency: text('currency'),70 priceUsd: money('price_usd'),71 seller: text('seller'),72 sellerReputation: text('seller_reputation'),73 location: text('location'),74 shippingCost: money('shipping_cost'),75 quantity: integer('quantity'),76 condition: text('condition'),77 grader: text('grader'),78 grade: text('grade'),79 certificationNumber: text('certification_number'),80 imageUrls: jsonb('image_urls').$type<string[]>().notNull().default([]),81 rawTitle: text('raw_title').notNull(),82 description: text('description'),83 listedAt: ts('listed_at'),84 endsAt: ts('ends_at'),85 availability: text('availability').notNull().default('available'),86 bidCount: integer('bid_count'),87 firstSeenAt: ts('first_seen_at').notNull(),88 lastSeenAt: ts('last_seen_at').notNull(),89 priceChangedAt: ts('price_changed_at'),90 crossListingGroupId: text('cross_listing_group_id'),91 confidence: ratio('confidence').notNull().default(0.8),92 dataQuality: real('data_quality').notNull().default(0),93 flags: textArray('flags'),94 /** value opportunity vs RIV, computed (§123) */95 discountToRiv: ratio('discount_to_riv'),96 createdAt: createdAt(),97 updatedAt: updatedAt(),98 },99 (t) => [100 uniqueIndex('listings_source_external_uq').on(t.sourceId, t.externalId),101 index('listings_asset_avail_idx').on(t.assetId, t.availability),102 index('listings_avail_price_idx').on(t.availability, t.priceUsd),103 index('listings_ends_idx').on(t.endsAt),104 index('listings_last_seen_idx').on(t.lastSeenAt),105 ],106);107108export const listingEvents = pgTable(109 'listing_events',110 {111 id: text('id').primaryKey(),112 listingId: text('listing_id').notNull(),113 eventType: text('event_type').notNull(), // new | price_changed | sold | removed | relisted | auction_ended114 oldPrice: money('old_price'),115 newPrice: money('new_price'),116 currency: text('currency'),117 occurredAt: ts('occurred_at').notNull(),118 },119 (t) => [index('listing_events_listing_idx').on(t.listingId, t.occurredAt)],120);121122export const auctions = pgTable(123 'auctions',124 {125 id: text('id').primaryKey(),126 sourceId: text('source_id').notNull(),127 auctionHouse: text('auction_house').notNull(),128 name: text('name').notNull(),129 url: text('url').notNull(),130 startsAt: ts('starts_at'),131 endsAt: ts('ends_at'),132 location: text('location'),133 categorySlugs: textArray('category_slugs'),134 lotCount: integer('lot_count'),135 status: text('status').notNull().default('upcoming'), // upcoming | live | ended136 currency: text('currency'),137 createdAt: createdAt(),138 updatedAt: updatedAt(),139 },140 (t) => [uniqueIndex('auctions_url_uq').on(t.url), index('auctions_ends_idx').on(t.endsAt)],141);142143export const auctionLots = pgTable(144 'auction_lots',145 {146 id: text('id').primaryKey(),147 auctionId: text('auction_id').notNull(),148 assetId: text('asset_id'),149 variantId: text('variant_id'),150 sourceId: text('source_id').notNull(),151 lotNumber: text('lot_number'),152 title: text('title').notNull(),153 url: text('url').notNull(),154 estimateLow: money('estimate_low'),155 estimateHigh: money('estimate_high'),156 currentBid: money('current_bid'),157 hammerPrice: money('hammer_price'),158 currency: text('currency'),159 bidCount: integer('bid_count'),160 startsAt: ts('starts_at'),161 endsAt: ts('ends_at'),162 status: text('status').notNull().default('upcoming'),163 imageUrls: jsonb('image_urls').$type<string[]>().notNull().default([]),164 grader: text('grader'),165 grade: text('grade'),166 createdAt: createdAt(),167 updatedAt: updatedAt(),168 },169 (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)],170);171172/** Price-guide observations (market/low/mid/high) — informative, weighted below transactions. */173export const priceObservations = pgTable(174 'price_observations',175 {176 id: text('id').primaryKey(),177 assetId: text('asset_id').notNull(),178 variantId: text('variant_id'),179 sourceId: text('source_id').notNull(),180 connectorId: text('connector_id').notNull(),181 rawRecordId: text('raw_record_id'),182 sourceUrl: text('source_url').notNull(),183 priceKind: text('price_kind').notNull(),184 price: money('price').notNull(),185 currency: text('currency').notNull(),186 priceUsd: money('price_usd').notNull(),187 observationDate: date('observation_date').notNull(),188 sampleSize: integer('sample_size'),189 dedupeKey: text('dedupe_key').notNull(),190 createdAt: createdAt(),191 },192 (t) => [uniqueIndex('price_observations_dedupe_uq').on(t.dedupeKey), index('price_observations_asset_date_idx').on(t.assetId, t.observationDate)],193);194195export const crossListingGroups = pgTable('cross_listing_groups', {196 id: text('id').primaryKey(),197 assetId: text('asset_id'),198 signals: jsonObject<Record<string, unknown>>('signals'),199 createdAt: createdAt(),200});201202/** Daily FX rates from an official dataset (ECB via frankfurter); base USD (§136). */203export const fxRates = pgTable(204 'fx_rates',205 {206 date: date('date').notNull(),207 base: text('base').notNull(),208 quote: text('quote').notNull(),209 rate: real('rate').notNull(),210 source: text('source').notNull().default('ecb'),211 },212 (t) => [uniqueIndex('fx_rates_uq').on(t.date, t.base, t.quote)],213);214215export const news = pgTable(216 'news',217 {218 id: text('id').primaryKey(),219 sourceId: text('source_id').notNull(),220 url: text('url').notNull(),221 title: text('title').notNull(),222 summary: text('summary'),223 aiSummary: text('ai_summary'),224 publishedAt: ts('published_at'),225 categorySlugs: textArray('category_slugs'),226 newsType: text('news_type'), // auction_results | record_sales | grading | releases | trends | discoveries | events227 imageUrl: text('image_url'),228 fetchedAt: ts('fetched_at').notNull(),229 },230 (t) => [uniqueIndex('news_url_uq').on(t.url), index('news_published_idx').on(t.publishedAt)],231);232233/**234 * Timestamp-aware multi-currency view of sales (SPEC §19): every sale in USD, CAD, EUR, GBP and JPY235 * converted with the ECB rate in force on (or just before) the sale date — never today's rate.236 * fx_rates.rate = quote units per 1 USD (base USD).237 */238export const salesMultiCurrency = pgView('sales_multi_currency', {239 saleId: text('sale_id'),240 assetId: text('asset_id'),241 saleDate: ts('sale_date'),242 price: money('price'),243 currency: text('currency'),244 priceUsd: money('price_usd'),245 priceCad: money('price_cad'),246 priceEur: money('price_eur'),247 priceGbp: money('price_gbp'),248 priceJpy: money('price_jpy'),249 fxDateCad: date('fx_date_cad'),250 fxDateEur: date('fx_date_eur'),251 fxDateGbp: date('fx_date_gbp'),252 fxDateJpy: date('fx_date_jpy'),253}).as(sql`254 select s.id as sale_id, s.asset_id, s.sale_date, s.price, s.currency, s.price_usd,255 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,256 cad.date as fx_date_cad, eur.date as fx_date_eur, gbp.date as fx_date_gbp, jpy.date as fx_date_jpy257 from sales s258 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 true259 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 true260 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 true261 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 true262`);263264/** Same for live/historical listings, converted at the listing's last observation date. */265export const listingsMultiCurrency = pgView('listings_multi_currency', {266 listingId: text('listing_id'),267 assetId: text('asset_id'),268 lastSeenAt: ts('last_seen_at'),269 price: money('price'),270 currency: text('currency'),271 priceUsd: money('price_usd'),272 priceCad: money('price_cad'),273 priceEur: money('price_eur'),274 priceGbp: money('price_gbp'),275 priceJpy: money('price_jpy'),276}).as(sql`277 select l.id as listing_id, l.asset_id, l.last_seen_at, l.price, l.currency, l.price_usd,278 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_jpy279 from listings l280 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 true281 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 true282 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 true283 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 true284`);285