← back to Ventura Claw Leads
routes/public.js
782 lines
const express = require('express');
const crypto = require('node:crypto');
const db = require('../lib/db');
const { VERTICALS, BY_KEY } = require('../lib/verticals');
const router = express.Router();
// In-process cache for /api/businesses.geo — prevents repeated full-table scans
// from spiking Node RSS beyond the pm2 max_memory_restart threshold.
// TTL matches the HTTP cache-control: max-age=120.
let _geoCache = null;
let _geoCacheAt = 0;
const GEO_CACHE_TTL_MS = 120_000;
router.get('/', async (req, res, next) => {
try {
const stats = await db.one(`
SELECT COUNT(*)::int AS total,
COUNT(DISTINCT vertical)::int AS vertical_count,
COUNT(DISTINCT city)::int AS city_count,
(SELECT COUNT(*)::int FROM business_interest
WHERE created_at > now() - interval '30 days') AS messages_30d,
(SELECT COUNT(*)::int FROM businesses
WHERE status = 'active' AND claim_status IN ('self','claimed')) AS claimed_count
FROM businesses WHERE status = 'active'
`);
// Recently-joined rail. Only claimed (real onboarded) businesses;
// unclaimed seed shells would game this. Cap at 6 so it stays a single
// row on desktop. Fall through to no rendering when fewer than 3.
const recentlyJoined = await db.many(`
SELECT slug, business_name, vertical, neighborhood, city, photo_path, claimed_at
FROM businesses
WHERE status = 'active'
AND claim_status IN ('self','claimed')
AND claimed_at IS NOT NULL
ORDER BY claimed_at DESC
LIMIT 6
`);
const featured = await db.many(`
SELECT slug, business_name, vertical, neighborhood, city, headline, photo_path
FROM businesses
WHERE status = 'active' AND (claim_status IN ('self','claimed') OR tier != 'free')
ORDER BY tier = 'premier' DESC, tier = 'standard' DESC,
(photo_path IS NOT NULL) DESC, -- within tier, photos beat no-photos
RANDOM()
LIMIT 9
`);
const verticalCounts = await db.many(`
SELECT vertical, COUNT(*)::int AS n FROM businesses WHERE status = 'active' GROUP BY vertical
`);
const verticalCountMap = Object.fromEntries(verticalCounts.map(r => [r.vertical, r.n]));
// Per-vertical sample rails. Pick the 4 verticals with the most active
// listings (currently food/beauty/retail/creative ≈ 1,690 of 1,819).
// For each, pull 6 sample businesses prioritizing those with photos
// and claimed status — they look nicer in the rail and reward owners
// who've put work into their page. Random within each priority bucket
// so the rail rotates per request.
const TOP_RAIL_VERTICALS = Object.entries(verticalCountMap)
.sort((a, b) => b[1] - a[1])
.slice(0, 4)
.map(([k]) => k);
const verticalSamples = {};
for (const vk of TOP_RAIL_VERTICALS) {
const rows = await db.many(`
SELECT id, slug, business_name, vertical, headline, neighborhood, city, state,
tier, claim_status, verified, photo_path
FROM businesses
WHERE status = 'active' AND vertical = $1
ORDER BY (photo_path IS NOT NULL) DESC,
(claim_status IN ('self','claimed')) DESC,
tier = 'premier' DESC, tier = 'standard' DESC,
RANDOM()
LIMIT 6
`, [vk]);
if (rows.length) verticalSamples[vk] = rows;
}
res.render('public/home', {
title: 'Ventura Claw — directory of Ventura Blvd small businesses',
metaDescription: `Find local restaurants, salons, retail, fitness, pet, auto detail, cleaning, and creative businesses on Ventura Blvd. ${stats.total} listings across Sherman Oaks, Studio City, Encino, Tarzana, and Woodland Hills — owners claim and update their own page.`,
stats, featured, recentlyJoined, verticals: VERTICALS, verticalCountMap,
verticalSamples
});
} catch (err) { next(err); }
});
router.get('/find', async (req, res, next) => {
try {
const q = (req.query.q || '').trim().slice(0, 120);
const v = (req.query.vertical || '').trim();
const city = (req.query.city || '').trim().slice(0, 60);
const where = [`status = 'active'`];
const params = [];
if (q) {
params.push(`%${q.toLowerCase()}%`);
where.push(`(LOWER(business_name) LIKE $${params.length} OR LOWER(headline) LIKE $${params.length} OR LOWER(neighborhood) LIKE $${params.length})`);
}
if (v && BY_KEY[v]) {
params.push(v);
where.push(`vertical = $${params.length}`);
}
if (city) {
params.push(city);
where.push(`city ILIKE $${params.length}`);
}
// Sort whitelist — anything else falls back to 'featured' (server's natural order
// = tier-priority + claimed-first + name-asc). This is the directory-equivalent
// of the standing 'sort + density slider' rule for product grids.
const sortKey = String(req.query.sort || 'featured').trim();
const orderClauses = {
featured: `tier = 'premier' DESC, tier = 'standard' DESC, claim_status IN ('self','claimed') DESC, business_name ASC`,
newest: `created_at DESC`,
updated: `updated_at DESC`,
name_asc: `business_name ASC`,
name_desc: `business_name DESC`,
vertical: `vertical ASC, business_name ASC`
};
const orderBy = orderClauses[sortKey] || orderClauses.featured;
// Page param: 1-indexed, capped at a reasonable max so Google can crawl
// them all but a malicious actor can't ask for page=99999. Each page is
// 200 rows. Adding ORDER BY id as a tiebreaker keeps the order stable
// across pages so a row never appears on two pages.
const PAGE_SIZE = 200;
const MAX_PAGE = 50;
const requestedPage = parseInt(req.query.page || '1', 10);
const page = Math.max(1, Math.min(MAX_PAGE, isNaN(requestedPage) ? 1 : requestedPage));
const offset = (page - 1) * PAGE_SIZE;
const businesses = await db.many(`
SELECT id, slug, business_name, vertical, headline, neighborhood, city, state, tier, claim_status, verified, photo_path
FROM businesses
WHERE ${where.join(' AND ')}
ORDER BY ${orderBy}, id ASC
LIMIT ${PAGE_SIZE} OFFSET $${params.length + 1}
`, [...params, offset]);
// Per-vertical custom title + H1 = real SEO value. Each vertical filter is
// a distinct landing page in Google's eyes — 8 vertical-specific URLs that
// can rank for "[vertical] sherman oaks" / "studio city [vertical]" etc.
// Pull the unfiltered total + per-city distribution so the meta description
// reflects the real directory size (not the page LIMIT) and includes city
// names — Google rewards meta descriptions with concrete entity names.
const totalForFilter = await db.one(
`SELECT COUNT(*)::int AS n FROM businesses WHERE ${where.join(' AND ')}`,
params
);
const cityRows = await db.many(
`SELECT city, COUNT(*)::int AS n
FROM businesses
WHERE ${where.join(' AND ')}
GROUP BY city ORDER BY n DESC LIMIT 5`,
params
);
const totalCount = totalForFilter?.n ?? businesses.length;
const topCities = cityRows.map(c => c.city).filter(Boolean);
const verticalLabel = v && BY_KEY[v] ? BY_KEY[v].label : null;
const cityLabel = city || null;
const titlePartsList = [verticalLabel, cityLabel ? 'in ' + cityLabel : null].filter(Boolean);
const customTitle = titlePartsList.length
? `${titlePartsList.join(' ')} on Ventura Blvd · Ventura Claw`
: 'Find a business — Ventura Claw';
const cityListing = topCities.length ? ` in ${topCities.slice(0, 3).join(', ')}` : '';
const customMeta = titlePartsList.length
? `${totalCount} ${verticalLabel ? verticalLabel.toLowerCase() : 'local'} businesses on Ventura Blvd${cityLabel ? ' in ' + cityLabel : cityListing}. Search by name or message a business directly — Ventura Claw routes leads with no markup.`
: `Browse ${totalCount} local businesses on Ventura Blvd${cityListing}.${v ? ' Filtered by ' + (BY_KEY[v]?.label || v) + '.' : ''}`;
const customH1 = titlePartsList.length
? `${titlePartsList.join(' ')} on Ventura Blvd`
: 'Search Ventura Blvd';
const totalPages = Math.max(1, Math.ceil(totalCount / PAGE_SIZE));
// Build absolute prev/next URLs for the head <link rel> tags. SEO signal
// for paginated content — Google uses these to understand the series.
const baseUrl = (process.env.PUBLIC_URL || 'https://leads.venturaclaw.com').replace(/\/+$/, '');
const buildPageUrl = (n) => {
const qsParts = [];
if (q) qsParts.push(`q=${encodeURIComponent(q)}`);
if (v) qsParts.push(`vertical=${encodeURIComponent(v)}`);
if (city) qsParts.push(`city=${encodeURIComponent(city)}`);
if (sortKey && orderClauses[sortKey] && sortKey !== 'featured') qsParts.push(`sort=${encodeURIComponent(sortKey)}`);
if (n > 1) qsParts.push(`page=${n}`);
return baseUrl + '/find' + (qsParts.length ? '?' + qsParts.join('&') : '');
};
const prevUrl = page > 1 ? buildPageUrl(page - 1) : null;
const nextUrl = page < totalPages ? buildPageUrl(page + 1) : null;
// Canonical for /find: include the meaningful filter params so Google
// indexes /find?vertical=beauty&page=2 as its own page, not as a dup of
// /find. Use the same buildPageUrl helper to ensure consistent ordering.
const canonicalPath = buildPageUrl(page).replace(baseUrl, '');
const canonicalUrl = baseUrl + canonicalPath;
// rel=alternate JSON representation of this exact query — apps + agents +
// structured-data crawlers can fetch the same paginated results without
// HTML scraping. Mirrors the HTML query string into /api/find.
const buildApiUrl = (n) => {
const qsParts = [];
if (q) qsParts.push(`q=${encodeURIComponent(q)}`);
if (v) qsParts.push(`vertical=${encodeURIComponent(v)}`);
if (city) qsParts.push(`city=${encodeURIComponent(city)}`);
if (sortKey && orderClauses[sortKey] && sortKey !== 'featured') qsParts.push(`sort=${encodeURIComponent(sortKey)}`);
if (n > 1) qsParts.push(`page=${n}`);
return baseUrl + '/api/find' + (qsParts.length ? '?' + qsParts.join('&') : '');
};
const alternateJsonUrl = buildApiUrl(page);
// HTTP Link header — equivalent to <link rel> tags in head, but readable
// by crawlers that don't parse HTML (and survives any proxy that strips
// head tags). Order matches RFC 8288.
const linkParts = [`<${canonicalUrl}>; rel="canonical"`];
if (prevUrl) linkParts.push(`<${prevUrl}>; rel="prev"`);
if (nextUrl) linkParts.push(`<${nextUrl}>; rel="next"`);
linkParts.push(`<${alternateJsonUrl}>; rel="alternate"; type="application/json"`);
res.set('Link', linkParts.join(', '));
res.render('public/find', {
title: customTitle,
metaDescription: customMeta,
businesses, totalCount, q, v, city, verticals: VERTICALS,
sort: orderClauses[sortKey] ? sortKey : 'featured',
customH1, verticalLabel,
page, totalPages, pageSize: PAGE_SIZE,
prevUrl, nextUrl, canonicalPath,
alternateJsonUrl
});
} catch (err) { next(err); }
});
// JSON-API mirror of /find — same query/filter/pagination, JSON output.
// Surfaced via rel="alternate" link tag + HTTP Link header on the HTML /find
// route so apps/agents/structured-data crawlers can fetch the same data
// without scraping HTML. Matches /find's whitelist + page caps exactly.
router.get('/api/find', async (req, res, next) => {
try {
const q = (req.query.q || '').trim().slice(0, 120);
const v = (req.query.vertical || '').trim();
const city = (req.query.city || '').trim().slice(0, 60);
const where = [`status = 'active'`];
const params = [];
if (q) {
params.push(`%${q.toLowerCase()}%`);
where.push(`(LOWER(business_name) LIKE $${params.length} OR LOWER(headline) LIKE $${params.length} OR LOWER(neighborhood) LIKE $${params.length})`);
}
if (v && BY_KEY[v]) {
params.push(v);
where.push(`vertical = $${params.length}`);
}
if (city) {
params.push(city);
where.push(`city ILIKE $${params.length}`);
}
const sortKey = String(req.query.sort || 'featured').trim();
const orderClauses = {
featured: `tier = 'premier' DESC, tier = 'standard' DESC, claim_status IN ('self','claimed') DESC, business_name ASC`,
newest: `created_at DESC`,
updated: `updated_at DESC`,
name_asc: `business_name ASC`,
name_desc: `business_name DESC`,
vertical: `vertical ASC, business_name ASC`
};
const orderBy = orderClauses[sortKey] || orderClauses.featured;
const PAGE_SIZE = 200;
const MAX_PAGE = 50;
const requestedPage = parseInt(req.query.page || '1', 10);
const page = Math.max(1, Math.min(MAX_PAGE, isNaN(requestedPage) ? 1 : requestedPage));
const offset = (page - 1) * PAGE_SIZE;
const businesses = await db.many(`
SELECT id, slug, business_name, vertical, headline, neighborhood, city, state, tier, claim_status, verified, photo_path
FROM businesses
WHERE ${where.join(' AND ')}
ORDER BY ${orderBy}, id ASC
LIMIT ${PAGE_SIZE} OFFSET $${params.length + 1}
`, [...params, offset]);
const totalForFilter = await db.one(
`SELECT COUNT(*)::int AS n FROM businesses WHERE ${where.join(' AND ')}`,
params
);
const totalCount = totalForFilter?.n ?? businesses.length;
const totalPages = Math.max(1, Math.ceil(totalCount / PAGE_SIZE));
res.set('Cache-Control', 'public, max-age=300');
res.json({
query: { q, vertical: v || null, city: city || null, sort: orderClauses[sortKey] ? sortKey : 'featured' },
pagination: { page, totalPages, pageSize: PAGE_SIZE, totalCount },
results: businesses
});
} catch (err) { next(err); }
});
// Per-neighborhood landing pages. Match by slugified neighborhood OR city
// so /neighborhood/sherman-oaks catches both city='Sherman Oaks' and
// neighborhood='Sherman Oaks' rows. SEO win: Google can rank the URL for
// '<neighborhood> businesses'-style searches.
const NEIGHBORHOODS = [
{ slug: 'sherman-oaks', label: 'Sherman Oaks' },
{ slug: 'studio-city', label: 'Studio City' },
{ slug: 'encino', label: 'Encino' },
{ slug: 'tarzana', label: 'Tarzana' },
{ slug: 'woodland-hills', label: 'Woodland Hills' }
];
router.get('/neighborhood/:slug', async (req, res, next) => {
try {
const slug = String(req.params.slug || '').toLowerCase().trim();
const meta = NEIGHBORHOODS.find(n => n.slug === slug);
if (!meta) return res.status(404).render('public/404', { title: 'Not found' });
// Sort whitelist — matches the /find route's options so the JS layer can
// reuse the same persisted localStorage key shape. 'featured' is the
// server's natural directory order (tier-priority + claimed-first + photo).
const sortKey = String(req.query.sort || 'featured').trim();
const orderClauses = {
featured: `tier = 'premier' DESC, tier = 'standard' DESC, claim_status IN ('self','claimed') DESC, (photo_path IS NOT NULL) DESC, business_name ASC`,
newest: `created_at DESC`,
updated: `updated_at DESC`,
name_asc: `business_name ASC`,
name_desc: `business_name DESC`,
vertical: `vertical ASC, business_name ASC`
};
const orderBy = orderClauses[sortKey] || orderClauses.featured;
const businesses = await db.many(`
SELECT id, slug, business_name, vertical, headline, neighborhood, city, state,
tier, claim_status, verified, photo_path
FROM businesses
WHERE status = 'active'
AND (LOWER(REPLACE(neighborhood, ' ', '-')) = $1
OR LOWER(REPLACE(city, ' ', '-')) = $1)
ORDER BY ${orderBy}, id ASC
LIMIT 200
`, [slug]);
// Per-vertical counts for this neighborhood — drives the inline
// 'Beauty in Sherman Oaks (155)' cross-links that point to the new
// /find?vertical=X&city=Y combo pages. Strong internal-linking signal
// for Google: the combo pages are part of the site's primary structure,
// not just sitemap orphans.
const verticalRows = await db.many(`
SELECT vertical, COUNT(*)::int AS n
FROM businesses
WHERE status = 'active'
AND (LOWER(REPLACE(neighborhood, ' ', '-')) = $1
OR LOWER(REPLACE(city, ' ', '-')) = $1)
GROUP BY vertical
`, [slug]);
const verticalCountMap = Object.fromEntries(verticalRows.map(r => [r.vertical, r.n]));
const totalCount = verticalRows.reduce((sum, r) => sum + r.n, 0);
res.render('public/neighborhood', {
title: `${meta.label} businesses on Ventura Blvd · Ventura Claw`,
metaDescription: `${totalCount} small businesses on Ventura Blvd in ${meta.label} — restaurants, salons, retail, fitness, pet, auto detail, cleaning, creative pros. Search and message a business directly via Ventura Claw.`,
neighborhood: meta,
businesses, totalCount,
verticals: VERTICALS,
verticalCountMap,
sort: orderClauses[sortKey] ? sortKey : 'featured'
});
} catch (err) { next(err); }
});
router.get('/business/:slug', async (req, res, next) => {
try {
const biz = await db.one(`
SELECT * FROM businesses WHERE slug = $1 AND status IN ('active','pending')
`, [req.params.slug]);
if (!biz) return res.status(404).render('public/404', { title: 'Not found' });
// Track view — one increment per session per business per 30min window.
// Bot user-agents skipped (heuristic, not perfect; better than nothing).
// Logged-in business owner viewing their own profile doesn't count.
const ua = String(req.headers['user-agent'] || '').toLowerCase();
const isBot = /bot|crawler|spider|slurp|preview|fetch|monitor|pingdom|uptime|headlesschrome/.test(ua);
const isOwner = res.locals.currentBusiness && res.locals.currentBusiness.id === biz.id;
if (!isBot && !isOwner && req.session) {
const seenKey = 'viewed_' + biz.id;
const lastSeen = req.session[seenKey];
const now = Date.now();
if (!lastSeen || (now - lastSeen) > 30 * 60 * 1000) {
req.session[seenKey] = now;
// Fire-and-forget; don't block render on a counter write.
db.query(
`INSERT INTO business_views (business_id, day, n)
VALUES ($1, CURRENT_DATE, 1)
ON CONFLICT (business_id, day) DO UPDATE SET n = business_views.n + 1`,
[biz.id]
).catch(e => console.warn('[views] write fail', e.message));
}
}
// Activity stat — only surface when meaningful. Threshold = 3 messages OR
// 25 views so newly listed businesses with zero traction don't show "0".
const activity = await db.one(`
SELECT
(SELECT COUNT(*)::int FROM business_interest
WHERE business_id = $1 AND created_at > now() - interval '30 days') AS msgs_30d,
(SELECT COALESCE(SUM(n), 0)::int FROM business_views
WHERE business_id = $1 AND day > CURRENT_DATE - interval '30 days') AS views_30d
`, [biz.id]);
const showActivity = activity.msgs_30d >= 3 || activity.views_30d >= 25;
// Similar-nearby rail: 3 same-vertical businesses sorted by squared
// cartesian lat/lng distance (good enough for a 12-mile corridor; no
// PostGIS extension required). Only when this business has coords.
let nearby = [];
if (biz.latitude != null && biz.longitude != null) {
nearby = await db.many(`
SELECT slug, business_name, vertical, neighborhood, city, photo_path
FROM businesses
WHERE id != $1
AND vertical = $2
AND status = 'active'
AND latitude IS NOT NULL AND longitude IS NOT NULL
ORDER BY POW(latitude - $3, 2) + POW(longitude - $4, 2) ASC
LIMIT 3
`, [biz.id, biz.vertical, biz.latitude, biz.longitude]);
}
// Open Graph image: prefer the business's own photo when present (better
// social-share preview), else fall back to the dynamically rendered
// /og.png card. Both are absolute URLs because OG crawlers don't run JS
// and need fully-qualified hrefs.
const _publicUrl = (process.env.PUBLIC_URL || 'https://leads.venturaclaw.com').replace(/\/+$/, '');
const ogImage = biz.photo_path
? (biz.photo_path.startsWith('http') ? biz.photo_path : _publicUrl + biz.photo_path)
: `${_publicUrl}/business/${encodeURIComponent(biz.slug)}/og.png`;
res.render('public/business', {
title: `${biz.business_name} · ${BY_KEY[biz.vertical]?.label || biz.vertical} on Ventura Blvd`,
metaDescription: biz.headline || `${biz.business_name} — ${BY_KEY[biz.vertical]?.label || biz.vertical} in ${biz.neighborhood || biz.city || 'the Ventura Blvd corridor'}.`,
business: biz,
verticalMeta: BY_KEY[biz.vertical] || null,
activity, showActivity, nearby,
verticals: VERTICALS,
ogImage
});
} catch (err) { next(err); }
});
// Dynamic OG image. 24h CDN cache. Generated PNG never carries personal
// data — just business name, vertical, neighborhood — so caching is safe.
router.get('/business/:slug/og.png', async (req, res, next) => {
try {
const biz = await db.one(`
SELECT slug, business_name, vertical, neighborhood
FROM businesses WHERE slug = $1 AND status = 'active'
`, [req.params.slug]);
if (!biz) return res.status(404).type('text/plain').send('not_found');
const og = require('../lib/og-image');
const png = await og.renderPng({
businessName: biz.business_name,
verticalKey: biz.vertical,
verticalLabel: BY_KEY[biz.vertical]?.label || biz.vertical,
neighborhood: biz.neighborhood || ''
});
res.set('Content-Type', 'image/png');
res.set('Cache-Control', 'public, max-age=86400, immutable');
res.send(png);
} catch (err) {
if (err && err.message === 'sharp_unavailable') {
return res.status(503).type('text/plain').send('og_unavailable');
}
next(err);
}
});
router.post('/business/:slug/contact', async (req, res, next) => {
try {
const biz = await db.one(`SELECT id, slug, business_name, claim_status, status FROM businesses WHERE slug = $1`, [req.params.slug]);
if (!biz) return res.status(404).render('public/404', { title: 'Not found' });
const consumer_name = String(req.body.name || '').trim().slice(0, 120);
const consumer_email = String(req.body.email || '').trim().toLowerCase().slice(0, 200);
const consumer_phone = String(req.body.phone || '').trim().slice(0, 32) || null;
const message = String(req.body.message || '').trim().slice(0, 1500) || null;
const zip = String(req.body.zip || '').trim().slice(0, 10) || null;
if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(consumer_email)) {
return res.status(400).render('public/contact-result', {
title: 'Email needed', business: biz, ok: false,
error: 'Please enter a valid email address so the business can reply.'
});
}
if (!consumer_name) {
return res.status(400).render('public/contact-result', {
title: 'Name needed', business: biz, ok: false,
error: 'Please enter your name so the business knows how to address you.'
});
}
const ip_hash = crypto
.createHash('sha256')
.update((req.headers['x-forwarded-for'] || req.ip || '') + (process.env.SESSION_SECRET || ''))
.digest('hex')
.slice(0, 16);
await db.query(`
INSERT INTO business_interest
(business_id, consumer_name, consumer_email, consumer_phone, message, zip, source, ip_hash, user_agent)
VALUES ($1, $2, $3, $4, $5, $6, 'profile_form', $7, $8)
`, [biz.id, consumer_name, consumer_email, consumer_phone, message, zip, ip_hash, String(req.headers['user-agent'] || '').slice(0, 200)]);
res.render('public/contact-result', {
title: 'Message sent', business: biz, ok: true,
consumer_name, consumer_email
});
} catch (err) { next(err); }
});
router.get('/for-businesses', (req, res) => {
res.render('public/for-businesses', {
title: 'For businesses — Ventura Claw',
metaDescription: 'List your Ventura Blvd business on Ventura Claw. Subscription tiers from $49/mo. Pay-per-lead billing for qualified consumer inquiries. No setup fees.'
});
});
router.get('/about', (req, res) => {
res.render('public/about', {
title: 'About Ventura Claw',
metaDescription: 'Ventura Claw is a directory and lead-routing service for unregulated small businesses on Ventura Blvd. We connect locals to nearby restaurants, salons, retail, fitness, pet, auto, cleaning, and creative services.'
});
});
router.get('/privacy', (req, res) => {
res.render('public/legal', { title: 'Privacy · Ventura Claw', kind: 'privacy' });
});
router.get('/terms', (req, res) => {
res.render('public/legal', { title: 'Terms · Ventura Claw', kind: 'terms' });
});
router.get('/map', async (req, res, next) => {
try {
const stats = await db.one(`
SELECT COUNT(*) FILTER (WHERE latitude IS NOT NULL)::int AS pinned,
COUNT(*)::int AS total
FROM businesses WHERE status = 'active'
`);
res.render('public/map', {
title: 'Map of businesses · Ventura Claw',
metaDescription: `${stats.pinned} of ${stats.total} small businesses on Ventura Blvd, mapped. Click any pin for the studio profile.`,
stats, verticals: VERTICALS
});
} catch (err) { next(err); }
});
// Share-track ping. Frontend fires after a successful navigator.share or
// clipboard copy. CSRF is bypassed at the server-mount level (see server.js).
// Per-session dedup mirrors the view-counter pattern — same browser hitting
// the share button repeatedly counts once per 30 minutes.
router.post('/api/businesses/:slug/share-track', async (req, res, next) => {
try {
const slug = String(req.params.slug || '').slice(0, 200);
const biz = await db.one(`SELECT id FROM businesses WHERE slug = $1 AND status = 'active'`, [slug]);
if (!biz) return res.json({ ok: false, reason: 'not_found' });
if (req.session) {
const seenKey = 'shared_' + biz.id;
const lastSeen = req.session[seenKey];
const now = Date.now();
if (lastSeen && (now - lastSeen) <= 30 * 60 * 1000) {
return res.json({ ok: true, dedup: true });
}
req.session[seenKey] = now;
}
await db.query(`UPDATE businesses SET share_count = share_count + 1 WHERE id = $1`, [biz.id]);
res.json({ ok: true });
} catch (err) { next(err); }
});
router.get('/api/businesses/suggest', async (req, res, next) => {
try {
const q = String(req.query.q || '').trim().toLowerCase().slice(0, 60);
if (q.length < 2) return res.json({ q, results: [] });
const results = await db.many(`
SELECT slug, business_name, vertical, neighborhood, city, photo_path
FROM businesses
WHERE status = 'active'
AND (LOWER(business_name) LIKE $1
OR LOWER(headline) LIKE $1
OR LOWER(neighborhood) LIKE $1
OR LOWER(city) LIKE $1)
ORDER BY
(LOWER(business_name) LIKE $2) DESC, -- prefix match wins
tier = 'premier' DESC, tier = 'standard' DESC,
claim_status IN ('self','claimed') DESC,
business_name ASC
LIMIT 8
`, [`%${q}%`, `${q}%`]);
res.set('Cache-Control', 'no-store').json({ q, results });
} catch (err) { next(err); }
});
router.get('/api/businesses.geo', async (req, res, next) => {
try {
const now = Date.now();
if (!_geoCache || (now - _geoCacheAt) > GEO_CACHE_TTL_MS) {
const rows = await db.many(`
SELECT id, slug, business_name, vertical, neighborhood, city, state, headline,
latitude, longitude, tier, claim_status, verified
FROM businesses
WHERE status = 'active' AND latitude IS NOT NULL AND longitude IS NOT NULL
`);
_geoCache = { businesses: rows };
_geoCacheAt = now;
}
res.set('cache-control', 'public, max-age=120').json(_geoCache);
} catch (err) { next(err); }
});
// Unsubscribe — both GET (landing page for humans clicking the link) and
// POST (RFC 8058 one-click unsubscribe via List-Unsubscribe-Post header).
// Token consumption + suppression-list write is idempotent: hitting the
// link twice doesn't error, second time is a no-op.
const compliance = require('../lib/compliance');
async function processUnsubscribe(token) {
if (!token) return { ok: false, reason: 'no_token' };
const consumed = await compliance.consumeUnsubscribeToken(token);
if (!consumed) return { ok: false, reason: 'invalid' };
// consumed_at may have been already set by a prior click — that's fine.
await compliance.addSuppression({
channel: consumed.channel,
identifier: consumed.identifier,
reason: 'unsubscribe',
source: 'unsubscribe_link',
businessId: consumed.business_id || null,
notes: 'campaign:' + (consumed.campaign || 'unknown')
});
return { ok: true, identifier: consumed.identifier, campaign: consumed.campaign };
}
router.get('/unsubscribe', async (req, res, next) => {
try {
const token = String(req.query.t || '').trim();
if (!token) {
return res.status(400).render('public/unsubscribe', {
title: 'Unsubscribe', ok: false, reason: 'no_token', identifier: null, campaign: null
});
}
const r = await processUnsubscribe(token);
res.render('public/unsubscribe', {
title: r.ok ? 'Unsubscribed' : 'Unsubscribe', ...r,
identifier: r.identifier || null, campaign: r.campaign || null,
reason: r.reason || null
});
} catch (err) { next(err); }
});
// One-click — RFC 8058. Email clients POST automatically when the user
// clicks the "Unsubscribe" button surfaced by Gmail/Outlook. No CSRF check
// because the token IS the bearer auth and the request comes from the
// recipient's mail client, not their browser session.
router.post('/unsubscribe', async (req, res, next) => {
try {
const token = String(req.query.t || req.body.t || '').trim();
const r = await processUnsubscribe(token);
res.json({ ok: r.ok, reason: r.reason || null });
} catch (err) { next(err); }
});
// SEO: robots.txt + sitemap.xml. Sitemap lists every active business +
// the static pages, with lastmod from businesses.updated_at so Google's
// crawler revisits when listings change.
router.get('/robots.txt', (req, res) => {
const url = (process.env.PUBLIC_URL || 'https://leads.venturaclaw.com').replace(/\/+$/, '');
res.type('text/plain').send(
`User-agent: *\nAllow: /\nDisallow: /admin\nDisallow: /api/\nDisallow: /webhooks/\nDisallow: /unsubscribe\nDisallow: /claim/\n\nSitemap: ${url}/sitemap.xml\n`
);
});
router.get('/sitemap.xml', async (req, res, next) => {
try {
const baseUrl = (process.env.PUBLIC_URL || 'https://leads.venturaclaw.com').replace(/\/+$/, '');
const businesses = await db.many(`
SELECT slug, updated_at, vertical, business_name, photo_path
FROM businesses
WHERE status = 'active'
ORDER BY updated_at DESC
LIMIT 50000
`);
const escape = (s) => String(s).replace(/&/g,'&').replace(/</g,'<').replace(/>/g,'>').replace(/"/g,'"').replace(/'/g,''');
const today = new Date().toISOString().slice(0, 10);
const staticPages = [
{ loc: '/', changefreq: 'daily', priority: '1.0' },
{ loc: '/find', changefreq: 'daily', priority: '0.9' },
{ loc: '/map', changefreq: 'daily', priority: '0.8' },
{ loc: '/for-businesses', changefreq: 'weekly', priority: '0.7' },
{ loc: '/about', changefreq: 'monthly', priority: '0.5' },
{ loc: '/privacy', changefreq: 'yearly', priority: '0.3' },
{ loc: '/terms', changefreq: 'yearly', priority: '0.3' }
];
// One <url> per vertical filter — useful long-tail landing pages.
// Plus page=2..N for any vertical whose total >200 (mirrors the /find
// pagination), so Google can crawl every business through the listing
// pages and not rely solely on direct /business/<slug> URLs.
const verticalCounts = await db.many(`
SELECT vertical, COUNT(*)::int AS n
FROM businesses WHERE status = 'active'
GROUP BY vertical
`);
const PAGE_SIZE = 200;
const verticalCountMap = Object.fromEntries(verticalCounts.map(r => [r.vertical, r.n]));
const verticalPages = [];
for (const v of VERTICALS) {
verticalPages.push({ loc: `/find?vertical=${v.key}`, changefreq: 'daily', priority: '0.7' });
const totalPages = Math.ceil((verticalCountMap[v.key] || 0) / PAGE_SIZE);
for (let p = 2; p <= totalPages; p++) {
verticalPages.push({ loc: `/find?vertical=${v.key}&page=${p}`, changefreq: 'daily', priority: '0.5' });
}
}
// One <url> per neighborhood landing page — strong local-SEO signal.
const neighborhoodPages = NEIGHBORHOODS.map(n => ({
loc: `/neighborhood/${n.slug}`, changefreq: 'daily', priority: '0.7'
}));
// 8 verticals × 5 cities = 40 long-tail combo landing pages. The /find
// route already supports both filters and renders a unique H1 + meta
// for each combo (e.g. "Beauty in Sherman Oaks on Ventura Blvd").
// Only include combos that actually have ≥3 businesses — empty results
// would be soft-404s in Google's eyes.
const comboCounts = await db.many(`
SELECT vertical, city, COUNT(*)::int AS n
FROM businesses
WHERE status = 'active' AND city IS NOT NULL
GROUP BY vertical, city
HAVING COUNT(*) >= 3
`);
const verticalCityPages = [];
for (const c of comboCounts) {
// city goes through encodeURIComponent so 'Sherman Oaks' → 'Sherman%20Oaks'.
// Sitemap URLs need to be percent-encoded, then '&' XML-escaped by escape().
verticalCityPages.push({
loc: `/find?vertical=${c.vertical}&city=${encodeURIComponent(c.city)}`,
changefreq: 'weekly', priority: '0.5'
});
}
// image: namespace declared on <urlset> so Google Image search can ingest
// <image:image> children for businesses that have a photo on file. Unphotographed
// listings still appear (just without an <image:image> child) — falling back
// to the dynamic OG card here would produce thin Image-search results since
// those aren't real photos of the business.
let xml = '<?xml version="1.0" encoding="UTF-8"?>\n<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9" xmlns:image="http://www.google.com/schemas/sitemap-image/1.1">\n';
for (const p of [...staticPages, ...verticalPages, ...neighborhoodPages, ...verticalCityPages]) {
xml += ` <url><loc>${baseUrl}${escape(p.loc)}</loc><lastmod>${today}</lastmod><changefreq>${p.changefreq}</changefreq><priority>${p.priority}</priority></url>\n`;
}
for (const b of businesses) {
const lastmod = (b.updated_at instanceof Date ? b.updated_at : new Date(b.updated_at)).toISOString().slice(0, 10);
const loc = `${baseUrl}/business/${escape(b.slug)}`;
let imageBlock = '';
if (b.photo_path) {
const imgUrl = b.photo_path.startsWith('http')
? b.photo_path
: baseUrl + b.photo_path;
imageBlock = `<image:image><image:loc>${escape(imgUrl)}</image:loc><image:title>${escape(b.business_name || '')}</image:title></image:image>`;
}
xml += ` <url><loc>${loc}</loc><lastmod>${lastmod}</lastmod><changefreq>weekly</changefreq><priority>0.6</priority>${imageBlock}</url>\n`;
}
xml += '</urlset>\n';
res.set('Cache-Control', 'public, max-age=3600').type('application/xml').send(xml);
} catch (err) { next(err); }
});
router.get('/healthz', async (req, res) => {
try {
await db.query('SELECT 1');
res.json({ ok: true, ts: Date.now() });
} catch (e) {
res.status(503).json({ ok: false, error: 'db_unreachable' });
}
});
module.exports = router;