spb/rareindex

1import { eq, sql } from 'drizzle-orm';2import { certificateSightings, certificates } from '@rareindex/database';3import { getGrader } from '@rareindex/taxonomy';4import { deterministicId, newId } from '@rareindex/shared';5import { db } from '../lib/db.ts';67/**8 * Certification-number tracking (SPEC §22). Every sale/listing/lot carrying a slab cert number is9 * recorded as a sighting of one (grader, cert) row. Over time the same cert appearing on several10 * marketplaces builds a provenance graph; cert_lookup connectors fill `verification`.11 */12export interface CertSighting {13  grader: string;14  certNumber: string;15  assetId: string | null;16  variantId: string | null;17  grade: string | null;18  qualifier?: string | null;19  kind: 'sale' | 'listing' | 'auction_lot' | 'lookup';20  targetId: string | null;21  sourceId: string;22  connectorId: string | null;23  sourceUrl: string | null;24  priceUsd: number | null;25  price?: number | null;26  currency?: string | null;27  observedAt: Date;28}2930/** Normalise a raw cert string: digits and letters only, uppercase, no separators. Returns null when implausible. */31export function normalizeCertNumber(grader: string | null | undefined, raw: string | null | undefined): string | null {32  if (!raw) return null;33  const s = String(raw).trim().toUpperCase().replace(/^(CERT|CERT\.|#|NO\.?|№)\s*/i, '').replace(/[\s.\-_/]/g, '');34  if (!/^[A-Z0-9]{5,20}$/.test(s)) return null;35  // grader-specific sanity: PSA/SGC/CGC/BGS certs are numeric; PCGS/NGC are numeric (NGC may carry -NNN suffix already stripped)36  if (grader && /^(psa|sgc|cgc|bgs|pcgs|ngc|pmg|cbcs|tag|ace|wata|vga)$/.test(grader) && !/^\d{5,12}$/.test(s)) return null;37  return s;38}3940export function verifyUrlFor(grader: string, cert: string): string | null {41  const g = getGrader(grader);42  if (!g?.verifyUrl) return null;43  return g.verifyUrl.includes('{cert}') ? g.verifyUrl.replace('{cert}', encodeURIComponent(cert)) : g.verifyUrl;44}4546export async function recordCertificate(s: CertSighting): Promise<string | null> {47  const grader = s.grader.toLowerCase();48  const cert = normalizeCertNumber(grader, s.certNumber);49  if (!cert) return null;50  const id = deterministicId('cert', `${grader}|${cert}`);51  await db()52    .insert(certificates)53    .values({ id, grader, certNumber: cert, assetId: s.assetId, variantId: s.variantId, grade: s.grade, qualifier: s.qualifier ?? null, verifyUrl: verifyUrlFor(grader, cert), firstSeenAt: s.observedAt, lastSeenAt: s.observedAt, sightings: 1, sourceIds: [s.sourceId], lastSourceUrl: s.sourceUrl, lastPriceUsd: s.priceUsd })54    .onConflictDoUpdate({55      target: [certificates.grader, certificates.certNumber],56      set: {57        assetId: sql`coalesce(${certificates.assetId}, ${s.assetId})`,58        variantId: sql`coalesce(${certificates.variantId}, ${s.variantId})`,59        grade: sql`coalesce(${certificates.grade}, ${s.grade})`,60        firstSeenAt: sql`least(${certificates.firstSeenAt}, ${s.observedAt})`,61        lastSeenAt: sql`greatest(${certificates.lastSeenAt}, ${s.observedAt})`,62        sightings: sql`${certificates.sightings} + 1`,63        sourceIds: sql`(select array_agg(distinct x) from unnest(${certificates.sourceIds} || ${sql.raw(`'{${s.sourceId.replace(/[^a-z0-9_-]/gi, '')}}'::text[]`)}) x)`,64        lastSourceUrl: sql`case when ${s.observedAt} >= ${certificates.lastSeenAt} then ${s.sourceUrl} else ${certificates.lastSourceUrl} end`,65        lastPriceUsd: sql`case when ${s.priceUsd} is not null and ${s.observedAt} >= ${certificates.lastSeenAt} then ${s.priceUsd} else ${certificates.lastPriceUsd} end`,66      },67    });68  await db()69    .insert(certificateSightings)70    .values({ id: newId('event'), certificateId: id, kind: s.kind, targetId: s.targetId, sourceId: s.sourceId, connectorId: s.connectorId, sourceUrl: s.sourceUrl, priceUsd: s.priceUsd, currency: s.currency ?? null, price: s.price ?? null, observedAt: s.observedAt })71    .onConflictDoNothing();72  return id;73}7475/** Cert history for an asset or a cert id (provenance timeline). */76export async function certificateTimeline(certificateId: string) {77  return db().select().from(certificateSightings).where(eq(certificateSightings.certificateId, certificateId)).orderBy(certificateSightings.observedAt);78}79