← back to Ventura Corridor
iter 109+110: building-mates on /magazine/:id reader + scoreboard CSV export — reader page now also pulls v_building_canonical for the same canonical building (strip suite from address) and renders 'In the same building · <bldg_address>' section above 'More in <category>' carousel; lists up to 8 building-mates each as either a Cormorant-italic feature link (if featured) or 'not yet featured' grayed line w/ suite (if not); /api/magazine.csv full export of every feature with id/headline/subhead/category/status/model/views/generated_pt/published_pt/editorial_len/biz/address/city/zip/paid_ads_count, RFC-safe quoting, dated filename; /scoreboard.html nav grows '⬇ CSV' button
715edc1d188aaeb9c5598ca2639c867fc3860ee2 · 2026-05-06 17:43:36 -0700 · SteveStudio2
Files touched
M public/scoreboard.htmlM src/server/index.ts
Diff
commit 715edc1d188aaeb9c5598ca2639c867fc3860ee2
Author: SteveStudio2 <steve@designerwallcoverings.com>
Date: Wed May 6 17:43:36 2026 -0700
iter 109+110: building-mates on /magazine/:id reader + scoreboard CSV export — reader page now also pulls v_building_canonical for the same canonical building (strip suite from address) and renders 'In the same building · <bldg_address>' section above 'More in <category>' carousel; lists up to 8 building-mates each as either a Cormorant-italic feature link (if featured) or 'not yet featured' grayed line w/ suite (if not); /api/magazine.csv full export of every feature with id/headline/subhead/category/status/model/views/generated_pt/published_pt/editorial_len/biz/address/city/zip/paid_ads_count, RFC-safe quoting, dated filename; /scoreboard.html nav grows '⬇ CSV' button
---
public/scoreboard.html | 1 +
src/server/index.ts | 60 ++++++++++++++++++++++++++++++++++++++++++++++++--
2 files changed, 59 insertions(+), 2 deletions(-)
diff --git a/public/scoreboard.html b/public/scoreboard.html
index 46599ee..27c3e75 100644
--- a/public/scoreboard.html
+++ b/public/scoreboard.html
@@ -43,6 +43,7 @@ main{display:grid;grid-template-columns:repeat(auto-fit,minmax(360px,1fr));gap:2
<a href="/issue">public issue</a>
<a href="/scoreboard.html" style="color:var(--metal);border-bottom:1px solid var(--metal)">scoreboard</a>
<a href="/buildings.html">buildings</a>
+ <a href="/api/magazine.csv" download style="color:var(--metal-glow);border:1px solid var(--rule);padding:3px 9px">⬇ CSV</a>
</nav>
<main id="boards"><div class="empty">Loading…</div></main>
diff --git a/src/server/index.ts b/src/server/index.ts
index 36e68e3..a19b023 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -71,7 +71,7 @@ const ADMIN_PATHS = [
/^\/api\/data-freshness\/?$/i,
/^\/api\/stale-pitches\/?$/i,
/^\/magazine(\.html)?\/?$/i,
- /^\/api\/magazine(\/.*)?$/i,
+ /^\/api\/magazine(\.csv|\/.*)?$/i,
/^\/magazine\/\d+\/?$/i,
/^\/share\/\d+\/?$/i,
/^\/issue(\/[\w-]+)?\/?$/i,
@@ -1818,6 +1818,37 @@ app.get('/api/magazine/random', async (req, res) => {
}
});
+// Magazine CSV export — every published feature for accountant/CRM/spreadsheet hand-off
+app.get('/api/magazine.csv', async (_req, res) => {
+ try {
+ const r = await query(`
+ SELECT mf.id, mf.headline, mf.subhead, mf.category_tag, mf.status, mf.model, mf.views,
+ to_char(mf.generated_at AT TIME ZONE 'America/Los_Angeles', 'YYYY-MM-DD HH24:MI') AS generated_pt,
+ to_char(mf.published_at AT TIME ZONE 'America/Los_Angeles', 'YYYY-MM-DD HH24:MI') AS published_pt,
+ length(mf.editorial) AS editorial_len,
+ b.name AS biz_name, b.address AS biz_address, b.city, b.zip,
+ COALESCE((be.ad_signals->>'paid_ads_count')::int, 0) AS paid_ads_count
+ FROM magazine_features mf
+ JOIN businesses b ON b.id = mf.business_id
+ LEFT JOIN business_enrichment be ON be.business_id = mf.business_id
+ ORDER BY mf.id ASC
+ `);
+ const headers = ['id','headline','subhead','category_tag','status','model','views','generated_pt','published_pt','editorial_len','biz_name','biz_address','city','zip','paid_ads_count'];
+ const rows = r.rows.map((row: any) => headers.map(h => {
+ const v = row[h];
+ if (v === null || v === undefined) return '';
+ const s = String(v);
+ return /[",\n]/.test(s) ? `"${s.replace(/"/g, '""')}"` : s;
+ }).join(','));
+ const csv = [headers.join(','), ...rows].join('\n');
+ res.setHeader('Content-Type', 'text/csv; charset=utf-8');
+ res.setHeader('Content-Disposition', `attachment; filename="the-corridor-${new Date().toISOString().slice(0,10)}.csv"`);
+ res.send(csv);
+ } catch (e: any) {
+ res.status(500).json({ error: e.message });
+ }
+});
+
app.get('/api/magazine/scoreboard', async (_req, res) => {
try {
const [topViewed, hotVerticals, latest, longest, sponsors] = await Promise.all([
@@ -2574,6 +2605,19 @@ app.get('/magazine/:id', async (req, res) => {
LIMIT 3`,
[f.category_tag, id]
);
+ // Pull building-mates: other businesses in the same canonical building
+ const bldg = (f.biz_address || '').replace(/\s*(SUITE|STE|UNIT|#).*$/i, '').trim();
+ const mates = bldg ? await query(
+ `SELECT mf2.id, mf2.headline, b2.name AS biz_name, bc.suite
+ FROM v_building_canonical bc
+ LEFT JOIN magazine_features mf2 ON mf2.business_id = bc.id
+ JOIN businesses b2 ON b2.id = bc.id
+ WHERE bc.bldg_address = $1
+ AND bc.id <> $2
+ ORDER BY mf2.id IS NULL ASC, bc.suite NULLS LAST, b2.name
+ LIMIT 8`,
+ [bldg, f.business_id]
+ ) : { rows: [] as any[] };
const esc = (s: any) => String(s ?? '').replace(/[&<>"']/g, c => ({'&':'&','<':'<','>':'>','"':'"',"'":'''}[c]!));
res.setHeader('Content-Type', 'text/html; charset=utf-8');
res.send(`<!doctype html><html lang="en"><head><meta charset="utf-8">
@@ -2636,7 +2680,19 @@ footer{margin-top:48px;padding:24px 0;text-align:center;font-size:10px;letter-sp
<dt>Generated</dt><dd class="mono">${new Date(f.generated_at).toLocaleString('en-US', { month:'short', day:'numeric', year:'numeric'})} via ${esc(f.model)}</dd>
<dt>Views</dt><dd class="mono">${f.views || 0}</dd>
</dl>
- ${rel.rows.length > 0 ? `<section style="margin-top:64px;padding-top:32px;border-top:1px solid var(--rule)">
+ ${mates.rows.length > 0 ? `<section style="margin-top:48px;padding-top:24px;border-top:1px solid var(--rule)">
+ <div style="font-size:9px;letter-spacing:.32em;text-transform:uppercase;color:var(--ink-mute);margin-bottom:14px">In the same building · ${esc(bldg)}</div>
+ <div style="display:flex;flex-direction:column;gap:6px">
+ ${mates.rows.map((m: any) => m.id ? `<a href="/magazine/${m.id}" style="color:var(--ink);text-decoration:none;display:flex;justify-content:space-between;align-items:baseline;padding:6px 0;border-bottom:1px dotted var(--rule)">
+ <span style="font-family:'Cormorant Garamond',serif;font-style:italic;font-size:16px">${esc(m.headline || m.biz_name)}</span>
+ <span style="font-family:'Inter',sans-serif;font-size:10px;letter-spacing:.18em;color:var(--ink-mute);text-transform:uppercase">${m.suite ? '#' + esc(m.suite) : esc(m.biz_name)}</span>
+ </a>` : `<div style="color:var(--ink-mute);font-style:italic;font-size:13px;padding:6px 0;border-bottom:1px dotted var(--rule);display:flex;justify-content:space-between">
+ <span>${esc(m.biz_name)}</span>
+ <span style="font-family:'Inter',sans-serif;font-size:10px;letter-spacing:.18em">${m.suite ? '#' + esc(m.suite) : ''} · not yet featured</span>
+ </div>`).join('')}
+ </div>
+ </section>` : ''}
+ ${rel.rows.length > 0 ? `<section style="margin-top:48px;padding-top:24px;border-top:1px solid var(--rule)">
<div style="font-size:9px;letter-spacing:.32em;text-transform:uppercase;color:var(--ink-mute);margin-bottom:16px">More in ${esc(f.category_tag || 'this category')}</div>
<div style="display:grid;grid-template-columns:repeat(3,1fr);gap:18px">
${rel.rows.map((r: any) => `<a href="/magazine/${r.id}" style="display:block;padding:14px;border:1px solid var(--rule);text-decoration:none;color:var(--ink)">
← a45aad1 fix: move RSS route ahead of /issue/:cat handler — Express w
·
back to Ventura Corridor
·
iter 111+112: /api/magazine/:id/embed.html iframe-friendly m 97d5a13 →