← back to Stayclaim

src/app/admin/data/page.tsx

451 lines

import Link from 'next/link';
import { pool } from '@/lib/db';
import { safeHttpUrlUnknown as safeHttpUrl } from '@/lib/url-guards';

export const dynamic = 'force-dynamic';

type Source = {
  key: string;
  label: string;
  table: string;
  searchFields: string[];      // columns we ILIKE against
  displayFields: { col: string; label: string; format?: 'date' | 'money' | 'truncate' }[];
  sourceUrlField?: string;
  sourceLabelField?: string;
  filterable?: { col: string; label: string }[]; // dropdown filters
  defaultSort?: string;
};

const SOURCES: Source[] = [
  {
    key: 'listing',
    label: 'Listings (addresses)',
    table: 'listing',
    searchFields: ['title', 'address_line1', 'city', 'postal_code', 'description', 'headline', 'property_type'],
    displayFields: [
      { col: 'slug', label: 'slug' },
      { col: 'source', label: 'source' },
      { col: 'title', label: 'title' },
      { col: 'address_line1', label: 'address' },
      { col: 'city', label: 'city' },
      { col: 'is_public', label: 'public' },
    ],
    filterable: [{ col: 'source', label: 'source' }],
    defaultSort: 'updated_at DESC',
  },
  {
    key: 'permit',
    label: 'Permits (LADBS / BH / Pasadena / SM)',
    table: 'permit',
    searchFields: ['permit_number', 'primary_address', 'work_desc', 'use_desc', 'permit_type', 'permit_sub_type', 'apn', 'zip_code', 'zone'],
    displayFields: [
      { col: 'source', label: 'source' },
      { col: 'permit_number', label: 'permit #' },
      { col: 'primary_address', label: 'address' },
      { col: 'permit_type', label: 'type' },
      { col: 'permit_sub_type', label: 'sub-type' },
      { col: 'use_desc', label: 'use' },
      { col: 'issue_date', label: 'issued', format: 'date' },
      { col: 'valuation', label: 'value', format: 'money' },
      { col: 'status_desc', label: 'status' },
      { col: 'work_desc', label: 'work', format: 'truncate' },
    ],
    sourceUrlField: 'source_url',
    sourceLabelField: 'source_label',
    filterable: [{ col: 'source', label: 'source' }, { col: 'source_dataset', label: 'dataset' }],
    defaultSort: 'issue_date DESC NULLS LAST',
  },
  {
    key: 'code_enforcement',
    label: 'Code enforcement cases',
    table: 'code_enforcement_case',
    searchFields: ['case_number', 'primary_address', 'case_type', 'apn', 'zip_code'],
    displayFields: [
      { col: 'case_number', label: 'case #' },
      { col: 'primary_address', label: 'address' },
      { col: 'case_type', label: 'type' },
      { col: 'status', label: 'status' },
      { col: 'opened_at', label: 'opened', format: 'date' },
      { col: 'apn', label: 'APN' },
    ],
    sourceUrlField: 'source_url',
    sourceLabelField: 'source_label',
    filterable: [{ col: 'source_dataset', label: 'dataset' }, { col: 'status', label: 'status' }],
    defaultSort: 'opened_at DESC NULLS LAST',
  },
  {
    key: 'parcel',
    label: 'Parcels (LA County Assessor)',
    table: 'parcel',
    searchFields: ['apn', 'situs_full', 'city', 'zip_code', 'use_code', 'use_desc'],
    displayFields: [
      { col: 'apn', label: 'APN' },
      { col: 'situs_full', label: 'address' },
      { col: 'city', label: 'city' },
      { col: 'use_desc', label: 'use' },
      { col: 'year_built', label: 'built' },
      { col: 'bldg_sqft', label: 'sqft' },
      { col: 'assessed_total', label: 'AV', format: 'money' },
      { col: 'roll_year', label: 'roll' },
    ],
    filterable: [{ col: 'use_code', label: 'use' }, { col: 'roll_year', label: 'roll year' }],
    defaultSort: 'assessed_total DESC NULLS LAST',
  },
  {
    key: 'film',
    label: 'Film productions',
    table: 'film_production',
    searchFields: ['title', 'blurb'],
    displayFields: [
      { col: 'title', label: 'title' },
      { col: 'kind', label: 'kind' },
      { col: 'year', label: 'year' },
      { col: 'blurb', label: 'blurb', format: 'truncate' },
      { col: 'source_label', label: 'source' },
    ],
    sourceLabelField: 'source_label',
    filterable: [{ col: 'kind', label: 'kind' }],
    defaultSort: 'year DESC NULLS LAST',
  },
  {
    key: 'filming_location',
    label: 'Filming locations (permits)',
    table: 'filming_location',
    searchFields: ['permit_number', 'role'],
    displayFields: [
      { col: 'permit_number', label: 'permit #' },
      { col: 'role', label: 'role' },
      { col: 'shoot_date_from', label: 'from', format: 'date' },
      { col: 'source_label', label: 'source' },
    ],
    sourceLabelField: 'source_label',
    defaultSort: 'shoot_date_from DESC NULLS LAST',
  },
  {
    key: 'news',
    label: 'Newspaper issues (OCR)',
    table: 'news_issue',
    searchFields: ['paper_name', 'title', 'source_identifier'],
    displayFields: [
      { col: 'paper_short', label: 'paper' },
      { col: 'issue_date', label: 'date' },
      { col: 'source_identifier', label: 'IA id', format: 'truncate' },
      { col: 'ocr_fetched_at', label: 'ocr at', format: 'date' },
    ],
    sourceUrlField: 'ia_url',
    sourceLabelField: 'source_label',
    filterable: [{ col: 'paper_short', label: 'paper' }],
    defaultSort: 'issue_date DESC NULLS LAST',
  },
  {
    key: 'mention',
    label: 'Address mentions in newspapers',
    table: 'news_address_mention',
    searchFields: ['raw_address', 'street_name_normalized', 'context'],
    displayFields: [
      { col: 'raw_address', label: 'mention' },
      { col: 'street_name_normalized', label: 'normalized' },
      { col: 'context', label: 'context', format: 'truncate' },
      { col: 'match_method', label: 'matched' },
    ],
    defaultSort: 'extracted_at DESC',
  },
  {
    key: 'entity',
    label: 'People / entities',
    table: 'entity',
    searchFields: ['display_name', 'kind'],
    displayFields: [
      { col: 'display_name', label: 'name' },
      { col: 'kind', label: 'kind' },
      { col: 'birth_year', label: 'b.' },
      { col: 'death_year', label: 'd.' },
    ],
    filterable: [{ col: 'kind', label: 'kind' }],
    defaultSort: 'display_name ASC',
  },
  {
    key: 'sponsored_placement',
    label: 'Sponsored placement intake',
    table: 'sponsored_placement',
    searchFields: ['headline', 'external_url', 'contact_email', 'permit_number'],
    displayFields: [
      { col: 'headline', label: 'headline' },
      { col: 'source', label: 'source' },
      { col: 'status', label: 'status' },
      { col: 'contact_email', label: 'email' },
      { col: 'submitted_at', label: 'submitted', format: 'date' },
    ],
    filterable: [{ col: 'status', label: 'status' }],
    defaultSort: 'submitted_at DESC',
  },
  {
    key: 'claim_request',
    label: 'Claim requests',
    table: 'claim_request',
    searchFields: ['email', 'status'],
    displayFields: [
      { col: 'email', label: 'email' },
      { col: 'status', label: 'status' },
      { col: 'submitted_at', label: 'submitted', format: 'date' },
    ],
    filterable: [{ col: 'status', label: 'status' }],
    defaultSort: 'submitted_at DESC',
  },
];

