← back to Professional Directory
agents/api-agent/data_market.js
864 lines
/**
* Doctor data marketplace — sell lists of LA County licensed practitioners
* filtered by specialty and/or city. Mirrors Counsel & Bar's data_market
* pattern (single data_orders table, download_token = auth, server-rendered
* HTML with editorial brand) for a different buyer type (medical recruiters,
* pharma reps, B2B sales into healthcare).
*
* GET /data — public catalog: pick specialties + cities
* POST /data/checkout — creates data_orders row + Stripe Checkout
* GET /data/thanks?session_id=… — Stripe redirect, marks paid, gives DL link
* GET /data/download/:token — gated CSV (live one_time + active sub)
* GET /admin/data-orders — admin queue (basic-auth same as pd-api admin)
* POST /webhooks/stripe-data — Stripe webhook (raw body, sig-verified)
*
* Pricing model (same shape as lawyer marketplace):
* one_time: $49 base + $0.04 per doctor, capped at $299 / specialty
* multi-pick (≥3 selections across specialties+cities) → 30% off
* subscription: $99/month, all LA County, monthly refresh
*/
const express = require('express');
const crypto = require('node:crypto');
const Stripe = require('stripe');
const { query } = require('../shared/db');
const STRIPE_KEY = process.env.STRIPE_SECRET_KEY || '';
const stripe = STRIPE_KEY.startsWith('sk_') ? new Stripe(STRIPE_KEY) : null;
// Codex P1 #2 fix: build Stripe success/cancel URLs from a configured base, not
// from request headers. Reading req.headers.host / x-forwarded-host lets a
// hostile Host header redirect the buyer (with their session_id) to an
// attacker-controlled domain. Default to loopback for dev; override in env.
const PUBLIC_BASE_URL = (process.env.BUY_PUBLIC_BASE_URL
|| process.env.PUBLIC_BASE_URL
|| `http://127.0.0.1:${process.env.PORT_API || 9874}`).replace(/\/+$/, '');
const SUBSCRIPTION_CENTS_MONTHLY = 9900;
const ONETIME_BASE_CENTS = 4900;
const ONETIME_PER_DOCTOR_CENTS = 4; // $0.04 — bulk-friendly
const ONETIME_CAP_PER_PICK_CENTS = 29900; // $299
const BUNDLE_DISCOUNT_PCT = 30; // 3+ picks total
const ONETIME_FLOOR_CENTS = 1900; // $19
const router = express.Router();
router.use(express.urlencoded({ extended: false }));
// ─── escape + shell ─────────────────────────────────────────────────────────
function esc(s) {
if (s === null || s === undefined) return '';
return String(s).replace(/[&<>"']/g, c => ({ '&': '&', '<': '<', '>': '>', '"': '"', "'": ''' }[c]));
}
function shellHead(title, opts = {}) {
return `<!doctype html>
<html lang="en"><head>
<meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>${esc(title)} · Practitioner Index</title>
<link rel="preconnect" href="https://fonts.googleapis.com">
<link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
<link href="https://fonts.googleapis.com/css2?family=Cormorant+Garamond:ital,wght@0,300;0,400;0,500;1,300;1,400&family=Inter:wght@300;400;500;600&display=swap" rel="stylesheet">
<style>
:root{--noir:#0a0a0c;--noir-rise:#131316;--noir-deep:#050507;--rule:#2a2724;--rule-faint:#1a1815;--ink:#f4f1ea;--ink-soft:#d8d2c5;--ink-mute:#8b857a;--metal:#b89968;--metal-glow:#d4b683;--metal-deep:#8a7044;--good:#34d399;--warn:#d4a04a;--bad:#d87a7a;--serif:"Cormorant Garamond",Georgia,serif;--sans:"Inter",-apple-system,system-ui,sans-serif}
*{box-sizing:border-box}html{-webkit-font-smoothing:antialiased}
body{margin:0;background:var(--noir);color:var(--ink);font-family:var(--sans);font-weight:300;font-size:15px;line-height:1.55}
::selection{background:var(--metal);color:var(--noir)}
a{color:var(--metal);text-decoration:none}a:hover{color:var(--metal-glow)}
header.brand{padding:22px 48px;display:flex;align-items:center;gap:18px;justify-content:space-between;border-bottom:1px solid var(--rule);background:var(--noir-deep)}
header.brand h1{margin:0;font-family:var(--serif);font-size:22px;font-weight:400}
header.brand h1 a{color:var(--ink)}
header.brand h1 .accent{color:var(--metal);font-style:italic;padding:0 4px}
.topnav{font-size:11px;letter-spacing:.14em;text-transform:uppercase;color:var(--ink-mute);display:flex;gap:24px}
.topnav a{color:var(--ink-mute)}.topnav a:hover{color:var(--ink)}
main{max-width:${opts.wide ? '1240px' : '980px'};margin:0 auto;padding:64px 48px}
.eyebrow{font-size:10px;letter-spacing:.3em;text-transform:uppercase;color:var(--metal);font-weight:500;margin:0 0 18px}
h1.display{font-family:var(--serif);font-weight:300;font-size:clamp(40px,6vw,68px);line-height:1.05;letter-spacing:-.02em;margin:0 0 16px;color:var(--ink)}
h1.display em{font-style:italic;color:var(--metal);font-weight:400}
.lede{font-size:17px;color:var(--ink-soft);max-width:60ch;margin:0 0 40px;font-weight:300}
.flash{padding:14px 18px;margin-bottom:24px;border:1px solid var(--rule);font-size:13px;letter-spacing:.06em}
.flash.error{background:#2a0e0e;color:var(--bad);border-color:#4a1a1a}
.flash.success{background:#0e2a1a;color:var(--good);border-color:#1a4a30}
.muted{color:var(--ink-mute);font-size:13px}
button,input,select{font:inherit;font-family:var(--sans);color:var(--ink);background:var(--noir-rise);border:1px solid var(--rule);padding:13px 16px;transition:border-color 180ms ease}
input:focus,select:focus{outline:none;border-color:var(--metal);box-shadow:0 0 0 3px rgba(184,153,104,.12)}
button.btn{background:var(--metal);border-color:var(--metal);color:var(--noir);font-weight:500;font-size:12px;letter-spacing:.18em;text-transform:uppercase;padding:14px 26px;cursor:pointer}
button.btn:hover{background:var(--metal-glow);border-color:var(--metal-glow)}
button.btn.ghost{background:transparent;color:var(--metal)}
button.btn.ghost:hover{background:#1a1611;color:var(--metal-glow)}
table{width:100%;border-collapse:collapse;font-size:13px}
th,td{padding:14px 12px;border-bottom:1px solid var(--rule);text-align:left;vertical-align:top}
th{color:var(--ink-mute);font-weight:500;font-size:10px;text-transform:uppercase;letter-spacing:.18em}
.pill{display:inline-block;font-size:10px;padding:3px 9px;border:1px solid var(--rule);letter-spacing:.14em;text-transform:uppercase;font-weight:500}
.pill.pending_payment{background:#2a2010;color:var(--warn);border-color:#4a3a1a}
.pill.paid{background:#0e2a1a;color:var(--good);border-color:#1a4a30}
.pill.refunded{background:#2a1410;color:var(--warn);border-color:#4a2520}
.pill.cancelled,.pill.sub_cancelled{background:var(--noir-rise);color:var(--ink-mute);border-color:var(--rule)}
</style></head><body>
<header class="brand">
<h1><a href="/">Practitioner <span class="accent">·</span> Index</a></h1>
<nav class="topnav">
<a href="/">Directory</a>
<a href="/data">Data</a>
</nav>
</header>
<main>`;
}
const FOOT = `</main>
<footer style="padding:36px 48px;border-top:1px solid var(--rule);text-align:center;color:var(--ink-mute);font-size:11px;letter-spacing:.16em;text-transform:uppercase">
Practitioner Index · LA County licensed clinicians · Sourced from CMS NPPES public registry · HIPAA-permissible directory data only
</footer>
</body></html>`;
// ─── pricing math (pure) ────────────────────────────────────────────────────
function computePrice(kind, picksCount, doctorCount) {
if (kind === 'subscription') return SUBSCRIPTION_CENTS_MONTHLY;
const raw = ONETIME_BASE_CENTS + (doctorCount * ONETIME_PER_DOCTOR_CENTS);
const cappedPerPick = Math.min(raw, ONETIME_CAP_PER_PICK_CENTS * Math.max(1, picksCount));
const discounted = picksCount >= 3 ? Math.round(cappedPerPick * (1 - BUNDLE_DISCOUNT_PCT / 100)) : cappedPerPick;
return Math.max(discounted, ONETIME_FLOOR_CENTS);
}
function fmtCents(c) {
if (c % 100 === 0) return `$${(c / 100).toLocaleString()}`;
return `$${(c / 100).toFixed(2)}`;
}
// ─── catalog data ───────────────────────────────────────────────────────────
async function loadSpecialtyRollup() {
// Top primary specialties by doctor count. Cap to a manageable list (~30).
// Group COALESCE-cleaned names so we don't show empty rows.
const r = await query(`
SELECT primary_specialty AS name, COUNT(*)::int AS doctor_count
FROM professionals
WHERE opted_out=false
AND primary_specialty IS NOT NULL
AND primary_specialty <> ''
GROUP BY primary_specialty
HAVING COUNT(*) >= 50
ORDER BY doctor_count DESC
LIMIT 30
`, []);
return r.rows;
}
async function loadCityRollup() {
// City rollup from organizations (where doctors practice). Minimum 25 to keep
// the picker tight. INITCAP cleans the all-caps NPI city values.
const r = await query(`
SELECT INITCAP(LOWER(o.city)) AS city,
COUNT(DISTINCT pl.professional_id)::int AS doctor_count
FROM organizations o
JOIN professional_locations pl ON pl.organization_id = o.id
JOIN professionals p ON p.id = pl.professional_id AND p.opted_out=false
WHERE o.state='CA' AND o.county='Los Angeles'
AND o.city IS NOT NULL AND o.city <> ''
GROUP BY INITCAP(LOWER(o.city))
HAVING COUNT(DISTINCT pl.professional_id) >= 25
ORDER BY doctor_count DESC
LIMIT 60
`, []);
return r.rows;
}
// Doctor count for the buyer's filter combo. Specialties + cities = AND.
// If no specialties picked → all specialties; same for cities. (At least one
// dimension must have a pick — POST validates that.)
async function doctorCountForPicks(specialties, cities) {
const params = [];
const where = [`p.opted_out=false`];
if (specialties.length) {
params.push(specialties);
where.push(`p.primary_specialty = ANY($${params.length}::text[])`);
}
if (cities.length) {
params.push(cities.map(c => c.toLowerCase()));
where.push(`LOWER(o.city) = ANY($${params.length}::text[])`);
}
const r = await query(`
SELECT COUNT(DISTINCT p.id)::text AS n
FROM professionals p
LEFT JOIN professional_locations pl ON pl.professional_id = p.id
LEFT JOIN organizations o ON o.id = pl.organization_id
WHERE ${where.join(' AND ')}
`, params);
return parseInt(r.rows[0]?.n || '0', 10);
}
// ─── GET /data — public catalog ─────────────────────────────────────────────
router.get('/data', async (req, res) => {
const flash = req.query.err
? `<div class="flash error">${esc(String(req.query.err).slice(0, 200))}</div>`
: req.query.msg
? `<div class="flash success">${esc(String(req.query.msg).slice(0, 200))}</div>`
: '';
const [specialties, cities, totalDoctors] = await Promise.all([
loadSpecialtyRollup(),
loadCityRollup(),
query(`SELECT COUNT(*)::text AS n FROM professionals WHERE opted_out=false AND npi_number IS NOT NULL`)
.then(r => parseInt(r.rows[0].n, 10)),
]);
const specialtyHtml = specialties.map(s => `
<label class="pick">
<input type="checkbox" name="specialties" value="${esc(s.name)}" data-doctors="${s.doctor_count}">
<span class="pn">${esc(s.name)}</span>
<span class="pc">${s.doctor_count.toLocaleString()}</span>
</label>`).join('');
const cityHtml = cities.map(c => `
<label class="pick">
<input type="checkbox" name="cities" value="${esc(c.city)}" data-doctors="${c.doctor_count}">
<span class="pn">${esc(c.city)}</span>
<span class="pc">${c.doctor_count.toLocaleString()}</span>
</label>`).join('');
res.send(shellHead('Doctor data lists', { wide: true }) + `
<style>
.data-grid{display:grid;grid-template-columns:1.4fr 1fr;gap:64px;align-items:start;margin-top:32px}
.picker-row{display:grid;grid-template-columns:1fr 1fr;gap:24px}
.picker{border:1px solid var(--rule);max-height:420px;overflow:auto}
.picker-h{padding:11px 16px;background:var(--noir-rise);border-bottom:1px solid var(--rule);font-size:11px;letter-spacing:.18em;text-transform:uppercase;color:var(--ink-mute)}
.pick{display:flex;align-items:center;gap:12px;padding:10px 16px;border-bottom:1px solid var(--rule-faint);cursor:pointer;transition:background 160ms ease}
.pick:hover{background:var(--noir-rise)}
.pick input{width:auto;padding:0;margin:0;accent-color:var(--metal)}
.pick .pn{flex:1;font-size:13.5px}
.pick .pc{font-variant-numeric:tabular-nums;color:var(--metal);font-size:12px;font-weight:500;letter-spacing:.04em}
.pricing-card{border:1px solid var(--rule);background:var(--noir-rise);padding:32px}
.pricing-card h3{font-family:var(--serif);font-weight:400;font-size:24px;margin:0 0 8px}
.pricing-card h3 em{font-style:italic;color:var(--metal)}
.pricing-card .price{font-family:var(--serif);font-weight:300;font-size:54px;line-height:1;color:var(--metal);font-variant-numeric:tabular-nums;margin:18px 0 8px}
.pricing-card .price-sub{font-size:13px;color:var(--ink-mute);margin-bottom:20px}
.pricing-card ul{list-style:none;padding:0;margin:0 0 24px}
.pricing-card li{padding:8px 0;border-bottom:1px solid var(--rule-faint);font-size:13px;color:var(--ink-soft);display:flex;justify-content:space-between}
.pricing-card li:before{content:"✓ ";color:var(--metal);margin-right:8px}
.pricing-card.sub{border-color:var(--metal);position:relative}
.pricing-card.sub:before{content:"Best value";position:absolute;top:-11px;left:24px;background:var(--metal);color:var(--noir);font-size:10px;letter-spacing:.18em;text-transform:uppercase;font-weight:500;padding:3px 10px}
.quick-pills{display:flex;flex-wrap:wrap;gap:8px;margin:14px 0 18px}
.quick-pills button{background:var(--noir-rise);border:1px solid var(--rule);color:var(--ink-soft);padding:7px 14px;font-size:11px;letter-spacing:.1em;text-transform:uppercase;cursor:pointer}
.quick-pills button:hover{border-color:var(--metal);color:var(--metal)}
.selected-summary{padding:18px 20px;background:var(--noir-rise);border:1px solid var(--rule);margin-bottom:18px;font-size:13px}
.selected-summary b{color:var(--metal);font-variant-numeric:tabular-nums}
.compliance{padding:16px 18px;background:#1a1611;border:1px solid #4a3a1a;color:#d4a04a;font-size:11px;line-height:1.55;letter-spacing:.04em;margin-top:32px}
.compliance b{color:var(--metal-glow)}
label.field{display:block;margin-top:14px;font-size:11px;letter-spacing:.14em;text-transform:uppercase;color:var(--ink-mute)}
label.field input{display:block;width:100%;margin-top:6px}
</style>
<p class="eyebrow">Data marketplace · ${totalDoctors.toLocaleString()} LA County licensed practitioners</p>
<h1 class="display">Doctor lists, <em>cleaned and current.</em></h1>
<p class="lede">Built from CMS's public NPPES NPI Registry, filtered to LA County, deduplicated by NPI. Pick by specialty, by city, or both — buy once for a snapshot, or subscribe for monthly refreshes as new clinicians are credentialed.</p>
${flash}
<form method="post" action="/data/checkout" class="data-grid" id="orderForm">
<div>
<p class="eyebrow" style="margin-top:0">Step 01 — Pick your set</p>
<div class="quick-pills">
<button type="button" data-pick="MD">All physicians (MD/DO)</button>
<button type="button" data-pick="MENTAL">Mental health</button>
<button type="button" data-pick="DENTAL">All dentists</button>
<button type="button" data-pick="LA">All LA city</button>
<button type="button" data-pick="CLEAR">Clear</button>
</div>
<div class="picker-row">
<div class="picker">
<div class="picker-h">By specialty</div>
${specialtyHtml}
</div>
<div class="picker">
<div class="picker-h">By city</div>
${cityHtml}
</div>
</div>
<p class="muted" style="margin-top:14px;font-size:12px">${specialties.length} specialties · ${cities.length} cities · only categories with ≥25–50 practitioners shown · counts are de-duplicated by NPI · combine specialty + city for an AND filter.</p>
</div>
<div>
<div class="selected-summary" id="summary">
<div>Selected specialties: <b id="sumSpec">0</b></div>
<div>Selected cities: <b id="sumCity">0</b></div>
<div>Doctors in set: <b id="sumDoctors">${totalDoctors.toLocaleString()}</b> <span class="muted" style="font-size:11px">(estimate, refines on selection)</span></div>
</div>
<div class="pricing-card" style="margin-bottom:24px">
<h3>One-time <em>purchase</em></h3>
<div class="price" id="onetimePrice">$49</div>
<div class="price-sub">snapshot CSV · download today</div>
<ul>
<li><span>Format</span><span>CSV (Excel-compatible)</span></li>
<li><span>Fields</span><span>Name · NPI · Specialty · Practice · Phone · City · ZIP</span></li>
<li><span>Refunds</span><span>14-day, no-questions</span></li>
<li><span>License</span><span>Single use, no resale</span></li>
</ul>
<button class="btn" type="submit" name="kind" value="one_time" style="width:100%">Buy snapshot →</button>
</div>
<div class="pricing-card sub">
<h3>Subscription · <em>$99/mo</em></h3>
<div class="price">$99</div>
<div class="price-sub">all LA County · monthly refresh</div>
<ul>
<li><span>Coverage</span><span>All specialties + cities</span></li>
<li><span>Refresh</span><span>Monthly diff + full export</span></li>
<li><span>Cancel</span><span>Anytime, in 1 click</span></li>
<li><span>Format</span><span>CSV + JSON</span></li>
</ul>
<button class="btn ghost" type="submit" name="kind" value="subscription" style="width:100%">Start subscription →</button>
</div>
<label class="field">Email <input name="email" type="email" required placeholder="you@company.com" autocomplete="email"></label>
<label class="field">Name <input name="full_name" type="text" placeholder="Your name"></label>
<label class="field">Company <input name="company" type="text" placeholder="Optional"></label>
</div>
</form>
<div class="compliance">
<b>About this data.</b> Records sourced from the CMS National Plan and Provider Enumeration System (NPPES) — a public-domain federal registry of US healthcare providers — and filtered to LA County practice locations. All buyers must comply with TCPA, CAN-SPAM, CCPA/CPRA, and any state-specific medical-marketing rules. Data must not be used for unsolicited bulk SMS, robocalls, prescribing recommendations, or any unlawful purpose. We exclude practitioners who have opted out of directory listing.
</div>
<script>
const cbSpec = document.querySelectorAll('input[name="specialties"]');
const cbCity = document.querySelectorAll('input[name="cities"]');
const sumS = document.getElementById('sumSpec');
const sumC = document.getElementById('sumCity');
const sumD = document.getElementById('sumDoctors');
const otp = document.getElementById('onetimePrice');
function recompute(){
const sels = [...cbSpec].filter(c => c.checked);
const cels = [...cbCity].filter(c => c.checked);
sumS.textContent = sels.length;
sumC.textContent = cels.length;
// Crude estimate: if both dimensions picked, take the smaller of the two sums
// (real count comes from server-side AND query at checkout). If only one
// dimension picked, sum its rows.
const sSum = sels.reduce((a,c) => a + parseInt(c.dataset.doctors||'0',10), 0);
const cSum = cels.reduce((a,c) => a + parseInt(c.dataset.doctors||'0',10), 0);
let est;
if (sels.length && cels.length) est = Math.min(sSum, cSum);
else if (sels.length) est = sSum;
else if (cels.length) est = cSum;
else est = ${totalDoctors};
sumD.textContent = est.toLocaleString();
// Mirror computePrice() — picksCount = total selections across both dims.
const picks = sels.length + cels.length;
const base = ${ONETIME_BASE_CENTS};
const per = ${ONETIME_PER_DOCTOR_CENTS};
const cap = ${ONETIME_CAP_PER_PICK_CENTS} * Math.max(1, picks);
let raw = base + (est * per);
raw = Math.min(raw, cap);
if (picks >= 3) raw = Math.round(raw * 0.7);
raw = Math.max(raw, ${ONETIME_FLOOR_CENTS});
otp.textContent = '$' + (raw/100).toLocaleString(undefined,{minimumFractionDigits: raw%100?2:0, maximumFractionDigits:2});
}
cbSpec.forEach(c => c.addEventListener('change', recompute));
cbCity.forEach(c => c.addEventListener('change', recompute));
recompute();
const PICKS = {
MD: { specialties: [...cbSpec].filter(c => /internal|family|cardio|surgery|pediatric|emergency|radiology|anesthesi|psychiatry|dermatology|oncology|gastro|orthopaed|orthopedic|neurology/i.test(c.value)).map(c=>c.value), cities: [] },
MENTAL:{ specialties: [...cbSpec].filter(c => /psycho|marriage|social|mental|counsel|addict/i.test(c.value)).map(c=>c.value), cities: [] },
DENTAL:{ specialties: [...cbSpec].filter(c => /dent|orthodont|periodont/i.test(c.value)).map(c=>c.value), cities: [] },
LA: { specialties: [], cities: [...cbCity].filter(c => /^Los Angeles$/i.test(c.value)).map(c=>c.value) },
CLEAR: { specialties: [], cities: [] },
};
document.querySelectorAll('button[data-pick]').forEach(b => b.addEventListener('click', () => {
const set = PICKS[b.dataset.pick];
cbSpec.forEach(c => { c.checked = (set.specialties || []).includes(c.value); });
cbCity.forEach(c => { c.checked = (set.cities || []).includes(c.value); });
recompute();
}));
</script>
` + FOOT);
});
// ─── POST /data/checkout — start Stripe Checkout ────────────────────────────
router.post('/data/checkout', async (req, res) => {
if (!stripe) return res.redirect('/data?err=' + encodeURIComponent('Payments not configured'));
const b = req.body || {};
const kind = (b.kind === 'subscription') ? 'subscription' : 'one_time';
const email = String(b.email || '').trim().toLowerCase();
if (!email || !/^[^@\s]+@[^@\s]+\.[^@\s]+$/.test(email))
return res.redirect('/data?err=' + encodeURIComponent('Valid email required'));
// Normalize specialties + cities — POST may send single string or array.
const norm = (raw) => {
if (Array.isArray(raw)) return raw.map(s => String(s).trim()).filter(Boolean);
if (typeof raw === 'string' && raw.trim()) return [raw.trim()];
return [];
};
const specialties = norm(b.specialties);
const cities = norm(b.cities);
if (kind === 'one_time' && specialties.length === 0 && cities.length === 0)
return res.redirect('/data?err=' + encodeURIComponent('Pick at least one specialty or city'));
const doctorCount = kind === 'subscription'
? parseInt((await query(`SELECT COUNT(*)::text AS n FROM professionals WHERE opted_out=false AND npi_number IS NOT NULL`)).rows[0].n, 10)
: await doctorCountForPicks(specialties, cities);
const picksCount = specialties.length + cities.length;
const amount = computePrice(kind, picksCount, doctorCount);
const downloadToken = crypto.randomBytes(32).toString('hex');
const ins = await query(`
INSERT INTO data_orders (
kind, email, full_name, company, specialties, cities, doctor_count, amount_cents,
download_token, ip, user_agent
) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11) RETURNING id
`, [
kind, email,
String(b.full_name || '').trim().slice(0, 120) || null,
String(b.company || '').trim().slice(0, 120) || null,
kind === 'subscription' ? [] : specialties,
kind === 'subscription' ? [] : cities,
doctorCount, amount, downloadToken,
req.ip || null, (req.headers['user-agent'] || '').toString().slice(0, 200),
]);
const orderId = ins.rows[0].id;
// Codex P1 #2 fix: pinned base URL — never read from request headers.
const base = PUBLIC_BASE_URL;
// Hoisted out of try so the catch block can reach it for session.expire().
let session;
try {
if (kind === 'subscription') {
session = await stripe.checkout.sessions.create({
mode: 'subscription',
payment_method_types: ['card'],
customer_email: email,
line_items: [{
price_data: {
currency: 'usd',
recurring: { interval: 'month' },
product_data: {
name: 'Practitioner Index — Doctor Data, monthly subscription',
description: 'Unlimited LA County downloads · monthly refresh · cancel anytime',
},
unit_amount: amount,
},
quantity: 1,
}],
metadata: { kind: 'data_subscription', order_id: String(orderId) },
success_url: `${base}/data/thanks?session_id={CHECKOUT_SESSION_ID}`,
cancel_url: `${base}/data?err=${encodeURIComponent('Checkout cancelled')}`,
});
} else {
const desc = [
specialties.length ? `${specialties.slice(0, 3).join(', ')}${specialties.length > 3 ? ` +${specialties.length - 3}` : ''}` : null,
cities.length ? `${cities.slice(0, 3).join(', ')}${cities.length > 3 ? ` +${cities.length - 3}` : ''}` : null,
].filter(Boolean).join(' · ');
session = await stripe.checkout.sessions.create({
mode: 'payment',
payment_method_types: ['card'],
customer_email: email,
line_items: [{
price_data: {
currency: 'usd',
product_data: {
name: `Practitioner Index — Doctor List (${picksCount} ${picksCount === 1 ? 'pick' : 'picks'})`,
description: `${doctorCount.toLocaleString()} LA County practitioners · ${desc}`,
},
unit_amount: amount,
},
quantity: 1,
}],
metadata: {
kind: 'data_one_time',
order_id: String(orderId),
specialties: specialties.slice(0, 8).join('|').slice(0, 240),
cities: cities.slice(0, 8).join('|').slice(0, 240),
doctor_count: String(doctorCount),
},
success_url: `${base}/data/thanks?session_id={CHECKOUT_SESSION_ID}`,
cancel_url: `${base}/data?err=${encodeURIComponent('Checkout cancelled')}`,
});
}
await query(`UPDATE data_orders SET payment_link = $2, stripe_session_id = $3 WHERE id = $1`,
[orderId, session.url, session.id]);
res.redirect(303, session.url || '/data');
} catch (e) {
// Codex P2 #6 fix: don't leave a zombie pending_payment row with a live
// download_token if Stripe checkout creation failed. Mark cancelled so
// it doesn't sit in admin queue forever and the token is dead.
//
// Cycle-2 Claude review P1: if Stripe.create() succeeded but the local
// UPDATE for stripe_session_id failed, the buyer can still complete
// payment against an orphan session that the /thanks UPDATE will reject
// (because stripe_session_id is null in the row). Expire the Stripe
// session as a safety net so the buyer can't pay into a dead order.
console.error('[data] checkout error:', e);
try {
await query(`UPDATE data_orders SET status='cancelled', cancelled_at=NOW() WHERE id=$1 AND status='pending_payment'`, [orderId]);
} catch (uErr) { console.error('[data] checkout cleanup error:', uErr.message); }
if (session?.id && stripe) {
try { await stripe.checkout.sessions.expire(session.id); }
catch (expErr) { console.error('[data] session expire failed:', expErr.message); }
}
res.redirect('/data?err=' + encodeURIComponent('Stripe error: ' + e.message.slice(0, 160)));
}
});
// ─── GET /data/thanks — Stripe redirect, mark paid ──────────────────────────
router.get('/data/thanks', async (req, res) => {
const sid = String(req.query.session_id || '');
let paid = false;
let token = '';
let kind = 'one_time';
if (stripe && sid) {
try {
const s = await stripe.checkout.sessions.retrieve(sid);
const orderId = s.metadata?.order_id ? parseInt(s.metadata.order_id, 10) : null;
const subId = (typeof s.subscription === 'string') ? s.subscription : s.subscription?.id;
const custId = (typeof s.customer === 'string') ? s.customer : s.customer?.id;
const isPaid = s.payment_status === 'paid' || s.payment_status === 'no_payment_required';
if (orderId && isPaid) {
// Codex P1 #3 fix: require stripe_session_id match before marking paid.
// A poisoned redirect with a different (or replayed) session_id can't
// flip the wrong order. The session_id comes from the Stripe URL — we
// re-retrieved it above, so it's authoritative.
const r = await query(`
UPDATE data_orders
SET status = 'paid', paid_at = NOW(),
stripe_subscription_id = COALESCE($2, stripe_subscription_id),
stripe_customer_id = COALESCE($3, stripe_customer_id)
WHERE id = $1 AND stripe_session_id = $4 AND status = 'pending_payment'
RETURNING download_token, kind
`, [orderId, subId || null, custId || null, sid]);
if (r.rowCount && r.rows[0]) {
paid = true;
token = r.rows[0].download_token;
kind = r.rows[0].kind === 'subscription' ? 'subscription' : 'one_time';
} else {
// Already paid on a prior page-load — re-read. Also gate on
// stripe_session_id (cycle-2 Claude review): the UPDATE branch
// already requires it, so the fallback must too — otherwise an
// attacker holding a different valid Stripe session for the same
// order_id could fetch the download_token by replaying /thanks.
const r2 = await query(
`SELECT download_token, kind, status FROM data_orders WHERE id = $1 AND stripe_session_id = $2`,
[orderId, sid]
);
if (r2.rowCount && r2.rows[0].status === 'paid') {
paid = true;
token = r2.rows[0].download_token;
kind = r2.rows[0].kind === 'subscription' ? 'subscription' : 'one_time';
}
}
}
} catch (e) {
console.error('[data] thanks lookup err:', e);
}
}
const dlUrl = token ? `/data/download/${token}` : '';
const body = paid
? `<p class="eyebrow">Order received · payment confirmed</p>
<h1 class="display">Your data is <em>ready.</em></h1>
<p class="lede">${kind === 'subscription'
? 'Your subscription is active. Bookmark this page — your token is the same key for every monthly refresh.'
: 'Your one-time export is ready. The download link below is yours; bookmark it for the next 30 days.'}</p>
<p style="margin:30px 0">
<a href="${esc(dlUrl)}" class="btn" style="display:inline-block;background:var(--metal);color:var(--noir);padding:16px 28px;font-size:12px;letter-spacing:.18em;text-transform:uppercase;font-weight:500;text-decoration:none;border:1px solid var(--metal)">Download CSV →</a>
</p>
<p class="muted">Bookmark this URL — your private download link:<br><code style="background:var(--noir-rise);padding:8px 14px;display:inline-block;margin-top:8px;border:1px solid var(--rule);color:var(--ink-soft);font-size:12px">${esc(dlUrl)}</code></p>`
: `<p class="eyebrow">Almost there</p>
<h1 class="display">Reservation noted.</h1>
<p class="lede">If your payment is still processing, give it a moment and refresh. If you cancelled, no charge was made.</p>
<p><a href="/data">← Back to data</a></p>`;
res.send(shellHead('Thanks') + body + FOOT);
});
// ─── GET /data/download/:token — CSV export ─────────────────────────────────
router.get('/data/download/:token', async (req, res) => {
const token = String(req.params.token || '').trim();
if (!/^[a-f0-9]{32,128}$/i.test(token))
return res.status(400).send('Invalid token format');
const r = await query(`
SELECT id, kind, status, specialties, cities, stripe_subscription_id, paid_at,
download_token
FROM data_orders WHERE download_token = $1`, [token]);
if (!r.rowCount) return res.status(404).send('Token not found');
const o = r.rows[0];
// Cycle-2 Claude review P2: constant-time compare of stored vs supplied
// token. The B-tree lookup above is already O(log n) and the UNIQUE index
// makes a strict equality check, but B-tree comparisons short-circuit on
// first mismatching byte; the constant-time check defends against any
// residual timing oracle.
try {
const a = Buffer.from(o.download_token);
const b = Buffer.from(token);
if (a.length !== b.length || !crypto.timingSafeEqual(a, b)) {
return res.status(404).send('Token not found');
}
} catch { return res.status(404).send('Token not found'); }
if (o.status !== 'paid') return res.status(402).send('Order not paid');
// Codex P2 #8 fix: one-time purchases are described in copy as a 30-day
// download window — enforce it here. After 30 days from `paid_at` the
// token expires (subscription downloads are gated on Stripe state below).
if (o.kind === 'one_time' && o.paid_at) {
const ageDays = (Date.now() - new Date(o.paid_at).getTime()) / 86400000;
if (ageDays > 30) {
return res.status(410).send('Download window expired (30 days). Re-purchase or contact support.');
}
}
// Subscription: verify Stripe subscription is still active.
if (o.kind === 'subscription') {
if (!stripe) return res.status(503).send('Subscription verification unavailable');
if (!o.stripe_subscription_id) return res.status(402).send('Subscription not on file');
try {
const sub = await stripe.subscriptions.retrieve(o.stripe_subscription_id);
if (sub.status !== 'active' && sub.status !== 'trialing') {
await query(`UPDATE data_orders SET status='sub_cancelled', cancelled_at=NOW() WHERE id=$1`, [o.id]);
return res.status(402).send('Subscription is no longer active');
}
} catch (e) {
console.error('[data] sub verify err:', e);
return res.status(502).send('Could not verify subscription');
}
}
// Build the CSV — subscription returns all-LA, one_time uses the recorded picks.
const params = [];
const where = [`p.opted_out=false`, `p.npi_number IS NOT NULL`];
if (o.kind !== 'subscription') {
if ((o.specialties || []).length) {
params.push(o.specialties);
where.push(`p.primary_specialty = ANY($${params.length}::text[])`);
}
if ((o.cities || []).length) {
params.push(o.cities.map(c => c.toLowerCase()));
where.push(`LOWER(o.city) = ANY($${params.length}::text[])`);
}
}
// Codex P2 #7 fix: city pricing/CSV mismatch. The price quote (count) joins
// ANY professional_location and matches city against any of them. The CSV
// used to join only `pl.is_primary`, so a doctor with secondary LA practice
// would price-in but get omitted from the export. Resolution: use EXISTS
// for the city predicate (matches count semantics), then pick the primary
// location for contact display in the SELECT.
for (let i = 0; i < where.length; i++) {
if (where[i].startsWith('LOWER(o.city)')) {
// Replace the inline join-side filter with an EXISTS subquery that
// matches ANY of the doctor's locations.
const paramIdx = where[i].match(/\$(\d+)/)[1];
where[i] = `EXISTS (
SELECT 1 FROM professional_locations pl2
JOIN organizations o2 ON o2.id = pl2.organization_id
WHERE pl2.professional_id = p.id
AND LOWER(o2.city) = ANY($${paramIdx}::text[])
)`;
break;
}
}
const rows = await query(`
SELECT DISTINCT ON (p.id)
p.npi_number, p.full_name, p.first_name, p.last_name, p.title,
p.primary_specialty, p.license_number, p.license_status,
o.name AS practice_name, COALESCE(pl.phone, o.phone) AS phone,
pl.address, o.city, o.state, o.zip
FROM professionals p
LEFT JOIN professional_locations pl ON pl.professional_id = p.id AND pl.is_primary
LEFT JOIN organizations o ON o.id = pl.organization_id
WHERE ${where.join(' AND ')}
ORDER BY p.id`, params);
await query(`UPDATE data_orders SET downloads_count = downloads_count + 1, last_downloaded_at = NOW() WHERE id = $1`, [o.id]);
const today = new Date().toISOString().slice(0, 10);
const fname = `practitioner-index-doctors-${today}.csv`;
res.setHeader('Content-Type', 'text/csv; charset=utf-8');
res.setHeader('Content-Disposition', `attachment; filename="${fname}"`);
const csvCell = (v) => '"' + (v == null ? '' : String(v)).replace(/"/g, '""') + '"';
res.write(['NPI','Full Name','First','Last','Title','Specialty','License','License Status','Practice','Phone','Address','City','State','ZIP'].map(csvCell).join(',') + '\n');
for (const row of rows.rows) {
res.write([
row.npi_number, row.full_name, row.first_name, row.last_name, row.title,
row.primary_specialty, row.license_number, row.license_status,
row.practice_name, row.phone, row.address, row.city, row.state, row.zip,
].map(csvCell).join(',') + '\n');
}
res.end();
});
// ─── /admin/data-orders ─────────────────────────────────────────────────────
router.get('/admin/data-orders', (req, res, next) => {
// Phase 1 auth gate: req.user is set either by passport session OR by the
// loopback synthetic-admin middleware in server.js (gated by
// ADMIN_LOOPBACK_TRUST=1). Public callers via tunnel get no bypass.
if (!req.user || req.user.role !== 'admin') {
return res.status(req.user ? 403 : 401).json({ error: req.user ? 'forbidden' : 'auth required' });
}
next();
}, async (req, res) => {
const rows = await query(`
SELECT id, kind, email, full_name, company, specialties, cities, doctor_count,
amount_cents, status, downloads_count, paid_at, created_at, payment_link
FROM data_orders ORDER BY id DESC LIMIT 200`, []);
const counts = await query(`
SELECT 'total' AS k, COUNT(*)::text AS v FROM data_orders
UNION ALL SELECT 'paid', COUNT(*)::text FROM data_orders WHERE status='paid'
UNION ALL SELECT 'subs', COUNT(*)::text FROM data_orders WHERE kind='subscription' AND status='paid'
UNION ALL SELECT 'revenue', COALESCE(SUM(amount_cents),0)::text FROM data_orders WHERE status='paid'
`, []);
const cm = {}; for (const r of counts.rows) cm[r.k] = r.v;
const trs = rows.rows.map(o => {
const pickStr = o.kind === 'subscription'
? 'all LA'
: [...(o.specialties || []).slice(0, 2), ...(o.cities || []).slice(0, 2)].join(', ') +
(((o.specialties || []).length + (o.cities || []).length) > 4 ? ' …' : '');
return `<tr>
<td>#${o.id}<br><span class="muted">${new Date(o.created_at).toISOString().slice(0,16).replace('T',' ')}</span></td>
<td><b>${esc(o.email)}</b><br><span class="muted">${esc(o.full_name || '—')} · ${esc(o.company || '—')}</span></td>
<td>${esc(o.kind)}<br><span class="muted">${esc(pickStr)}</span></td>
<td>${o.doctor_count != null ? o.doctor_count.toLocaleString() : '—'}</td>
<td>${fmtCents(o.amount_cents)}<br><span class="muted">downloads: ${o.downloads_count}</span></td>
<td><span class="pill ${esc(o.status)}">${esc(o.status)}</span>${o.paid_at ? `<br><span class="muted">${new Date(o.paid_at).toISOString().slice(0,10)}</span>` : ''}</td>
<td>${o.payment_link ? `<a href="${esc(o.payment_link)}" target="_blank">Stripe ↗</a>` : '<span class="muted">—</span>'}</td>
</tr>`;
}).join('');
res.send(shellHead('Admin · Data Orders', { wide: true }) + `
<p class="eyebrow">Admin · Data marketplace</p>
<h1 class="display">Data <em>orders.</em></h1>
<div style="display:flex;gap:32px;margin:24px 0 32px;font-family:var(--serif)">
<div><div style="font-size:32px;color:var(--metal);font-variant-numeric:tabular-nums">${cm.total || 0}</div><div class="muted" style="font-size:11px;letter-spacing:.18em;text-transform:uppercase">total orders</div></div>
<div><div style="font-size:32px;color:var(--good);font-variant-numeric:tabular-nums">${cm.paid || 0}</div><div class="muted" style="font-size:11px;letter-spacing:.18em;text-transform:uppercase">paid</div></div>
<div><div style="font-size:32px;color:var(--metal);font-variant-numeric:tabular-nums">${cm.subs || 0}</div><div class="muted" style="font-size:11px;letter-spacing:.18em;text-transform:uppercase">active subs</div></div>
<div><div style="font-size:32px;color:var(--metal-glow);font-variant-numeric:tabular-nums">$${(parseInt(cm.revenue || '0', 10) / 100).toLocaleString()}</div><div class="muted" style="font-size:11px;letter-spacing:.18em;text-transform:uppercase">gross revenue</div></div>
</div>
<table>
<thead><tr><th>#</th><th>Buyer</th><th>Kind / Picks</th><th>Doctors</th><th>Amount</th><th>Status</th><th>Stripe</th></tr></thead>
<tbody>${trs || '<tr><td colspan="7" class="muted" style="padding:32px;text-align:center">No orders yet.</td></tr>'}</tbody>
</table>
` + FOOT);
});
// ─── Stripe webhook handler ─────────────────────────────────────────────────
//
// Codex P1 #1 fix: this handler is exported separately so server.js can mount
// it with `express.raw({type:'application/json'})` BEFORE the global
// `express.json()` body parser. If the global parser runs first, `req.body` is
// a parsed object and `stripe.webhooks.constructEvent()` rejects every event
// — silently breaking webhook processing in prod.
//
// Codex P1 #3 fix: webhook UPDATE includes `stripe_session_id=$N` in the WHERE
// clause and verifies amount_total + mode + metadata kind before transitioning
// to 'paid', so a poisoned redirect (or replayed metadata.order_id) can't mark
// the wrong order paid.
async function stripeWebhookHandler(req, res) {
const secret = process.env.STRIPE_DATA_WEBHOOK_SECRET;
if (!stripe || !secret) return res.status(503).send('webhook not configured');
const sig = req.headers['stripe-signature'];
let event;
try { event = stripe.webhooks.constructEvent(req.body, sig, secret); }
catch (e) { return res.status(400).send(`webhook signature err: ${e.message}`); }
try {
switch (event.type) {
case 'checkout.session.completed': {
const s = event.data.object;
const orderId = s.metadata?.order_id ? parseInt(s.metadata.order_id, 10) : null;
const expectedKind = s.metadata?.kind === 'data_subscription' ? 'subscription' : 'one_time';
if (!orderId || (s.metadata?.kind !== 'data_one_time' && s.metadata?.kind !== 'data_subscription')) {
break;
}
const subId = (typeof s.subscription === 'string') ? s.subscription : s.subscription?.id;
const custId = (typeof s.customer === 'string') ? s.customer : s.customer?.id;
// Look up the stored order and verify shape before marking paid.
const r = await query(
`SELECT kind, amount_cents, status FROM data_orders WHERE id=$1 AND stripe_session_id=$2`,
[orderId, s.id]
);
if (!r.rowCount) {
console.warn(`[data webhook] no matching order id=${orderId} session=${s.id}`);
break;
}
const stored = r.rows[0];
if (stored.status === 'paid') break; // idempotent — already done
if (stored.kind !== expectedKind) {
console.warn(`[data webhook] kind mismatch order=${orderId} stored=${stored.kind} session=${expectedKind}`);
break;
}
// For one-time payments Stripe sends amount_total; for subscriptions
// Stripe doesn't include it on the session — skip the dollar check.
if (s.mode === 'payment' && typeof s.amount_total === 'number'
&& s.amount_total !== stored.amount_cents) {
console.warn(`[data webhook] amount mismatch order=${orderId} stored=${stored.amount_cents} session=${s.amount_total}`);
break;
}
await query(`
UPDATE data_orders
SET status='paid', paid_at=NOW(),
stripe_subscription_id = COALESCE($2, stripe_subscription_id),
stripe_customer_id = COALESCE($3, stripe_customer_id)
WHERE id = $1 AND stripe_session_id = $4 AND status='pending_payment'`,
[orderId, subId || null, custId || null, s.id]);
break;
}
case 'customer.subscription.deleted': {
const sub = event.data.object;
await query(`UPDATE data_orders SET status='sub_cancelled', cancelled_at=NOW()
WHERE stripe_subscription_id = $1 AND status='paid'`, [sub.id]);
// Phase 1 user-tier sync: if this subscription belongs to a pd-api
// user (mirror in subscriptions table), flip them back to 'free' and
// record the end date.
await query(`UPDATE subscriptions SET status='canceled', ended_at=NOW(), raw_json=$2::jsonb
WHERE stripe_subscription_id = $1`, [sub.id, JSON.stringify(sub)]);
await query(`UPDATE users SET tier='free' WHERE id IN
(SELECT user_id FROM subscriptions WHERE stripe_subscription_id = $1)`, [sub.id]);
break;
}
case 'customer.subscription.created':
case 'customer.subscription.updated': {
const sub = event.data.object;
const custId = (typeof sub.customer === 'string') ? sub.customer : sub.customer?.id;
const priceId = sub.items?.data?.[0]?.price?.id || '';
const planMeta = sub.metadata?.plan
|| (priceId === process.env.STRIPE_PRICE_DOCTOR_PRO ? 'doctor_pro_monthly'
: priceId === process.env.STRIPE_PRICE_PATIENT_PLUS ? 'patient_plus_monthly'
: 'unknown');
// Locate pd-api user by stripe_customer_id.
const u = (await query('SELECT id FROM users WHERE stripe_customer_id = $1', [custId])).rows[0];
if (!u) break; // belongs to data_market data_orders (older flow), not user-tier
const periodEnd = sub.current_period_end ? new Date(sub.current_period_end * 1000) : null;
await query(`
INSERT INTO subscriptions (user_id, stripe_subscription_id, stripe_price_id, plan,
status, current_period_end, cancel_at_period_end, raw_json)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8::jsonb)
ON CONFLICT (stripe_subscription_id) DO UPDATE SET
status = EXCLUDED.status,
current_period_end = EXCLUDED.current_period_end,
cancel_at_period_end = EXCLUDED.cancel_at_period_end,
raw_json = EXCLUDED.raw_json
`, [u.id, sub.id, priceId, planMeta, sub.status, periodEnd, !!sub.cancel_at_period_end, JSON.stringify(sub)]);
// Tier flip: 'active' or 'trialing' → paid; anything else → free.
const tier = (sub.status === 'active' || sub.status === 'trialing') ? 'paid' : 'free';
await query('UPDATE users SET tier = $1 WHERE id = $2', [tier, u.id]);
break;
}
}
} catch (e) {
console.error('[data webhook] handler error:', e.message);
// Return 200 so Stripe doesn't retry our bug indefinitely.
}
res.json({ received: true });
}
module.exports = router;
module.exports.stripeWebhookHandler = stripeWebhookHandler;