import { pgTable, pgView, text, integer, boolean, real, index, uniqueIndex, jsonb, date } from 'drizzle-orm/pg-core'; import { sql } from 'drizzle-orm'; import { createdAt, updatedAt, ts, money, ratio, jsonObject, textArray } from './_common.js'; /** Verified/observed transactions (§110). Prices keep native currency + USD at historical FX (§136). */ export const sales = pgTable( 'sales', { id: text('id').primaryKey(), assetId: text('asset_id').notNull(), variantId: text('variant_id'), sourceId: text('source_id').notNull(), connectorId: text('connector_id').notNull(), rawRecordId: text('raw_record_id'), normalizedRecordId: text('normalized_record_id'), sourceUrl: text('source_url').notNull(), externalId: text('external_id'), saleType: text('sale_type').notNull().default('unknown'), saleDate: ts('sale_date').notNull(), price: money('price').notNull(), currency: text('currency').notNull(), priceUsd: money('price_usd').notNull(), fxRate: real('fx_rate'), fxDate: date('fx_date'), buyerPremiumIncluded: boolean('buyer_premium_included'), /** buyer-pays price in USD: price_usd + estimated buyer premium when the record is hammer-only (§35) */ allInUsd: money('all_in_usd'), /** included | added_published | added_approximate | added_default | none | unknown */ feeBasis: text('fee_basis'), buyerPremiumRate: real('buyer_premium_rate'), quantity: integer('quantity').notNull().default(1), isBundle: boolean('is_bundle').notNull().default(false), condition: text('condition'), grader: text('grader'), grade: text('grade'), certificationNumber: text('certification_number'), location: text('location'), auctionHouse: text('auction_house'), lotNumber: text('lot_number'), imageUrls: jsonb('image_urls').$type().notNull().default([]), rawTitle: text('raw_title').notNull(), confidence: ratio('confidence').notNull().default(0.8), dataQuality: real('data_quality').notNull().default(0), /** valid | flagged | excluded (never deleted, §116) */ status: text('status').notNull().default('valid'), flags: textArray('flags'), dedupeKey: text('dedupe_key').notNull(), createdAt: createdAt(), }, (t) => [ uniqueIndex('sales_dedupe_uq').on(t.dedupeKey), index('sales_asset_date_idx').on(t.assetId, t.saleDate), index('sales_variant_date_idx').on(t.variantId, t.saleDate), index('sales_source_idx').on(t.sourceId, t.saleDate), index('sales_date_idx').on(t.saleDate), index('sales_price_idx').on(t.priceUsd), // every market query filters status = 'valid' with a date/price order index('sales_valid_date_idx').on(t.saleDate).where(sql`status = 'valid'`), ], ); /** Current and historical listings (§111). Listing price is never treated as market value. */ export const listings = pgTable( 'listings', { id: text('id').primaryKey(), assetId: text('asset_id').notNull(), variantId: text('variant_id'), sourceId: text('source_id').notNull(), connectorId: text('connector_id').notNull(), rawRecordId: text('raw_record_id'), sourceUrl: text('source_url').notNull(), externalId: text('external_id').notNull(), listingType: text('listing_type').notNull().default('unknown'), price: money('price'), currency: text('currency'), priceUsd: money('price_usd'), seller: text('seller'), sellerReputation: text('seller_reputation'), location: text('location'), shippingCost: money('shipping_cost'), quantity: integer('quantity'), condition: text('condition'), grader: text('grader'), grade: text('grade'), certificationNumber: text('certification_number'), imageUrls: jsonb('image_urls').$type().notNull().default([]), rawTitle: text('raw_title').notNull(), description: text('description'), listedAt: ts('listed_at'), endsAt: ts('ends_at'), availability: text('availability').notNull().default('available'), bidCount: integer('bid_count'), firstSeenAt: ts('first_seen_at').notNull(), lastSeenAt: ts('last_seen_at').notNull(), priceChangedAt: ts('price_changed_at'), crossListingGroupId: text('cross_listing_group_id'), confidence: ratio('confidence').notNull().default(0.8), dataQuality: real('data_quality').notNull().default(0), flags: textArray('flags'), /** value opportunity vs RIV, computed (§123) */ discountToRiv: ratio('discount_to_riv'), createdAt: createdAt(), updatedAt: updatedAt(), }, (t) => [ uniqueIndex('listings_source_external_uq').on(t.sourceId, t.externalId), index('listings_asset_avail_idx').on(t.assetId, t.availability), index('listings_avail_price_idx').on(t.availability, t.priceUsd), index('listings_ends_idx').on(t.endsAt), index('listings_last_seen_idx').on(t.lastSeenAt), // valuation worker updates asks per variant; deal rails / Deal Radar order by discount index('listings_variant_avail_idx').on(t.variantId, t.availability), index('listings_discount_idx').on(t.discountToRiv).where(sql`discount_to_riv is not null and availability = 'available'`), ], ); export const listingEvents = pgTable( 'listing_events', { id: text('id').primaryKey(), listingId: text('listing_id').notNull(), eventType: text('event_type').notNull(), // new | price_changed | sold | removed | relisted | auction_ended oldPrice: money('old_price'), newPrice: money('new_price'), currency: text('currency'), occurredAt: ts('occurred_at').notNull(), }, (t) => [index('listing_events_listing_idx').on(t.listingId, t.occurredAt)], ); export const auctions = pgTable( 'auctions', { id: text('id').primaryKey(), sourceId: text('source_id').notNull(), auctionHouse: text('auction_house').notNull(), name: text('name').notNull(), url: text('url').notNull(), startsAt: ts('starts_at'), endsAt: ts('ends_at'), location: text('location'), categorySlugs: textArray('category_slugs'), lotCount: integer('lot_count'), status: text('status').notNull().default('upcoming'), // upcoming | live | ended currency: text('currency'), createdAt: createdAt(), updatedAt: updatedAt(), }, (t) => [uniqueIndex('auctions_url_uq').on(t.url), index('auctions_ends_idx').on(t.endsAt)], ); export const auctionLots = pgTable( 'auction_lots', { id: text('id').primaryKey(), auctionId: text('auction_id').notNull(), assetId: text('asset_id'), variantId: text('variant_id'), sourceId: text('source_id').notNull(), lotNumber: text('lot_number'), title: text('title').notNull(), url: text('url').notNull(), estimateLow: money('estimate_low'), estimateHigh: money('estimate_high'), currentBid: money('current_bid'), hammerPrice: money('hammer_price'), currency: text('currency'), bidCount: integer('bid_count'), // ---- USD normalisation + auction intelligence (§33–§35), written by workers/auctions ---- estimateLowUsd: money('estimate_low_usd'), estimateHighUsd: money('estimate_high_usd'), currentBidUsd: money('current_bid_usd'), hammerPriceUsd: money('hammer_price_usd'), fxRate: real('fx_rate'), fxDate: date('fx_date'), buyerPremiumRate: real('buyer_premium_rate'), feeBasis: text('fee_basis'), /** buyer-pays cost of the current bid (or low estimate when no bid) in USD, premium included */ allInBidUsd: money('all_in_bid_usd'), allInEstimateLowUsd: money('all_in_estimate_low_usd'), allInEstimateHighUsd: money('all_in_estimate_high_usd'), rivUsdAtAssessment: money('riv_usd_at_assessment'), /** (all-in bid − RIV) / RIV, same sign convention as listings.discount_to_riv; null when ungated */ bidVsRiv: real('bid_vs_riv'), /** (all-in low estimate − RIV) / RIV */ estimateVsRiv: real('estimate_vs_riv'), /** deal | fair | premium | review | anomaly | ungated */ assessmentVerdict: text('assessment_verdict'), assessedAt: ts('assessed_at'), startsAt: ts('starts_at'), endsAt: ts('ends_at'), status: text('status').notNull().default('upcoming'), imageUrls: jsonb('image_urls').$type().notNull().default([]), grader: text('grader'), grade: text('grade'), createdAt: createdAt(), updatedAt: updatedAt(), }, (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')`)], ); /** Price-guide observations (market/low/mid/high) — informative, weighted below transactions. */ export const priceObservations = pgTable( 'price_observations', { id: text('id').primaryKey(), assetId: text('asset_id').notNull(), variantId: text('variant_id'), sourceId: text('source_id').notNull(), connectorId: text('connector_id').notNull(), rawRecordId: text('raw_record_id'), sourceUrl: text('source_url').notNull(), priceKind: text('price_kind').notNull(), price: money('price').notNull(), currency: text('currency').notNull(), priceUsd: money('price_usd').notNull(), observationDate: date('observation_date').notNull(), sampleSize: integer('sample_size'), dedupeKey: text('dedupe_key').notNull(), createdAt: createdAt(), }, (t) => [uniqueIndex('price_observations_dedupe_uq').on(t.dedupeKey), index('price_observations_asset_date_idx').on(t.assetId, t.observationDate)], ); export const crossListingGroups = pgTable('cross_listing_groups', { id: text('id').primaryKey(), assetId: text('asset_id'), signals: jsonObject>('signals'), createdAt: createdAt(), }); /** Daily FX rates from an official dataset (ECB via frankfurter); base USD (§136). */ export const fxRates = pgTable( 'fx_rates', { date: date('date').notNull(), base: text('base').notNull(), quote: text('quote').notNull(), rate: real('rate').notNull(), source: text('source').notNull().default('ecb'), }, (t) => [uniqueIndex('fx_rates_uq').on(t.date, t.base, t.quote)], ); export const news = pgTable( 'news', { id: text('id').primaryKey(), sourceId: text('source_id').notNull(), url: text('url').notNull(), title: text('title').notNull(), summary: text('summary'), aiSummary: text('ai_summary'), publishedAt: ts('published_at'), categorySlugs: textArray('category_slugs'), newsType: text('news_type'), // auction_results | record_sales | grading | releases | trends | discoveries | events imageUrl: text('image_url'), fetchedAt: ts('fetched_at').notNull(), }, (t) => [uniqueIndex('news_url_uq').on(t.url), index('news_published_idx').on(t.publishedAt)], ); /** * Timestamp-aware multi-currency view of sales (SPEC §19): every sale in USD, CAD, EUR, GBP and JPY * converted with the ECB rate in force on (or just before) the sale date — never today's rate. * fx_rates.rate = quote units per 1 USD (base USD). */ export const salesMultiCurrency = pgView('sales_multi_currency', { saleId: text('sale_id'), assetId: text('asset_id'), saleDate: ts('sale_date'), price: money('price'), currency: text('currency'), priceUsd: money('price_usd'), priceCad: money('price_cad'), priceEur: money('price_eur'), priceGbp: money('price_gbp'), priceJpy: money('price_jpy'), fxDateCad: date('fx_date_cad'), fxDateEur: date('fx_date_eur'), fxDateGbp: date('fx_date_gbp'), fxDateJpy: date('fx_date_jpy'), }).as(sql` select s.id as sale_id, s.asset_id, s.sale_date, s.price, s.currency, s.price_usd, 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, cad.date as fx_date_cad, eur.date as fx_date_eur, gbp.date as fx_date_gbp, jpy.date as fx_date_jpy from sales s 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 true 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 true 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 true 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 true `); /** Same for live/historical listings, converted at the listing's last observation date. */ export const listingsMultiCurrency = pgView('listings_multi_currency', { listingId: text('listing_id'), assetId: text('asset_id'), lastSeenAt: ts('last_seen_at'), price: money('price'), currency: text('currency'), priceUsd: money('price_usd'), priceCad: money('price_cad'), priceEur: money('price_eur'), priceGbp: money('price_gbp'), priceJpy: money('price_jpy'), }).as(sql` select l.id as listing_id, l.asset_id, l.last_seen_at, l.price, l.currency, l.price_usd, 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_jpy from listings l 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 true 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 true 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 true 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 true `);