Revalue a lease portfolio

git clone https://github.com/cardog-ai/example-portfolio-revaluation.git

# Copy to .env.local. With these values the app runs on recorded fixtures
# and a development auth secret.

# Your Cardog API key (https://cardog.app/account/api). Server-side only; it
# never reaches the browser. Leave empty for fixture mode.
CARDOG_API_KEY=

# Signs sessions. This value is a DEVELOPMENT PLACEHOLDER so a fresh clone
# builds and runs; the app warns on every production start while it is set.
# Replace it before deploying: openssl rand -base64 32
BETTER_AUTH_SECRET=orvanne-portfolio-dev-only-secret-replace-before-deploying

# Where the app is served. Needed when it is not http://localhost:3027.
BETTER_AUTH_URL=http://localhost:3027

# SQLite file. Created on first run.
DATABASE_PATH=data/orvanne.db

# Seeded review logins (created by `pnpm db:setup` on an empty database) are
# listed in the README.

pnpm create next-app@latest orvanne-portfolio --ts --app --tailwind --src-dir
cd orvanne-portfolio
pnpm add @cardog/api better-auth better-sqlite3 drizzle-orm server-only
pnpm add -D drizzle-kit tsx @types/better-sqlite3

import type { NextConfig } from "next";

const nextConfig: NextConfig = {
  // better-sqlite3 is a native module: load it from node_modules at runtime.
  serverExternalPackages: ["better-sqlite3"],
  // This app is its own root, even when it sits inside another repository.
  outputFileTracingRoot: process.cwd(),
};

export default nextConfig;

function authSecret(): string {
  const set = process.env.BETTER_AUTH_SECRET?.trim();
  const production = process.env.NODE_ENV === "production" && process.env.NEXT_PHASE !== "phase-production-build";
  if (set) {
    if (production && set === DEV_SECRET) {
      console.warn("[orvanne-portfolio] BETTER_AUTH_SECRET is the development placeholder. Replace it before deploying.");
    }
    return set;
  }
  if (!production) return DEV_SECRET;
  throw new Error("[orvanne-portfolio] BETTER_AUTH_SECRET is not set. Generate one with `openssl rand -base64 32`.");
}

export const auth = betterAuth({
  appName: "Orvanne Credit",
  baseURL: process.env.BETTER_AUTH_URL || "http://localhost:3027",
  secret: authSecret(),
  database: drizzleAdapter(db, {
    provider: "sqlite",
    schema: { user: schema.user, session: schema.session, account: schema.account, verification: schema.verification },
  }),
  emailAndPassword: { enabled: true, minPasswordLength: 8, autoSignIn: true },
  user: {
    additionalFields: {
      role: { type: "string", required: false, defaultValue: "lessee", input: false },
    },
  },
  plugins: [nextCookies()],
});

import { toNextJsHandler } from "better-auth/next-js";
import { auth } from "@/lib/auth";

export const { GET, POST } = toNextJsHandler(auth);

const apiKey = process.env.CARDOG_API_KEY?.trim();

/** The one Cardog client. Server-side only. */
export const cardog: CardogClient | null = apiKey
  ? new CardogClient({ apiKey, appId: "orvanne-portfolio", baseUrl: process.env.CARDOG_BASE_URL || undefined })
  : null;

export const dataMode: "live" | "fixture" = cardog ? "live" : "fixture";

// The v2 response types, read off the client's own method signatures.
type V2 = CardogClient["v2"];
export type VinBatch = Awaited<ReturnType<V2["vin"]["batch"]>>;
export type VinBatchItem = VinBatch["results"][number];
export type VinIdentity = Extract<VinBatchItem, { ok: true }>["identity"];
export type MultiQuote = Awaited<ReturnType<V2["quotes"]["getMany"]>>;
export type Quote = MultiQuote["quotes"][number];
export type ModelYearRecalls = Awaited<ReturnType<V2["recalls"]["entity"]>>;
export type Recall = ModelYearRecalls["recalls"][number];

export interface Result<T> {
  data: T | null;
  call: CallRecord;
  error: string | null;
}

