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 // every market query filters status = 'valid' with a date/price order53 index('sales_valid_date_idx').on(t.saleDate).where(sql`status = 'valid'`),54 ],55);5657/** Current and historical listings (§111). Listing price is never treated as market value. */58export const listings = pgTable(59 'listings',60 {61 id: text('id').primaryKey(),62 assetId: text('asset_id').notNull(),63 variantId: text('variant_id'),64 sourceId: text('source_id').notNull(),65 connectorId: text('connector_id').notNull(),66 rawRecordId: text('raw_record_id'),67 sourceUrl: text('source_url').notNull(),68 externalId: text('external_id').notNull(),69 listingType: text('listing_type').notNull().default('unknown'),70 price: money('price'),71 currency: text('currency'),72 priceUsd: money('price_usd'),73 seller: text('seller'),74 sellerReputation: text('seller_reputation'),75 location: text('location'),76 shippingCost: money('shipping_cost'),77 quantity: integer('quantity'),78 condition: text('condition'),79 grader: text('grader'),80 grade: text('grade'),81 certificationNumber: text('certification_number'),82 imageUrls: jsonb('image_urls').$type<string[]>().notNull().default([]),83 rawTitle: text('raw_title').notNull(),84 description: text('description'),85 listedAt: ts('listed_at'),86 endsAt: ts('ends_at'),87 availability: text('availability').notNull().default('available'),88 bidCount: integer('bid_count'),89 firstSeenAt: ts('first_seen_at').notNull(),90 lastSeenAt: ts('last_seen_at').notNull(),91 priceChangedAt: ts('price_changed_at'),92 crossListingGroupId: text('cross_listing_group_id'),93 confidence: ratio('confidence').notNull().default(0.8),94 dataQuality: real('data_quality').notNull().default(0),95 flags: textArray('flags'),96 /** value opportunity vs RIV, computed (§123) */97 discountToRiv: ratio('discount_to_riv'),98 createdAt: createdAt(),99 updatedAt: updatedAt(),100 },101 (t) => [102 uniqueIndex('listings_source_external_uq').on(t.sourceId, t.externalId),103 index('listings_asset_avail_idx').on(t.assetId, t.availability),104 index('listings_avail_price_idx').on(t.availability, t.priceUsd),105 index('listings_ends_idx').on(t.endsAt),106 index('listings_last_seen_idx').on(t.lastSeenAt),107 // valuation worker updates asks per variant; deal rails / Deal Radar order by discount108 index('listings_variant_avail_idx').on(t.variantId, t.availability),109 index('listings_discount_idx').on(t.discountToRiv).where(sql`discount_to_riv is not null and availability = 'available'`),110 ],111);112113export const listingEvents = pgTable(114 'listing_events',115 {116 id: text('id').primaryKey(),117 listingId: text('listing_id').notNull(),118 eventType: text('event_type').notNull(), // new | price_changed | sold | removed | relisted | auction_ended119 oldPrice: money('old_price'),120 newPrice: money('new_price'),121 currency: text('currency'),122 occurredAt: ts('occurred_at').notNull(),123 },124 (t) => [index('listing_events_listing_idx').on(t.listingId, t.occurredAt)],125);126127export const auctions = pgTable(128 'auctions',129 {130 id: text('id').primaryKey(),131 sourceId: text('source_id').notNull(),132 auctionHouse: text('auction_house').notNull(),133 name: text('name').notNull(),134 url: text('url').notNull(),135 startsAt: ts('starts_at'),136 endsAt: ts('ends_at'),137 location: text('location'),138 categorySlugs: textArray('category_slugs'),139 lotCount: integer('lot_count'),140 status: text('status').notNull().default('upcoming'), // upcoming | live | ended141 currency: text('currency'),142 createdAt: createdAt(),143 updatedAt: updatedAt(),144 },145 (t) => [uniqueIndex('auctions_url_uq').on(t.url), index('auctions_ends_idx').on(t.endsAt)],146);147148export const auctionLots = pgTable(149 'auction_lots',150 {151 id: text('id').primaryKey(),152 auctionId: text('auction_id').notNull(),153 assetId: text('asset_id'),154 variantId: text('variant_id'),155 sourceId: text('source_id').notNull(),156 lotNumber: text('lot_number'),157 title: text('title').notNull(),158 url: text('url').notNull(),159 estimateLow: money('estimate_low'),160 estimateHigh: money('estimate_high'),161 currentBid: money('current_bid'),162 hammerPrice: money('hammer_price'),163 currency: text('currency'),164 bidCount: integer('bid_count'),165 startsAt: ts('starts_at'),166 endsAt: ts('ends_at'),167 status: text('status').notNull().default('upcoming'),168 imageUrls: jsonb('image_urls').$type<string[]>().notNull().default([]),169 grader: text('grader'),170 grade: text('grade'),171 createdAt: createdAt(),172 updatedAt: updatedAt(),173 },174 (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)],175);176177/** Price-guide observations (market/low/mid/high) — informative, weighted below transactions. */178export const priceObservations = pgTable(179 'price_observations',180 {181 id: text('id').primaryKey(),182 assetId: text('asset_id').notNull(),183 variantId: text('variant_id'),184 sourceId: text('source_id').notNull(),185 connectorId: text('connector_id').notNull(),186 rawRecordId: text('raw_record_id'),187 sourceUrl: text('source_url').notNull(),188 priceKind: text('price_kind').notNull(),189 price: money('price').notNull(),190 currency: text('currency').notNull(),191 priceUsd: money('price_usd').notNull(),192 observationDate: date('observation_date').notNull(),193 sampleSize: integer('sample_size'),194 dedupeKey: text('dedupe_key').notNull(),195 createdAt: createdAt(),196 },197 (t) => [uniqueIndex('price_observations_dedupe_uq').on(t.dedupeKey), index('price_observations_asset_date_idx').on(t.assetId, t.observationDate)],198);199200export const crossListingGroups = pgTable('cross_listing_groups', {201 id: text('id').primaryKey(),202 assetId: text('asset_id'),203 signals: jsonObject<Record<string, unknown>>('signals'),204 createdAt: createdAt(),205});206207/** Daily FX rates from an official dataset (ECB via frankfurter); base USD (§136). */208export const fxRates = pgTable(209 'fx_rates',210 {211 date: date('date').notNull(),212 base: text('base').notNull(),213 quote: text('quote').notNull(),214 rate: real('rate').notNull(),215 source: text('source').notNull().default('ecb'),216 },217 (t) => [uniqueIndex('fx_rates_uq').on(t.date, t.base, t.quote)],218);219220export const news = pgTable(221 'news',222 {223 id: text('id').primaryKey(),224 sourceId: text('source_id').notNull(),225 url: text('url').notNull(),226 title: text('title').notNull(),227 summary: text('summary'),228 aiSummary: text('ai_summary'),229 publishedAt: ts('published_at'),230 categorySlugs: textArray('category_slugs'),231 newsType: text('news_type'), // auction_results | record_sales | grading | releases | trends | discoveries | events232 imageUrl: text('image_url'),233 fetchedAt: ts('fetched_at').notNull(),234 },235 (t) => [uniqueIndex('news_url_uq').on(t.url), index('news_published_idx').on(t.publishedAt)],236);237238/**239 * Timestamp-aware multi-currency view of sales (SPEC §19): every sale in USD, CAD, EUR, GBP and JPY240 * converted with the ECB rate in force on (or just before) the sale date — never today's rate.241 * fx_rates.rate = quote units per 1 USD (base USD).242 */243export const salesMultiCurrency = pgView('sales_multi_currency', {244 saleId: text('sale_id'),245 assetId: text('asset_id'),246 saleDate: ts('sale_date'),247 price: money('price'),248 currency: text('currency'),249 priceUsd: money('price_usd'),250 priceCad: money('price_cad'),251 priceEur: money('price_eur'),252 priceGbp: money('price_gbp'),253 priceJpy: money('price_jpy'),254 fxDateCad: date('fx_date_cad'),255 fxDateEur: date('fx_date_eur'),256 fxDateGbp: date('fx_date_gbp'),257 fxDateJpy: date('fx_date_jpy'),258}).as(sql`259 select s.id as sale_id, s.asset_id, s.sale_date, s.price, s.currency, s.price_usd,260 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,261 cad.date as fx_date_cad, eur.date as fx_date_eur, gbp.date as fx_date_gbp, jpy.date as fx_date_jpy262 from sales s263 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 true264 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 true265 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 true266 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 true267`);268269/** Same for live/historical listings, converted at the listing's last observation date. */270export const listingsMultiCurrency = pgView('listings_multi_currency', {271 listingId: text('listing_id'),272 assetId: text('asset_id'),273 lastSeenAt: ts('last_seen_at'),274 price: money('price'),275 currency: text('currency'),276 priceUsd: money('price_usd'),277 priceCad: money('price_cad'),278 priceEur: money('price_eur'),279 priceGbp: money('price_gbp'),280 priceJpy: money('price_jpy'),281}).as(sql`282 select l.id as listing_id, l.asset_id, l.last_seen_at, l.price, l.currency, l.price_usd,283 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_jpy284 from listings l285 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 true286 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 true287 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 true288 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 true289`);290