TypeScript 61.9%
HTML 37.2%
SQL 0.7%
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) => [137 index('asset_stats_riv_idx').on(t.rivUsd),138 index('asset_stats_trending_idx').on(t.trendingScore),139 index('asset_stats_liquidity_idx').on(t.liquidityScore),140 // movers, screener and opportunity rails order by these141 index('asset_stats_change_7d_idx').on(t.change7d),142 index('asset_stats_change_30d_idx').on(t.change30d),143 index('asset_stats_change_1y_idx').on(t.change1y),144 index('asset_stats_opportunity_idx').on(t.valueOpportunity).where(sql`value_opportunity is not null`),145 index('asset_stats_sales_30d_idx').on(t.sales30d),146 ],147);148149export const variantStats = pgTable('variant_stats', {150 variantId: text('variant_id').primaryKey(),151 assetId: text('asset_id').notNull(),152 rivUsd: money('riv_usd'),153 rivLowUsd: money('riv_low_usd'),154 rivHighUsd: money('riv_high_usd'),155 rivConfidence: ratio('riv_confidence'),156 rivSampleSize: integer('riv_sample_size').notNull().default(0),157 latestSaleUsd: money('latest_sale_usd'),158 latestSaleAt: ts('latest_sale_at'),159 change30d: ratio('change_30d'),160 change1y: ratio('change_1y'),161 salesCount: integer('sales_count').notNull().default(0),162 sales30d: integer('sales_30d').notNull().default(0),163 activeListings: integer('active_listings').notNull().default(0),164 minAskUsd: money('min_ask_usd'),165 liquidityScore: real('liquidity_score'),166 updatedAt: updatedAt(),167}, (t) => [index('variant_stats_asset_idx').on(t.assetId)]);168169export const images = pgTable(170 'images',171 {172 id: text('id').primaryKey(),173 assetId: text('asset_id'),174 listingId: text('listing_id'),175 saleId: text('sale_id'),176 sourceId: text('source_id'),177 url: text('url').notNull(),178 role: text('role').notNull().default('gallery'), // hero | gallery | source179 width: integer('width'),180 height: integer('height'),181 phash: text('phash'),182 embedding: vector('embedding', { dimensions: 512 }),183 attribution: text('attribution'),184 /** unchecked | ok | dead | blocked | error (§113 image pipeline) */185 status: text('status').notNull().default('unchecked'),186 checkedAt: ts('checked_at'),187 bytes: integer('bytes'),188 contentType: text('content_type'),189 /** sha1(url): key of the on-disk cache under RI_DATA_DIR/images */190 cacheKey: text('cache_key'),191 error: text('error'),192 createdAt: createdAt(),193 },194 (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)],195);196197/** Text embeddings for hybrid search / entity resolution (§112, §138). */198export const assetEmbeddings = pgTable('asset_embeddings', {199 assetId: text('asset_id').primaryKey(),200 model: text('model').notNull(),201 embedding: vector('embedding', { dimensions: 1536 }).notNull(),202 updatedAt: updatedAt(),203});204205export const populationReports = pgTable(206 'population_reports',207 {208 id: text('id').primaryKey(),209 assetId: text('asset_id').notNull(),210 grader: text('grader').notNull(),211 sourceId: text('source_id').notNull(),212 sourceUrl: text('source_url'),213 reportDate: text('report_date').notNull(),214 total: integer('total').notNull(),215 byGrade: jsonb('by_grade').$type<Record<string, number>>().notNull(),216 createdAt: createdAt(),217 },218 (t) => [uniqueIndex('population_reports_uq').on(t.assetId, t.grader, t.reportDate)],219);220221/** Empirical grade premiums (§118). */222export const gradePremiums = pgTable(223 'grade_premiums',224 {225 id: text('id').primaryKey(),226 categorySlug: text('category_slug').notNull(),227 grader: text('grader').notNull(),228 grade: text('grade').notNull(),229 marketMultiplier: real('market_multiplier').notNull(),230 sampleSize: integer('sample_size').notNull(),231 computedAt: ts('computed_at').notNull(),232 },233 (t) => [uniqueIndex('grade_premiums_uq').on(t.categorySlug, t.grader, t.grade)],234);235