export function parsePortfolio(text: string): ParsedFile {
  const table = splitCsv(text);
  if (table.length === 0) return { rows: [], rejected: [], missingColumns: [...REQUIRED] };
  const header = table[0].map(norm);
  const col = {} as Record<keyof typeof COLUMNS, number>;
  for (const [key, names] of Object.entries(COLUMNS) as [keyof typeof COLUMNS, readonly string[]][]) {
    col[key] = header.findIndex((h) => names.map(norm).includes(h));
  }
  const missingColumns = REQUIRED.filter((k) => col[k] < 0);
  if (missingColumns.length) return { rows: [], rejected: [], missingColumns };

  const rows: LeaseRow[] = [];
  const rejected: ParsedFile["rejected"] = [];
  const seen = new Set<string>();
  table.slice(1).forEach((cells, i) => {
    const line = i + 2;
    const get = (k: keyof typeof COLUMNS) => (col[k] >= 0 ? (cells[col[k]] ?? "").trim() : "");
    const vin = normalizeVin(get("vin"));
    const v = checkVin(vin);
    // A check-digit mismatch is legitimate on some imports, so the decoder
    // decides; a wrong length or character never reaches the API.
    if (v.state !== "ok" && v.state !== "check-mismatch") {
      const why = v.state === "typing" ? `VIN is ${vin.length} characters` : "message" in v ? v.message : "VIN is empty";
      return rejected.push({ line, reason: `${get("vin") || "(no VIN)"}: ${why}` });
    }
    const residual = money(get("residual"));
    if (residual === null) return rejected.push({ line, reason: `${vin}: contract residual "${get("residual")}" is not an amount` });
    const maturity = isoDate(get("maturity"));
    if (!maturity) return rejected.push({ line, reason: `${vin}: maturity "${get("maturity")}" is not a YYYY-MM-DD date` });
    const kmRaw = get("km").replace(/[,\s]/g, "");
    const km = kmRaw === "" ? null : Number(kmRaw);
    if (km !== null && (!Number.isFinite(km) || km < 0)) return rejected.push({ line, reason: `${vin}: km "${get("km")}" is not a number` });
    const contractNo = get("contractNo") || `VIN-${vin}`;
    if (seen.has(contractNo)) return rejected.push({ line, reason: `${contractNo}: appears twice in the file` });
    seen.add(contractNo);
    const email = get("lesseeEmail").toLowerCase();
    rows.push({
      line,
      contractNo,
      vin,
      residual,
      maturity,
      km: km === null ? null : Math.round(km),
      lesseeName: get("lesseeName") || null,
      lesseeEmail: email.includes("@") ? email : null,
    });
  });
  return { rows, rejected, missingColumns: [] };
}

export function decodeBatch(vins: string[], step: string) {
  return run<VinBatch>({
    step,
    method: "cardog.v2.vin.batch",
    args: [vins.length > 3 ? [...vins.slice(0, 3), `… ${vins.length - 3} more`] : vins],
    http: "POST /v2/vin/batch",
    returns: `One identity row per VIN, in request order (${vins.length} sent)`,
    fixture: () => fixtureBatch(vins),
    live: (c) => c.v2.vin.batch(vins),
  });
}

