spb/rareindex

1import { pgTable, text, integer, real, index, uniqueIndex, jsonb, boolean } from 'drizzle-orm/pg-core';2import { createdAt, ts, jsonObject } from './_common.js';34/**5 * Scanner sessions (§114): what the user submitted, what the model guessed, which candidates we6 * showed and what the user picked. The pick is the feedback loop for identification quality.7 */8export const scannerSessions = pgTable(9  'scanner_sessions',10  {11    id: text('id').primaryKey(),12    userId: text('user_id'),13    anonId: text('anon_id'),14    ipHash: text('ip_hash'),15    mode: text('mode').notNull(), // photo | url | text16    inputUrl: text('input_url'),17    inputText: text('input_text'),18    imageCount: integer('image_count').notNull().default(0),19    /** stored thumbnails (data URLs, ≤ 3 × ~40 KB) for review; never the originals */20    thumbnails: jsonb('thumbnails').$type<string[]>().notNull().default([]),21    guess: jsonObject<Record<string, unknown>>('guess'),22    guessConfidence: real('guess_confidence'),23    candidates: jsonb('candidates').$type<Array<{ assetId: string; slug: string; title: string; score: number }>>().notNull().default([]),24    chosenAssetId: text('chosen_asset_id'),25    /** matched listing (url mode) */26    listing: jsonObject<Record<string, unknown>>('listing'),27    model: text('model'),28    usdEst: real('usd_est').notNull().default(0),29    durationMs: integer('duration_ms'),30    error: text('error'),31    createdAt: createdAt(),32    chosenAt: ts('chosen_at'),33  },34  (t) => [index('scanner_sessions_created_idx').on(t.createdAt), index('scanner_sessions_ip_idx').on(t.ipHash, t.createdAt), index('scanner_sessions_user_idx').on(t.userId)],35);3637/** AI Research conversations (§128). One session per anonymous cookie/user thread. */38export const researchSessions = pgTable(39  'research_sessions',40  {41    id: text('id').primaryKey(),42    userId: text('user_id'),43    anonId: text('anon_id'),44    title: text('title'),45    model: text('model'),46    messageCount: integer('message_count').notNull().default(0),47    usdEst: real('usd_est').notNull().default(0),48    archived: boolean('archived').notNull().default(false),49    createdAt: createdAt(),50    updatedAt: ts('updated_at').notNull().defaultNow(),51  },52  (t) => [index('research_sessions_anon_idx').on(t.anonId, t.updatedAt), index('research_sessions_user_idx').on(t.userId, t.updatedAt)],53);5455export const researchMessages = pgTable(56  'research_messages',57  {58    id: text('id').primaryKey(),59    sessionId: text('session_id').notNull(),60    role: text('role').notNull(), // user | assistant61    content: text('content').notNull(),62    /** transparent tool trace: [{name, input, ok, ms, rows}] */63    toolCalls: jsonb('tool_calls').$type<Array<{ name: string; input: unknown; ok: boolean; ms: number; summary?: string }>>().notNull().default([]),64    usage: jsonObject<Record<string, number>>('usage'),65    usdEst: real('usd_est').notNull().default(0),66    model: text('model'),67    createdAt: createdAt(),68  },69  (t) => [index('research_messages_session_idx').on(t.sessionId, t.createdAt)],70);7172/** Per-key daily quota for anonymous AI features (scanner/research) keyed by ip hash or user. */73export const aiQuotas = pgTable(74  'ai_quotas',75  {76    key: text('key').notNull(),77    feature: text('feature').notNull(),78    date: text('date').notNull(),79    count: integer('count').notNull().default(0),80  },81  (t) => [uniqueIndex('ai_quotas_uq').on(t.key, t.feature, t.date)],82);83