# Build Log — Ace Pitch Engine V2 Read this first at the start of every session. Update it last at the end of every session. This is what prevents hallucinated continuation after a reset. --- ## Session 1 — 2026-08-19 — Phase 1: Foundation ### Completed - Full project scaffold: `package.json`, `.env.example`, `.gitignore` - SQLite schema (`src/db/schema.sql`) with all six tables from the brief: `businesses`, `research_versions`, `pitches`, `pdfs`, `sends`, `client_notes`. `pitches`/`pdfs`/`sends`/`client_notes` are created now but unused until Phase 3+, so the schema won't need structural changes later. - `src/db/connection.js` — single shared better-sqlite3 connection, WAL mode, applies schema automatically (idempotent) as a side effect of being required, so route modules that prepare statements at require-time never race the schema. - `src/db/businessesRepo.js` — upsert-by-`place_id` (no duplicate businesses on re-search) + version-stacking into `research_versions` in a single transaction. - `src/services/serpapi.js` — Google Maps search via SerpApi, normalizes results without ever inventing a field. Fields SerpApi doesn't return (e.g. email) are left `null`, not guessed. - `src/services/scoring.js` — opportunity scoring (0-100): no-website detection (+40), review sweet spot 10-75 reviews (+20), rating thresholds (≥4.0 → +15, <3.5 → -10), free-email domain detection (+15). Every factor returned alongside the score traces to a real field on the business record — nothing scored on data that isn't present. - Routes: `GET /health` (unauthenticated, for UptimeRobot), `POST /api/search`, `GET /api/businesses`, `GET /api/businesses/:id`. - `src/middleware/apiKeyAuth.js` — shared-secret `x-api-key` header check on everything under `/api`. Refuses to boot-serve `/api` routes if `ACE_API_KEY` is left at the placeholder value. ### Tested (locally, in the build sandbox — not on the VPS) - `npm install` completes cleanly, no errors. - Schema applies correctly — verified all 7 tables (6 + sqlite_sequence) exist after boot. - `GET /health` → 200, no auth required. - `GET /api/businesses` with no key → 401. With correct key → 200. - Scoring engine unit-tested against two representative business shapes (no-website mid-traction barbershop scored 75; established high-review salon with a site scored 5) — factors list matched expectations exactly. - Upsert + version stacking tested directly against `businessesRepo.js`: re-inserting the same `place_id` with changed rating/reviews updated the existing business row (no duplicate) and correctly created `research_versions` row #2 alongside #1. - **Not tested**: an actual live SerpApi call (no API key available in the build sandbox). The `searchGoogleMaps` → route wiring is untested against a real API response shape — first live search on the VPS should be watched closely in case SerpApi's actual `local_results` field names differ slightly from what's assumed in `normalizeResult`. ### Open decisions / things to verify next session - Confirm real SerpApi `google_maps` engine response field names match `normalizeResult` assumptions (`title`, `address`, `phone`, `website`, `place_id`, `type`, `rating`, `reviews`, `link`). Run one real search on the VPS and diff against a saved raw response before trusting scores at scale. - No process manager config shipped (pm2 instructions are in README but not automated) — operator needs to set that up manually on the VPS. - No reverse proxy / SSL config included — assumed to be handled by operator's existing VPS nginx/Caddy setup, not part of this app. ### Next phase **Phase 2 — Research depth**: Apify integration as secondary source, duplicate detection/merge (name + phone + website + address matching, uncertain matches flagged for manual review), refine scoring to show its factors more granularly across combined sources. --- ## Session 2 — 2026-08-19 — SQLite library swap + Phase 2: Research depth ### Part A: better-sqlite3 → node-sqlite3-wasm (risk mitigation, requested before Phase 2) **Why**: better-sqlite3 is a native C++ addon. Truehost's cPanel Node.js Selector is shared hosting with no guaranteed build toolchain, so if no prebuilt binary matches the exact platform/Node ABI, `npm install` could fail outright with no way to compile from source. **What changed**: swapped to `node-sqlite3-wasm` (SQLite compiled to WebAssembly, ships a prebuilt `.wasm` binary in the npm package). Verified via `npm install` that no `node-gyp`, `prebuild-install`, or `bindings` packages appear anywhere in `node_modules` — there is nothing for the host to compile. **Does this fix the risk completely or only reduce it?** It **fully eliminates the native-compilation failure mode specifically** — there is no native addon at all, so ABI/platform mismatches can't happen; WebAssembly runs through Node's built-in `WebAssembly` support on any platform Node itself runs on. It does **not** eliminate all deployment risk: - `node-sqlite3-wasm` is a small library (~90 GitHub stars vs better-sqlite3's ~3M weekly downloads) — far less battle-tested, especially for edge cases this app doesn't exercise (BLOBs, 64-bit integers beyond `Number.MAX_SAFE_INTEGER`). - Its locking model is directory-based, not a real OS file lock, and its `close()` uses SQLite's `close_v2` deferred-close semantics. Both required real fixes below rather than being drop-in transparent — see Part A.1 and A.2. These were not simple "swap the require line" issues; they required understanding the library's actual internals. - No WAL mode support (falls back to SQLite's default rollback journal — irrelevant for this single-process app, but noted as a behavioral difference from better-sqlite3). **A.1 — Stale lock directory after unclean shutdown (fixed, tested).** The library creates a directory at `.lock` to guard against concurrent writers, released only inside `close()`. A crash, OOM-kill, or Passenger force-restarting the app without a graceful SIGTERM leaves that directory behind, and every future boot fails immediately with "database is locked" until someone deletes it by hand — a real outage risk under Passenger. Fixed in `src/db/connection.js`: since this app is explicitly single-operator/single-process, a lock directory found at boot can only be stale, so it's checked for and removed proactively before opening the database. - *Tested*: manually planted a stale lock directory (simulating an unclean SIGKILL), confirmed the app self-heals and boots normally instead of crashing (verified via direct `node -e` calls, not full server subprocess — background-process testing proved unreliable in this build sandbox, see "not tested" below). **A.2 — `close()` doesn't actually release the lock (fixed, tested).** Deeper issue found while testing A.1: `node-sqlite3-wasm`'s `close()` uses `sqlite3_close_v2` semantics — it does NOT release the database until every prepared statement created from it has been finalized, and the library's own docs confirm this isn't automatic. This app's whole prepared-statement usage pattern (cache once at module load, reuse for the app's lifetime — same convention as better-sqlite3) means statements are never individually finalized during normal operation, so without a fix, `close()` would silently never release the lock, even after a graceful SIGTERM. Fixed by tracking every statement created through the `db.prepare()` compatibility wrapper in a `Set`, and finalizing all of them right before `close()` calls the underlying `rawDb.close()`. Also fixed `health.js`, which was creating a brand new (never-finalized) statement on every single request instead of caching one at module load — that would have been a slow-drip memory leak for a route UptimeRobot hits repeatedly, on top of the lock issue. - *Tested*: three independent checks after the fix — (1) a real write + cached-statement read + normal process exit releases the lock, (2) a manually-planted stale lock self-heals on boot and still releases cleanly on exit, (3) the actual HTTP server hit 5 times via `/health` then sent a real SIGTERM — lock released, no leak. All three passed. - *Not tested*: a true `SIGKILL` (uncatchable, simulates OOM-kill) against the actual running HTTP server subprocess — attempted this directly but background-process state did not reliably persist across separate tool calls in this build sandbox (processes and /tmp files vanished between calls even with `nohup`). The equivalent scenario *was* verified at the database-connection level directly (manually creating the lock directory to simulate what SIGKILL would leave behind, per A.1) — but the exact "process gets SIGKILL'd mid-write on the live server" sequence itself is unverified. Worth a real-world smoke test on the VPS: start the app, `kill -9` it while a search is running, confirm the next boot self-heals. **Compatibility layer**: `src/db/connection.js` also bridges better-sqlite3's calling conventions (multi-arg positional binds, named params without prefix characters, `db.transaction(fn)`) onto node-sqlite3-wasm's native API (single `values` arg, prefixed param names, no transaction helper), so every query-calling file in the project (`businessesRepo.js`, etc.) needed zero changes to its actual query code. **Regression-tested against Phase 1 behavior**: schema creation (all 7 tables), upsert-by-`place_id` + version-stacking, and the full HTTP surface (health check, auth gating 401/200) all re-verified working identically to Session 1 after the swap. ### Part B: Phase 2 — Research depth **Completed** - `src/services/apify.js` — Apify integration via the official `apify-client` package (`client.actor(id).call(input)` → `client.dataset(...).listItems()`, confirmed against Apify's own docs). Actor-agnostic: actor ID and input shape are both configurable via env vars (`APIFY_ACTOR_ID`, `APIFY_ACTOR_INPUT_JSON`) since the brief doesn't pin down a specific actor and different actors (Yelp scraper, Yellow Pages scraper, etc.) return different field names for the same data. `normalizeApifyItem()` defensively checks several common field-name variants per concept and leaves a field `null` if none match — same no-fabrication discipline as `serpapi.js`. **Deliberately does not** treat a directory listing's own page URL (`item.url`) as the business's website — conflating those would fabricate a "has a website" signal. - `src/services/dedup.js` — cross-source fuzzy duplicate matching (name + phone + website + address), no external dependency (self-contained token-overlap / Jaccard similarity, phone normalized to last-10-digits, website normalized to bare hostname). Confidence tiers: `high` (phone or website domain match exactly, or very high name+address similarity) → auto-merge; `medium` (partial name/address overlap) → flagged, never auto-merged; `none` → treated as a genuinely different business. - `src/services/scoring.js` — added `scoreCombinedBusiness()` alongside the unchanged Phase 1 `scoreBusiness()`. Combines evidence across every source known for a business: website absent-everywhere still scores the full no-website bonus; present on some sources but not others is surfaced as a "citation conflict" factor instead of silently averaged away; review counts sum across sources; ratings average across sources; free-email counted once even if multiple sources surface it. Every factor is tagged with its source, e.g. `[apify:yelp] 12 reviews`. - `src/db/schema.sql` — added `normalized_snapshot` column to `research_versions` (lets combined scoring re-read prior sources' evidence without re-deriving it from raw provider JSON) and a new `duplicate_candidates` table (flat, two business_id references, no CRM-style relationship modeling — per the brief's explicit no-CRM-scale-infrastructure rule). - `src/db/businessesRepo.js` — `upsertWithResearch()` rewritten to: try exact `place_id` match first (unchanged fast path for SerpApi), fall back to fuzzy matching for sources without one, merge on `high` confidence, insert-as-new + flag on `medium`, and recompute the business's combined score from every known source's latest snapshot on every upsert. All business-row fields now use `COALESCE(new, existing)` on update instead of unconditional overwrite, so a merge from a source with sparser data (e.g. Apify listing missing an address) never erases data already known from a richer source. `place_id` specifically uses `COALESCE(existing, new)` — once set it's never overwritten, but a business first found via Apify can adopt a place_id later if a matching SerpApi result comes in, putting it on the fast exact-match path for all future re-searches. - `src/routes/search.js` — added `POST /api/search/apify` (mirrors `POST /api/search`, runs the Apify pipeline instead). Shared storage/response logic extracted into `storeAndRespond()`. Responses now include `mergeType` (`new` | `exact_place_id` | `fuzzy_high` | `flagged_possible_duplicate`) and `flaggedDuplicateCandidateId` per business, so the operator can see in the API response itself whether something was merged, flagged, or brand new. - `src/routes/businesses.js` — added `GET /api/duplicates`, listing pending medium-confidence matches with both businesses' full data and the match reasons. Pure data endpoint, no review/merge UI (that's Phase 5's duplicate-review queue). - `.env.example` / `package.json` — added `apify-client` dependency and `APIFY_ACTOR_ID` / `APIFY_ACTOR_INPUT_JSON` / `APIFY_RUN_TIMEOUT_SECONDS`, with inline comments flagging the actor-choice ambiguity. **Tested (locally, in the build sandbox)** - `dedup.js` unit-tested against four hand-built cases (exact phone match, exact website-domain match despite different protocol/path, partial name+address overlap, and two genuinely unrelated businesses) — all four classified correctly (`high`, `high`, `medium`, `none`). - `scoreCombinedBusiness()` unit-tested: agreeing-evidence case, a citation-conflict case (website found via one source, not another — confirmed the no-website bonus is correctly withheld and the conflict is surfaced as its own factor instead), and confirmed single-source input produces numerically identical scores to the original `scoreBusiness()` (75 either way), with the new source-tagged factor format. - Full `businessesRepo.js` flow tested end-to-end through three real scenarios in sequence: (1) SerpApi finds a business fresh → inserted; (2) Apify/Yelp finds the same business (phone match) → merged into the same row, combined score correctly jumps from 75 to 90 reflecting both sources' evidence; (3) a third, similarly-named/addressed but unrelated-enough listing → correctly inserted as a new business with no duplicate flag (didn't clear the medium threshold — expected given the test data). Re-ran with data deliberately crafted to clear the medium threshold (75% name similarity, 86% address similarity, no phone overlap) — confirmed it inserted as a *separate* business AND recorded a `duplicate_candidates` row with `status: pending`, exactly per the "flag, don't auto-merge" requirement. - HTTP-level: `GET /api/duplicates` correctly surfaces the flagged pair with full business data and match reasons; `GET /api/businesses` shows correct combined scores; `POST /api/search/apify` fails cleanly with a 502 and a clear error message when `APIFY_TOKEN`/`APIFY_ACTOR_ID` aren't configured (doesn't crash the server). - Every test above ended with an explicit lock-file check — all passed clean (no leaked `.lock` directory). **Not tested** - The Apify pipeline has never been run against a real Apify actor (no `APIFY_TOKEN` available in the build sandbox). `normalizeApifyItem()`'s field-name mapping is a best-effort guess across common variants used by different scraper actors and **has not been verified against real output**. Before trusting Apify-sourced scores: run one real search, open the corresponding `research_versions.raw_response` for that entry, and confirm the field names in `apify.js`'s `normalizeApifyItem()` actually match what your chosen actor returns. Adjust the field-name lists there if not. - **Carried over from Phase 1, still unverified**: the SerpApi `google_maps` engine field-name assumptions in `serpapi.js` (`normalizeResult()`) have still never been checked against a real live SerpApi response — no key was available in Session 1 or Session 2's sandbox either. This should be checked at the same time as the Apify verification above, before trusting either source's scores at scale. - SIGKILL against the live running server (see A.2 "Not tested" above) — the underlying lock behavior was verified at the connection layer directly, but not via an actual `kill -9` on the live process due to sandbox limitations with persisting background processes across tool calls. Worth a real smoke test on the VPS. - `dedup.js`'s similarity thresholds (0.55 for medium, 0.9/0.7 for high) are reasonable starting points but untested against real messy directory-listing data — actual Yelp/Yellow Pages naming and address formatting conventions may need threshold tuning once real Apify data is flowing. ### Open decisions / things to verify next session - Confirm real SerpApi field names (carried over from Session 1, still open). - Confirm real Apify actor field names once `APIFY_ACTOR_ID` is chosen and a real token is available. - Consider tuning `dedup.js` similarity thresholds after seeing real cross-source match/near-miss data. - Do a real unclean-kill smoke test on the VPS (SIGKILL the live process, confirm self-heal on next boot) — see A.2. ### Next phase **Phase 3 — AI + Pitch**: Dual-AI orchestration (OpenAI for structuring/scoring, Anthropic for pitch narrative). Conversational Pitch Engine: angle selection based on evidence (no site / weak reviews / citation conflict / already strong — pick a different angle per case, avoiding "no website" as the only opening). The `duplicate_candidates` and citation-conflict evidence from Phase 2 directly feed the "citation conflict" pitch angle already anticipated in the original brief. Client Notes field per business + AI extraction pass (manual trigger, not automatic pipeline).