const PAGE_SIZE = 50;

function fmt(v: any, format?: string) {
  if (v == null) return <span className="text-ink/30">—</span>;
  if (format === 'date') {
    const s = String(v);
    return s.slice(0, 10);
  }
  if (format === 'money') {
    const n = Number(String(v).replace(/[^0-9.-]/g, ''));
    if (!isFinite(n)) return String(v);
    return `$${n.toLocaleString()}`;
  }
  if (format === 'truncate') {
    const s = String(v);
    return s.length > 80 ? s.slice(0, 80) + '…' : s;
  }
  if (typeof v === 'boolean') return v ? '✓' : '✗';
  return String(v);
}

// Whitelist identifiers from SOURCES so callers can never inject SQL via table/col.
const ALLOWED_TABLES = new Set(SOURCES.map(s => s.table));
const ALLOWED_COLS = new Set<string>(
  SOURCES.flatMap(s => [
    ...s.searchFields,
    ...s.displayFields.map(d => d.col),
    ...(s.filterable ?? []).map(f => f.col),
    s.sourceUrlField, s.sourceLabelField,
  ].filter(Boolean) as string[])
);
const IDENT_RE = /^[a-z_][a-z0-9_]*$/i;

async function getDistinctValues(table: string, col: string): Promise<string[]> {
  if (!IDENT_RE.test(table) || !IDENT_RE.test(col)) return [];
  if (!ALLOWED_TABLES.has(table) || !ALLOWED_COLS.has(col)) return [];
  try {
    const r = await pool.query<{ v: string }>(
      `SELECT DISTINCT ${col} as v FROM ${table} WHERE ${col} IS NOT NULL ORDER BY ${col} LIMIT 50`
    );
    return r.rows.map(x => String(x.v));
  } catch { return []; }
}

