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, updatedAt, ts, money, jsonObject } from './_common.js';34export const users = pgTable('users', {5 id: text('id').primaryKey(),6 email: text('email').notNull().unique(),7 emailVerifiedAt: ts('email_verified_at'),8 passwordHash: text('password_hash'),9 name: text('name'),10 role: text('role').notNull().default('user'), // user | pro | admin11 displayCurrency: text('display_currency').notNull().default('USD'),12 /** oauth/passkey providers linked (§175) */13 providers: jsonb('providers').$type<Array<{ provider: string; subject: string }>>().notNull().default([]),14 preferences: jsonObject<Record<string, unknown>>('preferences'),15 /** public collector handle (lowercase, unique) */16 handle: text('handle').unique(),17 avatarUrl: text('avatar_url'),18 bio: text('bio'),19 mfaEnabled: boolean('mfa_enabled').notNull().default(false),20 /** TOTP secret, encrypted at rest with SESSION_SECRET-derived key */21 totpSecretEnc: text('totp_secret_enc'),22 /** ask an e-mail code on every new device even without TOTP */23 alwaysAskCode: boolean('always_ask_code').notNull().default(true),24 passwordChangedAt: ts('password_changed_at'),25 pendingEmail: text('pending_email'),26 deletedAt: ts('deleted_at'),27 purgeAfter: ts('purge_after'),28 createdAt: createdAt(),29 lastLoginAt: ts('last_login_at'),30});3132export const sessions = pgTable(33 'sessions',34 {35 id: text('id').primaryKey(),36 userId: text('user_id').notNull(),37 expiresAt: ts('expires_at').notNull(),38 userAgent: text('user_agent'),39 ip: text('ip'),40 lastSeenAt: ts('last_seen_at'),41 revokedAt: ts('revoked_at'),42 createdAt: createdAt(),43 },44 (t) => [index('sessions_user_idx').on(t.userId)],45);4647export const collections = pgTable(48 'collections',49 {50 id: text('id').primaryKey(),51 userId: text('user_id').notNull(),52 name: text('name').notNull(),53 description: text('description'),54 isPublic: boolean('is_public').notNull().default(false),55 publicSlug: text('public_slug'),56 /** default | wishlist | vault … free-form */57 kind: text('kind').notNull().default('collection'),58 budgetUsd: money('budget_usd'),59 color: text('color'),60 createdAt: createdAt(),61 updatedAt: updatedAt(),62 },63 (t) => [index('collections_user_idx').on(t.userId), uniqueIndex('collections_public_slug_uq').on(t.publicSlug)],64);6566export const collectionItems = pgTable(67 'collection_items',68 {69 id: text('id').primaryKey(),70 collectionId: text('collection_id').notNull(),71 assetId: text('asset_id').notNull(),72 variantId: text('variant_id'),73 quantity: integer('quantity').notNull().default(1),74 acquiredAt: date('acquired_at'),75 purchasePrice: money('purchase_price'),76 purchaseCurrency: text('purchase_currency'),77 purchasePriceUsd: money('purchase_price_usd'),78 source: text('source'),79 grader: text('grader'),80 grade: text('grade'),81 certificationNumber: text('certification_number'),82 serial: text('serial'),83 photos: jsonb('photos').$type<string[]>().notNull().default([]),84 notes: text('notes'),85 tags: jsonb('tags').$type<string[]>().notNull().default([]),86 condition: text('condition'),87 /** member-entered fallback value when RareIndex has no valuation yet (never shown as RIV) */88 manualValueUsd: money('manual_value_usd'),89 soldAt: date('sold_at'),90 soldPriceUsd: money('sold_price_usd'),91 createdAt: createdAt(),92 updatedAt: updatedAt(),93 },94 (t) => [index('collection_items_collection_idx').on(t.collectionId), index('collection_items_asset_idx').on(t.assetId)],95);9697/** Daily portfolio value history (§130). */98export const collectionSnapshots = pgTable(99 'collection_snapshots',100 {101 collectionId: text('collection_id').notNull(),102 date: date('date').notNull(),103 valueUsd: money('value_usd').notNull(),104 costBasisUsd: money('cost_basis_usd').notNull(),105 items: integer('items').notNull(),106 },107 (t) => [primaryKey({ columns: [t.collectionId, t.date] })],108);109110export const watchlists = pgTable(111 'watchlists',112 {113 id: text('id').primaryKey(),114 userId: text('user_id').notNull(),115 name: text('name').notNull().default('Watchlist'),116 createdAt: createdAt(),117 },118 (t) => [index('watchlists_user_idx').on(t.userId)],119);120121export const watchlistItems = pgTable(122 'watchlist_items',123 {124 id: text('id').primaryKey(),125 watchlistId: text('watchlist_id').notNull(),126 targetType: text('target_type').notNull(), // asset | category | brand | set | source | auction127 targetId: text('target_id').notNull(),128 label: text('label'),129 note: text('note'),130 targetPriceUsd: money('target_price_usd'),131 /** RIV when added, to show change since watching */132 baselineUsd: money('baseline_usd'),133 createdAt: createdAt(),134 },135 (t) => [uniqueIndex('watchlist_items_uq').on(t.watchlistId, t.targetType, t.targetId)],136);137138export const alerts = pgTable(139 'alerts',140 {141 id: text('id').primaryKey(),142 userId: text('user_id').notNull(),143 alertType: text('alert_type').notNull(), // price_below | price_above | new_listing | new_auction | auction_ending | auction_below_riv | record_sale | unusual_volume | market_move | rare_item | population_update144 targetType: text('target_type').notNull(),145 targetId: text('target_id').notNull(),146 threshold: money('threshold'),147 currency: text('currency'),148 params: jsonObject<Record<string, unknown>>('params'),149 channel: text('channel').notNull().default('inapp'), // inapp | email | both150 active: boolean('active').notNull().default(true),151 name: text('name'),152 cooldownMinutes: integer('cooldown_minutes').notNull().default(1440),153 lastTriggeredAt: ts('last_triggered_at'),154 triggerCount: integer('trigger_count').notNull().default(0),155 createdAt: createdAt(),156 },157 (t) => [index('alerts_user_idx').on(t.userId), index('alerts_target_idx').on(t.targetType, t.targetId, t.active)],158);159160export const alertEvents = pgTable(161 'alert_events',162 {163 id: text('id').primaryKey(),164 alertId: text('alert_id').notNull(),165 userId: text('user_id').notNull(),166 message: text('message').notNull(),167 payload: jsonObject<Record<string, unknown>>('payload'),168 readAt: ts('read_at'),169 createdAt: createdAt(),170 },171 (t) => [index('alert_events_user_idx').on(t.userId, t.createdAt)],172);173174export const apiKeys = pgTable(175 'api_keys',176 {177 id: text('id').primaryKey(),178 userId: text('user_id'),179 name: text('name').notNull(),180 prefix: text('prefix').notNull(),181 keyHash: text('key_hash').notNull().unique(),182 tier: text('tier').notNull().default('free'), // free | hobby | professional | research | enterprise183 rateLimitPerMinute: integer('rate_limit_per_minute').notNull().default(60),184 dailyQuota: integer('daily_quota').notNull().default(1000),185 createdAt: createdAt(),186 lastUsedAt: ts('last_used_at'),187 revokedAt: ts('revoked_at'),188 },189 (t) => [index('api_keys_user_idx').on(t.userId)],190);191192export const apiUsage = pgTable(193 'api_usage',194 {195 keyId: text('key_id').notNull(),196 date: date('date').notNull(),197 endpoint: text('endpoint').notNull(),198 count: integer('count').notNull().default(0),199 latencyMsAvg: real('latency_ms_avg'),200 },201 (t) => [primaryKey({ columns: [t.keyId, t.date, t.endpoint] })],202);203204export const assetViews = pgTable(205 'asset_views',206 {207 assetId: text('asset_id').notNull(),208 date: date('date').notNull(),209 views: integer('views').notNull().default(0),210 },211 (t) => [primaryKey({ columns: [t.assetId, t.date] })],212);213214export const searchLog = pgTable(215 'search_log',216 {217 id: text('id').primaryKey(),218 query: text('query').notNull(),219 normalized: text('normalized').notNull(),220 results: integer('results').notNull(),221 userId: text('user_id'),222 createdAt: createdAt(),223 },224 (t) => [index('search_log_created_idx').on(t.createdAt), index('search_log_normalized_idx').on(t.normalized)],225);226