spb/rareindex

1import { sql } from 'drizzle-orm';2import type { Database } from '@rareindex/database';3import { userBadges } from '@rareindex/database';4import { logger } from '@rareindex/shared';56const log = logger.child({ job: 'account.badges' });78/** Recompute data-driven badges for every active member (no fake badges: each has stored evidence). */9export async function runBadges(db: Database): Promise<number> {10  const rows = (await db.execute(sql`11    with items as (12      select c.user_id, ci.id, a.category_slug, ci.certification_number, ci.acquired_at, ci.purchase_price_usd13      from collection_items ci join collections c on c.id = ci.collection_id join assets a on a.id = ci.asset_id14    ), watch as (15      select w.user_id, count(*)::int as n from watchlist_items wi join watchlists w on w.id = wi.watchlist_id group by w.user_id16    )17    select u.id, u.created_at, u.mfa_enabled,18           (select count(*)::int from items i where i.user_id = u.id) as item_count,19           (select count(distinct category_slug)::int from items i where i.user_id = u.id) as categories,20           (select count(*)::int from items i where i.user_id = u.id and i.certification_number is not null and i.certification_number <> '') as certified,21           (select count(*)::int from items i where i.user_id = u.id and (i.acquired_at is null or i.purchase_price_usd is null)) as undocumented,22           (select count(*)::int from collections c where c.user_id = u.id and c.is_public) as public_collections,23           coalesce((select n from watch where watch.user_id = u.id), 0) as watched24    from users u where u.deleted_at is null25  `)) as unknown as Array<{ id: string; created_at: Date; mfa_enabled: boolean; item_count: number; categories: number; certified: number; undocumented: number; public_collections: number; watched: number }>;26  let awarded = 0;27  for (const r of rows) {28    const badges: Array<[string, Record<string, unknown>]> = [];29    if (new Date(r.created_at) < new Date('2027-09-07')) badges.push(['early_member', { joined: new Date(r.created_at).toISOString().slice(0, 10) }]);30    if (r.categories >= 10) badges.push(['ten_categories', { categories: r.categories }]);31    if (r.item_count >= 100) badges.push(['hundred_items', { items: r.item_count }]);32    if (r.certified >= 10) badges.push(['graded_collector', { certified: r.certified }]);33    if (r.item_count >= 5 && r.undocumented === 0) badges.push(['documented', { items: r.item_count }]);34    if (r.public_collections >= 1) badges.push(['public_profile', { publicCollections: r.public_collections }]);35    if (r.watched >= 25) badges.push(['watcher', { watched: r.watched }]);36    if (r.mfa_enabled) badges.push(['two_factor', {}]);37    await db.delete(userBadges).where(sql`${userBadges.userId} = ${r.id}`);38    if (badges.length) {39      await db.insert(userBadges).values(badges.map(([badge, evidence]) => ({ userId: r.id, badge, evidence })));40      awarded += badges.length;41    }42  }43  log.info({ users: rows.length, awarded }, 'badges recomputed');44  return awarded;45}46