// Validate ORDER BY clause: only "<col> [ASC|DESC] [NULLS FIRST|LAST]", optionally comma-separated.
function safeSortClause(sort: string | undefined): string {
  if (!sort) return '1';
  const parts = sort.split(',').map(s => s.trim());
  for (const p of parts) {
    const m = /^([a-z_][a-z0-9_]*)(\s+(ASC|DESC))?(\s+NULLS\s+(FIRST|LAST))?$/i.exec(p);
    if (!m) return '1';
    if (!ALLOWED_COLS.has(m[1]!.toLowerCase()) && !/^\d+$/.test(m[1]!)) return '1';
  }
  return parts.join(', ');
}

async function search(src: Source, q: string, filters: Record<string, string>, page: number) {
  // src.table comes from a hardcoded SOURCES entry, but defense-in-depth:
  if (!IDENT_RE.test(src.table) || !ALLOWED_TABLES.has(src.table)) {
    return { rows: [], total: 0 };
  }
  const where: string[] = [];
  const params: any[] = [];
  // Cap query length to bound seq-scan cost.
  const qSafe = q.slice(0, 200);
  if (qSafe) {
    const orParts: string[] = [];
    for (const f of src.searchFields) {
      if (!IDENT_RE.test(f) || !ALLOWED_COLS.has(f)) continue;
      params.push(`%${qSafe}%`);
      orParts.push(`${f}::text ILIKE $${params.length}`);
    }
    if (orParts.length) where.push(`(${orParts.join(' OR ')})`);
  }
  for (const [k, v] of Object.entries(filters)) {
    if (!v) continue;
    if (!IDENT_RE.test(k) || !ALLOWED_COLS.has(k)) continue;
    params.push(v);
    where.push(`${k}::text = $${params.length}`);
  }
  const whereSql = where.length ? `WHERE ${where.join(' AND ')}` : '';
  const colsList = Array.from(new Set([
    ...src.displayFields.map(d => d.col),
    src.sourceUrlField, src.sourceLabelField,
  ].filter(Boolean) as string[])).filter(c => IDENT_RE.test(c) && ALLOWED_COLS.has(c));
  const cols = colsList.length ? colsList.join(', ') : '1';
  const sortClause = safeSortClause(src.defaultSort);
  const offset = Math.max(0, page) * PAGE_SIZE;
  const sql = `SELECT ${cols} FROM ${src.table} ${whereSql} ORDER BY ${sortClause} LIMIT ${PAGE_SIZE} OFFSET ${offset}`;
  const countSql = `SELECT count(*)::text as n FROM ${src.table} ${whereSql}`;
  try {
    const [rows, count] = await Promise.all([
      pool.query<any>(sql, params),
      pool.query<{ n: string }>(countSql, params),
    ]);
    const n = Number(count.rows[0]?.n ?? 0);
    return { rows: rows.rows, total: Number.isFinite(n) ? n : 0 };
  } catch {
    // Suppress error details from leaking into the response.
    return { rows: [], total: 0 };
  }
}

