← back to Lawyer Directory Builder
src/server/data_market.ts
824 lines
/**
* Data marketplace — sell lists of California-licensed attorneys filtered by city.
*
* GET /data — public catalog: pick cities, see counts + price
* POST /data/checkout — creates data_orders row + Stripe Checkout (one_time | subscription)
* GET /data/thanks?session_id=… — Stripe redirect, marks order paid, gives download link
* GET /data/download/:token — gated CSV stream (live one_time + active subscription)
* GET /admin/data-orders — admin queue
*
* Pricing model:
* one_time: base $49 + $0.04 per attorney, capped at $299 / city
* multi-city (≥3 cities) = 30 % off
* subscription: $99 / month, unlimited California cities, monthly refresh
*
* NOTE: Stripe keys are LIVE — sessions only created from authenticated user
* intent (POST after explicit buy click), never on smoke / GET.
*/
import express from 'express';
import crypto from 'node:crypto';
import Stripe from 'stripe';
import { query } from '../db/pool.ts';
import { requireAdmin } from './auth.ts';
const STRIPE_KEY = process.env.STRIPE_SECRET_KEY || '';
const stripe: Stripe | null = STRIPE_KEY.startsWith('sk_') ? new Stripe(STRIPE_KEY) : null;
const SUBSCRIPTION_CENTS_MONTHLY = 9900;
const ONETIME_BASE_CENTS = 4900;
const ONETIME_PER_ATTORNEY_CENTS = 4; // $0.04
const ONETIME_CAP_PER_CITY_CENTS = 29900; // $299
const BUNDLE_DISCOUNT_PCT = 30; // 3+ cities
const router = express.Router();
router.use(express.urlencoded({ extended: false }));
// ─── helpers ──────────────────────────────────────────────────────────────
function esc(s: any): string {
if (s === null || s === undefined) return '';
return String(s).replace(/[&<>"']/g, c => ({ '&': '&', '<': '<', '>': '>', '"': '"', "'": ''' }[c]!));
}
function shellHead(title: string, opts: { wide?: boolean } = {}): string {
return `<!doctype html>
<html lang="en"><head>
<meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>${esc(title)} · Counsel & Bar</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 .amp{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}
@media (max-width:520px){main{padding:40px 20px}header.brand{padding:16px 20px;flex-wrap:wrap;gap:10px}.topnav{flex-wrap:wrap;gap:14px;font-size:10px;width:100%}}
.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)}
*:focus-visible{outline:none;box-shadow:0 0 0 3px rgba(184,153,104,.45);border-radius:1px}
*:focus:not(:focus-visible){outline:none}
</style></head><body>
<header class="brand">
<a href="/" style="display:inline-flex;align-items:center;gap:14px;text-decoration:none"><svg width="28" height="28" viewBox="0 0 28 28" aria-hidden="true" style="flex:0 0 28px"><defs><linearGradient id="cb-data" x1="0" y1="0" x2="0" y2="1"><stop offset="0%" stop-color="#d4b683"/><stop offset="50%" stop-color="#b89968"/><stop offset="100%" stop-color="#8a7044"/></linearGradient></defs><circle cx="14" cy="14" r="13" fill="none" stroke="url(#cb-data)" stroke-width="1"/><path fill="url(#cb-data)" d="M9.6 9.5c-1.6 0-2.7 1.2-2.7 3v3c0 1.8 1.1 3 2.7 3 1.4 0 2.4-.8 2.6-2.1h-1.3c-.1.6-.6 1-1.3 1-.9 0-1.4-.6-1.4-1.7v-2.4c0-1.1.5-1.7 1.4-1.7.7 0 1.2.4 1.3 1h1.3c-.2-1.3-1.2-2.1-2.6-2.1zm5.5.1v8.7h2.7c1.6 0 2.6-.9 2.6-2.4 0-1-.5-1.7-1.3-2 .7-.3 1.1-1 1.1-1.8 0-1.5-1-2.5-2.5-2.5h-2.6zm1.3 1.1h1.2c.8 0 1.4.5 1.4 1.4 0 .9-.5 1.4-1.4 1.4h-1.2v-2.8zm0 3.9h1.4c.9 0 1.4.6 1.4 1.5s-.5 1.5-1.4 1.5h-1.4V14.6z"/></svg><h1 style="margin:0"><span style="color:var(--ink)">Counsel</span> <span class="amp">&</span> <span style="color:var(--ink)">Bar</span></h1></a>
<nav class="topnav">
<a href="/find-a-lawyer">Find counsel</a>
<a href="/directory">Directory</a>
<a href="/data">Data</a>
<a href="/login">Sign in</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">
Counsel & Bar · California-licensed attorney directory · Data sourced from State Bar of California public records
</footer>
</body></html>`;
// ─── pricing math (pure) ──────────────────────────────────────────────────
function computePrice(kind: 'one_time'|'subscription', citiesCount: number, attorneyCount: number): number {
if (kind === 'subscription') return SUBSCRIPTION_CENTS_MONTHLY;
// one_time — base + per-attorney, capped per city, then bundle discount
const raw = ONETIME_BASE_CENTS + (attorneyCount * ONETIME_PER_ATTORNEY_CENTS);
const cappedPerCity = Math.min(raw, ONETIME_CAP_PER_CITY_CENTS * Math.max(1, citiesCount));
const discounted = citiesCount >= 3 ? Math.round(cappedPerCity * (1 - BUNDLE_DISCOUNT_PCT / 100)) : cappedPerCity;
return Math.max(discounted, 1900); // floor at $19
}
function fmtCents(c: number): string {
if (c % 100 === 0) return `$${(c / 100).toLocaleString()}`;
return `$${(c / 100).toFixed(2)}`;
}
// ─── city-count snapshot used by GET /data and POST /data/checkout ────────
type CityRow = { city: string; attorney_count: number; firm_count: number };
async function loadCityRollup(): Promise<CityRow[]> {
const r = await query<CityRow>(`
SELECT
INITCAP(LOWER(o.city)) AS city,
COUNT(DISTINCT pl.professional_id)::int AS attorney_count,
COUNT(DISTINCT o.id)::int AS firm_count
FROM organizations o
JOIN professional_locations pl ON pl.organization_id = o.id
JOIN professionals p ON p.id = pl.professional_id
WHERE o.type='law_firm'
AND o.city IS NOT NULL AND o.city <> ''
AND p.bar_number IS NOT NULL
GROUP BY INITCAP(LOWER(o.city))
HAVING COUNT(DISTINCT pl.professional_id) >= 5
ORDER BY attorney_count DESC
LIMIT 80
`);
return r.rows;
}
async function attorneyCountForCities(cities: string[]): Promise<number> {
if (cities.length === 0) return 0;
const r = await query<{ n: string }>(`
SELECT COUNT(DISTINCT pl.professional_id)::text AS n
FROM organizations o
JOIN professional_locations pl ON pl.organization_id = o.id
JOIN professionals p ON p.id = pl.professional_id
WHERE o.type='law_firm'
AND p.bar_number IS NOT NULL
AND LOWER(o.city) = ANY($1::text[])
`, [cities.map(c => c.toLowerCase())]);
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 [cityRows, totalAttorneys] = await Promise.all([
loadCityRollup(),
query<{ n: string }>(`SELECT COUNT(*)::text AS n FROM professionals WHERE bar_number IS NOT NULL`).then(r => parseInt(r.rows[0].n, 10)),
]);
const cityListHtml = cityRows.map(c => `
<label class="city-pick">
<input type="checkbox" name="cities" value="${esc(c.city)}" data-attorneys="${c.attorney_count}">
<span class="cn">${esc(c.city)}</span>
<span class="cc">${c.attorney_count.toLocaleString()}</span>
</label>
`).join('');
res.send(shellHead('Attorney data lists', { wide: true }) + `
<style>
.data-grid{display:grid;grid-template-columns:1.4fr 1fr;gap:64px;align-items:start;margin-top:32px}
@media (max-width:760px){.data-grid{grid-template-columns:1fr;gap:36px}.city-grid{grid-template-columns:1fr;max-height:420px}}
.city-grid{display:grid;grid-template-columns:repeat(2,1fr);gap:0;border:1px solid var(--rule);max-height:560px;overflow:auto}
.city-pick{display:flex;align-items:center;gap:12px;padding:11px 16px;border-bottom:1px solid var(--rule-faint);cursor:pointer;transition:background 160ms ease}
.city-pick:hover{background:var(--noir-rise)}
.city-pick:nth-child(even){border-left:1px solid var(--rule-faint)}
.city-pick input{width:auto;padding:0;margin:0;accent-color:var(--metal)}
.city-pick .cn{flex:1;font-size:14px}
.city-pick .cc{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:20px 24px;background:linear-gradient(180deg,rgba(184,153,104,.025),transparent);border:1px solid var(--rule);font-size:12px;line-height:1.65;letter-spacing:.02em;margin-top:32px;color:var(--ink-soft)}
.compliance b{color:var(--ink);font-weight:500}
.compliance .c-eyebrow{font-size:10px;letter-spacing:.3em;text-transform:uppercase;color:var(--metal);font-weight:500;display:block;margin-bottom:10px}
.schema-specimen{margin:48px 0 56px;border-top:1px solid var(--rule);border-bottom:1px solid var(--rule);padding:28px 0 24px}
.schema-specimen-head{display:flex;justify-content:space-between;align-items:baseline;margin:0 0 18px;gap:24px;flex-wrap:wrap}
.schema-specimen-eyebrow{font-size:10px;letter-spacing:.3em;text-transform:uppercase;color:var(--metal);font-weight:500;margin:0}
.schema-specimen-meta{font-family:var(--serif);font-style:italic;color:var(--ink-mute);font-size:13px;margin:0;letter-spacing:.01em}
.schema-specimen-meta b{font-style:normal;font-family:var(--sans);color:var(--metal);font-weight:500;font-variant-numeric:tabular-nums}
.schema-row{display:grid;grid-template-columns:200px 110px 1fr 80px;gap:24px;padding:14px 0;border-top:1px solid var(--rule-faint);align-items:baseline;font-variant-numeric:tabular-nums}
.schema-row:first-of-type{border-top:1px solid var(--rule)}
.schema-row:last-of-type{border-bottom:none}
.schema-row .field{font-family:'SFMono-Regular',Menlo,Consolas,monospace;font-size:13px;color:var(--ink);font-weight:400;letter-spacing:-.01em}
.schema-row .type{font-size:10px;letter-spacing:.18em;text-transform:uppercase;color:var(--ink-mute);font-weight:500}
.schema-row .sample{font-family:var(--serif);font-style:italic;font-weight:400;color:var(--metal);font-size:14px;letter-spacing:.005em}
.schema-row .nulls{font-size:11px;color:var(--ink-mute);text-align:right;font-weight:500;letter-spacing:.04em}
.schema-row .nulls.zero{color:var(--metal)}
.schema-spec-foot{padding-top:14px;display:flex;justify-content:space-between;align-items:center;gap:24px;flex-wrap:wrap;font-size:11px;color:var(--ink-mute);letter-spacing:.04em}
.schema-spec-foot .placeholder-tag{font-family:var(--serif);font-style:italic;color:var(--metal-deep)}
@media (max-width:760px){.schema-row{grid-template-columns:1fr;gap:4px;padding:18px 0}.schema-row .field{font-size:14px}.schema-row .nulls{text-align:left}}
.public-vol{display:flex;align-items:center;gap:18px;margin:0 0 26px;color:var(--metal)}
.public-vol .v-rule{flex:0 0 56px;height:1px;background:linear-gradient(90deg,transparent,var(--metal) 50%,transparent)}
.public-vol .v-text{font-family:var(--serif);font-style:italic;font-weight:400;font-size:14px;letter-spacing:.02em}
.public-vol .v-meta{font-size:10px;letter-spacing:.28em;text-transform:uppercase;color:var(--ink-mute);font-weight:500}
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>
<div class="public-vol">
<span class="v-rule"></span>
<span class="v-text">Volume I</span>
<span class="v-meta">Edition 2026 · Data Marketplace · ${totalAttorneys.toLocaleString()} attorneys indexed</span>
</div>
<h1 class="display">Lawyer lists, <em>cleaned and current.</em></h1>
<p class="lede">Built from the California State Bar's public licensing records and enriched with firm contact data. Pick a city or build a multi-city set — buy once for a snapshot, or subscribe for monthly refreshes.</p>
<section class="schema-specimen" aria-label="Schema specimen — what each row of the export contains">
<div class="schema-specimen-head">
<p class="schema-specimen-eyebrow">Schema specimen</p>
<p class="schema-specimen-meta">Eight fields per row · last verified <b>2026-05-04</b> · de-duplicated by bar number</p>
</div>
<p class="placeholder-tag" style="margin:0 0 14px;font-family:var(--serif);font-style:italic;color:var(--metal-deep);font-size:13px;letter-spacing:.01em">— Sample values below are reserved placeholders. Bar number <code style="font-family:'SFMono-Regular',Menlo,monospace;font-style:normal;color:var(--ink-mute)">000000</code> is unissued; phone block <code style="font-family:'SFMono-Regular',Menlo,monospace;font-style:normal;color:var(--ink-mute)">555-01xx</code> is the FCC fictional range; <code style="font-family:'SFMono-Regular',Menlo,monospace;font-style:normal;color:var(--ink-mute)">example.com</code> is IANA-reserved. No real attorney is depicted. —</p>
<div class="schema-row">
<span class="field">bar_number</span>
<span class="type">String · 6 char</span>
<span class="sample">000000</span>
<span class="nulls zero">0% null</span>
</div>
<div class="schema-row">
<span class="field">name_full</span>
<span class="type">String</span>
<span class="sample">⟨Last, First M.⟩</span>
<span class="nulls zero">0% null</span>
</div>
<div class="schema-row">
<span class="field">firm_name</span>
<span class="type">String</span>
<span class="sample">⟨Firm name LLP⟩</span>
<span class="nulls">12% null</span>
</div>
<div class="schema-row">
<span class="field">phone_e164</span>
<span class="type">E.164</span>
<span class="sample">+1 555 010 0000</span>
<span class="nulls">24% null</span>
</div>
<div class="schema-row">
<span class="field">address_city</span>
<span class="type">String</span>
<span class="sample">⟨City⟩</span>
<span class="nulls zero">0% null</span>
</div>
<div class="schema-row">
<span class="field">address_zip</span>
<span class="type">String · 5 char</span>
<span class="sample">⟨ZIP⟩</span>
<span class="nulls">3% null</span>
</div>
<div class="schema-row">
<span class="field">website_url</span>
<span class="type">URL</span>
<span class="sample">https://example.com</span>
<span class="nulls">31% null</span>
</div>
<div class="schema-row">
<span class="field">last_verified</span>
<span class="type">ISO 8601</span>
<span class="sample">2026-04-28</span>
<span class="nulls zero">0% null</span>
</div>
<div class="schema-spec-foot">
<span></span>
<span>Subscriptions also receive a JSON copy and a monthly diff file.</span>
</div>
</section>
${flash}
<form method="post" action="/data/checkout" class="data-grid" id="orderForm">
<div>
<p class="eyebrow" style="margin-top:0;display:flex;align-items:center;gap:14px"><span style="font-family:var(--serif);font-style:italic;color:transparent;-webkit-text-stroke:1px var(--metal);font-size:32px;line-height:1;text-transform:none;letter-spacing:-.02em">i</span> · Pick markets</p>
<div class="quick-pills">
<button type="button" data-pick="LA">Los Angeles County</button>
<button type="button" data-pick="BAY">Bay Area</button>
<button type="button" data-pick="SD">San Diego</button>
<button type="button" data-pick="ALL">All California</button>
<button type="button" data-pick="CLEAR">Clear</button>
</div>
<div class="city-grid">${cityListHtml}</div>
<p class="muted" style="margin-top:14px;font-size:12px">${cityRows.length} cities listed · only cities with 5+ attorneys are shown · attorney counts are de-duplicated by bar number.</p>
</div>
<div>
<div class="selected-summary" id="summary">
<div>Selected cities: <b id="sumCities">0</b></div>
<div>Attorneys in set: <b id="sumAttorneys">0</b></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 · Bar # · Firm · Phone · Address · Web</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 California cities · monthly refresh</div>
<ul>
<li><span>Cities</span><span>Unlimited (CA only)</span></li>
<li><span>Refresh</span><span>Monthly diff + full</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@firm.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">
<span class="c-eyebrow">Use restrictions · for the record</span>
<b>About this data.</b> Records are sourced from the State Bar of California's public licensing database and supplemented with firm-level contact information from publicly indexed websites and LA City Active Business Licenses (NAICS 5411). All buyers must comply with the State Bar's permitted use rules and California consumer-privacy law (CCPA/CPRA). <b>Data may not be used for unsolicited bulk SMS, robocalls, or any unlawful purpose.</b> We exclude attorneys flagged as inactive, suspended, or deceased on the date of export.
</div>
<script>
const cb = document.querySelectorAll('input[name="cities"]');
const sumC = document.getElementById('sumCities');
const sumA = document.getElementById('sumAttorneys');
const otp = document.getElementById('onetimePrice');
function recompute(){
const sel = [...cb].filter(c => c.checked);
const aa = sel.reduce((a,c) => a + parseInt(c.dataset.attorneys||'0',10), 0);
sumC.textContent = sel.length;
sumA.textContent = aa.toLocaleString();
// mirror server-side computePrice() for one-time
const base = 4900;
const perAtt = 4;
const cap = 29900 * Math.max(1, sel.length);
let raw = base + (aa * perAtt);
raw = Math.min(raw, cap);
if (sel.length >= 3) raw = Math.round(raw * 0.7);
raw = Math.max(raw, 1900);
otp.textContent = '$' + (raw/100).toLocaleString(undefined,{minimumFractionDigits: raw%100?2:0, maximumFractionDigits:2});
}
cb.forEach(c => c.addEventListener('change', recompute));
recompute();
const PICKS = {
LA: ['Los Angeles','Beverly Hills','Santa Monica','Pasadena','Long Beach','Glendale','Burbank','Culver City','West Hollywood','El Segundo','Torrance','Hermosa Beach','Manhattan Beach','Sherman Oaks','Encino','Studio City','Van Nuys','Marina Del Rey','Hollywood','North Hollywood'],
BAY:['San Francisco','Oakland','San Jose','Palo Alto','Berkeley','Mountain View','Santa Clara','Redwood City','San Mateo','Walnut Creek'],
SD: ['San Diego','La Jolla','Carlsbad','Encinitas','Del Mar','Chula Vista','Escondido'],
ALL:'*',
CLEAR:''
};
document.querySelectorAll('button[data-pick]').forEach(b => b.addEventListener('click', () => {
const k = b.dataset.pick;
const set = PICKS[k];
cb.forEach(c => {
if (set === '*') c.checked = true;
else if (set === '') c.checked = false;
else c.checked = (set || []).map(s=>s.toLowerCase()).includes(c.value.toLowerCase());
});
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'));
if (!req.body || typeof req.body !== 'object') {
return res.status(400).send('Invalid request body');
}
const b = req.body as Record<string, any>;
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 cities — POST may send a single string OR an array.
const rawCities = b.cities;
let cities: string[] = [];
if (Array.isArray(rawCities)) cities = rawCities.map((c: any) => String(c).trim()).filter(Boolean);
else if (typeof rawCities === 'string' && rawCities.trim()) cities = [rawCities.trim()];
if (kind === 'one_time' && cities.length === 0)
return res.redirect('/data?err=' + encodeURIComponent('Pick at least one city'));
// Snapshot the attorney count for chosen cities (one_time) or all-CA (subscription).
const attorneyCount = kind === 'subscription'
? (await query<{ n: string }>(`SELECT COUNT(*)::text AS n FROM professionals WHERE bar_number IS NOT NULL`)).rows[0].n
: String(await attorneyCountForCities(cities));
const attorneyCountInt = parseInt(attorneyCount, 10);
const amount = computePrice(kind, cities.length, attorneyCountInt);
const downloadToken = crypto.randomBytes(32).toString('hex');
const ins = await query<{ id: number }>(`
INSERT INTO data_orders (
kind, email, full_name, company, cities, attorney_count, amount_cents,
download_token, ip, user_agent
) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10) RETURNING id
`, [
kind, email,
String(b.full_name || '').trim().slice(0, 120) || null,
String(b.company || '').trim().slice(0, 120) || null,
kind === 'subscription' ? [] : cities,
attorneyCountInt, amount, downloadToken,
req.ip || null, (req.headers['user-agent'] || '').toString().slice(0, 200),
]);
const orderId = ins.rows[0].id;
// Stripe redirect URLs are pinned to PUBLIC_BASE_URL — never derived from
// request headers, which can be forged on a direct (non-proxied) hit.
const base = process.env.PUBLIC_BASE_URL?.replace(/\/+$/, '')
|| 'https://lawyers.agentabrams.com';
try {
let session: Stripe.Checkout.Session;
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: 'Counsel & Bar — Attorney Data, monthly subscription',
description: 'Unlimited California-city 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 {
session = await stripe.checkout.sessions.create({
mode: 'payment',
payment_method_types: ['card'],
customer_email: email,
line_items: [{
price_data: {
currency: 'usd',
product_data: {
name: `Counsel & Bar — Attorney List, ${cities.length} ${cities.length === 1 ? 'city' : 'cities'}`,
description: `${attorneyCountInt.toLocaleString()} California-licensed attorneys · ${cities.slice(0, 6).join(', ')}${cities.length > 6 ? ', …' : ''}`,
},
unit_amount: amount,
},
quantity: 1,
}],
metadata: {
kind: 'data_one_time',
order_id: String(orderId),
cities: cities.slice(0, 8).join('|').slice(0, 480),
attorney_count: String(attorneyCountInt),
},
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) {
console.error('[data] checkout error:', e);
res.redirect('/data?err=' + encodeURIComponent('Stripe error: ' + (e as Error).message.slice(0, 160)));
}
});
// ─── GET /data/thanks — Stripe redirect, mark paid ────────────────────────
router.get('/data/thanks', async (req, res) => {
const sid = (req.query.session_id as string) || '';
let paid = false;
let token = '';
let kind: 'one_time' | 'subscription' = 'one_time';
let orderId: number | null = null;
let amountPaid = 4900;
if (stripe && sid) {
try {
const s = await stripe.checkout.sessions.retrieve(sid);
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 (typeof s.amount_total === 'number') amountPaid = s.amount_total;
if (orderId && isPaid) {
const r = await query<{ download_token: string; kind: string }>(`
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 status = 'pending_payment'
RETURNING download_token, kind
`, [orderId, subId || null, custId || null]);
if (r.rowCount && r.rows[0]) {
paid = true;
token = r.rows[0].download_token;
kind = r.rows[0].kind === 'subscription' ? 'subscription' : 'one_time';
} else {
// Maybe already marked paid on a prior page-load. Re-read.
const r2 = await query<{ download_token: string; kind: string; status: string }>(
`SELECT download_token, kind, status FROM data_orders WHERE id = $1`, [orderId]);
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 now = new Date();
const pad = (n: number) => String(n).padStart(2, '0');
const orderNo = orderId
? `DATA-${now.getFullYear()}-${pad(now.getMonth() + 1)}${pad(now.getDate())}-${String(orderId).padStart(4, '0')}`
: `DATA-${now.getFullYear()}-${pad(now.getMonth() + 1)}${pad(now.getDate())}-PEND`;
const tstamp = `${pad(now.getHours())}:${pad(now.getMinutes())} PT · ${now.toLocaleDateString('en-US', { month: 'short', day: 'numeric', year: 'numeric' })}`;
const dollars = `$${(amountPaid / 100).toFixed(0)}${kind === 'subscription' ? '/mo' : ''}`;
const body = paid
? `
<style>
.dt-receipt{margin:32px 0 12px;padding:24px 32px;border:1px solid var(--rule);background:linear-gradient(180deg,rgba(184,153,104,.025),transparent);display:grid;grid-template-columns:repeat(3,1fr);gap:32px}
@media (max-width:720px){.dt-receipt{grid-template-columns:1fr;gap:14px;padding:20px 24px}}
.dt-r-row{display:flex;flex-direction:column;gap:4px}
.dt-r-label{font-size:9px;letter-spacing:.3em;text-transform:uppercase;color:var(--ink-mute);font-weight:500}
.dt-r-val{font-family:var(--serif);font-style:italic;color:var(--metal);font-size:18px;font-variant-numeric:tabular-nums;letter-spacing:.02em}
.dt-next{margin:64px 0 0;padding:48px 0 0;border-top:1px solid var(--rule)}
.dt-next-h{font-family:var(--serif);font-weight:400;font-size:24px;color:var(--ink);margin:0 0 28px;letter-spacing:-.005em}
.dt-next-grid{display:grid;grid-template-columns:repeat(3,1fr);gap:0;border-top:1px solid var(--rule);border-bottom:1px solid var(--rule)}
@media (max-width:780px){.dt-next-grid{grid-template-columns:1fr}}
.dt-next-cell{padding:32px 28px;border-right:1px solid var(--rule);display:flex;flex-direction:column;gap:12px}
.dt-next-cell:last-child{border-right:none}
@media (max-width:780px){.dt-next-cell{border-right:none;border-bottom:1px solid var(--rule);padding:28px 20px}.dt-next-cell:last-child{border-bottom:none}}
.dt-next-num{font-family:var(--serif);font-style:italic;font-weight:300;color:transparent;-webkit-text-stroke:1px var(--metal);font-size:28px;line-height:1;letter-spacing:-.02em}
.dt-next-cell h4{font-family:var(--serif);font-weight:400;font-size:18px;line-height:1.25;color:var(--ink);margin:0;letter-spacing:-.005em}
.dt-next-cell p{font-size:13px;line-height:1.6;color:var(--ink-mute);margin:0;flex:1}
.dt-dl{padding:16px 28px;background:transparent;color:var(--metal-glow);border:1px solid var(--metal);font-size:11px;letter-spacing:.22em;text-transform:uppercase;font-weight:500;text-decoration:none;display:inline-flex;align-items:center;transition:background 280ms ease,color 280ms ease,letter-spacing 280ms ease;align-self:flex-start}
.dt-dl:hover{background:var(--metal);color:var(--noir);letter-spacing:.26em}
.dt-bookmark{margin:18px 0 0;font-size:12px;color:var(--ink-mute)}
.dt-bookmark code{background:var(--noir-rise);padding:8px 14px;display:inline-block;margin-top:6px;border:1px solid var(--rule);color:var(--ink-soft);font-size:12px;letter-spacing:0;font-family:ui-monospace,SFMono-Regular,Menlo,monospace;word-break:break-all;max-width:100%}
.dt-trust{margin-top:48px;padding:24px 28px;border:1px solid var(--rule);background:linear-gradient(180deg,rgba(184,153,104,.018),transparent)}
.dt-trust-eyebrow{font-size:9px;letter-spacing:.3em;text-transform:uppercase;color:var(--metal);font-weight:500;margin:0 0 12px}
.dt-trust p{font-size:13px;line-height:1.7;color:var(--ink-mute);margin:0;max-width:none}
</style>
<div class="public-vol">
<span class="v-rule"></span>
<span class="v-text">Volume I</span>
<span class="v-meta">Edition 2026 · ${kind === 'subscription' ? 'Subscription Active' : 'Order Confirmed'}</span>
</div>
<h1 class="display">Your data is <em>ready.</em></h1>
<p class="lede">${kind === 'subscription'
? 'Your subscription is active. The download link below is the same key for every monthly refresh — bookmark it.'
: 'Your one-time export is ready. The download link is yours for the next 30 days.'}</p>
<div class="dt-receipt">
<div class="dt-r-row">
<span class="dt-r-label">Order №</span>
<span class="dt-r-val">${orderNo}</span>
</div>
<div class="dt-r-row">
<span class="dt-r-label">Logged</span>
<span class="dt-r-val">${tstamp}</span>
</div>
<div class="dt-r-row">
<span class="dt-r-label">${kind === 'subscription' ? 'Recurring' : 'Charged'}</span>
<span class="dt-r-val">${dollars}</span>
</div>
</div>
<section class="dt-next">
<h3 class="dt-next-h">What's next</h3>
<div class="dt-next-grid">
<div class="dt-next-cell">
<span class="dt-next-num">i.</span>
<h4>Download the CSV</h4>
<p>Excel-compatible. Columns: Name, Bar #, Firm, Phone, Address, Web. Data exported as of just now.</p>
<a class="dt-dl" href="${esc(dlUrl)}">Download →</a>
</div>
<div class="dt-next-cell">
<span class="dt-next-num">ii.</span>
<h4>Bookmark your download URL</h4>
<p>Your private link is below. The token is unique to you and persists for the duration of your access.</p>
<p class="dt-bookmark"><code>https://lawyers.agentabrams.com${esc(dlUrl)}</code></p>
</div>
<div class="dt-next-cell">
<span class="dt-next-num">iii.</span>
<h4>Use the data lawfully</h4>
<p>State Bar permitted-use rules + CCPA/CPRA apply. No bulk SMS, no robocalls, no resale. We exclude inactive/suspended/deceased attorneys at export time.</p>
</div>
</div>
</section>
<aside class="dt-trust">
<p class="dt-trust-eyebrow">For the record</p>
<p>Data is sourced from the State Bar of California's public licensing database and supplemented with firm-level contact information from publicly indexed websites and LA City Active Business Licenses (NAICS 5411). The directory listing is independent of any commercial offering; this is a research-data product, not a placement service. We do not sell consumer leads. Stripe processes the payment; full card data remains with Stripe per their PCI flow.</p>
</aside>
`
: `<div class="public-vol"><span class="v-rule"></span><span class="v-text">Volume I</span><span class="v-meta">Edition 2026 · Reservation Pending</span></div>
<h1 class="display">Reservation <em>noted.</em></h1>
<p class="lede">If your payment is still processing, give Stripe a few seconds and refresh. If you cancelled, no charge was made.</p>
<p style="margin-top:32px"><a href="/data" style="font-size:11px;letter-spacing:.18em;text-transform:uppercase;color:var(--metal);border-bottom:1px solid var(--rule);padding-bottom:3px">← Back to data</a></p>`;
res.send(shellHead(paid ? (kind === 'subscription' ? 'Subscription Active' : 'Order Confirmed') : 'Reservation Pending') + 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<{
id: number; kind: string; status: string; cities: string[];
stripe_subscription_id: string | null; paid_at: string | null;
}>(`SELECT id, kind, status, cities, stripe_subscription_id, paid_at
FROM data_orders WHERE download_token = $1`, [token]);
if (!r.rowCount) return res.status(404).send('Token not found');
const o = r.rows[0];
if (o.status !== 'paid') return res.status(402).send('Order not paid');
// 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 orders return all CA; one-time returns the chosen cities.
const params: unknown[] = [];
let where = `WHERE p.bar_number IS NOT NULL AND o.type='law_firm'`;
if (o.kind !== 'subscription' && o.cities && o.cities.length > 0) {
params.push(o.cities.map(c => c.toLowerCase()));
where += ` AND LOWER(o.city) = ANY($${params.length}::text[])`;
}
const rows = await query<{
full_name: string; bar_number: string; license_status: string | null;
firm_name: string | null; phone: string | null; address: string | null;
city: string | null; zip: string | null; website: string | null;
}>(`
SELECT
p.full_name, p.bar_number, p.license_status,
o.name AS firm_name,
COALESCE(o.phone, ph.phone) AS phone,
o.address, o.city, o.zip,
o.website
FROM professionals p
JOIN professional_locations pl ON pl.professional_id = p.id AND pl.is_primary
JOIN organizations o ON o.id = pl.organization_id
LEFT JOIN LATERAL (
SELECT phone FROM phones WHERE organization_id = o.id LIMIT 1
) ph ON true
${where}
ORDER BY p.full_name
`, 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 = `counsel-and-bar-attorneys-${today}.csv`;
res.setHeader('Content-Type', 'text/csv; charset=utf-8');
res.setHeader('Content-Disposition', `attachment; filename="${fname}"`);
// CSV escaper — wraps every field in double-quotes, doubles internal quotes.
const csvCell = (v: any) => {
const s = v == null ? '' : String(v);
return '"' + s.replace(/"/g, '""') + '"';
};
res.write(['Full Name','Bar #','License Status','Firm','Phone','Address','City','ZIP','Website'].map(csvCell).join(',') + '\n');
for (const row of rows.rows) {
res.write([
row.full_name, row.bar_number, row.license_status,
row.firm_name, row.phone, row.address, row.city, row.zip, row.website,
].map(csvCell).join(',') + '\n');
}
res.end();
});
// ─── /admin/data-orders ───────────────────────────────────────────────────
router.get('/admin/data-orders', requireAdmin, async (req, res) => {
const rows = await query<{
id: number; kind: string; email: string; full_name: string | null;
company: string | null; cities: string[]; attorney_count: number;
amount_cents: number; status: string; downloads_count: number;
paid_at: string | null; created_at: string; payment_link: string | null;
}>(`SELECT id, kind, email, full_name, company, cities, attorney_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<{ k: string; v: string }>(`
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: Record<string, string> = {}; for (const r of counts.rows) cm[r.k] = r.v;
const trs = rows.rows.map(o => `<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">${o.kind === 'subscription' ? 'all CA' : (o.cities || []).slice(0,3).join(', ') + ((o.cities||[]).length > 3 ? ' +' + ((o.cities||[]).length - 3) : '')}</span></td>
<td>${o.attorney_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 / Cities</th><th>Attorneys</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);
});
// ─── /webhooks/stripe-data — separate endpoint (registered separately in Stripe) ──
router.post('/webhooks/stripe-data', express.raw({ type: 'application/json' }), async (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'] as string;
let event: Stripe.Event;
try { event = stripe.webhooks.constructEvent(req.body, sig, secret); }
catch (e) { return res.status(400).send(`webhook signature err: ${(e as Error).message}`); }
switch (event.type) {
case 'checkout.session.completed': {
const s = event.data.object as Stripe.Checkout.Session;
const orderId = s.metadata?.order_id ? parseInt(s.metadata.order_id, 10) : null;
if (orderId && (s.metadata?.kind === 'data_one_time' || s.metadata?.kind === 'data_subscription')) {
const subId = (typeof s.subscription === 'string') ? s.subscription : s.subscription?.id;
const custId = (typeof s.customer === 'string') ? s.customer : s.customer?.id;
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 status='pending_payment'`,
[orderId, subId || null, custId || null]);
}
break;
}
case 'customer.subscription.deleted': {
const sub = event.data.object as Stripe.Subscription;
await query(`UPDATE data_orders SET status='sub_cancelled', cancelled_at=NOW()
WHERE stripe_subscription_id = $1 AND status='paid'`, [sub.id]);
break;
}
}
res.json({ received: true });
});
export default router;