← back to Norma
app/api/search/route.ts
193 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import { getOrgId } from '@/lib/orgId';
/* ═══════════════════════════════════════════════════════════════════════════
GET /api/search?q=jillian+berman&limit=20
Global search across journalists, articles, politicians, organizations,
petitions, topics, contacts, grants, and foundations.
═══════════════════════════════════════════════════════════════════════════ */
interface SearchResult {
id: string;
type: 'journalist' | 'article' | 'politician' | 'organization' | 'petition' | 'topic' | 'contact' | 'grant' | 'foundation';
title: string;
subtitle: string;
meta?: string;
url?: string;
}
export async function GET(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
const { searchParams } = new URL(request.url);
const q = searchParams.get('q')?.trim();
const limit = Math.min(parseInt(searchParams.get('limit') || '25', 10), 50);
if (!q || q.length < 2) {
return NextResponse.json({ results: [], query: q });
}
const orgId = auth.role === 'admin' ? getOrgId(request) : auth.orgId;
const pattern = `%${q}%`;
// For org-scoped tables, use parameterized org_id filter
const orgScopedFilter = orgId ? ' AND org_id = $3' : '';
const orgScopedParams = orgId ? [pattern, limit, orgId] : [pattern, limit];
try {
// Run all searches in parallel for speed
const [journalists, articles, politicians, organizations, petitions, topics, contacts, grants, foundations] = await Promise.all([
// Journalists (no org_id column)
query<{ id: string; name: string; outlet: string; beat: string }>(
`SELECT id, name, outlet, COALESCE(beat, '') as beat
FROM journalists
WHERE name ILIKE $1 OR outlet ILIKE $1 OR beat ILIKE $1
ORDER BY CASE WHEN name ILIKE $1 THEN 0 ELSE 1 END, name
LIMIT $2`,
[pattern, limit]
),
// Articles (no org_id column)
query<{ id: string; title: string; outlet: string; published_date: string; url: string; journalist_name: string }>(
`SELECT ja.id, ja.title, ja.outlet, ja.published_date::text,
COALESCE(ja.url, '') as url,
COALESCE(j.name, '') as journalist_name
FROM journalist_articles ja
LEFT JOIN journalists j ON j.id = ja.journalist_id
WHERE ja.title ILIKE $1 OR ja.outlet ILIKE $1 OR ja.summary ILIKE $1
ORDER BY ja.published_date DESC NULLS LAST
LIMIT $2`,
[pattern, limit]
),
// Politicians (no org_id column)
query<{ id: string; name: string; party: string; state: string; title: string }>(
`SELECT id, name, COALESCE(party, '') as party, COALESCE(state, '') as state, COALESCE(title, '') as title
FROM politicians
WHERE name ILIKE $1 OR state ILIKE $1 OR title ILIKE $1
ORDER BY CASE WHEN name ILIKE $1 THEN 0 ELSE 1 END, name
LIMIT $2`,
[pattern, limit]
),
// Organizations (no org_id column)
query<{ id: string; name: string; category: string; state: string }>(
`SELECT id, name, COALESCE(category, '') as category, COALESCE(state, '') as state
FROM organizations
WHERE name ILIKE $1 OR category ILIKE $1 OR description ILIKE $1
ORDER BY CASE WHEN name ILIKE $1 THEN 0 ELSE 1 END, name
LIMIT $2`,
[pattern, limit]
),
// Petitions (org-scoped)
query<{ id: string; title: string; category: string; url: string }>(
`SELECT id, title, COALESCE(category, '') as category, COALESCE(url, '') as url
FROM petitions
WHERE (title ILIKE $1 OR description ILIKE $1 OR category ILIKE $1)${orgScopedFilter}
ORDER BY created_at DESC
LIMIT $2`,
orgScopedParams
),
// Topics (no org_id column)
query<{ id: string; name: string; urgency: string; category: string }>(
`SELECT id, name, COALESCE(urgency, '') as urgency, COALESCE(category, '') as category
FROM pulse_topics
WHERE name ILIKE $1 OR category ILIKE $1 OR description ILIKE $1
ORDER BY CASE WHEN name ILIKE $1 THEN 0 ELSE 1 END, name
LIMIT $2`,
[pattern, limit]
),
// Contacts (no org_id column)
query<{ id: string; first_name: string; last_name: string; company: string; position: string }>(
`SELECT id, COALESCE(first_name, '') as first_name, COALESCE(last_name, '') as last_name,
COALESCE(company, '') as company, COALESCE(position, '') as position
FROM contacts
WHERE first_name ILIKE $1 OR last_name ILIKE $1 OR company ILIKE $1
OR (first_name || ' ' || last_name) ILIKE $1
ORDER BY CASE WHEN (first_name || ' ' || last_name) ILIKE $1 THEN 0 ELSE 1 END
LIMIT $2`,
[pattern, limit]
),
// Grants (org-scoped)
query<{ id: string; title: string; funder: string; amount_max: string }>(
`SELECT id, COALESCE(title, '') as title, COALESCE(funder, '') as funder,
COALESCE(amount_max::text, '') as amount_max
FROM grants
WHERE (title ILIKE $1 OR funder ILIKE $1 OR description ILIKE $1)${orgScopedFilter}
ORDER BY CASE WHEN title ILIKE $1 THEN 0 ELSE 1 END
LIMIT $2`,
orgScopedParams
),
// Foundations (no org_id column)
query<{ id: string; name: string; state: string; focus_areas: string[] }>(
`SELECT id, name, COALESCE(state, '') as state, focus_areas
FROM foundations
WHERE name ILIKE $1 OR state ILIKE $1 OR president ILIKE $1
ORDER BY CASE WHEN name ILIKE $1 THEN 0 ELSE 1 END, name
LIMIT $2`,
[pattern, limit]
),
]);
// Build unified results
const results: SearchResult[] = [];
for (const r of journalists.rows) {
results.push({ id: r.id, type: 'journalist', title: r.name, subtitle: r.outlet, meta: r.beat });
}
for (const r of articles.rows) {
results.push({ id: r.id, type: 'article', title: r.title, subtitle: `${r.outlet} • ${r.journalist_name}`, meta: r.published_date, url: r.url });
}
for (const r of politicians.rows) {
results.push({ id: r.id, type: 'politician', title: r.name, subtitle: r.title || `${r.party}-${r.state}`, meta: r.state });
}
for (const r of organizations.rows) {
results.push({ id: r.id, type: 'organization', title: r.name, subtitle: r.category, meta: r.state });
}
for (const r of petitions.rows) {
results.push({ id: r.id, type: 'petition', title: r.title, subtitle: r.category, url: r.url });
}
for (const r of topics.rows) {
results.push({ id: r.id, type: 'topic', title: r.name, subtitle: r.category, meta: r.urgency });
}
for (const r of contacts.rows) {
results.push({ id: r.id, type: 'contact', title: `${r.first_name} ${r.last_name}`.trim(), subtitle: r.company, meta: r.position });
}
for (const r of grants.rows) {
results.push({ id: r.id, type: 'grant', title: r.title, subtitle: r.funder, meta: r.amount_max ? `$${parseInt(r.amount_max).toLocaleString()}` : '' });
}
for (const r of foundations.rows) {
results.push({ id: r.id, type: 'foundation', title: r.name, subtitle: r.state, meta: r.focus_areas?.slice(0, 2).join(', ') || '' });
}
return NextResponse.json({
results,
query: q,
counts: {
journalists: journalists.rows.length,
articles: articles.rows.length,
politicians: politicians.rows.length,
organizations: organizations.rows.length,
petitions: petitions.rows.length,
topics: topics.rows.length,
contacts: contacts.rows.length,
grants: grants.rows.length,
foundations: foundations.rows.length,
},
total: results.length,
});
} catch (err) {
console.error('[search] error:', (err as Error).message);
return NextResponse.json({ error: 'Search failed' }, { status: 500 });
}
}