1import { pgTable, text, integer, boolean, real, index, uniqueIndex, jsonb, date } from 'drizzle-orm/pg-core';2import { createdAt, updatedAt, ts, money, ratio, jsonObject, textArray } from './_common.js';34/** Verified/observed transactions (§110). Prices keep native currency + USD at historical FX (§136). */5export const sales = pgTable(6 'sales',7 {8 id: text('id').primaryKey(),9 assetId: text('asset_id').notNull(),10 variantId: text('variant_id'),11 sourceId: text('source_id').notNull(),12 connectorId: text('connector_id').notNull(),13 rawRecordId: text('raw_record_id'),14 normalizedRecordId: text('normalized_record_id'),15 sourceUrl: text('source_url').notNull(),16 externalId: text('external_id'),17 saleType: text('sale_type').notNull().default('unknown'),18 saleDate: ts('sale_date').notNull(),19 price: money('price').notNull(),20 currency: text('currency').notNull(),21 priceUsd: money('price_usd').notNull(),22 fxRate: real('fx_rate'),23 fxDate: date('fx_date'),24 buyerPremiumIncluded: boolean('buyer_premium_included'),25 quantity: integer('quantity').notNull().default(1),26 isBundle: boolean('is_bundle').notNull().default(false),27 condition: text('condition'),28 grader: text('grader'),29 grade: text('grade'),30 certificationNumber: text('certification_number'),31 location: text('location'),32 auctionHouse: text('auction_house'),33 lotNumber: text('lot_number'),34 imageUrls: jsonb('image_urls').$type<string[]>().notNull().default([]),35 rawTitle: text('raw_title').notNull(),36 confidence: ratio('confidence').notNull().default(0.8),37 dataQuality: real('data_quality').notNull().default(0),38 /** valid | flagged | excluded (never deleted, §116) */39 status: text('status').notNull().default('valid'),40 flags: textArray('flags'),41 dedupeKey: text('dedupe_key').notNull(),42 createdAt: createdAt(),43 },44 (t) => [45 uniqueIndex('sales_dedupe_uq').on(t.dedupeKey),46 index('sales_asset_date_idx').on(t.assetId, t.saleDate),47 index('sales_variant_date_idx').on(t.variantId, t.saleDate),48 index('sales_source_idx').on(t.sourceId, t.saleDate),49 index('sales_date_idx').on(t.saleDate),50 index('sales_price_idx').on(t.priceUsd),51 ],52);5354/** Current and historical listings (§111). Listing price is never treated as market value. */55export const listings = pgTable(56 'listings',57 {58 id: text('id').primaryKey(),59 assetId: text('asset_id').notNull(),60 variantId: text('variant_id'),61 sourceId: text('source_id').notNull(),62 connectorId: text('connector_id').notNull(),63 rawRecordId: text('raw_record_id'),64 sourceUrl: text('source_url').notNull(),65 externalId: text('external_id').notNull(),66 listingType: text('listing_type').notNull().default('unknown'),67 price: money('price'),68 currency: text('currency'),69 priceUsd: money('price_usd'),70 seller: text('seller'),71 sellerReputation: text('seller_reputation'),72 location: text('location'),73 shippingCost: money('shipping_cost'),74 quantity: integer('quantity'),75 condition: text('condition'),76 grader: text('grader'),77 grade: text('grade'),78 certificationNumber: text('certification_number'),79 imageUrls: jsonb('image_urls').$type<string[]>().notNull().default([]),80 rawTitle: text('raw_title').notNull(),81 description: text('description'),82 listedAt: ts('listed_at'),83 endsAt: ts('ends_at'),84 availability: text('availability').notNull().default('available'),85 bidCount: integer('bid_count'),86 firstSeenAt: ts('first_seen_at').notNull(),87 lastSeenAt: ts('last_seen_at').notNull(),88 priceChangedAt: ts('price_changed_at'),89 crossListingGroupId: text('cross_listing_group_id'),90 confidence: ratio('confidence').notNull().default(0.8),91 dataQuality: real('data_quality').notNull().default(0),92 flags: textArray('flags'),93 /** value opportunity vs RIV, computed (§123) */94 discountToRiv: ratio('discount_to_riv'),95 createdAt: createdAt(),96 updatedAt: updatedAt(),97 },98 (t) => [99 uniqueIndex('listings_source_external_uq').on(t.sourceId, t.externalId),100 index('listings_asset_avail_idx').on(t.assetId, t.availability),101 index('listings_avail_price_idx').on(t.availability, t.priceUsd),102 index('listings_ends_idx').on(t.endsAt),103 index('listings_last_seen_idx').on(t.lastSeenAt),104 ],105);106107export const listingEvents = pgTable(108 'listing_events',109 {110 id: text('id').primaryKey(),111 listingId: text('listing_id').notNull(),112 eventType: text('event_type').notNull(), // new | price_changed | sold | removed | relisted | auction_ended113 oldPrice: money('old_price'),114 newPrice: money('new_price'),115 currency: text('currency'),116 occurredAt: ts('occurred_at').notNull(),117 },118 (t) => [index('listing_events_listing_idx').on(t.listingId, t.occurredAt)],119);120121export const auctions = pgTable(122 'auctions',123 {124 id: text('id').primaryKey(),125 sourceId: text('source_id').notNull(),126 auctionHouse: text('auction_house').notNull(),127 name: text('name').notNull(),128 url: text('url').notNull(),129 startsAt: ts('starts_at'),130 endsAt: ts('ends_at'),131 location: text('location'),132 categorySlugs: textArray('category_slugs'),133 lotCount: integer('lot_count'),134 status: text('status').notNull().default('upcoming'), // upcoming | live | ended135 currency: text('currency'),136 createdAt: createdAt(),137 updatedAt: updatedAt(),138 },139 (t) => [uniqueIndex('auctions_url_uq').on(t.url), index('auctions_ends_idx').on(t.endsAt)],140);141142export const auctionLots = pgTable(143 'auction_lots',144 {145 id: text('id').primaryKey(),146 auctionId: text('auction_id').notNull(),147 assetId: text('asset_id'),148 variantId: text('variant_id'),149 sourceId: text('source_id').notNull(),150 lotNumber: text('lot_number'),151 title: text('title').notNull(),152 url: text('url').notNull(),153 estimateLow: money('estimate_low'),154 estimateHigh: money('estimate_high'),155 currentBid: money('current_bid'),156 hammerPrice: money('hammer_price'),157 currency: text('currency'),158 bidCount: integer('bid_count'),159 startsAt: ts('starts_at'),160 endsAt: ts('ends_at'),161 status: text('status').notNull().default('upcoming'),162 imageUrls: jsonb('image_urls').$type<string[]>().notNull().default([]),163 grader: text('grader'),164 grade: text('grade'),165 createdAt: createdAt(),166 updatedAt: updatedAt(),167 },168 (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)],169);170171/** Price-guide observations (market/low/mid/high) — informative, weighted below transactions. */172export const priceObservations = pgTable(173 'price_observations',174 {175 id: text('id').primaryKey(),176 assetId: text('asset_id').notNull(),177 variantId: text('variant_id'),178 sourceId: text('source_id').notNull(),179 connectorId: text('connector_id').notNull(),180 rawRecordId: text('raw_record_id'),181 sourceUrl: text('source_url').notNull(),182 priceKind: text('price_kind').notNull(),183 price: money('price').notNull(),184 currency: text('currency').notNull(),185 priceUsd: money('price_usd').notNull(),186 observationDate: date('observation_date').notNull(),187 sampleSize: integer('sample_size'),188 dedupeKey: text('dedupe_key').notNull(),189 createdAt: createdAt(),190 },191 (t) => [uniqueIndex('price_observations_dedupe_uq').on(t.dedupeKey), index('price_observations_asset_date_idx').on(t.assetId, t.observationDate)],192);193194export const crossListingGroups = pgTable('cross_listing_groups', {195 id: text('id').primaryKey(),196 assetId: text('asset_id'),197 signals: jsonObject<Record<string, unknown>>('signals'),198 createdAt: createdAt(),199});200201/** Daily FX rates from an official dataset (ECB via frankfurter); base USD (§136). */202export const fxRates = pgTable(203 'fx_rates',204 {205 date: date('date').notNull(),206 base: text('base').notNull(),207 quote: text('quote').notNull(),208 rate: real('rate').notNull(),209 source: text('source').notNull().default('ecb'),210 },211 (t) => [uniqueIndex('fx_rates_uq').on(t.date, t.base, t.quote)],212);213214export const news = pgTable(215 'news',216 {217 id: text('id').primaryKey(),218 sourceId: text('source_id').notNull(),219 url: text('url').notNull(),220 title: text('title').notNull(),221 summary: text('summary'),222 aiSummary: text('ai_summary'),223 publishedAt: ts('published_at'),224 categorySlugs: textArray('category_slugs'),225 newsType: text('news_type'), // auction_results | record_sales | grading | releases | trends | discoveries | events226 imageUrl: text('image_url'),227 fetchedAt: ts('fetched_at').notNull(),228 },229 (t) => [uniqueIndex('news_url_uq').on(t.url), index('news_published_idx').on(t.publishedAt)],230);231