spb/rareindex

1import { pgTable, text, integer, boolean, index, uniqueIndex, jsonb } from 'drizzle-orm/pg-core';2import { createdAt, ts, money, jsonObject, textArray } from './_common.js';34/**5 * Member-account tables (auth flows, security, personal tooling). Everything a member creates is6 * private by default (§176); public sharing is an explicit opt-in on the parent record.7 */89/** One-time codes: e-mail verification, MFA e-mail fallback, password reset, e-mail change, new device. */10export const authCodes = pgTable(11  'auth_codes',12  {13    id: text('id').primaryKey(),14    userId: text('user_id'),15    email: text('email').notNull(),16    purpose: text('purpose').notNull(), // verify_email | mfa_email | password_reset | change_email | new_device17    codeHash: text('code_hash').notNull(),18    expiresAt: ts('expires_at').notNull(),19    attempts: integer('attempts').notNull().default(0),20    consumedAt: ts('consumed_at'),21    /** arbitrary payload, e.g. { newEmail } for change_email */22    payload: jsonObject<Record<string, unknown>>('payload'),23    createdAt: createdAt(),24  },25  (t) => [index('auth_codes_lookup_idx').on(t.email, t.purpose, t.createdAt)],26);2728/** MFA recovery codes (hashed, single use). */29export const recoveryCodes = pgTable(30  'recovery_codes',31  {32    id: text('id').primaryKey(),33    userId: text('user_id').notNull(),34    codeHash: text('code_hash').notNull(),35    usedAt: ts('used_at'),36    createdAt: createdAt(),37  },38  (t) => [index('recovery_codes_user_idx').on(t.userId)],39);4041/** Devices that may skip the second factor for 30 days. */42export const trustedDevices = pgTable(43  'trusted_devices',44  {45    id: text('id').primaryKey(),46    userId: text('user_id').notNull(),47    tokenHash: text('token_hash').notNull(),48    label: text('label'),49    userAgent: text('user_agent'),50    ip: text('ip'),51    createdAt: createdAt(),52    lastUsedAt: ts('last_used_at'),53    expiresAt: ts('expires_at').notNull(),54    revokedAt: ts('revoked_at'),55  },56  (t) => [index('trusted_devices_user_idx').on(t.userId), uniqueIndex('trusted_devices_token_uq').on(t.tokenHash)],57);5859/** Login history shown in security settings. */60export const loginEvents = pgTable(61  'login_events',62  {63    id: text('id').primaryKey(),64    userId: text('user_id'),65    email: text('email'),66    ip: text('ip'),67    userAgent: text('user_agent'),68    outcome: text('outcome').notNull(), // success | bad_password | mfa_required | mfa_failed | locked | unknown_user69    method: text('method'), // password | totp | email_code | recovery_code | trusted_device70    createdAt: createdAt(),71  },72  (t) => [index('login_events_user_idx').on(t.userId, t.createdAt)],73);7475/** Fixed-window rate limit counters (works across PM2 instances). */76export const rateLimits = pgTable('rate_limits', {77  key: text('key').primaryKey(),78  count: integer('count').notNull().default(0),79  resetAt: ts('reset_at').notNull(),80});8182/** In-app notification inbox (alerts, security, digests, system). */83export const notifications = pgTable(84  'notifications',85  {86    id: text('id').primaryKey(),87    userId: text('user_id').notNull(),88    kind: text('kind').notNull(), // alert | security | system | digest | target_hit89    title: text('title').notNull(),90    body: text('body'),91    href: text('href'),92    payload: jsonObject<Record<string, unknown>>('payload'),93    readAt: ts('read_at'),94    emailedAt: ts('emailed_at'),95    createdAt: createdAt(),96  },97  (t) => [index('notifications_user_idx').on(t.userId, t.createdAt), index('notifications_unread_idx').on(t.userId, t.readAt)],98);99100/** Saved searches (Explore / Search filter URLs) — powers Deal Radar and digests. */101export const savedSearches = pgTable(102  'saved_searches',103  {104    id: text('id').primaryKey(),105    userId: text('user_id').notNull(),106    name: text('name').notNull(),107    url: text('url').notNull(),108    params: jsonObject<Record<string, unknown>>('params'),109    notify: boolean('notify').notNull().default(false),110    lastRunAt: ts('last_run_at'),111    lastCount: integer('last_count'),112    createdAt: createdAt(),113  },114  (t) => [index('saved_searches_user_idx').on(t.userId)],115);116117/** Personal price targets with progress tracking. */118export const priceTargets = pgTable(119  'price_targets',120  {121    id: text('id').primaryKey(),122    userId: text('user_id').notNull(),123    assetId: text('asset_id').notNull(),124    variantId: text('variant_id'),125    direction: text('direction').notNull().default('above'), // above | below126    targetUsd: money('target_usd').notNull(),127    /** RIV in USD when the target was set — used for progress */128    baselineUsd: money('baseline_usd'),129    note: text('note'),130    hitAt: ts('hit_at'),131    notifiedAt: ts('notified_at'),132    createdAt: createdAt(),133  },134  (t) => [index('price_targets_user_idx').on(t.userId), index('price_targets_asset_idx').on(t.assetId, t.hitAt)],135);136137/** Files uploaded by members (collection photos, avatars). Stored under RI_DATA_DIR/uploads. */138export const uploads = pgTable(139  'uploads',140  {141    id: text('id').primaryKey(),142    userId: text('user_id').notNull(),143    kind: text('kind').notNull(), // item_photo | avatar144    path: text('path').notNull(),145    mime: text('mime').notNull(),146    bytes: integer('bytes').notNull(),147    width: integer('width'),148    height: integer('height'),149    createdAt: createdAt(),150  },151  (t) => [index('uploads_user_idx').on(t.userId)],152);153154/** Badges are computed from data, never hand-assigned (no fake badges). Cached here for profile pages. */155export const userBadges = pgTable(156  'user_badges',157  {158    userId: text('user_id').notNull(),159    badge: text('badge').notNull(),160    evidence: jsonb('evidence').$type<Record<string, unknown>>().notNull().default({}),161    awardedAt: createdAt(),162  },163  (t) => [uniqueIndex('user_badges_uq').on(t.userId, t.badge)],164);165166export const collectionTags = textArray;167