← back to Ventura Corridor
fix: rebuild ventura_corridor schema — backfill ledger 003-009, fix migration 010 (add outreach_channel + dw_proximity), add migration 017 for magazine_issues/reader_feedback/sponsor_inquiries/coverage_snapshots, fix migrate.ts to actually record schema_migrations
e145ce498290f724d082fe9f015e9e36ffd4c9f7 · 2026-05-07 08:34:56 -0700 · SteveStudio2
Files touched
M db/migrate.tsM db/migrations/010_response_tracking.sqlA db/migrations/017_magazine_addons.sqlM src/jobs/generate_features.tsM src/server/index.ts
Diff
commit e145ce498290f724d082fe9f015e9e36ffd4c9f7
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date: Thu May 7 08:34:56 2026 -0700
fix: rebuild ventura_corridor schema — backfill ledger 003-009, fix migration 010 (add outreach_channel + dw_proximity), add migration 017 for magazine_issues/reader_feedback/sponsor_inquiries/coverage_snapshots, fix migrate.ts to actually record schema_migrations
---
db/migrate.ts | 4 ++
db/migrations/010_response_tracking.sql | 6 +++
db/migrations/017_magazine_addons.sql | 74 +++++++++++++++++++++++++++++++++
src/jobs/generate_features.ts | 20 +++++----
src/server/index.ts | 20 +++++----
5 files changed, 106 insertions(+), 18 deletions(-)
diff --git a/db/migrate.ts b/db/migrate.ts
index f1de83e..7c55ffc 100644
--- a/db/migrate.ts
+++ b/db/migrate.ts
@@ -37,9 +37,13 @@ async function main() {
const sql = readFileSync(join(migrationsDir, f), 'utf8');
console.log(`[apply] ${f}`);
try {
+ await pool.query('BEGIN');
await pool.query(sql);
+ await pool.query(`INSERT INTO schema_migrations (filename) VALUES ($1) ON CONFLICT DO NOTHING`, [f]);
+ await pool.query('COMMIT');
console.log(`[done] ${f}`);
} catch (e: any) {
+ try { await pool.query('ROLLBACK'); } catch {}
console.error(`[fail] ${f}: ${e.message}`);
process.exit(1);
}
diff --git a/db/migrations/010_response_tracking.sql b/db/migrations/010_response_tracking.sql
index 8d13b4b..0b684b9 100644
--- a/db/migrations/010_response_tracking.sql
+++ b/db/migrations/010_response_tracking.sql
@@ -5,6 +5,12 @@
-- + structured loss reasons + scheduled follow-up.
ALTER TABLE pitches
+ ADD COLUMN IF NOT EXISTS outreach_channel TEXT,
+ ADD COLUMN IF NOT EXISTS dw_proximity TEXT,
+ ADD COLUMN IF NOT EXISTS contact_name TEXT,
+ ADD COLUMN IF NOT EXISTS email TEXT,
+ ADD COLUMN IF NOT EXISTS phone TEXT,
+ ADD COLUMN IF NOT EXISTS linkedin TEXT,
ADD COLUMN IF NOT EXISTS reply_text TEXT,
ADD COLUMN IF NOT EXISTS reply_channel TEXT,
ADD COLUMN IF NOT EXISTS won_value_usd NUMERIC(10,2),
diff --git a/db/migrations/017_magazine_addons.sql b/db/migrations/017_magazine_addons.sql
new file mode 100644
index 0000000..bf68606
--- /dev/null
+++ b/db/migrations/017_magazine_addons.sql
@@ -0,0 +1,74 @@
+-- Migration 017 — magazine layer add-ons created ad-hoc during the
+-- 2026-05-06 overnight loop session: monthly cover picker, reader feedback
+-- intake, sponsor inquiries persistence, coverage snapshot history.
+
+-- Issue cover picker (iter 138 + 144 image upload)
+CREATE TABLE IF NOT EXISTS magazine_issues (
+ issue_month TEXT PRIMARY KEY, -- 'YYYY-MM'
+ cover_feature_id BIGINT REFERENCES magazine_features(id) ON DELETE SET NULL,
+ cover_caption TEXT,
+ cover_kicker TEXT,
+ cover_image_path TEXT, -- filename in data/covers/
+ cover_set_at TIMESTAMPTZ,
+ published_at TIMESTAMPTZ,
+ notes TEXT
+);
+
+-- Reader feedback intake (iter 130 + 143 George email)
+CREATE TABLE IF NOT EXISTS reader_feedback (
+ id BIGSERIAL PRIMARY KEY,
+ feature_id BIGINT, -- nullable; comments on whole issue ok
+ kind TEXT NOT NULL, -- typo|correction|tip|complaint|praise|note
+ body TEXT NOT NULL,
+ contact TEXT, -- optional reader email
+ source_path TEXT, -- where the form was submitted from
+ received_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ reviewed BOOLEAN NOT NULL DEFAULT FALSE
+);
+CREATE INDEX IF NOT EXISTS idx_reader_feedback_received_at ON reader_feedback (received_at DESC);
+CREATE INDEX IF NOT EXISTS idx_reader_feedback_unreviewed ON reader_feedback (received_at DESC) WHERE NOT reviewed;
+
+-- Sponsor inquiries persistence (paired with /sponsor.html, iter ~127)
+CREATE TABLE IF NOT EXISTS sponsor_inquiries (
+ id BIGSERIAL PRIMARY KEY,
+ feature_id BIGINT,
+ biz_name TEXT,
+ contact_name TEXT NOT NULL,
+ contact_email TEXT NOT NULL,
+ contact_phone TEXT,
+ tier TEXT, -- 'spread' | 'cover' | 'ask'
+ notes TEXT,
+ received_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ status TEXT NOT NULL DEFAULT 'new', -- new | responded | closed | spam
+ email_message_id TEXT
+);
+CREATE INDEX IF NOT EXISTS idx_sponsor_inquiries_received_at ON sponsor_inquiries (received_at DESC);
+CREATE INDEX IF NOT EXISTS idx_sponsor_inquiries_status ON sponsor_inquiries (status, received_at DESC);
+
+-- Daily coverage snapshots (iter 158, populates /coverage.html growth line)
+CREATE TABLE IF NOT EXISTS coverage_snapshots (
+ id BIGSERIAL PRIMARY KEY,
+ taken_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ total_biz INTEGER NOT NULL,
+ biz_with_features INTEGER NOT NULL,
+ total_features INTEGER NOT NULL,
+ drafts INTEGER NOT NULL,
+ reviewed INTEGER NOT NULL,
+ published INTEGER NOT NULL,
+ spiked INTEGER NOT NULL,
+ feedback_total INTEGER NOT NULL DEFAULT 0,
+ feedback_unread INTEGER NOT NULL DEFAULT 0,
+ inquiries_total INTEGER NOT NULL DEFAULT 0,
+ inquiries_open INTEGER NOT NULL DEFAULT 0,
+ notes TEXT
+);
+CREATE INDEX IF NOT EXISTS idx_coverage_snapshots_taken_at ON coverage_snapshots (taken_at);
+
+-- Editor margin notes column on features (iter 149) — only add if not already there
+ALTER TABLE magazine_features
+ ADD COLUMN IF NOT EXISTS notes TEXT;
+
+-- Initial empty row for current month so the cover picker has a row to PATCH
+INSERT INTO magazine_issues (issue_month, cover_set_at)
+SELECT to_char(now(),'YYYY-MM'), now()
+ON CONFLICT (issue_month) DO NOTHING;
diff --git a/src/jobs/generate_features.ts b/src/jobs/generate_features.ts
index e04ea84..4bc0de5 100644
--- a/src/jobs/generate_features.ts
+++ b/src/jobs/generate_features.ts
@@ -22,15 +22,17 @@ const COUNT = parseInt(args.find(a => /^\d+$/.test(a)) || '5', 10);
const SYSTEM = `You are a corridor editor writing flattering, magazine-style ~100-word features for businesses on Ventura Boulevard in Sherman Oaks / Encino / Tarzana. Your tone is warm but never gushing — think Conde Nast Traveler shopping guide.
-HEADLINE STYLE — pick ONE of these voice patterns each time, vary across features:
- 1. Verb-led: "Threading Stories Through Linen", "Sharpening Knives Since 1979"
- 2. Specific-noun-first: "The Corner Booth at Anh-Anh", "A Chair in the Window at Mark Morgan"
- 3. Sensory detail: "Brass Hardware, Shaved Ice", "Marble Counters and Espresso Steam"
- 4. Contrast: "Mid-Century Lobby, Cellphone Repair Inside", "Quiet Office, Loud Reputation"
- 5. Time + place: "After Hours at Khanh Nguyen", "Mornings on the 11200 Block"
- 6. Question or fragment: "Why the Line Forms at Anh-Anh's", "Twelve Stools, No Reservations"
-
-NEVER use any of these stale phrasings: "X in Y's Heart", "X Meets Y", "Where X Meets Y", "X Rooted in Y", "Precision Care", "Legal Precision", "Legal Expertise", "Steady Counsel", "Timeless Elegance", "Vibrant Hub". If you find yourself reaching for one of these, stop and try a different opening.
+HEADLINE STYLE — pick ONE of these abstract voice patterns each time, but write a phrase UNIQUE to THIS business. Do NOT reuse any example below verbatim.
+ 1. Verb-led participle: an "-ing" verb + sensory or material noun specific to this business's actual trade
+ 2. Specific-noun-first: a concrete piece of the storefront ("counter", "doorway", "shelf", "stool", "case") + the business's own name or address
+ 3. Two-noun sensory pair: two short noun phrases joined by "and" or comma, each from this trade's vocabulary (a baker has "crumb" and "yeast"; a cobbler has "leather" and "wax")
+ 4. Contrast: an unexpected pairing of two things that ARE actually present at this address (e.g. an old building + new service inside it)
+ 5. Time + place: a specific moment of day or week + the business's address or block
+ 6. Fragment / question: a partial sentence or rhetorical fragment about something visible at the storefront
+
+NEVER reuse these stale phrasings: "X in Y's Heart", "X Meets Y", "Where X Meets Y", "X Rooted in Y", "Precision Care", "Legal Precision", "Legal Expertise", "Steady Counsel", "Timeless Elegance", "Vibrant Hub", "Quiet Office Loud Reputation", "Espresso Steam".
+
+The headline must reflect THIS specific business — its trade, its actual location, its real building. Do not reach for stock images.
Output a JSON object with EXACTLY these fields:
{
diff --git a/src/server/index.ts b/src/server/index.ts
index f651eab..b5e2de8 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -2646,15 +2646,17 @@ app.post('/api/magazine/:id/regen', express.json(), async (req, res) => {
const SYSTEM = `You are a corridor editor writing flattering, magazine-style ~100-word features for businesses on Ventura Boulevard in Sherman Oaks / Encino / Tarzana. Your tone is warm but never gushing — think Conde Nast Traveler shopping guide.
-HEADLINE STYLE — pick ONE of these voice patterns each time, vary across features:
- 1. Verb-led: "Threading Stories Through Linen", "Sharpening Knives Since 1979"
- 2. Specific-noun-first: "The Corner Booth at Anh-Anh", "A Chair in the Window at Mark Morgan"
- 3. Sensory detail: "Brass Hardware, Shaved Ice", "Marble Counters and Espresso Steam"
- 4. Contrast: "Mid-Century Lobby, Cellphone Repair Inside", "Quiet Office, Loud Reputation"
- 5. Time + place: "After Hours at Khanh Nguyen", "Mornings on the 11200 Block"
- 6. Question or fragment: "Why the Line Forms at Anh-Anh's", "Twelve Stools, No Reservations"
-
-NEVER use any of these stale phrasings: "X in Y's Heart", "X Meets Y", "Where X Meets Y", "X Rooted in Y", "Precision Care", "Legal Precision", "Legal Expertise", "Steady Counsel", "Timeless Elegance", "Vibrant Hub". If you find yourself reaching for one of these, stop and try a different opening.
+HEADLINE STYLE — pick ONE of these abstract voice patterns each time, but write a phrase UNIQUE to THIS business. Do NOT reuse any example below verbatim.
+ 1. Verb-led participle: an "-ing" verb + sensory or material noun specific to this business's actual trade
+ 2. Specific-noun-first: a concrete piece of the storefront ("counter", "doorway", "shelf", "stool", "case") + the business's own name or address
+ 3. Two-noun sensory pair: two short noun phrases joined by "and" or comma, each from this trade's vocabulary (a baker has "crumb" and "yeast"; a cobbler has "leather" and "wax")
+ 4. Contrast: an unexpected pairing of two things that ARE actually present at this address (e.g. an old building + new service inside it)
+ 5. Time + place: a specific moment of day or week + the business's address or block
+ 6. Fragment / question: a partial sentence or rhetorical fragment about something visible at the storefront
+
+NEVER reuse these stale phrasings: "X in Y's Heart", "X Meets Y", "Where X Meets Y", "X Rooted in Y", "Precision Care", "Legal Precision", "Legal Expertise", "Steady Counsel", "Timeless Elegance", "Vibrant Hub", "Quiet Office Loud Reputation", "Espresso Steam".
+
+The headline must reflect THIS specific business — its trade, its actual location, its real building. Do not reach for stock images.
Output a JSON object with EXACTLY these fields:
{
← d607f2b iter 177: SESSION_LOG addendum 2 — prompt-rewrite win confir
·
back to Ventura Corridor
·
feat: 9 AM daily corpus-summary email — totals + cover + top d817e36 →