← back to Ventura Corridor
scripts/build-virtual-gallery.sh
111 lines
#!/bin/bash
# build-virtual-gallery.sh — autonomous 3am build for The Blvd Gallery.
# Per Steve's overnight rules: local Ollama only (qwen3:14b), no Claude API,
# no email/DNS/destructive ops, every step reversible.
#
# What it does:
# 1. Apply 002_gallery_tiers.sql migration (idempotent — IF NOT EXISTS)
# 2. Seed tier_pricing table with consensus prices (idempotent — ON CONFLICT)
# 3. Generate art metadata via qwen3:14b for top 2000 businesses by category
# priority (restaurants > salons > hotels > cafes > law firms > clinics
# > shops > rest). Batched, one INSERT per business. Each is independent
# and idempotent (UNIQUE on business_id).
# 4. Write a run log to logs/build-virtual-gallery-<date>.log
# 5. Final stats line written to logs/build-virtual-gallery-summary.txt
#
# Reversible: every INSERT is keyed on business_id. To roll back:
# psql -d ventura_corridor -c "TRUNCATE gallery_pieces RESTART IDENTITY;"
set -euo pipefail
cd "$(dirname "$0")/.."
ROOT="$(pwd)"
LOG_DIR="$ROOT/logs"
mkdir -p "$LOG_DIR"
DATE=$(date +%Y-%m-%d_%H%M)
LOG="$LOG_DIR/build-virtual-gallery-$DATE.log"
exec > >(tee -a "$LOG") 2>&1
echo "════════════════════════════════════════════════════════════════"
echo " The Blvd Gallery · autonomous build"
echo " start: $(date -u +%Y-%m-%dT%H:%M:%SZ)"
echo "════════════════════════════════════════════════════════════════"
# ── 1. Schema migration ─────────────────────────────────────────────
echo
echo "── 1. Applying schema migration ──"
psql -d ventura_corridor -f db/002_gallery_tiers.sql | tail -20
# ── 2. Seed tier_pricing ────────────────────────────────────────────
echo
echo "── 2. Seeding tier_pricing ──"
psql -d ventura_corridor <<'SQL'
INSERT INTO tier_pricing (category, tier, display_name, price_cents, marketing_copy) VALUES
-- Restaurants: Free / Featured $19 / Chef-Spotlight $59
('amenity: restaurant', 'basic', 'Basic listing', 0,
'{"title":"Free listing","subtitle":"OSM-sourced name, address, phone, hours","bullets":["Always free","Auto-updated from OpenStreetMap"],"cta":"Already live"}'),
('amenity: restaurant', 'featured', 'Featured Restaurant', 1900,
'{"title":"Featured · $19/mo","subtitle":"Logo + 5 photos + boosted sort","bullets":["Custom logo","Up to 5 dining-room photos","Boosted in browse + search","Cuisine + dietary tags","Featured ribbon"],"cta":"Light up the listing"}'),
('amenity: restaurant', 'spotlight', 'Chef-Spotlight', 5900,
'{"title":"Chef-Spotlight · $59/mo","subtitle":"Chef bio + video + featured dish","bullets":["Everything in Featured","Chef bio + headshot","60-second chef video","Featured-dish callout w/ price","Weekly nearby-user email digest","Priority placement"],"cta":"Be the corridor''s named chef"}'),
-- Law firms: Free / Verified $29 / Lead-Magnet $79
('office: lawyer', 'basic', 'Basic listing', 0,
'{"title":"Free listing","subtitle":"Name, address, phone","bullets":["Always free"],"cta":"Already live"}'),
('office: lawyer', 'verified', 'Practice-Verified', 2900,
'{"title":"Practice-Verified · $29/mo","subtitle":"5 practice areas + State Bar # + Verified badge","bullets":["Up to 5 practice areas with descriptions","State Bar number displayed","Consultation request form","Attorney headshot","Verified badge"],"cta":"Get verified"}'),
('office: lawyer', 'spotlight', 'Lead-Magnet', 7900,
'{"title":"Lead-Magnet · $79/mo","subtitle":"Intake form + priority placement","bullets":["Everything in Verified","AI-powered intake form (case pre-screening)","Weekly lead digest delivered","Priority placement in browse + search","Map-pin highlight"],"cta":"Open the lead pipe"}'),
-- Salons / Hairdresser: Free / Gallery $15 / Book-Online $39
('shop: hairdresser', 'basic', 'Basic listing', 0, NULL),
('shop: hairdresser', 'featured', 'Gallery · $15/mo', 1500, NULL),
('shop: hairdresser', 'spotlight', 'Book-Online · $39/mo', 3900, NULL),
('shop: beauty', 'basic', 'Basic listing', 0, NULL),
('shop: beauty', 'featured', 'Gallery · $15/mo', 1500, NULL),
('shop: beauty', 'spotlight', 'Book-Online · $39/mo', 3900, NULL),
-- Cafes: Free / Featured $15 / Local-Favorite $39
('amenity: cafe', 'basic', 'Basic', 0, NULL),
('amenity: cafe', 'featured', 'Featured · $15/mo', 1500, NULL),
('amenity: cafe', 'spotlight', 'Local-Favorite · $39/mo', 3900, NULL),
-- Hotels: Free / Featured $39 / Concierge $99
('tourism: hotel', 'basic', 'Basic', 0, NULL),
('tourism: hotel', 'featured', 'Featured · $39/mo', 3900, NULL),
('tourism: hotel', 'spotlight', 'Concierge · $99/mo', 9900, NULL),
-- Dentists/clinics: Free / Verified $29 / Premium $79
('amenity: dentist', 'basic', 'Basic', 0, NULL),
('amenity: dentist', 'featured', 'Verified · $29/mo', 2900, NULL),
('amenity: dentist', 'spotlight', 'Premium · $79/mo', 7900, NULL),
('amenity: clinic', 'basic', 'Basic', 0, NULL),
('amenity: clinic', 'featured', 'Verified · $29/mo', 2900, NULL),
('amenity: clinic', 'spotlight', 'Premium · $79/mo', 7900, NULL)
ON CONFLICT (category, tier) DO NOTHING;
SQL
echo "tier_pricing seeded — $(psql -d ventura_corridor -tA -c 'SELECT COUNT(*) FROM tier_pricing') rows"
# ── 3. Generate gallery pieces via qwen3:14b ────────────────────────
echo
echo "── 3. Generating gallery_pieces via local qwen3:14b ──"
node scripts/generate-gallery-pieces.js --limit 2000 2>&1
# ── 4. Final summary ────────────────────────────────────────────────
echo
echo "── Summary ──"
SUMMARY=$(psql -d ventura_corridor -tA <<'SQL'
SELECT 'gallery_pieces: ' || COUNT(*) FROM gallery_pieces;
SELECT 'tier_pricing: ' || COUNT(*) FROM tier_pricing;
SELECT 'businesses (tier!=basic): ' || COUNT(*) FROM businesses WHERE tier != 'basic';
SQL
)
echo "$SUMMARY"
echo "$SUMMARY" > "$LOG_DIR/build-virtual-gallery-summary.txt"
echo
echo "════════════════════════════════════════════════════════════════"
echo " Build complete: $(date -u +%Y-%m-%dT%H:%M:%SZ)"
echo " Log: $LOG"
echo "════════════════════════════════════════════════════════════════"