← back to Homesonspec
apps/admin/src/app/business/page.tsx
203 lines
import { prisma } from "@homesonspec/database";
import { readFile } from "node:fs/promises";
import path from "node:path";
import SortableTable, { type Col, type Row } from "./SortableTable";
export const dynamic = "force-dynamic";
// live data surfaces (tailnet-reachable; localhost when viewing admin on the Mac)
const FEED = process.env.HOS_FEED ?? "http://127.0.0.1:9799";
const USRE = process.env.USRE_FEED ?? "http://127.0.0.1:9796";
const VIEWER = process.env.HOS_VIEWER ?? "http://127.0.0.1:9977";
const WEB = process.env.HOS_WEB ?? "http://127.0.0.1:3100"; // consumer site — real home pages
async function raw<T = any>(sql: string): Promise<T[]> {
try { return (await prisma.$queryRawUnsafe(sql)) as T[]; } catch { return []; }
}
const N = (v: unknown) => (v === null || v === undefined ? null : Number(v));
async function data() {
const one = async (sql: string) => N((await raw<{ n: unknown }>(sql))[0]?.n) ?? 0;
const [homes, communities, enriched] = await Promise.all([
prisma.inventoryHome.count({ where: { status: "PUBLISHED" } }),
prisma.community.count(),
prisma.community.count({ where: { NOT: { amenities: { equals: null as never } } } }).catch(() => 0),
]);
const builders = await prisma.inventoryHome.findMany({ where: { status: "PUBLISHED" }, select: { builderId: true }, distinct: ["builderId"] }).then((r) => r.length).catch(() => 0);
const permits = await one(`select count(*)::int n from building_permits`);
const permitsGeo = await one(`select count(*)::int n from building_permits where lat is not null`);
const commercial = await one(`select count(*)::int n from building_permits where permit_type ilike '%commercial%' or permit_type ilike '%apartment%'`);
const byBuilder = (await raw(`select b.slug builder, count(*)::int homes, count(distinct h.state)::int states
from "Builder" b join "InventoryHome" h on h."builderId"=b.id where h.status='PUBLISHED'
group by b.slug having count(*)>0 order by homes desc`)).map((r: any) => ({ builder: r.builder, homes: N(r.homes), states: N(r.states) }));
const byState = (await raw(`select state, count(*)::int permits,
count(*) filter(where permit_type ilike '%commercial%')::int commercial,
count(*) filter(where permit_type ilike '%apartment%')::int multifamily,
count(*) filter(where lat is not null)::int geocoded
from building_permits where state is not null group by state order by permits desc limit 15`))
.map((r: any) => ({ state: r.state, permits: N(r.permits), commercial: N(r.commercial), multifamily: N(r.multifamily), geocoded: N(r.geocoded) }));
const metros = (await raw(`select city, state, count(*)::int projects,
count(*) filter(where permit_type ilike '%commercial%')::int commercial,
count(*) filter(where permit_type ilike '%apartment%')::int multifamily,
coalesce(round(sum(valuation)),0)::float8 total_valuation
from building_permits where (permit_type ilike '%commercial%' or permit_type ilike '%apartment%') and valuation is not null
group by city, state order by total_valuation desc limit 25`))
.map((r: any) => ({ city: r.city, state: r.state, projects: N(r.projects), commercial: N(r.commercial), multifamily: N(r.multifamily), total_valuation: N(r.total_valuation) }));
const projects = (await raw(`select address, city, state, coalesce(valuation,0)::float8 valuation, permit_type, issued_date::text issued
from building_permits where valuation is not null order by valuation desc limit 30`))
.map((r: any) => ({ address: r.address, city: r.city, state: r.state, valuation: N(r.valuation), permit_type: r.permit_type, issued: r.issued }));
const sampleHomes = (await raw(`select h.id, h.street, h.city, h.state, coalesce(h.price,0)::float8 price, h.beds, h.sqft, h."constructionStatus" status, b.name builder
from "InventoryHome" h join "Builder" b on b.id=h."builderId"
where h.status='PUBLISHED' and h.price is not null order by h."publishedAt" desc nulls last limit 60`))
.map((r: any) => ({ id: r.id, address: r.street, city: r.city, state: r.state, price: N(r.price), beds: N(r.beds), sqft: N(r.sqft), status: r.status, builder: r.builder }));
return { homes, builders, communities, enriched, permits, permitsGeo, commercial, byBuilder, byState, metros, projects, sampleHomes };
}
function Bar({ items, unit }: { items: { label: string; value: number; href?: string }[]; unit?: string }) {
const max = Math.max(1, ...items.map((i) => i.value));
return (
<div className="space-y-1.5">
{items.map((it) => (
<div key={it.label} className="flex items-center gap-2 text-xs">
<div className="w-24 shrink-0 truncate text-neutral-600">{it.href ? <a href={it.href} target="_blank" rel="noreferrer" className="text-teal-700 hover:underline">{it.label}</a> : it.label}</div>
<div className="h-3.5 flex-1 rounded bg-neutral-100"><div className="h-3.5 rounded bg-teal-500" style={{ width: `${(it.value / max) * 100}%` }} /></div>
<div className="w-20 shrink-0 text-right tabular-nums text-neutral-700">{(unit === "$" ? "$" : "") + Math.round(it.value).toLocaleString()}</div>
</div>
))}
</div>
);
}
async function loadDoc() {
for (const p of [path.join(process.cwd(), "../../docs/product/BUSINESS-ANALYSIS.md"), path.join(process.cwd(), "docs/product/BUSINESS-ANALYSIS.md"), "/Users/macstudio3/Projects/homesonspec/docs/product/BUSINESS-ANALYSIS.md"])
{ try { return await readFile(p, "utf8"); } catch {} }
return "";
}
// linkify data points in the narrative to real feed endpoints
const linkify = (s: string) => s
.replace(/(\$17\.4B[^,.]*pipeline|\$17\.4B)/gi, `<a class="lk" href="${FEED}/feed/developments?state=CA" target=_blank>$1</a>`)
.replace(/(~?542k|~?541,982|~?539k)(\s*(building )?permits)?/gi, `<a class="lk" href="${FEED}/feed/permits?limit=100" target=_blank>$&</a>`)
.replace(/(~?198k|~?197,755)/gi, `<a class="lk" href="${FEED}/feed/permits?sector=commercial&limit=100" target=_blank>$&</a>`)
.replace(/(~?2\.0M|~?2M)(\s*(national )?broker)?/gi, `<a class="lk" href="${USRE}/feed/brokers?limit=100" target=_blank>$&</a>`)
.replace(/(~?214k)(\s*firm)?/gi, `<a class="lk" href="${USRE}/feed/firms?limit=100" target=_blank>$&</a>`)
.replace(/(~?154k)(\s*commercial parcel)?/gi, `<a class="lk" href="${USRE}/feed/commercial?limit=100" target=_blank>$&</a>`)
.replace(/(~?27k|~?35k)(\s*(new-home )?listings|\s*homes)?/gi, `<a class="lk" href="${FEED}/feed/homes?limit=100" target=_blank>$&</a>`)
.replace(/`:9799\/feed\/\*`/g, `<a class="lk" href="${FEED}/" target=_blank>:9799/feed/*</a>`)
.replace(/`:9796\/feed\/\*`/g, `<a class="lk" href="${USRE}/" target=_blank>:9796/feed/*</a>`);
const inline = (s: string) => linkify(s.replace(/&/g, "&").replace(/</g, "<"))
.replace(/\*\*(.+?)\*\*/g, "<strong>$1</strong>").replace(/`(.+?)`/g, '<code class="rounded bg-neutral-100 px-1 text-[0.85em]">$1</code>').replace(/(^|[^*])\*([^*]+?)\*/g, "$1<em>$2</em>");
function Markdown({ text }: { text: string }) {
const els: React.ReactNode[] = []; let tbl: string[] = [], list: string[] = [];
const flushList = () => { if (list.length) { els.push(<ul key={els.length} className="my-2 ml-5 list-disc space-y-1 text-sm text-neutral-700">{list.map((l, i) => <li key={i} dangerouslySetInnerHTML={{ __html: inline(l) }} />)}</ul>); list = []; } };
const flushTbl = () => {
if (!tbl.length) return;
const rows = tbl.filter((r) => !/^\|[\s:-]+\|$/.test(r)).map((r) => r.split("|").slice(1, -1).map((c) => c.trim()));
const head = rows[0] ?? []; const body = rows.slice(1);
els.push(<div key={els.length} className="my-3 overflow-x-auto"><table className="w-full text-sm"><thead><tr>{head.map((h, i) => <th key={i} className="border-b border-neutral-300 bg-neutral-50 px-3 py-1.5 text-left font-semibold" dangerouslySetInnerHTML={{ __html: inline(h) }} />)}</tr></thead><tbody>{body.map((r, i) => <tr key={i} className="odd:bg-white even:bg-neutral-50">{r.map((c, j) => <td key={j} className="border-b border-neutral-200 px-3 py-1.5 align-top" dangerouslySetInnerHTML={{ __html: inline(c) }} />)}</tr>)}</tbody></table></div>);
tbl = [];
};
for (const l of text.split("\n")) {
if (l.startsWith("|")) { flushList(); tbl.push(l); continue; } else flushTbl();
if (l.startsWith("- ")) { list.push(l.slice(2)); continue; } else flushList();
if (l.startsWith("### ")) els.push(<h3 key={els.length} className="mt-4 text-base font-semibold" dangerouslySetInnerHTML={{ __html: inline(l.slice(4)) }} />);
else if (l.startsWith("## ")) els.push(<h2 key={els.length} className="mt-6 border-b border-neutral-200 pb-1 text-lg font-bold text-teal-800" dangerouslySetInnerHTML={{ __html: inline(l.slice(3)) }} />);
else if (l.startsWith("# ")) els.push(<h1 key={els.length} className="text-2xl font-bold" dangerouslySetInnerHTML={{ __html: inline(l.slice(2)) }} />);
else if (l.startsWith("---")) els.push(<hr key={els.length} className="my-4 border-neutral-200" />);
else if (l.trim() === "") els.push(<div key={els.length} className="h-1" />);
else els.push(<p key={els.length} className="my-1.5 text-sm leading-relaxed text-neutral-700" dangerouslySetInnerHTML={{ __html: inline(l) }} />);
}
flushTbl(); flushList();
return <div>{els}</div>;
}
export default async function BusinessPage() {
const [d, doc] = await Promise.all([data(), loadDoc()]);
const kpis = [
["New-home listings", d.homes, `${WEB}/search`],
["Builders", d.builders, `${WEB}/builders`],
["Building permits", d.permits, `${FEED}/feed/permits?limit=200`],
["Permits geocoded", d.permitsGeo, `${FEED}/feed/permits?geo=1&limit=200`],
["Commercial + multifamily", d.commercial, `${FEED}/feed/permits?sector=commercial&limit=200`],
["Communities enriched", d.enriched, `${VIEWER}`],
] as const;
const builderCols: Col[] = [
{ key: "builder", label: "Builder", href: `${WEB}/builders` },
{ key: "homes", label: "Homes", num: true }, { key: "states", label: "States", num: true },
];
// real home listings — every row links to the actual /homes/[id] page on the consumer site
const homeCols: Col[] = [
{ key: "address", label: "Address", href: `${WEB}/homes/{id}` },
{ key: "city", label: "City" }, { key: "state", label: "ST" },
{ key: "price", label: "Price", num: true, money: true }, { key: "beds", label: "Bd", num: true },
{ key: "sqft", label: "Sqft", num: true }, { key: "status", label: "Status" }, { key: "builder", label: "Builder" },
];
const stateCols: Col[] = [
{ key: "state", label: "State", href: `${FEED}/feed/permits?state={state}&limit=200` },
{ key: "permits", label: "Permits", num: true }, { key: "commercial", label: "Commercial", num: true },
{ key: "multifamily", label: "Multifamily", num: true }, { key: "geocoded", label: "Geocoded", num: true },
];
const metroCols: Col[] = [
{ key: "city", label: "City" }, { key: "state", label: "ST" }, { key: "projects", label: "Projects", num: true },
{ key: "commercial", label: "Comm.", num: true }, { key: "multifamily", label: "MF", num: true },
{ key: "total_valuation", label: "Total valuation", num: true, money: true, href: `${FEED}/feed/developments?state={state}` },
];
const projCols: Col[] = [
{ key: "address", label: "Address" }, { key: "city", label: "City" }, { key: "state", label: "ST" },
{ key: "valuation", label: "Valuation", num: true, money: true }, { key: "permit_type", label: "Type" }, { key: "issued", label: "Issued" },
];
return (
<div>
<style>{`.lk{color:#0f766e;text-decoration:underline;text-underline-offset:2px}.lk:hover{color:#134e4a}`}</style>
<div className="flex items-baseline justify-between">
<h1 className="text-2xl font-bold">Business Analysis</h1>
<span className="text-xs text-neutral-400">every figure links to live data · tables sortable · refreshed on load</span>
</div>
{/* KPI cards — each links to the real records */}
<div className="mt-4 grid grid-cols-2 gap-3 sm:grid-cols-3 lg:grid-cols-6">
{kpis.map(([k, v, href]) => (
<a key={k} href={href as string} target="_blank" rel="noreferrer" className="group rounded-xl border border-neutral-200 bg-white p-3 shadow-sm transition hover:border-teal-400 hover:shadow">
<div className="text-[10px] uppercase tracking-wide text-neutral-500">{k}</div>
<div className="mt-0.5 text-xl font-extrabold text-neutral-900 group-hover:text-teal-700">{(v as number).toLocaleString()}</div>
<div className="text-[9px] text-teal-600 opacity-0 transition group-hover:opacity-100">view data →</div>
</a>
))}
</div>
{/* Charts — real aggregates */}
<div className="mt-6 grid grid-cols-1 gap-4 lg:grid-cols-3">
<div className="rounded-2xl border border-neutral-200 bg-white p-4 shadow-sm">
<h3 className="mb-3 text-sm font-semibold">Permits by state (top)</h3>
<Bar items={d.byState.slice(0, 10).map((s) => ({ label: s.state ?? "?", value: s.permits ?? 0, href: `${FEED}/feed/permits?state=${s.state}&limit=200` }))} />
</div>
<div className="rounded-2xl border border-neutral-200 bg-white p-4 shadow-sm">
<h3 className="mb-3 text-sm font-semibold">Homes by builder</h3>
<Bar items={d.byBuilder.slice(0, 10).map((b) => ({ label: b.builder, value: b.homes ?? 0 }))} />
</div>
<div className="rounded-2xl border border-neutral-200 bg-white p-4 shadow-sm">
<h3 className="mb-3 text-sm font-semibold">CRE pipeline $ by metro</h3>
<Bar unit="$" items={d.metros.slice(0, 10).map((m) => ({ label: `${m.city}`, value: m.total_valuation ?? 0, href: `${FEED}/feed/developments?state=${m.state}` }))} />
</div>
</div>
{/* Sortable field tables */}
<div className="mt-6 space-y-6">
<section><h3 className="mb-2 text-sm font-semibold text-neutral-700">Real home listings <span className="text-neutral-400">— each row opens the live /homes page (click a header to sort)</span></h3><SortableTable cols={homeCols} rows={d.sampleHomes as Row[]} initialSort="price" /></section>
<section><h3 className="mb-2 text-sm font-semibold text-neutral-700">Builders <span className="text-neutral-400">(click a header to sort)</span></h3><SortableTable cols={builderCols} rows={d.byBuilder as Row[]} initialSort="homes" /></section>
<section><h3 className="mb-2 text-sm font-semibold text-neutral-700">Permits by state</h3><SortableTable cols={stateCols} rows={d.byState as Row[]} initialSort="permits" /></section>
<section><h3 className="mb-2 text-sm font-semibold text-neutral-700">Top CRE development metros</h3><SortableTable cols={metroCols} rows={d.metros as Row[]} initialSort="total_valuation" /></section>
<section><h3 className="mb-2 text-sm font-semibold text-neutral-700">Largest projects (permit valuation)</h3><SortableTable cols={projCols} rows={d.projects as Row[]} initialSort="valuation" /></section>
</div>
{/* Narrative — data points linkified */}
<article className="mt-8 rounded-2xl border border-neutral-200 bg-white p-6 shadow-sm">
<Markdown text={doc} />
</article>
</div>
);
}