← back to Ventura Corridor
YOLO tick 6: qwen3 fills tier marketing_copy + /admin/upgrade-leads board
cf9f1800a9f5159655e920b079af83cee116fbc1 · 2026-05-12 14:23:52 -0700 · SteveStudio2
scripts/fill-tier-copy.cjs — backfills marketing_copy JSONB on
tier_pricing rows where it's NULL. Per-row qwen3:14b generation:
- System prompt frames qwen3 as a Ventura Corridor copywriter with
tone constraints (confident, friendly, no buzzwords, no AI talk)
- TIER_HINTS guide what features each tier-level (basic/featured/
verified/spotlight) typically unlocks so copy stays consistent
- Strict-JSON output: {title, subtitle, bullets[3-6], cta}
- Idempotent — only fills NULL rows, re-run safe
Filled all 24 NULL rows in 3:14 (avg ~8s/row). Sample outputs:
- Hotel Spotlight : 'Stand out with video, priority placement, and
exclusive booking tools to drive more guests.'
- Cafe Featured : 'Showcase your cafe with a logo, photos, and a
special badge that makes you stand out in searches.'
- Salon Spotlight : 'Get featured with video, bookings, and priority
placement to stand out on Ventura Boulevard.'
The /upgrade/:bizId page now hydrates with category-specific bullets
instead of the generic TIER_COPY fallback strings.
NEW admin pages, both behind the existing Basic-auth gate (ADMIN_PATHS
regex already protects /signals, /pitches, /linkedin — added matching
/admin/upgrade-leads + /api/admin/upgrade-leads):
GET /api/admin/upgrade-leads — JSON list of upgrade_intents joined
with businesses + tier_pricing. Last 500.
GET /admin/upgrade-leads — SSR HTML board:
- Stats: total intents · pipeline MRR if all convert · average ticket
- Table: when · business (link to /upgrade/:id) · requested tier badge
· contact name + mailto: link · notes excerpt · tier price right-aligned
- Empty state: explains how leads land here
Smoke tests:
- /admin/upgrade-leads without auth → 401
- Marketing copy verified populated for all 24 tier rows
- /api/upgrade/3 (Shanghai Rose) returns all 3 tiers with filled copy
Reversible: UPDATE tier_pricing SET marketing_copy = NULL rolls back
the copy fill. Routes can be unmounted by removing the regex from
ADMIN_PATHS + the handlers.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Files touched
A scripts/fill-tier-copy.cjsM src/server/index.ts
Diff
commit cf9f1800a9f5159655e920b079af83cee116fbc1
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date: Tue May 12 14:23:52 2026 -0700
YOLO tick 6: qwen3 fills tier marketing_copy + /admin/upgrade-leads board
scripts/fill-tier-copy.cjs — backfills marketing_copy JSONB on
tier_pricing rows where it's NULL. Per-row qwen3:14b generation:
- System prompt frames qwen3 as a Ventura Corridor copywriter with
tone constraints (confident, friendly, no buzzwords, no AI talk)
- TIER_HINTS guide what features each tier-level (basic/featured/
verified/spotlight) typically unlocks so copy stays consistent
- Strict-JSON output: {title, subtitle, bullets[3-6], cta}
- Idempotent — only fills NULL rows, re-run safe
Filled all 24 NULL rows in 3:14 (avg ~8s/row). Sample outputs:
- Hotel Spotlight : 'Stand out with video, priority placement, and
exclusive booking tools to drive more guests.'
- Cafe Featured : 'Showcase your cafe with a logo, photos, and a
special badge that makes you stand out in searches.'
- Salon Spotlight : 'Get featured with video, bookings, and priority
placement to stand out on Ventura Boulevard.'
The /upgrade/:bizId page now hydrates with category-specific bullets
instead of the generic TIER_COPY fallback strings.
NEW admin pages, both behind the existing Basic-auth gate (ADMIN_PATHS
regex already protects /signals, /pitches, /linkedin — added matching
/admin/upgrade-leads + /api/admin/upgrade-leads):
GET /api/admin/upgrade-leads — JSON list of upgrade_intents joined
with businesses + tier_pricing. Last 500.
GET /admin/upgrade-leads — SSR HTML board:
- Stats: total intents · pipeline MRR if all convert · average ticket
- Table: when · business (link to /upgrade/:id) · requested tier badge
· contact name + mailto: link · notes excerpt · tier price right-aligned
- Empty state: explains how leads land here
Smoke tests:
- /admin/upgrade-leads without auth → 401
- Marketing copy verified populated for all 24 tier rows
- /api/upgrade/3 (Shanghai Rose) returns all 3 tiers with filled copy
Reversible: UPDATE tier_pricing SET marketing_copy = NULL rolls back
the copy fill. Routes can be unmounted by removing the regex from
ADMIN_PATHS + the handlers.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
---
scripts/fill-tier-copy.cjs | 81 ++++++++++++++++++++++++++++++++++++++++++++
src/server/index.ts | 84 ++++++++++++++++++++++++++++++++++++++++++++++
2 files changed, 165 insertions(+)
diff --git a/scripts/fill-tier-copy.cjs b/scripts/fill-tier-copy.cjs
new file mode 100644
index 0000000..e03d7f3
--- /dev/null
+++ b/scripts/fill-tier-copy.cjs
@@ -0,0 +1,81 @@
+#!/usr/bin/env node
+// fill-tier-copy.js — backfill marketing_copy JSONB on tier_pricing rows
+// where it's NULL. Uses local qwen3:14b. Per-row generation, idempotent
+// (only fills NULL — re-run safe).
+
+const { execSync } = require('child_process');
+const DB = process.env.DB_NAME || 'ventura_corridor';
+const PSQL = (process.platform === 'linux') ? `sudo -n -u postgres psql ${DB} -At -F"|" -q` : `psql ${DB} -At -F"|" -q`;
+function psql(sql) { return execSync(PSQL, { input: sql, encoding: 'utf8', maxBuffer: 16 * 1024 * 1024 }).trim(); }
+
+async function qwen3(sysPrompt, userPrompt) {
+ const r = await fetch('http://127.0.0.1:11434/api/chat', {
+ method: 'POST', headers: { 'Content-Type': 'application/json' },
+ body: JSON.stringify({
+ model: 'qwen3:14b',
+ stream: false, think: false, format: 'json',
+ options: { temperature: 0.65, num_predict: 400 },
+ messages: [
+ { role: 'system', content: '/no_think\n' + sysPrompt },
+ { role: 'user', content: userPrompt },
+ ],
+ }),
+ });
+ if (!r.ok) throw new Error('ollama http ' + r.status);
+ const j = await r.json();
+ const raw = (j.message?.content || '').replace(/<think>[\s\S]*?<\/think>/g, '').trim();
+ try { return JSON.parse(raw); }
+ catch { const m = raw.match(/\{[\s\S]*\}/); return m ? JSON.parse(m[0]) : null; }
+}
+
+const SYS = `You are a copywriter for Ventura Corridor, a directory of businesses on Ventura Boulevard. You write tier marketing copy for business owners deciding whether to upgrade their listing. Tone: confident, friendly, sales-aware, no buzzwords, no AI talk.
+
+Output STRICT JSON only:
+{
+ "title": "<5-9 words — tier display name + price>",
+ "subtitle": "<one sentence — the value prop in plain English>",
+ "bullets": ["<concise feature 1>","<feature 2>","<feature 3>","<feature 4>","<feature 5>"],
+ "cta": "<2-4 word call to action — verb-led>"
+}
+
+Bullets must be 3-6 items. Each bullet is a real feature, not a vibe. Mention specific things (logo, photos, video, booking, badge, priority sort, lead form, weekly digest, reservations link, etc.) appropriate to the tier level and the business category.`;
+
+const TIER_HINTS = {
+ basic: { intent: 'free baseline — what every listing gets', features: 'OSM-sourced name + address + phone + hours; standard sort; no logo' },
+ featured: { intent: 'mid-tier — visual upgrade + boost', features: 'logo + photos + boosted in search/browse + a badge + category tags' },
+ verified: { intent: 'mid-tier — credentials + intake', features: 'verification badge + credentials displayed + lead/intake form + headshot' },
+ spotlight:{ intent: 'top-tier — video + priority + niche extras', features: 'video embed + reservations/booking link + priority placement + weekly digest + promo slot' },
+};
+
+async function main() {
+ const rows = psql(`SELECT category||'|'||tier||'|'||display_name||'|'||price_cents FROM tier_pricing WHERE marketing_copy IS NULL ORDER BY category, price_cents`).split('\n').filter(Boolean);
+ console.log(`📝 Filling marketing_copy for ${rows.length} tier rows · qwen3:14b`);
+ let ok = 0, fail = 0;
+ const t0 = Date.now();
+ for (const line of rows) {
+ const [category, tier, display, priceCents] = line.split('|');
+ const price = parseInt(priceCents, 10);
+ const hint = TIER_HINTS[tier] || TIER_HINTS.featured;
+ const user = `Category: ${category} (OSM-style)
+Tier: ${tier}
+Display name: ${display}
+Price: ${price === 0 ? 'FREE' : '$' + (price/100).toFixed(0) + '/month'}
+Tier role: ${hint.intent}
+Typical features at this tier: ${hint.features}
+
+Generate JSON.`;
+ try {
+ const copy = await qwen3(SYS, user);
+ if (!copy || !copy.bullets || !Array.isArray(copy.bullets)) { fail++; process.stdout.write('✗'); continue; }
+ const json = JSON.stringify(copy).replace(/'/g, "''");
+ psql(`UPDATE tier_pricing SET marketing_copy = '${json}'::jsonb WHERE category = '${category.replace(/'/g, "''")}' AND tier = '${tier}'`);
+ ok++;
+ process.stdout.write('.');
+ } catch (e) {
+ fail++; process.stdout.write('✗');
+ console.error(`\n · err ${category}/${tier}:`, e.message);
+ }
+ }
+ console.log(`\nDone: ${ok} filled · ${fail} failed · ${((Date.now()-t0)/1000).toFixed(1)}s`);
+}
+main().catch(e => { console.error(e); process.exit(1); });
diff --git a/src/server/index.ts b/src/server/index.ts
index 91d3ba5..3da04fd 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -77,6 +77,8 @@ const ADMIN_PATHS = [
/^\/api\/snapshots\/?$/i,
/^\/api\/activity\/?$/i,
/^\/api\/data-freshness\/?$/i,
+ /^\/admin\/upgrade-leads\/?$/i,
+ /^\/api\/admin\/upgrade-leads\/?$/i,
/^\/api\/stale-pitches\/?$/i,
/^\/magazine(\.html)?\/?$/i,
/^\/api\/magazine(\.csv|\/.*)?$/i,
@@ -297,6 +299,88 @@ app.get('/upgrade/:bizId([0-9]+)', (req, res) => {
res.sendFile(path.join(here, '..', '..', 'public', 'upgrade.html'));
});
+// Admin: upgrade leads board. Already behind Basic-auth via ADMIN_PATHS.
+app.get('/api/admin/upgrade-leads', async (_req, res) => {
+ try {
+ const r = await query<any>(`
+ SELECT ui.id, ui.requested_tier, ui.contact_email, ui.contact_name, ui.notes,
+ ui.ip_addr, ui.created_at,
+ b.id AS business_id, b.name AS business_name, b.category, b.address, b.website,
+ tp.price_cents, tp.display_name AS tier_display
+ FROM upgrade_intents ui
+ LEFT JOIN businesses b ON b.id = ui.business_id
+ LEFT JOIN tier_pricing tp ON tp.category = b.category AND tp.tier = ui.requested_tier
+ ORDER BY ui.created_at DESC LIMIT 500
+ `);
+ res.json({ ok:true, items: r.rows });
+ } catch (e:any) { res.status(500).json({ ok:false, error: e.message }); }
+});
+app.get('/admin/upgrade-leads', async (_req, res) => {
+ try {
+ const r = await query<any>(`
+ SELECT ui.id, ui.requested_tier, ui.contact_email, ui.contact_name, ui.notes,
+ ui.created_at, b.id AS business_id, b.name AS business_name, b.category,
+ tp.price_cents, tp.display_name AS tier_display
+ FROM upgrade_intents ui
+ LEFT JOIN businesses b ON b.id = ui.business_id
+ LEFT JOIN tier_pricing tp ON tp.category = b.category AND tp.tier = ui.requested_tier
+ ORDER BY ui.created_at DESC LIMIT 500
+ `);
+ const total = r.rows.length;
+ const pipelineCents = r.rows.reduce((s: number, x: any) => s + (parseInt(x.price_cents,10)||0), 0);
+ const esc = (s: any) => String(s||'').replace(/[&<>"]/g, (c: string) => ({'&':'&','<':'<','>':'>','"':'"'} as any)[c]);
+ const fmt = (d: any) => d ? new Date(d).toISOString().slice(0,16).replace('T',' ') : '—';
+ const dollars = (cents: number) => cents === 0 ? 'Free' : '$' + (cents/100).toFixed(0);
+ res.type('html').send(`<!doctype html>
+<html><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
+<title>Upgrade Leads — Ventura Corridor</title>
+<link rel="preconnect" href="https://fonts.googleapis.com"><link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
+<link rel="stylesheet" href="https://fonts.googleapis.com/css2?family=Playfair+Display:wght@700&family=Inter:wght@400;500;600;700&display=swap">
+<style>
+:root{--bg:#0e0e0e;--panel:#181613;--fg:#f1ece2;--muted:#9a8f7e;--rule:rgba(255,255,255,0.10);--gold:#c9a96e;--green:#7ee69b}
+*{box-sizing:border-box}body,html{margin:0;padding:0;background:var(--bg);color:var(--fg);font-family:'Inter',sans-serif}
+header{padding:22px 28px;border-bottom:1px solid var(--rule);display:flex;justify-content:space-between;align-items:center}
+header h1{margin:0;font-family:'Playfair Display',serif;font-size:1.6em}header h1 small{display:block;color:var(--muted);font-family:'Inter',sans-serif;font-size:0.5em;letter-spacing:2px;text-transform:uppercase;margin-top:6px;font-weight:500}
+main{padding:28px;max-width:1240px;margin:0 auto}
+.stats{display:grid;grid-template-columns:1fr 1fr 1fr;gap:14px;margin-bottom:28px}
+.stat{background:var(--panel);border:1px solid var(--rule);border-radius:12px;padding:18px 22px}
+.stat .label{color:var(--muted);font-size:0.7em;letter-spacing:2px;text-transform:uppercase;font-weight:600;margin-bottom:6px}
+.stat .num{font-family:'Playfair Display',serif;font-size:1.85em;font-weight:700;color:var(--gold)}
+table{width:100%;border-collapse:collapse;font-size:0.9em;background:var(--panel);border:1px solid var(--rule);border-radius:10px;overflow:hidden}
+th{background:rgba(255,255,255,0.03);text-align:left;padding:11px 14px;color:var(--muted);font-weight:600;font-size:0.72em;letter-spacing:1.5px;text-transform:uppercase}
+td{padding:11px 14px;border-bottom:1px solid var(--rule)}tr:last-child td{border-bottom:0}tr:hover td{background:rgba(255,255,255,0.02)}
+.empty{padding:60px;text-align:center;color:var(--muted)}
+a{color:var(--gold);text-decoration:none}.tiny{font-size:0.78em;color:var(--muted)}
+.badge{display:inline-block;padding:3px 8px;border-radius:999px;font-size:0.7em;letter-spacing:1px;text-transform:uppercase;border:1px solid var(--rule);color:var(--muted)}
+.badge.gold{border-color:var(--gold);color:var(--gold)}
+.num{text-align:right;color:var(--green);font-weight:600}
+@media (max-width:780px){.stats{grid-template-columns:1fr}table{font-size:0.78em}th,td{padding:8px 10px}main{padding:18px 14px}}
+</style></head><body>
+<header><h1>Upgrade Leads <small>Captured intents from /upgrade/:bizId</small></h1><a class="tiny" href="/">← back</a></header>
+<main>
+<div class="stats">
+ <div class="stat"><div class="label">Total intents</div><div class="num">${total}</div></div>
+ <div class="stat"><div class="label">Pipeline (MRR if all convert)</div><div class="num">${dollars(pipelineCents)}/mo</div></div>
+ <div class="stat"><div class="label">Average ticket</div><div class="num">${total ? dollars(Math.round(pipelineCents/total)) : '—'}</div></div>
+</div>
+${r.rows.length === 0
+ ? `<div class="empty"><h2 style="font-family:'Playfair Display',serif;color:var(--fg);margin:0 0 8px">No leads yet</h2><p>Intents land here when a business owner clicks 'Notify me when this tier opens' on /upgrade/:bizId.</p></div>`
+ : `<table><thead><tr>
+ <th>When</th><th>Business</th><th>Requested tier</th><th>Contact</th><th>Notes</th><th>Price</th>
+ </tr></thead><tbody>
+ ${r.rows.map((row: any) => `<tr>
+ <td>${fmt(row.created_at)}</td>
+ <td><a href="/upgrade/${row.business_id}" target="_blank">${esc(row.business_name||'(unknown)')}</a><div class="tiny">${esc(row.category||'')}</div></td>
+ <td><span class="badge gold">${esc(row.tier_display || row.requested_tier)}</span></td>
+ <td>${esc(row.contact_name || '')}<div class="tiny"><a href="mailto:${esc(row.contact_email)}">${esc(row.contact_email)}</a></div></td>
+ <td class="tiny">${esc(row.notes||'—').slice(0,80)}</td>
+ <td class="num">${dollars(row.price_cents||0)}</td>
+ </tr>`).join('')}
+ </tbody></table>`}
+</main></body></html>`);
+ } catch (e:any) { res.status(500).send('error: ' + e.message); }
+});
+
// ─── Blvd Gallery — virtual art walk along Ventura Blvd ──────────────
// One imagined piece of art per business, generated overnight by qwen3:14b.
// East (Encino, position_pct=0) → West (Studio City, position_pct=100).
← e0407a5 YOLO tick 5: /upgrade/:bizId tier ladder page + intent captu
·
back to Ventura Corridor
·
ventura-corridor: ship 'POSSIBLY NO BUSINESS LICENSE' badge e1392fb →