← back to Govarbitrage
scripts/send-digest.ts
236 lines
// Twice-daily "Top 10 Opportunities" email digest.
// Ranks still-open auctions by Overall Opportunity score and emails the top 10
// via George (the DW Gmail HTTP agent). Scheduled by launchd at 06:00 and 17:00.
//
// Env (from the project .env, loaded below):
// DATABASE_URL - Postgres (required)
// GEORGE_URL - George base URL (required to send)
// GEORGE_BASIC_AUTH - base64 "user:pass" for George Basic Auth (required)
// DIGEST_TO - recipient (default steve@designerwallcoverings.com)
// DIGEST_FROM_ACCOUNT - George account (default steve-office)
// APP_URL - base URL for listing links (default http://localhost:3737)
// DIGEST_DRY_RUN=1 - print the email instead of sending
//
// Run: npx tsx scripts/send-digest.ts
import { readFileSync } from "node:fs";
import { fileURLToPath } from "node:url";
import { dirname, join } from "node:path";
// --- minimal .env loader (don't rely on Prisma's dotenv side-effect) ----------
function loadEnv() {
const root = join(dirname(fileURLToPath(import.meta.url)), "..");
try {
const raw = readFileSync(join(root, ".env"), "utf8");
for (const line of raw.split("\n")) {
const m = line.match(/^\s*([A-Z0-9_]+)\s*=\s*(.*)\s*$/i);
if (!m) continue;
const key = m[1];
let val = m[2].trim();
if ((val.startsWith('"') && val.endsWith('"')) || (val.startsWith("'") && val.endsWith("'"))) {
val = val.slice(1, -1);
}
if (process.env[key] === undefined) process.env[key] = val;
}
} catch {
/* no .env — rely on ambient env */
}
}
loadEnv();
const { queryListings } = await import("../src/lib/listings");
const { prisma } = await import("../src/lib/db");
const { formatMoney, formatPercent, countdown } = await import("../src/lib/utils");
const DIGEST_TO = process.env.DIGEST_TO || "steve@designerwallcoverings.com";
const FROM_ACCOUNT = process.env.DIGEST_FROM_ACCOUNT || "steve-office";
const APP_URL = process.env.APP_URL || "http://localhost:3737";
const n = (d: unknown): number => (d == null ? 0 : Number(d));
type Detail = NonNullable<Awaited<ReturnType<typeof fetchDetails>>>[number];
/** Rank still-open auctions by Overall Opportunity and return the top-10 ids in order. */
async function topTenIds(): Promise<string[]> {
const { rows } = await queryListings({
profile: "OVERALL_OPPORTUNITY",
sort: "opportunityScore",
dir: "desc",
pageSize: 200,
});
const now = Date.now();
return rows
.filter((r) => r.closingAt && new Date(r.closingAt).getTime() > now)
.slice(0, 10)
.map((r) => r.id);
}
/** Fetch full research + cost + scores for the given ids, preserving order. */
async function fetchDetails(ids: string[]) {
const listings = await prisma.listing.findMany({
where: { id: { in: ids } },
include: { research: true, costBreakdown: true, scores: true },
});
const byId = new Map(listings.map((l) => [l.id, l]));
return ids.map((id) => byId.get(id)).filter((x): x is NonNullable<typeof x> => !!x);
}
function kv(label: string, value: string, strong = false): string {
return `<tr>
<td style="padding:2px 6px 2px 0;color:#64748b">${label}</td>
<td style="padding:2px 0;text-align:right;font-variant-numeric:tabular-nums${strong ? ";font-weight:700" : ""}">${value}</td>
</tr>`;
}
function valuationTable(r: NonNullable<Detail["research"]>): string {
return `<table style="border-collapse:collapse;font-size:12px;width:100%">
${kv("New Retail", formatMoney(n(r.newRetail)))}
${kv("New Replacement", formatMoney(n(r.newReplacement)))}
${kv("Avg Retail", formatMoney(n(r.avgRetail)))}
${kv("Used Low / Avg / High", `${formatMoney(n(r.usedLow))} / ${formatMoney(n(r.usedSoldPrice))} / ${formatMoney(n(r.usedHigh))}`)}
${kv("Used Asking", formatMoney(n(r.usedAskingPrice)))}
${kv("Wholesale", formatMoney(n(r.wholesaleValue)))}
${kv("Liquidation", formatMoney(n(r.liquidationValue)))}
${kv("Sell Today", formatMoney(n(r.sellTodayValue)))}
${kv("7 / 30 / 90-Day", `${formatMoney(n(r.value7Day))} / ${formatMoney(n(r.value30Day))} / ${formatMoney(n(r.value90Day))}`)}
${kv("Expected Sale", formatMoney(n(r.expectedSalePrice)), true)}
${kv("Prob. of Sale", formatPercent(n(r.probabilityOfSale)))}
${kv("Days to Sell", `${r.daysUntilSold ?? "—"}`)}
${kv("Confidence", `${Math.round(n(r.confidenceScore))}/100`)}
</table>`;
}
function costTable(c: NonNullable<Detail["costBreakdown"]>): string {
return `<table style="border-collapse:collapse;font-size:12px;width:100%">
${kv("Winning Bid", formatMoney(n(c.winningBid)))}
${kv("Buyer Premium", formatMoney(n(c.buyerPremium)))}
${kv("Sales Tax", formatMoney(n(c.salesTax)))}
${kv("Shipping / Freight", `${formatMoney(n(c.shipping))} / ${formatMoney(n(c.freight))}`)}
${kv("Insurance / Packing", `${formatMoney(n(c.insurance))} / ${formatMoney(n(c.packing))}`)}
${kv("Pickup / Testing", `${formatMoney(n(c.pickupLabor))} / ${formatMoney(n(c.testing))}`)}
${kv("Repairs / Cert.", `${formatMoney(n(c.repairs))} / ${formatMoney(n(c.certification))}`)}
${kv("Mkt / Payment Fees", `${formatMoney(n(c.marketplaceFees))} / ${formatMoney(n(c.paymentFees))}`)}
${kv("Storage / Photo / List", `${formatMoney(n(c.storage))} / ${formatMoney(n(c.photography))} / ${formatMoney(n(c.listingLabor))}`)}
${kv("Total Investment", formatMoney(n(c.totalInvestment)), true)}
${kv("Expected Returns", formatMoney(n(c.expectedReturns)))}
${kv("Expected Net Profit", formatMoney(n(c.expectedNetProfit)), true)}
${kv("ROI / Annualized", `${formatPercent(n(c.roi))} / ${formatPercent(n(c.annualizedReturn))}`, true)}
${kv("Recommended Max Bid", formatMoney(n(c.recommendedMaxBid)), true)}
</table>`;
}
function scoreTable(scores: Detail["scores"]): string {
const overall = scores.find((s) => s.profile === "OVERALL_OPPORTUNITY") ?? scores[0];
if (!overall) return "";
const chip = (label: string, v: number) =>
kv(label, `${Math.round(v)}`);
return `<table style="border-collapse:collapse;font-size:12px;width:100%">
${kv("Opportunity", `${Math.round(overall.value)}`, true)}
${chip("Arbitrage", overall.arbitrage)}
${chip("Demand", overall.demand)}
${chip("Velocity", overall.velocity)}
${chip("Logistics", overall.logistics)}
${chip("Condition", overall.condition)}
${chip("Competition", overall.competition)}
${chip("Buyer", overall.buyer)}
${kv("Risk", overall.risk)}
${kv("Drop Ship", overall.dropShip)}
</table>
<div style="font-size:11px;color:#64748b;margin-top:6px">${escapeHtml(overall.explanation)}</div>`;
}
function renderItem(l: Detail, rank: number): string {
const closes = countdown(l.closingAt);
const net = formatMoney(n(l.costBreakdown?.expectedNetProfit));
const roi = formatPercent(n(l.costBreakdown?.roi));
return `<div style="border:1px solid #e2e8f0;border-radius:8px;margin:0 0 16px;padding:12px">
<div style="font-size:15px;font-weight:700">#${rank} · <a href="${APP_URL}/listings/${l.id}" style="color:#2563eb;text-decoration:none">${escapeHtml(l.title)}</a></div>
<div style="color:#64748b;font-size:12px;margin:2px 0 10px">
${l.source.replace(/_/g, " ")} · #${escapeHtml(l.sourceAuctionId)} · ${escapeHtml(l.category || "—")} ·
Qty ${l.quantity} · ${l.condition.replace(/_/g, " ")} ·
${[l.locationCity, l.locationState].filter(Boolean).join(", ") || "—"} ·
closes ${closes} · <b style="color:#16a34a">${net} net</b> · <b>${roi} ROI</b>
</div>
<table style="width:100%;border-collapse:collapse"><tr valign="top">
<td style="width:34%;padding-right:12px">
<div style="font-weight:600;color:#334155;font-size:12px;margin-bottom:2px">Valuation</div>
${l.research ? valuationTable(l.research) : "<i>pending</i>"}
</td>
<td style="width:36%;padding-right:12px">
<div style="font-weight:600;color:#334155;font-size:12px;margin-bottom:2px">Cost Breakdown & Profit</div>
${l.costBreakdown ? costTable(l.costBreakdown) : "<i>pending</i>"}
</td>
<td style="width:30%">
<div style="font-weight:600;color:#334155;font-size:12px;margin-bottom:2px">Scores</div>
${scoreTable(l.scores)}
</td>
</tr></table>
</div>`;
}
function buildHtml(items: Detail[]): string {
const when = new Date().toLocaleString("en-US", { dateStyle: "medium", timeStyle: "short" });
if (items.length === 0) {
return `<p>No open auction opportunities right now (${when}).</p>`;
}
return `<div style="font-family:-apple-system,Segoe UI,Roboto,Helvetica,Arial,sans-serif;color:#0f172a;max-width:900px">
<h2 style="margin:0 0 4px">GovArbitrage — Top ${items.length} Opportunities</h2>
<div style="color:#64748b;font-size:13px;margin-bottom:14px">${when} · ranked by Overall Opportunity · still-open auctions · detailed valuation, costs & scores per item</div>
${items.map((l, i) => renderItem(l, i + 1)).join("")}
<p style="margin-top:8px"><a href="${APP_URL}" style="color:#2563eb">Open the dashboard →</a></p>
<p style="color:#94a3b8;font-size:11px">Recommended max bids back-solve to a 40% target ROI. Figures are estimates — verify comps before bidding.</p>
</div>`;
}
function escapeHtml(s: string): string {
return s.replace(/[&<>"']/g, (c) => ({ "&": "&", "<": "<", ">": ">", '"': """, "'": "'" }[c]!));
}
async function sendViaGeorge(subject: string, html: string) {
const base = process.env.GEORGE_URL;
const auth = process.env.GEORGE_BASIC_AUTH;
if (!base || !auth) throw new Error("GEORGE_URL / GEORGE_BASIC_AUTH not set");
const res = await fetch(`${base}/api/send`, {
method: "POST",
headers: { "Content-Type": "application/json", Authorization: `Basic ${auth}` },
body: JSON.stringify({ account: FROM_ACCOUNT, to: DIGEST_TO, subject, body: html }),
});
const text = await res.text();
if (!res.ok) throw new Error(`George send failed ${res.status}: ${text.slice(0, 300)}`);
return text;
}
async function main() {
const ids = await topTenIds();
const items = await fetchDetails(ids);
const hour = new Date().getHours();
const slot = hour < 12 ? "Morning" : "Evening";
const subject = `GovArbitrage — Top ${items.length} Opportunities (${slot})`;
const html = buildHtml(items);
if (process.env.DIGEST_DRY_RUN === "1") {
const { writeFileSync } = await import("node:fs");
writeFileSync("logs/digest-preview.html", html);
console.log(`[DRY RUN] to=${DIGEST_TO} subject="${subject}" (${items.length} items, ${html.length} bytes)`);
console.log(`Preview written to logs/digest-preview.html`);
items.forEach((l, i) =>
console.log(
` ${i + 1}. ${l.title.slice(0, 40)} — net ${Math.round(n(l.costBreakdown?.expectedNetProfit))}, ROI ${Math.round(n(l.costBreakdown?.roi) * 100)}%`,
),
);
await prisma.$disconnect();
return;
}
const result = await sendViaGeorge(subject, html);
console.log(`Sent "${subject}" to ${DIGEST_TO}: ${result.slice(0, 160)}`);
await prisma.$disconnect();
}
main()
.then(() => process.exit(0))
.catch((e) => {
console.error(`[digest] ${new Date().toISOString()} ERROR:`, e.message);
process.exit(1);
});