cycle 15 docs: YOLO_NOTES ledger + GO-LIVE pg_trgm prerequisite

f899e1c5c7df35e151dbdff6146513d74f7f5b17 · 2026-09-23 23:17:30 -0700 · Steve

Records the SQL-correctness review (clean) + the search trigram-index fix
(Cody-benchmarked ~20x), the CONCURRENTLY + q-floor fixes, the documented
inline-server-route test-harness gap, and the pg_trgm superuser prereq in
the go-live migration step.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01TouFkmUGKHtwqpZwgReVic

Files touched

M YOLO_NOTES.md
M docs/GO-LIVE.md

Diff

commit f899e1c5c7df35e151dbdff6146513d74f7f5b17
Author: Steve <steve@designerwallcoverings.com>
Date:   Wed Sep 23 23:17:30 2026 -0700

    cycle 15 docs: YOLO_NOTES ledger + GO-LIVE pg_trgm prerequisite
    
    Records the SQL-correctness review (clean) + the search trigram-index fix
    (Cody-benchmarked ~20x), the CONCURRENTLY + q-floor fixes, the documented
    inline-server-route test-harness gap, and the pg_trgm superuser prereq in
    the go-live migration step.
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
    Claude-Session: https://claude.ai/code/session_01TouFkmUGKHtwqpZwgReVic
---
 YOLO_NOTES.md   | 20 ++++++++++++++++++++
 docs/GO-LIVE.md |  2 ++
 2 files changed, 22 insertions(+)

diff --git a/YOLO_NOTES.md b/YOLO_NOTES.md
index e65e842..1d049a2 100644
--- a/YOLO_NOTES.md
+++ b/YOLO_NOTES.md
@@ -356,3 +356,23 @@ Canonical apply order now: `004_marketplace → 005_apple → 006_contacts → 0
 **Cost:** $0 (local tests + one Cody pass — which executed computeSplit to prove the guard checked the wrong column).
 
 **Backlog:** approval-enforcement decision on Steve's desk; provider-honored idempotency key + sig-encoding (live-only); per-event webhook-auto-reply try/catch; processorFee schema prerequisite (documented). Next cycle: SQL-correctness (missing hot-path indexes, admin-stats JOIN miscounts) or another fresh Cody-driven audit.
+
+## /yoloforever CYCLE 15 (2026-09-23, yf-costa) — SQL-correctness review → search hot-path trigram indexes
+
+**SQL correctness: CLEAN.** No double-count JOINs (admin `/stats` + build `/status` use independent scalar subqueries; the region/vertical aggregates are many→one COUNT(p.id), correct). No SQL injection (grep confirmed every query is `$1`-parameterized; no template-literal interpolation). The booking overlap query is already indexed (`idx_bookings_place_status_checkin` + a gist EXCLUDE). IDOR was already cleared in cycle 14.
+
+**The one real, MEASURED find — search performance:** `/api/search` + `/api/places` filter with a leading-wildcard `LIKE '%q%'` on `lower(name)/lower(address)/lower(description)` — unindexable by btree, so every search full-scanned the 34,285-row (growing) `places` table. The no-LIMIT `COUNT(*)` queries always did; `/api/search` can't even short-circuit (it `ORDER BY`s a computed rank).
+
+**Fix: `scripts/migrate_010_search_trgm.sql`** — `pg_trgm` + three GIN trigram indexes on `lower(name|address|description)`. Cody independently benchmarked on the dev DB: **~20x** (a ~30ms parallel seq scan → a ~1.5ms Bitmap Index Scan, BitmapOr of the three trgm indexes) for ~11.6MB storage. The index expression `lower(col) gin_trgm_ops` matches the query's `LOWER(col) LIKE` exactly (all call sites `.toLowerCase()` in JS).
+
+**Cody gate — FIX FIRST, both applied:**
+1. The migration recommended `CONCURRENTLY` in a comment but shipped **plain `CREATE INDEX`** (a copy-paste footgun — a plain build SHARE-locks the live `places` table, ~620ms now, worse as it grows). Changed to `CREATE INDEX CONCURRENTLY` (safe — `apply-migrations.sh` runs file 010 outside a transaction) + an invalid-index recovery note + the benchmarked write-cost justification (~44µs/row GIN overhead, immaterial vs the network-bound one-row-per-fetch scraper writes; `hacienda-enricher` never touches these columns).
+2. `/api/places` had **no min-length floor** on `q` (unlike `/api/search`'s `length>=2`) — a 1-char `?q=a` full-scanned and returned ~the whole directory. Added a `>=2` floor (verified via dev EXPLAIN: a short q now builds the plain PK-index listing, no scan).
+
+**Honest gap noted (future cycle):** inline `server.js` routes (~20, incl. `/api/places`) have NO test harness — server.js `app.listen`s on import and doesn't export the app. The q-guard is verified by dev EXPLAIN, not a route unit test; a follow-up should export the app behind `require.main === module` to make these routes testable.
+
+**Applied to dev** (reversible: DROP INDEX/EXTENSION). Prod `CREATE EXTENSION pg_trgm` needs superuser → Steve-gated (documented in `docs/GO-LIVE.md §3` + the migration). Suite unchanged 185/185 (indexes don't change results).
+
+**Cost:** $0 (local + one Cody pass — which independently ran EXPLAIN/benchmarks against the dev DB, confirming the 20x and the write cost rather than trusting my numbers).
+
+**Backlog:** approval-enforcement decision on Steve's desk; the inline-server-route test harness (export app for testability); provider-honored idempotency key + sig-encoding (live-only); processorFee schema prerequisite. Next cycle: the server.js testability refactor (unlocks testing ~20 inline routes), or another fresh Cody-driven audit.
diff --git a/docs/GO-LIVE.md b/docs/GO-LIVE.md
index dbf9147..5e00d2d 100644
--- a/docs/GO-LIVE.md
+++ b/docs/GO-LIVE.md
@@ -41,6 +41,8 @@ psql "$DATABASE_URL" -c "SELECT conname FROM pg_constraint WHERE conname IN ('bo
 ```
 NEVER `--baseline` on prod (marks pending files applied WITHOUT running → would skip the double-book/integrity guards); the runner refuses it unless `FORCE=1`. For partial adoption use `--baseline-through <file>`.
 
+**`migrate_010_search_trgm.sql` prerequisite:** it runs `CREATE EXTENSION pg_trgm`, which needs **SUPERUSER / rds_superuser**. If the app's DB role isn't a superuser on prod, run the extension line once as the superuser BEFORE the migration pass: `psql "$SUPERUSER_URL" -c 'CREATE EXTENSION IF NOT EXISTS pg_trgm;'`. The three `CREATE INDEX CONCURRENTLY` lines then build without blocking writes to `places`; if a build aborts it leaves an INVALID index → `DROP INDEX CONCURRENTLY IF EXISTS idx_places_<col>_trgm;` and re-run.
+
 ## 4. Deploy code to prod — SURGICAL only (do NOT use canonical /deploy)
 `deploy.sh` uses `rsync --delete` and prod holds prod-only data (`data/img/places/*.jpg` live images, OSM cache) missing locally → `/deploy` would erase them. Until local↔prod converge, ship code with a no-delete rsync (lib/routes WITHOUT trailing slashes so they stay directories):
 ```

← 3bd9aa2 costa-rica: trigram GIN indexes for the search hot path + /a · back to Costa Rica · costa-rica: export the app from server.js so inline routes a 2d3cca4 →