export async function importPortfolio(text: string, fileName: string, userId: string): Promise<ImportOutcome> {
  const parsed = parsePortfolio(text);
  if (parsed.missingColumns.length) {
    return { importId: null, calls: [], error: `The file has no ${parsed.missingColumns.join(", ")} column.` };
  }
  if (parsed.rows.length === 0) {
    return { importId: null, calls: [], error: "No row in the file could be imported." };
  }

  const existing = new Map(
    db
      .select()
      .from(unit)
      .where(inArray(unit.contractNo, parsed.rows.map((r) => r.contractNo)))
      .all()
      .map((u) => [u.contractNo, u])
  );

  // Decode only what the book has not already decoded.
  const toDecode = [
    ...new Set(parsed.rows.filter((r) => existing.get(r.contractNo)?.vin !== r.vin || !existing.get(r.contractNo)?.decodeOk).map((r) => r.vin)),
  ];
  const calls: CallRecord[] = [];
  const decoded = new Map<string, { identity: VinIdentity } | { error: string }>();
  for (let i = 0; i < toDecode.length; i += BATCH_MAX) {
    const chunk = toDecode.slice(i, i + BATCH_MAX);
    const r = await decodeBatch(chunk, "Import");
    calls.push(r.call);
    if (!r.data) return { importId: null, calls, error: `The decode call failed: ${r.error}` };
    for (const row of r.data.results) {
      decoded.set(row.vin, row.ok ? { identity: row.identity } : { error: row.error.message });
    }
  }

  const importId = crypto.randomUUID();
  let added = 0;
  let updated = 0;
  let ok = 0;
  let failed = 0;
  db.transaction((tx) => {
    tx.insert(portfolioImport)
      .values({
        id: importId,
        fileName,
        importedBy: userId,
        importedAt: new Date(),
        rows: parsed.rows.length + parsed.rejected.length,
        added: 0,
        updated: 0,
        decoded: 0,
        failed: 0,
        rejected: parsed.rejected,
        calls,
      })
      .run();
    for (const r of parsed.rows) {
      const prior = existing.get(r.contractNo);
      const d = decoded.get(r.vin);
      const decode =
        d && "identity" in d
          ? identityColumns(d.identity)
          : d
            ? { ...EMPTY_DECODE, decodeError: d.error }
            : null; // unchanged VIN, already decoded: keep what is there
      if (decode) decode.decodeOk ? ok++ : failed++;
      const terms = {
        vin: r.vin,
        residual: r.residual,
        maturity: r.maturity,
        km: r.km,
        lesseeName: r.lesseeName,
        lesseeEmail: r.lesseeEmail,
        importId,
        updatedAt: new Date(),
      };
      if (prior) {
        tx.update(unit).set({ ...terms, ...(decode ?? {}) }).where(eq(unit.id, prior.id)).run();
        updated++;
      } else {
        tx.insert(unit)
          .values({ id: crypto.randomUUID(), contractNo: r.contractNo, ...terms, ...(decode ?? EMPTY_DECODE) })
          .run();
        added++;
      }
    }
    tx.update(portfolioImport).set({ added, updated, decoded: ok, failed }).where(eq(portfolioImport.id, importId)).run();
  });
  return { importId, calls, error: null };
}

export function quoteMany(refs: string[], step: string) {
  return run<MultiQuote>({
    step,
    method: "cardog.v2.quotes.getMany",
    args: [refs],
    http: `GET /v2/quotes?refs=${refs.join(",")}`,
    returns: "Quote lines in request order: liveCount, priceP25, priceMedian, priceP75, asOf",
    fixture: () => fixtureQuotes(refs),
    live: (c) => c.v2.quotes.getMany(refs),
  });
}

