# Pending schema changes — member accounts (agent F1)

Applied locally with `drizzle-kit push` on the `rareindex_account` database; **no migration file was
generated** (per instructions). Generate one migration after merging (`pnpm db:generate`).

## Modified tables (`packages/database/src/schema/users.ts`)

- `users`: + `handle text unique`, `avatar_url text`, `bio text`, `mfa_enabled boolean not null default false`,
  `totp_secret_enc text` (AES-256-GCM, key derived from `SESSION_SECRET`), `always_ask_code boolean not null default true`,
  `password_changed_at timestamptz`, `pending_email text`, `deleted_at timestamptz`, `purge_after timestamptz`.
- `sessions`: + `ip text`, `last_seen_at timestamptz`, `revoked_at timestamptz`.
- `collections`: + `kind text not null default 'collection'` (collection | wishlist | vault | sold), `budget_usd numeric(18,4)`, `color text`.
- `collection_items`: + `tags jsonb not null default '[]'`, `condition text`, `manual_value_usd numeric(18,4)`, `sold_at date`, `sold_price_usd numeric(18,4)`.
- `watchlist_items`: + `label text`, `note text`, `target_price_usd numeric(18,4)`, `baseline_usd numeric(18,4)`.
- `alerts`: + `name text`, `cooldown_minutes integer not null default 1440`, `trigger_count integer not null default 0`; `channel` now accepts `both`.

## New tables (`packages/database/src/schema/account.ts`, exported from `schema/index.ts`)

| table | purpose |
|---|---|
| `auth_codes` | hashed one-time codes: verify_email, mfa_email, password_reset, change_email, new_device (expiry, attempts, consumed_at, payload) |
| `recovery_codes` | 10 single-use MFA recovery codes per user (hashed) |
| `trusted_devices` | 30-day second-factor skip per browser (hashed token, label, ip, expiry, revoked_at) |
| `login_events` | sign-in history (outcome, method, ip, user agent) |
| `rate_limits` | fixed-window counters keyed by string (works across PM2 instances) |
| `notifications` | in-app inbox (kind, title, body, href, payload, read_at, emailed_at) |
| `saved_searches` | named search URLs, notify flag, last run/count |
| `price_targets` | per-user buy/sell RIV targets with baseline, hit_at, notified_at |
| `uploads` | member files (avatars, item photos) stored under `RI_DATA_DIR/uploads/<userId>/` |
| `user_badges` | data-derived badges with evidence (recomputed daily) |

## Notes for the merge

- `apps/web/src/lib/db.ts` now re-exports tables explicitly from the `schema` namespace (Turbopack could
  not enumerate `export *` through the packages' `.js`-suffixed re-exports) and exports `Database` type.
- `apps/web/next.config.ts`: `@rareindex/notify` added to `transpilePackages`; root `.env` loaded via
  `process.loadEnvFile`; `webpack.resolve.extensionAlias` so `next dev/build --webpack` resolves
  `./x.js` → `x.ts` inside workspace packages. **Turbopack cannot resolve those imports** — build with
  `next build --webpack` or drop the `.js` suffixes in packages (tsconfig `moduleResolution: bundler`).
- `packages/notify/src/index.ts` uses explicit named exports for the same reason.
- `workers/package.json`: + `@rareindex/database`, `@rareindex/notify`, `drizzle-orm`; `workers/tsconfig.json` added.