spb/rareindex

1import { eq, sql } from 'drizzle-orm';2import type { Database } from '@rareindex/database';3import { collections, collectionSnapshots } from '@rareindex/database';4import { logger } from '@rareindex/shared';56const log = logger.child({ job: 'account.snapshots' });78/**9 * Daily portfolio snapshot per collection: Σ value (variant RIV → asset RIV → manual value) and10 * Σ cost basis in USD. Idempotent for a given date (upsert).11 */12export async function runPortfolioSnapshots(db: Database, date = new Date().toISOString().slice(0, 10)): Promise<number> {13  const rows = (await db.execute(sql`14    select c.id as collection_id,15           coalesce(sum(coalesce(vs.riv_usd, s.riv_usd, ci.manual_value_usd, 0) * greatest(ci.quantity, 1)), 0)::float as value_usd,16           coalesce(sum(coalesce(ci.purchase_price_usd, 0) * greatest(ci.quantity, 1)), 0)::float as cost_basis_usd,17           count(ci.id)::int as items18    from collections c19    left join collection_items ci on ci.collection_id = c.id and ci.sold_at is null20    left join asset_stats s on s.asset_id = ci.asset_id21    left join variant_stats vs on vs.variant_id = ci.variant_id22    group by c.id23  `)) as unknown as Array<{ collection_id: string; value_usd: number; cost_basis_usd: number; items: number }>;24  let n = 0;25  for (const r of rows) {26    if (r.items === 0) continue;27    await db28      .insert(collectionSnapshots)29      .values({ collectionId: r.collection_id, date, valueUsd: Number(r.value_usd), costBasisUsd: Number(r.cost_basis_usd), items: r.items })30      .onConflictDoUpdate({ target: [collectionSnapshots.collectionId, collectionSnapshots.date], set: { valueUsd: Number(r.value_usd), costBasisUsd: Number(r.cost_basis_usd), items: r.items } });31    n++;32  }33  log.info({ collections: n, date }, 'portfolio snapshots written');34  return n;35}3637export async function snapshotOne(db: Database, collectionId: string): Promise<void> {38  const date = new Date().toISOString().slice(0, 10);39  const rows = (await db.execute(sql`40    select coalesce(sum(coalesce(vs.riv_usd, s.riv_usd, ci.manual_value_usd, 0) * greatest(ci.quantity, 1)), 0)::float as value_usd,41           coalesce(sum(coalesce(ci.purchase_price_usd, 0) * greatest(ci.quantity, 1)), 0)::float as cost_basis_usd,42           count(ci.id)::int as items43    from collection_items ci44    left join asset_stats s on s.asset_id = ci.asset_id45    left join variant_stats vs on vs.variant_id = ci.variant_id46    where ci.collection_id = ${collectionId} and ci.sold_at is null47  `)) as unknown as Array<{ value_usd: number; cost_basis_usd: number; items: number }>;48  const r = rows[0];49  if (!r || r.items === 0) return;50  await db51    .insert(collectionSnapshots)52    .values({ collectionId, date, valueUsd: Number(r.value_usd), costBasisUsd: Number(r.cost_basis_usd), items: r.items })53    .onConflictDoUpdate({ target: [collectionSnapshots.collectionId, collectionSnapshots.date], set: { valueUsd: Number(r.value_usd), costBasisUsd: Number(r.cost_basis_usd), items: r.items } });54  await db.update(collections).set({ updatedAt: new Date() }).where(eq(collections.id, collectionId));55}56