export default async function AdminDataPage({
  searchParams,
}: {
  searchParams: Record<string, string | undefined>;
}) {
  const sourceKey = searchParams.source ?? 'listing';
  const src = SOURCES.find(s => s.key === sourceKey) ?? SOURCES[0]!;
  const q = (searchParams.q ?? '').trim().slice(0, 200);
  const rawPage = Number(searchParams.page ?? '0');
  const page = Number.isFinite(rawPage) ? Math.min(10000, Math.max(0, Math.floor(rawPage))) : 0;
  const filters: Record<string, string> = {};
  for (const f of src.filterable ?? []) {
    if (searchParams[f.col]) filters[f.col] = searchParams[f.col]!;
  }

  const { rows, total } = await search(src, q, filters, page);

  // Filter dropdown values
  const filterOpts: Record<string, string[]> = {};
  for (const f of src.filterable ?? []) {
    filterOpts[f.col] = await getDistinctValues(src.table, f.col);
  }

  // Sentinel: caller passes a key set to `null` to explicitly remove it; passing
  // `undefined` means "leave alone, inherit current". The previous version
  // conflated the two and made prev-page from page 1 a no-op.
  const buildHref = (overrides: Record<string, string | null>) => {
    const p = new URLSearchParams();
    p.set('source', sourceKey);
    if (!('q' in overrides)) {
      if (q) p.set('q', q);
    } else if (overrides.q) {
      p.set('q', overrides.q);
    }
    for (const [k, v] of Object.entries(filters)) {
      if (k in overrides) {
        if (overrides[k]) p.set(k, overrides[k]!);
      } else if (v) p.set(k, v);
    }
    if ('page' in overrides) {
      if (overrides.page) p.set('page', overrides.page);
    } else if (page > 0) p.set('page', String(page));
    return `/admin/data?${p}`;
  };

  return (
    <div className="space-y-6">
      <header>
        <h1 className="font-display text-4xl text-ink mb-2">Data browser</h1>
        <p className="text-sm text-ink/60">Search every source. Click any column header to deep-link in.</p>
      </header>

      <nav className="flex flex-wrap gap-1 border-b border-ink/10 pb-2">
        {SOURCES.map(s => (
          <Link key={s.key} href={`/admin/data?source=${s.key}`}
            className={`text-xs uppercase tracking-wider px-3 py-2 transition ${
              s.key === sourceKey
                ? 'bg-ink text-sand'
                : 'text-ink/60 hover:bg-ink/10'
            }`}>
            {s.label}
          </Link>
        ))}
      </nav>

      <form className="flex flex-wrap items-end gap-3 bg-white border border-ink/10 p-4" action="/admin/data">
        <input type="hidden" name="source" value={sourceKey} />
        <label className="flex flex-col gap-1 text-[10px] uppercase tracking-[0.15em] text-ink/55 flex-1 min-w-[300px]">
          search
          <input
            name="q" type="search" defaultValue={q}
            placeholder={`Search ${src.searchFields.join(', ')}…`}
            className="border border-ink/20 px-3 py-2 text-sm font-mono"
          />
        </label>
        {(src.filterable ?? []).map(f => (
          <label key={f.col} className="flex flex-col gap-1 text-[10px] uppercase tracking-[0.15em] text-ink/55">
            {f.label}
            <select name={f.col} defaultValue={filters[f.col] ?? ''}
              className="border border-ink/20 px-3 py-2 text-sm font-mono min-w-[140px]">
              <option value="">All</option>
              {(filterOpts[f.col] ?? []).map(v => (
                <option key={v} value={v}>{v}</option>
              ))}
            </select>
          </label>
        ))}
        <button type="submit" className="bg-ink text-sand px-5 py-2 text-xs uppercase tracking-wider hover:bg-moss transition">
          Search
        </button>
      </form>

      <p className="text-sm text-ink/60">
        <span className="font-mono tabular-nums font-medium text-ink">{total.toLocaleString()}</span>
        {' '}rows in <code className="font-mono text-xs">{src.table}</code>
        {q && <> matching &ldquo;{q}&rdquo;</>}
        {Object.keys(filters).length > 0 && <> · filtered by {Object.entries(filters).map(([k, v]) => `${k}=${v}`).join(', ')}</>}
        {' · '}page {page + 1} of {Math.ceil(total / PAGE_SIZE) || 1}
      </p>

      <div className="bg-white border border-ink/10 overflow-x-auto">
        <table className="w-full text-xs">
          <thead className="bg-ink/[0.04] text-[10px] uppercase tracking-[0.15em] text-ink/60">
            <tr>
              {src.displayFields.map(f => (
                <th key={f.col} className="text-left p-2.5 font-mono">{f.label}</th>
              ))}
              {src.sourceUrlField && <th className="text-left p-2.5 font-mono">source</th>}
            </tr>
          </thead>
          <tbody>
            {rows.length === 0 ? (
              <tr><td colSpan={src.displayFields.length + 1} className="p-6 text-center text-ink/40 italic">no rows</td></tr>
            ) : (
              rows.map((r, i) => (
                <tr key={i} className="border-t border-ink/5 hover:bg-ink/[0.02]">
                  {src.displayFields.map(f => (
                    <td key={f.col} className="p-2.5 align-top font-mono">
                      {fmt(r[f.col], f.format)}
                    </td>
                  ))}
                  {src.sourceUrlField && (() => {
                    const safeUrl = safeHttpUrl(r[src.sourceUrlField!]);
                    return (
                      <td className="p-2.5 align-top font-mono">
                        {safeUrl ? (
                          <a href={safeUrl} target="_blank" rel="noreferrer noopener" className="text-coral hover:underline">
                            {r[src.sourceLabelField!] ?? 'link'} ↗
                          </a>
                        ) : <span className="text-ink/30">—</span>}
                      </td>
                    );
                  })()}
                </tr>
              ))
            )}
          </tbody>
        </table>
      </div>

      <div className="flex justify-between items-center text-xs">
        {page > 0 ? (
          <Link href={buildHref({ page: page === 1 ? null : String(page - 1) })} className="text-ink/70 hover:text-coral">← prev</Link>
        ) : <span />}
        {(page + 1) * PAGE_SIZE < total ? (
          <Link href={buildHref({ page: String(page + 1) })} className="text-ink/70 hover:text-coral">next →</Link>
        ) : <span />}
      </div>
    </div>
  );
}