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 createdAt: createdAt(),16 lastLoginAt: ts('last_login_at'),17});1819export const sessions = pgTable(20 'sessions',21 {22 id: text('id').primaryKey(),23 userId: text('user_id').notNull(),24 expiresAt: ts('expires_at').notNull(),25 userAgent: text('user_agent'),26 createdAt: createdAt(),27 },28 (t) => [index('sessions_user_idx').on(t.userId)],29);3031export const collections = pgTable(32 'collections',33 {34 id: text('id').primaryKey(),35 userId: text('user_id').notNull(),36 name: text('name').notNull(),37 description: text('description'),38 isPublic: boolean('is_public').notNull().default(false),39 publicSlug: text('public_slug'),40 createdAt: createdAt(),41 updatedAt: updatedAt(),42 },43 (t) => [index('collections_user_idx').on(t.userId), uniqueIndex('collections_public_slug_uq').on(t.publicSlug)],44);4546export const collectionItems = pgTable(47 'collection_items',48 {49 id: text('id').primaryKey(),50 collectionId: text('collection_id').notNull(),51 assetId: text('asset_id').notNull(),52 variantId: text('variant_id'),53 quantity: integer('quantity').notNull().default(1),54 acquiredAt: date('acquired_at'),55 purchasePrice: money('purchase_price'),56 purchaseCurrency: text('purchase_currency'),57 purchasePriceUsd: money('purchase_price_usd'),58 source: text('source'),59 grader: text('grader'),60 grade: text('grade'),61 certificationNumber: text('certification_number'),62 serial: text('serial'),63 photos: jsonb('photos').$type<string[]>().notNull().default([]),64 notes: text('notes'),65 createdAt: createdAt(),66 updatedAt: updatedAt(),67 },68 (t) => [index('collection_items_collection_idx').on(t.collectionId), index('collection_items_asset_idx').on(t.assetId)],69);7071/** Daily portfolio value history (§130). */72export const collectionSnapshots = pgTable(73 'collection_snapshots',74 {75 collectionId: text('collection_id').notNull(),76 date: date('date').notNull(),77 valueUsd: money('value_usd').notNull(),78 costBasisUsd: money('cost_basis_usd').notNull(),79 items: integer('items').notNull(),80 },81 (t) => [primaryKey({ columns: [t.collectionId, t.date] })],82);8384export const watchlists = pgTable(85 'watchlists',86 {87 id: text('id').primaryKey(),88 userId: text('user_id').notNull(),89 name: text('name').notNull().default('Watchlist'),90 createdAt: createdAt(),91 },92 (t) => [index('watchlists_user_idx').on(t.userId)],93);9495export const watchlistItems = pgTable(96 'watchlist_items',97 {98 id: text('id').primaryKey(),99 watchlistId: text('watchlist_id').notNull(),100 targetType: text('target_type').notNull(), // asset | category | brand | set | source | auction101 targetId: text('target_id').notNull(),102 createdAt: createdAt(),103 },104 (t) => [uniqueIndex('watchlist_items_uq').on(t.watchlistId, t.targetType, t.targetId)],105);106107export const alerts = pgTable(108 'alerts',109 {110 id: text('id').primaryKey(),111 userId: text('user_id').notNull(),112 alertType: text('alert_type').notNull(), // price_below | price_above | new_listing | new_auction | auction_ending | record_sale | unusual_volume | market_move | rare_item | population_update113 targetType: text('target_type').notNull(),114 targetId: text('target_id').notNull(),115 threshold: money('threshold'),116 currency: text('currency'),117 params: jsonObject<Record<string, unknown>>('params'),118 channel: text('channel').notNull().default('inapp'), // inapp | email119 active: boolean('active').notNull().default(true),120 lastTriggeredAt: ts('last_triggered_at'),121 createdAt: createdAt(),122 },123 (t) => [index('alerts_user_idx').on(t.userId), index('alerts_target_idx').on(t.targetType, t.targetId, t.active)],124);125126export const alertEvents = pgTable(127 'alert_events',128 {129 id: text('id').primaryKey(),130 alertId: text('alert_id').notNull(),131 userId: text('user_id').notNull(),132 message: text('message').notNull(),133 payload: jsonObject<Record<string, unknown>>('payload'),134 readAt: ts('read_at'),135 createdAt: createdAt(),136 },137 (t) => [index('alert_events_user_idx').on(t.userId, t.createdAt)],138);139140export const apiKeys = pgTable(141 'api_keys',142 {143 id: text('id').primaryKey(),144 userId: text('user_id'),145 name: text('name').notNull(),146 prefix: text('prefix').notNull(),147 keyHash: text('key_hash').notNull().unique(),148 tier: text('tier').notNull().default('free'), // free | hobby | professional | research | enterprise149 rateLimitPerMinute: integer('rate_limit_per_minute').notNull().default(60),150 dailyQuota: integer('daily_quota').notNull().default(1000),151 createdAt: createdAt(),152 lastUsedAt: ts('last_used_at'),153 revokedAt: ts('revoked_at'),154 },155 (t) => [index('api_keys_user_idx').on(t.userId)],156);157158export const apiUsage = pgTable(159 'api_usage',160 {161 keyId: text('key_id').notNull(),162 date: date('date').notNull(),163 endpoint: text('endpoint').notNull(),164 count: integer('count').notNull().default(0),165 latencyMsAvg: real('latency_ms_avg'),166 },167 (t) => [primaryKey({ columns: [t.keyId, t.date, t.endpoint] })],168);169170export const assetViews = pgTable(171 'asset_views',172 {173 assetId: text('asset_id').notNull(),174 date: date('date').notNull(),175 views: integer('views').notNull().default(0),176 },177 (t) => [primaryKey({ columns: [t.assetId, t.date] })],178);179180export const searchLog = pgTable(181 'search_log',182 {183 id: text('id').primaryKey(),184 query: text('query').notNull(),185 normalized: text('normalized').notNull(),186 results: integer('results').notNull(),187 userId: text('user_id'),188 createdAt: createdAt(),189 },190 (t) => [index('search_log_created_idx').on(t.createdAt), index('search_log_normalized_idx').on(t.normalized)],191);192