← back to Japan Enrich
security: strip hardcoded secret -> env-first/passwordless. No rotation/deploy.
4f461fb7666953d6d1e41f5f74b8f3016d572c76 · 2026-09-13 00:01:06 -0700 · Steve
Files touched
A govarbitrage/src/lib/listings.tsA govarbitrage/src/lib/marketplace-links.test.tsA govarbitrage/src/lib/marketplace-links.tsA govarbitrage/src/lib/money-math-visibility.test.tsA govarbitrage/src/lib/newsletter.tsA govarbitrage/src/lib/password.tsA govarbitrage/src/lib/places.test.tsA govarbitrage/src/lib/places.tsA govarbitrage/src/lib/rate-limit.test.tsA govarbitrage/src/lib/rate-limit.tsA govarbitrage/src/lib/reports.tsA govarbitrage/src/lib/selling-avenues.tsA govarbitrage/src/lib/send-digest.tsA govarbitrage/src/lib/session.tsA govarbitrage/src/lib/site.tsA govarbitrage/src/lib/ssrf-guard.tsA govarbitrage/src/lib/tiers.tsA govarbitrage/src/lib/utils.tsA govarbitrage/src/middleware.test.tsA govarbitrage/src/middleware.tsA govarbitrage/src/pipeline/research.tsA govarbitrage/src/worker/index.tsA govarbitrage/tests/e2e/smoke.spec.tsA govarbitrage/vitest.config.tsA rentv-slideshow/capture-growth.mjsA rentv-slideshow/capture.mjsA send-projects-email.jsA test-enhanced-scraper.js
Diff
commit 4f461fb7666953d6d1e41f5f74b8f3016d572c76
Author: Steve <steve@designerwallcoverings.com>
Date: Sun Sep 13 00:01:06 2026 -0700
security: strip hardcoded secret -> env-first/passwordless. No rotation/deploy.
---
govarbitrage/src/lib/listings.ts | 254 +++++++++
govarbitrage/src/lib/marketplace-links.test.ts | 49 ++
govarbitrage/src/lib/marketplace-links.ts | 138 +++++
govarbitrage/src/lib/money-math-visibility.test.ts | 27 +
govarbitrage/src/lib/newsletter.ts | 141 +++++
govarbitrage/src/lib/password.ts | 21 +
govarbitrage/src/lib/places.test.ts | 538 +++++++++++++++++
govarbitrage/src/lib/places.ts | 634 +++++++++++++++++++++
govarbitrage/src/lib/rate-limit.test.ts | 32 ++
govarbitrage/src/lib/rate-limit.ts | 76 +++
govarbitrage/src/lib/reports.ts | 139 +++++
govarbitrage/src/lib/selling-avenues.ts | 168 ++++++
govarbitrage/src/lib/send-digest.ts | 88 +++
govarbitrage/src/lib/session.ts | 42 ++
govarbitrage/src/lib/site.ts | 9 +
govarbitrage/src/lib/ssrf-guard.ts | 104 ++++
govarbitrage/src/lib/tiers.ts | 103 ++++
govarbitrage/src/lib/utils.ts | 50 ++
govarbitrage/src/middleware.test.ts | 98 ++++
govarbitrage/src/middleware.ts | 142 +++++
govarbitrage/src/pipeline/research.ts | 257 +++++++++
govarbitrage/src/worker/index.ts | 66 +++
govarbitrage/tests/e2e/smoke.spec.ts | 38 ++
govarbitrage/vitest.config.ts | 15 +
rentv-slideshow/capture-growth.mjs | 42 ++
rentv-slideshow/capture.mjs | 63 ++
send-projects-email.js | 60 ++
test-enhanced-scraper.js | 127 +++++
28 files changed, 3521 insertions(+)
diff --git a/govarbitrage/src/lib/listings.ts b/govarbitrage/src/lib/listings.ts
new file mode 100644
index 0000000..c0d826b
--- /dev/null
+++ b/govarbitrage/src/lib/listings.ts
@@ -0,0 +1,254 @@
+import { prisma } from "@/lib/db";
+import type { Prisma } from "@prisma/client";
+
+// Flat row shape backing the dashboard table — every sortable column the spec
+// lists, denormalized from Listing + Research + CostBreakdown + the chosen Score.
+export interface ListingRow {
+ id: string;
+ source: string;
+ sourceAuctionId: string;
+ sourceUrl: string | null;
+ title: string;
+ category: string | null;
+ manufacturer: string | null;
+ model: string | null;
+ condition: string;
+ quantity: number;
+ currentBid: number;
+ currentCost: number; // bid + premium + tax (acquisition-in)
+ recommendedMaxBid: number;
+ retailLow: number | null;
+ retailAverage: number | null;
+ retailHigh: number | null;
+ usedLow: number | null;
+ usedAverage: number | null;
+ usedHigh: number | null;
+ wholesale: number | null;
+ liquidation: number | null;
+ sellNow: number | null;
+ value7Day: number | null;
+ value30Day: number | null;
+ value90Day: number | null;
+ expectedSale: number | null;
+ shipping: number;
+ freight: number;
+ repairs: number;
+ marketplaceFees: number;
+ netProfit: number;
+ roi: number;
+ risk: string;
+ confidence: number | null;
+ opportunityScore: number;
+ arbitrageScore: number;
+ demandScore: number;
+ velocityScore: number;
+ logisticsScore: number;
+ conditionScore: number;
+ competitionScore: number;
+ buyerScore: number;
+ dropShip: string;
+ closingAt: string | null;
+ researchStatus: string;
+ imageUrl: string | null;
+}
+
+type FullListing = Prisma.ListingGetPayload<{
+ include: { research: true; costBreakdown: true; scores: true };
+}>;
+
+const num = (d: Prisma.Decimal | number | null | undefined): number =>
+ d == null ? 0 : typeof d === "number" ? d : Number(d);
+const numN = (d: Prisma.Decimal | number | null | undefined): number | null =>
+ d == null ? null : typeof d === "number" ? d : Number(d);
+
+export function flattenListing(l: FullListing, profile = "OVERALL_OPPORTUNITY"): ListingRow {
+ const r = l.research;
+ const c = l.costBreakdown;
+ const s = l.scores.find((x) => x.profile === profile) ?? l.scores[0];
+ const currentCost = c ? num(c.winningBid) + num(c.buyerPremium) + num(c.salesTax) : num(l.currentBid);
+ return {
+ id: l.id,
+ source: l.source,
+ sourceAuctionId: l.sourceAuctionId,
+ sourceUrl: l.sourceUrl,
+ title: l.title,
+ category: l.category,
+ manufacturer: l.manufacturer,
+ model: l.model,
+ condition: l.condition,
+ quantity: l.quantity,
+ currentBid: num(l.currentBid),
+ currentCost,
+ recommendedMaxBid: num(c?.recommendedMaxBid),
+ retailLow: numN(r?.usedLow),
+ retailAverage: numN(r?.avgRetail),
+ retailHigh: numN(r?.newRetail),
+ usedLow: numN(r?.usedLow),
+ usedAverage: numN(r?.usedSoldPrice),
+ usedHigh: numN(r?.usedHigh),
+ wholesale: numN(r?.wholesaleValue),
+ liquidation: numN(r?.liquidationValue),
+ sellNow: numN(r?.sellTodayValue),
+ value7Day: numN(r?.value7Day),
+ value30Day: numN(r?.value30Day),
+ value90Day: numN(r?.value90Day),
+ expectedSale: numN(r?.expectedSalePrice),
+ shipping: num(c?.shipping),
+ freight: num(c?.freight),
+ repairs: num(c?.repairs),
+ marketplaceFees: num(c?.marketplaceFees),
+ netProfit: num(c?.expectedNetProfit),
+ roi: num(c?.roi),
+ risk: s?.risk ?? "MEDIUM",
+ confidence: numN(r?.confidenceScore),
+ opportunityScore: s?.value ?? 0,
+ arbitrageScore: s?.arbitrage ?? 0,
+ demandScore: s?.demand ?? 0,
+ velocityScore: s?.velocity ?? 0,
+ logisticsScore: s?.logistics ?? 0,
+ conditionScore: s?.condition ?? 0,
+ competitionScore: s?.competition ?? 0,
+ buyerScore: s?.buyer ?? 0,
+ dropShip: s?.dropShip ?? "MODERATE",
+ closingAt: l.closingAt ? l.closingAt.toISOString() : null,
+ researchStatus: l.researchStatus,
+ imageUrl: l.imageUrls[0] ?? null,
+ };
+}
+
+export interface QueryParams {
+ search?: string;
+ source?: string;
+ category?: string;
+ condition?: string;
+ risk?: string;
+ closingWithinHours?: number;
+ sort?: keyof ListingRow;
+ dir?: "asc" | "desc";
+ page?: number;
+ pageSize?: number;
+ profile?: string;
+ // Listing lifecycle filter. Defaults to ACTIVE so ended/removed (dead)
+ // listings never show on the live grid. Pass "ALL" to include everything.
+ status?: string;
+}
+
+// Listing scalar columns that map 1-to-1 to a DB field and can therefore be
+// pushed into Prisma's orderBy. Everything else (scores, risk, computed cost
+// fields) lives on joined relations and must be sorted in JS.
+const NATIVE_SORT_COLUMNS = new Set<keyof ListingRow>([
+ "currentBid",
+ "closingAt",
+ "title",
+ "source",
+ "sourceAuctionId",
+ "category",
+ "manufacturer",
+ "model",
+ "condition",
+ "quantity",
+ "researchStatus",
+]);
+
+const NULLABLE_NATIVE_SORT_COLUMNS = new Set<keyof ListingRow>([
+ "closingAt",
+ "category",
+ "manufacturer",
+ "model",
+]);
+
+export async function queryListings(params: QueryParams) {
+ const {
+ search,
+ source,
+ category,
+ condition,
+ risk,
+ closingWithinHours,
+ sort = "opportunityScore",
+ dir = "desc",
+ page = 1,
+ pageSize = 50,
+ profile = "OVERALL_OPPORTUNITY",
+ status = "ACTIVE",
+ } = params;
+
+ const where: Prisma.ListingWhereInput = {};
+ if (status && status !== "ALL") {
+ where.listingStatus = status as Prisma.ListingWhereInput["listingStatus"];
+ }
+ if (source) where.source = source as Prisma.ListingWhereInput["source"];
+ if (condition) where.condition = condition as Prisma.ListingWhereInput["condition"];
+ if (category) where.category = { contains: category, mode: "insensitive" };
+ if (search) {
+ where.OR = [
+ { title: { contains: search, mode: "insensitive" } },
+ { manufacturer: { contains: search, mode: "insensitive" } },
+ { model: { contains: search, mode: "insensitive" } },
+ { sourceAuctionId: { contains: search, mode: "insensitive" } },
+ ];
+ }
+ if (closingWithinHours) {
+ where.closingAt = { lte: new Date(Date.now() + closingWithinHours * 3_600_000), gte: new Date() };
+ }
+
+ // DB-side sort + pagination path: only when the sort column is a native
+ // Listing scalar AND no risk filter is active (risk is derived from the
+ // Score relation and cannot be pushed to the DB without a schema change).
+ const canPaginateAtDb = NATIVE_SORT_COLUMNS.has(sort) && !risk;
+
+ if (canPaginateAtDb) {
+ const [rawRows, total] = await Promise.all([
+ prisma.listing.findMany({
+ where,
+ include: { research: true, costBreakdown: true, scores: true },
+ // nulls first on asc / last on desc mirrors the JS fallback's
+ // String(v ?? "") ordering, so the DB path is order-equivalent.
+ orderBy: {
+ [sort]: NULLABLE_NATIVE_SORT_COLUMNS.has(sort)
+ ? { sort: dir, nulls: dir === "asc" ? "first" : "last" }
+ : dir,
+ } as Prisma.ListingOrderByWithRelationInput,
+ skip: (page - 1) * pageSize,
+ take: pageSize,
+ }),
+ prisma.listing.count({ where }),
+ ]);
+ const rows = rawRows.map((l) => flattenListing(l, profile));
+ return { rows, total, page, pageSize };
+ }
+
+ // JS-side sort + pagination fallback: used when sort is a relation/computed
+ // field (opportunityScore, arbitrageScore, netProfit, roi, risk, etc.) or
+ // when a risk filter is active. A hard cap prevents unbounded memory load.
+ // TODO(perf): denormalize score columns (value, arbitrage, risk, …) onto
+ // Listing to enable DB-side sort/pagination for score-based sorts — needs a
+ // gated migration adding columns + a backfill job.
+ const CAP = 5000;
+ const all = await prisma.listing.findMany({
+ where,
+ include: { research: true, costBreakdown: true, scores: true },
+ take: CAP,
+ });
+ if (all.length === CAP) {
+ // Not silent: past the cap, total (rows.length) undercounts for this
+ // filter+sort. The durable fix is the denormalization TODO above.
+ console.warn(`[queryListings] hit ${CAP}-row cap (sort=${sort}, risk=${risk ?? "none"}); total may undercount`);
+ }
+
+ let rows = all.map((l) => flattenListing(l, profile));
+ if (risk) rows = rows.filter((r) => r.risk === risk);
+
+ rows.sort((a, b) => {
+ const av = a[sort];
+ const bv = b[sort];
+ if (typeof av === "number" && typeof bv === "number") return dir === "asc" ? av - bv : bv - av;
+ const as = String(av ?? "");
+ const bs = String(bv ?? "");
+ return dir === "asc" ? as.localeCompare(bs) : bs.localeCompare(as);
+ });
+
+ const total = rows.length;
+ const start = (page - 1) * pageSize;
+ return { rows: rows.slice(start, start + pageSize), total, page, pageSize };
+}
diff --git a/govarbitrage/src/lib/marketplace-links.test.ts b/govarbitrage/src/lib/marketplace-links.test.ts
new file mode 100644
index 0000000..c7aec17
--- /dev/null
+++ b/govarbitrage/src/lib/marketplace-links.test.ts
@@ -0,0 +1,49 @@
+import { describe, expect, it } from "vitest";
+import { searchQuery, auctionNetworkLinks, boardLinks, internationalNetworkLinks, internationalTopLinks } from "./marketplace-links";
+
+describe("searchQuery", () => {
+ it("prefers manufacturer + model and strips lot/qty noise", () => {
+ expect(searchQuery({ title: "Computer Tables (4 EA)", manufacturer: "Herman Miller", model: "Aeron B" })).toBe(
+ "Herman Miller Aeron B",
+ );
+ expect(searchQuery({ title: "HOSPITAL STRECHERS (1 LOT)" })).toBe("HOSPITAL STRECHERS");
+ expect(searchQuery({ title: "Lot of 12 Apple MacBook Pro 14" })).toContain("Apple MacBook Pro 14");
+ });
+
+ it("ignores generic manufacturer/model values", () => {
+ expect(searchQuery({ title: "Mixed Metals Scrap", manufacturer: "Assorted", model: "Various" })).toBe(
+ "Mixed Metals Scrap",
+ );
+ });
+});
+
+describe("link builders", () => {
+ it("produces auction + resale + retail links, encoded", () => {
+ const links = auctionNetworkLinks({ title: "Toyota Forklift", category: "Industrial" });
+ const labels = links.map((l) => l.label);
+ expect(labels).toContain("eBay Sold");
+ expect(labels).toContain("GovDeals");
+ expect(labels).toContain("MachineryTrader"); // category specialist
+ expect(links.every((l) => l.url.includes("Toyota") || l.url.includes("Forklift"))).toBe(true);
+ });
+
+ it("adds DOTmed for medical items and produces board links", () => {
+ const med = auctionNetworkLinks({ title: "Patient Transfer Chair", category: "Medical" });
+ expect(med.map((l) => l.label)).toContain("DOTmed");
+ const boards = boardLinks({ title: "Patient Transfer Chair" });
+ expect(boards.map((l) => l.label)).toEqual(
+ expect.arrayContaining(["Reddit", "r/Flipping", "Facebook Marketplace", "Discord (Disboard)"]),
+ );
+ });
+
+ it("covers all five international regions with encoded queries", () => {
+ const intl = internationalNetworkLinks({ title: "Toyota Forklift" });
+ expect(intl.map((r) => r.region)).toEqual(["Europe", "Japan", "Hong Kong", "Australia", "China"]);
+ const allUrls = intl.flatMap((r) => r.links.map((l) => l.url));
+ expect(allUrls.some((u) => u.includes("yahoo.co.jp"))).toBe(true);
+ expect(allUrls.some((u) => u.includes("taobao.com"))).toBe(true);
+ expect(allUrls.some((u) => u.includes("graysonline.com"))).toBe(true);
+ expect(allUrls.every((u) => u.includes("Toyota") || u.includes("Forklift"))).toBe(true);
+ expect(internationalTopLinks({ title: "Toyota Forklift" })).toHaveLength(4);
+ });
+});
diff --git a/govarbitrage/src/lib/marketplace-links.ts b/govarbitrage/src/lib/marketplace-links.ts
new file mode 100644
index 0000000..ad2ddc8
--- /dev/null
+++ b/govarbitrage/src/lib/marketplace-links.ts
@@ -0,0 +1,138 @@
+// Build deep-search links to (a) other auction/resale marketplaces and (b)
+// social "goods-needed"/flipping boards, from a listing's identifying terms.
+// All $0, no API keys — each link opens the network's live results for the item.
+
+export interface LinkChip {
+ label: string;
+ url: string;
+ group: "auction" | "resale" | "retail" | "board";
+}
+
+interface Named {
+ title: string;
+ manufacturer?: string | null;
+ model?: string | null;
+ category?: string | null;
+}
+
+/** Best short search query for an item: prefer brand+model, strip lot/qty noise. */
+export function searchQuery(l: Named): string {
+ const generic = /^(assorted|mixed|various|lot|n\/a|unknown|misc)/i;
+ const parts: string[] = [];
+ if (l.manufacturer && !generic.test(l.manufacturer)) parts.push(l.manufacturer);
+ if (l.model && !generic.test(l.model)) parts.push(l.model);
+ let q = parts.join(" ").trim();
+ if (!q) q = l.title;
+ // Strip lot/quantity noise: "(4 EA)", "Lot of 12", "1 LOT", trailing counts.
+ q = q
+ .replace(/\([^)]*\)/g, " ")
+ .replace(/\b(lot of|qty|quantity)\b\s*\d*/gi, " ")
+ .replace(/\b\d+\s*(ea|each|lot|pcs?|units?)\b/gi, " ")
+ .replace(/\bDEMIL\b/gi, " ")
+ .replace(/[^\w\s.\-/]/g, " ")
+ .replace(/\s+/g, " ")
+ .trim();
+ return q.split(/\s+/).slice(0, 7).join(" ");
+}
+
+const enc = (s: string) => encodeURIComponent(s);
+
+/** Actual offers/comps on other auction + resale + retail networks. */
+export function auctionNetworkLinks(l: Named): LinkChip[] {
+ const q = searchQuery(l);
+ const e = enc(q);
+ const links: LinkChip[] = [
+ { label: "eBay Sold", url: `https://www.ebay.com/sch/i.html?_nkw=${e}&LH_Sold=1&LH_Complete=1`, group: "resale" },
+ { label: "eBay Active", url: `https://www.ebay.com/sch/i.html?_nkw=${e}`, group: "resale" },
+ { label: "GovDeals", url: `https://www.govdeals.com/search?kWord=${e}`, group: "auction" },
+ { label: "AllSurplus", url: `https://www.allsurplus.com/search?query=${e}`, group: "auction" },
+ { label: "Public Surplus", url: `https://www.publicsurplus.com/sms/browse/search?keyword=${e}`, group: "auction" },
+ { label: "Municibid", url: `https://municibid.com/Browse?search=${e}`, group: "auction" },
+ { label: "Google Shopping", url: `https://www.google.com/search?tbm=shop&q=${e}`, group: "retail" },
+ ];
+ // Category-specialist venues.
+ const cat = `${l.category ?? ""} ${l.title}`.toLowerCase();
+ if (/forklift|generator|welder|lathe|cnc|scissor|mower|tractor|compressor|industrial|machin/.test(cat)) {
+ links.push({ label: "MachineryTrader", url: `https://www.machinerytrader.com/listings/search?keywords=${e}`, group: "resale" });
+ }
+ if (/lab|microscope|centrifuge|analyzer|hplc|spectro|scientific/.test(cat)) {
+ links.push({ label: "LabX", url: `https://www.labx.com/search?q=${e}`, group: "resale" });
+ }
+ if (/medical|dental|patient|exam|surgical|hospital|stretcher|vital/.test(cat)) {
+ links.push({ label: "DOTmed", url: `https://www.dotmed.com/equipment/search?q=${e}`, group: "resale" });
+ }
+ return links;
+}
+
+export interface RegionLinks {
+ region: string;
+ links: LinkChip[];
+}
+
+/** International resale + auction venues to check an item's price abroad. */
+export function internationalNetworkLinks(l: Named): RegionLinks[] {
+ const e = enc(searchQuery(l));
+ return [
+ {
+ region: "Europe",
+ links: [
+ { label: "eBay UK", url: `https://www.ebay.co.uk/sch/i.html?_nkw=${e}`, group: "resale" },
+ { label: "eBay Germany", url: `https://www.ebay.de/sch/i.html?_nkw=${e}`, group: "resale" },
+ { label: "Catawiki", url: `https://www.catawiki.com/en/s?q=${e}`, group: "auction" },
+ { label: "Troostwijk", url: `https://www.troostwijkauctions.com/en/l?searchQuery=${e}`, group: "auction" },
+ ],
+ },
+ {
+ region: "Japan",
+ links: [
+ { label: "Yahoo! Auctions JP", url: `https://auctions.yahoo.co.jp/search/search?p=${e}`, group: "resale" },
+ { label: "Mercari JP", url: `https://jp.mercari.com/search?keyword=${e}`, group: "resale" },
+ ],
+ },
+ {
+ region: "Hong Kong",
+ links: [{ label: "Carousell HK", url: `https://www.carousell.com.hk/search/${e}`, group: "resale" }],
+ },
+ {
+ region: "Australia",
+ links: [
+ { label: "GraysOnline", url: `https://www.graysonline.com/search?keywords=${e}`, group: "auction" },
+ { label: "Pickles", url: `https://www.pickles.com.au/search?q=${e}`, group: "auction" },
+ { label: "eBay AU", url: `https://www.ebay.com.au/sch/i.html?_nkw=${e}`, group: "resale" },
+ ],
+ },
+ {
+ region: "China",
+ links: [
+ { label: "Taobao", url: `https://s.taobao.com/search?q=${e}`, group: "resale" },
+ { label: "JD Auction", url: `https://auction.jd.com/searchList.html?keyword=${e}`, group: "auction" },
+ { label: "Alibaba", url: `https://www.alibaba.com/trade/search?SearchText=${e}`, group: "retail" },
+ ],
+ },
+ ];
+}
+
+/** Flat top-N international chips (for the compact dashboard column). */
+export function internationalTopLinks(l: Named): LinkChip[] {
+ const e = enc(searchQuery(l));
+ return [
+ { label: "eBay UK", url: `https://www.ebay.co.uk/sch/i.html?_nkw=${e}`, group: "resale" },
+ { label: "Yahoo! JP", url: `https://auctions.yahoo.co.jp/search/search?p=${e}`, group: "resale" },
+ { label: "Grays AU", url: `https://www.graysonline.com/search?keywords=${e}`, group: "auction" },
+ { label: "Taobao CN", url: `https://s.taobao.com/search?q=${e}`, group: "resale" },
+ ];
+}
+
+/** Social "goods-needed"/flipping/ISO boards + local marketplaces. */
+export function boardLinks(l: Named): LinkChip[] {
+ const q = searchQuery(l);
+ const e = enc(q);
+ return [
+ { label: "Reddit", url: `https://www.reddit.com/search/?q=${e}`, group: "board" },
+ { label: "r/Flipping", url: `https://www.reddit.com/r/Flipping/search/?q=${e}&restrict_sr=1`, group: "board" },
+ { label: "Reddit ISO/Wanted", url: `https://www.reddit.com/search/?q=${enc(`${q} ISO OR wanted OR "looking for"`)}`, group: "board" },
+ { label: "Facebook Marketplace", url: `https://www.facebook.com/marketplace/search/?query=${e}`, group: "board" },
+ { label: "Craigslist", url: `https://www.craigslist.org/search/sss?query=${e}`, group: "board" },
+ { label: "Discord (Disboard)", url: `https://disboard.org/search?keyword=${e}`, group: "board" },
+ ];
+}
diff --git a/govarbitrage/src/lib/money-math-visibility.test.ts b/govarbitrage/src/lib/money-math-visibility.test.ts
new file mode 100644
index 0000000..eafd783
--- /dev/null
+++ b/govarbitrage/src/lib/money-math-visibility.test.ts
@@ -0,0 +1,27 @@
+import { describe, expect, it } from "vitest";
+import { moneyMathVisible } from "./current-user";
+import { TIERS, TIER_ORDER } from "./tiers";
+
+// Guideline 5.6 regression lock (TK-10279). The app was rejected because
+// money-math visibility keyed off an undisclosed client signal (the presence of
+// any Authorization header). The fix removed all client-sniffing: visibility is
+// a pure function of the resolved tier. These tests fail the moment anyone
+// reintroduces a client/header-dependent branch or re-gates the FREE tier.
+describe("money-math visibility (Guideline 5.6 lock)", () => {
+ it("moneyMathVisible depends ONLY on the tier argument — no request/header input", () => {
+ // A pure function of one argument cannot vary by client. Same tier in ⇒
+ // same answer out, every call.
+ expect(moneyMathVisible.length).toBe(1);
+ });
+
+ it("the FREE tier shows the full analysis, so a fresh anonymous install is complete", async () => {
+ expect(TIERS.FREE.limits.showMoneyMath).toBe(true);
+ expect(await moneyMathVisible("FREE")).toBe(true);
+ });
+
+ it("every tier shows money-math identically (nothing is client-specific)", async () => {
+ for (const t of TIER_ORDER) {
+ expect(await moneyMathVisible(t)).toBe(true);
+ }
+ });
+});
diff --git a/govarbitrage/src/lib/newsletter.ts b/govarbitrage/src/lib/newsletter.ts
new file mode 100644
index 0000000..5090442
--- /dev/null
+++ b/govarbitrage/src/lib/newsletter.ts
@@ -0,0 +1,141 @@
+import { randomBytes } from "node:crypto";
+import { appendFile, mkdir } from "node:fs/promises";
+import path from "node:path";
+
+// Newsletter email dispatch — TEST mode by default.
+//
+// Env placeholders (all optional until a live send is approved):
+// NEWSLETTER_SEND_MODE "test" (default) | "live". Live additionally
+// requires NEWSLETTER_SEND_APPROVAL_TOKEN to be
+// set — both must be present or we stay in test.
+// NEWSLETTER_SEND_APPROVAL_TOKEN Steve-issued token gating live sends.
+// NEWSLETTER_FROM_NAME Display name for the From header.
+// NEWSLETTER_FROM_EMAIL From address (live wiring TBD via George/SMTP).
+// NEWSLETTER_MAILING_ADDRESS CAN-SPAM physical postal address (required in
+// every commercial email footer before go-live).
+// NEWSLETTER_BASE_URL Public origin for confirm/unsubscribe links;
+// falls back to the request origin.
+//
+// In TEST mode every would-be email is written to the console AND appended to
+// logs/newsletter-outbox.jsonl. LIVE mode (both NEWSLETTER_SEND_MODE=live and
+// NEWSLETTER_SEND_APPROVAL_TOKEN set) sends via George /api/send; both modes
+// append a redacted record to the outbox as the audit trail.
+
+export interface NewsletterEmail {
+ to: string;
+ subject: string;
+ text: string;
+}
+
+export function newsletterSendMode(): "test" | "live" {
+ const live =
+ process.env.NEWSLETTER_SEND_MODE === "live" &&
+ !!process.env.NEWSLETTER_SEND_APPROVAL_TOKEN;
+ return live ? "live" : "test";
+}
+
+const OUTBOX = path.join(process.cwd(), "logs", "newsletter-outbox.jsonl");
+
+// Confirm/unsubscribe tokens are live single-use credentials — anything that
+// lands in a log file must carry only a redacted prefix, never the full token.
+export function redactTokens(text: string): string {
+ return text.replace(/(token=)([A-Za-z0-9_-]{9,})/g, (_, k, v) => `${k}${v.slice(0, 8)}…`);
+}
+
+export async function sendNewsletterEmail(email: NewsletterEmail): Promise<void> {
+ const record = {
+ ...email,
+ text: redactTokens(email.text),
+ mode: newsletterSendMode(),
+ fromName: process.env.NEWSLETTER_FROM_NAME || "GovArbitrage Digest (unset)",
+ mailingAddress: process.env.NEWSLETTER_MAILING_ADDRESS || "(NEWSLETTER_MAILING_ADDRESS unset)",
+ at: new Date().toISOString(),
+ };
+
+ if (record.mode === "live") {
+ // Live transport: George /api/send (approved 2026-07-13). External sends
+ // require the X-Send-Approval token — George fail-closes without it. The
+ // transport gets `email.text` (full tokens); everything logged is redacted.
+ const base = process.env.GEORGE_URL;
+ const auth = process.env.GEORGE_BASIC_AUTH;
+ const sendToken = process.env.GEORGE_EXTERNAL_SEND_TOKEN;
+ if (!base || !auth || !sendToken) {
+ throw new Error("Live send needs GEORGE_URL, GEORGE_BASIC_AUTH, and GEORGE_EXTERNAL_SEND_TOKEN.");
+ }
+ const HTML_ESCAPE: Record<string, string> = { "&": "&", "<": "<", ">": ">" };
+ const html = `<pre style="font-family:ui-monospace,Menlo,monospace;font-size:14px;white-space:pre-wrap">${email.text
+ .replace(/[&<>]/g, (c) => HTML_ESCAPE[c] ?? c)
+ .replace(/(https?:\/\/[^\s]+)/g, '<a href="$1">$1</a>')}</pre>`;
+ const res = await fetch(`${base}/api/send`, {
+ method: "POST",
+ headers: {
+ "Content-Type": "application/json",
+ Authorization: `Basic ${auth}`,
+ "X-Send-Approval": sendToken,
+ },
+ body: JSON.stringify({
+ account: process.env.NEWSLETTER_GEORGE_ACCOUNT || "steve-office",
+ to: email.to,
+ subject: email.subject,
+ body: html,
+ }),
+ });
+ if (!res.ok) {
+ const text = await res.text();
+ throw new Error(`George send failed ${res.status}: ${text.slice(0, 300)}`);
+ }
+ } else {
+ console.log(`[newsletter:test-outbox] to=${email.to} subject="${email.subject}"`);
+ console.log(record.text);
+ }
+
+ // Both modes append to the outbox (redacted) — the audit trail of every send.
+ await mkdir(path.dirname(OUTBOX), { recursive: true });
+ await appendFile(OUTBOX, JSON.stringify(record) + "\n", "utf8");
+}
+
+export function newToken(): string {
+ return randomBytes(32).toString("base64url");
+}
+
+export function normalizeEmail(raw: string): string {
+ return raw.trim().toLowerCase();
+}
+
+function footer(): string {
+ const addr = process.env.NEWSLETTER_MAILING_ADDRESS || "(mailing address pending)";
+ const from = process.env.NEWSLETTER_FROM_NAME || "GovArbitrage Digest";
+ return `—\n${from}\n${addr}`;
+}
+
+export function buildConfirmEmail(to: string, baseUrl: string, confirmToken: string): NewsletterEmail {
+ const confirmUrl = `${baseUrl}/api/newsletter/confirm?token=${confirmToken}`;
+ return {
+ to,
+ subject: "Confirm your GovArbitrage digest subscription",
+ text: [
+ "You (or someone using this address) asked to receive the GovArbitrage",
+ "Top-10 government-surplus deals digest, sent twice daily (6am & 5pm PT).",
+ "Confirm below and the next digest is yours.",
+ "",
+ `Confirm your subscription: ${confirmUrl}`,
+ "",
+ "If you didn't request this, ignore this email and you won't be subscribed.",
+ "",
+ footer(),
+ ].join("\n"),
+ };
+}
+
+export function buildUnsubscribeNoticeEmail(to: string): NewsletterEmail {
+ return {
+ to,
+ subject: "You've been unsubscribed from the GovArbitrage digest",
+ text: [
+ "You've been unsubscribed and will receive no further digests.",
+ "This was effective immediately — no login or confirmation was required.",
+ "",
+ footer(),
+ ].join("\n"),
+ };
+}
diff --git a/govarbitrage/src/lib/password.ts b/govarbitrage/src/lib/password.ts
new file mode 100644
index 0000000..7bb1bc7
--- /dev/null
+++ b/govarbitrage/src/lib/password.ts
@@ -0,0 +1,21 @@
+import crypto from "node:crypto";
+
+// Password hashing with Node's built-in scrypt — no native deps. Format:
+// "scrypt$<saltHex>$<derivedKeyHex>". Node-runtime only (login route).
+
+const KEYLEN = 64;
+
+export function hashPassword(password: string): string {
+ const salt = crypto.randomBytes(16).toString("hex");
+ const dk = crypto.scryptSync(password, salt, KEYLEN).toString("hex");
+ return `scrypt$${salt}$${dk}`;
+}
+
+export function verifyPassword(password: string, stored: string): boolean {
+ const parts = stored.split("$");
+ if (parts.length !== 3 || parts[0] !== "scrypt") return false;
+ const [, salt, dkHex] = parts;
+ const expected = Buffer.from(dkHex, "hex");
+ const actual = crypto.scryptSync(password, salt, KEYLEN);
+ return expected.length === actual.length && crypto.timingSafeEqual(expected, actual);
+}
diff --git a/govarbitrage/src/lib/places.test.ts b/govarbitrage/src/lib/places.test.ts
new file mode 100644
index 0000000..2158bd4
--- /dev/null
+++ b/govarbitrage/src/lib/places.test.ts
@@ -0,0 +1,538 @@
+import { describe, expect, it } from "vitest";
+import {
+ isCommercial,
+ dedupeByContact,
+ normalizePhone,
+ normalizeHost,
+ relevanceScore,
+ type AgentResult,
+ type RawPlace,
+} from "./places";
+
+// isCommercial() is the finder's core value prop: precision-first
+// commercial-vs-residential classification (the LA 57→28 verified drop).
+// These offline tests lock that behavior in — zero live Google Places billing,
+// per project rails. The apply order under test (see places.ts):
+// 1. off-topic Google type → DROP
+// 2. strong commercial name OR allowlisted brand → KEEP (beats residential)
+// 3. residential/irrelevant name → DROP
+// 4. residential-flavored type → DROP
+// 5/6. no commercial evidence → DROP (precision > recall)
+
+function place(name: string, types: string[] = ["real_estate_agency"]): RawPlace {
+ return { id: "x", displayName: { text: name }, primaryType: types[0], types };
+}
+
+describe("isCommercial — KEEP on strong commercial name signals", () => {
+ it.each([
+ "Downtown Commercial Real Estate Brokers",
+ "Metro CRE Advisors",
+ "Industrial Property Group",
+ "Prime Office Leasing",
+ "Retail & Industrial Brokerage",
+ "Apex Investment Sales",
+ "Tenant Representation Partners",
+ "Net Lease Advisors",
+ "Summit Capital Markets",
+ "Bay Area Multifamily Group",
+ "Harbor Income Properties",
+ ])("keeps %s", (name) => {
+ expect(isCommercial(place(name))).toBe(true);
+ });
+});
+
+describe("isCommercial — KEEP on allowlisted national brokerage brands", () => {
+ it.each([
+ "CBRE",
+ "JLL Los Angeles",
+ "Cushman & Wakefield",
+ "Colliers International",
+ "Marcus & Millichap",
+ "Lee & Associates",
+ "Newmark Group",
+ "Avison Young",
+ "Hughes Marino",
+ "NAI Capital",
+ ])("keeps brand %s", (name) => {
+ expect(isCommercial(place(name))).toBe(true);
+ });
+
+ it("is case-insensitive on brand match", () => {
+ expect(isCommercial(place("cbre commercial"))).toBe(true);
+ expect(isCommercial(place("MARCUS AND MILLICHAP"))).toBe(true);
+ });
+
+ it("word-boundary matches brands — no substring over-match", () => {
+ // A short brand like "voit" must not fire inside an unrelated word: a
+ // residential "Savoit Home Realty" contains the substring "voit" but is
+ // NOT the Voit brand and must drop (residential). The real brand still keeps.
+ expect(isCommercial(place("Voit Real Estate Services"))).toBe(true);
+ expect(isCommercial(place("Savoit Home Realty"))).toBe(false);
+ expect(isCommercial(place("NAI Capital"))).toBe(true);
+ // "nai" must not fire inside "domain"/"Sinai" (no word boundary).
+ expect(isCommercial(place("Sinai Home Sales"))).toBe(false);
+ });
+
+ it("matches a multi-word brand across space/hyphen/dot separators", () => {
+ // Google Places returns these two-word brands with varied separators; the
+ // boundary match must not require a literal space between the words.
+ expect(isCommercial(place("Jones-Lang LaSalle"))).toBe(true);
+ expect(isCommercial(place("Avison-Young"))).toBe(true);
+ expect(isCommercial(place("Kidder.Mathews"))).toBe(true);
+ expect(isCommercial(place("Avison Young"))).toBe(true); // plain space still works
+ });
+});
+
+describe("isCommercial — commercial evidence WINS over a residential word", () => {
+ it("keeps a name carrying both commercial and residential signals", () => {
+ // "commercial" (KEEP, step 2) is evaluated before "homes" (DROP, step 3).
+ expect(isCommercial(place("Commercial & Home Sales Group"))).toBe(true);
+ });
+ it("keeps an allowlisted brand even next to a residential word", () => {
+ expect(isCommercial(place("CBRE Residential Division"))).toBe(true);
+ });
+});
+
+describe("isCommercial — DROP on residential / irrelevant names", () => {
+ it.each([
+ "John Smith, Realtor",
+ "Dream Homes Realty",
+ "Sunset Home Sales",
+ "First-Time Buyer Specialists",
+ "The Home Team",
+ "Bluewater Property Management",
+ "City Apartments Locators",
+ "Acme Mortgage & Home Loans",
+ "Luxury Condos of Malibu",
+ "Single Family Realty Team",
+ ])("drops %s", (name) => {
+ expect(isCommercial(place(name))).toBe(false);
+ });
+});
+
+describe("isCommercial — DROP residential firms rescued only by a generic token", () => {
+ // Regression: COMMERCIAL_RE carries vertical-AMBIGUOUS tokens (brokerage/
+ // advisors/leasing/investments) that appear in residential names too. The
+ // commercial keep runs before the residential drop, so these were wrongly
+ // KEPT as commercial brokers on the strength of the generic token alone.
+ it.each([
+ "Coldwell Banker Residential Brokerage", // "brokerage" only
+ "Smith Residential Advisors", // "advisors" only
+ "Apartment Homes Leasing", // "leasing" only, apartment locator
+ "Hometown Residential Brokerage",
+ "Family Home Realty Advisors",
+ "Condo Leasing Specialists",
+ ])("drops %s (no strong commercial token, no brand)", (name) => {
+ expect(isCommercial(place(name))).toBe(false);
+ });
+
+ it("still KEEPS a strong commercial token next to a residential word", () => {
+ expect(isCommercial(place("Commercial & Home Sales Group"))).toBe(true);
+ expect(isCommercial(place("Office & Residential Brokerage"))).toBe(true); // "office" strong
+ expect(isCommercial(place("Multifamily Apartment Advisors"))).toBe(true); // "multifamily" strong
+ });
+
+ it("still KEEPS an allowlisted brand next to a residential word", () => {
+ expect(isCommercial(place("CBRE Residential"))).toBe(true);
+ expect(isCommercial(place("Colliers Residential Brokerage"))).toBe(true);
+ });
+});
+
+describe("isCommercial — DROP professional-practice offices (kept only by 'office' token)", () => {
+ // "office" is a CRE asset-class token in COMMERCIAL_RE, so "Law Office of Smith"
+ // was wrongly kept as a commercial broker. The "office OF <person>" structure
+ // marks a practice; legit CRE "Office Building"/"Office Leasing" names have no "of".
+ it.each([
+ "Law Office of Smith & Associates",
+ "Dental Office of Dr. Lee",
+ "Medical Office of Dr. Patel",
+ "Chiropractic Office of Dr. Kim",
+ "Offices of Dr. Jane Doe",
+ "Office of Attorney Robert Vance",
+ ])("drops %s", (name) => {
+ expect(isCommercial(place(name))).toBe(false);
+ });
+
+ it("does NOT over-drop legit commercial-RE office names (no 'of' structure)", () => {
+ expect(isCommercial(place("Downtown Office Leasing Advisors"))).toBe(true);
+ expect(isCommercial(place("Medical Office Building Advisors"))).toBe(true);
+ expect(isCommercial(place("Suburban Office Properties Group"))).toBe(true);
+ expect(isCommercial(place("Office & Industrial Brokerage"))).toBe(true);
+ });
+
+ it("does NOT mis-fire on 'Dr'-/'attorney'-prefixed surnames after 'Office of'", () => {
+ // The "of dr" branch must require a boundary — "Drummond"/"Draper" are not "Dr.".
+ expect(isCommercial(place("Office of Drummond Commercial Partners"))).toBe(true);
+ expect(isCommercial(place("Office of Draper Industrial"))).toBe(true);
+ // A real "Dr." / "Attorney" practice still drops.
+ expect(isCommercial(place("Office of Dr. Chen Dental"))).toBe(false);
+ expect(isCommercial(place("Office of Attorney Robert Vance"))).toBe(false);
+ });
+});
+
+describe("isCommercial — DROP on generic / no-evidence real-estate results", () => {
+ it("drops a plain real_estate_agency with no commercial signal", () => {
+ // Precision-first: Google's real_estate_agency type can't confirm commercial.
+ expect(isCommercial(place("Smith Real Estate"))).toBe(false);
+ expect(isCommercial(place("Downtown Realty"))).toBe(false);
+ });
+ it("drops a non-real-estate result with no signal either way", () => {
+ expect(isCommercial(place("Bob's Consulting", ["consultant"]))).toBe(false);
+ });
+});
+
+describe("isCommercial — DROP on off-topic Google place types (regardless of name)", () => {
+ it.each([
+ ["The Maimon Group", "travel_agency"],
+ ["Cozy Stays", "lodging"],
+ ["Shield Insurance", "insurance_agency"],
+ ["Two Guys Moving", "moving_company"],
+ ["SecureBox Self Storage", "self_storage"],
+ ["Mario's Restaurant", "restaurant"],
+ ])("drops %s tagged %s", (name, type) => {
+ expect(isCommercial(place(name, [type]))).toBe(false);
+ });
+
+ it("off-topic type beats an otherwise-commercial name", () => {
+ // A travel_agency mis-tag on a 'commercial'-named place is still dropped.
+ expect(isCommercial(place("Commercial Getaways", ["travel_agency"]))).toBe(false);
+ });
+
+ // Regression: Google place types are underscore-joined ("furniture_store"), and
+ // "_" is a JS word char, so the old \bstore\b boundary NEVER fired inside them —
+ // every compound retail/mall type with a commercial-sounding name leaked through
+ // (an "Office Furniture Warehouse" tagged furniture_store was kept as a broker).
+ // Now matched by exact type token + a "*_store" suffix rule.
+ it.each([
+ ["Office Furniture Warehouse", "furniture_store"],
+ ["Commercial Hardware Supply", "hardware_store"],
+ ["Downtown Office Depot", "office_supply_store"],
+ ["Retail Plaza Shopping Mall", "shopping_mall"],
+ ["Prime Electronics Outlet", "electronics_store"],
+ ["Industrial Home Improvement Center", "home_improvement_store"],
+ ["Commercial Auto Group", "car_dealer"],
+ ["Office Legal Advisors", "lawyer"],
+ ])("drops %s (compound off-topic type %s) despite a commercial-sounding name", (name, type) => {
+ expect(isCommercial(place(name, [type]))).toBe(false);
+ });
+
+ it("catches an off-topic type buried in the secondary types array", () => {
+ // primaryType is real_estate_agency but a furniture_store type is also present.
+ expect(
+ isCommercial({
+ id: "x",
+ displayName: { text: "Commercial Furnishings & Realty" },
+ primaryType: "real_estate_agency",
+ types: ["real_estate_agency", "furniture_store"],
+ }),
+ ).toBe(false);
+ });
+
+ it("does NOT over-drop a legit commercial real_estate_agency (no *_store false hit)", () => {
+ expect(isCommercial(place("Commercial Real Estate Brokers", ["real_estate_agency"]))).toBe(true);
+ });
+});
+
+// Regression: the "broker" and "finance" verticals overlap, so a Places text
+// search for "commercial real estate broker" surfaces WEALTH / FINANCIAL-
+// ADVISORY / securities firms. Their names carry finance-ambiguous tokens
+// ("investments", "advisors", "capital markets") that ALSO satisfy the generic
+// COMMERCIAL_RE keep — so before the fix, "Sterling Financial Advisors" and
+// "Vanguard Investment Advisors" were WRONGLY kept as CRE brokers. Now a
+// financial-advisory name with NO real-estate anchor is dropped, while a legit
+// CRE broker (which carries an RE anchor or an allowlisted brand) is preserved.
+describe("isCommercial — DROP on financial-advisory / wealth firms with no RE anchor", () => {
+ it.each([
+ "Morgan Stanley Wealth Investments",
+ "Sterling Financial Advisors",
+ "Vanguard Investment Advisors",
+ "Edward Jones Investments",
+ "Pinnacle Wealth Advisors",
+ "Fidelity Asset Management",
+ "Prudential Insurance Advisors",
+ "Cornerstone Wealth Management",
+ "Cambridge Investment Management",
+ "Heritage Retirement Advisors",
+ ])("drops finance firm %s (no real-estate anchor)", (name) => {
+ expect(isCommercial(place(name))).toBe(false);
+ });
+
+ it.each([
+ // A real-estate anchor rescues a finance-ambiguous name — these are real CRE.
+ "Net Lease Advisors", // "net lease" + "leasing" anchor
+ "Metro CRE Advisors", // "CRE" anchor
+ "Apex Investment Sales", // "investment sales" is the CRE-specific phrase
+ "Harbor Income Properties", // "properties" anchor
+ "Commercial Real Estate Investment Advisors", // "real estate" anchor
+ "Summit Capital Markets", // capital markets alone isn't a finance-drop token
+ ])("keeps CRE broker %s despite a finance-ambiguous word", (name) => {
+ expect(isCommercial(place(name))).toBe(true);
+ });
+
+ it("keeps an allowlisted CRE brand even when the name carries a finance word", () => {
+ // Brand allowlist overrides the financial-advisory drop.
+ expect(isCommercial(place("CBRE Investment Management"))).toBe(true);
+ expect(isCommercial(place("JLL Capital Markets"))).toBe(true);
+ });
+
+ it("still keeps a plainly-commercial broker with no finance word at all", () => {
+ // Guard against the finance gate over-reaching into ordinary CRE names.
+ expect(isCommercial(place("Downtown Commercial Leasing"))).toBe(true);
+ });
+});
+
+// CRE-adjacent SERVICE / trade false-positive gate. A Places "commercial real
+// estate broker" text search surfaces firms that WORK on commercial property but
+// are NOT buyer/leasing brokers — appraisers, title/escrow, construction, mortgage
+// lenders, inspectors, property/facilities managers. Their names carry the same
+// "commercial"/"property" tokens that satisfy COMMERCIAL_RE, so before the fix
+// "Commercial Property Appraisers" and "Sunbelt Commercial Construction" were
+// WRONGLY kept as brokers. Now a CRE-service name with NO explicit broker anchor
+// is dropped, while a real brokerage (broker anchor or allowlisted brand) — even
+// one with a valuation / property-management arm — is preserved.
+describe("isCommercial — DROP on CRE-adjacent service/trade firms with no broker anchor", () => {
+ it.each([
+ "Commercial Property Appraisers Inc",
+ "Titan Commercial Title & Escrow",
+ "Sunbelt Commercial Construction",
+ "Meridian Commercial Property Management",
+ "Apex Commercial Mortgage Capital",
+ "National Commercial Property Inspections",
+ "Green Commercial Environmental Services",
+ "Premier Commercial Interior Design Group",
+ "Statewide Commercial Escrow Services",
+ "Metro Commercial Facilities Management",
+ "Cornerstone Commercial Appraisal & Valuation",
+ "Summit Commercial General Contractors",
+ ])("drops CRE-service firm %s (no broker anchor)", (name) => {
+ expect(isCommercial(place(name))).toBe(false);
+ });
+
+ it.each([
+ // A broker anchor rescues a name that also mentions a service word.
+ "Titan Commercial Real Estate Brokerage", // "brokerage" + "real estate"
+ "Landmark Commercial Realty & Property Management", // "commercial realty" broker + PM arm
+ "Apex Commercial Real Estate & Construction Advisors", // "real estate" anchor
+ "Downtown Commercial Leasing", // "leasing" anchor, no service word
+ "Net Lease Advisors", // pure broker, unaffected
+ "Harbor Investment Sales", // "investment sales" anchor
+ ])("keeps CRE broker %s despite (or without) a service word", (name) => {
+ expect(isCommercial(place(name))).toBe(true);
+ });
+
+ it("keeps an allowlisted brand's valuation / management arm", () => {
+ // Brand allowlist overrides the CRE-service drop.
+ expect(isCommercial(place("Cushman & Wakefield Valuation Services"))).toBe(true);
+ expect(isCommercial(place("CBRE Property Management"))).toBe(true);
+ expect(isCommercial(place("Colliers Appraisal Group"))).toBe(true);
+ });
+
+ it("still keeps a plainly-commercial broker with no service word at all", () => {
+ // Guard against the service gate over-reaching into ordinary CRE names.
+ expect(isCommercial(place("Commercial Realty Advisors"))).toBe(true);
+ expect(isCommercial(place("Downtown Commercial Real Estate Brokers"))).toBe(true);
+ });
+});
+
+describe("isCommercial — DROP on residential-flavored place types", () => {
+ it.each(["apartment_complex", "home_goods_store"])("drops type %s with no commercial name", (type) => {
+ expect(isCommercial(place("Parkview", [type]))).toBe(false);
+ });
+ it("commercial name still wins over a residential-flavored type", () => {
+ expect(isCommercial(place("Commercial Leasing at Parkview", ["apartment_complex"]))).toBe(true);
+ });
+});
+
+describe("isCommercial — edge cases", () => {
+ it("drops a place with no name", () => {
+ expect(isCommercial({ id: "x", displayName: { text: "" }, types: ["real_estate_agency"] })).toBe(false);
+ });
+ it("drops a place with no types and no commercial name", () => {
+ expect(isCommercial({ id: "x", displayName: { text: "Generic Agency" } })).toBe(false);
+ });
+});
+
+// --- Contact-based dedupe -------------------------------------------------
+// dedupeByContact() collapses the same office surfaced under multiple lenses as
+// different place ids. Phone is authoritative; website only merges when phones
+// don't contradict (national brands share one domain across many branches).
+// Offline + deterministic — zero live Google Places billing.
+
+describe("normalizePhone", () => {
+ it.each([
+ ["(213) 555-0123", "2135550123"],
+ ["213-555-0123", "2135550123"],
+ ["+1 213 555 0123", "2135550123"],
+ ["1 (213) 555-0123", "2135550123"],
+ ])("normalizes %s → %s", (input, expected) => {
+ expect(normalizePhone(input)).toBe(expected);
+ });
+ it("returns null for null / partial / non-10-digit numbers", () => {
+ expect(normalizePhone(null)).toBeNull();
+ expect(normalizePhone("555-0123")).toBeNull(); // 7 digits — not keyable
+ expect(normalizePhone("")).toBeNull();
+ });
+});
+
+describe("normalizeHost", () => {
+ it.each([
+ ["https://www.cbre.com/office/la", "cbre.com"],
+ ["http://CBRE.com", "cbre.com"],
+ ["https://la.cbre.com", "la.cbre.com"], // subdomains stay distinct (precise)
+ ])("normalizes %s → %s", (input, expected) => {
+ expect(normalizeHost(input)).toBe(expected);
+ });
+ it("returns null for null / unparseable", () => {
+ expect(normalizeHost(null)).toBeNull();
+ expect(normalizeHost("not a url")).toBeNull();
+ });
+});
+
+function agent(over: Partial<AgentResult> & { id: string; name: string }): AgentResult {
+ return {
+ address: null,
+ rating: null,
+ reviews: 0,
+ phone: null,
+ website: null,
+ mapsUrl: null,
+ primaryType: null,
+ kinds: ["buyer"],
+ ...over,
+ };
+}
+
+describe("dedupeByContact", () => {
+ it("collapses the same office surfaced under two lenses (same phone)", () => {
+ // The motivating case: "CBRE" + "CBRE Investment Sales", same office/phone,
+ // two different place ids from two different query lenses.
+ const out = dedupeByContact([
+ agent({ id: "1", name: "CBRE", phone: "(213) 555-0100", reviews: 40, rating: 4.5, kinds: ["buyer"] }),
+ agent({
+ id: "2",
+ name: "CBRE Investment Sales",
+ phone: "213-555-0100",
+ reviews: 3,
+ rating: 4.9,
+ kinds: ["leasing"],
+ }),
+ ]);
+ expect(out).toHaveLength(1);
+ expect(out[0].id).toBe("1"); // first-seen kept canonical
+ expect(out[0].kinds.sort()).toEqual(["buyer", "leasing"]); // kinds unioned
+ // Richer rating/review pair adopted from the MORE-rated listing (id 1).
+ expect(out[0].reviews).toBe(40);
+ expect(out[0].rating).toBe(4.5);
+ });
+
+ it("keeps two DIFFERENT branch offices of one brand (same domain, different phones)", () => {
+ const out = dedupeByContact([
+ agent({ id: "1", name: "CBRE Downtown", phone: "213-555-0100", website: "https://www.cbre.com" }),
+ agent({ id: "2", name: "CBRE West LA", phone: "310-555-0200", website: "https://www.cbre.com" }),
+ ]);
+ expect(out).toHaveLength(2); // distinct phones ⇒ distinct offices, never merged
+ });
+
+ it("merges on website only when phones don't contradict (one side phone-less)", () => {
+ const out = dedupeByContact([
+ agent({ id: "1", name: "JLL", phone: "213-555-0300", website: "https://jll.com" }),
+ agent({ id: "2", name: "JLL Capital Markets", phone: null, website: "https://jll.com" }),
+ ]);
+ expect(out).toHaveLength(1);
+ expect(out[0].id).toBe("1");
+ });
+
+ it("enriches the canonical's missing contact fields from the duplicate", () => {
+ const out = dedupeByContact([
+ agent({ id: "1", name: "Colliers", phone: "213-555-0400", website: null, address: null }),
+ agent({
+ id: "2",
+ name: "Colliers Intl",
+ phone: "213-555-0400",
+ website: "https://colliers.com",
+ address: "1 Main St",
+ }),
+ ]);
+ expect(out).toHaveLength(1);
+ expect(out[0].website).toBe("https://colliers.com"); // filled from dup
+ expect(out[0].address).toBe("1 Main St"); // filled from dup
+ });
+
+ it("leaves genuinely distinct agencies untouched (no shared phone or host)", () => {
+ const out = dedupeByContact([
+ agent({ id: "1", name: "Newmark", phone: "213-555-0500", website: "https://nmrk.com" }),
+ agent({ id: "2", name: "Kidder Mathews", phone: "206-555-0600", website: "https://kidder.com" }),
+ ]);
+ expect(out).toHaveLength(2);
+ });
+
+ it("does not merge two phone-distinct entries that lack websites", () => {
+ const out = dedupeByContact([
+ agent({ id: "1", name: "Lee & Associates", phone: "213-555-0700" }),
+ agent({ id: "2", name: "Avison Young", phone: "213-555-0800" }),
+ ]);
+ expect(out).toHaveLength(2);
+ });
+});
+
+// relevanceScore() weights the RANKED list so a stronger CRE-broker match
+// (national brand / strong commercial name / surfaced by both lenses) surfaces
+// ahead of a weakly-commercial one at comparable rating-weight — without a
+// 0-review brand leapfrogging a genuinely reviewed office. Offline, no billing.
+describe("relevanceScore", () => {
+ it("returns the 1.0 baseline for a plain commercial listing", () => {
+ // No allowlisted brand, no strong COMMERCIAL_RE hit in the name, one lens.
+ expect(relevanceScore(agent({ id: "x", name: "Downtown Advisory" }))).toBeCloseTo(1.0);
+ });
+
+ it("adds weight for an allowlisted national brokerage brand", () => {
+ const s = relevanceScore(agent({ id: "x", name: "CBRE", kinds: ["buyer"] }));
+ expect(s).toBeGreaterThan(1.0);
+ // CBRE hits the brand bonus (+0.3) AND "commercial"? no — name is bare "CBRE"
+ // so only the brand bonus applies.
+ expect(s).toBeCloseTo(1.3);
+ });
+
+ it("adds weight for a strong commercial name signal", () => {
+ // "Industrial Property Group" hits COMMERCIAL_RE but is not an allowlist brand.
+ expect(relevanceScore(agent({ id: "x", name: "Industrial Property Group" }))).toBeCloseTo(1.2);
+ });
+
+ it("adds weight when surfaced by BOTH the buyer and leasing lenses", () => {
+ const both = relevanceScore(agent({ id: "x", name: "Anon Realty", kinds: ["buyer", "leasing"] }));
+ const one = relevanceScore(agent({ id: "x", name: "Anon Realty", kinds: ["buyer"] }));
+ expect(both - one).toBeCloseTo(0.1);
+ });
+
+ it("stacks signals (brand + commercial name + both lenses) up to the bound", () => {
+ const s = relevanceScore(
+ agent({ id: "x", name: "CBRE Commercial", kinds: ["buyer", "leasing"] }),
+ );
+ // +0.3 brand +0.2 commercial-name +0.1 both-lenses = 1.6 ceiling.
+ expect(s).toBeCloseTo(1.6);
+ });
+});
+
+// The ranking must PROMOTE a stronger commercial match at comparable
+// rating-weight, but a zero-review brand must NOT jump a genuinely reviewed
+// office. dedupeByContact is a pass-through here (distinct contacts), so we can
+// assert ordering behavior on the same sort comparator via a tiny local sort
+// mirroring searchCommercialAgents (kept in sync with places.ts).
+describe("relevance-weighted ranking behavior", () => {
+ const rankWeight = (r: AgentResult) =>
+ (r.rating ?? 0) * Math.log((r.reviews ?? 0) + 1) * relevanceScore(r);
+
+ it("promotes a stronger CRE match over a weakly-commercial one at equal rating/reviews", () => {
+ const brand = agent({ id: "1", name: "CBRE", rating: 4.5, reviews: 30, kinds: ["buyer", "leasing"] });
+ const weak = agent({ id: "2", name: "Anon Advisory", rating: 4.5, reviews: 30, kinds: ["buyer"] });
+ expect(rankWeight(brand)).toBeGreaterThan(rankWeight(weak));
+ });
+
+ it("does NOT let a zero-review brand leapfrog a well-reviewed office", () => {
+ const brandNoReviews = agent({ id: "1", name: "CBRE", rating: 5, reviews: 0, kinds: ["buyer", "leasing"] });
+ const reviewedOffice = agent({ id: "2", name: "Metro Advisory", rating: 4.2, reviews: 120, kinds: ["buyer"] });
+ // rating × log(1) = 0 for the brand regardless of relevance multiplier.
+ expect(rankWeight(brandNoReviews)).toBe(0);
+ expect(rankWeight(reviewedOffice)).toBeGreaterThan(rankWeight(brandNoReviews));
+ });
+});
diff --git a/govarbitrage/src/lib/places.ts b/govarbitrage/src/lib/places.ts
new file mode 100644
index 0000000..9f339b9
--- /dev/null
+++ b/govarbitrage/src/lib/places.ts
@@ -0,0 +1,634 @@
+// Google Places API (New) — Text Search for COMMERCIAL real-estate professionals
+// near a government-surplus property: buyer / acquisition brokers + leasing agents.
+//
+// Commercial-only by construction: the text queries target commercial CRE, and a
+// light residential filter drops obvious home-sale / apartment-locator results
+// ("just commercial for this build"). Server-side ONLY — the API key never reaches
+// the client. Live-query with `no-store`; we don't persist Google place data beyond
+// the response (Places ToS: no long-term caching of place fields except the id).
+
+export type AgentKind = "buyer" | "leasing";
+
+export interface AgentResult {
+ id: string;
+ name: string;
+ address: string | null;
+ rating: number | null;
+ reviews: number;
+ phone: string | null;
+ website: string | null;
+ mapsUrl: string | null;
+ primaryType: string | null;
+ kinds: AgentKind[]; // which query lens(es) surfaced it
+}
+
+export interface CommercialAgentSearch {
+ city?: string;
+ state?: string;
+ zip?: string;
+ kinds: AgentKind[];
+}
+
+export interface CommercialAgentResponse {
+ location: string;
+ kinds: AgentKind[];
+ results: AgentResult[];
+ queries: number; // billed Places Text Search calls (for cost surfacing)
+}
+
+const ENDPOINT = "https://places.googleapis.com/v1/places:searchText";
+const FIELD_MASK = [
+ "places.id",
+ "places.displayName",
+ "places.formattedAddress",
+ "places.rating",
+ "places.userRatingCount",
+ "places.nationalPhoneNumber",
+ "places.websiteUri",
+ "places.googleMapsUri",
+ "places.businessStatus",
+ "places.primaryTypeDisplayName",
+ "places.types",
+ "places.primaryType",
+].join(",");
+
+// Curated query "lenses" per kind — a small set (≤3) of complementary CRE
+// angles that widen coverage without ballooning cost. Each lens is one billed
+// Places Text Search call; results are unioned across lenses by place id.
+const KIND_LENSES: Record<AgentKind, string[]> = {
+ buyer: [
+ "commercial real estate broker",
+ "commercial real estate investment sales broker",
+ "tenant representation broker",
+ ],
+ leasing: [
+ "commercial real estate leasing agent",
+ "office space leasing broker",
+ "retail industrial leasing broker",
+ ],
+};
+
+// Positive COMMERCIAL name evidence — the primary "keep" gate. A real-estate
+// result is only kept if it carries one of these strong commercial signals (or
+// an allowlisted brokerage brand, below). "office"/"retail"/"industrial" are
+// asset-class words that reliably read commercial in a broker name; "leasing"
+// stays because our query lenses are commercial-leasing lenses.
+const COMMERCIAL_RE =
+ /\b(commercial|CRE|industrial|retail|office|investment\s+sales|investments?|tenant\s+rep(?:resentation)?|net\s+lease|leasing|brokerage|advisors?|capital\s+markets|properties\s+group|commercial\s+properties|multifamily|commercial\s+group|income\s+propert(?:y|ies))\b/i;
+
+// STRONG (vertical-UNAMBIGUOUS) commercial tokens — the subset of COMMERCIAL_RE
+// that reads commercial-only. It deliberately EXCLUDES the vertical-ambiguous
+// generics that appear just as often in residential firm names — "brokerage",
+// "advisors", "leasing", "investments" — because a residential firm is a
+// "brokerage" with "advisors" doing "leasing" too. Used by the residential gate
+// below so an explicit residential name isn't rescued by a generic token alone.
+const STRONG_COMMERCIAL_RE =
+ /\b(commercial|CRE|industrial|retail|office|investment\s+sales|tenant\s+rep(?:resentation)?|net\s+lease|capital\s+markets|properties\s+group|commercial\s+properties|multifamily|commercial\s+group|income\s+propert(?:y|ies))\b/i;
+
+// Known national / regional COMMERCIAL brokerage brands. Case-insensitive
+// substring match — presence of any of these keeps the result outright, on par
+// with a strong commercial name signal (and ahead of the residential drop).
+const COMMERCIAL_BRANDS: string[] = [
+ "cbre",
+ "jll",
+ "jones lang",
+ "cushman",
+ "wakefield",
+ "colliers",
+ "newmark",
+ "marcus & millichap",
+ "marcus and millichap",
+ "kidder mathews",
+ "lee & associates",
+ "lee and associates",
+ "avison young",
+ "savills",
+ "matthews real estate",
+ "stan johnson",
+ "northmarq",
+ "berkadia",
+ "institutional property advisors",
+ "voit",
+ "daum",
+ "nai ",
+ "nai capital",
+ "cresa",
+ "transwestern",
+ "stream realty",
+ "hughes marino",
+];
+
+// RESIDENTIAL / irrelevant name signals — drop UNLESS a commercial signal or an
+// allowlisted brand overrides (apply order enforced in isCommercial). Kept
+// word-boundary aware so "homes" hits "Earnest Homes" but the standalone-noun
+// senses don't over-match. Generic "realty"/"real estate" with no commercial
+// evidence is handled by the positive-gate default-drop, not here.
+const RESIDENTIAL_RE =
+ /\b(residential|homes?|home\s+sales?|for\s+sale|realtor|property\s+m(?:anage|gmt)ment|apartments?|condos?|single\s+family|first[-\s]?time|home\s+team|realty\s+team|real\s+estate\s+team|mortgage|home\s+loans?)\b/i;
+
+// Places `types` signals. `real_estate_agency` is the generic real-estate type
+// (does NOT distinguish commercial vs residential — name text decides).
+const REAL_ESTATE_TYPES = new Set(["real_estate_agency"]);
+
+// Off-vertical / off-topic Google place types — drop on match regardless of
+// name. "The Maimon Group" came back as travel_agency; storage/lodging/moving/
+// insurance are common mis-tags that are never a commercial broker.
+//
+// Matched by EXACT type-token equality (not a \b-regex): Google's place types
+// are underscore-joined tokens (e.g. "furniture_store"), and "_" is a JS word
+// character, so a \bstore\b boundary never fires inside "furniture_store" —
+// it silently let every "*_store" / "shopping_mall" / etc. type through. Any
+// type ending in "_store" is also treated as off-topic (Places has dozens of
+// retail *_store subtypes: hardware_store, electronics_store, furniture_store…).
+const OFF_TOPIC_TYPES = new Set([
+ "travel_agency",
+ "lodging",
+ "insurance_agency",
+ "moving_company",
+ "storage",
+ "self_storage",
+ "restaurant",
+ "food",
+ "store",
+ "school",
+ "hospital",
+ "gym",
+ "shopping_mall",
+ "finance", // banks/lenders mis-tagged onto RE queries — not a broker
+ "lawyer",
+ "accounting",
+ "car_dealer",
+ "car_rental",
+ "general_contractor",
+ "home_improvement_store",
+]);
+
+function isOffTopicType(t: string): boolean {
+ const tt = t.toLowerCase();
+ return OFF_TOPIC_TYPES.has(tt) || tt.endsWith("_store");
+}
+
+// Residential-flavored place types — exact-token match (same underscore-boundary
+// reason as above). Complements the off-topic blocklist for apartment/home mis-tags.
+const RESIDENTIAL_TYPES = new Set(["apartment_complex", "home_goods_store"]);
+
+// FINANCIAL-ADVISORY / wealth / securities name signals — the "broker" and
+// "finance" verticals overlap, so a Places text search for "commercial real
+// estate broker" reliably surfaces wealth-management, financial-advisory and
+// securities firms. Google's `finance` type-gate only catches the ones Google
+// explicitly tagged finance; many advisory firms come back tagged
+// `establishment`/`point_of_interest` and slip past it. These names carry the
+// finance-ambiguous tokens ("investments", "advisors", "capital markets") that
+// ALSO appear in legit CRE names, so a firm matching this is dropped UNLESS it
+// also carries an unambiguous real-estate anchor (RE_ANCHOR_RE, below).
+// Note: "investment sales" is a CRE-specific phrase and is EXCLUDED here (it's a
+// real-estate anchor, below) — only bare "investment(s)" / "investment advisors"
+// (with no RE anchor) reads as a wealth firm. The negative lookahead on
+// "investment" avoids matching "investment sales".
+const FINANCIAL_ADVISORY_RE =
+ /\b(wealth|financial\s+advisors?|financial\s+planning|financial\s+services|financial\s+group|securities|asset\s+management|wealth\s+management|investment\s+management|investment\s+advisors?|investments?(?!\s+sales)|private\s+equity|hedge\s+fund|insurance|retirement|portfolio\s+management|mutual\s+funds?|stockbrokers?|401k?)\b/i;
+
+// Unambiguous REAL-ESTATE anchor words. Presence of one proves a
+// finance-ambiguous name (e.g. "Investment Sales Advisors") is genuinely a
+// commercial-RE broker and not a wealth firm, so it survives the financial-
+// advisory drop. "real estate" / "realty" / "property/properties" / "leasing"
+// are the reliable RE anchors; "CRE" and "commercial real estate" too.
+const RE_ANCHOR_RE =
+ /\b(real\s+estate|realty|CRE|propert(?:y|ies)|leasing|net\s+lease|tenant\s+rep(?:resentation)?|multifamily|land\s+brokerage|investment\s+sales|commercial)\b/i;
+
+// CRE-ADJACENT SERVICE / TRADE name signals — firms that WORK on commercial
+// property but are NOT buyer/leasing brokers: appraisal/valuation, title &
+// escrow, construction & general contracting, mortgage lending / loan servicing,
+// property inspection, environmental, interior design, architecture/engineering,
+// surveying, facilities & property management, and the trades (roofing/HVAC/…).
+// A Places "commercial real estate broker" text search surfaces these because
+// their names carry the same "commercial"/"property" asset-class tokens that
+// satisfy COMMERCIAL_RE (e.g. "Commercial Property Appraisers", "Sunbelt
+// Commercial Construction", "Apex Commercial Mortgage Capital"), so they'd
+// otherwise be kept as brokers and pollute the "brokers near this property"
+// answer. This is distinct from the FINANCIAL_ADVISORY drop (wealth/securities
+// firms) and from the off-topic Google-`types` drop (retail/mall mis-tags): here
+// the NAME itself reads commercial-RE, but the firm is a CRE trade/service, not a
+// brokerage. Dropped UNLESS the name ALSO carries an explicit broker anchor
+// (BROKER_ANCHOR_RE) or an allowlisted brokerage brand — a full-service
+// brokerage that happens to run a valuation / property-management arm survives.
+const CRE_SERVICE_RE =
+ /\b(appraisals?|appraisers?|valuations?|title(?:\s+(?:company|services|insurance|&\s+escrow))?|escrow|construction|general\s+contract(?:or|ing)|contractors?|mortgage(?!\s+broker)|lending|lenders?|loan\s+servicing|inspections?|inspectors?|environmental|interior\s+design|architects?|architecture|engineering|surveyors?|surveying|janitorial|cleaning|landscaping|roofing|hvac|plumbing|restoration|property\s+tax|facilit(?:y|ies)\s+m(?:anage|gmt)ment|property\s+m(?:anage|gmt)ment)\b/i;
+
+// Explicit BROKER anchor — proves the firm actually does brokerage even if its
+// name also mentions a service word (e.g. a "Realty & Property Management" firm
+// or a "Real Estate & Construction Advisors" brokerage). Presence of one of
+// these overrides the CRE-service drop and keeps the listing.
+const BROKER_ANCHOR_RE =
+ /\b(brokers?|brokerage|realty|real\s+estate|leasing|tenant\s+rep(?:resentation)?|investment\s+sales|net\s+lease|realtors?)\b/i;
+
+// Word-boundary match each brand rather than a bare substring: a plain
+// `name.includes("voit")` also fires inside unrelated words ("Savoit Home
+// Realty" → wrongly treated as the Voit brand, then kept as commercial), and
+// short brands (voit/daum/nai) are the ones most prone to it. A `\bBRAND\b`
+// test keeps "Voit Real Estate" while rejecting "Savoit…". Brand strings are
+// regex-escaped and trimmed (the legacy "nai " trailing-space guard is now
+// redundant — `\bnai\b` already won't fire inside "domain"/"Sinai").
+// Professional PRACTICE office — a law / dental / medical / chiropractic / … firm
+// named "<practice> Office of …" or "Office(s) of Dr./Attorney …". NOT a CRE
+// broker: "office" is a CRE asset-class token in COMMERCIAL_RE, so "Law Office of
+// Smith & Associates" was kept as a commercial broker. The reliable giveaway is
+// the "office OF <person/Dr./attorney>" structure — a practice names itself
+// "Office of Dr. Lee", whereas a CRE firm says "Medical Office BUILDING Advisors"
+// or "Office LEASING Group" (no "of" after "office"). Deliberately narrow (requires
+// the "of" preposition) so it does NOT touch legit CRE office names. An allowlisted
+// brand or explicit broker anchor overrides.
+const PROFESSIONAL_OFFICE_RE =
+ /\b(?:law|dental|medical|chiropractic|veterinary|optometry|accounting|dermatolog\w*|orthodont\w*|podiatr\w*|pediatric|psychiatr\w*|counsel(?:ing|ling))\s+offices?\s+of\b|\boffices?\s+of\s+(?:drs?\.?\b|attorney\b|the\s+law\b)/i;
+
+function isProfessionalPracticeOffice(name: string): boolean {
+ return (
+ PROFESSIONAL_OFFICE_RE.test(name) &&
+ !BROKER_ANCHOR_RE.test(name) &&
+ !hasCommercialBrand(name)
+ );
+}
+
+function hasCommercialBrand(name: string): boolean {
+ const n = name.toLowerCase();
+ return COMMERCIAL_BRANDS.some((b) => {
+ const esc = b.trim().replace(/[.*+?^${}()|[\]\\]/g, "\\$&");
+ // A multi-word brand ("jones lang", "avison young") appears in real Google
+ // Places names with a space, hyphen, or dot separator ("Jones-Lang LaSalle",
+ // "Avison-Young"). Match any of those between words so the boundary fix
+ // doesn't trade the substring over-match bug for a separator-rigidity miss.
+ const pat = esc.replace(/ /g, "[\\s\\-.]");
+ return new RegExp(`\\b${pat}\\b`, "i").test(n);
+ });
+}
+
+// True when the name reads as a CRE-adjacent SERVICE / trade firm (appraisal,
+// title/escrow, construction, mortgage lending, inspection, property/facilities
+// management, …) that carries NO explicit broker anchor — i.e. a service
+// false-positive we must drop even though its "commercial"/"property" tokens
+// would otherwise satisfy COMMERCIAL_RE. An allowlisted brokerage brand always
+// overrides (a real brokerage's valuation / PM arm stays).
+function isCreServiceNoBroker(name: string): boolean {
+ return (
+ CRE_SERVICE_RE.test(name) &&
+ !BROKER_ANCHOR_RE.test(name) &&
+ !hasCommercialBrand(name)
+ );
+}
+
+// True when the name reads as a financial-advisory / wealth firm that lacks a
+// real-estate anchor — i.e. a finance false-positive we must drop even though a
+// bare finance-ambiguous token (investments/advisors/capital markets) would
+// otherwise satisfy COMMERCIAL_RE. An allowlisted CRE brand always overrides
+// (a real brokerage that happens to include "insurance" etc. in its name stays).
+function isFinancialAdvisoryNoRE(name: string): boolean {
+ return (
+ FINANCIAL_ADVISORY_RE.test(name) &&
+ !RE_ANCHOR_RE.test(name) &&
+ !hasCommercialBrand(name)
+ );
+}
+
+// True when the name carries an EXPLICIT residential signal (residential/homes/
+// apartments/realtor/…) and its only "commercial" evidence is a vertical-
+// AMBIGUOUS generic token (brokerage / advisors / leasing / investments) — i.e.
+// no STRONG (commercial-only) token and no allowlisted brand. This is the
+// residential mirror of the finance/service gates: without it, the positive
+// keep (COMMERCIAL_RE) short-circuits before the residential drop, so a plain
+// residential firm like "Coldwell Banker Residential Brokerage" or "Smith
+// Residential Advisors" is wrongly kept as a commercial broker on the strength
+// of "brokerage"/"advisors" alone. A strong commercial token ("Commercial &
+// Home Sales Group") or an allowlisted brand ("CBRE Residential") still wins.
+function isResidentialDespiteGeneric(name: string): boolean {
+ return (
+ RESIDENTIAL_RE.test(name) &&
+ !STRONG_COMMERCIAL_RE.test(name) &&
+ !hasCommercialBrand(name)
+ );
+}
+
+// --- Commercial-relevance ranking weight ----------------------------------
+// Every result in the ranked list has ALREADY passed isCommercial() (precision
+// gate), but they're not equally strong CRE-broker matches. A listing that is
+// an allowlisted national brokerage AND was surfaced by BOTH the buyer- and
+// leasing-lenses is a far more confident "commercial broker near this property"
+// answer than a lone generic result that only just cleared the commercial name
+// gate. The old ranking (pure rating × log(reviews)) ignored this entirely, so
+// a highly-rated but weakly-commercial listing outranked a two-lens national
+// brokerage with modest reviews.
+//
+// relevanceScore() returns a small multiplier (1.0 baseline → up to ~1.6) that
+// nudges stronger CRE matches upward. It's deliberately BOUNDED and multiplied
+// into the existing rating-weight so it re-orders near-comparable results and
+// breaks near-ties in favor of the better commercial match — WITHOUT letting a
+// zero-review brand leapfrog a genuinely well-reviewed office (a 0-review brand
+// still scores rating×log(1)=0 and stays below any reviewed listing). Pure +
+// deterministic; unit-tested offline with zero live Places billing.
+export function relevanceScore(r: AgentResult): number {
+ let s = 1;
+ // Allowlisted national/regional brokerage brand → the most confident signal.
+ if (hasCommercialBrand(r.name)) s += 0.3;
+ // Strong commercial name evidence (CRE/industrial/office/investment sales…).
+ if (COMMERCIAL_RE.test(r.name)) s += 0.2;
+ // Surfaced by BOTH the buyer- and leasing-lens → broader CRE relevance.
+ if (r.kinds.includes("buyer") && r.kinds.includes("leasing")) s += 0.1;
+ return s;
+}
+
+// --- Contact-based dedupe -------------------------------------------------
+// Google Places returns the SAME physical brokerage office under several of our
+// query lenses as DIFFERENT place ids (e.g. "CBRE" from the buyer lens and
+// "CBRE Investment Sales" from the investment-sales lens). Place-id union alone
+// can't collapse those, so we add a precision-first second pass:
+//
+// • PHONE is the authoritative per-office identity — one number rings one
+// desk. Same normalized phone ⇒ same office ⇒ merge.
+// • WEBSITE is a WEAKER signal: national brands share ONE domain across every
+// branch (all CBRE offices are cbre.com). So a website match only merges
+// when the phones DON'T contradict (at least one side is phone-less, or the
+// phones are equal). Two same-domain entries with two DIFFERENT phones are
+// two real branch offices and are kept apart.
+
+// Normalize a US phone to its 10 significant digits, or null if it isn't a
+// usable 10-digit number (precision-first: never key a merge on partial digits).
+export function normalizePhone(phone: string | null): string | null {
+ if (!phone) return null;
+ let d = phone.replace(/\D+/g, "");
+ if (d.length === 11 && d.startsWith("1")) d = d.slice(1);
+ return d.length === 10 ? d : null;
+}
+
+// Normalize a website to its lowercased host without a leading "www.", or null
+// if it doesn't parse. Full host (not just registrable domain) keeps it precise:
+// a duplicate of the same office carries the identical URL, so it still matches.
+export function normalizeHost(website: string | null): string | null {
+ if (!website) return null;
+ try {
+ const h = new URL(website).hostname.toLowerCase().replace(/^www\./, "");
+ return h || null;
+ } catch {
+ return null;
+ }
+}
+
+// Collapse near-identical listings that are the same office surfaced twice.
+// Keeps the first-seen record as canonical (preserving the ranked insertion
+// order), unions its `kinds`, adopts the richer rating/review pair, and fills
+// any missing contact fields from the duplicate. Pure + deterministic — no I/O,
+// so it's unit-tested offline with zero live Places billing.
+export function dedupeByContact(results: AgentResult[]): AgentResult[] {
+ const canon: AgentResult[] = [];
+ const byPhone = new Map<string, AgentResult>();
+ const byHost = new Map<string, AgentResult>();
+
+ const merge = (into: AgentResult, dup: AgentResult) => {
+ for (const k of dup.kinds) if (!into.kinds.includes(k)) into.kinds.push(k);
+ // Adopt the more-rated listing's rating/review pair (don't double-count).
+ if (dup.reviews > into.reviews) {
+ into.rating = dup.rating;
+ into.reviews = dup.reviews;
+ }
+ into.address ??= dup.address;
+ into.phone ??= dup.phone;
+ into.website ??= dup.website;
+ into.primaryType ??= dup.primaryType;
+ into.mapsUrl ??= dup.mapsUrl;
+ // Register any contact key the canonical only just adopted from the dup.
+ const p = normalizePhone(into.phone);
+ if (p) byPhone.set(p, into);
+ const h = normalizeHost(into.website);
+ if (h && !byHost.has(h)) byHost.set(h, into);
+ };
+
+ for (const r of results) {
+ const p = normalizePhone(r.phone);
+ const h = normalizeHost(r.website);
+
+ // 1. Same phone ⇒ same office (authoritative).
+ if (p && byPhone.has(p)) {
+ merge(byPhone.get(p)!, r);
+ continue;
+ }
+ // 2. Same website ⇒ merge ONLY when phones don't prove they're different
+ // offices (national brands share one domain across many branches).
+ if (h && byHost.has(h)) {
+ const c = byHost.get(h)!;
+ const cp = normalizePhone(c.phone);
+ if (!p || !cp || p === cp) {
+ merge(c, r);
+ continue;
+ }
+ }
+ // New canonical office.
+ canon.push(r);
+ if (p) byPhone.set(p, r);
+ if (h && !byHost.has(h)) byHost.set(h, r);
+ }
+ return canon;
+}
+
+export interface RawPlace {
+ id?: string;
+ displayName?: { text?: string };
+ formattedAddress?: string;
+ rating?: number;
+ userRatingCount?: number;
+ nationalPhoneNumber?: string;
+ websiteUri?: string;
+ googleMapsUri?: string;
+ businessStatus?: string;
+ primaryTypeDisplayName?: { text?: string };
+ primaryType?: string;
+ types?: string[];
+}
+
+function locationString(city?: string, state?: string, zip?: string): string {
+ return [city?.trim(), state?.trim(), zip?.trim()].filter(Boolean).join(", ");
+}
+
+// Precision-first commercial classifier. Google's `real_estate_agency` type does
+// NOT distinguish commercial from residential, so we REQUIRE positive commercial
+// evidence (a strong name signal or an allowlisted brokerage brand) to keep a
+// result, instead of defaulting to keep. Apply order:
+// 1. Off-topic Google type (travel/lodging/storage/…) → DROP regardless.
+// 2. Financial-advisory / wealth firm WITHOUT a real-estate anchor → DROP.
+// (Runs before the commercial keep because finance names carry ambiguous
+// "investments"/"advisors"/"capital markets" tokens that would otherwise
+// satisfy COMMERCIAL_RE — e.g. "Sterling Financial Advisors" is a wealth
+// firm, not a CRE broker. An allowlisted CRE brand overrides this.)
+// 3. CRE-adjacent SERVICE / trade firm WITHOUT an explicit broker anchor →
+// DROP. (Also runs before the commercial keep: appraisal/title/escrow/
+// construction/mortgage-lending/property-management names carry
+// "commercial"/"property" tokens that satisfy COMMERCIAL_RE but are NOT
+// brokers — e.g. "Sunbelt Commercial Construction". Broker anchor or brand
+// overrides.)
+// 3.5 Explicit RESIDENTIAL name whose only commercial evidence is a vertical-
+// AMBIGUOUS generic token (brokerage/advisors/leasing/investments) → DROP.
+// Runs before the positive keep so a residential firm ("Coldwell Banker
+// Residential Brokerage") isn't rescued by "brokerage" alone. A STRONG
+// commercial token or an allowlisted brand overrides.
+// 3.6 Professional PRACTICE office ("Law/Dental/Medical Office OF Dr. …") →
+// DROP. "office" is a CRE asset-class token that would otherwise keep it;
+// the "office of <person>" structure marks a practice, not a broker.
+// Narrow (requires the "of") so legit "Office Building"/"Office Leasing"
+// CRE names are untouched. Broker anchor or brand overrides.
+// 4. Strong commercial name signal OR allowlisted brand → KEEP (wins over
+// any residential signal, e.g. "Commercial Realty Advisors").
+// 5. Residential / irrelevant name signal → DROP.
+// 6. Residential-flavored place type → DROP.
+// 7. Generic real-estate result with no commercial evidence → DROP.
+// 8. Non-real-estate result with no signal either way → DROP (nothing kept it).
+export function isCommercial(p: RawPlace): boolean {
+ const name = p.displayName?.text ?? "";
+ const types = [p.primaryType ?? "", ...(p.types ?? [])];
+
+ // 1. Off-vertical place type → drop outright (catches the travel_agency mis-tag
+ // AND every underscore-compound retail type like furniture_store/shopping_mall).
+ if (types.some((t) => isOffTopicType(t))) return false;
+
+ const hasResidentialType = types.some((t) => RESIDENTIAL_TYPES.has(t.toLowerCase()));
+
+ // 2. Financial-advisory / wealth / securities firm with no real-estate anchor →
+ // drop. Guards against the broker↔finance vertical overlap that surfaces
+ // wealth firms on a "commercial real estate broker" text search.
+ if (isFinancialAdvisoryNoRE(name)) return false;
+
+ // 3. CRE-adjacent SERVICE / trade firm with no explicit broker anchor → drop.
+ // Catches appraisal/title/escrow/construction/mortgage-lending/inspection/
+ // property-management firms whose "commercial"/"property" tokens satisfy
+ // COMMERCIAL_RE but which are NOT buyer/leasing brokers. Runs before the
+ // positive keep for the same reason as the finance gate. An allowlisted
+ // brand or a broker anchor in the name overrides (handled inside).
+ if (isCreServiceNoBroker(name)) return false;
+
+ // 3.5 Explicit RESIDENTIAL name whose only commercial evidence is a vertical-
+ // AMBIGUOUS generic token (brokerage/advisors/leasing/investments) → drop.
+ // Runs before the positive keep for the same reason as the finance/service
+ // gates: otherwise "Coldwell Banker Residential Brokerage" is kept on
+ // "brokerage" alone. A STRONG commercial token or an allowlisted brand
+ // (checked inside) overrides, so "Commercial & Home Sales Group" and
+ // "CBRE Residential" still survive.
+ if (isResidentialDespiteGeneric(name)) return false;
+
+ // 3.6 Professional PRACTICE office ("Law/Dental/Medical Office of Dr. …") → drop.
+ // "office" is a CRE asset-class token that would otherwise keep these; the
+ // "office OF <person>" structure marks a practice, not a broker. Narrow by
+ // design — legit CRE "Office Building"/"Office Leasing" names have no "of".
+ if (isProfessionalPracticeOffice(name)) return false;
+
+ // 4. Positive commercial evidence wins over everything below.
+ if (COMMERCIAL_RE.test(name) || hasCommercialBrand(name)) return true;
+
+ // 5. Explicit residential / irrelevant name → drop.
+ if (RESIDENTIAL_RE.test(name)) return false;
+
+ // 6. Residential-flavored place type with no commercial evidence → drop.
+ if (hasResidentialType) return false;
+
+ // 7 & 8. No commercial evidence at all — whether or not it's a generic
+ // real_estate_agency, we can't confirm it's commercial, so drop it.
+ // (Precision > recall: the page promises commercial-only.)
+ return false;
+}
+
+async function textSearch(query: string, apiKey: string): Promise<RawPlace[]> {
+ const res = await fetch(ENDPOINT, {
+ method: "POST",
+ headers: {
+ "Content-Type": "application/json",
+ "X-Goog-Api-Key": apiKey,
+ "X-Goog-FieldMask": FIELD_MASK,
+ },
+ body: JSON.stringify({ textQuery: query, maxResultCount: 20, languageCode: "en", regionCode: "US" }),
+ cache: "no-store",
+ });
+ if (!res.ok) {
+ const detail = await res.text().catch(() => "");
+ throw new Error(`Places ${res.status}: ${detail.slice(0, 200)}`);
+ }
+ const json = (await res.json()) as { places?: RawPlace[] };
+ return json.places ?? [];
+}
+
+export async function searchCommercialAgents(
+ input: CommercialAgentSearch,
+): Promise<CommercialAgentResponse> {
+ const apiKey = process.env.GOOGLE_PLACES_API_KEY;
+ if (!apiKey) throw new Error("GOOGLE_PLACES_API_KEY is not configured");
+ const loc = locationString(input.city, input.state, input.zip);
+ if (!loc) throw new Error("A city, state, or ZIP is required");
+
+ const kinds = input.kinds.length ? input.kinds : (["buyer", "leasing"] as AgentKind[]);
+ const byId = new Map<string, AgentResult>();
+
+ // Build the full billed call plan: up to 3 lenses per kind. Each entry is one
+ // Places Text Search call, so `queries` = plan length = actual billed calls.
+ const plan = kinds.flatMap((kind) =>
+ KIND_LENSES[kind].map((lens) => ({ kind, query: `${lens} in ${loc}` })),
+ );
+ // Fire all lens fetches concurrently. Use allSettled so one failing lens
+ // (Google 429/500 on a single query) doesn't discard the results that DID
+ // succeed. `queries` counts only fulfilled calls — the honest billed count.
+ const settled = await Promise.allSettled(plan.map((p) => textSearch(p.query, apiKey)));
+ // If EVERY lens failed, surface the failure (route maps it to 502) rather than
+ // silently returning an empty "no agents found".
+ if (!settled.some((r) => r.status === "fulfilled")) {
+ const firstErr = settled.find((r) => r.status === "rejected") as PromiseRejectedResult | undefined;
+ throw firstErr?.reason instanceof Error ? firstErr.reason : new Error("Places search failed");
+ }
+ let queries = 0;
+
+ // Union results by place id, unioning the `kinds` array so a place surfaced by
+ // both a buyer- and leasing-lens carries both.
+ settled.forEach((r, i) => {
+ if (r.status !== "fulfilled") return;
+ queries++;
+ const places = r.value;
+ const kind = plan[i].kind;
+ for (const p of places) {
+ if (!p.id || !p.displayName?.text) continue;
+ if (p.businessStatus && p.businessStatus !== "OPERATIONAL") continue;
+ if (!isCommercial(p)) continue;
+ const existing = byId.get(p.id);
+ if (existing) {
+ if (!existing.kinds.includes(kind)) existing.kinds.push(kind);
+ continue;
+ }
+ byId.set(p.id, {
+ id: p.id,
+ name: p.displayName.text,
+ address: p.formattedAddress ?? null,
+ rating: typeof p.rating === "number" ? p.rating : null,
+ reviews: p.userRatingCount ?? 0,
+ phone: p.nationalPhoneNumber ?? null,
+ website: p.websiteUri ?? null,
+ mapsUrl: p.googleMapsUri ?? null,
+ primaryType: p.primaryTypeDisplayName?.text ?? null,
+ kinds: [kind],
+ });
+ }
+ });
+
+ // Collapse the same office surfaced under multiple lenses (same phone, or
+ // same website when phones don't contradict) BEFORE ranking.
+ const deduped = dedupeByContact([...byId.values()]);
+
+ // Rank by rating weighted by review volume AND commercial-relevance, then by
+ // relevance alone (surfaces a strong CRE match ahead of a weakly-commercial
+ // one at equal rating-weight), then rating, then review count. The relevance
+ // multiplier is bounded (≤~1.6) so it re-orders near-comparable results and
+ // breaks near-ties toward the better commercial match, but a 0-review listing
+ // still weighs rating×log(1)=0 and cannot leapfrog a genuinely reviewed office.
+ const results = deduped.sort((a, b) => {
+ const sa = (a.rating ?? 0) * Math.log((a.reviews ?? 0) + 1) * relevanceScore(a);
+ const sb = (b.rating ?? 0) * Math.log((b.reviews ?? 0) + 1) * relevanceScore(b);
+ if (sb !== sa) return sb - sa;
+ const ra = relevanceScore(a);
+ const rb = relevanceScore(b);
+ if (rb !== ra) return rb - ra;
+ if ((b.rating ?? 0) !== (a.rating ?? 0)) return (b.rating ?? 0) - (a.rating ?? 0);
+ return (b.reviews ?? 0) - (a.reviews ?? 0);
+ });
+
+ return { location: loc, kinds, results, queries };
+}
diff --git a/govarbitrage/src/lib/rate-limit.test.ts b/govarbitrage/src/lib/rate-limit.test.ts
new file mode 100644
index 0000000..6f8f9bf
--- /dev/null
+++ b/govarbitrage/src/lib/rate-limit.test.ts
@@ -0,0 +1,32 @@
+import { describe, expect, it, beforeEach } from "vitest";
+import { rateLimit, __resetRateLimit } from "./rate-limit";
+
+beforeEach(() => __resetRateLimit());
+
+describe("rateLimit (sliding window)", () => {
+ it("allows up to the limit, then blocks", () => {
+ const t0 = 1_000_000;
+ for (let i = 1; i <= 5; i++) {
+ expect(rateLimit("ip:a", 5, 60_000, t0).allowed).toBe(true);
+ }
+ const sixth = rateLimit("ip:a", 5, 60_000, t0);
+ expect(sixth.allowed).toBe(false);
+ expect(sixth.remaining).toBe(0);
+ expect(sixth.retryAfterSec).toBeGreaterThan(0);
+ });
+
+ it("resets after the window elapses", () => {
+ const t0 = 2_000_000;
+ for (let i = 0; i < 5; i++) rateLimit("ip:b", 5, 60_000, t0);
+ expect(rateLimit("ip:b", 5, 60_000, t0).allowed).toBe(false);
+ // A moment past the window: fresh bucket.
+ expect(rateLimit("ip:b", 5, 60_000, t0 + 60_001).allowed).toBe(true);
+ });
+
+ it("isolates buckets by key", () => {
+ const t0 = 3_000_000;
+ for (let i = 0; i < 5; i++) rateLimit("ip:c", 5, 60_000, t0);
+ expect(rateLimit("ip:c", 5, 60_000, t0).allowed).toBe(false);
+ expect(rateLimit("ip:d", 5, 60_000, t0).allowed).toBe(true);
+ });
+});
diff --git a/govarbitrage/src/lib/rate-limit.ts b/govarbitrage/src/lib/rate-limit.ts
new file mode 100644
index 0000000..c148844
--- /dev/null
+++ b/govarbitrage/src/lib/rate-limit.ts
@@ -0,0 +1,76 @@
+import { NextRequest, NextResponse } from "next/server";
+
+// Lightweight in-memory sliding-window rate limiter. Per-process (correct for a
+// single self-hosted `next start` instance). For multi-instance deployments,
+// swap the store for Redis (INCR + PEXPIRE) — same interface. `now` is injectable
+// for deterministic tests.
+
+interface Bucket {
+ count: number;
+ resetAt: number;
+}
+
+const store = new Map<string, Bucket>();
+let calls = 0;
+
+function sweep(now: number) {
+ for (const [k, b] of store) if (b.resetAt <= now) store.delete(k);
+}
+
+export interface RateResult {
+ allowed: boolean;
+ remaining: number;
+ limit: number;
+ retryAfterSec: number;
+}
+
+export function rateLimit(key: string, limit: number, windowMs: number, now = Date.now()): RateResult {
+ // Opportunistic cleanup so the map can't grow unbounded.
+ if (++calls % 500 === 0) sweep(now);
+
+ let b = store.get(key);
+ if (!b || b.resetAt <= now) {
+ b = { count: 0, resetAt: now + windowMs };
+ store.set(key, b);
+ }
+ b.count++;
+ const allowed = b.count <= limit;
+ return {
+ allowed,
+ remaining: Math.max(0, limit - b.count),
+ limit,
+ retryAfterSec: Math.ceil((b.resetAt - now) / 1000),
+ };
+}
+
+/** Best-effort client IP from proxy headers (nginx sets x-forwarded-for). */
+export function clientIp(req: NextRequest): string {
+ const fwd = req.headers.get("x-forwarded-for");
+ if (fwd) return fwd.split(",")[0].trim();
+ return req.headers.get("x-real-ip") || "unknown";
+}
+
+/**
+ * Build a rate-limit bucket key that prefers the UNFORGEABLE session subject
+ * over the spoofable client IP. A logged-in user is keyed on their verified
+ * `sub` so they can't reset their bucket (and run up the paid Places bill) by
+ * rotating X-Forwarded-For; only anonymous/last-resort callers fall back to IP.
+ * Pure + exported so the security invariant is unit-testable.
+ */
+export function sessionOrIpKey(prefix: string, userSub: string | null | undefined, ip: string): string {
+ return userSub ? `${prefix}:u:${userSub}` : `${prefix}:ip:${ip}`;
+}
+
+/** 429 response with a Retry-After header. */
+export function tooManyRequests(r: RateResult, extraHeaders?: Record<string, string>): NextResponse {
+ return NextResponse.json(
+ { error: "Too many requests. Please slow down." },
+ { status: 429, headers: { "Retry-After": String(r.retryAfterSec), ...extraHeaders } },
+ );
+}
+
+/** Test-only: clear all buckets. */
+export function __resetRateLimit() {
+ store.clear();
+ calls = 0;
+}
diff --git a/govarbitrage/src/lib/reports.ts b/govarbitrage/src/lib/reports.ts
new file mode 100644
index 0000000..093b7b6
--- /dev/null
+++ b/govarbitrage/src/lib/reports.ts
@@ -0,0 +1,139 @@
+import { prisma } from "@/lib/db";
+
+export interface GroupPerf {
+ key: string;
+ count: number;
+ avgRoi: number;
+ totalNet: number;
+}
+
+export interface Reports {
+ totalExpectedProfit: number;
+ avgRoi: number;
+ cashRequired: number;
+ inventoryByStatus: { status: string; count: number }[];
+ categoryPerformance: GroupPerf[];
+ sourcePerformance: GroupPerf[];
+ auctionSuccessRate: number; // won / (won + lost)
+ estimatedVsActual: { title: string; estimated: number; actual: number }[];
+}
+
+const num = (d: unknown) => (d == null ? 0 : Number(d));
+
+export async function getReports(): Promise<Reports> {
+ // Run all independent DB queries in parallel.
+ const [
+ costAgg,
+ inventoryGroups,
+ outcomeWonLost,
+ recentOutcomes,
+ ] = await Promise.all([
+ // Top-line aggregates over all listings that have a costBreakdown.
+ // These are intentionally all-time (not ACTIVE-scoped) so the report
+ // reflects the full pipeline value, not just currently live items.
+ prisma.costBreakdown.aggregate({
+ _sum: { expectedNetProfit: true, recommendedMaxBid: true, roi: true },
+ _avg: { roi: true },
+ _count: { id: true },
+ }),
+
+ // inventoryByStatus must cover ALL statuses (ACTIVE/ENDED/REMOVED) —
+ // scoping to ACTIVE would collapse all non-ACTIVE rows and lose the
+ // breakdown that the reports page displays.
+ prisma.listing.groupBy({
+ by: ["researchStatus"],
+ _count: { id: true },
+ }),
+
+ // Outcome counts for success-rate denominator (WON + LOST only).
+ prisma.auctionOutcome.groupBy({
+ by: ["status"],
+ _count: { id: true },
+ }),
+
+ // estimatedVsActual: only outcomes where actualSale is set. Bounded at
+ // 500 rows — the chart is not intended to render thousands of data points.
+ prisma.auctionOutcome.findMany({
+ where: { actualSale: { not: null } },
+ include: { listing: { select: { title: true } } },
+ orderBy: { createdAt: "desc" },
+ take: 500,
+ }),
+ ]);
+
+ // ---- top-line numbers ------------------------------------------------
+ const totalExpectedProfit = num(costAgg._sum.expectedNetProfit);
+ const cashRequired = num(costAgg._sum.recommendedMaxBid);
+ const avgRoi = num(costAgg._avg.roi);
+
+ // ---- inventory by research status ------------------------------------
+ const inventoryByStatus = inventoryGroups.map((g) => ({
+ status: g.researchStatus,
+ count: g._count.id,
+ }));
+
+ // ---- category / source performance -----------------------------------
+ // Prisma groupBy cannot traverse relations, so we cannot group CostBreakdown
+ // by Listing.category/source in a single DB aggregation without a schema
+ // change. Instead, fetch CostBreakdown rows with the needed Listing fields
+ // (capped at 5 000) and aggregate in JS — same semantics as the original
+ // but with a hard safety cap instead of an unbounded load.
+ const costRows = await prisma.costBreakdown.findMany({
+ select: {
+ roi: true,
+ expectedNetProfit: true,
+ listing: { select: { category: true, source: true } },
+ },
+ take: 5000,
+ });
+ if (costRows.length === 5000) {
+ // Not silent: category/source performance undercounts past the cap. Durable
+ // fix is a raw-SQL GROUP BY across the Listing↔CostBreakdown relation.
+ console.warn("[getReports] hit 5000-row cost cap; category/source performance may undercount");
+ }
+
+ const buildGroupPerf = (keyFn: (r: (typeof costRows)[number]) => string): GroupPerf[] => {
+ const m = new Map<string, { count: number; roiSum: number; net: number }>();
+ for (const r of costRows) {
+ const k = keyFn(r) || "Uncategorized";
+ const g = m.get(k) ?? { count: 0, roiSum: 0, net: 0 };
+ g.count++;
+ g.roiSum += num(r.roi);
+ g.net += num(r.expectedNetProfit);
+ m.set(k, g);
+ }
+ return [...m]
+ .map(([key, g]) => ({ key, count: g.count, avgRoi: g.roiSum / g.count, totalNet: g.net }))
+ .sort((a, b) => b.totalNet - a.totalNet);
+ };
+
+ const categoryPerformance = buildGroupPerf((r) => r.listing.category ?? "Uncategorized");
+ const sourcePerformance = buildGroupPerf((r) => r.listing.source);
+
+ // ---- auction success rate --------------------------------------------
+ let won = 0;
+ let lost = 0;
+ for (const g of outcomeWonLost) {
+ if (g.status === "WON") won = g._count.id;
+ if (g.status === "LOST") lost = g._count.id;
+ }
+ const auctionSuccessRate = won + lost > 0 ? won / (won + lost) : 0;
+
+ // ---- estimated vs actual --------------------------------------------
+ const estimatedVsActual = recentOutcomes.map((o) => ({
+ title: o.listing.title,
+ estimated: num(o.maxBidSet),
+ actual: num(o.actualSale),
+ }));
+
+ return {
+ totalExpectedProfit,
+ avgRoi,
+ cashRequired,
+ inventoryByStatus,
+ categoryPerformance,
+ sourcePerformance,
+ auctionSuccessRate,
+ estimatedVsActual,
+ };
+}
diff --git a/govarbitrage/src/lib/selling-avenues.ts b/govarbitrage/src/lib/selling-avenues.ts
new file mode 100644
index 0000000..f7189b2
--- /dev/null
+++ b/govarbitrage/src/lib/selling-avenues.ts
@@ -0,0 +1,168 @@
+// Structured "where to source" and "where to sell / validate demand before you
+// buy" reference, with real links. Rendered at /selling-avenues and mirrored in
+// docs/SELLING-AVENUES.md. Compliance-first: you may validate demand and take
+// CONTINGENT interest before winning, but you must never represent ownership of
+// an item you have not yet won.
+
+export interface Avenue {
+ name: string;
+ url: string;
+ note: string;
+}
+
+export interface AvenueGroup {
+ heading: string;
+ blurb: string;
+ avenues: Avenue[];
+}
+
+export const SOURCING: AvenueGroup[] = [
+ {
+ heading: "Federal surplus",
+ blurb: "US government agency surplus — often no buyer's premium.",
+ avenues: [
+ { name: "GSA Auctions", url: "https://gsaauctions.gov", note: "Federal personal property; frequently $0 buyer premium." },
+ { name: "GovDeals", url: "https://www.govdeals.com", note: "Largest gov-surplus marketplace (state/county/federal); ~10% premium." },
+ { name: "GovPlanet / AllSurplus", url: "https://www.govplanet.com", note: "Heavy equipment, vehicles, military rolling stock." },
+ ],
+ },
+ {
+ heading: "State & county surplus",
+ blurb: "State DGS/facilities programs and county auctions.",
+ avenues: [
+ { name: "Public Surplus", url: "https://www.publicsurplus.com", note: "Municipal, school-district and agency surplus nationwide." },
+ { name: "California DGS Surplus", url: "https://www.dgs.ca.gov/OFAM/Surplus-Property", note: "State of California surplus personal property." },
+ { name: "Texas Facilities Commission Surplus", url: "https://www.tfc.texas.gov/divisions/supportserv/prog/statesurplus/", note: "Texas state surplus program." },
+ { name: "Municibid", url: "https://www.municibid.com", note: "Local government surplus — vehicles, equipment." },
+ { name: "Bid4Assets", url: "https://www.bid4assets.com", note: "County tax-defaulted property + surplus." },
+ ],
+ },
+ {
+ heading: "University surplus",
+ blurb: "University asset-recovery programs, strong for lab & IT gear.",
+ avenues: [
+ { name: "UW Surplus", url: "https://surplus.uw.edu", note: "University of Washington." },
+ { name: "MSU Surplus Store", url: "https://surplus.msu.edu", note: "Michigan State University." },
+ { name: "UC Davis Aggie Surplus", url: "https://aggiesurplus.ucdavis.edu", note: "UC Davis." },
+ ],
+ },
+];
+
+export const SELLING: AvenueGroup[] = [
+ {
+ heading: "Retail resale marketplaces",
+ blurb: "Where the flipped item actually gets sold.",
+ avenues: [
+ { name: "eBay", url: "https://www.ebay.com", note: "Deepest buyer pool for used equipment; ~13% final-value fee." },
+ { name: "Facebook Marketplace", url: "https://www.facebook.com/marketplace", note: "Best for local, heavy, freight-averse items." },
+ { name: "Amazon Renewed", url: "https://www.amazon.com/renewed", note: "Refurb electronics at scale (approval required)." },
+ { name: "DOTmed / LabX", url: "https://www.dotmed.com", note: "Medical & lab equipment verticals — higher-value buyers." },
+ { name: "MachineryTrader", url: "https://www.machinerytrader.com", note: "Industrial / heavy equipment." },
+ ],
+ },
+ {
+ heading: "Wholesale & liquidation exits",
+ blurb: "Faster, lower-price exits when you don't want to retail one-by-one.",
+ avenues: [
+ { name: "B-Stock", url: "https://www.bstock.com", note: "Bulk B2B liquidation auctions." },
+ { name: "Liquidation.com", url: "https://www.liquidation.com", note: "Pallet/lot liquidation." },
+ ],
+ },
+ {
+ heading: "Demand validation (BEFORE you buy)",
+ blurb:
+ "Use these to prove resale price and demand before committing to a bid — this is research, not a sale.",
+ avenues: [
+ { name: "eBay Terapeak / Sold listings", url: "https://www.ebay.com/sh/research", note: "Free sold-price history — the single best comp source." },
+ { name: "Google Shopping", url: "https://shopping.google.com", note: "New-retail anchor across sellers." },
+ { name: "PriceCharting", url: "https://www.pricecharting.com", note: "For collectibles/electronics with model-level pricing." },
+ ],
+ },
+];
+
+// International sourcing + resale venues — government/industrial surplus auctions
+// and the dominant secondary marketplaces by region.
+export const INTERNATIONAL: AvenueGroup[] = [
+ {
+ heading: "Europe",
+ blurb: "Pan-European industrial/surplus auctions + high-end curated marketplaces.",
+ avenues: [
+ { name: "Troostwijk Auctions", url: "https://www.troostwijkauctions.com/en", note: "NL-based, industrial/machine surplus across 10+ EU countries." },
+ { name: "TBAuctions (BVA)", url: "https://tbauctions.com/brand-portfolio/", note: "Leading B2B auction group — BVA, Vavato, Klaravik, etc." },
+ { name: "Vavato", url: "https://www.vavato.com", note: "Belgium — industrial, overstock, bankruptcy goods." },
+ { name: "GovPlanet Europe", url: "https://www.govplanet.eu/", note: "Government + military surplus, EU." },
+ { name: "Catawiki", url: "https://www.catawiki.com/en/", note: "Curated high-end auctions (art, design, collectibles)." },
+ { name: "Ritchie Bros", url: "https://www.rbauction.com", note: "Global heavy equipment (EU yards)." },
+ ],
+ },
+ {
+ heading: "Hong Kong",
+ blurb: "Government surplus + the region's top resale app.",
+ avenues: [
+ { name: "HK Gov Logistics Dept — Public Auction", url: "https://www.gld.gov.hk/en/our-services/supplies/auction/", note: "Government surplus/unserviceable stores + confiscated goods." },
+ { name: "Carousell HK", url: "https://www.carousell.com.hk", note: "Dominant HK second-hand marketplace." },
+ ],
+ },
+ {
+ heading: "Japan",
+ blurb: "The largest JP auction + resale platforms (also host government auctions).",
+ avenues: [
+ { name: "Yahoo! Auctions Japan", url: "https://auctions.yahoo.co.jp", note: "Dominant JP auction site; also runs 官公庁 government auctions." },
+ { name: "Mercari Japan", url: "https://jp.mercari.com", note: "Largest JP C2C resale marketplace." },
+ ],
+ },
+ {
+ heading: "Australia",
+ blurb: "Government, council, ex-military and salvage auctions.",
+ avenues: [
+ { name: "GraysOnline", url: "https://www.graysonline.com", note: "Government/council/industrial + clearance auctions." },
+ { name: "Pickles", url: "https://www.pickles.com.au", note: "Vehicles, industrial, salvage, Defence surplus." },
+ { name: "Manheim Australia", url: "https://www.manheim.com.au", note: "Fleet + government vehicle auctions." },
+ ],
+ },
+ {
+ heading: "China (high-end & judicial)",
+ blurb: "Court-seized/judicial asset auctions + luxury/art houses in Beijing/Shanghai/Shenzhen.",
+ avenues: [
+ { name: "Taobao Judicial (阿里资产/司法拍卖)", url: "https://sf.taobao.com", note: "Alibaba court-seized asset auctions — property, vehicles, luxury." },
+ { name: "JD Auction (京东拍卖)", url: "https://auction.jd.com", note: "JD judicial + surplus auctions." },
+ { name: "Poly Auction", url: "https://www.polyauction.com", note: "High-end art/collectibles (Beijing/HK)." },
+ { name: "Alibaba", url: "https://www.alibaba.com", note: "Wholesale sourcing + resale reference pricing." },
+ ],
+ },
+];
+
+export interface ComplianceRule {
+ title: string;
+ body: string;
+}
+
+export const COMPLIANCE: ComplianceRule[] = [
+ {
+ title: "Never represent ownership before you win",
+ body:
+ "You do not own an auction lot until the auction closes in your favor and you have paid. Any marketing before that must be framed as CONTINGENT interest ('I intend to acquire this; contingent on winning'), never 'for sale now'.",
+ },
+ {
+ title: "eBay pre-sale rules",
+ body:
+ "eBay allows pre-sale listings only for items you have a firm commitment to supply and can ship within 40 business days (listing must state the ship date). You cannot list an auction lot you might lose. Practically: validate demand and collect contingent buyer interest off-platform, then list once you actually win and hold the item.",
+ },
+ {
+ title: "Contingent buyer interest is fine",
+ body:
+ "Collecting names, emails and non-binding offers on a 'buyer interest' page is compliant as long as it is clearly contingent and non-binding until you own the item. GovArbitrage's buyer workflow enforces the contingent flag and shows a disclaimer on every interest page.",
+ },
+ {
+ title: "Government auction terms bind you",
+ body:
+ "Gov surplus is sold AS-IS, often pickup-only, with removal deadlines and payment windows. Model those costs (pickup labor, freight, storage) before bidding — the cost engine does this. Missing a removal deadline can forfeit the item and your payment.",
+ },
+];
+
+// The worked example Steve gave: a dental chair bought cheap vs. new retail.
+export const WORKED_EXAMPLE = {
+ title: "Worked example — the dental chair",
+ body:
+ "An A-dec 511 dental patient chair sells new for ~$20,000. A county-clinic surplus unit in used-good condition might hammer at $500 on GovDeals. But 'buy $500, sell $20,000' is fiction. Realistic used A-dec 511 units sell for ~$6,000–9,000 (see eBay/DOTmed sold comps). Against that, subtract the 10% buyer's premium, ~$500 in freight (320 lb, pickup-only), pickup labor, testing, refurbishment, ~13% marketplace fees and payment fees. GovArbitrage runs exactly this math per listing and reports the honest expected net profit, ROI, and a recommended maximum bid — so you bid to a target return instead of a fantasy spread.",
+};
diff --git a/govarbitrage/src/lib/send-digest.ts b/govarbitrage/src/lib/send-digest.ts
new file mode 100644
index 0000000..dbd9983
--- /dev/null
+++ b/govarbitrage/src/lib/send-digest.ts
@@ -0,0 +1,88 @@
+import { prisma } from "@/lib/db";
+import { findHotDeals, type HotDeal } from "@/lib/hot-deals";
+import { sendNewsletterEmail, newsletterSendMode } from "@/lib/newsletter";
+import { persistDigestSnapshot, currentSlot } from "@/lib/digest-snapshot";
+
+// Digest send-to-list. Renders the same honesty-gated Top-10 the paid alerts
+// use and fans it out to every CONFIRMED subscriber, each with their own
+// one-click unsubscribe link (CAN-SPAM). All sends go through
+// sendNewsletterEmail(), which is TEST-logged by default and throws on an
+// unapproved live flip — so calling this is safe until Steve approves live
+// transport. Nothing schedules this yet; the launchd/cron wiring is part of
+// the gated go-live (see pending-approval memo).
+
+const fmtUsd = (n: number) => `$${Math.round(n).toLocaleString("en-US")}`;
+
+export function renderDigestText(deals: HotDeal[], unsubscribeUrl: string): string {
+ const lines: string[] = [
+ "GovArbitrage — Top-10 Deals Digest",
+ new Date().toLocaleString("en-US", { dateStyle: "medium", timeStyle: "short" }),
+ "",
+ ];
+
+ deals.forEach((d, i) => {
+ const where = [d.listing.locationCity, d.listing.locationState].filter(Boolean).join(", ");
+ lines.push(
+ `${i + 1}. ${d.listing.title}`,
+ ` Current bid ${fmtUsd(d.currentBid)} · rec. max bid ${fmtUsd(d.recMax)} · ` +
+ `est. resale ${fmtUsd(d.expectedSaleLow)} (${d.confidence} confidence)`,
+ ` Conservative ROI ${(d.roiConservative * 100).toFixed(0)}%${d.roiCapped ? " (capped)" : ""} · ${d.disclaimer}`,
+ ...(d.listing.sourceUrl ? [` ${d.listing.sourceUrl}`] : []),
+ ...(where ? [` Location: ${where}`] : []),
+ ""
+ );
+ });
+
+ if (!deals.length) lines.push("No deals cleared the hot-deal gate this run.", "");
+
+ lines.push(
+ "—",
+ "Every figure above is confidence-discounted and capped, never inflated.",
+ "Estimates are heuristic — always verify comps before bidding.",
+ "",
+ `Unsubscribe (one click, effective immediately): ${unsubscribeUrl}`
+ );
+ return lines.join("\n");
+}
+
+export interface DigestSendResult {
+ mode: "test" | "live";
+ subscribers: number;
+ sent: number;
+ errors: number;
+ dealCount: number;
+}
+
+export async function sendDigestToSubscribers(opts: { baseUrl?: string; limit?: number } = {}): Promise<DigestSendResult> {
+ const baseUrl = opts.baseUrl || process.env.NEWSLETTER_BASE_URL;
+ // Never let a live send fabricate localhost unsubscribe links — a broken
+ // unsubscribe URL in a real email is a CAN-SPAM problem, not a dev nit.
+ if (!baseUrl || (newsletterSendMode() === "live" && !baseUrl.startsWith("https://"))) {
+ throw new Error("sendDigestToSubscribers needs a public NEWSLETTER_BASE_URL (https) — refusing to build localhost links.");
+ }
+ const deals = await findHotDeals({ limit: opts.limit ?? 10 });
+
+ // Freeze this edition for the public /deals archive — even if every send
+ // below fails, the archive records what the digest showed at send time.
+ await persistDigestSnapshot(currentSlot(), deals);
+
+ const subscribers = await prisma.subscriber.findMany({ where: { status: "CONFIRMED" } });
+
+ let sent = 0;
+ let errors = 0;
+ for (const sub of subscribers) {
+ const unsubscribeUrl = `${baseUrl}/api/newsletter/unsubscribe?token=${sub.unsubscribeToken}`;
+ try {
+ await sendNewsletterEmail({
+ to: sub.email,
+ subject: `Top-10 gov-surplus deals — ${new Date().toLocaleDateString("en-US", { month: "short", day: "numeric" })}`,
+ text: renderDigestText(deals, unsubscribeUrl),
+ });
+ sent++;
+ } catch {
+ errors++;
+ }
+ }
+
+ return { mode: newsletterSendMode(), subscribers: subscribers.length, sent, errors, dealCount: deals.length };
+}
diff --git a/govarbitrage/src/lib/session.ts b/govarbitrage/src/lib/session.ts
new file mode 100644
index 0000000..0e56207
--- /dev/null
+++ b/govarbitrage/src/lib/session.ts
@@ -0,0 +1,42 @@
+import { SignJWT, jwtVerify } from "jose";
+
+// JWT session tokens (HS256) signed with AUTH_SECRET. Pure-JS (jose / Web
+// Crypto) so this module works in BOTH the Edge middleware and Node handlers.
+
+export const SESSION_COOKIE = "ga_session";
+export const SESSION_MAX_AGE = 60 * 60 * 24 * 7; // 7 days
+
+export interface SessionPayload {
+ sub: string; // user id
+ email: string;
+ role: string; // ADMIN | ANALYST | VIEWER
+}
+
+function secret(): Uint8Array {
+ const s = process.env.AUTH_SECRET;
+ // The dev fallback is in the repo — a production deploy that forgot to set
+ // AUTH_SECRET would mint forge-trivial sessions. Refuse to run instead.
+ if (!s && process.env.NODE_ENV === "production") {
+ throw new Error("AUTH_SECRET must be set in production — refusing to sign sessions with the dev fallback.");
+ }
+ return new TextEncoder().encode(s || "dev-only-change-me");
+}
+
+export async function createSession(payload: SessionPayload): Promise<string> {
+ return new SignJWT({ email: payload.email, role: payload.role })
+ .setProtectedHeader({ alg: "HS256" })
+ .setSubject(payload.sub)
+ .setIssuedAt()
+ .setExpirationTime(`${SESSION_MAX_AGE}s`)
+ .sign(secret());
+}
+
+export async function verifySession(token: string): Promise<SessionPayload | null> {
+ try {
+ const { payload } = await jwtVerify(token, secret());
+ if (!payload.sub || !payload.email) return null;
+ return { sub: payload.sub, email: payload.email as string, role: (payload.role as string) ?? "VIEWER" };
+ } catch {
+ return null;
+ }
+}
diff --git a/govarbitrage/src/lib/site.ts b/govarbitrage/src/lib/site.ts
new file mode 100644
index 0000000..6062e18
--- /dev/null
+++ b/govarbitrage/src/lib/site.ts
@@ -0,0 +1,9 @@
+// Public origin for canonical URLs, sitemap, robots, and OG tags.
+// NEWSLETTER_BASE_URL is already the env of record for public links
+// (confirm/unsubscribe emails use it) — reuse it rather than adding a twin.
+export function publicBaseUrl(): string {
+ const env = process.env.NEWSLETTER_BASE_URL;
+ if (env) return env.replace(/\/+$/, "");
+ if (process.env.NODE_ENV === "production") return "https://auctions.agentabrams.com";
+ return "http://localhost:3737";
+}
diff --git a/govarbitrage/src/lib/ssrf-guard.ts b/govarbitrage/src/lib/ssrf-guard.ts
new file mode 100644
index 0000000..8e76593
--- /dev/null
+++ b/govarbitrage/src/lib/ssrf-guard.ts
@@ -0,0 +1,104 @@
+import { lookup } from "node:dns/promises";
+
+// SSRF guard for the URL importer. Validates that a user-supplied URL points at
+// a publicly-routable host before Playwright is pointed at it, blocking cloud
+// metadata endpoints (169.254.169.254 / metadata.google.internal), localhost,
+// and RFC1918 / loopback / link-local / ULA ranges. Node-runtime only (uses
+// node:dns/promises) — do NOT import from Edge middleware.
+
+function ipv4ToInt(ip: string): number | null {
+ const parts = ip.split(".");
+ if (parts.length !== 4) return null;
+ let n = 0;
+ for (const p of parts) {
+ if (!/^\d+$/.test(p)) return null;
+ const o = Number(p);
+ if (o < 0 || o > 255) return null;
+ n = n * 256 + o;
+ }
+ return n >>> 0;
+}
+
+function isPrivateIPv4(ip: string): boolean {
+ const n = ipv4ToInt(ip);
+ if (n === null) return false;
+ const inRange = (base: string, bits: number) => {
+ const baseInt = ipv4ToInt(base)!;
+ const mask = bits === 0 ? 0 : (0xffffffff << (32 - bits)) >>> 0;
+ return (n & mask) === (baseInt & mask);
+ };
+ return (
+ inRange("10.0.0.0", 8) ||
+ inRange("172.16.0.0", 12) ||
+ inRange("192.168.0.0", 16) ||
+ inRange("127.0.0.0", 8) ||
+ inRange("169.254.0.0", 16) ||
+ inRange("0.0.0.0", 8)
+ );
+}
+
+function isPrivateIPv6(addr: string): boolean {
+ const ip = addr.toLowerCase().replace(/^\[|\]$/g, "");
+ // IPv4-mapped (::ffff:a.b.c.d) — defer to the IPv4 check.
+ const mapped = ip.match(/^::ffff:(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})$/);
+ if (mapped) return isPrivateIPv4(mapped[1]);
+ if (ip === "::1" || ip === "::") return true; // loopback / unspecified
+ const first = ip.split(":")[0];
+ const hi = parseInt((first || "0").padStart(4, "0"), 16);
+ if ((hi & 0xffc0) === 0xfe80) return true; // fe80::/10 link-local (fe80..febf)
+ if ((hi & 0xfe00) === 0xfc00) return true; // fc00::/7 unique-local (fc00..fdff)
+ return false;
+}
+
+function isBlockedAddress(addr: string): boolean {
+ return addr.includes(":") ? isPrivateIPv6(addr) : isPrivateIPv4(addr);
+}
+
+/**
+ * Parse and validate a user-supplied URL, throwing when it targets a
+ * non-public destination. Returns the parsed URL on success.
+ */
+export async function assertPublicUrl(rawUrl: string): Promise<URL> {
+ let url: URL;
+ try {
+ url = new URL(rawUrl);
+ } catch {
+ throw new Error("Invalid URL");
+ }
+
+ if (url.protocol !== "http:" && url.protocol !== "https:") {
+ throw new Error(`Blocked URL scheme: ${url.protocol}`);
+ }
+
+ const host = url.hostname.toLowerCase().replace(/^\[|\]$/g, "");
+ if (
+ host === "localhost" ||
+ host.endsWith(".internal") ||
+ host === "metadata.google.internal"
+ ) {
+ throw new Error(`Blocked hostname: ${host}`);
+ }
+
+ // A literal IP in the host still needs to pass the range check.
+ if (isBlockedAddress(host)) {
+ throw new Error(`Blocked private address: ${host}`);
+ }
+
+ // Resolve the hostname and reject if ANY address is private/loopback/etc.
+ let addresses: { address: string }[];
+ try {
+ addresses = await lookup(host, { all: true });
+ } catch {
+ throw new Error(`Could not resolve hostname: ${host}`);
+ }
+ if (addresses.length === 0) {
+ throw new Error(`Could not resolve hostname: ${host}`);
+ }
+ for (const { address } of addresses) {
+ if (isBlockedAddress(address)) {
+ throw new Error(`Blocked private address for ${host}: ${address}`);
+ }
+ }
+
+ return url;
+}
diff --git a/govarbitrage/src/lib/tiers.ts b/govarbitrage/src/lib/tiers.ts
new file mode 100644
index 0000000..415f47e
--- /dev/null
+++ b/govarbitrage/src/lib/tiers.ts
@@ -0,0 +1,103 @@
+// 3-tier paid-alerts SaaS config — the single source of truth for pricing,
+// features, and enforceable data limits. Prices are DRAFT (Steve sets final);
+// live charging stays gated (billing runs TEST/mock until a live key is set).
+import type { Tier } from "@prisma/client";
+
+export interface TierDef {
+ key: Tier;
+ label: string;
+ priceUsd: number; // per month; 0 = free
+ blurb: string;
+ features: string[];
+ limits: {
+ maxListings: number; // rows the dashboard/API returns (Infinity via a big number)
+ freshnessDelayHours: number; // Free sees deals aged by this many hours (not real-time)
+ showMoneyMath: boolean; // recommended max bid / ROI / net profit visible?
+ hotDealAlerts: boolean; // receive the 🔥 email alerts?
+ realtime: boolean; // real-time vs delayed
+ allSources: boolean; // all 8 feeds vs a limited subset
+ };
+}
+
+// env override for prices without a code change: TIER_PRICE_STANDARD, TIER_PRICE_PREMIUM
+function price(env: string, dflt: number): number {
+ const v = process.env[env];
+ const n = v == null ? NaN : Number(v);
+ return Number.isFinite(n) && n >= 0 ? n : dflt;
+}
+
+export const TIERS: Record<Tier, TierDef> = {
+ FREE: {
+ key: "FREE",
+ label: "Free",
+ priceUsd: 0,
+ blurb: "Current listings and full money-math analysis.",
+ features: [
+ "Browse current auction listings",
+ "Full money-math: recommended max bid, ROI, net profit, scores",
+ "Up to 25 listings per page",
+ "Daily newsletter available after subscription confirmation",
+ ],
+ limits: {
+ maxListings: 25,
+ freshnessDelayHours: 24,
+ showMoneyMath: true,
+ hotDealAlerts: false,
+ realtime: false,
+ allSources: false,
+ },
+ },
+ STANDARD: {
+ key: "STANDARD",
+ label: "Standard",
+ priceUsd: price("TIER_PRICE_STANDARD", 19),
+ blurb: "Preview plan with up to 500 listings per page.",
+ features: [
+ "Current listings and full money-math analysis",
+ "Up to 500 listings per page",
+ "Available sources are accessible on every plan",
+ "Daily newsletter available after subscription confirmation",
+ ],
+ limits: {
+ maxListings: 500,
+ freshnessDelayHours: 0,
+ showMoneyMath: true,
+ hotDealAlerts: true,
+ realtime: true,
+ allSources: true,
+ },
+ },
+ PREMIUM: {
+ key: "PREMIUM",
+ label: "Premium",
+ priceUsd: price("TIER_PRICE_PREMIUM", 49),
+ blurb: "Preview plan with a higher listing page-size allowance.",
+ features: [
+ "Current listings and full money-math analysis",
+ "Higher listing page-size allowance",
+ "Results remain subject to search and sorting limits",
+ "Daily newsletter available after subscription confirmation",
+ ],
+ limits: {
+ maxListings: 1_000_000,
+ freshnessDelayHours: 0,
+ showMoneyMath: true,
+ hotDealAlerts: true,
+ realtime: true,
+ allSources: true,
+ },
+ },
+};
+
+export const TIER_ORDER: Tier[] = ["FREE", "STANDARD", "PREMIUM"];
+
+export function tierDef(t: Tier | string | null | undefined): TierDef {
+ return TIERS[(t as Tier) in TIERS ? (t as Tier) : "FREE"];
+}
+
+/** Map a Stripe price/lookup to a tier (used by the webhook). */
+export function tierFromPriceUsd(usd: number): Tier {
+ if (usd >= TIERS.PREMIUM.priceUsd && TIERS.PREMIUM.priceUsd > 0) return "PREMIUM";
+ if (usd >= TIERS.STANDARD.priceUsd && TIERS.STANDARD.priceUsd > 0) return "STANDARD";
+ return "FREE";
+}
diff --git a/govarbitrage/src/lib/utils.ts b/govarbitrage/src/lib/utils.ts
new file mode 100644
index 0000000..d39b59b
--- /dev/null
+++ b/govarbitrage/src/lib/utils.ts
@@ -0,0 +1,50 @@
+import { clsx, type ClassValue } from "clsx";
+import { twMerge } from "tailwind-merge";
+
+/** shadcn/ui-style className combiner. */
+export function cn(...inputs: ClassValue[]) {
+ return twMerge(clsx(inputs));
+}
+
+/** Round to whole cents (avoids float drift; all engine money is in dollars). */
+export function round2(n: number): number {
+ return Math.round((n + Number.EPSILON) * 100) / 100;
+}
+
+export function clamp(n: number, lo: number, hi: number): number {
+ return Math.min(hi, Math.max(lo, n));
+}
+
+const usd = new Intl.NumberFormat("en-US", {
+ style: "currency",
+ currency: "USD",
+ maximumFractionDigits: 0,
+});
+const usdCents = new Intl.NumberFormat("en-US", {
+ style: "currency",
+ currency: "USD",
+});
+
+export function formatMoney(n: number | null | undefined, cents = false): string {
+ if (n === null || n === undefined || Number.isNaN(n)) return "—";
+ return cents ? usdCents.format(n) : usd.format(n);
+}
+
+export function formatPercent(ratio: number | null | undefined, digits = 0): string {
+ if (ratio === null || ratio === undefined || Number.isNaN(ratio)) return "—";
+ return `${(ratio * 100).toFixed(digits)}%`;
+}
+
+/** Human countdown to a future date, e.g. "2d 4h" or "Closed". */
+export function countdown(to: Date | string | null | undefined, now = new Date()): string {
+ if (!to) return "—";
+ const target = typeof to === "string" ? new Date(to) : to;
+ const ms = target.getTime() - now.getTime();
+ if (ms <= 0) return "Closed";
+ const d = Math.floor(ms / 86_400_000);
+ const h = Math.floor((ms % 86_400_000) / 3_600_000);
+ const m = Math.floor((ms % 3_600_000) / 60_000);
+ if (d > 0) return `${d}d ${h}h`;
+ if (h > 0) return `${h}h ${m}m`;
+ return `${m}m`;
+}
diff --git a/govarbitrage/src/middleware.test.ts b/govarbitrage/src/middleware.test.ts
new file mode 100644
index 0000000..fb63baf
--- /dev/null
+++ b/govarbitrage/src/middleware.test.ts
@@ -0,0 +1,98 @@
+import { describe, expect, it, beforeEach, vi } from "vitest";
+import { NextRequest } from "next/server";
+
+// TK-11476 — verify TK-11466 FIX 2 (presentedAdminBasicAuth) actually holds:
+// with the shared-password wall OFF (BASIC_AUTH=""), an anonymous caller on a
+// protected path must fall through to /login (pages) / 401 JSON (APIs) and
+// must NEVER be minted an ADMIN session. BASIC_AUTH is read as a top-level
+// module constant, so each case resets modules and re-imports after setting
+// the env var it needs to exercise.
+async function loadMiddleware() {
+ const mod = await import("./middleware");
+ return mod.middleware;
+}
+
+function req(path: string, headers: Record<string, string> = {}) {
+ return new NextRequest(new Request(`https://auctions.agentabrams.com${path}`, { headers }));
+}
+
+const basicHeader = (creds: string) => "Basic " + Buffer.from(creds).toString("base64");
+
+beforeEach(() => {
+ vi.resetModules();
+ process.env.AUTH_SECRET = "test-secret-please-change";
+ delete process.env.FLEET_SSO_SECRET;
+ delete process.env.IMPORT_TOKEN;
+});
+
+describe("middleware — wall OFF (BASIC_AUTH empty)", () => {
+ beforeEach(() => {
+ process.env.BASIC_AUTH = "";
+ });
+
+ it("anon GET / redirects to /login and mints no session cookie", async () => {
+ const middleware = await loadMiddleware();
+ const res = await middleware(req("/"));
+ expect(res.status).toBe(307);
+ expect(res.headers.get("location")).toContain("/login");
+ expect(res.cookies.get("ga_session")).toBeUndefined();
+ });
+
+ it("anon GET /api/credentials returns 401 JSON and mints no session cookie", async () => {
+ const middleware = await loadMiddleware();
+ const res = await middleware(req("/api/credentials"));
+ expect(res.status).toBe(401);
+ expect(res.cookies.get("ga_session")).toBeUndefined();
+ });
+
+ it("regression guard: a bogus Authorization header still does not mint ADMIN", async () => {
+ const middleware = await loadMiddleware();
+ const res = await middleware(req("/", { Authorization: basicHeader("anyone:anything") }));
+ expect(res.status).toBe(307);
+ expect(res.headers.get("location")).toContain("/login");
+ expect(res.cookies.get("ga_session")).toBeUndefined();
+ });
+
+ it("regression guard: even the correct default creds do not mint ADMIN once the wall is off", async () => {
+ // The exact TK-11466 bypass shape: basicAuthOk() would have been true
+ // unconditionally with the wall off. presentedAdminBasicAuth() must stay
+ // false regardless of what's presented when BASIC_AUTH itself is empty.
+ const middleware = await loadMiddleware();
+ const res = await middleware(req("/", { Authorization: basicHeader("admin:DW2024!") }));
+ expect(res.status).toBe(307);
+ expect(res.headers.get("location")).toContain("/login");
+ expect(res.cookies.get("ga_session")).toBeUndefined();
+ });
+
+ it("public paths (e.g. /pricing) stay reachable anonymously", async () => {
+ const middleware = await loadMiddleware();
+ const res = await middleware(req("/pricing"));
+ expect(res.status).toBe(200);
+ });
+});
+
+describe("middleware — wall ON (BASIC_AUTH set, unchanged behavior)", () => {
+ beforeEach(() => {
+ process.env.BASIC_AUTH = "admin:DW2024!";
+ });
+
+ it("anon GET / (no credentials) is 401'd by the wall", async () => {
+ const middleware = await loadMiddleware();
+ const res = await middleware(req("/"));
+ expect(res.status).toBe(401);
+ expect(res.headers.get("www-authenticate")).toContain("Basic");
+ });
+
+ it("the real shared credential still auto-mints an ADMIN session", async () => {
+ const middleware = await loadMiddleware();
+ const res = await middleware(req("/", { Authorization: basicHeader("admin:DW2024!") }));
+ expect(res.status).toBe(200);
+ expect(res.cookies.get("ga_session")?.value).toBeTruthy();
+ });
+
+ it("a wrong credential is 401'd, not redirected", async () => {
+ const middleware = await loadMiddleware();
+ const res = await middleware(req("/", { Authorization: basicHeader("admin:wrong") }));
+ expect(res.status).toBe(401);
+ });
+});
diff --git a/govarbitrage/src/middleware.ts b/govarbitrage/src/middleware.ts
new file mode 100644
index 0000000..c4271e0
--- /dev/null
+++ b/govarbitrage/src/middleware.ts
@@ -0,0 +1,142 @@
+import { NextRequest, NextResponse } from "next/server";
+import { SESSION_COOKIE, SESSION_MAX_AGE, createSession, verifySession } from "@/lib/session";
+import { FLEET_SSO_COOKIE, fleetSsoOk } from "@/lib/fleet-sso";
+
+// Public paths that never require a session:
+// - /login and the auth API
+// - /b/<slug> public contingent buyer-interest pages
+// - the public buyer-lead submission endpoint (anonymous buyers)
+const PUBLIC = [
+ /^\/login(?:\/|$)/,
+ /^\/api\/auth\//,
+ /^\/b\//,
+ // Public read-only catalog API the iOS app + any browser call anonymously.
+ // Same data for every client (money-math is tier-driven, FREE includes it);
+ // GET-only routes, so exposing them carries no write surface. Keeping this
+ // genuinely public — no shared password, no auto-minted session — is what
+ // makes the app's data-access behavior identical and demonstrable to review.
+ /^\/api\/listings(?:\/|$)/,
+ /^\/api\/listings\/[^/]+\/buyer-lead$/,
+ /^\/pricing(?:\/|$)/, // public marketing page (upgrade CTA)
+ /^\/api\/billing\/webhook$/, // Stripe calls this server-to-server, no session
+ /^\/newsletter(?:\/|$)/, // public digest subscribe landing
+ /^\/api\/newsletter\/(?:subscribe|confirm|unsubscribe)$/, // anonymous subscribers + email links
+ /^\/deals(?:\/|$)/, // public SEO digest archive (frozen snapshots, teaser numbers only)
+ /^\/selling-avenues(?:\/|$)/, // public SEO reference (where/how to resell surplus) — crawlable, in sitemap
+ /^\/privacy(?:\/|$)/, // public privacy policy — REQUIRED reachable w/o login for the App Store listing URL
+ /^\/sitemap\.xml$/, // crawlers (robots.txt is already excluded by the matcher)
+ /^\/robots\.txt$/,
+];
+
+function isPublic(pathname: string): boolean {
+ return PUBLIC.some((re) => re.test(pathname));
+}
+
+// Outer un/pw wall for the whole site (ideas/fleet-unified admin:DW2024!).
+// Override with BASIC_AUTH="user:pass"; BASIC_AUTH="" disables. Runs in Edge → use atob, not Buffer.
+const BASIC_AUTH = process.env.BASIC_AUTH ?? "admin:DW2024!";
+function basicAuthOk(req: NextRequest): boolean {
+ if (!BASIC_AUTH) return true;
+ const m = (req.headers.get("authorization") || "").match(/^Basic\s+(.+)$/i);
+ if (!m) return false;
+ try { return atob(m[1]) === BASIC_AUTH; } catch { return false; }
+}
+
+// True ONLY when a real, matching Basic-Auth credential was presented.
+// Unlike basicAuthOk(), this is FALSE when BASIC_AUTH is empty (wall disabled):
+// disabling the shared-password wall must never be treated as "logged in as admin".
+function presentedAdminBasicAuth(req: NextRequest): boolean {
+ if (!BASIC_AUTH) return false;
+ const m = (req.headers.get("authorization") || "").match(/^Basic\s+(.+)$/i);
+ if (!m) return false;
+ try { return atob(m[1]) === BASIC_AUTH; } catch { return false; }
+}
+
+export async function middleware(req: NextRequest) {
+ const { pathname } = req.nextUrl;
+
+ // Machine callers that cannot present a password bypass the wall:
+ // Stripe's server-to-server webhook and the extension/CSV import-token clients.
+ const importToken = process.env.IMPORT_TOKEN;
+ const machineOk =
+ pathname === "/api/billing/webhook" ||
+ (!!importToken && req.headers.get("x-import-token") === importToken);
+ // Public paths (login, privacy, the anonymous read API, SEO pages) must be
+ // reachable without the shared admin wall — otherwise the "public" API would
+ // silently require a password only insiders know, the kind of hidden gate
+ // Guideline 5.6 flags. The wall still guards every non-public surface.
+ if (!machineOk && !isPublic(pathname) && !basicAuthOk(req)) {
+ return new NextResponse("auth required", {
+ status: 401,
+ headers: { "WWW-Authenticate": 'Basic realm="GovArbitrage"' },
+ });
+ }
+
+ if (isPublic(pathname)) return NextResponse.next();
+
+ // Valid session cookie?
+ const token = req.cookies.get(SESSION_COOKIE)?.value;
+ const session = token ? await verifySession(token) : null;
+
+ // Machine clients (extension / CSV import) may present the import token.
+ const hasImportToken = !!importToken && req.headers.get("x-import-token") === importToken;
+
+ if (session || hasImportToken) return NextResponse.next();
+
+ // Basic-auth IS the login (Steve 2026-07-27: "my basic un and pw, not the email
+ // login"). Any human who cleared the outer un/pw wall (the BASIC_AUTH creds) is
+ // treated as ADMIN — we mint a real ADMIN session here so the app never bounces to the
+ // email /login form. basicAuthOk is already true for every non-public/non-machine
+ // request that reached this line (the wall 401s otherwise); the guard just makes
+ // sure a machine-token-only caller doesn't get an admin cookie.
+ if (presentedAdminBasicAuth(req)) {
+ const ga = await createSession({ sub: "basic-admin", email: "admin@agentabrams.com", role: "ADMIN" });
+ const res = NextResponse.next();
+ res.cookies.set(SESSION_COOKIE, ga, {
+ httpOnly: true,
+ secure: true,
+ sameSite: "lax",
+ path: "/",
+ maxAge: SESSION_MAX_AGE,
+ });
+ return res;
+ }
+
+ // Fleet single-sign-on: a valid shared *.agentabrams.com "aafleet" cookie
+ // establishes an ANALYST session here (operational — full data + imports +
+ // buyer pages), so one fleet login carries into auctions with no second form.
+ // The tier-0 credential surface still requires ADMIN, reached only via the
+ // local username/password login. Rationale: the aafleet token carries no
+ // per-user identity, audience, or role, so a leaked sibling-site cookie must
+ // not unlock this app's encrypted provider credentials or Stripe/billing
+ // state (DTD 2026-07-25, verdict B — scoped below ADMIN; bumped VIEWER→ANALYST
+ // per Steve so the fleet view is operational, not read-only).
+ // To fully sign out, log out at auth.agentabrams.com (clearing ga_session
+ // alone would just be re-minted from aafleet).
+ if (await fleetSsoOk(req.cookies.get(FLEET_SSO_COOKIE)?.value)) {
+ const ga = await createSession({ sub: "fleet-sso", email: "fleet@agentabrams.com", role: "ANALYST" });
+ const res = NextResponse.next();
+ res.cookies.set(SESSION_COOKIE, ga, {
+ httpOnly: true,
+ secure: true,
+ sameSite: "lax",
+ path: "/",
+ maxAge: SESSION_MAX_AGE,
+ });
+ return res;
+ }
+
+ // API → 401 JSON; page → redirect to login with a return path.
+ if (pathname.startsWith("/api/")) {
+ return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
+ }
+ const url = req.nextUrl.clone();
+ url.pathname = "/login";
+ url.searchParams.set("next", pathname);
+ return NextResponse.redirect(url);
+}
+
+export const config = {
+ // Run on everything except Next internals + static asset files.
+ matcher: ["/((?!_next/static|_next/image|favicon.ico|.*\\.(?:png|jpg|jpeg|svg|gif|ico|css|js|map|txt)$).*)"],
+};
diff --git a/govarbitrage/src/pipeline/research.ts b/govarbitrage/src/pipeline/research.ts
new file mode 100644
index 0000000..b541452
--- /dev/null
+++ b/govarbitrage/src/pipeline/research.ts
@@ -0,0 +1,257 @@
+import { prisma } from "@/lib/db";
+import { identifyProduct } from "@/lib/ai";
+import { estimateDemand } from "@/engines/demand";
+import { computeValuation } from "@/engines/valuation";
+import { computeCosts } from "@/engines/costs";
+import { computeScores } from "@/engines/scoring";
+import { estimateFreight } from "@/engines/freight";
+import { clamp } from "@/lib/utils";
+import type { ConditionKey, SourceKey } from "@/engines/types";
+
+export interface ResearchOptions {
+ /** Run the local AI identifier (Ollama). Off by default for deterministic seeds. */
+ useAI?: boolean;
+ /** Deterministic overrides (used by the seeder to inject realistic anchors). */
+ anchor?: { newRetail?: number; demandScore?: number };
+ comparables?: {
+ kind: "SOLD" | "ACTIVE" | "RETAIL";
+ title: string;
+ price: number;
+ url?: string;
+ source?: string;
+ soldAt?: Date;
+ }[];
+ /** Persist AI-identified manufacturer/model back onto the listing. */
+ writeIdentity?: boolean;
+}
+
+/** Infer local-pickup requirement from weight + auction terms. */
+function inferLocalPickup(weightLbs: number, terms?: string | null): boolean {
+ const t = (terms || "").toLowerCase();
+ if (t.includes("pickup only") || t.includes("pick up only") || t.includes("no shipping")) return true;
+ if (t.includes("shipping available") || t.includes("will ship")) return false;
+ return weightLbs > 300; // heavy lots are usually pickup-only at gov auctions
+}
+
+/**
+ * Full research run for one listing: identify → value → cost → score → persist.
+ * Idempotent (upserts), safe to re-run. Returns a compact summary.
+ */
+export async function runResearch(listingId: string, opts: ResearchOptions = {}) {
+ const listing = await prisma.listing.findUnique({ where: { id: listingId } });
+ if (!listing) throw new Error(`Listing ${listingId} not found`);
+
+ await prisma.listing.update({
+ where: { id: listingId },
+ data: { researchStatus: "IN_PROGRESS" },
+ });
+
+ const text = [listing.title, listing.category, listing.description]
+ .filter(Boolean)
+ .join("\n");
+ const heuristic = estimateDemand(text);
+
+ // Identity + anchor resolution: overrides > AI > heuristic.
+ let manufacturer = listing.manufacturer;
+ let model = listing.model;
+ let category = listing.category;
+ let newRetail = opts.anchor?.newRetail ?? heuristic.retailFloor;
+ let demandScore = opts.anchor?.demandScore ?? heuristic.demandScore;
+ let identificationConfidence = 0.45; // heuristic baseline
+ let identifiedBy = "heuristic";
+ let identifyRaw: unknown = { heuristic };
+
+ if (opts.useAI) {
+ const { result, model: aiModel } = await identifyProduct({
+ title: listing.title,
+ description: listing.description,
+ category: listing.category,
+ });
+ if (result) {
+ manufacturer = result.manufacturer || manufacturer;
+ model = result.model || model;
+ category = result.category || category;
+ if (typeof result.estimatedNewRetail === "number" && result.estimatedNewRetail > 0) {
+ // Blend AI estimate with the heuristic floor to avoid wild outliers.
+ newRetail = Math.max(result.estimatedNewRetail, heuristic.retailFloor * 0.5);
+ }
+ if (typeof result.demandScore === "number") demandScore = clamp(result.demandScore, 0, 100);
+ identificationConfidence = clamp(result.confidence ?? 0.6, 0, 1);
+ identifiedBy = aiModel;
+ identifyRaw = result;
+ }
+ }
+
+ const condition = listing.condition as ConditionKey;
+ const comps = opts.comparables ?? [];
+
+ // --- Valuation ---
+ const valuation = computeValuation({
+ newRetail,
+ condition,
+ quantity: listing.quantity,
+ demandScore,
+ identificationConfidence,
+ comparableCount: comps.length,
+ });
+
+ // --- Costs (winning bid modeled as the current bid) ---
+ const weightLbs = listing.weightLbs ?? 0;
+ const localPickup = inferLocalPickup(weightLbs, listing.auctionTerms);
+ const fr = estimateFreight(weightLbs);
+ const costs = computeCosts({
+ source: listing.source as SourceKey,
+ winningBid: Number(listing.currentBid),
+ quantity: listing.quantity,
+ weightLbs,
+ condition,
+ expectedSalePrice: valuation.expectedSalePrice,
+ daysUntilSold: valuation.daysUntilSold,
+ localPickup,
+ });
+
+ // --- Scores ---
+ const scores = computeScores({
+ roi: costs.roi,
+ expectedNetProfit: costs.expectedNetProfit,
+ demandScore,
+ daysUntilSold: valuation.daysUntilSold,
+ weightLbs,
+ condition,
+ bidCount: listing.bidCount,
+ localPickup,
+ hasBuyerLeads: false,
+ confidenceScore: valuation.confidenceScore,
+ probabilityOfSale: valuation.probabilityOfSale,
+ isFreight: fr.isFreight,
+ });
+
+ // --- Persist ---
+ await prisma.$transaction(async (tx) => {
+ await tx.research.upsert({
+ where: { listingId },
+ create: {
+ listingId,
+ ...valuationToDb(valuation),
+ sources: comps.map((c) => ({ name: c.source ?? "comp", url: c.url, kind: c.kind, price: c.price })),
+ summary: buildSummary(listing.title, valuation, costs, demandScore),
+ model: identifiedBy,
+ },
+ update: {
+ ...valuationToDb(valuation),
+ summary: buildSummary(listing.title, valuation, costs, demandScore),
+ model: identifiedBy,
+ },
+ });
+
+ await tx.costBreakdown.upsert({
+ where: { listingId },
+ create: { listingId, ...costsToDb(costs) },
+ update: { ...costsToDb(costs) },
+ });
+
+ // Replace comparables.
+ await tx.comparable.deleteMany({ where: { listingId } });
+ if (comps.length) {
+ await tx.comparable.createMany({
+ data: comps.map((c) => ({
+ listingId,
+ kind: c.kind,
+ title: c.title,
+ price: c.price,
+ url: c.url,
+ source: c.source,
+ soldAt: c.soldAt,
+ })),
+ });
+ }
+
+ await tx.score.deleteMany({ where: { listingId } });
+ await tx.score.createMany({
+ data: scores.map((s) => ({
+ listingId,
+ profile: s.profile,
+ value: s.value,
+ arbitrage: s.components.arbitrage,
+ demand: s.components.demand,
+ velocity: s.components.velocity,
+ logistics: s.components.logistics,
+ condition: s.components.condition,
+ competition: s.components.competition,
+ buyer: s.components.buyer,
+ risk: s.risk,
+ dropShip: s.dropShip,
+ explanation: s.explanation,
+ factors: s.factors,
+ })),
+ });
+
+ await tx.listing.update({
+ where: { id: listingId },
+ data: {
+ researchStatus: "COMPLETE",
+ identifiedBy,
+ identifyRaw: identifyRaw as object,
+ ...(opts.writeIdentity ? { manufacturer, model, category } : {}),
+ },
+ });
+
+ await tx.listingEvent.create({
+ data: {
+ listingId,
+ type: "RESEARCH_COMPLETED",
+ message: `Research complete via ${identifiedBy}: est. net ${costs.expectedNetProfit.toFixed(0)}, ROI ${(costs.roi * 100).toFixed(0)}%`,
+ meta: { model: identifiedBy },
+ },
+ });
+ });
+
+ return {
+ listingId,
+ expectedNetProfit: costs.expectedNetProfit,
+ roi: costs.roi,
+ overallScore: scores.find((s) => s.profile === "OVERALL_OPPORTUNITY")?.value ?? 0,
+ identifiedBy,
+ };
+}
+
+function valuationToDb(v: ReturnType<typeof computeValuation>) {
+ return {
+ newRetail: v.newRetail,
+ newReplacement: v.newReplacement,
+ avgRetail: v.avgRetail,
+ usedSoldPrice: v.usedSoldPrice,
+ usedAskingPrice: v.usedAskingPrice,
+ usedLow: v.usedLow,
+ usedHigh: v.usedHigh,
+ wholesaleValue: v.wholesaleValue,
+ liquidationValue: v.liquidationValue,
+ sellTodayValue: v.sellTodayValue,
+ value7Day: v.value7Day,
+ value30Day: v.value30Day,
+ value90Day: v.value90Day,
+ expectedSalePrice: v.expectedSalePrice,
+ probabilityOfSale: v.probabilityOfSale,
+ daysUntilSold: v.daysUntilSold,
+ confidenceScore: v.confidenceScore,
+ };
+}
+
+function costsToDb(c: ReturnType<typeof computeCosts>) {
+ const { assumptions, ...rest } = c;
+ return { ...rest, assumptions: assumptions as object };
+}
+
+function buildSummary(
+ title: string,
+ v: ReturnType<typeof computeValuation>,
+ c: ReturnType<typeof computeCosts>,
+ demand: number,
+): string {
+ return (
+ `${title}: est. resale ${v.expectedSalePrice.toFixed(0)} vs new retail ${v.newRetail.toFixed(0)}. ` +
+ `At the current bid, total-in ${c.totalInvestment.toFixed(0)} → net ${c.expectedNetProfit.toFixed(0)} ` +
+ `(${(c.roi * 100).toFixed(0)}% ROI). Demand ${demand.toFixed(0)}/100, ~${v.daysUntilSold} days to sell, ` +
+ `confidence ${v.confidenceScore.toFixed(0)}/100. Recommended max bid ${c.recommendedMaxBid.toFixed(0)}.`
+ );
+}
diff --git a/govarbitrage/src/worker/index.ts b/govarbitrage/src/worker/index.ts
new file mode 100644
index 0000000..5162357
--- /dev/null
+++ b/govarbitrage/src/worker/index.ts
@@ -0,0 +1,66 @@
+import { prisma } from "@/lib/db";
+import { runResearch } from "@/pipeline/research";
+import { ollamaReachable } from "@/lib/ai";
+
+// Research worker. Two modes:
+// • REDIS_URL set → BullMQ worker consuming a "research" queue.
+// • REDIS_URL unset → direct poll loop over PENDING listings (fine at this scale).
+
+const QUEUE = "research";
+
+async function processListing(listingId: string) {
+ await prisma.listing.update({ where: { id: listingId }, data: { researchStatus: "QUEUED" } });
+ try {
+ const r = await runResearch(listingId, { useAI: true, writeIdentity: true });
+ console.log(`✓ researched ${listingId} — net ${r.expectedNetProfit.toFixed(0)}, ROI ${(r.roi * 100).toFixed(0)}%`);
+ } catch (e) {
+ await prisma.listing.update({ where: { id: listingId }, data: { researchStatus: "FAILED" } });
+ await prisma.listingEvent.create({
+ data: { listingId, type: "RESEARCH_FAILED", message: (e as Error).message },
+ });
+ console.error(`✗ ${listingId}: ${(e as Error).message}`);
+ }
+}
+
+async function pendingIds(): Promise<string[]> {
+ const rows = await prisma.listing.findMany({
+ where: { researchStatus: { in: ["PENDING", "QUEUED"] } },
+ select: { id: true },
+ take: 25,
+ });
+ return rows.map((r) => r.id);
+}
+
+async function main() {
+ const local = await ollamaReachable();
+ console.log(`GovArbitrage worker starting. Local AI (Ollama) reachable: ${local}`);
+
+ if (process.env.REDIS_URL) {
+ // BullMQ mode (optional dependency).
+ const { Worker, Queue } = await import("bullmq");
+ const connection = { url: process.env.REDIS_URL };
+ const queue = new Queue(QUEUE, { connection });
+ // Enqueue any currently-pending listings.
+ for (const id of await pendingIds()) await queue.add("research", { listingId: id });
+ const worker = new Worker(
+ QUEUE,
+ async (job) => processListing(job.data.listingId as string),
+ { connection, concurrency: 2 },
+ );
+ worker.on("completed", (job) => console.log(`job ${job.id} done`));
+ console.log("BullMQ worker online.");
+ } else {
+ // Direct poll loop.
+ console.log("No REDIS_URL — running direct poll loop (Ctrl-C to stop).");
+ for (;;) {
+ const ids = await pendingIds();
+ for (const id of ids) await processListing(id);
+ await new Promise((r) => setTimeout(r, 5000));
+ }
+ }
+}
+
+main().catch((e) => {
+ console.error(e);
+ process.exit(1);
+});
diff --git a/govarbitrage/tests/e2e/smoke.spec.ts b/govarbitrage/tests/e2e/smoke.spec.ts
new file mode 100644
index 0000000..ce56f01
--- /dev/null
+++ b/govarbitrage/tests/e2e/smoke.spec.ts
@@ -0,0 +1,38 @@
+import { test, expect } from "@playwright/test";
+
+test("dashboard renders with cards and the listings table", async ({ page }) => {
+ await page.goto("/");
+ await expect(page.getByRole("heading", { name: /GovArbitrage/i })).toBeVisible();
+ await expect(page.getByText("Active Auctions")).toBeVisible();
+ await expect(page.getByText("Expected Profit")).toBeVisible();
+ // The table loads listings from the API.
+ await expect(page.getByText(/listings$/)).toBeVisible();
+});
+
+test("listings API returns computed rows", async ({ request }) => {
+ const res = await request.get("/api/listings?pageSize=5");
+ expect(res.ok()).toBeTruthy();
+ const data = await res.json();
+ expect(data.total).toBeGreaterThan(0);
+ expect(data.rows[0]).toHaveProperty("netProfit");
+ expect(data.rows[0]).toHaveProperty("opportunityScore");
+});
+
+test("scoring profile switch changes ordering", async ({ request }) => {
+ const overall = await (await request.get("/api/listings?profile=OVERALL_OPPORTUNITY&pageSize=1&sort=opportunityScore&dir=desc")).json();
+ const highProfit = await (await request.get("/api/listings?profile=HIGH_PROFIT&pageSize=1&sort=opportunityScore&dir=desc")).json();
+ expect(overall.rows[0]).toBeTruthy();
+ expect(highProfit.rows[0]).toBeTruthy();
+});
+
+test("selling-avenues page lists real sourcing links", async ({ page }) => {
+ await page.goto("/selling-avenues");
+ await expect(page.getByRole("heading", { name: /Sourcing & Selling Avenues/i })).toBeVisible();
+ await expect(page.getByRole("link", { name: /GovDeals/i }).first()).toBeVisible();
+});
+
+test("reports page renders performance tables", async ({ page }) => {
+ await page.goto("/reports");
+ await expect(page.getByRole("heading", { name: "Reports" })).toBeVisible();
+ await expect(page.getByText("Category Performance")).toBeVisible();
+});
diff --git a/govarbitrage/vitest.config.ts b/govarbitrage/vitest.config.ts
new file mode 100644
index 0000000..3dbf34f
--- /dev/null
+++ b/govarbitrage/vitest.config.ts
@@ -0,0 +1,15 @@
+import { defineConfig } from "vitest/config";
+import { fileURLToPath } from "node:url";
+
+export default defineConfig({
+ resolve: {
+ alias: {
+ "@": fileURLToPath(new URL("./src", import.meta.url)),
+ },
+ },
+ test: {
+ environment: "node",
+ include: ["src/**/*.test.ts", "tests/unit/**/*.test.ts"],
+ globals: true,
+ },
+});
diff --git a/rentv-slideshow/capture-growth.mjs b/rentv-slideshow/capture-growth.mjs
new file mode 100644
index 0000000..e35666d
--- /dev/null
+++ b/rentv-slideshow/capture-growth.mjs
@@ -0,0 +1,42 @@
+import { chromium } from 'playwright';
+import fs from 'fs';
+
+const BASE = 'https://rentv.agentabrams.com';
+const SECTIONS = [
+ 'reputation','swot','lowhanging','empire','competitors','wires',
+ 'advertising','timing','audit','teardown','social','suggested',
+ 'partners','revenue','videos','dealdesk','roadmap','sources',
+];
+
+const browser = await chromium.launch();
+const ctx = await browser.newContext({
+ httpCredentials: { username: 'admin', password: 'DW2024!' },
+ viewport: { width: 1680, height: 1050 },
+ deviceScaleFactor: 1,
+ ignoreHTTPSErrors: true,
+});
+const page = await ctx.newPage();
+await page.goto(BASE + '/consulting', { waitUntil: 'networkidle', timeout: 60000 });
+// expand all accordions
+try { await page.click('#xall', { timeout: 5000 }); } catch {}
+await page.waitForTimeout(2500);
+fs.mkdirSync('media/growth', { recursive: true });
+const out = [];
+for (const id of SECTIONS) {
+ try {
+ const el = await page.$('#' + id);
+ if (!el) { out.push(`${id}\tMISSING`); console.log('MISS', id); continue; }
+ await el.scrollIntoViewIfNeeded();
+ await page.waitForTimeout(600);
+ const box = await el.boundingBox();
+ await el.screenshot({ path: `media/growth/${id}.png` });
+ out.push(`${id}\tOK\th=${box ? Math.round(box.height) : '?'}`);
+ console.log('OK', id, box ? Math.round(box.height) : '?');
+ } catch (e) {
+ out.push(`${id}\tERR\t${e.message.slice(0,50)}`);
+ console.log('ERR', id, e.message.slice(0, 60));
+ }
+}
+await browser.close();
+fs.writeFileSync('/tmp/growth-capture.tsv', out.join('\n'));
+console.log('=== done ===');
diff --git a/rentv-slideshow/capture.mjs b/rentv-slideshow/capture.mjs
new file mode 100644
index 0000000..2eb83ca
--- /dev/null
+++ b/rentv-slideshow/capture.mjs
@@ -0,0 +1,63 @@
+import { chromium } from 'playwright';
+
+const BASE = 'https://rentv.agentabrams.com';
+const IP = '45.61.58.125';
+// route -> capture name
+const PAGES = [
+ // FRONT END
+ ['/', 'fe-home'],
+ ['/sales', 'fe-sales'],
+ ['/review', 'fe-review'],
+ ['/markets', 'fe-markets'],
+ ['/pulse', 'fe-pulse'],
+ ['/cre-talk', 'fe-cretalk'],
+ ['/la-commercial', 'fe-lacommercial'],
+ ['/sector', 'fe-sector'],
+ ['/brief', 'fe-brief'],
+ ['/deals', 'fe-deals'],
+ ['/tools', 'fe-tools'],
+ ['/directory', 'fe-directory'],
+ ['/hub', 'fe-hub'],
+ ['/subscribe', 'fe-subscribe'],
+ ['/blog', 'fe-blog'],
+ // BACKEND
+ ['/backend', 'be-hub'],
+ ['/versions', 'be-versions'],
+ ['/admin', 'be-admin'],
+ ['/desk', 'be-desk'],
+ ['/desk-admin', 'be-deskadmin'],
+ ['/audience', 'be-audience'],
+ ['/social', 'be-social'],
+ ['/consulting', 'be-consulting'],
+ ['/press', 'be-press'],
+];
+
+const browser = await chromium.launch();
+const ctx = await browser.newContext({
+ httpCredentials: { username: 'admin', password: 'DW2024!' },
+ viewport: { width: 1920, height: 1080 },
+ deviceScaleFactor: 1,
+ ignoreHTTPSErrors: true,
+});
+// force origin IP via route (bypass any DNS oddities)
+await ctx.route('**/*', (route) => route.continue());
+const page = await ctx.newPage();
+const results = [];
+for (const [route, name] of PAGES) {
+ try {
+ const resp = await page.goto(BASE + route, { waitUntil: 'networkidle', timeout: 45000 });
+ await page.waitForTimeout(2200);
+ // measure full height
+ const h = await page.evaluate(() => document.body.scrollHeight);
+ await page.screenshot({ path: `media/slides2/${name}.png`, fullPage: true });
+ results.push(`${name}\t${route}\t${resp ? resp.status() : '?'}\th=${h}`);
+ console.log('OK', name, route, resp && resp.status(), 'h='+h);
+ } catch (e) {
+ results.push(`${name}\t${route}\tERR\t${e.message.slice(0,60)}`);
+ console.log('ERR', name, route, e.message.slice(0, 80));
+ }
+}
+await browser.close();
+import fs from 'fs';
+fs.writeFileSync('/tmp/capture-results.tsv', results.join('\n'));
+console.log('\n=== done ===');
diff --git a/send-projects-email.js b/send-projects-email.js
new file mode 100644
index 0000000..c0502d1
--- /dev/null
+++ b/send-projects-email.js
@@ -0,0 +1,60 @@
+#!/usr/bin/env node
+/**
+ * Sends projects_list.xlsx to steve@designerwallcoverings.com via the
+ * george-gmail agent on Kamatera.
+ *
+ * Usage:
+ * GMAIL_AGENT_AUTH='admin:PASSWORD' node send-projects-email.js
+ *
+ * Or set GMAIL_AGENT_URL to override the endpoint (defaults to the public
+ * Kamatera IP). For local SSH-tunnel runs:
+ * GMAIL_AGENT_URL=http://localhost:9850 node send-projects-email.js
+ */
+
+const fs = require('fs');
+const path = require('path');
+
+const XLSX_PATH = path.join(__dirname, 'projects_list.xlsx');
+const GMAIL_AGENT = process.env.GMAIL_AGENT_URL || 'http://45.61.58.125:9850';
+const AUTH_RAW = process.env.GMAIL_AGENT_AUTH;
+const TO = process.env.MAIL_TO || 'steve@designerwallcoverings.com';
+
+if (!AUTH_RAW) {
+ console.error('ERROR: set GMAIL_AGENT_AUTH="user:password" before running.');
+ process.exit(1);
+}
+if (!fs.existsSync(XLSX_PATH)) {
+ console.error(`ERROR: ${XLSX_PATH} not found. Run projects_list.py first.`);
+ process.exit(1);
+}
+
+const auth = 'Basic ' + Buffer.from(AUTH_RAW).toString('base64');
+const content_base64 = fs.readFileSync(XLSX_PATH).toString('base64');
+const today = new Date().toISOString().slice(0, 10);
+
+const payload = {
+ to: TO,
+ subject: `Projects list (${today})`,
+ body: `<p>Hi Steve,</p>
+<p>Attached is the full list of projects under <code>~/Projects</code> on SteveStacStudio — 64 directories, two columns (Project Name, Last Modified).</p>
+<p>— generated ${today}</p>`,
+ attachments: [{
+ filename: 'projects_list.xlsx',
+ content_base64,
+ mime_type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
+ }]
+};
+
+(async () => {
+ const r = await fetch(`${GMAIL_AGENT}/api/send-with-attachment`, {
+ method: 'POST',
+ headers: { 'Content-Type': 'application/json', 'Authorization': auth },
+ body: JSON.stringify(payload)
+ });
+ const j = await r.json().catch(() => ({}));
+ if (!r.ok || j.error) {
+ console.error('Email failed:', r.status, j);
+ process.exit(1);
+ }
+ console.log(`Sent. messageId=${j.messageId} threadId=${j.threadId}`);
+})();
diff --git a/test-enhanced-scraper.js b/test-enhanced-scraper.js
new file mode 100644
index 0000000..f864a0b
--- /dev/null
+++ b/test-enhanced-scraper.js
@@ -0,0 +1,127 @@
+const { chromium } = require('playwright');
+
+async function testEnhancedScraper() {
+ const browser = await chromium.launch({ headless: true });
+ const page = await browser.newPage();
+
+ try {
+ // Test aux-abris which we know has products
+ const url = 'https://auxabris.com/collections/wallcovering?sort_by=created-descending';
+ console.log('🔍 Testing enhanced product extraction for:', url);
+
+ await page.goto(url, { waitUntil: 'networkidle', timeout: 30000 });
+ await page.waitForTimeout(3000);
+
+ // Extract detailed product information
+ const products = await page.evaluate(() => {
+ const extractedProducts = [];
+
+ // Find product elements - aux-abris uses .product-item
+ const productElements = document.querySelectorAll('.product-item, .grid__item, article');
+
+ console.log(`Found ${productElements.length} product elements`);
+
+ productElements.forEach((element, index) => {
+ try {
+ // Extract product name
+ let productName = '';
+ const nameEl = element.querySelector('.product-item__title, .product__title, h3, h2, .title');
+ if (nameEl) {
+ productName = nameEl.textContent.trim();
+ }
+
+ // Extract color from name or dedicated element
+ let productColor = '';
+ const colorEl = element.querySelector('.product-item__color, .color, .variant');
+ if (colorEl) {
+ productColor = colorEl.textContent.trim();
+ } else if (productName) {
+ // Extract color from name
+ const colorMatch = productName.match(/(Black|White|Gray|Grey|Blue|Green|Red|Brown|Gold|Silver|Pink|Purple|Orange|Yellow|Navy|Beige|Cream|Ivory|Medallion|Heirloom|Oak)/i);
+ if (colorMatch) {
+ productColor = colorMatch[0];
+ }
+ }
+
+ // Extract href
+ let productHref = '';
+ const linkEl = element.querySelector('a[href]');
+ if (linkEl) {
+ productHref = linkEl.href;
+ if (!productHref.startsWith('http')) {
+ productHref = new URL(productHref, window.location.href).href;
+ }
+ }
+
+ // Extract price
+ let productPrice = '';
+ const priceEl = element.querySelector('.price, .product-item__price, .money');
+ if (priceEl) {
+ productPrice = priceEl.textContent.trim();
+ }
+
+ // Extract image
+ let imageUrl = '';
+ const imgEl = element.querySelector('img');
+ if (imgEl) {
+ imageUrl = imgEl.src || imgEl.dataset.src || '';
+ }
+
+ if (productName && productHref) {
+ extractedProducts.push({
+ index: index + 1,
+ name: productName,
+ color: productColor || 'Multiple Colors Available',
+ href: productHref,
+ price: productPrice,
+ imageUrl: imageUrl
+ });
+ }
+ } catch (e) {
+ console.error('Error extracting product:', e);
+ }
+ });
+
+ return extractedProducts;
+ });
+
+ console.log(`\n✅ Found ${products.length} products with complete details:\n`);
+
+ // Display first 5 products with full details
+ products.slice(0, 5).forEach(product => {
+ console.log(`📦 Product ${product.index}:`);
+ console.log(` Name: ${product.name}`);
+ console.log(` Color: ${product.color}`);
+ console.log(` Link: ${product.href}`);
+ if (product.price) console.log(` Price: ${product.price}`);
+ if (product.imageUrl) console.log(` Image: ${product.imageUrl.substring(0, 50)}...`);
+ console.log('');
+ });
+
+ if (products.length > 5) {
+ console.log(`... and ${products.length - 5} more products\n`);
+ }
+
+ // Return formatted for API
+ const apiFormat = products.map((p, i) => ({
+ url: p.href,
+ title: `${p.name}${p.color !== 'Multiple Colors Available' ? ' - ' + p.color : ''}`,
+ name: p.name,
+ color: p.color,
+ code: `AUX-${i + 1}`,
+ productId: `aux-abris-${i + 1}`,
+ price: p.price,
+ imageUrl: p.imageUrl
+ }));
+
+ console.log('📊 API Format Sample:');
+ console.log(JSON.stringify(apiFormat.slice(0, 2), null, 2));
+
+ } catch (error) {
+ console.error('❌ Error:', error.message);
+ } finally {
+ await browser.close();
+ }
+}
+
+testEnhancedScraper();
\ No newline at end of file
← f9d835f security: strip hardcoded secret -> env-first/passwordless.
·
back to Japan Enrich
·
fix: untrack govarbitrage (has its own separate .git repo — 9d0edd2 →