TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1import { pgTable, text, integer, boolean, real, index, uniqueIndex, jsonb, date, primaryKey } from 'drizzle-orm/pg-core';2import { createdAt, ts, money, ratio, jsonObject, textArray } from './_common.js';34/** Full valuation record with method breakdown (§115, §191). */5export const valuations = pgTable(6 'valuations',7 {8 id: text('id').primaryKey(),9 assetId: text('asset_id').notNull(),10 variantId: text('variant_id'),11 computedAt: ts('computed_at').notNull(),12 rivUsd: money('riv_usd'),13 lowUsd: money('low_usd'),14 highUsd: money('high_usd'),15 confidence: ratio('confidence').notNull().default(0),16 confidenceLabel: text('confidence_label').notNull().default('insufficient'), // high | medium | low | insufficient17 sampleSize: integer('sample_size').notNull().default(0),18 windowDays: integer('window_days').notNull().default(365),19 methods: jsonObject<Record<string, number | null>>('methods'),20 salesUsed: textArray('sales_used'),21 observationsUsed: integer('observations_used').notNull().default(0),22 method: text('method').notNull().default('ensemble_v1'),23 notes: textArray('notes'),24 },25 (t) => [index('valuations_asset_idx').on(t.assetId, t.computedAt), index('valuations_variant_idx').on(t.variantId, t.computedAt)],26);2728/** Daily price series per asset/variant (repeat-sales friendly; §126 charts). */29export const priceSnapshots = pgTable(30 'price_snapshots',31 {32 assetId: text('asset_id').notNull(),33 variantId: text('variant_id').notNull().default(''),34 date: date('date').notNull(),35 rivUsd: money('riv_usd'),36 latestSaleUsd: money('latest_sale_usd'),37 medianUsd: money('median_usd'),38 salesCount: integer('sales_count').notNull().default(0),39 volumeUsd: money('volume_usd'),40 listingsCount: integer('listings_count').notNull().default(0),41 minAskUsd: money('min_ask_usd'),42 observationUsd: money('observation_usd'),43 },44 (t) => [primaryKey({ columns: [t.assetId, t.variantId, t.date] }), index('price_snapshots_date_idx').on(t.date)],45);4647/** Index definitions (§3). */48export const indices = pgTable('indices', {49 id: text('id').primaryKey(),50 ticker: text('ticker').notNull().unique(),51 name: text('name').notNull(),52 description: text('description'),53 categorySlugs: textArray('category_slugs'),54 familySlugs: textArray('family_slugs'),55 parentTicker: text('parent_ticker'),56 methodology: text('methodology').notNull().default('chain_linked_equal_weight_v1'),57 weighting: text('weighting').notNull().default('equal'), // equal | liquidity | value58 baseDate: date('base_date').notNull(),59 baseValue: real('base_value').notNull().default(1000),60 minConstituents: integer('min_constituents').notNull().default(10),61 active: boolean('active').notNull().default(true),62 isFlagship: boolean('is_flagship').notNull().default(false),63 color: text('color'),64 createdAt: createdAt(),65});6667export const indexValues = pgTable(68 'index_values',69 {70 indexId: text('index_id').notNull(),71 date: date('date').notNull(),72 value: real('value').notNull(),73 constituentsCount: integer('constituents_count').notNull().default(0),74 transactions: integer('transactions').notNull().default(0),75 volumeUsd: money('volume_usd'),76 medianSaleUsd: money('median_sale_usd'),77 avgSaleUsd: money('avg_sale_usd'),78 marketCapEstUsd: money('market_cap_est_usd'),79 marketCapConfidence: text('market_cap_confidence'),80 liquidityScore: real('liquidity_score'),81 momentum: real('momentum'),82 breadth: integer('breadth'),83 trackedAssets: integer('tracked_assets'),84 /** fraction of constituents with a fresh valuation that day */85 coverage: real('coverage'),86 },87 (t) => [primaryKey({ columns: [t.indexId, t.date] })],88);8990export const indexConstituents = pgTable(91 'index_constituents',92 {93 indexId: text('index_id').notNull(),94 assetId: text('asset_id').notNull(),95 variantId: text('variant_id').notNull().default(''),96 weight: real('weight').notNull().default(1),97 addedAt: date('added_at').notNull(),98 removedAt: date('removed_at'),99 reason: text('reason'),100 },101 (t) => [primaryKey({ columns: [t.indexId, t.assetId, t.variantId, t.addedAt] }), index('index_constituents_asset_idx').on(t.assetId)],102);103104/** Category-level daily aggregates for market pages & heatmap (§125, §151). */105export const categorySnapshots = pgTable(106 'category_snapshots',107 {108 categorySlug: text('category_slug').notNull(),109 date: date('date').notNull(),110 indexValue: real('index_value'),111 trackedAssets: integer('tracked_assets').notNull().default(0),112 assetsWithValuation: integer('assets_with_valuation').notNull().default(0),113 sales: integer('sales').notNull().default(0),114 volumeUsd: money('volume_usd'),115 medianSaleUsd: money('median_sale_usd'),116 activeListings: integer('active_listings').notNull().default(0),117 marketCapEstUsd: money('market_cap_est_usd'),118 liquidityScore: real('liquidity_score'),119 change1d: ratio('change_1d'),120 change7d: ratio('change_7d'),121 change30d: ratio('change_30d'),122 change1y: ratio('change_1y'),123 },124 (t) => [primaryKey({ columns: [t.categorySlug, t.date] })],125);126127/** Rare Radar findings (§155) and record sales (§154) are derived views persisted for speed. */128export const radarFindings = pgTable(129 'radar_findings',130 {131 id: text('id').primaryKey(),132 assetId: text('asset_id').notNull(),133 kind: text('kind').notNull(), // first_listing_in_years | ultra_low_population | rare_auction_appearance | price_discrepancy | undocumented_variation | record_sale134 score: real('score').notNull(),135 evidence: jsonb('evidence').$type<Record<string, unknown>>().notNull().default({}),136 entityType: text('entity_type'),137 entityId: text('entity_id'),138 detectedAt: ts('detected_at').notNull(),139 expiresAt: ts('expires_at'),140 },141 (t) => [index('radar_kind_idx').on(t.kind, t.detectedAt), uniqueIndex('radar_entity_uq').on(t.kind, t.entityType, t.entityId)],142);143144/** Correlation matrix cache (§186). */145export const correlations = pgTable(146 'correlations',147 {148 a: text('a').notNull(),149 b: text('b').notNull(),150 windowDays: integer('window_days').notNull(),151 coefficient: real('coefficient').notNull(),152 observations: integer('observations').notNull(),153 computedAt: ts('computed_at').notNull(),154 },155 (t) => [primaryKey({ columns: [t.a, t.b, t.windowDays] })],156);157158/** Benchmark series (S&P 500, gold, BTC, CPI) fetched from official/free datasets (§187). */159export const benchmarks = pgTable(160 'benchmarks',161 {162 ticker: text('ticker').notNull(),163 date: date('date').notNull(),164 value: real('value').notNull(),165 source: text('source').notNull(),166 },167 (t) => [primaryKey({ columns: [t.ticker, t.date] })],168);169