← back to Stayclaim
src/app/admin/page.tsx
205 lines
import Link from 'next/link';
import { pool } from '@/lib/db';
export const runtime = 'nodejs';
export const dynamic = 'force-dynamic';
type CountRow = { metric: string; n: string };
// Wrap a query in a per-statement timeout so a single hung query (lock,
// runaway seq scan) can't block the SSR response indefinitely and tie up a
// Node worker. statement_timeout is SET LOCAL → only lives inside this txn.
async function withTimeout<T>(fn: (client: import('pg').PoolClient) => Promise<T>): Promise<T> {
const client = await pool.connect();
try {
await client.query("BEGIN");
await client.query("SET LOCAL statement_timeout = '5s'");
const out = await fn(client);
await client.query("COMMIT");
return out;
} catch (e) {
try { await client.query("ROLLBACK"); } catch { /* ignore */ }
throw e;
} finally {
client.release();
}
}
async function getOverview(): Promise<CountRow[]> {
// NOTE: parens around `count(*) FILTER (...)` so the `::text` cast applies
// to the count, not to the filter predicate (was ambiguous before).
const sql = `
SELECT 'listings (public)' as metric, count(*)::text as n FROM listing WHERE is_public = true
UNION ALL SELECT 'listings (hidden)', count(*)::text FROM listing WHERE is_public = false
UNION ALL SELECT 'listings (manual seeds)', count(*)::text FROM listing WHERE source = 'manual'
UNION ALL SELECT 'permits (LADBS)', count(*)::text FROM permit
UNION ALL SELECT 'parcels', count(*)::text FROM parcel
UNION ALL SELECT 'film productions', count(*)::text FROM film_production
UNION ALL SELECT 'filming locations', count(*)::text FROM filming_location
UNION ALL SELECT 'newspaper issues', count(*)::text FROM news_issue
UNION ALL SELECT 'newspaper issues (OCR fetched)', (count(*) FILTER (WHERE ocr_text IS NOT NULL AND ocr_text <> ''))::text FROM news_issue
UNION ALL SELECT 'address mentions in news', count(*)::text FROM news_address_mention
UNION ALL SELECT 'address mentions matched', (count(*) FILTER (WHERE listing_id IS NOT NULL))::text FROM news_address_mention
UNION ALL SELECT 'entities (people)', count(*)::text FROM entity
UNION ALL SELECT 'place events', count(*)::text FROM place_event
UNION ALL SELECT 'address submissions', count(*)::text FROM address_submission
UNION ALL SELECT 'pending claim requests', count(*)::text FROM claim_request WHERE status = 'pending'
UNION ALL SELECT 'sponsored placements', count(*)::text FROM sponsored_placement
`;
const rows = await withTimeout(c => c.query<CountRow>(sql).then(r => r.rows));
return rows;
}
async function getSourceBreakdown() {
const rows = await withTimeout(c => c.query<{ source: string; n: string; visible: string }>(`
SELECT
source,
count(*)::text as n,
(count(*) FILTER (WHERE is_public))::text as visible
FROM listing
GROUP BY source
ORDER BY count(*) DESC
`).then(r => r.rows));
return rows;
}
async function getRecentIngests() {
// Order on the raw timestamp (not its text cast), then stringify so sort
// doesn't break the moment any branch returns a different timestamp format.
const rows = await withTimeout(c => c.query<{ source: string; latest: string | null; n: string }>(`
SELECT source, latest::text as latest, n::text as n FROM (
SELECT 'listing/' || source as source, max(updated_at) as latest, count(*) as n
FROM listing GROUP BY source
UNION ALL
SELECT 'permit/' || source_dataset, max(retrieved_at), count(*)
FROM permit GROUP BY source_dataset
UNION ALL
SELECT 'news_issue/' || paper_short, max(metadata_at), count(*)
FROM news_issue GROUP BY paper_short
) t
ORDER BY latest DESC NULLS LAST
LIMIT 20
`).then(r => r.rows));
return rows;
}
const QUICK_LINKS = [
{ href: '/admin/sources', label: 'Source attribution', hint: 'every dataset, last refresh, fake/seed flags' },
{ href: '/admin/listings', label: 'Listing search + edit', hint: 'find, hide, merge, fix addresses' },
{ href: '/admin/ingests', label: 'Run / monitor ingests', hint: 'trigger LADBS, BH, Pasadena, OCR pulls' },
{ href: '/admin/quality', label: 'Data quality reports', hint: 'orphan rows, missing source_url, geocoding gaps' },
{ href: '/admin/claims', label: 'Pending claims', hint: 'review submitted claim_request rows' },
{ href: '/admin/seeds', label: 'Seed / fake data', hint: 'review and remove demo content' },
];
// Render a number column safely: never show "NaN", never throw on null.
function n2(v: string | number | null | undefined): number {
const n = Number(v ?? 0);
return Number.isFinite(n) ? n : 0;
}
export default async function AdminOverviewPage() {
// Catch each fetch independently so one bad query doesn't blank the whole
// page and so error details never leak through Next's default error UI.
// Server-side console.error keeps the trace for ops; the user gets [].
const [overview, sources, recent] = await Promise.all([
getOverview().catch(e => { console.error('[admin] getOverview failed:', e); return [] as CountRow[]; }),
getSourceBreakdown().catch(e => { console.error('[admin] getSourceBreakdown failed:', e); return []; }),
getRecentIngests().catch(e => { console.error('[admin] getRecentIngests failed:', e); return []; }),
]);
return (
<div className="space-y-12">
<section>
<h1 className="font-display text-4xl text-ink mb-2">Overview</h1>
<p className="text-sm text-ink/60">Live counts from <code>stayclaim</code> Postgres on localhost.</p>
</section>
<section>
<h2 className="font-display text-2xl text-ink mb-4">Counts</h2>
<div className="grid grid-cols-2 md:grid-cols-3 lg:grid-cols-4 gap-3">
{overview.map(r => (
<div key={r.metric} className="border border-ink/10 bg-white p-4">
<p className="text-[10px] uppercase tracking-[0.15em] text-ink/50">{r.metric}</p>
<p className="font-display text-3xl text-ink tabular-nums mt-1">
{n2(r.n).toLocaleString()}
</p>
</div>
))}
</div>
</section>
<section>
<h2 className="font-display text-2xl text-ink mb-4">Listings by source</h2>
<table className="w-full text-sm border border-ink/10 bg-white">
<thead className="bg-ink/[0.03] text-[10px] uppercase tracking-[0.15em] text-ink/55">
<tr>
<th className="text-left p-3">source</th>
<th className="text-right p-3">total</th>
<th className="text-right p-3">public</th>
<th className="text-right p-3">hidden</th>
</tr>
</thead>
<tbody>
{sources.map(s => {
const total = n2(s.n);
const visible = n2(s.visible);
const hidden = total - visible;
const isFake = s.source === 'manual';
return (
<tr key={s.source ?? 'unknown'} className={`border-t border-ink/5 ${isFake ? 'bg-coral/5' : ''}`}>
<td className="p-3 font-mono text-xs">
{s.source}
{isFake && <span className="ml-2 text-[10px] uppercase tracking-wider text-coral">SEED</span>}
</td>
<td className="p-3 text-right tabular-nums">{total.toLocaleString()}</td>
<td className="p-3 text-right tabular-nums">{visible.toLocaleString()}</td>
<td className="p-3 text-right tabular-nums text-ink/50">{hidden.toLocaleString()}</td>
</tr>
);
})}
</tbody>
</table>
</section>
<section>
<h2 className="font-display text-2xl text-ink mb-4">Most-recent ingests</h2>
<table className="w-full text-sm border border-ink/10 bg-white">
<thead className="bg-ink/[0.03] text-[10px] uppercase tracking-[0.15em] text-ink/55">
<tr>
<th className="text-left p-3">source</th>
<th className="text-left p-3">last touched</th>
<th className="text-right p-3">rows</th>
</tr>
</thead>
<tbody>
{recent.map(r => (
<tr key={r.source ?? 'unknown'} className="border-t border-ink/5">
<td className="p-3 font-mono text-xs">{r.source}</td>
<td className="p-3 text-ink/65">{r.latest ? r.latest.slice(0, 19) : '—'}</td>
<td className="p-3 text-right tabular-nums">{n2(r.n).toLocaleString()}</td>
</tr>
))}
</tbody>
</table>
</section>
<section>
<h2 className="font-display text-2xl text-ink mb-4">Admin tasks</h2>
<div className="grid md:grid-cols-2 gap-3">
{QUICK_LINKS.map(q => (
<Link
key={q.href}
href={q.href}
className="block border border-ink/10 bg-white p-5 hover:border-moss transition"
>
<p className="font-display text-lg text-ink">{q.label}</p>
<p className="text-xs text-ink/55 mt-1">{q.hint}</p>
</Link>
))}
</div>
</section>
</div>
);
}