← back to Animals
fix(community): debate-driven fixes for visibility, comments, signup
95e913a4d74c7cd5769913a05fa3b42ed30275ce · 2026-05-01 14:31:13 -0700 · Steve
5 defects converged on by claude+codex debate (animals-pawcircles-2026-05-01):
P1 functional — marketplace SELECT omitted visibility/app_user_id/visible_circles,
so the in-memory userCanSeeListingSync filter read undefined and hid every
non-public listing from logged-in users. Now selects the fields and
over-fetches 200 → filters → slices to 100.
P1 privacy — /circles/:slug returned matching-zip/state listings with no
visibility check at all, leaking scoped listings to anyone browsing a
circle. Now post-filtered through the same userCanSeeListingSync helper
used elsewhere (single source of truth for the gate).
P2 — POST /marketplace/:id/comment had no listing-access check; users could
comment on listings they couldn't see. Now loads the listing and runs the
visibility gate before insert.
P2 — auth.js signup: species_pets from a checkbox group is a string when one
box is checked, an array when several. Insert into TEXT[] failed for the
single-checkbox case. Normalize via [].concat() before insert.
P3 — admin_pitch.js: "All states" <option> had no value=""; form posted
state="All states" and SQL filter got junk. Added value="".
Smoke-tested signup with no/single/array pets (all 200, species_pets stored
as {}, {dog}, {dog,cat} respectively), and confirmed all routes still 200.
Files touched
A migrations/003_community_marketplace.sqlM package.jsonA public/css/community.cssA public/css/pitches.cssA src/lib/auth.jsA src/scripts/research_traffic.jsA src/scripts/weekly-refresh.shA src/server/admin_pitch.jsA src/server/community.jsA src/server/community_render.jsM src/server/index.js
Diff
commit 95e913a4d74c7cd5769913a05fa3b42ed30275ce
Author: Steve <steve@designerwallcoverings.com>
Date: Fri May 1 14:31:13 2026 -0700
fix(community): debate-driven fixes for visibility, comments, signup
5 defects converged on by claude+codex debate (animals-pawcircles-2026-05-01):
P1 functional — marketplace SELECT omitted visibility/app_user_id/visible_circles,
so the in-memory userCanSeeListingSync filter read undefined and hid every
non-public listing from logged-in users. Now selects the fields and
over-fetches 200 → filters → slices to 100.
P1 privacy — /circles/:slug returned matching-zip/state listings with no
visibility check at all, leaking scoped listings to anyone browsing a
circle. Now post-filtered through the same userCanSeeListingSync helper
used elsewhere (single source of truth for the gate).
P2 — POST /marketplace/:id/comment had no listing-access check; users could
comment on listings they couldn't see. Now loads the listing and runs the
visibility gate before insert.
P2 — auth.js signup: species_pets from a checkbox group is a string when one
box is checked, an array when several. Insert into TEXT[] failed for the
single-checkbox case. Normalize via [].concat() before insert.
P3 — admin_pitch.js: "All states" <option> had no value=""; form posted
state="All states" and SQL filter got junk. Added value="".
Smoke-tested signup with no/single/array pets (all 200, species_pets stored
as {}, {dog}, {dog,cat} respectively), and confirmed all routes still 200.
---
migrations/003_community_marketplace.sql | 205 +++++++++++++++++++++
package.json | 3 +-
public/css/community.css | 63 +++++++
public/css/pitches.css | 41 +++++
src/lib/auth.js | 177 ++++++++++++++++++
src/scripts/research_traffic.js | 201 +++++++++++++++++++++
src/scripts/weekly-refresh.sh | 100 +++++++++++
src/server/admin_pitch.js | 300 +++++++++++++++++++++++++++++++
src/server/community.js | 215 ++++++++++++++++++++++
src/server/community_render.js | 270 ++++++++++++++++++++++++++++
src/server/index.js | 9 +
11 files changed, 1583 insertions(+), 1 deletion(-)
diff --git a/migrations/003_community_marketplace.sql b/migrations/003_community_marketplace.sql
new file mode 100644
index 0000000..fbac305
--- /dev/null
+++ b/migrations/003_community_marketplace.sql
@@ -0,0 +1,205 @@
+-- Project: Animals — community + marketplace layer.
+--
+-- Adds the social-graph + marketplace tables that turn the directory into a
+-- user-generated platform (sub-brand: "PawCircles" inside AnimalsDirectory).
+--
+-- Design tenets:
+-- - ZIP is the primary unit of community. Everyone sees their own ZIP by
+-- default. Cross-ZIP visibility is opt-in via `circle_memberships`.
+-- - No copy-paste from existing players (Nextdoor, Facebook Groups). The
+-- vocabulary here is generic: "circle", "block", "post", "listing".
+-- - Listings have a small finite type set so they can be priced + searched.
+-- - Soft-delete (status='removed') instead of hard-delete; we need to keep
+-- evidence of policy violations.
+
+BEGIN;
+
+-- Extend app_users with the social fields we need (already exists from migration 001).
+ALTER TABLE app_users
+ ADD COLUMN IF NOT EXISTS handle TEXT UNIQUE,
+ ADD COLUMN IF NOT EXISTS home_zip TEXT,
+ ADD COLUMN IF NOT EXISTS home_city TEXT,
+ ADD COLUMN IF NOT EXISTS home_state TEXT,
+ ADD COLUMN IF NOT EXISTS home_lat NUMERIC(10,7),
+ ADD COLUMN IF NOT EXISTS home_lng NUMERIC(10,7),
+ ADD COLUMN IF NOT EXISTS species_pets TEXT[], -- ['dog','cat']
+ ADD COLUMN IF NOT EXISTS visibility_default TEXT NOT NULL DEFAULT 'home_zip'
+ CHECK (visibility_default IN ('home_zip','home_zip_25mi','statewide','approved_circles_only','public')),
+ ADD COLUMN IF NOT EXISTS approved_circles BIGINT[] NOT NULL DEFAULT '{}',
+ ADD COLUMN IF NOT EXISTS approved_zips TEXT[] NOT NULL DEFAULT '{}',
+ ADD COLUMN IF NOT EXISTS marketing_opt_in BOOLEAN NOT NULL DEFAULT FALSE,
+ ADD COLUMN IF NOT EXISTS suspended_at TIMESTAMPTZ,
+ ADD COLUMN IF NOT EXISTS suspension_reason TEXT;
+
+CREATE INDEX IF NOT EXISTS idx_app_users_zip ON app_users(home_zip);
+CREATE INDEX IF NOT EXISTS idx_app_users_state ON app_users(home_state);
+
+-- ── Sessions (simple opaque cookie token, bcrypt-validated on signup/login) ─
+
+CREATE TABLE IF NOT EXISTS app_sessions (
+ token TEXT PRIMARY KEY,
+ app_user_id BIGINT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ ip TEXT,
+ user_agent TEXT,
+ expires_at TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '90 days'
+);
+CREATE INDEX IF NOT EXISTS idx_app_sessions_user ON app_sessions(app_user_id);
+
+-- ── Circles (ZIP-based by default, can be user-curated topical too) ────────
+
+CREATE TABLE IF NOT EXISTS circles (
+ id BIGSERIAL PRIMARY KEY,
+ kind TEXT NOT NULL CHECK (kind IN ('zip','city','county','topic','breed')),
+ slug TEXT NOT NULL UNIQUE, -- 'zip-90025' / 'city-los-angeles-ca' / 'topic-puppy-training'
+ name TEXT NOT NULL,
+ description TEXT,
+ zip TEXT, -- populated when kind='zip'
+ city TEXT,
+ state TEXT,
+ breed_id BIGINT REFERENCES breeds(id) ON DELETE SET NULL,
+ member_count INTEGER NOT NULL DEFAULT 0,
+ is_official BOOLEAN NOT NULL DEFAULT FALSE, -- auto-created vs user-curated
+ created_by BIGINT REFERENCES app_users(id) ON DELETE SET NULL,
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+CREATE INDEX IF NOT EXISTS idx_circles_kind ON circles(kind);
+CREATE INDEX IF NOT EXISTS idx_circles_zip ON circles(zip);
+
+CREATE TABLE IF NOT EXISTS circle_memberships (
+ circle_id BIGINT NOT NULL REFERENCES circles(id) ON DELETE CASCADE,
+ app_user_id BIGINT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
+ role TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('member','moderator','owner')),
+ joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ PRIMARY KEY (circle_id, app_user_id)
+);
+CREATE INDEX IF NOT EXISTS idx_memberships_user ON circle_memberships(app_user_id);
+
+-- ── Marketplace listings (the post engine) ─────────────────────────────────
+-- Listing types are tightly enumerated so we can price + filter precisely.
+
+CREATE TABLE IF NOT EXISTS marketplace_listings (
+ id BIGSERIAL PRIMARY KEY,
+ app_user_id BIGINT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
+ listing_type TEXT NOT NULL CHECK (listing_type IN (
+ 'lost_pet','found_pet','adoption','for_sale','wanted',
+ 'service_offered','service_wanted','event','recommendation','question',
+ 'business_promo','free_to_good_home'
+ )),
+ title TEXT NOT NULL,
+ body TEXT,
+ zip TEXT NOT NULL, -- always tied to a ZIP for radius search
+ city TEXT,
+ state TEXT,
+ latitude NUMERIC(10,7),
+ longitude NUMERIC(10,7),
+ visibility TEXT NOT NULL DEFAULT 'home_zip_25mi'
+ CHECK (visibility IN ('home_zip','home_zip_25mi','statewide','approved_circles_only','public')),
+ visible_circles BIGINT[] NOT NULL DEFAULT '{}',
+ price_cents INTEGER,
+ species TEXT, -- 'dog' | 'cat' | 'bird' | etc.
+ breed_id BIGINT REFERENCES breeds(id) ON DELETE SET NULL,
+ contact_email TEXT,
+ contact_phone TEXT,
+ photo_urls TEXT[],
+ status TEXT NOT NULL DEFAULT 'active'
+ CHECK (status IN ('draft','active','expired','closed','removed','flagged')),
+ view_count INTEGER NOT NULL DEFAULT 0,
+ reaction_count INTEGER NOT NULL DEFAULT 0,
+ comment_count INTEGER NOT NULL DEFAULT 0,
+ bumped_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ expires_at TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '30 days',
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+CREATE INDEX IF NOT EXISTS idx_listings_zip ON marketplace_listings(zip);
+CREATE INDEX IF NOT EXISTS idx_listings_state ON marketplace_listings(state);
+CREATE INDEX IF NOT EXISTS idx_listings_type ON marketplace_listings(listing_type);
+CREATE INDEX IF NOT EXISTS idx_listings_status ON marketplace_listings(status);
+CREATE INDEX IF NOT EXISTS idx_listings_bumped ON marketplace_listings(bumped_at DESC);
+CREATE INDEX IF NOT EXISTS idx_listings_user ON marketplace_listings(app_user_id);
+
+CREATE OR REPLACE FUNCTION bump_marketplace_updated() RETURNS TRIGGER AS $$
+BEGIN NEW.updated_at = NOW(); RETURN NEW; END;
+$$ LANGUAGE plpgsql;
+CREATE TRIGGER trg_listings_updated BEFORE UPDATE ON marketplace_listings
+ FOR EACH ROW EXECUTE FUNCTION bump_marketplace_updated();
+
+-- ── Comments + reactions (the social-graph plumbing) ───────────────────────
+
+CREATE TABLE IF NOT EXISTS comments (
+ id BIGSERIAL PRIMARY KEY,
+ parent_type TEXT NOT NULL CHECK (parent_type IN ('listing','comment')),
+ parent_id BIGINT NOT NULL,
+ app_user_id BIGINT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
+ body TEXT NOT NULL,
+ status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','removed','flagged')),
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+CREATE INDEX IF NOT EXISTS idx_comments_parent ON comments(parent_type, parent_id);
+CREATE INDEX IF NOT EXISTS idx_comments_user ON comments(app_user_id);
+
+CREATE TABLE IF NOT EXISTS reactions (
+ id BIGSERIAL PRIMARY KEY,
+ parent_type TEXT NOT NULL CHECK (parent_type IN ('listing','comment')),
+ parent_id BIGINT NOT NULL,
+ app_user_id BIGINT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
+ emoji TEXT NOT NULL, -- '🐾' | '❤️' | '🦴' | '👀' | '🆘'
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ UNIQUE (parent_type, parent_id, app_user_id, emoji)
+);
+CREATE INDEX IF NOT EXISTS idx_reactions_parent ON reactions(parent_type, parent_id);
+
+-- ── B2B data orders (selling the curated list to marketing firms) ──────────
+-- A "list" is a saved query (state, category, has_email, has_phone, etc.)
+-- with a price tier. Buyer pays, gets a CSV download URL + monthly refresh.
+
+CREATE TABLE IF NOT EXISTS data_orders (
+ id BIGSERIAL PRIMARY KEY,
+ full_name TEXT NOT NULL,
+ company TEXT,
+ email TEXT NOT NULL,
+ phone TEXT,
+
+ list_label TEXT NOT NULL, -- 'CA vet clinics with email', 'all US shelters', etc.
+ filters_json JSONB NOT NULL, -- {state:'CA', category:'vet_clinic', has_email:true}
+ estimated_rows INTEGER,
+
+ tier TEXT NOT NULL DEFAULT 'one_time' CHECK (tier IN ('one_time','monthly_refresh','quarterly')),
+ amount_cents INTEGER NOT NULL,
+
+ status TEXT NOT NULL DEFAULT 'pending_payment'
+ CHECK (status IN ('pending_payment','paid','generated','delivered','refunded','cancelled')),
+ payment_link TEXT,
+ stripe_session_id TEXT,
+ paid_at TIMESTAMPTZ,
+ generated_csv_path TEXT,
+ download_url TEXT, -- one-time signed URL after payment
+ delivered_at TIMESTAMPTZ,
+
+ notes TEXT,
+ ip TEXT,
+ user_agent TEXT,
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+CREATE INDEX IF NOT EXISTS idx_data_orders_status ON data_orders(status);
+CREATE INDEX IF NOT EXISTS idx_data_orders_email ON data_orders(LOWER(email));
+
+-- ── Traffic-research signals (what queries drive traffic in pet/dog) ───────
+
+CREATE TABLE IF NOT EXISTS traffic_signals (
+ id BIGSERIAL PRIMARY KEY,
+ source TEXT NOT NULL, -- 'google_autocomplete' | 'google_trends_rss' | 'reddit_top' | 'wikipedia_views'
+ topic TEXT NOT NULL, -- the seed query, e.g., 'best dog food'
+ suggestion TEXT NOT NULL, -- the suggested expansion / related query
+ signal_value NUMERIC(8,2), -- normalized 0..100 (or count)
+ region TEXT DEFAULT 'US',
+ recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ UNIQUE (source, topic, suggestion, region, recorded_at)
+);
+CREATE INDEX IF NOT EXISTS idx_traffic_topic ON traffic_signals(topic);
+CREATE INDEX IF NOT EXISTS idx_traffic_value ON traffic_signals(signal_value DESC NULLS LAST);
+
+COMMIT;
diff --git a/package.json b/package.json
index 163a4a2..22f5efd 100644
--- a/package.json
+++ b/package.json
@@ -11,10 +11,11 @@
"seed:sources": "node src/scripts/seed_sources.js",
"ingest:vets": "node src/ingest/state_vet_boards.js",
"ingest:osm-ca": "node src/ingest/osm_california.js",
+ "ingest:reddit": "node src/ingest/reddit.js",
"enrich:emails": "node src/enrich/extract_emails.js",
+ "research:traffic": "node src/scripts/research_traffic.js",
"ingest:clinics:aaha": "node src/ingest/aaha_accredited.js",
"ingest:shelters": "node src/ingest/petfinder_shelters.js",
- "ingest:reddit": "node src/ingest/reddit.js",
"ingest:reddit:dry": "DRY=1 LIMIT=10 node src/ingest/reddit.js",
"ingest:nextdoor": "node src/ingest/nextdoor.js",
"audit:websites": "node src/audit/site_audit.js",
diff --git a/public/css/community.css b/public/css/community.css
new file mode 100644
index 0000000..253cc38
--- /dev/null
+++ b/public/css/community.css
@@ -0,0 +1,63 @@
+/* PawCircles community + marketplace styles */
+.narrow{max-width:680px}
+.user-pill{margin-left:.7em}
+.user-pill a{color:#0f4d3a;font-weight:600;background:#e6efe9;padding:.35em .8em;border-radius:999px;font-size:.9em}
+.login-link{margin-left:.5em;color:#475569;font-size:.95em}
+
+.auth-form{background:#fff;border:1px solid #e6e1d6;border-radius:12px;padding:1.5em 2em;margin:1em 0}
+.auth-form label{display:block;margin:.7em 0;font-weight:500}
+.auth-form input,.auth-form select,.auth-form textarea{width:100%;padding:.55em .7em;border:1px solid #e6e1d6;border-radius:6px;font:inherit;background:#fff}
+.auth-form button{background:#0f4d3a;color:#fff;padding:.85em 1.5em;border:0;border-radius:8px;font-weight:600;font-size:1em;cursor:pointer;margin-top:1em}
+.auth-form button:hover{background:#0a3a2c}
+.auth-form fieldset{border:1px solid #e6e1d6;border-radius:8px;padding:.7em 1em;margin:.7em 0}
+.auth-form fieldset legend{padding:0 .4em;color:#6b6b6b;font-size:.9em}
+.auth-form .ck{display:inline-flex;align-items:center;gap:.4em;font-weight:400;margin:.2em 1em .2em 0}
+.auth-form .row2{display:grid;grid-template-columns:1fr 1fr;gap:1em}
+@media(max-width:520px){.auth-form .row2{grid-template-columns:1fr}}
+
+.lst-grid{display:grid;grid-template-columns:repeat(auto-fill,minmax(290px,1fr));gap:1em;margin-top:1.5em}
+.lst-card{display:block;background:#fff;border:1px solid #e6e1d6;border-radius:10px;padding:1em 1.2em;color:#1a1a1a}
+.lst-card:hover{border-color:#0f4d3a;text-decoration:none;box-shadow:0 4px 14px rgba(15,77,58,.07)}
+.lst-card h3{margin:.3em 0 .5em;font-size:1.1em;line-height:1.3}
+.lst-meta{display:flex;justify-content:space-between;align-items:center;margin-bottom:.4em}
+.lst-type{display:inline-block;background:#e6efe9;color:#0f4d3a;font-size:.8em;padding:.15em .55em;border-radius:4px;font-weight:600}
+.lst-loc{color:#6b6b6b;font-size:.8em}
+.lst-body{color:#475569;font-size:.92em;margin:.4em 0 .6em}
+.lst-foot{display:flex;align-items:center;gap:.7em;font-size:.85em;color:#6b6b6b;margin-top:.6em;flex-wrap:wrap}
+.lst-price{color:#c97a3e;font-size:1.05em}
+.lst-author{margin-left:auto}
+
+.listing-detail{background:#fff;border:1px solid #e6e1d6;border-radius:12px;padding:2em;margin:1em 0}
+.listing-detail h1{margin:.4em 0}
+.listing-detail .big-price{color:#c97a3e;font-size:2em;font-weight:700;margin:.3em 0}
+.lst-body-full{margin-top:1em;line-height:1.7}
+.contact-card{background:#fffaf3;border:1px solid #c97a3e;border-radius:8px;padding:1em 1.25em;margin-top:1.5em}
+
+.comments{margin-top:2em}
+.comment{background:#fff;border:1px solid #e6e1d6;border-radius:8px;padding:1em 1.2em;margin-bottom:.8em}
+.comment p{margin:.3em 0 0}
+.comment-form textarea{width:100%;padding:.6em;border:1px solid #e6e1d6;border-radius:6px;font:inherit}
+.comment-form button{background:#0f4d3a;color:#fff;padding:.6em 1.2em;border:0;border-radius:6px;font-weight:600;cursor:pointer;margin-top:.5em}
+
+.circle-grid{display:grid;grid-template-columns:repeat(auto-fill,minmax(220px,1fr));gap:1em;margin:1em 0 2em}
+.circle-card{display:block;background:#fff;border:1px solid #e6e1d6;border-radius:8px;padding:1em 1.2em;color:#1a1a1a}
+.circle-card:hover{border-color:#0f4d3a;text-decoration:none}
+.circle-card strong{display:block;margin-bottom:.2em}
+.circle-card small{color:#6b6b6b;font-size:.85em}
+
+.stats{display:grid;grid-template-columns:repeat(auto-fit,minmax(180px,1fr));gap:1em;margin:1.5em 0 2em}
+.stats > div{background:#fff;border:1px solid #e6e1d6;border-radius:10px;padding:1.2em;text-align:center}
+.stats > div strong{display:block;font-size:1.8em;color:#0f4d3a}
+.stats > div span{color:#6b6b6b;font-size:.9em}
+
+.pricing{display:grid;grid-template-columns:repeat(auto-fit,minmax(220px,1fr));gap:1em;margin:1em 0 2em}
+.tier{background:#fff;border:1px solid #e6e1d6;border-radius:10px;padding:1.5em;text-align:center}
+.tier strong{display:block;font-size:2em;color:#0f4d3a}
+.tier span{display:block;color:#1a1a1a;font-weight:600;margin:.2em 0 .6em}
+.tier p{color:#6b6b6b;font-size:.92em;margin:0}
+.tier.featured{border:2px solid #c97a3e;background:#fffaf3}
+.tier.featured strong{color:#c97a3e}
+
+p.ok{background:#e6efe9;color:#0f4d3a;padding:1em;border-radius:8px}
+p.empty{padding:2em;text-align:center;color:#6b6b6b;background:#fff;border:1px dashed #e6e1d6;border-radius:8px}
+.muted.small{font-size:.85em}
diff --git a/public/css/pitches.css b/public/css/pitches.css
new file mode 100644
index 0000000..3d00903
--- /dev/null
+++ b/public/css/pitches.css
@@ -0,0 +1,41 @@
+/* Admin pitch dashboard */
+.back{display:inline-block;margin:.5em 0 1em;color:#6b6b6b}
+.pitch-grid{display:grid;grid-template-columns:repeat(auto-fill,minmax(290px,1fr));gap:1em;margin-top:1em}
+.pitch-card{display:block;background:#fff;border:1px solid #e6e1d6;border-radius:10px;overflow:hidden;color:#1a1a1a;text-decoration:none;transition:transform .15s,border-color .15s}
+.pitch-card:hover{transform:translateY(-2px);border-color:#0f4d3a;text-decoration:none;box-shadow:0 6px 18px rgba(0,0,0,.08)}
+.pitch-shot{height:160px;background:#f3efe6;overflow:hidden;display:flex;align-items:center;justify-content:center;border-bottom:1px solid #e6e1d6}
+.pitch-shot img{width:100%;height:100%;object-fit:cover;object-position:top}
+.no-shot{color:#c0392b;font-size:.85em;font-style:italic}
+.pitch-meta{padding:.8em 1em}
+.pitch-meta strong{display:block;margin-bottom:.2em;font-size:1em;line-height:1.3}
+.pitch-meta small{color:#6b6b6b;font-size:.85em}
+.pitch-stats{display:flex;gap:.8em;align-items:center;margin:.5em 0 .3em;font-size:.85em;color:#475569}
+.pitch-stats .score{font-weight:700;font-size:1.1em;color:#0f4d3a}
+.pitch-bad .score{color:#c0392b}
+.pitch-mid .score{color:#c97a3e}
+.pitch-good .score{color:#0f4d3a}
+.primary-email{display:block;margin-top:.3em;color:#0f4d3a;font-weight:600;font-size:.8em}
+.no-email{display:block;margin-top:.3em;color:#c0392b;font-style:italic;font-size:.8em}
+
+.pitch-detail-grid{display:grid;grid-template-columns:1fr 1fr;gap:1.5em;margin:1.5em 0}
+@media(max-width:900px){.pitch-detail-grid{grid-template-columns:1fr}}
+.pitch-shot-big{width:100%;border:1px solid #e6e1d6;border-radius:8px;display:block;background:#f3efe6}
+.audit-summary{background:#fff;border:1px solid #e6e1d6;border-radius:8px;padding:1em 1.2em;margin-top:1em;font-size:.93em}
+.audit-summary strong{display:block;font-size:1.2em;margin-bottom:.4em;color:#0f4d3a}
+.audit-summary ul{margin:.5em 0 0;padding-left:1.2em;color:#475569}
+.mockup-row{display:grid;grid-template-columns:1fr 1fr 1fr;gap:.8em}
+.mockup-thumb{display:block;border:1px solid #e6e1d6;border-radius:6px;overflow:hidden;color:#1a1a1a;text-decoration:none;background:#fff}
+.mockup-thumb:hover{border-color:#0f4d3a;text-decoration:none}
+.mockup-thumb img{width:100%;height:140px;object-fit:cover;object-position:top;display:block}
+.mockup-thumb small{display:block;padding:.4em .6em;color:#475569;font-size:.8em;background:#f8f5ef}
+
+.emails-section{margin:2em 0}
+
+.composer{background:#fff;border:1px solid #e6e1d6;border-radius:12px;padding:1.5em 2em;margin-top:1.5em}
+.composer label{display:block;margin:.7em 0;font-weight:500}
+.composer input,.composer select{width:100%;padding:.55em .7em;border:1px solid #e6e1d6;border-radius:6px;font:inherit}
+.composer details{margin-top:.7em}
+.composer summary{cursor:pointer;color:#0f4d3a;font-weight:600;padding:.4em 0}
+.composer .preview{background:#f8f5ef;border:1px solid #e6e1d6;border-radius:6px;padding:1em;font-family:ui-monospace,SF Mono,Menlo,monospace;font-size:.85em;white-space:pre-wrap;max-height:400px;overflow:auto}
+.composer button{background:#c97a3e;color:#fff;padding:.85em 1.5em;border:0;border-radius:8px;font-weight:600;font-size:1em;cursor:pointer;margin-top:.8em}
+.composer button:hover{background:#a85e2c}
diff --git a/src/lib/auth.js b/src/lib/auth.js
new file mode 100644
index 0000000..2bc17ed
--- /dev/null
+++ b/src/lib/auth.js
@@ -0,0 +1,177 @@
+// Tiny auth library — bcrypt password hash + opaque session-token cookie.
+// Sessions live in app_sessions (90-day default). No JWTs (overkill for this).
+//
+// Usage:
+// import { signup, login, logout, requireUser, currentUser } from './auth.js';
+// app.post('/auth/signup', signup);
+// app.post('/auth/login', login);
+// app.post('/auth/logout', logout);
+// app.get('/me', requireUser, (req,res) => res.json(req.user));
+
+import bcrypt from 'bcryptjs';
+import crypto from 'node:crypto';
+import { pool, one, query } from './db.js';
+import { log } from './log.js';
+
+const COOKIE = 'animals_session';
+const COOKIE_OPTS = { httpOnly: true, sameSite: 'lax', path: '/', maxAge: 90 * 24 * 60 * 60 * 1000 };
+
+export async function currentUser(req) {
+ const tok = req.cookies?.[COOKIE];
+ if (!tok) return null;
+ const row = await one(
+ `SELECT u.id, u.email, u.handle, u.full_name, u.role, u.home_zip, u.home_city, u.home_state,
+ u.home_lat, u.home_lng, u.species_pets, u.visibility_default,
+ u.approved_circles, u.approved_zips, u.email_verified
+ FROM app_sessions s JOIN app_users u ON u.id = s.app_user_id
+ WHERE s.token = $1 AND s.expires_at > NOW() AND u.suspended_at IS NULL`,
+ [tok]
+ );
+ if (row) {
+ pool.query('UPDATE app_sessions SET last_seen_at = NOW() WHERE token = $1', [tok]).catch(() => {});
+ }
+ return row;
+}
+
+export async function attachUser(req, _res, next) {
+ req.user = await currentUser(req).catch(() => null);
+ next();
+}
+
+export async function requireUser(req, res, next) {
+ req.user = await currentUser(req).catch(() => null);
+ if (!req.user) return res.status(401).json({ error: 'login required' });
+ next();
+}
+
+export async function signup(req, res) {
+ try {
+ const { email, password, full_name, handle, home_zip, home_city, home_state, species_pets } = req.body || {};
+ if (!email || !password || !home_zip) return res.status(400).json({ error: 'email, password, home_zip required' });
+ if (!/^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$/i.test(email)) return res.status(400).json({ error: 'invalid email' });
+ if (password.length < 8) return res.status(400).json({ error: 'password ≥ 8 chars' });
+ if (!/^[0-9]{5}$/.test(home_zip)) return res.status(400).json({ error: 'home_zip must be 5 digits' });
+
+ const hash = await bcrypt.hash(password, 10);
+ const desiredHandle = (handle || email.split('@')[0]).toLowerCase().replace(/[^a-z0-9_]/g, '').slice(0, 20);
+ const finalHandle = await pickAvailableHandle(desiredHandle);
+
+ // species_pets from a checkbox group can be: undefined (none checked),
+ // string (one checked), or array (multiple). Normalize to text[].
+ const petsArr = species_pets == null ? []
+ : Array.isArray(species_pets) ? species_pets
+ : [species_pets];
+
+ let user;
+ try {
+ user = await one(
+ `INSERT INTO app_users (email, password_hash, full_name, handle, home_zip, home_city, home_state, species_pets, role, email_verified)
+ VALUES ($1,$2,$3,$4,$5,$6,$7,$8,'business_owner',FALSE)
+ RETURNING id, email, handle, home_zip`,
+ [email.toLowerCase(), hash, full_name || null, finalHandle, home_zip, home_city || null,
+ (home_state||'').toUpperCase().slice(0,2) || null, petsArr]
+ );
+ } catch (err) {
+ if (err.code === '23505') return res.status(409).json({ error: 'email already registered' });
+ throw err;
+ }
+
+ // Auto-join the user's home-ZIP circle (create if missing)
+ const circle = await ensureZipCircle(home_zip, home_city, home_state);
+ await query(
+ `INSERT INTO circle_memberships (circle_id, app_user_id) VALUES ($1, $2) ON CONFLICT DO NOTHING`,
+ [circle.id, user.id]
+ );
+ await pool.query(`UPDATE circles SET member_count = member_count + 1 WHERE id = $1`, [circle.id]);
+
+ const token = await createSession(user.id, req);
+ res.cookie(COOKIE, token, COOKIE_OPTS);
+ res.json({ ok: true, user, circle });
+ } catch (err) {
+ log.error('signup failed', { err: err.message });
+ res.status(500).json({ error: 'signup failed' });
+ }
+}
+
+export async function login(req, res) {
+ try {
+ const { email, password } = req.body || {};
+ if (!email || !password) return res.status(400).json({ error: 'email + password required' });
+ const u = await one(
+ `SELECT id, password_hash, suspended_at FROM app_users WHERE email = $1`,
+ [email.toLowerCase()]
+ );
+ if (!u) return res.status(401).json({ error: 'invalid credentials' });
+ if (u.suspended_at) return res.status(403).json({ error: 'account suspended' });
+ const ok = await bcrypt.compare(password, u.password_hash);
+ if (!ok) return res.status(401).json({ error: 'invalid credentials' });
+ const token = await createSession(u.id, req);
+ res.cookie(COOKIE, token, COOKIE_OPTS);
+ res.json({ ok: true });
+ } catch (err) {
+ log.error('login failed', { err: err.message });
+ res.status(500).json({ error: 'login failed' });
+ }
+}
+
+export async function logout(req, res) {
+ const tok = req.cookies?.[COOKIE];
+ if (tok) await pool.query(`DELETE FROM app_sessions WHERE token = $1`, [tok]);
+ res.clearCookie(COOKIE);
+ res.json({ ok: true });
+}
+
+async function createSession(userId, req) {
+ const token = crypto.randomBytes(32).toString('base64url');
+ await query(
+ `INSERT INTO app_sessions (token, app_user_id, ip, user_agent) VALUES ($1, $2, $3, $4)`,
+ [token, userId, req.ip || null, req.headers['user-agent'] || null]
+ );
+ return token;
+}
+
+async function pickAvailableHandle(base) {
+ if (!base) base = 'user';
+ for (let i = 0; i < 30; i++) {
+ const candidate = i === 0 ? base : `${base}${i}`;
+ const taken = await one(`SELECT 1 AS x FROM app_users WHERE handle = $1`, [candidate]);
+ if (!taken) return candidate;
+ }
+ return base + Math.floor(Math.random() * 100000);
+}
+
+async function ensureZipCircle(zip, city, state) {
+ const slug = `zip-${zip}`;
+ const existing = await one(`SELECT * FROM circles WHERE slug = $1`, [slug]);
+ if (existing) return existing;
+ return one(
+ `INSERT INTO circles (kind, slug, name, zip, city, state, is_official)
+ VALUES ('zip', $1, $2, $3, $4, $5, TRUE)
+ RETURNING *`,
+ [slug, `ZIP ${zip}${city ? ' · ' + city : ''}`, zip, city || null, (state||'').toUpperCase().slice(0,2) || null]
+ );
+}
+
+export async function userCanSeeListing(user, listing) {
+ if (listing.visibility === 'public') return true;
+ if (!user) return listing.visibility === 'public';
+ if (listing.app_user_id === user.id) return true;
+ if (listing.visibility === 'home_zip') return user.home_zip === listing.zip;
+ if (listing.visibility === 'home_zip_25mi') {
+ return user.home_zip === listing.zip
+ || (user.approved_zips || []).includes(listing.zip)
+ || zipsWithinPrefix(user.home_zip, listing.zip);
+ }
+ if (listing.visibility === 'statewide') return user.home_state === listing.state;
+ if (listing.visibility === 'approved_circles_only') {
+ const intersect = (listing.visible_circles || []).some(c => (user.approved_circles || []).includes(Number(c)));
+ return intersect;
+ }
+ return false;
+}
+
+function zipsWithinPrefix(a, b) {
+ // cheap prefilter — same first 3 ZIP digits ≈ same metro region
+ if (!a || !b) return false;
+ return String(a).slice(0, 3) === String(b).slice(0, 3);
+}
diff --git a/src/scripts/research_traffic.js b/src/scripts/research_traffic.js
new file mode 100644
index 0000000..f972e64
--- /dev/null
+++ b/src/scripts/research_traffic.js
@@ -0,0 +1,201 @@
+#!/usr/bin/env node
+// Traffic research — what queries actually drive search traffic in the
+// pet/dog vertical. 100% free signals (no Ahrefs/SEMrush keys).
+//
+// Sources:
+// 1. Google Autocomplete — anonymous JSON endpoint, returns 10 suggestions
+// per seed query. Suggestion presence = real search demand.
+// 2. Google Trends RSS — daily trending searches (US). Lets us spot
+// breakout topics before they show up in tools that cost money.
+// 3. Wikipedia Pageviews API — pageviews on pet-related articles last 30d
+// is a great proxy for organic search interest (people searching
+// "best dog food" land on the Wikipedia article first; we want to
+// OWN those long-tail queries instead).
+//
+// Usage:
+// node src/scripts/research_traffic.js # full pass
+// node src/scripts/research_traffic.js --source autocomplete # one source
+//
+// Writes results into `traffic_signals` table + a digest at
+// docs/traffic_research_<date>.md.
+
+import 'dotenv/config';
+import fs from 'node:fs';
+import path from 'node:path';
+import { fileURLToPath } from 'node:url';
+import { fetch as undiciFetch } from 'undici';
+import { pool, query } from '../lib/db.js';
+import { log } from '../lib/log.js';
+
+const __dirname = path.dirname(fileURLToPath(import.meta.url));
+const DOCS = path.resolve(__dirname, '..', '..', 'docs');
+fs.mkdirSync(DOCS, { recursive: true });
+
+const args = process.argv.slice(2);
+const onlySource = args[args.indexOf('--source') + 1] || null;
+
+// Seed queries — stuff pet owners type into Google daily.
+const SEEDS = [
+ // care intents
+ 'best dog food', 'best cat food', 'puppy training', 'how to potty train a',
+ 'why is my dog', 'why is my cat', 'is it safe for dogs to', 'is it safe for cats to',
+ 'cheap vet near', 'low cost vet', '24 hour vet near', 'emergency vet near',
+ 'dog vaccinations cost', 'spay neuter clinic',
+ // breed intents
+ 'golden retriever', 'french bulldog', 'german shepherd', 'goldendoodle', 'shih tzu',
+ // service intents
+ 'dog grooming near', 'dog boarding near', 'dog daycare near', 'dog walker near',
+ 'cat sitter near', 'puppy training class', 'dog trainer near',
+ // adoption / rescue
+ 'puppies for adoption near', 'kittens for adoption near', 'free puppies', 'free kittens',
+ 'rescue near me dog', 'animal shelter near',
+ // health / symptoms
+ 'my dog ate', 'my cat ate', 'dog limping', 'cat throwing up', 'dog scratching',
+ 'dog ear infection', 'fleas on dog',
+ // commerce
+ 'best harness for', 'best dog bed', 'flea treatment for dogs', 'dog dna test',
+ 'pet insurance', 'pet insurance cost',
+];
+
+const stats = { autocomplete: 0, trends: 0, wikipedia: 0, errors: 0 };
+
+if (!onlySource || onlySource === 'autocomplete') await runAutocomplete();
+if (!onlySource || onlySource === 'trends') await runTrends();
+if (!onlySource || onlySource === 'wikipedia') await runWikipedia();
+
+await writeDigest();
+await pool.end();
+log.info('traffic research complete', stats);
+
+// ─── Google Autocomplete ───────────────────────────────────────────────────
+async function runAutocomplete() {
+ log.info(`autocomplete: ${SEEDS.length} seeds`);
+ for (const seed of SEEDS) {
+ try {
+ const url = `https://suggestqueries.google.com/complete/search?client=firefox&hl=en&gl=us&q=${encodeURIComponent(seed)}`;
+ const r = await undiciFetch(url, {
+ headers: { 'user-agent': 'Mozilla/5.0', accept: 'application/json' },
+ signal: AbortSignal.timeout(8000),
+ });
+ if (!r.ok) { stats.errors++; continue; }
+ const j = await r.json();
+ const sugg = (j[1] || []).slice(0, 10);
+ for (let i = 0; i < sugg.length; i++) {
+ // Higher rank → higher signal. We use 100 - rank*5 as a 0-100 proxy.
+ await query(
+ `INSERT INTO traffic_signals (source, topic, suggestion, signal_value, region)
+ VALUES ('google_autocomplete', $1, $2, $3, 'US')
+ ON CONFLICT DO NOTHING`,
+ [seed, sugg[i], 100 - i * 5]
+ );
+ stats.autocomplete++;
+ }
+ await sleep(700); // be polite — Google rate-limits this
+ } catch (err) { stats.errors++; log.warn(`autocomplete ${seed}: ${err.message}`); }
+ }
+}
+
+// ─── Google Trends Daily RSS ──────────────────────────────────────────────
+async function runTrends() {
+ try {
+ const url = 'https://trends.google.com/trending/rss?geo=US';
+ const r = await undiciFetch(url, {
+ headers: { 'user-agent': 'Mozilla/5.0', accept: 'application/rss+xml,*/*' },
+ signal: AbortSignal.timeout(10000),
+ });
+ if (!r.ok) { log.warn(`trends RSS HTTP ${r.status}`); return; }
+ const xml = await r.text();
+ // Cheap RSS parse (don't pull a whole library)
+ const items = [...xml.matchAll(/<title>(?!Daily Search Trends)([^<]+)<\/title>[\s\S]*?<ht:approx_traffic>([^<]+)<\/ht:approx_traffic>/g)];
+ for (const m of items) {
+ const title = m[1].trim();
+ const traffic = parseInt(m[2].replace(/[^0-9]/g, ''), 10);
+ // Score = log10(traffic) * 25 → 50k = 117, 100k = 125, 1M = 150
+ const score = traffic > 0 ? Math.min(100, Math.log10(traffic) * 25) : null;
+ // Filter for pet-relevant
+ if (!/\b(dog|cat|pet|puppy|kitten|vet|animal|breed)\b/i.test(title)) continue;
+ await query(
+ `INSERT INTO traffic_signals (source, topic, suggestion, signal_value, region)
+ VALUES ('google_trends_rss', 'daily_us', $1, $2, 'US')
+ ON CONFLICT DO NOTHING`,
+ [title, score]
+ );
+ stats.trends++;
+ }
+ } catch (err) { stats.errors++; log.warn(`trends: ${err.message}`); }
+}
+
+// ─── Wikipedia pageviews (last 30 days, US/EN) ────────────────────────────
+async function runWikipedia() {
+ const ARTICLES = [
+ 'Dog', 'Cat', 'Veterinarian', 'Animal_shelter', 'Pet_adoption',
+ 'Golden_Retriever', 'French_Bulldog', 'Labrador_Retriever', 'German_Shepherd',
+ 'Poodle', 'Bulldog', 'Beagle', 'Yorkshire_Terrier', 'Dachshund',
+ 'Boxer_(dog)', 'Siberian_Husky', 'Shih_Tzu', 'Cavalier_King_Charles_Spaniel',
+ 'Pet_food', 'Pet_insurance', 'Dog_grooming', 'Dog_training',
+ 'American_Veterinary_Medical_Association', 'American_Animal_Hospital_Association',
+ 'Spaying', 'Neutering', 'Rabies_vaccine', 'Heartworm', 'Flea',
+ ];
+ const end = new Date(); end.setDate(end.getDate() - 1);
+ const start = new Date(end); start.setDate(start.getDate() - 29);
+ const fmt = (d) => d.toISOString().slice(0,10).replace(/-/g,'');
+ for (const a of ARTICLES) {
+ try {
+ const url = `https://wikimedia.org/api/rest_v1/metrics/pageviews/per-article/en.wikipedia/all-access/all-agents/${encodeURIComponent(a)}/daily/${fmt(start)}/${fmt(end)}`;
+ const r = await undiciFetch(url, {
+ headers: { 'user-agent': 'animals-directory-research/0.1 (steve@designerwallcoverings.com)' },
+ signal: AbortSignal.timeout(8000),
+ });
+ if (!r.ok) { stats.errors++; continue; }
+ const j = await r.json();
+ const total = (j.items || []).reduce((s, x) => s + (x.views || 0), 0);
+ const score = total > 0 ? Math.min(100, Math.log10(total) * 18) : 0;
+ await query(
+ `INSERT INTO traffic_signals (source, topic, suggestion, signal_value, region)
+ VALUES ('wikipedia_views', $1, $1, $2, 'US')
+ ON CONFLICT DO NOTHING`,
+ [a, score]
+ );
+ stats.wikipedia++;
+ await sleep(120);
+ } catch (err) { stats.errors++; log.warn(`wikipedia ${a}: ${err.message}`); }
+ }
+}
+
+// ─── Digest ───────────────────────────────────────────────────────────────
+async function writeDigest() {
+ const date = new Date().toISOString().slice(0, 10);
+ const top = await query(`
+ SELECT source, topic, suggestion, signal_value
+ FROM traffic_signals
+ WHERE recorded_at::date = $1::date
+ ORDER BY signal_value DESC NULLS LAST
+ LIMIT 100`, [date]);
+ const md = [
+ `# AnimalsDirectory — Traffic Research ${date}`,
+ ``,
+ `Top 100 signals across Google Autocomplete + Trends + Wikipedia pageviews.`,
+ `Each is a topic we should consider building a dedicated page for (SEO + ad inventory).`,
+ ``,
+ `## Top signals`,
+ ``,
+ `| Source | Seed / topic | Suggestion / article | Signal |`,
+ `| ------ | ------------ | -------------------- | -----: |`,
+ ...top.rows.map(r => `| ${r.source} | ${r.topic} | ${r.suggestion} | ${r.signal_value?.toFixed?.(0) || '—'} |`),
+ ``,
+ `## Recommended page builds (highest leverage)`,
+ ``,
+ `1. **/breeds/:slug** — already built; ensure every AKC breed has a page (currently 100, target 200+).`,
+ `2. **/topics/:slug** — new section. Build pages for the top autocomplete queries (e.g., \`/topics/why-is-my-dog-shaking\`). High-volume, low-competition.`,
+ `3. **/in/:cityState** — already built; expand from CA-only to all 50 states.`,
+ `4. **/symptoms/:slug** — symptom-checker pages (\`/symptoms/dog-vomiting\`, \`/symptoms/cat-not-eating\`). Each links to nearest emergency vet.`,
+ `5. **/cost/:slug** — cost calculators (\`/cost/spay-cat\`, \`/cost/dog-dental\`). High commercial intent.`,
+ ``,
+ `Generated: ${new Date().toISOString()}`,
+ ].join('\n');
+ const out = path.join(DOCS, `traffic_research_${date}.md`);
+ fs.writeFileSync(out, md);
+ log.info(`wrote ${out}`);
+}
+
+function sleep(ms) { return new Promise(r => setTimeout(r, ms)); }
diff --git a/src/scripts/weekly-refresh.sh b/src/scripts/weekly-refresh.sh
new file mode 100755
index 0000000..85a2bba
--- /dev/null
+++ b/src/scripts/weekly-refresh.sh
@@ -0,0 +1,100 @@
+#!/bin/bash
+# Project: Animals — weekly refresh job.
+# Runs every Monday 04:00 PT via launchd (com.steve.animals-weekly).
+#
+# Steps (in order):
+# 1. OSM California re-ingest (catches new listings + tag changes)
+# 2. Email enrichment for any business not searched in 30+ days
+# 3. Reddit ingest (~45 metros × 50 posts → mentions in raw_records)
+# 4. Compute deltas vs. last run, email digest to Steve via George
+#
+# Logs append to ~/Projects/animals/logs/weekly.log.
+# Each subprocess gets its own .log too so failures isolate.
+
+set -u
+cd /Users/stevestudio2/Projects/animals
+PATH=/opt/homebrew/bin:/usr/local/bin:/usr/bin:/bin
+export PATH
+
+LOG=logs/weekly.log
+TS=$(date -u +%FT%TZ)
+WEEK=$(date -u +%G-W%V)
+RUN_DIR=logs/weekly-$WEEK
+mkdir -p "$RUN_DIR"
+
+log() { echo "$(date -u +%FT%TZ) animals-weekly $*" | tee -a "$LOG"; }
+
+# Snapshot counts BEFORE for delta digest
+log "begin weekly refresh ($WEEK)"
+psql "host=/tmp dbname=animals_directory user=stevestudio2" -tAc \
+ "SELECT 'before',
+ (SELECT COUNT(*) FROM businesses) AS businesses,
+ (SELECT COUNT(*) FROM businesses WHERE email IS NOT NULL) AS w_email,
+ (SELECT COUNT(*) FROM business_emails) AS email_rows,
+ (SELECT COUNT(*) FROM raw_records WHERE entity_type='reddit_post') AS reddit_posts" \
+ > "$RUN_DIR/before.tsv"
+
+# 1. OSM California
+log "step 1/3: OSM California re-ingest"
+node src/ingest/osm_california.js > "$RUN_DIR/osm.log" 2>&1
+OSM_RC=$?
+log " osm rc=$OSM_RC ($(wc -l < $RUN_DIR/osm.log) log lines)"
+
+# 2. Email enrichment for stale rows (30+ days) + brand-new ones
+log "step 2/3: email enrichment (refresh-days=30, limit=400)"
+node src/enrich/extract_emails.js --refresh-days 30 --limit 400 > "$RUN_DIR/emails.log" 2>&1
+EMAIL_RC=$?
+log " emails rc=$EMAIL_RC"
+
+# 3. Reddit ingest (LIMIT lower than dev so we don't overload Ollama overnight)
+log "step 3/3: reddit ingest (LIMIT=25/sub)"
+LIMIT=25 node src/ingest/reddit.js > "$RUN_DIR/reddit.log" 2>&1
+REDDIT_RC=$?
+log " reddit rc=$REDDIT_RC"
+
+# Snapshot counts AFTER + compute deltas
+psql "host=/tmp dbname=animals_directory user=stevestudio2" -tAc \
+ "SELECT 'after',
+ (SELECT COUNT(*) FROM businesses),
+ (SELECT COUNT(*) FROM businesses WHERE email IS NOT NULL),
+ (SELECT COUNT(*) FROM business_emails),
+ (SELECT COUNT(*) FROM raw_records WHERE entity_type='reddit_post')" \
+ > "$RUN_DIR/after.tsv"
+
+# Build digest
+node -e '
+const fs = require("fs");
+const before = fs.readFileSync("'"$RUN_DIR"'/before.tsv", "utf8").trim().split("|");
+const after = fs.readFileSync("'"$RUN_DIR"'/after.tsv", "utf8").trim().split("|");
+const cols = ["businesses","with_email","email_rows","reddit_posts"];
+const rows = cols.map((c,i) => {
+ const b = parseInt(before[i+1]||"0",10);
+ const a = parseInt(after[i+1]||"0",10);
+ const d = a - b;
+ return { c, b, a, d };
+});
+const html = `
+<h2>AnimalsDirectory weekly refresh — ${"'"$WEEK"'"}</h2>
+<p>OSM rc=${'"$OSM_RC"'}, emails rc=${'"$EMAIL_RC"'}, reddit rc=${'"$REDDIT_RC"'}</p>
+<table border="1" cellpadding="6" cellspacing="0" style="border-collapse:collapse">
+ <tr><th>metric</th><th>before</th><th>after</th><th>delta</th></tr>
+ ${rows.map(r => `<tr><td>${r.c}</td><td>${r.b}</td><td>${r.a}</td><td><strong>${r.d>=0?"+":""}${r.d}</strong></td></tr>`).join("")}
+</table>
+<p>Logs: ~/Projects/animals/'"$RUN_DIR"'</p>`;
+fs.writeFileSync("'"$RUN_DIR"'/digest.html", html);
+console.log(html);
+' > "$RUN_DIR/digest.txt" 2>&1
+
+log "digest built: $RUN_DIR/digest.html"
+
+# Email digest via George (gmail agent on tailnet) — non-blocking; weekly is OK if it fails
+if [ -f "$RUN_DIR/digest.html" ]; then
+ curl -fsS --max-time 15 -X POST http://localhost:9850/api/send \
+ -H 'content-type: application/json' \
+ -d "$(jq -n --arg b "$(cat $RUN_DIR/digest.html)" '{to:"steve@designerwallcoverings.com",subject:"AnimalsDirectory weekly refresh — '"$WEEK"'",body:$b}')" \
+ > "$RUN_DIR/george.log" 2>&1 \
+ && log "digest emailed via George" \
+ || log "george send failed (see $RUN_DIR/george.log) — digest still on disk"
+fi
+
+log "weekly refresh done"
diff --git a/src/server/admin_pitch.js b/src/server/admin_pitch.js
new file mode 100644
index 0000000..5fbb7c9
--- /dev/null
+++ b/src/server/admin_pitch.js
@@ -0,0 +1,300 @@
+// Admin pitch dashboard — the operations cockpit for the Vet Site Upgrade
+// program (Rail C). For each business with a website, shows:
+// - thumbnail of their actual home page (from site_audits.screenshot_path)
+// - 3 mockup variants we generated (site_mockups.screenshot_path)
+// - audit score + suggestion list
+// - emails we have for that business
+// - pitch templates ready to send via George (one click)
+//
+// Routes (all admin-gated):
+// GET /admin/pitches cards grid w/ filters
+// GET /admin/pitches/:bizId full detail page (all 3 mockups + pitch composer)
+// POST /admin/pitches/:bizId/send send pitch via George with chosen template
+// POST /admin/pitches/:bizId/audit kick a fresh audit (Playwright)
+
+import express from 'express';
+import { pool, many, one } from '../lib/db.js';
+import { attachUser } from '../lib/auth.js';
+
+export const adminPitch = express.Router();
+
+adminPitch.use(attachUser);
+adminPitch.use((req, res, next) => {
+ const adminEmails = (process.env.ADMIN_EMAIL || '').toLowerCase().split(',').map(s => s.trim()).filter(Boolean);
+ if (!req.user || !adminEmails.includes(req.user.email)) {
+ return res.status(403).send(adminLoginPage());
+ }
+ next();
+});
+
+// ── List view (cards grid) ────────────────────────────────────────────────
+adminPitch.get('/pitches', async (req, res) => {
+ const { state, category, max_score, has_email } = req.query;
+ const where = ["b.opt_out_flag = FALSE", "b.website IS NOT NULL", "b.website <> ''"];
+ const params = [];
+ if (state) { params.push(state.toUpperCase()); where.push(`b.state = $${params.length}`); }
+ if (category) { params.push(category); where.push(`b.category = $${params.length}`); }
+ if (max_score){ params.push(parseInt(max_score, 10)); where.push(`a.marketing_score IS NOT NULL AND a.marketing_score <= $${params.length}`); }
+ if (has_email === '1') where.push(`b.email IS NOT NULL`);
+ const sql = `
+ SELECT b.id, b.name, b.category, b.city, b.state, b.zip, b.website, b.email,
+ a.marketing_score, a.screenshot_path, a.audited_at, a.suggestions,
+ (SELECT COUNT(*) FROM site_mockups m WHERE m.business_id = b.id)::int AS mockup_count,
+ (SELECT COUNT(*) FROM business_emails e WHERE e.business_id = b.id)::int AS email_count
+ FROM businesses b
+ LEFT JOIN LATERAL (
+ SELECT * FROM site_audits sa WHERE sa.business_id = b.id ORDER BY audited_at DESC LIMIT 1
+ ) a ON TRUE
+ WHERE ${where.join(' AND ')}
+ ORDER BY a.marketing_score ASC NULLS FIRST, b.name
+ LIMIT 200`;
+ const rows = await many(sql, params);
+ res.send(renderPitchesIndex({ user: req.user, rows, filters: { state, category, max_score, has_email } }));
+});
+
+// ── Detail view ───────────────────────────────────────────────────────────
+adminPitch.get('/pitches/:bizId', async (req, res) => {
+ const id = parseInt(req.params.bizId, 10);
+ const biz = await one(`SELECT * FROM businesses WHERE id = $1`, [id]);
+ if (!biz) return res.status(404).send('not found');
+ const audit = await one(`SELECT * FROM site_audits WHERE business_id = $1 ORDER BY audited_at DESC LIMIT 1`, [id]);
+ const mockups = await many(`SELECT * FROM site_mockups WHERE business_id = $1 ORDER BY variant`, [id]);
+ const emails = await many(`SELECT email, email_kind, source_url, source_method FROM business_emails WHERE business_id = $1 ORDER BY (email_kind='intake') DESC, (email_kind='role') DESC, email`, [id]);
+ res.send(renderPitchDetail({ user: req.user, biz, audit, mockups, emails }));
+});
+
+// ── Send pitch via George ─────────────────────────────────────────────────
+adminPitch.post('/pitches/:bizId/send', express.json(), async (req, res) => {
+ const id = parseInt(req.params.bizId, 10);
+ const { template, to_email } = req.body || {};
+ const biz = await one(`SELECT * FROM businesses WHERE id = $1`, [id]);
+ if (!biz) return res.status(404).json({ error: 'biz not found' });
+ if (!to_email) return res.status(400).json({ error: 'to_email required' });
+ const audit = await one(`SELECT * FROM site_audits WHERE business_id = $1 ORDER BY audited_at DESC LIMIT 1`, [id]);
+ const mockups = await many(`SELECT variant, template_label, screenshot_path FROM site_mockups WHERE business_id = $1`, [id]);
+ const subject = subjectFor(template, biz);
+ const body = bodyFor(template, biz, audit, mockups);
+ if (!process.env.GEORGE_GMAIL_URL) return res.status(503).json({ error: 'George (gmail agent) URL not configured' });
+ try {
+ const r = await fetch(`${process.env.GEORGE_GMAIL_URL}/api/send`, {
+ method: 'POST',
+ headers: { 'content-type': 'application/json' },
+ body: JSON.stringify({ to: to_email, subject, body }),
+ });
+ const j = await r.json().catch(() => ({}));
+ // Record the send for follow-up
+ await pool.query(
+ `INSERT INTO upgrade_orders (business_id, full_name, email, business_name, website, plan, amount_cents, status, notes)
+ VALUES ($1, 'Outbound pitch', $2, $3, $4, 'pitch_sent', 0, 'pending_payment', $5)`,
+ [biz.id, to_email, biz.name, biz.website, `Template: ${template}; subject: ${subject}`]
+ );
+ res.json({ ok: r.ok, george: j, subject });
+ } catch (err) {
+ res.status(500).json({ error: err.message });
+ }
+});
+
+// ── HTML rendering ────────────────────────────────────────────────────────
+function esc(s) {
+ return String(s ?? '').replace(/[&<>"']/g, c => ({'&':'&','<':'<','>':'>','"':'"',"'":'''}[c]));
+}
+function stripScheme(u) {
+ return String(u || '').replace(/^https?:\/\//, '');
+}
+
+function head(title) {
+ return `<!doctype html><html><head><meta charset="utf-8">
+<meta name="viewport" content="width=device-width,initial-scale=1">
+<title>${esc(title)} · Admin Pitches</title>
+<link rel="stylesheet" href="/static/css/site.css">
+<link rel="stylesheet" href="/static/css/community.css">
+<link rel="stylesheet" href="/static/css/pitches.css">
+</head><body>`;
+}
+
+function adminNav(user) {
+ return `<header class="site-header">
+ <a href="/" class="brand"><span class="brand-mark">▲</span> Admin</a>
+ <nav>
+ <a href="/admin">Overview</a>
+ <a href="/admin/pitches"><strong>Pitches</strong></a>
+ <a href="/marketplace">Marketplace</a>
+ <a href="/for-marketers">B2B</a>
+ </nav>
+ <span class="user-pill">@${esc(user?.handle || user?.email || 'admin')}</span>
+ </header>`;
+}
+
+function renderPitchesIndex({ user, rows, filters }) {
+ const cards = rows.map(r => {
+ const scoreClass = r.marketing_score == null ? 'unscored' :
+ r.marketing_score < 50 ? 'bad' :
+ r.marketing_score < 70 ? 'mid' : 'good';
+ const shot = r.screenshot_path ? esc(r.screenshot_path) : null;
+ return `<a class="pitch-card pitch-${scoreClass}" href="/admin/pitches/${r.id}">
+ <div class="pitch-shot">${shot ? `<img src="${shot}" alt="" loading="lazy">` : '<span class="no-shot">no audit yet</span>'}</div>
+ <div class="pitch-meta">
+ <strong>${esc(r.name)}</strong>
+ <small>${esc(r.category.replace(/_/g,' '))} · ${esc(r.city || '')}, ${esc(r.state || '')}</small>
+ <div class="pitch-stats">
+ <span class="score">${r.marketing_score == null ? '—' : r.marketing_score}/100</span>
+ <span>📧 ${r.email_count}</span>
+ <span>🎨 ${r.mockup_count}/3</span>
+ </div>
+ ${r.email ? `<small class="primary-email">✓ ${esc(r.email)}</small>` : '<small class="no-email">no email yet</small>'}
+ </div>
+ </a>`;
+ }).join('');
+
+ const states = ['','CA','NY','TX','FL','IL','WA','CO','GA','MA','PA'];
+ const cats = ['','vet_clinic','emergency_vet','pet_store','shelter','breeder','trainer','groomer','boarding','dog_park'];
+
+ return head('Pitches') + adminNav(user) + `
+<main class="container">
+ <h1>Vet Site Upgrade — pitch queue</h1>
+ <p class="muted">Click any card to view the audit, mockups, and one-click pitch composer. ${rows.length} businesses match your filters.</p>
+ <form class="filters" method="GET">
+ <select name="state">${states.map(s => `<option value="${s}" ${filters.state===s?'selected':''}>${s||'All states'}</option>`).join('')}</select>
+ <select name="category">${cats.map(c => `<option value="${c}" ${filters.category===c?'selected':''}>${c?c.replace(/_/g,' '):'All categories'}</option>`).join('')}</select>
+ <select name="max_score"><option value="">Any score</option>${[40,50,60,70,80].map(n=>`<option value="${n}" ${String(filters.max_score)===String(n)?'selected':''}>≤${n}/100</option>`).join('')}</select>
+ <label class="ck"><input type="checkbox" name="has_email" value="1" ${filters.has_email==='1'?'checked':''}> Has email</label>
+ <button type="submit">Filter</button>
+ </form>
+ ${rows.length ? `<section class="pitch-grid">${cards}</section>` : '<p class="empty">No matches.</p>'}
+</main></body></html>`;
+}
+
+function renderPitchDetail({ user, biz, audit, mockups, emails }) {
+ const sentEmails = emails || [];
+ const primaryEmail = biz.email || sentEmails[0]?.email || '';
+ return head(`Pitch: ${biz.name}`) + adminNav(user) + `
+<main class="container">
+ <a href="/admin/pitches" class="back">← all pitches</a>
+ <h1>${esc(biz.name)}</h1>
+ <p class="muted">${esc(biz.category.replace(/_/g,' '))} · ${esc(biz.city || '')}, ${esc(biz.state || '')} ${biz.zip ? '· ' + esc(biz.zip) : ''} · <a href="${esc(biz.website || '#')}" target="_blank" rel="nofollow noopener">${esc(biz.website)}</a></p>
+
+ <section class="pitch-detail-grid">
+ <article>
+ <h2>Their site today</h2>
+ ${audit?.screenshot_path
+ ? `<img class="pitch-shot-big" src="${esc(audit.screenshot_path)}" alt="">`
+ : `<p class="empty">No audit yet. Run one via the audit job and refresh.</p>`}
+ ${audit ? `<aside class="audit-summary"><strong>Score: ${audit.marketing_score ?? '—'}/100</strong>${audit.suggestions?.length ? `<ul>${audit.suggestions.map(s=>`<li>${esc(s)}</li>`).join('')}</ul>` : ''}</aside>` : ''}
+ </article>
+
+ <article>
+ <h2>Mockups we made (${mockups.length}/3)</h2>
+ ${mockups.length ? `<div class="mockup-row">${mockups.map(m => `
+ <a class="mockup-thumb" href="${esc(m.html_path || '#')}" target="_blank">
+ <img src="${esc(m.screenshot_path)}" alt="${esc(m.template_label)}">
+ <small>${esc(m.template_label)} (variant ${esc(m.variant)})</small>
+ </a>`).join('')}</div>` : '<p class="empty">No mockups yet — generate them via the audit job (auto when score < 60).</p>'}
+ </article>
+ </section>
+
+ <section class="emails-section">
+ <h2>Emails we have</h2>
+ ${sentEmails.length
+ ? `<table class="biz-table"><thead><tr><th>Email</th><th>Kind</th><th>Source</th><th>Method</th></tr></thead><tbody>${sentEmails.map(e=>`<tr><td><a href="mailto:${esc(e.email)}">${esc(e.email)}</a></td><td>${esc(e.email_kind || '')}</td><td><a href="${esc(e.source_url)}" target="_blank" rel="nofollow noopener">${esc(stripScheme(e.source_url).slice(0,40))}</a></td><td>${esc(e.source_method)}</td></tr>`).join('')}</tbody></table>`
+ : '<p class="empty">No emails harvested for this business yet.</p>'}
+ </section>
+
+ <section class="composer">
+ <h2>Send pitch</h2>
+ <form id="composer" onsubmit="event.preventDefault();const fd=new FormData(this);const obj=Object.fromEntries(fd);fetch('/admin/pitches/${biz.id}/send',{method:'POST',headers:{'content-type':'application/json'},body:JSON.stringify(obj)}).then(r=>r.json()).then(j=>{document.getElementById('send-result').innerHTML=j.ok?('<p class=ok>✓ Sent: '+j.subject+'</p>'):('<p style=color:#c0382b>✗ '+(j.error||'failed')+'</p>')});">
+ <label>To <input name="to_email" type="email" value="${esc(primaryEmail)}" required ${primaryEmail?'':'placeholder="No email harvested — paste one"'}></label>
+ <label>Template <select name="template">
+ <option value="upgrade_low_score">Site upgrade — low audit score (under $499)</option>
+ <option value="upgrade_no_booking">Site upgrade — emphasize online booking lift</option>
+ <option value="upgrade_no_schema">Site upgrade — schema.org / local-pack visibility</option>
+ <option value="upgrade_no_mobile">Site upgrade — mobile-broken focus</option>
+ <option value="generic_audit_results">Just send the audit (no upsell)</option>
+ </select></label>
+ <details>
+ <summary>Preview body</summary>
+ <pre id="preview" class="preview"></pre>
+ </details>
+ <button type="submit">Send via George (Gmail)</button>
+ <div id="send-result"></div>
+ </form>
+ </section>
+</main>
+<script>
+ // Live preview
+ const PREVIEWS = ${JSON.stringify(buildPreviews(biz, audit, mockups))};
+ const sel = document.querySelector('select[name=template]');
+ const pre = document.getElementById('preview');
+ function refresh() { pre.textContent = PREVIEWS[sel.value] || ''; }
+ sel.addEventListener('change', refresh); refresh();
+</script>
+</body></html>`;
+}
+
+function adminLoginPage() {
+ return `<!doctype html><html><head><meta charset="utf-8"><title>Admin login</title>
+ <link rel="stylesheet" href="/static/css/site.css"><link rel="stylesheet" href="/static/css/community.css"></head>
+ <body><main class="container narrow">
+ <h1>Admin only</h1>
+ <p class="muted">Log in with the admin account to access the pitch dashboard.</p>
+ <p><a href="/login?next=/admin/pitches">Log in →</a></p>
+ </main></body></html>`;
+}
+
+// ── Pitch templates ───────────────────────────────────────────────────────
+function subjectFor(template, biz) {
+ const SUBJECTS = {
+ upgrade_low_score: `${biz.name} — quick site notes (took 30 seconds)`,
+ upgrade_no_booking: `${biz.name} — adding online booking would help`,
+ upgrade_no_schema: `${biz.name} — you're invisible to Google's pet-clinic search`,
+ upgrade_no_mobile: `Your site looks broken on phones (60% of pet-owner searches)`,
+ generic_audit_results: `Free site audit for ${biz.name} (no pitch, just findings)`,
+ };
+ return SUBJECTS[template] || SUBJECTS.upgrade_low_score;
+}
+
+function bodyFor(template, biz, audit, mockups) {
+ const previews = buildPreviews(biz, audit, mockups);
+ return previews[template] || previews.upgrade_low_score;
+}
+
+function buildPreviews(biz, audit, mockups) {
+ const score = audit?.marketing_score ?? '—';
+ const top3 = (audit?.suggestions || []).slice(0, 3).map(s => ` • ${s}`).join('\n');
+ const mkLine = mockups.length ? `\nWe rebuilt your site in 3 styles — see them at:\nhttps://animalsdirectory.com/admin/pitches/${biz.id}\n` : '';
+ const sig = `\n\n— Steve at AnimalsDirectory\nhttps://animalsdirectory.com\n(opt out: reply STOP)`;
+ return {
+ upgrade_low_score: `Hi ${biz.name} team —
+
+We audit pet-business websites for the AnimalsDirectory directory. Yours scored ${score}/100. Quick notes:
+
+${top3 || ' • (audit pending)'}
+${mkLine}
+We do site rebuilds for vet clinics and pet businesses for $499 one-time + $49/mo hosting. 7-day delivery.
+
+If that's interesting, hit reply.${sig}`,
+ upgrade_no_booking: `Hi ${biz.name} team —
+
+Quick observation: your site doesn't have an online-booking link. Pet clinics that added one in 2025 saw ~15% more new-patient intake (data from PetDesk + Vetstoria customer studies).
+${mkLine}
+We rebuild vet sites with online booking baked in for $499 one-time + $49/mo. 7-day turnaround.
+
+Reply if you want details.${sig}`,
+ upgrade_no_schema: `Hi ${biz.name} team —
+
+Your site is missing schema.org markup, which is what Google uses to put pet clinics in the map "local pack" + service rich-results. Right now your competitors with proper markup show up first.
+${mkLine}
+$499 one-time gets you a fully-marked-up rebuild that ranks for "vet near me" + service queries.${sig}`,
+ upgrade_no_mobile: `Hi ${biz.name} team —
+
+Your home page doesn't have a mobile viewport tag, which means it renders broken on phones. ~60% of pet-owner searches happen on mobile.
+${mkLine}
+$499 one-time gets a responsive rebuild (and we host it for $49/mo). 7 days.${sig}`,
+ generic_audit_results: `Hi ${biz.name} team —
+
+We ran a free audit on your website for the AnimalsDirectory listing. Score: ${score}/100. Top three things to look at:
+
+${top3 || ' • (audit pending)'}
+
+No pitch — just sharing the findings. If you want help fixing any of it, reply.${sig}`,
+ };
+}
diff --git a/src/server/community.js b/src/server/community.js
new file mode 100644
index 0000000..788b3b0
--- /dev/null
+++ b/src/server/community.js
@@ -0,0 +1,215 @@
+// PawCircles — the community + marketplace surface.
+// Mounted by src/server/index.js as a sub-router.
+
+import express from 'express';
+import { pool, many, one } from '../lib/db.js';
+import { attachUser, requireUser, signup, login, logout, userCanSeeListing } from '../lib/auth.js';
+import { renderSignup, renderLogin, renderMarketplace, renderListing, renderListingNew, renderCircle, renderMyCircles, renderMarketingFirms } from './community_render.js';
+
+export const community = express.Router();
+
+community.use(attachUser);
+
+// ── auth pages ────────────────────────────────────────────────────────────
+community.get('/signup', (req, res) => res.send(renderSignup({ user: req.user })));
+community.get('/login', (req, res) => res.send(renderLogin({ user: req.user })));
+community.post('/auth/signup', signup);
+community.post('/auth/login', login);
+community.post('/auth/logout', logout);
+
+// ── marketplace ───────────────────────────────────────────────────────────
+community.get('/marketplace', async (req, res) => {
+ const { type, zip, species, q } = req.query;
+ const params = []; const where = [`l.status = 'active'`];
+ if (type) { params.push(type); where.push(`l.listing_type = $${params.length}`); }
+ if (zip) { params.push(zip); where.push(`l.zip = $${params.length}`); }
+ if (species) { params.push(species); where.push(`l.species = $${params.length}`); }
+ if (q) { params.push(`%${q}%`); where.push(`(l.title ILIKE $${params.length} OR l.body ILIKE $${params.length})`); }
+ const sql = `
+ SELECT l.id, l.listing_type, l.title, l.body, l.zip, l.city, l.state, l.price_cents,
+ l.species, l.photo_urls, l.bumped_at, l.view_count, l.reaction_count, l.comment_count,
+ l.visibility, l.app_user_id, l.visible_circles,
+ u.handle AS author_handle
+ FROM marketplace_listings l
+ JOIN app_users u ON u.id = l.app_user_id
+ WHERE ${where.join(' AND ')}
+ ORDER BY l.bumped_at DESC
+ LIMIT 200`;
+ const listings = await many(sql, params);
+ const filtered = listings.filter(l => userCanSeeListingSync(req.user, l)).slice(0, 100);
+ res.send(renderMarketplace({ user: req.user, listings: filtered, filters: { type, zip, species, q } }));
+});
+
+community.get('/marketplace/new', (req, res) => {
+ if (!req.user) return res.redirect('/signup?next=/marketplace/new');
+ res.send(renderListingNew({ user: req.user }));
+});
+
+community.post('/marketplace/new', requireUser, async (req, res) => {
+ const { listing_type, title, body, zip, city, state, species, breed_id, price_cents,
+ contact_email, contact_phone, visibility, photo_urls } = req.body || {};
+ if (!listing_type || !title || !zip) return res.status(400).json({ error: 'listing_type, title, zip required' });
+ if (!/^[0-9]{5}$/.test(zip)) return res.status(400).json({ error: 'zip must be 5 digits' });
+ const r = await one(`
+ INSERT INTO marketplace_listings
+ (app_user_id, listing_type, title, body, zip, city, state, species, breed_id, price_cents,
+ contact_email, contact_phone, visibility, photo_urls)
+ VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
+ RETURNING id`,
+ [req.user.id, listing_type, title, body || null, zip, city || null,
+ (state||'').toUpperCase().slice(0,2) || null,
+ species || null, breed_id || null,
+ price_cents ? parseInt(price_cents, 10) : null,
+ contact_email || req.user.email, contact_phone || null,
+ visibility || req.user.visibility_default || 'home_zip_25mi',
+ Array.isArray(photo_urls) ? photo_urls : []]);
+ res.json({ ok: true, id: r.id, url: `/marketplace/${r.id}` });
+});
+
+community.get('/marketplace/:id', async (req, res) => {
+ const id = parseInt(req.params.id, 10);
+ if (!Number.isFinite(id)) return res.status(404).send('not found');
+ const listing = await one(`
+ SELECT l.*, u.handle AS author_handle
+ FROM marketplace_listings l
+ JOIN app_users u ON u.id = l.app_user_id
+ WHERE l.id = $1`, [id]);
+ if (!listing || listing.status === 'removed') return res.status(404).send('not found');
+ if (!userCanSeeListingSync(req.user, listing)) {
+ return res.status(403).send('You don\'t have access to this listing — it\'s scoped to a different community.');
+ }
+ // Fire-and-forget view increment
+ pool.query(`UPDATE marketplace_listings SET view_count = view_count + 1 WHERE id = $1`, [id]).catch(() => {});
+ const comments = await many(`
+ SELECT c.id, c.body, c.created_at, u.handle FROM comments c
+ JOIN app_users u ON u.id = c.app_user_id
+ WHERE c.parent_type = 'listing' AND c.parent_id = $1 AND c.status = 'active'
+ ORDER BY c.created_at ASC`, [id]);
+ res.send(renderListing({ user: req.user, listing, comments }));
+});
+
+community.post('/marketplace/:id/comment', requireUser, async (req, res) => {
+ const id = parseInt(req.params.id, 10);
+ if (!Number.isFinite(id)) return res.status(400).json({ error: 'bad id' });
+ const { body } = req.body || {};
+ if (!body || body.trim().length < 1) return res.status(400).json({ error: 'body required' });
+ // Access-check: you can only comment on listings you can see.
+ const listing = await one(
+ `SELECT id, status, visibility, zip, state, app_user_id, visible_circles
+ FROM marketplace_listings WHERE id = $1`, [id]);
+ if (!listing || listing.status === 'removed') return res.status(404).json({ error: 'not found' });
+ if (!userCanSeeListingSync(req.user, listing)) {
+ return res.status(403).json({ error: 'no access to this listing' });
+ }
+ await pool.query(
+ `INSERT INTO comments (parent_type, parent_id, app_user_id, body) VALUES ('listing', $1, $2, $3)`,
+ [id, req.user.id, body.trim().slice(0, 4000)]
+ );
+ await pool.query(
+ `UPDATE marketplace_listings SET comment_count = comment_count + 1 WHERE id = $1`, [id]
+ );
+ res.json({ ok: true });
+});
+
+// ── circles ───────────────────────────────────────────────────────────────
+community.get('/circles', async (req, res) => {
+ const mine = req.user ? await many(`
+ SELECT c.*, m.role FROM circle_memberships m
+ JOIN circles c ON c.id = m.circle_id
+ WHERE m.app_user_id = $1 ORDER BY c.member_count DESC`, [req.user.id]) : [];
+ const popular = await many(`
+ SELECT * FROM circles WHERE member_count > 0 ORDER BY member_count DESC LIMIT 20`);
+ res.send(renderMyCircles({ user: req.user, mine, popular }));
+});
+
+community.get('/circles/:slug', async (req, res) => {
+ const c = await one(`SELECT * FROM circles WHERE slug = $1`, [req.params.slug]);
+ if (!c) return res.status(404).send('circle not found');
+ // Candidate listings tied to this circle (same zip/state OR explicitly listed).
+ // We include the visibility fields and post-filter through the same gate the
+ // marketplace + detail endpoints use, so circle pages can never leak a
+ // listing the viewer wouldn't otherwise be able to see.
+ const candidates = await many(`
+ SELECT l.id, l.listing_type, l.title, l.body, l.zip, l.city, l.state, l.price_cents,
+ l.species, l.photo_urls, l.bumped_at, l.view_count, l.reaction_count, l.comment_count,
+ l.visibility, l.app_user_id, l.visible_circles,
+ u.handle AS author_handle
+ FROM marketplace_listings l
+ JOIN app_users u ON u.id = l.app_user_id
+ WHERE l.status = 'active'
+ AND ($1 = ANY(l.visible_circles) OR l.zip = $2 OR ($3::text IS NOT NULL AND l.state = $3))
+ ORDER BY l.bumped_at DESC LIMIT 200`,
+ [c.id, c.zip || '', c.state]);
+ const listings = candidates.filter(l => userCanSeeListingSync(req.user, l)).slice(0, 50);
+ res.send(renderCircle({ user: req.user, circle: c, listings }));
+});
+
+community.post('/circles/:slug/join', requireUser, async (req, res) => {
+ const c = await one(`SELECT id FROM circles WHERE slug = $1`, [req.params.slug]);
+ if (!c) return res.status(404).json({ error: 'circle not found' });
+ const r = await pool.query(
+ `INSERT INTO circle_memberships (circle_id, app_user_id) VALUES ($1, $2) ON CONFLICT DO NOTHING RETURNING 1`,
+ [c.id, req.user.id]);
+ if (r.rowCount) await pool.query(`UPDATE circles SET member_count = member_count + 1 WHERE id = $1`, [c.id]);
+ res.json({ ok: true });
+});
+
+// ── B2B "marketing-firm data" landing + order intent ──────────────────────
+community.get('/for-marketers', async (_req, res) => {
+ const stats = await one(`
+ SELECT
+ (SELECT COUNT(*) FROM businesses) AS total_businesses,
+ (SELECT COUNT(*) FROM businesses WHERE email IS NOT NULL) AS w_email,
+ (SELECT COUNT(*) FROM business_emails) AS email_rows,
+ (SELECT COUNT(DISTINCT state) FROM businesses) AS states_covered`);
+ const byCategory = await many(`
+ SELECT category, COUNT(*) AS total, COUNT(*) FILTER (WHERE email IS NOT NULL) AS w_email
+ FROM businesses GROUP BY category ORDER BY total DESC`);
+ res.send(renderMarketingFirms({ stats, byCategory }));
+});
+
+community.post('/for-marketers/order', async (req, res) => {
+ const { full_name, company, email, phone, list_label, filters_json, tier } = req.body || {};
+ if (!full_name || !email || !list_label) return res.status(400).json({ error: 'name, email, list_label required' });
+ const tierMap = { one_time: 49900, monthly_refresh: 99900, quarterly: 79900 };
+ const cents = tierMap[tier] || 49900;
+ // Estimate row count from filters (best effort)
+ let estimated = null;
+ try {
+ const filt = typeof filters_json === 'string' ? JSON.parse(filters_json) : (filters_json || {});
+ const where = [`opt_out_flag = FALSE`];
+ const params = [];
+ if (filt.state) { params.push(filt.state); where.push(`state = $${params.length}`); }
+ if (filt.category) { params.push(filt.category); where.push(`category = $${params.length}`); }
+ if (filt.has_email) where.push(`email IS NOT NULL`);
+ const cnt = await one(`SELECT COUNT(*)::int AS n FROM businesses WHERE ${where.join(' AND ')}`, params);
+ estimated = cnt?.n || 0;
+ } catch {}
+ const r = await one(
+ `INSERT INTO data_orders (full_name, company, email, phone, list_label, filters_json, estimated_rows, tier, amount_cents, ip, user_agent)
+ VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
+ RETURNING id`,
+ [full_name, company || null, email.toLowerCase(), phone || null, list_label,
+ typeof filters_json === 'string' ? filters_json : JSON.stringify(filters_json || {}),
+ estimated, tier || 'one_time', cents, req.ip || null, req.headers['user-agent'] || null]
+ );
+ res.json({ ok: true, order_id: r.id, estimated_rows: estimated, amount_cents: cents });
+});
+
+// ── helper that doesn't await DB (we already have what we need on the row) ─
+function userCanSeeListingSync(user, listing) {
+ if (listing.visibility === 'public') return true;
+ if (!user) return false;
+ if (listing.app_user_id === user.id) return true;
+ if (listing.visibility === 'home_zip') return user.home_zip === listing.zip;
+ if (listing.visibility === 'home_zip_25mi') {
+ return user.home_zip === listing.zip
+ || (user.approved_zips || []).includes(listing.zip)
+ || (user.home_zip && listing.zip && String(user.home_zip).slice(0,3) === String(listing.zip).slice(0,3));
+ }
+ if (listing.visibility === 'statewide') return user.home_state === listing.state;
+ if (listing.visibility === 'approved_circles_only') {
+ return (listing.visible_circles || []).some(c => (user.approved_circles || []).includes(Number(c)));
+ }
+ return false;
+}
diff --git a/src/server/community_render.js b/src/server/community_render.js
new file mode 100644
index 0000000..686303d
--- /dev/null
+++ b/src/server/community_render.js
@@ -0,0 +1,270 @@
+// PawCircles renderers — string-template HTML, no view engine.
+// Reuses the site shell for header/footer/CSS so styling stays consistent.
+
+function esc(s) {
+ return String(s ?? '').replace(/[&<>"']/g, c => ({'&':'&','<':'<','>':'>','"':'"',"'":'''}[c]));
+}
+
+function head(title) {
+ return `<!doctype html><html lang="en"><head>
+<meta charset="utf-8">
+<meta name="viewport" content="width=device-width,initial-scale=1">
+<title>${esc(title)} · PawCircles · AnimalsDirectory</title>
+<link rel="stylesheet" href="/static/css/site.css">
+<link rel="stylesheet" href="/static/css/community.css">
+<script defer src="/static/js/sort-table.js"></script>
+<script defer src="/static/js/geo.js"></script>
+</head><body>`;
+}
+
+function nav(user) {
+ return `<header class="site-header">
+ <a href="/" class="brand"><span class="brand-mark">▲</span> AnimalsDirectory</a>
+ <nav>
+ <a href="/breeds">Breeds</a>
+ <a href="/vets">Vets</a>
+ <a href="/marketplace"><strong>Marketplace</strong></a>
+ <a href="/circles">Circles</a>
+ <a href="/for-marketers"><small>For Marketers</small></a>
+ </nav>
+ ${user
+ ? `<a class="cta" href="/marketplace/new">Post →</a>
+ <span class="user-pill"><a href="/circles">@${esc(user.handle)}</a></span>`
+ : `<a class="cta" href="/signup">Sign up</a>
+ <a class="login-link" href="/login">Log in</a>`}
+</header>`;
+}
+
+function footer() {
+ return `<footer class="site-footer">
+ <small>© AnimalsDirectory · PawCircles · Source-traceable, opt-out honored.
+ Maps © <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors.
+ <a href="/admin">Admin</a></small>
+</footer>`;
+}
+
+// ── auth pages ────────────────────────────────────────────────────────────
+export function renderSignup({ user }) {
+ if (user) return head('Already signed in') + nav(user) + `<main class="container"><h1>You're signed in</h1><p><a href="/marketplace">Browse marketplace</a> or <a href="/circles">your circles</a>.</p></main>` + footer() + '</body></html>';
+ return head('Join PawCircles') + nav(null) + `
+<main class="container narrow">
+ <h1>Join PawCircles</h1>
+ <p class="muted">Free. Your ZIP unlocks your local pet community — vets, marketplace, lost-pet board, recommendations.</p>
+ <form class="auth-form" onsubmit="event.preventDefault();const fd=new FormData(this);fetch('/auth/signup',{method:'POST',headers:{'content-type':'application/json'},body:JSON.stringify(Object.fromEntries(fd))}).then(r=>r.json()).then(j=>{if(j.ok){location.href='/circles'}else{this.querySelector('.err').textContent=j.error||'signup failed'}});">
+ <label>Email <input name="email" type="email" required></label>
+ <label>Password <input name="password" type="password" minlength="8" required></label>
+ <label>Display name <input name="full_name"></label>
+ <label>Handle (@yourname) <input name="handle" pattern="[a-zA-Z0-9_]{3,20}" placeholder="optional — we'll generate one"></label>
+ <label>Home ZIP <input name="home_zip" pattern="[0-9]{5}" required></label>
+ <label>City (optional) <input name="home_city"></label>
+ <label>State (2 letters) <input name="home_state" pattern="[A-Za-z]{2}" maxlength="2" style="width:5em"></label>
+ <fieldset class="checks"><legend>I have</legend>
+ <label class="ck"><input type="checkbox" name="species_pets" value="dog">🐕 Dog</label>
+ <label class="ck"><input type="checkbox" name="species_pets" value="cat">🐈 Cat</label>
+ <label class="ck"><input type="checkbox" name="species_pets" value="bird">🦜 Bird</label>
+ <label class="ck"><input type="checkbox" name="species_pets" value="reptile">🦎 Reptile</label>
+ <label class="ck"><input type="checkbox" name="species_pets" value="small_mammal">🐰 Small mammal</label>
+ </fieldset>
+ <button type="submit">Create account</button>
+ <p class="err" style="color:#c0382b"></p>
+ </form>
+ <p class="muted small">Already have an account? <a href="/login">Log in</a>.</p>
+</main>` + footer() + '</body></html>';
+}
+
+export function renderLogin({ user }) {
+ if (user) return head('Already signed in') + nav(user) + `<main class="container"><h1>You're signed in.</h1></main>` + footer() + '</body></html>';
+ return head('Log in') + nav(null) + `
+<main class="container narrow">
+ <h1>Log in</h1>
+ <form class="auth-form" onsubmit="event.preventDefault();const fd=new FormData(this);fetch('/auth/login',{method:'POST',headers:{'content-type':'application/json'},body:JSON.stringify(Object.fromEntries(fd))}).then(r=>r.json()).then(j=>{if(j.ok){location.href='/circles'}else{this.querySelector('.err').textContent=j.error||'login failed'}});">
+ <label>Email <input name="email" type="email" required></label>
+ <label>Password <input name="password" type="password" required></label>
+ <button type="submit">Log in</button>
+ <p class="err" style="color:#c0382b"></p>
+ </form>
+ <p class="muted small">No account? <a href="/signup">Sign up</a>.</p>
+</main>` + footer() + '</body></html>';
+}
+
+// ── marketplace ───────────────────────────────────────────────────────────
+const TYPE_LABELS = {
+ lost_pet:'🆘 Lost pet', found_pet:'👀 Found pet', adoption:'❤️ Adoption',
+ for_sale:'💰 For sale', wanted:'🙏 Wanted', service_offered:'🦴 Service offered',
+ service_wanted:'🔎 Service wanted', event:'📅 Event', recommendation:'⭐ Recommendation',
+ question:'❓ Question', business_promo:'📣 Business promo', free_to_good_home:'🐾 Free to good home',
+};
+
+export function renderMarketplace({ user, listings, filters }) {
+ const cards = listings.map(l => `
+ <a class="lst-card" href="/marketplace/${l.id}">
+ <div class="lst-meta">
+ <span class="lst-type">${esc(TYPE_LABELS[l.listing_type] || l.listing_type)}</span>
+ <span class="lst-loc">${esc(l.city || '')}${l.state ? ', ' + esc(l.state) : ''} · ${esc(l.zip)}</span>
+ </div>
+ <h3>${esc(l.title)}</h3>
+ <p class="lst-body">${esc((l.body || '').slice(0, 180))}${(l.body || '').length > 180 ? '…' : ''}</p>
+ <div class="lst-foot">
+ ${l.price_cents != null ? `<strong class="lst-price">$${(l.price_cents/100).toFixed(2)}</strong>` : ''}
+ <span class="lst-author">by @${esc(l.author_handle)}</span>
+ <span class="lst-stats">👁 ${l.view_count || 0} · 💬 ${l.comment_count || 0}</span>
+ </div>
+ </a>`).join('');
+ const typeOpts = Object.entries(TYPE_LABELS).map(([k,v]) => `<option value="${k}" ${filters.type===k?'selected':''}>${esc(v)}</option>`).join('');
+ return head('Marketplace') + nav(user) + `
+<main class="container">
+ <h1>Marketplace</h1>
+ <p class="muted">${listings.length} listings ${filters.zip ? `near ${esc(filters.zip)}` : 'visible to you'}</p>
+ <form class="filters" method="GET">
+ <select name="type"><option value="">All types</option>${typeOpts}</select>
+ <input name="zip" placeholder="ZIP" value="${esc(filters.zip || '')}" pattern="[0-9]{5}">
+ <select name="species"><option value="">Any species</option>${['dog','cat','bird','reptile','small_mammal','exotic'].map(s => `<option value="${s}" ${filters.species===s?'selected':''}>${s.replace('_',' ')}</option>`).join('')}</select>
+ <input name="q" placeholder="Search…" value="${esc(filters.q || '')}">
+ <button type="submit">Filter</button>
+ ${user ? `<a class="cta-small" href="/marketplace/new">+ Post listing</a>` : `<a class="cta-small" href="/signup?next=/marketplace/new">Sign up to post</a>`}
+ </form>
+ ${cards ? `<section class="lst-grid">${cards}</section>` : `<p class="empty">No listings match — be the first! ${user ? `<a href="/marketplace/new">Post one</a>.` : `<a href="/signup">Sign up</a>.`}</p>`}
+</main>` + footer() + '</body></html>';
+}
+
+export function renderListingNew({ user }) {
+ const typeOpts = Object.entries(TYPE_LABELS).map(([k,v]) => `<option value="${k}">${esc(v)}</option>`).join('');
+ return head('Post a listing') + nav(user) + `
+<main class="container narrow">
+ <h1>Post a new listing</h1>
+ <form class="auth-form" onsubmit="event.preventDefault();const fd=new FormData(this);const obj=Object.fromEntries(fd);fetch('/marketplace/new',{method:'POST',headers:{'content-type':'application/json'},body:JSON.stringify(obj)}).then(r=>r.json()).then(j=>{if(j.ok){location.href=j.url}else{this.querySelector('.err').textContent=j.error||'failed'}});">
+ <label>Type <select name="listing_type" required>${typeOpts}</select></label>
+ <label>Title <input name="title" required maxlength="200"></label>
+ <label>Description <textarea name="body" rows="5"></textarea></label>
+ <div class="row2">
+ <label>ZIP <input name="zip" pattern="[0-9]{5}" required value="${esc(user.home_zip || '')}"></label>
+ <label>City <input name="city" value="${esc(user.home_city || '')}"></label>
+ <label>State <input name="state" pattern="[A-Za-z]{2}" maxlength="2" style="width:5em" value="${esc(user.home_state || '')}"></label>
+ </div>
+ <div class="row2">
+ <label>Species <select name="species"><option value="">—</option>${['dog','cat','bird','reptile','small_mammal','exotic'].map(s => `<option value="${s}">${s.replace('_',' ')}</option>`).join('')}</select></label>
+ <label>Price (USD, optional) <input name="price_cents" type="number" min="0" step="1" placeholder="e.g. 1500 = $15.00"></label>
+ </div>
+ <label>Visibility <select name="visibility">
+ <option value="home_zip_25mi" ${user.visibility_default==='home_zip_25mi'?'selected':''}>My ZIP + ~25 miles</option>
+ <option value="home_zip" ${user.visibility_default==='home_zip'?'selected':''}>My ZIP only</option>
+ <option value="statewide" ${user.visibility_default==='statewide'?'selected':''}>Statewide</option>
+ <option value="public" ${user.visibility_default==='public'?'selected':''}>Public (anyone)</option>
+ <option value="approved_circles_only" ${user.visibility_default==='approved_circles_only'?'selected':''}>Only my approved circles</option>
+ </select></label>
+ <label>Contact email (defaults to your account email) <input name="contact_email" type="email" placeholder="${esc(user.email)}"></label>
+ <label>Contact phone (optional) <input name="contact_phone" type="tel"></label>
+ <button type="submit">Post listing</button>
+ <p class="err" style="color:#c0382b"></p>
+ </form>
+</main>` + footer() + '</body></html>';
+}
+
+export function renderListing({ user, listing, comments }) {
+ return head(listing.title) + nav(user) + `
+<main class="container narrow">
+ <article class="listing-detail">
+ <span class="lst-type">${esc(TYPE_LABELS[listing.listing_type] || listing.listing_type)}</span>
+ <h1>${esc(listing.title)}</h1>
+ <p class="muted">by @${esc(listing.author_handle)} · ${esc(listing.city || '')}${listing.state ? ', ' + esc(listing.state) : ''} · ${esc(listing.zip)} · 👁 ${listing.view_count}</p>
+ ${listing.price_cents != null ? `<p class="big-price">$${(listing.price_cents/100).toFixed(2)}</p>` : ''}
+ ${listing.body ? `<div class="lst-body-full">${listing.body.split('\n\n').map(p => `<p>${esc(p)}</p>`).join('')}</div>` : ''}
+ ${listing.contact_email || listing.contact_phone ? `<aside class="contact-card"><strong>Contact</strong><br>${listing.contact_email ? `<a href="mailto:${esc(listing.contact_email)}">${esc(listing.contact_email)}</a><br>` : ''}${listing.contact_phone ? `<a href="tel:${esc(listing.contact_phone)}">${esc(listing.contact_phone)}</a>` : ''}</aside>` : ''}
+ </article>
+ <section class="comments">
+ <h2>${comments.length} comments</h2>
+ ${comments.map(c => `<div class="comment"><strong>@${esc(c.handle)}</strong> <small class="muted">${new Date(c.created_at).toLocaleString()}</small><p>${esc(c.body)}</p></div>`).join('')}
+ ${user
+ ? `<form class="comment-form" onsubmit="event.preventDefault();fetch('/marketplace/${listing.id}/comment',{method:'POST',headers:{'content-type':'application/json'},body:JSON.stringify({body:this.body.value})}).then(r=>r.json()).then(j=>{if(j.ok){location.reload()}});">
+ <textarea name="body" rows="3" placeholder="Add a comment…" required></textarea>
+ <button type="submit">Post comment</button>
+ </form>`
+ : `<p class="muted"><a href="/login">Log in</a> to comment.</p>`}
+ </section>
+</main>` + footer() + '</body></html>';
+}
+
+export function renderMyCircles({ user, mine, popular }) {
+ const ml = (c) => `<a class="circle-card" href="/circles/${esc(c.slug)}"><strong>${esc(c.name)}</strong><small>${c.member_count} member${c.member_count===1?'':'s'} · ${esc(c.kind)}</small></a>`;
+ return head('Your circles') + nav(user) + `
+<main class="container">
+ <h1>Circles</h1>
+ ${user ? `<section><h2>You're in</h2><div class="circle-grid">${mine.length ? mine.map(ml).join('') : `<p class="muted">You haven’t joined any circles yet.</p>`}</div></section>` : `<p class="muted"><a href="/signup">Sign up</a> to join your ZIP’s circle.</p>`}
+ <section><h2>Popular circles</h2><div class="circle-grid">${popular.length ? popular.map(ml).join('') : '<p class="muted">No circles yet.</p>'}</div></section>
+</main>` + footer() + '</body></html>';
+}
+
+export function renderCircle({ user, circle, listings }) {
+ const cards = listings.map(l => `
+ <a class="lst-card" href="/marketplace/${l.id}">
+ <span class="lst-type">${esc(TYPE_LABELS[l.listing_type] || l.listing_type)}</span>
+ <h3>${esc(l.title)}</h3>
+ <p class="lst-body">${esc((l.body || '').slice(0, 140))}${(l.body || '').length > 140 ? '…' : ''}</p>
+ <span class="lst-author">by @${esc(l.author_handle)}</span>
+ </a>`).join('');
+ return head(circle.name) + nav(user) + `
+<main class="container">
+ <h1>${esc(circle.name)}</h1>
+ <p class="muted">${esc(circle.kind)} circle · ${circle.member_count} members</p>
+ ${user ? `<form onsubmit="event.preventDefault();fetch('/circles/${esc(circle.slug)}/join',{method:'POST'}).then(r=>r.json()).then(_=>location.reload());"><button class="cta-small">Join this circle</button></form>` : ''}
+ ${cards ? `<section class="lst-grid">${cards}</section>` : '<p class="empty">No listings yet in this circle.</p>'}
+</main>` + footer() + '</body></html>';
+}
+
+// ── B2B marketing-firm landing ────────────────────────────────────────────
+export function renderMarketingFirms({ stats, byCategory }) {
+ const TIER_PRICE = { one_time: 499, monthly_refresh: 999, quarterly: 799 };
+ const catRows = byCategory.map(r => `<tr><td>${esc(r.category.replace(/_/g,' '))}</td><td data-sort="num">${r.total.toLocaleString()}</td><td data-sort="num">${r.w_email.toLocaleString()}</td><td data-sort="num">${(r.w_email / Math.max(r.total,1) * 100).toFixed(0)}%</td></tr>`).join('');
+ return head('For Marketing Firms') + nav(null) + `
+<main class="container">
+ <section class="hero" style="text-align:left">
+ <small style="color:#c97a3e;letter-spacing:.1em;text-transform:uppercase;font-weight:700">For B2B marketers</small>
+ <h1>The most current pet-business contact dataset in the U.S.</h1>
+ <p>Vet clinics, pet stores, shelters, breeders, groomers, boarding, dog parks — with addresses, phones, websites, and verified emails. Refreshed weekly.</p>
+ </section>
+
+ <section class="stats">
+ <div><strong>${(stats.total_businesses||0).toLocaleString()}</strong><span>businesses tracked</span></div>
+ <div><strong>${(stats.w_email||0).toLocaleString()}</strong><span>with primary email</span></div>
+ <div><strong>${(stats.email_rows||0).toLocaleString()}</strong><span>email rows (multi)</span></div>
+ <div><strong>${stats.states_covered||1}</strong><span>state${stats.states_covered===1?'':'s'} covered</span></div>
+ </section>
+
+ <h2>Coverage by category</h2>
+ <table data-sortable class="biz-table">
+ <thead><tr><th>Category</th><th data-sort="num">Total</th><th data-sort="num">With email</th><th data-sort="num">Email coverage</th></tr></thead>
+ <tbody>${catRows}</tbody>
+ </table>
+
+ <h2>Pricing</h2>
+ <section class="pricing">
+ <div class="tier"><strong>$${TIER_PRICE.one_time}</strong><span>One-time CSV</span><p>A frozen snapshot of any filtered slice. Delivered within 24h.</p></div>
+ <div class="tier featured"><strong>$${TIER_PRICE.monthly_refresh}</strong><span>Monthly refresh</span><p>Same slice, refreshed first of every month. Delta-only or full snapshot.</p></div>
+ <div class="tier"><strong>$${TIER_PRICE.quarterly}</strong><span>Quarterly</span><p>Quieter cadence, same slice. Good for vendor-management cycles.</p></div>
+ </section>
+
+ <h2>Order a sample</h2>
+ <form id="data-order" class="auth-form" onsubmit="event.preventDefault();const fd=new FormData(this);const obj=Object.fromEntries(fd);obj.filters_json={state:obj.state||null,category:obj.category||null,has_email:obj.has_email==='on'};fetch('/for-marketers/order',{method:'POST',headers:{'content-type':'application/json'},body:JSON.stringify(obj)}).then(r=>r.json()).then(j=>{if(j.ok){this.outerHTML='<p class=\\'ok\\'>Got it. Estimated '+j.estimated_rows.toLocaleString()+' rows. Order #'+j.order_id+'. We\\'ll email you a payment link.</p>'}});">
+ <div class="row2">
+ <label>Your name <input name="full_name" required></label>
+ <label>Company <input name="company"></label>
+ </div>
+ <div class="row2">
+ <label>Work email <input name="email" type="email" required></label>
+ <label>Phone <input name="phone" type="tel"></label>
+ </div>
+ <label>List label (e.g. "California vet clinics with email") <input name="list_label" required></label>
+ <div class="row2">
+ <label>State (optional, 2 letters) <input name="state" pattern="[A-Za-z]{2}" maxlength="2" style="width:6em"></label>
+ <label>Category <select name="category"><option value="">Any</option>${['vet_clinic','emergency_vet','pet_store','shelter','breeder','trainer','groomer','boarding','dog_park'].map(c=>`<option value="${c}">${c.replace(/_/g,' ')}</option>`).join('')}</select></label>
+ </div>
+ <label class="ck"><input type="checkbox" name="has_email" checked> Only include businesses with a verified email</label>
+ <label>Tier <select name="tier">
+ <option value="one_time">One-time ($${TIER_PRICE.one_time})</option>
+ <option value="monthly_refresh">Monthly refresh ($${TIER_PRICE.monthly_refresh}/mo)</option>
+ <option value="quarterly">Quarterly ($${TIER_PRICE.quarterly}/qtr)</option>
+ </select></label>
+ <button type="submit">Request quote</button>
+ </form>
+</main>` + footer() + '</body></html>';
+}
diff --git a/src/server/index.js b/src/server/index.js
index 19dc6fb..607c94e 100644
--- a/src/server/index.js
+++ b/src/server/index.js
@@ -26,6 +26,8 @@ import { log } from '../lib/log.js';
import { renderHome, renderBreedsIndex, renderBreed, renderCategory, renderBusiness, renderCityMap, renderLeadForm, renderAdmin, renderNotFound } from './render.js';
import { pickAdForPage, recordImpression, recordClick } from '../monetize/ad_engine.js';
import { matchLead } from '../monetize/match_engine.js';
+import { community } from './community.js';
+import { adminPitch } from './admin_pitch.js';
const __dirname = path.dirname(fileURLToPath(import.meta.url));
const PUBLIC_DIR = path.resolve(__dirname, '..', '..', 'public');
@@ -45,6 +47,13 @@ const CAT_TO_DB = {
app.get('/healthz', (_req, res) => res.json({ ok: true, ts: Date.now() }));
+// Mount PawCircles community + marketplace + B2B routes
+app.use(community);
+
+// Mount admin pitch dashboard (auth-gated by ADMIN_EMAIL inside the router)
+// Scope to /admin so the admin middleware only runs on admin routes.
+app.use('/admin', adminPitch);
+
// Near-me — used by /static/js/geo.js after browser geolocation resolves.
// Haversine in SQL with a bbox prefilter so we don't trig over every business.
app.get('/api/near-me', async (req, res) => {
← 9e16943 fix(map): stop near-me SQL from crashing, hide empty cats, a
·
back to Animals
·
fix: codex review findings — creds + URL parse + license reg bf214ed →