1import { pgTable, text, integer, boolean, real, index, uniqueIndex, jsonb, vector } from 'drizzle-orm/pg-core';2import { sql } from 'drizzle-orm';3import { createdAt, updatedAt, ts, money, ratio, jsonObject, textArray, tsvector } from './_common.js';45/**6 * Canonical asset (§109): the collectible object independent of grade/condition.7 * `canonical_key` is a deterministic normalised key (category|set|number|name|variant|language|year…)8 * used for entity resolution; `identifiers` hold deterministic external ids.9 */10export const assets = pgTable(11 'assets',12 {13 id: text('id').primaryKey(),14 slug: text('slug').notNull(),15 canonicalKey: text('canonical_key').notNull(),16 categorySlug: text('category_slug').notNull(),17 subcategorySlug: text('subcategory_slug'),18 familySlug: text('family_slug').notNull(),19 franchise: text('franchise'),20 brand: text('brand'),21 series: text('series'),22 setSlug: text('set_slug'),23 setName: text('set_name'),24 setCode: text('set_code'),25 name: text('name').notNull(),26 /** full display title, e.g. "Charizard #4/102 — Base Set 1st Edition Holo (1999)" */27 title: text('title').notNull(),28 model: text('model'),29 reference: text('reference'),30 number: text('number'),31 year: integer('year'),32 edition: text('edition'),33 variant: text('variant'),34 language: text('language'),35 region: text('region'),36 country: text('country'),37 material: text('material'),38 size: text('size'),39 color: text('color'),40 rarity: text('rarity'),41 productionQuantity: integer('production_quantity'),42 originalMsrp: money('original_msrp'),43 originalMsrpCurrency: text('original_msrp_currency'),44 releaseDate: text('release_date'),45 description: text('description'),46 heroImageUrl: text('hero_image_url'),47 identifiers: jsonObject<Record<string, string>>('identifiers'),48 metadata: jsonObject<Record<string, unknown>>('metadata'),49 /** ids of assets merged into this one (audit trail) */50 mergedFrom: textArray('merged_from'),51 /** 0–100 (§150) */52 dataQuality: real('data_quality').notNull().default(0),53 verified: boolean('verified').notNull().default(false),54 search: tsvector('search').generatedAlwaysAs(55 (): ReturnType<typeof sql> =>56 sql`setweight(to_tsvector('simple', coalesce(name, '')), 'A') || setweight(to_tsvector('simple', coalesce(set_name, '') || ' ' || coalesce(number, '') || ' ' || coalesce(variant, '') || ' ' || coalesce(edition, '')), 'B') || setweight(to_tsvector('simple', coalesce(brand, '') || ' ' || coalesce(franchise, '') || ' ' || coalesce(reference, '') || ' ' || coalesce(model, '') || ' ' || coalesce(year::text, '') || ' ' || coalesce(category_slug, '')), 'C')`,57 ),58 createdAt: createdAt(),59 updatedAt: updatedAt(),60 },61 (t) => [62 uniqueIndex('assets_canonical_key_uq').on(t.canonicalKey),63 uniqueIndex('assets_slug_uq').on(t.slug),64 index('assets_category_idx').on(t.categorySlug),65 index('assets_family_idx').on(t.familySlug),66 index('assets_set_idx').on(t.setSlug),67 index('assets_search_gin').using('gin', t.search),68 index('assets_title_trgm').using('gin', sql`${t.title} gin_trgm_ops`),69 index('assets_identifiers_gin').using('gin', t.identifiers),70 ],71);7273/** Grade/condition-specific variant of an asset (PSA 10, CIB, deadstock size 10…) — §112, §117, §118. */74export const assetVariants = pgTable(75 'asset_variants',76 {77 id: text('id').primaryKey(),78 assetId: text('asset_id').notNull(),79 variantKey: text('variant_key').notNull(), // e.g. "psa|10", "raw|near_mint", "sealed", "bgs|9.5|black_label", "size|10"80 grader: text('grader'),81 grade: text('grade'),82 qualifier: text('qualifier'),83 condition: text('condition'),84 completeness: text('completeness'),85 sizeLabel: text('size_label'),86 label: text('label').notNull(), // human label "PSA 10"87 isDefault: boolean('is_default').notNull().default(false),88 createdAt: createdAt(),89 },90 (t) => [uniqueIndex('asset_variants_uq').on(t.assetId, t.variantKey), index('asset_variants_asset_idx').on(t.assetId)],91);9293/** Denormalised, frequently read metrics per asset (rebuilt by valuation/indices workers). */94export const assetStats = pgTable(95 'asset_stats',96 {97 assetId: text('asset_id').primaryKey(),98 rivUsd: money('riv_usd'),99 rivLowUsd: money('riv_low_usd'),100 rivHighUsd: money('riv_high_usd'),101 rivConfidence: ratio('riv_confidence'),102 rivSampleSize: integer('riv_sample_size').notNull().default(0),103 rivVariantId: text('riv_variant_id'),104 latestSaleUsd: money('latest_sale_usd'),105 latestSaleAt: ts('latest_sale_at'),106 change1d: ratio('change_1d'),107 change7d: ratio('change_7d'),108 change30d: ratio('change_30d'),109 change90d: ratio('change_90d'),110 change1y: ratio('change_1y'),111 athUsd: money('ath_usd'),112 athAt: ts('ath_at'),113 atlUsd: money('atl_usd'),114 atlAt: ts('atl_at'),115 salesCount: integer('sales_count').notNull().default(0),116 sales30d: integer('sales_30d').notNull().default(0),117 sales1y: integer('sales_1y').notNull().default(0),118 volume30dUsd: money('volume_30d_usd'),119 activeListings: integer('active_listings').notNull().default(0),120 minAskUsd: money('min_ask_usd'),121 observationsCount: integer('observations_count').notNull().default(0),122 sourcesCount: integer('sources_count').notNull().default(0),123 liquidityScore: real('liquidity_score'),124 rarityScore: real('rarity_score'),125 momentum7d: real('momentum_7d'),126 momentum30d: real('momentum_30d'),127 momentum90d: real('momentum_90d'),128 momentum1y: real('momentum_1y'),129 trendingScore: real('trending_score'),130 valueOpportunity: real('value_opportunity'),131 dataQuality: real('data_quality'),132 watchers: integer('watchers').notNull().default(0),133 views30d: integer('views_30d').notNull().default(0),134 updatedAt: updatedAt(),135 },136 (t) => [index('asset_stats_riv_idx').on(t.rivUsd), index('asset_stats_trending_idx').on(t.trendingScore), index('asset_stats_liquidity_idx').on(t.liquidityScore)],137);138139export const variantStats = pgTable('variant_stats', {140 variantId: text('variant_id').primaryKey(),141 assetId: text('asset_id').notNull(),142 rivUsd: money('riv_usd'),143 rivLowUsd: money('riv_low_usd'),144 rivHighUsd: money('riv_high_usd'),145 rivConfidence: ratio('riv_confidence'),146 rivSampleSize: integer('riv_sample_size').notNull().default(0),147 latestSaleUsd: money('latest_sale_usd'),148 latestSaleAt: ts('latest_sale_at'),149 change30d: ratio('change_30d'),150 change1y: ratio('change_1y'),151 salesCount: integer('sales_count').notNull().default(0),152 sales30d: integer('sales_30d').notNull().default(0),153 activeListings: integer('active_listings').notNull().default(0),154 minAskUsd: money('min_ask_usd'),155 liquidityScore: real('liquidity_score'),156 updatedAt: updatedAt(),157});158159export const images = pgTable(160 'images',161 {162 id: text('id').primaryKey(),163 assetId: text('asset_id'),164 listingId: text('listing_id'),165 saleId: text('sale_id'),166 sourceId: text('source_id'),167 url: text('url').notNull(),168 role: text('role').notNull().default('gallery'), // hero | gallery | source169 width: integer('width'),170 height: integer('height'),171 phash: text('phash'),172 embedding: vector('embedding', { dimensions: 512 }),173 attribution: text('attribution'),174 /** unchecked | ok | dead | blocked | error (§113 image pipeline) */175 status: text('status').notNull().default('unchecked'),176 checkedAt: ts('checked_at'),177 bytes: integer('bytes'),178 contentType: text('content_type'),179 /** sha1(url): key of the on-disk cache under RI_DATA_DIR/images */180 cacheKey: text('cache_key'),181 error: text('error'),182 createdAt: createdAt(),183 },184 (t) => [index('images_asset_idx').on(t.assetId), index('images_phash_idx').on(t.phash), uniqueIndex('images_url_uq').on(t.url), index('images_cache_key_idx').on(t.cacheKey), index('images_status_idx').on(t.status, t.checkedAt)],185);186187/** Text embeddings for hybrid search / entity resolution (§112, §138). */188export const assetEmbeddings = pgTable('asset_embeddings', {189 assetId: text('asset_id').primaryKey(),190 model: text('model').notNull(),191 embedding: vector('embedding', { dimensions: 1536 }).notNull(),192 updatedAt: updatedAt(),193});194195export const populationReports = pgTable(196 'population_reports',197 {198 id: text('id').primaryKey(),199 assetId: text('asset_id').notNull(),200 grader: text('grader').notNull(),201 sourceId: text('source_id').notNull(),202 sourceUrl: text('source_url'),203 reportDate: text('report_date').notNull(),204 total: integer('total').notNull(),205 byGrade: jsonb('by_grade').$type<Record<string, number>>().notNull(),206 createdAt: createdAt(),207 },208 (t) => [uniqueIndex('population_reports_uq').on(t.assetId, t.grader, t.reportDate)],209);210211/** Empirical grade premiums (§118). */212export const gradePremiums = pgTable(213 'grade_premiums',214 {215 id: text('id').primaryKey(),216 categorySlug: text('category_slug').notNull(),217 grader: text('grader').notNull(),218 grade: text('grade').notNull(),219 marketMultiplier: real('market_multiplier').notNull(),220 sampleSize: integer('sample_size').notNull(),221 computedAt: ts('computed_at').notNull(),222 },223 (t) => [uniqueIndex('grade_premiums_uq').on(t.categorySlug, t.grader, t.grade)],224);225