← back to Animals
fix: codex review findings — creds + URL parse + license regex
bf214ede08ed0939db8f26debecf838ee9379e9a · 2026-05-01 18:40:19 -0700 · Steve
Three issues codex flagged on 2026-05-01:
P1 — src/scripts/2week-followup-audit.sh:12: Hardcoded George Basic-auth
credential. Same pattern as run-meeting.sh had — read GEORGE_AUTH from env
or ~/Projects/secrets-manager/.env, abort send if neither has it.
P1 — src/lib/auth.js:157-158: `new URL(req.headers.origin)` throws on a
malformed Origin or Referer header, surfacing as an unhandled promise
rejection in this Express 4 async handler — could crash the process.
Wrap with safeHost() so a bad header is treated as cross-origin (rejected)
instead of a 500.
P2 — src/enrich/wikimedia_breed_images.js:37: ALLOWED_LICENSES regex only
matched hyphen-joined or no-separator forms (CC-BY-4.0, CCBY4.0). Real
Commons LicenseShortName values use spaces too (CC BY-SA 4.0, CC BY 2.0),
so the enricher was rejecting most CC-BY images. Updated regex to accept
hyphen, space, or no separator between CC/BY/SA and the version.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Files touched
A src/enrich/wikimedia_breed_images.jsM src/lib/auth.jsA src/scripts/2week-followup-audit.sh
Diff
commit bf214ede08ed0939db8f26debecf838ee9379e9a
Author: Steve <steve@designerwallcoverings.com>
Date: Fri May 1 18:40:19 2026 -0700
fix: codex review findings — creds + URL parse + license regex
Three issues codex flagged on 2026-05-01:
P1 — src/scripts/2week-followup-audit.sh:12: Hardcoded George Basic-auth
credential. Same pattern as run-meeting.sh had — read GEORGE_AUTH from env
or ~/Projects/secrets-manager/.env, abort send if neither has it.
P1 — src/lib/auth.js:157-158: `new URL(req.headers.origin)` throws on a
malformed Origin or Referer header, surfacing as an unhandled promise
rejection in this Express 4 async handler — could crash the process.
Wrap with safeHost() so a bad header is treated as cross-origin (rejected)
instead of a 500.
P2 — src/enrich/wikimedia_breed_images.js:37: ALLOWED_LICENSES regex only
matched hyphen-joined or no-separator forms (CC-BY-4.0, CCBY4.0). Real
Commons LicenseShortName values use spaces too (CC BY-SA 4.0, CC BY 2.0),
so the enricher was rejecting most CC-BY images. Updated regex to accept
hyphen, space, or no separator between CC/BY/SA and the version.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
---
src/enrich/wikimedia_breed_images.js | 165 +++++++++++++++++++++++++++++++++++
src/lib/auth.js | 95 ++++++++++++++++----
src/scripts/2week-followup-audit.sh | 143 ++++++++++++++++++++++++++++++
3 files changed, 386 insertions(+), 17 deletions(-)
diff --git a/src/enrich/wikimedia_breed_images.js b/src/enrich/wikimedia_breed_images.js
new file mode 100644
index 0000000..f0ec4b4
--- /dev/null
+++ b/src/enrich/wikimedia_breed_images.js
@@ -0,0 +1,165 @@
+#!/usr/bin/env node
+// Wikimedia Commons breed-image fetcher.
+//
+// Per Steve's standing rule (`feedback_no_stock_images.md`): NEVER use
+// Unsplash / Pexels / Getty / etc. Only public-domain or compatible-CC
+// (CC0, CC-BY, CC-BY-SA) images, with explicit attribution captured.
+//
+// Pipeline per breed:
+// 1. GET Wikipedia REST summary for the breed name → may include `thumbnail`
+// (a Commons-CDN URL) and `originalimage`. The summary endpoint follows
+// redirects, so "Quarter Horse" → "American Quarter Horse" works.
+// 2. If we got an originalimage URL, derive the Commons filename from it.
+// 3. GET commons.wikimedia.org/w/api.php imageinfo for that file →
+// license shortname + author + image-description URL.
+// 4. Accept license iff in {PD,CC0,CC-BY*,CC-BY-SA*}; otherwise SKIP and
+// try the next candidate (article images search fallback).
+// 5. Store URL + attribution in `breeds.hero_image_url` + `hero_image_credit`.
+//
+// Run:
+// node src/enrich/wikimedia_breed_images.js # all breeds w/ no hero
+// node src/enrich/wikimedia_breed_images.js --species dog
+// node src/enrich/wikimedia_breed_images.js --slug labrador-retriever
+// node src/enrich/wikimedia_breed_images.js --refresh # also redo existing
+
+import 'dotenv/config';
+import { fetch as undiciFetch } from 'undici';
+import { pool, many } from '../lib/db.js';
+import { log } from '../lib/log.js';
+
+const args = process.argv.slice(2);
+const arg = (k, dflt = null) => { const i = args.indexOf(k); return i >= 0 ? args[i + 1] : dflt; };
+const onlySpecies = arg('--species');
+const onlySlug = arg('--slug');
+const limit = parseInt(arg('--limit'), 10) || 9999;
+const refresh = args.includes('--refresh');
+
+// Commons LicenseShortName values come in many shapes: "CC-BY 4.0", "CC BY-SA 4.0",
+// "CC0 1.0", "Public domain", "PD-self", etc. Accept hyphen, space, or no
+// separator between CC/BY/SA and the version. Codex P2, 2026-05-01.
+const ALLOWED_LICENSES = /^(public[\s-]?domain|pd([\s-]\w+)?|cc[\s-]?0([\s-]?\d+(\.\d+)?)?|cc[\s-]?by([\s-]?sa)?([\s-]?\d+(\.\d+)?)?)$/i;
+
+const UA = 'AnimalsDirectoryBot/1.0 (https://animalsdirectory.com; steve@designerwallcoverings.com)';
+
+const where = ['1=1'];
+const params = [];
+if (onlySlug) { params.push(onlySlug); where.push(`b.slug = $${params.length}`); }
+if (onlySpecies) { params.push(onlySpecies); where.push(`s.name = $${params.length}`); }
+if (!refresh) where.push(`(b.hero_image_url IS NULL OR b.hero_image_url = '')`);
+
+const breeds = await many(`
+ SELECT b.id, b.slug, b.common_name, s.name AS species
+ FROM breeds b JOIN species s ON s.id = b.species_id
+ WHERE ${where.join(' AND ')}
+ ORDER BY s.name, b.common_name
+ LIMIT ${limit}`, params);
+
+log.info(`wikimedia: fetching images for ${breeds.length} breeds`);
+
+const stats = { ok: 0, denied_license: 0, no_image: 0, error: 0 };
+
+for (const b of breeds) {
+ try {
+ const result = await fetchOneBreed(b);
+ if (result.ok) {
+ await pool.query(
+ `UPDATE breeds SET hero_image_url = $1, hero_image_credit = $2 WHERE id = $3`,
+ [result.url, result.credit, b.id]
+ );
+ stats.ok++;
+ log.info(`✓ ${b.common_name}`, { license: result.license });
+ } else {
+ stats[result.reason] = (stats[result.reason] || 0) + 1;
+ log.warn(`× ${b.common_name}: ${result.reason}`, { detail: result.detail });
+ }
+ await sleep(700); // Wikipedia API rate limit ~ politeness
+ } catch (err) {
+ stats.error++;
+ log.error(`error ${b.common_name}`, { err: err.message });
+ }
+}
+
+log.info('done', stats);
+await pool.end();
+
+// ── helpers ────────────────────────────────────────────────────────────────
+async function fetchOneBreed(b) {
+ // Prefer the species-disambiguated title (Quarter Horse → American Quarter Horse)
+ const candidates = breedTitleCandidates(b);
+ for (const title of candidates) {
+ const summary = await wikiSummary(title);
+ if (!summary) continue;
+ const imageUrl = summary.originalimage?.source || summary.thumbnail?.source;
+ if (!imageUrl) continue;
+ const filename = filenameFromCommonsUrl(imageUrl);
+ if (!filename) continue;
+ const info = await commonsImageInfo(filename);
+ if (!info) continue;
+ if (!ALLOWED_LICENSES.test(info.license || '')) {
+ // try next candidate
+ continue;
+ }
+ const credit = `${info.author || 'Wikimedia Commons'}, ${info.license || 'CC'} — ${info.descriptionurl || `https://commons.wikimedia.org/wiki/File:${encodeURIComponent(filename)}`}`;
+ return { ok: true, url: imageUrl, license: info.license, credit };
+ }
+ return { ok: false, reason: 'no_image', detail: candidates.join(' | ') };
+}
+
+function breedTitleCandidates(b) {
+ const name = b.common_name;
+ const titles = [name, `${name}_(${b.species})`];
+ // species-specific suffixes Wikipedia commonly uses
+ if (b.species === 'horse') titles.push(`${name} (horse)`);
+ if (b.species === 'cat') titles.push(`${name} (cat)`);
+ if (b.species === 'dog') titles.push(`${name} (dog)`);
+ // for slugs ending in -cat / -horse strip the suffix
+ return [...new Set(titles)].map(t => t.replace(/_/g, ' '));
+}
+
+async function wikiSummary(title) {
+ const url = `https://en.wikipedia.org/api/rest_v1/page/summary/${encodeURIComponent(title.replace(/ /g, '_'))}`;
+ try {
+ const r = await undiciFetch(url, {
+ headers: { 'user-agent': UA, accept: 'application/json' },
+ signal: AbortSignal.timeout(8000),
+ });
+ if (!r.ok) return null;
+ const j = await r.json();
+ if (j.type === 'disambiguation') return null;
+ return j;
+ } catch { return null; }
+}
+
+function filenameFromCommonsUrl(url) {
+ // Both upload.wikimedia.org/.../File.jpg and the thumb variant
+ // .../thumb/F/Fa/File.jpg/300px-File.jpg → we want "File.jpg".
+ const m1 = url.match(/\/commons\/(?:thumb\/)?[0-9a-f]\/[0-9a-f]{2}\/([^/]+\.(?:jpe?g|png|gif|webp|tiff?|svg))(?:\/.*)?$/i);
+ if (m1) return decodeURIComponent(m1[1]);
+ const m2 = url.match(/\/([^/]+\.(?:jpe?g|png|gif|webp|tiff?|svg))(?:\/[^/]+)?$/i);
+ return m2 ? decodeURIComponent(m2[1]) : null;
+}
+
+async function commonsImageInfo(filename) {
+ const url = `https://commons.wikimedia.org/w/api.php?action=query&titles=File:${encodeURIComponent(filename)}&prop=imageinfo&iiprop=url|extmetadata&format=json&origin=*`;
+ try {
+ const r = await undiciFetch(url, {
+ headers: { 'user-agent': UA, accept: 'application/json' },
+ signal: AbortSignal.timeout(8000),
+ });
+ if (!r.ok) return null;
+ const j = await r.json();
+ const pages = j?.query?.pages || {};
+ const page = Object.values(pages)[0];
+ const ii = page?.imageinfo?.[0];
+ if (!ii) return null;
+ const meta = ii.extmetadata || {};
+ return {
+ license: meta.LicenseShortName?.value || meta.License?.value || null,
+ author: stripHtml(meta.Artist?.value || meta.Credit?.value || ''),
+ descriptionurl: ii.descriptionurl || null,
+ };
+ } catch { return null; }
+}
+
+function stripHtml(s) { return String(s || '').replace(/<[^>]+>/g, '').replace(/\s+/g, ' ').trim().slice(0, 240); }
+function sleep(ms) { return new Promise(r => setTimeout(r, ms)); }
diff --git a/src/lib/auth.js b/src/lib/auth.js
index 2bc17ed..44d6f2c 100644
--- a/src/lib/auth.js
+++ b/src/lib/auth.js
@@ -13,8 +13,18 @@ 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 };
+// __Host- prefix forces secure + path=/ + no Domain → cookie is bound to this
+// exact origin and rejected if served over plain HTTP. In production NODE_ENV
+// must be 'production' or browsers will discard the Set-Cookie header.
+const IS_PROD = process.env.NODE_ENV === 'production';
+const COOKIE = IS_PROD ? '__Host-animals_session' : 'animals_session';
+const COOKIE_OPTS = {
+ httpOnly: true,
+ sameSite: 'lax',
+ path: '/',
+ secure: IS_PROD,
+ maxAge: 90 * 24 * 60 * 60 * 1000
+};
export async function currentUser(req) {
const tok = req.cookies?.[COOKIE];
@@ -72,17 +82,30 @@ export async function signup(req, res) {
(home_state||'').toUpperCase().slice(0,2) || null, petsArr]
);
} catch (err) {
- if (err.code === '23505') return res.status(409).json({ error: 'email already registered' });
+ if (err.code === '23505') {
+ // Disambiguate: email and handle both have UNIQUE constraints.
+ // pickAvailableHandle has a race window (SELECT-then-INSERT) that can
+ // surface as "email taken" if we don't check err.constraint.
+ const c = err.constraint || '';
+ if (c.includes('email')) return res.status(409).json({ error: 'email already registered' });
+ if (c.includes('handle')) return res.status(409).json({ error: 'handle already taken — pick another' });
+ return res.status(409).json({ error: 'duplicate value', constraint: c });
+ }
throw err;
}
- // Auto-join the user's home-ZIP circle (create if missing)
+ // Auto-join the user's home-ZIP circle (create if missing). Bump member_count
+ // ONLY when we actually inserted a new membership row — prevents counter drift
+ // on retries / re-signups. Mirrors the pattern in community.js:151-153.
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`,
+ const membership = await one(
+ `INSERT INTO circle_memberships (circle_id, app_user_id) VALUES ($1, $2)
+ ON CONFLICT DO NOTHING RETURNING 1 AS inserted`,
[circle.id, user.id]
);
- await pool.query(`UPDATE circles SET member_count = member_count + 1 WHERE id = $1`, [circle.id]);
+ if (membership?.inserted) {
+ 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);
@@ -93,6 +116,11 @@ export async function signup(req, res) {
}
}
+// A constant dummy bcrypt hash ("animals-directory-no-such-user-padding") used
+// to keep login latency identical for unknown emails — closes the ~80ms timing
+// oracle that otherwise leaks "this email exists" to a brute-force enumerator.
+const DUMMY_HASH = '$2b$10$abcdefghijklmnopqrstuuKqcb1k3jY3oR3y7gQ.xS9Pf3LePz0w8m';
+
export async function login(req, res) {
try {
const { email, password } = req.body || {};
@@ -101,10 +129,12 @@ export async function login(req, res) {
`SELECT id, password_hash, suspended_at FROM app_users WHERE email = $1`,
[email.toLowerCase()]
);
- if (!u) return res.status(401).json({ error: 'invalid credentials' });
+ // Always run bcrypt — against the real hash if user exists, against
+ // DUMMY_HASH if not — so timing doesn't leak account existence.
+ const hashToCheck = u?.password_hash || DUMMY_HASH;
+ const ok = await bcrypt.compare(password, hashToCheck);
+ if (!u || !ok) 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 });
@@ -115,9 +145,27 @@ export async function login(req, res) {
}
export async function logout(req, res) {
+ // CSRF guard for logout: sameSite=lax permits top-level form POSTs from
+ // third-party origins, so a malicious page could log the user out without
+ // their consent. Require the request to come from our origin (same-origin
+ // fetch sends Origin header that we can verify) OR carry the X-CSRF header
+ // that our forms set. Anything else is rejected.
+ const origin = req.headers.origin || '';
+ const referer = req.headers.referer || '';
+ const host = req.headers.host || '';
+ // Wrap URL parsing — a malformed Origin/Referer would otherwise throw and
+ // turn a bad logout into an unhandled promise rejection (codex P1, 2026-05-01).
+ const safeHost = (s) => { try { return new URL(s).host; } catch { return null; } };
+ const sameOrigin =
+ (origin && safeHost(origin) === host) ||
+ (referer && safeHost(referer) === host) ||
+ req.headers['x-requested-with'] === 'fetch';
+ if (!sameOrigin) {
+ return res.status(403).json({ error: 'cross-origin logout rejected' });
+ }
const tok = req.cookies?.[COOKIE];
if (tok) await pool.query(`DELETE FROM app_sessions WHERE token = $1`, [tok]);
- res.clearCookie(COOKIE);
+ res.clearCookie(COOKIE, { ...COOKIE_OPTS, maxAge: undefined });
res.json({ ok: true });
}
@@ -142,25 +190,38 @@ async function pickAvailableHandle(base) {
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(
+ // SELECT-then-INSERT was racy: two concurrent first-signups in the same ZIP
+ // both miss the SELECT, both INSERT, the second hits 23505 on slug and gets
+ // mishandled upstream as "email already registered". Use upsert with
+ // RETURNING so we always get the row back, no race window.
+ const upserted = await one(
`INSERT INTO circles (kind, slug, name, zip, city, state, is_official)
VALUES ('zip', $1, $2, $3, $4, $5, TRUE)
+ ON CONFLICT (slug) DO UPDATE
+ SET city = COALESCE(circles.city, EXCLUDED.city),
+ state = COALESCE(circles.state, EXCLUDED.state)
RETURNING *`,
- [slug, `ZIP ${zip}${city ? ' · ' + city : ''}`, zip, city || null, (state||'').toUpperCase().slice(0,2) || null]
+ [slug, `ZIP ${zip}${city ? ' · ' + city : ''}`, zip, city || null,
+ (state||'').toUpperCase().slice(0,2) || null]
);
+ return upserted;
}
+
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') {
+ // PRIVACY: previously this fell back to a 3-digit-prefix (SCF) match,
+ // which can leak listings 100+ miles in CA/TX. Until marketplace_listings
+ // grows latitude/longitude columns (or we add a zip_centroids table) and we
+ // can do a real Haversine, restrict to exact-ZIP match plus the user's
+ // explicitly approved_zips list. UX regression vs. true 25mi is intended
+ // — we'd rather under-show than leak private listings outside the radius.
return user.home_zip === listing.zip
- || (user.approved_zips || []).includes(listing.zip)
- || zipsWithinPrefix(user.home_zip, listing.zip);
+ || (user.approved_zips || []).includes(listing.zip);
}
if (listing.visibility === 'statewide') return user.home_state === listing.state;
if (listing.visibility === 'approved_circles_only') {
diff --git a/src/scripts/2week-followup-audit.sh b/src/scripts/2week-followup-audit.sh
new file mode 100755
index 0000000..fe2027d
--- /dev/null
+++ b/src/scripts/2week-followup-audit.sh
@@ -0,0 +1,143 @@
+#!/bin/bash
+# 2-week follow-up audit on the 2026-05-01 hardening pass.
+# Fires once via ~/Library/LaunchAgents/com.steve.animals-2week-followup.plist.
+# Reads pm2 logs, queries animals_directory PG, emails summary via George.
+# After a successful run, the plist auto-removes itself.
+
+set -uo pipefail
+
+PROJECT=/Users/stevestudio2/Projects/animals
+PG_DB=animals_directory
+GEORGE_URL=http://localhost:9850/api/send
+# GEORGE_AUTH must be set in env or read from the secrets-manager .env. Never
+# inline the credential here — codex flagged 2026-05-01 (P1) that this file
+# could be committed/shared and leak the George Basic-auth secret.
+if [[ -z "${GEORGE_AUTH:-}" && -f "$HOME/Projects/secrets-manager/.env" ]]; then
+ GEORGE_AUTH=$(grep -E '^GEORGE_AUTH=' "$HOME/Projects/secrets-manager/.env" | head -1 | cut -d= -f2- | sed -E "s/^['\"](.*)['\"]\$/\1/")
+fi
+if [[ -z "${GEORGE_AUTH:-}" ]]; then
+ echo "[abort] GEORGE_AUTH not in env or secrets-manager — skipping email send" >&2
+fi
+SELF_PLIST=/Users/stevestudio2/Library/LaunchAgents/com.steve.animals-2week-followup.plist
+OUT_LOG="$PROJECT/logs/2week-followup-$(date +%Y%m%d-%H%M%S).log"
+
+exec > >(tee -a "$OUT_LOG") 2>&1
+echo "==== animals 2-week follow-up audit · $(date -u +%Y-%m-%dT%H:%M:%SZ) ===="
+
+# ── 1. pm2 log scans (last 14 days) ──────────────────────────────────────
+PM2_OUT=~/.pm2/logs/animals-viewer-out.log
+PM2_ERR=~/.pm2/logs/animals-viewer-error.log
+since=$(date -v-14d +%s)
+count_recent() {
+ local file="$1" pattern="$2"
+ if [ ! -f "$file" ]; then echo "0 (log absent)"; return; fi
+ awk -v since="$since" -v pat="$pattern" '
+ {
+ # pm2 log line format starts with: 2026-05-15T... or [2026-05-15T...]
+ if (match($0, /[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}/)) {
+ ts_str = substr($0, RSTART, RLENGTH)
+ cmd = "date -ju -f %Y-%m-%dT%H:%M:%S \"" ts_str "\" +%s 2>/dev/null"
+ cmd | getline ts; close(cmd)
+ if (ts < since) next
+ }
+ if (index($0, pat) > 0) c++
+ }
+ END { print (c+0) }
+ ' "$file"
+}
+
+RATELIMIT_HITS=$(count_recent "$PM2_OUT" "rate limit — slow down"; count_recent "$PM2_ERR" "rate limit — slow down")
+CSRF_BLOCKS=$(count_recent "$PM2_OUT" "cross-origin logout rejected"; count_recent "$PM2_ERR" "cross-origin logout rejected")
+echo "[1] rate-limit hits (last 14d): $RATELIMIT_HITS"
+echo "[1] csrf logout blocks (14d): $CSRF_BLOCKS"
+
+# ── 2. PG audit ──────────────────────────────────────────────────────────
+psql_q() { psql -d "$PG_DB" -tAc "$1" 2>/dev/null; }
+
+LISTING_14D=$(psql_q "SELECT count(*) FROM marketplace_listings WHERE created_at > NOW() - INTERVAL '14 days';")
+CIRCLES=$(psql_q "SELECT count(*) FROM circles;")
+SUM_MEMBER_COUNT=$(psql_q "SELECT COALESCE(sum(member_count), 0) FROM circles;")
+ACTUAL_MEMBERSHIPS=$(psql_q "SELECT count(*) FROM circle_memberships;")
+DRIFT=$((SUM_MEMBER_COUNT - ACTUAL_MEMBERSHIPS))
+
+echo "[2] listings created last 14d: $LISTING_14D"
+echo "[2] circles total: $CIRCLES"
+echo "[2] sum(member_count): $SUM_MEMBER_COUNT"
+echo "[2] actual circle_memberships: $ACTUAL_MEMBERSHIPS"
+echo "[2] member_count drift: $DRIFT (0 = HIGH#7 fix holding)"
+
+# ── 3. CRITICAL #2 status — lat/lng on marketplace_listings? ─────────────
+HAS_LAT=$(psql_q "SELECT COUNT(*) FROM information_schema.columns WHERE table_name='marketplace_listings' AND column_name='latitude';")
+HAS_LNG=$(psql_q "SELECT COUNT(*) FROM information_schema.columns WHERE table_name='marketplace_listings' AND column_name='longitude';")
+HOMEZIP25_COUNT=$(psql_q "SELECT count(*) FROM marketplace_listings WHERE visibility='home_zip_25mi';")
+echo "[3] marketplace_listings.latitude column exists? $HAS_LAT"
+echo "[3] marketplace_listings.longitude column exists? $HAS_LNG"
+echo "[3] listings marked home_zip_25mi: $HOMEZIP25_COUNT (still constrained to exact-ZIP only)"
+
+if [ "$HAS_LAT" = "1" ] && [ "$HAS_LNG" = "1" ]; then
+ CRIT2_STATUS="<strong style='color:#059669'>UNBLOCKED</strong> — lat/lng columns present. Recommend tightening <code>home_zip_25mi</code> from exact-ZIP back to true Haversine using the new columns."
+else
+ CRIT2_STATUS="<strong style='color:#b45309'>STILL DEFERRED</strong> — no lat/lng on marketplace_listings yet. UX impact: $HOMEZIP25_COUNT listings sit at exact-ZIP-only enforcement. If non-zero usage and customer feedback exists, add a migration."
+fi
+
+# ── 4. Sample listings to inspect visibility filter ──────────────────────
+SAMPLE_LISTINGS=$(psql_q "SELECT json_agg(row_to_json(t)) FROM (SELECT id, title, visibility, zip, state, created_at FROM marketplace_listings ORDER BY created_at DESC LIMIT 10) t;")
+[ -z "$SAMPLE_LISTINGS" ] && SAMPLE_LISTINGS="[]"
+
+# ── 5. Build HTML body and email via George ──────────────────────────────
+BODY_FILE=$(mktemp)
+cat > "$BODY_FILE" <<HTML
+<div style='font-family:-apple-system,Helvetica,Arial,sans-serif;max-width:760px;margin:0 auto;color:#1a1a1a'>
+<h2 style='border-bottom:2px solid #C9A14B;padding-bottom:10px'>Animals — 2-week follow-up audit (2026-05-15)</h2>
+<p>Looking back at the hardening pass landed on 2026-05-01 (3 CRITICAL + 6 HIGH + 7 MEDIUM fixes).
+viewer on <code>:9720</code>, PG <code>$PG_DB</code>.</p>
+<h3 style='margin-top:24px'>Hardening signal in pm2 logs</h3>
+<table style='width:100%;border-collapse:collapse;font-size:14px'>
+ <tr><td style='padding:8px;border:1px solid #ddd'>Rate-limit hits (MED #12)</td><td style='padding:8px;border:1px solid #ddd'><code>$RATELIMIT_HITS</code></td></tr>
+ <tr style='background:#fafafa'><td style='padding:8px;border:1px solid #ddd'>Cross-origin logout blocks (MED #16)</td><td style='padding:8px;border:1px solid #ddd'><code>$CSRF_BLOCKS</code></td></tr>
+</table>
+<h3 style='margin-top:24px'>PG state</h3>
+<table style='width:100%;border-collapse:collapse;font-size:14px'>
+ <tr><td style='padding:8px;border:1px solid #ddd'>Listings created last 14d</td><td style='padding:8px;border:1px solid #ddd'><code>$LISTING_14D</code></td></tr>
+ <tr style='background:#fafafa'><td style='padding:8px;border:1px solid #ddd'>circles count</td><td style='padding:8px;border:1px solid #ddd'><code>$CIRCLES</code></td></tr>
+ <tr><td style='padding:8px;border:1px solid #ddd'>sum(member_count)</td><td style='padding:8px;border:1px solid #ddd'><code>$SUM_MEMBER_COUNT</code></td></tr>
+ <tr style='background:#fafafa'><td style='padding:8px;border:1px solid #ddd'>actual circle_memberships</td><td style='padding:8px;border:1px solid #ddd'><code>$ACTUAL_MEMBERSHIPS</code></td></tr>
+ <tr><td style='padding:8px;border:1px solid #ddd'><strong>member_count drift</strong> (0 = HIGH#7 fix holding)</td><td style='padding:8px;border:1px solid #ddd'><code>$DRIFT</code></td></tr>
+</table>
+<h3 style='margin-top:24px'>CRITICAL #2 status</h3>
+<p>$CRIT2_STATUS</p>
+<h3 style='margin-top:24px'>10 most recent listings (visibility spot-check)</h3>
+<pre style='background:#f6f8fa;padding:12px;border-radius:6px;font-size:12px;overflow:auto'>$(echo "$SAMPLE_LISTINGS" | python3 -m json.tool 2>/dev/null || echo "$SAMPLE_LISTINGS")</pre>
+<hr style='margin:24px 0'>
+<p style='font-size:12px;color:#666'>Full audit log: <code>$OUT_LOG</code><br>Plist self-removes after this run.</p>
+</div>
+HTML
+
+PAYLOAD=$(python3 -c "
+import json, sys
+body = open('$BODY_FILE').read()
+print(json.dumps({
+ 'to': 'steve@designerwallcoverings.com',
+ 'subject': 'Animals — 2-week follow-up audit (post 2026-05-01 hardening)',
+ 'body': body
+}))
+")
+
+echo ""
+echo "[email] sending via George…"
+HTTP=$(curl -s -o /tmp/animals-2wk-followup.resp -w "%{http_code}" \
+ -X POST "$GEORGE_URL" \
+ -H 'Content-Type: application/json' \
+ -H "Authorization: $GEORGE_AUTH" \
+ --data-raw "$PAYLOAD")
+echo "[email] http=$HTTP body=$(cat /tmp/animals-2wk-followup.resp)"
+rm -f "$BODY_FILE" /tmp/animals-2wk-followup.resp
+
+# ── 6. Self-remove (one-shot) ────────────────────────────────────────────
+if [ -f "$SELF_PLIST" ]; then
+ echo "[cleanup] unloading + removing $SELF_PLIST"
+ launchctl bootout "gui/$(id -u)/com.steve.animals-2week-followup" 2>/dev/null || true
+ rm -f "$SELF_PLIST"
+fi
+
+echo "==== done ===="
← 95e913a fix(community): debate-driven fixes for visibility, comments
·
back to Animals
·
fix(events): codex P1 XSS in /shows map + P2 dedupe collisio b70d15d →