← back to Rentv Adintel
src/routes/api.js
676 lines
'use strict';
/**
* RENTV Advertiser Intelligence — /api/v1 router (spec §29).
* Express Router, CommonJS, pg 8, zero external dependencies.
* All list endpoints: { rows, nextCursor, total, demo? }
* All writes: append audit_logs row. Hand-rolled validation, no zod.
*/
const express = require('express');
const { pool, query } = require('../../db');
const T = require('../../lib/types');
const scoring = require('../../lib/scoring');
const { assertNotPanelistMislabeledAsSponsor } = require('../../lib/classification');
const router = express.Router();
// ── Request ID middleware ──────────────────────────────────────────────────────
router.use((req, res, next) => {
req.requestId = require('crypto').randomUUID();
res.set('X-Request-Id', req.requestId);
next();
});
// ── Helper: structured JSON error ────────────────────────────────────────────
function apiErr(res, code, error, message) {
return res.status(code).json({ error, message: message || error });
}
// ── Helper: safe integer ─────────────────────────────────────────────────────
function safeInt(v, def, min, max) {
const n = parseInt(v, 10);
if (isNaN(n)) return def;
if (min != null && n < min) return min;
if (max != null && n > max) return max;
return n;
}
// ── Helper: whitelist sort key ────────────────────────────────────────────────
function safeSort(v, allowed, def) {
return allowed.includes(v) ? v : def;
}
// ── Helper: UUID or null ──────────────────────────────────────────────────────
function isUuid(s) {
return typeof s === 'string' && /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i.test(s);
}
// ── Helper: check if a table exists ─────────────────────────────────────────
async function tableExists(tableName) {
try {
const r = await query(
`SELECT 1 FROM information_schema.tables WHERE table_schema='public' AND table_name=$1`,
[tableName]
);
return r.rows.length > 0;
} catch (_) { return false; }
}
// ── Helper: demo-safe query ───────────────────────────────────────────────────
// Returns { rows, demo } — if table missing/empty returns {rows:[], demo:true}
async function safeQuery(sql, params, demoCheck) {
try {
const r = await query(sql, params || []);
if (demoCheck && r.rows.length === 0) return { rows: [], demo: true };
return { rows: r.rows, demo: false };
} catch (e) {
if (e.code === '42P01') return { rows: [], demo: true }; // undefined_table
throw e;
}
}
// ── Helper: write audit log ───────────────────────────────────────────────────
async function auditLog(action, entityTable, entityId, detail, actor) {
try {
await query(
`INSERT INTO audit_logs (action, entity_table, entity_id, actor, detail)
VALUES ($1, $2, $3, $4, $5)`,
[action, entityTable || null, entityId || null, actor || 'api', JSON.stringify(detail || {})]
);
} catch (_) { /* non-fatal */ }
}
// ═══════════════════════════════════════════════════════════════════════════════
// GET /advertisers
// Filters: market=CA|AZ|ALL, verifiedOnly=1, category, q, cursor, limit, sort
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/advertisers', async (req, res) => {
try {
const limit = safeInt(req.query.limit, 50, 1, 200);
const cursor = req.query.cursor || null;
const market = ['CA', 'AZ', 'ALL'].includes(req.query.market) ? req.query.market : 'ALL';
const verifiedOnly = req.query.verifiedOnly === '1';
const category = req.query.category ? String(req.query.category).slice(0, 120) : null;
const q = req.query.q ? String(req.query.q).slice(0, 200) : null;
const ALLOWED_SORTS = ['display_name', 'last_seen_at', 'created_at', 'score'];
const sort = safeSort(req.query.sort, ALLOWED_SORTS, 'last_seen_at');
const conditions = [];
const params = [];
function addParam(v) { params.push(v); return '$' + params.length; }
if (market !== 'ALL') {
conditions.push(`o.headquarters_state = ${addParam(market)}`);
}
if (verifiedOnly) {
conditions.push(`(
SELECT COUNT(*) FROM ad_sightings s WHERE s.organization_id = o.id
AND s.relationship_status = ANY(${addParam(T.VERIFIED_STATUSES)})
) > 0`);
}
if (category) {
conditions.push(`o.advertiser_categories @> ${addParam(JSON.stringify([category]))}::jsonb`);
}
if (q) {
conditions.push(`(
o.normalized_name ILIKE ${addParam('%' + q.replace(/[%_]/g, '\\$&') + '%')}
OR o.display_name ILIKE ${addParam('%' + q.replace(/[%_]/g, '\\$&') + '%')}
OR o.domain ILIKE ${addParam('%' + q.replace(/[%_]/g, '\\$&') + '%')}
)`);
// last param added twice — fix the indices
const last = params.length;
params[last - 1] = '%' + q.replace(/[%_]/g, '\\$&') + '%';
params[last - 2] = '%' + q.replace(/[%_]/g, '\\$&') + '%';
}
if (cursor) {
conditions.push(`o.created_at < ${addParam(cursor)}`);
}
const where = conditions.length ? 'WHERE ' + conditions.join(' AND ') : '';
const sortSql = sort === 'score'
? 'opp.score DESC NULLS LAST'
: sort === 'display_name'
? 'o.display_name ASC'
: 'o.last_seen_at DESC NULLS LAST';
let result;
try {
result = await query(`
SELECT
o.id, o.display_name, o.legal_name, o.domain, o.headquarters_state,
o.headquarters_city, o.advertiser_categories, o.active_status,
o.logo_asset_id, o.first_seen_at, o.last_seen_at, o.created_at,
(SELECT s.relationship_status
FROM ad_sightings s
WHERE s.organization_id = o.id
ORDER BY array_position(
ARRAY['VERIFIED_ADVERTISER','VERIFIED_CONFERENCE_SPONSOR','VERIFIED_EXHIBITOR',
'VERIFIED_MEDIA_PARTNER','VERIFIED_CONTENT_PARTNER','PAST_ADVERTISER',
'LIKELY_PROSPECT','SPEAKER_OR_PANELIST_ONLY','RESEARCH_NEEDED','DISQUALIFIED'],
s.relationship_status)
LIMIT 1
) AS top_status,
(SELECT COUNT(*) FROM ad_sightings s WHERE s.organization_id = o.id
AND s.verification_status = 'VERIFIED') AS verified_sightings,
(SELECT s.observed_at FROM ad_sightings s WHERE s.organization_id = o.id
ORDER BY s.observed_at DESC NULLS LAST LIMIT 1) AS last_sighting_at,
(SELECT COUNT(*) FROM event_relationships er WHERE er.organization_id = o.id) AS conference_activity,
(SELECT opp.score FROM opportunity_scores opp WHERE opp.organization_id = o.id
ORDER BY opp.computed_at DESC LIMIT 1) AS score,
(SELECT cp.value FROM contact_points cp WHERE cp.organization_id = o.id
AND cp.type = 'BUSINESS_PHONE' AND cp.do_not_contact = false LIMIT 1) AS best_phone,
(SELECT cp.value FROM contact_points cp WHERE cp.organization_id = o.id
AND cp.type = 'BUSINESS_EMAIL' AND cp.do_not_contact = false LIMIT 1) AS best_email,
(SELECT cp.value FROM contact_points cp WHERE cp.organization_id = o.id
AND cp.type = 'WEBSITE' LIMIT 1) AS website,
(SELECT cp.value FROM contact_points cp WHERE cp.organization_id = o.id
AND cp.type = 'LINKEDIN' LIMIT 1) AS linkedin_url,
(SELECT p.full_name FROM people p WHERE p.organization_id = o.id
ORDER BY array_position(
ARRAY['CHIEF_MARKETING_OFFICER','VP_MARKETING','MARKETING_DIRECTOR',
'COMMUNICATIONS_PR_DIRECTOR','EVENTS_PARTNERSHIPS_SPONSORSHIPS_DIRECTOR',
'BUSINESS_DEVELOPMENT_DIRECTOR','REGIONAL_PRESIDENT_MARKET_LEADER',
'MANAGING_DIRECTOR_PRINCIPAL','PUBLIC_MEDIA_CONTACT','GENERAL_COMPANY_CONTACT'],
p.role_category) ASC NULLS LAST LIMIT 1) AS best_contact_name,
(SELECT s.source_page_url FROM ad_sightings s WHERE s.organization_id = o.id
ORDER BY s.observed_at DESC NULLS LAST LIMIT 1) AS latest_source_url
FROM organizations o
LEFT JOIN opportunity_scores opp ON opp.organization_id = o.id
AND opp.computed_at = (SELECT MAX(opp2.computed_at) FROM opportunity_scores opp2 WHERE opp2.organization_id = o.id)
${where}
ORDER BY ${sortSql}, o.id ASC
LIMIT ${addParam(limit + 1)}
`, params);
} catch (e) {
if (e.code === '42P01') return res.json({ rows: [], nextCursor: null, total: 0, demo: true });
throw e;
}
const rows = result.rows;
const hasMore = rows.length > limit;
if (hasMore) rows.pop();
const nextCursor = hasMore ? rows[rows.length - 1].created_at : null;
// Total count (estimated) — fast path
let total = 0;
try {
const ct = await query(`SELECT COUNT(*) FROM organizations o ${where}`, params.slice(0, params.length - 1));
total = parseInt(ct.rows[0].count, 10);
} catch (_) {}
const demo = total === 0 && rows.length === 0;
res.json({ rows, nextCursor, total, demo: demo || undefined });
} catch (e) {
console.error('[api] GET /advertisers', e.message);
apiErr(res, 500, 'query_error', e.message);
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /advertisers/:id
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/advertisers/:id', async (req, res) => {
const { id } = req.params;
if (!isUuid(id)) return apiErr(res, 400, 'invalid_id', 'id must be a UUID');
try {
const orgRes = await query(`SELECT * FROM organizations WHERE id = $1`, [id]);
if (!orgRes.rows.length) return apiErr(res, 404, 'not_found', 'Organization not found');
const org = orgRes.rows[0];
const [sightings, events, contacts, latestScore, evidence] = await Promise.all([
query(`SELECT s.*, p.name AS publication_name FROM ad_sightings s
LEFT JOIN publications p ON p.id = s.publication_id
WHERE s.organization_id = $1 ORDER BY s.observed_at DESC NULLS LAST LIMIT 50`, [id]),
query(`SELECT er.*, e.name AS event_name, e.start_date, e.city, e.state, e.official_url
FROM event_relationships er JOIN events e ON e.id = er.event_id
WHERE er.organization_id = $1 ORDER BY e.start_date DESC NULLS LAST LIMIT 50`, [id]),
query(`SELECT cp.*, p.full_name AS person_name, p.public_title AS person_title
FROM contact_points cp LEFT JOIN people p ON p.id = cp.person_id
WHERE cp.organization_id = $1 AND cp.do_not_contact = false
ORDER BY cp.confidence DESC LIMIT 50`, [id]),
query(`SELECT * FROM opportunity_scores WHERE organization_id = $1
ORDER BY computed_at DESC LIMIT 1`, [id]),
query(`SELECT er.* FROM evidence_records er
WHERE er.id IN (
SELECT evidence_id FROM ad_sightings WHERE organization_id = $1 AND evidence_id IS NOT NULL
LIMIT 20
)`, [id]),
]);
const scoreRow = latestScore.rows[0];
let scoreExplain = null;
if (scoreRow && scoreRow.factors) {
try { scoreExplain = scoring.explainScore(scoreRow.factors); } catch (_) {}
}
res.json({
...org,
sightings: sightings.rows,
events: events.rows,
contacts: contacts.rows,
score: scoreRow ? { ...scoreRow, explain: scoreExplain } : null,
evidence: evidence.rows,
});
} catch (e) {
console.error('[api] GET /advertisers/:id', e.message);
apiErr(res, 500, 'query_error', e.message);
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /ads
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/ads', async (req, res) => {
const limit = safeInt(req.query.limit, 50, 1, 200);
const cursor = req.query.cursor || null;
const params = [];
const conds = [];
function p(v) { params.push(v); return '$' + params.length; }
if (cursor) conds.push(`s.created_at < ${p(cursor)}`);
const where = conds.length ? 'WHERE ' + conds.join(' AND ') : '';
try {
const r = await query(`
SELECT s.*, o.display_name AS org_name, p.name AS publication_name
FROM ad_sightings s
LEFT JOIN organizations o ON o.id = s.organization_id
LEFT JOIN publications p ON p.id = s.publication_id
${where}
ORDER BY s.observed_at DESC NULLS LAST, s.id ASC
LIMIT ${p(limit + 1)}
`, params);
const rows = r.rows;
const hasMore = rows.length > limit;
if (hasMore) rows.pop();
const demo = rows.length === 0;
res.json({ rows, nextCursor: hasMore ? rows[rows.length - 1].created_at : null, total: rows.length, demo: demo || undefined });
} catch (e) {
if (e.code === '42P01') return res.json({ rows: [], nextCursor: null, total: 0, demo: true });
apiErr(res, 500, 'query_error', e.message);
}
});
// GET /ads/:id
router.get('/ads/:id', async (req, res) => {
if (!isUuid(req.params.id)) return apiErr(res, 400, 'invalid_id', 'id must be a UUID');
try {
const r = await query(`
SELECT s.*, o.display_name AS org_name, p.name AS publication_name,
er.source_url AS evidence_source_url, er.source_title AS evidence_title,
er.excerpt AS evidence_excerpt, er.observed_at AS evidence_observed_at
FROM ad_sightings s
LEFT JOIN organizations o ON o.id = s.organization_id
LEFT JOIN publications p ON p.id = s.publication_id
LEFT JOIN evidence_records er ON er.id = s.evidence_id
WHERE s.id = $1
`, [req.params.id]);
if (!r.rows.length) return apiErr(res, 404, 'not_found', 'Ad sighting not found');
res.json(r.rows[0]);
} catch (e) {
if (e.code === '42P01') return res.json({ demo: true, rows: [] });
apiErr(res, 500, 'query_error', e.message);
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /events
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/events', async (req, res) => {
const limit = safeInt(req.query.limit, 50, 1, 200);
const cursor = req.query.cursor || null;
const params = [];
function p(v) { params.push(v); return '$' + params.length; }
const conds = cursor ? [`e.start_date < ${p(cursor)}`] : [];
const where = conds.length ? 'WHERE ' + conds.join(' AND ') : '';
try {
const r = await query(`
SELECT e.*,
(SELECT COUNT(*) FROM event_relationships er
WHERE er.event_id = e.id AND er.relationship_status LIKE 'VERIFIED_%') AS sponsor_count,
(SELECT COUNT(*) FROM event_relationships er
WHERE er.event_id = e.id AND er.relationship_status = 'SPEAKER_OR_PANELIST_ONLY') AS panelist_count
FROM events e ${where}
ORDER BY e.start_date DESC NULLS LAST LIMIT ${p(limit + 1)}
`, params);
const rows = r.rows; const hasMore = rows.length > limit; if (hasMore) rows.pop();
res.json({ rows, nextCursor: hasMore ? rows[rows.length - 1].start_date : null, total: rows.length, demo: rows.length === 0 || undefined });
} catch (e) {
if (e.code === '42P01') return res.json({ rows: [], nextCursor: null, total: 0, demo: true });
apiErr(res, 500, 'query_error', e.message);
}
});
// GET /events/:id
router.get('/events/:id', async (req, res) => {
if (!isUuid(req.params.id)) return apiErr(res, 400, 'invalid_id', 'id must be a UUID');
try {
const [evRes, rels] = await Promise.all([
query(`SELECT * FROM events WHERE id = $1`, [req.params.id]),
query(`SELECT er.*, o.display_name AS org_name, o.domain, o.advertiser_categories
FROM event_relationships er
JOIN organizations o ON o.id = er.organization_id
WHERE er.event_id = $1
ORDER BY array_position(
ARRAY['VERIFIED_CONFERENCE_SPONSOR','VERIFIED_EXHIBITOR','VERIFIED_MEDIA_PARTNER',
'VERIFIED_CONTENT_PARTNER','SPEAKER_OR_PANELIST_ONLY','LIKELY_PROSPECT','RESEARCH_NEEDED'],
er.relationship_status), o.display_name`, [req.params.id]),
]);
if (!evRes.rows.length) return apiErr(res, 404, 'not_found', 'Event not found');
const ev = evRes.rows[0];
const sponsors = rels.rows.filter(r => r.relationship_status !== 'SPEAKER_OR_PANELIST_ONLY');
const panelists = rels.rows.filter(r => r.relationship_status === 'SPEAKER_OR_PANELIST_ONLY');
res.json({ ...ev, sponsors, panelists, relationships: rels.rows });
} catch (e) {
if (e.code === '42P01') return res.json({ demo: true });
apiErr(res, 500, 'query_error', e.message);
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /contacts
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/contacts', async (req, res) => {
const limit = safeInt(req.query.limit, 100, 1, 500);
const cursor = req.query.cursor || null;
const q = req.query.q ? String(req.query.q).slice(0, 200) : null;
const params = []; const conds = [];
function p(v) { params.push(v); return '$' + params.length; }
if (q) {
const like = '%' + q.replace(/[%_]/g, '\\$&') + '%';
conds.push(`(pe.full_name ILIKE ${p(like)} OR o.display_name ILIKE ${p(like)} OR cp.value ILIKE ${p(like)})`);
}
if (cursor) conds.push(`cp.created_at < ${p(cursor)}`);
const where = conds.length ? 'WHERE ' + conds.join(' AND ') : '';
try {
const r = await query(`
SELECT cp.*, pe.full_name, pe.public_title, pe.linkedin_url,
o.display_name AS org_name, o.id AS organization_id
FROM contact_points cp
LEFT JOIN people pe ON pe.id = cp.person_id
LEFT JOIN organizations o ON o.id = cp.organization_id
${where}
AND cp.do_not_contact = false
ORDER BY cp.confidence DESC, cp.created_at DESC
LIMIT ${p(limit + 1)}
`, params);
const rows = r.rows; const hasMore = rows.length > limit; if (hasMore) rows.pop();
res.json({ rows, nextCursor: hasMore ? rows[rows.length - 1].created_at : null, total: rows.length, demo: rows.length === 0 || undefined });
} catch (e) {
if (e.code === '42P01') return res.json({ rows: [], nextCursor: null, total: 0, demo: true });
apiErr(res, 500, 'query_error', e.message);
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /prospects — orgs ranked by latest opportunity score, non-verified
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/prospects', async (req, res) => {
const limit = safeInt(req.query.limit, 50, 1, 200);
const market = ['CA', 'AZ', 'ALL'].includes(req.query.market) ? req.query.market : 'ALL';
const params = []; const conds = [];
function p(v) { params.push(v); return '$' + params.length; }
// Prospects = no VERIFIED_* sightings
conds.push(`NOT EXISTS (
SELECT 1 FROM ad_sightings s WHERE s.organization_id = o.id
AND s.relationship_status = ANY(${p(T.VERIFIED_STATUSES)})
)`);
if (market !== 'ALL') conds.push(`o.headquarters_state = ${p(market)}`);
const where = 'WHERE ' + conds.join(' AND ');
try {
const r = await query(`
SELECT o.id, o.display_name, o.domain, o.headquarters_state, o.headquarters_city,
o.advertiser_categories, o.last_seen_at, o.created_at,
opp.score, opp.computed_at AS score_computed_at
FROM organizations o
LEFT JOIN opportunity_scores opp ON opp.organization_id = o.id
AND opp.computed_at = (SELECT MAX(o2.computed_at) FROM opportunity_scores o2 WHERE o2.organization_id = o.id)
${where}
ORDER BY opp.score DESC NULLS LAST, o.last_seen_at DESC NULLS LAST
LIMIT ${p(limit)}
`, params);
res.json({ rows: r.rows, total: r.rows.length, demo: r.rows.length === 0 || undefined });
} catch (e) {
if (e.code === '42P01') return res.json({ rows: [], total: 0, demo: true });
apiErr(res, 500, 'query_error', e.message);
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /analytics/summary
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/analytics/summary', async (req, res) => {
try {
const r = await query(`
SELECT
(SELECT COUNT(*) FROM organizations) AS total_orgs,
(SELECT COUNT(*) FROM ad_sightings WHERE relationship_status = 'VERIFIED_ADVERTISER') AS verified_advertisers,
(SELECT COUNT(*) FROM ad_sightings WHERE relationship_status = 'VERIFIED_CONFERENCE_SPONSOR') AS verified_sponsors,
(SELECT COUNT(*) FROM organizations WHERE headquarters_state = 'CA') AS california_orgs,
(SELECT COUNT(*) FROM organizations WHERE headquarters_state = 'AZ') AS arizona_orgs,
(SELECT COUNT(*) FROM ad_sightings WHERE observed_at >= NOW() - INTERVAL '30 days') AS sightings_30d,
(SELECT COUNT(*) FROM events) AS total_events,
(SELECT COUNT(*) FROM contact_points WHERE do_not_contact = false) AS total_contacts
`);
const summary = r.rows[0] || {};
const demo = Object.values(summary).every(v => v === '0' || v === 0);
res.json({ ...summary, demo: demo || undefined });
} catch (e) {
if (e.code === '42P01') return res.json({ demo: true });
apiErr(res, 500, 'query_error', e.message);
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /analytics/ga4
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/analytics/ga4', async (req, res) => {
const days = safeInt(req.query.days, 30, 7, 365);
try {
const r = await query(`
SELECT * FROM ga4_daily_metrics
WHERE metric_date >= CURRENT_DATE - $1::int
ORDER BY metric_date DESC
`, [days]);
const demo = r.rows.length === 0 || r.rows[0].is_demo;
res.json({ rows: r.rows, total: r.rows.length, demo: demo || undefined });
} catch (e) {
if (e.code === '42P01') return res.json({ rows: [], total: 0, demo: true });
apiErr(res, 500, 'query_error', e.message);
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /analytics/gsc
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/analytics/gsc', async (req, res) => {
const limit = safeInt(req.query.limit, 50, 1, 500);
const days = safeInt(req.query.days, 28, 7, 365);
try {
const r = await query(`
SELECT * FROM gsc_query_metrics
WHERE metric_date >= CURRENT_DATE - $1::int
ORDER BY impressions DESC NULLS LAST
LIMIT $2
`, [days, limit]);
const demo = r.rows.length === 0 || r.rows[0].is_demo;
res.json({ rows: r.rows, total: r.rows.length, demo: demo || undefined });
} catch (e) {
if (e.code === '42P01') return res.json({ rows: [], total: 0, demo: true });
apiErr(res, 500, 'query_error', e.message);
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /sources
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/sources', async (req, res) => {
try {
const r = await query(`
SELECT sp.*,
(SELECT COUNT(*) FROM sources s WHERE s.source_policy_id = sp.id) AS item_count,
(SELECT MAX(shc.checked_at) FROM source_health_checks shc WHERE shc.source_key = sp.source_key) AS last_checked
FROM source_policies sp
ORDER BY sp.display_name ASC
`);
const demo = r.rows.length === 0;
res.json({ rows: r.rows, total: r.rows.length, demo: demo || undefined });
} catch (e) {
if (e.code === '42P01') return res.json({ rows: [], total: 0, demo: true });
apiErr(res, 500, 'query_error', e.message);
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// POST /imports — 202 stub (queues import job)
// ═══════════════════════════════════════════════════════════════════════════════
router.post('/imports', async (req, res) => {
const kind = req.body && req.body.kind ? String(req.body.kind).slice(0, 80) : 'MANUAL_UPLOAD';
try {
await auditLog('import_queued', 'imports', null, { kind, body: req.body }, 'api');
} catch (_) {}
res.status(202).json({ queued: true, kind, message: 'Import job queued — upload your file via the /imports UI.' });
});
// ═══════════════════════════════════════════════════════════════════════════════
// POST /research/jobs — 202 stub
// ═══════════════════════════════════════════════════════════════════════════════
router.post('/research/jobs', async (req, res) => {
const jobType = req.body && req.body.type ? String(req.body.type).slice(0, 80) : 'GENERAL';
const dryRun = req.body && req.body.dryRun ? true : false;
try {
await query(
`INSERT INTO ingestion_runs (source_key, dry_run, status, stats)
VALUES ($1, $2, 'PENDING', '{}')`,
[jobType, dryRun]
);
} catch (_) {}
res.status(202).json({ queued: true, jobType, dryRun, message: 'Research job queued — monitor at /admin/jobs.' });
});
// ═══════════════════════════════════════════════════════════════════════════════
// POST /review/:id/verify
// ═══════════════════════════════════════════════════════════════════════════════
router.post('/review/:id/verify', async (req, res) => {
const { id } = req.params;
if (!isUuid(id)) return apiErr(res, 400, 'invalid_id', 'id must be a UUID');
const idempotencyKey = req.headers['idempotency-key'] || null;
// Optional target status the reviewer is confirming (a promotion). If omitted,
// verification only blesses the EXISTING status (never a silent promotion).
const targetStatus = req.body && req.body.relationship_status
? String(req.body.relationship_status) : null;
try {
// Load the sighting FIRST so the panelist≠sponsor guard runs on the live
// write path (spec §6.16). A guard that only lives in tests is not a guard.
const cur = await query('SELECT * FROM ad_sightings WHERE id = $1', [id]);
if (!cur.rows.length) return apiErr(res, 404, 'not_found', 'Ad sighting not found');
const sighting = cur.rows[0];
const fromStatus = sighting.relationship_status;
const toStatus = targetStatus || fromStatus;
const hasSponsorEvidence = !!sighting.evidence_id;
try {
assertNotPanelistMislabeledAsSponsor(fromStatus, toStatus, hasSponsorEvidence);
} catch (guardErr) {
await auditLog('verify_blocked', 'ad_sightings', id,
{ fromStatus, toStatus, reason: guardErr.message }, 'api');
return apiErr(res, 409, 'panelist_mislabel_blocked', guardErr.message);
}
const r = await query(
`UPDATE ad_sightings SET verification_status = 'VERIFIED',
relationship_status = $2, verified_by_user_id = NULL
WHERE id = $1 RETURNING *`,
[id, toStatus]
);
await auditLog('verify', 'ad_sightings', id,
{ idempotencyKey, fromStatus, toStatus }, 'api');
res.json({ ok: true, id, status: 'VERIFIED', relationship_status: toStatus });
} catch (e) {
if (e.code === '42P01') return apiErr(res, 404, 'not_found', 'Ad sightings table does not exist yet');
apiErr(res, 500, 'query_error', e.message);
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// POST /review/:id/reject
// ═══════════════════════════════════════════════════════════════════════════════
router.post('/review/:id/reject', async (req, res) => {
const { id } = req.params;
if (!isUuid(id)) return apiErr(res, 400, 'invalid_id', 'id must be a UUID');
const reason = req.body && req.body.reason ? String(req.body.reason).slice(0, 500) : '';
try {
const r = await query(
`UPDATE ad_sightings SET verification_status = 'REJECTED' WHERE id = $1 RETURNING *`,
[id]
);
if (!r.rows.length) return apiErr(res, 404, 'not_found', 'Ad sighting not found');
await auditLog('reject', 'ad_sightings', id, { reason }, 'api');
res.json({ ok: true, id, status: 'REJECTED', reason });
} catch (e) {
if (e.code === '42P01') return apiErr(res, 404, 'not_found', 'Ad sightings table does not exist yet');
apiErr(res, 500, 'query_error', e.message);
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// POST /exports
// ═══════════════════════════════════════════════════════════════════════════════
router.post('/exports', async (req, res) => {
const kind = (req.body && req.body.kind) ? String(req.body.kind).slice(0, 80) : 'DOWNLOAD_EVERYTHING';
// If the export builder exists, delegate to it
let buildExport;
try { buildExport = require('../export/build'); } catch (_) { buildExport = null; }
if (buildExport) {
try {
const result = await buildExport({ kind });
return res.status(202).json({ queued: true, ...result });
} catch (e) {
return apiErr(res, 500, 'export_error', e.message);
}
}
// Stub: create an export record and return it
try {
const r = await query(
`INSERT INTO exports (kind, status, row_counts) VALUES ($1, 'PENDING', '{}') RETURNING id, created_at`,
[kind]
);
const exportRec = r.rows[0];
await auditLog('export_queued', 'exports', exportRec.id, { kind }, 'api');
res.status(202).json({ queued: true, id: exportRec.id, kind, status: 'PENDING', created_at: exportRec.created_at });
} catch (e) {
// Table might not exist yet
res.status(202).json({ queued: true, kind, message: 'Export queued (db not migrated yet)' });
}
});
// ═══════════════════════════════════════════════════════════════════════════════
// GET /exports/:id
// ═══════════════════════════════════════════════════════════════════════════════
router.get('/exports/:id', async (req, res) => {
const { id } = req.params;
if (!isUuid(id)) return apiErr(res, 400, 'invalid_id', 'id must be a UUID');
try {
const r = await query(`SELECT * FROM exports WHERE id = $1`, [id]);
if (!r.rows.length) return apiErr(res, 404, 'not_found', 'Export not found');
const exp = r.rows[0];
res.json({
...exp,
download_url: exp.status === 'DONE' && exp.object_key ? '/assets/' + exp.object_key : null,
});
} catch (e) {
if (e.code === '42P01') return res.json({ status: 'PENDING', demo: true });
apiErr(res, 500, 'query_error', e.message);
}
});
// ── 404 fallback within /api/v1 ───────────────────────────────────────────────
router.use((req, res) => {
apiErr(res, 404, 'not_found', `No route for ${req.method} ${req.path}`);
});
module.exports = router;