export async function revaluePortfolio(userId: string | null): Promise<RunOutcome> {
  const units = db.select().from(unit).where(eq(unit.decodeOk, true)).all();
  if (units.length === 0) return { runId: null, calls: [], error: "No decoded units to revalue. Import a file first." };

  const refs = [...new Set(units.map((u) => u.modelYearRef!).filter(Boolean))];
  const calls: CallRecord[] = [];
  const book = new Map<string, Quote>();
  for (let i = 0; i < refs.length; i += QUOTES_MAX) {
    const r = await quoteMany(refs.slice(i, i + QUOTES_MAX), "Revalue");
    calls.push(r.call);
    if (!r.data) return { runId: null, calls, error: `The quote call failed: ${r.error}` };
    for (const q of r.data.quotes) book.set(q.ref, q);
  }

  for (const ref of refs) {
    const r = await readModelYearRecalls(ref, "Recalls issued");
    calls.push(r.call);
    if (r.data) {
      const row = { ref, total: r.data.total, recalls: r.data.recalls, fetchedAt: new Date() };
      db.insert(modelYearRecalls).values(row).onConflictDoUpdate({ target: modelYearRecalls.ref, set: row }).run();
    }
  }

  const runId = crypto.randomUUID();
  const marks = units.map((u) => {
    const q = book.get(u.modelYearRef!);
    const median = q?.priceMedian ?? null;
    return {
      id: crypto.randomUUID(),
      runId,
      unitId: u.id,
      ref: u.modelYearRef!,
      residual: u.residual,
      median,
      p25: q?.priceP25 ?? null,
      p75: q?.priceP75 ?? null,
      liveCount: q?.liveCount ?? null,
      asOf: q?.asOf ?? null,
      gap: median === null ? null : median - u.residual,
    };
  });
  const valued = marks.filter((m) => m.gap !== null);
  db.transaction((tx) => {
    tx.insert(revaluationRun)
      .values({
        id: runId,
        runAt: new Date(),
        runBy: userId,
        source: dataMode,
        units: units.length,
        valued: valued.length,
        totalResidual: valued.reduce((s, m) => s + m.residual, 0),
        totalMarket: valued.reduce((s, m) => s + m.median!, 0),
        netGap: valued.reduce((s, m) => s + m.gap!, 0),
        calls,
      })
      .run();
    for (const m of marks) tx.insert(valuation).values(m).run();
  });
  return { runId, calls, error: null };
}

export function book(opts: { runId?: string; lesseeEmail?: string } = {}): { units: BookUnit[]; current: ReturnType<typeof runs>[number] | null; previous: ReturnType<typeof runs>[number] | null } {
  const all = runs();
  const i = opts.runId ? all.findIndex((r) => r.id === opts.runId) : 0;
  const current = all[i] ?? null;
  const previous = i >= 0 ? (all[i + 1] ?? null) : null;
  const cur = marksFor(current?.id);
  const prev = marksFor(previous?.id);
  const rows = opts.lesseeEmail
    ? db.select().from(unit).where(eq(unit.lesseeEmail, opts.lesseeEmail.toLowerCase())).all()
    : db.select().from(unit).all();
  const units = rows
    .map((u): BookUnit => {
      const c = cur.get(u.id) ?? null;
      return {
        id: u.id,
        contractNo: u.contractNo,
        vin: u.vin,
        lesseeName: u.lesseeName,
        lesseeEmail: u.lesseeEmail,
        residual: u.residual,
        maturity: u.maturity,
        km: u.km,
        decodeOk: u.decodeOk,
        decodeError: u.decodeError,
        year: u.year,
        make: u.make,
        model: u.model,
        trim: u.trim,
        modelYearRef: u.modelYearRef,
        current: c && { ...c, gap: c.median == null ? null : c.median - u.residual },
        previous: prev.get(u.id) ?? null,
      };
    })
    .sort((a, b) => a.maturity.localeCompare(b.maturity) || a.contractNo.localeCompare(b.contractNo));
  return { units, current, previous };
}

export function driftOf(u: BookUnit): number | null {
  const a = u.current?.median;
  const b = u.previous?.median;
  return a != null && b != null ? a - b : null;
}

export function totals(units: BookUnit[]): Totals {
  const t: Totals = { units: units.length, valued: 0, residual: 0, market: 0, gap: 0, over: 0, under: 0, exposure: 0, drift: null };
  let drift = 0;
  let drifted = 0;
  for (const u of units) {
    const g = gapOf(u);
    if (g === null || u.current?.median == null) continue;
    t.valued++;
    t.residual += u.residual;
    t.market += u.current.median;
    t.gap += g;
    if (g >= 0) t.over++;
    else {
      t.under++;
      t.exposure += g;
    }
    const d = driftOf(u);
    if (d !== null) {
      drift += d;
      drifted++;
    }
  }
  t.drift = drifted ? drift : null;
  return t;
}

