spb/rareindex

1/**2 * One-off / periodic repair (§84, §176): sales and listings whose grader was read but whose grade was3 * not (or that carry a grader token in the title the connector missed) were attached to the RAW4 * variant of their asset, polluting raw valuations and making graded asks look 80× "above RIV".5 *6 * `regradeRecords` re-parses the raw title with the current parser, moves the row to the proper7 * variant (creating it if needed; "grader · grade unknown" when the grade still cannot be read) and8 * marks the touched assets for revaluation by bumping `updated_at`/`created_at` watermarks the9 * valuation scheduler already watches. Nothing is deleted; every move is audited.10 */11import { and, eq, inArray, isNotNull, isNull, or, sql } from 'drizzle-orm';12import { assetVariants, listings, sales } from '@rareindex/database';13import { parseGradeFromTitle } from '@rareindex/taxonomy';14import { logger } from '@rareindex/shared';15import { db } from '../lib/db.ts';16import { auditMany } from '../lib/audit.ts';17import { ensureVariant } from './resolver.ts';1819const log = logger.child({ component: 'regrade' });2021export interface RegradeResult {22  scanned: number;23  moved: number;24  unknownGrade: number;25  unchanged: number;26  assets: number;27}2829interface Row {30  id: string;31  assetId: string;32  variantId: string | null;33  grader: string | null;34  grade: string | null;35  rawTitle: string;36  variantKey: string | null;37  vGrader: string | null;38  vGrade: string | null;39  condition: string | null;40  completeness: string | null;41  sizeLabel: string | null;42}4344async function candidates(kind: 'sale' | 'listing', limit: number, connectorId?: string): Promise<Row[]> {45  const t = kind === 'sale' ? sales : listings;46  const where = and(47    connectorId ? eq(t.connectorId, connectorId) : undefined,48    kind === 'listing' ? eq(listings.availability, 'available') : undefined,49    or(50      // grader known, grade unreadable → currently in the raw variant51      and(isNotNull(t.grader), isNull(t.grade)),52      // no grader recorded but the title carries a grader token (heuristic prefilter; parser decides)53      and(isNull(t.grader), sql`${t.rawTitle} ~* '\\m(PSA|BGS|BECKETT|CGC|SGC|CBCS|PGX|WATA|VGA|PCGS|NGC|ANACS|ICCS|PMG)\\M'`),54    ),55  );56  const rows = await db()57    .select({ id: t.id, assetId: t.assetId, variantId: t.variantId, grader: t.grader, grade: t.grade, rawTitle: t.rawTitle, variantKey: assetVariants.variantKey, vGrader: assetVariants.grader, vGrade: assetVariants.grade, condition: assetVariants.condition, completeness: assetVariants.completeness, sizeLabel: assetVariants.sizeLabel })58    .from(t)59    .leftJoin(assetVariants, eq(assetVariants.id, t.variantId))60    .where(where)61    .limit(limit);62  return rows as Row[];63}6465/** Rows touched by an earlier regrade pass (audit_log action = regraded), to re-verify with the current parser. */66async function regradedRows(kind: 'sale' | 'listing', limit: number): Promise<Row[]> {67  const t = kind === 'sale' ? sales : listings;68  const rows = await db()69    .select({ id: t.id, assetId: t.assetId, variantId: t.variantId, grader: t.grader, grade: t.grade, rawTitle: t.rawTitle, variantKey: assetVariants.variantKey, vGrader: assetVariants.grader, vGrade: assetVariants.grade, condition: assetVariants.condition, completeness: assetVariants.completeness, sizeLabel: assetVariants.sizeLabel })70    .from(t)71    .leftJoin(assetVariants, eq(assetVariants.id, t.variantId))72    .where(sql`${t.id} in (select entity_id from audit_log where action = 'regraded' and entity_type = ${kind})`)73    .limit(limit);74  return rows as Row[];75}7677export async function regradeRecords(opts: { kind?: 'sale' | 'listing' | 'both'; limit?: number; connectorId?: string; dryRun?: boolean; recheck?: boolean } = {}): Promise<RegradeResult> {78  const kinds: Array<'sale' | 'listing'> = opts.kind === 'sale' ? ['sale'] : opts.kind === 'listing' ? ['listing'] : ['sale', 'listing'];79  const limit = opts.limit ?? 50_000;80  const res: RegradeResult = { scanned: 0, moved: 0, unknownGrade: 0, unchanged: 0, assets: 0 };81  const touched = new Set<string>();82  for (const kind of kinds) {83    const rows = opts.recheck ? await regradedRows(kind, limit) : await candidates(kind, limit, opts.connectorId);84    res.scanned += rows.length;85    const audits: Array<{ entityType: string; entityId: string; action: string; reason: string; details: Record<string, unknown> }> = [];86    for (const r of rows) {87      const parsed = parseGradeFromTitle(r.rawTitle);88      const grader = r.grader && r.grader !== 'raw' ? r.grader : parsed.grader && parsed.grader !== 'raw' ? parsed.grader : null;89      if (!grader) {90        res.unchanged++;91        continue;92      }93      // grade recorded WITH a grader is trusted; a grade recorded without one is a condition value → title wins94      const titleGrade = parsed.grader === grader ? parsed.grade : null;95      const grade = opts.recheck ? (titleGrade ?? r.grade) : r.grader && r.grader !== 'raw' ? (r.grade ?? titleGrade) : titleGrade;96      const target = await ensureVariant(r.assetId, { grader, grade, qualifier: parsed.qualifier, certificationNumber: null }, { condition: r.condition, completeness: r.completeness }, r.sizeLabel);97      if (target.id === r.variantId && r.grader === grader && r.grade === grade) {98        res.unchanged++;99        continue;100      }101      if (!grade) res.unknownGrade++;102      res.moved++;103      touched.add(r.assetId);104      if (opts.dryRun) continue;105      const t = kind === 'sale' ? sales : listings;106      await db().update(t).set({ variantId: target.id, grader, grade, ...(kind === 'listing' ? { discountToRiv: null } : {}) }).where(eq(t.id, r.id));107      audits.push({ entityType: kind, entityId: r.id, action: 'regraded', reason: 'grade_reparsed_from_title', details: { from: r.variantKey, to: target.key, grader, grade, title: r.rawTitle } });108    }109    if (audits.length) await auditMany(audits);110    log.info({ kind, scanned: rows.length, moved: res.moved, dryRun: Boolean(opts.dryRun) }, 'regrade pass');111  }112  res.assets = touched.size;113  if (!opts.dryRun && touched.size) {114    // Make the valuation scheduler pick these assets up (it watches max(listings.updated_at) / sales.created_at).115    const ids = [...touched];116    for (let i = 0; i < ids.length; i += 1000) await db().update(listings).set({ updatedAt: new Date() }).where(and(inArray(listings.assetId, ids.slice(i, i + 1000)), eq(listings.availability, 'available')));117    // assets without an available listing: touch their stats watermark backwards so they re-qualify118    for (let i = 0; i < ids.length; i += 1000) await db().execute(sql`update asset_stats set updated_at = '1970-01-01' where asset_id in ${ids.slice(i, i + 1000)}`);119  }120  return res;121}122