export function rollup(units: BookUnit[], by: "make" | "modelYear" | "maturityMonth"): Rollup[] {
  const groups = new Map<string, { label: string; units: BookUnit[] }>();
  for (const u of units) {
    const [key, label] =
      by === "make"
        ? [u.make ?? "Not decoded", u.make ?? "Not decoded"]
        : by === "modelYear"
          ? [u.year ? String(u.year) : "—", u.year ? String(u.year) : "Not decoded"]
          : [u.maturity.slice(0, 7), monthLabel(u.maturity.slice(0, 7))];
    const g = groups.get(key) ?? { label, units: [] };
    g.units.push(u);
    groups.set(key, g);
  }
  const rows = [...groups.entries()].map(([key, g]) => ({ key, label: g.label, ...totals(g.units) }));
  return by === "make" ? rows.sort((a, b) => a.gap - b.gap || a.label.localeCompare(b.label)) : rows.sort((a, b) => a.key.localeCompare(b.key));
}

export function calendar(units: BookUnit[], from: Date) {
  const start = `${from.getFullYear()}-${String(from.getMonth() + 1).padStart(2, "0")}`;
  const last = units.reduce((m, u) => (u.maturity.slice(0, 7) > m ? u.maturity.slice(0, 7) : m), start);
  const months: { key: string; label: string; year: number; units: BookUnit[]; past: boolean }[] = [];
  // Whole calendar years, so every year reads as twelve cells.
  const earliest = units.reduce((m, u) => (u.maturity.slice(0, 7) < m ? u.maturity.slice(0, 7) : m), start);
  let y = Number(earliest.slice(0, 4));
  let mo = 1;
  const end = `${last.slice(0, 4)}-12`;
  for (let i = 0; i < 120; i++) {
    const key = `${y}-${String(mo).padStart(2, "0")}`;
    months.push({
      key,
      label: monthLabel(key, "short"),
      year: y,
      units: units.filter((u) => u.maturity.startsWith(key)).sort((a, b) => a.maturity.localeCompare(b.maturity)),
      past: key < start,
    });
    if (key >= end) break;
    mo++;
    if (mo > 12) {
      mo = 1;
      y++;
    }
  }
  return months;
}

export function readModelYearRecalls(ref: string, step: string) {
  return run<ModelYearRecalls>({
    step,
    method: "cardog.v2.recalls.entity",
    args: [ref, { limit: 25 }],
    http: `GET /v2/recalls/entity/${ref}?limit=25`,
    returns: "Recall campaigns issued for the model year, newest first, and the total",
    fixture: () => fixtureRecalls(ref),
    live: (c) => c.v2.recalls.entity(ref, { limit: 25 }),
  });
}

export function recallsFor(refs: string[]) {
  if (refs.length === 0) return new Map<string, { total: number; recalls: Recall[]; fetchedAt: Date }>();
  return new Map(
    db
      .select()
      .from(modelYearRecalls)
      .where(inArray(modelYearRecalls.ref, refs))
      .all()
      .map((r) => [r.ref, { total: r.total, recalls: r.recalls as Recall[], fetchedAt: r.fetchedAt }])
  );
}

# Orvanne portfolio: one container, SQLite on a mounted volume.
#   docker build -t orvanne-portfolio .
#   docker run -p 3027:3027 -v orvanne-data:/app/data \
#     -e CARDOG_API_KEY=... -e BETTER_AUTH_SECRET=... -e BETTER_AUTH_URL=https://your.host orvanne-portfolio
FROM node:22-bookworm-slim AS build
WORKDIR /app
RUN corepack enable
COPY package.json pnpm-lock.yaml ./
RUN pnpm install --frozen-lockfile
COPY . .
RUN pnpm build

FROM node:22-bookworm-slim
WORKDIR /app
ENV NODE_ENV=production
RUN corepack enable
COPY --from=build /app ./
VOLUME /app/data
EXPOSE 3027
CMD ["pnpm", "start"]

docker build -t orvanne-portfolio .
docker run -p 3027:3027 -v orvanne-portfolio-data:/app/data \
  -e CARDOG_API_KEY=your_cardog_api_key \
  -e BETTER_AUTH_SECRET=$(openssl rand -base64 32) \
  -e BETTER_AUTH_URL=https://portfolio.example.com \
  orvanne-portfolio